MAKE A SNAPSHOT CLONE OF PDB

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.

Snapshot clone
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                          string

Create 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 NO

Tried 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.dbf

Check 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 open

Check 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_.dbf

Verify 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

Leave a Reply

%d bloggers like this: