12c Steps to restore database from full backup in case of DR

This is a an restore exercise for a CDB database with CDB name.

1. Create INITcdb.ora file with content:

db_name='cdb'

2. Startup database in nomount (it will read the init file and will start the instace)

SQL> startup nomount
ORACLE instance started.

Total System Global Area  234881024 bytes
Fixed Size                  3044152 bytes
Variable Size             176164040 bytes
Database Buffers           50331648 bytes
Redo Buffers                5341184 bytes

3.  Restore SPFILE

In situations requiring the recovery of your SPFILE or control file from autobackup, such as disaster recovery when you have lost all database files, you will need to use your DBID, or you will hit following errors:

RMAN> restore spfile from autobackup;

Starting restore at 01-JUL-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=174 device type=DISK

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 07/01/2015 11:41:26
RMAN-06495: must explicitly specify DBID with SET DBID command

If you do not have a record of the DBID of your database, there are two places you can find it without opening your database.

• The DBID is used in forming the filename for the control file autobackup. Locate that file and check DBID (IIIIIIIIII) from filename c-IIIIIIIIII-YYYYMMDD-QQ.
  
• If you have any text files that preserve the output from an RMAN session, the DBID is displayed by the RMAN client when it starts up and connects to your database.

 RMAN> set dbid=1992057407;

executing command: SET DBID

RMAN> restore spfile from autobackup;

Starting restore at 01-JUL-15
using channel ORA_DISK_1

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150701
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150630
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150629
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150628
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150627
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150626
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150625
channel ORA_DISK_1: no AUTOBACKUP in 7 days found
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 07/01/2015 11:48:53
RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece

RMAN> restore spfile from 'E:\app\oracle12c\fast_recovery_area\cdb\AUTOBACKUP\20 15_07_01\O1_MF_S_883904217_BS709BCN_.BKP';

RMAN> shutdown immediate

Oracle instance shut down

RMAN> startup nomount

connected to target database (not started)

Oracle instance started

Total System Global Area    2147483648 bytes

Fixed Size                     3047720 bytes
Variable Size               1493176024 bytes
Database Buffers             637534208 bytes
Redo Buffers                  13725696 bytes


4. Restore CONTROLFILES



RMAN> restore controlfile from 'E:\app\oracle12c\fast_recovery_area\cdb\AUTOBACK UP\2015_07_01\O1_MF_S_883904217_BS709BCN_.BKP';

Starting restore at 01-JUL-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=13 device type=DISK

channel ORA_DISK_1: restoring control file
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 07/01/2015 11:55:26
ORA-19870: error while restoring backup piece E:\APP\ORACLE12C\FAST_RECOVERY_ARE
A\CDB\AUTOBACKUP\2015_07_01\O1_MF_S_883904217_BS709BCN_.BKP
ORA-19504: failed to create file "E:\APP\ORACLE12C\ORADATA\CDB\CONTROL01.CTL"
ORA-27040: file create error, unable to create file
OSD-04002: unable to open file
O/S-Error: (OS 3) The system cannot find the path specified.

NOTE: Create necessary folders before restoring controlfiles


RMAN> restore controlfile from 'E:\app\oracle12c\fast_recovery_area\cdb\AUTOBACK UP\2015_07_01\O1_MF_S_883904217_BS709BCN_.BKP';

Starting restore at 01-JUL-15
using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=E:\APP\ORACLE12C\ORADATA\CDB\CONTROL01.CTL
output file name=E:\APP\ORACLE12C\FAST_RECOVERY_AREA\CDB\CONTROL02.CTL
Finished restore at 01-JUL-15

RMAN> alter database mount;

Statement processed

5. Check backups catalogued in controlfiles

RMAN> list backup summary;

6. Restore database (folders of datafiles must be created prior restoring)

RMAN> restore database;

7. Recover database

RMAN> recover database;

8. Open database with resetlogs

RMAN> alter database open resetlogs;

12c ORA-63999: data file suffered media failure

After a datafile missing in a PDB, the instance is terminated.
alert.log file:


