Thursday, August 23, 2012

DataGuard and Oracle Restart - how to make it work

If you are going to implement Oracle Data Guard together with Oracle Restart you should be aware that there is a configuration problem in version 11.2.0.3 (and probably in other 11.2. as well). Oracle Restart is not checking what is a current database role and it is going to start standby instance in OPEN mode. This can end up with license issue if your Data Guard Broker will start applying process on standby database. If you don't have license to use Active Data Guard you just broken your license agreement.

There is couple of possible solutions:
  • manually set up "mount" state as start up mode for standby database and change it after every switch- or fail-over
  • disable MRP functionality on standby database and keep it open in read only mode 
  • add your own script to open primary database only and keep standby in "mount" state
I want to describe last solution and share script which I have created for it.
  1. First of all you have to change your Oracle Restart configuration to open both databases (standby and primary) in "mount" state using following command.  
    [oracle@testdb1 ~] srvctl modify database -d database_name -s MOUNT
    
  2. In next step user script has to be created - you can use this one as an template. I have based it on CRS demo script.Script has been save in /tmp directory on both server using name "opendb"
    #!/bin/sh
    # These messages goes into the CRSD agent log file.
    echo " *******   `date` ********** "
    echo "Action script '$_CRS_ACTION_SCRIPT' for resource[$_CRS_NAME] called for action $1"
    #env
    #
    
    #setup database home
    export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db3
    #setup database SID
    export ORACLE_SID=testa
    DBROLE=''
    
    
    # check database role and return following values
    # OPENED if DB is open and it is primary
    # PRIMARY if DB is mounted and DB role is primary
    # PHYSICAL is DB is mounted and DB role is standby
    
    function getrole() {
    
    $ORACLE_HOME/bin/sqlplus -s / as sysdba << EOF
         spool /tmp/getrole.tmp
         set echo off feedback off head off
         select case when OPEN_MODE = 'READ WRITE' then 'OPENED' when OPEN_MODE='MOUNTED' and DATABASE_ROLE='PRIMARY' then 'PRIMARY' when OPEN_MODE='MOUNTED' and DATABASE_ROLE='PHYSICAL STANDBY' then 'PHYSICAL' end from v\$database;
         exit
    EOF
    DBROLE=`cat /tmp/getrole.tmp | sed 's/[ \t]*$//' | sed 's/^[ \t]*//' | tail -1`
    
    }
    
    
    case "$1" in
      'start')
         echo "START entry point has been called.."
         getrole
         # check role and do following actions
         case $DBROLE in
           'PHYSICAL')   echo "This is standby - do nothing" ;;
           'PRIMARY')
         $ORACLE_HOME/bin/sqlplus -s / as sysdba << EOF
         alter database open;
         exit
    EOF
         echo ;;
         esac
         exit 0
         ;;
    
      'stop')
         echo "STOP entry point has been called.."
         exit 0
         ;;
    
      'check')
        echo "CHECK entry point has been called.."
        getrole
        if [ "$DBROLE" == 'OPENED' ] || [ "$DBROLE" == 'PHYSICAL' ]; then
            echo "Check -- SUCCESS"
            exit 0
        else
            echo "Check -- FAILED"
            exit 1
        fi
        ;;
    
      'clean')
         echo "CLEAN entry point has been called.."
         exit 0
         ;;
    
    esac
    
  3. Register user script in Oracle Restart and set up dependencies using following command. Ora.testa.db is my database resource name in CRS - please change to your database resource name. New resource is called "ora.opendb" and it has to be registered on both servers and started.There is hard dependency between my resource and database resource so my script will be started only when database has been started as well.
    [oracle@testdb1 ~]$ /u01/app/oracle/product/11.2.0/grid3/bin/crsctl add resource ora.opendb -type cluster_resource \
    > -attr "ACTION_SCRIPT=/tmp/opendb,CHECK_INTERVAL=30,RESTART_ATTEMPTS=2 \
    > ,START_DEPENDENCIES=hard(intermediate:ora.testa.db)"
    
    [oracle@testdb1 ~]$ /u01/app/oracle/product/11.2.0/grid3/bin/crsctl start resource ora.opendb
    
  4. Check status of resources.
    [oracle@testdb1 ~]$ /u01/app/oracle/product/11.2.0/grid3/bin/crsctl status res -t
    --------------------------------------------------------------------------------
    NAME           TARGET  STATE        SERVER                   STATE_DETAILS
    --------------------------------------------------------------------------------
    Local Resources
    --------------------------------------------------------------------------------
    ora.LISTENER.lsnr
                   ONLINE  ONLINE       testdb1
    ora.ons
                   OFFLINE OFFLINE      testdb1
    --------------------------------------------------------------------------------
    Cluster Resources
    --------------------------------------------------------------------------------
    ora.cssd
          1        OFFLINE OFFLINE
    ora.diskmon
          1        OFFLINE OFFLINE
    ora.evmd
          1        ONLINE  ONLINE       testdb1
    ora.opendb
          1        ONLINE  ONLINE       testdb1
    ora.testa.db
          1        ONLINE  ONLINE       testdb1                  Open
    
    

When everything is deployed is it running in following way: Oracle Restart is starting both data bases in mount state and then next resource (user script) is started. Script is checking current database role and it is opening primary database and doesn't do any action on standby database. 

Disclamer:
Please check script in your development and test environment before you deploy it and change anything in production environment. This script has been created for my personal tests and there is no guarantee that it is bug free.

regards,
Marcin
     
     

Monday, August 6, 2012

Oracle Grid Cloud Control 12c BP1 on Oracle Enterprise Linux 6.1

I have installed it in my test box and I hit a problem with auto-discovery due to missing libssl.so library for nmap. Issue is very easy to fix using common Linux troubleshooting approach. First what versions I'm using:
[root@oem-server ~]# cat /etc/oracle-release
Oracle Linux Server release 6.1
[root@oem-server ~]# openssl version
OpenSSL 1.0.0-fips 29 Mar 2010
[root@oem-server ~]# yum list | grep -e "^openssl"
openssl.x86_64                           1.0.0-20.el6_2.5            @ol6_latest
openssl.i686                             1.0.0-20.el6_2.5            ol6_latest
openssl-devel.i686                       1.0.0-20.el6_2.5            ol6_latest
openssl-devel.x86_64                     1.0.0-20.el6_2.5            ol6_latest
openssl-perl.x86_64                      1.0.0-20.el6_2.5            ol6_latest
openssl-static.x86_64                    1.0.0-20.el6_2.5            ol6_latest
openssl098e.i686                         0.9.8e-17.0.1.el6_2.2       ol6_latest
openssl098e.x86_64                       0.9.8e-17.0.1.el6_2.2       ol6_latest
Now what kind of issue I had
[root@oem-server ~]# /u01/Middleware/agent/agent_inst/discovery/nmap/bin/nmap
/u01/Middleware/agent/agent_inst/discovery/nmap/bin/nmap: error while loading shared libraries: libssl.so.4: cannot open shared object file: No such file or directory
How I fixed it
[oracle@oem-server ~]$  ldd /u01/Middleware/agent/agent_inst/discovery/nmap/bin/nmap
        linux-vdso.so.1 =>  (0x00007fff447ff000)
        libssl.so.4 => not found
        libcrypto.so.4 => not found
        libdl.so.2 => /lib64/libdl.so.2 (0x0000003d40800000)
        libstdc++.so.6 => /usr/lib64/libstdc++.so.6 (0x0000003d43c00000)
        libm.so.6 => /lib64/libm.so.6 (0x0000003d41000000)
        libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x0000003d43400000)
        libc.so.6 => /lib64/libc.so.6 (0x0000003d40400000)
        /lib64/ld-linux-x86-64.so.2 (0x0000003d40000000)

