Oracle 12c: RMAN restore / recover datafile

July 1st, 2013 | Posted in 12c, Blog, RMAN | No Comments

In this scenario, we will show how to restore / recover a datafile belongs to the pluggable database;

I- Restore / Recover Datafile connecting from Container DB:

First, connect to the target container database:

sandbox1(orawiss):/home/oracle>sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Mon Jul 1 04:58:35 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;

    CON_ID       DBID NAME                           OPEN_MODE
---------- ---------- ------------------------------ ----------
         2 4061740350 PDB$SEED                       READ ONLY
         3 1574282659 ORAWISS12C                     READ WRITE

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

Backup the database from CDB;

sandbox1(orawiss):/home/oracle>rman

Recovery Manager: Release 12.1.0.1.0 - Production on Mon Jul 1 04:59:50 2013

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

RMAN> CONNECT TARGET "sys AS SYSBACKUP";

target database Password:
connected to target database: ORAWISS (DBID=3257067578)

RMAN> backup as copy section size 20M datafile 13 format '/home/oracle/wissem/backup_dfile_13_%U';

Starting backup at 07/01/2013 05:00:54
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 copy
input datafile file number=00013 name=+DATA/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/DATAFILE/tbs_rec.287.819121381
backing up blocks 1 through 2560
channel ORA_DISK_2: starting datafile copy
input datafile file number=00013 name=+DATA/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/DATAFILE/tbs_rec.287.819121381
backing up blocks 2561 through 5120
channel ORA_DISK_3: starting datafile copy
input datafile file number=00013 name=+DATA/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/DATAFILE/tbs_rec.287.819121381
backing up blocks 5121 through 7680
channel ORA_DISK_4: starting datafile copy
input datafile file number=00013 name=+DATA/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/DATAFILE/tbs_rec.287.819121381
backing up blocks 7681 through 10240
channel ORA_DISK_5: starting datafile copy
input datafile file number=00013 name=+DATA/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/DATAFILE/tbs_rec.287.819121381
backing up blocks 10241 through 12800
output file name=/home/oracle/wissem/backup_dfile_13_data_D-ORAWISS_I-3257067578_TS-TBS_REC_FNO-13_1kodkes6 tag=TAG20130701T050054
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:04
output file name=/home/oracle/wissem/backup_dfile_13_data_D-ORAWISS_I-3257067578_TS-TBS_REC_FNO-13_1kodkes6 tag=TAG20130701T050054
channel ORA_DISK_2: datafile copy complete, elapsed time: 00:00:03
output file name=/home/oracle/wissem/backup_dfile_13_data_D-ORAWISS_I-3257067578_TS-TBS_REC_FNO-13_1kodkes6 tag=TAG20130701T050054
channel ORA_DISK_3: datafile copy complete, elapsed time: 00:00:01
output file name=/home/oracle/wissem/backup_dfile_13_data_D-ORAWISS_I-3257067578_TS-TBS_REC_FNO-13_1kodkes6 tag=TAG20130701T050054
channel ORA_DISK_4: datafile copy complete, elapsed time: 00:00:01
output file name=/home/oracle/wissem/backup_dfile_13_data_D-ORAWISS_I-3257067578_TS-TBS_REC_FNO-13_1kodkes6 tag=TAG20130701T050054
channel ORA_DISK_5: datafile copy complete, elapsed time: 00:00:01
Finished backup at 07/01/2013 05:00:59

Starting Control File and SPFILE Autobackup at 07/01/2013 05:00:59
piece handle=+DATA/ORAWISS/AUTOBACKUP/2013_07_01/s_819608459.315.819608459 comment=NONE
Finished Control File and SPFILE Autobackup at 07/01/2013 05:01:00

Let’s check the list of backup available for datafile 13;


RMAN> LIST COPY OF DATAFILE 13;

List of Datafile Copies
=======================

Key     File S Completion Time     Ckp SCN    Ckp Time
------- ---- - ------------------- ---------- -------------------
6       13   A 07/01/2013 05:00:55 2325346    07/01/2013 05:00:54
        Name: /home/oracle/wissem/backup_dfile_13_data_D-ORAWISS_I-3257067578_TS-TBS_REC_FNO-13_1kodkes6
        Tag: TAG20130701T050054

5       13   A 07/01/2013 05:00:25 2325148    07/01/2013 05:00:24
        Name: /home/oracle/wissem/bck_datafile_13_data_D-ORAWISS_I-3257067578_TS-TBS_REC_FNO-13_1iodker8
        Tag: TAG20130701T050023

4       13   A 06/27/2013 04:46:15 2024175    06/27/2013 04:46:14
        Name: /home/oracle/wissem/bckp_dfile_13_data_D-ORAWISS_I-3257067578_TS-TBS_REC_FNO-13_14od784m
        Tag: TAG20130627T044614

3       13   A 06/27/2013 04:43:52 2024062    06/27/2013 04:43:52
        Name: /home/oracle/wissem/backup_dfile_13_data_D-ORAWISS_I-3257067578_TS-TBS_REC_FNO-13_0uod7808
        Tag: TAG20130627T044352
RMAN> report schema;

Report of database schema for database with db_unique_name ORAWISS

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    800      SYSTEM               ***     +DATA/ORAWISS/DATAFILE/system.258.819111197
3    940      SYSAUX               ***     +DATA/ORAWISS/DATAFILE/sysaux.257.819111131
4    230      UNDOTBS1             ***     +DATA/ORAWISS/DATAFILE/undotbs1.260.819111263
5    260      PDB$SEED:SYSTEM      ***     +DATA/ORAWISS/DD7C48AA5A4404A2E04325AAE80A403C/DATAFILE/system.271.819111307
6    5        USERS                ***     +DATA/ORAWISS/DATAFILE/users.259.819111261
7    640      PDB$SEED:SYSAUX      ***     +DATA/ORAWISS/DD7C48AA5A4404A2E04325AAE80A403C/DATAFILE/sysaux.270.819111307
8    270      ORAWISS12C:SYSTEM    ***     +DATA/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/DATAFILE/system.274.819112037
9    690      ORAWISS12C:SYSAUX    ***     +DATA/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/DATAFILE/sysaux.275.819112037
10   5        ORAWISS12C:USERS     ***     +DATA/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/DATAFILE/users.277.819112065
12   100      ORAWISS12C:TBS_TST   ***     +DATA/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/DATAFILE/tbs_tst.290.819120929
13   100      ORAWISS12C:TBS_REC   ***     +DATA/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/DATAFILE/tbs_rec.287.819121381

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    88       TEMP                 32767       +DATA/ORAWISS/TEMPFILE/temp.269.819111301
2    87       PDB$SEED:TEMP        32767       +DATA/ORAWISS/DD7C48AA5A4404A2E04325AAE80A403C/DATAFILE/pdbseed_temp01.dbf
3    20       ORAWISS12C:TEMP      32767       +DATA/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/TEMPFILE/temp.276.819112043

RMAN> exit


Recovery Manager complete.

Let’s recover the datafile 13 from CDB backup;

sandbox1(orawiss):/home/oracle>sqlplus wissem/wissem@ORAWISS12C

SQL*Plus: Release 12.1.0.1.0 Production on Mon Jul 1 05:03:59 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Last Successful login time: Sat Jun 29 2013 06:39:15 -04:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> alter database datafile 13 offline;

Database altered.

SQL> alter database datafile 13 online;
alter database datafile 13 online
*
ERROR at line 1:
ORA-01113: file 13 needs media recovery
ORA-01110: data file 13:
'+DATA/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/DATAFILE/tbs_rec.287.819121381'


SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
sandbox1(orawiss):/home/oracle>rman

Recovery Manager: Release 12.1.0.1.0 - Production on Mon Jul 1 05:05:08 2013

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

