9.2. Setup and Scripts
Many customers have experience with the Oracle GUI Database Creation Assistant; however, walking through a simple creation example will shed some light on how a basic database is created, why certain sizes are when the defaults are over-ridden. Although Oracle is a very complex product, this example tries to be simple, yet useful. There is no reason you cannot use any other tool to create and manage the database once you have read (or skipped) this section.
Define these variables for the oracle user on every node. Only the $ORACLE_INSTANCE variable needs to be changed for a given node.
export ORACLE_BASE=/mnt/ohome/oracle/1010
export ORACLE_HOME=/mnt/ohome/oracle/1010/product/db
export ORACLE_BASE_SID=rhel
export ORACLE_INSTANCE=1
export ORACLE_SID=$ORACLE_BASE_SID$ORACLE_INSTANCE
export ADMHOME=/mnt/ohome/oracle/admin
export PATH=$ORACLE_HOME/bin:$CRS_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
The $ADMHOME is a variable that is defined in addition to the standard list above and it refers to the location of all of the critical administration files for the RAC cluster. This admin directory must be on the shared home GFS volume. Some aliases you can’t live without are listed below that help you navigate around both $ADMHOME and $ORACLE_HOME, including a simple alias named dba that starts up sqlplus and connects as sysdba with the minimum of typing:
alias dba="sqlplus '/ as sysdba'" alias pfile='cd $ADMHOME/$ORACLE_BASE_SID/pfile' alias create='cd $ADMHOME/$ORACLE_BASE_SID/create' alias trc='cd $ADMHOME/$ORACLE_BASE_SID/bdump/$ORACLE_SID' alias utrc='cd $ADMHOME/$ORACLE_BASE_SID/udump' alias hm='cd $ORACLE_HOME' alias lib='cd $ORACLE_HOME/lib' alias bin='cd $ORACLE_HOME/bin' alias dbs='cd $ORACLE_HOME/dbs' alias rlib='cd $ORACLE_HOME/rdbms/lib' alias adm='cd $ORACLE_HOME/rdbms/admin' alias net='cd $ORACLE_HOME/network/admin' alias lsn='lsnrctl'
The init.ora file is Oracle’s equivalent to the /etc/sysctl.conf file and must contain a minimum of settings.
Do not recycle an init.ora from some previous released version of Oracle. Oracle releases change enough so that old init.ora settings become irrelevant or even counter-productive.
Each instance can have its own private init.ora, but it is not required and creates more problems than it solves. A single init.ora can be customized to contain instance-specific parameters.
Some DBAs will require the use of the SPFILE feature in Oracle, which stores a copy of the init.ora inside the database. This is often a production policy preference, but this example uses text mode, despite the risk that somebody could delete or corrupt it. Both the init.ora and controlfile should be regularly archived or backed up with something as simple as a cronjob.
The actual init.ora must be kept in a common location on the shared home admin directory for parameter files ($ADMHOME/$ORACLE_SID/pfile). All instances have must access to it and other admin related files. The Oracle SQL command startup command assumes that the instance’s init.ora file is located in $ORACLE_HOME/dbs. Four symbolic links must be created. This example shows how the environment variables factor in locating key files:
rac1 $ ln -s $ADMHOME/$ORACLE_BASE_SID/pfile/initrhel.ora initrhel1.ora
rac1 $ ln -s $ADMHOME/$ORACLE_BASE_SID/pfile/initrhel.ora initrhel2.ora
rac1 $ ln -s $ADMHOME/$ORACLE_BASE_SID/pfile/initrhel.ora initrhel3.ora
rac1 $ ln -s $ADMHOME/$ORACLE_BASE_SID/pfile/initrhel.ora initrhel4.ora
rac1 $ ls –l init*.ora
initrhel1.ora -> /mnt/ohome/oracle/admin/rhel/pfile/initrhel.ora initrhel2.ora -> /mnt/ohome/oracle/admin/rhel/pfile/initrhel.ora initrhel3.ora -> /mnt/ohome/oracle/admin/rhel/pfile/initrhel.ora initrhel4.ora -> /mnt/ohome/oracle/admin/rhel/pfile/initrhel.ora
This is the sample init.ora that will be used by all four instances to run this RAC cluster.
control_files='/mnt/ohome/oracle/admin/rhel/ctl/control01.ctl', '/mnt/oradata/oracle/ctl/control02.ctl' *.db_name = 'rhel' *.db_block_size = 8192 # # SGA Sizing *.sga_target = 3300M # # File I/O filesystemio_options = setall # # Network and Listeners rhel1.local_listener = listener_rac1 rhel2.local_listener = listener_rac2 rhel3.local_listener = listener_rac3 rhel4.local_listener = listener_rac4 # # Undo *.undo_management = 'AUTO' rhel1.undo_tablespace = 'UNDOTBS1' rhel2.undo_tablespace = 'UNDOTBS2' rhel3.undo_tablespace = 'UNDOTBS3' rhel4.undo_tablespace = 'UNDOTBS4' # # Foreground and Background Dump Destinations rhel1.background_dump_dest ='/mnt/ohome/oracle/admin/rhel/bdump/rhel1' rhel2.background_dump_dest ='/mnt/ohome/oracle/admin/rhel/bdump/rhel2' rhel3.background_dump_dest ='/mnt/ohome/oracle/admin/rhel/bdump/rhel3' rhel4.background_dump_dest ='/mnt/ohome/oracle/admin/rhel/bdump/rhel4' *.core_dump_dest ='/mnt/ohome/oracle/admin/rhel/cdump' *.user_dump_dest ='/mnt/ohome/oracle/admin/rhel/udump' # # RAC Identification *.cluster_database_instances= 4 *.cluster_database = FALSE # FALSE ONLY for database create phase rhel1.thread = 1 rhel2.thread = 2 rhel3.thread = 3 rhel4.thread = 4 rhel1.instance_name = rhel1 rhel2.instance_name = rhel2 rhel3.instance_name = rhel3 rhel4.instance_name = rhel4 rhel1.instance_number = 1 rhel2.instance_number = 2 rhel3.instance_number = 3 rhel4.instance_number = 4
When defaults are over-ridden, it is usually because they are mandatory parameters that need to be set and are specific to this database or to these instances. A simple example of this is the db_name parameter; if it is not defined, no instances will start up.
Some parameters are optional, but are considered best practice.
In a few cases, some parameters are “optionally mandatory”. You do not have to set an optionally mandatory parameter, but if you do not, your system will not work effecitively. An example of such parameters is setting the size of the SGA buffers and pools.
You can never have enough backup controlfiles—if you lose all copies, your database is effectively GONE. A single point of failure in an Oracle RAC database can be the loss of all controlfiles.
If the controlfiles are in the same directory as the datafiles, then there can be a slight performance impact on create or extend operations. This is easily avoided by just putting the controlfiles in a sub-directory underneath the datafiles. All controlfiles need to be on shared media for cluster recovery. Create a cron job to email the contents to a safe place.
Needs to be the same as what you used in the CREATE DATABASE statement in the create database script described below.
On modern systems, set this value to at least 8K and for 64-bit systems where you plan to have more than 8GB of SGA, set it to 16K, especially for new 64-bit deployments. Most 64-bit ports of Oracle support up to 32KB block sizes, which is a common block size for data warehouses, but is now becoming common in large memory 64-bit deployments.
This parameter does a reasonable job of replacing db_cache_size, shared_pool_size, and large_pool_size. Setting them to appropriate values is essential to basic functionality of the instance. If you want to set these parameters by hand, db_cache_size and shared_pool_size are the two most important. Db_cache_size controls the size of DB block buffer cache (and this is typically 80% of the SGA). The Shared_pool_size parameter controls the size of the shared pool which mostly contains parsed SQL queries (cursors) and their execution plans.
The *. at the beginning of any parameter indicates that this value applies to all instances that use this init.ora. If rac4 could accommodate a buffer cache of 5GB, then a rac4-specific entry might look like:
rac4.sga_target = 5072M
*.filesystemio_options=setall (for AsyncIO and DirectIO)
*.filesystemio_options=directIO
*.filesystemio_options=asyncIO
This parameter enables either DirectIO or AsyncIO. DirectIO bypasses the GFS filesystem buffer cache. This prevents memory from being buffered in memory by two buffer caches. DirectIO provides near-raw performance. AsyncIO increases I/O performance further still, but its benefit is usually only seen at very high I/O rates. Both setall are recommended for RHEL4.3 and higher.
Each instance needs to have a SQL*Net listener (tnslsnr) defined. The execution of the utility netca defines all the corresponding values in the listener.ora file. In order for applications to connect to this cluster, a tnsname.ora alias should be created that specifies a list of listener end-points. load_balance=on optional parameter will randomly assign new connections to any of the listed addresses. It is not very dynamic and, if the cluster is a symmetrical cluster architecture, this might not be the appropriate strategy—so plan accordingly. failover=on is the default for address lists and only applies at connect time. More sophisticated connection management requires the use of a new Oracle 10gR2 feature called FAN (Fast Application Notification).
rhel =
(DESCRIPTION=
(ADDRESS_LIST=
(CONNECT_DATA=(SERVICE_NAME=rhel))
(LOAD_BALANCE=OFF)
(ADDRESS = (PROTOCOL = tcp)(HOST = rac1)(PORT = 1921))
(ADDRESS = (PROTOCOL = tcp)(HOST = rac2)(PORT = 1921))
(ADDRESS = (PROTOCOL = tcp)(HOST = rac3)(PORT = 1921))
(ADDRESS = (PROTOCOL = tcp)(HOST = rac4)(PORT = 1921))
)
)
If you forget this step, then you should see these error messages when attempting to start an instance:
ORA-00119: invalid specification for system parameter LOCAL_LISTENER ORA-00132: syntax error or unresolved network name 'listener_rac1'
This is where all the trace files and alert logs can be found. The background dump files are in the $ADMHOME/bdump sub-directory, but in this example are further separated by instance, so that locating instance-specific trace files is easier. The core file dump and user trace file locations are common across all four nodes, but could also be separated into instance-specific subdirectories. Make sure these directories are created before you attempt to startup an instance or you might see these errors:
ORA-00444: background process "LMD0" failed while starting ORA-07446: sdnfy: bad value '' for parameter.
Specifies the maximum number of instances that can mount this database. This value cannot exceed the MAXINSTANCES parameter that was specified on the SQL CREATE DATABASE statement.
During database creation, the parameter *.cluster_database needs to be set to TRUE. Once the creation steps are complete, the first node needs to be shutdown and this parameter must be changed to TRUE to enable multi-node operation before any of the instances can be started.
Most of this is basic housekeeping that identifies instance-specific parameters that must be set for each node to startup and be identified as unique.
The Alert log is the Oracle equivalent of /var/log/messages and is located in the background dump destination. If any problems occur that are not written to the console, they will appear in this file; it is first place to look whenever there seems to be something wrong with the normal operation of the database or any of the instances. Each instance has its own alert log and they are found in the instance-specific bdump directories as specified in the init.ora (for example, rhel1.background_dump_dest). Its name will be derived from the $ORACLE_SID, such as:
$ADMHOME/$ORACLE_BASE/bdump/$ORACLE_SID/alert_<$ORACLE_SID>.log for example, /mnt/ohome/oracle/admin/rhel/bdump/rhel1/alert_rhel1.log
If you start up the instance on rac1 with the nomount option, it will create an SGA and start up the background sessions. This will verify that it is possible to bring up an instance. Here is a simple script, which can be modified to bring the database down easily (shutdown immediate). The following script assumes the default location for the init.ora ($ORACLE_HOME/dbs):
#!/bin/sh sqlplus << EOF connect / as sysdba startup nomount exit EOF
rac1 $ nomnt
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Mar 29 12:42:28 2006 Copyright (c) 1982, 2005, Oracle. All rights reserved. Enter user-name: Enter password: Connected to an idle instance. SQL> ORACLE instance started. Total System Global Area 3221225472 bytes Fixed Size 2024240 bytes Variable Size 3154119888 bytes Database Buffers 50331648 bytes Redo Buffers 14749696 bytes ORA-00205: error in identifying control file, check alert log for more info
Because no database has been created yet, there is no valid controlfile and this is a normal error at this early stage. However, you have verified that an Oracle instance on rac1 can start up. The next step is to actually create the database using the following script:
spool db_create
STARTUP NOMOUNT
CREATE DATABASE rhel CONTROLFILE REUSE
LOGFILE
GROUP 1 ('/mnt/log1/oracle/logs/redo11.log') SIZE 512M reuse,
GROUP 2 ('/mnt/log1/oracle/logs/redo12.log') SIZE 512M reuse,
GROUP 3 ('/mnt/log1/oracle/logs/redo13.log') SIZE 512M reuse
CHARACTER SET UTF8
NATIONAL CHARACTER SET UTF8
NOARCHIVELOG
MAXINSTANCES 4
MAXLOGFILES 128
MAXLOGMEMBERS 3
MAXLOGHISTORY 10240
MAXDATAFILES 256
DATAFILE '/mnt/oradata/oracle/sys.dbf'
SIZE 256M REUSE EXTENT MANAGEMENT LOCAL
SYSAUX
DATAFILE '/mnt/oradata/oracle/sysaux.dbf'
SIZE 256M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
UNDO TABLESPACE undotbs1
DATAFILE '/mnt/log1/oracle/undo1.dbf'
SIZE 64M REUSE AUTOEXTEND ON NEXT 64M MAXSIZE 2048M
DEFAULT TEMPORARY TABLESPACE temp
TEMPFILE '/mnt/oradata/oracle/temp.dbf'
SIZE 256M REUSE AUTOEXTEND ON NEXT 1024M MAXSIZE UNLIMITED;
rem Make sure that the basic create works and then either re-run
rem the whole thing or paste the rest of it into a sqlplus session
rem
exit;
CREATE UNDO TABLESPACE undotbs2
DATAFILE '/mnt/log2/oracle/undo2.dbf'
SIZE 64M REUSE AUTOEXTEND ON NEXT 64M MAXSIZE 2048M;
CREATE UNDO TABLESPACE undotbs3
DATAFILE '/mnt/log3/oracle/undo3.dbf'
SIZE 64M REUSE AUTOEXTEND ON NEXT 64M MAXSIZE 2048M;
CREATE UNDO TABLESPACE undotbs4
DATAFILE '/mnt/log4/oracle/undo4.dbf'
SIZE 64M REUSE AUTOEXTEND ON NEXT 64M MAXSIZE 2048M;
ALTER DATABASE ADD LOGFILE THREAD 2
GROUP 4 ( '/mnt/log2/oracle/logs/redo21.log' ) SIZE 512M reuse,
GROUP 5 ( '/mnt/log2/oracle/logs/redo22.log' ) SIZE 512M reuse,
GROUP 6 ( '/mnt/log2/oracle/logs/redo23.log' ) SIZE 512M reuse;
ALTER DATABASE ENABLE PUBLIC THREAD 2;
ALTER DATABASE ADD LOGFILE THREAD 3
GROUP 7 ( '/mnt/log3/oracle/logs/redo31.log' ) SIZE 512M reuse,
GROUP 8 ( '/mnt/log3/oracle/logs/redo32.log' ) SIZE 512M reuse,
GROUP 9 ( '/mnt/log3/oracle/logs/redo33.log' ) SIZE 512M reuse;
ALTER DATABASE ENABLE PUBLIC THREAD 3;
ALTER DATABASE ADD LOGFILE THREAD 4
GROUP 10 ( '/mnt/log4/oracle/logs/redo41.log' ) SIZE 512M reuse,
GROUP 11 ( '/mnt/log4/oracle/logs/redo42.log' ) SIZE 512M reuse,
GROUP 12 ( '/mnt/log4/oracle/logs/redo43.log' ) SIZE 512M reuse;
ALTER DATABASE ENABLE PUBLIC THREAD 4;
Run this script and remember to first create all sub-directories for the logs and the controlfiles, as Oracle will not create these sub-directories. This simple script creates the database by calling the create.sql script:
#!/bin/bash set echo on # @? In sqlplus translates to @$ORACLE_HOME time sqlplus /nolog << EOF > bld.lst connect / as sysdba shutdown abort @create @?/rdbms/admin/catalog EOF
The catalog.sql is a master script that defines the Oracle data dictionary. If these steps are successful, then you have a single node RAC database running in Exclusive mode. Shut down the database, change *.cluster_database = TRUE and then start up rac1 again. (Remember, this is still all on rac1). If the listener is running on this node, then the network status of this node can be verified using the command:
rac1 $ lsnrctl status listener_rac1
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 20-APR-2006 23:15:06 Copyright (c) 1991, 2005, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1-vip)(PORT=1921)(IP=FIRST))) STATUS of the LISTENER ------------------------ Alias LISTENER_RAC1 Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production Start Date 20-APR-2006 23:12:12 Uptime 0 days 0 hr. 2 min. 54 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /mnt/ohome/oracle/1010/product/db/network/admin/listener.ora Listener Log File /mnt/ohome/oracle/1010/product/db/network/log/listener_rac1.log Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.20)(PORT=1921))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.150)(PORT=1921))) Services Summary... Service "PLSExtProc" has 1 instance(s). Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service... Service "rhel" has 1 instance(s). Instance "rhel1", status READY, has 3 handler(s) for this service... Service "rhel_XPT" has 1 instance(s). Instance "rhel1", status READY, has 3 handler(s) for this service... The command completed successfully
Oracle Clusterware supports the capability of registering both the database and each instance so that it will automatically start up an instance when it detects that it is not running. The utility srvctl is used to register the instances for auto-start:
rac1 $ srvctl add database -d rhel -o $ORACLE_HOME rac1 $ srvctl add instance -d rhel -i rhel1 -n rac1 rac1 $ srvctl add instance -d rhel -i rhel2 -n rac2 rac1 $ srvctl add instance -d rhel -i rhel3 -n rac3 rac1 $ srvctl add instance -d rhel -i rhel4 -n rac4
It also permits the use of srvctl to manually start and stop the instances from one node:
rac1 $ srvctl start instance -d rhel -i rhel1 rac1 $ srvctl start instance -d rhel -i rhel2 rac1 $ srvctl start instance -d rhel -i rhel3 rac1 $ srvctl start instance -d rhel -i rhel4
This command may be run from any node and it can retrieve the status of any node:
rac1 $ srvctl status nodeapps -n rac3
VIP is running on node: rac3 GSD is running on node: rac3 Listener is running on node: rac3 ONS daemon is running on node: rac3
To get a consolidated cluster-wide status, the $CRS_HOME/bin/crs_stat –t utility can be used. This example shows all services and instances registered and online. The instances are listed at the end and their online status indicates that the database instance is registered with Oracle Clusterware and is running.
rac1 $ crs_stat –t
------------------------------------------------------------ ora....C1.lsnr application ONLINE ONLINE rac1 ora.rac1.gsd application ONLINE ONLINE rac1 ora.rac1.ons application ONLINE ONLINE rac1 ora.rac1.vip application ONLINE ONLINE rac1 ora....C2.lsnr application ONLINE ONLINE rac2 ora.rac2.gsd application ONLINE ONLINE rac2 ora.rac2.ons application ONLINE ONLINE rac2 ora.rac2.vip application ONLINE ONLINE rac2 ora....C3.lsnr application ONLINE ONLINE rac3 ora.rac3.gsd application ONLINE ONLINE rac3 ora.rac3.ons application ONLINE ONLINE rac3 ora.rac3.vip application ONLINE ONLINE rac3 ora....C4.lsnr application ONLINE ONLINE rac4 ora.rac4.gsd application ONLINE ONLINE rac4 ora.rac4.ons application ONLINE ONLINE rac4 ora.rac4.vip application ONLINE ONLINE rac4 ora.rhel.db application ONLINE ONLINE rac2 ora....l1.inst application ONLINE ONLINE rac1 ora....l2.inst application ONLINE ONLINE rac2 ora....l3.inst application ONLINE ONLINE rac3 ora....l4.inst application ONLINE ONLINE rac4
A corresponding network status inquiry from node1 shows the presence of services for all four nodes:
rac1 $ lsnrctl status listener_rac1
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 15-APR-2006 02:28:02 Copyright (c) 1991, 2005, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1-vip)(PORT=1921)(IP=FIRST))) STATUS of the LISTENER ------------------------ Alias LISTENER_RAC1 Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production Start Date 14-APR-2006 17:08:13 Uptime 0 days 9 hr. 19 min. 49 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /mnt/ohome/oracle/1010/product/db/network/admin/listener.ora Listener Log File /mnt/ohome/oracle/1010/product/db/network/log/listener_rac1.log Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.20)(PORT=1921))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.150)(PORT=1921))) Services Summary... Service "PLSExtProc" has 1 instance(s). Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service... Service "rhel" has 4 instance(s). Instance "rhel1", status READY, has 3 handler(s) for this service... Instance "rhel2", status READY, has 2 handler(s) for this service... Instance "rhel3", status READY, has 2 handler(s) for this service... Instance "rhel4", status READY, has 2 handler(s) for this service... Service "rhel_XPT" has 4 instance(s). Instance "rhel1", status READY, has 3 handler(s) for this service... Instance "rhel2", status READY, has 2 handler(s) for this service... Instance "rhel3", status READY, has 2 handler(s) for this service... Instance "rhel4", status READY, has 2 handler(s) for this service... The command completed successfully