How to open a database while restoring and recovering without archivelogs

In other words, how can I open a database, in a case that database is successfully restored, but cannot be recovered due to absence of  archivelogs?

•         The heart of opening the "wounded" database is based on using below init.ora parameters:

_ALLOW_RESETLOGS_CORRUPTION = TRUE    

_ALLOW_READ_ONLY_CORRUPTION = TRUE     

UNDO_MANAGEMENT = MANUAL

Beware of using above parameters that it will be last resort for opening the database.

Further, we asked for _corrupted_rollback_segments parameter to Oracle ACS, thanks Osman Abi:),  he told that we could have been used that parameter in case of corruption on undo segments which may prevent opening database.

•          Restore environment is 11.2.0.3, and source database is 11.2.0.2, therefore we opened the db as if making upgrade by:

alter database open resetlogs upgrade;

 

•          Lesson learnt from the case: 

Monitor archivelog backup health by following:

select sequence#,archived,deleted,backup_count, status from gv$archived_log where sequence#=111430 and inst_id=2;

 

 

DETAILS

While initiating recovery from RMAN, we got below errors:
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 10/09/2012 12:31:11
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of archived log for thread 2 with sequence 111430 and starting SCN of 11345139269565 found to restore
RMAN-06025: no backup of archived log for thread 2 with sequence 111428 and starting SCN of 11344754878547 found to restore
RMAN-06025: no backup of archived log for thread 2 with sequence 111426 and starting SCN of 11344754846270 found to restore
RMAN-06025: no backup of archived log for thread 2 with sequence 111420 and starting SCN of 11344754612059 found to restore
RMAN-06025: no backup of archived log for thread 2 with sequence 111418 and starting SCN of 11344754547555 found to restore
RMAN-06025: no backup of archived log for thread 2 with sequence 111416 and starting SCN of 11344754483883 found to restore
RMAN-06025: no backup of archived log for thread 2 with sequence 111415 and starting SCN of 11344754472813 found to restore
RMAN-06025: no backup of archived log for thread 2 with sequence 111414 and starting SCN of 11344754461578 found to restore
RMAN-06025: no backup of archived log for thread 2 with sequence 111413 and starting SCN of 11344754450683 found to restore
RMAN-06025: no backup of archived log for thread 2 with sequence 111412 and starting SCN of 11344754438403 found to restore
...
 

After investigating the cause of above messages by following:

1-      Whether archive log sequence 111430  exists in controlfile from RMAN connected to target database controlfile by:

oracle@kiev:/zfssa/restore1>rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Tue Oct 9 16:52:27 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: MYDB (DBID=1032014565, not open)
RMAN> list backup of archivelog FROM SEQUENCE 111245 UNTIL SEQUENCE 111247;
using target database control file instead of recovery catalog
specification does not match any backup in the repository
RMAN>


2-      Whether archive log sequence 111430  exists in recovery catalog querying from CATALOG DB metadata by:
RMAN> list backup of archivelog all completed between '2012-09-23 00:00:00' AND '2012-09-24 00:00:00';
List of Backup Sets
===================
BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
199745  11.25M     SBT_TAPE    00:00:10     23.09.2012 00:00:47
BP Key: 205852   Status: AVAILABLE  Compressed: NO  Tag: TAG20120923T000036
Handle: a3nlsh95_1_1   Media: J30643
List of Archived Logs in backup set 199745
Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
---- ------- ---------- ------------------- ---------- ---------
1    83839   11343439796011 22.09.2012 23:30:05 11343439805018 23.09.2012 00:00:03
...
3- Whether the sequence number exists in RMAN log file in /nsr/rman/log.
Not surpsrisingly, with parallel to above checking results,
there is not any entry for relevant archivelog sequence in RMAN log file.

4-  Whether the each archivelog was archived, backed up before deleted; by querying production database:
select sequence#,archived,deleted,backup_count, status
from gv$archived_log where sequence#=111430 and inst_id=2;

SEQUENCE#	ARCHIVED	DELETED	BACKUP_COUNT	STATUS
111430	YES	YES	0	D

As a result, we achieve a conclusion that archivelog file was deleted but not backed up because backup_count is 0.

  

