Oracle 12c: RMAN restore / recover pluggable database

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

In this scenario, we will show how to restore / recover the pluggable database;
The procedure of restore / recover of the database from Oracle 12c has changed compared to the previous versions of Oracle.
In this example, I will leave all the ORA/ RMAN errors I had to show how is different the restore from previous versions of Oracle database.

1- Create Table for test from :

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

SQL*Plus: Release 12.1.0.1.0 Production on Mon Jul 1 05:24:07 2013

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

Last Successful login time: Mon Jul 01 2013 05:22:13 -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> CREATE TABLESPACE TEST_REST_DF DATAFILE AUTOEXTEND ON MAXSIZE 100M;

Tablespace created.

SQL> create table wissem.TEST_REST_DF(ID NUMBER) TABLESPACE TEST_REST_DF;
insert into wissem.TEST_REST_DF values (1);
commit;
Table created.

SQL>
1 row created.

SQL>

Commit complete.

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>

2- Backup pluggable DB:

NOTE: here connect to 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:40:57 2013

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

connected to target database: ORAWISS (DBID=3257067578)

RMAN> backup as compressed backupset database format '/home/oracle/wissem/orawiss12c_db_%U';

Starting backup at 07/01/2013 05:41:01
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=23 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=44 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=266 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=267 device type=DISK
allocated channel: ORA_DISK_5
channel ORA_DISK_5: SID=272 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00009 name=+DATA/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/DATAFILE/sysaux.275.819112037
input datafile file number=00010 name=+DATA/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/DATAFILE/users.277.819112065
channel ORA_DISK_1: starting piece 1 at 07/01/2013 05:41:05
channel ORA_DISK_2: starting compressed full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00008 name=+DATA/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/DATAFILE/system.274.819112037
input datafile file number=00014 name=+DATA/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/DATAFILE/test_rest_df.289.819610465
channel ORA_DISK_2: starting piece 1 at 07/01/2013 05:41:05
channel ORA_DISK_3: starting compressed full datafile backup set
channel ORA_DISK_3: specifying datafile(s) in backup set
input datafile file number=00012 name=+DATA/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/DATAFILE/tbs_tst.290.819120929
input datafile file number=00013 name=+DATA/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/DATAFILE/tbs_rec.287.819121381
channel ORA_DISK_3: starting piece 1 at 07/01/2013 05:41:05
channel ORA_DISK_3: finished piece 1 at 07/01/2013 05:41:06
piece handle=/home/oracle/wissem/orawiss12c_db_28odkh7h_1_1 tag=TAG20130701T054104 comment=NONE
channel ORA_DISK_3: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_2: finished piece 1 at 07/01/2013 05:41:20
piece handle=/home/oracle/wissem/orawiss12c_db_27odkh7h_1_1 tag=TAG20130701T054104 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_1: finished piece 1 at 07/01/2013 05:41:40
piece handle=/home/oracle/wissem/orawiss12c_db_26odkh7h_1_1 tag=TAG20130701T054104 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
Finished backup at 07/01/2013 05:41:40

Starting Control File and SPFILE Autobackup at 07/01/2013 05:41:40
piece handle=+DATA/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/AUTOBACKUP/2013_07_01/s_819610900.314.819610901 comment=NONE
Finished Control File and SPFILE Autobackup at 07/01/2013 05:41:41

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
---- -------- -------------------- ------- ------------------------
8    270      SYSTEM               ***     +DATA/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/DATAFILE/system.274.819112037
9    690      SYSAUX               ***     +DATA/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/DATAFILE/sysaux.275.819112037
10   5        USERS                ***     +DATA/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/DATAFILE/users.277.819112065
12   100      TBS_TST              ***     +DATA/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/DATAFILE/tbs_tst.290.819120929
13   100      TBS_REC              ***     +DATA/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/DATAFILE/tbs_rec.287.819121381
14   100      TEST_REST_DF         ***     +DATA/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/DATAFILE/test_rest_df.289.819610465

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
3    20       TEMP                 32767       +DATA/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/TEMPFILE/temp.276.819112043

