Create 12c database manually using database creation script.

Create 12c database manually using database creation script.

Note:- database is a simple database not container database

--->OS version:

[JAY@O12c pfile]# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 5.4 (Tikanga)
[JAY@O12c pfile]#

--->Database version:

[oracle@O12c ~]$ sqlplus
SQL*Plus: Release 12.1.0.1.0 Production on Sat Jul 18 07:35:10 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

--->Create following directory on server and give permission to oracle user and oinstall group.

[JAY@O12c cdb1]#mkdir -p /u01/app/oracle/admin/orcl/dpdump
[JAY@O12c cdb1]#mkdir -p /u01/app/oracle/admin/orcl/adump
[JAY@O12c cdb1]#mkdir -p /u01/app/oracle/admin/orcl/pfile
[JAY@O12c cdb1]#mkdir -p /u01/app/oracle/oradata/orcl/controlfile
[JAY@O12c cdb1]#mkdir -p/u01/app/oracle/fast_recovery_area/orcl/controlfile/
[JAY@O12c cdb1]#chomd 775 /u01/app/oracle/
[JAY@O12c cdb1]#chown oracle:oinstall /u01/app/oracle/

--->Create pfile(init_orcl.ora) in pfile directory

[JAY@O12c cdb1]$ cd /u01/app/oracle/admin/orcl/pfile
[JAY@O12c pfile]$ vi init_orcl.ora
##############################################################################
# Copyright (c) 1991, 2013 by Oracle Corporation
##############################################################################
###########################################
# Cache and I/O
###########################################
db_block_size=8192
###########################################
# Cursors and Library Cache
###########################################
open_cursors=300
###########################################
# Database Identification
###########################################
db_domain=""
db_name="orcl"
###########################################
# File Configuration
###########################################
db_create_file_dest="/u01/app/oracle/oradata"
db_recovery_file_dest="/u01/app/oracle/fast_recovery_area"
db_recovery_file_dest_size=4800m
###########################################
# Miscellaneous
###########################################
compatible=12.1.0.0.0
diagnostic_dest=/u01/app/oracle
###########################################
# Processes and Sessions
###########################################
processes=300
###########################################
# SGA Memory
###########################################
sga_target=1269m
###########################################
# Security and Auditing
###########################################
audit_file_dest="/u01/app/oracle/admin/orcl/adump"
audit_trail=db
remote_login_passwordfile=EXCLUSIVE
###########################################
# Shared Server
###########################################
dispatchers="(PROTOCOL=TCP) (SERVICE=cdb1XDB)"
###########################################
# Sort, Hash Joins, Bitmap Indexes
###########################################
pga_aggregate_target=423m
###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_tablespace=UNDOTBS1
control_files=("/u01/app/oracle/oradata/orcl/controlfile/o1_mf_bsmgyw36_.ctl",
"/u01/app/oracle/fast_recovery_area/orcl/controlfile/o1_mf_bsmgyw7v_.ctl")

--->Set ORACLE_SID variable

[oracle@O12c dbs]$ export ORACLE_SID=orcl
[oracle@O12c dbs]$ echo $ORACLE_SID
orcl
[oracle@O12c dbs]$

--->Create password file

[oracle@O12c dbs]$orapwd file=/u01/app/oracle/product/12.1.0.2/db_1/dbs/orapworcl password=Admin entries=10
[oracle@O12c dbs]$ cd /u01/app/oracle/product/12.1.0.2/db_1/dbs
[oracle@O12c dbs]$ ls -ltr
total 32
-rw-r--r-- 1 oracle oinstall 2992 Feb 3 2012 init.ora
-rw-rw---- 1 oracle oinstall 1544 Jul 6 04:48 hc_cdb1.dat
-rw-r----- 1 oracle oinstall 24 Jul 6 04:48 lkCDB1
-rw-r----- 1 oracle oinstall 7680 Jul 6 04:51 orapwcdb1
-rw-r----- 1 oracle oinstall 3584 Jul 18 07:30 spfilecdb1.ora
-rw-r----- 1 oracle oinstall 7680 Jul 18 08:41 orapworcl

-->Login to database

