Martins Blog

Trying to explain complex things in simple terms

Automatic Deployment of CDBs with non-default database options in 12.1.0.2

Posted by Martin Bach on June 20, 2016

I have written about the importance of automation in previous posts, and this one is following the same line. This time I wanted to test and document my experience with “golden database templates”. Wy? Because most DBAs will appreciate that there are more interesting things to do than to run “create database” scripts all day, so why not automate the process? For quite some time DBCA, or the Database Creation Assistant offers you the option to create templates. One approach you could use would be to

  • Create a “golden master” database
  • Use DBCA to create a template based on this
  • And enjoy the fact that every database is exactly the same

All of this is done in hope to make life for the administrators a lot easier.

Motivation

In many environments a database is not an island, it’s rather part of a wider ecosystem where monitoring solutions, auditing, and what-have-you need to integrate with each database from the moment it is deployed. A template – at least to my mind – is better suited to this task than executing a number of scripts post database creation. SQL scripts can always run into errors, and their execution might require review of the output by a human (which is what I’d like to avoid). Oh, and it seems to take forever, even on reasonably modern hardware, to create a database from scripts.

Furthermore, it is possible to create a CDB with fewer options in 12.1.0.2, as explained by Mike Dietrich and on MOS. I wanted to get a reference to myself on how to deploy a CDB exactly the same way, each time, without all the options in it. Please have a look at these references for more background information.

All of them are very good references, and they contain the necessary “ifs” and “buts”. Please make sure you understand them all, especially those in MOS DOC ID 1616554.1, section 1. If the referenced documents resonate with you, and the pros/cons are understood, read on :)

Further to the introductory question on why to do this, there is more to mention: I don’t want any extra components in my database, for 2 main reasons:

  1. A component that is not installed can’t be used as an attack vector
  2. Components that aren’t installed in the RDBMS can’t be used accidentally

And there are many more … For the purpose of this post I am going to create a database with all the extra components I ever need: none.

In case you found this via an Internet search engine, here are the facts about my environment.

[oracle@server1 OPatch]$ ./opatch lspatches
22674709;Database PSU 12.1.0.2.160419, Oracle JavaVM Component (Apr2016)
22291127;Database Patch Set Update : 12.1.0.2.160419 (22291127)

OPatch succeeded.
[oracle@server1 OPatch]$ cat /etc/oracle-release
Oracle Linux Server release 6.8
[oracle@server1 OPatch]$ uname -r -o
4.1.12-37.4.1.el6uek.x86_64 GNU/Linux

This is not a RAC nor Oracle Restart database, just a single RDBMS home with the databases on traditional file system. This is a VM running on VirtualBox 5.0.20 in my lab.

Create the Golden Image database

Following MOS note 2001512.1 and understanding the comments in each section I consciously make the decision to remove everything that’s not needed for my purposes (these aren’t necessarily yours!) I decided to create the scripts for a CDB named CDBTEMPL. After exiting DBCA I can find these scripts in /u01/app/oracle/admin/CDBTEMPL/scripts/

[oracle@server1 scripts]$ ls -l
total 72
-rwxr-xr-x. 1 oracle oinstall  834 Jun 15 08:31 CDBTEMPL.sh
-rwxr-xr-x. 1 oracle oinstall 1260 Jun 15 08:31 CDBTEMPL.sql
-rw-r-----. 1 oracle oinstall  550 Jun 15 08:31 CreateClustDBViews.sql
-rw-r-----. 1 oracle oinstall 1552 Jun 15 08:31 CreateDB.sql
-rw-r-----. 1 oracle oinstall 1668 Jun 15 08:31 CreateDBCatalog.sql
-rw-r-----. 1 oracle oinstall  397 Jun 15 08:31 CreateDBFiles.sql
-rw-r-----. 1 oracle oinstall 1171 Jun 15 08:31 JServer.sql
-rw-r-----. 1 oracle oinstall  386 Jun 15 08:31 apex.sql
-rw-r-----. 1 oracle oinstall  998 Jun 15 08:31 context.sql
-rw-r-----. 1 oracle oinstall  353 Jun 15 08:31 cwmlite.sql
-rw-r-----. 1 oracle oinstall  365 Jun 15 08:31 datavault.sql
-rw-r-----. 1 oracle oinstall 2011 Jun 15 08:31 init.ora
-rw-r-----. 1 oracle oinstall  342 Jun 15 08:31 interMedia.sql
-rw-r-----. 1 oracle oinstall  344 Jun 15 08:31 labelSecurity.sql
-rw-r-----. 1 oracle oinstall 1155 Jun 15 08:31 lockAccount.sql
-rw-r-----. 1 oracle oinstall  360 Jun 15 08:31 ordinst.sql
-rw-r-----. 1 oracle oinstall  857 Jun 15 08:31 postDBCreation.sql
-rw-r-----. 1 oracle oinstall  335 Jun 15 08:31 spatial.sql

I need to update the $ORACLE_SID.sql file, which originally looks like this:

set verify off
ACCEPT sysPassword CHAR PROMPT 'Enter new password for SYS: ' HIDE
ACCEPT systemPassword CHAR PROMPT 'Enter new password for SYSTEM: ' HIDE
ACCEPT dvOwnerPassword CHAR PROMPT 'Enter  user password: ' HIDE
ACCEPT dvAccountManagerPassword CHAR PROMPT 'Enter  user password: ' HIDE
host /u01/app/oracle/product/12.1.0.2/dbhome_1/bin/orapwd file=/u01/app/oracle/product/12.1.0.2/dbhome_1/dbs/orapwCDBTEMPL force=y format=12
@/u01/app/oracle/admin/CDBTEMPL/scripts/CreateDB.sql
@/u01/app/oracle/admin/CDBTEMPL/scripts/CreateDBFiles.sql
@/u01/app/oracle/admin/CDBTEMPL/scripts/CreateDBCatalog.sql
@/u01/app/oracle/admin/CDBTEMPL/scripts/JServer.sql
@/u01/app/oracle/admin/CDBTEMPL/scripts/context.sql
@/u01/app/oracle/admin/CDBTEMPL/scripts/ordinst.sql
@/u01/app/oracle/admin/CDBTEMPL/scripts/interMedia.sql
@/u01/app/oracle/admin/CDBTEMPL/scripts/cwmlite.sql
@/u01/app/oracle/admin/CDBTEMPL/scripts/spatial.sql
@/u01/app/oracle/admin/CDBTEMPL/scripts/labelSecurity.sql
@/u01/app/oracle/admin/CDBTEMPL/scripts/apex.sql
@/u01/app/oracle/admin/CDBTEMPL/scripts/datavault.sql
@/u01/app/oracle/admin/CDBTEMPL/scripts/CreateClustDBViews.sql
@/u01/app/oracle/admin/CDBTEMPL/scripts/lockAccount.sql
@/u01/app/oracle/admin/CDBTEMPL/scripts/postDBCreation.sql

Using the very useful dependency matrix in MOS note 2001512.1 I can safely remove all components I don’t need for my particular case (again yours might be different). The resulting file looks like this for me:

set verify off
ACCEPT sysPassword CHAR PROMPT 'Enter new password for SYS: ' HIDE
ACCEPT systemPassword CHAR PROMPT 'Enter new password for SYSTEM: ' HIDE
ACCEPT dvOwnerPassword CHAR PROMPT 'Enter  user password: ' HIDE
ACCEPT dvAccountManagerPassword CHAR PROMPT 'Enter  user password: ' HIDE
host /u01/app/oracle/product/12.1.0.2/dbhome_1/bin/orapwd file=/u01/app/oracle/product/12.1.0.2/dbhome_1/dbs/orapwCDBTEMPL force=y format=12
@/u01/app/oracle/admin/CDBTEMPL/scripts/CreateDB.sql
@/u01/app/oracle/admin/CDBTEMPL/scripts/CreateDBFiles.sql
@/u01/app/oracle/admin/CDBTEMPL/scripts/CreateDBCatalog.sql
@/u01/app/oracle/admin/CDBTEMPL/scripts/CreateClustDBViews.sql
@/u01/app/oracle/admin/CDBTEMPL/scripts/lockAccount.sql
@/u01/app/oracle/admin/CDBTEMPL/scripts/postDBCreation.sql

Another VERY important aspect is the use of ORACLE’s perl. You need to make sure that your PATH uses oracle’s perl, and not the one installed with the host. In other words, “which perl” typed into the command prompt should return /u01/app/oracle/product/12.1.0.2/dbhome_1/perl/bin/perl, as shown in this example:

[oracle@server1 ~]$ export PATH=$ORACLE_HOME/perl/bin:$PATH
[oracle@server1 ~]$ which perl
/u01/app/oracle/product/12.1.0.2/dbhome_1/perl/bin/perl

If you are on a recent chip and want try these steps in Virtualbox like me, you might run into issues with perl core-dumping on you. Have a look at http://dbaontap.com/2016/01/13/vbox5/ before you start to see if you are susceptible to the same problem.

When you are ready, start the database creation in a screen session.

[oracle@server1 dbs]$ cd /u01/app/oracle/admin/CDBTEMPL/scripts/
[oracle@server1 scripts]$ export PATH=$ORACLE_HOME/perl/bin:$PATH
[oracle@server1 scripts]$ which perl
/u01/app/oracle/product/12.1.0.2/dbhome_1/perl/bin/perl
[oracle@server1 scripts]$ bash CDBTEMPL.sh

Don’t be startled by having to enter a password for USER twice, this is a slight irregularity with the script, looking at the source it appears it’s prompting you for Data Vault and Oracle Label Security information:

ACCEPT sysPassword CHAR PROMPT 'Enter new password for SYS: ' HIDE
ACCEPT systemPassword CHAR PROMPT 'Enter new password for SYSTEM: ' HIDE
ACCEPT dvOwnerPassword CHAR PROMPT 'Enter  user password: ' HIDE
ACCEPT dvAccountManagerPassword CHAR PROMPT 'Enter  user password: ' HIDE

The last 2 don’t quite make sense, and going by the extra space character there should have been a username …

Once the script has been started, go and get some coffee. It will take a while to finish. After it completed, it’s time to see what we have. The alias “sq” is just a shortcut for “sqlplus / as sysdba”.

[oracle@server1 ~]$ sq

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jun 15 10:20:10 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO

SQL> select comp_name, version, status from dba_server_registry;

COMP_NAME                                VERSION                        STATUS
---------------------------------------- ------------------------------ -----------
Oracle Workspace Manager                 12.1.0.2.0                     VALID
Oracle XML Database                      12.1.0.2.0                     VALID
Oracle Database Catalog Views            12.1.0.2.0                     VALID
Oracle Database Packages and Types       12.1.0.2.0                     VALID
Oracle Real Application Clusters         12.1.0.2.0                     OPTION OFF

SQL> select cdb, name from v$database;

CDB NAME
--- ---------
YES CDBTEMPL

SQL> select * From v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
SYS                            TRUE  TRUE  FALSE FALSE FALSE FALSE          0
SYSDG                          FALSE FALSE FALSE FALSE TRUE  FALSE          1
SYSBACKUP                      FALSE FALSE FALSE TRUE  FALSE FALSE          1
SYSKM                          FALSE FALSE FALSE FALSE FALSE TRUE           1

So this looks like it worked in my favour: the database is created, and I can merely see the bare minimum of options present. Well actually it seems that you could potentially also remove workspace manager and the RAC views, but they are ok for me to keep. Refer to MOS for more details about these two.

The database is in fact a CDB, and it’s name is CDBTEMPL. All the users I might want are in the password file as well.

Customising the Golden Image

First I want a new PDB that I use as en lieu of the PDB$SEED as the basis for new PDBs. Let’s create it:

SQL> !mkdir /u01/oradata/CDBTEMPL/MASTER

SQL> create pluggable database MASTER admin user PDBMASTER identified by PDBMASTERPWD
  2  default tablespace users
  3  datafile '/u01/oradata/CDBTEMPL/MASTER/users01.dbf'
  4  size 250m autoextend on next 250m maxsize 1G
  5  file_name_convert = ('/u01/oradata/CDBTEMPL/pdbseed','/u01/oradata/CDBTEMPL/MASTER')
  6  storage (maxsize 20G);

