Things we need before creating the database.
1) Proper Oracle HOME and the software should be installed in the $ORACLE_HOME
2) Folder structure in the admin directory which contains adump,bdump,cdump,and udump.
3) Init parameter file
4) DB Creation script
Step 1: The database software i.e ORACLE_HOME binaries should be installed. For installing ORACLE SOFTWARE please follow the steps described in "Oracle 10g Home Install" page.
On the database host do the following.
cd $ORACLE_HOME
ls -ltr -> Here it should list all the files which are installed during the software installation.
My Oracle home is located at /local/mnt/oracle/product/11.1.0.6 |
|
Step 2: Creating "diag" directories for diagnostic_dest parameter.
In 11g there is no need to create directories for adump, budmp, cdump and udump in "admin" location.
Normally admin directory will be created in the directory structure. Look for admin directory for oracle on your host.
In my system my admin directory is located at /local/mnt/oracle/admin/SRITSTDB/ |
Step 3: Creating INIT parameter file.
NOTE: Starting with oracle 11g there is no need to create the adump, bdump, cdump and udump directories. Instead of mentioning audit_file_dest, background_dump_dest and core_dump_dest and user_dump_dest parameters in init parameter file, it is enough to create one single parameter called diagnostic_dest parameter.
If you mention the diagnostic_dest parameter as "*.diagnostic_dest='/local/mnt/oracle/admin/SRITSTDB'", oracle will create a new directory "diag" under SRITSTDB directory.
In this "diag" directory you will find different directories.
The new directory structure under diag will be like this.
diag
|
|
---------rdbms
|
|
-------sritstdb/SRITSTDB/
|
|
---------- alert <== In this directory alert log file will be stored in xml format.
cdump
hm
incident
trace <== In this directory text format of alert log file will be stored.
ir
metadata
stage
On the database host do the following. The location of this file should be in $ORACLE_HOME/dbs/, because oracle will look into this folder by default for init parameter file.
cd $ORACLE_HOME
cd dbs
vi initSRITSTDB.ora
Enter the following entries in this file. All these may not be necessary. But some of them are must.
*.aq_tm_processes=0
*.COMPATIBLE='11.1.0.6'# 11.1
*.diagnostic_dest='/local/mnt/oracle/admin/SRITSTDB' # This parameter is the replacement for audit, background, core and user dump directories.
*.control_files='/u01/app/oracle/data/db1/data/SRITSTDB/SRITSTDB_control01.ctl','/u01/app/oracle/data/db2/data/SRITSTDB/SRITSTDB_control02.ctl',
'/u01/app/oracle/data/db1/data/SRITSTDB/SRITSTDB_control03.ctl'
*.db_block_size=8192
*.db_domain='WORLD'
*.db_file_multiblock_read_count=16
*.db_files=1024
*.db_name='SRITSTDB'
*.job_queue_processes=0
*.large_pool_size=150M# increase to 150M minimum req for 10.2, was 64M
*.log_archive_dest='/u01/app/oracle/admin/SRITSTDB/arch/SRITSTDB_'
*.nls_date_format='DD-MON-YYYY HH24:MI:SS'
*.NLS_LENGTH_SEMANTICS='CHAR'# turn on after upgrade
*.open_cursors=1800
*.pga_aggregate_target=500M
*.remote_os_authent=FALSE
*.sga_max_size=1000M
*.sga_target=512M
*.undo_management='AUTO'
*.undo_retention=28800
*.undo_tablespace='UNDOTBS1'
*.REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
Make sure all the yellow shaded paths should exists. |
Step 4: Create password file in the $ORACLE_HOME/dbs folder.
On the database host do the following.
cd $ORACLE_HOME
cd dbs
By Staying in "dbs" directory issue the following command. Here $ORACLE_SID is the name of the database which we are creating.$ORACLE_HOME/bin/orapwd file=orapw$ORACLE_SID password=srikanth entries=5
|
|
Step 5: DB creation scripts. In the next step I will show how to execute these scripts. In this step I will list all the scripts which are needed for the database creation.
We have to create the db creation scripts. The DB creation scripts will be as follows.
You can create these scripts in any folder you want in your machine.
Script 1: cr_db.sql file. --- This script accepts one parameter and the parameter will be the ORACLE DATABASE NAME. HERE the database name is SRITSTDB.
set time on timing on feed on
spool cr_db.log
startup nomount
CREATE DATABASE "&DBNAME"
MAXINSTANCES 8
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 1024
---
CHARACTER SET UTF8
NATIONAL CHARACTER SET AL16UTF16
---
DATAFILE '/opt/mis/oracle/data/db1/data/SRITSTDB/SRITSTDB_system1a.dbf' SIZE 1000M extent management local
SYSAUX DATAFILE '/opt/mis/oracle/data/db1/data/SRITSTDB/SRITSTDB_sysaux1a.dbf' SIZE 1000M
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/opt/mis/oracle/data/db1/data/SRITSTDB/SRITSTDB_temp1a.dbf' SIZE 1000M
extent management local uniform size 1m
UNDO TABLESPACE "UNDOTBS1" DATAFILE '/opt/mis/oracle/data/db1/data/SRITSTDB/SRITSTDB_undotbs1a.dbf' SIZE 1000M
---
LOGFILE
GROUP 1 ('/opt/mis/oracle/data/db1/data/SRITSTDB/SRITSTDB_log1a.dbf','/opt/mis/oracle/data/db2/data/SRITSTDB/SRITSTDB_log1b.dbf') SIZE 100M,
GROUP 2 ('/opt/mis/oracle/data/db1/data/SRITSTDB/SRITSTDB_log2a.dbf','/opt/mis/oracle/data/db2/data/SRITSTDB/SRITSTDB_log2b.dbf') SIZE 100M,
GROUP 3 ('/opt/mis/oracle/data/db1/data/SRITSTDB/SRITSTDB_log3a.dbf','/opt/mis/oracle/data/db3/data/SRITSTDB/SRITSTDB_log3b.dbf') SIZE 100M
---
USER SYS IDENTIFIED BY srikanth
USER SYSTEM IDENTIFIED BY srikanth
/
NOTE: MAKE SURE ALL THE SHADED PATHS SHOULD EXIST ON YOUR MACHINE. YOU CAN CHANGE THE THESE DIRECTORY PATHS WHAT EVER YOU WANT.
Script 2: @dictionary.sql
connect /as sysdba
spool dictionary.out
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catblock.sql@?/rdbms/admin/catproc.sql
@?/rdbms/admin/catoctk.sql@?/rdbms/admin/owminst.plb@?/javavm/install/initjvm.sql@?/xdk/admin/initxml.sql@?/xdk/admin/xmlja.sql@?/rdbms/admin/catjava.sql@?/rdbms/admin/catexf.sql@?/rdbms/admin/catclust.sql
connect SYSTEM/srikanth
spool sqlplus.log@?/sqlplus/admin/pupbld.sql@?/sqlplus/admin/help/hlpbld.sql helpus.sql
spool off
spool off |
Step 6: Creating a wrapper script to execute the above 2 scripts.
Make another wrapper script called "SRITSTDB.sql" with the following contents.
set feed on;
set echo on;
@cr_db.sql
@dictionary.sql
create tablespace users datafile '/opt/mis/oracle/data/db4/data/SRITSTDB/SRITSTDB_users1a.dbf' size 1000M;
alter database default tablespace users ;
alter profile default limit FAILED_LOGIN_ATTEMPTS unlimited ;
exit;
|
Step 7: Execution.
UNIX_PROMPT> sqlplus "/as sysdba"
@SRITSTDB.sql |
No comments:
Post a Comment