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.
Expand/Collapse
# 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)
 ora_create_sid.sh.zip | 6 kB |
Enjoyed this article? Please like it or share it.
Please connect with one of social login below (or fill up name and email)