My Oracle New Features Series on OTN

Some people have emailed me that they can't find my Oracle 11g New Features for DBAs and Developers on Oracle Technology Network anymore.

OTN has apparently done some cleanup, defragmentation and coalescing and as a result a lot of URLs have moved around, including those for articles of mine. The new URL for my Oracle 11g NF series is http://www.oracle.com/technetwork/articles/sql/index-099021.html

Just in case you are looking for my Oracle 10g New Features for DBAs and Developers series (including the 10gR2 addendum), here it is http://www.oracle.com/technetwork/articles/sql/index-082320.html.

While on the subject of changed URLs, in case you are wondering about my other series on OTN - Project Lockdown - that is a four part series on Oracle Security, here it is http://www.oracle.com/technetwork/articles/index-087388.html

Hope you find them useful.

Tool to Add Range Partitions Automatically to a Table

Continuing on my earlier posts on partitioning, here are is another tool on this topic. If you have been using partitioned tables, you might have noticed that one of the thorny issues in partitioning is the need to create partitions. If you don't have the partition ready, the inserts will fail with the following message

ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition

This error is pretty disastrous to applications as they essentially stop functioning, causing outage, until you add the necessary partition. What can you do to prevent it? Maintain a unblinking vigil, of course, which may quickly become impractical. Oracle 11g has a nice answer - the INTERVAL partitioning, which automatically creates partitions when the new records come in. What if you are on 10g or do not want to use INTERVAL partitioning for some reason? Is there a way to simulate the interval partitioning?

After getting frustrated with the lack of proper partitions more than once, I decided to develop this tool. I perfected the tool over several months to make it more generic. Here it is. Enjoy.

Important: This is provided for educational purpose only. Use it at your own risk. There is no implied or expressed warranty and the author assumes no responsibility for any adverse effect.

What the Tool does

The tool is implemented as a stored procedure. When executed, it reads a driver table called partition_create_schedule, which records the tables to which the partitions are to be added. The table is created as shown below:

/* ---------------------------------------------------------------------
Name        : partition_create_schedule
Purpose     : To store the retention period of partitions of
            : indivisual tables. This data is used by partition_drop
            : procedure to automatically drop partitions.
----------------------------------------------------------------------*/

create table partition_create_schedule
(
 OWNER                  VARCHAR2(30),
 TABLE_NAME             VARCHAR2(30),
 PARTITION_UNIT         VARCHAR2(15),
 NUM_PARTS_TO_CREATE    NUMBER(4)
)
/
comment on table partition_create_schedule is 'To record the tables for which partitions are to be added automatically'
/
comment on  column partition_create_schedule.owner is 'the owner of the table'
/
comment on  column partition_create_schedule.table_name is 'the name of the table'
/
comment on  column partition_create_schedule.PARTITION_UNIT is 'the partitioning scheme - MONTHLY/DAILY/WEEKELY/QUARTERLY/YEARLY'
/
comment on column partition_create_schedule.num_parts_to_create is 'the number of units to skip in creating the partition. For instance, if you want to create the next partition 3 days from now for a DAILY unit, place 3 here'
/

The partitioning unit is important. Remember, in date-range partitioned tables, you merely provide the range; not whether the range is monthly, weekly or something like that. Even if you have a somewhat similar range, there is no guarantee that it will remain so. You may decide to split some partition or coalesce two. Therefore it is important that you tell the tool what type of partitioning duration the table is under.

Let's insert some records:

insert into partition_create_schedule values ('ARUP','PTEST1','MONTHLY',1);
insert into partition_create_schedule values ('ARUP','PTEST2','MONTHLY',1);
insert into partition_create_schedule values ('ARUP','PTEST3','QUARTERLY',1);
insert into partition_create_schedule values ('ARUP','PTEST4','DAILY',1);
commit;

Now comes the tool - the procedure. To build it, I considered these objectives:

(1) The intervals could be daily, weekly, monthly, quarterly and yearly
(2) When this procedure executes, it should automatically compute the boundary of the partition to add from the existing partitions. This should not be expected to be input from the user.
(3) This procedure will be executed every day automatically. In case of a DAILY partition, a new partition will be created; but not for other ranges. For weekly ranges, the new partition should be created only on the first day of the week; for monthly, on the first day of the month, etc.
(4) The global indexes must be updated automatically
(5) It must handle the two possibilities of the existing highest partition, e.g. a partition with a distinct boundary value and one with (MAXVALUE) clause. In the former case, a partition is added. In the latter case, the maxvalue partition has to be "split"; not added. The procedure must take care of both cases automatically.
(6) When the partition is split, the local indexes get invalidated and must be rebuilt.
(7) It should send an email to the DBA in both success and failure.

The tool handles all the above objectives pretty well.

CREATE OR REPLACE procedure SYS.partition_create
as
 cursor sched_cur is
  select * 
  from partition_create_schedule
  ;
 l_sched_rec    sched_cur%rowtype;
 l_cutoff_date   date;
 l_hvc     varchar2(2000);
 l_new_hvc    varchar2(2000);
 l_old_hvc    varchar2(2000);
 l_part_name    varchar2(2000);
 l_new_part_name   varchar2(2000);
 l_table_name   varchar2(2000);
 l_hv     date;
 l_new_hv    date;
 l_sql     varchar2(2000);
 l_full     varchar2(32000);
 l_ts     varchar2(2000);
 l_errm     varchar2(2000);
 l_mail_body    varchar2(2000);
 l_out     varchar2(2000);
 l_recipients   varchar2(2000) :=
  'dba.ops@mycompany.com';
 errc     number(10);
 l_place     varchar2(2);
 l_exec_flag    varchar2(4);
 invalid_partition_unit exception;
