Manually Creating an Oracle 12c R2 Container Database on Windows OS

Purpose: Manually Create an Oracle Container Database using SQL*Plus command prompt on Windows OS

Environment:
Database Software: Oracle 12c Release 2
Operating System: Windows Server 2012 R2 Standard - 64 Bit

1. Oracle Install: Already installed Oracle 12c Release 2 software as per my previous post.

Tip # 1: If you plan on using this install for more serious stuff (business purposes), its highly recommended to apply the latest available patch(es). Refer to my post on Installing Oracle Patch.

2. Oracle Net: Configure listener.ora, tnsnames.ora and sqlnet.ora files at "F:\Oracle12c_64Bit_DB\product\network\admin" folder with my typical settings:-

listener.ora :-

DEFAULT_SERVICE_LISTENER = BABCDB

INBOUND_CONNECT_TIMEOUT_LISTENER = 0

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = F:\Oracle12c_64Bit_DB\product)
(PROGRAM = extproc))
(SID_DESC =
(GLOBAL_DBNAME = BABCDB)
(ORACLE_HOME = F:\Oracle12cR2_64Bit_DB\product)
(SID_NAME = BABCDB)))

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = SANDBOX26)(PORT = 1521))))

tnsnames.ora :-

BABCDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = SANDBOX26)(PORT = 1521)))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = BABCDB)))

sqlnet.ora :-

SQLNET.INBOUND_CONNECT_TIMEOUT = 0
SQLNET.EXPIRE_TIME = 2
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

3. DB Parameter [Init] File: Added below parameters in the initBABCDB.ora file and placed it at "F:\Oracle12c_64Bit_DB\product\database" folder:-

audit_file_dest='F:\Oracle12cR2_64Bit_DB\admin\adump'
audit_trail =none
compatible='12.0.0.0.0'
control_files='H:\ORACLE\ORADATA\BABCDB\control01.ctl','H:\ORACLE\ORADATA\BABCDB\control02.ctl','H:\ORACLE\ORADATA\BABCDB\control03.ctl'
db_block_size=8192
db_cache_size=157286400
db_domain=''
db_file_multiblock_read_count=16
db_name='BABCDB'
db_recovery_file_dest='F:\Oracle12cR2_64Bit_DB\admin\fast_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest='F:\Oracle12cR2_64Bit_DB\admin\diagnostic_dest'
dispatchers='(PROTOCOL=TCP) (SERVICE=BABCDBXDB)'
enable_pluggable_database=true
fast_start_mttr_target=300
java_pool_size=157286400
job_queue_processes=10
large_pool_size=67108864
local_listener='LISTENER_BABCDB'
open_cursors=2000
pga_aggregate_limit=2147483648
pga_aggregate_target=104857600
processes=500
query_rewrite_enabled='TRUE'
remote_login_passwordfile='EXCLUSIVE'
session_cached_cursors=50
sga_max_size=629145600
shared_pool_size=692060160
sort_area_size=524288
star_transformation_enabled='FALSE'
streams_pool_size=50331648
timed_statistics=TRUE
undo_management='AUTO'
undo_retention=10800
undo_tablespace='UNDOTBS1'

4. Destination / Location Folders: Create folders for

audit_file_dest
db_recovery_file_dest
diagnostic_dest
control_files
datafiles and PDB seed

5. Create Oracle Service: Navigate to the bin folder, run oradim and when asked, provide the password of the local OS user that was created in the previous post.

cd F:\Oracle12cR2_64Bit_DB\product\bin

oradim -new -SID BABCDB -INTPWD xxxxxxxx -STARTMODE m -PFILE F:\Oracle12cR2_64Bit_DB\product\database\initBABCDB.ora
Enter password for Oracle service user: [provide password for OracleWinUsr user]
Instance created.

Note, the created service's start mode is manual, but its started right after creation. If not, make sure the Oracle service OracleServiceBABCDB is started.

6. Start the listener and mount the database:-

> cd F:\Oracle12cR2_64Bit_DB\product\bin

> lsnrctl start

> sqlplus SYS/xxxxxxxxxx@BABCDB AS SYSDBA

SQL> startup nomount

7. Create the (container) database:-

SQL> CREATE DATABASE BABCDB
USER SYS IDENTIFIED BY xxxxxxxxx
USER SYSTEM IDENTIFIED BY xxxxxxxxx
CONTROLFILE REUSE
LOGFILE
GROUP 1 ('H:\ORACLE\ORADATA\BABCDB\REDO_01.LOG') SIZE 100M REUSE,
GROUP 2 ('H:\ORACLE\ORADATA\BABCDB\REDO_02.LOG') SIZE 100M REUSE,
GROUP 3 ('H:\ORACLE\ORADATA\BABCDB\REDO_03.LOG') SIZE 100M REUSE
MAXLOGFILES 32
MAXLOGMEMBERS 4
MAXLOGHISTORY 100
MAXDATAFILES 254
MAXINSTANCES 1
EXTENT MANAGEMENT LOCAL
DATAFILE 'H:\ORACLE\ORADATA\BABCDB\SYSTEM_01.DBF' SIZE 300M REUSE AUTOEXTEND ON NEXT 5M MAXSIZE 2048m
SYSAUX DATAFILE 'H:\ORACLE\ORADATA\BABCDB\SYSAUX_01.DBF' SIZE 120M REUSE AUTOEXTEND ON NEXT 5M MAXSIZE 2048m
DEFAULT TABLESPACE DEFAULTBS1
DATAFILE 'H:\ORACLE\ORADATA\BABCDB\DEFAULT_01.DBF' SIZE 50M REUSE AUTOEXTEND ON NEXT 5M MAXSIZE 2048m
UNDO TABLESPACE UNDOTBS1
DATAFILE 'H:\ORACLE\ORADATA\BABCDB\UNDO_01.DBF' SIZE 200M REUSE AUTOEXTEND ON NEXT 5M MAXSIZE 4096m
DEFAULT TEMPORARY TABLESPACE TEMPTBS1
TEMPFILE 'H:\ORACLE\ORADATA\BABCDB\TEMP_01.DBF' SIZE 25M REUSE AUTOEXTEND on NEXT 1m MAXSIZE 4096m
UNIFORM SIZE 1M
CHARACTER SET WE8MSWIN1252
NATIONAL CHARACTER SET AL16UTF16
NOARCHIVELOG
ENABLE PLUGGABLE DATABASE
SEED
FILE_NAME_CONVERT = ('H:\ORACLE\ORADATA\BABCDB\','H:\ORACLE\ORADATA\BABCDB\SEEDPDB\')
SYSTEM DATAFILES SIZE 50M AUTOEXTEND ON NEXT 10M MAXSIZE 2048m
SYSAUX DATAFILES SIZE 50M
LOCAL UNDO OFF;

8. Create the dictionary views and system stored procedures

I created a folder called "C:\tmp\spatial" to direct the output files while running the catcdb.sql script.

This is done by running "catcdb.sql", which calls a perl script "catcdb.pl", both these files are located in "ORACLE_HOME\rdbms\admin" folder.

Tip # 2: Can't locate util.pm bug - Check this out.

Tip # 3: SP2-0640: Not connected error message - Check this out.

> set ORACLE_SID=BABCDB

> set ORACLE_HOME=F:\Oracle12cR2_64Bit_DB\product
> set PATH=F:\Oracle12cR2_64Bit_DB\product\bin;F:\Oracle12cR2_64Bit_DB\product\perl\bin;C:\Program Files\EMC NetWorker\nsr\bin;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\

Get back to SQL*Plus and run "catcdb.sql" once again.

> sqlplus SYS/xxxxxxxxxx@BABCDB AS SYSDBA

SQL> @F:\Oracle12cR2_64Bit_DB\product\rdbms\admin\catcdb.sql

SQL> host perl -I &&rdbms_admin &&rdbms_admin_catcdb --logDirectory &&1 --logFilename &&2

Enter value for 1: C:\temp\catcdb [Location for log file]
Enter value for 2: BABU_CATCDB.LOG [Name of the log file]
Enter new password for SYS: ********
Enter new password for SYSTEM: ********
Enter temporary tablespace name: TEMPTBS1

9. Verify the log files at the provided location to confirm successful run or any errors. And query DBA_REGISTRY for components and its validity.

select comp_id, control, schema, version, status, comp_name from dba_registry order by 1;