MacDevCenter.com: Installing Oracle 9i on Mac OS X, Part 3

Published on MacDevCenter (http://www.macdevcenter.com/)
http://www.macdevcenter.com/pub/a/mac/2003/01/03/oracle_part3.html
See this if you're having trouble printing code examples

Installing Oracle 9i on Mac OS X, Part 3

by David Simpson
01/03/2003

Editor's Note: In Part One of this three-part series exploring Oracle 9i on Mac OS X, David Simpson provided some excellent background information and a look at the ramifications for Solaris administrators. In Part 2 he differentiated between FileMaker Pro and Oracle, with a side conversation about WebObjects. An overview of Oracle's pricing structure was also included. In this closing segment, he shows you how to actually install Oracle on Mac OS X.

Setup Summary for Oracle on Mac OS X

The following parameters are being used for this Oracle installation:

Hostname = G4
Oracle Home = /Users/oracle/v920
Oracle SID = prod1
Oracle UNIX User Account = /Users/oracle, shell=/bin/bash
Oracle datafile directories = /Volumes/u01, /Volumes/u02, /Volumes/u03
Archivelog Directory = /Volumes/archive
Backup Files Directory = /Volumes/backup

The disk layout shown above is designed to show how to spread out the files across multiple mount points on a disk array. This is done to improve performance and reliability. A production database server will generally use multiple mount points in either a mirrored (for optimal performance) or RAID5 (for reduced disk space) configuration. Control files and mirrored redo log files will also be located on separate disks. Of course, you can also install Oracle 9i on a single-drive computer and just put all of the files in one directory, as Oracle describes within their Mac OS X RelaseNotes.pdf file.

Here is a set of example parameters, which I used for the Oracle 9.2 installation on my PowerBook G4:

Hostname = G4
Oracle Home = /Users/oracle/v920
Oracle SID = prod1
Oracle UNIX User Account = /Users/oracle, shell=/bin/bash
Oracle datafile directory = /Users/oracle/prod1
Archivelog Directory = /Users/oracle/archive
Backup Files Directory = /Users/oracle/backup

Hardware and OS Requirements

The official minimum configuration for installing Oracle 9.2.0 on Mac OS X is a server with a G4 CPU and 512MB of RAM. Mac OS X Server 10.2 (also called Jaguar) is officially the minimum version of Mac OS X supported by Oracle. 2 GB (minimum) of available disk space will also be required for installing the database binaries and creating the database instance. However, I have found that it is possible to use either server or client editions of Mac OS X and either a G3 or a G4 CPU. If you want to install a production database in the future, then you will want to meet Oracle's official minimum requirements in order to receive technical support.

Pre-Install Tasks

As with any Oracle database installation, it's necessary to perform a number of pre-install tasks to prepare the server to run the Oracle database software. The pre-install tasks on Mac OS X follow the same basic pattern as other versions of UNIX, but with differing UNIX commands and options.

Prior to attempting any remote SSH connection to the server, it is necessary to enable the SSH service. Open the System Preferences pane, click the "Sharing" icon, and click the "Services" folder tab. Enable the "Remote Login" service by clicking the "On" checkbox.

Configure Ethernet settings prior to starting the database installation by opening the Network preferences pane, selecting either the "Built-in Ethernet" or the "PCI Ethernet" menu item. Enter the IP Address, subnet mask, router, DNS Servers, and Search Domains information for this server -- if this task has not previously been done.

I recommend that a static IP address be assigned so that client computers can reliably access the database server. Dynamic IP addressing would also prevent reliable access to the database server from the Oracle Enterprise Manager Console application, which uses Oracle Management Servers for authentication. Currently, the IP address of each node is embedded within the connection and authentication information stored on the OMS server. This functionality is expected to change with the OEM 4.0 release scheduled for January 2003.

Multiple IP addresses may be assigned to the same Ethernet card by selecting the Network Port Configurations menu item, then clicking the "New" or "Duplicate" buttons and entering the appropriate IP address information. This feature permits DHCP to be used as the primary IP address for the server, but also allows a static IP address to be assigned and used exclusively for Oracle access.

I prefer to enable the root account in Mac OS X so that UNIX setup scripts can be run with the proper permissions. If you are just starting to use the UNIX command line, then I recommend that you just run sudo before executing scripts or commands requiring administrative access. Please remember that if you make a mistake while using the root account, there is no "undo" feature in most cases, so you may have to reinstall Mac OS X to recover.
Mac OS X Server is pre-configured with the root user account enabled. However, this account will generally need to be enabled with the consumer version of Mac OS X. Open the Netinfo Manager application within the Applications/Utilities directory. From the Security pull-down menu (at the top of the screen), select the "Enable Root User" option. Enter the root password, click the "Set" button, enter the root password again, and click the "Verify" button.

On a production server, Energy Saver features should be disabled to prevent the computer from going to sleep due to lack of user activity on the server. Open the System Preferences pane and click on the Energy Saver icon. Under the "Sleep" folder tab, drag the timeout slider to "Never" for the "Put computer asleep..." feature. Click on the "Options" folder tab and select the "Restart automatically after a power failure." option.

Either use the Utilities/Terminal application or use an SSH connection to remotely log into the Mac OS X server. It may seem unusual compared to a typical Mac OS X product install, but you can perform virtually the entire installation process while remotely logged in from another computer. This is, however, typical for most UNIX software installs.

NetInfo is an LDAP-compatible directory service database used by Mac OS X for managing computers, users, groups, passwords, services, and networking configuration information. nicl is a command line program that can be used for managing NetInfo databases in place of the graphical interface. When specifying nicl commands for these examples, the local NetInfo database will be specified by using the "." designation. By managing users, groups, and passwords only within the local NetInfo database, the local Oracle database installation will be immune to a loss of the network connection or changes made to the master NetInfo directory server.

Create UNIX Group

Use the following NetInfo command to create the "dba" UNIX group in the local NetInfo directory. Pick a group id number that does not conflict with any existing gid used on the server. Adding the oracle UNIX account to the admin group provides the Oracle-recommended admin privileges to this account, as you would do manually by checking the "allow user to administer this computer" checkbox in the Accounts Preferences Pane (Mac OS X client) or "administer the server" checkbox within the Workgroup Manager utility (Mac OS X server).

oracle% sudo nicl . -create /groups/dba passwd "*" oracle% sudo nicl . -append /groups/dba gid 100 oracle% sudo nicl . -append /groups/dba users oracle oracle% sudo nicl . -append /groups/admin users oracle

Using the following command will verify that the "dba" group has been successfully created:

oracle% sudo nicl . -list /groups

Create UNIX User

Use the following commands to create the "oracle" user account that will own, install, and execute the Oracle database software. Pick a user id number that does not conflict with any existing uid used on the server.

mkdir /Users/oracle oracle% sudo nicl . -create /users/oracle oracle% sudo nicl . -append /users/oracle group dba oracle% sudo nicl . -append /users/oracle gid 100 oracle% sudo nicl . -append /users/oracle uid 700 oracle% sudo nicl . -append /users/oracle shell /bin/bash oracle% sudo nicl . -append /users/oracle home /Users/oracle oracle% sudo nicl . -append /users/oracle realname "Oracle database owner account" oracle% sudo chown oracle:dba /Users/oracle

Use the standard passwd utility to set a password for the oracle account.

oracle% sudo passwd oracle Changing password for oracle. New password: <enter password> Retype new password: <enter password again>

Using the following command will verify that the "oracle" user has been successfully created:

oracle% sudo nicl . -list /users

Add hostname entries to the NetInfo Directory for this server and any other servers with which this machine will need to communicate. With Mac OS X 10.2 and higher, direct entries may be made into the /etc/hosts file. However, adding these entries into the NetInfo Directory represents the recommended method for performing this type of configuration on Mac OS X. Following this recommendation ensures that this information is available to any servers, applications, or services that may only look for this information in NetInfo.
By default, this hostname is referred to by hostname.local in NetInfo. Adding an entry without the ".local" suffix allows applications to access the machine directly by hostname without adding this suffix. The following command adds this server with a hostname of "G4" and IP Address of "10.1.0.20" to NetInfo:

oracle% sudo nicl . /machines/G4 ip_address 10.1.0.20

If an OEM server is being used for management of this server, then its hostname and IP Address should also be entered.

oracle% sudo nicl . /machines/OEM1 ip_address 10.1.0.7

Note: It is not yet possible to manage Oracle 9.2 on Mac OS X with an OEM server, due to the lack of availability of the Oracle intelligent agent software ($ORACLE_HOME/bin/agentctl). This functionality is expected in a later release of Oracle 9.2 on Mac OS X.

Create Oracle File Directories

For this example installation, external drive volumes named u01, u02, u03 will be used for the installation. On Mac OS X, external drives are mounted automatically by the automounter daemon, after being partitioned and erased by the Disk Utility. These disk volumes are located under the /Volumes directory once they are mounted by Mac OS X. Mac OS X can easily work with volume and directory names containing space characters if the HFS+ file system is used for a volume; it is recommended that spaces not be used in volume names/directories that will be used for Oracle files. The Oracle database software operates as any other UNIX application and is not as forgiving when dealing with these issues as Mac OS X!

Change the ownership of each of the top-level mount points and dump directories:

oracle% sudo mkdir /Volumes/u01/prod1 oracle% sudo mkdir /Volumes/u01/udump oracle% sudo mkdir /Volumes/u01/cdump oracle% sudo mkdir /Volumes/u01/bdump oracle% sudo mkdir /Volumes/u02/prod1 oracle% sudo mkdir /Volumes/u03/prod1 oracle% sudo chown -R oracle:dba /Volumes/u01 oracle% sudo chown -R oracle:dba /Volumes/u02 oracle% sudo chown -R oracle:dba /Volumes/u03

Add the following info to the /Users/oracle/.bashrc file and add "source .bashrc" to the .bash_profile file so that you won't have to manually execute Oracle's set_ulimit script:

ulimit -c unlimited # max size of core dump file is unlimited ulimit -d unlimited # maximum data file size ulimit -s 65536 # set stacksize to 64Mb ulimit -u 500 # max number of processes ulimit -n 10000 # max number of open files

If you choose to use a different shell, like /bin/tcsh, then add the following limit settings to the .tcshrc file within the /Users/oracle directory:

limit coredumpsize unlimited limit datasize unlimited limit memoryuse unlimited limit stacksize 64M limit maxproc 500 limit descriptors 10000

I also recommend putting these values into the into the /private/var/root/.tcshrc file that will be used by the root account if you use the default tcsh shell. I have set the maximum number of processes to 500 and number of open files to 10000 because Mac OS X reports hard limits of 532 and 12288, respectively, on my Titanium Powerbook G4. These parameters don't cause problems if they are set higher than necessary; they only set maximum limits for resource usage. Feel free to adjust them lower if you like.

Update the /etc/profile file with the following environment variable enhancements:

ORACLE_BASE=/Users/oracle/v920 ORACLE_HOME=/Users/oracle/v920 PATH=$PATH:$ORACLE_HOME/bin SRCHOME=$ORACLE_HOME ORACLE_SID=prod1 CLASSPATH=$ORACLE_HOME/DBCreate/oradev/classes/orapts.jar:$ORACLE_HOME/jdbc/lib/classes12.zip:$CLASSPATH LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH DYLD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/rdbms/lib:$DYLD_LIBRARY_PATH ORACLE_OWNER=oracle ORAENV_ASK=NO TNS_ADMIN=$ORACLE_HOME/network/admin TERM=vt100 NLS_LANG=AMERICAN_AMERICA.US7ASCII

export TNS_ADMIN PATH ORACLE_HOME SRCHOME ORACLE_SID DYLD_LIBRARY_PATH CLASSPATH LD_LIBRARY_PATH ORACLE_OWNER ORAENV_ASK TERM NLS_LANG

You can verify the ulimit settings by using the ulimit command while logged in as the oracle UNIX user; this should show results similar to the following.

[G4:/Volumes/u01] oracle% ulimit -a -H core file size (blocks, -c) unlimited data seg size (kbytes, -d) unlimited file size (blocks, -f) unlimited max locked memory (Kbytes, -l) unlimited max memory size (Kbytes, -m) unlimited open files (-n) 10000 pipe size (512 bytes, -p) 1 stack size (Kbytes, -s) 65536 cpu time (seconds, -t) unlimited max user processes (-u) 500 virtual memory (Kbytes, -v) 9007199254806527

Note: If the hard process limits have not previously been set by root, then the oracle account will only be able to make changes up to the soft limit. This fact will probably affect the "max user processes" parameter more than any other, because it has a soft limit of 100. On a test server, this will not generally be an issue, but on a production server, you may commonly have a need for more than 100 user processes because each user or application logged into the server will generally spawn a new oracle process unless some type of connection pooling is implemented in the application.

The startup script listed below will set these process limits as the root user before invoking the database startup commands via an oracle UNIX account subshell process that will then use the limits specified in the .bashrc file.
If you want to avoid rebooting in order to get the higher values for the hard limits, put the tcsh version of the limit commands into the /private/var/root/.tcshrc file. Then open a new terminal session as root before executing the su - oracle command.

Create the Database Startup Script

The Oracle instructions for Developer Release 1 specify the use of the allup.sh script to be run manually to start up the database, and the use of the alldown.sh script to shut down the database before the Mac OS X server is shut down. This startup/shutdown procedure should be automated to ensure that the database can be started up or shut down unattended. Automating this task will ensure database availability upon server startup and prevent a known issue with datafile corruption if the database is not shut down properly before Mac OS X shuts down.

Unfortunately, it is not currently possible to run a shut down script with Mac OS X, so for now this task will still need to be performed manually. The startup script presented here does contain the shutdown code, in preparation for Apple's update of Mac OS X beyond version 10.2.2 to correct this issue. Two files are used by the Mac OS X SystemStarter in order to run scripts or applications upon system startup. The first file is either a binary executable or, more commonly, an executable shell script located within a directory with the same name as the executable file within /Library/StartupItems. The second file is a configuration property list file named StartupParameters.plist, located within the same directory as the startup script. This property list file contains the text describing the item to be started, along with dependencies and services provided.

Create an Oracle directory within /Library/StartupItems and put the executable script listed in Listing #1 into this directory. This script should be given the name of the enclosing directory, which will be Oracle (not Oracle.sh). Note: Some Mac OS X books (not written by O'Reilly!) incorrectly give examples of installing startup scripts in the path /System/Library/StartupItems/*. The directory paths under /System/Library/StartupItems and /Network/Library/StartupItems are reserved for startup scripts created and installed by Apple as part of the core operating system. The path under /Library/StartupItems is available for developers and system administrators to use for additional startup scripts that they may add to the server. For more details about the startup process, please see Chapter 4 of the Inside Mac OS X: System Overview book.

oracle% sudo mkdir /Library/StartupItems/Oracle oracle% sudo chmod 700 /Library/StartupItems/Oracle/Oracle oracle% sudo chown root:wheel /Library/StartupItems/Oracle/Oracle oracle% sudo cp Oracle_startup /Library/StartupItems/Oracle/Oracle oracle% sudo cp StartupParameters.plist /Library/StartupItems/Oracle/StartupParameters.plist oracle% sudo chown root:wheel /Library/StartupItems/Oracle/StartupParameters.plist

Listing #1 -- filename: /Library/StartupItems/Oracle/Oracle

#!/bin/bash # define globally used paths/variables PATH=/bin:/sbin:/usr/bin:/usr/sbin:. ORACLE_BASE=/Users/oracle/v920 ORACLE_HOME=/Users/oracle/v920 ORACLE_OWNER=oracle ORACLE_OWNER_PATH=/Users/oracle ORACLE_GROUP=dba PATH=$PATH:$ORACLE_HOME/bin SRCHOME=$ORACLE_HOME ORACLE_SID=prod1 CLASSPATH=$ORACLE_HOME/DBCreate/oradev/classes/orapts.jar:$ORACLE_HOME/jdbc/lib/classes12.zip:$CLASSPATH LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH DYLD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/rdbms/lib:$DYLD_LIBRARY_PATH ORAENV_ASK=NO TNS_ADMIN=$ORACLE_HOME/network/admin TERM=vt100 NLS_LANG=AMERICAN_AMERICA.US7ASCII

export PATH ORACLE_BASE ORACLE_HOME SRCHOME ORACLE_SID CLASSPATH LD_LIBRARY_PATH DYLD_LIBRARY_PATH ORACLE_OWNER TNS_ADMIN TERM NLS_LANG

# the following ulimit parameters are required by the Oracle database on Mac OS X ulimit -c unlimited ulimit -d unlimited ulimit -s 65536 ulimit -u 500 ulimit -n 10000

ORA_BDUMP_DIR=/Volumes/u01/bdump/ OEM_ADMIN=admin OEM_ADMIN_PASSWORD=zx-vnm

export OEM_ADMIN OEM_ADMIN_PASSWORD ORA_BDUMP_DIR

# get common system config settings . /etc/rc.common

if [ ! -f $ORACLE_HOME/bin/dbstart -o ! -d $ORACLE_HOME ] then echo 'Oracle startup: cannot start' echo ' (cannot find one or both of dbstart or $ORACLE_HOME)' exit fi

StartService() {

# Rotate the Oracle alert error log before database starts LOG=alert_$ORACLE_SID.log cd $ORA_BDUMP_DIR test -f $LOG.5 && mv $LOG.5 $LOG.6 test -f $LOG.4 && MV $LOG.4 $LOG.5 test -f $LOG.3 && MV $LOG.3 $LOG.4 test -f $LOG.2 && MV $LOG.2 $LOG.3 test -f $LOG.1 && MV $LOG.1 $LOG.2 test -f $LOG.0 && MV $LOG.0 $LOG.1 MV $LOG $LOG.0

ConsoleMessage 'Starting Oracle databases...' su - $ORACLE_OWNER -c $ORACLE_HOME/bin/dbstart ConsoleMessage 'Started Oracle database.'

ConsoleMessage 'Starting Oracle Intelligent Agent...' su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/agentctl start" ConsoleMessage 'Started Intelligent Agent.'

ConsoleMessage 'Starting Oracle listener...' su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/lsnrctl start" ConsoleMessage 'Started Listener.'

# ConsoleMessage 'Starting Oracle Enterprise Management Server' # su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/oemctl start oms &" # ConsoleMessage 'Started OMS.'

}

StopService() {

# stop in reverse order from startup process # ConsoleMessage 'Stopping Oracle Enterprise Management Server' # su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/oemctl stop oms $OEM_ADMIN/$OEM_ADMIN_PASSWORD &" # ConsoleMessage 'Stopped OMS.'

ConsoleMessage 'Stopping Oracle Intelligent Agent' su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/agentctl stop" ConsoleMessage 'Stopped Intelligent Agent.'

ConsoleMessage 'Stopping Oracle listener...' su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/lsnrctl stop" ConsoleMessage 'Stopped Listener.'

ConsoleMessage 'Stopping Oracle databases...' su - $ORACLE_OWNER -c $ORACLE_HOME/bin/dbshut ConsoleMessage 'Stopped Oracle database.'

}

RestartService() {

ConsoleMessage 'Stopping Oracle Intelligent Agent' su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/agentctl stop" ConsoleMessage 'Stopped Intelligent Agent.'

ConsoleMessage 'Stopping Oracle listener...' su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/lsnrctl stop" ConsoleMessage 'Stopped Listener.'

ConsoleMessage 'Stopping Oracle databases...' su - $ORACLE_OWNER -c $ORACLE_HOME/bin/dbshut ConsoleMessage 'Stopped Oracle database.'

# Rotate the Oracle alert error log before database starts LOG=alert_$ORACLE_SID.log CD $ORA_BDUMP_DIR test -f $LOG.5 && MV $LOG.5 $LOG.6 test -f $LOG.4 && MV $LOG.4 $LOG.5 test -f $LOG.3 && MV $LOG.3 $LOG.4 test -f $LOG.2 && MV $LOG.2 $LOG.3 test -f $LOG.1 && MV $LOG.1 $LOG.2 test -f $LOG.0 && MV $LOG.0 $LOG.1 MV $LOG $LOG.0

ConsoleMessage 'Starting Oracle databases...' su - $ORACLE_OWNER -c $ORACLE_HOME/bin/dbstart ConsoleMessage 'Started Oracle database.'

ConsoleMessage 'Starting Oracle Intelligent Agent...' su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/agentctl start" ConsoleMessage 'Started Intelligent Agent.'

ConsoleMessage 'Starting Oracle listener...' su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/lsnrctl start" ConsoleMessage 'Started Listener.'

# ConsoleMessage 'Starting Oracle Enterprise Management Server' # su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/oemctl start oms &" # ConsoleMessage 'Started OMS.'

}

RunService "$1"

Create the file /Library/StartupItems/Oracle/StartupParameters.plist from the information located within Listing #2. This file has been configured for an Oracle database startup by specifying a "Late" startup sequence to SystemStarter in order to ensure that any other necessary operating system resources are available by the point in time when the database is started. This file has been written in the newer XML format as recommended in the Mac OS X for UNIX Geeks book (p. 40).

Startup scripts may be tested without restarting the server by passing the "-nd" parameters to the SystemStarter: /sbin/SystemStarter ND.

This test helps you ensure that you haven't accidentally named the startup script incorrectly or made a serious typo in the writing of the plist file. If SystemStarter can't find the startup script, it will give a continuous list of "Waiting for Oracle 9.2.0 Database Server" error messages.

Listing #2 -- filename: /Library/StartupItems/Oracle/StartupParameters.plist

<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE plist PUBLIC "-//Apple Computer//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd"> <plist version="1.0"> <dict> <key>Description</key> <string>Oracle 9.2.0 Database Server</string> <key>Provides</key> <array> <string>Oracle 9.2.0 Database</string> </array> <key>Requires</key> <array> <string>Disks</string> </array> <key>Uses</key> <array> <string>Disks</string> <string>Network</string> <string>NFS</string> </array> <key>OrderPreference</key> <string>Late</string> </dict> </plist>

Install Tasks

From this point forward during the install process, you want to be logged into the oracle UNIX account. Copy the oracle installation file macosx_920_dev_rel.tar.gz either from the OTN Web site or from a CD (if you created one) to the location where you are going to place your Oracle Home directory. In this example, this would be the top level of the /Volumes/u01 directory. Uncompress and untar the file.

oracle% cd /Users/oracle oracle% gunzip macosx_920_dev_rel.tar.gz oracle% tar xvf macosx_920_dev_rel.tar

The resulting orahome directory will contain the Oracle binary software. In a regular UNIX installation, this step would only have been reached after running the Oracle Universal Installer and waiting for all of the files to be copied and linked.

Rename the orahome directory with the name you want to use for your Oracle Home directory, which will be v920 in this example.

oracle% mv orahome v920

Create the /etc/oratab Oracle startup configuration file. This file provides configuration info for the dbstart and dbshut scripts to indicate which database instances should be started/stopped by these scripts. Having a "Y" in the last column permits the dbstart/dbshut scripts to start or stop the database. This can be useful if you have multiple database instances installed on the same server, but you only want some instances to be started manually by the DBA. The contents of this file are in the format $ORACLE_SID:$ORACLE_HOME:<N|Y>. For this installation example, the contents are as follows:

prod1:/Users/oracle/v920:Y

It is not mandatory for you to use the Oracle-supplied $ORACLE_HOME/bin/dbstart and dbshut scripts to start or stop your database. If you don't want to use this mechanism, you can customize the Oracle startup script to simply use SQL/Plus to start up or shut down the database directly. However, this is the method commonly used by Oracle DBAs, so I prefer to use it to make my database installs more familiar for any other DBA who needs to administer the server.

Use the following file listings to create the /Users/oracle/network/admin/tnsnames.ora, /Users/oracle/network/admin/sqlnet.ora, /Users/oracle/network/admin/listener.ora, and /Users/oracle/network/admin/initprod1.ora files. The tnsnames.ora file provides configuration for software to communicate with the database that is being installed. This file should be copied to client computers after installing the Oracle client software CD. Since this CD does not yet exist for Mac OS X, you would need to install the actual database binary files just as was done here on the server itself. This way, remotely-connected client applications will be able to use Oracle's Net8 software or JDBC drivers to connect to the database. Once the database has been installed and created, you can test connectivity to the server with the command tnsping PROD1.

The sqlnet.ora and listener.ora files are used to configure the listener program on the database server. The initprod1.ora file provides the initial configuration for the server when it starts up, unless the server finds an spfile in the same directory.

Listing #3 -- filename: $ORACLE_HOME/network/admin/tnsnames.ora

PROD1 =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = g4)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = prod1)

)

)

Listing #4 -- filename: $ORACLE_HOME/network/admin/sqlnet.ora

NAMES.DIRECTORY_PATH= (TNSNAMES, HOSTNAME)

Listing #5 -- filename: $ORACLE_HOME/network/admin/listener.ora

LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC)) ) (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = g4)(PORT = 1521)) ) (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = g4)(PORT = 2481)) (PROTOCOL_STACK = (PRESENTATION = GIOP) (SESSION = RAW) ) ) )

SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /Users/oracle/v920) (PROGRAM = extproc) ) (SID_DESC = (GLOBAL_DBNAME = PROD1) (ORACLE_HOME = /Users/oracle/v920) (SID_NAME = prod1) ) )

Listing #6 -- filename: $ORACLE_HOME/dbs/initprod1.ora

########################################### # Miscellaneous ########################################### compatible=9.2.0.0.0

########################################### # Network Registration ########################################### instance_name = prod1 db_name = prod1 service_names = prod1

########################################### # Archive ########################################### log_archive_dest_1='LOCATION=/Volumes/archive' log_archive_format=prod1_%t_%s.arc log_archive_start=true

########################################### # Cache and I/O ########################################### db_block_size=16384 # small system - db_block_size=4096 # medium system - db_block_size=8192 # large system - db_block_size=16384

