Monday 5 August 2019

Complete recovery of the entire database in ARCHIVELOG Mode RAC 12.1.0.2.0

Scenario Preview: 

The database is running in ARCHIVELOG mode

 • All or most datafiles are lost

 • Recovery Catalog is not being use

Solution :

  • Start  database in mount stage using SRVCTL utility
  • Restore database preview (check datafile information)
  • Restore database validate  (check backup file is valid) 
  • restore database 
  • recover database 
  • open database using SRVCTL
  • Check Incarnation informaton


  • Stop daatabase and remove datafile 

[root@srv1 oracle]# srvctl stop database -d rac

ASMCMD> ls
IDATA.269.1014653909
ILOG.271.1015518129
INDX.270.1014656575
SOETBS.273.1015005687
SYSAUX.257.1011959959
SYSTEM.258.1011960053
UNDOTBS1.260.1011960171
UNDOTBS2.265.1011960859
USERS.259.1011960169
ASMCMD> rm -rf *
ASMCMD> ls
ASMCMD-8002: entry 'DATAFILE' does not exist in directory '+DATA/RAc/'
ASMCMD>
  • start database using SRVCTL command

[root@srv1 oracle]# srvctl start database -d rac
PRCR-1079 : Failed to start resource ora.rac.db
CRS-5017: The resource action "ora.rac.db start" encountered the following error:
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '+DATA/RAC/DATAFILE/system.258.1011960053'
. For details refer to "(:CLSN00107:)" in "/u01/app/grid/diag/crs/srv1/crs/trace/crsd_oraagent_oracle.trc".

CRS-2674: Start of 'ora.rac.db' on 'srv1' failed
CRS-2632: There are no more servers to try to place resource 'ora.rac.db' on that would satisfy its placement policy
CRS-5017: The resource action "ora.rac.db start" encountered the following error:
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '+DATA/RAC/DATAFILE/system.258.1011960053'
. For details refer to "(:CLSN00107:)" in "/u01/app/grid/diag/crs/srv2/crs/trace/crsd_oraagent_oracle.trc".

CRS-2674: Start of 'ora.rac.db' on 'srv2' failed
[root@srv1 oracle]#




  • Check error in alert log 
########################## Alert Log ###########################################


Completed: ALTER DATABASE MOUNT /* db agent *//* {1:42021:4466} */
ALTER DATABASE OPEN /* db agent *//* {1:42021:4466} */
Mon Aug 05 17:09:58 2019
This instance was first to open
Mon Aug 05 17:09:58 2019
Errors in file /u01/app/oracle/diag/rdbms/rac/rac2/trace/rac2_dbw0_18265.trc:
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '+DATA/RAC/DATAFILE/system.258.1011960053'
ORA-17503: ksfdopn:2 Failed to open file +DATA/RAC/DATAFILE/system.258.1011960053
ORA-15012: ASM file '+DATA/RAC/DATAFILE/system.258.1011960053' does not exist
Mon Aug 05 17:09:58 2019
Errors in file /u01/app/oracle/diag/rdbms/rac/rac2/trace/rac2_dbw0_18265.trc:
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '+DATA/RAC/DATAFILE/idata.269.1014653909'
ORA-17503: ksfdopn:2 Failed to open file +DATA/RAC/DATAFILE/idata.269.1014653909
ORA-15012: ASM file '+DATA/RAC/DATAFILE/idata.269.1014653909' does not exist
Mon Aug 05 17:09:58 2019
Errors in file /u01/app/oracle/diag/rdbms/rac/rac2/trace/rac2_dbw0_18265.trc:
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '+DATA/RAC/DATAFILE/sysaux.257.1011959959'
ORA-17503: ksfdopn:2 Failed to open file +DATA/RAC/DATAFILE/sysaux.257.1011959959
ORA-15012: ASM file '+DATA/RAC/DATAFILE/sysaux.257.1011959959' does not exist
Mon Aug 05 17:09:58 2019
Errors in file /u01/app/oracle/diag/rdbms/rac/rac2/trace/rac2_dbw0_18265.trc:
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '+DATA/RAC/DATAFILE/undotbs1.260.1011960171'
ORA-17503: ksfdopn:2 Failed to open file +DATA/RAC/DATAFILE/undotbs1.260.1011960171
ORA-15012: ASM file '+DATA/RAC/DATAFILE/undotbs1.260.1011960171' does not exist
Mon Aug 05 17:09:58 2019
Errors in file /u01/app/oracle/diag/rdbms/rac/rac2/trace/rac2_dbw0_18265.trc:
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '+DATA/RAC/DATAFILE/undotbs2.265.1011960859'
ORA-17503: ksfdopn:2 Failed to open file +DATA/RAC/DATAFILE/undotbs2.265.1011960859
ORA-15012: ASM file '+DATA/RAC/DATAFILE/undotbs2.265.1011960859' does not exist
Mon Aug 05 17:09:58 2019
Errors in file /u01/app/oracle/diag/rdbms/rac/rac2/trace/rac2_dbw0_18265.trc:
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '+DATA/RAC/DATAFILE/users.259.1011960169'
ORA-17503: ksfdopn:2 Failed to open file +DATA/RAC/DATAFILE/users.259.1011960169
ORA-15012: ASM file '+DATA/RAC/DATAFILE/users.259.1011960169' does not exist
Mon Aug 05 17:09:58 2019
Errors in file /u01/app/oracle/diag/rdbms/rac/rac2/trace/rac2_dbw0_18265.trc:
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: '+DATA/RAC/DATAFILE/indx.270.1014656575'
ORA-17503: ksfdopn:2 Failed to open file +DATA/RAC/DATAFILE/indx.270.1014656575
ORA-15012: ASM file '+DATA/RAC/DATAFILE/indx.270.1014656575' does not exist
Mon Aug 05 17:09:58 2019
Errors in file /u01/app/oracle/diag/rdbms/rac/rac2/trace/rac2_dbw0_18265.trc:
ORA-01157: cannot identify/lock data file 8 - see DBWR trace file
ORA-01110: data file 8: '+DATA/RAC/DATAFILE/ilog.271.1015518129'
ORA-17503: ksfdopn:2 Failed to open file +DATA/RAC/DATAFILE/ilog.271.1015518129
ORA-15012: ASM file '+DATA/RAC/DATAFILE/ilog.271.1015518129' does not exist
Mon Aug 05 17:09:58 2019
Errors in file /u01/app/oracle/diag/rdbms/rac/rac2/trace/rac2_dbw0_18265.trc:
ORA-01157: cannot identify/lock data file 9 - see DBWR trace file
ORA-01110: data file 9: '+DATA/RAC/DATAFILE/soetbs.273.1015005687'
ORA-17503: ksfdopn:2 Failed to open file +DATA/RAC/DATAFILE/soetbs.273.1015005687
ORA-15012: ASM file '+DATA/RAC/DATAFILE/soetbs.273.1015005687' does not exist
Block change tracking file is current.
Ping without log force is disabled
.
Buffer Cache Full DB Caching mode changing from FULL CACHING DISABLED to FULL CACHING ENABLED
Abort recovery for domain 0
Mon Aug 05 17:09:58 2019
Errors in file /u01/app/oracle/diag/rdbms/rac/rac2/trace/rac2_ora_18318.trc:
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '+DATA/RAC/DATAFILE/system.258.1011960053'
ORA-1157 signalled during: ALTER DATABASE OPEN /* db agent *//* {1:42021:4466} */...
Mon Aug 05 17:09:59 2019
License high water mark = 1
Mon Aug 05 17:09:59 2019
USER (ospid: 18470): terminating the instance
Mon Aug 05 17:10:00 2019
Instance terminated by USER, pid = 18470

  • Start database in mount mode 
[root@srv1 oracle]# srvctl start database -d rac -startoption mount
  • Check database status 
[root@srv1 oracle]# srvctl status database -d rac

Instance rac1 is running on node srv1
Instance rac2 is running on node srv2

[root@srv1 oracle]# crsctl status resource -t
ora.rac.db
      1        ONLINE  INTERMEDIATE srv1                     Mounted (Closed),STA
                                                             BLE
      2        ONLINE  INTERMEDIATE srv2                     Mounted (Closed),STA



[oracle@srv1 trace]$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Mon Aug 5 17:16:41 2019

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database: RAC (DBID=2599942250, not open)


  • Connect database and check restore preview
RMAN> RESTORE  DATABASE preview;