Wed Jul 01 11:07:43 2015
Errors in file E:\APP\ORACLE12C\diag\rdbms\cdb\cdb\trace\cdb_ckpt_7972.trc:
ORA-63999: data file suffered media failure
ORA-01110: data file 10: 'E:\APP\ORACLE12C\ORADATA\CDB\PDB1\EXAMPLE01.DBF'
ORA-01115: IO error reading block from file 10 (block # 1)
ORA-27072: File I/O error
OSD-04006: ReadFile() failure, unable to read from file
O/S-Error: (OS 6) The handle is invalid.
Wed Jul 01 11:07:43 2015
Errors in file E:\APP\ORACLE12C\diag\rdbms\cdb\cdb\trace\cdb_ckpt_7972.trc:
ORA-63999: data file suffered media failure
ORA-01110: data file 10: 'E:\APP\ORACLE12C\ORADATA\CDB\PDB1\EXAMPLE01.DBF'
ORA-01115: IO error reading block from file 10 (block # 1)
ORA-27072: File I/O error
OSD-04006: ReadFile() failure, unable to read from file
O/S-Error: (OS 6) The handle is invalid.
Wed Jul 01 11:07:43 2015
System state dump requested by (instance=1, osid=7972 (CKPT)), summary=[abnormal instance termination].
System State dumped to trace file E:\APP\ORACLE12C\diag\rdbms\cdb\cdb\trace\cdb_diag_9924_20150701110743.trc
Wed Jul 01 11:07:43 2015
USER (ospid: 7972): terminating the instance due to error 63999

C:\Users\oracle12c>rman target /

RMAN> restore tablespace pdb1:example;

RMAN> recover tablespace pdb1:example;


After the CBD restart, some other tablespaces were reported requiring media recovery:

SQL> alter pluggable database pdb1 open;
alter pluggable database pdb1 open
*
ERROR at line 1:
ORA-01113: file 34 needs media recovery
ORA-01110: data file 34: 'E:\APP\ORACLE12C\ORADATA\CDB\PDB1\MGMT.DBF'

RMAN> recover pluggable database pdb1;

SQL> alter pluggable database pdb1 open;

Pluggable database altered.

12c ORA-01122: database file 8 failed verification check

This are the steps that I took to recover from an datafile error in a pluggable database.

SQL> alter session set container=pdb1;

Session altered.


SQL> startup
ORA-01122: database file 8 failed verification check
ORA-01110: data file 8: 'E:\APP\ORACLE12C\ORADATA\CDB\PDB1\SYSAUX01.DBF'
ORA-01200: actual file size of 138240 is smaller than correct size of 139520
blocks
 
SQL> select open_mode from v$pdbs;

OPEN_MODE
----------
MOUNTED
 
C:\Users\oracle12c>rman target /

RMAN> validate check logical pluggable database pdb1;

Starting validate at 01-JUL-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=270 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00034 name=E:\APP\ORACLE12C\ORADATA\CDB\PDB1\MGMT.DBF

input datafile file number=00010 name=E:\APP\ORACLE12C\ORADATA\CDB\PDB1\EXAMPLE0
1.DBF
input datafile file number=00008 name=E:\APP\ORACLE12C\ORADATA\CDB\PDB1\SYSAUX01
.DBF
input datafile file number=00007 name=E:\APP\ORACLE12C\ORADATA\CDB\PDB1\SYSTEM01
.DBF
input datafile file number=00035 name=E:\APP\ORACLE12C\ORADATA\CDB\PDB1\MGMT_DEE
PDIVE.DBF
input datafile file number=00033 name=E:\APP\ORACLE12C\ORADATA\CDB\PDB1\MGMT_ECM
_DEPOT1.DBF
input datafile file number=00009 name=E:\APP\ORACLE12C\ORADATA\CDB\PDB1\SAMPLE_S
CHEMA_USERS01.DBF
channel ORA_DISK_1: validation complete, elapsed time: 00:01:35
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
7    OK     0              8136         67840           23430249
  File Name: E:\APP\ORACLE12C\ORADATA\CDB\PDB1\SYSTEM01.DBF
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              39856
  Index      0              17041
  Other      0              2807

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
8    FAILED 0              25182        139520          23429928
  File Name: E:\APP\ORACLE12C\ORADATA\CDB\PDB1\SYSAUX01.DBF
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              42004
  Index      0              21220
  Other      206            51114

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
9    OK     0              481          640             2131817
  File Name: E:\APP\ORACLE12C\ORADATA\CDB\PDB1\SAMPLE_SCHEMA_USERS01.DBF
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              15
  Index      0              2
  Other      0              142

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
10   OK     0              152794       166320          22539104
  File Name: E:\APP\ORACLE12C\ORADATA\CDB\PDB1\EXAMPLE01.DBF
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              6849
  Index      0              1209
  Other      0              5468

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
33   OK     0              6108         7680            23427785
  File Name: E:\APP\ORACLE12C\ORADATA\CDB\PDB1\MGMT_ECM_DEPOT1.DBF
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              578
  Index      0              258
  Other      0              736

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
34   OK     0              335452       454400          23427819
  File Name: E:\APP\ORACLE12C\ORADATA\CDB\PDB1\MGMT.DBF
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              54775
  Index      0              21641
  Other      0              42532

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
35   OK     0              25401        25600           17792522
  File Name: E:\APP\ORACLE12C\ORADATA\CDB\PDB1\MGMT_DEEPDIVE.DBF
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              30
  Index      0              6
  Other      0              163

validate found one or more corrupt blocks
See trace file E:\APP\ORACLE12C\diag\rdbms\cdb\cdb\trace\cdb_ora_5764.trc for de
tails
Finished validate at 01-JUL-15

In alert.log file:

Wed Jul 01 10:05:54 2015
Hex dump of (file 8, block 171) in trace file E:\APP\ORACLE12C\diag\rdbms\cdb\cdb\trace\cdb_ora_5764.trc

Corrupt block relative dba: 0x010000ab (file 8, block 171)
Bad header found during validation
Data in bad block:
 type: 1 format: 2 rdba: 0x00000ab0
 last change scn: 0x8044.000004f4 seq: 0x35 flg: 0x56
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x00000000
 check value in block header: 0x3
 computed block checksum: 0x0

Using DRA (Data Recovery Advisor) to list, advise and repair failure:

C:\Users\oracle12c>rman target /

connected to target database: CDB (DBID=1992057407)

RMAN> list failure ;

using target database control file instead of recovery catalog
Database Role: PRIMARY

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
7961       HIGH     OPEN      01-JUL-15     Datafile 8: 'E:\APP\ORACLE12C\ORADAT
A\CDB\PDB1\SYSAUX01.DBF' contains one or more corrupt blocks

RMAN> advise failure;

Database Role: PRIMARY

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
7961       HIGH     OPEN      01-JUL-15     Datafile 8: 'E:\APP\ORACLE12C\ORADAT
A\CDB\PDB1\SYSAUX01.DBF' contains one or more corrupt blocks

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=297 device type=DISK
analyzing automatic repair options complete

Mandatory Manual Actions
========================
no manual actions available

Optional Manual Actions
=======================
no manual actions available

Automated Repair Options
========================
Option Repair Description
------ ------------------
1      Recover multiple corrupt blocks in datafile 8
  Strategy: The repair includes complete media recovery with no data loss
  Repair script: E:\APP\ORACLE12C\diag\rdbms\cdb\cdb\hm\reco_3456085453.hm
 
RMAN> repair failure;

Strategy: The repair includes complete media recovery with no data loss
Repair script: E:\APP\ORACLE12C\diag\rdbms\cdb\cdb\hm\reco_3456085453.hm

contents of repair script:
   # block media recovery for multiple blocks
   recover datafile 8 block 171 to 376;

Do you really want to execute the above repair (enter YES or NO)? yes
executing repair script

Starting recover at 01-JUL-15
using channel ORA_DISK_1

channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00008
channel ORA_DISK_1: reading from backup piece E:\APP\ORACLE12C\FAST_RECOVERY_ARE
A\CDB\1FCD0926AC764930A9274C71DAB47DDC\BACKUPSET\2015_06_30\O1_MF_NNND0_20160630
_CDB_BKP_LVL_BS4H4RG8_.BKP
channel ORA_DISK_1: piece handle=E:\APP\ORACLE12C\FAST_RECOVERY_AREA\CDB\1FCD092
6AC764930A9274C71DAB47DDC\BACKUPSET\2015_06_30\O1_MF_NNND0_20160630_CDB_BKP_LVL_
BS4H4RG8_.BKP tag=20160630_CDB_BKP_LVL0
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:01:05

starting media recovery

archived log for thread 1 with sequence 1231 is already on disk as file E:\APP\O
RACLE12C\FAST_RECOVERY_AREA\CDB\ARCHIVELOG\2015_06_30\O1_MF_1_1231_BS4M4OD5_.ARC

...

archived log for thread 1 with sequence 1279 is already on disk as file E:\APP\O
RACLE12C\FAST_RECOVERY_AREA\CDB\ARCHIVELOG\2015_07_01\O1_MF_1_1279_BS746TMK_.ARC

media recovery complete, elapsed time: 00:00:47
Finished recover at 01-JUL-15
repair failure complete
 

Repair action failed. I restored and the recovery the tablespace.

RMAN> alter pluggable database pdb1 close;
RMAN> restore tablespace pdb1:sysaux;
RMAN> recover tablespace pdb1:sysaux;
RMAN> alter pluggable database pdb1 open;
RMAN> validate check logical pluggable database pdb1; -- check that are no corrupted database blocks in SYSAUX tablespace

12c recover from one redolog file lost

SQL> select group#, member, status from v$logfile;

    GROUP#
----------
MEMBER
--------------------------------------------------------------------------------

STATUS
-------
         3
E:\APP\ORACLE12C\ORADATA\CDB\REDO03.LOG


         2
E:\APP\ORACLE12C\ORADATA\CDB\REDO02.LOG


    GROUP#
----------
MEMBER
--------------------------------------------------------------------------------

STATUS
-------

         1
E:\APP\ORACLE12C\ORADATA\CDB\REDO01.LOG


         1
E:\APP\ORACLE12C\ORADATA\CDB\REDO01A.LOG

    GROUP#
----------
MEMBER
--------------------------------------------------------------------------------

STATUS
-------
INVALID

         2
E:\APP\ORACLE12C\ORADATA\CDB\REDO02A.LOG
INVALID

         3

    GROUP#
----------
MEMBER
--------------------------------------------------------------------------------

STATUS
-------
E:\APP\ORACLE12C\ORADATA\CDB\REDO03A.LOG
INVALID


6 rows selected.
 SQL> select group#, members, status from v$log;

    GROUP#    MEMBERS STATUS
---------- ---------- ----------------
         1          2 INACTIVE
         2          2 INACTIVE
         3          2 CURRENT
 

Delete one redolog file from group 1 with SO commands.

SQL> alter system switch logfile;

System altered.

1. Verify what logfile is missing

alert.log content:

Wed Jul 01 08:53:12 2015
Errors in file E:\APP\ORACLE12C\diag\rdbms\cdb\cdb\trace\cdb_lgwr_10736.trc:
ORA-00321: log 1 of thread 1, cannot update log file header
ORA-00312: online log 1 thread 1: 'E:\APP\ORACLE12C\ORADATA\CDB\REDO01A.LOG'
ORA-27070: async read/write failed
OSD-04008: WriteFile() failure, unable to write to file
O/S-Error: (OS 6) The handle is invalid.
Wed Jul 01 08:53:12 2015
Errors in file E:\APP\ORACLE12C\diag\rdbms\cdb\cdb\trace\cdb_lgwr_10736.trc:
ORA-00313: open failed for members of log group 1 of thread 1
Wed Jul 01 08:53:12 2015
Thread 1 advanced to log sequence 1264 (LGWR switch)
  Current log# 1 seq# 1264 mem# 0: E:\APP\ORACLE12C\ORADATA\CDB\REDO01.LOG


SQL> select group#, members, status from v$log;

    GROUP#    MEMBERS STATUS
---------- ---------- ----------------
         1          2 CURRENT
         2          2 INACTIVE
         3          2 ACTIVE

SQL> alter system switch logfile;

System altered.

SQL> select group#, members, status from v$log;

    GROUP#    MEMBERS STATUS
---------- ---------- ----------------
         1          2 ACTIVE
         2          2 CURRENT
         3          2 ACTIVE

2. Archive the redo log group’s contents if it's the case (database in ARCHIVELOG mode); if you clear this redo log group before archiving it, you must back up the full database to ensure maximum recoverability of the database in case of the loss of a data file.

SQL> alter system archive log group 1;
alter system archive log group 1
*
ERROR at line 1:
ORA-16013: log 1 sequence# 1264 does not need archiving

3. Make the log group INNACTIVE, with a checkpoint. If you try to CLEAR LOGFILE when the logfile group is ACTIVE or CURRENT, following error will be raised:

SQL> alter database clear logfile group 1;
alter database clear logfile group 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of instance cdb (thread 1)
ORA-00312: online log 1 thread 1: 'E:\APP\ORACLE12C\ORADATA\CDB\REDO01.LOG'
ORA-00312: online log 1 thread 1: 'E:\APP\ORACLE12C\ORADATA\CDB\REDO01A.LOG'

Make the log group INNACTIVE, with a checkpoint:

SQL> alter system checkpoint;

System altered.

SQL> select group#, members, status from v$log;

    GROUP#    MEMBERS STATUS
---------- ---------- ----------------
         1          2 INACTIVE
         2          2 CURRENT
         3          2 INACTIVE
 

4. Run command CLEAR LOGFILE GROUP to recreate the missing logfile from the group:

SQL> alter database clear logfile group 1;

Database altered.

After the recreation of redo log member, you might see the following error in alert.log:

Wed Jul 01 09:56:29 2015
Errors in file E:\APP\ORACLE12C\diag\rdbms\cdb\cdb\trace\cdb_lgwr_10736.trc:
ORA-00320: cannot read file header from log 1 of thread 1
ORA-00312: online log 1 thread 1: 'E:\APP\ORACLE12C\ORADATA\CDB\REDO01A.LOG'
ORA-27070: async read/write failed
OSD-04006: ReadFile() failure, unable to read from file
O/S-Error: (OS 6) The handle is invalid.
Wed Jul 01 09:56:29 2015
Errors in file E:\APP\ORACLE12C\diag\rdbms\cdb\cdb\trace\cdb_lgwr_10736.trc:
ORA-00321: log 1 of thread 1, cannot update log file header
ORA-00312: online log 1 thread 1: 'E:\APP\ORACLE12C\ORADATA\CDB\REDO01A.LOG'

In this case you have to recreate the log member:

SQL> alter database drop logfile member 'E:\APP\ORACLE12C\ORADATA\CDB\REDO01A.LO
G' ;

Database altered.

SQL> alter database add logfile member 'E:\APP\ORACLE12C\ORADATA\CDB\REDO01A.LOG
' to group 1;

Database altered.

Before running the ADD LOGFILE MEMBER command, be sure to delete the dropped logfile member from filesystem with SO commands. If not you will receive following error:

SQL> alter database add logfile member 'E:\APP\ORACLE12C\ORADATA\CDB\REDO01A.LOG
' to group 1;
alter database add logfile member 'E:\APP\ORACLE12C\ORADATA\CDB\REDO01A.LOG' to
group 1
*
ERROR at line 1:
ORA-00301: error in adding log file 'E:\APP\ORACLE12C\ORADATA\CDB\REDO01A.LOG'
- file cannot be created
ORA-27038: created file already exists
OSD-04010: <create> option specified, file already exists