In a previous blog , I talked about refreshable PDB technology which can be used to refresh a target PDB. The target PDB can be in mounted state (mandatory to be refreshed) or can be opened in a read only mode.
One application is to use the target refreshable PDB as a master to provide PDB snapshots.
A PDB snapshot is copy of a PDB at a given time. The source PDB can be open read-only or read/write while the snapshot is created.
The taken snapshot can be used to clone a full standalone PDB or can be materialized.
I am using the same configuration as in my previous blog (VM with XFS FS)
An Oracle 21c source CDB : DB21 with a source PDB PDB1
An Oracle 21c target CDB : TEST21 which will contain the refreshable clone of PDB1
From the refreshable PDB PDB1FRES, we can create for example 2 PDB snapshots
-DEVSNAP for the developers
-TESTSNAP for testing
Let’s do some change in PDB1
SQL> show con_name CON_NAME ------------------------------ PDB1 SQL> SQL> select * from test; ID ---------- 1 10 20 30 40
On TEST21 CDB, let’s do a refresh of PDB1FRES
SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 4 PDB1FRES MOUNTED SQL> alter pluggable database PDB1FRES refresh; Pluggable database altered.
We can verify that new changes on PDB1 are present on PDB1FRES
SQL> alter pluggable database PDB1FRES open ; Pluggable database altered. SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 4 PDB1FRES READ ONLY NO SQL> alter session set container=PDB1FRES; Session altered. SQL> show con_name; CON_NAME ------------------------------ PDB1FRES SQL> select * from test; ID ---------- 1 10 20 30 40 50 6 rows selected.
OK now let’s create the DEVSNAP snapshot from PDB1FRES
SQL> create pluggable database DEVSNAP from pdb1fres snapshot copy; create pluggable database DEVSNAP from pdb1fres snapshot copy * ERROR at line 1: ORA-65169: error encountered while attempting to copy file /u01/app/oracle/oradata/TEST21/D8843948FA4E1659E0531502A8C00AD6/datafile/o1_mf_u ndotbs1_k16n5zj0_.dbf ORA-17525: Database clone using storage snapshot not supported on file /u01/app/oracle/oradata/TEST21/D8843948FA4E1659E0531502A8C00AD6/datafile/o1_mf_u ndotbs1_k16n5zj0_.dbf
As specified in the documentation , the parameter cloned must be set to TRUE (as I am not using Exadata and a filesystem not supporting snapshots).
If the file system supports storage snapshots, then CREATE PLUGGABLE DATABASE … FROM … SNAPSHOT COPY copies a PDB from a source PDB, which can be read/write during the operation. The snapshot copy PDB files use copy-on-write technology. Only modified blocks require extra storage on disk. If the file system does not support storage snapshots or use Oracle Exadata sparse files, then the CLONEDB initialization parameter must be true, and the source PDB must be read-only for as long as the snapshot copy PDB exists.
Let’s set the parameter cloned to TRUE.
SQL> alter system set clonedb=TRUE scope=spfile; System altered. SQL> shut immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 1191180480 bytes Fixed Size 9685184 bytes Variable Size 335544320 bytes Database Buffers 838860800 bytes Redo Buffers 7090176 bytes Database mounted. Database opened. SQL> show parameter clonedb; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ clonedb boolean TRUE clonedb_dir string SQL>
And let’s try to recreate the DEVSNAP
SQL> create pluggable database DEVSNAP from pdb1fres snapshot copy; Pluggable database created. SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 DEVSNAP MOUNTED 4 PDB1FRES MOUNTED SQL>
It works.
Now let’s do some basic operations on the snapshot DEVSNAP
-Open DEVSNAP on RW
SQL> alter pluggable database DEVSNAP open; Pluggable database altered. SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 DEVSNAP READ WRITE NO 4 PDB1FRES MOUNTED SQL>
-Query the table test
SQL> alter session set container=DEVSNAP; Session altered. SQL> show con_name CON_NAME ------------------------------ DEVSNAP SQL> select * from test; ID ---------- 1 10 20 30 40 50 6 rows selected. SQL>
-Create a table on DEVSNAP
SQL> create table testsnap(id number); Table created. SQL> insert into testsnap values (1); 1 row created. SQL> commit; Commit complete. SQL> select * from testsnap; ID ---------- 1 SQL>
While DEVSNAP is open in RW,let’s open the source PDB1FRES in a RO mode
SQL> alter pluggable database PDB1FRES open; Pluggable database altered. SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 DEVSNAP READ WRITE NO 4 PDB1FRES READ ONLY NO SQL> alter session set container=PDB1FRES; Session altered. SQL> select * from test; ID ---------- 1 10 20 30 40 50 6 rows selected. SQL>
Let’s close again PDB1FRES and let’s refresh it with changes in PDB1 (remember that PDB1FRES is a refreshable PDB)
SQL> alter pluggable database PDB1FRES close; Pluggable database altered. SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 4 PDB1FRES MOUNTED SQL> alter pluggable database PDB1FRES refresh; alter pluggable database PDB1FRES refresh * ERROR at line 1: ORA-00283: recovery session canceled due to errors ORA-01114: IO error writing block to file 18 (block # 1) ORA-01110: data file 18: '/u01/app/oracle/oradata/TEST21/D8843948FA4E1659E0531502A8C00AD6/datafile/o1_mf_ users_k16n5zj1_.dbf' ORA-27091: unable to queue I/O ORA-27041: unable to open file Linux-x86_64 Error: 13: Permission denied Additional information: 3 SQL>
What happens? Let’s look the permissions files for PDB1FRES
SQL> show con_name CON_NAME ------------------------------ PDB1FRES SQL> select GUID from v$pdbs; GUID -------------------------------- D8843948FA4E1659E0531502A8C00AD6 SQL> [[email protected] ~]$ ls -l /u01/app/oracle/oradata/TEST21/D8843948FA4E1659E0531502A8C00AD6/datafile/ total 812324 -r--r-----. 1 oracle oinstall 440410112 Feb 21 09:45 o1_mf_sysaux_k16n5zhx_.dbf -r--r-----. 1 oracle oinstall 314580992 Feb 21 09:45 o1_mf_system_k16n5zhs_.dbf -rw-r-----. 1 oracle oinstall 135274496 Feb 21 09:43 o1_mf_temp_k16n5zhy_.dbf -r--r-----. 1 oracle oinstall 68165632 Feb 21 09:45 o1_mf_undotbs1_k16n5zhx_.dbf -r--r-----. 1 oracle oinstall 3350528 Feb 21 09:45 o1_mf_undotbs1_k16n5zj0_.dbf -r--r-----. 1 oracle oinstall 5251072 Feb 21 09:45 o1_mf_users_k16n5zj1_.dbf [[email protected] ~]$
If we compare these permissions to other PDBS
[email protected]:/home/oracle/ [DB21 (CDB$ROOT)] ls -l /u01/app/oracle/oradata/DB21/B61CD0352E9C6862E0531502A8C08AB7/datafile/ total 803016 -rw-r-----. 1 oracle oinstall 429924352 Feb 21 11:05 o1_mf_sysaux_hx477lhx_.dbf -rw-r-----. 1 oracle oinstall 314580992 Feb 21 11:10 o1_mf_system_hx477lhw_.dbf -rw-r-----. 1 oracle oinstall 135274496 Dec 21 14:25 o1_mf_temp_hx477lj0_.dbf -rw-r-----. 1 oracle oinstall 62922752 Feb 21 11:10 o1_mf_undotbs1_hx477lhy_.dbf -rw-r-----. 1 oracle oinstall 3350528 Feb 21 10:03 o1_mf_undotbs1_hx477lj0_.dbf [email protected]:/home/oracle/ [DB21 (CDB$ROOT)]
we can see that the permissions change for PDB1FRES datafiles since we create the snapshot.
Yes it’s a normal behavior. Looking into the following document
How To Safely Revert Back Datafile Permissions For Source PDB After Removing All Snapshot Clones (Doc ID 2627975.1)
We can read following lines
When a snapshot copy PDB is created from a source PDB using create snapshot copy pdb, the permissions of datafiles of the source PDB are changed to read only mode.
These datafile permissions are set to prevent accidental modification of the source PDB datafiles. Any modification of these datafiles will prevent snapshot copy PDBs depending on these datafiles from working correctly and consistently.
So if we want to refresh PDB1FRES after snapshot creation, let’s follow these steps
1-Remove all snapshot for this PDB1FRES
SQL> alter pluggable database devsnap close; Pluggable database altered. SQL> select GUID from v$PDBs where NAME like 'PDB1FRES'; GUID -------------------------------- D8843948FA4E1659E0531502A8C00AD6 SQL> select PDB_NAME from dba_pdb_history where CLONED_FROM_PDB_GUID like 'D8843948FA4E1659E0531502A8C00AD6'; PDB_NAME -------------------------------------------------------------------------------- DEVSNAP DEVSNAP DEVSNAP DEVSNAP SQL> SQL> drop pluggable database devsnap including datafiles; Pluggable database dropped. SQL>
2-Restore datafiles permissions of PDB1FRES
SQL> exec dbms_dnfs.restore_datafile_permissions('PDB1FRES'); PL/SQL procedure successfully completed. SQL>
We can verify that permissions are reverted
/u01/app/oracle/oradata/TEST21/D8843948FA4E1659E0531502A8C00AD6/datafile/ total 812324 -rw-rw----. 1 oracle oinstall 440410112 Feb 21 09:45 o1_mf_sysaux_k16n5zhx_.dbf -rw-rw----. 1 oracle oinstall 314580992 Feb 21 09:45 o1_mf_system_k16n5zhs_.dbf -rw-r-----. 1 oracle oinstall 135274496 Feb 21 09:43 o1_mf_temp_k16n5zhy_.dbf -rw-rw----. 1 oracle oinstall 68165632 Feb 21 09:45 o1_mf_undotbs1_k16n5zhx_.dbf -rw-rw----. 1 oracle oinstall 3350528 Feb 21 09:45 o1_mf_undotbs1_k16n5zj0_.dbf -rw-rw----. 1 oracle oinstall 5251072 Feb 21 09:45 o1_mf_users_k16n5zj1_.dbf [[email protected] ~]$
3-And then refresh
SQL> alter pluggable database PDB1FRES refresh; Pluggable database altered. SQL> alter pluggable database PDB1FRES open; Pluggable database altered. SQL>
We can then recreate snapshots from PDB1FRES after refresh and so on.
Ok we saw that we can open the snapshots in RW and work on it as a normal PDB. What else we can do with the snapshots
1-We can use a snapshot as a source to clone a full PDB
SQL> create pluggable database CLONESNAP from DEVSNAP; Pluggable database created. SQL> alter pluggable database CLONESNAP open; Pluggable database altered. SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 DEVSNAP MOUNTED 4 PDB1FRES MOUNTED 5 CLONESNAP READ WRITE NO SQL>
And then drop the DEVSNAP
SQL> drop pluggable database DEVSNAP including datafiles; Pluggable database dropped. SQL>
2-Materialize a snapshot copy
Materializing a snapshot copy PDB copies all data blocks. We can materialize a snapshot copy PDB by running an ALTER PLUGGABLE DATABASE statement with the MATERIALIZE clause.
When materialized, the snapshot copy will be transformed into a full pluggable database and will be no longer dependent of the source PDB
SQL> alter session set container=DEVSNAP; Session altered. SQL> startup Pluggable Database opened. SQL> alter pluggable database materialize; Pluggable database altered. SQL>
Conclusion
We have seen how refreshable PDB technology can be used with snapshots in a multitenant environment to provide PDBS for developers, for testing …
Hope this blog helps