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.
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