Pluggable database created.

While I’m still connected to the CDB$ROOT I create a few common user accounts that I need:

SQL> create user c##backupadmin identified by backupadminpwd;

User created.

SQL> grant sysbackup to c##backupadmin;

Grant succeeded.

SQL> create user c##monitor identified by monitorpassword;

User created.

-- grants as needed for the monitoring account

Switching to the MASTER-PDB in the next step I create components that I might need, for example STATSPACK or SQLT. Or anything else your standards require you to put in there. For example:

SQL> alter session set container = MASTER;
Session altered.
SQL> show con_name

CON_NAME
------------------------------
MASTER

SQL> create tablespace statpack_tbs datafile
  2  '/u01/oradata/CDBTEMPL/MASTER/statpack_tbs01.dbf'
  3  size 250m autoextend on next 250m maxsize 5g;

Tablespace created.

SQL> start ?/rdbms/admin/spcreate

Choose the Default tablespace for the PERFSTAT user
---------------------------------------------------
Below is the list of online tablespaces in this database which can
store user data.  Specifying the SYSTEM tablespace for the user's
default tablespace will result in the installation FAILING, as
using SYSTEM for performance data is not supported.

Choose the PERFSTAT users's default tablespace.  This is the tablespace
in which the STATSPACK tables and indexes will be created.

TABLESPACE_NAME                CONTENTS  STATSPACK DEFAULT TABLESPACE
------------------------------ --------- ----------------------------
STATPACK_TBS                   PERMANENT
SYSAUX                         PERMANENT *
USERS                          PERMANENT

Pressing <return> will result in STATSPACK's recommended default
tablespace (identified by *) being used.

Enter value for default_tablespace: STATPACK_TBS

Using tablespace STATPACK_TBS as PERFSTAT default tablespace.

Choose the Temporary tablespace for the PERFSTAT user
-----------------------------------------------------
Below is the list of online tablespaces in this database which can
store temporary data (e.g. for sort workareas).  Specifying the SYSTEM
tablespace for the user's temporary tablespace will result in the
installation FAILING, as using SYSTEM for workareas is not supported.

Choose the PERFSTAT user's Temporary tablespace.

TABLESPACE_NAME                CONTENTS  DB DEFAULT TEMP TABLESPACE
------------------------------ --------- --------------------------
TEMP                           TEMPORARY *

Pressing <return> will result in the database's default Temporary
tablespace (identified by *) being used.

Enter value for temporary_tablespace: TEMP

Using tablespace TEMP as PERFSTAT temporary tablespace.

... Creating PERFSTAT user

Create the Template

With the Golden Image complete, it is time to create a template from this. You might think that all you need to do is to execute dbca and create a clone template but wait! I did this too but ran into an issue. Fast forward a bit (don’t worry I’ll detail the steps later), when I created a database off the template, dbca errored out with this message:

[oracle@server1 ~]$ dbca -silent -createDatabase -templateName CDB_MASTER_20160615.dbc \
> -gdbName CDB1 -sysPassword sysPassword -systemPassword systemPassword \
> -datafileDestination /u01/oradata -recoveryAreaDestination /u01/fra -totalMemory 4096
Copying database files
1% complete
3% complete
37% complete
Creating and starting Oracle instance
40% complete
45% complete
50% complete
55% complete
DBCA Operation failed.
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/CDB1/CDB1.log" for further details.

[oracle@server1 ~]$ cat /u01/app/oracle/cfgtoollogs/dbca/CDB1/CDB1.log

Unique database identifier check passed.

/u01/ has enough space. Required space is 8660 MB , available space is 14332 MB.
File Validations Successful.
Copying database files
DBCA_PROGRESS : 1%
DBCA_PROGRESS : 3%
DBCA_PROGRESS : 37%
Creating and starting Oracle instance
DBCA_PROGRESS : 40%
DBCA_PROGRESS : 45%
DBCA_PROGRESS : 50%
DBCA_PROGRESS : 55%
Error while executing "/u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/admin/dbmssml.sql".
Refer to "/u01/app/oracle/cfgtoollogs/dbca/CDB1/dbmssml0.log" for more details.
Error in Process: /u01/app/oracle/product/12.1.0.2/dbhome_1/perl/bin/perl
DBCA_PROGRESS : DBCA Operation failed.

Hmmm, not too good. Funny enough there was no content in the log file mentioned in the error message, but there was a similarly named file nearby:

[oracle@server1 ~]$ ls -l /u01/app/oracle/cfgtoollogs/dbca/CDB1/dbmssml*
-rw-r-----. 1 oracle oinstall   0 Jun 15 11:51 /u01/app/oracle/cfgtoollogs/dbca/CDB1/dbmssml0.log
-rw-r-----. 1 oracle oinstall 521 Jun 15 11:51 /u01/app/oracle/cfgtoollogs/dbca/CDB1/dbmssml_catcon_11711.lst
[oracle@server1 ~]$

Looking at dbmssml_catcon_11711.lst I saw this message:

[oracle@server1 ~]$ cat /u01/app/oracle/cfgtoollogs/dbca/CDB1/dbmssml_catcon_11711.lst
catcon: See /u01/app/oracle/cfgtoollogs/dbca/CDB1/dbmssml*.log files for output generated by scripts
catcon: See /u01/app/oracle/cfgtoollogs/dbca/CDB1/dbmssml_*.lst files for spool files, if any
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

        catconInit: start logging catcon output at 2016-06-15 11:51:11

!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

validate_con_names: MASTER is not open
catconInit: Unexpected error returned by validate_con_names

So it seems I have to make sure that the clone database opens the MASTER PDB as part of the process. By default, a PDB is in mount mode when the database instance (re)starts. But I can force it to open. That’s not hard since 12.1.0.2, I can save a PDB’s state like this:

SQL> alter pluggable database master open;

Pluggable database altered.

SQL> alter pluggable database master save state;

Pluggable database altered.

SQL> SELECT con_name, instance_name, state FROM dba_pdb_saved_states;

CON_NAME                       INSTANCE_NAME        STATE
------------------------------ -------------------- --------------
MASTER                         CDBTEMPL             OPEN

That’s it-a quick test reveals that the MASTER PDB opens when I bounce the instance:

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  2932632 bytes
Variable Size             377487464 bytes
Database Buffers          687865856 bytes
Redo Buffers                5455872 bytes
Database mounted.
Database opened.
SQL> show pdbs

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

Now I can create the clone!

[oracle@server1 ~]$ dbca -createCloneTemplate -help
Create a clone template from an existing database by specifying the following parameters:
        -createCloneTemplate
                -sourceSID    <Database unique name for RAC Database and SID for Single
                               Instance Database>
                -templateName      <new template name>
                [-sysDBAUserName     <user name  with SYSDBA privileges>
                 -sysDBAPassword     <password for sysDBAUserName user name>]
                [-maxBackupSetSizeInMB     <Maximum backup set size (MB)>]
                [-rmanParallelism  <Parallelism value>]
                [-datafileJarLocation       <directory to place the datafiles in a compressed
                                             format>]

[oracle@server1 ~]$ dbca -silent -createCloneTemplate -sourceSID CDBTEMPL -templateName \
> CDB_MASTER_20160615 -sysDBAPassword sysDBAPassword \
> -rmanParallelism 2 -datafileJarLocation /u01/templates

Gathering information from the source database
4% complete
8% complete
13% complete
17% complete
22% complete
Backup datafiles
28% complete
88% complete
Creating template file
100% complete
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/silent.log_2016-06-15_11-31-28-AM"
for further details.

[oracle@server1 ~]$ cat /u01/app/oracle/cfgtoollogs/dbca/silent.log_2016-06-15_11-31-28-AM
Gathering information from the source database
DBCA_PROGRESS : 4%
DBCA_PROGRESS : 8%
DBCA_PROGRESS : 13%
DBCA_PROGRESS : 17%
DBCA_PROGRESS : 22%
Backup datafiles
DBCA_PROGRESS : 28%
DBCA_PROGRESS : 88%
Creating template file
DBCA_PROGRESS : 100%
The generation of the clone database template "CDB_MASTER_20160615" is successful.