And here comes, incomplete Inconsistent Recovery steps, make recovery without archivelogs:

 Restore controlfile and datafile as usual.

 Change parameter file as below.            

oracle@kiev:/tmp>more /u01/app/oracle/product/11.2.0/dbhome_1/dbs/initMYDB.ora
          *._ALLOW_RESETLOGS_CORRUPTION= TRUE
*._ALLOW_READ_ONLY_CORRUPTION=TRUE
*.cluster_database=false
*.audit_file_dest='/u01/app/oracle/admin/MYDB/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_file_record_keep_time=14
*.control_files='/tmp/ctl_mydb_01.dbf'
*.cursor_sharing='FORCE'
*.db_block_size=32768
*.db_cache_size=0
*.db_create_file_dest='/zfssa/restore1'
*.db_domain=''
*.db_name='MYDB'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=MYDBXDB)'
*.log_archive_dest_1='LOCATION=/zfssa/restore1'
*.memory_target=0
*.open_cursors=300
*.parallel_force_local=FALSE
*.parallel_max_servers=256
*.pga_aggregate_target=1212254720
*.processes=1000
*.remote_login_passwordfile='exclusive'
*.sessions=1105
*.sga_max_size=107374182400
*.sga_target=107374182400
*.shared_pool_size=34359738368
*.undo_tablespace='UNDOTBS1'
*.UNDO_MANAGEMENT=MANUAL

oracle@kiev:/tmp>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Oct 9 19:41:37 2012
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initMYDB.ora';

ORACLE instance started.

Total System Global Area 1,0689E+11 bytes
Fixed Size                2240984 bytes
Variable Size             3,4897E+10 bytes
Database Buffers          7,1941E+10 bytes
Redo Buffers              55267328 bytes

SQL> @bckcf.ctl
ORA-01081: cannot start already-running ORACLE - shut it down first
Control file created.
Database altered.

SQL> alter database open resetlogs upgrade;
Database altered.

Controlfile change content is like below:

oracle@kiev:/tmp>cat bckcf.ctl
-- End of tempfile additions.
--
--     Set #2. RESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- The contents of online logs will be lost and all backups will
-- be invalidated. Use this only if online logs are damaged.
-- WARNING! The current control file needs to be checked against
-- the datafiles to insure it contains the correct files. The
-- commands printed here may be missing log and/or data files.
-- Another report should be made after the database has been
-- successfully opened.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "MYDB" RESETLOGS  ARCHIVELOG
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 32
MAXLOGHISTORY 34824
LOGFILE
GROUP 5 '/zfssa/restore1/redolog5.dbf'  SIZE 500M BLOCKSIZE 512,
GROUP 6 '/zfssa/restore1/redolog6.dbf'  SIZE 500M BLOCKSIZE 512,
GROUP 7 '/zfssa/restore1/redolog7.dbf'  SIZE 500M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/zfssa/restore1/SYSTEM1.dbf',
'/zfssa/restore1/SYSAUX2.dbf',
'/zfssa/restore1/UNDOTBS3.dbf',
'/zfssa/restore1/UNDOTBS9.dbf',
'/zfssa/restore1/UNDOTBS4.dbf',
'/zfssa/restore1/UNDOTBS7.dbf',
'/zfssa/restore1/UNDOTBS8.dbf',
'/zfssa/restore1/UNDOTBS6.dbf',
'/zfssa/restore1/SYSAUX1.dbf',
'/zfssa/restore1/UNDOTBS2.dbf',
'/zfssa/restore1/UNDOTBS1.dbf',
'/zfssa/restore1/TBS_DWH_NODI_B.dbf'
CHARACTER SET WE8ISO8859P9;
-- Create log files for threads other than thread one.
ALTER DATABASE ADD LOGFILE THREAD 2
GROUP 8 '/zfssa/restore1/redolog8.dbf' SIZE 500M BLOCKSIZE 512 REUSE,
GROUP 9 '/zfssa/restore1/redolog9.dbf' SIZE 500M BLOCKSIZE 512 REUSE,
GROUP 10 '/zfssa/restore1/redolog10.dbf' SIZE 500M BLOCKSIZE 512 REUSE;
-- Database can now be opened zeroing the online logs.
--ALTER DATABASE OPEN RESETLOGS;
- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.