Summary
Recently I had to recover my database using an old backup set for which I used the normal steps as under:
1. startup the database at nomount state.
2. Connect to RMAN catalog
3. Run a script which allocates some tape channels, restore the controlfile, mount the database, restore database, recover applying the archives and redologs.
4. At the end I hade to open database with resetlogs;CANCEL
When i tried to open the database with resetlogs i got the error.
After the two changes in the spfile you can open the database with:
How to find all the corrupted objects in your Database
Partially restore database
2. Connect to RMAN catalog
3. Run a script which allocates some tape channels, restore the controlfile, mount the database, restore database, recover applying the archives and redologs.
4. At the end I hade to open database with resetlogs;
STARTUP NOMOUNT;
rman TARGET / CATALOG rman/****@rman
RUN {
ALLOCATE CHANNEL c1 TYPE 'SBT_TAPE';
restore CONTROLFILE;
alter database mount;
restore database;
recover database;
release channel c1;
}
alter database open resetlogs;
exit;
While recovering the database you will be asked for some archives. A typical message like this occurs:ORA-00279: change 9867098396261 generated at 08/21/2011 16:44 needed for
thread 1
ORA-00289: suggestion : /u01/oracle1/**********.arc
ORA-00280: change 9867098396261 for thread 1 is in sequence #****
Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01195: online backup of file 10 needs more recovery to be consistent
ORA-01110: data file 10: �Archivelog location and name�
ORA-01112: media recovery not started
At the archivelogs apply step as you notice i pressed When i tried to open the database with resetlogs i got the error.
SQL> alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-01195: online backup of file 10 needs more recovery to be consistent ORA-01110: data file 10: �/oracle/oradata/MIS/data_01.dbf�There is a hidden parameter _ALLOW_RESETLOGS_CORRUPTION=TRUE which will allow us to open database even though it�s not properly recovered.
ALTER SYSTEM SET "_allow_resetlogs_corruption"= TRUE SCOPE = SPFILE;Tip: Also change the undo_management to “Manual”
After the two changes in the spfile you can open the database with:
sqlplus "/ as sysdba" startup force
How to find all the corrupted objects in your Database
Partially restore database
NOTE: This is One of the Hidden Oracle Parameters. DO NOT use it until and unless instructed by Oracle Support. You are trying to start your DATABASE, you found the aforementioned error i.e. ORA-00354.
ReplyDelete.
Required Action: Perform recovery with a good version of the redo log file or perform cancel-based recovery up to, but not including, the corrupted redo log file.
What will happen, if you don’t have Valid Backup? There is a Workaround via which you can at least recover your Database to some extent:
SQL> select * from v$log;
SQL> alter system set “_allow_resetlogs_corruption”=true scope=spfile;
SQL> shutdown immediate;
SQL> startup mount;
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 897612315 generated at 10/19/2005 16:54:18 needed for thread 1 ORA-00289:
suggestion : /opt/oracle/oradata/conner/archive/1_160.dbf
ORA-00280: change 897612315 for thread 1 is in sequence #160
Specify log: {=suggested | filename | AUTO | CANCEL} cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1:’/opt/oracle/oradata/conner/system01.dbf’
ORA-01112: media recovery not started
SQL> alter database open resetlogs;
SQL> shutdown immediate;
SQL> startup
Fri Jun 10 16:30:25 2005
alter database open resetlogs
Fri Jun 10 16:30:25 2005
RESETLOGS is being done without consistency checks. This may result in a corrupted database.
The database should be recreated.
RESETLOGS after incomplete recovery UNTIL CHANGE 240677200 Resetting resetlogs
activation ID 3171937922 (0xbd0fee82)
NOTE : I would strongly recommend to immediately take Logical Backup
(either Using Data Pump in 10g or Import/Export, the conventional one) and Create New Database