RMAN> list backup summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time     #Pieces #Copies Compressed Tag
------- -- -- - ----------- ------------------- ------- ------- ---------- ---
7       B  A  A DISK        06/26/2013 12:39:27 1       1       NO         TAG20130626T123927
16      B  1  A DISK        06/27/2013 04:42:07 5       1       NO         TAG20130627T044206
26      B  F  A DISK        06/29/2013 06:57:42 1       1       NO         TAG20130629T065739
27      B  F  A DISK        06/29/2013 06:57:43 1       1       NO         TAG20130629T065739
28      B  F  A DISK        06/29/2013 06:57:45 1       1       NO         TAG20130629T065739
29      B  F  A DISK        06/29/2013 06:57:45 1       1       NO         TAG20130629T065739
36      B  F  A DISK        07/01/2013 05:41:05 1       1       YES        TAG20130701T054104
37      B  F  A DISK        07/01/2013 05:41:14 1       1       YES        TAG20130701T054104
38      B  F  A DISK        07/01/2013 05:41:34 1       1       YES        TAG20130701T054104
39      B  F  A DISK        07/01/2013 05:41:41 1       1       NO         TAG20130701T054140


Restore pluggable DB;

3- Let’s Try to restore the pluggable database:

RMAN> restore pluggable database orawiss12c;

Starting restore at 07/01/2013 05:42:43
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
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 07/01/2013 05:42:43
RMAN-07538: Pluggable Database qualifier not allowed when connected to a Pluggable Database

RMAN> exit

So, we need to run the restore from the Container DB session?
Ok let’s use rman backup through the container DB:

sandbox1(orawiss):/home/oracle>rman

Recovery Manager: Release 12.1.0.1.0 - Production on Mon Jul 1 05:43:23 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>  restore pluggable database orawiss12c;

Starting restore at 07/01/2013 05:43:50
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=23 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=267 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=271 device type=DISK
allocated channel: ORA_DISK_5
channel ORA_DISK_5: SID=266 device type=DISK

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 00012 to +DATA/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/DATAFILE/tbs_tst.290.819120929
channel ORA_DISK_1: restoring datafile 00013 to +DATA/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/DATAFILE/tbs_rec.287.819121381
channel ORA_DISK_1: reading from backup piece /home/oracle/wissem/orawiss12c_db_28odkh7h_1_1
channel ORA_DISK_2: starting datafile backup set restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_DISK_2: restoring datafile 00008 to +DATA/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/DATAFILE/system.274.819112037
channel ORA_DISK_2: restoring datafile 00014 to +DATA/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/DATAFILE/test_rest_df.289.819610465
channel ORA_DISK_2: reading from backup piece /home/oracle/wissem/orawiss12c_db_27odkh7h_1_1
channel ORA_DISK_3: starting datafile backup set restore
channel ORA_DISK_3: specifying datafile(s) to restore from backup set
channel ORA_DISK_3: restoring datafile 00009 to +DATA/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/DATAFILE/sysaux.275.819112037
channel ORA_DISK_3: restoring datafile 00010 to +DATA/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/DATAFILE/users.277.819112065
channel ORA_DISK_3: reading from backup piece /home/oracle/wissem/orawiss12c_db_26odkh7h_1_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 07/01/2013 05:43:53
ORA-19870: error while restoring backup piece /home/oracle/wissem/orawiss12c_db_28odkh7h_1_1
ORA-19573: cannot obtain exclusive enqueue for datafile 12

RMAN> exit
Recovery Manager complete.

Ok that means we need to shutdown pluggable DB?
Let’s shutdown the pluggable DB and see.

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

SQL*Plus: Release 12.1.0.1.0 Production on Mon Jul 1 05:44:20 2013

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

Last Successful login time: Mon Jul 01 2013 05:30:50 -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> shutdown immediate;
ORA-01031: insufficient privileges


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

Ok, Even with user “wissem” has sysdba privilege we got insufficient privileges error.
Let’s do it from COntainer DB and try ?

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

