OPEN RESETLOGS without really doing a Recovery


The OPEN RESETLOGS command does a check to see if it has been called after an Incomplete Recovery and does not execute if the database is in a NORMAL shutdown state.

Here is how the “Incomplete Recovery” could be simulated :
(I learnt this trick from HJR (dizwell) on forums.oracle.com !)

First I confirm that the database is a NORMAL shutdown :

ora10204>sqlplus
/ as sysdba
SQL*Plus: Release 10.2.0.4.0 – Production on Sat Nov 8 23:23:43 2008

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Enter user-name:
startup
Connected to an idle instance.

SQL> ORACLE instance started.

Total System Global Area 838860800 bytes
Fixed Size 2087672 bytes
Variable Size 213910792 bytes
Database Buffers 616562688 bytes
Redo Buffers 6299648 bytes
Database mounted.
Database opened.
SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit

Now I simulate a Recovery and then do an OPEN RESETLOGS :

ora10204>sqlplus

SQL*Plus: Release 10.2.0.4.0 – Production on Sat Nov 8 23:25:43 2008

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Enter user-name: / as sysdba
Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 838860800 bytes
Fixed Size 2087672 bytes
Variable Size 213910792 bytes
Database Buffers 616562688 bytes
Redo Buffers 6299648 bytes
Database mounted.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery

SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery

SQL> recover database until cancel;
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

SQL>

The first “RECOVER DATABASE” command failed with an ORA-264 error. This is explained as :

00264, 00000, “no recovery required”
// *Cause: An attempt was made to perform media recovery on files that do not // need any type of recovery.
// *Action: Do not attempt to perform media recovery on the selected
// files. Check to see that the filenames were entered properly.
// If not, retry the command with the proper filenames.

The “UNTIL CANCEL” in the second RECOVER DATABASE was an instruction to Oracle that I was attempting an Incomplete Recovery.
Therefore, the OPEN RESETLOGS after that allowed me to proceed !
These are the messages in the alert.log relating to the second (simulated Incomplete Recovery) :

Sat Nov 8 23:26:37 2008
ALTER DATABASE RECOVER database until cancel
Media Recovery Start
Sat Nov 8 23:26:37 2008
SUCCESS: diskgroup DG_1_2G was dismounted
SUCCESS: diskgroup DG_1_2G was mounted
Sat Nov 8 23:26:38 2008
parallel recovery started with 2 processes
Media Recovery Not Required
Sat Nov 8 23:26:38 2008
SUCCESS: diskgroup DG_1_2G was dismounted
SUCCESS: diskgroup DG_1_2G was mounted
SUCCESS: diskgroup DG_1_2G was dismounted
Sat Nov 8 23:26:38 2008
Completed: ALTER DATABASE RECOVER database until cancel
Sat Nov 8 23:26:43 2008
alter database open resetlogs
Sat Nov 8 23:26:46 2008
SUCCESS: diskgroup DG_1_2G was mounted
Sat Nov 8 23:26:48 2008
RESETLOGS after complete recovery through change 99552446
Resetting resetlogs activation ID 4148073936 (0xf73e95d0)
Sat Nov 8 23:26:53 2008
Setting recovery target incarnation to 11
Sat Nov 8 23:26:53 2008
SUCCESS: diskgroup DG_1_2G was dismounted
SUCCESS: diskgroup DG_1_2G was mounted
Sat Nov 8 23:26:54 2008
Assigning activation ID 4150071358 (0xf75d103e)
LGWR: STARTING ARCH PROCESSES
ARC0 started with pid=23, OS id=4290
Sat Nov 8 23:26:54 2008
ARC0: Archival started
ARC1: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC1 started with pid=24, OS id=4292
Sat Nov 8 23:26:54 2008
Thread 1 opened at log sequence 1
Current log# 1 seq# 1 mem# 0: /oracle_fs/Databases/ORT24FS/redo01.dbf
Successful open of redo thread 1

No Redo actually needed to be applied yet, Oracle did do an OPEN RESETLOGS and change the activation ID and reset the Log Sequence Number to 1.

Here we can see that this was the 11th incarnation of this database :

SQL> select dbid, name, created, resetlogs_change#, resetlogs_time, activation#, recovery_target_incarnation#, last_open_incarnation#, current_scn
2 from v$database;

DBID NAME CREATED RESETLOGS_CHANGE# RESETLOGS_TIME
———- ——— —————— —————– ——————
ACTIVATION# RECOVERY_TARGET_INCARNATION# LAST_OPEN_INCARNATION# CURRENT_SCN
———– —————————- ———————- ———–
4137213278 ORT24FS 14-JUN-08 23:28:30 99552447 08-NOV-08 23:26:46
4150071358 11 11 99553429

SQL>
SQL> select incarnation#, resetlogs_change#, resetlogs_time, status, resetlogs_id from v$database_incarnation where incarnation#=11;

INCARNATION# RESETLOGS_CHANGE# RESETLOGS_TIME STATUS RESETLOGS_ID
———— —————– —————— ——- ————
11 99552447 08-NOV-08 23:26:46 CURRENT 670289206

SQL>

Therefore, it is possible to simulate an Incomplete Recovery to fool the ALTER DATABASE OPEN RESETLOGS into thinking that Recovery is Incomplete — even though we know we have not lost any transactions as the last shutdown was a NORMAL shutdown.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s