MAKE A SNAPSHOT CLONE OF PDB
If a snapshot of PDB is required for the testing purpose, then it become very easy process.
COW technology is used for snapshot clone feature from storage layer. Oracle leverage this feature to create the sparse files from original datafiles of PDB. These sparse files
Sparse files should be supported by OS. Mostly as per oracle, linux systems should support this feature or dNFS or ACFS or any other filesystems.
kIsH@STAX<>show parameter clone
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
clonedb boolean FALSE
clonedb_dir string
kIsH@STAX<>alter system set clonedb=true scope=spfile;
System altered.
kIsH@STAX<>alter system set clonedb_dir='/apps01/oradata' scope=spfile;
System altered.
kIsH@STAX<>startup force
ORACLE instance started.
Total System Global Area 1962932432 bytes
Fixed Size 9136336 bytes
Variable Size 1358954496 bytes
Database Buffers 469762048 bytes
Redo Buffers 7639040 bytes
In-Memory Area 117440512 bytes
Database mounted.
Database opened.
kIsH@STAX<>show parameter clone
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
clonedb boolean TRUE
clonedb_dir stringCreate a source PDB in the CDB
kIsH@STAX<>create pluggable database PROX1 admin user pdbadmin identified by password;
Pluggable database created.
kIsH@STAX<>alter pluggable database PROX1 open;
Pluggable database altered.
kIsH@STAX<>show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
4 PROX1 READ WRITE NOTried to create pluggable database in read write but since the snapshot PDB can only be created in READ ONLY mode, hence restart the PDB. Then create snapshot clone of the original PDB.
Note the cloned files uses a bitmap file which cannot be dropped after snapshot PDB drop. These need to deleted manually.
kIsH@STAX<>create pluggable database PROX2 from PROX1 snapshot copy;
create pluggable database PROX2 from PROX1 snapshot copy
*
ERROR at line 1:
ORA-65081: database or pluggable database is not open in read only mode
kIsH@STAX<>alter pluggable database PROX1 close;
Pluggable database altered.
kIsH@STAX<>alter pluggable database PROX1 open read only;
Pluggable database altered.
kIsH@STAX<>create pluggable database PROX2 from PROX1 snapshot copy;
Pluggable database created.
[oracle@staxhost ~]$ ls -lrt /apps01/oradata/*bitmap*
-rw-r-----. 1 oracle oinstall 2105344 Oct 20 16:48 /apps01/oradata/STAXDB_3951326993_bitmap.dbf
-rw-r-----. 1 oracle oinstall 2105344 Oct 20 17:02 /apps01/oradata/STAXDB_0259964111_bitmap.dbf
-rw-r-----. 1 oracle oinstall 2105344 Oct 20 17:03 /apps01/oradata/STAXDB_0900924109_bitmap.dbfCheck the PDB’s state and open it.
kIsH@STAX<>show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
4 PROX1 READ ONLY NO
6 PROX2 MOUNTED
kIsH@STAX<>alter pluggable database PROX2 open;
Pluggable database altered.
If the original PDB is tried to be opened in read write mode, then IO error is thrown because of snapshot PDB.
kIsH@STAX<>alter pluggable database PROX1 close;
Pluggable database altered.
kIsH@STAX<>alter pluggable database PROX1 open;
alter pluggable database PROX1 open
*
ERROR at line 1:
ORA-01114: IO error writing block to file 21 (block # 1)
ORA-01110: data file 21:
'/u01/app/oracle/oradata/STAXDB/STAXDB/E9E87BBFFBE82E19E053C738A8C06078/datafile
/o1_mf_undotbs1_kmg6h19y_.dbf'
ORA-27091: unable to queue I/O
ORA-27041: unable to open file
Linux-x86_64 Error: 13: Permission denied
Additional information: 3
kIsH@STAX<>alter pluggable database PROX1 open read only;
Pluggable database altered.If the original PDB is dropped, then the snapshot PDB still remain
kIsH@STAX<>alter pluggable database PROX2 open;
Pluggable database altered.
kIsH@STAX<>alter pluggable database PROX1 close;
Pluggable database altered.
kIsH@STAX<>drop pluggable database PROX1 including datafiles;
Pluggable database dropped.If the snapshot PDB is closed and opened, the source files cannot be accessed and ORA-01157-ORA-01110 error is triggered.
kIsH@STAX<>alter pluggable database PROX2 close;
Pluggable database altered.
kIsH@STAX<>alter pluggable database PROX2 open;
alter pluggable database PROX2 open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 45 - see DBWR trace file
ORA-01110: data file 45:
'/u01/app/oracle/oradata/STAXDB/STAXDB/EB767DAECCF93496E053C738A8C03E21/datafile
/o1_mf_undotbs1_ko2dhp7b_.dbf'
kIsH@STAX<>drop pluggable database PROX2 including datafiles;
Pluggable database dropped.Alert log message
alter pluggable database PROX2 open
2022-10-20T16:48:53.366344+05:30
PROX2(6):Pluggable database PROX2 opening in read write
PROX2(6):dNFS CloneDB bitmap file is invalidated
PROX2(6):CloneDB: open bitmap file /apps01/oradata/STAXDB_3951326993_bitmap.dbf
PROX2(6):SUPLOG: Initialize PDB SUPLOG SGA, old value 0x0, new value 0x18
PROX2(6):Autotune of undo retention is turned on.
PROX2(6):Endian type of dictionary set to little
PROX2(6):Undo initialization recovery: Parallel FPTR complete: start:9550295 end:9550298 diff:3 ms (0.0 seconds)
PROX2(6):Undo initialization recovery: err:0 start: 9550295 end: 9550299 diff: 4 ms (0.0 seconds)
PROX2(6):[13462] Successfully onlined Undo Tablespace 2.
PROX2(6):Undo initialization online undo segments: err:0 start: 9550299 end: 9550354 diff: 55 ms (0.1 seconds)
PROX2(6):Undo initialization finished serial:0 start:9550295 end:9550357 diff:62 ms (0.1 seconds)
PROX2(6):Deleting old file#19 from file$
PROX2(6):Deleting old file#20 from file$
PROX2(6):Deleting old file#21 from file$
PROX2(6):Adding new file#31 to file$(old file#19). fopr-3, newblks-48640, oldblks-19200
PROX2(6):Adding new file#32 to file$(old file#20). fopr-3, newblks-51200, oldblks-15360
PROX2(6):Adding new file#33 to file$(old file#21). fopr-3, newblks-19200, oldblks-12800
PROX2(6):Successfully created internal service PROX2 at openCheck all the cloned file available in the CDB
kIsH@STAX<>col SNAPSHOTFILENAME for a20
kIsH@STAX<>col CLONEFILENAME for a20
kIsH@STAX<>select SNAPSHOTFILENAME,CLONEFILENAME from v$clonedfile;
SNAPSHOTFILENAME CLONEFILENAME
-------------------- --------------------
/u01/app/oracle/orad /u01/app/oracle/orad
ata/STAXDB/STAXDB/EB ata/STAXDB/STAXDB/EB
76DC7A4E5E3A46E053C7 76DC7A4E633A46E053C7
38A8C0F02C/datafile/ 38A8C0F02C/datafile/
o1_mf_system_ko2f6pc o1_mf_system_ko2ffvp
7_.dbf n_.dbf
/u01/app/oracle/orad /u01/app/oracle/orad
ata/STAXDB/STAXDB/EB ata/STAXDB/STAXDB/EB
76DC7A4E5E3A46E053C7 76DC7A4E633A46E053C7
38A8C0F02C/datafile/ 38A8C0F02C/datafile/
SNAPSHOTFILENAME CLONEFILENAME
-------------------- --------------------
o1_mf_sysaux_ko2f6pc o1_mf_sysaux_ko2ffvp
b_.dbf n_.dbf
/u01/app/oracle/orad /u01/app/oracle/orad
ata/STAXDB/STAXDB/EB ata/STAXDB/STAXDB/EB
76DC7A4E5E3A46E053C7 76DC7A4E633A46E053C7
38A8C0F02C/datafile/ 38A8C0F02C/datafile/
o1_mf_undotbs1_ko2f6 o1_mf_undotbs1_ko2ff
pcb_.dbf vpo_.dbfVerify all the datafiles under original and snapshot PDB’s
kIsH@STAX<>set lines 200 pages 1000
col PDB_NAME for a20
col DATAFILE_NAME for a60
col OPEN_MODE for a20
select pdb.NAME as PDB_NAME,pdb.OPEN_MODE,
df.NAME as DATAFILE_NAME,df.bytes/1048576 MB
from v$pdbs pdb
inner join v$datafile df on ( pdb.GUID = REGEXP_SUBSTR('EB76DC7A4E5E3A46E053C738A8C0F02C','(.*?)( My Delimiter |$)', 1, 1, NULL, 1 ))
where df.CON_ID in (3,5)
order by PDB_NAME desc;kIsH@STAX<>kIsH@STAX<>kIsH@STAX<>kIsH@STAX<> 2 3 4 5 6
PDB_NAME OPEN_MODE DATAFILE_NAME MB
-------------------- -------------------- ------------------------------------------------------------ ----------
PROX1 READ ONLY /u01/app/oracle/oradata/STAXDB/STAXDB/EB76DC7A4E5E3A46E053C7 380
38A8C0F02C/datafile/o1_mf_system_ko2f6pc7_.dbf
PROX1 READ ONLY /u01/app/oracle/oradata/STAXDB/STAXDB/EB76DC7A4E5E3A46E053C7 440
38A8C0F02C/datafile/o1_mf_sysaux_ko2f6pcb_.dbf
PROX1 READ ONLY /u01/app/oracle/oradata/STAXDB/STAXDB/EB77766474A143BFE053C7 230
38A8C09422/datafile/o1_mf_undotbs1_ko2hrkn6_.dbf
PROX1 READ ONLY /u01/app/oracle/oradata/STAXDB/STAXDB/EB77766474A143BFE053C7 380
38A8C09422/datafile/o1_mf_system_ko2hrkn5_.dbf
PROX1 READ ONLY /u01/app/oracle/oradata/STAXDB/STAXDB/EB77766474A143BFE053C7 460
38A8C09422/datafile/o1_mf_sysaux_ko2hrkn6_.dbf
PROX1 READ ONLY /u01/app/oracle/oradata/STAXDB/STAXDB/EB76DC7A4E5E3A46E053C7 230
38A8C0F02C/datafile/o1_mf_undotbs1_ko2f6pcb_.dbf
Look at the size of files for snapshot and original PDB
[oracle@staxhost ~]$ du -sh /u01/app/oracle/oradata/STAXDB/STAXDB/EB77766474A143BFE053C738A8C09422/datafile/o1_mf_system_ko2hrkn5_.dbf
2.1M<===== Snapshot PDB /u01/app/oracle/oradata/STAXDB/STAXDB/EB77766474A143BFE053C738A8C09422/datafile/o1_mf_system_ko2hrkn5_.dbf
[oracle@staxhost ~]$ du -sh /u01/app/oracle/oradata/STAXDB/STAXDB/EB76DC7A4E5E3A46E053C738A8C0F02C/datafile/o1_mf_system_ko2f6pc7_.dbf
381M<=====Original PDB
/u01/app/oracle/oradata/STAXDB/STAXDB/EB76DC7A4E5E3A46E053C738A8C0F02C/datafile/o1_mf_system_ko2f6pc7_.dbf