Loading....

Here are a few examples of backup and recovery in an Oracle 12c multitenant environment with Container and Pluggable databases involved.

The first thing to keep in mind is the structure of a 12c Container and Pluggable database. There is only one set of control files and redo log files and that is at the container level. So the same principle applies to the archived redo log files as well.

Individual pluggable databases do not have redo log files or control files – but they can have individual temporary tablespace tempfiles.

Backup can be performed at the container level.

Single RMAN backup database command will backup the root container database as well as all the pluggable databases.

RMAN> backup database;

Starting backup at 02-APR-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=31 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00003 name=/u01/app/oracle/oradata/condb1/sysaux01.dbf
input datafile file number=00001 name=/u01/app/oracle/oradata/condb1/system01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/condb1/undotbs01.dbf
input datafile file number=00016 name=/u01/app/oracle/oradata/condb1/ggs_data01.dbf
input datafile file number=00006 name=/u01/app/oracle/oradata/condb1/users01.dbf
channel ORA_DISK_1: starting piece 1 at 02-APR-14
channel ORA_DISK_1: finished piece 1 at 02-APR-14
piece handle=/u01/app/oracle/fast_recovery_area/CONDB1/backupset/2014_04_02/o1_mf_nnndf_TAG20140402T081602_9mpop2my_.bkp tag=TAG20140402T081602 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00009 name=/u01/app/oracle/oradata/condb1/pdb1/sysaux01.dbf
input datafile file number=00011 name=/u01/app/oracle/oradata/condb1/pdb1/example01.dbf
input datafile file number=00008 name=/u01/app/oracle/oradata/condb1/pdb1/system01.dbf
input datafile file number=00010 name=/u01/app/oracle/oradata/condb1/pdb1/SAMPLE_SCHEMA_users01.dbf
channel ORA_DISK_1: starting piece 1 at 02-APR-14
channel ORA_DISK_1: finished piece 1 at 02-APR-14
piece handle=/u01/app/oracle/fast_recovery_area/CONDB1/EA795F28CCF12888E0438D15060AAF42/backupset/2014_04_02/o1_mf_nnndf_TAG20140402T081602_9mpoqhvh_.bkp tag=TAG20140402T081602 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00013 name=/u01/app/oracle/oradata/condb1/pdb2/sysaux01.dbf
input datafile file number=00015 name=/u01/app/oracle/oradata/condb1/pdb2/example01.dbf
input datafile file number=00012 name=/u01/app/oracle/oradata/condb1/pdb2/system01.dbf
input datafile file number=00014 name=/u01/app/oracle/oradata/condb1/pdb2/SAMPLE_SCHEMA_users01.dbf
channel ORA_DISK_1: starting piece 1 at 02-APR-14
channel ORA_DISK_1: finished piece 1 at 02-APR-14
piece handle=/u01/app/oracle/fast_recovery_area/CONDB1/EAA0B10062AA3A41E0438D15060AC71B/backupset/2014_04_02/o1_mf_nnndf_TAG20140402T081602_9mpor93z_.bkp tag=TAG20140402T081602 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=/u01/app/oracle/oradata/condb1/pdbseed/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/condb1/pdbseed/system01.dbf
channel ORA_DISK_1: starting piece 1 at 02-APR-14
channel ORA_DISK_1: finished piece 1 at 02-APR-14
piece handle=/u01/app/oracle/fast_recovery_area/CONDB1/EA792426F4762CDBE0438D15060A3359/backupset/2014_04_02/o1_mf_nnndf_TAG20140402T081602_9mpos2bl_.bkp tag=TAG20140402T081602 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 02-APR-14

Starting Control File and SPFILE Autobackup at 02-APR-14
piece handle=/u01/app/oracle/fast_recovery_area/CONDB1/autobackup/2014_04_02/o1_mf_s_843812283_9mposw73_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 02-APR-14

Backups can also be performed at the pluggable database level. Note control file which is backed up in this case is at the container database level.

In this we have connected via RMAN to the container database and are backing up one of the pluggable databases.

