In this post, we are going to learn “how to create an Oracle database manually”. The database in Oracle can be created using DBCA and using the manual method. it would be a good idea to create a database manually at least once to understand how a database is created step by step in Oracle.
Step 1: Decide the name of your database instance(Here I have used MTEST)
|
1 2 3 |
[oracle@stagdb ~]$ [oracle@stagdb ~]$export ORACLE_SID=MTEST |
Also, make sure that ORACLE_HOME is set
Step 2: Create a pfile with the minimal and mandatory requirements
[oracle@stagdb ~]$ cd $ORACLE_HOME/dbs
[oracle@stagdb dbs]$ vi initMTEST.ora
*.audit_file_dest=’/u01/app/admin/MTEST/adump’
*.audit_trail=’db’
*.compatible=’11.2.0.0.0′
*.control_files=’/u01/MTEST/control01.ctl’,’/u01/MTEST/control02.ctl’
*.db_block_size=8192
*.db_name=’MTEST’
*.diagnostic_dest=’/u01/app’
*.SGA_TARGET=2G
*.undo_tablespace=’UNDOTBS1′
save and exit.
Step 3: Create a required directory structure.
|
1 2 3 4 5 |
[oracle@stagdb dbs]$ [oracle@stagdb dbs]$ mkdir -p /u01/app/admin/MTEST/adump [oracle@stagdb dbs]$ mkdir -p /u01/MTEST |
Step 4: Connect to the sql prompt and startup with nomount option.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
[oracle@stagdb dbs]$ [oracle@stagdb dbs]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 22 15:56:46 2018 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to an idle instance. SYS> startup nomount; ORACLE instance started. Total System Global Area 2137886720 bytes Fixed Size 2230072 bytes Variable Size 503318728 bytes Database Buffers 1627389952 bytes Redo Buffers 4947968 bytes SYS> |
Step 5: Issue the create database statement
create database MTEST
datafile ‘/u01/MTEST/system01.dbf’ size 500M
sysaux datafile ‘/u01/MTEST/sysaux.dbf’ size 100m
undo tablespace UNDOTBS1
datafile ‘/u01/MTEST/UNDOTBS01.dbf’ size 100m
default temporary tablespace temp
tempfile ‘/u01/MTEST/tmpp01.dbf’ size 100m
logfile
group 1 ‘/u01/MTEST/redolog1.ora’ size 50m,
group 2 ‘/u01/MTEST/redolog2.ora’ size 50m;
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
SYS> SYS> create database MTEST 2 datafile '/u01/MTEST/system01.dbf' size 500M 3 sysaux datafile '/u01/MTEST/sysaux.dbf' size 100m 4 undo tablespace UNDOTBS1 5 datafile '/u01/MTEST/UNDOTBS01.dbf' size 100m 6 default temporary tablespace temp 7 tempfile '/u01/MTEST/tmpp01.dbf' size 100m 8 logfile 9 group 1 '/u01/MTEST/redolog1.ora' size 50m, 10 group 2 '/u01/MTEST/redolog2.ora' size 50m; Database created. |
Step 6: Run Scripts to Build Data Dictionary Views
|
1 2 3 4 |
@?/rdbms/admin/catalog.sql @?/rdbms/admin/catproc.sql @?/sqlplus/admin/pupbld.sql EXIT |
Here,
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.
connect sys/passwd
Important dictionary views
|
1 2 3 4 5 6 7 8 9 |
v$sga v$instance v$database v$process v$sysaux_occupants |
Words from dbapath
Thank you for giving your valuable time to read the above information. I hope the content served your purpose in reaching out the blog.
Suggestions for improvement of the blog are highly appreciable. Please contact us for any information/suggestion/feedback.
If you want to be updated with all our articles
please follow us on Facebook | Twitter
Please subscribe to our newsletter.