[oracle@O12c dbs]$ sqlplus
SQL*Plus: Release 12.1.0.1.0 Production on Sat Jul 18 08:46:42 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to an idle instance.
SQL> create spfile from pfile='/u01/app/oracle/admin/orcl/pfile/init_orcl.ora';
File created.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 1336176640 bytes
Fixed Size 2288104 bytes
Variable Size 469763608 bytes
Database Buffers 855638016 bytes
Redo Buffers 8486912 bytes

SQL>@DBcreation.sql;
Database created.

-------->/*Create database script (DBcreation.sql)..
[JAY@O12c ~]# cd /u01/app/oracle/product/12.1.0.2/db_1/dbs
[JAY@O12c dbs]# vi DBcreation.sql
CREATE DATABASE orcl
USER SYS IDENTIFIED BY sys_password
USER SYSTEM IDENTIFIED BY system_password
LOGFILE GROUP 1 ('/u01/app/oracle/oradata/orcl/redo01a.log','/u01/app/oracle/oradata/orcl/redo01b.log') SIZE 100M BLOCKSIZE 512,
GROUP 2 ('/u01/app/oracle/oradata/orcl/redo02a.log','/u01/app/oracle/oradata/orcl/redo02b.log') SIZE 100M BLOCKSIZE 512,
GROUP 3 ('/u01/app/oracle/oradata/orcl/redo03a.log','/u01/app/oracle/oradata/orcl/redo03b.log') SIZE 100M BLOCKSIZE 512
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 1024
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL
DATAFILE '/u01/app/oracle/oradata/orcl/system01.dbf'
SIZE 700M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
SYSAUX DATAFILE '/u01/app/oracle/oradata/orcl/sysaux01.dbf'
SIZE 550M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
DEFAULT TABLESPACE users
DATAFILE '/u01/app/oracle/oradata/orcl/users01.dbf'
SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE tempts1
TEMPFILE '/u01/app/oracle/oradata/orcl/temp01.dbf'
SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
UNDO TABLESPACE UNDOTBS1
DATAFILE '/u01/app/oracle/oradata/orcl/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
USER_DATA TABLESPACE usertbs
DATAFILE '/u01/app/oracle/oradata/orcl/usertbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED; */<-----------------------

---> Run below script for build database scripts

SQL>@/u01/app/oracle/product/12.1.0.2/db_1/rdbms/admin/catalog.sql;
(Creates the views of the data dictionary tables, the dynamic performance views,
And public synonyms for many of the views. Grants PUBLIC access to the synonyms)

SQL>@/u01/app/oracle/product/12.1.0.2/db_1/rdbms/admin/catproc.sql;
(Runs all scripts required for or used with PL/SQL.)

SQL> alter user system
2 identified by manager;
User altered.
SQL> alter user system account unlock;
User altered.
SQL>conn system/manager
Connected.
SQL>
SQL>@/u01/app/oracle/product/12.1.0.2/db_1/sqlplus/admin/pupbld.sql;
(Required for SQL*Plus. Enables SQL*Plus to disable commands by user.)

---> Bounce the database

SQL> startup force;
ORACLE instance started.
Total System Global Area 1336176640 bytes
Fixed Size 2288104 bytes
Variable Size 469763608 bytes
Database Buffers 855638016 bytes
Redo Buffers 8486912 bytes
Database mounted.
Database opened.
SQL> select name from v$database;
NAME
---------
ORCL
1 row selected.
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/users01.dbf
/u01/app/oracle/oradata/orcl/usertbs01.dbf
5 rows selected.

-->Create extra tablespace..

SQL>CREATE TABLESPACE apps_tbs LOGGING
DATAFILE '/u01/app/oracle/oradata/orcl/apps01.dbf'
SIZE 500M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL;
Tablespace created.

-- Create a tablespace for indexes, separate from user tablespace (optional)
SQL> CREATE TABLESPACE indx_tbs LOGGING
DATAFILE '/u01/app/oracle/oradata/orcl/indx01.dbf'
SIZE 100M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL;
Tablespace created.
SQL> select NAME from v$tablespace;
NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMPTS1
USERS
USERTBS
APPS_TBS
INDX_TBS
8 rows selected.
SQL>