RMAN> CONNECT TARGET "sys AS SYSBACKUP";

target database Password:
connected to target database: ORAWISS (DBID=3257067578)

RMAN> list backup of datafile 13;

using target database control file instead of recovery catalog

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


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
16      Incr 1  1.24M      DISK        00:00:01     06/27/2013 04:42:07
  List of Datafiles in backup set 16
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  13   1  Incr 2023976    06/27/2013 04:42:06 +DATA/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/DATAFILE/tbs_rec.287.819121381

  Backup Set Copy #1 of backup set 16
  Device Type Elapsed Time Completion Time     Compressed Tag
  ----------- ------------ ------------------- ---------- ---
  DISK        00:00:01     06/27/2013 04:42:07 NO         TAG20130627T044206

    List of Backup Pieces for backup set 16 Copy #1
    BP Key  Pc# Status      Piece Name
    ------- --- ----------- ----------
    16      1   AVAILABLE   /home/oracle/wissem/backup_datafile_13_0ood77su_1_1
    17      2   AVAILABLE   /home/oracle/wissem/backup_datafile_13_0ood77su_2_1
    18      3   AVAILABLE   /home/oracle/wissem/backup_datafile_13_0ood77su_3_1
    19      4   AVAILABLE   /home/oracle/wissem/backup_datafile_13_0ood77su_4_1
    20      5   AVAILABLE   /home/oracle/wissem/backup_datafile_13_0ood77su_5_1

RMAN> restore datafile 13;

Starting restore at 07/01/2013 05:05:48
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=267 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=44 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=50 device type=DISK
allocated channel: ORA_DISK_5
channel ORA_DISK_5: SID=266 device type=DISK

channel ORA_DISK_1: restoring datafile 00013
input datafile copy RECID=6 STAMP=819608455 file name=/home/oracle/wissem/backup_dfile_13_data_D-ORAWISS_I-3257067578_TS-TBS_REC_FNO-13_1kodkes6
destination for restore of datafile 00013: +DATA/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/DATAFILE/tbs_rec.287.819121381
channel ORA_DISK_1: copied datafile copy of datafile 00013
output file name=+DATA/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/DATAFILE/tbs_rec.287.819121381 RECID=0 STAMP=0
Finished restore at 07/01/2013 05:05:53

RMAN> recover datafile 13;

Starting recover at 07/01/2013 05:05:59
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 07/01/2013 05:06:00


RMAN> alter database datafile 13 ONLINE;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 07/01/2013 05:06:17
ORA-01516: nonexistent log file, data file, or temporary file "13"

RMAN> exit


Recovery Manager complete.

Note from the output above, we need to connect to ORAWISS12C PDB to make the datafile ONLINE;


sandbox1(orawiss):/home/oracle>sqlplus wissem/wissem@ORAWISS12C

SQL*Plus: Release 12.1.0.1.0 Production on Mon Jul 1 05:06:43 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Last Successful login time: Mon Jul 01 2013 05:04:00 -04:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> alter database datafile 13 ONLINE;

Database altered.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
sandbox1(orawiss):/home/oracle>

II- Restore / Recover Datafile connecting to Pluggable DB:

sandbox1(orawiss):/home/oracle>sqlplus wissem/wissem@ORAWISS12C

SQL*Plus: Release 12.1.0.1.0 Production on Mon Jul 1 05:27:48 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Last Successful login time: Mon Jul 01 2013 05:24:07 -04:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL>  alter database datafile 14 offline;

Database altered.

SQL> alter database datafile 14 online;
alter database datafile 14 online
*
ERROR at line 1:
ORA-01113: file 14 needs media recovery
ORA-01110: data file 14:
'+DATA/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/DATAFILE/test_rest_df.289.819609
909'


SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

Let’s restore / recover the datafile after being connected to the pluggable DB;

sandbox1(orawiss):/home/oracle>rman target wissem/wissem@ORAWISS12C

