creating awr reports manually

When working with the third parties on the same database there happens some confusion. The operational dba 's are responsible from the database performance and other standard operational duties like backup, security, architecture .. and so on. Vendor dba 's are also responsible to achieve SLA contract requirements and promised performance values. Both side should be causious not to overlap the other side 's duties. Here i tried to answer one of the requests of our vendor dba by supplying hourly AWR reports directly in their directories on the UNIX environment. I think they can follow database activitiy better from now on.


### creating the awr report ###
ORACLE_HOME=/oracle/product/10.2.0; export ORACLE_HOME
ORACLE_SID=montc1; export ORACLE_SID
PATH=$PATH:$ORACLE_HOME/bin; export PATH

$ORACLE_HOME/bin/sqlplus "/ as sysdba" << EOF
declare
i_begin_snap_id number;
i_end_snap_id number;
i_db_id number;
i_inst_num number;
begin

--select the db_id and the instance_number of the instance,
--these values are going to be used to create the awr_report
select DBID, INSTANCE_NUMBER into i_db_id, i_inst_num from dba_hist_database_instance where rownum=1;

--find the snap_id of the time being
select SNAP_ID into i_end_snap_id from dba_hist_snapshot where trunc(end_interval_time,'HH24') = trunc(sysdate,'HH24') order by SNAP_ID desc;

--set the begin snap_id of the previous hour as we are generation hourly awr reports
i_begin_snap_id := i_end_snap_id - 1;

--generate and write the report to a table
insert into avea_sysmon.temp_awr_output select output from table (SYS.DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(i_db_id, i_inst_num, i_begin_snap_id, i_end_snap_id));
commit;

end;
/
EOF

### creating report name ###
report_name=/moneta_home/avea_data/awr/awr_report_`date | awk '{print $3"_"$2"_"$6"_"$4}'`.html
export report_name

### spooling previously created report to the file specified ###
$ORACLE_HOME/bin/sqlplus -s "/ as sysdba" << EOF
set linesize 1500;
set echo off;
set heading off;
set termout on;

spool $report_name;
select s_output from avea_sysmon.temp_awr_output;
truncate table avea_sysmon.temp_awr_output;
spool off;
EOF

### delete the older reports ###
ctl=`ls -l /moneta_home/avea_data/awr | grep .html | wc -l`
if [ $ctl -gt 110 ]
then
ls -l /moneta_home/avea_data/awr | grep .html | head -10 | awk '{print "/moneta_home/avea_data/awr/"$9}' | xargs rm
#else
#echo "dont delete anything"
fi


After i tested the script activity of the script, i used crontab to schedule this script as a job.

$ crontab -l
00,10,20,30,40,50 * * * * /oracle/epeker/check_arch.sh > /oracle/epeker/check_arch.log 2>&1
20 * * * * /oracle/awr_reports/get_awr.sh > /oracle/awr_reports/get_awr.log 2>&1

The awr reports of the specified time are now available on application directories.

$ ls -lrt /moneta_home/avea_data/awr
total 1053328
-rw-r--r--   1 oracle   dba      5229793 Nov  6 14:20 awr_report_6_Nov_2008_14:20:45.html
-rw-r--r--   1 oracle   dba      4911565 Nov  6 15:20 awr_report_6_Nov_2008_15:20:08.html
-rw-r--r--   1 oracle   dba      4865032 Nov  6 16:20 awr_report_6_Nov_2008_16:20:12.html
-rw-r--r--   1 oracle   dba      6112427 Nov  6 17:20 awr_report_6_Nov_2008_17:20:06.html
-rw-r--r--   1 oracle   dba      5057170 Nov  6 18:20 awr_report_6_Nov_2008_18:20:03.html
-rw-r--r--   1 oracle   dba      5031651 Nov  6 19:20 awr_report_6_Nov_2008_19:20:02.html
-rw-r--r--   1 oracle   dba      4798984 Nov  6 20:20 awr_report_6_Nov_2008_20:20:03.html
-rw-r--r--   1 oracle   dba      5016641 Nov  6 21:20 awr_report_6_Nov_2008_21:20:02.html
-rw-r--r--   1 oracle   dba      4895053 Nov  6 22:20 awr_report_6_Nov_2008_22:20:05.html
-rw-r--r--   1 oracle   dba      5228292 Nov  6 23:20 awr_report_6_Nov_2008_23:20:09.html
-rw-r--r--   1 oracle   dba      5018142 Nov  7 00:20 awr_report_7_Nov_2008_00:20:03.html
-rw-r--r--   1 oracle   dba      5072180 Nov  7 01:20 awr_report_7_Nov_2008_01:20:10.html