[root@oem-server ~]# ln -s /usr/lib64/libssl.so.10 /usr/lib64/libssl.so.4
[root@oem-server ~]# ln -s /usr/lib64/libcrypto.so.10 /usr/lib64/libcrypto.so.4

[oracle@oem-server ~]$  ldd /u01/Middleware/agent/agent_inst/discovery/nmap/bin/nmap
        linux-vdso.so.1 =>  (0x00007fffb0686000)
        libssl.so.4 => /usr/lib64/libssl.so.4 (0x00007fd6b1c0a000)
        libcrypto.so.4 => /usr/lib64/libcrypto.so.4 (0x00007fd6b1870000)
        libdl.so.2 => /lib64/libdl.so.2 (0x0000003d40800000)
        libstdc++.so.6 => /usr/lib64/libstdc++.so.6 (0x0000003d43c00000)
        libm.so.6 => /lib64/libm.so.6 (0x0000003d41000000)
        libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x0000003d43400000)
        libc.so.6 => /lib64/libc.so.6 (0x0000003d40400000)
        libgssapi_krb5.so.2 => /lib64/libgssapi_krb5.so.2 (0x0000003d48c00000)
        libkrb5.so.3 => /lib64/libkrb5.so.3 (0x0000003d46c00000)
        libcom_err.so.2 => /lib64/libcom_err.so.2 (0x0000003d44000000)
        libk5crypto.so.3 => /lib64/libk5crypto.so.3 (0x0000003d47800000)
        libz.so.1 => /lib64/libz.so.1 (0x0000003d41400000)
        /lib64/ld-linux-x86-64.so.2 (0x0000003d40000000)
        libkrb5support.so.0 => /lib64/libkrb5support.so.0 (0x0000003d46800000)
        libkeyutils.so.1 => /lib64/libkeyutils.so.1 (0x0000003d47000000)
        libresolv.so.2 => /lib64/libresolv.so.2 (0x0000003d42400000)
        libpthread.so.0 => /lib64/libpthread.so.0 (0x0000003d40c00000)
        libselinux.so.1 => /lib64/libselinux.so.1 (0x0000003d41c00000)
Now it is working
[oracle@oem-server ~]$ /u01/Middleware/agent/agent_inst/discovery/nmap/bin/nmap -v

Starting Nmap 5.51.3 ( http://nmap.org ) at 2012-07-08 19:19 IST
Unable to find nmap-services!  Resorting to /etc/services
Read data files from: /etc
WARNING: No targets were specified, so 0 hosts scanned.
Nmap done: 0 IP addresses (0 hosts up) scanned in 0.03 seconds
[oracle@oem-server ~]$
After that I checked MOS and Grid Control 12c is certified with OEL 6 so this errors was not expected but then I realized that I didn't read know issues for Oracle® Enterprise Manager Cloud Control Support Notes for Linux x86 and x86-64 and I found it in point called "Host Discovery Job Displays Error While Loading Shared Libraries". Official solution is a little bit more complicated and required downloading openssl sources and compiling it. Anyway I found article above using Google and I couldn't find it on entry documentation page.

regards,
Marcin

RMAN Transportable tablespace bug

I was a long time since I last used Transportable Tablespaces and I decided to refresh my memory and setup a test environment for Streams using TTS feature. I was going to use example script from Oracle Streams Documentation which is creating necessary data files for specified point of time (no need to switch source tablespace into read only mode) and also is running Data Pump creating meta data file. I have done necessary  changes and run it.
[oracle@testdb1 oracle]$ rsp / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon Aug 6 12:50:04 2012
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

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

Session altered.

SET SERVEROUTPUT ON SIZE 1000000
DECLARE
  until_scn NUMBER;
BEGIN
  until_scn:= DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER;
      DBMS_OUTPUT.PUT_LINE('Until SCN: ' || until_scn);
SYS@testa AS SYSDBA >   2    3    4    5    6  END;
  7  /
Until SCN: 6300731

PL/SQL procedure successfully completed.
SYS@testa AS SYSDBA > exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@testdb1 oracle]$ rlwrap rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Mon Aug 6 12:50:33 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: TESTA (DBID=243207980)