db_cache_size=1638400 # small system - db_cache_size=10000000 # medium system - db_cache_size=50000000 # large system - db_cache_size=10000000000

########################################### # Cursors and Library Cache ###########################################

open_cursors=100 # small system - open_cursors=100 # medium system - open_cursors=300 # large system - open_cursors=600

########################################### # Diagnostics and Statistics ########################################### background_dump_dest=/Volumes/u01/bdump core_dump_dest =/Volumes/u01/cdump timed_statistics =FALSE user_dump_dest =/Volumes/u01/udump #max_dump_file_size = 10000 # limit trace file size to 5M each ########################################### # Distributed, Replication and Snapshot ###########################################

remote_login_passwordfile=EXCLUSIVE # Global Naming -- enforce that a dblink has same name as the db it connects to # global_names = false

# The following parameters are needed for the Advanced Replication Option # Job Queue Processes is also used when gathering Oracle 9i system statistics job_queue_processes = 2 #distributed_transactions = 500 #open_links = 16 #aq_tm_processes=1

########################################### # File Configuration ########################################### control_files=("/Volumes/u01/prod1/control01.ctl", "/Volumes/u02/prod1/control02.ctl", "/Volumes/u03/prod1/control03.ctl")

# UTL_FILE_DIR is used by various utilities # including log_miner to allow pl/sql to read/write # data to the hard drive UTL_FILE_DIR=/Volumes/backup/oracle_utl_file_dir ########################################### # MTS ########################################### # Uncomment the following line when your listener is configured for SSL # (listener.ora and sqlnet.ora) # dispatchers = "(PROTOCOL=TCPS)(SER=MODOSE)", "(PROTOCOL=TCPS)(PRE=oracle.aurora.server.GiopServer)", "(PROTOCOL=TCPS)(PRE=oracle.aurora.server.SGiopServer)" dispatchers="(PROTOCOL=TCP)(SER=MODOSE)", "(PROTOCOL=TCP)(PRE=oracle.aurora.server.GiopServer)", "(PROTOCOL=TCP)(PRE=oracle.aurora.server.SGiopServer)"

