Failover to standby with a delay (until time)

Disaster scenario :

1) you have a primary database
2) you have a standby database
3) you want to be able to failover to the standby database until a given time (ex: within the last 24 hours)

First, you create your primary and standby databases.

If you have dataguard broker, you then set the DELAYMINS to 1440 (=1 day) for the standby database.


DGMGRL> edit database sdb01 set property DelayMins=1440;

If you do not use dg broker, then set the delay in your log_archive_dest_2 parameter

SQL> ALTER SYSTEM SET log_archive_dest_2='service=sdb01','LGWR ASYNC NOAFFIRM delay=1440';

Ok, verify your configuration, with OEM, with show configuration or simply with alter system archive log current.

Wait one day ;-)

After one day, you will have a lag between the last retrieved logfile and the last applied redo log entry.


SQL> select applied, max(NEXT_TIME) from v$archived_log group by applied;
APPLIED   MAX(NEXT_TIME)
--------- -------------------
NO        2011-11-17_14:06:53
YES       2011-11-17_13:51:46

Obviously I did not wait one day for this test, but I already see a log of 15 minutes.

Now for some obscure reason, your beloved colleague messed up the primary database and you must recover the database until 14:00.

Note that you cannot use the dataguard broker or OEM to do this.

Ok. Shutdown the production.


SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Now cancel the standby process (but do not issue a recover managed standby database finish).


SQL>  alter database recover managed standby database cancel;

Database altered.

Now we need to recover the standby until time. Check the syntax twice before you type enter!

On standby


SQL> recover automatic standby database until time '2011-11-17 14:00:00';
Media recovery complete.

On standby, start the database as a primary database


SQL> alter database activate standby database;

Database altered.
SQL> alter database open;

Database altered.

Before writing this post I did some research on how to do it with dataguard, but dataguard does not seem to offer point in time failover. Either you do an immediate failover (and you will lose 1440 minutes of data) or you do a complete failover (and you will apply all logs, inclusive the one after 14:00)

Enterprise Manager command line interface

emcli has been around for a while, but in 12c the installation has never been easier

do not search on otn for the jar, go to

download with : Setup –>My Preferences –>Command line interface –> download

install with : java -jar emclikit.jar client -install_dir=/u01/app/oracle/emcli

configure with : emcli setup -url=https://precision.example.com:4901/em -username=sysman -password=sysmanpw -dir=/u01/app/oracle/emcli.

That’s it.

Let’s try


$ emcli get_targets
Status  Status           Target Type           Target Name                        
 ID                                                                               
1       Up               host                  precision.example.com              
1       Up               j2ee_application      /EMGC_GCDomain/GCDomain/EMGC_OMS1/e
                                               mgc                                
1       Up               j2ee_application      /EMGC_GCDomain/GCDomain/EMGC_OMS1/O
                                               CMRepeater                         
1       Up               j2ee_application      /EMGC_GCDomain/GCDomain/EMGC_OMS1/e
                                               mpbs                               
-9      n/a              metadata_repository   /EMGC_GCDomain/GCDomain/EMGC_ADMINS
                                               ERVER/mds-sysman_mds               
-9      n/a              metadata_repository   /EMGC_GCDomain/GCDomain/EMGC_ADMINS
                                               ERVER/mds-owsm                     
1       Up               oracle_apache         /EMGC_GCDomain/instance1/ohs1      
1       Up               oracle_apm            /EMGC_GCDomain/GCDomain/EMGC_OMS1/o
                                               racle.security.apm(11.1.1.3.0)     
1       Up               oracle_beacon         EM Management Beacon               
1       Up               oracle_database       LSC01                              
1       Up               oracle_database       LSC02                              
1       Up               oracle_database       LSC03                              
1       Up               oracle_database       LSC05                              
1       Up               oracle_database       LSC04                              
1       Up               oracle_dbsys          LSC01_sys                          
1       Up               oracle_dbsys          LSC03_sys                          
1       Up               oracle_dbsys          LSC04_sys                          
1       Up               oracle_dbsys          LSC02_sys                          
1       Up               oracle_em_service     EM Console Service                 
1       Up               oracle_em_service     EM Jobs Service                    
1       Up               oracle_emd            precision.example.com:1830         
1       Up               oracle_emrep          Management Services and Repository 
-9      n/a              oracle_home           oms12g1_8_precision                
-9      n/a              oracle_home           WebLogicServer10_3_5_0_0_precision 
-9      n/a              oracle_home           OraDb10g_home1_5_precision         
-9      n/a              oracle_home           OraDb11g_home1_1_precision         
-9      n/a              oracle_home           agent12g1_13_precision             
-9      n/a              oracle_home           webtier12g1_24_precision           
-9      n/a              oracle_ias_farm       EMGC_GCDomain                      
1       Up               oracle_listener       LISTENER_precision.example.com     
1       Up               oracle_oms            precision.example.com:4890_Manageme
                                               nt_Service                         
