Clonewars – Next Gen Cloning with Oracle 12.2 Multitenancy (Part Deux)… With a Sprinkle of PDB Refresh

by

 

This is Part 2 of the Remote [PDB] Cloning capabilities of Oracle 12.2 Mulitenant.

Cloning Example 2:  Remote clone copy from an existing CBD/PDB into a local PDB (PDB->PDB).  In this example “darkside” is  CDB with darthmaul being the source/remote PDB and  yoda (PDB) is local target

 

SQL> select database_name from v$database;

DATABASE_NAME
--------------------------------------------------------
DARKSIDE

darkside$SQL> alter pluggable database darthmaul open;
Pluggable database altered.

SQL> select name, open_mode from v$pdbs;
NAME .    OPEN_MODE

--------------------
PDB$SEED  READ ONLY
DARTHMAUL READ WRITE

darkside$SQL> archive log list ;
Database log mode            Archive Mode
Automatic archival           Enabled
Archive destination          USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1
Next log sequence to archive   3
Current log sequence         3

darkside$SQL> select name, open_mode from v$database;
NAME     OPEN_MODE
--------- --------------------
DARKSIDE  READ WRITE

darkside$SQL> COLUMN property_name FORMAT A30
COLUMN property_value FORMAT A30
SELECT property_name, property_value
FROM   database_properties
WHERE  property_name = 'LOCAL_UNDO_ENABLED'; 
PROPERTY_NAME                PROPERTY_VALUE
------------------------------ ------------------------------
LOCAL_UNDO_ENABLED           TRUE


$ cat darkside_create_remote_clone_user.sql
create user c##darksidecloneuser identified by cloneuser123 container=ALL;
grant create session, create pluggable database to c##darksidecloneuser  container=ALL;

$cat darkside_db_link.sql
create database link darksideclone_link
CONNECT TO c##darksidecloneuser IDENTIFIED BY cloneuser123 USING 'darkside'

Nishan$SQL> select DB_LINK,HOST from dba_db_links;
DB_LINK        HOST
------------  ---------------------------
SYS_HUB          SEEDDATA
REMOTECLONELINK  hansolo
DARKSIDECLONE_LINK darkside

darkside$SQL> select name from v$datafile;
NAME
-------------------------------------------------------------------------------

+ACFSDATA/DARKSIDE/4E63D836FC5AF80DE053B214A8C07E55/DATAFILE/system.276.942656929

+ACFSDATA/DARKSIDE/4E63D836FC5AF80DE053B214A8C07E55/DATAFILE/sysaux.277.942656929

+ACFSDATA/DARKSIDE/4E63D836FC5AF80DE053B214A8C07E55/DATAFILE/undotbs1.275.942656929

+ACFSDATA/DARKSIDE/4E63D836FC5AF80DE053B214A8C07E55/DATAFILE/users.279.942657041

+ACFSDATA/DARKSIDE/4E63D836FC5AF80DE053B214A8C07E55/DATAFILE/rey.291.942877803

+ACFSDATA/DARKSIDE/4E63D836FC5AF80DE053B214A8C07E55/DATAFILE/luke.292.942877825

darkside$SQL> show con_name
CON_NAME
-----------------------------
DARTHMAUL


darkside$SQL> create table foofighters tablespace rey as select * from obj$;
Table created.

Nishan$SQL> create pluggable database yoda from darthmaul@DARKSIDECLONE_LINK;

Pluggable database created.

Nishan$SQL> alter session set container = yoda;
Session altered.

yoda$SQL> select name, open_mode from v$pdbs;
NAME                    OPEN_MODE
----------------------------------------
YODA                   MOUNTED

yoda$SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
+DATA/NISHAN/4E82D233272C7273E0538514A8C00DF3/DATAFILE/system.310.942878321
+DATA/NISHAN/4E82D233272C7273E0538514A8C00DF3/DATAFILE/sysaux.311.942878321
+DATA/NISHAN/4E82D233272C7273E0538514A8C00DF3/DATAFILE/undotbs1.309.942878321
+DATA/NISHAN/4E82D233272C7273E0538514A8C00DF3/DATAFILE/users.306.942878319
+DATA/NISHAN/4E82D233272C7273E0538514A8C00DF3/DATAFILE/rey.307.942878319
+DATA/NISHAN/4E82D233272C7273E0538514A8C00DF3/DATAFILE/luke.308.942878319


Now on to Refresh the PDB


SQL>create table foofighters tablespace rey as select * from obj$

Table created.




SQL> select segment_name from dba_segments where tablespace_name = 'REY'

SEGMENT_NAME

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

FOOFIGHTERS




SQL> select name, open_mode from v$pdbs;

NAME            OPEN_MODE

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

PDB$SEED        READ ONLY

OBIWAN          READ WRITE

FORCEAWAKENS    MOUNTED

YODA            MOUNTED




SQL> alter pluggable database yoda open read only;

Pluggable database altered.




SQL> select segment_name from dba_segments where tablespace_name = 'REY';

no rows selected




SQL> alter session set container = yoda;

Session altered.




SQL> ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE;

Pluggable database altered.




SQL> ALTER PLUGGABLE DATABASE refresh;

Pluggable database altered.




SQL> select segment_name from dba_segments where tablespace_name = 'REY';

select segment_name from dba_segments where tablespace_name = 'REY'  

ERROR at line 1:

ORA-01219: database or pluggable database not open: queries allowed on fixed

tables or views only




SQL> ALTER PLUGGABLE DATABASE open read only;

Pluggable database altered.




SQL> select segment_name from dba_segments where tablespace_name = 'REY';

SEGMENT_NAME

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

FOOFIGHTERS