Opening the Door Without the Keys

Apr 24, 2013 / By Christine Kivi

Tags: , ,

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.

6 Responses to “Opening the Door Without the Keys”

  • John Hallas says:

    A really nice real-world example. Unfortunately I have seen the “ORA-01194: file 1 needs more recovery to be consistent” message too many times for comfort. It is nice to see that you got around it.
    Wheere you not concerened that the data is the schema might not be consistent or were you just happy to get all the data back. John

    • Christine Kivi says:

      Thanks John! Yes – it was a concern, however we were willing to take that chance to get the required data. We did run a test import of the data onto another system to check for any inconsistencies/issues.

  • It is a nice war story Christine, Thanks for sharing.

  • Maris Elsins says:

    Well written and fun to read. Good post!
    I had only one question about jobs that were causing ORA-600 errors. Did you try to disable(break)/remove them to avoid crashes?

    • Christine Kivi says:

      Thanks Maris – yes we did try to disable all the jobs that were causing the ora-600 errors, but received ora-0607 errors during the attempt:
      SQL> begin
      2 DBMS_SCHEDULER.drop_job (job_name => ‘XMLDB_NFS_CLEANUP_JOB’);
      3 DBMS_SCHEDULER.drop_job (job_name => ‘FGR$AUTOPURGE_JOB’);
      4 DBMS_SCHEDULER.drop_job (job_name => ‘BSLN_MAINTAIN_STATS_JOB’);
      5 DBMS_SCHEDULER.drop_job (job_name => ‘DRA_REEVALUATE_OPEN_FAILURES’);
      6 DBMS_SCHEDULER.drop_job (job_name => ‘HM_CREATE_OFFLINE_DICTIONARY’);
      7 DBMS_SCHEDULER.drop_job (job_name => ‘ORA$AUTOTASK_CLEAN’);
      8 DBMS_SCHEDULER.drop_job (job_name => ‘PURGE_LOG’);
      9 DBMS_SCHEDULER.drop_job (job_name => ‘MGMT_STATS_CONFIG_JOB’);
      10 DBMS_SCHEDULER.drop_job (job_name => ‘MGMT_CONFIG_JOB’);
      11 DBMS_SCHEDULER.drop_job (job_name => ‘RLM$SCHDNEGACTION’);
      12 DBMS_SCHEDULER.drop_job (job_name => ‘RLM$EVTCLEANUP’);
      13 DBMS_SCHEDULER.drop_job (job_name => ‘DB_UPTIME_JOB’);
      14 end;
      15 /
      begin
      *
      ERROR at line 1:
      ORA-00607: Internal error occurred while making a change to a data block
      ORA-06512: at “SYS.DBMS_ISCHED”, line 182
      ORA-06512: at “SYS.DBMS_SCHEDULER”, line 615
      ORA-06512: at line 6

  • om amhad says:

    the power is off suddenly and i have errors as ora:08102 and
    frm – 40735: when buttun_pressed trigger raised unhandled exception ora-0607
    how can i correct this errors. thank you for help

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>