using bad backup by hidden parameter _allow_resetlogs_corruption

Case: how to use bad backup

To learn this, first we need to take a bad backup that we will use later to recover the database. Let’s checking the database.

C:\Users\jayva>echo %ORACLE_SID%

target

C:\Users\jayva>sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jul 6 19:16:14 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Enter user-name: / as sysdba

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from v$backup

  2  ;

     FILE# STATUS                CHANGE# TIME

---------- ------------------ ---------- ---------

         1 NOT ACTIVE                  0

         2 NOT ACTIVE                  0

         3 NOT ACTIVE                  0

         4 NOT ACTIVE                  0

SQL> exit

So we have 4 datafiles and all in open status. We will prefer to use user manage backup and do we will do without taking datafilesin backup mode. Also same time, I will execute a  pl/sql block that will push incremental data in a table. This I have done to keep changing the SCN number with respect of datafiles.

So what we will do is we will keep database up and running with executing the block i said above and copy the datafiles to backup location. Once done with datafiles copy we will also backup the controlfile. 

C:\app\JAYVA\oradata\target>sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jul 6 19:26:32 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Enter user-name: / as sysdba

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter system archive log current;

System altered.

SQL> alter database backup controlfile to 'C:\app\JAYVA\backup\control.ctl';

Database altered.

Once done will the backup, let shutdown the database.

SQL> shu abort

ORACLE instance shut down.

SQL>

Now what we need to is to delete all the datafiles and restore the files from what backup that we have taken previously. Once done with restoring the bakcup, let us try to start the database.

SQL> startup mount

ORACLE instance started.

Total System Global Area  418484224 bytes

Fixed Size                  1375004 bytes

Variable Size             322962660 bytes

Database Buffers           88080384 bytes

Redo Buffers                6066176 bytes

Database mounted.

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

Now we know these files we need recovery, so trying that.

SQL> recover database until cancel using backup controlfile;

ORA-00279: change 6610452 generated at 07/06/2012 19:22:46 needed for thread 1

ORA-00289: suggestion :

C:\APP\JAYVA\FLASH_RECOVERY_AREA\TARGET\TARGET\ARCHIVELOG\2012_07_06\O1_MF_1_108

_7ZFVMWOZ_.ARC

ORA-00280: change 6610452 for thread 1 is in sequence #108

ORA-00278: log file

'C:\APP\JAYVA\FLASH_RECOVERY_AREA\TARGET\TARGET\ARCHIVELOG\2012_07_06\O1_MF_1_10

7_7ZFVFL8O_.ARC' no longer needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 6672502 generated at 07/06/2012 19:26:09 needed for thread 1

ORA-00289: suggestion :

C:\APP\JAYVA\FLASH_RECOVERY_AREA\TARGET\TARGET\ARCHIVELOG\2012_07_06\O1_MF_1_109

_7ZFVNZJ0_.ARC

ORA-00280: change 6672502 for thread 1 is in sequence #109

ORA-00278: log file

'C:\APP\JAYVA\FLASH_RECOVERY_AREA\TARGET\TARGET\ARCHIVELOG\2012_07_06\O1_MF_1_10

8_7ZFVMWOZ_.ARC' no longer needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 6751368 generated at 07/06/2012 19:26:46 needed for thread 1

ORA-00289: suggestion :

C:\APP\JAYVA\FLASH_RECOVERY_AREA\TARGET\TARGET\ARCHIVELOG\2012_07_06\O1_MF_1_110

_7ZFVO6QJ_.ARC

ORA-00280: change 6751368 for thread 1 is in sequence #110

ORA-00278: log file

'C:\APP\JAYVA\FLASH_RECOVERY_AREA\TARGET\TARGET\ARCHIVELOG\2012_07_06\O1_MF_1_10

9_7ZFVNZJ0_.ARC' no longer needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 6768172 generated at 07/06/2012 19:26:53 needed for thread 1

ORA-00289: suggestion :

C:\APP\JAYVA\FLASH_RECOVERY_AREA\TARGET\TARGET\ARCHIVELOG\2012_07_06\O1_MF_1_111

_7ZFVOL8M_.ARC

ORA-00280: change 6768172 for thread 1 is in sequence #111

ORA-00278: log file

'C:\APP\JAYVA\FLASH_RECOVERY_AREA\TARGET\TARGET\ARCHIVELOG\2012_07_06\O1_MF_1_11

0_7ZFVO6QJ_.ARC' no longer needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 6850266 generated at 07/06/2012 19:27:05 needed for thread 1

ORA-00289: suggestion :

C:\APP\JAYVA\FLASH_RECOVERY_AREA\TARGET\TARGET\ARCHIVELOG\2012_07_06\O1_MF_1_112

_7ZFVPC9X_.ARC

ORA-00280: change 6850266 for thread 1 is in sequence #112

ORA-00278: log file

'C:\APP\JAYVA\FLASH_RECOVERY_AREA\TARGET\TARGET\ARCHIVELOG\2012_07_06\O1_MF_1_11