########################################### # Pools ###########################################

java_pool_size =150000000 # small system - java_pool_size=25000000 # medium system - java_pool_size=50000000 # large system - java_pool_size=100000000

large_pool_size =10000000 # small system - shared_pool_size= 10000000 # medium system - shared_pool_size=100000000 # large system - shared_pool_size=500000000

shared_pool_size=150000000 # medium system - shared_pool_size=25000000 # large system - shared_pool_size=60000000

dml_locks= 100 # small system - dml_locks= 100 # medium system - dml_locks= 500 # large system - dml_locks=2000

log_buffer= 3000 # small system - log_buffer=512000 # medium system - log_buffer=1024000 # large system - log_buffer=5120000

########################################### # PL/SQL Native Compilation Parameters ########################################### plsql_compiler_flags = 'INTERPRETED' #plsql_native_c_compiler = '/usr/local/bin/gcc' # this parameter is specified in makefile plsql_native_library_dir = '/Users/oracle/v920/plsql_compiled_code' plsql_native_library_subdir_count = 1000 #plsql_native_linker = '/usr/local/bin/ld' # this parameter is specified in makefile plsql_native_make_file_name = '/Users/oracle/v920/plsql/spnc_makefile.mk' plsql_native_make_utility = 'gmake' ########################################### # Processes and Sessions ########################################### processes=50 parallel_automatic_tuning = true # small system - processes=50 # medium system - processes=1000 # large system - processes=10000