RMAN> backup pluggable  database pdb2;

Starting backup at 02-APR-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00013 name=/u01/app/oracle/oradata/condb1/pdb2/sysaux01.dbf
input datafile file number=00015 name=/u01/app/oracle/oradata/condb1/pdb2/example01.dbf
input datafile file number=00012 name=/u01/app/oracle/oradata/condb1/pdb2/system01.dbf
input datafile file number=00014 name=/u01/app/oracle/oradata/condb1/pdb2/SAMPLE_SCHEMA_users01.dbf
channel ORA_DISK_1: starting piece 1 at 02-APR-14
channel ORA_DISK_1: finished piece 1 at 02-APR-14
piece handle=/u01/app/oracle/fast_recovery_area/CONDB1/EAA0B10062AA3A41E0438D15060AC71B/backupset/2014_04_02/o1_mf_nnndf_TAG20140402T120428_9mq32f70_.bkp tag=TAG20140402T120428 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 02-APR-14

Starting Control File and SPFILE Autobackup at 02-APR-14
piece handle=/u01/app/oracle/fast_recovery_area/CONDB1/autobackup/2014_04_02/o1_mf_s_843825894_9mq336jk_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 02-APR-14

We can also use RMAN to connect to an individual pluggable database instead of the container database.

$ rman target sys/syspasswd@pdb1

Recovery Manager: Release 12.1.0.1.0 - Production on Wed Apr 2 08:48:30 2014

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

connected to target database: CONDB1 (DBID=3738773602)

RMAN> list backup of database;

using target database control file instead of recovery catalog

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


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5       Full    905.24M    DISK        00:00:22     02-APR-14
        BP Key: 5   Status: AVAILABLE  Compressed: NO  Tag: TAG20140402T081602
        Piece Name: /u01/app/oracle/fast_recovery_area/CONDB1/EA795F28CCF12888E0438D15060AAF42/backupset/2014_04_02/o1_mf_nnndf_TAG20140402T081602_9mpoqhvh_.bkp
  List of Datafiles in backup set 5
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  8       Full 8195981    02-APR-14 /u01/app/oracle/oradata/condb1/pdb1/system01.dbf
  9       Full 8195981    02-APR-14 /u01/app/oracle/oradata/condb1/pdb1/sysaux01.dbf
  10      Full 8195981    02-APR-14 /u01/app/oracle/oradata/condb1/pdb1/SAMPLE_SCHEMA_users01.dbf
  11      Full 8195981    02-APR-14 /u01/app/oracle/oradata/condb1/pdb1/example01.dbf

Loss of Tempfile at pluggable database level

Temp file is automatically created when the pluggable database is closed and opened.

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/condb1/pdb1/pdb1_temp01.dbf

SQL> !rm /u01/app/oracle/oradata/condb1/pdb1/pdb1_temp01.dbf


SQL> conn sys as sysdba
Enter password:
Connected.
SQL> alter pluggable database pdb1 close immediate;

Pluggable database altered.

SQL> alter pluggable database pdb1 open read write;

Pluggable database altered.

SQL> !ls /u01/app/oracle/oradata/condb1/pdb1/pdb1_temp01.dbf
/u01/app/oracle/oradata/condb1/pdb1/pdb1_temp01.dbf

Loss of Non-System data file at pluggable database level

Online recovery of SYSAUX tablespace

SQL> conn sys/syspasswd@pdb1 as sysdba
Connected.

SQL>  select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/condb1/undotbs01.dbf
/u01/app/oracle/oradata/condb1/pdb1/system01.dbf
/u01/app/oracle/oradata/condb1/pdb1/sysaux01.dbf
/u01/app/oracle/oradata/condb1/pdb1/SAMPLE_SCHEMA_users01.dbf
/u01/app/oracle/oradata/condb1/pdb1/example01.dbf

SQL> !rm /u01/app/oracle/oradata/condb1/pdb1/sysaux01.dbf

SQL> alter tablespace sysaux offline;

