In this post and some upcoming posts, I’m going to write more about Oracle application servers, a subject we have addressed too little on the Pythian blog.

In this post, I am addressing how to bounce a whole application server, including all tiers and databases from one location. The reason being, I have a request from a client to have the application server be bounced automatically during the weekend to release swaps and to address memory leaks. The application server on this client includes one Mid tier, one Infra tier, and one database (a metarepository database) in three different Oracle homes on two boxes.

As you know, for application server, tiers should be stopped and started up in a specific order. On startup, the sequence is like: database –> listener –> Infra tier –> Mid tier.

For shutdown, the sequence is vice versa. It is not safe to shutdown the database or Infra tier before making sure that the Mid tier is totally done.

So, in order to address client’s request, the basic plan was to have a script to shutdown Mid tier, then handshake with a tscript on the Infra tier to let it know that it is safe to shutdown the Infra tier. We would use the same approach for the Infra shutdown script and database shutdown script.

Is there a simpler way?

opmnctl, the main tool for startup/shutdown of application server components, is able to bounce the whole farm. However, first time you try to run opmnctl status @farm, you may just see the status of AS component only for single box rather than for the whole farm. Why?

(more…)

I just want to raise a warning flag for DBAs using RMAN and flash recovery area in Oracle 10g.

An RMAN script is usually something like:

backup database plus archivelog

However, the behavior of plus archivelog depends on whether optimization is enabled or disabled in RMAN. The RMAN reference document says that when the plus archivelog command is part of a backup script, RMAN:

1. runs an ALTER SYSTEM ARCHIVE LOG CURRENT statement.
2. runs the BACKUP ARCHIVELOG ALL command. Note that if backup optimization is enabled, RMAN only backs up logs that have not yet been backed up.
3. backs up the files specified in the BACKUP command.
4. runs an ALTER SYSTEM ARCHIVE LOG CURRENT statement.
5. backs up any remaining archived redo logs

As the result, if backup optimization is off (which it is by default in RMAN), plus archivelogs will backup all archivelogs. If the archivelog destination is a flash recovery area, your database may keep very old archivelogs (assuming space is not an issue). Thus, the backup size will gradually grow and fill up the flash recovery area, since each backup includes all archivelogs in the flash recovery area.

The lesson is, to avoid backing up archivelogs that have already been backed up when using plus archivelog in a backup script, make sure you enable RMAN optimization.

You can also find more details at http://oradbatips.blogspot.com/. Hope this helps.

I noticed sufficient swap/paging on AIX boxes for a Pythian client. (PAGING column in topas result). When I was investigating more about this issue, I found helpful document in Vivek Sharma ’s blog and metalink note 316533.1.

Here is excerpt from metalink: The default values for the AIX VMM are generally not appropriate for use with relational databases.

Basically metalink recommends setting proper values for minper% and maxper% OR setting lru_file_repage to 0 in order to change AIX default virtual memory management.

I faced a problem yesterday. The Oracle listener crashed each time a connection was made. The OS was WINXP SP2. It goes without saying that this was a testing install.

The event log showed the following error in SYSTEM log and APPS log.

Faulting application TNSLSNR.EXE, version 0.0.0.0, faulting module orantcp9.dll, version 0.0.0.0, fault address 0×00005732

The following items are proposed by metalink to resolve the issue :

1. Setting use_shared_socket=true in HKEY_LOCAL_MACHINE \software\oracle\home0 Note don’t do this casually!!!
2. Removing all 3rd party software for Internet download such as download accelerator.
3. Changing TNSNAMES.ORA so that it explicitly mentions about ORACLE_HOME.

None of above solutions worked for me. In addition, I could not find anything in the listener trace file or listener log which led me to the cause of the problem.

By the way, I checked out that no process was listening on the listener default port. Changing the listener port did not change anything.

Finally I found the following magic command which fixed the issue after running and restarting the box:

netsh winsock reset catalog

Simple explanation, taken from Ramesh’s Troubleshooting Windows site:

