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.

My Sessions at IOUG Collaborate 2010

Thank you for all those attended my sessions during Collaborate 2010. Two of the sessions I presented were not mine; but that of Riyaj Shamsuddin. Riyaj was stuck in Denmark thanks to the ash cloud scenario in Europe and asked if I could present his. I agreed to and, with a lot of trepidation, I did. I hope I did justice to the sessions. For questions on those sessions, please reach out to Riyaj directly.

Going to my own sessions, here is where you can download the presentations. For the sessions I wanted to show live demos; but in a short span of 30 minutes for Quick Tips, it was impossible. You can download the scripts here so that you can check them out yourself. The slides show which scripts to execute.

RAC Performance Tuning, part of RAC Bootcamp (Recorded)
Stats with Intelligence (Recorded)
Publish Stats after Checking, part of Manageability Bootcamp (Recorded and shown via Webcast)

Once again, your patronage by attending is highly appreciated. A speaker is nothing without attendees. I sincerely hope that you got some value from the sessions. As always, I am looking forward to hearing from you – not just that you liked; but things you didn't.

Online Materialized View Complete Refresh With Partitions

The other day I was posed with an interesting problem regarding the refresh of the materialized views. First, a little background. MVs are nothing new in Oracle; they have been around since Oracle 7 (called snapshots then). Essentially they are similar to tables (as they actually store data) but populated by running a query on some source tables. This query is the defining query of the MV. So, the user can select from the MV instead of executing the defining query – reducing the execution time significantly.

However, the MV and the query would both give the same result if the source tables have not changed. If the data has changed, the MV will not have known about it and will give a stale result. Therefore, you need to refresh the MV from time to time. Usually the MVs are refreshed by the following method:

begin
dbms_mview.refresh(‘’);
end;

So, what’s the problem? The problem is, during complete refreshes, the MV remains unavailable to the users. This duration of refresh depends on the size of the MV, the execution time of the underlying query and so on; and the unavailability is a nuisance for most applications. In some cases the applications are designed to timeout after some wait; and they do so quite consistently in this case – making it more than just a nuisance.

So, I was asked to devise a solution to make the MVs available during the complete refreshes. I accomplished that with a not-so-common use of partitions. Here is how I did it.

Prebuilt Table

First, as an architectural standard, I advocate the use of prebuilt tables in MVs. Here is how an MV is usually created:

create materialized view mv_bookings_01
refresh complete on demand
enable query rewrite
as
select hotel_id, sum(book_txn) tot_txn
from bookings
group by hotel_id
/

This creates the segment mv_bookings_01 during this process. Instead of this approach, I recommend first creating and populating a regular table and then converting that to an MV. In this revised approach, you would first create the table:

create table mv_bookings_01
as
select HOTEL_ID, sum(book_txn) tot_txn
from bookings
group by HOTEL_ID
/

Then, you would create the MV using the prebuilt table option:

create materialized view mv_bookings_01
on prebuilt table
refresh complete on demand
enable query rewrite
as
select HOTEL_ID, sum(book_txn) tot_txn
from bookings
group by HOTEL_ID
/

This does not do anything to the functionality of the MV itself. The MV feels, looks and smells like it was before; but with two significant advantages:

(1) You can easily alter the columns
(2) You can manipulate the table

I blogged about it at http://arup.blogspot.com/2009/01/ultra-fast-mv-alteration-using-prebuilt.html. Anyway, back to the issue at hand. Using the prebuilt trick, I can create a partitioned MV as well. In this specific case, I will add a column to partition on. Remember, this column does not actually participate in the application; it simply creates an anchor point for the partition.

create table mv_bookings_01
(
ref_type number,
hotel_id number(3),
tot_txn number
)
partition by list (ref_type)
(
partition p0 values (0),
partition p1 values (1)
)
/

After the table is created, I can insert data into it:

insert /*+ append */ into mv_bookings_01
select 0, hotel_id, sum(book_txn) tot_txn
from bookings
group by 0, hotel_id
/

Of course I could have selected the data from the source tables directly while creating the prebuilt table; but I just wanted to show a different approach of data loading. The column REF_TYPE is not part of the application; but I have added it to divide the table along a value by list partitioning. The column can hold only two values – 0 and 1, and hence the table has only two partitions.

Once the table is created, I can use the prebuilt table option to create the MV as shown above. However, the presence of the new column makes it a little different. My MV creation script now looks like this:

create materialized view mv_bookings_01
on prebuilt table
enable query rewrite
as
select 0 as ref_type, HOTEL_ID, sum(book_txn) tot_txn
from bookings
group by 0, HOTEL_ID
/

Note that I have used “0 as ref_type” in the select clause, i.e. the ref_type will always be 0. This is not going to a problem as you can see later. When the MV is first created, the value of ref_type is 0; hence the partition p0 is the one is that is populated; not the partition p1. We can confirm that:

SQL> select count(1) from mv_bookings_01 partition (p0);

COUNT(1)
----------
2

SQL> select count(1) from mv_bookings_01 partition (p1);

COUNT(1)
----------
0

Now that the partitions are in place, let’s see how we refresh this MV. We will no longer use the dbms_mview approach. Rather, we will use this:

(1) Create a temporary table
(2) Create all the indexes
(3) Exchange the partition that is not used with this temporary table

Step 1: Temporary table

First we create a temporary table that is identical to the prebuilt table of the MV in structure; but just not partitioned. We will use the value of ref_type column as 0 or 1 based on the value already in the table, in fact exactly opposite of what is in the table. Since we have 0 in the table, we will use 1 in the temporary table. This temporary table will contain the data that we need refreshed as.

create table tmp1
as
select 1 as ref_type, hotel_id, sum(book_txn) tot_txn
from bookings
group by 1, hotel_id
/

We also need to create another temporary table with no data.

create table tmp0
(
ref_type number,
hotel_id number(3),
tot_txn number
)
/

Step 2: Create Indexes

In this case we don’t have any indexes on the MV; but if we had, we would have created them here on the TMP0 and TMP1 tables.

Step 3: Exchange Partitions

When the temporary table is ready, we can exchange the inactive partition with this.

alter table mv_bookings_01
exchange partition p1
with table tmp1
including indexes
without validation
update global indexes
/
alter table mv_bookings_01
exchange partition p0
with table tmp0
including indexes
without validation
update global indexes
/

This SQL performs a switch: the segment that was called partition P1 in the table mv_bookings_01 is now called TMP1 and former segment TMP1 is now called partition P1 in the table mv_bookings_01. This occurs at the data dictionary level; no actual data is ever transferred from one segment to the other. As a result this is very quick. A lock is required on the table only for that instance is such an insignificant amount of time that it may not be even noticeable. Similarly the segment used to be called P0 partition is now known as TMP0 table and TMP0 is called P0.

There are several key things to note here. Note the clause “without validation”, which tells Oracle not to bother checking inside the TMP0 table that it will confirm to the partition specification. This is critical for the performance. Since we built the table with “1” as a hardcoded value, we know that the REF_TYPE column will definitely contain 1, satisfying the partition requirement for P1. A further checking is not required.

The “including indexes” clause switched the local index partitions as well. If there is a global index on the MV’s prebuilt table, that would have been invalidated and needed rebuilding afterwards. But, we avoided that invalidation by using a “update global indexes” clause in the alter statement.

Once the switchover is complete, we can check the MV to see if the data has been visible.

SQL> select count(1) from mv_bookings_01 partition (p0)
2 /

COUNT(1)
----------
0

SQL> select count(1) from mv_bookings_01 partition (p1)
2 /

COUNT(1)
----------
2

This completes the refreshing of the MV and the data is visible to end users. Let’s see the timeline

(1) Building of temporary table
(2) Building of indexes on the temporary table
(3) Exchange partitions

Steps 1 and 2 take most of the time; but that is done offline, without affecting the MV itself. So, long time there does not affect the availability of the MV itself. The step 3 is where the availability is impacted; but that is miniscule.

The table TMP0 now has the rows from partition P0 of the MV. To confirm that:

SQL> select count(1) from tmp0;

COUNT(1)
----------
2

You can drop the table by “drop table tmp0 purge”. You should also drop TMP1 since that table will contain the previous contents of the partition P1, which is useless now.

The next time this MV needs refreshing, you have to repeat the process; but with a very important distinction – the partition P0 needs exchange now. I used a script that checks the value of REF_TYPE in the MV now and use the other value passed to an SQL script with a positional parameter that accepts 0 or 1. Using this approach you can refresh a MV very quickly.

Oracle 11g Variation

In Oracle 11g, there is a new feature – Virtual Column. You can define a column as virtual in the table, which is not actually stored in the table; but calculated during runtime. What’s more, this virtual column can also be used as a partition key. You can define the REF_TYPE as a virtual column in that case.

Takeaways

(1) Just because there is an Materialized View, it does not have to be refreshed by the usual approach, i.e. dbms_mview.refresh. There are other options as well.
(2) Partitioning is not just for performance or purge reasons; it can be used in clever ways such as MV refreshes.
(3) In this specific case, we didn’t have a column that could have been used as a partitioning key; so we had to resort to adding a column. In many cases, the table may already have a column for that purpose, making the process even simpler.

Difference between Session and Process

Someone asked me the other day the difference between process and session in Oracle. Are they the same thing? Well, why then there are two parameters in the initialization parameter file – sessions and processes? Is it possible that a process does not have a corresponding session? Conversely, is it possible to have a session without a process?

And, furthermore, the processes parameter is larger than the sessions. So, are there more processes than sessions? To answer that, she got the count from v$session and v$process:

SQL> select count(1) from v$process;

COUNT(1)
----------
23

SQL> select count(1) from v$session;

COUNT(1)
----------
20
At least the answer was pretty clear – there are more processes than sessions (23 versus 20). But then, someone pointed to the view V$RESOURCE_LIMIT, which records the current usage of the various definable limits. Checking the limits for two resources – sessions and processes:

select resource_name, current_utilization, max_utilization, limit_value
from v$resource_limit
where resource_name in ( 'sessions', 'processes')
/

RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION LIMIT_VALUE
------------- ------------------- --------------- -----------
processes 23 23 40
sessions 26 26 49This shows that the highest possible numbers for sessions and processes are 40 and 49 respectively. [Note, I have set the processes parameter in init.ora to 49 to reduce the limit artificially]. It shows that currently there are 26 sessions and 23 processes. Finally it shows the sessions and processes have touched a high number of 26 and 23 respectively.

This contradicts what we saw earlier. The processes usage matches – 23 processes seen from the resource limit view and count from v$process; but the session count differs. From the resource limit, we see that there are 26 sessions; but v$session shows only 20. Why there is a difference?

Processes without Sessions

Let’s see the first one – processes without sessions. We can easily find them:

select pid, spid, addr, program
from v$process
where addr not in (
select paddr from v$session
)

PID SPID ADDR PROGRAM
---------- ---------- -------- ------------------------------
1 4116A2B8 PSEUDO
18 23904 411758AC oracle@oradba1 (D000)
19 23906 41176360 oracle@oradba1 (S000)

Other than the first one, these are real processes. The processes refer to the dispatcher process and the shared server processes. These are not background processes; so they don’t appear in V$BGPROCESS.

Sessions without Processes

Now what about the second case – the sessions without processes? That one is harder to visualize. A session in Oracle is a connection to the database. As we know from the two-task architecture, a client process of a session kicks off a server process which in turn does the heavy lifting from the Oracle database. Keeping that in mind, it might be difficult to envision a session without a process. With a twist of the same concept, is it possible for two sessions to share a single process? Once again, that defies understanding as well. A session is an exclusive conduit to the database; how can that be shared? Even in case of share servers, each shared server session has its own process.

It is possible, under some special circumstances. They are used by Recursive Database Sessions, persistent for a very small amount of time; but they are sessions. When you create a table, alter an index or perform other tasks, Oracle must perform certain tasks on your behalf, which are called recursive actions. One example may make it clear. When you create a table, Oracle must determine that you have sufficient privileges to do that and the determination is made through a recursive session. Let’s see a demonstration of the sessions in action.

First connect to the database and get your own SID:

select sid, paddr, spid
from v$session s, v$process p
where s.audsid = userenv('SESSIONID')
and p.addr = s.paddr
/

SID PADDR SPID
---------- -------- ------------------------
17 4117F938 24042
This shows that my own session has the SID 17, the OS process id of 24042 and the address of the server process is 4117F938. I know, I know; this is a 32-bit system, so the process address is small.

If you look at v$process, you can get the details on that process as well, using the paddr value. Conversely, if you select the sessions with that paddr value:

SQL> select sid from v$session where paddr = '4117F938';

SID
---
17
There is exactly one session. If you check it for any process paddr, you will see exactly one SID. But, that’s where the mystery lies. The v$session view does not tell the whole story; it does not show all the sessions. Here is the definition of the view:

SQL> select view_definition from v$fixed_view_definition where view_name = 'GV$SESSION';

VIEW_DEFINITION
----------------------------------------------------------------------------------
select s.inst_id,s.addr,s.indx,s.ksuseser,s.ksuudses,s.ksusepro,s.ksuudlui,s.ksuudlna,s.ksuudoct,s.ksusesow, decode(s.ksusetrn,hextoraw('00'),null,s.ksusetr
n),decode(s.ksqpswat,hextoraw('00'),null,s.ksqpswat),decode(bitand(s.ksuseidl,11),1,'ACTIVE',0,decode(bitand(s.ksuseflg,4096),0,'INACTIVE','CACHED'),2,'SNIP
ED',3,'SNIPED', 'KILLED'),decode(s.ksspatyp,1,'DEDICATED',2,'SHARED',3,'PSEUDO','NONE'), s.ksuudsid,s.ksuudsna,s.ksuseunm,s.ksusepid,s.ksusemnm,s.ksusetid,
s.ksusepnm, decode(bitand(s.ksuseflg,19),17,'BACKGROUND',1,'USER',2,'RECURSIVE','?'), s.ksusesql, s.ksusesqh, s.ksusesqi, decode(s.ksusesch, 65535, to_numbe
r(null), s.ksusesch), s.ksusesesta, decode(s.ksuseseid, 0, to_number(null), s.ksuseseid), s.ksusepsq, s.ksusepha, s.ksusepsi, decode(s.ksusepch, 65535,
to_number(null), s.ksusepch), s.ksusepesta, decode(s.ksusepeid, 0, to_number(null), s.ksusepeid), decode(s.ksusepeo,0,to_number(null),s.ksusepeo), decod
e(s.ksusepeo,0,to_number(null),s.ksusepes), decode(s.ksusepco,0,to_number(null),s.ksusepco), decode(s.ksusepco,0,to_number(null),s.ksusepcs), s.ksuseapp,
s.ksuseaph, s.ksuseact, s.ksuseach, s.ksusecli, s.ksusefix, s.ksuseobj, s.ksusefil, s.ksuseblk, s.ksuseslt, s.ksuseltm, s.ksusectm,decode(bitand(s.ksusepxo
pt, 12),0,'NO','YES'),decode(s.ksuseft, 2,'SESSION', 4,'SELECT',8,'TRANSACTIONAL','NONE'),decode(s.ksusefm,1,'BASIC',2,'PRECONNECT',4,'PREPARSE','NONE'),dec
ode(s.ksusefs, 1, 'YES', 'NO'),s.ksusegrp,decode(bitand(s.ksusepxopt,4),4,'ENABLED',decode(bitand(s.ksusepxopt,8),8,'FORCED','DISABLED')),decode(bitand(s.ks
usepxopt,2),2,'FORCED',decode(bitand(s.ksusepxopt,1),1,'DISABLED','ENABLED')),decode(bitand(s.ksusepxopt,32),32,'FORCED',decode(bitand(s.ksusepxopt,16),16,'
DISABLED','ENABLED')), s.ksusecqd, s.ksuseclid, decode(s.ksuseblocker,4294967295,'UNKNOWN', 4294967294, 'UNKNOWN',4294967293,'UNKNOWN',4294967292,'NO HOLD
ER', 4294967291,'NOT IN WAIT','VALID'),decode(s.ksuseblocker, 4294967295,to_number(null),4294967294,to_number(null), 4294967293,to_number(null), 4294967292
,to_number(null),4294967291, to_number(null),bitand(s.ksuseblocker, 2147418112)/65536),decode(s.ksuseblocker, 4294967295,to_number(null),4294967294,to_numb
er(null), 4294967293,to_number(null), 4294967292,to_number(null),4294967291, to_number(null),bitand(s.ksuseblocker, 65535)),w.kslwtseq,w.kslwtevt,e.kslednam, e.ksledp1,w.kslwtp1,w.kslwtp1r, e.ksledp2,w.kslwtp2,w.kslwtp2r,e.ksledp3,w.kslwtp3,w.kslwtp3r, e.ksledclassid,e.ksledclass#,e.ksledclass, decode(w.kslwtin
wait, 0,decode(bitand(w.kslwtflags,256), 0,-2, decode(round(w.kslwtstime/10000), 0,-1,
round(w.kslwtstime/10000))), 0), decode(w.kslwtinwait,0,round((w.kslwtstime+w.kslwtltime)/1000000), round(w.kslwtstime/1000000)), de
code(w.kslwtinwait,1,'WAITING', decode(bitand(w.kslwtflags,256),0,'WAITED UNKNOWN TIME', decode(round(w.kslwtstime/10000),0,'WAITED SHORT TIME', 'WAIT
ED KNOWN TIME'))),w.kslwtstime, decode(w.kslwtinwait,0,to_number(null), decode(bitand(w.kslwtflags,64),64,0,w.kslwttrem)), , decode(
bitand(s.ksuseflg2,32),32,'ENABLED','DISABLED'),decode(bitand(s.ksuseflg2,64),64,'TRUE','FALSE'),decode(bitand(s.ksuseflg2,128),128,'TRUE','FALSE'),decode(b
itand(s.ksuseflg2,65536) + bitand(s.ksuseflg2,131072),65536,'ALL EXEC',131072,'NEVER',0,'FIRST EXEC'),s.ksuudsae,s.ksusecre,s.ksusecsn from x$ksuse s, x$ksled e, x$kslwt w where bitand(s.ksspaflg,1)!=0 and bitand(s.ksuseflg,1)!=0 and s.indx=w.kslwtsid and w.kslwtevt=e.indx
Note the section shown in red. The ksuseflg column in x$ksuse contains a lot of information, which the v$session view extracts and presents in a readable manner. Let’s use exactly the same SQL directly from the prompt. Noting that the ksusepro and indx and ksuudsna columns show the process address (paddr), the SID and the username respectively, we can extract the information:

select indx, decode(bitand(ksuseflg,19),17,'BACKGROUND',1,'USER',2,'RECURSIVE','UNKNOWN'),
ksuudsna user_name
from x$ksuse
where ksusepro = '41182408'
/
INDX DECODE(BIT USER_NAME
---------- ---------- ------------------------------
7 USER ARUP
It shows the same information that v$session would have shown; but with a big difference. Note the view definition again – there is a join condition at the end with two other views: x$ksled and x$kslwt. Not all the session information is available on the other views. So the join filters out some rows from x$ksuse. To examine when it does that and that sessions get created automatically, you have to conduct an experiment.

First you have to look at a view called V$RESOURCE_LIMIT, which shows the various definable limits, how much is being used and – most important – how much has been the high water mark usage of the limit. We are interested in only 2 limits – sessions and processes.

select resource_name, current_utilization, max_utilization, limit_value
from v$resource_limit
where resource_name in ( 'sessions', 'processes')
/

RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION LIMIT_VALUE
------------- ------------------- --------------- -----------
processes 23 23 40
sessions 26 26 49
This shows that the highest possible numbers for sessions and processes are 40 and 49 respectively. [Note, I have set the processes parameter in init.ora to 49 to reduce the limit artificially]. It shows that currently there are 26 sessions and 23 processes. Finally it shows the sessions and processes have touched a high number of 26 and 23 respectively.

In this experiment, keep the above session connected to SYS. On a different OS prompt, connect to database using SQL*Plus as user ARUP. At the SQL> prompt, type host, which will show an OS prompt. From that shell, connect to the database again as ARUP. From the prompt go to host prompt and connect using SQL*Plus. Do it over and over so that the number of sessions builds up as shown below:

SQL> host
oracle@oradba1 ~/arup# sqlplus arup/arup

SQL*Plus: Release 11.1.0.7.0 - Production on Wed Mar 24 21:54:56 2010

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


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

SQL> host
oracle@oradba1 ~/arup# sqlplus arup/arup
.....
and so on ....
After some time you will receive an error like this:

ERROR:
ORA-00020: maximum number of processes (%s) exceeded
From session 1 (where you are connected as sysdba), check the resource limits:

select resource_name, current_utilization, max_utilization, limit_value
from v$resource_limit
where resource_name in ( 'sessions', 'processes')
/

RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION LIMIT_VALUE
------------- ------------------- --------------- -----------
processes 39 40 40
sessions 45 47 49
This makes perfect sense. Note the current number of processes – 39, and the limit is 40. The very last session tried to create a session; and it failed, since it would have pushed the limit above 40.

Now, in the session of ARUP, execute a PL/SQL code that creates and drops a lot of objects in succession.

begin
for i in 1..1000 loop
execute immediate 'drop table t';
execute immediate 'create table t as select * from all_objects where 1=2';
end loop;
end;
/
This simply creates and drops the table called t in succession, 1000 times. While this code is running, check for the session from the x$ksuse view directly. You already know the process address:

select indx, decode(bitand(ksuseflg,19),17,'BACKGROUND',1,'USER',2,'RECURSIVE','UNKNOWN'),
ksuudsna user_name
from x$ksuse
where ksusepro = '41182408'
/

INDX DECODE(BIT USER_NAME
---------- ---------- ------------------------------
7 USER ARUP
10 RECURSIVE SYS
This is interesting. There are two sessions – SIDs 7 and 10 against the same process with address 41182408. Why is that? SID 7 is the actual session which you connected. If you look at the decoded value, it shows RECURSIVE for the SID 10, which shows that for the user SYS. This session was kicked off by Oracle for the recursive actions, which pushed the session count up without a real user session. This is a situation where a single process serves 2 sessions.

Another interesting point is to know what this recursive session’s waiting on? To know that you have to check the view X$KSLWT. First set the null display to “?” so that null values show up as “?”:

SQL> set null ?
Now execute the query to get the wait event:

select s.indx, decode(bitand(ksuseflg,19),17,'BACKGROUND',1,'USER',2,'RECURSIVE','UNKNOWN'),
ksuudsna user_name, w.kslwtevt
from x$ksuse s, x$kslwt w
where ksusepro = '41182408'
and s.indx=w.kslwtsid (+)
/


INDX DECODE(BIT USER_NAME KSLWTEVT
---------- ---------- ------------------------------ ----------
4 RECURSIVE SYS ?
7 USER ARUP 72
Note, I used the outer join. Why? It’s because the session will not be found in the wait events view, which is confirmed by the null output. As you can glean from the above output, the wait event is not recorded for the recursive sessions. This is why a corresponding row was not found in the view V$SESSION which joins x$ksuse and x$kslwt without an outer join.

Now you must be curious about other such sessions where they share the same process. Let’s check:

SQL> select ksusepro, count(1)
2 from x$ksuse
3 group by ksusepro
4 having count(1) > 1;

KSUSEPRO COUNT(1)
-------- ----------
00 2
4117AE4C 2
41174344 2
41178E30 2
4117EE84 2
4117D91C 2
41174DF8 2
4117B900 2
4117837C 2
4117CE68 2
4116CD88 2
4117A398 2
We got several processes with addresses. Next, let’s find out the exact program names.

SQL> with proc_tab as
2 (
3 select ksusepro addr
4 from x$ksuse
5 group by ksusepro
6 having count(1) > 1
7 )
8 select v.addr, v.program
9 from proc_tab p, v$process v
10 where v.addr = p.addr;

ADDR PROGRAM
-------- ------------------------------------------------
4117AE4C oracle@oradba1 (FBDA)
41174344 oracle@oradba1 (MMON)
41178E30 oracle@oradba1 (ARC1)
4117EE84 oracle@oradba1 (W000)
4117D91C oracle@oradba1 (CJQ0)
41174DF8 oracle@oradba1 (MMNL)
4117B900 oracle@oradba1 (QMNC)
4117837C oracle@oradba1 (ARC0)
4117CE68 oracle@oradba1 (q001)
4116CD88 oracle@oradba1 (DBRM)
4117A398 oracle@oradba1 (ARC3)
These are the sessions related to the background processes – Flashback Data Archiver, Memory Monitor and so on. Let’s dive down even further and look into the sessions these processes serve:

SQL> with proc_tab as
2 (
3 select ksusepro addr
4 from x$ksuse
5 group by ksusepro
6 having count(1) > 1
7 )
8 select p.addr, indx sid, decode(bitand(ksuseflg,19),17,'BACKGROUND',1,'USER',2,'RECURSIVE','UNKNOWN'),
9 ksuudsna user_name
10 from x$ksuse x, proc_tab p
11 where ksusepro = p.addr
12 order by 1,2;

ADDR SID DECODE(BIT USER_NAME
-------- ---------- ---------- ------------------------------
00 1 RECURSIVE SYS
00 10 RECURSIVE SYS
4116CD88 23 RECURSIVE SYS
4116CD88 45 BACKGROUND SYS
41174344 30 RECURSIVE SYS
41174344 34 BACKGROUND SYS
41174DF8 20 RECURSIVE SYS
41174DF8 33 BACKGROUND SYS
4117837C 3 RECURSIVE SYS
4117837C 29 BACKGROUND SYS
41178E30 2 RECURSIVE SYS
41178E30 28 BACKGROUND SYS
4117A398 25 RECURSIVE SYS
4117A398 26 BACKGROUND SYS
4117AE4C 21 RECURSIVE SYS
4117AE4C 22 BACKGROUND SYS
4117B900 19 RECURSIVE SYS
4117B900 24 BACKGROUND SYS
4117CE68 15 RECURSIVE SYS
Note, for each of the background processes has a corresponding recursive session, which is counted towards the total session count in V$RESOURCE_LIMIT, even if you don’t actually create user sessions. These recursive sessions are not visible in V$SESSION.

Takeaways

1. The first lesson to learn is a process not always ≠ a session. In many cases there is a one-to-one relationship; but not always. When Oracle has to run a recursive SQL as a result of some user commands, it creates the session objects behind the scenes to fulfill that request.
2. Second, these recursive sessions count towards the overall sessions limit. To know the current sessions connected, use the V$RESOURCE_LIMIT view instead of selecting the sum from V$SESSION.

Thank you for all those who attended my webcast today on SQL Plan Management for All India Oracle User Group. It was a privilege to present before you and to be able to address your questions. I am sorry I couldn;t read all the questions properly; since it was extremely difficult to see the questions scrolling up in the tiny chat window. Also, as the webcast was designed, I couldn't hear anything the attendees were saying.

The presentation and the associated SQL scripts are available here. The article I referred to can be found here.

If you have a question regarding that specific webcast, please post a comment here and I will address it here. Please, limit your questions to the material discussed in the webcast only.

Instance_Number is busy Message during Standby Instance Startup

Recently I encountered a situation that demonstrates how, in an Oracle database, an error may stem from a very unrelated cause. A DBA was building a physical standby database for an upcominmg training. The two servers he was using were part of a RAC cluster; so the Oracle binaries were already there. He decided to use the same ORACLE_HOME for the new databases as well - a quite logical decision to savbe on space and administration issues. He created a primary database on server n1 and a standby database on the server n2. Follooing the usual manual procedure in building the standby database, he copied the pfile from primary database, modified the parameters and brought up the standby instance in nomount mode on the server n2.

SQL> startup nomount pfile=initSTBY.ora

But it refused to come up, with the following error:

ORA-00304: requested INSTANCE_NUMBER is busy

Alert log showed:

USER (ospid: 14210): terminating the instance due to error 304
Instance terminated by USER, pid = 14210

This was highly unusual. The primary and standby both were non-RAC; there was no instance number concept in a non-RAC database. By the way, the RAC instance on that server (or on the server n1) was not running; so there was no question of any conflict with the RAC instances either. The primary database was called PRIM while the standby was called STBY - eliminating the possibility of an instance name clash as well. And this error came while merely trying to start the instance, not even while mounting - eliminating the standby controlfile as a cause.

The error 304 showed:

00304, 00000, "requested INSTANCE_NUMBER is busy"
// *Cause: An instance tried to start by using a value of the
// initialization parameter INSTANCE_NUMBER that is already in use.
// *Action: Either
// a) specify another INSTANCE_NUMBER,
// b) shut down the running instance with this number
// c) wait for instance recovery to complete on the instance with
// this number.

Needless to say, being for a non-RAC database there was no "instance_number" parameter in the initialization parameter file of primary or the standby. So, the suggestions for the resolution seemed odd. MetaLink provided no help. All the ORA-304 errors were related to RAC with the instance_number mismatch.

As it always happens, it fell on my lap at this time. With just days to go live, I had to find a solution quickly. Long hours of troubleshooting, tracing the processes and examination of occasional trace files did not yield any clue. All the clue seemed to point to RAC, which this database was not. The Oracle Home was a RAC home, which meant the oracle binary was linked with the "rac" option.

So, the next logical step was to install a new Oracle Home without the rac option. After doing so, I tried to bring up the instance, using the new ORACLE_HOME and LD_LIBRARY_PATH variable; but, alas, the same error popped up.

Just to eliminate the possibility of some unknown bug, I decided to put an instance_number parameter, setting it to "1", from the default "0". The same error. I changed it to "2", again, the result was the same error.

Although this didn't help, at least it gave a clue that the error was not related to instance_number. The error message was clearly wrong. With this in mind, I went back to the basics. I went through the alert log with a fine toothed comb, scanning and analyzing each line.

The following line drew my attention:

DB_UNQIUE_NAME STBY is not in the Data Guard configuration

This was odd; the db_unique_name STBY was not defined in DG configuration. [BTW, note the spelling of "unique" in the message above. That is not what I typed; it was a copy and paste from the actual message in the alert log. Someone in Oracle Development should really pay atytention to typos in messages. This is clearly more than a nuisance; what if some process scans for db_unique_name related errors? It will not find the message at all!]

Checking the dg configuration, I found that the DBA has correctly defined the primary and standby names. In any case, Data Guard has not been started yet; this is merely at the instance startup - why is it complaining for data guard configuration at this time.

Perplexed, I resorted to a different approach. I renamed the pfile and all other relevant files. Then I built the standby myself, from scratch - using the same names - PRIM and STBY. And this time, everything worked fine. The instance STBY did come up.

While this solvbed the urgency problem, everyone, inclduing myself, wanted to know what the issue was in the earlier case where the DBA had failed to bring up the instance. To get the answer, I compared the files I created with the DBA created when tried and failed. Voila! The cause was immediately clear - the DBA forgot to put a vital parameter in the pfile of the standby instance:

db_unique_name = 'STBY'

This parameter was absent; so it took the default value as the db_name, which was "PRIM". This caused the instance to fail with a seemingly unrelated message - "ORA-304 Instance_number is busy"!

Learning Points

1. In Oracle, most errors are obvious; but some are not. So, do not assume the error message is accurate. If all logic fails, assume the error messsage is wrong, or at least inaccurate.
2. Instead of over-analyzing the process already followed, it may make sense to take a breather, wipe out everything and start fropm scratch. This is evben mor effective when someone else does it, offering a fresh approach and possibly not repeating the same mistakes.
3. Finally, the issue at hand: if you do not define db_unique_name parameter in the standnby instance, you will receive ORA-304 during instance startup.

Hope this was helpful. Happy New Year, everybody.

Resolving Gaps in Data Guard Apply Using Incremental RMAN BAckup

Recently, we had a glitch on a Data Guard (physical standby database) on infrastructure. This is not a critical database; so the monitoring was relatively lax. And that being done by an outsourcer does not help it either. In any case, the laxness resulted in a failure remaining undetected for quite some time and it was eventually discovered only when the customer complained. This standby database is usually opened for read only access from time to time.This time, however, the customer saw that the data was significantly out of sync with primary and raised a red flag. Unfortunately, at this time it had become a rather political issue.

Since the DBA in charge couldn’t resolve the problem, I was called in. In this post, I will describe the issue and how it was resolved. In summary, there are two parts of the problem:

(1) What happened
(2) How to fix it

What Happened

Let’s look at the first question – what caused the standby to lag behind. First, I looked for the current SCN numbers of the primary and standby databases. On the primary:

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
1447102

On the standby:

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
1301571

Clearly there is a difference. But this by itself does not indicate a problem; since the standby is expected to lag behind the primary (this is an asynchronous non-real time apply setup). The real question is how much it is lagging in the terms of wall clock. To know that I used the scn_to_timestamp function to translate the SCN to a timestamp:

SQL> select scn_to_timestamp(1447102) from dual;

SCN_TO_TIMESTAMP(1447102)
-------------------------------
18-DEC-09 08.54.28.000000000 AM

I ran the same query to know the timestamp associated with the SCN of the standby database as well (note, I ran it on the primary database, though; since it will fail in the standby in a mounted mode):

SQL> select scn_to_timestamp(1301571) from dual;

SCN_TO_TIMESTAMP(1301571)
-------------------------------
15-DEC-09 07.19.27.000000000 PM

This shows that the standby is two and half days lagging! The data at this point is not just stale; it must be rotten.

The next question is why it would be lagging so far back in the past. This is a 10.2 database where FAL server should automatically resolved any gaps in archived logs. Something must have happened that caused the FAL (fetch archived log) process to fail. To get that answer, first, I checked the alert log of the standby instance. I found these lines that showed the issue clearly:

…
Fri Dec 18 06:12:26 2009
Waiting for all non-current ORLs to be archived...
Media Recovery Waiting for thread 1 sequence 700
Fetching gap sequence in thread 1, gap sequence 700-700
… …
Fri Dec 18 06:13:27 2009
FAL[client]: Failed to request gap sequence
GAP - thread 1 sequence 700-700
DBID 846390698 branch 697108460
FAL[client]: All defined FAL servers have been attempted.
…

Going back in the alert log, I found these lines:

Tue Dec 15 17:16:15 2009
Fetching gap sequence in thread 1, gap sequence 700-700
Error 12514 received logging on to the standby
FAL[client, MRP0]: Error 12514 connecting to DEL1 for fetching gap sequence
Tue Dec 15 17:16:15 2009
Errors in file /opt/oracle/admin/DEL2/bdump/del2_mrp0_18308.trc:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Tue Dec 15 17:16:45 2009
Error 12514 received logging on to the standby
FAL[client, MRP0]: Error 12514 connecting to DEL1 for fetching gap sequence
Tue Dec 15 17:16:45 2009
Errors in file /opt/oracle/admin/DEL2/bdump/del2_mrp0_18308.trc:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

This clearly showed the issue. On December 15th at 17:16:15, the Managed Recovery Process encountered an error while receiving the log information from the primary. The error was ORA-12514 “TNS:listener does not currently know of service requested in connect descriptor”. This is usually the case when the TNS connect string is incorrectly specified. The primary is called DEL1 and there is a connect string called DEL1 in the standby server.

The connect string works well. Actually, right now there is no issue with the standby getting the archived logs; so there connect string is fine - now. The standby is receiving log information from the primary. There must have been some temporary hiccups causing that specific archived log not to travel to the standby. If that log was somehow skipped (could be an intermittent problem), then it should have been picked by the FAL process later on; but that never happened. Since the sequence# 700 was not applied, none of the logs received later – 701, 702 and so on – were applied either. This has caused the standby to lag behind since that time.

So, the fundamental question was why FAL did not fetch the archived log sequence# 700 from the primary. To get to that, I looked into the alert log of the primary instance. The following lines were of interest:

…
Tue Dec 15 19:19:58 2009
Thread 1 advanced to log sequence 701 (LGWR switch)
Current log# 2 seq# 701 mem# 0: /u01/oradata/DEL1/onlinelog/o1_mf_2_5bhbkg92_.log
Tue Dec 15 19:20:29 2009Errors in file /opt/oracle/product/10gR2/db1/admin/DEL1/bdump/del1_arc1_14469.trc:
ORA-00308: cannot open archived log '/u01/oraback/1_700_697108460.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Tue Dec 15 19:20:29 2009
FAL[server, ARC1]: FAL archive failed, see trace file.
Tue Dec 15 19:20:29 2009
Errors in file /opt/oracle/product/10gR2/db1/admin/DEL1/bdump/del1_arc1_14469.trc:
ORA-16055: FAL request rejected
ARCH: FAL archive failed.
Archiver continuing
Tue Dec 15 19:20:29 2009
ORACLE Instance DEL1 - Archival Error. Archiver continuing.
…

These lines showed everything clearly. The issue was:

ORA-00308: cannot open archived log '/u01/oraback/1_700_697108460.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory

The archived log simply was not available. The process could not see the file and couldn’t get it across to the standby site.

Upon further investigation I found that the DBA actually removed the archived logs to make some room in the filesystem without realizing that his action has removed the most current one which was yet to be transmitted to the remote site. The mystery surrounding why the FAL did not get that log was finally cleared.

Solution

Now that I know the cause, the focus was now on the resolution. If the archived log sequence# 700 was available on the primary, I could have easily copied it over to the standby, registered the log file and let the managed recovery process pick it up. But unfortunately, the file was gone and I couldn’t just recreate the file. Until that logfile was applied, the recovery will not move forward. So, what are my options?

One option is of course to recreate the standby - possible one but not technically feasible considering the time required. The other option is to apply the incremental backup of primary from that SCN number. That’s the key – the backup must be from a specific SCN number. I have described the process since it is not very obvious. The following shows the step by step approach for resolving this problem. I have shown where the actions must be performed – [Standby] or [Primary].

1. [Standby] Stop the managed standby apply process:

SQL> alter database recover managed standby database cancel;

Database altered.

2. [Standby] Shutdown the standby database

3. [Primary] On the primary, take an incremental backup from the SCN number where the standby has been stuck:

RMAN> run {
2> allocate channel c1 type disk format '/u01/oraback/%U.rmb';
3> backup incremental from scn 1301571 database;
4> }

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: sid=139 devtype=DISK

Starting backup at 18-DEC-09
channel c1: starting full datafile backupset
channel c1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/oradata/DEL1/datafile/o1_mf_system_5bhbh59c_.dbf
… …
piece handle=/u01/oraback/06l16u1q_1_1.rmb tag=TAG20091218T083619 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:06
Finished backup at 18-DEC-09
released channel: c1

4. [Primary] On the primary, create a new standby controlfile:

SQL> alter database create standby controlfile as '/u01/oraback/DEL1_standby.ctl';

Database altered.

5. [Primary] Copy these files to standby host:

oracle@oradba1 /u01/oraback# scp *.rmb *.ctl oracle@oradba2:/u01/oraback
oracle@oradba2's password:
06l16u1q_1_1.rmb 100% 43MB 10.7MB/s 00:04
DEL1_standby.ctl 100% 43MB 10.7MB/s 00:04

6. [Standby] Bring up the instance in nomount mode:

SQL> startup nomount

7. [Standby] Check the location of the controlfile:

SQL> show parameter control_files

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /u01/oradata/standby_cntfile.ctl

8. [Standby] Replace the controlfile with the one you just created in primary.

9. $ cp /u01/oraback/DEL1_standby.ctl /u01/oradata/standby_cntfile.ctl

10.[Standby] Mount the standby database:

SQL> alter database mount standby database;

11.[Standby] RMAN does not know about these files yet; so you must let it know – by a process called cataloging. Catalog these files:

$ rman target=/

Recovery Manager: Release 10.2.0.4.0 - Production on Fri Dec 18 06:44:25 2009

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

connected to target database: DEL1 (DBID=846390698, not open)
RMAN> catalog start with '/u01/oraback';

using target database control file instead of recovery catalog
searching for all files that match the pattern /u01/oraback

List of Files Unknown to the Database
=====================================
File Name: /u01/oraback/DEL1_standby.ctl
File Name: /u01/oraback/06l16u1q_1_1.rmb

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/oraback/DEL1_standby.ctl
File Name: /u01/oraback/06l16u1q_1_1.rmb

12.Recover these files:

RMAN> recover database;

Starting recover at 18-DEC-09
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/oradata/DEL2/datafile/o1_mf_system_5lptww3f_.dbf
...…
channel ORA_DISK_1: reading from backup piece /u01/oraback/05l16u03_1_1.rmb
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/oraback/05l16u03_1_1.rmb tag=TAG20091218T083619
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07

starting media recovery

archive log thread 1 sequence 8012 is already on disk as file /u01/oradata/1_8012_697108460.dbf
archive log thread 1 sequence 8013 is already on disk as file /u01/oradata/1_8013_697108460.dbf
… …

13. After some time, the recovery fails with the message:

archive log filename=/u01/oradata/1_8008_697108460.dbf thread=1 sequence=8009
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 12/18/2009 06:53:02
RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile '/u01/oradata/1_8008_697108460.dbf'
ORA-00310: archived log contains sequence 8008; sequence 8009 required
ORA-00334: archived log: '/u01/oradata/1_8008_697108460.dbf'

This happens because we have come to the last of the archived logs. The expected archived log with sequence# 8008 has not been generated yet.

14.At this point exit RMAN and start managed recovery process:

SQL> alter database recover managed standby database disconnect from session;

Database altered.

15.Check the SCN’s in primary and standby:

[Standby] SQL> select current_scn from v$database;

CURRENT_SCN
-----------
1447474
[Primary] SQL> select current_scn from v$database;

CURRENT_SCN
-----------
1447478
Now they are very close to each other. The standby has now caught up.