This week, my DBA team fulfilled a request to restore 1 month old lost data. A review of the RMAN backups showed that we indeed had a valid level 0 from the requested day, which was 33 days ago. However, upon recovery, the necessary archive logs to bring the database to a consistent point were unavailable. Having a 28 day retention policy and running level 0 backups every 15 days and level 1s each night, we did have the necessary level 0 backup, but the archive logs had been deleted. How to retrieve a copy of a user schema from a database that we cannot open? With the hidden “_allow_resetlogs_corruption” parameter. To make matters more difficult, we were also doing just a partial database restore – we only required 1 schema located in its own tablespace, but this tablespace was encrypted. The partial database restore was simple enough. The team began the restore and recovery on another server. The server where recovery is to be performed should have the same path/location of files as the old server. If not, update the path of the datafiles using set newname command in rman recovery, update the logfiles using alter database rename commands, and update the tempfile by re-creating it.
RMAN> run
{
ALLOCATE CHANNEL C1 DEVICE TYPE DISK;
ALLOCATE CHANNEL C2 DEVICE TYPE DISK;
ALLOCATE CHANNEL C3 DEVICE TYPE DISK;
set until time "to_date('16-03-2013 07:00:00','dd-mm-yyyy hh24:mi:ss')";
restore database skip forever tablespace 'USERS1','USERS2',' USERS3',' USERS4';
recover database skip forever tablespace 'USERS1','USERS2',' USERS3',' USERS4';
RELEASE CHANNEL C1;
RELEASE CHANNEL C3;
RELEASE CHANNEL C3;
} RMAN completed the restore of the SYSTEM, SYSAUX, UNDO, and our USERS tablespace without issue, but sadly, during the recovery, we received the Oracle Error:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
RMAN-03002: failure of recover command at 04/19/2013 17:24:34
RMAN-06053: unable to perform media recovery because of missing log
We know at this point that our data is right there – so close to grab and provide for our customer and become the DBA hero we long to be – but Oracle won’t open the door and let us in. What to do? All we need is to get the data and get out. We are not going to allow users into this half-way house we call an Oracle database. So, we allow the corruption with the hidden parameter “_allow_resetlogs_corruption”. This should not be used lightly – the database will be a mess – but it will allow us to open it and hopefully get the data we need.
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 530288640 bytes
Fixed Size 2131120 bytes
Variable Size 310381392 bytes
Database Buffers 209715200 bytes
Redo Buffers 8060928 bytes
Database mounted.
SQL> ALTER SYSTEM SET "_allow_resetlogs_corruption"= TRUE SCOPE = SPFILE;
SQL> ALTER SYSTEM SET undo_management=MANUAL SCOPE = SPFILE;
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 530288640 bytes
Fixed Size 2131120 bytes
Variable Size 310381392 bytes
Database Buffers 209715200 bytes
Redo Buffers 8060928 bytes
Database mounted.
SQL> alter database open resetlogs;
Database altered.
Hallelujah! As a DBA, these are words we long to see on our screen: “Database opened”. At this point, I am certain my team has super human DBA talent. The data is there for the taking! But wait, we are still unable to grab it. The database continues to crash with ORA-600 errors. The team realizes that the errors are related to Oracle jobs that are failing We set job_queue_processes=0; and restart the instance. The database seems to be stable now, but the expdp needs a job process in order to run. If we modify the job_queue_processes parameter, then the instance will crash. We go back to our roots and try the old export utility “exp”. This works great, except that if you remember, our tablespaces are encrypted. Exp cannot export the data from them. Painstakingly, the team moves each object into a new non-encrypted tablespace. Many ORA-00600 errors are hit along the way. The road is not smooth, but with perseverance, all objects get moved. We then create a new UNDO tablespace:
SQL> CREATE UNDO TABLESPACE undo1 datafile '<ora_data_path>\undo1_1.dbf' size 200m autoextend on maxsize unlimited;
Tablespace created.
SQL> ALTER SYSTEM SET undo_tablespace = undo1 SCOPE=spfile;
System altered.
SQL> alter system set undo_management=auto scope=spfile;
System altered.
SQL> shutdown immediate
SQL> startup
Finally the team is able to generate a successful export dump using exp:
exp file=<path_to_exportlocation>\tran.dmp indexes=n direct=y feedback=100000 statistics=none owner=('<schema_owner>') The team members are now experts at both partial recoveries and getting the front door to an Oracle database open when you fail to have the right keys. The lesson here? Keep all your keys to the front door, but remember that back doors do exist.
.svg){kind=small}
.svg){kind=small}