Example #1 -- Creating PDB
Snapshot using Full Test Masters
Scenario is to
create a test master full copy of production, developers then create snapshots
from the test master
Show pdbs
/* Create Full test
master PDB - note this would usually be a remote clone*/
create pluggable database prod1tm1 from prod1 keystore
identified by "WELcome__2019";
show pdbs;
/* Need to open Test
Master read write before opening it read only */
alter pluggable
database prod1tm1 open instances=all;
alter pluggable
database prod1tm1 close immediate instances=all;
alter pluggable
database prod1tm1 open read only instances=all;
/* read only test master pdb */
/* Create First PDB snapshot from Test Master */
create pluggable database tm1snap1 from prod1tm1
tempfile reuse create_file_dest='+SPRC1' snapshot copy keystore identified by
"WELcome__2019";
alter pluggable
database tm1snap1 open instances=all;
alter session set
container=tm1snap1;
create table panda
as select * from dba_users;
select * from panda;
/* Create second
snapshot from same test master */
create pluggable database tm1snap2 from prod1tm1
tempfile reuse create_file_dest='+SPRC1' snapshot copy keystore identified by
"WELcome__2019";
alter pluggable
database tm1snap2 open instances=all;
alter session set
container=cdb$root;
Show pdbs
/* query shows
parent of snapshots */
column name format
a20
select CON_ID, NAME,
OPEN_MODE, SNAPSHOT_PARENT_CON_ID from v$pdbs;
alter pluggable
database tm1snap1 close immediate instances=all;
alter pluggable
database tm1snap2 close immediate instances=all;
alter pluggable
database prod1tm1 close immediate instances=all;
drop pluggable
database tm1snap1 including datafiles;
drop pluggable
database tm1snap2 including datafiles;
drop pluggable
database prod1tm1 including datafiles;
Example #2 -- Creating PDB
Snapshots using Sparse Test Masters
Scenario is to
create one test master full copy of production, then create sparse test masters updated each night from
prod using GG, developers then create snapshots from the test masters. Provide a new copy of production each night
for the developers to create snaps from, full test master for Monday , sparse
test masters Tuesday thru Friday.
Note: Using the same
name for the test master each night so that the GG configuration doesn't have
to change
/* create full copy
of test master from prod, usually this would be a remote clone */
create pluggable database prod2tm from prod2 keystore
identified by "WELcome__2019";
alter pluggable
database prod2tm open instances=all;
/* setup GG , catch
up test master with production */
/* stop GG when it is time to create a new test master*/
Alter pluggable
database prod2tm close immediate instances=all;
alter pluggable
database prod2tm unplug into '/home/oracle/snapshot/prod2tm_monday.xml' encrypt
using "zzz";
drop pluggable
database prod2tm keep datafiles;
/* create full copy
of test master*/
create pluggable database prod2tm_monday using
'/home/oracle/snapshot/prod2tm_monday.xml' nocopy keystore identified by
"WELcome__2019" decrypt using "zzz";
/* open full copy of
test master read only, need to open
read/write first then read only */
alter pluggable
database prod2tm_monday open instances=all;
alter pluggable
database prod2tm_monday close immediate instances=all;
alter pluggable
database prod2tm_monday open read only
instances=all;
/* create next days
test master as a sparse */
create pluggable database prod2tm from prod2tm_monday
tempfile reuse create_file_dest='+SPRC1' snapshot copy keystore identified by
"WELcome__2019";
alter pluggable
database prod2tm open instances=all;
/* sync test master
to prod*/
/* Restart GG */
/* developer creates
sparse pdb to use */
create pluggable database prod2tm_monday_greg from
prod2tm_monday tempfile reuse create_file_dest='+SPRC1' snapshot copy keystore
identified by "WELcome__2019";
alter pluggable
database prod2tm_Monday_greg open instances=all;
show pdbs
column name format
a20
select CON_ID, NAME,
OPEN_MODE, SNAPSHOT_PARENT_CON_ID from v$pdbs;
/* repeat process
for each day's sparse test master */
alter pluggable
database prod2tm close immediate instances=all;
alter pluggable
database prod2tm_monday close immediate instances=all;
alter pluggable
database prod2tm_Monday_greg close immediate instances=all;
drop pluggable
database prod2tm including datafiles;
drop pluggable
database prod2tm_monday including datafiles;
drop pluggable
database prod2tm_Monday_greg including datafiles;
rm
/home/oracle/snapshot/*.xml
ASM
sqlplus / as sysasm
ALTER DISKGROUP
DATAC1 SET ATTRIBUTE 'ACCESS_CONTROL.ENABLED' = 'TRUE';