SQL*Plus: Release 12.1.0.1.0 Production on Mon Jul 1 05:44: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> alter session set container=ORAWISS12C;

Session altered.

SQL> shutdown immediate;
Pluggable Database closed.
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

Ok, now DB shutdown; let ‘s try to restore the pluggable DB;
Note the syxtax of “restore pluggable database”, this is new in 12c.

sandbox1(orawiss):/home/oracle>rman

Recovery Manager: Release 12.1.0.1.0 - Production on Mon Jul 1 05:45:13 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>  restore pluggable database orawiss12c;

Starting restore at 07/01/2013 05:45:27
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=267 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=284 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=266 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=43 device type=DISK
allocated channel: ORA_DISK_5
channel ORA_DISK_5: SID=273 device type=DISK

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 00012 to +DATA/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/DATAFILE/tbs_tst.290.819120929
channel ORA_DISK_1: restoring datafile 00013 to +DATA/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/DATAFILE/tbs_rec.287.819121381
channel ORA_DISK_1: reading from backup piece /home/oracle/wissem/orawiss12c_db_28odkh7h_1_1
channel ORA_DISK_2: starting datafile backup set restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_DISK_2: restoring datafile 00008 to +DATA/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/DATAFILE/system.274.819112037
channel ORA_DISK_2: restoring datafile 00014 to +DATA/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/DATAFILE/test_rest_df.289.819610465
channel ORA_DISK_2: reading from backup piece /home/oracle/wissem/orawiss12c_db_27odkh7h_1_1
channel ORA_DISK_3: starting datafile backup set restore
channel ORA_DISK_3: specifying datafile(s) to restore from backup set
channel ORA_DISK_3: restoring datafile 00009 to +DATA/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/DATAFILE/sysaux.275.819112037
channel ORA_DISK_3: restoring datafile 00010 to +DATA/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/DATAFILE/users.277.819112065
channel ORA_DISK_3: reading from backup piece /home/oracle/wissem/orawiss12c_db_26odkh7h_1_1
channel ORA_DISK_1: piece handle=/home/oracle/wissem/orawiss12c_db_28odkh7h_1_1 tag=TAG20130701T054104
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_2: piece handle=/home/oracle/wissem/orawiss12c_db_27odkh7h_1_1 tag=TAG20130701T054104
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:00:15
channel ORA_DISK_3: piece handle=/home/oracle/wissem/orawiss12c_db_26odkh7h_1_1 tag=TAG20130701T054104
channel ORA_DISK_3: restored backup piece 1
channel ORA_DISK_3: restore complete, elapsed time: 00:00:35
Finished restore at 07/01/2013 05:46:05

Pluggable DB restored, let’s try the recover.
Note the syxtax of “recover pluggable database”; , this is new in 12c.

RMAN> recover pluggable database ORAWISS12C;

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

Finished recover at 07/01/2013 05:46:15

Good, we recovered the PDB; Let’s open the PDB;

RMAN> alter pluggable database ORAWISS12C OPEN;

Statement processed

RMAN> 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


RMAN> exit


Recovery Manager complete.

From the output above, we restored / recovered the pluggable DB and we opened it READ WRITE Mode.

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

SQL*Plus: Release 12.1.0.1.0 Production on Mon Jul 1 05:47:44 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> alter session set container=ORAWISS12C;

Session altered.

SQL>  select * from wissem.TEST_REST_DF;

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

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>



One Comment to “Oracle 12c: RMAN restore / recover pluggable database”

  1. Kal says:

    Quality – thanks…


Leave a Comment





Polls

which oracle topic interests you most?

View Results

Loading ... Loading ...


Oracle Class Tweets


Recent Posts


Recent Comments

  • 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!
  • Nav: Indeed helpful, to the point article..
  • Koti: Can you please explain all the sections of AWR report in RAC. I mean to ask output produced by awrgrpt.sql...
  • Himanshu: Nice post.can you please comment on this Why RMAN restored the DATA_REORTING datafie to +DATA diskgroup?
  • gabriel: what version of sqldeveloper support plugable database?