Tablespace altered.

$ rman target sys/syspasswd@pdb1

Recovery Manager: Release 12.1.0.1.0 - Production on Wed Apr 2 10:31:30 2014

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

connected to target database: CONDB1 (DBID=3738773602)

RMAN> restore tablespace sysaux;

Starting restore at 02-APR-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=274 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 00009 to /u01/app/oracle/oradata/condb1/pdb1/sysaux01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/CONDB1/EA795F28CCF12888E0438D15060AAF42/backupset/2014_04_02/o1_mf_nnndf_TAG20140402T081602_9mpoqhvh_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/CONDB1/EA795F28CCF12888E0438D15060AAF42/backupset/2014_04_02/o1_mf_nnndf_TAG20140402T081602_9mpoqhvh_.bkp tag=TAG20140402T081602
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 02-APR-14

RMAN> recover tablespace sysaux;

Starting recover at 02-APR-14
using channel ORA_DISK_1

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

Finished recover at 02-APR-14

RMAN> alter tablespace sysaux online;

Statement processed

Loss of SYSTEM tablespace datafile at pluggable database level

Note – online pluggable database recovery cannot be performed.

The entire container database has to be shut down and mounted and pluggable database recovered


SQL> !rm /u01/app/oracle/oradata/condb1/pdb1/system01.dbf


SQL> alter session set container=pdb1;

Session altered.


SQL> shutdown abort
ORA-00604: error occurred at recursive SQL level 1
ORA-01116: error in opening database file 8
ORA-01110: data file 8: '/u01/app/oracle/oradata/condb1/pdb1/system01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

SQL> alter pluggable database pdb1 close;
alter pluggable database pdb1 close
*
ERROR at line 1:
ORA-01116: error in opening database file 8
ORA-01110: data file 8: '/u01/app/oracle/oradata/condb1/pdb1/system01.dbf'
ORA-27041: unable to open file

To recover the pluggable database we need to connect to the container database, shutdown the container database (this will shutdown all other pluggable databases) , mount the container database and then recover the pluggable database.


SQL> shutdown abort
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 2137886720 bytes
Fixed Size                  2290416 bytes
Variable Size            1207962896 bytes
Database Buffers          922746880 bytes
Redo Buffers                4886528 bytes
Database mounted.

RMAN> restore tablespace pdb1:system;

Starting restore at 02-APR-14
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 00008 to /u01/app/oracle/oradata/condb1/pdb1/system01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/CONDB1/EA795F28CCF12888E0438D15060AAF42/backupset/2014_04_02/o1_mf_nnndf_TAG20140402T081602_9mpoqhvh_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/CONDB1/EA795F28CCF12888E0438D15060AAF42/backupset/2014_04_02/o1_mf_nnndf_TAG20140402T081602_9mpoqhvh_.bkp tag=TAG20140402T081602
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 02-APR-14

RMAN> recover tablespace pdb1:system;

Starting recover at 02-APR-14
using channel ORA_DISK_1

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

Finished recover at 02-APR-14

RMAN> alter database open;

Statement processed


RMAN> alter pluggable database all open read write;

Statement processed

Loss of SYSTEM data file at the Container database level

Note – If we lose the container database SYSTEM datafile we cannot connect to any of the pluggable databases as well.

We have to shutdown abort the container database and then mount the container database and perform offline recovery of the SYSTEM tablespace


SQL> !rm /u01/app/oracle/oradata/condb1/system01.dbf

SQL> alter system flush buffer_cache;

System altered.

SQL> select count(*) from dba_objects;
select count(*) from dba_objects
                     *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/app/oracle/oradata/condb1/system01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


SQL> alter session set container=pdb1;
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/app/oracle/oradata/condb1/system01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00604: error occurred at recursive SQL level 2
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/app/oracle/oradata/condb1/system01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

SQL> shutdown abort
ORACLE instance shut down.

$ rman target /

Recovery Manager: Release 12.1.0.1.0 - Production on Wed Apr 2 10:43:29 2014

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