########################################### # Redo Log and Recovery ########################################### fast_start_mttr_target=300

########################################### # Resource Manager ########################################### resource_manager_plan=SYSTEM_PLAN

########################################### # Sort, Hash Joins, Bitmap Indexes ########################################### sort_area_size=524288 # small system - sort_area_size= 50000 # medium system - sort_area_size=256000 # large system - sort_area_size=524288

########################################### # System Managed Undo and Rollback Segments ########################################### undo_management=AUTO undo_tablespace=undotbs

Create the Instance

The following (Listing #7) script will build the basic database instance using locally-managed tablespaces and with a database character set of AL32UTF8 and NLS character set of AL16UTF16.

These character set parameters make it possible for the database to accurately store unicode information within the database, in case this functionality is required in the future. I have also specified a block size of 16K; however, if you want to save a little disk space on a laptop configuration, then you might set this parameter to 4K or 8K.

Listing #7 -- filename: db_create.sql

connect / as SYSDBA -- remove any previously created spfile host rm /Users/oracle/v920/dbs/spfileprod1.ora create spfile='/Users/oracle/v920/dbs/spfileprod1.ora' FROM pfile='/Users/oracle/v920/dbs/initprod1.ora';

-- remove any previously created orapw file host rm /Users/oracle/v920/dbs/orapw -- create new orapw file host /Users/oracle/v920/bin/orapwd file=/Users/oracle/v920/dbs/orapw password=s9_21sv entries=20 spool /Users/oracle/CreateDB.log

set echo on spool /Users/oracle/v920/assistants/dbca/logs/CreateDB.log shutdown abort connect / as SYSDBA startup nomount pfile="/Users/oracle/v920/dbs/initprod1.ora"; CREATE DATABASE PROD1 MAXINSTANCES 1 MAXLOGHISTORY 1 MAXLOGFILES 50 MAXLOGMEMBERS 5 MAXDATAFILES 100 DATAFILE '/Volumes/u01/prod1/system01.dbf' SIZE 300M REUSE AUTOEXTEND ON NEXT 1024K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL UNDO TABLESPACE "undotbs" DATAFILE '/Volumes/u01/prod1/undo01.dbf' SIZE 500M DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/Volumes/u01/prod1/temp01.dbf' SIZE 55M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M CHARACTER SET AL32UTF8 NATIONAL CHARACTER SET AL16UTF16 LOGFILE GROUP 1 ('/Volumes/u01/prod1/redo01.log', '/Volumes/u03/prod1/redo01g1f2.log') SIZE 10M, GROUP 2 ('/Volumes/u02/prod1/redo02.log', '/Volumes/u01/prod1/redo02g2f2.log') SIZE 10M, GROUP 3 ('/Volumes/u03/prod1/redo03.log', '/Volumes/u02/prod1/redo03g3f2.log') SIZE 10M set time_zone = 'America/Los_Angeles';

spool off

spool /Users/oracle/CreateDBFiles.log CREATE TABLESPACE "TOOLS" LOGGING DATAFILE '/Volumes/u01/prod1/tools01.dbf' SIZE 10M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL; CREATE TABLESPACE "USERS" LOGGING DATAFILE '/Volumes/u02/prod1/users01.dbf' SIZE 10M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL; CREATE TABLESPACE "INDX" LOGGING DATAFILE '/Volumes/u02/prod1/indx01.dbf' SIZE 50M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL; CREATE TABLESPACE "XDB" LOGGING DATAFILE '/Volumes/u03/prod1/xdb01.dbf' SIZE 10M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL; CREATE TABLESPACE "EXAMPLE" LOGGING DATAFILE '/Volumes/u01/prod3/example01.dbf' SIZE 10M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL; spool off

spool /Users/oracle/CreateDBCatalog.log @/Users/oracle/v920/rdbms/admin/catalog.sql; @/Users/oracle/v920/rdbms/admin/catexp7.sql; @/Users/oracle/v920/rdbms/admin/catblock.sql; @/Users/oracle/v920/rdbms/admin/catproc.sql; @/Users/oracle/v920/rdbms/admin/catoctk.sql; conn system/manager @/Users/oracle/v920/sqlplus/admin/pupbld.sql; exit;

Use SQL/Plus (from an Oracle account terminal session) to execute this script to create the instance. If the db_create_sql file is not located within the directory where you invoked SQL/Plus, then you will need to provide the full path for SQL/Plus to use when executing the file.

oracle% sqlplus /nolog @/Users/oracle/db_create.sql

SQL/Plus will run for a couple of hours to create the instance. When it completes, you will be left back at the UNIX prompt. During the database build process you should see quite a few commands scrolling up on the screen. You want to look for situations in which you get "Not connected to Oracle" types of errors. This can be an indication that the database did not actually get created. It is a good idea to get in the habit of checking the /Users/oracle/bdump/alert_prod1.log file to see if some type of error has occurred that prevented the database from being created --for instance, if your computer didn't have enough memory to create the database instance, you would see that error listed in the alert_prod1.log file. Important issues such as missing/damaged database files will also be listed in the alert.log, as well.

You can also use the ps command to check which oracle account processes are currently running on the computer. Here is an example listing of the ps command that shows the various Oracle processes that are running, like pmon, lgwr, ckpt, smon, arc1, reco, etc.

su-2.05a$ ps -aux | grep oracle oracle 1015 0.0 0.1 66852 692 std S 3:27PM 0:00.08 -su (bash) oracle 1024 0.0 0.9 345236 6004 ?? Ss 3:50PM 0:02.40 ora_pmon_prod1 oracle 1026 0.0 0.8 344888 5380 ?? Ss 3:50PM 0:02.55 ora_dbw0_prod1 oracle 1028 0.0 0.8 344676 4972 ?? Ss 3:50PM 0:02.55 ora_lgwr_prod1 oracle 1030 0.0 0.8 344692 5320 ?? Ss 3:50PM 0:02.58 ora_ckpt_prod1 oracle 1032 0.0 0.8 344676 5000 ?? Ss 3:50PM 0:02.43 ora_smon_prod1 oracle 1034 0.0 0.8 344676 4964 ?? Ss 3:50PM 0:02.42 ora_reco_prod1 oracle 1036 0.0 0.8 344676 4980 ?? Ss 3:50PM 0:02.64 ora_cjq0_prod1 oracle 1038 0.0 0.8 345260 4976 ?? Ss 3:50PM 0:02.32 ora_s000_prod1 oracle 1040 0.0 0.8 345104 5284 ?? Ss 3:50PM 0:02.39 ora_d000_prod1 oracle 1042 0.0 0.8 345104 5292 ?? Ss 3:50PM 0:02.34 ora_d001_prod1 oracle 1044 0.0 0.8 345104 5300 ?? Ss 3:50PM 0:02.20 ora_d002_prod1 oracle 1046 0.0 0.8 348788 5064 ?? Ss 3:50PM 0:02.25 ora_arc0_prod1 oracle 1048 0.0 0.8 348788 5052 ?? Ss 3:50PM 0:02.13 ora_arc1_prod1 oracle 1051 0.0 0.0 66140 56 std R+ 3:51PM 0:00.01 grep oracle

Post-Install Tasks

Now that the database has been built, you should start the listener so that remote connections can be made to the database.

oracle% lsnrctl start

You should now be able to ping the database with tnsping:

oracle% tnsping prod1

If tnsping gives an error, then check to make sure that the tnsnames.ora file exists and that the connection string "PROD1" exists within the file. If you try to use tnsping with a connection string that does not exist, then you will get the following error:

tnsping prod123 TNS-12545: Connect failed because target host or object does not exist

If the listener has not been started, then you will get the following error:
TNS-12541: TNS:no listener

Log In Locally

You can log directly into the database from the server where Oracle has been installed by using the SQL/Plus. You will need to do this from the oracle UNIX account.

oracle% sqlplus /nolog conn / as sysdba

After being logged in as sysdba, you can start up, shut down, create database user accounts, and perform any other required DBA tasks. Here is how you would create another user named user1 (with password "welcome") with non-DBA privileges in the database:

create user user1 identified by welcome default tablespace users temporary tablespace temp quota unlimited on users; grant connect to user1; grant resource to user1;

Log In Remotely

You can use SQL/Plus to make a remote connection from another computer by using the format:

oracle% sqlplus /nolog conn user1/welcome sysdba@<host>:<port>:<sid>

which would look like this:

conn user1/welcome sysdba@G4:1521:prod1

The above listed connection method should work even if you have problems with the tnsnames.ora file on your remote computer. If the tnsnames.ora file is set up correctly on your remote computer, then you can simply use the connection string within tnsnames.ora:

conn user1/welcome sysdba@prod1

When configuring a production database, there are many other post-install tasks which should be completed. These tasks include configuring archivelog mode, writing/installing/scheduling/testing nightly RMAN backups, regularly scheduling full or partial database exports, writing/installing/scheduling performance tuning scripts, and documenting the database and its recovery procedures.

Please feel free to look through the rest of the example scripts on my Web site; these are used for building Oracle 9.2 databases on Mac OS X. I intend to update these scripts as Oracle provides updated beta versions and the final production software for Mac OS X (currently expected in Q2 2003).

There is also a top-level Web page that you may find helpful if you want to compare the Mac OS X database creation scripts to the scripts that are used to create Oracle 9.2 databases on Solaris or Windows.

Conclusion

I have covered a lot of ground in this series of articles. Everything from what the release of Oracle 9i on Mac OS X means to Oracle, Apple, Mac OS X users, and UNIX DBAs to the actual process of installing Oracle 9.2 on Mac OS X.

We have succeeded in creating an Oracle 9.2.0 database on Mac OS X in a manner that eliminates a number of the limitations imposed by Oracle's official instructions. This installation procedure:

Allows the DBA to install the Oracle database software and datafiles anywhere on the server, attached hard drives, or disk array.
Uses a DBA-defined name for the Oracle Home directory -- instead of the default orahome directory.
Makes use of the standard Oracle Optimal Flexible Architecture (OFA) layout for database files -- instead of using the default $ORACLE_HOME/dbs directory.
Configures the database and listener for automatic startup during the system booting process -- instead of requiring the DBA to perform these tasks manually. (Shutting down the database must still be done manually, due to a Mac OS X 10.2.2 limitation.)
Eliminates the need for the DBA to manually run the Oracle set_ulimit and DBENV scripts before using SQL/Plus or other database utilities.

I look forward to Oracle's release of additional beta versions and finally, the production release of Oracle 9i on Mac OS X.

David Simpson is president of .com Solutions Inc. and the developer of the Installgen application.

Return to the Mac DevCenter.