Create a new database

Set the SID

Set the unique SID of new database at your environment. Example of UNIX command:

#example of C shell (csh) command
setenv ORACLE_SID RCVCAT
#example of Korn shell (ksh) and Bourne shell (sh) command
export ORACLE_SID=RCVCAT

You can also create an environment script that will set all variables necessary for oracle. Example of UNIX command creating a file .env with vi editor:

vi ~/RCVCAT.envwiki oracle create a new database

Create new password file

Example of UNIX command which creates new password file:

orapwd FILE=$ORACLE_HOME/dbs/orapwRCVCAT PASSWORD=********wiki oracle create a new database

Create new parameter files

Create pfile

You can create a new file on your own or you can copy and edit sample file provided by oracle. Sample file name is init.ora and it should be located in directory $ORACLE_HOME/dbs. Example of UNIX commands:

cp $ORACLE_HOME/dbs/init.ora $ORACLE_HOME/dbs/initRCVCAT.oravi $ORACLE_HOME/dbs/initRCVCAT.orawiki oracle create a new database

*This is example of new pfile for database RCVCAT

Create spfile

When you’re done with pfile you can create from in spfile. Connect to your new database and issue following SQL command:

create spfile from pfile;wiki oracle create a new database

Create new database

Execute create database command

Before you execute create database command you need to start the database to nomount state. To do this, execute following SQL command:

startup nomount;wiki oracle create a new database

Now you can issue the create database command. Following example creates database with SID RCVCAT:

CREATE DATABASE RCVCAT
USER SYS IDENTIFIED BY ********
USER SYSTEM IDENTIFIED BY ********
LOGFILE GROUP 1 ('/oracle/REDOA/RCVCAT/redo01.log') SIZE 50M,
GROUP 2 ('/oracle/REDOB/RCVCAT/redo02.log') SIZE 50M
CHARACTER SET WE8ISO8859P1
NATIONAL CHARACTER SET AL16UTF16
DATAFILE '/oracle/DATA/RCVCAT/system01.dbf' SIZE 480M REUSE
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/oracle/DATA/RCVCAT/sysaux01.dbf' SIZE 240M REUSE
DEFAULT TABLESPACE catalog
DATAFILE '/oracle/DATA/RCVCAT/catalog01.dbf'
SIZE 200M REUSE EXTENT MANAGEMENT LOCAL
DEFAULT TEMPORARY TABLESPACE temp
TEMPFILE '/oracle/TEMP/RCVCAT/temp01.dbf'
SIZE 20M REUSE
UNDO TABLESPACE undo
DATAFILE '/oracle/UNDO/RCVCAT/undo01.dbf'
SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
wiki oracle create a new database

Execute postcreation scripts

Once database is create you need to execute two final sql scripts provided by oracle. First script “catalog.sql” creates the views of the data dictionary tables, the dynamic performance views, and public synonyms for many of the views and grants PUBLIC access to the synonyms. Example of SQL command:

@?/rdbms/admin/catalog.sql

Second script “catproc.sql” runs all scripts required for or used with PL/SQL. Example of SQL command:

@?/rdbms/admin/catproc.sql

Update listener

Create new entry in listener.ora

Edit your listener.ora with your favourite editor. Example of UNIX command using VI editor:

vi $ORACLE_HOME/network/admin/listener.orawiki oracle create a new database

Create new entry in tnsnames.ora

Edit your tnsnames.ora with your favourite editor. Example of UNIX command using VI editor:

vi $ORACLE_HOME/network/admin/tnsnames.orawiki oracle create a new database

Restart listener

Example of UNIX commands for restarting listener with standard name LISTENER:

lsnrctl stop LISTENER
lsnrctl start LISTENER
wiki oracle create a new database

Leave a Reply