Time to start securing the listeners

I decided to apply basic security issues to all of my databases listener processes. As always i prefer Oracle Documentation for a reliable reference, this time i found a detailed white paper on integrigy web site which has a lot of hits from external links.

Both two resources call attention for two most important issues. Setting a password and Logging for the listener. The other issues can be taken in account are securing lsnrctl and tnslsnr executables in $ORACLE_HOME/bin/ directory, setting ADMIN_RESTRICTIONS parameter of the listener itself, changing default port of the listener utility and so on mentioned in the referenced documents. But here i decided to secure most common tasks of setting a password for listener and logging the listener activity for my initial action.

Setting a listener password:

-bash-3.00$ lsnrctl

LSNRCTL for Linux: Version 10.2.0.3.0 - Production on 03-NOV-2008 14:06:01

Copyright (c) 1991, 2006, Oracle.  All rights reserved.

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> CHANGE_PASSWORD
Old password: <if not set then just hit enter>
New password: 
Reenter new password: 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=foa-op)(PORT=1521)))
Password changed for LISTENER
The command completed successfully
LSNRCTL> SAVE_CONFIG
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=foa-op)(PORT=1521)))
Saved LISTENER configuration parameters.
Listener Parameter File   /oracle/product/10.2.0/OFADB/network/admin/listener.ora
Old Parameter File   /oracle/product/10.2.0/OFADB/network/admin/listener.bak
The command completed successfully
LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=foa-op)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.3.0 - Production
Start Date                03-NOV-2008 11:12:23
Uptime                    0 days 2 hr. 54 min. 43 sec
Trace Level               off
Security                  ON: Password or Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/product/10.2.0/OFADB/network/admin/listener.ora
Listener Log File         /oracle/product/10.2.0/OFADB/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=foa-op)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary...
Service "OFADB" has 1 instance(s).
  Instance "OFADB", status READY, has 1 handler(s) for this service...
Service "OFADBXDB" has 1 instance(s).
  Instance "OFADB", status READY, has 1 handler(s) for this service...
Service "OFADB_XPT" has 1 instance(s).
  Instance "OFADB", status READY, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL> exit

Logging the listener:

-bash-3.00$ lsnrctl

LSNRCTL for Linux: Version 10.2.0.3.0 - Production on 03-NOV-2008 11:18:43

Copyright (c) 1991, 2006, Oracle.  All rights reserved.

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> SET LOG_DIRECTORY /oracle/product/10.2.0/OFADB/network/admin/lsnr_log
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=foa-op)(PORT=1521)))
LISTENER parameter "log_directory" set to /oracle/product/10.2.0/OFADB/network/admin/lsnr_log
The command completed successfully
LSNRCTL> set log_file lsnr.log
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=foa-op)(PORT=1521)))
LISTENER parameter "log_file" set to lsnr.log
The command completed successfully
LSNRCTL> set log_status on
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=foa-op)(PORT=1521)))
LISTENER parameter "log_status" set to ON
The command completed successfully
LSNRCTL> SAVE_CONFIG
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=foa-op)(PORT=1521)))
Saved LISTENER configuration parameters.
Listener Parameter File   /oracle/product/10.2.0/OFADB/network/admin/listener.ora
Old Parameter File   /oracle/product/10.2.0/OFADB/network/admin/listener.bak
The command completed successfully
LSNRCTL> exit
-bash-3.00$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.3.0 - Production on 03-NOV-2008 11:20:08