1       Up               oracle_oms_console    precision.example.com:4890_Manageme
                                               nt_Service_CONSOLE                 
1       Up               oracle_oms_pbs        precision.example.com:4890_Manageme
                                               nt_Service_PBS                     
-9      n/a              weblogic_domain       /EMGC_GCDomain/GCDomain            
1       Up               weblogic_j2eeserver   /EMGC_GCDomain/GCDomain/EMGC_OMS1  
1       Up               weblogic_j2eeserver   /EMGC_GCDomain/GCDomain/EMGC_ADMINS
                                               ERVER 

All green (one could argue command line has no color)

delete unused shared memory segments from an Oracle instance

Once upon a time, a dba issues some kill -9 to clean up dying database processes. Or the database instance crashes. This will left some shared memory segments. Note 68281.1 describe how to remove them on a server with multiple databases.

First, list the ipc process


$ ipcs
IPC status from /dev/mem as of Mon Nov 14 11:28:58 CET 2011
T        ID     KEY        MODE       OWNER    GROUP
Message Queues:
q         0 0x4107001c -Rrw-rw----     root   printq

Shared Memory:
m         0 0x7800006f --rw-rw-rw- itmuser1 itmusers
m         1 0x78000070 --rw-rw-rw- itmuser1 itmusers
m   5242882 0x41d2ba80 --rw-r-----   oracle      dba
m  99614723 0xb0d4d164 --rw-rw----   oracle      dba
m  12582917 0xb84cbc28 --rw-rw----   oracle      dba
m  79691782 0x1058873f --rw-------   oracle      dba
m 638582792 0x78000382 --rw-rw-rw-     root   system
m 218103817 0x780003b7 --rw-rw-rw-     root   system
Semaphores:
s         1 0x6202c477 --ra-r--r--     root   system
s   6291461 0x0102c2d8 --ra-------     root   system
s         6 0xa100004b --ra-ra-ra-     root   system

Get a list of the running databases


$ ps -ef | grep pmon | grep -v grep
  oracle  483334       1   1   Aug 16      -  6:46 ora_pmon_db03
  oracle 1253476       1   0   Oct 31      -  2:00 ora_pmon_db01
  oracle 2298042       1   0   Sep 05      - 11:07 ora_pmon_db02

Then, for each database, get the ipc information
$ export ORACLE_SID=db01
$ sqlplus / as sysdba
SQL> oradebug setmypid
Statement processed.
SQL> oradebug ipc
Information written to trace file.
SQL> oradebug tracefile_name
/u01/app/oracle/admin/db01/udump/db01_ora_2625574.trc
$ awk '/Shared Memory:/{getline;getline;print}' /u01/app/oracle/admin/db01/udump/db01_ora_2625574.trc
5242882         0x41d2ba80
$ export ORACLE_SID=db02
$ sqlplus / as sysdba
SQL> oradebug setmypid
Statement processed.
SQL> oradebug ipc
Information written to trace file.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/db02a/db02/trace/db02_ora_2441408.trc
$ awk '/Shared Memory:/{getline;getline;print}' /u01/app/oracle/diag/rdbms/db02a/db02/trace/db02_ora_2441408.trc
99614723        0xb0d4d164
$ export ORACLE_SID=db03
$ sqlplus / as sysdba
SQL> oradebug setmypid
Statement processed.
SQL> oradebug ipc
Information written to trace file.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/db03b/db03/trace/db03_ora_2617416.trc
$ awk '/Shared Memory:/{getline;getline;print}' /u01/app/oracle/diag/rdbms/db03b/db03/trace/db03_ora_2617416.trc
12582917        0xb84cbc28

Compare it with the first list, and if you are absolutely sure to do what you are doing, remove the oracle segments that are not assigned to any database instance with ipcrm. If possible try first to figure out where they come from and do a shutdown abort of the not-correctly-stopped database.

Ok, with ipcrm


$ ipcrm -m 79691782

I removed the segment that apparently does not relate to any running instance

This could help you if you are really forced to remove some shared memory segments and you cannot afford shutting down other databases.

TNSNAMES and Active Directory

It is highly probable you already have MS AD in your company. Probably you use a local tnsnames.ora. Apart from setting a Oracle Internet Directory or Oracle Virtual Directory, there is one more option that you may want to consider : AD.

Ok, here is a bit of a road map :

- Schema Extension :
extending the schema is irreversible and you will have to test this properly and explain why you need this (remove the need of distributing a tnsnames, central administration) to your Microsoft Admin friends. To extend the schema, use Oracle Network Configuration Assistant. The step-by-step guide is there

- Anonymous or authenticated bind
prior to 11g, you needed to allow anonymous bind on the AD server. Your Security Admin friends will probably prefer the 11g approach of setting NAMES.LDAP_AUTHENTICATE_BIND to true. If you set NAMES.LDAP_AUTHENTICATE_BIND to true, the Oracle clients will use your windows credentials to do the tnsnames resolution.

For sql developer, use Connection Type=TNS, Connect Identifier=DB01. connection type=Ldap does not work with authenticated bind

- Import the tnsnames and / or create new entries
all done with Net Manager and pretty intuitively. Except that you will use “Directory –> Export Net Service Names” to import the tnsnames in AD

- Configure the clients
sqlnet.ora
NAMES.DIRECTORY_PATH= (LDAP)
NAMES.LDAP_AUTHENTICATE_BIND=1

ldap.ora
DEFAULT_ADMIN_CONTEXT = “DC=example,DC=com”
DIRECTORY_SERVER_TYPE = AD

- test it!
tnsping first


C:\> tnsping db01

TNS Ping Utility for 64-bit Windows: Version 11.2.0.2.0 - Production on 10-NOV-2011 14:42:16

Copyright (c) 1997, 2010, Oracle.  All rights reserved.

Used parameter files:
C:\oracle\product\11.2.0\client_3\network\admin\sqlnet.ora

Used LDAP adapter to resolve the alias
Attempting to contact (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(Host=srv01)(Port=1521)))(CONNECT
_DATA=(SID=DB01)))
OK (20 msec)

I wrote a simple java program to check the connection :


import java.sql.*;
import oracle.jdbc.pool.*;
public class HelloWorld {
  public  static void main(String[] args) throws SQLException {
    OracleDataSource ods = new OracleDataSource();
    ods.setDriverType("oci");
    ods.setTNSEntryName("DB01");
    ods.setUser("scott");
    ods.setPassword("tiger");
    ResultSet res = ods.
      getConnection().
        prepareCall("select 'Hello World' txt from dual").
          executeQuery();
    res.next();
    System.out.println(res.getString("TXT"));
  }
}


C:\> set PATH=C:\oracle\product\11.2.0\client_3\bin
C:\> javac -classpath .;C:\oracle\product\11.2.0\client_3\jdbc\lib\ojdbc6.jar HelloWorld.java
C:\> java -classpath .;C:\oracle\product\11.2.0\client_3\jdbc\lib\ojdbc6.jar -Doracle.net.tns_admin=C:\oracle\product\11.2.0\client_3\network\admin HelloWorld
Hello World

If you get and issue with ocijdbc11, you either do not have the *ocijdbc11* driver in your PATH / LD_LIBRARY_PATH / LIBPATH or the use the wrong driver. For instance if you compile with java 32bits, you cannot use the oci 64 bit.

If you use a jdbc thin ldap resolution and have no anonymous bind, it will return an error


import java.sql.*;
public class HelloWorld {
  public  static void main(String[] args) throws SQLException {
    DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
    ResultSet res = DriverManager.
      getConnection("jdbc:oracle:thin:@ldap://example.com:389/db01,cn=OracleContext", "scott", "tiger").
      prepareCall("select 'Hello World' txt from dual").
      executeQuery();
    res.next();
    System.out.println(res.getString("TXT"));
  }
}


C:\>java -classpath .;C:\oracle\product\11.2.0\client_3\jdbc\lib\ojdbc6.jar HelloWorld
Exception in thread "main" java.sql.SQLException: I/O-Fehler: JNDI Package failure avax.naming.NamingException: [LDAP:error code 1 - 000004DC: LdapErr: DSID-0C0906DC, comment: In order to perform this operation a successful bind must be completed on the connection., data 0, v1db0 ]; remaining name 'cn=db01,cn=OracleContext'
        at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:419)
        at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:536)
        at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:228)
        at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
        at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:521)
        at java.sql.DriverManager.getConnection(DriverManager.java:525)
        at java.sql.DriverManager.getConnection(DriverManager.java:171)
        at HelloWorld.main(HelloWorld.java:5)

As the error message says, the ldap server requires a bind

Let’s try to bind


import java.sql.*;
import java.util.*;
import oracle.jdbc.pool.*;
public class HelloWorld {
  public  static void main(String[] args) throws SQLException {
    OracleDataSource ods = new OracleDataSource();
    ods.setDriverType("thin");
    Properties prop = new Properties();
    prop.put("java.naming.security.authentication", "simple");
    prop.put("java.naming.security.principal","CN=Laurent Schneider,CN=Users,DC=example,DC=com");
    prop.put("java.naming.security.credentials", "my_ad_pw");
 
    ods.setConnectionProperties(prop);

    ods.setURL("jdbc:oracle:thin:@ldap://w2k8.local:389/db01,cn=OracleContext,DC=example,DC=com");
    ods.setUser("scott");
    ods.setPassword("tiger");
    ResultSet res = ods.
      getConnection().
        prepareCall("select 'Hello World' txt from dual").
          executeQuery();
    res.next();
    System.out.println(res.getString("TXT"));
  }
}

This works!