Point in time recovery of one PDB alone in a oracle 12c container database ( CDB)

 

 

1) A CDB Contains two PDBs.

 

SQL> select name,OPEN_MODE from v$pdbs;

 

NAME                           OPEN_MODE

------------------------------ ----------

PDB$SEED                       READ ONLY

PLUGDB1                        READ WRITE

PLUGDB2                        READ WRITE

 

 

2) Backup the CDB 

CDBJEY@oracle@exadata0103 [UAT ACTIVE]:/app/oracle

$ rman target /

 

Recovery Manager: Release 12.1.0.2.0 - Production on Tue Jan 12 02:29:52 2016

 

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

 

connected to target database: CDBJEY (DBID=642267487)

 

RMAN>  backup database plus archivelog;

Starting backup at 12-01-2016 02:29:57

.........

Finished backup at 12-01-2016 02:30:12

RMAN>

====

 

3) Create a tablespace, user ,  capture the SCN & popuate some data in PDB1

 

SQL> create tablespace test_recover datafile '+FRA' size 1G;

 

Tablespace created.

 

SQL> create user jeya identified by jeya123;

 

User created.

 

SQL> grant create session, create table,unlimited tablespace to jeya;

 

Grant succeeded.

 

SQL> create table jeya.recover_table(name varchar2(10)) tablespace test_recover;

 

Table created.

 

SQL> select timestamp_to_scn(sysdate) from v$database;

 

TIMESTAMP_TO_SCN(SYSDATE)

-------------------------

                  1993433

SQL> begin

  for i in 1.. 10000 loop

     insert into jeya.recover_table values ('hari');

  end loop;

  commit;

 end;

 /  2    3    4    5    6    7

 

PL/SQL procedure successfully completed.

 

SQL> select timestamp_to_scn(sysdate) from v$database;

 

TIMESTAMP_TO_SCN(SYSDATE)

-------------------------

                  1993523

4.Create a tablespace, user ,  capture the SCN & popuate some data in PDB2

SQL> alter session set container=PLUGDB2;

 

Session altered.

 

SQL> create tablespace retain datafile '+FRA' size 1g;

 

Tablespace created.

 

SQL> create user jeya_pdb2 identified by jeya123;

 

User created.

 

SQL> grant create session, create table,unlimited tablespace to jeya_pdb2;

 

Grant succeeded.

 

 

SQL> create table jeya_pdb2.retain_tab( name varchar2(20)) tablespace retain;

 

Table created.

 

SQL> insert into jeya_pdb2.retain_tab values('hello');

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

SQL> select timestamp_to_scn(sysdate) from v$database;

 

TIMESTAMP_TO_SCN(SYSDATE)

-------------------------

                  1995814

 

SQL> begin

  for i in 1.. 10000 loop

     insert into jeya_pdb2.retain_tab values ('test');

  end loop;

  commit;

 end;

 /  2    3    4    5    6    7

 

PL/SQL procedure successfully completed.

 

SQL> select timestamp_to_scn(sysdate) from v$database;

 

TIMESTAMP_TO_SCN(SYSDATE)

-------------------------

                  1995867

 

SQL>

 

 

SQL> select timestamp_to_scn(sysdate) from v$database;

 

TIMESTAMP_TO_SCN(SYSDATE)

-------------------------

                  1995814

 

SQL> begin

  for i in 1.. 10000 loop

     insert into jeya_pdb2.retain_tab values ('test');

  end loop;

  commit;

 end;

 /  2    3    4    5    6    7

 

PL/SQL procedure successfully completed.

 

SQL> select timestamp_to_scn(sysdate) from v$database;

 

TIMESTAMP_TO_SCN(SYSDATE)

-------------------------

                  1995867

 

5. Close the PDB2 to make it ready for PITR recover

 

SQL> alter pluggable database PLUGDB2 close;

 

Pluggable database altered.

 

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Advanced Analytics and Real Application Testing options

6. Recover the PDB2 to SCN 1993433 ( captured in step 3)

 

CDBJEY@oracle@exadata0103 [UAT ACTIVE]:/app/oracle

$ rman target /

 

Recovery Manager: Release 12.1.0.2.0 - Production on Tue Jan 12 03:19:38 2016

 

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

 

connected to target database: CDBJEY (DBID=642267487)

 

RMAN> run {

 set until SCN = 1993433 ;

 restore pluggable database PLUGDB2;

 recover pluggable database PLUGDB2 auxiliary destination='+DATA01';

 alter pluggable database PLUGDB2 open resetlogs;

 }2> 3> 4> 5> 6>

 

executing command: SET until clause

 

Starting restore at 12-01-2016 03:21:46

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

Finished recover at 12-01-2016 03:22:23

Statement processed

 

RMAN>

 

7. Verify if  the PDB2 alone rollback to SCN 1993433 ( captured in step 3)

 

CDBJEY@oracle@exadata0103 [UAT ACTIVE]:/app/oracle

$ sqlplus /nolog

 

SQL*Plus: Release 12.1.0.2.0 Production on Tue Jan 12 03:23:38 2016

 

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

 

SQL> conn / as sysdba

Connected.

SQL> alter session set container=PLUGDB2;

 

Session altered.

 

SQL> select name from v$tablespace;

 

NAME

------------------------------

UNDOTBS1

SYSTEM

SYSAUX

TEMP

USERS

 

SQL> alter session set container=PLUGDB1

  2  ;

 

Session altered.

 

SQL> select name from v$tablespace;

 

NAME

------------------------------

UNDOTBS1

SYSTEM

SYSAUX

TEMP

USERS

TEST_RECOVER

 

6 rows selected.

 

SQL> select count(*) from jeya.recover_table;

 

  COUNT(*)

----------

     10000

 

SQL>