Select Page

How to: point in time restore of pluggable database with multiples

Author: Megan Elphingstone | | 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.

 

 

How to Solve the Oracle Error ORA-12154: TNS:could not resolve the connect identifier specified

The “ORA-12154: TNS Oracle error message is very common for database administrators. Learn how to diagnose & resolve this common issue here today.

Vijay Muthu | February 4, 2021

How to Recover a Table from an Oracle 12c RMAN Backup

Our database experts explain how to recover and restore a table from an Oracle 12c RMAN Backup with this step-by-step blog. Read more.

Megan Elphingstone | February 2, 2017

Data Types: The Importance of Choosing the Correct Data Type

Most DBAs have struggled with the pros and cons of choosing one data type over another. This blog post discusses different situations.

Craig Mullins | October 11, 2017

Subscribe to Our Blog

Never miss a post! Stay up to date with the latest database, application and analytics tips and news. Delivered in a handy bi-weekly update straight to your inbox. You can unsubscribe at any time.

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.

CONTACT US

Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.

EXPLORE JOBS