Background & Overview
The following documentation provides instructions for building an Oracle database for Oracle Grid Control conforming to Oracle OFA database standards and residing on a Sun Solaris 10 (Version 5.10) operating system.
Solaris 10 introduces many new features to the Unix environment. Of particular use to the DBA is the dynamic memory management feature which means that pre-configuration of the kernel - /etc/system - is no longer necessary.
Another new features which will require a change to DBA process and procedure is the use of the Service Management Facility (SMF) which will require all services - the database and listener included - to be stopped and started using SMF commands. As of January 2007, this feature is not supported by Oracle and therefore will not be incorporated into this initial documentation. Instead, the use of shutdown and startup scripts has utilised on earlier versions of Solaris will be maintained.
Assumptions & Pre-Requisites
This document expects and assumes the following:
- The instructions are carried out by a qualified DBA.
- Access to the internet is available.
- All necessary client software, e.g. Telnet and X-Server is available.
- All references to SID should be replaced with correct database name as derived using a valid database naming standard.
- All $variable references assume the .profile as described in the .profile listing section has been implemented and run.
- There is only ONE database installed on the server.
Useful Information
None Currently.
Step-By-Step Guide
1. Ensure oracle account and dba group exists.
2. Ensure /var/opt/oracle directory exists and is owned by oracle:dba.
3. Create and run .profile - see .Profile Listing
4. Ensure the database mountpoints exist as defined in the FileSystem Listing section below.
5. Ensure the oratab exists as defined in the oratab Listing section below.
6. Ensure the init.ora exists as defined in the init.ora Listing section below.
7. Ensure the createdb.sql file as defined in the createdb.sql Listing section below.
8. Ensure the tnsnames.ora file as defined in the tnsnames.ora Listing section below.
9. Download the latest oracle binaries from OTN. For example:
Enterprise Edition for Solaris Operating System (SPARC) (64-bit)
10gr2_db_sol.cpio.gz : 873,334,160 bytes; cksum - 1491417549
p6810189_10204_Solaris-64.zip : 1,276,477,084 bytes; cksum - 2774168749
10. Download the recommended patches for Oracle 10g. At the time of producing this guide the following patches were recommended.
Enterprise Edition for Solaris Operating System (SPARC) (64-bit)
p7592346_10204_Solaris-64.zip : 876,496 bytes; cksum - 1847758887
p7612639_10204_Solaris-64.zip : 7,178,899 bytes; cksum - 3973015041
11. Unzip the downloaded files using the following commands:
- cd /u01/app/oracle
- mkdir -p SOFTWARE/DATABASE/Base
- mkdir -p SOFTWARE/DATABASE/Upgrade
- mkdir -p SOFTWARE/DATABASE/Patches
- cd /u01/app/oracle/SOFTWARE/DATABASE/Base
- gunzip 10gr2_db_sol.cpio.gz
- cpio -idmv < 10gr2_db_sol.cpio
- cd /u01/app/oracle/SOFTWARE/DATABASE/Upgrade
- unzip p6810189_10204_Solaris-64.zip
- cd /u01/app/oracle/SOFTWARE/DATABASE/Patches
- unzip p7592346_10204_Solaris-64.zip
- unzip p7612639_10204_Solaris-64.zip
12. Configure the DISPLAY variable.
13. Start x-server.
14. Run runInstaller and follow the instructions as demonstrated in the Example Install section below.
- cd /u01/app/oracle/software/DATABSE/Base
- ./runInstaller>
15. Run runInstaller for the database patch upgrade.
- Ensure the DISPLAY variable is still configured.
- Ensure x-server is still running.
- cd /u01/app/oracle/software/DATABSE/Upgrade/Disk1
- ./runInstaller>
16. Follow the instructions as demonstrated in the Example Patch Install section below.
17. Apply the recommended patches.
- cd /u01/app/oracle/SOFTWARE/DATABASE/Patches/7612639
- opatch napply -skip_subset -skip_duplicate
- cd /u01/app/oracle/SOFTWARE/DATABASE/Patches/7592346
- opatch napply -skip_subset -skip_duplicate
- Get a Unix administrator to run cpu_root.sh
18. Adjust the oracle file permissions by running
- $ORACLE_HOME/install/changePerm.sh.
- Reply: y
19. Remove template init.ora and initdw.ora files from $ORACLE_HOME/dbs.
20. Configure Oracle environment variables using the following commands:
- . oraenv
- SID
21. Start the listener using the command lsnrctl start.
22. Build the embryo database using the following commands
- cd $ORACLE_BASE/SID/admin/SID/create
- sqlplus '/ as sysdba'
- SQL> @createdb.sql
- SQL> Enter SYS password.
- SQL> Enter SYSTEM password.
- SQL> Enter SID name.
23. Wait for database to build - this may take up to 30 mins…
- Confirm there are no unexpected errors in $ORACLE_BASE/SID/admin/SID/create/createdb.log
24. Set-up the spfile.
- connect / as sysdba
- SQL>CREATE SPFILE='/u01/app/oracle/SID/admin/SID/pfile/spfileSID.ora' FROM PFILE='/u01/app/oracle/SID/admin/SID/pfile/initSID.ora';
- SQL>shutdown immediate
- SQL>exit
- cd $ORACLE_HOME/dbs
- orapwd file=orapwSID password=<password>
- rm initSID.ora
- ln -s /u01/app/oracle/SID/admin/SID/pfile/spfileSID.ora spfileSID.ora
- sqlplus '/ as sysdba'=
- SQL>startup
- SQL>exit
25. Confirm database has been recognised by the listener using the command
- lsnrctl status
26. Confirm connections via SQL*net are working using the following commands:
- sqlplus system/<password>@SID
- SQL>exit
27. Unexpire and unlock dbsnmp
- . oraenv
- ORACLE_SID = [oracle] ? SID
- sqlplus '/ as sysdba'
- SQL> alter user dbsnmp account unlock;
- SQL> alter user identified by password;
28. Drop database control repository if it exists.
- cd $ORACLE_HOME/bin
- ./emca -deconfig dbcontrol db -repos drop
Security Compliance
The following steps should be performed once the database has been built to ensure that the environment conforms to Oracle's Best Practices and also ensures that it doesn't fall foul of any Internal Audit.
1. Remove the GRANT EXECUTE TO PUBLIC privilege from potentially harmful packages.
- sqlplus '/ as sysdba'
- SQL> revoke execute on utl_file from public;
- SQL> revoke execute on dbms_random from public;
- SQL> revoke execute on utl_http from public;
- SQL> revoke execute on utl_smtp from public;
- SQL> revoke execute on utl_tcp from public;
- SQL> exit
2. Ensure the following user-ids' passwords, where applicable, have been changed:
SYS
SYSTEM
DBSNMP
OUTLN
CTXSYS
PERFSTAT
3. Adjust the database DEFAULT TABLESPACE settings so that the SYSTEM is not used inappropriately.
sqlplus '/ as sysdba'
- SQL> alter database default tablespace users;
- SQL> alter user sys default tablespace system;
- SQL> alter user system default tablespace system;
- SQL> quit;
4. Correct any users that already have =SYSTEM= as their =DEFAULT TABLESPACE=:
- sqlplus '/ as sysdba'
- SQL> select username, default_tablespace from dba_users where default_tablespace = 'SYSTEM';
- SQL> alter user <user name> default tablespace <non-SYSTEM tablespace>;
- SQL> exit
5. Lock and expire potentially harmful built-in user-ids if required.
- sqlplus '/ as sysdba'
- SQL> alter user outln password expire account lock;
- SQL> exit
Example Install
- Select Basic Installation.
- Enter Oracle Home Location.
- Enter Unix DBA Group - should be dba.
- De-select Create Starter Database.
- Click Next.
- Enter full path of the orainventroy directory.
- Enter operating system group name - should be dba.
- Click Next.
- Ensure there are 0 requirements to be verified.
- Click Next.
- Select Install database Software only.
- Click Next.
- Review Summary.
- Click Install.
- Ask Unix Administrator to run root.sh. - use default answers.
- Click OK.
- Note iSQL*Plus URL's.
- Click Exit.
- Click Yes.
Example Patch Install
- Click Next.
- Enter Name of the ORACLE_HOME.
- If unsure, check the value by clicking Installed Products.
- Enter full Path of the ORACLE_HOME directory.
- Click Next.
- If you want to use OCM, tick Enable Oracle Configuration Manager
- Enter a valid CSI
- Enter A Valid Metalink Account.
- Click Next.
NOTE You will need to configure the connection settings. These will be site dependant.
- Review Summary.
- Click Install.
- Ask Unix Administrator to run root.sh. - use default locations and overwrite where necessary.
- Click OK.
- Click Exit.
- Click Yes.
oracle .profile
#----------------------------------------------------------------------
# Configure Terminal Settings.
#----------------------------------------------------------------------
stty susp ^Z
stty quit ^C
stty erase ^H
export TERM=vt100-w
export ORACLE_TERM=vt100
#----------------------------------------------------------------------
# Configure Shell Settings.
#----------------------------------------------------------------------
set -o vi
export PATH=/bin:/usr/sbin:/usr/bin:/usr/local/bin:/usr/ccs/bin:$PATH
export EDITOR=vi
export HOSTNAME=‘hostname`
export PS1=’$LOGNAME@$HOSTNAME:$ORACLE_SID> '
export TMPDIR=/tmp
export TEMP=/tmp
umask 022
#----------------------------------------------------------------------
# Configure Aliases.
#----------------------------------------------------------------------
alias ll="ls -la"
alias bdf="df -k"
#----------------------------------------------------------------------
# Configure Oracle Settings.
#----------------------------------------------------------------------
export ORACLE_BASE=/u01/app/oracle
export SQLPATH=$ORACLE_BASE/DBA/SQL
export ORACLE_HOME=$ORACLE_BASE/SID/product/10.2.0
export TNS_ADMIN=$ORACLE_HOME/network/admin
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH
export ORACLE_HOME_LISTNER=/u01/app/oracle/SID/product/10.2.0
export ORATAB=/var/opt/oracle/oratab
#----------------------------------------------------------------------
# Configure Netbackup Settings.
#----------------------------------------------------------------------
export NBU=/opt/openv/netbackup/ext/db_ext/oracle
File Systems
/ --- /var --- /opt ------ /oracle
|
--- /u01 --- /app ------ /oracle ----- /local ----- /bin
|aaaaaaaaaaaaaaaaaaaaaaaaaaaa|
|aaaaaaaaaaaaaaaaaaaaaaaaaaaa--- /software
|aaaaaaaaaaaaaaaaaaaaaaaaaaaa|
|aaaaaaaaaaaaaaaaaaaaaaaaaaaa--- /dba ---- /crontab
|aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa|
|aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa--- /dbchecks
|aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa|
|aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa--- /files
|aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa|
|aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa--- /housekeeping
|aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa|
|aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa--- /scripts
|aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa|
|aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa--- /sql
|aaaaaaaaaaaaaaaaaaaaaaaaaaaa|
|aaaaaaaaaaaaaaaaaaaaaaaaaaaa--- /SID --- /product --- /10.2.0
|aaaaaaaaaaaaaaaaaaaaaaaaaaaa|
|aaaaaaaaaaaaaaaaaaaaaaaaaaaa--- /SID --- /admin ----- /SID ---------- /adump
|aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa|
|aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa--- /bdump
|aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa|
|aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa--- /cdump
|aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa|
|aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa--- /create
|aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa|
|aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa--- /exp
|aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa| (link to /u09)
|aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa|
|aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa--- /parfile
|aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa|
|aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa--- /pfile
|aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa|
|aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa--- /scripts
|aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa|
|aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa--- /udump
|aaaaaaaaaaaaaaaaaaaaaaaaaaaa|
|aaaaaaaaaaaaaaaaaaaaaaaaaaaa--- /OMS --- /product --- /10.2.4
|aaaaaaaaaaaaaaaaaaaaaaaaaaaa|
|aaaaaaaaaaaaaaaaaaaaaaaaaaaa--- /oraInventory
|aaaaaaaaaaaaaaaaaaaaaaaaaaaa|
|aaaaaaaaaaaaaaaaaaaaaaaaaaaa--- /AGENT10G --- /product --- /10.2.4
|aaaaaaaaaaaaaaaaaaaaaaaaaaaa|
|aaaaaaaaaaaaaaaaaaaaaaaaaaaa--- /SOFTWARE
|
|
--- /u02 --- /oradata --- /SID -------- /redo ------ /d1
|
--- /u03 --- /oradata --- /SID -------- /indx ------ /d1
|aaaaaaaaaaaaaaaaaaaaaaaaaaaaa|
|aaaaaaaaaaaaaaaaaaaaaaaaaaaaa--- /sysaux ---- /d1
|aaaaaaaaaaaaaaaaaaaaaaaaaaaaa|
|aaaaaaaaaaaaaaaaaaaaaaaaaaaaa--- /system ---- /d1
|aaaaaaaaaaaaaaaaaaaaaaaaaaaaa|
|aaaaaaaaaaaaaaaaaaaaaaaaaaaaa--- /tools ----- /d1
|aaaaaaaaaaaaaaaaaaaaaaaaaaaaa|
|aaaaaaaaaaaaaaaaaaaaaaaaaaaaa--- /users ----- /d1
|
--- /u04 --- /oradata --- /SID -------- /undo ------ /d1
|
--- /u05 --- /oradata --- /SID -------- /temp ------ /d1
|
--- /u06 --- /oradata --- /SID -------- /arch ------ /d1
|
--- /u07 --- /oradata --- /SID -------- /data ------ /d1
|
--- /u08 --- /oradata --- /SID -------- /index ------ /d1
|
--- /u09 --- /oradata --- /SID -------- /exp ------- /d1
oratab
SID:/u01/app/oracle/SID/product/10.2.0:Y
Initialisation Parameters
###########################################
# Cache and I/O
###########################################
db_block_size=8192
db_cache_size=1G
db_block_checking=MEDIUM
db_block_checksum='FULL'
db_cache_advice='ON'
db_file_multiblock_read_count=16
#filesystemio_options=setall #Unhash this parameter if using SVM
log_buffer=1048576
sga_max_size=6G
sga_target=3G###########################################
# Cursors and Library Cache
###########################################
open_cursors=500
session_cached_cursors=200###########################################
# Database Identification
###########################################
compatible='10.2.0.4.0'
db_name=SID###########################################
# Diagnostics and Statistics
###########################################
audit_file_dest=/u01/app/oracle/SID/admin/SID/adump
background_dump_dest=/u01/app/oracle/SID/admin/SID/bdump
core_dump_dest=/u01/app/oracle/SID/admin/SID/cdump
user_dump_dest=/u01/app/oracle/SID/admin/SID/udump###########################################
# File Configuration
###########################################
control_files=("/u03/oradata/SID/users/d1/control01.ctl", "/u03/oradata/SID/tools/d1/control02.ctl", "/u03/oradata/SID/indx/d1/control03.ctl")
db_files=1000###########################################
# Job Queues
###########################################
job_queue_processes=10###########################################
# Miscellaneous
###########################################
optimizer_mode=choose
statistics_level=typical
timed_statistics=TRUE###########################################
# Pools
###########################################
java_pool_size=125829120
large_pool_size=20971520
shared_pool_size=524288000###########################################
# Processes
###########################################
aq_tm_processes=1
processes=1000###########################################
# Security and Auditing
###########################################
remote_login_passwordfile=exclusive###########################################
# Sort, Hash Joins, Bitmap Indexes
###########################################
pga_aggregate_target=1048576000###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_management=AUTO
undo_retention=10800
undo_tablespace=UNDO###########################################
# Enabling Archivelog mode
###########################################
log_archive_dest='/u06/oradata/SID/arch/d1'
log_archive_format=arch_%r_%t_%s.log###########################################
# Hidden Parameters
###########################################
_b_tree_bitmap_plans=false
Create DB Script
--
-- Title : createdb.sql
-- Description : Build an empty database.
--
-- Usage/Notes : For use with 10g databases.
-- Required answers for SYS,
-- SYSTEM and SID.
--
--ACCEPT syspw PROMPT 'Enter SYS password : '
ACCEPT systpw PROMPT 'Enter SYSTEM password : '
ACCEPT sid PROMPT 'Enter SID name : 'SET VERIFY OFF
SET ECHO ONSPOOL createdb.log
STARTUP NOMOUNT
CREATE DATABASE &sid
USER SYS IDENTIFIED BY &syspw
USER SYSTEM IDENTIFIED BY &systpw
MAXDATAFILES 1024
MAXINSTANCES 1
MAXLOGFILES 32
MAXLOGMEMBERS 4
MAXLOGHISTORY 100
CHARACTER SET AL32UTF8
ARCHIVELOG
DATAFILE '/u03/oradata/&sid/system/d1/system_01.dbf'
SIZE 400m
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/u03/oradata/&sid/sysaux/d1/sysaux_01.dbf'
SIZE 400m
LOGFILE
GROUP 1 ('/u02/oradata/&sid/redo/d1/redo001a.dbf')
SIZE 1024m,
GROUP 2 ('/u02/oradata/&sid/redo/d1/redo002a.dbf')
SIZE 1024m,
GROUP 3 ('/u02/oradata/&sid/redo/d1/redo003a.dbf')
SIZE 1024m,
GROUP 4 ('/u02/oradata/&sid/redo/d1/redo004a.dbf')
SIZE 1024m
UNDO TABLESPACE undo
DATAFILE '/u04/oradata/&sid/undo/d1/undo_01.dbf'
SIZE 400m
DEFAULT TEMPORARY TABLESPACE temp
TEMPFILE '/u05/oradata/&sid/temp/d1/temp_01.dbf'
SIZE 400m
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1m
/SET ECHO OFF
PROMPT
PROMPT
PROMPT Now running CATALOG. Please wait….
PROMPT
PROMPTSET TERMOUT OFF
@${ORACLE_HOME}/rdbms/admin/catalog
SET TERMOUT ON
PROMPT
PROMPT
PROMPT Now running CATPROC. Please wait….
PROMPT
PROMPTSET TERMOUT OFF
@${ORACLE_HOME}/rdbms/admin/catproc
SET TERMOUT ON
SET ECHO ON
CREATE TABLESPACE tools
DATAFILE '/u03/oradata/&sid/tools/d1/tools_01.dbf' SIZE 400m
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO
/CREATE TABLESPACE users
DATAFILE '/u03/oradata/&sid/users/d1/users_01.dbf' SIZE 400m
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO
/CREATE TABLESPACE indx
DATAFILE '/u03/oradata/&sid/indx/d1/indx_01.dbf' SIZE 400m
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO
/CREATE USER ops$oracle IDENTIFIED EXTERNALLY
DEFAULT TABLESPACE users
/GRANT DBA TO ops$oracle
/CONN system/&systpw
SET ECHO OFF
PROMPT
PROMPT
PROMPT Now running PUPBLD. Please wait….
PROMPT
PROMPTSET TERMOUT OFF
@?/sqlplus/admin/pupbld.sql
SET TERMOUT ON
SPOOL OFF
tnsnames.ora
<SID> =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = tcp)(HOST = <hostname>)(PORT = 1521))
(CONNECT_DATA = (SID = <SID>))
)EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0)))
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO))
)