connected to target database (not started)

RMAN> startup mount;

Oracle instance started
database mounted

Total System Global Area    2137886720 bytes

Fixed Size                     2290416 bytes
Variable Size               1207962896 bytes
Database Buffers             922746880 bytes
Redo Buffers                   4886528 bytes

RMAN> restore tablespace system;

Starting restore at 02-APR-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=11 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 00001 to /u01/app/oracle/oradata/condb1/system01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/CONDB1/backupset/2014_04_02/o1_mf_nnndf_TAG20140402T081602_9mpop2my_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/CONDB1/backupset/2014_04_02/o1_mf_nnndf_TAG20140402T081602_9mpop2my_.bkp tag=TAG20140402T081602
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 02-APR-14

RMAN> recover tablespace system;

Starting recover at 02-APR-14
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 175 is already on disk as file /u01/app/oracle/fast_recovery_area/CONDB1/archivelog/2014_04_02/o1_mf_1_175_9mpqqpvv_.arc
archived log for thread 1 with sequence 176 is already on disk as file /u01/app/oracle/fast_recovery_area/CONDB1/archivelog/2014_04_02/o1_mf_1_176_9mpxgpnl_.arc
archived log for thread 1 with sequence 177 is already on disk as file /u01/app/oracle/fast_recovery_area/CONDB1/archivelog/2014_04_02/o1_mf_1_177_9mpy4lj5_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/CONDB1/archivelog/2014_04_02/o1_mf_1_175_9mpqqpvv_.arc thread=1 sequence=175
media recovery complete, elapsed time: 00:00:01
Finished recover at 02-APR-14


RMAN> alter database open;

Statement processed

RMAN>


RMAN> alter pluggable database all open read write;

Statement processed

Point-in-time Recovery of Pluggable Database

Note – an auxiliary instance is created to perform the point in time recovery of the pluggable database.

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    8388302


SQL> conn sh/sh@pdb1
Connected.

SQL> truncate table sales;

Table truncated.


$ rman target /

Recovery Manager: Release 12.1.0.1.0 - Production on Wed Apr 2 11:31:37 2014

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

connected to target database: CONDB1 (DBID=3738773602)

RMAN> run {
2> set until scn  8388302;
3> restore pluggable database pdb1;
4> recover pluggable database pdb1;
5> alter pluggable database pdb1 open resetlogs;
6> }
executing command: SET until clause

Starting restore at 02-APR-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=29 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 00008 to /u01/app/oracle/oradata/condb1/pdb1/system01.dbf
channel ORA_DISK_1: restoring datafile 00009 to /u01/app/oracle/oradata/condb1/pdb1/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00010 to /u01/app/oracle/oradata/condb1/pdb1/SAMPLE_SCHEMA_users01.dbf
channel ORA_DISK_1: restoring datafile 00011 to /u01/app/oracle/oradata/condb1/pdb1/example01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/CONDB1/EA795F28CCF12888E0438D15060AAF42/backupset/2014_04_02/o1_mf_nnndf_TAG20140402T081602_9mpoqhvh_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/CONDB1/EA795F28CCF12888E0438D15060AAF42/backupset/2014_04_02/o1_mf_nnndf_TAG20140402T081602_9mpoqhvh_.bkp tag=TAG20140402T081602
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 02-APR-14

Starting recover at 02-APR-14
current log archived
using channel ORA_DISK_1
RMAN-05026: WARNING: presuming following set of tablespaces applies to specified Point-in-Time

List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace UNDOTBS1

Creating automatic instance, with SID='zpDF'

initialization parameters used for automatic instance:
db_name=CONDB1
db_unique_name=zpDF_pitr_pdb1_CONDB1
compatible=12.1.0.0.0
db_block_size=8192
db_files=200
sga_target=1G
processes=80
diagnostic_dest=/u01/app/oracle
#No auxiliary destination in use
enable_pluggable_database=true
_clone_one_pdb_recovery=true
control_files=/u01/app/oracle/fast_recovery_area/CONDB1/controlfile/o1_mf_9mq17sor_.ctl
#No auxiliary parameter file used


