Overview
This is a sample procedure a non-CDB Oracle 12.1.0.2 database with OMF.
References
Procedure
Preliminaries
The procedure to follow can be found in Creating a Database with the CREATE DATABASE Statement .
Step 1: Specify an Instance Identifier (SID)
The database and instance name is CAN .
Step 2: Ensure That the Required Environment Variables Are Set
The following update is made to /etc/oratab :
can:/opt/app/oracle/product/12.1.0/dbhome_1:N
The appropriate environment variables are set as follows:
. oraenv
Step 3: Choose a Database Administrator Authentication Method
The password file is created as follows:
cd ${ORACLE_HOME}/dbs
orapwd file=orapwd${ORACLE_SID}
Step 4: Create the Initialization Parameter File
The following data is entered into /home/oracle/initcan.ora :
*.audit_file_dest='/opt/app/oracle/admin/can/adump'
*.audit_trail='db'
*.compatible='12.1.0.2.0'
*.db_block_size=8192
*.db_create_file_dest='/opt/app/oracle/oradata'
*.db_create_online_log_dest_1='/opt/app/oracle/oradata'
*.db_create_online_log_dest_2='/opt/app/oracle/fast_recovery_area'
*.db_domain='yaocm.id.au'
*.db_name='can'
*
.db_recovery_file_dest='/opt/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4560m
*.diagnostic_dest='/opt/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=canXDB)'
*.memory_target=1470m
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
The following directories are created:
mkdir -p /opt/app/oracle/admin/can/adump
mkdir -p /opt/app/oracle/oradata
mkdir -p /opt/app/oracle/fast_recovery_area
Step 6: Connect to the Instance
Instead of following the procedure, I used the following commands instead:
sqlplus / as sysdba
startup nomount pfile=/home/oracle/initcan.ora
The reason is to check the syntax and validity of the initialization parameters before creating the spfile.
Step 7: Create a Server Parameter File
The spfile is created in the default location ( ${ORACLE_HOME}/dbs ) as follows:
CREATE SPFILE FROM PFILE='/home/oracle/initcan.ora';
Step 8: Start the Instance
The instance is started using the spfile:
startup nomount force
Step 9: Issue the CREATE DATABASE Statement
The database is created using the following command:
create database can
user sys identified by "&pw_sys"
user system identified by "&pw_system"
logfile group 1 size 50m,
group 2 size 50m,
group 3 size 50m
character set al32utf8 national character set al16utf16
set default bigfile tablespace
archivelog
set time_zone='+10:00'
extent management local
default temporary tablespace temp
undo tablespace undotbs1
/
create tablespace users;
alter database default tablespace users;
The default tablespace, USERS, is created separately because I have found the CREATE DATABASE command to fail when using BIGFILE as the default.
Step 11: Run Scripts to Build Data Dictionary Views
The following commands are run:
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
connect system/&pw_system
@?/sqlplus/admin/pupbld.sql