Please see my other blog for Oracle EBusiness Suite Posts - EBMentors

Search This Blog

Note: All the posts are based on practical approach avoiding lengthy theory. All have been tested on some development servers. Please don’t test any post on production servers until you are sure.

Wednesday, November 06, 2013

Recovering/Opening database for which archive log is missing

Some times you don't have the missing archivelogs and database cannot be opened due to it.
After incomplete recover we tried to open database with resetlogs but it failed, one hidden parameter (_ALLOW_RESETLOGS_CORRUPTION=TRUE) can be used to open database even though it’s not properly recovered.
RMAN> alter database open resetlogs;

using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 11/04/2013 13:13:54
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: 'D:\APP\INAM\ORADATA\HOMEDB\SYSTEM.256.770482167'

RMAN> exit

D:\app\Inam\product\11.2.0.3\dbhome_1\BIN>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon Nov 4 13:14:11 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Oracle Label Security, OLAP,
Data Mining and Real Application Testing options

SQL> recover database until cancel using backup controlfile;
ORA-00279: change 2462530013 generated at 11/03/2013 22:28:53 needed for thread
1
ORA-00289: suggestion :
D:\APP\INAM\FAST_RECOVERY_AREA\HOMEDB\ARCHIVELOG\2013_11_04\O1_MF_1_510_%U_.ARC
ORA-00280: change 2462530013 for thread 1 is in sequence #510

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

cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'D:\APP\INAM\ORADATA\HOMEDB\SYSTEM.256.770482167'

ORA-01112: media recovery not started


Shutdown database.
SQL> shutdown immediate;

Open PFILE and add following parameter (Bold )
_ALLOW_RESETLOGS_CORRUPTION=TRUE

memory_target=3G
audit_file_dest='D:\app\inam\admin\homedb\adump'
audit_trail='db'
compatible='11.2.0.0.0'
control_files='D:\app\Inam\oradata\homedb\CONTROL1.CTL'
db_block_size=8192
db_domain=''
db_name='homedb'
db_recovery_file_dest=D:\app\inam\fast_recovery_area
db_recovery_file_dest_size=50G
diagnostic_dest='D:\app\inam'
remote_login_passwordfile='exclusive'
_ALLOW_RESETLOGS_CORRUPTION=TRUE



Mount database
SQL> startup mount pfile='D:\INITHOMEDB.ORA'


Recover database until cancel using backup controlfile

SQL> recover database until cancel using backup controlfile;
ORA-00279: change 2462530013 generated at 11/03/2013 22:28:53 needed for thread
1
ORA-00289: suggestion :
D:\APP\INAM\FAST_RECOVERY_AREA\HOMEDB\ARCHIVELOG\2013_11_04\O1_MF_1_510_%U_.ARC
ORA-00280: change 2462530013 for thread 1 is in sequence #510

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

cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'D:\APP\INAM\ORADATA\HOMEDB\SYSTEM.256.770482167'

ORA-01112: media recovery not started



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], [2462530023], [0],
[2462532652], [75497608], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [2662], [0], [2462530022], [0],
[2462532652], [75497608], [], [], [], [], [], []
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2662], [0], [2462530020], [0],
[2462532652], [75497608], [], [], [], [], [], []
Process ID: 8020
Session ID: 918 Serial number: 19


SQL> alter database open;
ERROR:
ORA-03114: not connected to ORACLE

Exit and reconnect through sqlplus

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Oracle Label Security, OLAP,
Data Mining and Real Application Testing options


D:\app\Inam\product\11.2.0.3\dbhome_1\BIN>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Nov 4 13:25:14 2013

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

Connected to an idle instance.


SQL> startup;
ORACLE instance started.

Total System Global Area 3206836224 bytes
Fixed Size 2259480 bytes
Variable Size 1744831976 bytes
Database Buffers 1442840576 bytes
Redo Buffers 16904192 bytes
Database mounted.
Database opened.

No comments: