I am getting the below error while executing "report obsolete" and "delete obsolete " commands in RMAN. except these two all the RMAN commands are working fine like "list backup summary" "list backup" .....
Error:
RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of delete command at 08/28/2012 11:03:04 RMAN-06004: ORACLE error from recovery catalog database: ORA-01403: no data found OS: IBM AIX Oracle Version: 9.2.0.8
I need to remove duplicate values from concatenated long string of state codes(comma separated). Ex: 'VA,VA,PA,PA,CT,NJ,CT,VA'. I tried following query and did not get required out put.
select regexp_replace('VA,VA,PA,PA,CT,NJ,CT,VA,CT,PA,VA,CT','([^,]*)(,1)+($|,)', '13') new_str from dual;
Define Meta-character's format in regular expression to get desired result. Out put required: VA,PA,CT,NJ (with out any duplicates).
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options ******************************************************************************** SYS AS SYSDBA in MyDB....> startup ******************************************************************************** ORA-32004: obsolete and/or deprecated parameter(s) specified ORACLE instance started.
Total System Global Area 612368384 bytes Fixed Size 1250428 bytes Variable Size 121637764 bytes Database Buffers 482344960 bytes Redo Buffers 7135232 bytes Database mounted. Database opened.
SYS AS SYSDBA in MyDB....> ******************************************************************************** PFILE.ORA ******************************************************************************** *.db_name='MYDB' *.db_unique_name='MYDB'
We have EM (Cloud 12c) initiated scheduled backup jobs that perform (all below are online backups, obviously archivelog mode)
- level 0 Incremental 3AM on Sunday - with options backup all archive logs, delete input, delete obsolete
- level 1 Incremental (non cumulative) 3AM every other day - same options
Recovery window is set to 3 days, other options below:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 3 DAYS; CONFIGURE BACKUP OPTIMIZATION OFF; # default CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default CONFIGURE CONTROLFILE AUTOBACKUP ON; CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 2;
[code]....
Everything appears to be working normally (archive logs are being backed up and once they are the archive logs themselves are being deleted from the FRA), but I'm having some trouble understanding the behavior of the delete obsolete.
With a recovery window of 3 days, on Monday at 11AM my expectation would be that I would find in backups the following:
- level 0s from day before (Sunday) and the previous Sunday (8 days prior)
- all level 1s from previous Sunday on (so Mon-Sat and this morning)
- archivelog backups from Friday until this morning
What I'm not understanding is that I'm seeing archivelog backups from last Monday, Tuesday, etc. - i would have thought these would have gone obsolete based on my recovery window, yet when I run report obsolete in RMAN nothing is showing up.
I am working on Oracle 10g Rel 2. I am unable to delete the OBSOLETE from rman
previously my senior use to take backup on remote m/c.
While giving the command
rman>delete obsolete
RMAN-06207: WARNING: 228 objects could not be deleted for DISK channel(s) due RMAN-06208: to mismatched status. Use CROSSCHECK command to fix status RMAN-06210: List of Mismatched objects RMAN-06211: ========================== RMAN-06212: Object Type Filename/Handle RMAN-06213: --------------- --------------------------------------------------- RMAN-06214: Backup Piece \192.168.1.162BACKUPFINPRODDB_T619092194_S12_P1 RMAN-06214: Backup Piece \192.168.1.162BACKUPFINPRODDB_T619093015_S13_P1
I've created a small script witch delete the obsolete and expired backups of RMAN.
The problem is: the "delete obsolete" and the "delete expired backup" command needs a confirmation with YES. I don't need to confirm the command and he deletes the backups directly?
Here is my little script:
#!/bin/ksh export ORACLE_SID=dbtest2 export ORACLE_HOME=/home/oracle/srv/ora/product/11.2.0/dbtest_2 export ORACLE_BIN=/home/oracle/srv/ora/product/11.2.0/dbtest_2/bin alias RMAN=$ORACLE_BIN/rman
We observed that our RMAN backup was taking more time to complete. CROSSCHECK is the main culprit as because of having data in the catalog for more than 3 years.
So in the part of script we added delte obsolete recovery windoe of 14 days. (Redundancey 1).
Here the problem is we are getting error as
RMAN-03002 - failure of delete command RMAN-06091 - NO channel allocated for maintenance (of an appropriate type).
Reason: Till 2 years back the backup was stored in tape and after that it got changed to disk. So allocating channel disk to delete obsolet is not working. How we can over come this.
We tried like allocating channel tape sbt_type. It got failed as no media manager information configured. We dont have that information.
I have a problem in running delete obsolete or report obsolete using rman. Although the retention policy is set to "CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 60 DAYS;", when running delete obsolete or report obsolete it deletes or returns all the backupsets till sysdate. What should I do to solve this issue?
I am trying to delete the backupset using Delete Obsolete command, but i am unable to get the success. As per the oracle Doc after completion of Retention Period oracle automatically deletes the backupset. I have set the retention period of 8 days.
Environment: 2 Node Oracle(10.2.0.4.0) RAC on Aix 6.1.
Backups using RMAN utility.Our system is configured to take backups on Tape library using TSM server.
We take incremental level 1 backup on all days and Level 0 backup on Wednesday.We would like to keep the RMAN retention period of 14 days.I delete the backups that are 7 days old from the disk to save space and 14 days older backup from Tape.
I am getting below error in my RMAN backup logfiles.Also attached
RMAN-06207: WARNING: 147 objects could not be deleted for DISK channel(s) due RMAN-06208: to mismatched status. Use CROSSCHECK command to fix status RMAN-06210: List of Mismatched objects RMAN-06211: ========================== RMAN-06212: Object Type Filename/Handle RMAN-06213: --------------- ---------------------------------------------------
The configuration of RMAN is:
RMAN> show all; RMAN configuration parameters are: CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS; CONFIGURE BACKUP OPTIMIZATION ON; CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE'; CONFIGURE CONTROLFILE AUTOBACKUP ON;
[code].....
How to avoid this error ? Can we set "Retention period" as 14 days in RMAN level and delete the backups in disk which is older than 7 days?Should the deletion of tape backup be handled in TSM level ?
- level 1 for datafile not backed up since 'sysdate-1' then another policy makes:
- level 0 when the datafiles become read only
The recovery windows is set to 1 day.
In the following situation:
RMAN> list backup of datafile 394;
List of Backup Sets =================== BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 12721 Incr 0 13.25M SBT_TAPE 00:01:13 25-MAY-11 BP Key: 12777 Status: AVAILABLE Compressed: NO Tag: ST_TRONLINE_FB20110525 Handle: bk_13201_1_752082473 Media: A00003
[code]....
I expect that the first L0 backup is going to become obsolete, but even if I run crosscheck, it never become obsolete.
note in our database we changed RMAN backup to redundancy 7.
RMAN> show all; using target database control file instead of recovery catalog RMAN configuration parameters are: CONFIGURE RETENTION POLICY TO REDUNDANCY 7; CONFIGURE BACKUP OPTIMIZATION OFF; # default CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE MAXSETSIZE TO UNLIMITED; # default CONFIGURE ENCRYPTION FOR DATABASE OFF; # default CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/10.2.0/db_1/dbs/snapcf_mscmada.f'; # default
Also we have scheduled in our dailly backup to delete obsolete backup means to delete backup which completed more that 7 times, but when we check our backup still in our disk for 1 Month.Note we have scheduled to delete obsolete backup dailly.
I have set the RETENTION POLICY TO RECOVERY WINDOW Values to 2 days,but my backup sets which backuped before 2 days(30-NOV-11),and the backup set is avaliable also,i think it must be expired or obsolete,why?
RMAN> show all; RMAN configuration parameters are: CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS; CONFIGURE BACKUP OPTIMIZATION OFF; # default CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
I used Region, Process by to search the report which appears as shown above. Then I use Choose Auditors column to select my Auditor and copy paste it into the report under To be Audited By col. Is there a way to automate the process. I am here using a tabular form in APEX. My main aim is to assign auditors based on Region, not equal to Processed by.
As I am doing a outer join, if the resource is not available on a particular day the resource_id is coming as NULL as it is not available. Is there any way to populate this NULL resource_id with the original resource_id as the resource_id is same for all the result set.
My scenario is to insert values into 'out' column by comparing 's' and 'IP' columns of temp table.The exact situation is at first need to go to ip column,take a value and then go to source column and check for the same value of ip which is taken previously.Then after corresponding ip of that source column should be inserted back in previous source column.
The situation is marked clearly in file which i am attaching with '--' comments at respective places.I am also pasting the code which i tried out,unfortunately it is giving error as exact fetch returns more than requested number of rows since there are duplicates in the table.I tried it using nested for loops.Also implemented using rowid,but it didnt work.
fixing the errors or if there is any new logic that can be implemented.
DECLARE i_e NUMBER(10); BEGIN FOR cur_1 IN(SELECT IP from temp where IP IS NOT NULL) LOOP FOR cur_2 IN(SELECT IP from temp where s=cur_1.IP)
I receive source data with leading zeros. The Rules says: "If there is a leading 0 it has to be removed and data has to be shown starting with character 2".Here is my code I started with:
I am searching the simplest way for ad hoc MINUS.I do:
SELECT * FROM uam_rss_user_XXXXXXX WHERE host_name IN ('XXX0349', 'XXX0362', 'XXX0363', 'XXX0343', 'XXX0342', 'XXX0499', [code]....
and look in the table which values are missing (values that are in host_name IN but not in actual table).is there a simpler way for doing an ad hoc MINUS? I know to insert values in temp. Table. How are experienced Oracle pros doing this task?
I have a file name field in my database that stores each file name with the extension .TXT and almost each file name is different.I would like to remove this extension from all of the file names without using the different file name each time I update. Is there any SQL statement that will allow me to do this? I am using Oracle.
I install oracle 10 g but I don't remember password . so I then remove oracle 10 g by select Universal Installer and click Deinstall and then reinstall but I can't reinstall because I don't remove/delete oracle_home I then delete folder in path C:Oracle and reinstall Question
1.in this remove oracle 10 g method true or wrong if wrong , How effect on my computer 2.why after reinstall my computer is very slow.
remove duplicates from my collection(table type).Or an alternate solution for my issue is get clean data into my collection which i am not able to get to either.
Object creation
create table testingtype_table(ordernumber number,org_id number , company_name varchar2(10)) insert into testingtype_table values (1124,2424,'cbaaa'); insert into testingtype_table values (1124,2424,'cbaaa');
create or replace type testingtype_obj as object (ordernumber number,org_id number , company_name varchar2(10)); create or replace type testingtype_tab as table of testingtype_obj;
Code Block declare l_testingtype_tab testingtype_tab := testingtype_tab(); begin select distinct testingtype_obj(ordernumber ,org_id ,company_name) bulk collect into l_testingtype_tab from testingtype_table; end;
If only i can get a way to bulk collect only distinct values into the table type that will just do great but when i try the above (with distinct highlighted in red) it throws an error
ORA-22950: cannot ORDER objects without MAP or ORDER method