Friday, June 23, 2017

RHEL 7.3 update to kernel 3.10.0-514.21.2.el7 makes EM12c agent not to start with ./emctl: line 286: /usr/bin/uname: Argument list too long

After updating the RHEL 7.3 to kernel 3.10.0-514.21.2.el7  EM12c agent did not start due to :

./emctl: line 286: /usr/bin/uname: Argument list too long
./emctl: line 287: /usr/bin/uname: Argument list too long
./emctl: line 466: /oracle/agent12c/core/12.1.0.5.0/perl/bin/perl: Argument list too long

The issue is coming from the [EM_AGENT_HOME]/bin/commonenv, which is called through [EM_AGENT_HOME]/bin/emctl, and sets EM_THREAD_STACK_SIZE for the agent to 3072.

Apparently a BUG introduced with kernel 3.10.0-514.21.2.el7 [BUG 1463241] makes every Linux command (like uname) to fail execute:

$ ulimit -S -s 3072 
$ uname 
-bash: /usr/bin/uname: Argument list too long

In order to bypass this issue you have to set the value more than 4200 

$ ulimit -S -s 4200
[oracleas@s-eacea-wlgui01-d bin]$ uname

Linux

In order to resolve the agent startup issue issue you edit the [EM_AGENT_HOME]/bin/commonenv and change the value of 3072 to any value higher than 4200

e.g

....
if [ "$EM_THREAD_STACK_SIZE" = "" ] ; then
        #EM_THREAD_STACK_SIZE=3072 # Default value
        EM_THREAD_STACK_SIZE=5000 # New value
    fi
    ulimit -S -s $EM_THREAD_STACK_SIZE
....

REFERENCES:
https://bugzilla.redhat.com/show_bug.cgi?id=1463241






Thursday, October 13, 2016

Manually deinstall EM12C agent

The procedure below will use the Installer to remove the agent software from a host.
Before this step you have to use the OMS 12c console to remove the monitored targets and the Agent from OMS. Alternatively this can be done with the emcli tool , but this is not the scope of the post.

ACTIONS

Login the configured oracle user on the host and check if EM agent is running:

output may be different depending your setup !!

$ ps -ef | grep emagent.nohup
oracle    4006     1  0 Aug30 ?        00:00:01 /u01/app/oracle/product/agent12c/core/12.1.0.2.0/perl/bin/perl /u01/app/oracle/product/agent12c/core/12.1.0.2.0/bin/emwd.pl agent /u01/app/oracle/product/agent12c/agent_inst/sysman/log/emagent.nohup
oracle   16962 10385  0 12:30 pts/0    00:00:00 grep emagent.nohup

Agent is running so you need to stop it.

The highlighted directory is the AGENT_HOME !!

$ /u01/app/oracle/product/agent12c/core/12.1.0.2.0/bin/emctl stop agent
......

Then you have to invoke the installer to remove the software.

$ /u01/app/oracle/product/agent12c/core/12.1.0.2.0/oui/bin/runInstaller -deinstall ORACLE_HOME=/u01/app/oracle/product/agent12c -removeallfiles

After the installer starts you have to follow a specific order to safely remove the software:

1. First remove the plugin homes
They are usually named OraHome[X] where X=1,2... , by selecting the in the installer and press remove

2. Then remove the oracle_sysman_db_....agent_Home0 (if exists)

3. Then remove the sbin12C1 home

4. Finally remove the agent12c1 home

ATTENTION : The names for the previous homes may be different in your setup

Close the installer and then remove the agent base directory to cleanup the leftovers

$ cd /u01/app/oracle/product
$ rm -fr agent12c


Wednesday, August 31, 2016

ORA-20011 & KUP-11024 during DBMS_STATS: GATHER_STATS_JOB

ORA-20011: Approximate NDV failed: ORA-29913: error in executing ODCIEXTTABLEOPEN callout
KUP-11024: This external table can only be accessed from within a Data Pump job.

If you encounter these errors in the alert.log of an instance , usually the cause is that an OS file for an external table existed at some point in time but does not now, but the database still believes the OS file for the table exists.

When DBMS_STATS is run against the table in question, it makes a call out to the external table which fails. 

Error KUP-11024 is for temporary Datapump external tables that have not been cleaned up properly. 

