Fast-Start Failover on Oracle Error Conditions

Using Oracle 11g Data Guard configuration you can initiate Fast-Start Failover
based on different Oracle errors - Configure Conditions for Fast-start Failover

I decided to test it and created DG configuration using not only documentation
but running "ENABLE FAST_START FAILOVER" command, getting errors :) ,
looking into drc...log files (it is 11g, they are under trace directory),
correcting problems like: standby redo log files, flashback mode, increasing
Fast-Start Failover Lag Limit and finally got SUCCESS status for
"SHOW CONFIGURATION VERBOSE":

DGMGRL> show configuration verbose

Configuration
  Name:                bee
  Enabled:             YES
  Protection Mode:     MaxPerformance
  Databases:
    bee    - Primary database
    beesby - Physical standby database
           - Fast-Start Failover target

Fast-Start Failover: ENABLED
  Threshold:           30 seconds
  Target:              beesby
  Observer:            oel1.pythian
  Lag Limit:           30 seconds
  Shutdown Primary:    TRUE
  Auto-reinstate:      TRUE

Current status for "bee":
SUCCESS

To see conditions which are configured to initiate FSF you need to execute
another command (I usually run help command and do copy/paste
of commands that I need):

DGMGRL> SHOW FAST_START FAILOVER;

Fast-Start Failover: ENABLED
  Threshold:           30 seconds
  Target:              beesby
  Observer:            oel1.pythian
  Lag Limit:           30 seconds
  Shutdown Primary:    TRUE
  Auto-reinstate:      TRUE

Configurable Failover Conditions
  Health Conditions:
    Corrupted Controlfile          YES
    Corrupted Dictionary           YES
    Inaccessible Logfile            NO
    Stuck Archiver                  NO
    Datafile Offline               YES

  Oracle Error Conditions:
    (none)

There were predefined logically named health conditions for Fast-Start Failover
and no Oracle Error Conditions.

Using help command and provided syntax I set FSF for ORA-00001 error (!)
that means failover should be initiated after violation of unique constraint
(That's terrific condition!)

DGMGRL> ENABLE FAST_START FAILOVER CONDITION 1;
 Succeeded.

alert.log also mentioned it but in decoded manner:

OS Pid: 2964 executed alter system set events '1 trace name failover level 1'

where PID=2964 belonged to DMON process. The error now is on conditions list:

DGMGRL> SHOW FAST_START FAILOVER;

Fast-Start Failover: ENABLED
  Threshold:           30 seconds
  Target:              beesby
  Observer:            oel1.pythian
  Lag Limit:           30 seconds
  Shutdown Primary:    TRUE
  Auto-reinstate:      TRUE

Configurable Failover Conditions
  Health Conditions:
    Corrupted Controlfile          YES
    Corrupted Dictionary           YES
    Inaccessible Logfile            NO
    Stuck Archiver                  NO
    Datafile Offline               YES

  Oracle Error Conditions:
    ORA-00001: unique constraint (%s.%s) violated

The only thing left wass to raise the error - I created the table with PK and tried to insert
duplicated value, got the error:

SQL> insert into t2 values(1);
insert into t2 values(1)
*
ERROR at line 1:
ORA-00001: unique constraint (AG.SYS_C009503) violated

And failover was started:
alert.log on primary

...
OS Pid: 2964 executed alter system set events '1 trace name failover level 1'
Wed May 27 19:30:58 2009
Foreground: user-configured ORA-1 requesting FSFO
Wed May 27 19:31:03 2009
A user-configurable Fast-Start Failover condition was detected. The primary is shutting down due to ORA-00001: unique constraint (%s.%s) violated.
Database bee will not be automatically reinstated.
USER (ospid: 3587): terminating the instance
Instance terminated by USER, pid = 3587
...

alert.log on standby

...
Attempting Fast-Start Failover due to detection of condition ORA-00001: unique constraint (%s.%s) violated.
Database bee will not be automatically reinstated.
...
Failover succeeded. Primary database is now beesby.
...

observer output

...
19:31:05.81  Wednesday, May 27, 2009
Initiating Fast-Start Failover to database "beesby"...
Performing failover NOW, please wait...
Failover succeeded, new primary is "beesby"
19:31:19.15  Wednesday, May 27, 2009
...

Wonderful, failover was done and now I had to reinstate the standby

And that was completed but through several steps (I did not use static configuration of global database name
in listener - that might was the cause of failure to accomplish it at once - I need to check it):

1. startup new standby database in mount mode (bee)
2. REINSTATE DATABASE bee (from dgmgrl connected as sys/sys from beesby environment)
3. startup standby database in mount mode (bee)
4. REINSTATE DATABASE bee (from dgmgrl connected as sys/sys from beesby environment)

=== 1 ===
SQL> startup mount
 ...
Database mounted.

=== 2 ===
 [oracle@oel1 beesby ~]$ dgmgrl sys/sys
DGMGRL for Linux: Version 11.1.0.7.0 - Production

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

Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> REINSTATE DATABASE bee
 Reinstating database "bee", please wait...
Operation requires shutdown of instance "bee" on database "bee"
Shutting down instance "bee"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "bee" on database "bee"
Starting instance "bee"...
Unable to connect to database
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Failed.
You are no longer connected to ORACLE
Please connect again.
Unable to start instance "bee"
You must start instance "bee" manually
Reinstatement of database "bee" failed
DGMGRL> exit

=== 3 ===
 SQL> startup mount
 ...
Database mounted.

===4 ===
 [oracle@oel1 beesby ~]$ dgmgrl sys/sys
DGMGRL for Linux: Version 11.1.0.7.0 - Production

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

Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> REINSTATE DATABASE bee
 Reinstating database "bee", please wait...
Reinstatement of database "bee" succeeded


DGMGRL> show configuration verbose

Configuration
  Name:                bee
  Enabled:             YES
  Protection Mode:     MaxPerformance
  Databases:
    beesby - Primary database
    bee    - Physical standby database
           - Fast-Start Failover target

Fast-Start Failover: ENABLED
  Threshold:           30 seconds
  Target:              bee
  Observer:            oel1.pythian
  Lag Limit:           30 seconds
  Shutdown Primary:    TRUE
  Auto-reinstate:      TRUE

Current status for "bee":
SUCCESS

Have a good day!

TNS names parsing in Data Guard configurations

Building DG configurations for Oracle 10g and 11g databases
I noticed that 11g does not do transformation of tns name
provided at stage of DG creation with regard to Oracle 10g

That might be useful if you need to tune some parameters
of SQL*Net - SDU, RECV_BUF_SIZE, SEND_BUF_SIZE

But some problems can be hidden if full string of connection
is not shown but dr....log can shed some light on them.


Have a good day!

Top 10 topics for Data Guard on Metalink by usage

Metalink Knowledge Base Data Guard top 10 notes by usage

1. 10.2 Data Guard Physical Standby Switchover(751600.1)
   
2. Data Guard Support for Heterogeneous Primary and Standby Systems in Same Data Guard Configuration (413484.1)
   
3. Applying Patchset with a 10g Physical Standby in Place (278641.1)
   
4. The Gains and Pains of Nologging Operations (290161.1)
   
5. Steps To Create Physical Standby Database (736863.1)
   
6. Oracle10g: Data Guard Switchover and Failover Best Practices (387266.1)
   
7. MAA - Creating a RAC Physical Standby for a RAC Primary (380449.1)
   
8. Script to Collect Data Guard Primary Site Diagnostic Information (241374.1)
   
9. MAA - Data Guard Redo Transport and Network Best Practices (387174.1)
   
10. Creating a 10gr2 Data Guard Physical Standby database with Real-Time apply (343424.1)
    

As you can see the 2nd place is for Heterogeneous standbies and also MAA
best practices are definitely on the edge of usage.
I think it shows that standby and Data Guard configuration are used not only to
increase availability of systems but also to migrate from one platform to another
as for example it is possible to migrate from Windows 32-bit to Linux 64-bit
with Oracle 11g and minimal downtime.

Have a good day!

silent installation of agent 10.2.0.5

I recently installed agents 10.2.0.5 on SunOS (SPARC) 5.9 and Linux x86-64
silently and noted the same problem during secure command - the screen
had being erased if password was not sent as a parameter
in emctl secure agent command.

If a password is there - everything is fine, agent was secured (there is less
information about completion in output for that version of agent)

Oracle Enterprise Manager 10g Release 5 Grid Control 10.2.0.5.0.
Copyright (c) 1996, 2009 Oracle Corporation.  All rights reserved.
Agent is already stopped...   Done.
Securing agent...   Started.
Securing agent...   Successful.

But I have to mention that I spent several hours trying
to install agent silently (on Solaris 9) and it was failing:

./runInstaller -silent -responseFile /downloads/agent10.2.0.5/solaris/response/additional_agent.rsp \
FROM_LOCATION="/downloads/agent10.2.0.5/solaris/agent/stage/products.xml" \
BASE_DIR="/opt/app/oracle/agent10g" \
SHOW_ROOTSH_CONFIRMATION=true \
b_doAgentConfig=false \
SHOW_ROOTSH_CONFIRMATION=true \
b_doAgentConfig=false \
METALINK_USERNAME=""
Starting Oracle Universal Installer...

Checking installer requirements...

Checking operating system version: must be 5.8, 5.9 or 5.10.    Actual 5.9
                                     Passed

Checking Temp space: must be greater than 250 MB.   Actual 14145 MB    Passed
Checking swap space: must be greater than 500 MB.   Actual 16220 MB    Passed

All installer requirements met.

Preparing to launch Oracle Universal Installer
from /tmp/OraInstall2009-04-30_01-11-35AM.
Please wait ...
Please specify a Unique Oracle Base directory, 
or remove previously installed products and their directories. 
Directory name should contain only valid alphanumeric characters, 
including '_', or '.' characters.

But the directory /opt/app/oracle/agent10g did not even exist there
and there was no entry about that oracle home neither in inventory.xml
nor in oratab

Resolution was simple - I created a copy of original response file,
removed comments and empty rows, changed parameters accordingly
the environment and it worked!. The installation had been completed,
root.sh had been executed(new requirement came with 10.2.0.5)
and everything was completed.

/tmp/additional_agent.rsp

RESPONSEFILE_VERSION=2.2.1.0.0
UNIX_GROUP_NAME="oinstall"
FROM_LOCATION="../stage/products.xml"
BASEDIR="/opt/app/oracle"
b_upgrade=false
SHOW_WELCOME_PAGE=false
SHOW_NODE_SELECTION_PAGE=false
SHOW_CUSTOM_TREE_PAGE=false
SHOW_COMPONENT_LOCATIONS_PAGE=false
SHOW_SUMMARY_PAGE=false
SHOW_INSTALL_PROGRESS_PAGE=false
SHOW_REQUIRED_CONFIG_TOOL_PAGE=false
SHOW_CONFIG_TOOL_PAGE=false
SHOW_RELEASE_NOTES=false
SHOW_ROOTSH_CONFIRMATION=false
SHOW_END_SESSION_PAGE=false
SHOW_EXIT_CONFIRMATION=false
NEXT_SESSION=false
NEXT_SESSION_ON_FAIL=false
DEINSTALL_LIST={"oracle.sysman.top.agent","10.2.0.5.0"}
SHOW_DEINSTALL_CONFIRMATION=false
SHOW_DEINSTALL_PROGRESS=false
b_silentInstall=true
b_doAgentConfig=false
SHOW_XML_PREREQ_PAGE=false
SHOW_END_OF_INSTALL_MSGS=false
ACCEPT_LICENSE_AGREEMENT=true
TOPLEVEL_COMPONENT={"oracle.sysman.top.agent","10.2.0.5.0"}
SHOW_SPLASH_SCREEN=false
SELECTED_LANGUAGES={"en"}
COMPONENT_LANGUAGES={"en"}
DECLINE_SECURITY_UPDATES=true
METALINK_USERNAME=""
sl_OMSConnectInfo={"grid","4889"}

./runInstaller -silent -responseFile /tmp/additional_agent.rsp

Worth to mention that port should be http for OMS connection
even if OMS is secured (1159 by default)

I hope your installation will go smooth and quick

Have a good day!

Heterogenous standby Windows 32-bit -> Linux 32-bit in Oracle 11g

Based on Metalink Note 413484.1 physical Standby configuration
is supported for mixed platforms.

The configuration that attracted my attention is cascaded physical standby that
can be configured between Windows 32-bit -> Linux 32-bit and Linux 64-bit.

It can be considered as minimal downtime migration from Windows to Linux
using switchover operations.

To setup configuration I started with standby for Windows 32 and Linux 32
and created primary database 11.1.0.7 on Windows and
installed APEX 3.2 there as well as put the same version of RDBMS at Linux side.

To duplicate the database RMAN have to be used but because of differences
between windows and linux like paths "duplicate" command can't be used.
So I took full backup of the database including backup of current
controlfile for standby and copied it over to standby server.

backup as compressed backupset tag 'FOR_STBY' (database)
(current controlfile for standby);

I took the following steps to restore database at standby:
startup nomount;

restore standby controlfile from '/u02/fra/O1_MF_NCNNF_FOR_STBY_4Z0O8NPK_.BKP';

mount database;

catalog start with '/u02/fra';

run {
set newname for datafile 1 to new;
set newname for datafile 2 to new;
set newname for datafile 3 to new;
set newname for datafile 4 to new;
set newname for datafile 5 to new;
set newname for datafile 6 to new;
set newname for tempfile 1 to new;
restore database;
switch datafile all;
switch tempfile all;
}

I started DG broker at primary and standby databases,
added standby log files on standby and created DG configuration:

create configuration dev_dg as primary database is dev
connect identifier is '//192.168.1.3/dev';

add database dev_srv2 as connect identifier
is '//192.168.1.7/dev_srv2' maintained as physical;

enable configuration;

The configuration showed SUCCESS but I checked log files and see
that there are windows style files left in controlfile.

SQL> select member from v$logfile;

MEMBER
-----------------------------------------------
D:\APP\GORYUNOV\ORADATA\DEV\REDO03.LOG
D:\APP\GORYUNOV\ORADATA\DEV\REDO02.LOG
D:\APP\GORYUNOV\ORADATA\DEV\REDO01.LOG
/u02/oradata/DEV_SRV2/onlinelog/o1_mf_5_4z0sofxh_.log
/u02/fra/DEV_SRV2/onlinelog/o1_mf_5_4z0sogml_.log
/u02/oradata/DEV_SRV2/onlinelog/o1_mf_6_4z0soyrl_.log
/u02/fra/DEV_SRV2/onlinelog/o1_mf_6_4z0sozjh_.log

I was able to drop only one group (after cancelling recovery
and changing standby_file_management to manual), others
were not dropped since they needed for crash recovery

When all preparations were done (IP addresses and name to hosts files,
addition of dev_DGMGRL, dev_srv2_DGMGRL service names to listener.ora)
I executed switchover to standby database on Linux
and it was completed successfully:

DGMGRL> switchover to dev_srv2;
Performing switchover NOW, please wait...
New primary database "dev_srv2" is opening...
Operation requires shutdown of instance "dev" on database "dev"
Shutting down instance "dev"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "dev" on database "dev"
Starting instance "dev"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "dev_srv2"

Check of log files in new primary dev_srv2 showed new names:

SQL> select member from v$logfile;
MEMBER
---------------------------------------------------
/u01/app/oracle/product/11.1.0/db_1/dbs/D:APPGORYUNOVORADATADEVREDO03.LOG
/u01/app/oracle/product/11.1.0/db_1/dbs/D:APPGORYUNOVORADATADEVREDO02.LOG
/u02/oradata/DEV_SRV2/onlinelog/o1_mf_5_4z0sofxh_.log
/u02/fra/DEV_SRV2/onlinelog/o1_mf_5_4z0sogml_.log
/u02/oradata/DEV_SRV2/onlinelog/o1_mf_6_4z0soyrl_.log
/u02/fra/DEV_SRV2/onlinelog/o1_mf_6_4z0sozjh_.log

but I easily added new OMF logfiles and dropped files from dbs directory.


After switchover I was able to connect to APEX through standby IP address

And now I need switchover it back and create standby database on
Linux 64-bit
(to be continued...)


Have a good day!

LargeRepository option in EM Grid Control 10.2.0.5

If you have lots of targets, many jobs, getting plenty of alerts in
Enterprise Manager Grid Control you possibly already set
LargeRepository to true in emoms.properties
to speed up navigation of Console.

And of course you already downloaded and upgraded it to 10.2.0.5
which is available for Linux 64-bit as well.

The upgrade and configuration of repository would go well
but you get error 404 after login to Grid Control Console

404 Not Found
Resource /em/home.jsp/page/internalError not found on this server

There is note on Metalink that mentions that problem and
workaround is very simple and straightforward - set LargeRepository to false.

Known Issues during 10.2.0.5 Upgrade

But does everybody want to change the parameter back to false?...
I hope it will be fixed soon.

Have a good day!

silent installation of Enterprise Manager 10g Grid Control Release 5

The Enterprise Manager 10g Grid Control Release 5 for Linux x86-64 is out
on OTN and also there is patch available on Metalink

I already installed 10.2.0.3 (full release) silently and wanted to upgrade
using the same way.

There are steps clearly described in documentation how to upgrade
existing version together with post-installation actions:
Installing Enterprise Manager 10g Grid Control Release 5 (10.2.0.5) Using Existing Database

So I started:
./runInstaller -noconfig -silent \
-responseFile /opt/oracle/install/gc/10205/3731593/Disk1/response/patchset.rsp \
b_softwareonly=true s_sysPassword="sys24" \
ORACLE_HOME="/home/oracle/grid/oms10g" \
oracle.iappserver.st_midtier:szl_InstanceInformation={"rep24"}

It went ok without errors and asked to execute root.sh at the end

After that following further configuration procedures
I set PERL5LIB and started upgrade of OMS repository:
export PERL5LIB=$ORACLE_HOME/perl/lib/5.6.1

$ORACLE_HOME/perl/bin/perl /home/oracle/grid/oms10g/sysman/install/ConfigureGC.pl /home/oracle/grid

It bounced database to remove running jobs (even I did pre-installation steps
for it mentioned in Oracle Enterprise Manager
Grid Control ReadMe for Linux x86-64
10g Release 5

and finally executed RepManager with -action upgrade ... options
spooling output to $OMS_HOME/sysman/log/emrepmgr.log.10.2.0.5.0

When the script had been finished I was able to start oms and it was
showing the newest verison.

Another step was an upgrade of the agent running 10.2.0.4 version

The same installation can be used for agent upgrade but
with ORACLE_HOME related to agent home:
./runInstaller -noconfig -silent \
-responseFile /opt/oracle/install/gc/10205/3731593/Disk1/response/patchset.rsp \
b_softwareonly=true ORACLE_HOME="/home/oracle/grid/agent10g"

And that upgrade went smoothly asking to run root.sh at the end.


I started agent but it could not upload files due to errors in XML files.
So I completely cleaned files there, secured the agent and tried to start
it again but it failed with errors in emagent.trc

ERROR pingManager: Did not receive valid response to ping "ERROR-Agent is blocked. Blocked reason is: Agent is out-of-sync with repository. This most likely means that the agent was reinstalled or recovered. Please contact an EM administrator to unblock the agent by performing an agent resync from the console. Please contact EM adminstrator to unblock the agent"

There is one note on Metalink exactly related to the issue
and method to fix it is to run Synchronization from Grid Control Console
Upload Agent Failing With Error
"OMS Version Not Checked Yet" ,
After Upgrading Agent to 10.2.0.5.0

Submit of synchronization button failed with another error:

Agent Operation completed with errors. For those targets that could not be saved, please go to the target's monitoring configuration page to save them. All other targets have been saved successfully. Agent has not been unblocked. Error saving target EnterpriseManager0.rac2.home_Web Cache:oracle_webcache - Skipping target {EnterpriseManager0.rac2.home_Web Cache, oracle_webcache}: Missing properties - authpwd, authuser

I manually unblocked the agent in there and it started to upload files to OMS
without errors.


Have a good day!