Thursday, October 22, 2015

Install ORACLE PSU in an Active Data Guard Environment with FAST START FAILOVER monitored by Data Guard Broker Observer

This procedure will describe installing the 11.2.0.3.15 Oracle PSU on top of 11.2.0.3 with no other PSU / CPU patch installed. 


Patch p21150891_112030_Linux-x86-64.zip contains the Oracle PSU 11.2.0.3.15 (20760997) and the 11.2.0.3.5 (21068553) JVM PSU.

The procedure applies for both, but here is about the installation of the Oracle PSU 11.2.0.3.15 (20760997).

Only one off patch has been applied to both Primary & Standby Oracle Homes.

Latest opatch version has been installed in both Oracle Homes.

It can be used as a reference for 10gR2 up to 11gR2 (11.2.0.4) for any PSU , CPU or JVM patch.

ATTENTION : Its not about Standby First Installabale Patch Set , even the 11.2.0.3.15 is one!

Lets start:


1. Configured Environment


- Primary DB : apfpl
- Standby DB : apfpl2
- Active Data Guard (Phisical Standby in READ ONLY mode) 
- Data Guard Broker is used to manage the configuration - (Configured for Maximum Performance)
- Fast Start Failover has been enabled and Observer has been started on a seperate machine from the DB servers


2. PSU 11.2.0.3.15 Apply Procedure

A. Download & Unzip Patch on the DB servers and check for conflicts ith other patches

$ cd 21150891/20760997/
$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./ 

!! If opatch mentions conflict remove the one off patches. !!


a. On Primary DB Server 

$ $ORACLE_HOME/OPatch/opatch rollback -id 12919564 -connectString apfpl:sys:apfpl 

b. On Standby DB Server 

$ $ORACLE_HOME/OPatch/opatch rollback -id 12919564 -connectString apfpl:sys:apfpl


B. Disable Active Data Guard usign Data Guard Broker



a. Stop redo apply on Standby DB (MRP Process)


$ dgmgrl
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys@apfpl
Password:
Connected.
DGMGRL> show configuration;

Configuration - DG_APFPL_FSF

  Protection Mode: MaxPerformance
  Databases:
    apfpl  - Primary database
    apfpl2 - (*) Physical standby database

Fast-Start Failover: ENABLED

Configuration Status:
SUCCESS

DGMGRL> edit database apfpl2 set state = 'apply-off';
Succeeded.
DGMGRL> show configuration;

Configuration - DG_APFPL_FSF

  Protection Mode: MaxPerformance
  Databases:
    apfpl  - Primary database
      Warning: ORA-16829: fast-start failover configuration is lagging

    apfpl2 - (*) Physical standby database
      Warning: ORA-16829: fast-start failover configuration is lagging

Fast-Start Failover: ENABLED

Configuration Status:
WARNING

DGMGRL>


b. Shutdown the physical standby database and start it to mount state

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 22 10:45:16 2015

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

SQL>shutdown immediate
...
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>startup mount
....
Database mounted
SQL> exit;


c. Connect to DG Broker again and enable redo apply on the Standby

$ dgmgrl
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys@apfpl
Password:
Connected.
DGMGRL> edit database apfpl2 set state = 'apply-on';

!! Wait a bit and then !!

DGMGRL> show configuration

Configuration - DG_APFPL_FSF

  Protection Mode: MaxPerformance
  Databases:
    apfpl  - Primary database
    apfpl2 - (*) Physical standby database

Fast-Start Failover: ENABLED

Configuration Status:
SUCCESS
DGMGRL>exit;


C. On the observer machine connect with dgmgrl  



a. Stop observer & disable Fast Start Failover

$ dgmgrl
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys@apfpl
Password:
Connected.
DGMGRL> stop observer;
Done.
DGMGRL> show configuration;

Configuration - DG_APFPL_FSF

  Protection Mode: MaxPerformance
  Databases:
    apfpl  - Primary database
      Warning: ORA-16819: fast-start failover observer not started

    apfpl2 - (*) Physical standby database
      Warning: ORA-16819: fast-start failover observer not started

Fast-Start Failover: ENABLED

Configuration Status:
WARNING
DGMGRL> disable fast_start failover;
Disabled.
DGMGRL> show configuration;

Configuration - DG_APFPL_FSF

  Protection Mode: MaxPerformance
  Databases:
    apfpl  - Primary database
    apfpl2 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS


b. Disable Primary Site Redo Log transport to Standby DB

DGMGRL> edit database apfpl set state='TRANSPORT-OFF';
Succeeded.
DGMGRL> show database apfpl;

Database - apfpl

  Role:            PRIMARY
  Intended State:  TRANSPORT-OFF
  Instance(s):
    apfpl

Database Status:
SUCCESS
DGMGRL> exit


D. Apply Patch on Standby Database Server 



a. Stop All Oracle Processes on Standby Database Server

$ export ORACLE_SID=apfpl
$ lsnrctl stop
The command completed successfully
$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 22 10:45:16 2015

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
SQL>shutdown immediate
...
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>exit;


b. Install PSU on Standby Server Oracle Home

$ cd 21150891/20760997
$ $ORACLE_HOME/OPatch/opatch apply
....
OPatch succeeded.

!! If opatch completes with warnings , please check in Oracle Support site.    !!
!! In most of cases is ok to proceed, otherwise follow Oracle Support Advice. !!

c. Start Listener and Standby Database in mount state

$ lsnrctl start
The command completed successfully
$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 22 10:45:16 2015

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
SQL> startup mount
Database mounted.

E. Apply Patch on Primary Database Server 

a. Stop All Oracle Processes on Primary Database Server

$ export ORACLE_SID=apfpl
$ lsnrctl stop
The command completed successfully
$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 22 10:45:16 2015

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
SQL>shutdown immediate
...
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>exit;


b. Install PSU on Primary Server Oracle Home

$ cd 21150891/20760997
$ $ORACLE_HOME/OPatch/opatch apply
....
OPatch succeeded.


F. Start Primary in upgrade state and run the upgrade script

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 22 10:45:16 2015

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
SQL> startup upgrade
Database opened.
SQL> @?/rdbms/admin/catbundle.sql psu apply

!! Check the log report in the end of the script for errros !!

SQL> shutdown immediate
Database Closeed
SQL>startup
Database opened
SQL>@?/rdbms/admin/utlrp
SQL>exit;


G. On the Observer Machine re-enable Primary Site archiving to Standby Site

$ dgmgrl
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys@apfpl
Password:
Connected.
DGMGRL> edit database apfpl set state='TRANSPORT-ON';
Succeeded.

!! Wait for Standby Database to Re-sync with Primary by applying the upgrade redo logs !!
!! Check the Standby alert log until you see the following                                                         !!

.......
Media Recovery Waiting for thread 1 sequence 33 (in transit)
Recovery of Online Redo Log: Thread 1 Group 4 Seq 33 Reading mem 0
  Mem# 0: /oraslow/oradata/apfpl2/apfdbou2/standby_redo01.log


H. Re-enable Active Data Guard & Fast Failover of the configuration



a. Stop redo apply on Standby

DGMGRL> edit database apfpl2 set state = 'apply-off';
Succeeded.
DGMGRL>show configuration


b. Open the physical standby database in read-only mode 

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 22 10:45:16 2015

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
SQL> alter database open read only;
Database opened.


c. Re-enable redo apply on Standby

DGMGRL> edit database apfpl2 set state = 'apply-on';
Succeeded.


d. Enable Fast Start Failover

DGMGRL> enable fast_start failover;
Enabled.


e. Start the Observer 

DGMGRL> start observer;
Started.

f. Check the configuration

DGMGRL> show configuration verbose;

Configuration - DG_APFPL_FSF

  Protection Mode: MaxPerformance
  Databases:
    apfpl  - Primary database
    apfpl2 - (*) Physical standby database

  (*) Fast-Start Failover target

  Properties:
    FastStartFailoverThreshold      = '30'
    OperationTimeout                = '30'
    FastStartFailoverLagLimit       = '30'
    CommunicationTimeout            = '180'
    FastStartFailoverAutoReinstate  = 'TRUE'
    FastStartFailoverPmyShutdown    = 'TRUE'
    BystandersFollowRoleChange      = 'ALL'

Fast-Start Failover: ENABLED

  Threshold:        30 seconds
  Target:           apfpl2
  Observer:         s-eacea-rh-mgmtdbo01-p
  Lag Limit:        30 seconds
  Shutdown Primary: TRUE
  Auto-reinstate:   TRUE

Configuration Status:
SUCCESS
DGMGRL>exit;


YOU ARE DONE !!


Wednesday, October 14, 2015

Oracle Streams & CDC 11gR2 - ORA-01341: LogMiner out-of-memory

The ORA-01341: LogMiner out-of-memory occurs when LogMiner session is out of memory and causes the Capture Process to abort.

The problem is often encountered after the database was upgraded to 11.2.0.3

This means that the Capture Process needs to be configured internally to have more memory space:

1. Find the capture name:

select capture_name from dba_capture;

CAPTURE_NAME                 
------------------------------
MYCAPTURE      

2. Stop capture , set the new size and start the capture

exec dbms_capture_adm.stop_capture('MYCAPTURE');
exec dbms_capture_adm.set_parameter('MYCAPTURE','_SGA_SIZE','80');
exec dbms_capture_adm.start_capture('MYCAPTURE');

The default size of space for capture / log miner activity is 10 M. A value for 80M - 150M should be sufficient , but can be increased even more.


If you have this error frequently then you may have hit BUG 16747736 LogMiner memory leak witch is fixed on 11.2.3.0.10 PSU onwards and in 11.2.0.4.



REFERENCES
Oracle support Documents :
1564035.1
16747736.8
335516.1



Tuesday, June 30, 2015

Oracle 11g Transaction Backout

