Kill Two Birds with One Stone - Create Physical Standby Database in Oracle10g with Data Guard

Oracle Data Guard, included with Oracle Database Enterprise Edition, is really a cool product. Implement Oracle Data Guard and you will most likely kill two birds with one stone. Oracle Data Guard will help you achieve high availability and disaster recovery. One tool and two benefits. With Oracle Data Guard, you have a standby database that is in synch with the primary database. God forbids and you lose your primary database, you can very quickly switch to the standby database with minimal or no data loss. Your standby database is just a command away from being a primary database.

Most importantly, Oracle manages the Data Guard environment. You don't need to write any custom scripts. You don't need to setup any backup jobs. You don't need to write a job to copy archive logs. You don't need to manage archive logs and RMAN hot backups. Implementation is quite simple, and it works very well.

Based on your Recovery Point Objective(RPO) and Recovery Time Objective(RTO), you can configure Oracle Data Guard in three different modes:

• Maximum Performance
• Maximum Availability
• Maximum Protection

Oracle Data Guard operates on a simple principle - ship redo stream to the standby database and apply it to standby database to keep it in synch with the primary database. A simple but very effective solution. If you have licensed Oracle Enterprise Edition and haven't implemented Data Guard, then you should seriously consider implementing one.

In this blog, I will illustrate the steps that you need to perform to implement physical standby database in maximum performance mode.

Environment

My data guard test environment is as follows:

• Primary Database Server: HRPRDSVR
• Oracle Version: 10gR2 10.2.0.4
• Operating System: Windows
• Standby Type: Physical
• Data Guard Mode: Maximum Performance
• Redo Apply: Real-time
• Archive Log Mode: Enabled on primary
• Primary DB Unique Name: HRPRD
• Primary DB TNS Service Name: HRPRD
• Data and Log Files Location: E:\Oradata\HRPRD
• Standby Database Server: HRSTBYSVR
• Standby DB Unique Name: HRPRDS
• Standby DB TNS Service Name: HRPRDS
• Data and Log Files Location: E:\Oradata\HRPRDS
 

Note that Primary and Standby Database servers have identical disk configuration.

Primary Database Configuration

Step 1 - Update init.ora parameters on primary database

There are only few init.ora parameters that govern the behavior and implementation of Data Guard based physical standby databases. In order to make it easy for you to understand data guard related parameters, I have created a table that lists data guard related parameters and their corresponding values. This approach would help you understand these parameters and allow you to compare the values between primary and standby databases. I have also added my comments to further explain or clarify these parameters.

Please use this table as a guide while setting up init.ora parameters in your environment. The parameter values listed below are representative values, and should be applicable to most implementations. Update values as appropriate to reflect environment specific characteristics.

Parameter Name	Primary DB Parameters	Physical Standby DB Maximum Performance Mode with real-time redo apply	Comments
db_name	HRPRD	HRPRD	Db_name must be the same for primary and physical standby
instance_name	HRPRD	HRPRD
db_unique_name	HRPRD	HRPRDS	Db_unique_name must be different for primary and physical standby
service_names	HRPRD	HRPRDS
remote_login_passwordfile	exclusive	exclusive
log_archive_config	'dg_config= (HRPRD,HRPRDS)'	'dg_config= (HRPRD,HRPRDS)'
log_archive_format	HRPRD%r%s.%t	HRPRD%r%s.%t
log_archive_trace	1	1
log_archive_dest_1	"location= f:\orarch\HRPRD "	"location= f:\orarch\HRPRD"	Replace with your Local Archive Destination
log_archive_dest_2	'service= HRPRDS async lgwr db_unique_name= HRPRDS valid_for= (primary_role, online_logfile)'	'service= HRPRD async lgwr db_unique_name= HRPRD valid_for= (primary_role, online_logfile)'	For maximum performance mode, make sure that you specify async value as part of this parameter. You also need to use lgwr value to enable real-time redo apply.
log_archive_dest_state_1	enable	enable
log_archive_dest_state_2	defer	enable	Start with deferred configuration on primary. We will enable it later.
standby_file_management	auto	auto	Let Oracle add/resize data and log files on standby
fal_server	HRPRDS	HRPRD	In the event primary assumes the role of standby.
fal_client	HRPRD	HRPRDS	In the event primary assumes the role of standby.
log_file_name_convert	('HRPRDS', 'HRPRD')	('HRPRD', 'HRPRDS')	Let Oracle manage logfiles on the standby node through this parameter. Specify on both nodes to manage role reversal.
db_file_name_convert	'HRPRDS', 'HRPRD')	('HRPRD', 'HRPRDS')	Let Oracle manage datafiles on the standby node through this parameter. Specify on both nodes to manage role reversal.

Step 2 - Create a password file on primary

In an Oracle Data Guard environment, the primary and standby databases authenticates via password file. Use the following command to create a password file on the primary database server.

orapwd file=PWDHRPRD.ora password=verysecretpassword (On Windows platform)
orapwd file=orapwHRPRD.ora password=verysecretpassword (On Unix platform)

The default name and location of the password file on Windows platform is %ORACLE_HOME%\DATABASE\PWD<ORACLE_SID>.ora and $ORACLE_HOME\dbs\orapw<ORACLE_SID> on Unix platform. You need to restart the instance after creating the password file.

A sidebar on the password file is in order as many Oracle DBAs are not very clear on password file concepts. When you want to startup an Oracle database instance, you need an authentication mechanism outside the database. During startup, you can't use database authentication as Oracle database instance is not up at that time.

Oracle operating system group membership or password file provide authentication outside the database. If you are a member of ORA_DBA group on Windows or dba group on Unix, then you can login "SYS AS SYSDBA" without any password and startup the instance.

But if you are not a member of these groups, then you need a password file to authenticate your "SYS AS SYSDBA" connections. In addition, if you want to startup and/or shutdown databases from remote servers on different domains, then you also need a password file to authenticate "SYS AS SYSDBA" connections. Databases participating in an Oracle Data Guard environment also need password file for authentication.

Step 3 - Enable FORCE LOGGING

Oracle Data Guard operates the standby database with the primary database redo stream. Primary database ships redo stream to the standby database. Redo stream shipment to the standby database can be synchronous or asynchronous depending on the data guard protection mode that you choose. Standby database's managed recovery process applies the redo stream to keep both the databases in synch. Any issues or interruption with the redo stream would put your standby database in jeopardy. For instance, NOLOGGING operations on primary database will definitely jeopardize your standby database. To prevent NOLOGGING operations, you must enable FORCE LOGGING on the primary database.

SQL> SHUTDOWN IMMEDIATE ;
SQL> STARTUP MOUNT ;
SQL> ALTER DATABASE FORCE LOGGING
SQL> ALTER DATABSE OPEN;

Run the following query just to make sure:

SELECT FORCE_LOGGING
FROM V$DATABASE ;

You can also query V$DATAFILE to see if there are any datafiles with NOLOGGED operations, as shown below.

SELECT FILE#, FIRST_NONLOGGED_SCN, FIRST_NONLOGGED_TIME
FROM V$DATAFILE ;

Step 4 - Last Night's RMAN Backup on primary

You need to use your last RMAN backup to seed the standby database. If you don't have RMAN backups, then you need to take a fresh RMAN backup. But I am sure you do perform RMAN backups. If you don't need to take RMAN backups, then I don't think you would be needing a standby database!

If this is a test implementation and you don't have RMAN backups, then take RMAN backup through BACKUP DATABASE command.

BACKUP FORMAT 'C:\OraBack\FullBackup%U.rman' DATABASE PLUS ARCHIVELOG;

Your database must be in ARCHIVELOG mode. If not, enable ARCHIVELOG mode.

Step 5 - Create Standby Control File on primary

A physical standby database needs a control file that is created with "FOR STANDBY" additional clause. You can use RMAN or SQL*Plus to create a standby control file.

SQL> alter database create standby controlfile as 'c:\oraback\stbyctr.ctl';


RMAN> BACKUP FORMAT 'c:\oraback\stbyctr.ctl' CURRENT CONTROLFILE FOR STANDBY;

Step 6 - Add TNSNAMES.ORA Service on primary server

Add the secondary database's TNSNAMES service entry to the primary server's TNSNAMES.ORA file:

HRPRDS =
  (DESCRIPTION =
     (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = HRSTBYSVR)(PORT = 1521))
     )
    (CONNECT_DATA =
        (SERVICE_NAME = HRPRDS)
    )
)

Step 7 - Copy Backups

Copy the following backups to the standby server on the same location as primary database:

• RMAN Hot Backup with database and archive logs
• Standby Control File  

You may need to copy additional archive log backups that were generated since you took the RMAN backup and executed RMAN DUPLICATE command to create a standby database.

Standby Database Configuration

Step 1 - Add TNSNAMES.ORA Service on Secondary

Add the primary database's TNSNAMES service entry on the standby server to TNSNAMES.ORA file :

HRPRD =
  (DESCRIPTION =
     (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = HRPRDSVR)(PORT = 1521))
     )
     (CONNECT_DATA =
        (SERVICE_NAME = HRPRD)
     )
  )

Test connectivity to the primary database server as follows:

C:\> SQLPLUS sys/verysecretpassword@HRPRD as sysdba

Step 2 - Configure Listener.ora on standby server

Update listener.ora as appropriate, and reload or restart listener.

(SID_DESC =
    (ORACLE_HOME = <ORACLE_HOME>)
    (SID_NAME = HRPRDS)
    (SDU=8192)
)

Step 3 - Create init.ora file on standby server

Create init.ora file on the secondary server. Refer to the table above for Oracle Data Guard related parameters that you need to configure on the standby server.

If you are on Windows platform, then you need to create the Windows service as follows:

C:>oradim -new -sid HRPRDS -startmode auto -pfile initHRPRDS.ora -syspwd verysecretpassword
Note that the sys password on standby database must be the same as primary database.

Now start the database instance on standby server and logout from your SQL*Plus session.

Step 4 - Create password file on standby

If you are on Windos platform, then you don't need to create a password file. Otherwise, create the password file on the secondary server as follows. Note that the password on standby database must be the same as primary database.

orapwd file=PWDHRPRDS.ora password=verysecretpassword (On Windows platform)

Use SQL*Plus to test connectivity from the primary database server to the standby server.

C:\> SQLPLUS sys/verysecretpassword@HRPRDS as sysdbaDon't proceed to the next step if you are having connectivity issues. Verify passwords and existance of password files

Step 5 - Create Standby Database

RAMN is the tool to create the standby database as follows:

C:\> set ORACLE_SID=HRPRD

C:\>rman nocatalog

Recovery Manager: Release 10.2.0.4.0 - Production on Wed Oct 13 17:04:40 2010

Copyright (c) 1982, 2007, Oracle. All rights reserved.

RMAN> connect auxiliary /

connected to auxiliary database: HRPRD (not mounted)

RMAN> connect target sys@HRPRD

target database Password:
connected to target database: HRPRD (DBID=358909779)
using target database control file instead of recovery catalog

RMAN> duplicate target database for standby dorecover ;

-----
-----
-----

media recovery complete, elapsed time: 00:07:50
Finished recover at 13-OCT-10
Finished Duplicate Db at 13-OCT-10

RMAN>

Good News! Physical Standby Database has been created.

Common Errors

If you haven't specified db_name = HRPRD and db_unique_name=HRPRDS, then you will get the following error:

RMAN-03002: failure of Duplicate Db command
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of sql command on clone_default channel
RMAN-11003: failure during parse/execution of SQL statement: alter database mount standby database
ORA-01103: database name 'HRPRD' in control file is not 'HRPRDS'

If you haven't copied the primary database backup to the standby database server in the same location as primary database, then you would get the following errro.

executing command: SET until clause
Starting restore at 16-DEC-09
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece .......
ORA-19870: error reading backup piece .........
ORA-19505: failed to identify file ........
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 3) The system cannot find the path specified. failover to previous backup
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at ........
RMAN-03015: error occurred in stored script Memory Script
RMAN-06026: some targets not found - aborting restore
RMAN-06024: no backup or copy of the control file found to restore

If you haven't copied all the archive logs that were generated since you took the RMAN backup, you would get the following error:

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: ...........
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 12/16/2010 11:15:38
RMAN-03015: error occurred in stored script Memory Script
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of log thread 1 seq 10 lowscn 113033639 found to restore
RMAN-06025: no backup of log thread 1 seq 9 lowscn 113029915 found to restore
RMAN-06025: no backup of log thread 1 seq 8 lowscn 113014889 found to restore

Note that the standby database must be in NOMOUNT state when you initiate the RMAN duplicate command.

RMAN> duplicate target database for standby dorecover ;
Starting Duplicate Db at 16-DEC-10
using channel ORA_AUX_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 12/16/2009 11:26:19
RMAN-05500: the auxiliary database must be not mounted when issuing a DUPLICATE
command
RMAN> quit

Step 6 - Add Standby Redo Log Files on primary and standby databases

You must configure standby redo logs on standby database to activate real-time redo transport. Standby redo log files must be of the same size as primary database redo log files. You need to create at least the same number of standby redo log files as primary database.

ALTER DATABASE
ADD STANDBY LOGFILE 'E:\oradata\HRPRDS\StbyRedo1.log'
SIZE 100M;

Also perform the same task on primary database in the event primary assumes the role of standby database.

Step 7 - Activate standby database managed recovery on Standby

SQL> Alter database recover managed standby database using current logfile disconnect ;

Step 8 - Enable Physical Standby on Primary

Enable log_archive_dest_state_2 parameter and then perform a log switch on the primary database server:

SQL> ALTER SYSTEM SET log_archive_dest_state_2 = enable ;

SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> ALTER SYSTEM SWITCH LOGFILE;

And you should see the latest archive log on the standby server! Oracle Data Guard Physical Standby Database is running!!

If you don't see what you were expecting, then review alert logs and trace files. You may also query V$ARCHIVE_LOG table to degug this issue further.

Test It!

You can also run a simple test case to demonstrate that redo is being shipped in real-time and application data is available on standby database.

Run the following on primary database.

SQL> drop table test purge ;

Table dropped.

SQL> create table test
2 ( test date ) ;

 Table created.

SQL> insert into test values(sysdate) ;

1 row created.

SQL> /

1 row created.

SQL> commit ;

Commit complete.

SQL> alter session set nls_date_format='MM/DD/YYYY:HH24:MI:SS' ;

Session altered.

SQL> SELECT * FROM TEST ;

TEST
-------------------
10/17/2010:00:01:57
10/17/2010:00:01:58

Run the following on standby database server:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL ;

Database altered.

SQL> ALTER DATABASE OPEN READ ONLY ;

Database altered.

SQL> SELECT * FROM JMEHTA.TEST ;

TEST
-------------------
10/17/2010:00:01:57
10/17/2010:00:01:58

SQL> ALTER DATABASE CLOSE ;

Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT ;

Database altered.

Verify Data Guard Implementation

Few Verification Checks you should perform on Physical Standby Database running in Maximum Performance Mode.

For physical standby database to be relevant to your high availability and disaster/recovery requirements, you need to make sure that it's keeping up with the primary database and is in sync with the primary database. Once implemented, you need to make sure that it is up and running at all time. Below is a list of few important tasks that you should perform to verify physical standby database's operation.

Test 1 - Archive Logs on Primary and Standby

When you perform a log switch on the primary database, you will see the corresponding archive log on the standby database server. Archive log location on primary and standby is defined by log_archive_dest_1 and its format by log_archive_format parameter.

In addition, V$ARCHIVE_LOG view on primary displays archive logs generated by both primary and standby databases. Run the following query to verify:

