We will learn to create an oracle 8i database; ultimate objective is to upgrade same to 9i
System preparation:
1) Run a disk clean up
2) Run disk defragmenter.
3) Install database software
Pre database creation steps:
1) Check system resources (Memory, RAM, privileges)
2) Familiarize with initialization parameter.
3) Decide block size.
4) Decide Oracle SID
5) Use undo table space to manage your undo record rather then roll back segment.
6) Create parameter file (init.ora) {name is initORACLE_SID.ora}
7) Create backup and recovery strategy.
Steps to Create/verify Dir structure:
1) Find out your oracle home
2) Now create folders in following order
C:\ORACLE_HOME\ADMIN\ORACLE_SID
C:\ORACLE_HOME\ADMIN\ORACLE_SID\UDUMP
/* Place for user/custom generated dump files */
C:\ORACLE_HOME\ADMIN\ORACLE_SID\BDUMP /* place for dump file alert log etc. */
C:\ORACLE_HOME\ADMIN\ORACLE_SID\PFILE /* Place for parameter file */
C:\ORACLE_HOME\ADMIN\ORACLE_SID\LOGS /* Place for log file*/
C:\ORACLE_HOME\ORADATA\ORACLE_SID /*Place for data file*/
Note: Your password file will be @ C:\ORACLE_HOME\DATABASE\pwdORACLE_SIDora
Let’s Jump to action:
1) Place initORACLE_SID.ora file @ C:\ORACLE_HOME\ADMIN\ORACLE_SID\PFILE
An example pfile is given below {you can use it in your database creation ; file name will be initPROD.ora}
-------
#
# Copyright (c) 1991, 2000 by Oracle Corporation
#
##############################################################################
# Example INIT.ORA file
#
# This file is provided by Oracle Corporation to help you customize
# your RDBMS installation for your site. Important system parameters
# are discussed, and example settings given.
#
# Some parameter settings are generic to any size installation.
# For parameters that require different values in different size
# installations, three scenarios have been provided: SMALL, MEDIUM
# and LARGE. Any parameter that needs to be tuned according to
# installation size will have three settings, each one commented
# according to installation size.
#
# Use the following table to approximate the SGA size needed for the
# three scenarious provided in this file:
#
# -------Installation/Database Size------
# SMALL MEDIUM LARGE
# Block 2K 4500K 6800K 17000K
# Size 4K 5500K 8800K 21000K
#
# To set up a database that multiple instances will be using, place
# all instance-specific parameters in one file, and then have all
# of these files point to a master file using the IFILE command.
# This way, when you change a public
# parameter, it will automatically change on all instances. This is
# necessary, since all instances must run with the same value for many
# parameters. For example, if you choose to use private rollback segments,
# these must be specified in different files, but since all gc_*
# parameters must be the same on all instances, they should be in one file.
#
# INSTRUCTIONS: Edit this file and the other INIT files it calls for
# your site, either by using the values provided here or by providing
# your own. Then place an IFILE= line into each instance-specific
# INIT file that points at this file.
#
# NOTE: Parameter values suggested in this file are based on conservative
# estimates for computer memory availability. You should adjust values upward
# for modern machines.
#
###############################################################################
db_name = "prod"
instance_name = prod
service_names = prod
db_files = 1024
control_files = ("C:\oraAmresh\oradata\prod\control01.ctl", "C:\oraAmresh\oradata\prod\control02.ctl", "C:\oraAmresh\oradata\prod\control03.ctl")
open_cursors = 100
max_enabled_roles = 30
db_file_multiblock_read_count = 8
db_block_buffers = 12465
shared_pool_size = 17019392
large_pool_size = 614400
java_pool_size = 0
log_checkpoint_interval = 10000
log_checkpoint_timeout = 1800
processes = 150
parallel_max_servers = 5
log_buffer = 32768
#audit_trail = true # if you want auditing
#timed_statistics = true # if you want timed statistics
max_dump_file_size = 10240 # limit trace file size to 5M each
# Uncommenting the line below will cause automatic archiving if archiving has
# been enabled using ALTER DATABASE ARCHIVELOG.
# log_archive_start = true
# log_archive_dest_1 = "location=C:\oraAmresh\oradata\prod\archive"
# log_archive_format = %%ORACLE_SID%%T%TS%S.ARC
# If using private rollback segments, place lines of the following
# form in each of your instance-specific init.ora files:
#rollback_segments = ( RBS0 )
# Global Naming -- enforce that a dblink has same name as the db it connects to
global_names = true
# Uncomment the following line if you wish to enable the Oracle Trace product
# to trace server activity. This enables scheduling of server collections
# from the Oracle Enterprise Manager Console.
# Also, if the oracle_trace_collection_name parameter is non-null,
# every session will write to the named collection, as well as enabling you
# to schedule future collections from the console.
# oracle_trace_enable = true
oracle_trace_collection_name = ""
# define directories to store trace and alert files
background_dump_dest = C:\oraAmresh\admin\prod\bdump
#Uncomment this parameter to enable resource management for your database.
#The SYSTEM_PLAN is provided by default with the database.
#Change the plan name if you have created your own resource plan.# resource_manager_plan = system_plan
user_dump_dest = C:\oraAmresh\admin\prod\udump
db_block_size = 4096
remote_login_passwordfile = Shared
os_authent_prefix = ""
# The following parameters are needed for the Advanced Replication Option
job_queue_processes = 4
job_queue_interval = 60
open_links = 4
distributed_transactions = 10
compatible = 8.1.0.0.0
sort_area_size = 65536
sort_area_retained_size = 65536
2) Create Oracle Service.
Each instance requires a window service to create this run blow command.
3) ORADIM –NEW –SID prod –INTPWD manager –STARTMODE AUTO –PFILE C:\ORACLE_HOME\ADMIN\ORACLE_SID\PFILE\init
4) Open command prompt and run following command {you can change various parameters as par your need.}
a) svrmgrl
b) connect to internal
c) shutdown any other database (if running)
d) run below statement
CREATE DATABASE prod
LOGFILE 'C:\ORACLE_HOME \oradata\prod\LOG1A.ora' SIZE 1024K,
'C:\ORACLE_HOME \oradata\prod\LOG1B.ora' SIZE 1024K,
'C:\ORACLE_HOME \oradata\prod\LOG1C.ora' SIZE 1024K
MAXLOGFILES 32
MAXLOGMEMBERS 2
MAXLOGHISTORY 1
DATAFILE 'C:\ORACLE_HOME \oradata\prod\sys1prod.dbf' SIZE 264M REUSE AUTOEXTEND ON NEXT 10240K
MAXDATAFILES 254
MAXINSTANCES 1
CHARACTER SET WE8ISO8859P1
NATIONAL CHARACTER SET WE8ISO8859P1;
Your database should be created as this point but in order to make it working we need to do some additional tasks.
I am giving my session snap shot for this.
SVRMGR> spool C:\OraAmresh\admin\prod\LOGS
SVRMGR> connect internal/********
Connected.
SVRMGR> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SVRMGR> startup nomount pfile=c:\oraAmresh\admin\pfile\initprod.ora
LRM-00109: could not open parameter file 'c:\oraAmresh\admin\pfile\initprod.ora'
ORA-01078: failure in processing system parameters
SVRMGR> startup nomount pfile=c:\oraAmresh\admin\prod\pfile\initprod.ora
ORACLE instance started.
Total System Global Area 73431068 bytes
Fixed Size 75804 bytes
Variable Size 22220800 bytes
Database Buffers 51056640 bytes
Redo Buffers 77824 bytes
SVRMGR>
SVRMGR>
SVRMGR> CREATE DATABASE prod
2> LOGFILE 'C:\oraAmresh\oradata\prod\LOG1A.ora' SIZE 1024K,
3> 'C:\oraAmresh\oradata\prod\LOG1B.ora' SIZE 1024K,
4> 'C:\oraAmresh\oradata\prod\LOG1C.ora' SIZE 1024K
5> MAXLOGFILES 32
6> MAXLOGMEMBERS 2
7> MAXLOGHISTORY 1
8> DATAFILE 'C:\oraAmresh\oradata\prod\sys1prod.dbf' SIZE 264M REUSE AUTOEXTEND ON NEXT 10240K
9> MAXDATAFILES 254
10> MAXINSTANCES 1
11> CHARACTER SET WE8ISO8859P1
12> NATIONAL CHARACTER SET WE8ISO8859P1;
Statement processed.
SVRMGR>
SVRMGR> CREATE TABLESPACE prodT01
2> DATAFILE 'C:\ORAAMRESH\ORADATA\prod\prodd01.DBF' SIZE 80M AUTOEXTEND ON
3> NEXT 32K MAXSIZE 150M
4> EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K
5> LOGGING
6> ONLINE
7> ;
Statement processed.
SVRMGR>
SVRMGR> CREATE TABLESPACE prodT02
2> DATAFILE 'C:\ORAAMRESH\ORADATA\prod\prodd02.DBF' SIZE 80M AUTOEXTEND ON
3> NEXT 32K MAXSIZE 150M
4> EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K
5> LOGGING
6> ONLINE
7> ;
Statement processed.
SVRMGR>
SVRMGR> CREATE ROLLBACK SEGMENT prod_dummy TABLESPACE system
2> STORAGE (INITIAL 128K NEXT 128K MINEXTENTS 2);
Statement processed.
SVRMGR> alter rollback segment prod_dummy online;
Statement processed.
SVRMGR>
SVRMGR> CREATE TEMPORARY TABLESPACE temp_prod
2> TEMPFILE 'C:\ORAAMRESH\ORADATA\prod\prod_TEMP.DBF' SIZE 200M
3> EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M
4> ;
Statement processed.
SVRMGR>
SVRMGR> CREATE TABLESPACE users
2> DATAFILE 'C:\ORAAMRESH\ORADATA\prod\prod01.DBF' SIZE 200M
3> EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
4> ONLINE
5> ;
Statement processed.
SVRMGR>
SVRMGR> drop tablespace users;
Statement processed.
SVRMGR>
SVRMGR> CREATE TABLESPACE users
2> DATAFILE 'C:\ORAAMRESH\ORADATA\prod\prodd03.DBF' SIZE 200M
3> EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
4> ONLINE;
Statement processed.
SVRMGR>
SVRMGR> ALTER USER SYS IDENTIFIED BY MANAGER TEMPORARY TABLESPACE
2> TEMPORARY_DATA
3> ;
Statement processed.
SVRMGR> ALTER USER SYSTEM IDENTIFIED BY MANAGER TEMPORARY TABLESPACE
2> TEMPORARY_DATA
3> ;
Statement processed.
SVRMGR>
SVRMGR> create public rollback segment PD01 tablespace prodT01 storage(initial 1M OPTIMAL NULL);
Statement processed.
SVRMGR> create public rollback segment PD02 tablespace prodT01 storage(initial 1M OPTIMAL NULL);
Statement processed.
SVRMGR> create public rollback segment PD03 tablespace prodT01 storage(initial 1M OPTIMAL NULL);
Statement processed.
SVRMGR> create public rollback segment PD04 tablespace prodT01 storage(initial 1M OPTIMAL NULL);
Statement processed.
SVRMGR> create public rollback segment PD05 tablespace prodT01 storage(initial 1M OPTIMAL NULL);
Statement processed.
SVRMGR> ALTER ROLLBACK SEGMENT PD01 ONLINE;
Statement processed.
SVRMGR> ALTER ROLLBACK SEGMENT PD02 ONLINE;
Statement processed.
SVRMGR> ALTER ROLLBACK SEGMENT PD03 ONLINE;
Statement processed.
SVRMGR> ALTER ROLLBACK SEGMENT PD04 ONLINE;
Statement processed.
SVRMGR> ALTER ROLLBACK SEGMENT PD05 ONLINE;
Statement processed.
SVRMGR>
SVRMGR> create public rollback segment PD21 tablespace prodT02 storage(initial 1M OPTIMAL NULL);
Statement processed.
SVRMGR> create public rollback segment PD22 tablespace prodT02 storage(initial 1M OPTIMAL NULL);
Statement processed.
SVRMGR> create public rollback segment PD23 tablespace prodT02 storage(initial 1M OPTIMAL NULL);
Statement processed.
SVRMGR> create public rollback segment PD24 tablespace prodT02 storage(initial 1M OPTIMAL NULL);
Statement processed.
SVRMGR> create public rollback segment PD25 tablespace prodT02 storage(initial 1M OPTIMAL NULL);
Statement processed.
SVRMGR> ALTER ROLLBACK SEGMENT PD21 ONLINE;
Statement processed.
SVRMGR> ALTER ROLLBACK SEGMENT PD22 ONLINE;
Statement processed.
SVRMGR> ALTER ROLLBACK SEGMENT PD23 ONLINE;
Statement processed.
SVRMGR> ALTER ROLLBACK SEGMENT PD24 ONLINE;
Statement processed.
SVRMGR> ALTER ROLLBACK SEGMENT PD25 ONLINE;
Statement processed.
SVRMGR>
SVRMGR> connect sys/manager
Connected.
SVRMGR> select name from v$database;
NAME
---------
PROD
1 row selected.
Next step is to connect thru sys/manger account or “/as sysdba”
And run following scripts.
catalog.sql {creates the data dictionary }
catsnmp.sql { creates the dbsnmp account and the SNMPAGENT role. The catsnmp.sql script is run by catalog.sql during the installation }
catexp7.sql (creates the necessary export views{for full database export})
catproc.sql {Creates structure needed to run PL/SQL}
10 XXRPA_ENTITLEMENT_REFERENCE_TABLE.sql rita/apb/functional/datamodel/scripts 1.2
15 XXRPA_ENTITLEMENTS_WFL02_LOG_TABLE.sql rita/apb/functional/datamodel/scripts 1.1
17 XXRPA_LANDPARCEL_ORDER_S_SEQUENCE.sql rita/apb/functional/datamodel/scripts 1.2
21 XXRPA_NR_REFERENCE_AMOUNT_TABLE.sql rita/apb/functional/datamodel/scripts 1.2
29 XXRPA_REF_AMTS_LINK_TABLE.sql rita/apb/functional/datamodel/scripts 1.2
No comments:
Post a Comment