Explore Oracle: Recover

Sunday, May 25, 2014

Recovering datafile not backed up using RMAN

(1) For test case, We are creating a new tablespace namely tbs2 and is not backed up

RMAN> sql "create tablespace tbs2 datafile ''+DATA'' size 50m";

sql statement: create tablespace tbs2 datafile ''+DATA'' size 50m

RMAN> report schema;

Report of database schema for database with db_unique_name CVRMAN

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    750      SYSTEM               ***     +DATA/cvrman/datafile/system.256.847354793
2    640      SYSAUX               ***     +DATA/cvrman/datafile/sysaux.257.847354793
3    50       UNDOTBS1             ***     +DATA/cvrman/datafile/undotbs1.318.848201233
4    5        USERS                ***     +DATA/cvrman/datafile/users.259.847354795
5    345      EXAMPLE              ***     +DATA/cvrman/datafile/example_new.dbf
6    10       TBS1                 ***     +DATA/cvrman/datafile/tbs1_new.dbf
7    50       TBS2                 ***     +DATA/cvrman/datafile/tbs2.328.848467411

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    21       TEMP                 32767       +DATA/cvrman/tempfile/temp.268.847355331

RMAN> list backup of tablespace tbs2;

specification does not match any backup in the repository

2. Manually removed the datafile of tablespace tbs2 and validating the same tablespace for errors and listing the failure details

RMAN> validate tablespace tbs2;

Starting validate at 25-MAY-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=32 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=59 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=60 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=30 device type=DISK
allocated channel: ORA_DISK_5
channel ORA_DISK_5: SID=54 device type=DISK
RMAN-06169: could not read file header for datafile 7 error reason 4
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of validate command at 05/25/2014 05:43:16
RMAN-06056: could not access datafile 7
RMAN> list failure;

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

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
11374      HIGH     OPEN      25-MAY-14     Tablespace 8: 'TBS2' is offline
2348       HIGH     OPEN      25-MAY-14     One or more non-system datafiles are missing
2342       HIGH     OPEN      21-MAY-14     One or more non-system datafiles are offline

RMAN> list failure 2348
2> ;

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

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
2348       HIGH     OPEN      25-MAY-14     One or more non-system datafiles are

RMAN> list failure 2342 detail
2> ;

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

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
2342       HIGH     OPEN      21-MAY-14     One or more non-system datafiles are
  Impact: See impact for individual child failures
  List of child failures for parent failure ID 2342
  Failure ID Priority Status    Time Detected Summary
  ---------- -------- --------- ------------- -------
  11365      HIGH     OPEN      25-MAY-14     Datafile 7: '+DATA/cvrman/datafile
    Impact: Some objects in tablespace TBS2 might be unavailable

RMAN>  list failure 2348 detail
2> ;

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

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
2348       HIGH     OPEN      25-MAY-14     One or more non-system datafiles are missing
  Impact: See impact for individual child failures
  List of child failures for parent failure ID 2348
  Failure ID Priority Status    Time Detected Summary
  ---------- -------- --------- ------------- -------
  11371      HIGH     OPEN      25-MAY-14     Datafile 7: '+DATA/cvrman/datafile/tbs2.328.848467411' is missing
    Impact: Some objects in tablespace TBS2 might be unavailable

(3) Checking the repository for backup of tablespace tbs2 to restore

RMAN> list backup of tablespace tbs2;
specification does not match any backup in the repository

(4)Intrestingly, after issuing the restore command for tablespace tbs2, the missing datafiles for tbs2 is created.

RMAN> restore tablespace tbs2;

Starting restore at 25-MAY-14
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5

creating datafile file number=7 name=+DATA/cvrman/datafile/tbs2.328.848467411
restore not done; all files read only, offline, or already restored
Finished restore at 25-MAY-14

(5) Recover the tablespace tbs2

RMAN> recover tablespace tbs2;

Starting recover at 25-MAY-14
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5

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

Finished recover at 25-MAY-14

(6) Bringing back the tablespace tbs2 to online

RMAN> sql "alter tablespace tbs2 online";

sql statement: alter tablespace tbs2 online

Thursday, May 22, 2014

Script to test RMAN recover tablespace operation

(1) Script for RMAN test recover operation

 
#Rman script to test the recovery operation of a tablespace example
sql"alter tablespace example offline";
restore tablespace example;
recover tablespace example test;

RMAN> #Rman script to test the recovery operation of a tablespace example
2> sql"alter tablespace example offline";
sql statement: alter tablespace example offline

RMAN> restore tablespace example;
Starting restore at 22-MAY-14
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5

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 00005 to +DATA/cvrman/datafile/example.308.847525493
channel ORA_DISK_1: reading from backup piece +DATA/cvrman/backupset/2014_05_22/nnndf0_tag20140522t051830_0.290.848207929
channel ORA_DISK_1: piece handle=+DATA/cvrman/backupset/2014_05_22/nnndf0_tag20140522t051830_0.290.848207929 tag=TAG20140522T051830
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 22-MAY-14

RMAN> recover tablespace example test;
Starting recover at 22-MAY-14
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5

starting media recovery
RMAN-11006: WARNING: test recovery results:
ORA-10574: Test recovery did not corrupt any data block
ORA-10573: Test recovery tested redo from change 1484624 to 1484846
ORA-10572: Test recovery canceled due to errors
ORA-10585: Test recovery can not apply redo that may modify control file

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

Finished recover at 22-MAY-14

RMAN>
RMAN> **end-of-file**