Database creation

Manual Database Creation in 10g:

step 1: create Directory structure.

mkdir -p /apps/prod/oradata
mkdir -p /apps/prod/admin/{arch,scripts,network,bdump,cdump,udump}

step2: Give the permissions.

chown -R oracle:dba /apps/prod
chmod -R 777 /apps/prod

Step3: export variables

export ORACLE_HOME=/apps/oracle/product/10.2.0
export PATH=$ORACLE_HOME/bin:$PATH
export LD_ASSUME_PATH=$ORACLE_HOME/lib:$PATH

export TNS_ADMIN=/apps/prod/admin/network
export ORACLE_SID=prod

Step4: create parameter file

[oracle@ravi ~]$ cd $ORACLE_HOME/dbs
[oracle@ravi dbs]$ vi initprod.ora

prod.__db_cache_size=369098752
prod.__java_pool_size=4194304
prod.__large_pool_size=4194304
prod.__shared_pool_size=142606336
prod.__streams_pool_size=0
*.background_dump_dest='/apps/prod/admin/bdump'
*.compatible='10.2.0'
*.control_files='/apps/prod/oradata/control01.ctl'
*.core_dump_dest='/apps/prod/admin/cdump'
*.db_block_size=4096
*.db_name='prod'
*.log_archive_dest='/apps/prod/admin/arch'
*.log_archive_format='redo_%s_%r_%t.arc'
*.sga_target=500M
*.undo_management='auto'
*.undo_tablespace='undotbs'
*.user_dump_dest='/apps/prod/admin/udump'


Step5: start the database in nomount state.

SQL> STARTUP NOMOUNT

Step6: create database creation script.

create database prod
datafile '/apps/prod/oradata/system01.dbf' size 300M
sysaux datafile '/apps/prod/oradata/sysaux01.dbf' size 200m
undo tablespace undotbs datafile '/apps/prod/oradata/undotbs01.dbf' size 100m
default temporary tablespace temp tempfile '/apps/prod/oradata/temp01.dbf' size 100m
default tablespace userdata datafile '/apps/prod/oradata/userdata01.dbf' size 400m
logfile
group 1 '/apps/prod/oradata/redo1a.log' size 30m,
group 2 '/apps/prod/oradata/redo2a.log' size 30m
character set UTF8;
~                          

Step6: run the post scripts like

@?/rdbms/admin/catalog.sql  ------> create Indexes
@?/rdbms/admin/catproc.sql ------> packages are create
connect system/manager
@?/sqlplus/admin/pupbld.sql

Note: by using OMF (oracle Managed files) doesn't create directories manually automatically create those locations will be write in p-file.

                                db_create_file_dest ---> to specify the CRD files.

Reasions for DB creation errors:

1. If physical files are not there.
2. Database is Inconsistent.

Errors:

ora-01092- signalled during: alter database open...

Sol: check Undo_tablespace prameter and undo tablespace names are same or not.