starting up automatic instance CONDB1

Oracle instance started

Total System Global Area    1068937216 bytes

Fixed Size                     2296576 bytes
Variable Size                281019648 bytes
Database Buffers             780140544 bytes
Redo Buffers                   5480448 bytes
Automatic instance created

contents of Memory Script:
{
# set requested point in time
set until  scn 8388302;
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
}
executing Memory Script

executing command: SET until clause

Starting restore at 02-APR-14
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=75 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/CONDB1/autobackup/2014_04_02/o1_mf_s_843822011_9mpz9w6k_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/CONDB1/autobackup/2014_04_02/o1_mf_s_843822011_9mpz9w6k_.bkp tag=TAG20140402T110011
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/fast_recovery_area/CONDB1/controlfile/o1_mf_9mq17sor_.ctl
Finished restore at 02-APR-14

sql statement: alter database mount clone database

contents of Memory Script:
{
# set requested point in time
set until  scn 8388302;
# switch to valid datafilecopies
switch clone datafile  8 to datafilecopy
 "/u01/app/oracle/oradata/condb1/pdb1/system01.dbf";
switch clone datafile  9 to datafilecopy
 "/u01/app/oracle/oradata/condb1/pdb1/sysaux01.dbf";
switch clone datafile  10 to datafilecopy
 "/u01/app/oracle/oradata/condb1/pdb1/SAMPLE_SCHEMA_users01.dbf";
switch clone datafile  11 to datafilecopy
 "/u01/app/oracle/oradata/condb1/pdb1/example01.dbf";
# set destinations for recovery set and auxiliary set datafiles
set newname for datafile  1 to
 "/u01/app/oracle/fast_recovery_area/CONDB1/datafile/o1_mf_system_9mq181xx_.dbf";
set newname for datafile  4 to
 "/u01/app/oracle/fast_recovery_area/CONDB1/datafile/o1_mf_undotbs1_9mq181yx_.dbf";
set newname for datafile  3 to
 "/u01/app/oracle/fast_recovery_area/CONDB1/datafile/o1_mf_sysaux_9mq182cw_.dbf";
set newname for datafile  6 to
 "/u01/app/oracle/fast_recovery_area/CONDB1/datafile/o1_mf_users_9mq188rk_.dbf";
set newname for datafile  16 to
 "/u01/app/oracle/fast_recovery_area/CONDB1/datafile/o1_mf_ggs_data_9mq188rq_.dbf";
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile  1, 4, 3, 6, 16;
switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

datafile 8 switched to datafile copy
input datafile copy RECID=7 STAMP=843824010 file name=/u01/app/oracle/oradata/condb1/pdb1/system01.dbf

datafile 9 switched to datafile copy
input datafile copy RECID=8 STAMP=843824010 file name=/u01/app/oracle/oradata/condb1/pdb1/sysaux01.dbf

datafile 10 switched to datafile copy
input datafile copy RECID=9 STAMP=843824010 file name=/u01/app/oracle/oradata/condb1/pdb1/SAMPLE_SCHEMA_users01.dbf

datafile 11 switched to datafile copy
input datafile copy RECID=10 STAMP=843824010 file name=/u01/app/oracle/oradata/condb1/pdb1/example01.dbf

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 02-APR-14
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oracle/fast_recovery_area/CONDB1/datafile/o1_mf_system_9mq181xx_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/fast_recovery_area/CONDB1/datafile/o1_mf_undotbs1_9mq181yx_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/fast_recovery_area/CONDB1/datafile/o1_mf_sysaux_9mq182cw_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00006 to /u01/app/oracle/fast_recovery_area/CONDB1/datafile/o1_mf_users_9mq188rk_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00016 to /u01/app/oracle/fast_recovery_area/CONDB1/datafile/o1_mf_ggs_data_9mq188rq_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/CONDB1/backupset/2014_04_02/o1_mf_nnndf_TAG20140402T081602_9mpop2my_.bkp

channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 02-APR-14

datafile 1 switched to datafile copy
input datafile copy RECID=16 STAMP=843824065 file name=/u01/app/oracle/fast_recovery_area/CONDB1/datafile/o1_mf_system_9mq181xx_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=17 STAMP=843824065 file name=/u01/app/oracle/fast_recovery_area/CONDB1/datafile/o1_mf_undotbs1_9mq181yx_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=18 STAMP=843824065 file name=/u01/app/oracle/fast_recovery_area/CONDB1/datafile/o1_mf_sysaux_9mq182cw_.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=19 STAMP=843824065 file name=/u01/app/oracle/fast_recovery_area/CONDB1/datafile/o1_mf_users_9mq188rk_.dbf
datafile 16 switched to datafile copy
input datafile copy RECID=20 STAMP=843824065 file name=/u01/app/oracle/fast_recovery_area/CONDB1/datafile/o1_mf_ggs_data_9mq188rq_.dbf

contents of Memory Script:
{
# set requested point in time
set until  scn 8388302;
# online the datafiles restored or switched
sql clone "alter database datafile  1 online";
sql clone "alter database datafile  4 online";
sql clone "alter database datafile  3 online";
sql clone 'PDB1' "alter database datafile
 8 online";
sql clone 'PDB1' "alter database datafile
 9 online";
sql clone 'PDB1' "alter database datafile
 10 online";
sql clone 'PDB1' "alter database datafile
 11 online";
sql clone "alter database datafile  6 online";
sql clone "alter database datafile  16 online";
# recover pdb
recover clone database tablespace  "SYSTEM", "UNDOTBS1", "SYSAUX", "USERS", "GGS_DATA" pluggable database
 'PDB1'   delete archivelog;
sql clone 'alter database open read only';
plsql <<>>;
plsql <<>>;
# shutdown clone before import
shutdown clone abort
plsql <<  'PDB1');
end; >>>;
}
executing Memory Script