Starting restore at 05-AUG-19
using channel ORA_DISK_1


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
169     Full    5.05G      DISK        00:11:35     05-AUG-19
        BP Key: 171   Status: AVAILABLE  Compressed: NO  Tag: FULL_DB_MOUNT
        Piece Name: /u01/Backup/ora_df1015515357_s220_s1
  List of Datafiles in backup set 169
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 16289785   05-AUG-19 +DATA/RAC/DATAFILE/system.258.1011960053
  2       Full 16289785   05-AUG-19 +DATA/RAC/DATAFILE/idata.269.1014653909
  3       Full 16289785   05-AUG-19 +DATA/RAC/DATAFILE/sysaux.257.1011959959
  4       Full 16289785   05-AUG-19 +DATA/RAC/DATAFILE/undotbs1.260.1011960171
  5       Full 16289785   05-AUG-19 +DATA/RAC/DATAFILE/undotbs2.265.1011960859
  6       Full 16289785   05-AUG-19 +DATA/RAC/DATAFILE/users.259.1011960169
  7       Full 16289785   05-AUG-19 +DATA/RAC/DATAFILE/indx.270.1014656575
  8       Full 16289785   05-AUG-19 +DATA/RAC/DATAFILE/ilog.271.1015518129
  9       Full 16289785   05-AUG-19 +DATA/RAC/DATAFILE/soetbs.273.1015005687

List of Archived Log Copies for database with db_unique_name RAC
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
842     1    1       A 05-AUG-19
        Name: +FRA/RAC/ARCHIVELOG/2019_08_05/thread_1_seq_1.477.1015520811

841     2    1       A 05-AUG-19
        Name: +FRA/RAC/ARCHIVELOG/2019_08_05/thread_2_seq_1.479.1015520387

recovery will be done up to SCN 16289786
Media recovery start SCN is 16289786
Recovery must be done beyond SCN 16289786 to clear datafile fuzziness
Finished restore at 05-AUG-19

RMAN>


  • Check with Restore database validate the backup piece  
RMAN> RESTORE DATABASE VALIDATE;

Starting restore at 05-AUG-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=42 instance=rac1 device type=DISK

channel ORA_DISK_1: starting validation of datafile backup set
channel ORA_DISK_1: reading from backup piece /u01/Backup/ora_df1015515357_s220_s1
channel ORA_DISK_1: piece handle=/u01/Backup/ora_df1015515357_s220_s1 tag=FULL_DB_MOUNT
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:02:46
Finished restore at 05-AUG-19


  • Restore database 
RMAN> restore database;

Starting restore at 05-AUG-19
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to +DATA/RAC/DATAFILE/system.258.1011960053
channel ORA_DISK_1: restoring datafile 00002 to +DATA/RAC/DATAFILE/idata.269.1014653909
channel ORA_DISK_1: restoring datafile 00003 to +DATA/RAC/DATAFILE/sysaux.257.1011959959
channel ORA_DISK_1: restoring datafile 00004 to +DATA/RAC/DATAFILE/undotbs1.260.1011960171
channel ORA_DISK_1: restoring datafile 00005 to +DATA/RAC/DATAFILE/undotbs2.265.1011960859
channel ORA_DISK_1: restoring datafile 00006 to +DATA/RAC/DATAFILE/users.259.1011960169
channel ORA_DISK_1: restoring datafile 00007 to +DATA/RAC/DATAFILE/indx.270.1014656575
channel ORA_DISK_1: restoring datafile 00008 to +DATA/RAC/DATAFILE/ilog.271.1015518129
channel ORA_DISK_1: restoring datafile 00009 to +DATA/RAC/DATAFILE/soetbs.273.1015005687
channel ORA_DISK_1: reading from backup piece /u01/Backup/ora_df1015515357_s220_s1

channel ORA_DISK_1: piece handle=/u01/Backup/ora_df1015515357_s220_s1 tag=FULL_DB_MOUNT
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:09:35
Finished restore at 05-AUG-19


  • Recover database 
RMAN> recover database;

Starting recover at 05-AUG-19
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:11

Finished recover at 05-AUG-19


  • Open database 

RMAN> ALTER DATABASE OPEN;

Statement processed


  • Check incarnation point

RMAN>

SQL> SELECT INCARNATION#, RESETLOGS_CHANGE#, PRIOR_RESETLOGS_CHANGE#, STATUS FROM V$DATABASE_INCARNATION ORDER BY 1;

INCARNATION# RESETLOGS_CHANGE# PRIOR_RESETLOGS_CHANGE# STATUS
------------ ----------------- ----------------------- -------
           1                 1                       0 PARENT
           2           1594143                       1 PARENT
           3          16289786                 1594143 CURRENT

SQL>

Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)