begin
 execute immediate 'alter session set smtp_out_server = ''smtp.mycompany.com''';
 l_out :=
  rpad('Owner',10)||
  rpad('Table',30)||
  rpad('Part Unit',10)||
  rpad('Old HV',11)||
  rpad('New HV',11)||
  rpad('Exec',4)
  ;
 dbms_output.put_line(l_out);
 l_out :=
  rpad('-',9,'-')||' '||
  rpad('-',29,'-')||' '||
  rpad('-',9,'-')||' '||
  rpad('-',10,'-')||' '||
  rpad('-',10,'-')||' '||
  rpad('-',3,'-')
  ;
 dbms_output.put_line(l_out);
 open sched_cur;
 loop --{
  fetch sched_cur into l_sched_rec;
  exit when sched_cur%notfound;
  select high_value, tablespace_name
  into l_old_hvc, l_ts
  from dba_tab_partitions tp
  where partition_position = (
    select max(partition_position) from dba_tab_partitions
    where table_name = tp.table_name
    and table_owner = tp.table_owner
  )
  and table_name = l_sched_rec.table_name
  and table_owner = l_sched_rec.owner;
  if (l_old_hvc = 'MAXVALUE') then --{
   -- PMAX code. Split the partition
   -- first get the hign value of the partition just prior to PMAX
   select high_value, tablespace_name
   into l_hvc, l_ts
   from dba_tab_partitions tp
   where partition_position = (
     select max(partition_position) - 1 from dba_tab_partitions
     where table_name = tp.table_name
     and table_owner = tp.table_owner
   )
   and table_name = l_sched_rec.table_name
   and table_owner = l_sched_rec.owner;
   execute immediate 'select '||l_hvc||' from dual' into l_hv;
   l_exec_flag := 'NO';
   case l_sched_rec.partition_unit  --{
    when 'DAILY' then
     l_new_hv := l_hv + l_sched_rec.num_parts_to_create;
     l_new_part_name := 'Y'||to_char(l_new_hv,'YY')||'M'||to_char(l_new_hv,'MM')||'D'||to_char(l_new_hv,'DD');
     l_exec_flag := 'YES';
    when 'WEEKLY' then
     l_new_hv := l_hv + (7*l_sched_rec.num_parts_to_create);
     l_new_part_name := 'Y'||to_char(l_new_hv,'YY')||'W'||to_char(l_new_hv,'WW');
     if (to_char(sysdate,'d') = '1') then
      l_exec_flag := 'YES';
     end if;
    when 'MONTHLY' then
     l_new_hv := add_months(l_hv,l_sched_rec.num_parts_to_create);
     if (to_char(sysdate,'dd') = '1') then
      l_exec_flag := 'YES';
     end if;
     l_new_part_name := 'Y'||to_char(l_new_hv,'YY')||'M'||to_char(l_new_hv,'MM');
    when 'QUARTERLY' then
     l_new_hv := add_months(l_hv,3*l_sched_rec.num_parts_to_create);
     l_new_part_name := 'Y'||to_char(l_new_hv,'YY')||'Q'||to_char(l_new_hv,'Q');
     if (to_char(sysdate,'mm/dd') in ('01/01','04/01','07/01','10/01')) then
      l_exec_flag := 'YES';
     end if;
    when 'ANNUALLY' then
     l_new_hv := add_months(l_hv,12*l_sched_rec.num_parts_to_create);
     l_new_part_name := 'Y'||to_char(l_new_hv,'YY');
     if to_char(sysdate,'mm/dd') = '01/01' then
      l_exec_flag := 'YES';
     end if;
    else
     raise invalid_partition_unit; 
   end case; --}
   l_new_hvc := to_char(l_new_hv,'mm/dd/yyyy');
   l_sql := 'alter table '||
    l_sched_rec.owner||'.'||
    l_sched_rec.table_name||
    ' split partition pmax '||
    ' at (to_date('''||l_new_hvc||
    ''',''mm/dd/yyyy''))'||
    ' into ( partition '||l_new_part_name||
    ' tablespace '||l_ts||
    ' initrans 11 storage (freelists 11 freelist groups 7)'||
    ' , partition pmax) update global indexes';
  else --}{
   -- non PMAX code. Add the partition
   l_hvc := l_old_hvc;
   execute immediate 'select '||l_hvc||' from dual' into l_hv;
   l_exec_flag := 'NO';
   case l_sched_rec.partition_unit  --{
    when 'DAILY' then
     l_new_hv := l_hv + l_sched_rec.num_parts_to_create;
     l_new_part_name := 'Y'||to_char(l_new_hv,'YY')||'M'||to_char(l_new_hv,'MM')||'D'||to_char(l_new_hv,'DD');
     l_exec_flag := 'YES';
    when 'WEEKLY' then
     l_new_hv := l_hv + (7*l_sched_rec.num_parts_to_create);
     l_new_part_name := 'Y'||to_char(l_new_hv,'YY')||'W'||to_char(l_new_hv,'WW');
     if (to_char(sysdate,'d') = '1') then
      l_exec_flag := 'YES';
     end if;
    when 'MONTHLY' then
     l_new_hv := add_months(l_hv,l_sched_rec.num_parts_to_create);
     if (to_char(sysdate,'dd') = '1') then
      l_exec_flag := 'YES';
     end if;
     l_new_part_name := 'Y'||to_char(l_new_hv,'YY')||'M'||to_char(l_new_hv,'MM');
    when 'QUARTERLY' then
     l_new_hv := add_months(l_hv,3*l_sched_rec.num_parts_to_create);
     l_new_part_name := 'Y'||to_char(l_new_hv,'YY')||'Q'||to_char(l_new_hv,'Q');
     if (to_char(sysdate,'mm/dd') in ('01/01','04/01','07/01','10/01')) then
      l_exec_flag := 'YES';
     end if;
    when 'ANNUALLY' then
     l_new_hv := add_months(l_hv,12*l_sched_rec.num_parts_to_create);
     l_new_part_name := 'Y'||to_char(l_new_hv,'YY');
     if to_char(sysdate,'mm/dd') = '01/01' then
      l_exec_flag := 'YES';
     end if;
    else
     raise invalid_partition_unit; 
   end case; --}
   l_new_hvc := to_char(l_new_hv,'mm/dd/yyyy');
   l_sql := 'alter table '||
    l_sched_rec.owner||'.'||
    l_sched_rec.table_name||
    ' add partition '||
    l_new_part_name||
    ' values less than '||
    ' (to_date('''||l_new_hvc||
    ''',''mm/dd/yyyy''))'||
    ' tablespace '||l_ts||
    ' initrans 11 storage (freelists 11 freelist groups 7)';
  end if; --}
  l_out :=
   rpad(l_sched_rec.owner,10)||
   rpad(l_sched_rec.table_name,30)||
   rpad(l_sched_rec.partition_unit,10)||
   rpad(to_char(l_hv,'mm/dd/yyyy'),11)||
   rpad(l_new_hvc,11)||
   rpad(l_exec_flag,4)
  ;
  dbms_output.put_line(l_out);
  if (l_exec_flag = 'YES') then --{
   dbms_output.put_line('l_new_part_name='||l_new_part_name);
   dbms_output.put_line('l_new_hvc='||l_new_hvc);
   l_mail_body := 'Statement Executed:'||l_sql;
         dbms_output.put_line('l_mail_body='||l_mail_body);
   begin
    execute immediate l_sql;
             l_mail_body := l_mail_body ||'...OK';
   exception
    when OTHERS then
     l_errm := SQLERRM;
     l_mail_body := l_mail_body||' ERROR='||l_errm;
     raise;
   end;
   -- table partition split. Now index.
   for ind_cur in (
    select index_owner, index_name, partition_name
    from dba_ind_partitions
    where (index_owner, index_name) in (
     select owner, index_name
     from dba_indexes
     where table_name = l_sched_rec.table_name
     and table_owner = l_sched_rec.owner
    )
   and status = 'UNUSABLE'
   ) loop --{
    dbms_output.put_line('Index Part='||ind_cur.index_owner||'.'||ind_cur.index_name||'.'||ind_cur.partition_name);
    l_sql := 'alter index '||
     ind_cur.index_owner||'.'||
     ind_cur.index_name||' rebuild partition '||
     ind_cur.partition_name||' nologging online';
    l_mail_body := l_mail_body||chr(12)||
     'Statement Executed:'||l_sql;
             dbms_output.put_line('l_mail_body='||l_mail_body);
    begin
     execute immediate l_sql;
        l_mail_body := l_mail_body||'...OK';
    exception
     when OTHERS then
         l_errm := SQLERRM;
         l_mail_body := l_mail_body||' ERROR='||l_errm;
      raise;
    end;
   end loop; --}
   -- index partitions made usable
  end if; --}
 end loop; --}
 close sched_cur;
 dbms_output.put_line(l_full);
 utl_mail.send(
  'oracle@prodb1',l_recipients,
  null, null,
  'Succeeded: PROPRD1 Partition Create',
  l_mail_body
 );
exception
 when OTHERS then
  l_errm := SQLERRM;
  utl_mail.send(
   'oracle@prodb1',l_recipients,
   null, null,
   'Failed: PROPRD1 Partition Create',
   l_mail_body
  );
  raise_application_error(-20001,l_errm);
end;
/
show error

The code is self explanatory. Let's see some example outputs:

SQL> @ex
Owner     Table                         Part Unit Old HV     New HV     Exec
--------- ----------------------------- --------- ---------- ---------- ---
ARUP      PTEST1                        MONTHLY   02/01/2010 04/01/2010 NO
ARUP      PTEST2                        MONTHLY   02/01/2010 04/01/2010 NO
ARUP      PTEST3                        QUARTERLY 04/01/2010 10/01/2010 NO
ARUP      PTEST4                        DAILY     01/12/2010 01/14/2010 YES
l_new_part_name=Y10M01D14
l_new_hvc=01/14/2010
l_mail_body=Statement Executed:alter table ARUP.PTEST4 split partition pmax  at
(to_date('01/14/2010','mm/dd/yyyy')) into ( partition Y10M01D14 tablespace
MGMT_TABLESPACE initrans 11 storage (freelists 11 freelist groups 7) , partition
pmax) update global indexes
ARUP      PTEST5                        DAILY     01/04/2010 01/06/2010 YES
l_new_part_name=Y10M01D06
l_new_hvc=01/06/2010
l_mail_body=Statement Executed:alter table ARUP.PTEST5 add partition Y10M01D06
values less than  (to_date('01/06/2010','mm/dd/yyyy')) tablespace
MGMT_TABLESPACE initrans 11 storage (freelists 11 freelist groups 7)
ARUP      PTEST6                        DAILY     01/02/2010 01/04/2010 YES
l_new_part_name=Y10M01D04
l_new_hvc=01/04/2010
l_mail_body=Statement Executed:alter table ARUP.PTEST6 add partition Y10M01D04
values less than  (to_date('01/04/2010','mm/dd/yyyy')) tablespace
MGMT_TABLESPACE initrans 11 storage (freelists 11 freelist groups 7)
ARUP      PTEST7                        DAILY     01/02/2010 01/04/2010 YES
l_new_part_name=Y10M01D04
l_new_hvc=01/04/2010
l_mail_body=Statement Executed:alter table ARUP.PTEST7 add partition Y10M01D04
values less than  (to_date('01/04/2010','mm/dd/yyyy')) tablespace
MGMT_TABLESPACE initrans 11 storage (freelists 11 freelist groups 7)

The output is sent to the mail address recorded in the stored procedure - in case of both success and failure. Now all you have to do is to put it in a Scheduler Job and let it run every day. Populate the table PARTITION_CREATE_SCHEDULE as needed and those tables will be automatically added partitions.

Hope you enjoy and find it useful. As always, I will appreciate your feedback.

Tool to Present a Consolidated View of the Status of RMAN Backups

A very important area of database administration is backups which are essential to the availability of the databases. If you use RMAN to backup databases (as you should be doing), how will you ensure that all the database backups have successfully completed? One way of making sure that occurs is by going into each server and checking the backup log – a task not just tedious but often impractical considering the large number of databases we have. Is there an easy way – via a consolidated output of all databases?

The Tool

Yes, there is an easy way – by checking the catalog, assuming of course that you are using a catalog, as you should be. I developed this tool to extract the information from the catalog for each database. Since the repository may be different for each database being backed up, I used a view to union all the RC_* views from all the repositories on a common schema which I named RMAN_COMMON. Then I wrote a PL/SQL program to pull the report presenting the information on all databases together. Since my objective was to have a consolidated view of all the backups, whether they succeeded or not, I selected from multiple views to provide an indented output in case of failures to identify specifically which component failed. I will illustrate with an output shown below. The tool can be customized for number of days it reports. Here I have chosen for the last 2 days.

SQL> conn rman_common/rman_common@catalogdb
Connected.
SQL> @status

********  RMAN Database Backup Report *********

DB Name  Start Time         End Time           Status   Time Tak Output S Type
-------- ------------------ ------------------ -------- -------- -------- -------
DEMO1    2010-11-08 00:25:1 2010-11-08 00:50:4 COMPLETE 00:25:27    17.99 DISK
DEMO1    2010-11-09 00:25:1 2010-11-09 00:50:3 COMPLETE 00:25:16    17.99 DISK
ECR      2010-11-07 23:12:0 2010-11-07 23:14:2 COMPLETE 00:02:13     5.55 DISK
ECR      2010-11-08 23:12:0 2010-11-08 23:14:3 COMPLETE 00:02:21     5.58 DISK
EMREP    2010-11-08 01:00:2 2010-11-08 01:02:5 COMPLETE 00:02:35   326.07 DISK
EMREP    2010-11-09 01:00:2 2010-11-09 01:02:5 COMPLETE 00:02:28   353.71 DISK
PROPRD   2010-11-07 23:00:1 2010-11-08 00:04:0 COMPLETE 01:03:49    50.48 DISK
PROPRD   2010-11-08 00:04:2 2010-11-08 11:47:1 COMPLETE 11:42:42    27.59
PROPRD   2010-11-08 10:35:3 2010-11-08 11:20:4 COMPLETE 00:45:12    30.00 DISK
PROPRD   2010-11-08 11:28:5 2010-11-08 12:21:2 COMPLETE 00:52:33    30.00 DISK
PROPRD   2010-11-08 12:23:5 2010-11-08 12:38:5 COMPLETE 00:15:00    10.00 DISK
PROPRD   2010-11-08 12:43:3 2010-11-08 12:43:4 COMPLETE 00:00:07   192.00 DISK
PROPRD   2010-11-08 12:46:1 2010-11-08 12:46:2 COMPLETE 00:00:07   224.00 DISK
PROPRD   2010-11-08 12:48:1 2010-11-08 13:14:0 COMPLETE 00:25:50    20.00 DISK
PROPRD   2010-11-08 13:37:3 2010-11-08 13:58:4 COMPLETE 00:21:11    15.00 DISK
PROPRD   2010-11-08 14:00:2 2010-11-08 14:13:5 COMPLETE 00:13:30    10.00 DISK
PROPRD   2010-11-08 14:29:0 2010-11-08 14:29:0 FAILED   00:00:01     0.00
. Operation            Input                Status
. -------------------- -------------------- --------------------
. .                    .                    FAILED
.  Level  Status   Operation            Object Type
.  ------ -------- -------------------- --------------------
.  >      COMPLETE RMAN                 .
.  ->     FAILED   BACKUP               DATAFILE INCR
PROPRD   2010-11-08 23:00:2 2010-11-09 00:21:0 COMPLETE 01:20:43    66.75 DISK
PROPRD   2010-11-09 00:21:2 2010-11-09 14:07:3 COMPLETE 13:46:09    40.25
LIGPRD1  2010-11-08 03:00:0 2010-11-08 03:00:4 COMPLETE 00:00:37     1.74 DISK
LIGPRD1  2010-11-08 03:00:5 2010-11-08 03:04:1 COMPLETE 00:03:12     1.49
LIGPRD1  2010-11-09 03:00:0 2010-11-09 03:00:5 COMPLETE 00:00:45     2.59 DISK
LIGPRD1  2010-11-09 03:01:0 2010-11-09 03:05:1 COMPLETE 00:04:05     1.37
LIGPRD2  2010-11-08 02:00:0 2010-11-08 02:00:3 COMPLETE 00:00:29     1.04 DISK
LIGPRD2  2010-11-08 02:00:4 2010-11-08 02:02:3 COMPLETE 00:01:52     1.34
LIGPRD2  2010-11-09 02:00:0 2010-11-09 02:00:4 COMPLETE 00:00:35     1.67 DISK
LIGPRD2  2010-11-09 02:00:5 2010-11-09 02:03:2 COMPLETE 00:02:26     1.38
SW1      2010-11-08 00:05:0 2010-11-08 00:06:1 COMPLETE 00:01:06   519.17 DISK
SW1      2010-11-09 00:05:0 2010-11-09 00:08:0 COMPLETE 00:03:04     2.01 DISK

PL/SQL procedure successfully completed.

Here you can see the databases in the catalog – PROPRD, LIGPRD1, LIGPRD2 and SW1. The columns – “Start Time”, “End Time” and “Time Taken” – are self-explanatory. The “Output Size” shows the size of the backupset produced. The “Status” column shows the status of the job – the key to this report. If it shows “COMPLETE”, then all was well in the job. If it shows “FAILED” then lines below show what actually failed. For instance you can see on 8^th Nov, incremental backup of one datafile of PROPRD failed. That one definitely needs investigating.  You got all that important information in just one report. As you add all the databases into the same catalog, your reports will be more complete and expansive.

Construction

Now that you saw the result of the tool, let’s see the code behind it. First I created a user – RMAN_COMMON:

SQL> grant create session, unlimited tablespace, create view to rman_common identified by rman_common;

We need just three views from the repositories; so this user needs to be granted select privileges on those only. As SYS user, grant these:

grant select on rman_PROPRD.rc_rman_backup_job_details to rman_common;
grant select on rman_LIGPRD11.rc_rman_backup_job_details to rman_common;
grant select on rman_LIGPRD21.rc_rman_backup_job_details to rman_common;
grant select on rman_11g.rc_rman_backup_job_details to rman_common;
--
grant select on rman_PROPRD.rc_rman_backup_subjob_details to rman_common;
grant select on rman_LIGPRD11.rc_rman_backup_subjob_details to rman_common;
grant select on rman_LIGPRD21.rc_rman_backup_subjob_details to rman_common;
grant select on rman_11g.rc_rman_backup_subjob_details to rman_common;
--
grant select on rman_PROPRD.rc_rman_status to rman_common;
grant select on rman_LIGPRD11.rc_rman_status to rman_common;
grant select on rman_LIGPRD21.rc_rman_status to rman_common;
grant select on rman_11g.rc_rman_status to rman_common;

Of course, you will need to customize the above script to add more repositories as you add them.

Next, you will create the views by selecting from the corresponding views from individual repositories. Instead of creating a view with all the columns, I chose only a few columns. This will help us in combining the views from 10g and 11g where the columns could be different.

conn rman_common/rman_common
--
create view rc_rman_backup_job_details
as
select
        db_name,
        start_time,
        end_time,
        status,
        time_taken_display,
        output_bytes_display,
        output_device_type,
        session_key,
        session_recid,
        session_stamp
from rman_PROPRD.rc_rman_backup_job_details
union all
select
        db_name,
        start_time,
        end_time,
        status,
        time_taken_display,
        output_bytes_display,
        output_device_type,
        session_key,
        session_recid,
        session_stamp
from rman_LIGPRD11.rc_rman_backup_job_details
union all
select
        db_name,
        start_time,
        end_time,
        status,
        time_taken_display,
        output_bytes_display,
        output_device_type,
        session_key,
        session_recid,
        session_stamp
from rman_LIGPRD21.rc_rman_backup_job_details
union all
select
        db_name,
        start_time,
        end_time,
        status,
        time_taken_display,
        output_bytes_display,
        output_device_type,
        session_key,
        session_recid,
        session_stamp
from rman_11g.rc_rman_backup_job_details
/
create view rc_rman_backup_subjob_details
as
select
        operation,
        input_type,
        status,
        session_stamp
from rman_PROPRD.rc_rman_backup_subjob_details
union all
select
        operation,
        input_type,
        status,
        session_stamp
from rman_LIGPRD11.rc_rman_backup_subjob_details
union all
select
        operation,
        input_type,
        status,
        input_type,
        status,
        session_stamp
from rman_LIGPRD21.rc_rman_backup_subjob_details
union all
select
        operation,
        input_type,
        status,
        session_stamp
from rman_11g.rc_rman_backup_subjob_details
/
create view rc_rman_status
as
select
        row_level,
        operation,
        object_type,
        status,
        session_key,
        session_recid
from rman_PROPRD.rc_rman_status
union all
select
        row_level,
        operation,
        object_type,
        status,
        session_key,
        session_recid
from rman_LIGPRD11.rc_rman_status
union all
select
        row_level,
        operation,
        object_type,
        status,
        session_key,
        session_recid
        session_key,
        session_recid
from rman_LIGPRD21.rc_rman_status
union all
select
        row_level,
        operation,
        object_type,
        status,
        session_key,
        session_recid
from rman_11g.rc_rman_status
/

Now that the views are in place, you can create the actual tool. Here is the PL/SQL code:

/*---------------------------------------------------------------------------
Name            : RMAN Job Summary Report
Version         : 2.0
Released        : Nov 9th, 2010
Author          : Arup Nanda.
Description     : Creates a report of all backup jobs in a specified number
                  of days. If anything failed, it also shows the sub-job details.
                  Tested on 11g; but will work on 10gR2 as well.
                  Connect to RMAN repository and execute.
                  For number of days, change the l_days constant.
---------------------------------------------------------------------------*/
prompt
prompt ********  RMAN Database Backup Report *********
prompt
set serveroutput on size unlimited
declare
        l_days                  constant number := 2;
        l_place                 varchar2(400);
        l_db_name               rc_rman_backup_job_details.db_name%type;
        l_start_time            varchar2(40);
        l_end_time              varchar2(40);
        l_status                rc_rman_backup_job_details.status%type;
        l_time_taken_display    rc_rman_backup_job_details.time_taken_display%type;
        l_output_bytes_display  rc_rman_backup_job_details.output_bytes_display%type;
        l_output_device_type    rc_rman_backup_job_details.output_device_type%type;
        l_session_key           rc_rman_backup_job_details.session_key%type;
        l_session_recid         rc_rman_backup_job_details.session_recid%type;
        l_session_stamp         rc_rman_backup_job_details.session_stamp%type;

        l_operation             rc_rman_backup_subjob_details.operation%type;
        l_input_type            rc_rman_backup_subjob_details.input_type%type;

        l_command_level         varchar2(9);
        l_object_type           rc_rman_status.object_type%type;
        cursor bjd_cur
        (
                p_days in number
        )
        is
        select
                bjd.db_name,
                to_char(bjd.start_time, 'yyyy-mm-dd hh24:mi:ss'),
                to_char(bjd.end_time, 'yyyy-mm-dd hh24:mi:ss'),
                bjd.status,
                bjd.time_taken_display,
                bjd.output_bytes_display,
                bjd.output_device_type,
                bjd.session_key,
                bjd.session_recid,
                bjd.session_stamp
        from rc_rman_backup_job_details bjd
        where end_time > sysdate - p_days
        order by  bjd.db_name, bjd.start_time;

begin
        l_place := 'Place 100';
        dbms_output.put_line(
                rpad('DB Name',8)
                ||' '||
                rpad('Start Time',18)
                ||' '||
                rpad('End Time',18)
                ||' '||
                rpad('Status',8)
                ||' '||
                rpad('Time Taken',8)
                ||' '||
                rpad('Output Size',8)
                ||' '||
                rpad('Type',8)
        );
        dbms_output.put_line(
                rpad('-',8,'-')
                ||' '||
                rpad('-',18,'-')
                ||' '||
                rpad('-',18,'-')
                ||' '||
                rpad('-',8,'-')
                ||' '||
                rpad('-',8,'-')
                ||' '||
                rpad('-',8,'-')
                ||' '||
                rpad('-',8,'-')
        );
        open bjd_cur (l_days);
        loop
                fetch bjd_cur
                into
                        l_db_name,
                        l_start_time,
                        l_end_time,
                        l_status,
                        l_time_taken_display,
                        l_output_bytes_display,
                        l_output_device_type,
                        l_session_key,
                        l_session_recid,
                        l_session_stamp
                ;
                exit when bjd_cur%notfound;
                dbms_output.put_line(
                        rpad(l_db_name ,8)
                        ||' '||
                        rpad(l_start_time ,18)
                        ||' '||
                        rpad(l_end_time ,18)
                        ||' '||
                        rpad(l_status ,8)
                        ||' '||
                        rpad(l_time_taken_display ,8)
                        ||' '||
                        rpad(l_output_bytes_display ,8)
                        ||' '||
                        rpad(l_output_device_type,8)
                );
                --
                --
                l_place := 'Place 300';
                if (l_status != 'COMPLETED') then
                        for bsjd_rec in (
                        select
                                operation,
                                input_type,
                                status
                        from rc_rman_backup_subjob_details
                        where session_stamp = l_session_stamp
                        ) loop
                        l_place := 'Place 400';
                        dbms_output.put_line(
                                '.'
                                ||' '||
                                rpad('Operation',20)
                                ||' '||
                                rpad('Input',20)
                                ||' '||
                                rpad('Status',20)
                        );
                        dbms_output.put_line(
                                '.'
                                ||' '||
                                rpad('-',20,'-')
                                ||' '||
                                rpad('-',20,'-')
                                ||' '||
                                rpad('-',20,'-')
                        );
                        dbms_output.put_line(
                                '.'
                                ||' '||
                                rpad(nvl(l_operation,'.') ,20)
                                ||' '||
                                rpad(nvl(l_input_type,'.') ,20)
                                ||' '||
                                rpad(nvl(l_status,'.') ,20)
                        );
                        end loop;
                        --
                        l_place := 'Place 500';
                        dbms_output.put_line(
                                '.  '||
                                rpad('Level' ,6)
                                ||' '||
                                rpad('Status' ,8)
                                ||' '||
                                rpad('Operation' ,20)
                                ||' '||
                                rpad('Object Type' ,20)
                        );
                        dbms_output.put_line(
                                '.  '||
                                rpad('-' ,6,'-')
                                ||' '||
                                rpad('-' ,8,'-')
                                ||' '||
                                rpad('-' ,20,'-')
                                ||' '||
                                rpad('-' ,20,'-')
                        );
                        for status_rec in (
                                select
                                        rpad('-', row_level, '-')||'>' command_level,
                                        operation,
                                        object_type,
                                        status
                                from rc_rman_status
                                where session_key = l_session_key
                                order by row_level, session_recid
                        ) loop
                                l_place := 'Place 600';
                                dbms_output.put_line(
                                        '.  '||
                                        rpad(nvl(status_rec.command_level,'.') ,6)
                                        ||' '||
                                        rpad(nvl(status_rec.status,'.') ,8)
                                        ||' '||
                                        rpad(nvl(status_rec.operation,'.') ,20)
                                        ||' '||
                                        rpad(nvl(status_rec.object_type,'.') ,20)
                                );
                        end loop;
                end if;
                end loop;
exception
        when OTHERS then
                dbms_output.put_line(l_place);
                raise;
end;
/

This is the PL/SQL code in the file status.sql.  You can adjust the value of the constant l_days to generate the report for as many days as you want. When you run the script it shows the status of the RMAN jobs in last 2 days.

Hope you find this useful. As always, any feedback will be highly appreciated.

Automatic Range Partition Dropping Tool

You have probably used paritions on your tables, especially period based range partitions on date columns. In some applications older partitions become useless after a specified period of time and you have to drop them. But you have to manually drop them; there is no automatic drop utility. Oracle offers interval partitioning for automatic creation of range partitions; but there is no counterpart in dropping operation. It could be chore, especially when the partitions are defined based on different time intervals on different tables.

I have developed a tool to make this process automatic. The concept of the tool is simple - I use a driver table that stores the preferences such as the partitioning time intervals of individual tables I am interested in and how often then should be purged. The actual tool is a stored procedure that reads this driver table, determines if the oldest partition satisfies the condition of purge and drops it if is it the case. You can put this procedure in a Scheduler job and run it every day. It will compute the partitions automatically is decide on dropping the relevant partitions everyday. It will also update global indexes.

Disclaimer: this is being provided for educational purpose only; use at your own risk. The author assumes absolutely no responsibility for any issues caused by the use of this tool. 

First, I will need the driver table:

/* ---------------------------------------------------------------------
Name : partition_drop_schedule
Purpose : To store the retention period of partitions of 
        : indivisual tables. This data is used by partition_drop
        : procedure to automatically drop partitions.
----------------------------------------------------------------------*/
create table partition_drop_schedule
(

OWNER VARCHAR2(30),
   TABLE_NAME VARCHAR2(30),
   PART_SCHEME VARCHAR2(10),
   RETENTION_DURATION NUMBER(5),
   RETENTION_UNIT VARCHAR2(10)
) 
/
comment on table partition_drop_schedule is 'To store the retention period of partitions of individual tables. This data is used by partition_drop procedure to automatically drop partitions.'

/

comment on column partition_drop_schedule.owner is 'the owner of the table'
/
comment on column partition_drop_schedule.table_name is 'the name of the table'
/
comment on column partition_drop_schedule.part_scheme is 'the partitioning time scheme (DAILY/WEEKLY/MONTHLY/QUARTERLY/HALFYEARLY/YEARLY) of the table'
/
comment on column partition_drop_schedule.RETENTION_DURATION is 'the duration of retention of the partition in number'
/
comment on column partition_drop_schedule.RETENTION_UNIT is 'the unit of duration of retention (DAY/WEEK/MONTH/QUARTER/HALFYEAR/YEAR of the table'
/ 

Here are some sample inserts into the table:

insert into partition_drop_schedule values
(
    'ARUP','TEST','MONTHLY',12,'MONTH'
)
/

commit

/

Next comes the stored procedure:

CREATE OR REPLACE procedure SYS.partition_drop
as
 cursor sched_cur is
  select * 
  from partition_drop_schedule
  ;
 l_sched_rec    sched_cur%rowtype;
 l_cutoff_date   date;
 l_hv     date;
 l_sql     varchar2(2000);
 l_dis_cons_sql   varchar2(2000);
 l_ena_cons_sql   varchar2(2000);
 l_drop_sql    varchar2(2000);
 errm     varchar2(2000);
 l_out     varchar2(2000);
 l_full     varchar2(32000);
 l_recipients   varchar2(2000) :=
  'dba.ops@myowncompany.com';
 errc     number(10);
 l_place     varchar2(2);
 l_cons_disabled   boolean := FALSE;
 type l_varchar2_30_ty is table of varchar2(30);
 l_child_owner    l_varchar2_30_ty;
 l_child_table_name  l_varchar2_30_ty;
 l_constraint_name  l_varchar2_30_ty;
 CURSOR cons_cur (
  p_owner in varchar2,
  p_table_name in varchar2
 ) is
  select owner, table_name, constraint_name
  from dba_constraints
  where constraint_type = 'R'
  and status = 'ENABLED'
  and r_constraint_name = (
   select constraint_name
   from dba_constraints
   where owner = p_owner
   and table_name = p_table_name
   and constraint_type = 'P'
  );
begin
 -- if the email server is not set already, set it here
      execute immediate 'alter session set smtp_out_server = ''smtp.myowncompany.com''';
 l_out :=
  rpad('Owner',10)||
  rpad('Table',30)||
  rpad('Retention',10)||
  rpad('Cutoff Dt',10)
  ;
 dbms_output.put_line(l_out);
 l_full := l_out;
 l_out :=
  rpad('-',9,'-')||' '||
  rpad('-',29,'-')||' '||
  rpad('-',9,'-')||' '||
  rpad('-',9,'-')
  ;
 l_full := l_full||chr(12)||l_out;
 dbms_output.put_line(l_out);
 open sched_cur;
 loop
  fetch sched_cur into l_sched_rec;
  exit when sched_cur%notfound;
  l_out := rpad(l_sched_rec.owner,10);
  l_out := l_out||rpad(l_sched_rec.table_name,30);
  l_cutoff_date :=
   case l_sched_rec.PART_SCHEME
    when 'MONTHLY' then
     case l_sched_rec.retention_unit
      when 'MONTH' then
       add_months(sysdate,-1*l_sched_rec.retention_duration)
      else
       null
      end
    when 'DAILY' then
     case l_sched_rec.retention_unit
      when 'DAY' then
       sysdate-l_sched_rec.retention_duration
      else
       null
      end
    when 'HALFYEARLY' then
     case l_sched_rec.retention_unit
      when 'HALFYEAR' then
       add_months(sysdate,-6*l_sched_rec.retention_duration)
      when 'MONTH' then
       add_months(sysdate,-1*l_sched_rec.retention_duration)
      else
       null
      end
    when 'YEARLY' then
     null
    when 'WEEKLY' then
     null
    when 'QUARTERLY' then
     case l_sched_rec.retention_unit
      when 'QUARTER' then
       add_months(sysdate,-3*l_sched_rec.retention_duration)
      when 'MONTH' then
       add_months(sysdate,-1*l_sched_rec.retention_duration)
      else
       null
      end
   end;
  l_out := l_out||rpad(l_sched_rec.retention_duration||' '||l_sched_rec.retention_unit,10);
  l_out := l_out||rpad(l_cutoff_date,10);
  dbms_output.put_line(l_out);
  l_full := l_full||chr(12)||l_out;
  for part_cur in
  (
   select partition_name, high_value
   from dba_tab_partitions
   where table_owner = l_sched_rec.owner
   and table_name = l_sched_rec.table_name
   order by partition_position
  ) loop
   -- dbms_output.put_line('l_sched_rec.owner='||l_sched_rec.owner);
   -- dbms_output.put_line('l_sched_rec.table_name='||l_sched_rec.table_name);
   if part_cur.high_value != 'MAXVALUE' then
    execute immediate 'select '||part_cur.high_value||
     ' from dual' into l_hv;
    if l_cutoff_date > l_hv then
     l_out := 'Partition '||
      part_cur.partition_name||
      ' with high value '||
      l_hv||
      ' to be dropped ...';
     dbms_output.put_line(l_out);
     l_full := l_full||chr(12)||l_out;
     l_drop_sql := 'alter table '||l_sched_rec.owner||'.'||
     l_sched_rec.table_name||
     ' drop partition '||part_cur.partition_name||
     ' update global indexes';
     dbms_output.put_line('l_drop_sql='||l_drop_sql);
     begin
      --
      -- Disable the FKs 
      --
      l_cons_disabled := FALSE;
      open cons_cur (l_sched_rec.owner, l_sched_rec.table_name);
      fetch cons_cur
      bulk collect
      into l_child_owner, l_child_table_name, l_constraint_name;
      close cons_cur;
      if nvl(l_child_owner.COUNT,0) > 0 then
       l_cons_disabled := TRUE;
       for ctr in l_child_owner.FIRST .. l_child_owner.LAST loop
        dbms_output.put_line('l_child_owner='||l_child_owner(ctr));
        dbms_output.put_line('l_child_table_name='||l_child_table_name(ctr));
        dbms_output.put_line('l_constraint_name='||l_constraint_name(ctr));
        l_dis_cons_sql := 'alter table '||l_child_owner(ctr)||'.'||
         l_child_table_name(ctr)||' disable constraint '||
         l_constraint_name(ctr);
        dbms_output.put_line('l_dis_cons_sql='||l_dis_cons_sql);
        l_sql := l_dis_cons_sql;
        execute immediate l_sql;
       end loop;
      end if;
      l_sql := l_drop_sql;
      execute immediate l_sql;
      --
      -- Enable the FKs
      --
      if (l_cons_disabled) then
       for ctr in l_child_owner.FIRST .. l_child_owner.LAST loop
        l_dis_cons_sql := 'alter table '||l_child_owner(ctr)||'.'||
         l_child_table_name(ctr)||' enable novalidate constraint '||
         l_constraint_name(ctr);
        dbms_output.put_line('l_dis_cons_sql='||l_dis_cons_sql);
        l_sql := l_dis_cons_sql;
        execute immediate l_sql;
       end loop;
      end if;
      l_out := l_out||'DONE';
      dbms_output.put_line(l_sql);
      dbms_output.put_line(l_out);
      l_full := l_full||'DONE';
      l_full := l_full||chr(12)||l_sql;
     exception
      when others then
       errm := SQLERRM;
       errc := SQLCODE;
       l_out := l_out||'FAILED';
       dbms_output.put_line(l_sql);
       dbms_output.put_line('Place:'||l_place);
       l_full := l_full||'FAILED';
       l_full := l_full||chr(12)||chr(12)||l_sql;
     end;
    end if;
   end if;
  end loop;
 end loop;
 close sched_cur;
 dbms_output.put_line(l_full);
 utl_mail.send(
  'oracle@prodb1',l_recipients,
  null, null,
  'Succeeded: PROPRD Partition Drop',
  l_full
 );
exception
 when OTHERS then
  errm := SQLERRM;
  utl_mail.send(
   'oracle@prodb1',l_recipients,
   null, null,
   'Failed: PROPRD Partition Drop',
   l_full
  );
  raise_application_error(-20001,errm);
end;
/
show error

Now that the procedure is complete, you should do a quick test to make sure the procedure is working as expected. Update the record in the table PARTITION_DROP_SCHEDULE to reflect 1 month retention and execute this procedure. It should drop the partition and maintain the global indexes. You can confirm the actions by:

(1) checking the DBA_TAB_PARTITIONS to see that the partition is indeed gone
(2) checking the DBA_INDEXES to see that the global index is indeed in USABLE state

If all looks well, you should just put it in a DBMS_SCHEDULER job and set it to run every day at a time that will be considered off peak, e.g. 3 AM.


Important Considerations

1. This is not intended for mass partition drops. For instance if you are planning to drop 100 partitions of a table, do not use it. This tool will update global indexes for each partition - a process highly inefficient for large numbers of partitions. You are much better off making the global indexes unusable, dropping all the partitions and then rebuilding global indexes. That will be much faster.
2. At the time of the drop, the session will get an exclusive lock on the table for a fraction of a second. So you will see a slight performance degradation for that instant only.
3. Never use this tool without adequately testing. Partition drop is a DDL operation - an irreversible process.

As always, I would appreciate comments and suggestions from you on this and any other blog entries. Thanks for reading and happy tooling.

Keynote Presentation at NOUG Oct 2010

Thank you all who came to watch my keynote presentation - "Real World DBA Best Practices" - for Northeast Oracle User Group in Boston, 21st October, 2010.

You can download the presentation here. Before downloading, please let me reiterate what I mentioned during the meeting - a best practice is not one if you do not understand the reason behind it and do not understand the applicability to your specific situation. I was not selling a product or service nor was I asking you to blindly follow it. All I wanted from you was to consider the points.

The Business of Religion

Most of you who read my blog will probably be shocked to see a topic like this. I work with Oracle technologies and my blogs have been predominantly about that (well, actually always about that). But I am also a human being living in societies and there are other thoughts that cross my mind. So here is something very, very different. This is not about Oracle, or IT, or even working in general. Please stop reading if you are not interested.

One of the activities I perform outside of my work is mentoring. These are not just rookies trying to go up in their careers; but accomplished professional (some more accomplished than me) trying to get some sense from a different perspective and I am willing to help. One such mentee (I will not name him to protect his privacy) mentioned something about religion that got me thinking. It was about a debate on religion and the threats on Americans travelling internationally and some other assorted topics. As a rule, I do not engage in debates about religion. Don't get me wrong, I love to debate passionately on topics I find interesting or important; but with some exceptions - religion being one of them.

It's rather sad to see how some reasonably intelligent or outwardly sound people justify the killing of innocent people as a manifestation of their religious obligations. One could argue that that act by itself is not religious or even spiritual; it's fanatical bordering on lunacy. But from the perspective of the perpetrators, that's what it is - sanctioned religious beliefs. Religion is a very dangerous double edged sword. On one hand it brings discipline, moral values and, for the lack of a better term - "humanity" to humans. Consider this - why would you not steal from thy neighbor?  It makes your act profitable and your actions immensely efficient (a lot of gain from a small effort); but you won't; because it's against established morality. Belief system - whether in the form of religion or otherwise, pours the very foundation of morality - a fact most of us probably do not realize. It differentiates us from other animals.

But the root of problem is that there is no boundary. Where should the belief system stop? Religion is after all a set of rituals and behavioral standards formed by human beings (although in some cases presumed or claimed vetted by supreme beings). Its specific actions are defined by the leaders, like any other group, but with an important difference. Belief systems adapt to situations - making the process forming the set of acceptable activities of religion highly fluid and devoid of specific direction. Some adapt it to their interpretation, in so much so, that they actually believe in their interpretation. Contrary to popular belief, these people may not be illiterate goat herding nomads; they may be at the top echelons of established societies, with a firm belief in their own direction of the religious activities.

And that makes the business of religion highly dangerous. While on one hand we cherish the extremely positive role religion plays in forming societies, we also witness the catastrophic consequence of the abuse of religion. That's why I avoid debating about religion in any form - there is a fine line between being spiritual and fanatical - and with each side deciding where the line should be. The debate has at least one attribute - logic and reason take a backseat to emotion and sentiment.

Presentation at New York Oracle User Group

Thank you for all those who attended the session - Analyzing Application Performance in RAC - at New York Oracle User Group's Fall Meeting in New York City.

You can download the slides from here. And, please watch out for the article on the same topic in NYOUG Tech Journal.

OOW 2010 Session Application Profiling in RAC

Thank you all who attended my session at OOW. It was the last day of the conference and I appreciate your taking the time. Here are the slides.

Thank you for attending and hope you will find it useful. Please look my other blog entry where I described the tool I built as well.

OOW 2010 Session Stats with Confidence

Thank you very much to all those who attended my session "Stats with Confidence". Unfortunately I was delayed by the keynote running late. With the big party coming up, I appreciate the spirit of those brave souls who stayed back. The late start didn't allow me to show the demo completely. But here are the scripts; hope you will be able to follow it along and run it on your own infrastructure.

It contains the presentation as well. Thanks for attending and hope you will find it useful.

A Tool to Enable Stats Collection for Future Sessions for Application Profiling

The other day I was putting together my presentation for Oracle Open World on Application Profiling in RAC. I was going to describe a methodology for putting a face to an app by measuring how it behaves in a database – a sort of a signature of that application. I was going to use the now-ubiquitous 10046 trace for wait events and other activities inside the database. For resource consumption such as redo generated, logical I/Os, etc., I used the v$sesstat; but then I was stuck. How would I collect the stats of a session when the session has not even started and I don’t know the SID. That problem led to the development of this tool where the stats of a future session can be recorded based on some identifying factors such as username, module, etc. Hope this helps in your performance management efforts.


The Problem

Suppose you want to find out the resource consumed by a session. The resources could be redo generation, CPU used, logical I/O, undo records generated – the list is endless. This is required for a lot of things. Consider a case where you want to find out which apps are generating the most redo; you would issue a query like this:

select sid, value
from v$sesstat s, v$statname n
where n.statistic# = s.statistic#
and n.name = 'redo size'
/

The value column will show the redo generated. From the SID you can identify the session. Your next stop is v$session to get the other relevant information such as username, module, authentication scheme, etc. Problem solved, right?

Not so fast. Look at the above query; it selects from v$sesstat. When the session is disconnected, the stats disappear, making the entries for that session go from v$sesstat. If you run the query, you will not find these sessions. You have to constantly select from the v$sesstat view to capture the stats of the sessions hoping that you would capture the stats before the session disconnects. But it will be not be guaranteed. Some short sessions will be missed in between collection samples. Even if you are lucky to capture some stats of a short session, the other relevant information from v$session will be gone.

Oracle provides a package dbms_monitor, where a procedure named client_id_stat_enable allows you to enable stats collection on a future session where the client_id matches a specific value, e.g. CLIENT1. Here is an example:

execute dbms_monitor.client_id_stat_enable('CLIENT1');

However there are three issues:

(1) It collects only about 27 stats, out of 400+

(2) It offers only three choices for selecting sessions – client_id, module_name and service_name.

(3) It aggregate them, sums up all stats for a specific client_id. That is pretty much useless without a detailed session level.

So, in short, I didn’t have a readily available solution.

Solution

Well, necessity is the mother of invention. When you can’t find a decent tool; you build it; and so did I. I built this tool to capture the stats. This is version 1 of the tool. It has some limitations, as shown at the end. These limitations do not apply to all situations; so the tool may be useful in a majority of the cases. Later I will expand the tool to overcome these limitations.

Concept

The fundamental problem, as you recall, is not the dearth of data (v$sesstat has plenty); it’s the sessions in the future. To capture those sessions, the tool relies on a post-logon database trigger to capture the values.

The second problem was persistence. V$SESSTAT is a dynamic performance view, which means the records of the session will be gone when the session disappears. So, the tool relies on a table to store the data.

The third problem is the getting the values at the very end of the session. The difference between the values captured at the end and beginning of the session are the stats. To capture the values at the very end; not anytime before, the tool relies on a pre-logoff database trigger.

The fourth challenge is identification of sessions. SID of a session is not unique; it can be reused for a new session; it will definitely be reused when the database is recycled. So, the tool uses a column named CAPTURE_ID, a sequentially incremented number for each capture. Since we capture once at the beginning and then at the end, I must use the same capture_id. I use a package variable to store that capture_Id.

Finally, the tool allows you to enable stats collections based on some session attributes such as username, client_id, module, service_name, etc. For instance you may want to enable stats for any session where the username = ‘SCOTT’ or where the os_user is ‘ananda’, etc. These preferences are stored in a table reserved for that purpose.

Construction

Now that you understand how the tool is structured, let me show the actual code and scripts to create the tool.

(1) First, we should create the table that holds the preferences. Let’s call this table RECSTATS_ENABLED. This table holds all the different sessions attributes (ip address, username, module, etc.) that can enable stats collection in a session.

CREATE TABLE SYS.RECSTATS_ENABLED
(
  SESSION_ATTRIBUTE  VARCHAR2(200 BYTE),
  ATTRIBUTE_VALUE    VARCHAR2(2000 BYTE)
)
/

If you want to enable stats collection of a session based on a session attribute, insert a record into this table with the session attribute and the value. Here are some examples. I want to collect stats on all sessions where client_info matches ‘MY_CLIENT_INFO1’. You would insert a record like this:

insert into recstats_enabled values ('CLIENT_INFO','MY_CLIENT_INFO1');

Here are some more examples. All sessions where ACTION is ‘MY_ACTION1’:

insert into recstats_enabled values ('ACTION','MY_ACTION1');

Those of user SCOTT:

insert into recstats_enabled values ('SESSION_USER','SCOTT')

Those with service name APP:

insert into recstats_enabled values ('SERVICE_NAME','APP')



You can insert as many preferences as you want. You can even insert multiple values of a specific attribute. For instance, to enable stats on sessions with service names APP1 and APP2, insert two records.

Important: the session attribute names follow the naming convention of the USERENV context used in SYS_CONTEXT function.

(2) Next, we will create a table to hold the statistics

CREATE TABLE SYS.RECSTATS
(
  CAPTURE_ID            NUMBER,
  CAPTURE_POINT         VARCHAR2(10 BYTE),
  SID                   NUMBER,
  SERIAL#               NUMBER,
  ACTION                VARCHAR2(2000 BYTE),
  CLIENT_DENTIFIER      VARCHAR2(2000 BYTE),
  CLIENT_INFO           VARCHAR2(2000 BYTE),
  CURRENT_EDITION_NAME  VARCHAR2(2000 BYTE),
  CURRENT_SCHEMA        VARCHAR2(2000 BYTE),
  CURRENT_USER          VARCHAR2(2000 BYTE),
  DATABASE_ROLE         VARCHAR2(2000 BYTE),
  HOST                  VARCHAR2(2000 BYTE),
  IDENTIFICATION_TYPE   VARCHAR2(2000 BYTE),
  IP_ADDRESS            VARCHAR2(2000 BYTE),
  ISDBA                 VARCHAR2(2000 BYTE),
  MODULE                VARCHAR2(2000 BYTE),
  OS_USER               VARCHAR2(2000 BYTE),
  SERVICE_NAME          VARCHAR2(2000 BYTE),
  SESSION_USER          VARCHAR2(2000 BYTE),
  TERMINAL              VARCHAR2(2000 BYTE),
  STATISTIC_NAME        VARCHAR2(2000 BYTE),
  STATISTIC_VALUE       NUMBER;
)
TABLESPACE USERS

Note, I used the tablespace USERS; because I don’t want this table, which can potentially grow to huge size, to overwhelm the system tablespace. The STATISTIC_NAME and STATISTIC_VALUE columns record the stats collected. The other columns record the other relevant data from the sessions. All the attributes here have been shown with VARCHAR2(2000) for simplicity; of course they don’t need that much of space. In the future versions, I will put a more meaningful limit; but 2000 does not hurt as it is varchar2.

The capture point will show when the values were captured – START or END of the session.

(3) We will also need a sequence to identify the sessions. Each session will have 400+ stats; we will have a set at the end and once at the beginning. We could choose SID as an identifier; but SIDs could be reused. So, we need something that is truly unique – a sequence number. This will be recorded in the CAPTURE_ID column in the stats table.

SQL> create sequence seq_recstats;

(4) To store the capture ID when the post-logon trigger is fired, to be used inside the pre-logoff trigger, we must use a variable that would be visible to entire session. A package variable is the best for that.

create or replace package pkg_recstats
is
   g_recstats_id number;
end;

(5) Finally, we will go on to the meat of the tool – the triggers. First, the post-logon trigger to capture the stats in the beginning of the session:

CREATE OR REPLACE TRIGGER SYS.tr_post_logon_recstats
after logon on database
declare
 l_stmt varchar2(32000);
 l_attr_val recstats_enabled.attribute_value%TYPE;
 l_capture_point recstats.capture_point%type := 'START';
 l_matched boolean := FALSE;
begin
 pkg_recstats.g_recstats_id := null;
 for r in (
  select session_attribute, attribute_value
  from recstats_enabled
  order by session_attribute
 )
 loop
  exit when l_matched;
            -- we select the userenv; but the null values may cause
            -- problems in matching; so let’s use a value for NVL
            -- that will never be used - !_!_!
  l_stmt := 'select nvl(sys_context(''USERENV'','''||
   r.session_attribute||'''),''!_!_!_!'') from dual';
  execute immediate l_stmt into l_attr_val;
  if l_attr_val = r.attribute_value then
   -- match; we should record the stats
   -- and exit the loop; since stats should
   -- be recorded only for one match.
   l_matched := TRUE;
   select seq_recstats.nextval
   into pkg_recstats.g_recstats_id
   from dual;
   insert into recstats
   select
    pkg_recstats.g_recstats_id,
    l_capture_point,
    sys_context('USERENV','SID'),
    null,
    sys_context('USERENV','ACTION'),
    sys_context('USERENV','CLIENT_IDENTIFIER'),
    sys_context('USERENV','CLIENT_INFO'),
    sys_context('USERENV','CURRENT_EDITION_NAME'),
    sys_context('USERENV','CURRENT_SCHEMA'),
    sys_context('USERENV','CURRENT_USER'),
    sys_context('USERENV','DATABASE_ROLE'),
    sys_context('USERENV','HOST'),
    sys_context('USERENV','IDENTIFICATION_TYPE'),
    sys_context('USERENV','IP_ADDRESS'),
    sys_context('USERENV','ISDBA'),
    sys_context('USERENV','MODULE'),
    sys_context('USERENV','OS_USER'),
    sys_context('USERENV','SERVICE_NAME'),
    sys_context('USERENV','SESSION_USER'),
    sys_context('USERENV','TERMINAL'),
    n.name,
    s.value
   from v$mystat s, v$statname n
   where s.statistic# = n.statistic#;
  end if;
 end loop;
end;

The code is self explanatory. I have provided more explanation as comments where needed.

(6) Next, the pre-logoff trigger to capture the stats at the end of the session:

CREATE OR REPLACE TRIGGER SYS.tr_pre_logoff_recstats
before logoff on database
declare
 l_capture_point recstats.capture_point%type := 'END';
begin
 if (pkg_recstats.g_recstats_id is not null) then
  insert into recstats
  select
   pkg_recstats.g_recstats_id,
   l_capture_point,
   sys_context('USERENV','SID'),
   null,
   sys_context('USERENV','ACTION'),
   sys_context('USERENV','CLIENT_IDENTIFIER'),
   sys_context('USERENV','CLIENT_INFO'),
   sys_context('USERENV','CURRENT_EDITION_NAME'),
   sys_context('USERENV','CURRENT_SCHEMA'),
   sys_context('USERENV','CURRENT_USER'),
   sys_context('USERENV','DATABASE_ROLE'),
   sys_context('USERENV','HOST'),
   sys_context('USERENV','IDENTIFICATION_TYPE'),
   sys_context('USERENV','IP_ADDRESS'),
   sys_context('USERENV','ISDBA'),
   sys_context('USERENV','MODULE'),
   sys_context('USERENV','OS_USER'),
   sys_context('USERENV','SERVICE_NAME'),
   sys_context('USERENV','SESSION_USER'),
   sys_context('USERENV','TERMINAL'),
   n.name,
   s.value
  from v$mystat s, v$statname n
  where s.statistic# = n.statistic#;
  commit;
 end if;
end;
/

Again the code is self explanatory. We capture the stats only of the global capture ID has been set by the post-logoff trigger. If we didn’t do that all the sessions would have started recording stats at their completion.

Execution

Now that the setup is complete, let’s perform a test by connecting as a user with the service name APP:

SQL> connect arup/arup@app



In this session, perform some actions that will generate a lot of activity. The following SQL will do nicely:

SQL> create table t as select * from all_objects;


SQL> exit



Now check the RECSTATS table to see the stats on this catured_id, which happens to be 1330.

col name format a60
col value format 999,999,999
select a.statistic_name name, b.statistic_value - a.statistic_value value
from recstats a, recstats b
where a.capture_id = 1330
and a.capture_id = b.capture_id
and a.statistic_name = b.statistic_name
and a.capture_point = 'START'
and b.capture_point = 'END'
and (b.statistic_value - a.statistic_value) != 0
order by 2
/

Here is the output:

NAME                                                                VALUE
------------------------------------------------------------ ------------
workarea memory allocated                                              -2
change write time                                                       1
parse time cpu                                                          1
table scans (long tables)                                               1
cursor authentications                                                  1
sorts (memory)                                                          1
user commits                                                            2
opened cursors current                                                  2
IMU Flushes                                                             2
index scans kdiixs1                                                     2
parse count (hard)                                                      2
workarea executions - optimal                                           2
redo synch writes                                                       2
redo synch time                                                         3
rows fetched via callback                                               5
table fetch by rowid                                                    5
parse time elapsed                                                      5
recursive cpu usage                                                     8
switch current to new buffer                                           10
cluster key scan block gets                                            10
cluster key scans                                                      10
deferred (CURRENT) block cleanout applications                         10
Heap Segment Array Updates                                             10
table scans (short tables)                                             12
messages sent                                                          13
index fetch by key                                                     15
physical read total multi block requests                               15
SQL*Net roundtrips to/from client                                      18
session cursor cache hits                                              19
session cursor cache count                                             19
user calls                                                             25
CPU used by this session                                               28
CPU used when call started                                             29
buffer is not pinned count                                             33
execute count                                                          34
parse count (total)                                                    35
opened cursors cumulative                                              36
physical read total IO requests                                        39
physical read IO requests                                              39
calls to get snapshot scn: kcmgss                                      45
non-idle wait count                                                    67
user I/O wait time                                                    116
non-idle wait time                                                    120
redo ordering marks                                                   120
calls to kcmgas                                                       143
enqueue releases                                                      144
enqueue requests                                                      144
DB time                                                               149
hot buffers moved to head of LRU                                      270
recursive calls                                                       349
active txn count during cleanout                                      842
cleanout - number of ktugct calls                                     842
consistent gets - examination                                         879
IMU undo allocation size                                              968
physical reads cache prefetch                                         997
physical reads                                                      1,036
physical reads cache                                                1,036
table scan blocks gotten                                            1,048
commit cleanouts                                                    1,048
commit cleanouts successfully completed                             1,048
no work - consistent read gets                                      1,060
redo subscn max counts                                              1,124
Heap Segment Array Inserts                                          1,905
calls to kcmgcs                                                     2,149
consistent gets from cache (fastpath)                               2,153
free buffer requested                                               2,182
free buffer inspected                                               2,244
HSC Heap Segment Block Changes                                      2,519
db block gets from cache (fastpath)                                 2,522
consistent gets                                                     3,067
consistent gets from cache                                          3,067
bytes received via SQL*Net from client                              3,284
bytes sent via SQL*Net to client                                    5,589
redo entries                                                        6,448
db block changes                                                    9,150
db block gets                                                      10,194
db block gets from cache                                           10,194
session logical reads                                              13,261
IMU Redo allocation size                                           16,076
table scan rows gotten                                             72,291
session uga memory                                                 88,264
session pga memory                                                131,072
session uga memory max                                            168,956
undo change vector size                                           318,640
session pga memory max                                            589,824
physical read total bytes                                       8,486,912
cell physical IO interconnect bytes                             8,486,912
physical read bytes                                             8,486,912
redo size                                                       8,677,104

This clearly shows you all the stats of that session. Of course the table recorded all other details of the session as well – such as username, client_id, etc., which are useful later for more detailed analysis. You can perform aggregations as well now. Here is an example of the stats collected for redo size:

select session_user, sum(STATISTIC_VALUE) STVAL
from recstats
where STATISTIC_NAME = 'redo size'
group by session_user
/

Output:

SESSION_USER STVAL
------------ ---------
ARUP            278616
APEX           4589343
… and so on …

You can disassemble the aggregates to several attributes as well. Here is an example where you want to find out the redo generated from different users coming from different client machines

select session_user, host, sum(STATISTIC_VALUE) stval
from recstats
where STATISTIC_NAME = 'redo size'
group by session_user, host
/

Output:

SESSION_USER HOST          STVAL
------------ ----------- -------
ARUP         oradba2       12356
ARUP         oradba1      264567
APEX         oradba2       34567
… and so on …

Granularity like this shows you how the application from different client servers helped; not just usernames.



Limitations

As I mentioned, there are some limitations you should be aware of. I will address them in the next iterations of the tool. These are not serious and applicable in only certain cases. As long as you don’t encounter that case, you should be fine.

(1) The logoff trigger does not fire when the user exits from the session ungracefully, such as closing down the SQL*Plus window, or closing the program before exiting. In such cases the stats at the end of the session will not be recorded. In most application infrastructure it does not happen; but it could happen for adhoc user sessions such as people connecting through TOAD.

(2) The session attributes such as module, client_id and action can be altered within the session. If that is the case, this tool does not record that fact since there is no triggering event. The logoff trigger records the module, action and client_id set at that time. These attributes are not usually changed in application code; so it may not apply to your case.

(3) Parallel Query sessions will have a special issue since there will be no logoff trigger. So in case of parallel queries, you will not see any differential stats. If you don’t use PQ, as most OLTP applications do, you will not be affected.

(4) If the session just sits there without disconnecting, the logoff trigger will never fire and the stats will never be captured. Of course, it will be eventually captured when the session exits.

Once again, these limitations apply only to certain occasions. As long as you are aware of these caveats, you will be able to use this tool to profile many of your applications.

Happy Profiling!

Webcast: Under the Hoods of Cache Fusion for LAOUG and NZOUG

Many thanks to all those who attended my webcast - Under the Hoods of Cache Fusion - for the Latin American and New Zealand Oracle User Groups on July 22nd, 2010. I'm sure the user groups will host the recording of the event on their websites. As I mentioned in my call, you can download the scripts I used here.

I hope you have enjoyed the session and found it useful. As always, I appreciate your feedback, critique and suggestions. You can either post here as comments or send me an email at arup@proligence.com.

Build a Simple Firewall for Databases Using SQL Net

This article was initially published in 2003 in DBAZine.com, which has since been folded.

So, you want to set up a secured database infrastructure?

You are not alone. With the proliferation of threats from all sources — identity thefts to corporate espionage cases — and with increased legislative pressures designed to protect and serve consumer privacy, security has a taken on a new meaning and purpose. Part of the security infrastructure of an organization falls right into your lap as a DBA, since it’s your responsibility to secure the database servers from malicious entities and curious insiders.

What are your options? Firewalls are first to come to mind. Using a firewall to protect a server, and not just a database server, is not a new concept and has been around for a while. However, a firewall may be overkill in some cases. Even if a firewall is desirable, it may still have to be configured and deployed properly. The complexity in administering a firewall, not to mention the cost to acquire one, may be prohibitive. If the threat level can be reduced by proper positioning of existing firewalls, the functionality of additional ones can be created by a tool available free with Oracle Net, Node Validation. In this article, you will learn how to build a rudimentary, but effective, firewall-like setup with just Oracle Net, and nothing else.

Background

Let’s see a typical setup. Acme, Inc. has several departments — two of which are Payroll and Benefits. Each department’s database resides on a separate server. Similarly, each department’s applications run on separate servers. There are several application servers and database servers for each department. To protect the servers from unauthorized access, each database server is placed behind a firewall with ports open to communicate SQL*Net traffic only. This can be depicted in figure 1 as follows:

Figure 1: Protecting departmental database and application servers using multiple firewalls.

This scheme works. But notice how many firewalls are required and the complexity that having this number adds to the administration process. What can we do to simplify the setup? How about removing all the firewalls and having one master firewall around the servers, as in Figure 2?

Figure 2: One master firewall around all the servers.

This method protects the servers from outside attacks; however, it still leaves inside doors open. For instance, the application server PAYROLL1 can easily connect to the database server of the Benefits Department BENEFITDB1, which is certainly inappropriate. In some organizations, there could be legal requirements to prevent this type of access.

Rather than creating a maze of firewalls as in the case we noted previously, we can take advantage of the SQL*Net Node Validation to create our own firewall. We will do this using only Oracle Net, which is already installed as a part of the database install. The rest of this article will explain in detail how to accomplish this.

Objective

Our objective is to design a setup as shown in figure 3. In this network, the application servers benefits1 and benefits2 access the database on server benefitsdb1. Similarly, application servers payroll1 and payroll2 access the database on server payrolldb1. Clients should be given free access to the intended machines. Client machines shouldn’t be allowed to access the database on other departments (e.g., benefits1 and benefits2 shouldn’t be able to access the database on payrolldb1). Likewise, application servers payroll1 and payroll2 should not be allowed to access benefitsdb1.

Figure 3: One master firewall and restricting access from non-departmental clients.

Note the key difference in requirements here — we are not interested in disallowing any type of access from client machines to servers of another department. Rather, it’s enough to disable access at the Oracle level only. This type of restriction is enforced by the listener. A listener can check the IP address of the client machine and, based on certain rules, decide to allow or deny the request. This can be enabled by a facility called Valid Node Checking, available as a part of Oracle Net installation. Let’s see how this can be done.

To set up valid node checking, simply place a set of lines on a specific file on the server. In our example, the following lines are placed in the parameter file on the server payrolldb1, allowing access to servers payroll1 and payroll2.

tcp.validnode_checking = yes

tcp.invited_nodes = (payroll1, payroll2)

Where this parameter file is located depends on the Oracle version. In Oracle 8i, it’s a file named protocol.ora; in Oracle 9i, it’s called sqlnet.ora. Both these files are located in the directory specified by the environmental variable TNS_ADMIN, which defaults to $ORACLE_HOME/network/admin in UNIX or %ORACLE_HOME%\network\admin in Windows.

These parameters are self-explanatory. The first line, tcp.validnode_checking = yes, specifies that the nodes are to be validated before accepting the connection.

The second line specifies that only the clients payroll1 and payroll2 are allowed to connect to the listener. The clients are indicated by either IP address (e.g., 192.168.1.1) or the node name as shown above. The list of node names is specified by a single line separated by commas. It is important to have only one line — you shouldn’t break it up.

The values take effect only during the startup of the listener. After making the change in protocol.ora (in Oracle 8i) or sqlnet.ora (in Oracle 9i and later), stop and restart the listener. After you’ve done so, if a user, regardless of the authentication in the database or authority level, attempts to connect to the database on benefits1 from the node payroll1, he receives the error as shown below.

$ sqlplus scott/tiger@payrolldb1

SQL*Plus: Release 9.2.0.4.0 - Production on Tue Jan 2o 9:03:33 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

ERROR: ORA-12537: TNS:connection closed

Enter user-name:

The error message is not very informative; it does not explicitly state the nature of the error. This error occured, however, because the connection request came from a client that is not listed as accepted. In this case, the listener simply rejected the connection originating from the node benefits1, regardless of the user. Yet the same user trying to connect from node payroll1 would succeed.

Excluded Nodes

In the previous example, we saw how to allow only a certain set of clients, and disallow all others. Similarly, you can specify the other type of rule — exclude some clients and allow all others. Say the lines in the parameter file are as follows:

tcp.validnode_checking = yes

tcp.excluded_nodes = (payroll3)

All clients but those connecting from payroll3 would be able to connect to all nodes. So, in this case, clients benefits1 and benefits2 would be able to connect to payrolldb1 in addition to clients payroll1 and payroll2. Isn’t that counter to what we wanted to achieve? Where can this exclusion be used?

In real life cases, networks are subdivided into subnetworks, and they offer adequate protection. In a particular subnet, there may be a greater number of clients needing access than the number being restricted. In such a case, it might be easier to specifically refuse access from a set of named clients, conveniently named in the tcp.excluded_nodes parameter. You can also use of this parameter to refuse access from certain machines that had been used to launch attacks in the past.

You can also mix excluded and included nodes, in which case, the invited nodes are given precedence over excluded ones. But there are three very big drawbacks to this approach.
1. There is no way to specify a wild card character in the node list. You must specify a node explicitly by its name or its IP address.
2. All excluded or invited nodes are to be specified in only one line, severely limiting your ability to specify a large number of nodes.
3. Since the validation is based on IP address or client names only and it’s relatively easy to spoof these two key pieces of identification, the system is not inherently secure.

For these reasons, mixing excluded and included nodes is not quite suitable for excluding a large list of servers from a network or subnetwork. This method can be used when the list of machines accessing the network is relatively small and the machines are in a subnetwork, behind a firewall. In such a configuration, the risk of external attacks is very slight, and the risk of unauthorized access by spoofing key identification is negligible.

Oracle Net also provides another means to develop a rudimentary firewall using a lesser known and even lesser used tool called Connection Manager. This tool is far more flexible in the setup; you can specify wildcards n-node names without restrictions such as the need to have only a single line for naming the nodes. A detailed discussion of Connection Manager with real-life examples can be found in the book Oracle Privacy Security Auditing.

Troubleshooting

Of course, things may not always proceed as smoothly as in the examples we’ve cited so far. One of the common problems you can encounter is that the exclusion may not work even though the files may be present and the parameters seem to be defined properly.

To diagnose a node checking issue you may encounter, you need to turn on tracing during the connection process. Tracing the process can be done in several levels of detail, and in this case, you should enable it for the level called support, or "16." Place the following line in the file sqlnet.ora:

trace_level_server = support

Doing this causes the connection process to write detailed information in a trace file under the directory $ORACLE_HOME/network/trace. The directory can be specified to a different value by a parameter in the file sqlnet.ora, as
trace_directory_server = /tmp

By doing this, the trace information to be written to the directory /tmp instead of the default. After setting the parameters as shown above, you should attempt the connection again. There is no need to bounce the listener. The connection attempt will create trace files named similar to svr_0.trc to be written in the proper directory. You should open this file in an editor (parts of the file are shown below).

[20-JAN-2004 12:00:01:234] Attempted load of system pfile
source /u02/oracle/product/9.2/network/admin/sqlnet.ora

[20-JAN-2004 12:00:01:234] Parameter source loaded successfully

[20-JAN-2004 12:00:01:234]

[20-JAN-2004 12:00:01:234] -> PARAMETER TABLE LOAD RESULTS FOLLOW <-

[20-JAN-2004 12:00:01:234] Successful parameter table load

[20-JAN-2004 12:00:01:234] -> PARAMETER TABLE HAS THE FOLLOWING CONTENTS <-

[20-JAN-2004 12:00:01:234] tcp.validnode_checking = yes

[20-JAN-2004 12:00:01:234] trace_level_server = support

[20-JAN-2004 12:00:01:234] tcp.invited_nodes = (192.168.1.1, 192.168.1.2)

[20-JAN-2004 18:27:04:484] NAMES.DIRECTORY_PATH = (TNSNAMES)

[20-JAN-2004 18:27:04:484] tcp.excluded_nodes = (192.168.1.3)

[20-JAN-2004 18:27:04:484] --- PARAMETER SOURCE INFORMATION ENDS ---

These lines indicate that

1. The parameter file /u02/oracle/product/9.2/network/admin/sqlnet.ora was read by the listener.

2. The parameters were loaded successfully.

3. The contents of the parameter were read as they were mentioned.

4. The names of the excluded and invited nodes are displayed.

If the information is not as shown here, the problem be caused by the way the parameter file is written; most likely a typographical error such as a missing parenthesis. This type of error should be fixed before proceeding further along the trace file.

If the parameters are indeed loaded properly, you should next check the section of the file in which the node validity checking is done. This section looks like this:

[20-JAN-2004 12:30:45:321] ntvllt: Found tcp.invited_nodes. Now loading...

[20-JAN-2004 12:30:45:321] ntvllhs: entry

[20-JAN-2004 12:30:45:321] ntvllhs: Adding Node 192.168.1.1

[20-JAN-2004 12:30:45:321] ntvllhs: Adding Node 192.168.1.2

[20-JAN-2004 12:30:45:321] ntvllhs: exit

[20-JAN-2004 12:30:45:321] ntvllt: exit

[20-JAN-2004 12:30:45:321] ntvlin: exit

[20-JAN-2004 12:30:45:321] nttcnp: Validnode Table IN use; err 0x0

The first line indicates that the parameter tcp.invited_nodes was found. Next, the entries in that list are read and displayed one after the other. This is the most important clue. If the addresses were written incorrectly, or the syntax were wrong, the trace files would have indicated this by not specifying the node names checked. The last line in this section shows that the ValidNode table was read and used with error code of 0x0 (in hexadecimal, equating to zero) — the table has no errors. If there were a problem in the way the valid node parameters were written in the parameter file, the trace file would have shown something different. For instance, say the parameters were written as

tcp.excluded_nodes = (192.168.1.3

Note how a parenthesis is left out, indicating a syntax problem. However, this does not affect the connection; the listener simply ignores the error and allows the connection without doing a valid node checking. Upon investigation, we would find the root of the problem in the trace file. The trace file shows the following information.

--- PARAMETER SOURCE INFORMATION FOLLOWS ---

[20-JAN-2004 12:45:03:214] Attempted load of system pfile

source /u201/oracle/product/9.2/network/admin/sqlnet.ora

[20-JAN-2004 12:45:03:214] Load contained errors 14] Error stack follows: NL-00422: premature end of file NL-00427: bad list

[20-JAN-2004 12:45:03:214]

[20-JAN-2004 12:45:03:214] -> PARAMETER TABLE LOAD RESULTS FOLLOW <-

[20-JAN-2004 12:45:03:214] Some parameters may not have been loaded

[20-JAN-2004 12:45:03:214]

See dump for parameters which loaded OK This clearly shows that the parameter file had errors that prevented the parameters from loading. Because of this, the valid node checking is turned on and in use, but there is nothing in the list of the excluded nodes as shown in the following line from the trace file:

[20-JAN-2004 12:45:03:214] nttcnp: Validnode Table IN use; err 0x0

Since the error is 0x0, no error is reported by the validity checking routine. The subsequent lines on the trace file show other valuable information. For instance this line,

[20-JAN-2004 12:45:13:211] nttbnd2addr: using host IP address: 192.168.1.1

shows that the IP address of the server to which the listener was supposed to route the connection was 192.168.1.1. If all goes well, the listener allows the client to open a connection. This is confirmed by the following line:

[20-JAN-2004 12:45:14:320] nttcon: NT layer TCP/IP connection has been established.

As the line says, the TCP/IP connection has been established. If any other problems exist, the trace file will show enough helpful information for a diagnosis.

Summary

To summarize:

1. Node Validation can be used to instruct listeners to accept or reject a connection from a specific client.
2. The parameter file is sqlnet.ora in Oracle 9i and protocol.ora in Oracle8i.
3. The nodes must be explicitly specified by name or by IP Address; no wildcards are supported.

Webcast for Latin American Oracle User Group

Thank you all those attended the websession today for LAOUG. It was a great honor to be the first speaker in the virtual conference series. Many thanks for inviting me, Francisco. I'm also looking forward to the next three I am supposed to deliver.

For those who attended, you may want to download the scripts at www.proligence.com/sec_scripts.zip

RACSIG Webcast on June 24th Files

Thank you all those who attended my presentation - "Under the Hoods: Cache Fusion, GCS, GES and GRD". I hope you found it useful. Here are the demo scripts I used during the session, if you want to play with on your own system.



The actual presentation itself will most likely be available at a later date on the oracleracsig.org website.

Mining Listener Logs

When is the last time you looked at the listener logs? Perhaps never. Not a very good idea. Listener logs contain a wealth of information on security events - it shows you the profile of hosts connecting to the database, the programs they are using and attempting to communicate but failed, among other things. But reading the listener logs is not exactly a pleasant experience. A while ago I wrote a series of articles on an online eZine called DBAZine.com on how to create an external table to read the listener logs using simple SQL; but unfortunately DBAZine.com has folded.

I have placed the articles on my website for your reference. As always, I would love to hear from you how you felt about these, stories of your own use and everything in between.

Mining Listener Logs Part 1
Mining Listener Logs Part 2
Mining Listener Logs Part 3

IOUG Webcast on Security

Many thanks to those who attended my webcast "Secure Your Database in a Single Day" for IOUG's wecast series. I hope you found it useful. I would highly appreciate if you take a moment to let me know how you felt - good, bad and ugly. Please write to me at arup@proligence.com.



You can find the scripts referenced in the webcast here.

Oracle Celebrity Seminar Tour in APAC

For those who attended my seminars in Shanghai, Beijing, Shenzhen, Kualalampur and Singapore - I thank you very much for taking the time. I sincerely appreciate the gesture and hope that you found them useful.

As I mentioned during the seminars, I would love to hear from you your thoughts - what you liked, didn't like, wanted to learn but didn't, or specific areas you applied in your workplace. Please drop me a line at arup@proligence.com.