Home > problem-cause-solution, tips > How to open a database while restoring and recovering without archivelogs

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.

Advertisements
  1. ari
    November 9, 2012 at 2:44 pm

    good, just had a problem “chicken-egg” , asm based database trying create from script and must upgrade on the fly from 11.2.0.2>> 11.2.0.3. Conflict with …..resetlogs upgrade .. options. Using this tip worked ok and now open.

  2. deryaoktay
    December 31, 2012 at 2:29 pm

    After executing “Alter database open resetlogs upgrade” it gave following error:

    Errors in file /u01/app/oracle/diag/rdbms/xrds/XRDS_1/trace/XRDS_1_ora_91718.trc:
    ORA-00704: bootstrap process failure
    ORA-00704: bootstrap process failure
    ORA-00604: error occurred at recursive SQL level 1
    ORA-01555: snapshot too old: rollback segment number 312 with name “_SYSSMU312_2240053179$” too small
    Errors in file /u01/app/oracle/diag/rdbms/xrds/XRDS_1/trace/XRDS_1_ora_91718.trc:

    In order to solve this issue add folowing parameter:
    *. _corrupted_rollback_segment=’_SYSSMU312_2240053179$’

    If you need, you can add more rollback segment names separated by comma, in _corrupted_rollback_segments parameter.

    Thank you Burak Akkuş, for sharing the _corrupted_rollback_segments usage.

  3. August 6, 2013 at 12:41 am

    I’m starting up a internet blog directory and was wanting to know if I can submit your website? I’m trying to increase my directory slowly
    by hand so that it maintains good quality. I will make sure and
    put your blog in the correct category and I’ll additionally use, “How to open a database while restoring and recovering without archivelogs | Derya Oktay’s Oracle Weblog” as your anchor text. Please let me know if this is acceptable with you by contacting me. Many thanks

    • deryaoktay
      August 27, 2013 at 2:01 am

      It is OK, you may use it.

  4. November 29, 2013 at 10:59 am

    Very nice documentation. It is great to find such documents online. It gave people hope at the most difficult times 🙂

  1. November 29, 2013 at 10:35 am

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

Don Charisma

because anything is possible with Charisma

Carol no Mundo!

Aventuras de uma intercambista a trabalho pelo mundo!

Blog do PHP

Oracle DBA for rookies!

nimaidba

Welcome to the world of Oracle with me....

Tech

News and reviews from the world of gadgets, gear, apps and the web

WordPress.com

WordPress.com is the best place for your personal blog or business site.

Gurcan Orhan's ODI and DWH Blog

Some words about Oracle Data Integrator and Data Warehousing.

%d bloggers like this: