Oracle Post-Configuration

Overview

Once Oracle is installed and the data migrated, a set of configuration settings must be applied before the database is made operational. This document describes, loosely, the steps to take in making configuration changes.

Patches

One critical patch must be applied in order to fix a security hole in the Oracle database server and listener, plus a minor security hole in the Oracle Enterprise Manager. Download patch 3811942.

Once downloaded, perform the following steps:

• Unzip the file p3811942.zip.
• Change to the 3811942 directory.
• Apply the patch by executing: $ORACLE_HOME/OPatch/opatch

Next, run some post-installation scripts. Some of the scripts require that the account wksys be available by unlocking the account. Execute the command with SQL*Plus:

ALTER USER "WKSYS" ACCOUNT UNLOCK;

You may also want to set a known password on the WKSYS account.

Next, Perform the following commands:

sqlplus wksys/<passwd> @$ORACLE_HOME/ultrasearch/admin/wk0pkh.sql
sqlplus wksys/<passwd> @$ORACLE_HOME/ultrasearch/admin/wk0plb.sql

Now set the current directory to $ORACLE_HOME/md/admin and run some more SQL*Plus commands:

alter session set current_schema=MDSYS;
@@mdprivs.sql
@@prvtgmd.plb
@@sdolrsmd.sql
drop table sdo_cmt_cbk_fn_table;
drop table sdo_cmt_cbk_dml_table;
@@prvtccbk.plb

Now set the current directory to $ORACLE_HOME/rdbms/admin and run some more SQL*Plus commands:

@dbmspexp.sql
@prvtpexp.plb

Now set the current directory to $ORACLE_HOME/ctx/admin and run some more SQL*Plus commands:

alter session set current_schema=CTXSYS;
@driload.pkh

Once done, clean up the unlocked account by executing the command with SQL*Plus:

ALTER USER "WKSYS" ACCOUNT LOCK;

Stop and restart the Oracle database.

Patches (Part 2)

A new critcal patch is available from Oracle, superceding the one above. Should you install a brand new database, only this patch needs applying. If you've already patched the one above, then remove that patch first, then apply this patch. The Oracle patch number is 4003051.

Download the patch, then perform the following steps:

• Stop the Oracle database (including the listener, Enterprise Manager and iSQL*Plus).
• Unzip the file p4003051.zip.
• Change to the 4003051 directory.
• Apply the patch by executing: $ORACLE_HOME/OPatch/opatch
• Restart the Oracle database (just the database)

Next, run the following script to make post-configuration changes:

set -xe

cd $ORACLE_HOME/rdbms/admin
sqlplus "/ as sysdba" <<'XXX'
@dbmstxfm.sql
@prvtpexp.plb
@prvttxfm.plb
@prvtcdpu.plb
@prvtcdpi.plb
@prvtxdb.plb
@prvtxdbz.plb
XXX

cd $ORACLE_HOME/rdbms/admin
sqlplus "/ as sysdba" <<'XXX'
alter session set current_schema=EXFSYS;
@exfeapvs.plb
XXX

cd $ORACLE_HOME/md/admin
sqlplus "/ as sysdba" <<'XXX'
alter session set current_schema=MDSYS;
@sdopatch.sql
XXX

cd $ORACLE_HOME/dm/admin
sqlplus "/ as sysdba" <<'XXX'
alter session set current_schema=DMSYS;
@dmpsysh.plb
@dmpsysb.plb
@prvtdm.plb
XXX

cd $ORACLE_HOME/olap/admin
sqlplus "/ as sysdba" <<'XXX'
alter session set current_schema=OLAPSYS;
@cwm2utl2.plb
XXX

cd $ORACLE_HOME/ctx/admin
sqlplus "/ as sysdba" <<'XXX'
alter session set current_schema=CTXSYS;
@driload.pkh
XXX

sqlplus "/ as sysdba" <<'XXX'
ALTER USER "WKSYS" ACCOUNT UNLOCK;
XXX

cd $ORACLE_HOME/ultrasearch/admin
sqlplus "/ as sysdba" <<'XXX'
alter session set current_schema=WKSYS;
@wk0pkh.sql
@wk0plb.sql
XXX

sqlplus "/ as sysdba" <<'XXX'
ALTER USER "WKSYS" ACCOUNT LOCK;
XXX

cd $ORACLE_HOME/rdbms/admin
sqlplus "/ as sysdba" <<'XXX'
@utlprp.sql 1
XXX

At this point, the patch is installed and ready to use. Stop the database process, then restart, this time starting the listener, Enterprise Manager and iSQL*Plus.

Parameter Settings

Now set some settings on the server process to handle a larger environment that is initially available. Run the following commands in SQL*Plus:

ALTER SYSTEM SET open_cursors = 1000 SCOPE=BOTH
ALTER SYSTEM SET processes = 500 SCOPE=SPFILE
ALTER SYSTEM SET sessions = 555 SCOPE=SPFILE
ALTER SYSTEM SET db_recovery_file_dest_size = 10737418240 SCOPE=BOTH

Stop and restart the Oracle database.

Host Script Execution

In order for the Enterprise Manager to execute scripts on behalf of the web server interface, the mode on the executable nmo needs updating. Execute the following commands as root:

chown root:root $ORACLE_HOME/bin/nmo
chmod 4755 $ORACLE_HOME/bin/nmo

Enterprise Manager

Now, start the Enterprise Manager and make some setting changes. To start the Enterprise Manager, I suggest the following: Create an ssh tunnel to Levee with X11 port forwarding enabled, then start an instance of Mozilla to the local web server. Do the following commands:

• Log on to Oracle by executing: ssh oracle@levee -X
• Start Mozilla with: mozilla http://localhost:5500/em

This makes it easier by using the same web browser for all Enterprise Manager work, plus it encrypts the traffic, since Oracle's Enterprise Manager doesn't have SSL capability on its own.

Now perform the following setting changes.

Setup MetaLink

First, let's setup and install MetaLink settings. MetaLink settings help the Enterprise Manager determine if patches or updates are needed for the database server.

Log on to the Enterprise Manager as sys as sysdba.

On the initial screen near the bottom right, is a section for Critical Patch Advisories. Click on the link for Job: RefreshFromMetalink and follow the directions. You'll need a login and password for the MetaLink site. I'm using my login and password that was approved for use by the Purdue DBAs.

Be sure to check that MetaLink patch checks are running occasionally and check the Enterprise Manager for any patches that it deems in need of applying. Unfortunately, the patch for the one listed above (3811942) never goes away from the advisory list.

Schedule and Run Full Backup

Once the database is running, patched and configured, it needs to have a full backup run so an initial starting point can be made for future backups. The Oracle Enterprise Manager can do a full backup, plus schedule daily backups from that point forward.

• Log on to the Enterprise Manager as sys as sysdba.
• Click on Maintenance.
• Click on Schedule Backup.
• Change the Backup Strategy to Customized.
• Keep the default of Whole Database.
• Enter the oracle login and password. Click on Next.
• Select Full Backup and Online Backup.
• Check Back up all archived logs on disk.
• Check Delete all archived logs from disk after they are successfully backed up.
• Check Delete obsolete backups.
• Click on Next.
• Keep the default of Disk. Click on Next.
• Keep the defaults and click on Next.
• Click on Submit Job.

Now monitor the progress of the backup. Once the backup completes, it is time to run through this set of steps again, but this time, select on the third step to schedule a repeating backup:

• Click on Later.
• Change the date to tomorrow at 2:00am.
• Click on Interval, and set the frequency to 1 Days.
• Continue through to the job schedule as before.

Since Oracle runs this pretty much silently every night, it would be a good idea to check that the Enterprise Manager thinks that the backups are going okay, along with checking the filesystem at $HOME/flash_recovery_area/ECN2 to see if the backup set files are really there, have a reasonable size and up to date.