Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]

Search This Blog

Thursday, September 3, 2015

Create Database using SQLPLUS


How to create a 11gR2 database using SQLPLUS.

Create directories:

rm -rf /u01/app/oracle/SHAIKDB
mkdir -p /u01/app/oracle/SHAIKDB/fra
mkdir -p /u01/app/oracle/SHAIKDB/adump
mkdir -p /u01/app/oracle/SHAIKDB/

cd $ORACLE_HOME/dbs
cp init.ora initSHAIKDB.ora

Change the directory locations and DBNAME.

vi initSHAIKDB.ora
db_name='SHAIKDB'
memory_target=450m
processes = 150
audit_file_dest='/u01/app/oracle/SHAIKDB/adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest='/u01/app/oracle/SHAIKDB/fra'
db_recovery_file_dest_size=2G
diagnostic_dest='/u01/app/oracle/SHAIKDB'
dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
control_files = '/u01/app/oracle/SHAIKDB/control01.ctl','/u01/app/oracle/SHAIKDB/control02.ctl'
compatible ='11.2.0'

Create DB creation script:

create database SHAIKDB
 user sys identified by test1
  user system identified by test1
logfile group 1 ('/u01/app/oracle/SHAIKDB/redo1.log','/u01/app/oracle/SHAIKDB/redo2.log') size 100m,
group 2 ('/u01/app/oracle/SHAIKDB/redo3.log','/u01/app/oracle/SHAIKDB/redo4.log') size 100m,
group 3 ('/u01/app/oracle/SHAIKDB/redo5.log','/u01/app/oracle/SHAIKDB/redo6.log') size 100m
character set al32utf8
national character set al16utf16
datafile '/u01/app/oracle/SHAIKDB/sys01.dbf' size 100m autoextend on maxsize unlimited
sysaux datafile '/u01/app/oracle/SHAIKDB/sysaux01.dbf' size 100m autoextend on maxsize unlimited
default tablespace users datafile '/u01/app/oracle/SHAIKDB/users01.dbf' size 100m autoextend on maxsize unlimited
default temporary tablespace temp tempfile '/u01/app/oracle/SHAIKDB/temp01.dbf' size 100m autoextend on maxsize unlimited
undo tablespace undotbs1 datafile '/u01/app/oracle/SHAIKDB/undo01.dbf' size 100m autoextend on maxsize unlimited;


vi postdb_create.sql
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
@?/sqlplus/admin/pupbld.sql



[oracle@collabn1 oracle]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Sep 3 08:15:12 2015

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area  471830528 bytes
Fixed Size            2214456 bytes
Variable Size          301991368 bytes
Database Buffers      163577856 bytes
Redo Buffers            4046848 bytes
SQL> @createdb.sql

--
---
---
database created.




SQL>@postdbcreate.sql
--
--

Synonym created.

SQL>
SQL> -- End of pupbld.sql



SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0    Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production


COMP_NAME                 STATUS           VERSION
---------------------------------------- -------------------- ------------------------------
Oracle Database Catalog Views         VALID              11.2.0.1.0
Oracle Database Packages and Types     VALID              11.2.0.1.0

No comments: