User Rating: 1 / 5

Star ActiveStar InactiveStar InactiveStar InactiveStar Inactive
 

This article shows how to manually create a Oracle database, known aswell as Oracle Instance, in command line with sqlplus.
For the laziest, there is a custom shell script for download which does all the creation database steps.

Another way to create database is to use dbca as explain here.

(ora_create_sid.sh zip file is attached in this article)

Before starting, you need ...
... Oracle Grid Infrastructure installed (optional but recommanded, see here)
... ASM installed and confgured (only if ASM is used as db storage, see here)
... Oracle 11gR2 RDBMS installed (see here)

In this article, we will create an Oracle instance named UXODB with or without ASM.

Create init.ora

As oracle user, create a minimal init<DB_NAME>.ora file in $ORACLE_HOME/dbs directory.
In our case, we will create the following file /u01/app/oracle/product/11.2.0/db_1/dbs/initUXODB.ora

For filesystem (no ASM)

db_name=UXODB
control_files='/u01/app/oracle/oradata/UXODB/control01.ctl'
control_files='/u01/app/oracle/flash_recovery_area/UXODB/control02.ctl'
memory_max_target=2G
memory_target=2G
undo_management=auto

For ASM

db_name=UXODB
control_files=+DATA/UXODB/control01.ctl
control_files=+FRA/UXODB/control02.ctl
memory_max_target=2G
memory_target=2G
undo_management=auto
DB_CREATE_FILE_DEST=+DATA
DB_RECOVERY_FILE_DEST=+FRA
DB_RECOVERY_FILE_DEST_SIZE=2G
DB_CREATE_ONLINE_LOG_DEST_1=+DATA
DB_CREATE_ONLINE_LOG_DEST_2=+FRA

Start instance

Normally ORACLE_HOME should already be set, and we will set ORACLE_SID and start Oracle instance

# Set ORACLE_SID
[oracle]$ export ORACLE_SID=UXODB
[oracle]$ sqlplus / as sysdba
    SQL*Plus: Release 11.2.0.1.0 Production on ...
    
    Copyright (c) 1982, 2009, Oracle.  All rights reserved.
    
    Connected to an idle instance.
    
SQL>  startup nomount ;
    ORACLE instance started.

    Total System Global Area 2137886720 bytes
    Fixed Size		    2215064 bytes
    Variable Size		 1224737640 bytes
    Database Buffers	  905969664 bytes
    Redo Buffers		    4964352 bytes

Create database

For filesystem (no ASM)

# Create Oracle database on fs
[oracle]$ sqlplus / as sysdba

    SQL*Plus: Release 11.2.0.1.0 Production on ...

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


    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
SQL> CREATE DATABASE UXODB
  CHARACTER SET AL32UTF8
  NATIONAL CHARACTER SET AL16UTF16
LOGFILE
  GROUP 1 (
   '/u01/app/oracle/oradata/UXODB/redo01a.rdo',
   '/u01/app/oracle/flash_recovery_area/UXODB/redo01b.rdo'
  ) SIZE 128M,
  GROUP 2 (
   '/u01/app/oracle/oradata/UXODB/redo02a.rdo',
   '/u01/app/oracle/flash_recovery_area/UXODB/redo02b.rdo'
  ) SIZE 128M,
  GROUP 3 (
   '/u01/app/oracle/oradata/UXODB/redo03a.rdo',
   '/u01/app/oracle/flash_recovery_area/UXODB/redo03b.rdo'
  ) SIZE 128M
  DATAFILE '/u01/app/oracle/oradata/UXODB/system01.dbf'
   SIZE 1G AUTOEXTEND ON NEXT 128M MAXSIZE 32767M
  SYSAUX DATAFILE '/u01/app/oracle/oradata/UXODB/sysaux01.dbf'
   SIZE 1G AUTOEXTEND ON NEXT 128M MAXSIZE 32767M
  UNDO TABLESPACE undo
   DATAFILE '/u01/app/oracle/oradata/UXODB/undo01.dbf'
   SIZE 128M AUTOEXTEND ON NEXT 128M MAXSIZE 32767M
  DEFAULT TEMPORARY TABLESPACE temp 
   TEMPFILE '/u01/app/oracle/oradata/UXODB/temp01.dbf'
   SIZE 128M AUTOEXTEND ON NEXT 128M MAXSIZE 32767M
  DEFAULT TABLESPACE users
   DATAFILE '/u01/app/oracle/oradata/UXODB/users01.dbf'
   SIZE 128M AUTOEXTEND ON NEXT 128M MAXSIZE 32767M
;

    Database created.

SQL> /* Create spfile */
SQL> create spfile from pfile ;

    File created.

For ASM

Command becomes a bit simplier with ASM.

# Create Oracle database on ASM
[oracle]$ sqlplus / as sysdba

    SQL*Plus: Release 11.2.0.1.0 Production on ...

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


    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
SQL> CREATE DATABASE UXODB
  CHARACTER SET AL32UTF8
  NATIONAL CHARACTER SET AL16UTF16
LOGFILE
  GROUP 1 SIZE 128M,
  GROUP 2 SIZE 128M,
  GROUP 3 SIZE 128M
DEFAULT TEMPORARY TABLESPACE temp TEMPFILE
  SIZE 128M AUTOEXTEND ON NEXT 128M MAXSIZE 32767M
DEFAULT TABLESPACE users DATAFILE
  SIZE 128M AUTOEXTEND ON NEXT 128M MAXSIZE 32767M
;

    Database created.

SQL> /* Create spfile for ASM */
SQL> CREATE SPFILE='+DATA/UXODB/spfileUXODB.ora' from PFILE ;

    File created.

SQL> /* Add spfile parameter to init.ora (for ASM only) */
SQL> HOST echo SPFILE='+DATA/UXODB/spfileUXODB.ora' >> $ORACLE_HOME/dbs/initUXODB.ora

Create catalog

# Create Oracle database on ASM
[oracle]$ sqlplus / as sysdba

    SQL*Plus: Release 11.2.0.1.0 Production on ...

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


    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
SQL> /* Compile catalog scripts may take a while ... */
SQL> @?/rdbms/admin/catalog.sql
    ...
SQL> @?/rdbms/admin/catproc.sql
    ...

SQL> /* Need to connect as system to compile this script */
SQL> ALTER USER SYSTEM IDENTIFIED BY ORASYSTEMPWD ACCOUNT UNLOCK ;

    User altered.

SQL> CONNECT SYSTEM/ORASYSTEMPWD
SQL> @?/sqlplus/admin/pupbld.sql
    ...

SQL> /* Recompile all objects in case of */
SQL> CONNECT / AS SYSDBA
SQL> @?/rdbms/admin/utlrp
    ...

SQL> /* Restart */
SQL> SHUTDOWN IMMEDIATE ;
SQL> STARTUP ;

Post database creation

# Add db to /etc/oratab
[oracle]$ echo "${ORACLE_SID}:${ORACLE_HOME}:Y" >> /etc/oratab

# Add to srvctl
[oracle]$ ${ORACLE_HOME}/bin/srvctl add database -d UXODB
    ...

# Set SYS password
[oracle]$ ${ORACLE_HOME}/bin/orapwd file=${ORACLE_HOME}/dbs/orapwUXODB password=...

[oracle]$ sqlplus / as sysdba

    SQL*Plus: Release 11.2.0.1.0 Production on ...

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


    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
SQL> /* Set system password */
SQL> ALTER USER SYSTEM IDENTIFIED BY ... ACCOUNT UNLOCK ;

    User altered.

SQL> /* Lock all account except SYS/SYSTEM */
SQL> BEGIN 
 FOR item IN ( SELECT USERNAME FROM DBA_USERS WHERE ACCOUNT_STATUS IN ('OPEN', 'LOCKED', 'EXPIRED') AND USERNAME NOT IN ('SYS','SYSTEM') ) 
 LOOP 
  dbms_output.put_line('Locking and Expiring: ' || item.USERNAME); 
  execute immediate 'alter user ' ||
   sys.dbms_assert.enquote_name(
   sys.dbms_assert.schema_name(
   item.USERNAME),false) || ' password expire account lock' ;
 END LOOP;
END;
/

    PL/SQL procedure successfully completed.

SQL> /* Register with listener (if already configured) */
SQL> ALTER SYSTEM REGISTER ;

    System altered.

Shell Script

I wrote a shell script that does all these database creation steps, it can be downloaded at the end of this article

How to use

# Script help
[oracle]$ ./ora_create_sid.sh -h
 SYNOPSIS
  ora_create_sid.sh [-thv] [-o[file]] [-b[orabase]] [-d[orahome]] [-c[charset]] [-n[ncharset]] [-m[memsize]] [-r[redosize]] [-p[password]] SID_name

 DESCRIPTION
  Script to create a oracle database (or an instance SID)
  with OFA directory structure

 OPTIONS
  -b [orabase],    --orabase=[orabase]     Set oracle base (default=$ORACLE_BASE)
  -d [orahome],    --orahome=[orahome]     Set oracle home (default=${ORACLE_HOME})
  -c [dbcharset],  --charset=[dbcharset]   Set db charset (default=AL32UTF8)
  -n [dbncharset], --ncharset=[dbncharset] Set db ncharset (default=AL16UTF16)
  -m [dbmemsize],  --memsize=[dbmemsize]   Set db memory size (default=1024M)
  -r [dbredosize], --redosize=[dbredosize] Set db redo log size (default=128M)
  -s [dbstortype], --storage=[dbstortype]  Set db storage type : FS or ASM (default=FS)
       By default, +DATA diskGroupName and +FRA recoveryGroupName is set for ASM
       This can be set to +DATADG and +FRADG with "ASM:DATADG:FRADG" value
  -p [dbpassword], --passwd=[dbpassword]   Set db password (default=pwd_to_change)
       use :VAR:<varname> keyword to specify a environement variable as a password
       if no <varname>, "ORA_PWD_DEFAULT" variable will be used by default
  -o [file], --output=[file]    Set log file (default=/dev/null)
       use DEFAULT keyword to autoname file
       The default value is /dev/null
  -t, --timelog                 Add timestamp to log ("+%y/%m/%d@%H:%M:%S")
  -h, --help                    Print this help
  -v, --version                 Print script information

 EXAMPLES
  ora_create_sid.sh -o DEFAULT -p:VAR:MYPWD db_1

 IMPLEMENTATION
  version         ora_create_sid.sh (www.uxora.com) 0.0.1
  author          Michel VONGVILAY
  copyright       Copyright (c) http://www.uxora.com
  license         GNU General Public License
  script_id       12346