RMAN>  RUN
2>     {
        TRANSPORT TABLESPACE 'SOE_TS'
        UNTIL SCN 6300731
        TABLESPACE DESTINATION '/u01/app/oracle/dest';
        DATAPUMP DIRECTORY SOURCE_DIR
        AUXILIARY DESTINATION '/u01/app/oracle/aux_files'
        DUMP FILE 'soe_ts.dmp'
        EXPORT LOG 'soe.log' 
        IMPORT SCRIPT 'soe_ts.sql'
        TABLESPACE DESTINATION '/u01/app/oracle/dest';
       }

using target database control file instead of recovery catalog
RMAN-05026: WARNING: presuming following set of tablespaces applies to specified point-in-time

List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace UNDOTBS2
...
Unfortunately there is a RMAN bug related to export log file. Due to it first attempt end up with following error
contents of Memory Script:
{
# make read only the tablespace that will be exported
sql clone 'alter tablespace  "SOE" read only';
}
executing Memory Script

sql statement: alter tablespace  "SOE" read only

Performing export of metadata...

Removing automatic instance
shutting down automatic instance
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of transport tablespace command at 08/05/2012 23:18:47
RMAN-06136: ORACLE error from auxiliary database: ORA-01097: cannot shutdown while in a transaction - commit or rollback first
RMAN-06136: ORACLE error from auxiliary database: ORA-01460: unimplemented or unreasonable conversion requested

RMAN>
At first sight error message has nothing to do with log file but I know that RMAN error messaging is well .. different is a good word here. I search for solution and I found out that workaround is quite simple - EXPORT LOG clause has to be removed from script. So let's run it again
[oracle@testdb1 oracle]$ rlwrap rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Mon Aug 6 12:50:33 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: TESTA (DBID=243207980)

RMAN>  RUN
2>       {
        TRANSPORT TABLESPACE 'SOE_TS'
        UNTIL SCN 6300731
        TABLESPACE DESTINATION '/u01/app/oracle/dest';
        DATAPUMP DIRECTORY SOURCE_DIR
        AUXILIARY DESTINATION '/u01/app/oracle/aux_files'
        DUMP FILE 'soe_ts.dmp'
        #EXPORT LOG 'soe.log' - bug
        IMPORT SCRIPT 'soe_ts.sql'
        TABLESPACE DESTINATION '/u01/app/oracle/dest';
      }

using target database control file instead of recovery catalog
RMAN-05026: WARNING: presuming following set of tablespaces applies to specified point-in-time

List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace UNDOTBS2
...
contents of Memory Script:
{
# make read only the tablespace that will be exported
sql clone 'alter tablespace  "SOE_TS" read only';
}
executing Memory Script

sql statement: alter tablespace  "SOE_TS" read only

Performing export of metadata...
   EXPDP> FLASHBACK automatically enabled to preserve database integrity.
   EXPDP> Starting "SYS"."TSPITR_EXP_khar":
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX/FUNCTIONAL_INDEX/INDEX
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
   EXPDP> Master table "SYS"."TSPITR_EXP_khar" successfully loaded/unloaded
   EXPDP> ******************************************************************************
   EXPDP> Dump file set for SYS.TSPITR_EXP_khar is:
   EXPDP>   /u01/app/oracle/dest/soe_ts.dmp
   EXPDP> ******************************************************************************
   EXPDP> Datafiles required for transportable tablespace SOE_TS:
   EXPDP>   /u01/app/oracle/dest/soe_ts01.dbf
   EXPDP> Job "SYS"."TSPITR_EXP_khar" successfully completed at 12:55:35
Export completed
Now looks much better - tablespace has been exported and import script has been generated as well. Now I'm ready for next steps and can continue my work with Streams.

regards,
Marcin