Copyright (c) 1991, 2006, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=foa-op)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.3.0 - Production
Start Date                03-NOV-2008 11:12:23
Uptime                    0 days 0 hr. 7 min. 44 sec
Trace Level               off
Security                  ON: Password or Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/product/10.2.0/OFADB/network/admin/listener.ora
Listener Log File         /oracle/product/10.2.0/OFADB/network/admin/lsnr_log/lsnr.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=foa-op)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary...
Service "OFADB" has 1 instance(s).
  Instance "OFADB", status READY, has 1 handler(s) for this service...
Service "OFADBXDB" has 1 instance(s).
  Instance "OFADB", status READY, has 1 handler(s) for this service...
Service "OFADB_XPT" has 1 instance(s).
  Instance "OFADB", status READY, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

But while i was monitoring the log file of the listener i found some warning messages right before every server process created by listener. The warning is something like;

WARNING: Subscription for node down event still pending

There are some comments on forums for this warning message but the general sentence is setting the

SUBSCRIBE_FOR_NODE_DOWN_EVENT_<listener_name>

parameter to OFF in listener.ora file. By disabling this option we prevent listener to automatically registers itself to a notification service as it is unsuccessful to do because there is not one installed. Setting this parameter to OFF is worked for my environment and warning messages are cleared. Following you can find my final listener.ora file.

-bash-3.00$ cat listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /oracle/product/10.2.0/OFADB)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = xxx-xx)(PORT = xxxx))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )

#----ADDED BY TNSLSNR 03-NOV-2008 11:08:38---
PASSWORDS_LISTENER = xxxxxxxxxxxxxxxx
LOGGING_LISTENER = ON
#--------------------------------------------

#----ADDED BY TNSLSNR 03-NOV-2008 11:19:57---
LOG_FILE_LISTENER = lsnr.log
LOG_DIRECTORY_LISTENER = /oracle/product/10.2.0/OFADB/network/admin/lsnr_log
#--------------------------------------------

SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF

Another handy operation of setting the password for the listener is remote administration option. The following demonstration is from my client computer to administer the server listener remotely. In a nutshell if you now the ip and port that the listeners runs on and also know the password, then you can remotely administer you listener.

Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\>lsnrctl status 10.4.38.32:1521

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 03-NOV-2008 14:36:02

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=10.4.38.32))(ADDRESS=(PRO
TOCOL=TCP)(HOST=10.4.38.32)(PORT=1521)))
TNS-01169: The listener has not recognized the password
 TNS-01189: The listener could not authenticate the user

C:\>lsnrctl

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 03-NOV-2008 14:36:05

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> set password
Password:
The command completed successfully
LSNRCTL> status 10.4.38.32:1521
Connecting to (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=10.4.38.32))(ADDRESS=(PRO
TOCOL=TCP)(HOST=10.4.38.32)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.3.0 - Production
Start Date                03-NOV-2008 11:12:23
Uptime                    0 days 3 hr. 24 min. 0 sec
Trace Level               off
Security                  ON: Password or Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/product/10.2.0/OFADB/network/admin/listener.ora
Listener Log File         /oracle/product/10.2.0/OFADB/network/admin/lsnr_log/lsnr.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=foa-op)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary...
Service "OFADB" has 1 instance(s).
  Instance "OFADB", status READY, has 1 handler(s) for this service...
Service "OFADBXDB" has 1 instance(s).
  Instance "OFADB", status READY, has 1 handler(s) for this service...
Service "OFADB_XPT" has 1 instance(s).
  Instance "OFADB", status READY, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL>