Oracle Setup - Basics John Meister - Dec 12, 2003 Oracle Install notes -------------------- Oracle 8i - 2 gig Oracle 9i - 3 gig -------------------- Server Memory Min Base memory - 328M - Recommended - 512M 4mb + 4mb swap for each user Min Swap 1 gig ------------------------------------ ------------------------------------ oratab tnsnames.ora listener.ora ------------------------------------ ------------------------------------ DATABASE LAYOUT: ------- SYSTEM - data dictionary - growth is proportional to metadata, i/o moderate ------- TOOLS - store standard objects - functions, procedures & queries - growth, i/o moderate ------- USERS - Oracle User info - if only one user accessed infrequently - doesn't get filled ------ TEMP - scratch pad - heavily used I/O HIGH ------ RBS - Roll Back Segment - allows "undo" expands w/metadata - i/o moderate, heavier then system. ------ SID_DATA - metadata for instance. BUSIEST most growth, most important, I/O heavy ISOLATE (prod_data) ------ SID_iLOG - logs required tables and indexes, may be specific to application ------ Redo Logs - Oracle buffers all incoming transactions SIX logs - 2 sets of 3 usually mirrored - I/O is moderate for each log ------ Control files - three copies - minimal I/O, need to be separate from database. ------------------------------------ ------------------------------------ GUIDLINES: --------- -Separate the Redo log mirrors -Place control File on a separate drive -Keep the DATA tablespace separated from the others -Separate the TEMP tablespace from RBS and SYSTEM -Separate the RBS tablespace from the others -Separate the Redo Logs from the tablespaces ------------------------------------ Oracle DBA handbook - 22 disks! for optimal performance. 17,13ok-7 disk compromise reality - 5 disks (separate file systems) ------------------------------------ / - nothing on root /do /d1 /d2 /d3 /d4 System/Tools/Users - d1 - lowest priority RBS - d1 TEMP anywhere but d2 - d0 or d1 DATA - d2 _Log - d0 RL G1 - d0 RL G2 - d2 CF1 - d1 CF2 - d2 CF3 - d3 ------------------------------------ 4 disk scenario: Tablespace - size - drv#/Ctrl# - dir system-tools-users - 300/5/1M - 3/1 - /d1/prod RBS - 50M 3/1 - /d2/prod TEMP - 50M 2/2 - /d3/prod PROD_DATA 200M 4/2 - /d4/prod PROD_ILOG - 50M - 2/2 - /d1/prod Redo Logs 1-3 member 1 20M 1/1 /d1/prod Redo Logs 1-3 member 2 20M 2/2 /d2/prod Control Files 1 2/2 /d1/prod Control Files 2 3/1 /d2/prod Control Files 3 4/2 /d3/prod ------------------------------------ Solaris: /var/opt/oracle HP-UX: /etc ------------------------------------ for Oracle user, edit profile: ORACLE_HOME=/d1/oracleXXXX (ver) PATH $ORACLE_HOME/bin: ... export PATH ------------------------------------ ORACLE_HOME ORACLE_SID ------------------------------------ SOLARIS: /etc/system: 1) set shmsys:shminfo_shmamax=4294967295 2) set shmsys:shminfo_shmmin=1 3) set shmsys:shminfo_shmni=100 4) set shmsys:shminfo_shmseg=10 5) set semsys:seminfo_semmni=100 6) set semsys:seminfo_semmsl=100 7) set semsys:seminfo_semmns=200 8) set semsys:seminfo_semopm=100 9) set semsys:seminfo_semvmx=32767 /reconfigure - (new kernel - reboot) ------------------------------------ HP-UX: SAM (/etc/stand/system): Apply Tuned parameter set: General OLTP/Database Server System then: maxdsiz to 1G maxssiz to 250MB maxtsiz to 1G max_thread_proc to 256 process new kernel ------------------------------------ login as Oracle to install, NOT root! cd /d1/oracle817/bin ./runInstaller ------------------------------------ JDK - Java Development Kit (oracle 8.1.7.4 minimum) mkdir /opt/java1.2 ------------------------------------ SID - System Identifier ------------------------------------ login as oracle, establish the environment - ORACLE_SID (prod) To Run Server Manager: svrmgrl SVRMGR> connect internal SVRMGR> startup SVRMGR> shutdown SVRMGR> exit ------------------------------------ ps -ef | grep ora_ ------------------------------------ /etc/init.d/oracle.daemon - SUN /etc/rc3.d/S99oracle.daemon /etc/rc3.d/K99oracle.daemon /sbin/init.d/oracle.daemon - HP-UX /sbin/rc3.d/S909oracle.daemon /sbin/rc3.d/K909oracle.daemon /etc/oracle.daemon - AIX ==================================== ORACLE DATABASE ------------------- Physical Structure ------------------- Data files Control file Redo log files Archived redo log files Parameter file Password file Logical Structure ------------------- Database - The first component in the storage hierarchy is the database, which is logically divided into tablespaces. Tablespaces - The tablespace is used to group related logical structures. For example, tablespaces commonly group all objects of an application to simplify certain administrative operations. Segments - A tablespace may contain one or more segments. A segment is the space used by one logical structure, such as a table or an index. Extents - An extent is a contiguous set of blocks used to store a specific type of information. Extents are added when a segment requires more space. Blocks - An Oracle block is the smallest unit of input/output. When data needs to be retrieved from the disk, the Oracle server requests one or more Oracle blocks. Its size should be a multiple of the operating system block size. ---------------------------------------------------------------- Oracle 8i Installation -- SUN OS System Requirements Memory 256 mb RAM (minimum) Swap Space 3 times the Physical Memory (unless the system exceeds 1 GB of Physical Memory, then 2 times the amount is sufficient /tmp 400 mb OS Packages SUNWarc SUNWbtool SUNWhea SUNWlibm SUNWlibms SUNWsprot SUNWtoo Minimum kernel parameters: SHMMAX 4294967295 SHMMIN 1 SHMMNI 100 SHMSEG 10 SEMMNI 100 SEMMSL 200 SEMMNS 1000 SEMOPM 100 SEMVMX 32767 Create unix accounts: Login Default Password Group Home Directory Shell oracle oracledba dba /apps/oracle ksh Oracle and precise users need permission to execute cron and ftp. Create directory: /var/opt/oracle Oracle needs write permissions to this directory Create filesystem mount points owned by oracle/dba File name Size /apps/oracle 8 GB /d01 DBA will provide /d02 DBA will provide /d03 DBA will provide /d04 DBA will provide /d05 DBA will provide etc Enable large file sizes During installation run root.sh or provide vroot password to dba After Oracle Installation Setup DB start and stop copy /apps/oracle/dbora to /etc/init.d Create soft link to /etc/rc3.d: ln -s /etc/init.d/dbora /etc/rc3.d/S99dbstart Create soft link to /etc/rc2.d: ln -s /etc/init.d/dbora /etc/rc2.d/K01dbshut Create unix accounts for Oracle DBA.s Enable sudo to oracle INFO ON ORACLE UNIX SHELL SCRIPTS: http://www.orafaq.net/scripts/unix/otfcheck.txt