How to: point in time restore of pluggable database with multiples
Author: Megan Elphingstone | 15 min read | February 8, 2017
This post will show how to do a Point in Time Restore of one pluggable database in a container with multiple pluggable databases.
In this scenario we are fixing the following mistake: You dropped a table in your pluggable databases PDB1 and PDB2. But oops! You only meant to drop the table in PDB1. So now you need to do a point in time restore on PDB2 to right before you dropped the table.
Requirements: In order do to a point in time restore, you need to have a backup. You also need to have an auxiliary dest configured in RMAN.
Process
1. Login and show the list of pluggable databases
2. Create a table in three different pluggable databases
3. Backup database
4. Drop the table in PDB1 and PDB2
5. Restore pluggable database PDB2 to point in time before the tables are dropped
6. Show the table has been restored on PDB2, but is still missing from PDB1, which is is the goal of this test scenario.
7. Conclusion
1. Login and show the list of pluggable databases
Set your environment on your server – I use a shell script.
$ . cdb2 [CDB2] oracle@localhost:~ Login and confirm the name of the container database: Sqlplus / as sysba Show con_name SQL> show con_name CON_NAME ------------------------------ CDB$ROOT
List names of all all containers:
s
elect name, con_id from v$active_services order by 1; SQL> select name, con_id from v$active_services order by 1; NAME CON_ID ------------------------------ ---------- CDB2 1 CDB2XDB 1 SYS$BACKGROUND 1 SYS$USERS 1 pdb1 3 pdb2 4 pdb3 5 7 rows selected.
2. Create a table in three different pluggable databases
Sqlplus / as sysdba SQL> alter session set container=PDB1 2 / SQL> create table test_drop (col1 number); Table created. SQL> insert into test_drop values(1); 1 row created. SQL> commit 2 / Commit complete. SQL> alter session set container=PDB2 2 / Session altered. SQL> create table test_drop (col1 number); Table created. SQL> insert into test_drop values(1); 1 row created. SQL> commit 2 / Commit complete. SQL> alter session set container=PDB3 2 / Session altered. SQL> create table test_drop (col1 number); Table created. SQL> insert into test_drop values(1); 1 row created. SQL> commit 2 / Commit complete. SQL>
Switch a few log files – this must be done from the root container
SQL> alter system switch logfile 2 / System altered. SQL> alter system switch logfile 2 / System altered.
Check that the tables exist in each pluggable database:
SELECT a.CON_ID, b.con_name, SUBSTR(a.TABLE_NAME,1,10) table_name FROM CDB_TABLES a, v$active_services b WHERE a.TABLE_NAME='TEST_DROP' and a.con_id=b.con_id order by 1 / CON_ID CON_NAME TABLE_NAME ---------- ------------------------------ ------------------------------ 3 PDB1 TEST_DROP 4 PDB2 TEST_DROP 5 PDB3 TEST_DROP
3. Create an RMAN backup
RMAN> backup database plus archivelog; Starting backup at 24-JAN-17 current log archived 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 archived log backup set channel ORA_DISK_1: specifying archived log(s) in backup set input archived log thread=1 sequence=425 RECID=170 STAMP=934147565 input archived log thread=1 sequence=426 RECID=171 STAMP=934147565 input archived log thread=1 sequence=427 RECID=172 STAMP=934148994 input archived log thread=1 sequence=428 RECID=173 STAMP=934149711 channel ORA_DISK_1: starting piece 1 at 24-JAN-17 channel ORA_DISK_1: finished piece 1 at 24-JAN-17 piece handle=/oradata/CDB2/arch/backup_55rqrvif_1_1 tag=TAG20170124T220151 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 24-JAN-17
Starting backup at 24-JAN-17 using channel ORA_DISK_1 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00005 name=/oradata/CDB2/undotbs01.dbf input datafile file number=00003 name=/oradata/CDB2/sysaux01.dbf input datafile file number=00001 name=/oradata/CDB2/system01.dbf input datafile file number=00006 name=/oradata/CDB2/users01.dbf channel ORA_DISK_1: starting piece 1 at 24-JAN-17 channel ORA_DISK_1: finished piece 1 at 24-JAN-17 piece handle=/oradata/CDB2/arch/backup_56rqrvih_1_1 tag=TAG20170124T220153 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=00012 name=/oradata/CDB2/pdb2/sysaux01.dbf input datafile file number=00011 name=/oradata/CDB2/pdb2/system01.dbf input datafile file number=00013 name=/oradata/CDB2/pdb2/users01.dbf channel ORA_DISK_1: starting piece 1 at 24-JAN-17 channel ORA_DISK_1: finished piece 1 at 24-JAN-17 piece handle=/oradata/CDB2/arch/backup_57rqrvju_1_1 tag=TAG20170124T220153 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=00022 name=/oradata/CDB2/CDB2/46C8426CF7FB65ABE0530100007FF500/datafile/o1_mf_sysaux_d8dm6ytx_.dbf input datafile file number=00021 name=/oradata/CDB2/CDB2/46C8426CF7FB65ABE0530100007FF500/datafile/o1_mf_system_d8dm6ytk_.dbf input datafile file number=00023 name=/oradata/CDB2/CDB2/46C8426CF7FB65ABE0530100007FF500/datafile/o1_mf_users_d8dm6ytz_.dbf channel ORA_DISK_1: starting piece 1 at 24-JAN-17 channel ORA_DISK_1: finished piece 1 at 24-JAN-17 piece handle=/oradata/CDB2/arch/backup_58rqrvkn_1_1 tag=TAG20170124T220153 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=00004 name=/oradata/CDB2/pdbseed/sysaux01.dbf input datafile file number=00002 name=/oradata/CDB2/pdbseed/system01.dbf channel ORA_DISK_1: starting piece 1 at 24-JAN-17 channel ORA_DISK_1: finished piece 1 at 24-JAN-17 piece handle=/oradata/CDB2/arch/backup_59rqrvlh_1_1 tag=TAG20170124T220153 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=00014 name=/oradata/CDB2/pdb3/system01.dbf input datafile file number=00015 name=/oradata/CDB2/pdb3/sysaux01.dbf input datafile file number=00020 name=/oradata/CDB2/pdb3/users01.dbf channel ORA_DISK_1: starting piece 1 at 24-JAN-17 channel ORA_DISK_1: finished piece 1 at 24-JAN-17 piece handle=/oradata/CDB2/arch/backup_5arqrvma_1_1 tag=TAG20170124T220153 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15 Finished backup at 24-JAN-17
Starting backup at 24-JAN-17 current log archived using channel ORA_DISK_1 channel ORA_DISK_1: starting archived log backup set channel ORA_DISK_1: specifying archived log(s) in backup set input archived log thread=1 sequence=429 RECID=174 STAMP=934149849 channel ORA_DISK_1: starting piece 1 at 24-JAN-17 channel ORA_DISK_1: finished piece 1 at 24-JAN-17 piece handle=/oradata/CDB2/arch/backup_5brqrvmp_1_1 tag=TAG20170124T220409 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 24-JAN-17
Starting Control File and SPFILE Autobackup at 24-JAN-17 piece handle=/u01/app/oracle/product/12.1.0.2/dbs/c-600824249-20170124-06 comment=NONE Finished Control File and SPFILE Autobackup at 24-JAN-17
4. Drop the table in PDB1 and PDB2
Check the time
SYSDATE ------------------- 24-01-2017 22:06:32 [CDB2] oracle@localhost:~ $ sqlplus / as sysdba SQL> alter session set container=PDB1 2 / Session altered. SQL> drop table test_drop 2 / Table dropped. SQL> alter session set container=PDB2 2 / Session altered. SQL> drop table test_drop 2 / Table dropped. SQL> alter session set container=cdb$root; Session altered.
Look for your tables. Oops you only meant to drop one of them, and now it is missing from two pluggable databases!
SELECT
a.CON_ID,
b.con_name,
SUBSTR(a.TABLE_NAME,1,10) table_name
FROM
CDB_TABLES a,
v$active_services b
WHERE
a.TABLE_NAME='TEST_DROP'
and a.con_id=b.con_id
order by 1
/
CON_ID CON_NAME TABLE_NAME
---------- --------- ------------------------------
5 PDB3 TEST_DROP
1
2 5. Restore pluggable database PDB2 to point in time before the tables are dropped
3
4 Close the pluggable database that needs to be restored.
5
RMAN> ALTER PLUGGABLE DATABASE PDB2 CLOSE;
RMAN> run
{
SET UNTIL TIME "TO_DATE('24-JAN-2017 22:06:00','DD-MON-YYYY HH24:MI:SS')";
RESTORE pluggable database pdb2;
RECOVER pluggable database pdb2 auxiliary destination='/oradata/CDB2/arch/pitr';
ALTER PLUGGABLE DATABASE pdb2 OPEN RESETLOGS;
}
executing command: SET until clause
Starting restore at 24-01-2017 10:41:54
using channel ORA_DISK_1
skipping datafile 11; already restored to file /oradata/CDB2/pdb2/system01.dbf
skipping datafile 12; already restored to file /oradata/CDB2/pdb2/sysaux01.dbf
skipping datafile 13; already restored to file /oradata/CDB2/pdb2/users01.dbf
restore not done; all files read only, offline, or already restored
Finished restore at 24-01-2017 10:41:54
Starting recover at 24-01-2017 10:41:54
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='qobn'
initialization parameters used for automatic instance:
db_name=CDB2
db_unique_name=qobn_pitr_pdb2_CDB2
compatible=12.1.0.2.0
db_block_size=8192
db_files=200
diagnostic_dest=/oradata
_system_trig_enabled=FALSE
sga_target=1000M
processes=200
db_create_file_dest=/oradata/CDB2/arch/pitr
log_archive_dest_1='location=/oradata/CDB2/arch/pitr'
enable_pluggable_database=true
_clone_one_pdb_recovery=true
#No auxiliary parameter file used
starting up automatic instance CDB2
Oracle instance started
Total System Global Area 1048576000 bytes
Fixed Size 2932336 bytes
Variable Size 276824464 bytes
Database Buffers 763363328 bytes
Redo Buffers 5455872 bytes
Automatic instance created
contents of Memory Script:
{
# set requested point in time
set until time "TO_DATE('24-JAN-2017 22:06:00','DD-MON-YYYY HH24:MI:SS')";
# 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 24-01-2017 10:42:04
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=12 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/product/12.1.0.2/dbs/c-600824249-20170124-06
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/product/12.1.0.2/dbs/c-600824249-20170124-06 tag=TAG20170124T220411
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/oradata/CDB2/arch/pitr/CDB2/controlfile/o1_mf_d8hp1x3k_.ctl
Finished restore at 24-01-2017 10:42:06
sql statement: alter database mount clone database
contents of Memory Script:
{
# set requested point in time
set until time "TO_DATE('24-JAN-2017 22:06:00','DD-MON-YYYY HH24:MI:SS')";
# switch to valid datafilecopies
switch clone datafile 11 to datafilecopy
"/oradata/CDB2/pdb2/system01.dbf";
switch clone datafile 12 to datafilecopy
"/oradata/CDB2/pdb2/sysaux01.dbf";
switch clone datafile 13 to datafilecopy
"/oradata/CDB2/pdb2/users01.dbf";
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile 1 to new;
set newname for clone datafile 5 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 6 to new;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 1, 5, 3, 6;
switch clone datafile all;
}
executing Memory Script
executing command: SET until clause
datafile 11 switched to datafile copy
input datafile copy RECID=1 STAMP=934152131 file name=/oradata/CDB2/pdb2/system01.dbf
datafile 12 switched to datafile copy
input datafile copy RECID=2 STAMP=934152131 file name=/oradata/CDB2/pdb2/sysaux01.dbf
datafile 13 switched to datafile copy
input datafile copy RECID=3 STAMP=934152131 file name=/oradata/CDB2/pdb2/users01.dbf
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 24-01-2017 10:42:10
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 /oradata/CDB2/arch/pitr/CDB2/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00005 to /oradata/CDB2/arch/pitr/CDB2/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /oradata/CDB2/arch/pitr/CDB2/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00006 to /oradata/CDB2/arch/pitr/CDB2/datafile/o1_mf_users_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /oradata/CDB2/arch/backup_56rqrvih_1_1
channel ORA_AUX_DISK_1: piece handle=/oradata/CDB2/arch/backup_56rqrvih_1_1 tag=TAG20170124T220153
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 24-01-2017 10:43:06
datafile 1 switched to datafile copy
input datafile copy RECID=8 STAMP=934152186 file name=/oradata/CDB2/arch/pitr/CDB2/datafile/o1_mf_system_d8hp238g_.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=9 STAMP=934152186 file name=/oradata/CDB2/arch/pitr/CDB2/datafile/o1_mf_undotbs1_d8hp237x_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=10 STAMP=934152186 file name=/oradata/CDB2/arch/pitr/CDB2/datafile/o1_mf_sysaux_d8hp2388_.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=11 STAMP=934152186 file name=/oradata/CDB2/arch/pitr/CDB2/datafile/o1_mf_users_d8hp23b4_.dbf
contents of Memory Script:
{
# set requested point in time
set until time "TO_DATE('24-JAN-2017 22:06:00','DD-MON-YYYY HH24:MI:SS')";
# online the datafiles restored or switched
sql clone "alter database datafile 1 online";
sql clone "alter database datafile 5 online";
sql clone "alter database datafile 3 online";
sql clone 'PDB2' "alter database datafile
11 online";
sql clone 'PDB2' "alter database datafile
12 online";
sql clone 'PDB2' "alter database datafile
13 online";
sql clone "alter database datafile 6 online";
# recover pdb
recover clone database tablespace "SYSTEM", "UNDOTBS1", "SYSAUX", "USERS" pluggable database
'PDB2' delete archivelog;
sql clone 'alter database open read only';
plsql <<<begin
add_dropped_ts;
end; >>>;
plsql <<<begin
save_pdb_clean_scn;
end; >>>;
# shutdown clone before import
shutdown clone abort
plsql <<<begin
pdbpitr_inspect(pdbname => 'PDB2');
end; >>>;
}
executing Memory Script
executing command: SET until clause
sql statement: alter database datafile 1 online
sql statement: alter database datafile 5 online
sql statement: alter database datafile 3 online
sql statement: alter database datafile 11 online
sql statement: alter database datafile 12 online
sql statement: alter database datafile 13 online
sql statement: alter database datafile 6 online
Starting recover at 24-01-2017 10:43:07
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 429 is already on disk as file /u01/app/oracle/product/12.1.0.2/dbs/arch/1_429_853777209.dbf
archived log for thread 1 with sequence 430 is already on disk as file /u01/app/oracle/product/12.1.0.2/dbs/arch/1_430_853777209.dbf
archived log file name=/u01/app/oracle/product/12.1.0.2/dbs/arch/1_429_853777209.dbf thread=1 sequence=429
archived log file name=/u01/app/oracle/product/12.1.0.2/dbs/arch/1_430_853777209.dbf thread=1 sequence=430
media recovery complete, elapsed time: 00:00:02
Finished recover at 24-01-2017 10:43:10
sql statement: alter database open read only
Oracle instance shut down
Removing automatic instance
Automatic instance removed
auxiliary instance file /oradata/CDB2/arch/pitr/CDB2/datafile/o1_mf_sysaux_d8hp2388_.dbf deleted
auxiliary instance file /oradata/CDB2/arch/pitr/CDB2/controlfile/o1_mf_d8hp1x3k_.ctl deleted
Finished recover at 24-01-2017 10:43:13
Statement processed
RMAN>
6. Show the table has been restored on PDB2, but is still missing from PDB1, which is is the goal of this test scenario.
CON_ID CON_NAME TABLE_NAME ---------- ------------------------------ ------------------------------ 4 PDB2 TEST_DROP 5 PDB3 TEST_DROP
Voila. Restore to a point in time is successful and your table has reappeared.
7. Conclusion
With an RMAN backup you can restore one pluggable database while leaving others as they are. This would be very handy in situations where you have DEV, TEST and QA as pluggable databases on the same server. Each can be restored or refreshed independently. The caveat is that if you are using flashback database, you will not be able to flashback any pluggable to a point in time before your restore.
Make sure you have an auxiliary destination setup in RMAN. This will probably not be an issue in a real life situation, but you may run across it testing this in a lab. The auxiliary destination allows RMAN to create some temporary files (Control file and sysaux table space) that are used for the restore and then deleted.
Go forth and test, and then be ready to use this process when you need it.
Datavail Script: Terms & Conditions
By using this software script (“Script”), you are agreeing to the following terms and condition, as a legally enforceable contract, with Datavail Corporation (“Datavail”). If you do not agree with these terms, do not download or otherwise use the Script. You (which includes any entity whom you represent or for whom you use the Script) and Datavail agree as follows:
1. CONSIDERATION. As you are aware, you did not pay a fee to Datavail for the license to the Script. Consequently, your consideration for use of the Script is your agreement to these terms, including the various waivers, releases and limitations of your rights and Datavail’s liabilities, as setforth herein.
2. LICENSE. Subject to the terms herein, the Script is provided to you as a non-exclusive, revocable license to use internally and not to transfer, sub-license, copy, or create derivative works from the Script, not to use the Script in a service bureau and not to disclose the Script to any third parties. No title or other ownership of the Script (or intellectual property rights therein) is assigned to you.
3. USE AT YOUR OWN RISK; DISCLAIMER OF WARRANTIES. You agree that your use of the Script and any impacts on your software, databases, systems, networks or other property or services are solely and exclusively at your own risk. Datavail does not make any warranties, and hereby expressly disclaims any and all warranties, implied or express, including without limitation, the following: (1) performance of or results from the Script, (2) compatibility with any other software or hardware, (3) non-infringement or violation of third party’s intellectual property or other property rights, (4) fitness for a particular purpose, or (5) merchantability.
4. LIMITATION ON LIABILITY; RELEASE. DATAVAIL SHALL HAVE NO, AND YOU WAIVE ANY, LIABILITY OR DAMAGES UNDER THIS AGREEMENT.
You hereby release Datavail from any claims, causes of action, losses, damages, costs and expenses resulting from your downloading or other use of the Script.
5. AGREEMENT. These terms and conditions constitute your complete and exclusive legal agreement between you and Datavail.
