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.
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.
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;
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
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.
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;
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
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.
[twitter-follow screen_name=’RonEkins’ show_count=’yes’]