Oracle 12c Manual DB creation
Step 1:
Create a pfile with following parameters
cat /tmp/pfile_CDB.ora
*.audit_file_dest='/opt/app/oracle/admin/cdb/adump'
*.audit_trail='db'
*.compatible='12.1.0.0.0'
*.control_files='+DATA/CDB/CONTROLFILE/control.ctl'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='CDB'
*.db_recovery_file_dest='+DATA'
*.db_recovery_file_dest_size=4800m
*.diagnostic_dest='/opt/app/oracle'
*.enable_pluggable_database=true
*.open_cursors=300
*.pga_aggregate_target=200m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=600m
*.undo_tablespace='UNDOTBS1'
Note: enable_pluggable_database, db_name, control_files are must be mentioned.
Step 2:
Start the new CDB in NOMOUNT mode
sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Thu Jul 4 10:07:21 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> STARTUP NOMOUNT pfile='/tmp/pfile_CDB.ora';
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2291472 bytes
Variable Size 180357360 bytes
Database Buffers 436207616 bytes
Redo Buffers 7471104 bytes
Step 3:
Create the CDB using CREATE DATABASE Statement; Note the following options / parameters:
– SEED FILE_NAME_CONVERT:
This clause specifies how to generate the names of the seed’s files using the names of root’s files. This clause must be added to the “CREATE DATABASE” statement.
– PDB_FILE_NAME_CONVERT Initialization Parameter:
specify the names and locations of the seed’s files. The seed can be used as a template to create new Pluggable databases (PDB).
SQL> CREATE DATABASE CDB
USER SYS IDENTIFIED BY Wissem123
USER SYSTEM IDENTIFIED BY Wissem123
LOGFILE GROUP 1 ('+DATA/CDB/ONLINELOG/redo01a.log','+DATA/CDB/ONLINELOG/redo01b.log')
SIZE 20M BLOCKSIZE 512,
GROUP 2 ('+DATA/CDB/ONLINELOG/redo02a.log','+DATA/CDB/ONLINELOG/redo02b.log')
SIZE 20M BLOCKSIZE 512,
GROUP 3 ('+DATA/CDB/ONLINELOG/redo03a.log','+DATA/CDB/ONLINELOG/redo03b.log')
SIZE 20M BLOCKSIZE 512
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 1024
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL
DATAFILE '+DATA/CDB/DATAFILE/CDB/system01.dbf'
SIZE 100M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
SYSAUX DATAFILE '+DATA/CDB/DATAFILE/CDB/sysaux01.dbf'
SIZE 100M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
DEFAULT TABLESPACE deftbs
DATAFILE '+DATA/CDB/DATAFILE/CDB/deftbs01.dbf'
SIZE 100M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE TEMP
TEMPFILE '+DATA/CDB/DATAFILE/CDB/temp01.dbf'
SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
UNDO TABLESPACE undotbs1
DATAFILE '+DATA/CDB/DATAFILE/CDB/undotbs01.dbf'
SIZE 100M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
ENABLE PLUGGABLE DATABASE
SEED
FILE_NAME_CONVERT = ('+DATA/CDB/DATAFILE/CDB/',
'+DATA/CDB/DATAFILE/pdbseed/')
SYSTEM DATAFILES SIZE 125M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
SYSAUX DATAFILES SIZE 100M
USER_DATA TABLESPACE usertbs
DATAFILE '+DATA/CDB/DATAFILE/usertbs01.dbf'
SIZE 100M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
Database created.
You may have the following error during CREATE DATABASE statement:
ORA-65005: missing or invalid file name pattern for file - string
Cause: Either source or replacement file name pattern was missing or invalid in a SOURCE_FILE_NAME_CONVERT or FILE_NAME_CONVERT clause.
Action: Correct the SOURCE_FILE_NAME_CONVERT or FILE_NAME_CONVERT clause and reissue the statement.
Step 4:
Open the Pluggable database
SQL> alter session set "_oracle_script"=true;
Session altered.
SQL> alter pluggable database pdb$seed close;
Pluggable database altered.
SQL> alter pluggable database pdb$seed open;
alter pluggable database pdb$seed open
ERROR at line 1:
ORA-00704: bootstrap process failure
ORA-00942: table or view does not exist
-- repeat again
SQL> alter pluggable database pdb$seed open;
Pluggable database altered.
Enter the following in SQL*Plus to run the scripts:
@$ORACLE_HOME/rdbms/admin/catalog.sql
@$ORACLE_HOME/rdbms/admin/catblock.sql
@$ORACLE_HOME/rdbms/admin/catproc.sql
@$ORACLE_HOME/rdbms/admin/catoctk.sql
@$ORACLE_HOME/rdbms/admin/owminst.plb
@$ORACLE_HOME/sqlplus/admin/pupbld.sql
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;
CON_ID DBID NAME OPEN_MODE
---------- ---------- ------------------------------ ----------
2 4062472313 PDB$SEED READ WRITE
1 row selected.
SQL>
SQL> SELECT NAME from v$database;
NAME
---------
CDB
1 row selected.
Step 5:
Configure Enterprise Manager Express for CDB
Connect AS SYSDBA and run
exec DBMS_XDB_CONFIG.SETHTTPSPORT(https_port_number);
Step 6:
The URL will be: https://database_hostname:https_port_number/em/
Create a pfile with following parameters
cat /tmp/pfile_CDB.ora
*.audit_file_dest='/opt/app/oracle/admin/cdb/adump'
*.audit_trail='db'
*.compatible='12.1.0.0.0'
*.control_files='+DATA/CDB/CONTROLFILE/control.ctl'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='CDB'
*.db_recovery_file_dest='+DATA'
*.db_recovery_file_dest_size=4800m
*.diagnostic_dest='/opt/app/oracle'
*.enable_pluggable_database=true
*.open_cursors=300
*.pga_aggregate_target=200m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=600m
*.undo_tablespace='UNDOTBS1'
Note: enable_pluggable_database, db_name, control_files are must be mentioned.
Step 2:
Start the new CDB in NOMOUNT mode
sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Thu Jul 4 10:07:21 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> STARTUP NOMOUNT pfile='/tmp/pfile_CDB.ora';
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2291472 bytes
Variable Size 180357360 bytes
Database Buffers 436207616 bytes
Redo Buffers 7471104 bytes
Step 3:
Create the CDB using CREATE DATABASE Statement; Note the following options / parameters:
– SEED FILE_NAME_CONVERT:
This clause specifies how to generate the names of the seed’s files using the names of root’s files. This clause must be added to the “CREATE DATABASE” statement.
– PDB_FILE_NAME_CONVERT Initialization Parameter:
specify the names and locations of the seed’s files. The seed can be used as a template to create new Pluggable databases (PDB).
SQL> CREATE DATABASE CDB
USER SYS IDENTIFIED BY Wissem123
USER SYSTEM IDENTIFIED BY Wissem123
LOGFILE GROUP 1 ('+DATA/CDB/ONLINELOG/redo01a.log','+DATA/CDB/ONLINELOG/redo01b.log')
SIZE 20M BLOCKSIZE 512,
GROUP 2 ('+DATA/CDB/ONLINELOG/redo02a.log','+DATA/CDB/ONLINELOG/redo02b.log')
SIZE 20M BLOCKSIZE 512,
GROUP 3 ('+DATA/CDB/ONLINELOG/redo03a.log','+DATA/CDB/ONLINELOG/redo03b.log')
SIZE 20M BLOCKSIZE 512
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 1024
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL
DATAFILE '+DATA/CDB/DATAFILE/CDB/system01.dbf'
SIZE 100M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
SYSAUX DATAFILE '+DATA/CDB/DATAFILE/CDB/sysaux01.dbf'
SIZE 100M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
DEFAULT TABLESPACE deftbs
DATAFILE '+DATA/CDB/DATAFILE/CDB/deftbs01.dbf'
SIZE 100M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE TEMP
TEMPFILE '+DATA/CDB/DATAFILE/CDB/temp01.dbf'
SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
UNDO TABLESPACE undotbs1
DATAFILE '+DATA/CDB/DATAFILE/CDB/undotbs01.dbf'
SIZE 100M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
ENABLE PLUGGABLE DATABASE
SEED
FILE_NAME_CONVERT = ('+DATA/CDB/DATAFILE/CDB/',
'+DATA/CDB/DATAFILE/pdbseed/')
SYSTEM DATAFILES SIZE 125M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
SYSAUX DATAFILES SIZE 100M
USER_DATA TABLESPACE usertbs
DATAFILE '+DATA/CDB/DATAFILE/usertbs01.dbf'
SIZE 100M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
Database created.
You may have the following error during CREATE DATABASE statement:
ORA-65005: missing or invalid file name pattern for file - string
Cause: Either source or replacement file name pattern was missing or invalid in a SOURCE_FILE_NAME_CONVERT or FILE_NAME_CONVERT clause.
Action: Correct the SOURCE_FILE_NAME_CONVERT or FILE_NAME_CONVERT clause and reissue the statement.
Step 4:
Open the Pluggable database
SQL> alter session set "_oracle_script"=true;
Session altered.
SQL> alter pluggable database pdb$seed close;
Pluggable database altered.
SQL> alter pluggable database pdb$seed open;
alter pluggable database pdb$seed open
ERROR at line 1:
ORA-00704: bootstrap process failure
ORA-00942: table or view does not exist
-- repeat again
SQL> alter pluggable database pdb$seed open;
Pluggable database altered.
Enter the following in SQL*Plus to run the scripts:
@$ORACLE_HOME/rdbms/admin/catalog.sql
@$ORACLE_HOME/rdbms/admin/catblock.sql
@$ORACLE_HOME/rdbms/admin/catproc.sql
@$ORACLE_HOME/rdbms/admin/catoctk.sql
@$ORACLE_HOME/rdbms/admin/owminst.plb
@$ORACLE_HOME/sqlplus/admin/pupbld.sql
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;
CON_ID DBID NAME OPEN_MODE
---------- ---------- ------------------------------ ----------
2 4062472313 PDB$SEED READ WRITE
1 row selected.
SQL>
SQL> SELECT NAME from v$database;
NAME
---------
CDB
1 row selected.
Step 5:
Configure Enterprise Manager Express for CDB
Connect AS SYSDBA and run
exec DBMS_XDB_CONFIG.SETHTTPSPORT(https_port_number);
Step 6:
The URL will be: https://database_hostname:https_port_number/em/