9/09/2017

How to create oracle database with sqlplus


In case you want to create an Oracle Database, without the GUI (dbca), use the following simple steps: 

1. First set the enviroment for the oracle user.


export ORACLE_HOME=/oradb/CUSTDB/orabin/11.2.0
export PATH=/oradb/CUSTDB/orabin/11.2.0/bin:$PATH
export ORACLE_SID=CUSTDB
export TNS_ADMIN=/oradb/CUSTDB/orabin/11.2.0/network/admin

2. Create the initSID.ora (for example SID=CUSTDB) 

The file usually is located in the $ORACLE_HOME/dbs/initSID.ora

db_name='CUSTDB'
memory_target=1G
processes = 500
audit_file_dest='/oradb/CUSTDB/orabin/11.2.0/rdbms/audit'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest='/oradb/CUSTDB/oraarch'
db_recovery_file_dest_size=4G
diagnostic_dest='/oradb/CUSTDB/orabin/oradiag_oracust'
open_cursors=300 
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS'
control_files = (/oradb/CUSTDB/oradata1/controlfiles/control1.ctl,
/oradb/CUSTDB/oradata2/controlfiles/control2.ctl,
/oradb/CUSTDB/oradata2/controlfiles/control2.ctl)
compatible ='11.2.0'

3. Login as oracle user and startup nomount the database 

sqlplus "/ as sysdba" 
SQL>startup nomount pfile=$ORACLE_HOME/dbs/initSID.ora 

4. Run the script to create the database


CREATE DATABASE CUSTDB
   USER SYS IDENTIFIED BY password
   USER SYSTEM IDENTIFIED BY password
   LOGFILE GROUP 1 ('/oradb/CUSTDB/oradata1/redologs/redo01_a.rdlog',
 '/oradb/CUSTDB/oradata2/redologs/redo01_b.rdlog') SIZE 1024M,
           GROUP 2 ('/oradb/CUSTDB/oradata2/redologs/redo02_a.rdlog',
 '/oradb/CUSTDB/oradata3/redologs/redo02_b.rdlog') SIZE 1024M,
           GROUP 3 ('/oradb/CUSTDB/oradata3/redologs/redo03_a.rdlog',
 '/oradb/CUSTDB/oradata1/redologs/redo03_b.rdlog') SIZE 1024M,
           GROUP 4 ('/oradb/CUSTDB/oradata1/redologs/redo04_a.rdlog',
 '/oradb/CUSTDB/oradata2/redologs/redo04_b.rdlog') SIZE 1024M
   MAXLOGFILES 5
   MAXLOGMEMBERS 5
   MAXLOGHISTORY 1
   MAXDATAFILES 1000
   CHARACTER SET AL32UTF8
   NATIONAL CHARACTER SET AL16UTF16
   EXTENT MANAGEMENT LOCAL
   DATAFILE '/oradb/CUSTDB/oradata1/datafiles/system01.dbf'
 SIZE 3000M REUSE
   SYSAUX DATAFILE '/oradb/CUSTDB/oradata1/datafiles/sysaux01.dbf'
 SIZE 1000M REUSE
   DEFAULT TABLESPACE users
      DATAFILE '/oradb/CUSTDB/oradata1/datafiles/users01.dbf'
      SIZE 10M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
   DEFAULT TEMPORARY TABLESPACE temp
      TEMPFILE '/oradb/CUSTDB/oradata1/datafiles/temp01.dbf'
      SIZE 1000M REUSE
   UNDO TABLESPACE undotbs
      DATAFILE '/oradb/CUSTDB/oradata1/datafiles/undotbs01.dbf'
      SIZE 2000M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

Of course, you have to modify appropriately for your case. 

5. Run the following scripts to create dictionary and basic
 database functionality.

sqlplus "/ as sysdba"
SQL>@$ORACLE_HOME/rdbms/admin/catalog.sql
SQL>@$ORACLE_HOME/rdbms/admin/catproc.sql

login as SYSTEM user and run the script

SQL>@$ORACLE_HOME/sqlplus/admin/pupbld.sql

No comments:

Post a Comment

Popular Posts