dbi Blog

Creating a snapshopt copy Pluggable Database (PDB) on top of Automatic Storage Management (ASM) is not possible, because you need to have a filesystem which supports sparse files. According the following MOS-Note sparse files are supported on Automatic Storage Management Cluster File System (ACFS) since 12.2.:

12.2 Oracle ACFS Sparse Files Enhancements (Doc ID 2200264.1)

However, I thought it is more fun to create a PDB Snapshot Copy on a XFS-filesystem, which I wanted to create as a 3rd party filesystem on top of Oracle ASM Dynamic Volume Manager (ADVM) volumes.

REMARK1: See the steps at the end of this Blog on how to create a PDB Snapshot Copy on top of ACFS.
REMARK2: All tests have been done on the Oracle Cloud with a DB system running as a VM.

Here’s what I did:

1. Create a XFS-filesystem of size 20G on ASM diskgroup DATA

Create the volume MYXFSVOL on diskgroup DATA as grid:

grid@cbl:/home/grid/ [+ASM1] asmcmd volcreate -G DATA -s 20g MYXFSVOL
grid@cbl:/home/grid/ [+ASM1] asmcmd volinfo --all
Diskgroup Name: DATA

	 Volume Name: MYXFSVOL
	 Volume Device: /dev/asm/myxfsvol-187
	 State: ENABLED
	 Size (MB): 20480
	 Resize Unit (MB): 64
	 Redundancy: UNPROT
	 Stripe Columns: 8
	 Stripe Width (K): 1024
	 Usage: 
	 Mountpath:

Create the xfs-filesystem as root:

[root@cbl ~]# mkdir /u01/app/oracle/myxfs
[root@cbl ~]# mkfs.xfs /dev/asm/myxfsvol-187
meta-data=/dev/asm/myxfsvol-187  isize=256    agcount=4, agsize=1310720 blks
         =                       sectsz=4096  attr=2, projid32bit=1
         =                       crc=0        finobt=0, sparse=0
data     =                       bsize=4096   blocks=5242880, imaxpct=25
         =                       sunit=0      swidth=0 blks
naming   =version 2              bsize=4096   ascii-ci=0 ftype=1
log      =internal log           bsize=4096   blocks=2560, version=2
         =                       sectsz=4096  sunit=1 blks, lazy-count=1
realtime =none                   extsz=4096   blocks=0, rtextents=0
[root@cbl ~]# mount -t xfs /dev/asm/myxfsvol-187 /u01/app/oracle/myxfs
[root@cbl ~]# chown oracle:oinstall /u01/app/oracle/myxfs

2. Prepare the CDB for Snapshot Copy

SQL> connect / as sysdba
Connected.
SQL> show parameter clonedb

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
clonedb 			     boolean	 FALSE
clonedb_dir			     string

SQL> alter system set clonedb=true scope=spfile;

System altered.

and restart the DB:

oracle@cbl:/home/oracle/ [DB0111 (CDB$ROOT)] srvctl stop database -db DB0111_zrh13m
oracle@cbl:/home/oracle/ [DB0111 (CDB$ROOT)] srvctl start database -db DB0111_zrh13m

3. Create a PDB as a Read-Only master for the Snapshot Copy

SQL> create pluggable database pdb2 admin user pdbadmin identified by "manager";

Pluggable database created.

SQL> alter pluggable database pdb2 open;

Pluggable database altered.

SQL> alter session set container=pdb2;

Session altered.

SQL> create user cbleile identified by "in_reality_I_used_something_different";

User created.

SQL> grant connect, resource, dba to cbleile;

Grant succeeded.

SQL> create table cbleile.t1 (n number, m varchar2(100));

Table created.

SQL> insert into cbleile.t1 values (1,'Before Snapshot Copy created');

1 row created.

SQL> commit;

Commit complete.

SQL> connect / as sysdba
Connected.
SQL> alter pluggable database pdb2 close immediate;

Pluggable database altered.

SQL> alter pluggable database pdb2 open read only;

Pluggable database altered.

Here the file names of my read-only opened PDB:

SQL> select file_name from cdb_data_files where con_id=5;

FILE_NAME
-------------------------------------------------------------------------------------------
+DATA/DB0111_ZRH13M/C72C15BBCD038123E0530300000A0FAF/DATAFILE/system.288.1077982211
+DATA/DB0111_ZRH13M/C72C15BBCD038123E0530300000A0FAF/DATAFILE/sysaux.289.1077982211
+DATA/DB0111_ZRH13M/C72C15BBCD038123E0530300000A0FAF/DATAFILE/undotbs1.287.1077982211
+DATA/DB0111_ZRH13M/C72C15BBCD038123E0530300000A0FAF/DATAFILE/undotbs1_temp.286.1077982211

4. Create the Snapshot Copy:

SQL> create pluggable database pdb2sc from pdb2 snapshot copy file_name_convert=('+DATA/DB0111_ZRH13M/C72C15BBCD038123E0530300000A0FAF','/u01/app/oracle/myxfs');
create pluggable database pdb2sc from pdb2 snapshot copy file_name_convert=('+DATA/DB0111_ZRH13M/C72C15BBCD038123E0530300000A0FAF','/u01/app/oracle/myxfs')
*
ERROR at line 1:
ORA-65169: error encountered while attempting to copy file
+DATA/DB0111_ZRH13M/C72C15BBCD038123E0530300000A0FAF/DATAFILE/undotbs1_temp.286.1077982211
ORA-17515: Creation of clonedb failed using snapshot file
+DATA/DB0111_ZRH13M/C72C15BBCD038123E0530300000A0FAF/DATAFILE/undotbs1_temp.286.1077982211
ORA-17637: failed to change permission on file
'+DATA/DB0111_ZRH13M/C72C15BBCD038123E0530300000A0FAF/DATAFILE/undotbs1_temp.286.1077982211'
ORA-15304: operation requires ACCESS_CONTROL.ENABLED attribute to be TRUE

So obviously Oracle tried to set the files of the read only started PDB PDB2 explicitely to read only on ASM-level.

OK, let’s enable ACCESS_CONTROL:

grid@cbl:/home/grid/ [+ASM1] asmcmd
ASMCMD> setattr -G DATA access_control.enabled TRUE

It still doesn’t work, but now I get a permission denied error:

SQL> create pluggable database pdb2sc from pdb2 snapshot copy file_name_convert=('+DATA/DB0111_ZRH13M/C72C15BBCD038123E0530300000A0FAF','/u01/app/oracle/myxfs');
create pluggable database pdb2sc from pdb2 snapshot copy file_name_convert=('+DATA/DB0111_ZRH13M/C72C15BBCD038123E0530300000A0FAF','/u01/app/oracle/myxfs')
*
ERROR at line 1:
ORA-65169: error encountered while attempting to copy file
+DATA/DB0111_ZRH13M/C72C15BBCD038123E0530300000A0FAF/DATAFILE/undotbs1_temp.286.1077982211
ORA-17515: Creation of clonedb failed using snapshot file
+DATA/DB0111_ZRH13M/C72C15BBCD038123E0530300000A0FAF/DATAFILE/undotbs1_temp.286.1077982211
ORA-17637: failed to change permission on file
'+DATA/DB0111_ZRH13M/C72C15BBCD038123E0530300000A0FAF/DATAFILE/undotbs1_temp.286.1077982211'
ORA-15260: permission denied on ASM disk group

ASMCMD> pwd
+DATA/DB0111_ZRH13M/C72C15BBCD038123E0530300000A0FAF/DATAFILE
ASMCMD> ls --permission 
User  Group  Permission  Name
              rw-rw-rw-  SYSAUX.289.1077982211
              rw-rw-rw-  SYSTEM.288.1077982211
              rw-rw-rw-  UNDOTBS1.287.1077982211
              rw-rw-rw-  UNDOTBS1_TEMP.286.1077982211

So I have to create the User oracle in ASM for diskgroup DATA:

ASMCMD> mkusr DATA oracle

And then set the owner of the PDB2-datafiles to oracle:

