{"id":17134,"date":"2022-02-21T16:54:30","date_gmt":"2022-02-21T15:54:30","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/pluggable-database-snapshots-with-oracle-21c\/"},"modified":"2023-06-20T10:04:36","modified_gmt":"2023-06-20T08:04:36","slug":"pluggable-database-snapshots-with-oracle-21c","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/pluggable-database-snapshots-with-oracle-21c\/","title":{"rendered":"Pluggable Database Snapshots with Oracle 21c"},"content":{"rendered":"

By Mouhamadou Diaw<\/strong><\/p>\n

In a previous blog <\/a>, 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.<\/p>\n

One application is to use the target refreshable PDB as a master to provide PDB snapshots.<\/p>\n

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.
\nThe taken snapshot can be used to clone a full standalone PDB or can be materialized.<\/p>\n

I am using the same configuration as in my previous blog <\/a> (VM with XFS FS)
\nAn Oracle 21c source CDB : DB21 with a source PDB PDB1
\nAn Oracle 21c target CDB : TEST21 which will contain the refreshable clone of PDB1<\/p>\n

From the refreshable PDB PDB1FRES, we can create for example 2 PDB snapshots
\n-DEVSNAP for the developers
\n-TESTSNAP for testing<\/p>\n

\"\"<\/a><\/p>\n

Let’s do some change in PDB1<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n
4<\/div>\n
5<\/div>\n
6<\/div>\n
7<\/div>\n
8<\/div>\n
9<\/div>\n
10<\/div>\n
11<\/div>\n
12<\/div>\n
13<\/div>\n
14<\/div>\n
15<\/div>\n
16<\/div>\n<\/td>\n
\n
\n
SQL> show con_name<\/code><\/div>\n
<\/div>\n
CON_NAME<\/code><\/div>\n
------------------------------<\/code><\/div>\n
PDB1<\/code><\/div>\n
SQL><\/code><\/div>\n
<\/div>\n
SQL> <\/code>select<\/code> * <\/code>from<\/code> test;<\/code><\/div>\n
<\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>ID<\/code><\/div>\n
----------<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>1<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>10<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>20<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>30<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>40<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

On TEST21 CDB, let’s do a refresh of PDB1FRES<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n
4<\/div>\n
5<\/div>\n
6<\/div>\n
7<\/div>\n
8<\/div>\n
9<\/div>\n<\/td>\n
\n
\n
SQL> show pdbs;<\/code><\/div>\n
<\/div>\n
\u00a0\u00a0\u00a0\u00a0<\/code>CON_ID CON_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/code>OPEN<\/code> MODE\u00a0 RESTRICTED<\/code><\/div>\n
---------- ------------------------------ ---------- ----------<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>2 PDB$SEED\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/code>READ<\/code> ONLY<\/code>\u00a0 NO<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>4 PDB1FRES\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 MOUNTED<\/code><\/div>\n
SQL> <\/code>alter<\/code> pluggable <\/code>database<\/code> PDB1FRES refresh;<\/code><\/div>\n
<\/div>\n
Pluggable <\/code>database<\/code> altered.<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

We can verify that new changes on PDB1 are present on PDB1FRES<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n
4<\/div>\n
5<\/div>\n
6<\/div>\n
7<\/div>\n
8<\/div>\n
9<\/div>\n
10<\/div>\n
11<\/div>\n
12<\/div>\n
13<\/div>\n
14<\/div>\n
15<\/div>\n
16<\/div>\n
17<\/div>\n
18<\/div>\n
19<\/div>\n
20<\/div>\n
21<\/div>\n
22<\/div>\n
23<\/div>\n
24<\/div>\n
25<\/div>\n
26<\/div>\n
27<\/div>\n
28<\/div>\n
29<\/div>\n
30<\/div>\n
31<\/div>\n<\/td>\n
\n
\n
SQL> <\/code>alter<\/code> pluggable <\/code>database<\/code> PDB1FRES <\/code>open<\/code> ;<\/code><\/div>\n
<\/div>\n
Pluggable <\/code>database<\/code> altered.<\/code><\/div>\n
<\/div>\n
SQL> show pdbs;<\/code><\/div>\n
<\/div>\n
\u00a0\u00a0\u00a0\u00a0<\/code>CON_ID CON_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/code>OPEN<\/code> MODE\u00a0 RESTRICTED<\/code><\/div>\n
---------- ------------------------------ ---------- ----------<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>2 PDB$SEED\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/code>READ<\/code> ONLY<\/code>\u00a0 NO<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>4 PDB1FRES\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/code>READ<\/code> ONLY<\/code>\u00a0 NO<\/code><\/div>\n
SQL> <\/code>alter<\/code> session <\/code>set<\/code> container=PDB1FRES;<\/code><\/div>\n
<\/div>\n
Session altered.<\/code><\/div>\n
<\/div>\n
SQL> show con_name;<\/code><\/div>\n
<\/div>\n
CON_NAME<\/code><\/div>\n
------------------------------<\/code><\/div>\n
PDB1FRES<\/code><\/div>\n
SQL> <\/code>select<\/code> * <\/code>from<\/code> test;<\/code><\/div>\n
<\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>ID<\/code><\/div>\n
----------<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>1<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>10<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>20<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>30<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>40<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>50<\/code><\/div>\n
<\/div>\n
6 <\/code>rows<\/code> selected.<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

OK now let’s create the DEVSNAP snapshot from PDB1FRES<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n
4<\/div>\n
5<\/div>\n
6<\/div>\n
7<\/div>\n
8<\/div>\n
9<\/div>\n
10<\/div>\n<\/td>\n
\n
\n
SQL> <\/code>create<\/code> pluggable <\/code>database<\/code> DEVSNAP <\/code>from<\/code> pdb1fres snapshot copy;<\/code><\/div>\n
create<\/code> pluggable <\/code>database<\/code> DEVSNAP <\/code>from<\/code> pdb1fres snapshot copy<\/code><\/div>\n
*<\/code><\/div>\n
ERROR <\/code>at<\/code> line 1:<\/code><\/div>\n
ORA-65169: error encountered while attempting <\/code>to<\/code> copy file<\/code><\/div>\n
\/u01\/app\/oracle\/oradata\/TEST21\/D8843948FA4E1659E0531502A8C00AD6\/datafile\/o1_mf_u<\/code><\/div>\n
ndotbs1_k16n5zj0_.dbf<\/code><\/div>\n
ORA-17525: <\/code>Database<\/code> clone using storage snapshot <\/code>not<\/code> supported <\/code>on<\/code> file<\/code><\/div>\n
\/u01\/app\/oracle\/oradata\/TEST21\/D8843948FA4E1659E0531502A8C00AD6\/datafile\/o1_mf_u<\/code><\/div>\n
ndotbs1_k16n5zj0_.dbf<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

As specified in the documentation <\/a>, the parameter cloned must be set to TRUE (as I am not using Exadata and a filesystem not supporting snapshots).
\nIf 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.<\/em><\/p>\n

Let’s set the parameter cloned to TRUE.<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n
4<\/div>\n
5<\/div>\n
6<\/div>\n
7<\/div>\n
8<\/div>\n
9<\/div>\n
10<\/div>\n
11<\/div>\n
12<\/div>\n
13<\/div>\n
14<\/div>\n
15<\/div>\n
16<\/div>\n
17<\/div>\n
18<\/div>\n
19<\/div>\n
20<\/div>\n
21<\/div>\n
22<\/div>\n
23<\/div>\n
24<\/div>\n
25<\/div>\n<\/td>\n
\n
\n
SQL> <\/code>alter<\/code> system <\/code>set<\/code> clonedb=<\/code>TRUE<\/code> scope=spfile;<\/code><\/div>\n
<\/div>\n
System altered.<\/code><\/div>\n
<\/div>\n
SQL> shut immediate<\/code><\/div>\n
Database<\/code> closed.<\/code><\/div>\n
Database<\/code> dismounted.<\/code><\/div>\n
ORACLE instance shut down.<\/code><\/div>\n
SQL> startup<\/code><\/div>\n
ORACLE instance started.<\/code><\/div>\n
<\/div>\n
Total System <\/code>Global<\/code> Area 1191180480 bytes<\/code><\/div>\n
Fixed <\/code>Size<\/code>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 9685184 bytes<\/code><\/div>\n
Variable <\/code>Size<\/code>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 335544320 bytes<\/code><\/div>\n
Database<\/code> Buffers\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 838860800 bytes<\/code><\/div>\n
Redo Buffers\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 7090176 bytes<\/code><\/div>\n
Database<\/code> mounted.<\/code><\/div>\n
Database<\/code> opened.<\/code><\/div>\n
SQL> show parameter clonedb;<\/code><\/div>\n
<\/div>\n
NAME<\/code>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 TYPE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 VALUE<\/code><\/div>\n
------------------------------------ ----------- ------------------------------<\/code><\/div>\n
clonedb\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 boolean\u00a0\u00a0\u00a0\u00a0 <\/code>TRUE<\/code><\/div>\n
clonedb_dir\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 string<\/code><\/div>\n
SQL><\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

And let’s try to recreate the DEVSNAP<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n
4<\/div>\n
5<\/div>\n
6<\/div>\n
7<\/div>\n
8<\/div>\n
9<\/div>\n
10<\/div>\n
11<\/div>\n
12<\/div>\n<\/td>\n
\n
\n
SQL> <\/code>create<\/code> pluggable <\/code>database<\/code> DEVSNAP <\/code>from<\/code> pdb1fres snapshot copy;<\/code><\/div>\n
<\/div>\n
Pluggable <\/code>database<\/code> created.<\/code><\/div>\n
<\/div>\n
SQL> show pdbs<\/code><\/div>\n
<\/div>\n
\u00a0\u00a0\u00a0\u00a0<\/code>CON_ID CON_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/code>OPEN<\/code> MODE\u00a0 RESTRICTED<\/code><\/div>\n
---------- ------------------------------ ---------- ----------<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>2 PDB$SEED\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/code>READ<\/code> ONLY<\/code>\u00a0 NO<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>3 DEVSNAP\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 MOUNTED<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>4 PDB1FRES\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 MOUNTED<\/code><\/div>\n
SQL><\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

It works.<\/p>\n

Now let’s do some basic operations on the snapshot DEVSNAP<\/p>\n

-Open DEVSNAP on RW<\/strong><\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n
4<\/div>\n
5<\/div>\n
6<\/div>\n
7<\/div>\n
8<\/div>\n
9<\/div>\n
10<\/div>\n
11<\/div>\n
12<\/div>\n<\/td>\n
\n
\n
SQL> <\/code>alter<\/code> pluggable <\/code>database<\/code> DEVSNAP <\/code>open<\/code>;<\/code><\/div>\n
<\/div>\n
Pluggable <\/code>database<\/code> altered.<\/code><\/div>\n
<\/div>\n
SQL> show pdbs;<\/code><\/div>\n
<\/div>\n
\u00a0\u00a0\u00a0\u00a0<\/code>CON_ID CON_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/code>OPEN<\/code> MODE\u00a0 RESTRICTED<\/code><\/div>\n
---------- ------------------------------ ---------- ----------<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>2 PDB$SEED\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/code>READ<\/code> ONLY<\/code>\u00a0 NO<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>3 DEVSNAP\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/code>READ<\/code> WRITE <\/code>NO<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>4 PDB1FRES\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 MOUNTED<\/code><\/div>\n
SQL><\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

-Query the table test<\/strong><\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n
4<\/div>\n
5<\/div>\n
6<\/div>\n
7<\/div>\n
8<\/div>\n
9<\/div>\n
10<\/div>\n
11<\/div>\n
12<\/div>\n
13<\/div>\n
14<\/div>\n
15<\/div>\n
16<\/div>\n
17<\/div>\n
18<\/div>\n
19<\/div>\n
20<\/div>\n
21<\/div>\n
22<\/div>\n
23<\/div>\n<\/td>\n
\n
\n
SQL> <\/code>alter<\/code> session <\/code>set<\/code> container=DEVSNAP;<\/code><\/div>\n
<\/div>\n
Session altered.<\/code><\/div>\n
<\/div>\n
SQL> show con_name<\/code><\/div>\n
<\/div>\n
CON_NAME<\/code><\/div>\n
------------------------------<\/code><\/div>\n
DEVSNAP<\/code><\/div>\n
SQL> <\/code>select<\/code> * <\/code>from<\/code> test;<\/code><\/div>\n
<\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>ID<\/code><\/div>\n
----------<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>1<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>10<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>20<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>30<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>40<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>50<\/code><\/div>\n
<\/div>\n
6 <\/code>rows<\/code> selected.<\/code><\/div>\n
<\/div>\n
SQL><\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

-Create a table on DEVSNAP<\/strong><\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n
4<\/div>\n
5<\/div>\n
6<\/div>\n
7<\/div>\n
8<\/div>\n
9<\/div>\n
10<\/div>\n
11<\/div>\n
12<\/div>\n
13<\/div>\n
14<\/div>\n
15<\/div>\n
16<\/div>\n
17<\/div>\n
18<\/div>\n
19<\/div>\n<\/td>\n
\n
\n
SQL> <\/code>create<\/code> table<\/code> testsnap(id number);<\/code><\/div>\n
<\/div>\n
Table<\/code> created.<\/code><\/div>\n
<\/div>\n
SQL> <\/code>insert<\/code> into<\/code> testsnap <\/code>values<\/code> (1);<\/code><\/div>\n
<\/div>\n
1 row created.<\/code><\/div>\n
<\/div>\n
SQL> <\/code>commit<\/code>;<\/code><\/div>\n
<\/div>\n
Commit<\/code> complete.<\/code><\/div>\n
<\/div>\n
SQL> <\/code>select<\/code> * <\/code>from<\/code> testsnap;<\/code><\/div>\n
<\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>ID<\/code><\/div>\n
----------<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>1<\/code><\/div>\n
<\/div>\n
SQL><\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

While DEVSNAP is open in RW,let’s open the source PDB1FRES in a RO mode<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n
4<\/div>\n
5<\/div>\n
6<\/div>\n
7<\/div>\n
8<\/div>\n
9<\/div>\n
10<\/div>\n
11<\/div>\n
12<\/div>\n
13<\/div>\n
14<\/div>\n
15<\/div>\n
16<\/div>\n
17<\/div>\n
18<\/div>\n
19<\/div>\n
20<\/div>\n
21<\/div>\n
22<\/div>\n
23<\/div>\n
24<\/div>\n
25<\/div>\n
26<\/div>\n
27<\/div>\n
28<\/div>\n
29<\/div>\n<\/td>\n
\n
\n
SQL> <\/code>alter<\/code> pluggable <\/code>database<\/code> PDB1FRES <\/code>open<\/code>;<\/code><\/div>\n
<\/div>\n
Pluggable <\/code>database<\/code> altered.<\/code><\/div>\n
<\/div>\n
SQL> show pdbs;<\/code><\/div>\n
<\/div>\n
\u00a0\u00a0\u00a0\u00a0<\/code>CON_ID CON_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/code>OPEN<\/code> MODE\u00a0 RESTRICTED<\/code><\/div>\n
---------- ------------------------------ ---------- ----------<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>2 PDB$SEED\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/code>READ<\/code> ONLY<\/code>\u00a0 NO<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>3 DEVSNAP\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/code>READ<\/code> WRITE <\/code>NO<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>4 PDB1FRES\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/code>READ<\/code> ONLY<\/code>\u00a0 NO<\/code><\/div>\n
SQL> <\/code>alter<\/code> session <\/code>set<\/code> container=PDB1FRES;<\/code><\/div>\n
<\/div>\n
Session altered.<\/code><\/div>\n
<\/div>\n
SQL> <\/code>select<\/code> * <\/code>from<\/code> test;<\/code><\/div>\n
<\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>ID<\/code><\/div>\n
----------<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>1<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>10<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>20<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>30<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>40<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>50<\/code><\/div>\n
<\/div>\n
6 <\/code>rows<\/code> selected.<\/code><\/div>\n
<\/div>\n
SQL><\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

Let’s close again PDB1FRES and let’s refresh it with changes in PDB1 (remember that PDB1FRES is a refreshable PDB)<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n
4<\/div>\n
5<\/div>\n
6<\/div>\n
7<\/div>\n
8<\/div>\n
9<\/div>\n
10<\/div>\n
11<\/div>\n
12<\/div>\n
13<\/div>\n
14<\/div>\n
15<\/div>\n
16<\/div>\n
17<\/div>\n
18<\/div>\n
19<\/div>\n
20<\/div>\n
21<\/div>\n
22<\/div>\n
23<\/div>\n
24<\/div>\n
25<\/div>\n<\/td>\n
\n
\n
SQL> <\/code>alter<\/code> pluggable <\/code>database<\/code> PDB1FRES <\/code>close<\/code>;<\/code><\/div>\n
<\/div>\n
Pluggable <\/code>database<\/code> altered.<\/code><\/div>\n
<\/div>\n
SQL> show pdbs<\/code><\/div>\n
<\/div>\n
\u00a0\u00a0\u00a0\u00a0<\/code>CON_ID CON_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/code>OPEN<\/code> MODE\u00a0 RESTRICTED<\/code><\/div>\n
---------- ------------------------------ ---------- ----------<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>4 PDB1FRES\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 MOUNTED<\/code><\/div>\n
SQL> <\/code>alter<\/code> pluggable <\/code>database<\/code> PDB1FRES refresh;<\/code><\/div>\n
alter<\/code> pluggable <\/code>database<\/code> PDB1FRES refresh<\/code><\/div>\n
*<\/code><\/div>\n
ERROR <\/code>at<\/code> line 1:<\/code><\/div>\n
ORA-00283: recovery session canceled due <\/code>to<\/code> errors<\/code><\/div>\n
ORA-01114: IO error writing block <\/code>to<\/code> file 18 (block # 1)<\/code><\/div>\n
ORA-01110: data file 18:<\/code><\/div>\n
'\/u01\/app\/oracle\/oradata\/TEST21\/D8843948FA4E1659E0531502A8C00AD6\/datafile\/o1_mf_<\/code><\/div>\n
users_k16n5zj1_.dbf'<\/code><\/div>\n
ORA-27091: unable <\/code>to<\/code> queue I\/O<\/code><\/div>\n
ORA-27041: unable <\/code>to<\/code> open<\/code> file<\/code><\/div>\n
Linux-x86_64 Error: 13: Permission denied<\/code><\/div>\n
Additional information: 3<\/code><\/div>\n
<\/div>\n
<\/div>\n
SQL><\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

What happens? Let’s look the permissions files for PDB1FRES<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n
4<\/div>\n
5<\/div>\n
6<\/div>\n
7<\/div>\n
8<\/div>\n
9<\/div>\n
10<\/div>\n
11<\/div>\n
12<\/div>\n
13<\/div>\n
14<\/div>\n
15<\/div>\n
16<\/div>\n
17<\/div>\n
18<\/div>\n
19<\/div>\n
20<\/div>\n
21<\/div>\n<\/td>\n
\n
\n
SQL> show con_name<\/code><\/div>\n
<\/div>\n
CON_NAME<\/code><\/div>\n
------------------------------<\/code><\/div>\n
PDB1FRES<\/code><\/div>\n
SQL> <\/code>select<\/code> GUID <\/code>from<\/code> v$pdbs;<\/code><\/div>\n
<\/div>\n
GUID<\/code><\/div>\n
--------------------------------<\/code><\/div>\n
D8843948FA4E1659E0531502A8C00AD6<\/code><\/div>\n
<\/div>\n
SQL><\/code><\/div>\n
<\/div>\n
[oracle@oraadserver ~]$ ls -l \/u01\/app\/oracle\/oradata\/TEST21\/D8843948FA4E1659E0531502A8C00AD6\/datafile\/\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 total 812324<\/code><\/div>\n
-r<\/code>--r-----. 1 oracle oinstall 440410112 Feb 21 09:45 o1_mf_sysaux_k16n5zhx_.dbf<\/code><\/div>\n
-r<\/code>--r-----. 1 oracle oinstall 314580992 Feb 21 09:45 o1_mf_system_k16n5zhs_.dbf<\/code><\/div>\n
-rw-r<\/code>-----. 1 oracle oinstall 135274496 Feb 21 09:43 o1_mf_temp_k16n5zhy_.dbf<\/code><\/div>\n
-r<\/code>--r-----. 1 oracle oinstall\u00a0 68165632 Feb 21 09:45 o1_mf_undotbs1_k16n5zhx_.dbf<\/code><\/div>\n
-r<\/code>--r-----. 1 oracle oinstall\u00a0\u00a0 3350528 Feb 21 09:45 o1_mf_undotbs1_k16n5zj0_.dbf<\/code><\/div>\n
-r<\/code>--r-----. 1 oracle oinstall\u00a0\u00a0 5251072 Feb 21 09:45 o1_mf_users_k16n5zj1_.dbf<\/code><\/div>\n
[oracle@oraadserver ~]$<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

If we compare these permissions to other PDBS<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n
4<\/div>\n
5<\/div>\n
6<\/div>\n
7<\/div>\n
8<\/div>\n<\/td>\n
\n
\n
oracle@oraadserver:\/home\/oracle\/ [DB21 (CDB$ROOT)] ls -l \/u01\/app\/oracle\/oradata\/DB21\/B61CD0352E9C6862E0531502A8C08AB7\/datafile\/<\/code><\/div>\n
total 803016<\/code><\/div>\n
-rw-r<\/code>-----. 1 oracle oinstall 429924352 Feb 21 11:05 o1_mf_sysaux_hx477lhx_.dbf<\/code><\/div>\n
-rw-r<\/code>-----. 1 oracle oinstall 314580992 Feb 21 11:10 o1_mf_system_hx477lhw_.dbf<\/code><\/div>\n
-rw-r<\/code>-----. 1 oracle oinstall 135274496 Dec 21 14:25 o1_mf_temp_hx477lj0_.dbf<\/code><\/div>\n
-rw-r<\/code>-----. 1 oracle oinstall\u00a0 62922752 Feb 21 11:10 o1_mf_undotbs1_hx477lhy_.dbf<\/code><\/div>\n
-rw-r<\/code>-----. 1 oracle oinstall\u00a0\u00a0 3350528 Feb 21 10:03 o1_mf_undotbs1_hx477lj0_.dbf<\/code><\/div>\n
oracle@oraadserver:\/home\/oracle\/ [DB21 (CDB$ROOT)]<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

we can see that the permissions change for PDB1FRES datafiles since we create the snapshot.
\nYes it’s a normal behavior. Looking into the following document<\/p>\n

How To Safely Revert Back Datafile Permissions For Source PDB After Removing All Snapshot Clones (Doc ID 2627975.1)<\/em><\/p>\n

We can read following lines<\/p>\n

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.
\nThese 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.<\/em><\/p>\n

So if we want to refresh PDB1FRES after snapshot creation, let’s follow these steps<\/p>\n

1-Remove all snapshot for this PDB1FRES<\/strong><\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n
4<\/div>\n
5<\/div>\n
6<\/div>\n
7<\/div>\n
8<\/div>\n
9<\/div>\n
10<\/div>\n
11<\/div>\n
12<\/div>\n
13<\/div>\n
14<\/div>\n
15<\/div>\n
16<\/div>\n
17<\/div>\n
18<\/div>\n
19<\/div>\n
20<\/div>\n
21<\/div>\n
22<\/div>\n
23<\/div>\n
24<\/div>\n
25<\/div>\n
26<\/div>\n
27<\/div>\n<\/td>\n
\n
\n
SQL> <\/code>alter<\/code> pluggable <\/code>database<\/code> devsnap <\/code>close<\/code>;<\/code><\/div>\n
<\/div>\n
Pluggable <\/code>database<\/code> altered.<\/code><\/div>\n
<\/div>\n
<\/div>\n
SQL> <\/code>select<\/code> GUID <\/code>from<\/code> v$PDBs <\/code>where<\/code> NAME<\/code> like<\/code> 'PDB1FRES'<\/code>;<\/code><\/div>\n
<\/div>\n
GUID<\/code><\/div>\n
--------------------------------<\/code><\/div>\n
D8843948FA4E1659E0531502A8C00AD6<\/code><\/div>\n
<\/div>\n
SQL> <\/code>select<\/code> PDB_NAME <\/code>from<\/code> dba_pdb_history <\/code>where<\/code> CLONED_FROM_PDB_GUID <\/code>like<\/code> 'D8843948FA4E1659E0531502A8C00AD6'<\/code>;<\/code><\/div>\n
<\/div>\n
PDB_NAME<\/code><\/div>\n
--------------------------------------------------------------------------------<\/code><\/div>\n
DEVSNAP<\/code><\/div>\n
DEVSNAP<\/code><\/div>\n
DEVSNAP<\/code><\/div>\n
DEVSNAP<\/code><\/div>\n
<\/div>\n
SQL><\/code><\/div>\n
<\/div>\n
SQL> <\/code>drop<\/code> pluggable <\/code>database<\/code> devsnap including datafiles;<\/code><\/div>\n
<\/div>\n
Pluggable <\/code>database<\/code> dropped.<\/code><\/div>\n
<\/div>\n
SQL><\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

2-Restore datafiles permissions of PDB1FRES<\/strong><\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n
4<\/div>\n
5<\/div>\n<\/td>\n
\n
\n
SQL> <\/code>exec<\/code> dbms_dnfs.restore_datafile_permissions(<\/code>'PDB1FRES'<\/code>);<\/code><\/div>\n
<\/div>\n
PL\/SQL <\/code>procedure<\/code> successfully completed.<\/code><\/div>\n
<\/div>\n
SQL><\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

We can verify that permissions are reverted<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n
4<\/div>\n
5<\/div>\n
6<\/div>\n
7<\/div>\n
8<\/div>\n
9<\/div>\n<\/td>\n
\n
\n
\/u01\/app\/oracle\/oradata\/TEST21\/D8843948FA4E1659E0531502A8C00AD6\/datafile\/<\/code><\/div>\n
total 812324<\/code><\/div>\n
-rw-rw<\/code>----. 1 oracle oinstall 440410112 Feb 21 09:45 o1_mf_sysaux_k16n5zhx_.dbf<\/code><\/div>\n
-rw-rw<\/code>----. 1 oracle oinstall 314580992 Feb 21 09:45 o1_mf_system_k16n5zhs_.dbf<\/code><\/div>\n
-rw-r<\/code>-----. 1 oracle oinstall 135274496 Feb 21 09:43 o1_mf_temp_k16n5zhy_.dbf<\/code><\/div>\n
-rw-rw<\/code>----. 1 oracle oinstall\u00a0 68165632 Feb 21 09:45 o1_mf_undotbs1_k16n5zhx_.dbf<\/code><\/div>\n
-rw-rw<\/code>----. 1 oracle oinstall\u00a0\u00a0 3350528 Feb 21 09:45 o1_mf_undotbs1_k16n5zj0_.dbf<\/code><\/div>\n
-rw-rw<\/code>----. 1 oracle oinstall\u00a0\u00a0 5251072 Feb 21 09:45 o1_mf_users_k16n5zj1_.dbf<\/code><\/div>\n
[oracle@oraadserver ~]$<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

3-And then refresh <\/strong><\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n
4<\/div>\n
5<\/div>\n
6<\/div>\n
7<\/div>\n
8<\/div>\n
9<\/div>\n<\/td>\n
\n
\n
SQL> <\/code>alter<\/code> pluggable <\/code>database<\/code> PDB1FRES refresh;<\/code><\/div>\n
<\/div>\n
Pluggable <\/code>database<\/code> altered.<\/code><\/div>\n
<\/div>\n
SQL> <\/code>alter<\/code> pluggable <\/code>database<\/code> PDB1FRES <\/code>open<\/code>;<\/code><\/div>\n
<\/div>\n
Pluggable <\/code>database<\/code> altered.<\/code><\/div>\n
<\/div>\n
SQL><\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

We can then recreate snapshots from PDB1FRES after refresh and so on.
\nOk 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<\/p>\n

1-We can use a snapshot as a source to clone a full PDB<\/strong><\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n
4<\/div>\n
5<\/div>\n
6<\/div>\n
7<\/div>\n
8<\/div>\n
9<\/div>\n
10<\/div>\n
11<\/div>\n
12<\/div>\n
13<\/div>\n
14<\/div>\n
15<\/div>\n
16<\/div>\n
17<\/div>\n<\/td>\n
\n
\n
SQL> <\/code>create<\/code> pluggable <\/code>database<\/code> CLONESNAP <\/code>from<\/code> DEVSNAP;<\/code><\/div>\n
<\/div>\n
Pluggable <\/code>database<\/code> created.<\/code><\/div>\n
<\/div>\n
SQL> <\/code>alter<\/code> pluggable <\/code>database<\/code> CLONESNAP <\/code>open<\/code>;<\/code><\/div>\n
<\/div>\n
Pluggable <\/code>database<\/code> altered.<\/code><\/div>\n
<\/div>\n
SQL> show pdbs<\/code><\/div>\n
<\/div>\n
\u00a0\u00a0\u00a0\u00a0<\/code>CON_ID CON_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/code>OPEN<\/code> MODE\u00a0 RESTRICTED<\/code><\/div>\n
---------- ------------------------------ ---------- ----------<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>2 PDB$SEED\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/code>READ<\/code> ONLY<\/code>\u00a0 NO<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>3 DEVSNAP\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 MOUNTED<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>4 PDB1FRES\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 MOUNTED<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>5 CLONESNAP\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/code>READ<\/code> WRITE <\/code>NO<\/code><\/div>\n
SQL><\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

And then drop the DEVSNAP<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n
4<\/div>\n
5<\/div>\n<\/td>\n
\n
\n
SQL> <\/code>drop<\/code> pluggable <\/code>database<\/code> DEVSNAP including datafiles;<\/code><\/div>\n
<\/div>\n
Pluggable <\/code>database<\/code> dropped.<\/code><\/div>\n
<\/div>\n
SQL><\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

2-Materialize a snapshot copy<\/strong><\/p>\n

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.
\nWhen materialized, the snapshot copy will be transformed into a full pluggable database and will be no longer dependent of the source PDB<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n
4<\/div>\n
5<\/div>\n
6<\/div>\n
7<\/div>\n
8<\/div>\n
9<\/div>\n
10<\/div>\n
11<\/div>\n
12<\/div>\n<\/td>\n
\n
\n
SQL> <\/code>alter<\/code> session <\/code>set<\/code> container=DEVSNAP;<\/code><\/div>\n
<\/div>\n
Session altered.<\/code><\/div>\n
<\/div>\n
SQL> startup<\/code><\/div>\n
Pluggable <\/code>Database<\/code> opened.<\/code><\/div>\n
<\/div>\n
SQL> <\/code>alter<\/code>\u00a0 pluggable <\/code>database<\/code>\u00a0 materialize;<\/code><\/div>\n
<\/div>\n
Pluggable <\/code>database<\/code> altered.<\/code><\/div>\n
<\/div>\n
SQL><\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

Conclusion<\/h3>\n

We have seen how refreshable PDB technology can be used with snapshots in a multitenant environment to provide PDBS for developers, for testing …
\nHope this blog helps<\/p>\n","protected":false},"excerpt":{"rendered":"

By Mouhamadou Diaw 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 […]<\/p>\n","protected":false},"author":27,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229,198,59],"tags":[2490,137,2491,2480],"type_dbi":[],"class_list":["post-17134","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","category-database-management","category-oracle","tag-materialize-pdb-snapdhot","tag-oracle-21c","tag-pdb-snapshots","tag-refreshable-clone-pdb"],"acf":[],"yoast_head":"\nPluggable Database Snapshots with Oracle 21c - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/pluggable-database-snapshots-with-oracle-21c\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Pluggable Database Snapshots with Oracle 21c\" \/>\n<meta property=\"og:description\" content=\"By Mouhamadou Diaw 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 […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/pluggable-database-snapshots-with-oracle-21c\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2022-02-21T15:54:30+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-06-20T08:04:36+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/02\/Picture1-2.png\" \/>\n<meta name=\"author\" content=\"Oracle Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Oracle Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"9 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/pluggable-database-snapshots-with-oracle-21c\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/pluggable-database-snapshots-with-oracle-21c\\\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"Pluggable Database Snapshots with Oracle 21c\",\"datePublished\":\"2022-02-21T15:54:30+00:00\",\"dateModified\":\"2023-06-20T08:04:36+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/pluggable-database-snapshots-with-oracle-21c\\\/\"},\"wordCount\":711,\"commentCount\":0,\"image\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/pluggable-database-snapshots-with-oracle-21c\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2022\\\/02\\\/Picture1-2.png\",\"keywords\":[\"Materialize PDB snapdhot\",\"Oracle 21C\",\"PDB snapshots\",\"Refreshable clone PDB\"],\"articleSection\":[\"Database Administration & Monitoring\",\"Database management\",\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/pluggable-database-snapshots-with-oracle-21c\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/pluggable-database-snapshots-with-oracle-21c\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/pluggable-database-snapshots-with-oracle-21c\\\/\",\"name\":\"Pluggable Database Snapshots with Oracle 21c - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/pluggable-database-snapshots-with-oracle-21c\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/pluggable-database-snapshots-with-oracle-21c\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2022\\\/02\\\/Picture1-2.png\",\"datePublished\":\"2022-02-21T15:54:30+00:00\",\"dateModified\":\"2023-06-20T08:04:36+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/66ab87129f2d357f09971bc7936a77ee\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/pluggable-database-snapshots-with-oracle-21c\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/pluggable-database-snapshots-with-oracle-21c\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/pluggable-database-snapshots-with-oracle-21c\\\/#primaryimage\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2022\\\/02\\\/Picture1-2.png\",\"contentUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2022\\\/02\\\/Picture1-2.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/pluggable-database-snapshots-with-oracle-21c\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Pluggable Database Snapshots with Oracle 21c\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/66ab87129f2d357f09971bc7936a77ee\",\"name\":\"Oracle Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"caption\":\"Oracle Team\"},\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/oracle-team\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Pluggable Database Snapshots with Oracle 21c - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/pluggable-database-snapshots-with-oracle-21c\/","og_locale":"en_US","og_type":"article","og_title":"Pluggable Database Snapshots with Oracle 21c","og_description":"By Mouhamadou Diaw 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 […]","og_url":"https:\/\/www.dbi-services.com\/blog\/pluggable-database-snapshots-with-oracle-21c\/","og_site_name":"dbi Blog","article_published_time":"2022-02-21T15:54:30+00:00","article_modified_time":"2023-06-20T08:04:36+00:00","og_image":[{"url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/02\/Picture1-2.png","type":"","width":"","height":""}],"author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"9 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/pluggable-database-snapshots-with-oracle-21c\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/pluggable-database-snapshots-with-oracle-21c\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"Pluggable Database Snapshots with Oracle 21c","datePublished":"2022-02-21T15:54:30+00:00","dateModified":"2023-06-20T08:04:36+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/pluggable-database-snapshots-with-oracle-21c\/"},"wordCount":711,"commentCount":0,"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/pluggable-database-snapshots-with-oracle-21c\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/02\/Picture1-2.png","keywords":["Materialize PDB snapdhot","Oracle 21C","PDB snapshots","Refreshable clone PDB"],"articleSection":["Database Administration & Monitoring","Database management","Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/pluggable-database-snapshots-with-oracle-21c\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/pluggable-database-snapshots-with-oracle-21c\/","url":"https:\/\/www.dbi-services.com\/blog\/pluggable-database-snapshots-with-oracle-21c\/","name":"Pluggable Database Snapshots with Oracle 21c - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/pluggable-database-snapshots-with-oracle-21c\/#primaryimage"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/pluggable-database-snapshots-with-oracle-21c\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/02\/Picture1-2.png","datePublished":"2022-02-21T15:54:30+00:00","dateModified":"2023-06-20T08:04:36+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/pluggable-database-snapshots-with-oracle-21c\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/pluggable-database-snapshots-with-oracle-21c\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/pluggable-database-snapshots-with-oracle-21c\/#primaryimage","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/02\/Picture1-2.png","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/02\/Picture1-2.png"},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/pluggable-database-snapshots-with-oracle-21c\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Pluggable Database Snapshots with Oracle 21c"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee","name":"Oracle Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","caption":"Oracle Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/17134","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/27"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=17134"}],"version-history":[{"count":2,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/17134\/revisions"}],"predecessor-version":[{"id":26118,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/17134\/revisions\/26118"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=17134"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=17134"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=17134"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=17134"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}