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:
Create new password file
Example of UNIX command which creates new password file:
orapwd FILE=$ORACLE_HOME/dbs/orapwRCVCAT PASSWORD=********
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.ora
vi $ORACLE_HOME/dbs/initRCVCAT.ora
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 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:
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;
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.ora
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.ora
Restart listener
Example of UNIX commands for restarting listener with standard name LISTENER: