More database cloning magic with Oracle multi-tenancy and dNFS

Background

One of the most exciting announcements at last years Oracle Open World #OOW19 was around multi-tenancy and Oracle change of licensing allowing non multi-tenant users to have up to 3 user-created PDB’s in a container database at any time.

https://docs.oracle.com/en/database/oracle/oracle-database/19/dblic/Licensing-Information.html#GUID-AB354617-6614-487E-A022-7FC9A5A08472

With the above, and the announcement of de-support of non-CDB architecture from 20c there has never been a better time to start migrating to use PDB’s, so with that in mind let’s start.

Database Environment

As you can see from the below I have a test 242GB Pluggable database (PDB) called pdb1 on a dNFS filesystem.

operating system view of datafiles

$ ls -lh /u03/oracle/oradata/PSTG/pdb1
total 236G
-rw-r----- 1 oracle dba  11G Jan 13 10:04 example01.dbf
-rw-r----- 1 oracle dba 371M Jan 13 15:17 sysaux01.dbf
-rw-r----- 1 oracle dba 291M Jan 13 15:49 system01.dbf
-rw-r----- 1 oracle dba 1.4G Jan 13 09:58 temp012019-09-04_13-26-08-038-PM.dbf
-rw-r----- 1 oracle dba 101G Jan 13 10:07 tpcctab.dbf
-rw-r----- 1 oracle dba 101G Jan 13 10:04 tpchtab.dbf
-rw-r----- 1 oracle dba  24G Jan 13 15:49 undotbs01.dbf
-rw-r----- 1 oracle dba 5.1M Jan 13 10:04 users01.dbf
$ df -h /u03/oracle/oradata/PSTG/pdb1
Filesystem                  Size  Used Avail Use% Mounted on
192.168.4.100:/z-rac2_pstg  1.0T  242G  783G  24% /u03
$ du -hs /u03/oracle/oradata/PSTG/pdb1
236G /u03/oracle/oradata/PSTG/pdb1

Before we clone our pluggable database, let’s check its size on the storage platform, from below we can see our FlashBlade had provided a Data Reduction of 3.9 to 1.

FlashBlade view of pdb1

Connect to container database

$ sqlplus / as sysdba

Show connection Name

SQL> SHOW CON_NAME
CON_NAME
------------------------------
CDB$ROOT

oracle CLONEDB parameters

SQL> SHOW PARAMETER CLONEDB 
NAME         TYPE    VALUE
------------ ------- ------------
clonedb      boolean FALSE
clonedb_dir  string

The clonedb parameter is required on dNFS clients to enable the use read-only backup / snapshots files for thin provisioned clones, by default the value is FALSE.

As we plan to test the use of snapshot clones we need to check the clonedb parameter is TRUE to avoid the ORA-17525 error message.

ORA-17525: Database clone using storage snapshot not supported on file

Note, the clonedb parameter does not support dynamic setting and therefore the will requrire a database restart.

SQL> ALTER SYSTEM SET CLONEDB=TRUE SCOPE=SPFILE;
SQL> SHOW PARAMETER CLONEDB 
NAME         TYPE     VALUE
------------ -------- ------------
clonedb      boolean  TRUE
clonedb_dir  string

The clonedb_dir specifies where the CloneDB bitmap file are created, by default it’s under the $ORACLE_HOME/dbs directory.

Show status of pluggable databases

SQL> SHOW PDBS
    CON_ID CON_NAME   OPEN MODE  RESTRICTED
---------- ---------- ---------- ----------
         2 PDB$SEED   READ ONLY  NO
         3 PDB1       READ WRITE NO

We can confirm our database is using dNFS with the v$dnfs_files view, if your database is on an NFS mount point but you don’t see any data files you need to check your configuration, you may find this Blog helpful in getting you back on track.

oracle dnfs datafiles

column filename heading 'File Name' format a90
column filesize heading 'File Size MB' format a10

select filename, to_char(filesize/1024/1024,'9,999,999') 
as filesize from v$dnfs_files;
dNFS pdb1 datafiles

Create Pluggable Database (pdb2)

Let’s start by using CREATE PLUGGABLE DATABASE, I will use the rather nifty FILE_NAME_CONVERT to create my new pdb in an empty separate file system again using dNFS.

$ df -h /u04/oracle/oradata/PSTG/pdb2
Filesystem                       Size  Used Avail Use% Mounted on
192.168.4.100:/z-rac2_pluggable2 1.0T     0  1.0T   0% /u04

You need to be in the Container database to create a pluggable database, if not you will get an ORA-65040.