ASMCMD> chown oracle SYSAUX.289.1077982211 SYSTEM.288.1077982211 UNDOTBS1.287.1077982211 UNDOTBS1_TEMP.286.1077982211
ASMCMD> ls --permission
User                                     Group  Permission  Name
oracle@0a5d56d4388b4f7bbf6da84da547ae9e          rw-rw-rw-  SYSAUX.289.1077982211
oracle@0a5d56d4388b4f7bbf6da84da547ae9e          rw-rw-rw-  SYSTEM.288.1077982211
oracle@0a5d56d4388b4f7bbf6da84da547ae9e          rw-rw-rw-  UNDOTBS1.287.1077982211
oracle@0a5d56d4388b4f7bbf6da84da547ae9e          rw-rw-rw-  UNDOTBS1_TEMP.286.1077982211
ASMCMD>

Let’s do it for the tempfile as well:

ASMCMD> cd ../TEMPFILE
ASMCMD> ls
TEMP.290.1077982223
ASMCMD> chown oracle TEMP.290.1077982223
ASMCMD> ls --permission
User                                     Group  Permission  Name
oracle@0a5d56d4388b4f7bbf6da84da547ae9e          rw-rw-rw-  TEMP.290.1077982223
ASMCMD>

Finally I just created the necessesary folders:

SQL> !mkdir /u01/app/oracle/myxfs/DATAFILE/
SQL> !mkdir /u01/app/oracle/myxfs/TEMPFILE/

And now it worked:

SQL> create pluggable database pdb2sc from pdb2 snapshot copy file_name_convert=('+DATA/DB0111_ZRH13M/C72C15BBCD038123E0530300000A0FAF','/u01/app/oracle/myxfs');

Pluggable database created.

SQL> alter pluggable database PDB2SC open;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 PDB1 			  READ WRITE NO
	 5 PDB2 			  READ ONLY  NO
	 6 PDB2SC			  READ WRITE NO

That looks promising. Let’s try it:

SQL> alter session set container=PDB2SC;

Session altered.

SQL> set lines 200 pages 999
SQL> column m format a32
SQL> select * from cbleile.t1;

         N M
---------- --------------------------------
         1 Before Snapshot Copy created

SQL> 
SQL> column file_name format a70
SQL> select file_name from dba_data_files;

FILE_NAME
----------------------------------------------------------------------
/u01/app/oracle/myxfs/DATAFILE/system.288.1077982211
/u01/app/oracle/myxfs/DATAFILE/sysaux.289.1077982211
/u01/app/oracle/myxfs/DATAFILE/undotbs1.287.1077982211
/u01/app/oracle/myxfs/DATAFILE/undotbs1_temp.286.1077982211

SQL> 
SQL> !ls -lhs /u01/app/oracle/myxfs/DATAFILE
total 196K
116K -rw-r----- 1 oracle asmadmin 401M Jul 15 16:02 sysaux.289.1077982211
 48K -rw-r----- 1 oracle asmadmin 291M Jul 15 16:02 system.288.1077982211
 16K -rw-r----- 1 oracle asmadmin  46M Jul 15 16:02 undotbs1.287.1077982211
 16K -rw-r----- 1 oracle asmadmin 3.2M Jul 15 16:02 undotbs1_temp.286.1077982211

SQL>

–> Cool, the files are only a couple of KB in size.

SQL> insert into cbleile.t1 values (2,'After creating the Snapshot Copy');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from cbleile.t1;

         N M
---------- --------------------------------
         1 Before Snapshot Copy created
         2 After creating the Snapshot Copy

SQL>

Summary: You can create a Snapshot Copy Pluggable Database on top of ASM by using a filesystem which supports sparse files. If your read-only source-PDB is on ASM then you have to enable access control in ASM and set oracle as the owner of the datafiles of the read-only source-PDB.

REMARK: Above tests with XFS have been done on a single instance setup. Running your CDB in RAC then ACFS as the filesystem for the Snapshot Copy PDB should to be used.

For completness below the steps to create the Snapshot Copy on top of ACFS (as I used the same Read-Only source-PDB, I didn’t have to set permissions again):