Recovery Manager: Release 12.1.0.1.0 - Production on Mon Jul 1 05:28:41 2013

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

connected to target database: ORAWISS (DBID=3257067578)

RMAN> restore datafile 14;

Starting restore at 07/01/2013 05:28:48
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=40 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=46 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=284 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=272 device type=DISK
allocated channel: ORA_DISK_5
channel ORA_DISK_5: SID=273 device type=DISK

channel ORA_DISK_1: restoring datafile 00014
input datafile copy RECID=8 STAMP=819610032 file name=/home/oracle/wissem/backup_dfile_14_data_D-ORAWISS_I-3257067578_TS-TEST_REST_DF_FNO-14_20odkgdg
destination for restore of datafile 00014: +DATA/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/DATAFILE/test_rest_df.289.819609909
channel ORA_DISK_1: copied datafile copy of datafile 00014
output file name=+DATA/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/DATAFILE/test_rest_df.289.819609909 RECID=0 STAMP=0
Finished restore at 07/01/2013 05:28:51

RMAN> recover datafile 14;

Starting recover at 07/01/2013 05:29:16
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:01

Finished recover at 07/01/2013 05:29:17

RMAN>  alter database datafile 14 online;

Statement processed

RMAN> exit


Recovery Manager complete.
sandbox1(orawiss):/home/oracle>
RMAN> select * from wissem.TEST_REST_DF;

        ID
----------
         1

RMAN> exit


Recovery Manager complete.
sandbox1(orawiss):/home/oracle>
sandbox1(orawiss):/home/oracle>



No Comments to “Oracle 12c: RMAN restore / recover datafile”

There are no comments yet, add one below.


Leave a Comment





Polls

which oracle topic interests you most?

View Results

Loading ... Loading ...


Oracle Class Tweets


Recent Posts


Recent Comments

  • Mike: Very nice article!! Thanks for sharing!!
  • yathish: I really appreciate if you can send me, if you have tutorial for 11g (11.2.0.1) RAC & ASM (prefer to be...
  • Farrukh Kamal: Fahad, I’m interesting in pursuing remote employment with Pythian. What tips would you suggest...
  • pavankumar: for the error it need to set env correctly http://orcl11gdba.blogspot.in/ 2014/08/asmcmd-08102-no-con...
  • wissem: Post result off; crsctl check has crsctl check crs
  • ron: unable to connect to Cluster Synchronization Service
  • KevinB: Please post or attach the response file
  • nnarimanov: I’d like to clarify the reason why rman restores all datafiles into +DATA. It’s because of...
  • Sergio: Hi, Where is declared the function agent_clean() ? I dont see agent_clean() definition !! regards, Sergio
  • Sikandar: I do salute you brother, This is very………..nti mes helpful and correct documents. I...
  • Karthik Singh: Thanks for sharing a useful article. Looks like you have installed weblogic 10.3.4. Then shall I...
  • Joseph: Paul, Here target is from which database we are taking cloning. and auxilary is the cloning database. Wissem,...
  • sundar: In Doc ID 1472171.1 it says that the snapshot control file should be in a shared location for RAC databases....
  • Taoqir: Hello, This is a wonderfull and very helping material.now I am going to configure Data Guard. Thanks
  • Adrian Iriarte: Saludos, Que buen articulo, casi siempre al momento de querer implementar soluciones para el...
  • Patricia: Hi Wissem, is there a workaround for this? I mean I want to use the sqlnet.authentication_services =none,...
  • Brian: I created the cdb manually using the instruction, but then I tried to create pdb within in, it failed. have...
  • Farmer Johnson: Hi Wissem. How did you avoid the error you had previsouly encountered: “sudo opatch auto...
  • Héctor Moro: Querido Nelson; Después de entrevistar a mas de 50 profesionales de primer nivel en Uruguay, al fin te...
  • What is NO2 Maximus: I genuinely apрreciated your site! You shoulԁ update it wiϳth new info!