Oracle Database Administration

1
Oracle Database Administration

• Session 3
• Installation

2
Announcements

• The TA sessions will be held at 53 Church St.,
  Room 202
• The first TA session starts tonight 735PM, after
  class.
• Online TA session Thursday nights 730PM to
  930PM
• Web site http//www.courses.harvard.edu/ext/22750

3
(No Transcript)
4
(No Transcript)
5
The OFA Standard
6
Oracle Directory Structure
7
Files

• Datafiles contain the actual data stored in a
  database
• The Parameter file contain the initialization
  parameters used to create the memory area it will
  use, to manage the database
• Control files map the physical files of the
  database to the logical tablespaces and online
  redo logs. It helps ensure the database remains
  consistent

8
Files

• Redo files contain enough information to allow
  Oracle to reconstruct or back out a transaction,
  if the database should shutdown before these
  changes have been written to the disk
• INSERT, UPDATE, DELETE, CREATE, ALTER, or DROP
  operations generate redo
• Alert and Trace files contain information on the
  health of the database and provides warnings when
  problems occur

9
Files

• Backup files contain copies of the database files
  and can be used to recover the database.
• The standard convention for file extensions or
  endings to file names are
• data files .dbf
• control files .ctl
• redo log files .dbf (some use .rdo)
• parameter file .ora

10
Datafiles and Tablespaces
11
Oracles Logical World

• A Tablespace is a logical division of a database.
  
• Each tablespace is made up of one or more
  datafiles.
• A datafile belongs to one tablespace

12
Oracles Logical World

• A tablespace can belong to only one database
• There must be at least two tablespaces SYSTEM and
  SYSAUX to create a database
• Other include USERS, UNDO, TOOLS, etc.

13
Undo segments

• Undo segments hold the before image of the data
  in a transaction
• As a program begins to change the data in the
  database, Oracle changes the physical blocks that
  contain that information.
• Before changing the data block buffers in the SGA
  or writing to disk, Oracle takes a copy of this
  data in an undo segment

14
Tablespace Examples

• System holds all objects owned by the sys user
• SYSAUX is an auxiliary tablespace to the SYSTEM
  tablespace
• Rollback (RBS) (undo) is used to store the
  rollback segments
• Temp (Temporary) is used for sort functions.

15
Tablespace Examples

• Users is the default space for user accounts
• Tools should be the default space for the system
  user, after database creation.
• Data holds the real application data
• Index holds the index data for the application

16
System Requirements

• Memory minimum of 1024M required for Oracle11g
  installation
• /usr/sbin/prtconf grep Memory size
• Disk space of approx 3.3GB needed for the
  Oracle11g Enterprise Edition
• 400MB swap minimum
• /usr/sbin/swap l
• 400MB free space in the /tmp directory
• df -k /tmp

17
Operating System Requirements

• O/S Solaris 9 or 10
• uname -r
• O/S kernel patch set version for the specific O/S
  version
• /bin/isainfo -kv

18
Operating System Requirements

• We need a native window manager or some xterm
  emulation software. Character mode installs are
  not supported in 9i or 10g
• The Oracle Universal Installer (OUI) is java
  based

19
Operating System Requirements

• The Java Runtime Environment (JRE) is shipped
  with Oracle10g and 11g
• To determine your operating system information,
  use the following commands
• uname a for system information
• showrev p for patch levels

20
Root Level Setup

• Configure the Kernal
• Create the mount points
• /u01
• /u02
• /u0??
• Create the dba group
• Create the Oracle user

21
Kernel Parameters

• These are the kernel parameters for Oracle11g
• All are found in the /etc/system file
• For Solaris you can also use /etc/project
• Shared Memory is a region that can be shared
  between different processes
• shmmax 4GB (max) (4294967295)
• shmmni 100

22
Kernel Parameters

• SHMMAX - kernel parameter controlling maximum
  size of one shared memory segment
• SEMMNI - kernel parameter controls the maximum
  number of semaphore sets. Semaphores in Unix are
  allocated in sets of 1 to SEMMSL.
• SEMMSL - kernel parameter controls the maximum
  number of semaphores in a semaphore set.
• SEMMNS - kernel parameter controls the maximum
  number of semaphores in the system

23
Kernel Parameters

• What is a Semaphore
• http//en.wikipedia.org/wiki/Semaphore_(programmin
  g)
• http//searchenterpriselinux.techtarget.com/sDefin
  ition/0,,sid39_gci212959,00.html
• Semaphores
• semmni 100
• semmsl 256
• semmns 256

24
Kernel Parameters

• ipcs The ipcs utility provides information
  about active inter-process communication
  facilities
• man ipcs
• ipcrm Can be used to remove a message queue,
  semaphore set, or shared memory ID
• man ipcrm

25
Oracle Requirements

• Oracle 11g cannot be installed into an older
  oracle_home, that contains Oracle Software
  earlier than 11g

26
Unix Account

• The DBA Unix group
• The Oracle Unix account
• Set umask to 022, sets the file creation mask
• Set the DISPLAY variable
• DISPLAYora256.dce.harvard.edu0.0
• (Another example DISPLAY128.103.81.2220.0)
• export DISPLAY

27
Unix Account

• ORACLE_BASE is the directory at the top of the
  Oracle Software and administrative file
  structure. The OFA-recommended value is
  mount_point/app/oracle
• ORACLE_HOME is the directory that contains the
  Oracle software for a given release.
  ORACLE_BASE/product/release_version is the OFA
  standard

28
Unix Account

• NLS_LANG is required if creating a database using
  a character set different than US7ASCII (the
  default)
• ORA_NLS10 is required if creating a database with
  a storage character set other than US7ASCII.

29
Unix Account

• To update the current session
• . ./.profile for Bourne or Korn
• To get the disk space available use the
  following
• df k cat
• To describe the environment and list the
  variables
• env

30
Example Profile

• Local .profile
• Set Environment
• set -a
• EDITORvi
• TERMvt100
• Set Oracle Environment
• ORACLE_BASE/export/home/oracle
• ORACLE_HOMEORACLE_BASE/product/10.0.1test
• TNS_ADMINORACLE_HOME/network/admin
• ORACLE_TERMvt100
• LD_LIBRARY_PATHORACLE_HOME/libLD_LIBRARY_PATH
  
• TMPDIR/tmp
• Set Path Search Directories
• PATHORACLE_HOME/binPATH
• set a

31
Installation

• Mount the CD
• This usually mounts automatically to
  /cdrom/oracle10g
• If not, login as root
• su root
• mkdir cdrom
• Manually mount the CD
• mount r F hsfs device_name /cdrom

32
Installation

• For 11g when codeset downloaded from Oracle
  website and expanded
• 11g/database
• solaris.sparc64_11gR1_database.zip 2GB

33
Installation

• Start the OUI as the Oracle user
• Go to the CD-ROM mount point
• cd /cdrom/oracle10g
• Start the OUI
• ./runInstaller

34
Inventory

• Orainventory is a repository of all installed
  Oracle products
• Make it Oracle Home specific
• /var/opt/oracle for Sun Solaris
• oraInst.loc
• /etc for HPUX, AIX

35
Inventory

• Oracle Installer Location File Location
• inst_groupdba
• inventory_loc/u01/app/oracle/oraInventory
• inventory_loc/u01/app/oracle/product/oraInventory

36
(No Transcript)
37
(No Transcript)
38
(No Transcript)
39
(No Transcript)
40
(No Transcript)
41
(No Transcript)
42
(No Transcript)
43
(No Transcript)
44
(No Transcript)
45
(No Transcript)
46
(No Transcript)
47
(No Transcript)
48
(No Transcript)
49
(No Transcript)
50
(No Transcript)
51
(No Transcript)
52
(No Transcript)
53
(No Transcript)
54
(No Transcript)
55
(No Transcript)
56
(No Transcript)
57
(No Transcript)
58
(No Transcript)
59
(No Transcript)
60
(No Transcript)
61
(No Transcript)
62
(No Transcript)
63
(No Transcript)
64
(No Transcript)
65
(No Transcript)
66
(No Transcript)
67
(No Transcript)
68
(No Transcript)
69
(No Transcript)
70
(No Transcript)
71
(No Transcript)
72
(No Transcript)
73
(No Transcript)
74
(No Transcript)
75
(No Transcript)
76
(No Transcript)
77
(No Transcript)
78
(No Transcript)
79
(No Transcript)
80
(No Transcript)
81
(No Transcript)
82
(No Transcript)
83
(No Transcript)
84
(No Transcript)
85
(No Transcript)
86
Notes

• Assignment 1
• TA Session
• Use the Blog to work together