|
- Install Oracle Binaries
- Create the init.ora file in /oracle/admin/create
#####################################################################
# Common parms
#####################################################################
ifile = ?/dbs/orabase-dg.ora
#####################################################################
# Structural Parms
#####################################################################
db_domain = psoug
db_block_size = 8192
db_writer_processes = 8
#####################################################################
# Identification & Control Files
#####################################################################
db_name = orabase
control_files = (/oracle/oradata/control.ctl
/oracle/oradata/control.ctl)
#####################################################################
# Version Specific
#####################################################################
compatible = 10.2.0
#####################################################################
# Platform Specific
#####################################################################
disk_asynch_io = false
filesystemio_options = directio
use_indirect_data_buffers = true
#####################################################################
# Security, Audit and Resource Limit
#####################################################################
audit_trail = DB
resource_limit = true
#####################################################################
# NLS Settings
#####################################################################
nls_date_format = DD-MON-YYYY
#####################################################################
# Archive & Redo Logs
#####################################################################
#log_archive_dest = /oracle/arch
log_buffer = 4194304
log_checkpoint_interval = 1050624
log_archive_max_processes = 4
#_log_simultaneous_copies = 48
archive_lag_target = 1800
#####################################################################
# Dump & Output Directories
#####################################################################
audit_file_dest = /oracle/admin/adump
background_dump_dest = /oracle/admin/bdump
core_dump_dest = /oracle/admin/cdump
user_dump_dest = /oracle/admin/udump
utl_file_dir = /oracle/admin/output
#####################################################################
# DB & Instance Limits
#####################################################################
db_files = 512
sessions = 4000
processes = 1500
transactions = 200
#####################################################################
# Process & Session Specific
#####################################################################
open_cursors = 4000
open_links = 10
session_cached_cursors = 40
session_max_open_files = 30
sort_area_retained_size = 1048576
sort_area_size = 4194304
#####################################################################
# Buffer Pool
#####################################################################
db_block_buffers = 300000
db_block_checksum = true
db_block_checking = true
_db_block_lru_latches = 2048
_db_block_hash_latches = 65536
#buffer_pool_keep = (buffers:120000, lru_latches:150)
#buffer_pool_recycle = (buffers:55296, lru_latches:48)
#####################################################################
# Shared Pool & Other "Pools"
# Sort, Hash Joins, Bitmap Indexes
#####################################################################
java_pool_size = 0
large_pool_size = 500M
pga_aggregate_target = 1024M
shared_pool_size = 750M
shared_pool_reserved_size = 96M
streams_pool_size = 0
_shared_pool_reserved_min_alloc = 4000
#####################################################################
# UNDO
#####################################################################
# use automatic undo
undo_management = 'auto'
# which tablespace
undo_tablespace = 'undo_t1'
# keep 8 hours (8*3600)
undo_retention = 28000
_undo_autotune = false
#####################################################################
# Parallelism
#####################################################################
parallel_max_servers = 32
parallel_min_servers = 0
parallel_threads_per_cpu = 8
parallel_execution_message_size = 65535
recovery_parallelism = 16
#parallel_automatic_tuning = true (deprecated in 10g)
#####################################################################
# Shared Server
#####################################################################
dispatchers="(PROTOCOL=TCP) (SERVICE=orabaseXDB)"
#max_dispatchers = 40
#max_shared_servers = 150
#mts_service = orabase
#shared_servers = 8
#####################################################################
# Job Processing
#####################################################################
job_queue_processes = 8
aq_tm_processes = 1
#####################################################################
# Miscellaneous
#####################################################################
background_core_dump = partial
db_file_multiblock_read_count = 16
fast_start_parallel_rollback = FALSE
optimizer_index_caching = 80
optimizer_index_cost_adj = 10
recyclebin = off
_disable_selftune_checkpointing = true
#####################################################################
# Undocumented Parameters & Temporary Fixes
#####################################################################
_b_tree_bitmap_plans = false
_shared_pool_reserved_min_alloc = 4000
_small_table_threshold = 2560
_optim_peek_user_binds = false
#####################################################################
# Events
#####################################################################
#event = "600 trace name library_cache level 10"
# tracing PMON actions
#event = "10500 trace name context forever"
#event = "10196 trace name context forever"
#event = "10246 trace name context forever"
##event = "4031 trace name errorstack level 10"
#event = "10511 trace name context forever, level 2"
#event = "32333 trace name context forever, level 8"
- Create the CreateDB.sql file in /oracle/admin/create
connect "SYS"/"password" as SYSDBA
set echo on
spool /oracle/admin/create/CreateDB.log
startup nomount pfile="/oracle/admin/create/init.ora";
CREATE DATABASE "newdb"
MAXINSTANCES 8
MAXLOGHISTORY 680
MAXLOGFILES 24
MAXLOGMEMBERS 3
MAXDATAFILES 400
DATAFILE '/oracle/oradata01/system01.dbf' SIZE 1000M AUTOEXTEND OFF
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/oracle/oradata02/sysaux01.dbf' SIZE 500M AUTOEXTEND OFF
SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE 'oracle/oradata02/temp01.dbf' SIZE 2000M AUTOEXTEND OFF
SMALLFILE UNDO TABLESPACE "UNDOTBS1" DATAFILE '/oracle/oradata01/undotbs01.dbf' SIZE 2000M AUTOEXTEND OFF
CHARACTER SET WE8MSWIN1252
NATIONAL CHARACTER SET AL16UTF16
LOGFILE GROUP 1 ('oracle/oraredo01/redo01a.log','/oracle/oraredo02/redo01b.log') SIZE 200M,
GROUP 2 ('/oracle/oraredo01/redo02a.log','/oracle/oraredo02/redo02b.log') SIZE 200M,
GROUP 3 ('/oracle/oraredo01/redo03a.log','/oracle/oraredo02/redo03b.log') SIZE 200M,
GROUP 4 ('oracle/oraredo01/redo04a.log','/oracle/oraredo02/redo04b.log') SIZE 200M
USER SYS IDENTIFIED BY "password" USER SYSTEM IDENTIFIED BY "password";
spool off
exit;
- Create the CreateDBFiles.sql script in /oracle/admin/create. You may wish to
use different tablsapce names as required. This is just an example.
connect "SYS"/"password" as SYSDBA
set echo on
spool /oracle/admin/create/CreateDBFiles.log
CREATE SMALLFILE TABLESPACE "USERS" LOGGING DATAFILE '/oracle/oradata01/users_data_01.dbf' SIZE 100M AUTOEXTEND OFF EX
TENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
ALTER DATABASE DEFAULT TABLESPACE "USERS";
CREATE SMALLFILE TABLESPACE "ADDRPOINT" LOGGING DATAFILE '/oracle/oradata01/addrpoint_data_01.dbf' SIZE 10000M AUTOEXT
END OFF EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE SMALLFILE TABLESPACE "APL_INDEX01" LOGGING DATAFILE '/oracle/oradata02/apl_index_01.dbf' SIZE 6000M AUTOEXTEND
OFF EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE SMALLFILE TABLESPACE "EXCHLAYER01" LOGGING DATAFILE '/oracle/oradata01/exchla_data_01.dbf' SIZE 4000M AUTOEXTEN
D OFF EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE SMALLFILE TABLESPACE "INDEX01" LOGGING DATAFILE '/oracle/oradata02/index01_index_01.dbf' SIZE 1600M AUTOEXTEND
OFF EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE SMALLFILE TABLESPACE "INDEX02" LOGGING DATAFILE '/oracle/oradata02/index02_index_01.dbf' SIZE 5000M AUTOEXTEND
OFF EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE SMALLFILE TABLESPACE "INDEX03" LOGGING DATAFILE '/oracle/oradata02/index03_index_01.dbf' SIZE 1200M AUTOEXTEND
OFF EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE SMALLFILE TABLESPACE "INFRADB" LOGGING DATAFILE '/oracle/oradata01/infradb_data_01.dbf' SIZE 1000M AUTOEXTEND O
FF EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE SMALLFILE TABLESPACE "INFRA_INDEX01" LOGGING DATAFILE '/oracle/oradata02/infra_index01_index_01.dbf' SIZE 1000M
AUTOEXTEND OFF EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE SMALLFILE TABLESPACE "LINEREF" LOGGING DATAFILE '/oracle/oradata01/lineref_data_01.dbf' SIZE 5000M AUTOEXTEND O
FF EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE SMALLFILE TABLESPACE "MISC" LOGGING DATAFILE '/oracle/oradata01/misc_data_01.dbf' SIZE 600M AUTOEXTEND OFF EXTE
NT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE SMALLFILE TABLESPACE "PCPREF" LOGGING DATAFILE '/oracle/oradata01/pcpref_data_01.dbf' SIZE 500M AUTOEXTEND OFF
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE SMALLFILE TABLESPACE "RDL_INDEX01" LOGGING DATAFILE '/oracle/oradata02/rdl_index01_index_01.dbf' SIZE 12000M AU
TOEXTEND OFF EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE SMALLFILE TABLESPACE "RDL_INDEX02" LOGGING DATAFILE '/oracle/oradata02/rdl_index02_index_01.dbf' SIZE 250M AUTO
EXTEND OFF EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE SMALLFILE TABLESPACE "RESULTS01" LOGGING DATAFILE '/oracle/oradata01/result01_data_01.dbf' SIZE 7000M AUTOEXTEN
D OFF EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE SMALLFILE TABLESPACE "RESULTS02" LOGGING DATAFILE '/oracle/oradata01/result02_data_01.dbf' SIZE 500M AUTOEXTEND
OFF EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE SMALLFILE TABLESPACE "ROADLAYER01" LOGGING DATAFILE '/oracle/oradata01/roadlayer01_data_01.dbf' SIZE 4000M AUTO
EXTEND OFF EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE SMALLFILE TABLESPACE "ROADLAYER02" LOGGING DATAFILE '/oracle/oradata01/roadlayer02_data_01.dbf' SIZE 1000M AUTO
EXTEND OFF EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE SMALLFILE TABLESPACE "RSLT_INDEX01" LOGGING DATAFILE '/oracle/oradata02/rslt_index01_index_01.dbf' SIZE 1000M A
UTOEXTEND OFF EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE SMALLFILE TABLESPACE "SPLAYER01" LOGGING DATAFILE '/oracle/oradata01/splayer01_data_01.dbf' SIZE 8000M AUTOEXTE
ND OFF EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE SMALLFILE TABLESPACE "SPL_INDEX01" LOGGING DATAFILE '/oracle/oradata02/spl_index01_index_01.dbf' SIZE 2500M AUT
OEXTEND OFF EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE SMALLFILE TABLESPACE "SPL_INDEX02" LOGGING DATAFILE '/oracle/oradata02/spl_index02_index_01.dbf' SIZE 1500M AUT
OEXTEND OFF EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE SMALLFILE TABLESPACE "TEST_RESULTS" LOGGING DATAFILE '/oracle/oradata01/test_results_data_01.dbf' SIZE 3000M AU
TOEXTEND OFF EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE SMALLFILE TABLESPACE "TRSLT_INDEX01" LOGGING DATAFILE '/oracle/oradata02/trslt_index01_index_01.dbf' SIZE 4000M
AUTOEXTEND OFF EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
spool off
- Create the Add_Users.sql script in /oracle/admin/create. You may wish to create a role
if there are many users for grants.
spool /oracle/admin/create/Add_Users.log
REM DROP ROLE NEWDB_LOC_USER;
CREATE ROLE "NEWDB_LOC_USER" NOT IDENTIFIED;
GRANT CONNECT,RESOURCE,CREATE VIEW,CREATE LIBRARY,CREATE SYNONYM,CREATE DIMENSION,CREATE ANY CONTEXT,CREATE MATERIALIZED VIEW to NEWDB
_LOC_USER;
REM DROP user newuser;
create user newuser identified by newpassword default tablespace
MISC TEMPORARY TABLESPACE temp;
GRANT NEWDB_LOC_USER to newuser;
spool off
- Set up Oracle variables such as ORACLE_HOME, ORACLE_SID and PATH=$PATH:$ORACLE_HOME/bin
- As the oracle user run "sqlplus /nolog"
SQL> @/oracle/admin/create/CreateDB.sql
This command will create the database that was specified.
- Create the additional tablesapces. As oracle user run "sqlplus /nolog"
SQL> @/oracle/admin/create/CreateDBFiles.sql
- Create users. As above..
- Create listener.ora in /var/opt/oracle.
LISTENER =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL=tcp)
(HOST=hostname.localdomain) #<-- Use YOUR machines HOST NAME
(PORT=1521)
(COMMUNITY=UK_SUP_TCPIP) #<-- Optional Community
)
(ADDRESS= #<-- UNIX DOMAIN SOCKETS
(PROTOCOL=ipc) #<-- These are used for 'LOCAL'
(KEY=700) #<-- connections.
(COMMUNITY=UK_SUP_IPC) #<-- Optional Community
)
)
- Create tnsnames.ora in /var/opt/oracle
newdb=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = hostname.localdomain)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = hostname.localdomain)
)
)
- Update the /var/opt/oracle/oratab file accordingly.
|