Here are the steps I have used to create a seed template (i.e a template using RMAN backup) to be able to create a container database faster in Oracle Cloud.
DISCLAIMER: Currently the way to create a 12.2.0.1 container database with CREATE DATABASE in Oracle Cloud does not look fully documented or tested: I have encountered some errors with catcdb.sql and the way these errors have been fixed is currently not documented by Oracle as far as I know.
First I have created a minimum container database with Unicode character set.
Here minimum means:
I have used following script:
#!/bin/sh export PATH=$ORACLE_HOME/perl/bin:$PATH export PERL5LIB=/var/opt/oracle/perl_lib/DBAAS export ORACLE_SID=CTPL export PFILE=$ORACLE_HOME/dbs/initCTPL.ora rm -rf /u02/CTPL rm -rf /u04/CTPL rm -f $ORACLE_HOME/dbs/spfileCTPL.ora # echo "db_name=CTPL" > $PFILE echo "db_create_file_dest=/u02" >> $PFILE echo "db_recovery_file_dest=/u04" >> $PFILE echo "db_recovery_file_dest_size=10G" >> $PFILE echo "memory_target=2G" >> $PFILE echo "encrypt_new_tablespaces=DDL" >> $PFILE echo "enable_pluggable_database=TRUE" >> $PFILE # export CATCDB_SYS_PASSWD=oracle12C export CATCDB_SYSTEM_PASSWD=oracle12C export CATCDB_TEMPTS=temp # sqlplus / as sysdba << EOF shutdown abort startup nomount create spfile from pfile; host rm $PFILE shutdown abort startup nomount spool crdb.log set echo on create database CTPL character set al32utf8 enable pluggable database default temporary tablespace TEMP; @?/rdbms/admin/catcdb.sql /home/oracle/scripts catcdb -- create pluggable database pdb admin user pdba identified by oracle12C; -- EOF
The above script is using perl executable from $ORACLE_HOME and perl library located in /var/opt/oracle/perl_lib/DBAAS
in order to avoid following error in catcdb.sql:
SQL> host perl -I &&rdbms_admin &&rdbms_admin_catcdb --logDirectory &&1 --logFilename &&2 Can't locate util.pm in @INC (you may need to install the util module) (@INC contains: /u01/app/oracle/product/12 .2.0/dbhome_1/rdbms/admin /u01/app/oracle/product/12.2.0/dbhome_1/perl/lib/site_perl/5.22.0/x86_64-linux-thread-m ulti /u01/app/oracle/product/12.2.0/dbhome_1/perl/lib/site_perl/5.22.0 /u01/app/oracle/product/12.2.0/dbhome_1/pe rl/lib/5.22.0/x86_64-linux-thread-multi /u01/app/oracle/product/12.2.0/dbhome_1/perl/lib/5.22.0 .) at /u01/app/or acle/product/12.2.0/dbhome_1/rdbms/admin/catcdb.pl line 35. BEGIN failed--compilation aborted at /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/catcdb.pl line 35.
The above script is using CATCDB_SYS_PASSWD, CATCDB_SYSTEM_PASSWD and CATCDB_TEMPTS to avoid following errors in catcdb.sql:
SQL> host perl -I &&rdbms_admin &&rdbms_admin_catcdb --logDirectory &&1 --logFilename &&2 Enter new password for SYS: Enter new password for SYSTEM: Enter temporary tablespace name: No options to contain er mapping specified, no options will be installed in any containers user password environment variable CATCDB_SYS_PASSWD specified with -W was undefined user password environment variable CATCDB_SYS_PASSWD specified with -W was undefined user password environment variable CATCDB_SYS_PASSWD specified with -W was undefined user password environment variable CATCDB_SYS_PASSWD specified with -W was undefined user password environment variable CATCDB_SYS_PASSWD specified with -W was undefined user password environment variable CATCDB_SYS_PASSWD specified with -W was undefined user password environment variable CATCDB_SYS_PASSWD specified with -W was undefined user password environment variable CATCDB_SYS_PASSWD specified with -W was undefined user password environment variable CATCDB_SYS_PASSWD specified with -W was undefined user password environment variable CATCDB_SYS_PASSWD specified with -W was undefined
The created database is a container database with following options (Workspace Manager is unexpected for me):
SQL> select name, cdb, log_mode from v$database;
NAME CDB LOG_MODE
--------- --- ------------
CTPL YES NOARCHIVELOG
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB MOUNTED
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u02/CTPL/datafile/o1_mf_system_d4dyln21_.dbf
/u02/CTPL/42FA6811B2B673FEE05342DCC40A6FD0/datafile/o1_mf_system_d4dylpwy_.dbf
/u02/CTPL/datafile/o1_mf_sysaux_d4dylw2f_.dbf
/u02/CTPL/42FA6811B2B673FEE05342DCC40A6FD0/datafile/o1_mf_sysaux_d4dylx89_.dbf
/u02/CTPL/datafile/o1_mf_sys_undo_d4dylyws_.dbf
/u02/CTPL/42FA6811B35173FEE05342DCC40A6FD0/datafile/o1_mf_system_d4f46j1w_.dbf
/u02/CTPL/42FA6811B35173FEE05342DCC40A6FD0/datafile/o1_mf_sysaux_d4f46j26_.dbf
7 rows selected.
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u02/CTPL/onlinelog/o1_mf_1_d4dylhnj_.log
/u04/CTPL/onlinelog/o1_mf_1_d4dylkcv_.log
/u02/CTPL/onlinelog/o1_mf_2_d4dylkjm_.log
/u04/CTPL/onlinelog/o1_mf_2_d4dyll05_.log
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/u02/CTPL/controlfile/o1_mf_d4dylh19_.ctl
/u04/CTPL/controlfile/o1_mf_d4dylh68_.ctl
SQL> --
SQL> column parameter format a30
SQL> column value format a10
SQL> select parameter, value from nls_database_parameters where parameter like '%SET%';
PARAMETER VALUE
------------------------------ ----------
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_CHARACTERSET AL32UTF8
SQL> --
SQL> column action_time format a15
SQL> column action format a10
SQL> column version format a12
SQL> column description format a50
SQL> column comp_name format a40
SQL> set linesize 120
SQL> --
SQL> select comp_name, version, status
2 from dba_registry
3 order by comp_name;
COMP_NAME VERSION STATUS
---------------------------------------- ------------ --------------------------------------------
Oracle Database Catalog Views 12.2.0.1.0 VALID
Oracle Database Packages and Types 12.2.0.1.0 VALID
Oracle Real Application Clusters 12.2.0.1.0 OPTION OFF
Oracle Workspace Manager 12.2.0.1.0 VALID
Oracle XML Database 12.2.0.1.0 VALID
SQL> --
SQL> select * from dba_registry_sqlpatch;
no rows selected
SQL>
Before creating the template the pluggable database must be opened and I have changed its default state so that it is always opened at instance startup:
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB MOUNTED
SQL> alter pluggable database pdb open;
Pluggable database altered.
SQL> alter pluggable database pdb save state;
Pluggable database altered.
SQL> startup force
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed Size 8794848 bytes
Variable Size 1342180640 bytes
Database Buffers 788529152 bytes
Redo Buffers 7979008 bytes
Database mounted.
Database opened.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB READ WRITE NO
SQL>
I have created the template with following script:
dbca -createCloneTemplate \ -silent \ -sourceSID CTPL \ -templateName CDBT
I have run this script:
+ dbca -createCloneTemplate -silent -sourceSID CTPL -templateName CDBT 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-12-06_10-39-43-AM" for further details.
The template is made up of 5 files in $ORACLE_HOME/assistants/dbca/templates:
$ ls $ORACLE_HOME/assistants/dbca/templates/CDBT* /u01/app/oracle/product/12.2.0/dbhome_1/assistants/dbca/templates/CDBT.ctl /u01/app/oracle/product/12.2.0/dbhome_1/assistants/dbca/templates/CDBT.dbc /u01/app/oracle/product/12.2.0/dbhome_1/assistants/dbca/templates/CDBT.dfb62 /u01/app/oracle/product/12.2.0/dbhome_1/assistants/dbca/templates/CDBT.dfb63 /u01/app/oracle/product/12.2.0/dbhome_1/assistants/dbca/templates/CDBT.dfb64
Some unexepected errors (DBCA bugs) have occured when trying to use this template.
To avoid following error:
[FATAL] [DBT-11211] The Automatic Memory Management option is not allowed when the total physical memory is greater than 4GB. CAUSE: The current total physical memory is 7,220GB.
I have removed in CDBT.dbc file:
initParam name="memory_target" value="2147483648"
To avoid following error:
ORA-00201: control file version 12.2.0.0.0 incompatible with ORACLE version 12.1.0.2.0 ORA-00202: control file: '/u01/app/oracle/cfgtoollogs/dbca/CDB1/tempControl.ctl'
I have replaced in CDBT.dbc file:
initParam name="compatible" value="12.1.0.2.0"
by
initParam name="compatible" value="12.2.0.1.0"
I have used following script to create a database using CDBT template (note that the template is a container database template and it is not needed to specify pluggable database parameters to have only 1 pluggable database):
dbca -silent \ -createDatabase \ -templateName CDBT.dbc \ -gdbName CDB \ -sid CDB \ -SysPassword oracle12c \ -SystemPassword oracle12c \ -characterSet AL32UTF8 \ -emConfiguration NONE \ -storageType FS \ -datafileDestination /u02 \ -recoveryAreaDestination /u04 \ -initParams sga_target=1536M
Running this script gives following output:
[WARNING] [DBT-06208] The 'SYS' password entered does not conform to the Oracle recommended standards. CAUSE: a. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9]. b.The password entered is a keyword that Oracle does not recommend to be used as password ACTION: Specify a strong password. If required refer Oracle documentation for guidelines. [WARNING] [DBT-06208] The 'SYSTEM' password entered does not conform to the Oracle recommended standards. CAUSE: a. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9]. b.The password entered is a keyword that Oracle does not recommend to be used as password ACTION: Specify a strong password. If required refer Oracle documentation for guidelines. [WARNING] [DBT-06801] Specified Fast Recovery Area size (10,240 MB) is less than the recommended value. CAUSE: Fast Recovery Area size should at least be three times the database size (4,312 MB). ACTION: Specify Fast Recovery Area Size to be at least three times the database size. Copying database files 1% complete 2% complete 18% complete 33% complete Creating and starting Oracle instance 35% complete 40% complete 44% complete 49% complete 50% complete 53% complete 55% complete Completing Database Creation 56% complete 57% complete 58% complete 62% complete 65% complete 66% complete Executing Post Configuration Actions 100% complete Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/CDB/CDB12.log" for further details.
I have checked the created database:
SQL> select name, cdb, log_mode from v$database;
NAME CDB LOG_MODE
--------- --- ------------
CDB YES NOARCHIVELOG
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB READ WRITE NO
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u02/CDB/datafile/o1_mf_system_d4f5xnt8_.dbf
/u02/CDB/42FA6811B2B673FEE05342DCC40A6FD0/datafile/o1_mf_system_d4f5yg2j_.dbf
/u02/CDB/datafile/o1_mf_sysaux_d4f5yx5l_.dbf
/u02/CDB/42FA6811B2B673FEE05342DCC40A6FD0/datafile/o1_mf_sysaux_d4f5zpbd_.dbf
/u02/CDB/datafile/o1_mf_sys_undo_d4f605hf_.dbf
/u02/CDB/42FA6811B35173FEE05342DCC40A6FD0/datafile/o1_mf_system_d4f60ynx_.dbf
/u02/CDB/42FA6811B35173FEE05342DCC40A6FD0/datafile/o1_mf_sysaux_d4f61frh_.dbf
7 rows selected.
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u02/CDB/onlinelog/o1_mf_2_d4f633mm_.log
/u04/CDB/onlinelog/o1_mf_2_d4f63561_.log
/u02/CDB/onlinelog/o1_mf_1_d4f633m1_.log
/u04/CDB/onlinelog/o1_mf_1_d4f6354p_.log
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/u02/CDB/controlfile/o1_mf_d4f62x81_.ctl
/u04/CDB/controlfile/o1_mf_d4f62xgc_.ctl
SQL> --
SQL> column parameter format a30
SQL> column value format a10
SQL> select parameter, value from nls_database_parameters where parameter like '%SET%';
PARAMETER VALUE
------------------------------ ----------
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_CHARACTERSET AL32UTF8
SQL> --
SQL> column action_time format a15
SQL> column action format a10
SQL> column version format a12
SQL> column description format a50
SQL> column comp_name format a40
SQL> set linesize 120
SQL> --
SQL> select comp_name, version, status
2 from dba_registry
3 order by comp_name;
COMP_NAME VERSION STATUS
---------------------------------------- ------------ --------------------------------------------
Oracle Database Catalog Views 12.2.0.1.0 VALID
Oracle Database Packages and Types 12.2.0.1.0 VALID
Oracle Real Application Clusters 12.2.0.1.0 OPTION OFF
Oracle Workspace Manager 12.2.0.1.0 VALID
Oracle XML Database 12.2.0.1.0 VALID
SQL> --
SQL> select * from dba_registry_sqlpatch;
no rows selected
SQL>
I have changed pluggable database default state so that it is always opened at instance startup:
SQL> alter pluggable database pdb save state; Pluggable database altered. SQL>
I have already modified tnsnames.ora to have separate Transparent Data Encryption (TDE) key stores for each database:
$ grep wallet $ORACLE_HOME/network/admin/sqlnet.ora ENCRYPTION_WALLET_LOCATION = (SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/u01/app/oracle/admin/$ORACLE_SID/tde_wallet))) WALLET_LOCATION = (SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/u01/app/oracle/admin/$ORACLE_SID/db_wallet))) $
I have setup TDE with following SQL script:
set echo on select name from v$database; set linesize 120 column status format a10 column wrl_parameter format a40 column wallet_type format a15 column wrl_type format a10 column key_id format a60 -- administer key management create keystore '/u01/app/oracle/admin/CDB/tde_wallet' identified by xxx; administer key management set keystore open identified by xxx container=all; administer key management set key identified by xxx with backup container=all; select * from v$encryption_wallet; alter session set container=pdb; select * from v$encryption_wallet; select con_id, key_id, keystore_type from v$encryption_keys; alter session set container=cdb$root; administer key management create auto_login keystore from keystore '/u01/app/oracle/admin/CDB/tde_wallet' identified by xxx; administer key management set keystore close identified by xxx container=all; -- exit
I have run this script:
SQL> select name from v$database;
NAME
---------
CDB
SQL> set linesize 120
SQL> column status format a10
SQL> column wrl_parameter format a40
SQL> column wallet_type format a15
SQL> column wrl_type format a10
SQL> column key_id format a60
SQL> --
SQL> administer key management create keystore '/u01/app/oracle/admin/CDB/tde_wallet' identified by xxx;
keystore altered.
SQL> administer key management set keystore open identified by xxx container=all;
keystore altered.
SQL> administer key management set key identified by xxx with backup container=all;
keystore altered.
SQL> select * from v$encryption_wallet;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
---------- ---------------------------------------- ---------- --------------- --------- --------- ----------
FILE /u01/app/oracle/admin/CDB/tde_wallet/ OPEN PASSWORD SINGLE NO 1
SQL> alter session set container=pdb;
Session altered.
SQL> select * from v$encryption_wallet;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
---------- ---------------------------------------- ---------- --------------- --------- --------- ----------
FILE OPEN PASSWORD SINGLE NO 3
SQL> select con_id, key_id, keystore_type from v$encryption_keys;
CON_ID KEY_ID KEYSTORE_TYPE
---------- ------------------------------------------------------------ -----------------
3 Abc7t62yIk8sv7xt4MeffRgAAAAAAAAAAAAAAAAAAAAAAAAAAAAA SOFTWARE KEYSTORE
SQL> alter session set container=cdb$root;
Session altered.
SQL> administer key management create auto_login keystore from keystore '/u01/app/oracle/admin/CDB/tde_wallet' identified by xxx;
keystore altered.
SQL> administer key management set keystore close identified by xxx container=all;
keystore altered.
SQL>
At this step in case of unexpected error such as ORA-28374: typed master key not found in wallet the simplest thing to do is just to drop, re-create the database and remove all files located in ENCRYPTION_WALLET_DIRECTORY.
This is the last test step that must succeed: I have also created an application tablespace in the pluggable database and tested that database instance can be restarted without any error:
SQL> select name from v$database;
NAME
---------
CDB
SQL> show parameter new
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
encrypt_new_tablespaces string CLOUD_ONLY
SQL> alter session set container=pdb;
Session altered.
SQL> create tablespace data;
Tablespace created.
SQL> select tablespace_name, encrypted from dba_tablespaces;
TABLESPACE_NAME ENC
------------------------------ ---
SYSTEM NO
SYSAUX NO
TEMP NO
DATA YES
SQL> alter session set container=cdb$root;
Session altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 1610612736 bytes
Fixed Size 8793304 bytes
Variable Size 520094504 bytes
Database Buffers 1073741824 bytes
Redo Buffers 7983104 bytes
Database mounted.
Database opened.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB READ WRITE NO
SQL>
This template is now ready to be used to create a 12.2.0.1 container database in Oracle Cloud.