Manual Database Creation 10g

Step 1: Set .profile and Install Oracle Binaries

ORACLE_SID=DB1

ORACLE_BASE=/u01/app/oracle

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/oracle/sybase/ocli/lib

CLASSPATH=$ORACLE_HOME/JRE/lob:$ORACLE_HOME/jlib

TNS_ADMIN=$ORACLE_HOME/network/admin

ORACLE_HOME=/u01/app/oracle/product/10.2.0.4.0

PATH=$ORACLE_HOME/bin:/oracle/sybase/ocli/bin:/bin:/usr/bin:/usr/ccs/bin:/usr/sbin:/usr/ucb

export ORACLE_HOME ORACLE_BASE LD_LIBRARY_PATH PATH CLASSPATH TNS_ADMIN ORACLE_SID

if [ "`tty`" = "/dev/console" ] ; then

        if [ "$TERM" = "sun" -o "$TERM" = "sun-color" -o "$TERM" = "AT386" ]

        then

                if [ ${OPENWINHOME:-""} = "" ] ; then

                        OPENWINHOME=/usr/openwin

                        export OPENWINHOME

                fi

                echo ""

                echo "Starting OpenWindows in 5 seconds (type Control-C to interrupt)"

                sleep 5

                echo ""

                $OPENWINHOME/bin/openwin

                clear           # get rid of annoying cursor rectangle

                exit            # logout after leaving windows system

        fi

fi

umask 022

DISPLAY=10.239.199.233:0.0

export DISPLAY

Step 2: Create initDB1.ora file

cd $ORACLE_HOME/dbs

vi initDB1.ora

*.audit_file_dest='$ORACLE_HOME/db1/admin/adump'

*.background_dump_dest='$ORACLE_HOME/db1/admin/bdump'

*.compatible='10.2.0.4.0'

*.control_files='/u03/controlfile/db1/control01.ctl',

'/u04/controlfile/db1/control02.ctl','/u05/controlfile/db1/control03.ctl'

*.core_dump_dest='$ORACLE_HOME/db1/admin/cdump'

*.db_block_size=8192

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_name='DB1'

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

*.job_queue_processes=10

*.log_archive_dest_1='LOCATION=/u05/archives/db1/archive'

*.log_archive_format='%t_%s_%r.dbf'

*.open_cursors=300

*.pga_aggregate_target=200278016

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=601882624

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS'

*.user_dump_dest='$ORACLE_HOME/db1/admin/udump'

*.db_recovery_file_dest='/u05/recovery/db1/backup'

*.db_recovery_file_dest_size=2147483648

Step3: Make necessary directories:

mkdir -p /u03/controlfile/db1 /u04/controlfile/db1 /u05/controlfile/db1 $ORACLE_HOME/db1/admin/cdump /u05/archives/db1/archive $ORACLE_HOME/db1/admin/udump$ORACLE_HOME/db1/admin/udump /u05/recovery/db1/backup $ORACLE_HOME/db1/admin/bdump $ORACLE_HOME/db1/admin/adump

mkdir –p /u04/redolog/db1 /u05/redolog/db1 /u03/redolog/db1 /u03/datafile/db1

Step 4 :Make the database script:dbscript.sql

CREATE DATABASE "DB1"

MAXLOGFILES 5

MAXLOGMEMBERS 5

MAXDATAFILES 100

MAXINSTANCES 1

MAXLOGHISTORY 292

LOGFILE

GROUP 1 '/u04/redolog/db1/redo1.log' SIZE 10M,

GROUP 2 '/u05/redolog/db1/redo2.log' SIZE 10M,

GROUP 3 '/u03/redolog/db1/redo3.log' SIZE 10M

DATAFILE

'/u03/datafile/db1/system.dbf' size 100m,

'/u03/datafile/db1/usr01.dbf' size 10m

sysaux datafile '/u03/datafile/db1/sysaux.dbf' size 100m

undo tablespace undotbs

datafile '/u03/datafile/db1/undo.dbf' size 50m

CHARACTER SET US7ASCII

;

Step 5 :Goto the sql prompt to create database

ggntestb: $PWD\> echo $ORACLE_SID

DB1

ggntestb: $PWD\> sqlplus ' / as sysdba '

SQL*Plus: Release 10.2.0.4.0 - Production on Mon May 6 07:25:58 2013

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup pfile='/u01/app/oracle/product/10.2.0.4.0/dbs/initDB1.ora' nomount

ORACLE instance started.

Total System Global Area  603979776 bytes

Fixed Size                  2140768 bytes

Variable Size             165500320 bytes

Database Buffers          432013312 bytes

Redo Buffers                4325376 bytes

SQL> @/u01/app/oracle/product/10.2.0.4.0/dbs/dbscript.sql

Database created.

Step 6 : Run the catalog.sql and catproc.sql scripts in cd $ORACLE_HOME/rdbms/admin

SQL> @$ORACLE_HOME/rdbms/admin/catalog.sql

Comment created.

Comment created.

Comment created.

Comment created.

Synonym created.

Grant succeeded.

PL/SQL procedure successfully completed

SQL> @$ORACLE_HOME/rdbms/admin/catproc.sql

SQL>

SQL>

SQL> Rem ADD NEW PACKAGES/NEW POST CATPROC FIXED VIEWS ABOVE THIS BLOCK

SQL> ------------------------------------------------------------------------------

SQL>

SQL> SET SERVEROUTPUT ON

SQL>

SQL> Rem Indicate CATPROC load complete and check validity

SQL> BEGIN

  2     dbms_registry.update_schema_list('CATPROC',

  3       dbms_registry.schema_list_t('SYSTEM', 'OUTLN', 'DBSNMP'));

  4     dbms_registry.loaded('CATPROC');

  5     dbms_registry_sys.validate_catproc;

  6     dbms_registry_sys.validate_catalog;

  7  END;

  8  /

BEGIN

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-01654: unable to extend index SYS.I_HH_OBJ#_COL# by 2 in tablespace SYSTEM

ORA-06512: at "SYS.DBMS_STATS", line 13591

ORA-06512: at "SYS.DBMS_STATS", line 13898

ORA-06512: at "SYS.DBMS_STATS", line 16240

ORA-06512: at "SYS.DBMS_STATS", line 16282

ORA-06512: at "SYS.DBMS_REGISTRY_SYS", line 953

ORA-06512: at "SYS.DBMS_REGISTRY", line 561

ORA-06512: at line 4

SQL>

SQL> SET SERVEROUTPUT OFF

SQL> select name, open_mode from v$database;

NAME      OPEN_MODE

--------- ----------

DB1       READ WRITE

1 row selected.

Step 7 : Run the pupbld.sql script in cd $ORACLE_HOME/ sqlplus/admin/

SQL> alter user system identified by system01;

SQL> conn system/system01

SQL> @$ORACLE_HOME/sqlplus/admin/pupbld.sql

Step 8 : Create sample schema hr

SQL> exit

ggntestb: $PWD\> cd $ORACLE_HOME/ sqlplus/admin

ggntestb: $PWD\> vi hr_main.sql

ggntestb: $PWD\> sqlplus ' / as sysdba '

SQL*Plus: Release 10.2.0.4.0 - Production on Mon May 6 09:25:39 2013

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @hr_main.sql

 (sql script present in  my previous blog)

SQL> conn system/system01

SQL> @$ORACLE_HOME/sqlplus/admin/pupbld.sql

SQL> alter user hr identified by hr account unlock;