Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Sunday, 3 June 2012

Manual Creation of Oracle database 9i on Windows 2003 env.‎

1. Open the CMD prompt and connect to the default database as sysdba


















2. Now open another command prompt and set oracle SID as set oracle_sid=SHAHID and use ORADIM utility to create service for Shahid Database.
Note: Create a directory called oracle on the required location where all of your database file and parameter files is going to be stored
3. Now create Pfile with default database SHAAN and copy it to the location D:\oracle (use the first CMD which is already open with default database)
4. Modify this pfile (SID=SHAHID) or any other parameter as per your pfile. For your convenient I am pasting the modified copy of pfile. Here in the below example I directly create the pfile on the location d:\oracle.
aq_tm_processes=1
background_dump_dest='D:\oracle\bdump'
compatible='9.2.0.0.0'
control_files='D:\oracle\control01.ctl','D:\oracle\control02.ctl','D:\oracle\control03.ctl'
core_dump_dest='D:\oracle\cdump'
db_block_size=8192
db_cache_size=25165824
db_domain=''
db_file_multiblock_read_count=16
db_name='shahid'
dispatchers='(PROTOCOL=TCP) (SERVICE=shaanXDB)'
fast_start_mttr_target=300
hash_join_enabled=TRUE
instance_name='shahid'
java_pool_size=33554432
job_queue_processes=10
large_pool_size=8388608
open_cursors=300
pga_aggregate_target=25165824
processes=150
query_rewrite_enabled='FALSE'
remote_login_passwordfile='EXCLUSIVE'
shared_pool_size=50331648
sort_area_size=524288
star_transformation_enabled='FALSE'
timed_statistics=TRUE
undo_management='AUTO'
undo_retention=10800
undo_tablespace='UNDOTBS1'
user_dump_dest='d:\oracle\udump'
Note: Create required directory udump, bdump, cdump on the location d:\oracle

5. Now in the current command prompt type sqlplus/nolog and then in SQL prompt type connect sys with new password as sysdba then start the instance in NOMOUNT with the edited pfile.

6. Script to create the database using DB_Create.sql script that I saved in d:\oracle
DB_Create.sql
CREATE DATABASE SHAHID
LOGFILE GROUP 1(‘d:\oracle\redo01.log’) SIZE 100M,
GROUP 2(‘d:\oracle\redo02.log’) SIZE 100M,
GROUP 3(‘d:\oracle\redo03.log’) SIZE 100M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
DATAFILE ‘d:\oracle\system01.dbf’ SIZE 200M
UNDO TABLESPACE UNDOTBS
DATAFILE ‘d:\oracle\UNDOTBS.dbf’
SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED;
Note: do not forget to character set

7. Run this script in the SQL prompt as SQL>@d:\oracle\DB_Create.sql once you run this script you can see the control files, redo log file, Alert log file, data files and trace files are created in d:\oracle directory location.
8.  Now the shutdown the database using “shutdown” command and once the database shutdown reboot your PC then again on CMD prompt
C:\>set oracle_sid=shahid
C:\>sqlplus /nolog;
SQL>conn sys/ahmed as sysdba;
SQL>select name from v$database;
Note: If windows service (Shahid) is not started then start it manually (in RUN type services.msc to start the services) and then start the database directly or using pfile.
SQL>create spfile from pfile=’d:\oracle\shahid.sql’;
SQL>shutdown;
SQL>startup;
9. Execute catalog.sql and catproc.sql script from the default location.
Note: if (in case not necessary) the password file is corrupted or if you get an error in authentication you can recreate the password file as follows, but make sure to delete the existing password file first.
10. Add new entry on TNSNAME.ORA AND LISTENER.ORA through the use of oracle net configuration assistant tools.
# Generated by Oracle configuration tools.
SHAHID =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = IT-TEST)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = shahid)
    )
  )
# LISTENER.ORA Network Configuration File from the default location
# Generated by Oracle configuration tools.
LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = IT-TEST)(PORT = 1521))
  )
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = shahid)
      (SID_NAME = shahid)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = shaan)
      (SID_NAME = shaan)
    )
 )
Note: You can use tnsping Shahid to check the tnsname setting also reload the listener to check the listener setting (use LSNRCTL> stop; start;)
11. Finally start the database perform required checking on SQL prompt.
SELECT DBID, DATABASE_NAME FROM V$DATABASE;
SELECT * FROM GLOBAL_NAME;
SELECT INSTANCE_NAME FROM V$INSTANCE;
SELECT UTL_INADDR.GET_HOST_ADDRESS FROM DUAL;
SELECT UTL_INADDR.GET_HOST_NAME FROM DUAL;
SELECT LOG_MODE FROM V$DATABASE;
SELECT DEST_NAME,STATUS,DESTINATION from V$ARCHIVE_DEST;
SELECT * FROM DATABASE_PROPERTIES;
SELECT FILE_ID, FILE_NAME, TABLESPACE_NAME, BYTES, bytes/(1024*1024*1024) “in GB” FROM DBA_DATA_FILES;
SELECT FILE_ID, FILE_NAME, TABLESPACE_NAME, BYTES, bytes/(1024*1024*1024) “in GB”  FROM DBA_TEMP_FILES
SELECT * FROM V$LOGFILE;
SELECT * FROM V$LOG;
SELECT * FROM V$CONTROLFILE;
SELECT SUM(BYTES)/(1024*1024*1024) "SIZE IN GB" FROM DBA_DATA_FILES;



0 comments:

Post a Comment