Creating Database Manually

CREATING DATABASE MANUALLY

Steps to follow to create a database manually

1. Create a pfile
2. Create a sql script to create database
3. Startup database in nomount
4. Execute the sql script to create database
5. Run the post database creation scripts

Create pfile

$ cat inittest.ora

db_name=test

memory_max_target=1024m

memory_target=1024m

control_files='/app/oracle/oradata/TEST/controlfile01.ctl','/app/oracle/oradata/TEST/controlfile02.ctl'

audit_file_dest='/app/oracle/admin/test/adump'

audit_trail='db'

compatible='11.2.0.0.0'

db_block_size=8192

diagnostic_dest='/app/oracle'

open_cursors=300

processes=150

remote_login_passwordfile='EXCLUSIVE'

undo_tablespace='UNDOTBS1'

observations:

These are few of the parameters which are defining the database, noticeably control_files, diagnostic_dest, compatible, memory_target

Create SQL script to create database-à Create a sql script at a desired location, so that it can be called during execution.

$ cat dbcreate.sql

create database test

datafile '/app/oracle/oradata/TEST/system1.dbf' size 500m

sysaux datafile '/app/oracle/oradata/TEST/sysaux01.dbf' size 100m

default tablespace users datafile '/app/oracle/oradata/TEST/users1.dbf' size 100m

default temporary tablespace temp tempfile '/app/oracle/oradata/TEST/temp1.dbf' size 100m

undo tablespace undotbs1 datafile '/app/oracle/oradata/TEST/undo1.dbf' size 100m

logfile

group 1 ('/app/oracle/oradata/TEST/redolog1.log') size 10m,

group 2 ('/app/oracle/oradata/TEST/redolog2.log') size 10m,

group 3 ('/app/oracle/oradata/TEST/redolog3.log') size 10m;

Observationsà syntax is important, typo’s, incorrect names, incorrect syntax will cause failure of database creation

Startup database in NOMOUNT

Connect as “ / as sysdba” & startup the instance in nomount. If the pfile entries are correct (depending on the location/directories specified) it will start up the instance, where in it allocates memory to Oracle.

Execute the Database creation script

On starting up the database in NOMOUNT state, being connected as “ / as sysdba” execute the create database script

SQL> @dbcreate.sql;

You can monitor the alert_test.log to see the database creation in progress.

Post database creation steps

These include building data dictionary views, creating public synonyms, granting access to public.

Run the following scripts as “ / as sysdba”

@ORACLE_HOME/rdbms/admin/catalog.sql

@ORACLE_HOME/rdbms/admin/catproc.sql

 .

..

....

.....

SQL> SELECT dbms_registry_sys.time_stamp('CATPROC') AS timestamp FROM DUAL;

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP CATPROC    2012-07-08 12:41:35

1 row selected.

Run the following script as “SYSTEM” user

@ORACLE_HOME/sqlplus/admin/pupbld.sql

...

...

...

......
SQL> CREATE PUBLIC SYNONYM PRODUCT_USER_PROFILE FOR SYSTEM.PRODUCT_PRIVS;

Synonym created.

SQL>
SQL> -- End of pupbld.sql

 For database created manually we will need to add the lines in "/etc/oratab", whereas databases created by DBCA entries get added.

SQL> select instance_name, host_name, status from v$instance;

INSTANCE_NAME    HOST_NAME                                                        STATUS
---------------- ---------------------------------------------------------------- ------------
test             aix-lab                                                          OPEN

SQL> select tablespace_name, status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE

5 rows selected.