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:
- A component that is not installed can’t be used as an attack vector
- 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.
Martins Blog 