1_7ZFVOL8M_.ARC' no longer needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}

Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 6931017 generated at 07/06/2012 19:27:30 needed for thread 1

ORA-00289: suggestion :

C:\APP\JAYVA\FLASH_RECOVERY_AREA\TARGET\TARGET\ARCHIVELOG\2012_07_06\O1_MF_1_113

_7ZFVPSLT_.ARC

ORA-00280: change 6931017 for thread 1 is in sequence #113

ORA-00278: log file

'C:\APP\JAYVA\FLASH_RECOVERY_AREA\TARGET\TARGET\ARCHIVELOG\2012_07_06\O1_MF_1_11

2_7ZFVPC9X_.ARC' no longer needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 7012625 generated at 07/06/2012 19:27:45 needed for thread 1

ORA-00289: suggestion :

C:\APP\JAYVA\FLASH_RECOVERY_AREA\TARGET\TARGET\ARCHIVELOG\2012_07_06\O1_MF_1_114

_7ZFVQ5WF_.ARC

ORA-00280: change 7012625 for thread 1 is in sequence #114

ORA-00278: log file

'C:\APP\JAYVA\FLASH_RECOVERY_AREA\TARGET\TARGET\ARCHIVELOG\2012_07_06\O1_MF_1_11

3_7ZFVPSLT_.ARC' no longer needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 7094066 generated at 07/06/2012 19:27:57 needed for thread 1

ORA-00289: suggestion :

C:\APP\JAYVA\FLASH_RECOVERY_AREA\TARGET\TARGET\ARCHIVELOG\2012_07_06\O1_MF_1_115

_7ZFVQKP3_.ARC

ORA-00280: change 7094066 for thread 1 is in sequence #115

ORA-00278: log file

'C:\APP\JAYVA\FLASH_RECOVERY_AREA\TARGET\TARGET\ARCHIVELOG\2012_07_06\O1_MF_1_11

4_7ZFVQ5WF_.ARC' no longer needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00283: recovery session canceled due to errors

ORA-00354: corrupt redo log block header

ORA-00353: log corruption near block 24576 change 7115299 time 07/06/2012

19:28:02

ORA-00334: archived log:

'C:\APP\JAYVA\FLASH_RECOVERY_AREA\TARGET\TARGET\ARCHIVELOG\2012_07_06\O1_MF_1_11

5_7ZFVQKP3_.ARC'

ORA-01112: media recovery not started

Once done with the recovery, let try to open the database.

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1: 'C:\APP\JAYVA\ORADATA\TARGET\SYSTEM01.DBF'

SQL> alter database open noresetlogs;

alter database open noresetlogs

*

ERROR at line 1:

ORA-01588: must use RESETLOGS option for database open

SQL> recover database until cancel using backup controlfile;

ORA-00279: change 7111011 generated at 07/06/2012 19:27:57 needed for thread 1

ORA-00289: suggestion :

C:\APP\JAYVA\FLASH_RECOVERY_AREA\TARGET\TARGET\ARCHIVELOG\2012_07_06\O1_MF_1_115

_7ZFVQKP3_.ARC

ORA-00280: change 7111011 for thread 1 is in sequence #115

Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00283: recovery session canceled due to errors

ORA-00354: corrupt redo log block header

ORA-00353: log corruption near block 24576 change 7111704 time 07/06/2012

19:28:02

ORA-00334: archived log:

'C:\APP\JAYVA\FLASH_RECOVERY_AREA\TARGET\TARGET\ARCHIVELOG\2012_07_06\O1_MF_1_11

5_7ZFVQKP3_.ARC'

ORA-01112: media recovery not started

SQL>

We tried to open the database with all the option avalliable but none works. Also checking the alert log files, we is what we get the error.

In alert log +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Media Recovery Start

 started logmerger process

Fri Jul 06 21:42:19 2012

WARNING! Recovering data file 1 from a fuzzy file. If not the current file

it might be an online backup taken without entering the begin backup command.

WARNING! Recovering data file 2 from a fuzzy file. If not the current file

it might be an online backup taken without entering the begin backup command.

WARNING! Recovering data file 3 from a fuzzy file. If not the current file

it might be an online backup taken without entering the begin backup command.

WARNING! Recovering data file 4 from a fuzzy file. If not the current file

it might be an online backup taken without entering the begin backup command.

Parallel Media Recovery started with 2 slaves

ORA-279 signalled during: ALTER DATABASE RECOVER  database until cancel using backup controlfile  ...

ALTER DATABASE RECOVER    CONTINUE DEFAULT 

Media Recovery Log C:\APP\JAYVA\FLASH_RECOVERY_AREA\TARGET\TARGET\ARCHIVELOG\2012_07_06\O1_MF_1_115_7ZFVQKP3_.ARC

Errors in file c:\app\jayva\diag\rdbms\target\target\trace\target_pr00_17832.trc  (incident=62563):

ORA-00353: log corruption near block 24576 change 7111704 time 07/06/2012 19:28:02

ORA-00334: archived log: 'C:\APP\JAYVA\FLASH_RECOVERY_AREA\TARGET\TARGET\ARCHIVELOG\2012_07_06\O1_MF_1_115_7ZFVQKP3_.ARC'

