How to create a database manually in Oracle 11gR2
Posted by Mir Sayeed Hassan on February 10th, 2019
How to create a database manually in Oracle 11gR2
An Oracle database can be created either using manually or DBCA or at the time of installation of the Oracle software, the below given step by step process is to manually creation of database, Analyse the requirement of the database from the client size regarding the storage of db etc
Pre-require site & Execution Process
————————————————–
-
Installation of the OS – Oracle Linux 6.7 or Higher & Install the Oracle database software in it
-
Set the environment variable in bash profile & execute the bash profile
-
Create the require OS Level directories for database creation
-
Create the PFILE in default location in $ORACLE_HOME/dbs
-
Assign the IP & set the hostname for database in /etc/hosts
-
Create the script for manually creation of database
-
Create the listener & tnsnames file in default location $ORACLE_HOME/network/admin
In my case the Oracle linux 6.7 & Oracle software is install init.
Create the environment variable in Bash Profile
[oracle@testdb ~]$ vi .bash_profile # .bash_profile # Get the aliases and functions if [ -f ~/.bashrc ]; then . ~/.bashrc fi # User specific environment and startup programs PATH=$PATH:$HOME/.local/bin:$HOME/bin export PATH # Oracle variables TMP=/tmp; export TMP TMPDIR=$TMP; export TMPDIR ORACLE_HOSTNAME=testdb; export ORACLE_HOSTNAME ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1; export ORACLE_HOME ORACLE_SID=testdb; export ORACLE_SID ORACLE_UNQNAME=testdb; export ORACLE_UNQNAME PATH=/usr/sbin:$PATH; export PATH PATH=$ORACLE_HOME/bin:$PATH; export PATH LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH :wq
Run the bash profile
[oracle@testdb ~]$ . .bash_profile
[oracle@testdb ~]$ echo $ORACLE_SID testdb [oracle@testdb ~]$ echo $ORACLE_HOME /u01/app/oracle/product/11.2.0/dbhome_1
Create a directory structure
[oracle@testdb ~]$ cd /u01/app/oracle/ [oracle@testdb oracle]$ mkdir testdb [oracle@testdb oracle]$ cd testdb/ [oracle@testdb testdb]$ ]$ mkdir admin oradata diag flash_recovery_area controlfile [oracle@testdb testdb]$ cd admin/ [oracle@testdb admin]$ mkdir adump bdump cdump [oracle@testdb testdb]$ ls admin diag flash_recovery_area oradata controlfile [oracle@testdb oracle]$ chown -R oracle:oinstall testdb/ [oracle@testdb oracle]$ ll drwxrwxr-x. 6 oracle oinstall 69 Feb 10 14:57 testdb
Create a pfile in default location $ORACLE_HOME/dbs
[oracle@testdb ~]$ cd $ORACLE_HOME/dbs [oracle@testdb dbs]$ vi inittestdb.ora db_name='testdb' compatible='11.2.0' db_block_size=8192 audit_file_dest='/u01/app/oracle/testdb/admin/adump' background_dump_dest='/u01/app/oracle/testdb/admin/bdump' core_dump_dest='/u01/app/oracle/testdb/admin/cdump' control_files='/u01/app/oracle/testdb/controlfile/control1.ctl' db_recovery_file_dest='/u01/app/oracle/testdb/flash_recovery_area' db_recovery_file_dest_size=2G diagnostic_dest='/u01/app/oracle/testdb/diag' log_archive_format='%t_%s_%r.arc' undo_management='AUTO' undo_tablespace='UNDOTBS' open_cursors=600 memory_target=2g :wq!
Set the hostname:
[root@testdb etc]# cat hosts 127.0.0.1 localhost localhost.localdomain 10.20.0.123 testdb
Create a database scripts
[oracle@testdb dbs]$ cd /home/oracle/scripts/ [oracle@testdb scripts]$ vi create_db.sql CREATE DATABASE testdb MAXLOGFILES 5 MAXLOGMEMBERS 5 MAXDATAFILES 100 MAXINSTANCES 1 LOGFILE GROUP 1 '/u01/app/oracle/testdb/oradata/redo1.log' SIZE 50M, GROUP 2 '/u01/app/oracle/testdb/oradata/redo2.log' SIZE 50M, GROUP 3 '/u01/app/oracle/testdb/oradata/redo3.log' SIZE 50M DATAFILE '/u01/app/oracle/oradata/system.dbf' size 300M sysaux datafile '/u01/app/oracle/testdb/oradata/sysaux.dbf’ size 100m undo tablespace UNDOTBS datafile '/u01/app/oracle/testdb/oradata/undo.dbf' size 100m DEFAULT TEMPORARY TABLESPACE temp TEMPFILE '/u01/app/oracle/testdb/oradata/temp.dbf' SIZE 100M CHARACTER SET UTF8; :wq!
Create the listener & tnsnames
[oracle@testdb admin]$ cat listener.ora LISTENER = (DESCRIPTION= (ADDRESS=(PROTOCOL=TCP)(HOST=testdb)(PORT=1521)) ) SID_LIST_LISTENER = (SID_LIST= (SID_DESC= (SID_NAME=testdb) (ORACLE_HOME= /u01/app/oracle/product/11.2.0/dbhome_1) ) ) ADR_BASE_LISTENER = /u01/app/oracle [oracle@testdb admin]$ cat tnsnames.ora TESTDB = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.20.0.123)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = testdb) ) )
Start the process of manually creation of database
Startup database in nomount
testdb> startup pfile='$ORACLE_HOME/dbs/inittestdb.ora' nomount ORACLE instance started. Total System Global Area 2137886720 bytes Fixed Size 2254952 bytes Variable Size 1241515928 bytes Database Buffers 889192448 bytes Redo Buffers 4923392 bytes
testdb> select status from V$instance; STATUS ------------ STARTED
Execute the manually creation script
testdb> @/home/oracle/scripts/create_db.sql; Database created.
Verify the database
testdb> select instance_name,status,version from V$instance; INSTANCE_NAME STATUS VERSION ---------------- ------------ ----------------- testdb OPEN 11.2.0.4.0
Therefore the database is successfully created by using the manual method, now there are 2 mandatory scripts to run, this are data dictionary views (CATALOG and CATPROC) & 1 pupbld.sql scripts
testdb> @$ORACLE_HOME/rdbms/admin/catalog.sql; testdb> @$ORACLE_HOME/rdbms/admin/catproc.sql;
(The above scripts take approx. around 15 mins to complete)
Note:
catalog.sql – It Creates dictionary tables and views
catproc.sql – It Creates PL/SQL procedures, functions & packages necessary
pupbld.sql – It creates user profiles
testdb> connect system/manager Connected. system@TESTDB> @$ORACLE_HOME/sqlplus/admin/pupbld.sql;
Now the database is running with pfile
sys@TESTDB> show parameter pfile NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string
Create the spfile from pfile & shutdown & startup the database
sys@TESTDB> create spfile from pfile; File created.
sys@TESTDB> shu immediate Database closed. Database dismounted. ORACLE instance shut down. sys@TESTDB> startup ORACLE instance started. Total System Global Area 2137886720 bytes Fixed Size 2254952 bytes Variable Size 1241515928 bytes Database Buffers 889192448 bytes Redo Buffers 4923392 bytes Database mounted. Database opened.
sys@TESTDB> show parameter pfile NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfiletestdb.ora
Verify the status of the listener & tnsnames
[oracle@testdb ~]$ lsnrctl status LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 10-FEB-2019 16:13:03 Copyright (c) 1991, 2013, Oracle. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production Start Date 10-FEB-2019 15:57:41 Uptime 0 days 0 hr. 15 min. 21 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/testdb/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=testdb)(PORT=1521))) Services Summary... Service "testdb" has 1 instance(s). Instance "testdb", status READY, has 1 handler(s) for this service... The command completed successfully
[oracle@testdb ~]$ tnsping testdb TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 10-FEB-2019 16:13:09 Copyright (c) 1997, 2013, Oracle. All rights reserved. Used parameter files: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.20.0.123)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = testdb))) OK (0 msec)
===========Hence its tested & verified in our test env==========