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.env

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.oravi $ORACLE_HOME/dbs/initRCVCAT.ora

*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;

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;

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:

lsnrctl stop LISTENER lsnrctl start LISTENER

zavisko.com