In some times there is a need to undo a whole transaction that changes a lot of tables and their dependence. From Oracle 11g and onward this can be done using Transaction Backout feature of Flashback features.

Here is an example of a session that connected , changed data in lots of tables, commited and disconnected.

DB Username for the example is MYUSER

1. Login as sys dba and start a logminer session using the time that the transaction happened

BEGIN
  dbms_logmnr.start_logmnr ( starttime => to_timestamp('30/06/15 11:10',
  'DD/MM/YY HH24:MI:SS'), endtime => to_timestamp('30/06/15 11:15',
  'DD/MM/YY HH24:MI:SS'), OPTIONS => dbms_logmnr.dict_from_online_catalog +
  dbms_logmnr.continuous_mine                                             +
  dbms_logmnr.no_sql_delimiter                                            +
  dbms_logmnr.print_pretty_sql );
END;
/

2. Find all the transaction of the MYUSER for the logminer session  oppened. You ma need to spool the output to a file if there are a lot of transactions:

select username , xid , operation , sql_redo
from v$logmnr_contents
WHERE username = 'MYUSER';

3. Since you identified the XID end the logminer session:

BEGIN
  DBMS_LOGMNR.END_LOGMNR;
END;
/

4. No use dbms_flashback.TRANSACTION_BACKOUT to backout the transaction:

BEGIN
  dbms_flashback.TRANSACTION_BACKOUT ( numtxns => 1 , xids => xid_array(
  '02001200ED010000'), OPTIONS => DBMS_FLASHBACK.CASCADE );
END;
/

5. Before you commit , just check your data to confirm that the transaction was backed out using the query below

SELECT
  a.xid ,
  b.xid_report
FROM
  dba_flashback_txn_state a ,
  dba_flashback_txn_report b
WHERE
  a.compensating_xid = b.compensating_xid
AND a.xid            = '02001200ED010000';


6. If you are ok then commit and the transaction will be backed out.

REMARKS

DBMS_FLASHBACK.TRANSACTION_BACKOUT options default value is NOCASCADE

Here is an explanation of the options values:

NOCASCADE  rollbacks the specified transaction, but if a dependency is found, an error will be raised.

NOCASCADE_FORCE  rollbacks the specified transaction without considering any dependent transactions.

CASCADE rollbacks the specified transaction along with the dependent transactions (child first parent later)


NONCONFLICT_ONLY Only non conflicting rows of the transaction are rolled back.


REFERENCES:

http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_flashb.htm#CHDFBDCG
http://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#ADFNS1008


Friday, June 5, 2015

Database open resetlogs fails with ORA-00392: log X of thread Y is being cleared, operation not allowed

I encountered this error when during point in time recovery the server i run out of space.

When i freed up some space and tried to pen the database with the resetlogs clause i got ORA-00392:

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00392: log 3 of thread 1 is being cleared, operation not allowed
ORA-00312: online log 3 thread 1:
'/aux/AUX/onlinelog/o1_mf_3_%u_.log'
ORA-00312: online log 3 thread 1:
'/aux/fra/AUX/onlinelog/o1_mf_3_%u_.log'

This happened because at the time that DB was trying to clear and archive the online logs (after the open resetlogs command) the server run out of space and operation did not completed successfully.

After releasing space on the server the solution is the following :

SQL> alter database clear unarchived logfile group 3;

Database altered.

SQL> alter database open resetlogs;

Database altered.


Thursday, March 19, 2015

Oracle Change Data Capture (CDC) Apply encountered ORA-54013

This issue is raised on 11g when a function based index is created on the CDC's schema change set's change table.

Then a transaction against this table (Insert, update, delete) will not apply the change and will fail with ORA-54013: INSERT operation disallowed on virtual columns
recorder on the DBA_APPLY_ERROR table.

Work around is to drop the function based index from the change table, restart capture and apply process and then re-execute the failed transactions errors.

As sys execute : dbms_apply_adm.execute_all_errors;

REFERENCES: Oracle Support Doc ID 1555572.1

Thursday, March 5, 2015

Relink Oracle binaries


Among other reasons after upgrading or patching the O/S it is recommended by Oracle to relink the Oracle binaries.

This can be done manually as following:

1. Set environment variables

DISPLAY
TERM or ORACLE_TERM
ORACLE_HOME
PATH to include $ORACLE_HOME/bin
LD_LIBRARY_PATH $ORACLE_HOME/lib:/usr/lib


2. Perform the relink

As oracle user on the DB server execute:

-- All versions


$ORACLE_HOME/bin/relink all >> relink.log

 -- Additional for 11GR2 using the OUI

$ORACLE_HOME/oui/bin/runInstaller -relink -waitForCompletion -maketargetsxml  $ORACLE_HOME/inventory/make/makeorder.xml  -logLocation $ORACLE_HOME/install ORACLE_HOME=$ORACLE_HOME > $ORACLE_HOME/install/relink.log 2>&1


REFERENCES:

Oracle support Documents 1467060.1,883299.1