Solution:

Ensure that there are no DataPump jobs running at the same time as the DBMS_STATS job run.

The run as sysdba:

1. SQL to identify the external table name and the owner:

select
    owner,
    object_name,
    object_type,
    status,
    to_char(created,'dd-mon-yyyy hh24:mi:ss') created ,
    to_char(last_ddl_time , 'dd-mon-yyyy hh24:mi:ss') last_ddl_time
from
    dba_objects
where
    object_name like 'ET$%' ;

"OWNER" "OBJECT_NAME" "OBJECT_TYPE" "STATUS" "CREATED" "LAST_DDL_TIME"
"SYSTEM" "ET$00F9E3ED0001" "TABLE" "VALID" "11-dec-2012 10:59:30" "11-dec-2012 10:59:30"

2. SQL to check the dirctory the DB thinks the external table is

select owner, TABLE_NAME, DEFAULT_DIRECTORY_NAME, ACCESS_TYPE
from dba_external_tables
ORDER BY 1,2
;

3. Drop the table 

drop table SYSTEM.ET$00F9E3ED0001 purge;

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

Thursday, October 23, 2014

Active-passive High Availability Solution for Oracle Database without Data Guard

Here is a very useful solution to create an Oracle Standby DB without using Data Guard (DG).
Oracle DG  does not supported in 10g & 11g Standard  Editions.

Part1 : http://wadhahdaouehi.tn/2014/10/part1-active-passive-high-availability-solution-for-oracle-database-without-dataguard/

Part2: http://wadhahdaouehi.tn/2014/10/part-2-active-passive-high-availability-solution-for-oracle-database-without-data-guard/


Wednesday, August 6, 2014

How to open a Physical Standby as Primary in an (Active) Data Guard Configuration

Here are the steps to open a Physical Standby as a new independent Database by removing (Active) Data Guard & Data Guard Broker (if exists) Configuration


1. Check the status of the DG COnfiguration

Log on to primary and standby and run in SQLPLUS:

On Primary :

SQL> SELECT OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE FROM V$DATABASE;

OPEN_MODE            PROTECTION_MODE      DATABASE_ROLE
-------------------- -------------------- ----------------
READ WRITE           MAXIMUM PERFORMANCE  PRIMARY


On Standby :

SQL> SELECT OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE FROM V$DATABASE;

OPEN_MODE            PROTECTION_MODE      DATABASE_ROLE
-------------------- -------------------- ----------------
READ WRITE           MAXIMUM PERFORMANCE  PRIMARY


Protection mode should be MAXIMUM PERFORMANCE as above.

If not you have to set it on Primary 

SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;

If you use DG Broker , use DGMGRL login to primary DB and set it as follows:

$ 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@myprimary
Password:
Connected.

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXPERFORMANCE;

Now you will have to remove the DG Broker configuration:

DGMGRL> show configuration verbose;

Configuration - DG_FSF

  Protection Mode: MaxPerformance
  Databases:
    myprimary - Primary database
    mystandby - (*) 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:           eoletbou2
  Observer:         s-eacea-rh-mgmtdbo01-p
  Lag Limit:        30 seconds
  Shutdown Primary: TRUE
  Auto-reinstate:   TRUE

Configuration Status:
SUCCESS

DGMGRL>

Stop Observer
DGMGRL> stop observer;

Disable Fast Start Failover (Active Dataguard)
DGMGRL> DISABLE FAST_START FAILOVER;
Disabled.

Remove the DG configuration
DGMGRL> REMOVE CONFIGURATION;
Removed configuration

DGMGRL>exit;


2. On the primary and standby database set dg_broker_start=false:


SQL> alter system set dg_broker_start=false scope=both;

System altered.

SQL> show parameter dg_broker

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      /oraslow/app/oracle/product/11
                                                 .2.0/dbhome_2/dbs/dr1myprimary.dat
dg_broker_config_file2               string      /oraslow/app/oracle/product/11
                                                 .2.0/dbhome_2/dbs/dr2myprimary
                                                 .dat
dg_broker_start                      boolean     FALSE


3. On both system rename the metadata files shown by the previous command