Incident details in: c:\app\jayva\diag\rdbms\target\target\incident\incdir_62563\target_pr00_17832_i62563.trc

Recovery interrupted!

Fri Jul 06 21:42:24 2012

Trace dumping is performing id=[cdmp_20120706214224]

Recovered data files to a consistent state at change 7111011

Media Recovery failed with error 354

Errors in file c:\app\jayva\diag\rdbms\target\target\trace\target_pr00_17832.trc:

ORA-00283: recovery session canceled due to errors

ORA-00354: corrupt redo log block header

ORA-00353: log corruption near block 24576 change 7111704 time 07/06/2012 19:28:02

ORA-00334: archived log: 'C:\APP\JAYVA\FLASH_RECOVERY_AREA\TARGET\TARGET\ARCHIVELOG\2012_07_06\O1_MF_1_115_7ZFVQKP3_.ARC'

Fri Jul 06 21:42:25 2012

Sweep [inc][62563]: completed

ORA-283 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT  ...

ALTER DATABASE RECOVER CANCEL

ORA-1112 signalled during: ALTER DATABASE RECOVER CANCEL ...

Errors in file c:\app\jayva\diag\rdbms\target\target\trace\target_m000_14772.trc  (incident=62603):

ORA-00353: log corruption near block 24576 change 7115299 time 07/06/2012 19:28:02

ORA-00334: archived log: 'C:\APP\JAYVA\FLASH_RECOVERY_AREA\TARGET\TARGET\ARCHIVELOG\2012_07_06\O1_MF_1_115_7ZFVQKP3_.ARC'

Incident details in: c:\app\jayva\diag\rdbms\target\target\incident\incdir_62603\target_m000_14772_i62603.trc

Trace dumping is performing id=[cdmp_20120706214227]

Fri Jul 06 21:42:49 2012

Sweep [inc][62603]: completed

Sweep [inc2][62603]: completed

Sweep [inc2][62563]: completed

 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 

So alert log confirms we are using the bad backup. So now we will use hidden parameter _allow_resetlogs_corrution=yes in pfile and try to open the database with that.

C:\app\JAYVA\oradata\target>type pfile.ora

target.__db_cache_size=92274688

target.__java_pool_size=4194304

target.__large_pool_size=4194304

target.__oracle_base='C:\app\JAYVA'#ORACLE_BASE set from environment

target.__pga_aggregate_target=146800640

target.__sga_target=272629760

target.__shared_io_pool_size=0

target.__shared_pool_size=155189248

target.__streams_pool_size=8388608

*.audit_file_dest='C:\app\JAYVA\admin\target\adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='C:\app\JAYVA\oradata\target\control01.ctl','C:\app\JAYVA\oradat

a\target\control02.ctl'

*.db_block_size=8192

*.db_create_file_dest='C:\APP\JAYVA\ORADATA\TARGET'

*.db_domain=''

*.db_name='target'

*.db_recovery_file_dest_size=21474836480

*.db_recovery_file_dest='C:\app\JAYVA\flash_recovery_area\target'

*.diagnostic_dest='C:\app\JAYVA'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=targetXDB)'

*.local_listener='LISTENER_TARGET'

*.memory_target=419430400

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.result_cache_max_size=10485760

*.sga_target=0

*.shared_pool_size=104857600

*.undo_tablespace='UNDOTBS1'

*._allow_resetlogs_corruption=yes

C:\app\JAYVA\oradata\target>

SQL> startup mount pfile=C:\app\JAYVA\oradata\target\pfile.ora

ORACLE instance started.

Total System Global Area  418484224 bytes

Fixed Size                  1375004 bytes

Variable Size             318768356 bytes

Database Buffers           92274688 bytes

Redo Buffers                6066176 bytes

Database mounted.

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE

--------------------

READ WRITE

So now we have open the database, lets checking the data.

SQL> conn scott/tiger

Connected.

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID

------------------------------ ------- ----------

BONUS                          TABLE

DEPT                           TABLE

EMP                            TABLE

MAGIC1                         TABLE

SALGRADE                       TABLE

TESTTABLE1                     TABLE

6 rows selected.

SQL> select count(1) from TESTTABLE1;

  COUNT(1)

----------

    450927

SQL>

We have recovered the data now.

It resolved our issue and database was up and running without any issue.

_ALLOW_RESETLOGS_CORRUPTION=TRUE allows database to open without consistency checks. This may result in a corrupted database. The database should be recreated. As per Oracle Metalink, there is no 100% guarantee that setting _ALLOW_RESETLOGS_CORRUPTION=TRUE will open the database. However, once the database is opened, then we must immediately rebuild the database. Database rebuild means doing the following, namely: (1) perform a full-database export, (2) create a brand new and separate database, and finally (3) import the recent export dump. This option can be tedious and time consuming, but once we successfully open the new database, then we expect minimal or perhaps no data loss at all.Using this hidden should only used when we have confirm statement form Oracle Suppport.