dbi Blog

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

Post Views: 1,750

Pluggable Database Snapshots with Oracle 21c

Conclusion

Mouhamadou Diaw

Leave a Reply:

Related blog articles

Conclusion

Mouhamadou Diaw

Leave a Reply:

Related blog articles

Installing Oracle 23c Free developer release on Proxmox

PostgreSQL 16: More statistics in pg_stat_*_tables

2023.pgconf.de and the Swiss PGDay are just around the corner

An overview of “Oracle Cloud Infrastructure: everything for an efficient migration” event by dbi services