SQL> SELECT STANDBY_DEST,DEST_ID, ARCHIVED, APPLIED, MAX(SEQUENCE#)
2 FROM V$ARCHIVED_LOG
3 GROUP BY STANDBY_DEST, DEST_ID, ARCHIVED, APPLIED;

STA DEST_ID    ARC APP MAX(SEQUENCE#)
--- ---------- --- --- --------------
NO   1         YES  NO 164607
YES  2         YES YES 164607

Test 2 - Archive Log Gap

Query V$ARCHIVE_GAP to determine if you have gaps in archive logs on standby database. Since you should have configured fal_server and fal_client parameters, Oracle Data Guard should automatically resolve the archive log gaps by requesting to the primary database to send the missing archive logs.

But if you don't have the archive logs that the physical standby database needs, then you would have archive log gaps and your standby database would be out of sync. Remedies to fix this issue may vary, but your standby database is out of sync.

Test 3 - Real-time Redo Propagation

You want to make sure that your physical standby database is keeping up with the primary database.

Method 1

On standby, Run the following query to evaluate "apply lag" and "transport lag" parameters. The values for these parameters will vary, but you will get an idea whether your standby database is keeping up with the primary database or not.

SQL> SELECT *
2 FROM V$DATAGUARD_STATS ;

NAME
--------------------------------
VALUE
----------------------------------------------------------------
UNIT TIME_COMPUTED
------------------------------ ------------------------------
apply finish time
+00 00:00:00.0
day(2) to second(1) interval 16-OCT-2010 23:35:11

apply lag
+00 00:00:05
day(2) to second(0) interval 16-OCT-2010 23:35:11

estimated startup time
14
second 16-OCT-2010 23:35:11

standby has been open
N
16-OCT-2010 23:35:11

transport lag
+00 00:00:00
day(2) to second(0) interval 16-OCT-2010 23:35:11

Method 2

On standby, Run the following query to compute redo lag. You may see negative value for RedoAsOf, but at least you would get an idea about synchronization lag.

SQL> SELECT SYSDATE CurrentTime, MAX(LAST_TIME) RedoAsOf,
2 SYSDATE - MAX(LAST_TIME) RedoLag
3 FROM V$STANDBY_LOG ;

CURRENTTIME          REDOASOF           REDOLAG
------------------- ------------------- ----------
10/16/2010:23:41:02 10/16/2010:23:41:03 -.00001157

Method 3

On primary database, run the following query to compute the current SCN

SQL> SELECT CURRENT_SCN FROM V$DATABASE ;

CURRENT_SCN
--------------------
9,084,966,569,214

On secondary database, run the following query to compute the current SCN, and compare it against the value from the previous query.

SQL> SELECT MAX(LAST_CHANGE#) CURRENT_SCN FROM V$STANDBY_LOG ;

CURRENT_SCN
--------------------
9,084,966,569,212

Long Live Data Pump!

Oracle DBAs would love to reminisce about export and import utilities. The primary usage of export was to perform logical backup of the database, and then import the dump file to recreate the database on the same OS platform or different OS platform. Quite useful! In addition, Oracle DBAs routinely extracted DDLs and user creation scripts off of export dump file. Few third-party tools and utilities were specifically designed to extract DDLs off of the dump file. In fact, export backup did save my day when I had to extract hashed passwords for few users whose passwords were accidently changed.

As of Oracle11g, Export/Import has been desupported and given way to much more versatile Data Pump utility. Oracle describes Data Pump as very high-speed data movement utility. Indeed, Data Pump is quite powerful and DBAs should add data pump backups to Database Disaster/Recovery Plan.

The purpose of this blog is to examplify few of its indirect usages that will come quite handy. For example, you may ask, “Can I extract hashed passwords with data pump?” The answer is, “Of course!” Let’s discuss.

Extract Hashed Passwords

To extract hashed passwords, all you need to do is run data pump import with SQLFILE and INCLUDE parameters, as illustrated below:

C:\Oracle\oraback\jdev11g>impdp system/****** directory=dp_backup dumpfile=jdev11g.dmp logfile=jdev11gimp.log include=USER sqlfile=jdev11g.sql

Import: Release 11.2.0.1.0 - Production on Mon Sep 27 21:58:04 2010 Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "SYSTEM"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded

Starting "SYSTEM"."SYS_SQL_FILE_FULL_01":  system/******** dumpfile=jdev11g_1.dmp directory=dp_backup include=USER logfile=jdev11gimp.log sqlfile=jdev 11g.sql content=metadata_only Processing object type DATABASE_EXPORT/SYS_USER/USER Processing object type

DATABASE_EXPORT/SCHEMA/USER Job "SYSTEM"."SYS_SQL_FILE_FULL_01" successfully completed at 21:58:10

--- Contents from SQLFILE ---

CREATE USER "JMEHTA" IDENTIFIED

BY VALUES 'hash string'     

DEFAULT TABLESPACE "USERS"  

TEMPORARY TABLESPACE "TEMP";

SQLFILE parameter directs data pump import (impdp) to write DDLs to the file specified with this parameter. Writing DDLs to the SQLFILE is “instead of behavior.” Impdp doesn’t execute the DDLs in the database. Impdp needs a database connection to execute DBMS_METADATA and DBMS_DATAPUMP PL/SQL packages to perform its work.

Also note that with SQLFILE parameter, you cannot specify CONTENT=ALL or DATA_ONLY. In other words, impdp doesn’t generate SQL statements that could be used later to populate the database tables. SQLFILE includes DDLs for tablespaces, users, role grants, packages, procedures, functions, tables, indexes, primary and foreign keys, etc.

INCLUDE parameter allows to target the DDLs you are interested in. Specifying INCLUDE=USER will give you CREATE USER statements. To see a list of valid paths for use with the INCLUDE parameter, you can query the following views: DATABASE_EXPORT_OBJECTS for Full mode, SCHEMA_EXPORT_OBJECTS for schema mode, and TABLE_EXPORT_OBJECTS for table and tablespace mode.

Cloning an Oracle 10g Database on a New Server

In the previous blog, I explained how to clone an Oracle 10g Database on the same server using RMAN. As explained in the previous blog, cloning an Oracle database on the same server contends for the resources with the primary database, and it may not work for many of you. Your only option is to clone the database on a different server.

Whether you are planning to clone an Oracle database on the same server or different server, the RMAN procedure is pretty much the same with the exception of few additional steps that you need to perform. All steps are described below.

The requirement for this task is similar – clone an existing development database C10gDEV as C11gDEV, but on a new server. Let’s use the terms primary and secondary servers. C10gDEV DB resides on the primary server, but C11gDEV DB will reside on the secondary server. The plan is to clone C10gDEV database and then upgrade C11gDEV database to 11g R2. Whatever your requirement may be, the steps described here are still applicable. 

Oracle database version is 10.2.0.4 and operating system is Windows 2008 64-bit.

Step 1 - Assign Oracle SID

Assign the following identifiers to the new database. All identifiers should be different than the database being cloned.

Database Name - C11gDEV
Instance Name - C11gDEV
Service Name - C11gDEV
TNS Connect String – C11gDEV

Step 2 - Allocate Disk Resources

The first step is to allocate sufficient disk space for the new database. For simplicity, assume that all data files and log files are going to the same folder E:\Oradata\C11gDEV. It’s development environment!

Step 3 - Create folder structure

Create the following folders to hold administrative data. All folder names defined here are as per local organization naming standards. Follow your organizational naming standards.

Audit Files - E:\Oradmin\C11gDEV\audit
Background Dump Destination - E:\Oradmin\C11gDEV\bdump
Core Dump Destination - E:\Oradmin\C11gDEV\cdump
User Dump Destination - E:\Oradmin\C11gDEV\udump

Archive Log Destination– E:\Orarch\C10gDEV (if ARCHIVELOG needed)

Please note that the name of the archive log destination is the same on both databases. Change the name later if needed. Otherwise you need to copy the archive logs to new location on the primary server and then run “CATALOG FROM” command to catalog archive logs.

Step 4 - Init.ora Updates

Copy existing C10gDEV’s init.ora file as initC11gDEV.ora, and make the following changes. The change list suggested below should be sufficient for most DBAs, but it’s not a complete list. You may have additional parameters that would require updates.

db_name=C11gDEV
instance_name= C11gDEV
service_names= C11gDEV

Update the following parameters to point to log/trace file locations as described above.

background_dump_dest
core_dump_dest
user_dump_dest
audit_file_dest

Update the following parameter to the location where you want to store C11gDEV’s control files. In this example, we will use E drive. Make sure that this folder is accessible.

control_files=(‘e:\oradata\C11gDEV\control1.ctl’,‘e:\oradata\C11gDEV\control2.ctl’ )

C10GDEV’s datafiles reside on E:\OraData\C10gDEV, but Oracle DBAs have decided to store C11gDEV’s database in E:\OraData\C11gDEV.

Update the following parameters to instruct Oracle to translate datafile names during the cloning by specifying db_file_name_convert init.ora parameter.

db_file_name_convert=('e:\oradata\C10gDEV','e:\oradata\C11gDEV')

Add additional translation strings to this parameter if you have multiple disk drives hosting the database.

For example,

db_file_name_convert=('e:\oradata\C10gDEV','e:\oradata\C11gDEV',
                      'd:\oradata\C10gDEV','e:\oradata\C11gDEV' )

Specify log_file_name_convert parameter to instruct Oracle to translate log file names.

log_file_name_convert=('e:\oradata\C10gDEV','e:\oradata\C11gDEV')

Adjust the following memory parameters as per memory availability.

sga_target
sga_max_size
db_cache_size
pga_aggregate_target
java_pool_size
large_pool_size
shared_pool_size
streams_pool_size

Make sure that COMPATIBLE parameter is the same for both the databases. Otherwise, you will get “ORA-01130: database file version incompatible” error.

Step 5 - Service and/or Password File Creation

On Windows platform, create a service for the new database using oradim as shown below:

oradim -new -sid C11gDEV -syspwd verysecretdbpassword -startmode auto -pfile initc11gdev.ora

This command creates a Windows service for C11gDEV database. In addition to Windows service, this command also creates a password file. Note that pfile resides in %ORACLE_HOME%\database.

On *nix platforms, you don’t need to create a service, but create a password file by running orapwd. Password file is needed to connect to C11gDEV database as SYSDBA.

orapwd file=orapwC11gDEV password=verysecretpassword

Please note the password file name.

Step 6 - C11gDEV Instance Startup

Start C11gDEV Oracle database instance with nomount option by:

C:\> set ORACLE_SID=C11gDEV
C:> sqlplus /nolog
SQL> connect / as sysdba
SQL> Startup nomount
SQL> exit

Remember to logout from this SQL*Plus session. Otherwise, your cloning process would hang as new database will be shutdown in normal mode during the cloning process.

Step 7 - Listener Configuration

Add C11gDEV database to listener.ora file. Also create a new TNSNAMES.ORA alias. Use tnsping or SQL*plus to test connectivity by

Sqlplus sys@c10gdev as sysdba

Sqlplus sys@c11gdev as sysdba

Step 8 - Copy Backup to the Secondary Server

Restore on secondary server C10gDEV database’s RMAN backup and required set of archived redo logs to the same location as on the primary server.  RMAN expects the backup sets to be available in the location on the secondary server as primary database server.

If you don’t have the same folder structure available on the secondary server, then you need to perform few additional steps. For example, C10gDEV’s RMAN backup resides on D:\OraBack\C10GDEV, but you need to restore backup to E:\OraBack\C11gDEV on the secondary server. Copy C10gDEV’s RMAN backup to the new location on the primary server, and run CATALOG FROM command to catalog a copy of the backup.

Step 9 - Update RMAN Catalog

C:\> set ORACLE_SID=C10gDEV

C:\> rman nocatalog

RMAN> connect target /

RMAN> crosscheck backup

RMAN> crosscheck archivelog all

RMAN> delete expired backup

RMAN> catalog start with '<RMAN Backup Location>' ;

Step 10 - RMAN Cloning

Start RMAN session as shown below. This example is for RMAN with NOCATALOG.

C:\> set ORACLE_SID=C11gDEV

C:\> rman nocatalog

RMAN> connect target sys@C10gDEV

RMAN> Connect auxiliary /

RMAN> duplicate target database to C11gDEV ;

If you want to clone the database till a specific point-in-time, then use the following command.

RMAN> duplicate target database to C11gDEV until time '08/03/2010:23:00:00'

Make sure that you specify the same database name above as specified in init.ora file. Otherwise, you would get “RMAN-05520: database name mismatch”

Wow! C11gDEV Database is up and running.

DUPLICATE command has performed the following steps:

• Restored and recovered C10GDEV database
• Datafile and logfile names are converted per db_file_name_convert and log_file_name_convert init.ora parameters
• New Control files created for C11gDEV database per control_files init.ora parameter
• DB_NAME renamed to C11gDEV
• Mounted C11gDEV database and performed complete media recovery. Applied necessary archive logs to accomplish this.
• GLOBAL_NAME updated
• TEMP files created 
• Opened database with RESETLOGS option

We are greatful, Mr. Export/Import!

We had a production issue yesterday that caused an outage. Export backups came to the rescue and saved the day!

Per organizational security policy, Oracle database passwords of all database accounts, including those used in database links must be changed at regular interval. Database links have been established over secure network between our database and our partner’s database. DB Links are in place for over 10 years, and serving the business purpose of one-directional data/information dissemination. We have DB accounts in our database that our partners use in database links to access our database.

Per organizational security policy, yesterday was the password-change day. DBAs changed the passwords of DB link accounts. When DBAs tried to contact our partner so that they could re-create the database links with the new passwords, there was no one on the other side to re-create the DB link. DBAs tried home and cell, but no avail. A five-minute of planned maintenance window turned into a thirty-minute of production outage, and still no response from the other side. That’s when a decision was made to revert the passwords back to original passwords. But DBAs realized that they didn’t have, and they don’t maintain DB Link accounts passwords. Production outage had reached a 45-minute mark. DBAs tried jogging their memory, referred to old notes, etc., but couldn’t trace the original passwords.

This is when an idea came up! Export backup! This was an Oracle10g database. Nightly export backups were performed. Export dump file stores “CREATE USER username IDENTIFIED BY VALUES ’hash string'” statements. Hash strings represent hashed Oracle passwords. DBAs quickly located last night’s export dump file, grabbed the applicable CREATE USER statements, and changed passwords to their original values with “ALTER USER username IDENTIFIED BY VALUES 'hash string'”. DBAs had to work around password reuse settings in database profiles to restore the passwords, but that wasn’t a problem. DB Links were back in operation! Export saved the day! We are greatful, Mr. Export/Import!

P.S. As of Oracle 11g R2, Export/Import has been desupported in favor of more versatile Data Pump. Export and Import executables are provided with Oracle11g so data from older versions of Oracle can be imported or exported to. Oracle Data Pump import do provide option to retrieve hashed passwords.

Cloning an Oracle Database on the Same Server

Oracle DBAs routinely clone Oracle databases for variety of reasons. This blog explains how to clone an Oracle database on the same server. The requirement for this task is quite simple – clone an existing database. A development Oracle10g instance C10gDEV already exists. We just need to clone this database on the same server as C11gDEV, and then upgrade to 11g later. In this blog, we will talk about the cloning. Upgrade discussion would be part of future blogs.

With RMAN, Oracle DBAs can easily clone the database on the same server with few steps. The time needed to clone the database on the same server is proportional to the time it would take to restore/recover the database plus time to perform the few steps listed below.

In addition to creating or cloning a database for upgrade testing, you may also find few other creative and innovative applications. I have effectively used cloning for the following purposes:

• To quickly create a second database environment on the same server for testing/debugging/tuning.
• To validate and verify RMAN Backup/Recovery procedures by cloning a database and then verifying the data.
• To test Oracle upgrades by cloning the database and then upgrading it. You can also use this approach to test the patches.

Cloning the Database the Same Server technique is quite fast. No need to copy the backups to secondary server. RMAN backups are on disk and available. There are only few steps to perform the cloning.

Caution

• Think twice if you are planning to clone the production database on the same server. Some DBAs supporting mission critical applicaitons may not be able to clone the database on thhe same server. If you are 24 X 7 X 365, then be extra careful.

• Please ensure that you have sufficient CPU, disk and memory capacity on the server to mount a cloned instance and database on the server.

• You may want to perform cloning during maintenance window or off-peak hours to minimize performance impact on the primary database.

• And ensure that your backups are good and recoverable, just in-case!

1.2 Clone the DB!

The requirement for this task is quite simple – clone an existing development database C10gDEV on the same server as C11gDEV. Oracle DBA’s plan is to clone C10gDEV database on the same server and then upgrade C11gDEV database to 11g R2. Existing database version is 10.2.0.4 and operating system is Windows 2008 64-bit.

1.2.1 Assign Oracle SID

Assign the following identifiers to the new database. All identifiers must be different than the identifiers used for the database being cloned.

Database Name - C11gDEV
Instance Name - C11gDEV
Service Name - C11gDEV
TNS Connect String – C11gDEV

1.2.2 Allocate Disk Resources

The first step is to allocate sufficient disk space for the new database. For simplicity, assume that all data files and log files are going to the same folder E:\Oradata\C11gDEV. It’s development environment!

1.2.3 Create folder structure

Create the following folders to hold administrative data. All folder names defined here are as per local organization naming standards. Follow your organizational naming standards.

Audit Files - E:\Oradmin\C11gDEV\audit
Background Dump Destination - E:\Oradmin\C11gDEV\bdump
Core Dump Destination - E:\Oradmin\C11gDEV\cdump
User Dump Destination - E:\Oradmin\C11gDEV\udump
Archive Log Destination– E:\Orarch\C11gDEV (if ARCHIVELOG needed)

1.2.4 Init.ora Updates

Copy existing C10gDEV’s init.ora file as initC11gDEV.ora, and make the following changes. The change list suggested below should be sufficient for most DBAs, but it’s not a complete list. You may have additional parameters that would require updates.

db_name=C11gDEV
instance_name= C11gDEV
service_names= C11gDEV

Update the following parameters to point to log/trace file locations as described above.

background_dump_dest
core_dump_dest
user_dump_dest
audit_file_dest

Update the following parameter to the location where you want to store C11gDEV’s control files. In this example, we will use E drive. Make sure that this folder is accessible.

control_files=(‘e:\oradata\C11gDEV\control1.ctl’, ‘e:\oradata\C11gDEV\control2.ctl’ )

C10GDEV’s datafiles reside on E:\OraData\C10gDEV, but Oracle DBAs have decided to store C11gDEV’s database in E:\OraData\C11gDEV.

Update the following parameters to instruct Oracle to translate datafile names during the cloning by specifying db_file_name_convert init.ora parameter.

db_file_name_convert=('e:\oradata\C10gDEV','e:\oradata\C11gDEV')

Add additional translation strings to this parameter if you have multiple disk drives hosting the database.

For example,
db_file_name_convert=('e:\oradata\C10gDEV','e:\oradata\C11gDEV',
                                     'd:\oradata\C10gDEV','e:\oradata\C11gDEV' )

Specify log_file_name_convert parameter to instruct Oracle to translate log file names.
log_file_name_convert=('e:\oradata\C10gDEV','e:\oradata\C11gDEV')

Adjust the following memory parameters as per memory availability.

sga_target
sga_max_size
db_cache_size
pga_aggregate_target
java_pool_size
large_pool_size
shared_pool_size
streams_pool_size

Make sure that COMPATIBLE parameter is the same for both the databases. Otherwise, you will get “ORA-01130: database file version incompatible” error.

1.2.5 Service and/or Password File Creation

On Windows platform, create a service for the new database using oradim as shown below:

set ORACLE_SID=C11gDEV

oradim -new -sid C11gDEV -syspwd verysecretdbpassword -startmode auto -pfile initc11gdev.ora

This command creates a Windows service for C11gDEV database. In addition to Windows service, this command also creates a password file. Note that pfile resides in %ORACLE_HOME%\database.

On *nix platforms, you don’t need to create a service, but create a password file by running orapwd. Password file is needed to connect to C11gDEV database as SYSDBA.

orapwd file=orapwC11gDEV password=verysecretpassword

Note the difference in password file names on Windows and Unix. Please be careful with the password file name.

1.2.6 C11gDEV Instance Startup

Start C11gDEV Oracle database instance with nomount option by:

C:\> set ORACLE_SID=C11gDEV
C:\> sqlplus /nolog
SQL> connect / as sysdba
SQL> Startup nomount
SQL> exit

Remember to logout from this SQL*Plus session. Otherwise, your cloning process would hang as Oracle shuts down the new database in normal mode during the cloning process.

1.2.7 Listener Configuration

Add C11gDEV database to listener.ora file. Also create a new TNSNAMES.ORA alias. Use tnsping or SQL*plus to test connectivity.

1.2.8 Update RMAN Catalog

Most likely, you won't need this statp. This step is needed if (1) your objective is to verify database recovery process by restoring the RAMN backups from tape to disk to an alternate location and then clonig the database or (2) RMAN backup location has changed and control file doesn’t have the correct information.

If your objective is to test RMAN backup/recovery procedures, then you want to make sure that RMAN picks up the backups that were restored from the tape. Rename the existing C10gDEV database's RMAN backup location temporarily on disk to a new name, and then perform the following steps. If you don't perform this step, then RMAN would pick up the latest backup on disks, if available.

C:\> set ORACLE_SID=C10gDEV

C:\> rman nocatalog
RMAN> connect target /
RMAN> crosscheck backup
RMAN> crosscheck archivelog all
RMAN> delete expired backup
RMAN> catalog start with 'E:\OraBack\C10gDev\RMANHot' ;

1.2.9 RMAN Cloning

Start RMAN session as shown below. This example is for RMAN with NOCATALOG.

Note that target database is your source database - the database that you want to clone. Auxiliary specifies the new database.

C:\> set ORACLE_SID=C10gDEV

C:\> rman nocatalog
RMAN> connect target /
RMAN> Connect auxiliary sys@C11gDEV
RMAN> duplicate target database to C11gDEV ;

Wow! C11gDEV Database is up and running.

If you want to clone the database till a specific point-in-time, then use the following command.

RMAN> duplicate target database to C11gDEV until time '08/03/2010:23:00:00'

Make sure that you specify the same database name as specified in init.ora file. Otherwise, you would get “RMAN-05520: database name mismatch”

DUPLICATE command has performed the following steps:

• Restored and recovered C10GDEV database
• Datafile and logfile names are converted per db_file_name_convert and log_file_name_convert init.ora parameters
• New Control files created for C11gDEV database per control_files init.ora parameter
• DB_NAME renamed to C11gDEV
• Mounted C11gDEV database and performed complete media recovery. Applied necessary archive logs to accomplish this.
• GLOBAL_NAME updated
• TEMP files created
• Opened database with RESETLOGS option

1.2.10 Log File Excerpts

RMAN> duplicate target database to C11gDEV ;
Starting Duplicate Db at 15-DEC-10
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=156 devtype=DISK
contents of Memory Script:
{
   set newname for datafile  1 to ......
   set newname for datafile  2 to ......
   set newname for datafile  3 to ......
   ...........
   ...........
   ...........
   restore
   check readonly
   clone database
   ;
}
executing Memory Script
executing command: SET NEWNAME
executing command: SET NEWNAME
..........
..........
..........
Starting restore at 15-DEC-10
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to .........
restoring datafile 00002 to .........
restoring datafile 00003 to .........
channel ORA_AUX_DISK_1: reading from backup piece ..........
Finished restore at 15-DEC-09
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "C11gDEV" RESETLOGS NOARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
..........
..........
..........
 CHARACTER SET WE8MSWIN1252

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script
datafile 2 switched to datafile copy
.............
.............
.............
contents of Memory Script:
{
   recover
   clone database
   noredo
   ,
    delete archivelog
   ;
}
executing Memory Script
Starting recover at 15-DEC-09
using channel ORA_AUX_DISK_1
Finished recover at 15-DEC-09
contents of Memory Script:
{
   shutdown clone;
   startup clone nomount ;
}
executing Memory Script
database dismounted
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area     629145600 bytes
Fixed Size                     1298288 bytes
Variable Size                171966608 bytes
Database Buffers             452984832 bytes
Redo Buffers                   2895872 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "C11gDEV" RESETLOGS NOARCHIVELOG
...............
...............

renamed temporary file 1 ......
contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 15-DEC-09
RMAN>

Do It Yourself: Implement Performance Monitoring Tool - Eagle

During the era of Oracle7 back in 1998, I wrote an article about how to build a performance monitoring tool. This article, titled ‘Do It Yourself: Implement Performance Monitoring Tool - Eagle’, was published in Select magazine. This article received several kudos from fellow DBAs.

Although this article may not be as relavent as it was 10 years ago, it still demonstrate a concept that can be applied to solve real-life business problems. Eagle introduces a monitoring framework that can be easily extended and fully customized. Some of the key features of Eagle are: Multiple database monitoring from control center database, trend analysis, implemented using PL/SQL and Unix shell, easy to implement and use, customizable and extensible to meet your needs. Source code and procedures to implement Eagle are included here.


-----------------------------------------------------------------------------------------------

Do It Yourself

Implement Performance Monitoring Tool

Keval is a database administrator for a financial company in New York. One day, John queried dynamic performance views to calculate database buffer hit ratio for a Sales database. John found that database buffer hit ratio was 65% which was low and not acceptable. He knew that database buffer hit ratio was 90% just a week ago. He thought this drop in the buffer hit ratio might be due to some spike activities. But he wasn’t sure. John decided to watch database buffer hit ratio everyday for at least a week before taking any corrective actions. John also decided to watch library cache hit ratio, dictionary cache hit ratio and sort area hit ratio to identify memory parameters tuning requirements, if any. John needed a performance monitoring tool that could collect database performance statistics in an automated fashion.

Rupa, a database administrator for a communication company in Fairfax, VA, was informed by her human resources department that they were going to make a heavy use of HR database as company was going through a merger. As she was running low on free space in HR database, she decided to watch % free space and object extensions more closely along with other performance parameters. Rupa needed an automated database monitoring procedures to do this.

Kunal is a database administrator for a consulting firm in Chicago. His company decided to form a centralized DBA service to support all six US offices from a single site. This re-engineering move placed a heavy burden on Mark and his team, as there were 50+ Oracle databases in US six offices. Mark found it very time consuming to log into every system at all six US offices, and examine the health of all Oracle databases he was responsible for. Mark needed an automated database monitoring tool that can reach out to all six sites from a central site, and collect information pertaining to the health of all databases.

Like Keval, Kunal or Rupa, if you are looking for a database monitoring tool, then Eagle may be of interest to you. In fact, I was facing the same challenge as many of us do: monitor multiple databases proactively and efficiently. I evaluated few commercially available DBA tools. Some of them were good but too expensive while some of them didn’t do what I wanted. Finally, I decided to design and implement a tool, Eagle, for database monitoring and administration. I started using Eagle for day-to-day dba chorus and found it very useful. I passed Eagle to couple of my DBA friends, and they also found it very useful. That’s when I decided to write an article about Eagle. I feel that Eagle will also be very useful to all of you. I will be using the name Eagle throughout this article to refer to this performance monitoring tool.

Eagle is a tool designed by the DBA for the DBA to monitor Oracle databases. It falls into do-it-yourself category. With the help of Eagle, you can monitor critical performance tuning parameters like database buffer hit ratio, library cache hit ratio, dictionary cache hit ratio or sort area hit ratio, and space management parameters like % free space and object extension. Eagle can also be customized and extended easily to meet ever-changing requirements.

Now let’s look at the results produced by Eagle. Eagle's output report is shown in Figure 1. Watch Name is the name of the watch for the database. Parameter column is used to provide an additional information for the watch. The rest of the columns represent values of the watch 4 weeks ago, 3 weeks ago, 2 weeks ago, 1 week ago, 6 days ago, 5 days ago, and so on. Today column shows today's value for the watch. Change column represents the change in value of the watch in last 4 weeks.

For example, Database buffer hit ratio in the Sales database was 98% 4 weeks ago, 95% 3 weeks age, 95% 2 weeks age, 90% 1 week ago, and 65% today. The drop of 33%, as shown in change column of Figure 1, is the difference between the values of database buffer hit ratio in 4 weeks. By the same token, you can also examine the trend in the values of library cache hit ratio, dictionary cache hit ratio and sort area hit ratio. % Free Space and Object Extension watches help you analyze database space usage pattern.


Overview
Eagle is based on the concept of watch. Watches are used extensively by programmers to debug code or to evaluate expressions at runtime. In Eagle’s terminology, a watch is used to monitor a parameter of importance. A parameter could be a database buffer hit ratio or library cache hit ratio or % free space. Watches are run against databases being monitored. Eagle lets you monitor watches over a period of time. Not only Eagle collects monitoring data, but also displays trend in the value of a parameter. Here are some of the features of Eagle:

Control Center Database: Eagle uses a control center database to store monitoring information and database statistics of all the databases that are being monitored. You can monitor multiple databases from a single site. Control center database can be an independent Oracle database, or it can be a part of any database. Advantage of having an independent control center database for Eagle is an isolation of monitoring information and database statistics from your production data. Control center database provides a centralized monitoring capability.

Multiple Databases: In today's world, most of the DBAs are called upon to support, monitor and tune more than one Oracle databases. Eagle can help you monitor multiple databases simultaneously. Databases being monitored by Eagle can be on different platforms (e.g. UNIX, NT, Open VMS) at different locations (New York, Washington, San Francisco). There is no limit on number of monitored databases in Eagle. It’s very easy to add new databases for monitoring in Eagle.

History of Watches: Eagle lets you monitor a watch over a period of time. It is very important to collect sufficient data before a conclusion can be drawn as to the behavior of a parameter. You need to distinguish between a drop in the value of a critical parameter due to an unusual activity and a drop in the value of a critical parameter due to a routine activity against your database. Eagle’s output report shows a watch for last 28 days, and you can determine a trend in the value of a watch.

Standard Watches: Eagle provides quite a few standard watches. Standard watches, described below, include categories like memory tuning to I/O tuning, auditing to space management. All you have to do is activate watches for the database that you wish to monitor. Note that the same watch can be used to monitor more than one databases.

Extensibility: Eagle is fully extensible. If you are not satisfied with standard watches available in Eagle, you can add watches of your own. If you want to add a watch to monitor, for example, rollback segment contention, you can do so easily. You define a watch, activate the watch, and you are ready to go. Watches in Eagle are defined in SQL language. This is one of the advantages of Eagle. We all are familiar with SQL, and have used SQL scripts extensively to manage our day-to-day chorus of database administration.

Customization of Watches: It’s likely that features available in a tool may not meet your tuning and monitoring requirements. You may need to customize performance monitoring tool to accommodate conflicting tuning needs of your databases. For example, OLTP database may have differing tuning requirements than OLAP database. It's very easy to customize standard and/or user defined watches in Eagle. Just modify the criteria of the watch.
Standard Watches

To gain more insight, let’s go through standard watches, and how you can use them to tune your database.

Database Buffer Hit Ratio: Database buffer hit ratio shows how effective your database buffers in memory are to satisfy database block access requests. If a database block is not in buffers, then Oracle must perform disk I/O to access this database block, and disk I/O is always more expensive than memory access. This ratio is calculated using the formulae (consistent gets + db block gets) / (consistent gets + db block gets + physical reads). Dynamic performance view v$sysstat stores information about these parameters. You should try to keep database buffer ratio as high as possible. Increase db_block_buffers parameter of init.ora to achieve high database buffer hit ratio.

Dictionary Cache Hit Ratio: Dictionary cache hit ratio represents the percentage of dictionary cache information requests satisfied by dictionary cache in memory. This ratio is calculated using the formulae sum(gets) / (sum(gets) + sum(misses)). Values of sum(gets) and sum(misses) are obtained from v$rowcache dynamic performance view. If you find this ratio, for example, to be 90 or lower over the period of time, consider increasing shared_pool_size parameter of init.ora.

Library Cache Hit Ratio: Library cache hit ratio represents the percentage of library objects requests satisfied by library cache area of SGA. This ratio is calculated using the formulae sum(pins) / (sum(pins) + sum(reloads) ). Values of sums(pins) and sum(reloads) can be obtained from v$library cache dynamic performance view table. Consider increasing shared_pool_size parameter if you find library cache hit ratio in the low range consistently.

Sort Area Hit Ratio: Sort area hit ratio shows the percentage of sorts performed in memory. Sort area hit ratio is calculated by using the formulae sorts(memory) / (sorts(memory) + sorts(disk)). Values of sorts(memory) and sorts(disk) are obtained from v$sysstat dynamic performance view. Sort area hit ratio can vary from 100 to 0 where value of 100 is the ideal situation. If you find sort area hit ratio consistently low, you should consider increasing sort_area_size parameter of init.ora.

% Free Space Alert: One of the daily DBA chores is to ensure that there is enough free space in your database for growth. % free space alert warns you whenever % free space in tablespace drops below 10% of the total space or if there is change of 5% in free space in last 28 days. % free space alert derives its information from data dictionary.

Object Extension Alert: One of the other DBA chores is to watch for object extension. If an object tries to grow beyond maximum extent, you will get “Maximum number of extents reached” error. Object extension alert will alert you about the rate at which object is growing. You can also identify “hot” objects in your database, and take appropriate actions. Even though the later version of Oracle will allow unlimited number of extents, it’s good to identify “hot” objects.

Design
Entity relationship diagram for Eagle is shown in Figure 2, and DDL for Eagle is shown in the script eddl.sql. There are four entities in Eagle: Database, Watch, Database Watch and Database Watch Result.

Information about databases that are going to be monitored is stored in database entity. This entity contains three attributes: database id, name and a database link name. Database id and name attributes of this entity are the id and the name of the database. Database link name is the name of the database link from control center database to the database we want to monitor. You need to add one row in this entity for each database you wish to monitor. As database that is being monitored is queried via database link, you need to create a database link via SQL command. At run time, value of the database link is substituted in the database link parameter of the watch criteria. This allows us to use the same watch for multiple databases.

Watch entity contains watch id, name and watch criteria. Watch criteria is written in SQL language. SQL statement is broken into three parts for simplicity and manageability: insert clause, select clause and where clause. As names imply, Insert clause contains an insert part of the SQL statement while select clause contains select part of the SQL statement and where clause contains where clause of SQL statement, as shown in the script estdwtch.sql.

To simplify SQL programming, you can use prepare1 clause, prepare2 clause, close1 clause and close2 clause attributes of watch entity. One example would be to store SQL statements that create views, and refer them in select clause. One of the standard watch that monitors % free space creates two views: free space view and data file view. Both of these views are referred in subsequent select clause. Since these views are created for temporary purposes, SQL statements to drop them are stored in close1 clause and close2 clause. Refer to estdwtch.sql script to see how watch entity and its components are used to build a watch criteria.

Database watch entity contains information about the watches that are used for a database. It allows you to mix and match databases and watches for monitoring. You can use the same watch for more than one database, and one database can have many watches. There are three attributes in database watch entity: watch id, database id and active yesno. You can turn the watch on by setting it to ‘Y’ and off by setting it to ‘N’.

Database watch result entity stores the monitoring information. There are five attributes in this entity: database id, watch id, run time, parameter and value. Database id and watch id attributes are self-explanatory. Run time attribute contains the time the watch was run. Parameter attribute is used to store optional information about monitoring data. For example, % free space watch uses parameter attribute to store tablespace name, and extent watch uses this attribute to store segment name. Value attribute stores the result or value of the watch.
Execution of Watches

A PL/SQL script, eplsql.sql, is at the heart of Eagle. This script is run at a pre-determined time everyday. First of all, it queries database watch entity for active database watches. A watch is active if active yesno attribute of database watch entity is set to ‘Y’. This script queries database entity to retrieve database link name for the database that is going to be watched. Then it queries watch entity to retrieve watch criteria. Database link name is substituted in the watch criteria. Before executing watch criteria, it executes watch prepare clauses if existent. For example, % free space watch has prepare clauses. Then, it executes watch criteria, and stores results in database watch result entity. Then Eagle performs cleanup, if any, by executing close clauses. The same process is repeated for all active watches.

Installation
If you like what you read so far, then let’s discuss a step-by-step procedure to install Eagle on your system. All scripts have been included in this article

Creating an Oracle Account: Eagle employs a control center database to store monitoring information. If you have a database exclusively for administration, you can use it for Eagle. If you plan to monitor quite a few databases, you might be better off creating a separate database just for Eagle. If you don't want to create a separate database for Eagle, then you can pick any of your database as an Eagle control center database. In any event, create an Oracle account for Eagle with dba privileges. You also need to grant two other privileges as shown in the script eaccount.sql.

Creating Objects, Standard Watches and PL/SQL procedure: Run DDL script, as shown in eddl.sql script, to create necessary tables and indexes. Run estdwtch.sql script to create Eagle’s standard watches. Create execute_db_watch PL/SQL stored procedure by executing a eplsql.sql script. All of these scripts should be run from Eagle Oracle account.

Creating Database Link: Create a database link from the control center database to the database that you wish to monitor, as shown below:

CREATE DATABASE LINK
CONNECT TO EAGLE IDENTIFIED BY
USING 'ConnectString

Note that you can use SQL*Net 1.X connect string or SQL*Net 2.X connect descriptor while creating a database link. If you want to use any Oracle account other than SYS in your database link create statement, then you need to grant appropriate privileges to that Oracle account so that database monitoring information can be selected. Eagle needs select privileges on V$ dynamic performance views and data dictionary views. If you want to monitor control center database itself, then you also need to create a database link to itself as database that is being monitored is queried via database link. Another scenario in which you need to create a database link to the same database is when you are using your production database for Eagle and you want to monitor your production database.

Adding Databases: The next step is to add rows in database table for the databases that are going to be monitored by Eagle. There are three columns in this table: database id, name and database link name. Use the following statement with appropriate values for your database.
INSERT INTO DATABASE (DB_ID, NAME, DB_LINK)
VALUES ('','','');

Activating Watches: Use the following statement to add watches:
INSERT INTO DB_WATCH(DB_ID, WATCH_ID, ACTIVE_YN)
VALUES('','WATCH_ID>','Y') ;
DB_ID and WATCH_ID columns in this table are foreign keys from database and watch tables, respectively. You can de-activate a watch for a database by setting active_yn column to ‘N’ for a corresponding row in db_watch table.

Running Watches: You should execute execute_db_watch stored procedure from SQL*Plus once everyday. You might want to run watches in the evening rather than in the morning so as to take into account daily activities. We have provided eagle.sh Unix shell script that executes EXECUTE_DB_WATCH stored procedure. You can submit this shell script via scheduling utilities like cron in Unix or you can execute this stored procedure via dbms_jobs package. Note that results of watches like Database Buffer Hit Ratio are based on statistics from V$ views, and hence reflect the values since the last database startup, not from the time you ran Eagle last time.

Generating Report: Run a SQL*Plus script, eoutput.sql, to generate an output report. The output produced by this script is shown in Figure 1. The script eagle.sh executes eoutput.sql script along with running execute_db_watch stored procedure. If you run execute_db_watch more than once a day, then you need to modify eoutput.sql script to take into account the script running frequency.

Customizing Watches: As shown in eddl.sql script, Watch criteria is divided into insert clause, select clause and where clause. If you need to modify watch criteria, just update the where clause of the watch. For example, as shown in estdwtch.sql script, the where clause of Extents watch contains extents > 1 clause. If you want to monitor objects with 10 or more extents, you just need to modify where clause to extents > 10. We have shown all standard watches in estdwtch.sql. Make sure that you use two single quotes for character string in the where clause.

Creating a Watch of Your Own: With some modifications, you may be able to import your SQL scripts into Eagle as watches, and use them over and over again to monitor databases. To wtire a watch of your own, you need to construct a SQL statement and store it in watch table. estdwtch.sql script gives you some examples on how to create a watch of your own. Note in the watch criteria. acts as a formal parameter, and is replaced by the database link name during runtime.
Summary

There are two aspects of performance monitoring process: in-depth knowledge of Oracle database and necessary statistics collected over the period of time. Eagle will help you do the later part. By using the sample scripts provided in this article, you should be able to automate performance statistics collection procedures. The purpose of this article is not to provide a shrink-wrapped performance monitoring tool, but to provide a frame-work upon which you can build a sound performance monitoring tool to meet your needs. Eagle maynot meet all of your requirements, but you should be able to customize Eagle to meet your needs. Eagle maynot have all the watches you need, but you should be able to add new watches to extend its functionality. Eagle is a do-it-yourself tool and you are encouraged to enhance and improve Eagle to meet your requirements.

Script 1: Oracle Account Script

REM File Name: eaccount.sql
REM Description: Script to create Eagle Oracle Account
REM Usage: Run from sys or privileged Oracle account
REM Assigns USERS as default and TEMP as temporary tablespace
REM

CREATE USER EAGLE
IDENTIFIED BY EAGLE
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP ;

GRANT DBA TO EAGLE ;
GRANT CREATE VIEW TO EAGLE ;
GRANT EXECUTE ON DBMS_SQL TO EAGLE ;
GRANT SELECT ON TO EAGLE;

Script 2 : Objects Creation Script
REM Name: eddl.sql
REM Description: Creates Objects(Tables/Indexes) in USERS and USERS_IDX tablespace
REM Usage: Run this script from SQL*Plus, Use Eagle Oracle account
REM

CREATE TABLE WATCH (
WATCH_ID VARCHAR2(12) NOT NULL,
NAME VARCHAR2(30) NOT NULL,
PREPARE1_CLAUSE VARCHAR2(256) NULL,
PREPARE2_CLAUSE VARCHAR2(256) NULL,
INSERT_CLAUSE VARCHAR2(256) NOT NULL,
SELECT_CLAUSE VARCHAR2(256) NOT NULL,
WHERE_CLAUSE VARCHAR2(256) NULL,
CLOSE1_CLAUSE VARCHAR2(256) NULL,
CLOSE2_CLAUSE VARCHAR2(256) NULL,
CONSTRAINT WATCK_PK
PRIMARY KEY (WATCH_ID)
USING INDEX TABLESPACE USERS_IDX
STORAGE(INITIAL 16K NEXT 16K PCTINCREASE 0)
) TABLESPACE USERS
STORAGE ( INITIAL 16K NEXT 16K PCTINCREASE 0) ;


CREATE TABLE DATABASE (
DB_ID VARCHAR2(12) NOT NULL,
NAME VARCHAR2(30) NOT NULL,
DB_LINK VARCHAR2(30) NOT NULL,
CONSTRAINT DATABASE_PK
PRIMARY KEY (DB_ID)
USING INDEX TABLESPACE USERS_IDX
STORAGE(INITIAL 16K NEXT 16K PCTINCREASE 0)
) TABLESPACE USERS
STORAGE ( INITIAL 16K NEXT 16K PCTINCREASE 0) ;


CREATE TABLE DB_WATCH (
DB_ID VARCHAR2(12) NOT NULL,
WATCH_ID VARCHAR2(12) NOT NULL,
ACTIVE_YN VARCHAR2(1),
CONSTRAINT DB_WATCH_PK
PRIMARY KEY (DB_ID, WATCH_ID)
USING INDEX TABLESPACE USERS_IDX
STORAGE(INITIAL 16K NEXT 16K PCTINCREASE 0),
CONSTRAINT DB_WATCH_FK1
FOREIGN KEY (DB_ID)
REFERENCES DATABASE (DB_ID),
CONSTRAINT DB_WATCH_FK2
FOREIGN KEY (WATCH_ID)
REFERENCES WATCH (WATCH_ID)
) TABLESPACE USERS
STORAGE ( INITIAL 16K NEXT 16K PCTINCREASE 0) ;


CREATE TABLE DB_WATCH_RESULT(
DB_ID VARCHAR2(12) NOT NULL,
WATCH_ID VARCHAR2(12) NOT NULL,
RUN_TIME DATE NOT NULL,
PARAMETER VARCHAR2(256) NULL,
VALUE NUMBER(12,2) NOT NULL,
CONSTRAINT DB_WATCH_RESULT_FK
FOREIGN KEY (DB_ID,WATCH_ID)
REFERENCES DB_WATCH(DB_ID, WATCH_ID)
) TABLESPACE USERS
STORAGE ( INITIAL 16K NEXT 16K PCTINCREASE 0) ;


Script 3 : Standard Watches Creation Script
REM Name: estdwtch.sql
REM Description: Script to create Standard Watches
REM Usage: Run from SQL*Plus, Use Eagle account
REM
rem
rem
rem Database Buffer Watch
rem
INSERT INTO WATCH(WATCH_ID,NAME,INSERT_CLAUSE,SELECT_CLAUSE,WHERE_CLAUSE)
VALUES('DB_BUF_HR','Database Buffer Hit Ratio',
'INSERT INTO DB_WATCH_RESULT(DB_ID,WATCH_ID,RUN_TIME,VALUE) ',
'SELECT '''',''DB_BUF_HR'',SYSDATE,100*ROUND(((A.VALUE + B.VALUE - C.VALUE)/(A.VALUE + B.VALUE)),3) FROM SYS.V$SYSSTAT@ A,SYS.V$SYSSTAT@ B,SYS.V$SYSSTAT@ C ',
'WHERE C.NAME = ''physical reads'' AND A.NAME = ''consistent gets'' AND B.NAME = ''db block gets'' ') ;
rem
rem Sort Area Hit Ratio
rem
INSERT INTO WATCH(WATCH_ID,NAME,INSERT_CLAUSE,SELECT_CLAUSE,WHERE_CLAUSE)
VALUES('SORT_HR','Sort Area Hit Ratio',
'INSERT INTO DB_WATCH_RESULT(DB_ID,WATCH_ID,RUN_TIME,VALUE) ',
'SELECT '''',''SORT_HR'',SYSDATE,100*ROUND(((A.VALUE - B.VALUE)/(NVL(A.VALUE,1))),3) FROM SYS.V$SYSSTAT@ A,SYS.V$SYSSTAT@ B ',
'WHERE A.NAME = ''sorts (memory)'' AND B.NAME = ''sorts (disk)'' ') ;
rem
rem Dictionary Cache Hit Ratio
rem
INSERT INTO WATCH(WATCH_ID,NAME,INSERT_CLAUSE,SELECT_CLAUSE,WHERE_CLAUSE)
VALUES('DC_HR','Dictionary Cache Hit Ratio',
'INSERT INTO DB_WATCH_RESULT(DB_ID,WATCH_ID,RUN_TIME,VALUE) ',
'SELECT '''',''DC_HR'',SYSDATE,100*ROUND(((SUM(GETS) - SUM(GETMISSES))/(SUM(GETS))),3) FROM SYS.V$ROWCACHE@ ',
' ');
rem
rem Library Cache Hit Ratio
rem
INSERT INTO WATCH(WATCH_ID,NAME,INSERT_CLAUSE,SELECT_CLAUSE,WHERE_CLAUSE)
VALUES('LIB_HR','Library Cache Hit Ratio',
'INSERT INTO DB_WATCH_RESULT(DB_ID,WATCH_ID,RUN_TIME,VALUE) ',
'SELECT '''',''LIB_HR'',SYSDATE,100*ROUND(((SUM(PINS) - SUM(RELOADS))/(SUM(PINS))),3) FROM SYS.V$LIBRARYCACHE@ ',
' ');


rem
rem Extents Watch
rem
INSERT INTO WATCH(WATCH_ID,NAME,INSERT_CLAUSE,SELECT_CLAUSE,WHERE_CLAUSE)
VALUES('EXTENTS','Object Extention',
'INSERT INTO DB_WATCH_RESULT(DB_ID,WATCH_ID,RUN_TIME,PARAMETER,VALUE) ',
'SELECT '''',''EXTENTS'',SYSDATE,OWNER''.''SEGMENT_NAME,EXTENTS FROM SYS.DBA_SEGMENTS@ ',
'WHERE (EXTENTS > 10 AND OWNER NOT IN (''SYSTEM'',''SYS'')) OR SEGMENT_TYPE = ''ROLLBACK''') ;
rem
rem Free Space Watch
rem
INSERT INTO WATCH(WATCH_ID,NAME,INSERT_CLAUSE,SELECT_CLAUSE,WHERE_CLAUSE,PREPARE1_CLAUSE,PREPARE2_CLAUSE,CLOSE1_CLAUSE,CLOSE2_CLAUSE)
VALUES('FREESPC','% Free Space',
'INSERT INTO DB_WATCH_RESULT(DB_ID,WATCH_ID,RUN_TIME,PARAMETER,VALUE) ',
'SELECT '''',''FREESPC'',SYSDATE,B.TABLESPACE_NAME,((NVL(A.BYTES,0))/((B.BYTES)))*100 FROM FREE_SPACE_VIEW A,DATA_FILE_VIEW B ',
'WHERE A.TABLESPACE_NAME(+)=B.TABLESPACE_NAME ',
'CREATE VIEW FREE_SPACE_VIEW(TABLESPACE_NAME,BYTES) AS SELECT TABLESPACE_NAME,SUM(BYTES) FROM SYS.DBA_FREE_SPACE@ GROUP BY TABLESPACE_NAME',
'CREATE VIEW DATA_FILE_VIEW(TABLESPACE_NAME,BYTES) AS SELECT TABLESPACE_NAME,SUM(BYTES) FROM SYS.DBA_DATA_FILES@ GROUP BY TABLESPACE_NAME',
'DROP VIEW FREE_SPACE_VIEW',
'DROP VIEW DATA_FILE_VIEW');
rem
rem
commit;

Script 4 : PL/SQL Stored Procedure
REM
REM Name: eplsql.sql
REM Description: Create PL/SQL procudure that executes active watched
REM Usage: Run from SQL*Plus, Use Eagle account
REM
CREATE OR REPLACE PROCEDURE EXECUTE_DB_WATCH
AS
CURSOR C_DB_WATCH IS
SELECT DB_ID, WATCH_ID, ACTIVE_YN
FROM DB_WATCH ;
db_watch_rec DB_WATCH%ROWTYPE ;
watch_rec WATCH%ROWTYPE ;
database_rec DATABASE%ROWTYPE ;
sql_stmt VARCHAR2(1000) ;
ret_val INTEGER ;
cursor_id INTEGER ;
c_get_data INTEGER ;
temp_clause VARCHAR2(256) ;
BEGIN
OPEN C_DB_WATCH;
LOOP
FETCH C_DB_WATCH INTO db_watch_rec;
EXIT WHEN C_DB_WATCH%NOTFOUND ;
IF db_watch_rec.active_yn = 'Y' THEN
SELECT *
INTO watch_rec
FROM WATCH
WHERE WATCH_ID = db_watch_rec.watch_id ;
/* fetch database record */
SELECT *
INTO database_rec
FROM DATABASE
WHERE DB_ID = db_watch_rec.db_id ;
watch_rec.select_clause := REPLACE(watch_rec.select_clause,
'',database_rec.db_id);
watch_rec.select_clause := REPLACE(watch_rec.select_clause,
'',database_rec.db_link);
sql_stmt := watch_rec.insert_clause
watch_rec.select_clause
watch_rec.where_clause;
IF watch_rec.prepare1_clause IS NOT NULL THEN
watch_rec.prepare1_clause := REPLACE(watch_rec.prepare1_clause,
'',database_rec.db_link);
cursor_id := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cursor_id,watch_rec.prepare1_clause,DBMS_SQL.V7);
ret_val := DBMS_SQL.EXECUTE(cursor_id) ;
DBMS_SQL.CLOSE_CURSOR(cursor_id);
END IF;

IF watch_rec.prepare2_clause IS NOT NULL THEN
watch_rec.prepare2_clause := REPLACE(watch_rec.prepare2_clause,
'',database_rec.db_link);
cursor_id := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cursor_id,watch_rec.prepare2_clause,DBMS_SQL.V7);
ret_val := DBMS_SQL.EXECUTE(cursor_id) ;
DBMS_SQL.CLOSE_CURSOR(cursor_id);
END IF;

c_get_data := DBMS_SQL.OPEN_CURSOR ;
DBMS_SQL.PARSE(c_get_data,sql_stmt,DBMS_SQL.V7) ;
ret_val := DBMS_SQL.EXECUTE(c_get_data) ;
DBMS_SQL.CLOSE_CURSOR(c_get_data) ;

IF watch_rec.close1_clause IS NOT NULL THEN
cursor_id := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cursor_id,watch_rec.close1_clause,DBMS_SQL.V7);
ret_val := DBMS_SQL.EXECUTE(cursor_id) ;
DBMS_SQL.CLOSE_CURSOR(cursor_id);
END IF;

IF watch_rec.close2_clause IS NOT NULL THEN
cursor_id := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cursor_id,watch_rec.close2_clause,DBMS_SQL.V7);
ret_val := DBMS_SQL.EXECUTE(cursor_id) ;
DBMS_SQL.CLOSE_CURSOR(cursor_id);
END IF;

END IF;
END LOOP;
END ;
/

Script 5 : Output Report Script
REM
REM Name: eoutput.sql
REM Description: Script to generate output report
REM Usage: Run from SQL*Plus, Use Eagle Oracle account
REM sqlplus eagle/eagle

SPOOL &1
COLUMN NAME FORMAT A26 HEADING 'WatchName'
COLUMN PARAMETER FORMAT A28 HEADING 'Parameter Name'
COLUMN WEEK4 FORMAT 999 HEADING '4wkAgo'
COLUMN WEEK3 FORMAT 999 HEADING '3wkAgo'
COLUMN WEEK2 FORMAT 999 HEADING '2wkAgo'
COLUMN WEEK1 FORMAT 999 HEADING '1wkAgo'
COLUMN DAYS6 FORMAT 999 HEADING '6dyAgo'
COLUMN DAYS5 FORMAT 999 HEADING '5dyAgo'
COLUMN DAYS4 FORMAT 999 HEADING '4dyAgo'
COLUMN DAYS3 FORMAT 999 HEADING '3dyAgo'
COLUMN DAYS2 FORMAT 999 HEADING '2dyAgo'
COLUMN DAYS1 FORMAT 999 HEADING '1dyAgo'
COLUMN TODAY FORMAT 999 HEADING 'Tdy'
COLUMN CHANGE FORMAT 999 HEADING 'Chg'
rem
SET PAGESIZE 60 LINESIZE 130
SET TERMOUT OFF ECHO OFF FEEDBACK OFF
rem
rem
BREAK ON DB_ID NODUP SKIP 2 ON NAME NODUP SKIP 1
rem TTITLE CENTER 'Eagle Output' SKIP 2

SELECT DB_ID,
WATCH.NAME,
PARAMETER,
MAX(DECODE(TRUNC(RUN_TIME),TRUNC(SYSDATE-28),VALUE,0)) WEEK4,
MAX(DECODE(TRUNC(RUN_TIME),TRUNC(SYSDATE-21),VALUE,0)) WEEK3,
MAX(DECODE(TRUNC(RUN_TIME),TRUNC(SYSDATE-14),VALUE,0)) WEEK2,
MAX(DECODE(TRUNC(RUN_TIME),TRUNC(SYSDATE-7),VALUE,0)) WEEK1,
MAX(DECODE(TRUNC(RUN_TIME),TRUNC(SYSDATE-6),VALUE,0)) DAYS6,
MAX(DECODE(TRUNC(RUN_TIME),TRUNC(SYSDATE-5),VALUE,0)) DAYS5,
MAX(DECODE(TRUNC(RUN_TIME),TRUNC(SYSDATE-4),VALUE,0)) DAYS4,
MAX(DECODE(TRUNC(RUN_TIME),TRUNC(SYSDATE-3),VALUE,0)) DAYS3,
MAX(DECODE(TRUNC(RUN_TIME),TRUNC(SYSDATE-2),VALUE,0)) DAYS2,
MAX(DECODE(TRUNC(RUN_TIME),TRUNC(SYSDATE-1),VALUE,0)) DAYS1,
MAX(DECODE(TRUNC(RUN_TIME),TRUNC(SYSDATE),VALUE,0)) TODAY,
MAX(DECODE(TRUNC(RUN_TIME),TRUNC(SYSDATE),VALUE,0)) -
MAX(DECODE(TRUNC(RUN_TIME), TRUNC(SYSDATE-28),VALUE,0)) CHANGE
FROM DB_WATCH_RESULT ,WATCH
WHERE WATCH.WATCH_ID = DB_WATCH_RESULT.WATCH_ID
GROUP BY DB_ID,WATCH.NAME,PARAMETER
ORDER BY DB_ID, WATCH.NAME,PARAMETER;
REM
SET NEWPAGE 0
REM
EXIT

Script 6 : Unix Shell Script
# Name: eagle.sh
# Description: Unix shell script to run PL/SQL procudure and generate report
# Set ORACLE_HOME and ORACLE_SID in this script
# eoutput.sql is in home directory
# Output file also created in home directory
#
REPORTDIR=$HOME
REPORTFILE=$REPORTDIR/eagle`date "+%m%d"`.out
touch $REPORTFILE
sqlplus -s eagle/eagle <