Create oracle 11g database manually on linux

Make sure oracle binaries are installed.We will proceed with database installation if binaries are already installed.

Step 1:Create Directory structure with Oracle ownership and permission as below:

[oracle@localhost ~]$ cd /u01

[oracle@localhost u01]$ mkdir testdb

[oracle@localhost u01]$ chmod -R 777 /u01/testdb/*

[oracle@localhost u01]$ chown -R oracle:oinstall /u01/testdb/*

[oracle@localhost u01] cd testdb

[oracle@localhost testdb]$ mkdir adump diag flash_recovery_area

[oracle@localhost u01]$ chmod -Rf 777 /u01/testdb/*

[oracle@localhost u01]$ chown -Rf oracle:oinstall /u01/testdb/*


Step 2:Create Parameter file in $ORACLE_HOME/dbs location:

[oracle@localhost testdb]$ cd $ORACLE_HOME/dbs

[oracle@localhost dbs]$ vi init_testdb.ora

db_name='testdb'
memory_target=1G
processes = 150
audit_file_dest='/u01/testdb/adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest='/u01/testdb/flash_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest='/u01/testdb/diag'
dispatchers='(PROTOCOL=TCP) (SERVICE=testdb)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
# You may want to ensure that control files are created on separate physical
# devices
control_files = (/u01/testdb/ora_control1.ctl,/u01/testdb/ora_control2.ctl)
compatible ='11.2.0'


Step 3:Prepare Create Database script :

[oracle@localhost u01]$ cd /u01/testdb/

[oracle@localhost testdb]$ vi createdb_test.sql

CREATE DATABASE testdb
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
LOGFILE
GROUP 1 '/u01/testdb/redo1.log' SIZE 10M,
GROUP 2 '/u01/testdb/redo2.log' SIZE 10M,
GROUP 3 '/u01/testdb/redo3.log' SIZE 10M
DATAFILE
'/u01/testdb/system.dbf' size 200M REUSE
sysaux datafile '/u01/testdb/sysaux.dbf' size 100m
undo tablespace UNDOTBS1
datafile '/u01/testdb/undo1.dbf' size 100m
DEFAULT TEMPORARY TABLESPACE temp1
TEMPFILE '/u01/testdb/temp01.dbf'
SIZE 100M REUSE
CHARACTER SET AL32UTF8
;
:wq

Step 4:Set the Oracle ENVIRONMENT and SID of Database in the Operating System:

[root@localhost testdb]# su - oracle

[oracle@localhost ~]$ vi .bash_profile


# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

export PATH
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export ORACLE_SID=testdb
export TNS_ADMIN=/u01/app/oracle/product/11.2.0/dbhome_1/network/admin

:wq
[oracle@localhost ~]$ . .bash_profile
     This will set the Oracle Environment variables in Unix-based operating system.

[oracle@localhost ~]$ export ORACLE_SID=testdb
     This will set the SID of the current Database in Unix-based operating system.


Step 5:Create the Password file.

[oracle@localhost ~]$orapwd file=$ORACLE_HOME/dbs/orapwtestdb password=Oracle entries=10


Step 6:Create server parameter file.

[oracle@localhost dbs]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Wed Mar 28 14:08:02 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL>create spfile from pfile='$ORACLE_HOME/dbs/init_testdb.ora';


step 7:Start the Database in nomount State.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 1071333376 bytes
Fixed Size                  1341312 bytes
Variable Size             620759168 bytes
Database Buffers          444596224 bytes
Redo Buffers                4636672 bytes
SQL> select status from v$instance;

STATUS
------------
STARTED



Step 8:Execute Create Database script created in Step 3

SQL> @/u01/testdb/createdb_test.sql

Database created

Step 9:Execute the catalog.sql,catproc.sql and pupbld.sql scripts:
a)catalog.sql =>Creates dictionary tables and views
b)catproc.sql =>Creates PL/SQL procedures,functions and packages necessary.
c)pupbld.sql  =>Creates user profiles.


So our database is created. Now just run the catalog.sql,catproc.sql and pupbld.sql scripts.
WE will find catalog.sql and catproc.sql in $ORACLE_HOME/rdbms/admin path and pupbld.sql in $ORACLE_HOME/sqlplus/admin path.

SQL> @$ORACLE_HOME/rdbms/admin/catalog.sql
SQL> @$ORACLE_HOME/rdbms/admin/catproc.sql
conn system/manager

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

SQL> alter user system identified by manager;

User altered.

SQL> conn system
Enter password:
Connected.
SQL> @$ORACLE_HOME/sqlplus/admin/pupbld.sql
DROP SYNONYM PRODUCT_USER_PROFILE
             *
ERROR at line 1:
ORA-01434: private synonym to be dropped does not exist


  DATE_VALUE FROM PRODUCT_USER_PROFILE
                  *
ERROR at line 3:
ORA-00942: table or view does not exist


DROP TABLE PRODUCT_USER_PROFILE
           *
ERROR at line 1:
ORA-00942: table or view does not exist


ALTER TABLE SQLPLUS_PRODUCT_PROFILE ADD (LONG_VALUE LONG)
*
ERROR at line 1:
ORA-00942: table or view does not exist



Table created.

DROP TABLE PRODUCT_PROFILE
           *
ERROR at line 1:
ORA-00942: table or view does not exist


DROP VIEW PRODUCT_PRIVS
*
ERROR at line 1:
ORA-00942: table or view does not exist



View created.


Grant succeeded.

DROP PUBLIC SYNONYM PRODUCT_PROFILE
                    *
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist



Synonym created.

DROP SYNONYM PRODUCT_USER_PROFILE
             *
ERROR at line 1:
ORA-01434: private synonym to be dropped does not exist



Synonym created.

DROP PUBLIC SYNONYM PRODUCT_USER_PROFILE
                    *
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

Synonym created.


Step 10:Verify the Dictionary views created.


SQL> select name from v$database;
NAME
---------
TESTDB

Step 11:Change the Database mode from noarchive log to archive log mode
Changing to archive log mode:

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


SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1
Current log sequence           3
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1071333376 bytes
Fixed Size                  1341312 bytes
Variable Size             620759168 bytes
Database Buffers          444596224 bytes
Redo Buffers                4636672 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> select status from v$instance;

STATUS
------------
MOUNTED

SQL> alter database open;

Database altered.

SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1
Next log sequence to archive   3
Current log sequence           3

This completes the manual Database creation on Unix based Operating system....

Enjoy learning...





Please share your ideas and opinions about this topic.

If you like this post, then please share with others.
Please subscribe on email for every updates on mail.


No comments:

Post a Comment