Manual Oracle 11g r2 Database creation on Linux
--->>> Overview of Database creation
--->>> Set environment variable by editing ‘.DEV.env' file.
--->>> Create the Initialization Parameter File
--->>> Create necessary directory structure.
--->>> Create database creation script
--->>> Start the Instance
--->>> Run the Database creation script at SQL prompt
--->>> Run Scripts to Build Data Dictionary Views
--->>>Check the Database name and status
--->>> Overview of Database creation
Using the CREATE DATBASE SQL statement is a more manual approach to creating a database. If you use the CREATE DATABASE statement, you must complete additional actions before you have an operational database. These actions include building views on the data dictionary tables and installing standard PL/SQL packages. You perform these actions by running prepared scripts.
Specify an Instance Identifier (SID)
Decide on a unique Oracle system identifier (SID) for your instance, open a command window, and set the ORACLE_SID environment variable. Use this command windows for the subsequent steps.
The maximum number of characters for ORACLE_SID is 12, and only letters and numeric digits are permitted. On some platforms, the SID is case-sensitive.
The following example for UNIX and Linux operating systems
Bourne, Bash, or Korn shell:
ORACLE_SID=DEV
export ORACLE_SID
The Required Environment Variables
Before you can start SQL*Plus, you may have to set environment variables
For example, ORACLE_SID and ORACLE_HOME must be set. In addition, it is advisable to set the PATH variable to include the ORACLE_HOME/bin directory. On the UNIX and Linux platforms, you must set these environment variables manually.
--->>> Set environment variable by editing ‘.DEV.env' file.
[oracle@linux ~]$ pwd
/home/oracle
[oracle@linux ~]$ vi DEV.env
ORACLE_BASE=/u01/app/DEV; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0.4; export ORACLE_HOME
ORACLE_SID=DEV; export ORACLE_SID
PATH=$ORACLE_HOME/bin:$PATH; export PATH
[oracle@linux ~]$
[oracle@linux ~]$. DEV.env
--->>> Create the Initialization Parameter File
When an Oracle instance starts, it reads an initialization parameter file. This file can be a text file, which can be created and modified with a text editor, or a binary file, which is created and dynamically modified by the database. The binary file, which is preferred, is called a server parameter file. In this step, you create a text initialization parameter file. In a later step, you create a server parameter file from the text file.
go to $ORACLE_HOME/dbs
[oracle@linux dbs]$ pwd
/u01/app/DEV/product/11.2.0.4/dbs
[oracle@linux dbs]$ vi initDEV.ora
DEV.__db_cache_size=247463936
DEV.__java_pool_size=4194304
DEV.__large_pool_size=8388608
DEV.__oracle_base='/u01/app/DEV'#ORACLE_BASE set from environment
DEV.__pga_aggregate_target=251658240
DEV.__sga_target=373293056
DEV.__shared_io_pool_size=0
DEV.__shared_pool_size=104857600
DEV.__streams_pool_size=0
*.audit_file_dest='/u01/app/DEV/admin/DEV/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/DEV/oradata/DEV/control01.ctl','/u01/app/DEV/fast_recovery_area/DEV/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='DEV'
*.db_recovery_file_dest='/u01/app/DEV/fast_recovery_area'
*.db_recovery_file_dest_size=4385144832
*.diagnostic_dest='/u01/app/DEV'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=DEVXDB)'
#*.local_listener='LISTENER_DEV'
*.memory_target=624951296
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
[oracle@linux dbs]$
--->>> Create directory structure
$mkdir -p /u01/app/DEV/oradata/
$mkdir -p /u01/app/DEV/admin/DEV/adump
$mkdir -p /u01/app/DEV/oradata/DEV/
$mkdir -p /u01/app/DEV/fast_recovery_area/DEV/
--->>> Create database creation script
Before you create the database make sure you have done the planning about the size of the database, number of tablespaces and redo log files you want in the database.
The following script creates database DEV. This database name must agree with the DB_NAME parameter in the initialization parameter file. This example assumes the following:
The initialization parameter file specifies the number and location of control files with the CONTROL_FILES parameter.
[oracle@linux ~]$ pwd
/home/oracle
[oracle@linux ~]$ vi DEV.sql
CREATE DATABASE "DEV"
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/DEV/oradata/redo1.log' SIZE 100M,
GROUP 2 '/u01/app/DEV/oradata/redo2.log' SIZE 100M,
GROUP 3 '/u01/app/DEV/oradata/redo3.log' SIZE 100M
DATAFILE
'/u01/app/DEV/oradata/system.dbf' size 100m,
'/u01/app/DEV/oradata/usr04.dbf' size 200m
sysaux datafile '/u01/app/DEV/oradata/sysaux.dbf' size 100m
undo tablespace UNDOTBS1
datafile '/u01/app/DEV/oradata/undo.dbf' size 50m
[oracle@linux ~]$
--->>> Start the Instance
[oracle@linux ~]$ pwd
/home/oracle
[oracle@linux ~]$. DEV.env
[oracle@linux ~]$cd $ORACLE_HOME/dbs
[oracle@linux dbs]$sqlplus '/as sysdba'
SQL> startup nomount pfile='initDEV.ora';
ORACLE instance started.
Total System Global Area 622149632 bytes
Fixed Size 2255792 bytes
Variable Size 423625808 bytes
Database Buffers 192937984 bytes
Redo Buffers 3330048 bytes
sql>
Create Server parameter file (SPFILE) using this parameter file and STARTUP the instance in NOMOUNT mode.
--->>> Run the Database creation script at SQL prompt
sql>@/home/oracle/DEV.sql
Database created.
SQL>
--->>> Run Scripts to Build Data Dictionary Views
Run the scripts necessary to build data dictionary views, synonyms, and PL/SQL packages, and to support proper functioning of SQL*Plus:
$sqlplus '/as sysdba'
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
@?/sqlplus/admin/pupbld.sql
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.
CATPROC.SQL :- Runs all scripts required for or used with PL/SQL.
PUPBLD.SQL:- Required for SQL*Plus. Enables SQL*Plus to disable commands by user.
--->>> Check the Database name and status
Open new terminal as oracle user
Run env file
$. DEV.env
$sqlplus ‘/as sysdba’
SQL> select name from v$database;
NAME
---------
DEV
SQL> select status from v$instance;
STATUS
------------
OPEN
SQL>