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
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’;
}