That was a success!

Create a CDB from Template

This step is almost exactly the same as for the non-custom template. All you need to do is to invoke dbca in silent mode and point it to the new template. Here is an example:

[oracle@server1 ~]$ dbca -silent -createDatabase -templateName CDB_MASTER_20160615.dbc \
> -gdbName CDB1 -sysPassword sysPassword -systemPassword systemPassword \
> -datafileDestination /u01/oradata -recoveryAreaDestination /u01/fra -totalMemory 4096
Copying database files
1% complete
3% complete
37% complete
Creating and starting Oracle instance
40% complete
45% complete
50% complete
55% complete
56% complete
60% complete
62% complete
Completing Database Creation
66% complete
70% complete
73% complete
85% complete
96% complete
100% complete
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/CDB1/CDB10.log" for further details.

[oracle@server1 ~]$ cat /u01/app/oracle/cfgtoollogs/dbca/CDB1/CDB10.log

Unique database identifier check passed.

/u01/ has enough space. Required space is 10270 MB , available space is 13312 MB.
File Validations Successful.
Copying database files
DBCA_PROGRESS : 1%
DBCA_PROGRESS : 3%
DBCA_PROGRESS : 37%
Creating and starting Oracle instance
DBCA_PROGRESS : 40%
DBCA_PROGRESS : 45%
DBCA_PROGRESS : 50%
DBCA_PROGRESS : 55%
DBCA_PROGRESS : 56%
DBCA_PROGRESS : 60%
DBCA_PROGRESS : 62%
Completing Database Creation
DBCA_PROGRESS : 66%
DBCA_PROGRESS : 70%
DBCA_PROGRESS : 73%
DBCA_PROGRESS : 85%
DBCA_PROGRESS : 96%
DBCA_PROGRESS : 100%
Database creation complete. For details check the logfiles at:
 /u01/app/oracle/cfgtoollogs/dbca/CDB1.
Database Information:
Global Database Name:CDB1
System Identifier(SID):CDB1

Unlike in my first attempt this worked. But there are a couple of post-creation steps now to be taken. First, I would like to use MASTER as the golden master copy for each PDB. That requires the PDB to be opened read-only. But I would also like to check if everything worked

[oracle@server1 ~]$ echo $ORACLE_SID $ORACLE_HOME
CDB1 /u01/app/oracle/product/12.1.0.2/dbhome_1
[oracle@server1 ~]$ sq

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jun 15 13:07:51 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 MASTER                         READ WRITE NO
SQL> alter session set container = MASTER;

Session altered.

SQL> select username, account_status
  2    from dba_users
  3   where oracle_maintained <> 'Y';

USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
SQLTXPLAIN                     OPEN
SQLTXADMIN                     EXPIRED & LOCKED
PERFSTAT                       OPEN
C##BACKUPADMIN                 EXPIRED & LOCKED
C##MONITOR                     EXPIRED & LOCKED
PDBMASTER                      OPEN

6 rows selected.

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
TEMP
USERS
STATPACK_TBS
SQLT_TBS

This seems to have worked. Notice how the common users are expired and locked. Now to enable cloning, I simply repeat the save state command but this time I want the PDB to be open read-only.

SQL> alter pluggable database master close immediate;

Pluggable database altered.

SQL> alter pluggable database master open read only;

Pluggable database altered.

SQL> alter pluggable database master save state;

Pluggable database altered.

-- create a new PDB

SQL> create pluggable database userpdb from master...;

Pluggable database created.

SQL> alter pluggable database userpdb open;

Pluggable database altered.

SQL> conn perfstat/perfstatPWD@localhost/userpdb
Connected.
SQL> show con_name

CON_NAME
------------------------------
USERPDB
SQL>

That seems to have worked as well. Of course there are more variations to the theme, and you certainly need to perform intensive testing before you can roll out a strategy like this but I think you get the idea. The process cannot stop here of course, there are many more important tasks (backup, monitoring, auditing, logging, …) to be configured but those are left out for now.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

 
%d bloggers like this: