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 12.2.0.1 database faster in Oracle Cloud.
First I have created a minimum non-container database with Unicode character set.
Here minimum means:
I have used following script:
#!/bin/sh export ORACLE_SID=NCTPL export PFILE=$ORACLE_HOME/dbs/initNCTPL.ora rm -rf /u02/NCTPL rm -rf /u04/NCTPL rm -f $ORACLE_HOME/dbs/spfileNCTPL.ora # echo "db_name=NCTPL" > $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 # sqlplus / as sysdba << EOF shutdown abort startup nomount create spfile from pfile; host rm $PFILE shutdown abort startup nomount spool crdb.log create database NCTPL character set al32utf8 default temporary tablespace TEMP; @?/rdbms/admin/catalog.sql @?/rdbms/admin/catproc.sql @?/rdbms/admin/utlrp.sql EOF
Note that if there is no temporary tablespace there will a syntax error in DBCA template .dbc files for Tempfiles tag that will only trigger an error when using the template:
[FATAL] [DBT-11004] Invalid template file specified (...). CAUSE: The given template is not compliant to the expected schema. ACTION: Verify the correctness of the template syntactically and semantically. Alternatively contact Oracle Support Services.
I have created the template with following script:
dbca -createCloneTemplate \ -silent \ -sourceSID NCTPL \ -templateName NCDBT
I have run the script:
+ dbca -createCloneTemplate -silent -sourceSID NCTPL -templateName NCDBT 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-01_05-47-31-PM" for further details.
The template is made up of 3 files in $ORACLE_HOME/dbca/templates
$ ls -al /u01/app/oracle/product/12.2.0/dbhome_1/assistants/dbca/templates/*NCDBT* -rw-r----- 1 oracle oinstall 8634368 Dec 1 17:49 /u01/app/oracle/product/12.2.0/dbhome_1/assistants/dbca/templates/NCDBT.ctl -rw-r----- 1 oracle oinstall 4245 Dec 1 17:49 /u01/app/oracle/product/12.2.0/dbhome_1/assistants/dbca/templates/NCDBT.dbc -rw-r----- 1 oracle oinstall 133398528 Dec 1 17:48 /u01/app/oracle/product/12.2.0/dbhome_1/assistants/dbca/templates/NCDBT.dfb
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 NCDBT.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/NCDB1/tempControl.ctl'
I have replaced in NCDBT.dbc file:
initParam name="compatible" value="12.1.0.2.0"
with:
initParam name="compatible" value="12.2.0.1.0"
To test the template I have used following script:
dbca -silent \ -createDatabase \ -templateName NCDBT.dbc \ -gdbName NCDB \ -sid NCDB \ -SysPassword oracle12c \ -SystemPassword oracle12c \ -characterSet AL32UTF8 \ -emConfiguration NONE \ -storageType FS \ -datafileDestination /u02 \ -recoveryAreaDestination /u04 \ -totalMemory 2048
Script output is:
++ dbca -silent -createDatabase -templateName NCDBT.dbc -gdbName NCDB -sid NCDB -SysPassword oracle12c -SystemPassword oracle12c -characterSet AL32UTF8 -emConfiguration NONE -storageType FS -datafileDestination /u02 -recoveryAreaDestination /u04 -totalMemory 2048 [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. 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/NCDB/NCDB2.log" for further details.
I have checked the created database:
SQL> select name, cdb, log_mode from v$database; NAME CDB LOG_MODE --------- --- ------------ NCDB NO NOARCHIVELOG SQL> show pdbs; SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /u02/NCDB/datafile/o1_mf_system_d40sot8p_.dbf /u02/NCDB/datafile/o1_mf_sysaux_d40spxgz_.dbf /u02/NCDB/datafile/o1_mf_sys_undo_d40sr0n5_.dbf SQL> select member from v$logfile; MEMBER -------------------------------------------------------------------------------- /u02/NCDB/onlinelog/o1_mf_2_d40st02o_.log /u04/NCDB/onlinelog/o1_mf_2_d40st1s7_.log /u02/NCDB/onlinelog/o1_mf_1_d40st01y_.log /u04/NCDB/onlinelog/o1_mf_1_d40st1q1_.log SQL> select name from v$controlfile; NAME -------------------------------------------------------------------------------- /u02/NCDB/controlfile/o1_mf_d40sst01_.ctl /u04/NCDB/controlfile/o1_mf_d40sst5l_.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 XML Database 12.2.0.1.0 VALID SQL> -- SQL> select * from dba_registry_sqlpatch; no rows selected SQL>
I have already modified tnsnames.ora to have separate 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 Transparent Data Encryption (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 -- host mkdir /u01/app/oracle/admin/NCDB/tde_wallet -- administer key management create keystore '/u01/app/oracle/admin/NCDB/tde_wallet' identified by xxx; administer key management set keystore open identified by xxx; select * from v$encryption_wallet; administer key management create auto_login keystore from keystore '/u01/app/oracle/admin/NCDB/tde_wallet' identified by xxx; administer key management set key identified by xxx with backup; select con_id, key_id, keystore_type from v$encryption_keys; select * from v$encryption_wallet; -- exit
I have run this script:
SQL> select name from v$database;
NAME
---------
NCDB
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> host mkdir /u01/app/oracle/admin/NCDB/tde_wallet
SQL> --
SQL> administer key management create keystore '/u01/app/oracle/admin/NCDB/tde_wallet' identified by xxx;
keystore altered.
SQL> administer key management set keystore open identified by xxx;
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/NCDB/tde_wallet/ OPEN_NO_MA PASSWORD SINGLE UNDEFINED 0
STER_KEY
SQL> administer key management create auto_login keystore from keystore '/u01/app/oracle/admin/NCDB/tde_wallet' identified by xxx;
keystore altered.
SQL> administer key management set key identified by xxx with backup;
keystore altered.
SQL> select con_id, key_id, keystore_type from v$encryption_keys;
CON_ID KEY_ID KEYSTORE_TYPE
---------- ------------------------------------------------------------ -----------------
0 AfWB+ptX8k8xv9XUX4vh7ooAAAAAAAAAAAAAAAAAAAAAAAAAAAAA SOFTWARE KEYSTORE
SQL> select * from v$encryption_wallet;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
---------- ---------------------------------------- ---------- --------------- --------- --------- ----------
FILE /u01/app/oracle/admin/NCDB/tde_wallet/ OPEN PASSWORD SINGLE NO 0
SQL>
At this step in case of unexpected error ORA-28374: typed master key not found in wallet the simplest thing to do is just to drop and re-create the database.
Reason for this is that encryption keys are stored in tablespace headers, database dictionary and redo logs: if there is some key mismatch it is much easier to drop and re-create the database.
This is the last test step that must succeed: I have also created an application tablespace and tested that database instance can be restarted without any error:
SQL> show parameter new NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ encrypt_new_tablespaces string CLOUD_ONLY SQL> create tablespace data; Tablespace created. SQL> select tablespace_name, encrypted from dba_tablespaces; TABLESPACE_NAME ENC ------------------------------ --- SYSTEM NO SYSAUX NO SYS_UNDOTS NO TEMP NO DATA YES SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 1610612736 bytes Fixed Size 8621232 bytes Variable Size 520094544 bytes Database Buffers 1073741824 bytes Redo Buffers 8155136 bytes Database mounted. Database opened. SQL> select * from v$encryption_wallet; WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID ---------- ---------------------------------------- ---------- --------------- --------- --------- ---------- FILE /u01/app/oracle/admin/NCDB/tde_wallet/ OPEN AUTOLOGIN SINGLE NO 0 SQL>
Note that the auto_login key store makes sure that it is opened at instance startup.
This template is now ready to be used to create a non-container database in Oracle Cloud.