grid@cbl:/home/grid/ [+ASM1] asmcmd volcreate -G DATA -s 20g MYACFSVOL
grid@cbl:/home/grid/ [+ASM1] asmcmd volinfo --all
Diskgroup Name: DATA

	 Volume Name: MYACFSVOL
	 Volume Device: /dev/asm/myacfsvol-187
	 State: ENABLED
	 Size (MB): 20480
	 Resize Unit (MB): 64
	 Redundancy: UNPROT
	 Stripe Columns: 8
	 Stripe Width (K): 1024
	 Usage: 
	 Mountpath: 
 
grid@cbl:/home/grid/ [+ASM1] 

[root@cbl ~]# mkdir /u01/app/oracle/myacfs
[root@cbl ~]# mkfs.acfs /dev/asm/myacfsvol-187
mkfs.acfs: version                   = 21.0.0.0.0
mkfs.acfs: on-disk version           = 50.0
mkfs.acfs: volume                    = /dev/asm/myacfsvol-187
mkfs.acfs: volume size               = 21474836480  (  20.00 GB )
mkfs.acfs: file system size          = 21474836480  (  20.00 GB )
mkfs.acfs: Format complete.
[root@cbl ~]#

REMARK: For simplicity I used the same commands to create the ACFS as with XFS above. To register it in the cluster registry you could use “srvctl add filesystem” or add it manually with “acfsutil registry”.

[root@cbl ~]# mount -t acfs /dev/asm/myacfsvol-187 /u01/app/oracle/myacfs
[root@cbl ~]# chown oracle:oinstall /u01/app/oracle/myacfs

oracle@cbl:/home/oracle/ [DB0111 (CDB$ROOT)] sqlplus / as sysdba

SQL> !ls -l /u01/app/oracle/myacfs
total 64
drwx------ 2 root root 65536 Jul 15 17:30 lost+found

SQL> !mkdir /u01/app/oracle/myacfs/DATAFILE

SQL> !mkdir /u01/app/oracle/myacfs/TEMPFILE

SQL> create pluggable database pdb2scacfs from pdb2 snapshot copy file_name_convert=('+DATA/DB0111_ZRH13M/C72C15BBCD038123E0530300000A0FAF','/u01/app/oracle/myacfs');

Pluggable database created.

SQL> alter pluggable database pdb2scacfs open;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 PDB1 			  READ WRITE NO
	 4 PDB2SCACFS			  READ WRITE NO
	 5 PDB2 			  READ ONLY  NO
	 6 PDB2SC			  READ WRITE NO

SQL> !ls -lhs /u01/app/oracle/myacfs/DATAFILE
total 208K
116K -rw-r----- 1 oracle asmadmin 401M Jul 15 17:34 sysaux.289.1077982211
 52K -rw-r----- 1 oracle asmadmin 291M Jul 15 17:34 system.288.1077982211
 20K -rw-r----- 1 oracle asmadmin  46M Jul 15 17:34 undotbs1.287.1077982211
 20K -rw-r----- 1 oracle asmadmin 3.2M Jul 15 17:34 undotbs1_temp.286.1077982211

SQL> alter session set container=PDB2SCACFS;

Session altered.

SQL> set lines 200 pages 999
SQL> column m format a50
SQL> select * from cbleile.t1;

	 N M
---------- --------------------------------------------------
	 1 Before Snapshot Copy created

SQL> insert into cbleile.t1 values (2,'After creating a Snapshot Copy on top of ACFS');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from cbleile.t1;

	 N M
---------- --------------------------------------------------
	 1 Before Snapshot Copy created
	 2 After creating a Snapshot Copy on top of ACFS

SQL>

Post Views: 2,289

Creating an Oracle Snapshot Copy PDB on top of ASM

Clemens Bleile

Leave a Reply:

Related blog articles

Clemens Bleile

Leave a Reply:

Related blog articles

ODA X10-L storage configuration is different from what you may expect

ODA patching: ASR manager stuck to old version

REKEY operation on Oracle Database configured with Oracle Key Vault

ODA X10 lineup now supports Standard Edition 2