executing command: SET until clause

sql statement: alter database datafile  1 online

sql statement: alter database datafile  4 online

sql statement: alter database datafile  3 online

sql statement: alter database datafile  8 online

sql statement: alter database datafile  9 online

sql statement: alter database datafile  10 online

sql statement: alter database datafile  11 online

sql statement: alter database datafile  6 online

sql statement: alter database datafile  16 online

Starting recover at 02-APR-14
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 175 is already on disk as file /u01/app/oracle/fast_recovery_area/CONDB1/archivelog/2014_04_02/o1_mf_1_175_9mpqqpvv_.arc
archived log for thread 1 with sequence 176 is already on disk as file /u01/app/oracle/fast_recovery_area/CONDB1/archivelog/2014_04_02/o1_mf_1_176_9mpxgpnl_.arc
archived log for thread 1 with sequence 177 is already on disk as file /u01/app/oracle/fast_recovery_area/CONDB1/archivelog/2014_04_02/o1_mf_1_177_9mpy4lj5_.arc
archived log for thread 1 with sequence 178 is already on disk as file /u01/app/oracle/fast_recovery_area/CONDB1/archivelog/2014_04_02/o1_mf_1_178_9mpyf1gy_.arc
archived log for thread 1 with sequence 179 is already on disk as file /u01/app/oracle/fast_recovery_area/CONDB1/archivelog/2014_04_02/o1_mf_1_179_9mpys08g_.arc
archived log for thread 1 with sequence 180 is already on disk as file /u01/app/oracle/fast_recovery_area/CONDB1/archivelog/2014_04_02/o1_mf_1_180_9mpzn0jl_.arc
archived log for thread 1 with sequence 181 is already on disk as file /u01/app/oracle/fast_recovery_area/CONDB1/archivelog/2014_04_02/o1_mf_1_181_9mq17sc3_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/CONDB1/archivelog/2014_04_02/o1_mf_1_175_9mpqqpvv_.arc thread=1 sequence=175
archived log file name=/u01/app/oracle/fast_recovery_area/CONDB1/archivelog/2014_04_02/o1_mf_1_176_9mpxgpnl_.arc thread=1 sequence=176
archived log file name=/u01/app/oracle/fast_recovery_area/CONDB1/archivelog/2014_04_02/o1_mf_1_177_9mpy4lj5_.arc thread=1 sequence=177
archived log file name=/u01/app/oracle/fast_recovery_area/CONDB1/archivelog/2014_04_02/o1_mf_1_178_9mpyf1gy_.arc thread=1 sequence=178
archived log file name=/u01/app/oracle/fast_recovery_area/CONDB1/archivelog/2014_04_02/o1_mf_1_179_9mpys08g_.arc thread=1 sequence=179
archived log file name=/u01/app/oracle/fast_recovery_area/CONDB1/archivelog/2014_04_02/o1_mf_1_180_9mpzn0jl_.arc thread=1 sequence=180
archived log file name=/u01/app/oracle/fast_recovery_area/CONDB1/archivelog/2014_04_02/o1_mf_1_181_9mq17sc3_.arc thread=1 sequence=181
media recovery complete, elapsed time: 00:00:05
Finished recover at 02-APR-14