Most of the Internet connectivity problems arise out of corrupt Winsock settings. Windows sockets settings may get corrupted due to the installation of a networking software, or perhaps due to Malware infestation. You will be able connect to the Internet, but the packets won’t transfer back and forth. And errors such as Page cannot be displayed may occur when using Internet Explorer.

This command resets the Winsock catalog to the default configuration. This can be useful if a malformed LSP is installed that results in loss of network connectivity. While use of this command can restore network connectivity, it should be used with care because any previously-installed LSPs will need to be re-installed.

(Link to Part 1)

In this second part , I will outline more details of a new STATSPACK methodology. As I mentioned in previous part , the statspack_setting table is a bridge between the user and Oracle’s STATSPACK.

The user can configure STATSPACK by setting the proper value on the statspack_setting table, and the settings will be automatically propagated. In this new methodology the user doesn’t need to have a deep knowledge of STATSPACK. The user need only decide to set up STATSPACK and configure it. The statspack_setting table keeps the STATSPACK configuration for each instance. These configurations determine:

• how often a STATSPACK snap is gathered (the snap_interval field in statspack_setting table)
• how long a STATSPACK snap is kept (the snap_retention_day field in statspack_setting table)
• how often old snaps are cleaned up. (the snap_cleanup_interval field in statspack_setting)

The statspack_setting table has a trigger that fires before any DML (Insert/Update/Delete). This trigger synchronizes any changes in the table to the relevant STATSPACK gathering/cleanup jobs, and its action is transparent to the user configuring STATSPACK. The trigger takes care of the following:

• avoiding any delete on existing records. (In order to disable STATSPACK, the snap_disable field should be set.)
• allowing STATSPACK configuration only for existing instances. The trigger only allows new records in statspack_setting whose dbid values match the database id.
• any updates on statspack_setting propagates to relevant jobs

I know that this process may raise some questions. Allow me to go through some of them.

What happens if the trigger is dropped or is disabled?

If the trigger is dropped or is disabled, the whole process will not function properly. For proper functionality, not only must the trigger exist, it must be enabled. This led me to implement a monitoring script that runs on daily basis. It reports the trigger status, checks whether STATSPACK jobs are in sync with the configuration in statspack_setting, and checks whether current snaps are gathered and cleaned up as the user has asked for in statspack_setting, generally checking consistency between current STATSPACK snaps and the user’s STATSPACK configuration.

Here is the text of monitoring script.

statspack_monitoring_script.txt

What happens if scheduled STATSPACK jobs are manually dropped?

Manual dropping of a STATSPACK job (either a STATSPACK gathering job or a STATSPACK cleanup job) can be discovered by the monitoring script. If the monitoring script cannot find the relevant job, it will report a warning. In addition, if STATSPACK jobs are broken, an monitoring script will warn us. The combination of these two monitoring facilities guarantees that STATSPACK jobs are alive and functioning well.

Let me provide more scenarios to show how trigger controls the proper functionality of this methodology.

Scenario 1: The user sets a configuration for a remote database.

SQL> select dbid from v$database;

DBID
----------
253241524

SQL> select * from statspack_setting;

DBID SNAP_DISABLE SNAP_INTERVAL SNAP_RETENTION_DAY SNAP_CLEA SNAP_CLEANUP_INTERVAL
---------- ------------ -------------------- ------------------ --------- --------------------------
253241524 0 +00000 01:00:00.0 14 01-SEP-06 +00007 00:00:00.0

STATSPACK has already been configured for the existing database. The following script tries to configure STATSPACK for the remote database.

SQL> insert into statspack_setting values (343643234,0,'0 01:00:00',14,to_date('01-09-06','DD-MM-YY'),'7 00:00:00');

insert into statspack_setting values (343643234,0,'0 01:00:00',14,to_date('01-09-06','DD-MM-YY'),'7
*
ERROR at line 1:
ORA-20000: DBID value does not match with the existing database select * from statspack_setting;

DBID SNAP_DISABLE SNAP_INTERVAL SNAP_RETENTION_DAY SNAP_CLEA SNAP_CLEANUP_INTERVAL
---------- ------------ -------------------- ------------------ --------- --------------------------
253241524 0 +00000 01:00:00.0 14 01-SEP-06 +00007 00:00:00.0

The new configuration is not accepted because DBID does not match the existing database.

Scenario 2: The user would like to remove a STATSPACK configuration

SQL> select * from statspack_setting;

DBID SNAP_DISABLE SNAP_INTERVAL SNAP_RETENTION_DAY SNAP_CLEA SNAP_CLEANUP_INTERVAL
---------- ------------ -------------------- ------------------ --------- --------------------------
253241524 0 +00000 01:00:00.0 14 01-SEP-06 +00007 00:00:00.0

SQL> delete from statspack_setting where dbid=253241524;
delete from statspack_setting where dbid=253241524
*
ERROR at line 1:
ORA-20000: Delete is not allowed
ORA-06512: at "SCOTT.TRG_STATSPACK_SETTING_AIUD", line 11
ORA-04088: error during execution of trigger 'SCOTT.TRG_STATSPACK_SETTING_AIUD'

delete is not permitted by the trigger. truncate on this table cannot be controlled by the trigger, but it can be reported by the monitoring script .

Scenario 3: The user adds more than one configuration for a database.

SQL> select * from statspack_setting;

DBID SNAP_DISABLE SNAP_INTERVAL SNAP_RETENTION_DAY SNAP_CLEA SNAP_CLEANUP_INTERVAL
---------- ------------ -------------------- ------------------ --------- --------------------------
253241524 0 +00000 01:00:00.0 14 01-SEP-06 +00007 00:00:00.0

SQL> insert into statspack_setting values ( 253241524,0,'0 00:15:00',7,to_date('01-09-06','DD-MM-YY'),'7 00:00:00');

ERROR at line 1:
ORA-00001: unique constraint (SCOTT.PKC_STATSPACK_SETTING) violated

The primary key on DBID guarantees that only one configuration exists for each database.

Scenario 4: The user rolls back a STATSPACK configuration.

SQL> select * from dba_jobs;

no rows selected

SQL> select * from statspack_setting;

no rows selected

SQL> insert into statspack_setting values (253241524,0,'0 01:00:00',14,to_date('01-09-06','DD-MM-YY'),'07 00:00:00');

1 row created.

SQL>select job,LAST_DATE,NEXT_DATE,interval,WHAT from dba_jobs;

JOB LAST_DATE NEXT_DATE INTERVAL WHAT
---------- ------------------- ------------------- ---------------------------------------- --------
27 27-08-2006 21:20:04 trunc(sysdate,'MI')+60/(48*30) CRE_SNAP;
28 01-09-2006 00:00:00 trunc(sysdate,'MI')+10080/(48*30) RM_SNAP(14);

The scheduled job was automatically submitted according to the configuration. These jobs are visible only to the current session and are not active until the end of the transaction.

SQL> rollback;

Rollback complete.

SQL> select * from statspack_setting;

no rows selected

SQL> select * from dba_jobs;

no rows selected

Scheduled STATSPACK jobs are rolled back when the STATSPACK configuration is not committed. This scenario shows that no sign of the scheduled job remains when the user rolls back the configuration setting.

Proper statspack cleanup

I found that some of our clients have a scheduled job for STATSPACK cleanup. That’s good, but it’s unfortunate that their cleanup script deletes from stats$snapshot only those records that are older than retention days, because this way of cleanup does not clean up all relevant data of old snaps, causing PERFSTAT default tablespace to fill up after a while.

If you look carefully at the the PERFSTAT schema, you will find that most tables in this schema are children of the stats$snapshot table with the cascade on delete option. But there are some tables which have no dependency on the stats$snapshot table.

These tables are not cleaned up when delete occurs only on stats$snapshot. Here is the listing of this table.

SQL>select table_name from dba_tables where owner='PERFSTAT' and table_name!='STATS$SNAPSHOT'
minus
select table_name
from dba_constraints
where
R_CONSTRAINT_NAME=(select constraint_name from dba_constraints where owner='PERFSTAT' and table_name='STATS$SNAPSHOT' and constraint_type='P')
and
r_owner='PERFSTAT'
and
delete_rule='CASCADE';

STATS$DATABASE_INSTANCE
STATS$IDLE_EVENT
STATS$LEVEL_DESCRIPTION
STATS$SEG_STAT_OBJ
STATS$SQLTEXT
STATS$SQL_PLAN
STATS$STATSPACK_PARAMETER
STATS$UNDOSTAT

In order to get rid of all old snaps and related data, cleanup of STATSPACK should be more than a simple delete on stats$snapshot. The contents of all of the above tables should be examined for any old snaps. I use the following stored procedure to cleanup STATSPACK. (Also, Oracle provides $ORACLE_HOME/rdbms/admin/sppurge.sql for STATSPACK cleanup.)

STATSPACK Cleanup Stored Procedure

I would like to hear your thoughts on this new approach to STATSPACK, so please leave a comment. I hope you find it useful.

Recently, I was doing some performance analysis for one of our clients, using STATSPACK and found that they were not using a consistent method for control. STATSPACK was installed on most instances but some instances had scheduled cron jobs for STATSPACK gathering, while others had dba_jobs. In addition, there was no cleanup implemented for some of the instances.

This situation led me to think about finding a consistent, integrated and simple way of setting up and configuring STATSPACK.

After reviewing the different options available, I’ve settled on a preferred methodology that I’ll be outlining in this post. I’ve used this strategy on several of the existing instances and the client is happy with the result.

In this methodology , all statspack settings are handled via the STATSPACK_SETTING table. Actually, STATSPACK_SETTING is the only gateway for any STATSPACK configuration. This table contains all configuration settings for STATSPACK. In order to make any changes on a STATSPACK configuration or in order to disable/enable a STATSPACK configuration, a user can simply update STATSPACK_SETTING. Changes are automatically propagated to the relevant jobs.

STATSPACK_SETTING has the following structure:

create table statspack_setting(
dbid number not null,
snap_disable number(1) default 0 not null,
snap_interval INTERVAL DAY(5) to second (1) not null,
snap_retention_day number(3) not null,
snap_cleanup_scheduled_time DATE not null,
snap_cleanup_interval INTERVAL DAY(5) to second (1) not null );

ALTER TABLE STATSPACK_SETTING ADD (CONSTRAINT PKC_STATSPACK_SETTING PRIMARY KEY(DBID));

Each configuration instance has only 1 record on this table. (Delete can not be run on an existing record). This record has all the configuration settings for a specified instance.

• snap_disable determines whether or STATSPACK should be gathered. (snap_disable=1, No STATSPACK gathering).
• snap_interval determines how often STATSPACK Should be gathered.
• snap_retention_day determines how long snaps should be kept.
• snap_cleanup_scheduled_time and snap_cleanup_interval determine scheduled time for first cleanup run and frequency of running cleanup.

That’s all we require for STATSPACK. As I mentioned before, any changes on this table will update the job accordingly. Allow me to provide some scenarios to see how the framework simplifies things:

Scenario 1: Enable STATSPACK on database with dbid=3259669668. STATSPACK gathers snapsevery hour and keeps snaps for a month . STATSPACK cleanup runs at 23:00 every sunday

Solution 1 :
insert into statspack_Setting values(3259669668,0,'0 01:00:00',30,to_date('08/06/06 23:00:00','MM/DD/YY HH24:MI:SS'),'7 00:00:00');

Scenario 2: Disable STATSPACK gathering.

Solution 2:
update statspack_setting set snap_disable=1 where dbid=3259669668;

Scenario 3: Increase STATSPACK frequency (every 15 minutes) to have more detailed information

Solution 3:
update statspack_setting set snap_interval='0 00:15:00' where dbid=3259669668;

In the following parts, I will explain:

• how this methodology works
• how to handle consistency between statspack_setting and scheduled job
• how to properly cleanup STATSPACK
• some more complex scenarios

New STATSPACK methodology script.