Execute

Here is an example to simply create a database on ASM.

# Execute script
[oracle]$ ./ora_create_sid.sh -o DEFAULT -m 2G -s ASM UXODB
    [I] ora_create_sid.sh: start 15/10/28@17:03:48 with process id 29978
    [I] by [email protected]:/mnt/nfs/uxora_share (LOG: /mnt/nfs/uxora_share/ora_create_sid.12346.151028170348.29978.log)
    [I] Creating OFA directories ...
    [I] Preparing scripts...
    [I] Executing ORAPWD...
    [I] Executing SQL*Plus scripts... This might take some time.
    [I] Creating database
    ORACLE instance started.

    Total System Global Area 2137886720 bytes
    Fixed Size		    2215064 bytes
    Variable Size		 1224737640 bytes
    Database Buffers	  905969664 bytes
    Redo Buffers		    4964352 bytes

    Database created.

    [I] Creating spfile

    File created.

    [I] Modifying oracle system users

    User altered.


    User altered.

    [I] Executing catalog.sql
    ................................................................................
    ................................................................................
    ................................................................................
    ....................................................................
    [I] Executing catproc.sql
    ................................................................................
    ................................................................................
    ................................................................................
    ................................................................................
    ................................................................................
    ................................................................................
    ................................................................................
    ................................................................................
    ...............................
    [I] Executing utlxplan.sql

    [I] Executing pupbld.sql

    [I] Recompiling invalid object

    [I] Restarting the database.

    SQL*Plus: Release 11.2.0.1.0 Production on Wed Oct 28 17:12:03 2015

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


    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
    With the Partitioning, Automatic Storage Management, OLAP, Data Mining
    and Real Application Testing options

    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    ORACLE instance started.

    Total System Global Area 2137886720 bytes
    Fixed Size		    2215064 bytes
    Variable Size		 1224737640 bytes
    Database Buffers	  905969664 bytes
    Redo Buffers		    4964352 bytes
    Database mounted.
    Database opened.
    Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
    With the Partitioning, Automatic Storage Management, OLAP, Data Mining
    and Real Application Testing options

    SQL*Plus: Release 11.2.0.1.0 Production on Wed Oct 28 17:12:23 2015

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


    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
    With the Partitioning, Automatic Storage Management, OLAP, Data Mining
    and Real Application Testing options


    System altered.

    Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
    With the Partitioning, Automatic Storage Management, OLAP, Data Mining
    and Real Application Testing options

    SQL*Plus: Release 11.2.0.1.0 Production on Wed Oct 28 17:12:23 2015

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


    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
    With the Partitioning, Automatic Storage Management, OLAP, Data Mining
    and Real Application Testing options

    SQL> 
    SQL> BEGIN
      2   FOR item IN ( SELECT USERNAME FROM DBA_USERS WHERE ACCOUNT_STATUS IN ('OPEN', 'LOCKED', 'EXPIRED') AND USERNAME NOT IN ('SYS','SYSTEM') )
      3   LOOP
      4    dbms_output.put_line('Locking and Expiring: ' || item.USERNAME);
      5    execute immediate 'alter user ' ||
      6  	      sys.dbms_assert.enquote_name(
      7  	      sys.dbms_assert.schema_name(
      8  	      item.USERNAME),false) || ' password expire account lock' ;
      9   END LOOP;
     10  END;
     11  /

    PL/SQL procedure successfully completed.

    SQL> 
    SQL> EXIT;
    Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
    With the Partitioning, Automatic Storage Management, OLAP, Data Mining
    and Real Application Testing options
    [I] Post-installation...
    [I] UXODB DB Setup Finished!
    [I] ora_create_sid.sh finished at 17H12 (Time=515.983s, Error=0, Warning=0, RC=0).

Others

Drop database

Be careful
This command may cause you data loss, so use it with caution.

You may want for some reason to delete a Oracle database. As oracle user, use the following command to properly delete database from host:

# Delete database
[oracle]$ sqlplus / as sysdba

    SQL*Plus: Release 11.2.0.1.0 Production on Wed Oct 28 09:07:51 2015

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


    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
SQL> SHUTDOWN IMMEDIATE ;
SQL> STARTUP MOUNT ;
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION ;
SQL> DROP DATABASE ;

Please leave comments and suggestions,
Michel.

Reference
Creating a Database with the CREATE DATABASE Statement (docs.oracle.com)
Silent database 11g creation with dbca (uxora.com)

Enjoyed this article? Please like it or share it.

Attachments:
Download this file (ora_create_sid.sh.zip)ora_create_sid.sh.zip6 kB

Add comment

Please connect with one of social login below (or fill up name and email)

     


Security code
Refresh