DATABASE CREATION :
-----------------------------
---> DATABASE CAN BE CREATED IN THE FOLLOWING WAYS
--- USING DBCA
--- MANUAL
MANUAL DB CREATION STEPS :
--------------------------------------
---> THE FOLLOWING ARE THE STEPS FOR CREATING A DATABASE
--- CREATE THE PFILE WITH NECESSORY PARAMETER
--- CREATE THE PASSWORD FILE
--- CREATE THE NECESSORY DIRECTORIES FOR DB
--- START THE DB IN NOMOUNT
--- RUN THE DATBASE CREATION COMMAND
--- RUN THE NECESSORY SCRIPTS
---
---
---
DATABASE CREATION IN UNIX:
--------------------------------------
--> SAVE THE FILE WITH EXTENSION .sh
echo "enter database name : "
read dbname
echo "enter database sid :"
read dbsid
echo "enter memory target :"
read memtarget
echo "enter block size : "
read blocksize
echo "enter control file loaction 1 :"
read cont1
echo "enter controlfile loaction 2 :"
read cont2
echo "enter controlfile location 3 :"
read cont3
echo "enter audit file destination :"
read auditdest
echo "enter diagnostic location :"
read diagdest
echo "enter flash recovery area loaction :"
read dbrecdest
echo "enter flash area size :"
read dbrecsize
echo "enter undo tablespace name :"
read undotbs
echo "enter datafiles location : "
read dbloc
echo "enter the pfile name :"
read pname
export ORACLE_SID=$dbsid
touch $ORACLE_HOME/dbs/$pname.ora
echo "db_name='$dbname'
db_block_size=$blocksize
control_files='$cont1/controlfile1.ctl','$cont2/controlfile2.ctl,'$cont3/controlfile3.ctl'
memory_target=$memtarget
undo_tablespace='$undotbs'
db_recovery_file_dest='$dbrecdest'
db_recovery_file_dest_size=$dbrecsize
diagnostic_dest='$diagdest'
audit_file_dest='$auditdest'
compatible='11.1.0.6.0'
db_domain=''
db_file_multiblock_read_count=16
job_queue_processes=10
dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
log_archive_format='%t_%s_%r.dbf'
open_cursors=300
processes=150
remote_login_passwordfile='EXCLUSIVE'
undo_management='AUTO'">>$ORACLE_HOME/dbs/$pname.ora
touch $ORACLE_HOME/dbs/$pname.sql
echo "CREATE DATABASE $dbname ">>$ORACLE_HOME/dbs/$pname.sql
echo " LOGFILE GROUP 1 ('$dbloc/redo01.log') SIZE 100M, ">>$ORACLE_HOME/dbs/$pname.sql
echo " GROUP 2 ('$dbloc/redo02.log') SIZE 100M,">>$ORACLE_HOME/dbs/$pname.sql
echo " GROUP 3 ('$dbloc/redo03.log') SIZE 100M">>$ORACLE_HOME/dbs/$pname.sql
echo " DATAFILE '$dbloc/system01.dbf' SIZE 325M REUSE ">>$ORACLE_HOME/dbs/$pname.sql
echo " SYSAUX DATAFILE '$dbloc/sysaux01.dbf' SIZE 325M REUSE ">>$ORACLE_HOME/dbs/$pname.sql
echo " DEFAULT TABLESPACE users ">>$ORACLE_HOME/dbs/$pname.sql
echo " DATAFILE '$dbloc/users01.dbf'">>$ORACLE_HOME/dbs/$pname.sql
echo " SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED">>$ORACLE_HOME/dbs/$pname.sql
echo " DEFAULT TEMPORARY TABLESPACE tempts1">>$ORACLE_HOME/dbs/$pname.sql
echo " TEMPFILE '$dbloc/temp01.dbf'">>$ORACLE_HOME/dbs/$pname.sql
echo " SIZE 20M REUSE ">>$ORACLE_HOME/dbs/$pname.sql
echo "UNDO TABLESPACE $undotbs">>$ORACLE_HOME/dbs/$pname.sql
echo " DATAFILE '$dbloc/undotbs01.dbf'">>$ORACLE_HOME/dbs/$pname.sql
echo " SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED; " >>$ORACLE_HOME/dbs/$pname.sql
sqlplus -S / as sysdba << EOF
startup nomount
@$ORACLE_HOME/dbs/init$ORACLE_SID.sql
EOF
DATABASE CREATION IN WINDOWS:
---------------------------------------------
1)creating required directories:
-------------------------------
mkdir E:\oracle\product\10.2.0\admin\sure
mkdir E:\oracle\product\10.2.0\admin\sure\adump
mkdir E:\oracle\product\10.2.0\admin\sure\bdump
mkdir E:\oracle\product\10.2.0\admin\sure\cdump
mkdir E:\oracle\product\10.2.0\admin\sure\dbdump
mkdir E:\oracle\product\10.2.0\admin\sure\pfile
mkdir E:\oracle\product\10.2.0\admin\sure\udump
mkdir E:\oracle\product\10.2.0\oradata
mkdir E:\oracle\product\10.2.0\oradata\sure
mkdir E:\oracle\product\10.2.0\flash_recovery_area
mkdir E:\oracle\product\10.2.0\flash_recovery_area\sure
2)creating parameter files:
-------------------------
i) edit and change the exsting parameter file(init.ora)
ii) create initsure.ora(it contains only link to init.ora)
and save in F:\oracle\product\10.2.0\db_1\database.it contains
iFILE=E:\oracle\product\10.2.0\admin\pfile\init.ora
3)creating password file:
-----------------------
orapwd.exe file=F:\oracle\product\10.2.0\db_1\database\pwdsure.ora password=sure force=y
4) create the service and alter the service as automatic
for create ---> oradim.exe -new -sid sure -startmode manual -spfile
for edit ---> oradim.exe -edit -sid sure -startmode auto -srvcstart system
set ORACLE_SID=sure
sqlplus /nolog
connect sys/sure as sysdba
startup nomount
set echo on
spool on E:\oracle\
5) create the database as follows:(createDB.sql)
-------------------------------
CREATE DATABASE "sure"
MAXINSTANCES 8
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
DATAFILE 'E:\oracle\product\10.2.0\oradata\sure\system01.dbf' SIZE 300M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE 'E:\oracle\product\10.2.0\oradata\sure\sysaux01.dbf' SIZE 120M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE 'E:\oracle\product\10.2.0\oradata\sure\temp01.dbf' SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
SMALLFILE UNDO TABLESPACE "UNDOTBS1" DATAFILE 'E:\oracle\product\10.2.0\oradata\sure\undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
CHARACTER SET WE8MSWIN1252
NATIONAL CHARACTER SET AL16UTF16
LOGFILE GROUP 1 ('E:\oracle\product\10.2.0\oradata\sure\redo01.log') SIZE 51200K,
GROUP 2 ('E:\oracle\product\10.2.0\oradata\sure\redo02.log') SIZE 51200K,
GROUP 3 ('E:\oracle\product\10.2.0\oradata\sure\redo03.log') SIZE 51200K
USER SYS IDENTIFIED BY "sure" USER SYSTEM IDENTIFIED BY "sure";
6) create the actual tablespaces and make one as default :(createDBFiles.sql)
-------------------------------------------------------
CREATE SMALLFILE TABLESPACE "USERS" LOGGING DATAFILE 'E:\oracle\product\10.2.0\oradata\sure\users01.dbf' SIZE 50M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
create smallfile tablespace "employee" logging datafile 'E:\oracle\product\10.2.0\oradata\sure\employee01.dbf' size 10M reuse autoextend on next 5M maxsize unlimited extent management local segment space management auto;
ALTER DATABASE DEFAULT TABLESPACE "USERS";
7) Run the following scripts:
--------------------------
i) for dictinaries & sql help :(CreateDBCatalog.sql)
--------------------------------------------------
@F:\oracle\product\10.2.0\db_1\rdbms\admin\catalog.sql;
@F:\oracle\product\10.2.0\db_1\rdbms\admin\catblock.sql;
@F:\oracle\product\10.2.0\db_1\rdbms\admin\catproc.sql;
@F:\oracle\product\10.2.0\db_1\rdbms\admin\catoctk.sql;
@F:\oracle\product\10.2.0\db_1\rdbms\admin\owminst.plb;
connect "SYSTEM"/"sure"
@F:\oracle\product\10.2.0\db_1\sqlplus\admin\pupbld.sql;
@F:\oracle\product\10.2.0\db_1\sqlplus\admin\help\hlpbld.sql helpus.sql;
ii) for java support :(JServer.sql)
-------------------------------
connect "SYS"/"sure" as SYSDBA
@F:\oracle\product\10.2.0\db_1\javavm\install\initjvm.sql;
@F:\oracle\product\10.2.0\db_1\xdk\admin\initxml.sql;
@F:\oracle\product\10.2.0\db_1\xdk\admin\xmlja.sql;
@F:\oracle\product\10.2.0\db_1\rdbms\admin\catjava.sql;
@F:\oracle\product\10.2.0\db_1\rdbms\admin\catexf.sql;
iii) for ODM :(odm.sql)
------------------
@F:\oracle\product\10.2.0\db_1\rdbms\admin\dminst.sql SYSAUX TEMP;
iv) for context :(context.sql)
------------------
@F:\oracle\product\10.2.0\db_1\ctx\admin\catctx change_on_install SYSAUX TEMP NOLOCK;
connect "CTXSYS"/"change_on_install"
@F:\oracle\product\10.2.0\db_1\ctx\admin\defaults\dr0defin.sql "AMERICAN";
v) for xdb_protocol :(xdb_protocol.sql)
------------------
@F:\oracle\product\10.2.0\db_1\rdbms\admin\catqm.sql change_on_install SYSAUX TEMP;
connect "SYS"/"sure" as SYSDBA
@F:\oracle\product\10.2.0\db_1\rdbms\admin\catxdbj.sql;
@F:\oracle\product\10.2.0\db_1\rdbms\admin\catrul.sql;
vi) for ordinst :(ordinst.sql)
------------------
@F:\oracle\product\10.2.0\db_1\rdbms\admin\dminst.sql SYSAUX TEMP;
vii) for intermedia :(intermedia.sql)
------------------
@F:\oracle\product\10.2.0\db_1\ord\im\admin\iminst.sql;
viii) for cwmlite :(cwmlite.sql)
------------------
connect "SYS"/"sure" as SYSDBA
@F:\oracle\product\10.2.0\db_1\olap\admin\olap.sql SYSAUX TEMP;
ix) for spatial :(ordinst.sql)
------------------
@F:\oracle\product\10.2.0\db_1\md\admin\mdinst.sql;
x) for emrepository :(dinst.sql)
------------------
@F:\oracle\product\10.2.0\db_1\sysman\admin\emdrep\sql\emreposcre F:\oracle\product\10.2.0\db_1 SYSMAN sure TEMP ON;
WHENEVER SQLERROR CONTINUE;
xi) for postDBCreation :(postDBCreation.sql)
------------------
shutdown immediate;
connect "SYS"/"sure" as SYSDBA
startup mount pfile="E:\oracle\product\10.2.0\admin\sure\pfile\init.ora";
alter database archivelog;
alter database open;
connect "SYS"/"sure" as SYSDBA
set echo on
create spfile='F:\oracle\product\10.2.0\db_1\database\spfilesure.ora' FROM pfile='E:\oracle\product\10.2.0\admin\sure\pfile\init.ora';
shutdown immediate;
connect "SYS"/"sure" as SYSDBA
startup ;
alter user SYSMAN identified by "sure" account unlock;
alter user DBSNMP identified by "sure" account unlock;
select 'utl_recomp_begin: ' || to_char(sysdate, 'HH:MI:SS') from dual;
execute utl_recomp.recomp_serial();
select 'utl_recomp_end: ' || to_char(sysdate, 'HH:MI:SS') from dual;
host F:\oracle\product\10.2.0\db_1\bin\emca.bat -config dbcontrol db -silent -DB_UNIQUE_NAME sure -PORT 1521 -EM_HOME F:\oracle\product\10.2.0\db_1 -LISTENER LISTENER -SERVICE_NAME sure -SYS_PWD &&sysPassword -SID sure -ORACLE_HOME F:\oracle\product\10.2.0\db_1 -DBSNMP_PWD &&dbsnmpPassword -HOST localhost -LISTENER_OH F:\oracle\product\10.2.0\db_1 -LOG_FILE F:\oracle\product\10.2.0\admin\sure\scripts\emConfig.log -SYSMAN_PWD &&sysmanPassword;
exit;
No comments:
Post a Comment