Solving the red stack



In the weeks running up to Christmas Red Stack laid down the gauntlet with their 'Christmas challenge' http://rstchristmaschallenge.instapage.com/ The challenge is now long since closed so i thought i'd post my solution to the problem as I'm intrigued to see if there was more than one way to attack this - here is what i did (I've left out all the trial and error stuff along the way and just given the solution.

If you still want to attempt this stop reading now and have a go yourself first!

While the situation is entirely fictional i did actually have to do something not too dissimilar to the challenge in real life.....

So how did i go about it?

First things first - i had to get the file ('thisaingonnabequick.bk') from the website and make sure i had version 11.2.0.3 installed somewhere.

Once i had both of those these were the steps:

1) work out what the file actually is - i did this via trying to catalog the file in an already existing database like this:

[oracle]:RUNNINGDB:[/oracle/sw]# rman target=/

Recovery Manager: Release 11.2.0.3.0 - Production on Wed Dec 9 10:44:53 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: RUNNINGDB(DBID=3562638475)

RMAN> catalog backuppiece '/oracle/sw/thisaingonnabequick.bk';

using target database control file instead of recovery catalog
ORA-19870: error while restoring backup piece /oracle/sw/thisaingonnabequick.bk
ORA-19691: /oracle/sw/thisaingonnabequick.bk is from different database: id=1442987370, name=DB1

So from the output of that i can see its a backup piece belonging to DB1 which has a dbid of 1442987370 - a very useful start

2) Now i have to try and work out what files make up DB1 and the characterset it uses - a quick dredge through the file reveals that

strings thisaingonnabequick.bk |grep .dbf |more

/u01/DB1/users01.dbf
/u01/DB1/undotbs01.dbf
/u01/DB1/sysaux01.dbf
/u01/DB1/system01.dbf
/u01/DB1/temp01.dbf

strings thisaingonnabequick.bk |grep -i characterset
/d52477da_CharacterSetAL32UTF8

3) now i need to build a database shell and attempt some sort of restore so I :

a) add DB1 to oratab

b) create init file - with minimal content below

db_name=DB1
db_recovery_file_dest='/oracle/sw'
db_recovery_file_dest_size=10G

c) startup nomount to create the instance to try and restore to

4) Now i have to use some plsql version of the rman api to restore from the backup file

DECLARE
v_dev varchar2(50);             -- device type allocated for restore
v_done boolean;                 -- has the controlfile been fully extracted yet
v_file varchar2(255);        -- Stores the backuppiece names
v_maxPieces number:=1;          -- Number of backuppieces in backupset
BEGIN

v_file:='/oracle/sw/thisaingonnabequick.bk';

v_dev:=sys.dbms_backup_restore.deviceAllocate(type=>null, ident=>'d1');
-- Begin the restore conversation
sys.dbms_backup_restore.restoreSetDatafile;
dbms_backup_restore.RestoreDatafileTo( 1,'/oracle/sw/system01.dbf');
dbms_backup_restore.RestoreDatafileTo( 2,'/oracle/sw/sysaux01.dbf');
dbms_backup_restore.RestoreDatafileTo( 3,'/oracle/sw/undotbs01.dbf');
dbms_backup_restore.RestoreDatafileTo( 4,'/oracle/sw/users01.dbf');
        sys.dbms_backup_restore.restoreBackupPiece(done=>v_done, handle=>v_file, params=>null);
sys.dbms_backup_restore.deviceDeallocate;
END;
/

5) Now we create the controlfile based on the info we know

SQL> CREATE CONTROLFILE REUSE DATABASE "DB1" RESETLOGS  NOARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 2
  4      MAXDATAFILES 30
  5      MAXINSTANCES 1
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 (
  9      '/oracle/sw/group1.log'
 10    ) SIZE 100M BLOCKSIZE 512,
 11    GROUP 2 (
 12      '/oracle/sw/group2.log'
 13    ) SIZE 100M BLOCKSIZE 512
DATAFILE
 14   15  '/oracle/sw/system01.dbf',
 16  '/oracle/sw/sysaux01.dbf',
 17  '/oracle/sw/undotbs01.dbf',
 18  '/oracle/sw/users01.dbf'
 19  CHARACTER SET AL32UTF8
 20  ;

Control file created.

6) If we just try and open the database now we just get this

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/oracle/sw/system01.dbf'


So we have to set 

_allow_resetlogs_corruption=TRUE

and then restart the instance to pick that up

SQL> alter database open resetlogs;

Database altered.

7) Now the database is up we just need to work out where the crypt key is held.

SQL> select distinct owner from dba_tables;

OWNER
------------------------------
MDSYS
JOSHUA_FALKEN
OUTLN
CTXSYS
OLAPSYS
FLOWS_FILES
OWBSYS
SYSTEM
EXFSYS
APEX_030200
SCOTT

OWNER
------------------------------
DBSNMP
ORDSYS
SYSMAN
APPQOSSYS
XDB
ORDDATA
SYS
WMSYS

19 rows selected.

The one that standsout is JOSHUA_FALKEN

SQL> select table_name from dba_tables where owner='JOSHUA_FALKEN';

TABLE_NAME
------------------------------
WARGAMES

SQL> select * from JOSHUA_FALKEN.WARGAMES;

KEY_CODE
--------------------------------------------------------------------------------
IMSAI 8080 microcomputer


And there you have it....

Now the strangest thing is in writing this up it actually worked way better than when i solved it originally. The first time round (and i'm not sure why) i was getting scn issues when opening and this could only be resolved by performing the create controlfile and open resetlogs in a loop about 100 times which seem to resolve the scn synchronisation issues - the error i was getting is shown below

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [2662], [0], [1010659], [0],
[1010767], [12583040], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [2662], [0], [1010658], [0],
[1010767], [12583040], [], [], [], [], [], []

For every cycle of the process the 3rd argument would get closer to the 5th until they matched and the database happily opened - oh well in both cases it got back up just the 2nd time through was much easier than the first.

A nice test of backup and recovery skills...... i didn't win the money though - better luck next time i guess.......


0 comments:

Post a Comment