Manual Database Creation

In order to manually create a database, mandb,  follow the steps-

Startup the current database.

Create pfile from spfile.

SQL> create pfile=’ /home/app/oracle/product/10.2.0/db_1/dbs/initmandb.ora’ from spfile;

Note: If there are more than one database you should provide the path and name of spfile.

Create a database creation script as, controlfile script.

SQL> alter database backup controlfile to trace as =’/home/app/oracle/product/10.2.0/      db_1/dbs/mandbctl.sql’;

SQL>exit;

Edit the pfile ( dbname, controfile path, dump folders path)

[oracle@localhost ~]$ cd $ORACLE_HOME

[oracle@localhost db_1]$ cd dbs

[oracle@localhost dbs]$ pwd

/home/app/oracle/product/10.2.0/db_1/dbs

[oracle@localhost dbs]$ vi initmandb.ora

[oracle@localhost dbs]$ cat initmandb.ora

mandb.__db_cache_size=444596224

mandb.__java_pool_size=4194304

mandb.__large_pool_size=4194304

mandb.__shared_pool_size=150994944

mandb.__streams_pool_size=0

*.audit_file_dest='/home/app/oracle/admin/mandb/adump'

*.background_dump_dest='/home/app/oracle/admin/mandb/bdump'

*.compatible='10.2.0.1.0'

*.control_files='/home/app/oracle/oradata/mandb/control01.ctl','/home/app/oracle/oradata/mandb/control02.ctl','/home/app/oracle/oradata/mandb/control03.ctl'

*.core_dump_dest='/home/app/oracle/admin/mandb/cdump'

*.db_block_size=8192

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_name='mandb'

*.db_recovery_file_dest='/home/app/oracle/flash_recovery_area'

*.db_recovery_file_dest_size=2147483648

*.dispatchers='(PROTOCOL=TCP) (SERVICE=mandbXDB)'

*.job_queue_processes=10

*.open_cursors=300

*.pga_aggregate_target=201326592

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=605028352

*.undo_management='AUTO'

*.undo_tablespace='undotbs'

*.user_dump_dest='/home/app/oracle/admin/mandb/udump'

Edit the Controlfile Script

CREATE DATABASE mandb

    MAXLOGFILES 16

    MAXLOGMEMBERS 3

    MAXDATAFILES 100

    MAXINSTANCES 8

    MAXLOGHISTORY 292

LOGFILE

  GROUP 1 '/home/app/oracle/oradata/mandb/redo01.log'  SIZE 50M,

  GROUP 2 '/home/app/oracle/oradata/mandb/redo02.log'  SIZE 50M,

  GROUP 3 '/home/app/oracle/oradata/mandb/redo03.log'  SIZE 50M

DATAFILE

          '/home/app/oracle/oradata/mandb/system01.dbf' size 100m,

          '/home/app/oracle/oradata/mandb/usrsers01.dbf' size 10m

sysaux datafile

          '/home/app/oracle/oradata/mandb/sysaux01.dbf' size 100m

undo tablespace undotbs

datafile  '/home/app/oracle/oradata/mandb/undo01.dbf' size 50m

default temporary tablespace temptbs

tempfile  '/home/app/oracle/oradata/mandb/temp01.dbf' size 50m

CHARACTER SET WE8ISO8859P1

;

Manually / using commands create the folders:- mandb, adump, bdump, cdump, udump as

/home/app/oracle/oradata/mandb/

[oracle@localhost dbs]$mkdir  /home/app/oracle/oradata/mandb

[oracle@localhost dbs]$mkdir  /home/app/oracle/admin/mandb

[oracle@localhost dbs]$ mkdir  /home/app/oracle/admin/mandb/adump

[oracle@localhost dbs]$ mkdir  /home/app/oracle/admin/mandb/bdump

[oracle@localhost dbs]$ mkdir  /home/app/oracle/admin/mandb/cdump

[oracle@localhost dbs]$ mkdir  /home/app/oracle/admin/mandb/udump

Startup the database testdb in nomount using pfile as

[oracle@localhost dbs]$ sqlplus / as sysdba

SQL> startup pfile=’/ home/app/oracle/product/10.2.0/db_1/dbs/initmandb.ora’ nomount;

Run/Execute the Controlfile Script as

SQL>@/home/app/oracle/product/10.2.0/db_1/dbs/mandbctl.sql

Database Created

Run the following scripts for data dictionary, views, catalogs, and procedures

SQL>@/home/app/oracle/product/10.2.0/db_1/rdbms/admin/catalog.sql

SQL>@/home/app/oracle/product/10.2.0/db_1/rdbms/admin/catproc.sql.