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.......
Comments
Post a Comment