sql statement: alter database open read only



Oracle instance shut down


Removing automatic instance
Automatic instance removed
auxiliary instance file /u01/app/oracle/fast_recovery_area/CONDB1/datafile/o1_mf_sysaux_9mq182cw_.dbf deleted
auxiliary instance file /u01/app/oracle/fast_recovery_area/CONDB1/controlfile/o1_mf_9mq17sor_.ctl deleted
Finished recover at 02-APR-14

Statement processed

RMAN>

SQL> conn sh/sh@pdb1
Connected.

SQL> select count(*) from sales;

  COUNT(*)
----------
    918843


SQL> select con_id,  DB_INCARNATION#, PDB_INCARNATION# , INCARNATION_TIME from v$pdb_incarnation order by con_id;

    CON_ID DB_INCARNATION# PDB_INCARNATION# INCARNATI
---------- --------------- ---------------- ---------
         3               2                0 06-NOV-13
         3               2                1 02-APR-14

SQL> conn / as sysdba
Connected.

SQL> /

    CON_ID DB_INCARNATION# PDB_INCARNATION# INCARNATI
---------- --------------- ---------------- ---------
         1               2                0 06-NOV-13
         1               1                0 24-MAY-13
         2               2                0 06-NOV-13
         2               1                0 24-MAY-13
         3               2                1 02-APR-14
         3               2                0 06-NOV-13
         4               2                0 06-NOV-13
         4               1                0 24-MAY-13

8 rows selected.

Note – FLASHBACK DATABASE has to be enabled at the Container database level – cannot be performed at the pluggable database level.

SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-03001: unimplemented feature


SQL> conn / as sysdba
Connected.
SQL> alter database flashback on;

Database altered.

SQL>  select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

Flashback database also cannot be performed at the pluggable database level.

SQL> flashback database pdb3 to scn  26541795;
flashback database pdb3 to scn  26541795
*
ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable database

Please follow and like us:

Last Update: June 22, 2020  

March 24, 2015 199 Gavin Soorma
Total 0 Votes:
0

Tell us how can we improve this post?

+ = Verify Human or Spambot ?

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?

, , , , , , ,

One thought on “12c Multitenancy Backup and Recovery

  1. Arul

    Excellent doc.

    Point-in-time Recovery of Pluggable Database – – > At this section you do not need to restore/recover entire pluggable database although it creates auxilary instance. I think you can perform rman table level recovery.

    run
    {
    recover table sh.sales
    until time “to_date(’09/01/2015 04:50:38′,’mm/dd/yyyy hh24:mi:ss’)”
    auxiliary destination ‘/oracle/RECOVER/A12C/table_recovery’;
    }

Leave a Comment

Your email address will not be published. Required fields are marked *

*
*

Social media & sharing icons powered by UltimatelySocial
Back To Top

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?