ORA-65040: operation not allowed from within a pluggable database
SQL> CREATE PLUGGABLE DATABASE pdb2 FROM pdb1 FILE_NAME_CONVERT = ('/u03/oracle/oradata/PSTG/pdb1','/u04/oracle/oradata/PSTG/pdb2');
Pluggable database created.

show status of pluggable database

SQL> SHOW PDBS 
    CON_ID CON_NAME   OPEN MODE  RESTRICTED
---------- ---------- ---------- ----------
         2 PDB$SEED   READ ONLY  NO
         3 PDB1       READ WRITE NO
         4 PDB2       MOUNTED

operating system view of datafiles

$ ls -lh /u04/oracle/oradata/PSTG/pdb2/
total 236G
-rw-r----- 1 oracle dba  11G Jan 13 16:38 example01.dbf
-rw-r----- 1 oracle dba 371M Jan 13 16:38 sysaux01.dbf
-rw-r----- 1 oracle dba 291M Jan 13 16:38 system01.dbf
-rw-r----- 1 oracle dba 1.4G Jan 13 16:38 temp012019-09-04_13-26-08-038-PM.dbf
-rw-r----- 1 oracle dba 101G Jan 13 16:38 tpcctab.dbf
-rw-r----- 1 oracle dba 101G Jan 13 16:38 tpchtab.dbf
-rw-r----- 1 oracle dba  24G Jan 13 16:38 undotbs01.dbf
-rw-r----- 1 oracle dba 5.1M Jan 13 16:38 users01.dbf
$ du -h /u04/oracle/oradata/PSTG/pdb2
236G /u04/oracle/oradata/PSTG/pdb2
$ df -h /u04/oracle/oradata/PSTG/pdb2
Filesystem                        Size  Used Avail Use% Mounted on
192.168.4.100:/z-rac2_pluggable2  1.0T  234G  791G  23% /u04
FlashBlade view of pdb2

Open Pluggable Database

Once we have opened our pdb we should be able to see our datafiles on dNFS using the previous query.

SQL> ALTER PLUGGABLE DATABASE pdb2 OPEN;
SQL> SHOW PDBS 
    CON_ID CON_NAME   OPEN MODE  RESTRICTED
---------- ---------- ---------- ----------
         2 PDB$SEED   READ ONLY  NO
         3 PDB1       READ WRITE NO
         4 PDB2       READ WRITE NO

oracle dnfs status

If repeat our previous query against v$dnfs_files we can see our newly cloned datafiles.

dNFS pdb2 datafiles

Create Pluggable Database (pdb3)

OK, now we are going to create another pluggable database clone, however this time we will be using the ‘SNAPSHOT COPY’ option.

Before we can create a clone from our local source pdb we need to close and re-open it read-only to avoid ORA-65081.

ORA-65081: database or pluggable database is not open in read only mode
SQL> ALTER PLUGGABLE DATABASE pdb1 CLOSE;
Pluggable database altered.

open pluggable database read only

SQL> ALTER PLUGGABLE DATABASE pdb1 OPEN READ ONLY;
Pluggable database altered.
SQL> SHOW PDBS
    CON_ID CON_NAME   OPEN MODE  RESTRICTED
---------- ---------- ---------- ----------
         2 PDB$SEED   READ ONLY  NO
         3 PDB1       READ ONLY  NO
         4 PDB2       READ WRITE NO

create pluggable database

CREATE PLUGGABLE DATABASE pdb3 FROM pdb1 SNAPSHOT COPY
FILE_NAME_CONVERT =('/u03/oracle/oradata/PSTG/pdb1','/u05/oracle/oradata/PSTG/pdb3');
Pluggable database created.
SQL> SHOW PDBS
    CON_ID CON_NAME   OPEN MODE  RESTRICTED
---------- ---------- ---------- ----------
         2 PDB$SEED   READ ONLY  NO
         3 PDB1       READ ONLY  NO
         4 PDB2       READ WRITE NO
         4 PDB3       MOUNTED
SQL> ALTER PLUGGABLE DATABASE pdb3 OPEN;
set feed off
set pages 100
set linesize 250

column SNAPSHOTFILENAME heading 'Snapshot File Name' format a60
column CLONEFILENAME heading 'Clone File Name' format a60
column BLOCKS_ALLOCATED heading 'Blocks Allocated'

select SNAPSHOTFILENAME, CLONEFILENAME 
from v$clonedfile;
v$clonedfile

OPERATING SYSTEM VIEW OF DATAFILES

$ ls -lh /u05/oracle/oradata/PSTG/pdb3
total 236G
-rw-r----- 1 oracle dba  11G Jan 27 14:48 example01.dbf
-rw-r----- 1 oracle dba 371M Jan 27 14:48 sysaux01.dbf
-rw-r----- 1 oracle dba 291M Jan 27 14:48 system01.dbf
-rw-r----- 1 oracle dba 1.4G Jan 27 14:45 temp012019-09-04_13-26-08-038-PM.dbf
-rw-r----- 1 oracle dba 101G Jan 27 14:48 tpcctab.dbf
-rw-r----- 1 oracle dba 101G Jan 27 14:48 tpchtab.dbf
-rw-r----- 1 oracle dba  24G Jan 27 14:48 undotbs01.dbf
-rw-r----- 1 oracle dba 5.1M Jan 27 14:48 users01.dbf
$ du -h /u05/oracle/oradata/PSTG/pdb3
236G /u05/oracle/oradata/PSTG/pdb3
$ df -h /u05/oracle/oradata/PSTG/pdb3
Filesystem                        Size  Used Avail Use% Mounted on
192.168.4.100:/z-rac2_pluggable3  1.0T  2.5M  1.0T   1% /u05
FlashBlade view of pdb3

We can see from the above the CLONEDB parameter has delivered a space efficient clone without using

Pluggable Clean-Up

close pluggable database

You can’t drop an open pluggable database, if you try to do this you will get an ORA-65025 error, so will will close the pluggable database before we drop it.

ERROR at line 1:
ORA-65025: Pluggable database PDB2 is not closed on all instances.

Close Pluggable Database(S)

SQL> ALTER PLUGGABLE DATABASE pdb2 CLOSE;
Pluggable database altered.
SQL> ALTER PLUGGABLE DATABASE pdb3 CLOSE;
Pluggable database altered.
SQL> SHOW PDBS
    CON_ID CON_NAME   OPEN MODE  RESTRICTED
---------- ---------- ---------- ----------
         2 PDB$SEED   READ ONLY  NO
         3 PDB1       READ ONLY  NO
         4 PDB2       MOUNTED
         5 PDB3       MOUNTED

Drop Pluggable Database(S)

SQL> DROP PLUGGABLE DATABASE pdb2 INCLUDING DATAFILES;
Pluggable database dropped.
SQL> DROP PLUGGABLE DATABASE pdb3 INCLUDING DATAFILES;
Pluggable database dropped.

STATUS OF PLUGGABLE DATABASE

SQL> SHOW PDBS 
    CON_ID CON_NAME   OPEN MODE  RESTRICTED
---------- ---------- ---------- ----------
         2 PDB$SEED   READ ONLY  NO
         3 PDB1       READ ONLY NO
SQL> ALTER PLUGGABLE DATABASE pdb1 CLOSE;
Pluggable database altered.
SQL> ALTER PLUGGABLE DATABASE pdb1 OPEN;
ALTER PLUGGABLE DATABASE pdb1 OPEN
*
ERROR at line 1:
ORA-01114: IO error writing block to file 12 (block # 1)
ORA-17500: ODM err:Invalid argument
ORA-01114: IO error writing block to file 11 (block # 1)
ORA-17500: ODM err:Invalid argument
ORA-17528: A read-only file or a file opened read-only cannot be written to:
/u03/oracle/oradata/PSTG/pdb1/tpchtab.dbf.

If we check the operating system we can see Oracle has changed the file permissions to ‘READ ONLY’ but did not change it back once the Snapshot Copy had been dropped. This is a known issue and documented on MOS 2419236.1

SQL> !ls -l /u03/oracle/oradata/PSTG/pdb1/tpchtab.dbf
-r--r----- 1 oracle dba 107374190592 Jan 27 14:42 /u03/oracle/oradata/PSTG/pdb1/tpchtab.dbf

Unable to Open the ‘Source PDB’ in ‘READ WRITE’ Mode After Dropping its ‘Snapshot Copy PDB’. It fails with ORA-01114, ORA-01110 and ORA-27091 (Doc ID 2419236.1)

The Oracle provided workaround is to change the file permissions back to 640 and try again.

$ chmod 640 /u03/oracle/oradata/PSTG/pdb1/*.dbf
SQL> ALTER PLUGGABLE DATABASE pdb1 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

If you have not yet migrated to the multi-tenant container database architecture and are using Oracle dNFS you can still create space efficient database clones using the Oracle CloneDB perl script, you can read all about that here.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s