SQL> ! mv /oraslow/app/oracle/product/11.2.0/dbhome_2/dbs/dr1myprimary.dat.dat /oraslow/app/oracle/product/11.2.0/dbhome_2/dbs/dr1myprimary.back

SQL> ! mv /oraslow/app/oracle/product/11.2.0/dbhome_2/dbs/dr2myprimary.dat /oraslow/app/oracle/product/11.2.0/dbhome_2/dbs/dr2myprimary.back

4. Check the values for the following prameters in both primary and stanby and unset/reset if needed.

ATTENTION : Some of them have been unset when previously removed the DG Broker configuration

show parameter LOG_ARCHIVE_CONFIG 
show parameter DB_FILE_NAME_CONVERT
show parameter LOG_FILE_NAME_CONVERT
show parameter LOG_ARCHIVE_DEST_n pointing to the Standby Database and valid for STANDBY_LOGFILES
show parameter LOG_ARCHIVE_DEST_STATE_n
show parameter DG_BROKER_START
show parameter DG_BROKER_CONFIG_FILE1
show parameter DG_BROKER_CONFIG_FILE2
show parameter STANDBY_ARCHIVE_DEST
show parameter STANDBY_FILE_MANAGEMENT
show parameter FAL_SERVER
show parameter FAL_CLIENT

5. Drop Standby Redologs from the Primary Database

SQL> SELECT GROUP# FROM V$STANDBY_LOG;

SQL>  SELECT GROUP# FROM V$STANDBY_LOG;

    GROUP#
----------
         4
         5
         6
         7

SQL> ALTER DATABASE DROP STANDBY LOGFILE GROUP 4;

Database altered.

SQL> ALTER DATABASE DROP STANDBY LOGFILE GROUP 5;

Database altered.

SQL> ALTER DATABASE DROP STANDBY LOGFILE GROUP 6;

Database altered.

SQL> ALTER DATABASE DROP STANDBY LOGFILE GROUP 7;

Database altered.

6. Shutdown standby and open it in mount state in order to disable managed recovery and activate it (Make Primary)

SQL> shutdown immediate;
SQL> startup mount;

-- Check the mode and the changed parameters

SQL> SELECT OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE FROM V$DATABASE;

OPEN_MODE            PROTECTION_MODE      DATABASE_ROLE
-------------------- -------------------- ----------------
MOUNTED              MAXIMUM PERFORMANCE  PHYSICAL STANDBY


SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

Database altered.

SQL> ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;

Database altered.


SQL> SELECT OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE FROM V$DATABASE;

OPEN_MODE            PROTECTION_MODE      DATABASE_ROLE
-------------------- -------------------- ----------------
MOUNTED              MAXIMUM PERFORMANCE  PRIMARY

SQL> ALTER DATABASE OPEN;

Database altered.

SQL> SELECT OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE FROM V$DATABASE;

OPEN_MODE            PROTECTION_MODE      DATABASE_ROLE
-------------------- -------------------- ----------------
READ WRITE           MAXIMUM PERFORMANCE  PRIMARY

7. Former Physical Standby is now and independent Primary DB 



REFERENCES

https://blogs.oracle.com/AlejandroVargas/resource/How-to-open-the-standby-when-the-primary-is-lost.pdf

Oracle Support Documents : 733794.1 , 975614.1, 261336.1

Monday, July 28, 2014

RMAN-03009: failure of delete command ... ORA-19606: Cannot copy or restore to snapshot control file

This error happen when a snapshot controlfile becomes obsolete, but rman is using it and cannot delete it. The problem that comes out of it is that all other obsolete backups are not deleted also since rman tries first to delete the controlfile copy.

Solution :

Take a note of the cuurent location of the snapshot controlfile and set a new location :

RMAN>show all;

RMAN configuration parameters for database with db_unique_name mydb;

.....

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/myloc/mysnapc.f'; # OLD

RMAN>

RMAN>CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/mylocation/mysnapc.f'; # Configure new location


RMAN>host 'rm /myloc/mysnapc.f'; # delete old file from disk 

RMAN> corsscheck copy; # Mark the deleted file expired

RMAN> delete expired copy; # Delete the expired file from rman catalog/db control file

RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/myloc/mysnapc.f'; # Restore to old location

RMAN>exit;

REFERENCES: Oracle Support Doc ID 1215493.1