Mani's Blog

December 14, 2012

Error: ORA-16751: failed to switchover to physical standby database. Both Oracle DB says Physical Standby

I ran the switchover to dataguard box.  I ran into issue where both database became physical standby.

Problem:

DGMGRL for Linux: Version 11.1.0.7.0 – 64bit Production

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

Welcome to DGMGRL, type “help” for information.
DGMGRL> connect sys/oracle
Connected.
DGMGRL> swtichover to xnodb01c
Unrecognized command “swtichover”, try “help”
DGMGRL> switchover to xnodb01c
Performing switchover NOW, please wait…
Error: ORA-16751: failed to switchover to physical standby database

Failed.
Unable to switchover, primary database is still “xnodb01cdg”

Solution:

Now I have to choose one database and promote as primary.   Following steps worked well for me.

SQL> select database_role from v$database;

DATABASE_ROLE
—————-
PHYSICAL STANDBY

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

Total System Global Area 2.6724E+10 bytes
Fixed Size            2160272 bytes
Variable Size         8321501552 bytes
Database Buffers     1.8254E+10 bytes
Redo Buffers          146423808 bytes
Database mounted.
SQL> alter database recover managed standby database cancel;
alter database recover managed standby database cancel
*
ERROR at line 1:
ORA-16136: Managed Standby Recovery not active

SQL> alter database recover managed standby database finish;

Database altered.

SQL> ALTER database commit to switchover to primary with session shutdown;

Database altered.

SQL> alter database open;

Database altered.

SQL>  select database_role from v$database;

DATABASE_ROLE
—————-
PRIMARY

SQL> alter system switch logfile;

System altered.

SQL>

Now other DB functioning as Physical Standby with no issues.

Advertisements

December 12, 2012

ORA-16055: FAL request rejected ARCH: FAL archive failed. Archiver continuing

Filed under: Database,Dataguard,Oracle — mani @ 5:35 am
Tags: , , ,

Problem:

We have environment with Primary and Active standby Dataguard.   For some reason storage on Dataguard is crashed and I had rebuild the Dataguard DB from backup.  Noticed Archive log is not being shipped and when I look at alert log  found following errors.

FAL[server, ARC3]: FAL archive failed, see trace file.
Errors in file /local/opt/oracle/diag/rdbms/xnodbcor/XNODBCOR1/trace/XNODBCOR1_arc3_2216.trc:
ORA-16055: FAL request rejected
ARCH: FAL archive failed. Archiver continuing
ORACLE Instance XNODBCOR1 – Archival Error. Archiver continuing.

Checked parameter values on archive_dest on primary DB and noticed “RESET” value assigned to ” log_archive_dest_state_1″

log_archive_dest_state_1         string     RESET

Solution:

Enabled the dest_1 as below, that fixed the problem.   Error disappeared and logs are being shipped and applied on Standby site.

SQL> alter system set log_archive_dest_state_1=enable;

System altered.

SQL> alter system switch logfile;

System altered.

SQL>

October 8, 2012

ORA-16789: standby redo logs not configured

Filed under: Data Guard Broker,Database,Dataguard,Oracle — mani @ 9:42 pm
Tags: ,

In our environment, Dataguard Manager log was showing following errors.

DG 2012-10-08-21:23:11        0 2 0 RSM0: HEALTH CHECK WARNING: ORA-16789: standby redo logs not configured
DG 2012-10-08-21:23:11        0 2 760559130 Operation CTL_GET_STATUS continuing with warning, status = ORA-16789
DG 2012-10-08-21:23:11        0 2 760559130 Operation CTL_GET_STATUS continuing with warning, status = ORA-16809
DG 2012-10-08-21:24:11        0 2 0 RSM Error: LogXptMode value ‘ASYNC’ of requires this database to have status redo logs, but they are not configured.

Show Configuration reports warning “ORA-16608: one or more databases have warnings”

DGMGRL> show configuration

Configuration
Name:                aclu1c
Enabled:             YES
Protection Mode:     MaxPerformance
Databases:
xnodb01c   – Primary database
xnodb01cdg – Physical standby database

Fast-Start Failover: DISABLED

Current status for “aclu1c”:
Warning: ORA-16608: one or more databases have warnings

show database verbose xnodb01cdg  returned success.

show database verbose xnodb01c returned errors, so I am sure the primary DB has issue.
Solution: is to add standby log files to primary DB.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 31 ( ‘+XN_DATA’) SIZE 209715200 reuse;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 32 ( ‘+XN_DATA’) SIZE 209715200 reuse;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 33 ( ‘+XN_DATA’) SIZE 209715200 reuse;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 34 ( ‘+XN_DATA’) SIZE 209715200 reuse;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 35 ( ‘+XN_DATA’) SIZE 209715200 reuse;

Database altered.

Problem solved:

After adding all above standby log files,   Dataguard log stopped reporting the error.  Also dgmgrl stopped reporting the warnings.

DGMGRL> show configuration

Configuration
Name:                aclu1c
Enabled:             YES
Protection Mode:     MaxPerformance
Databases:
xnodb01c   – Primary database
xnodb01cdg – Physical standby database

Fast-Start Failover: DISABLED

Current status for “aclu1c”:
SUCCESS

DGMGRL>

 

September 20, 2012

ORA-16792: configurable property value is inconsistent with database setting

I have DG broker configured on stand-alone database servers (primary and standby).  After I migrated standby database to new ASM diskgroup I ran into issue “Warning: ORA-16608: one or more databases have warnings” and “ORA-16792: configurable property value is inconsistent with database setting”

Restarting the Data guard broker fixed the issue.

DGMGRL> show configuration

Configuration
Name:                contact
Enabled:             YES
Protection Mode:     MaxPerformance
Databases:
xnodbcntdg – Primary database
xnodbcnt   – Physical standby database

Fast-Start Failover: DISABLED

Current status for “contact”:
Warning: ORA-16608: one or more databases have warnings

DGMGRL> show database xnodbcnt

Database
Name:            xnodbcnt
Role:            PHYSICAL STANDBY
Enabled:         YES
Intended State:  APPLY-ON
Instance(s):
XNODBCNT

Current status for “xnodbcnt”:
Warning: ORA-16792: configurable property value is inconsistent with database setting

I just restarted as below.

 

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

System altered.

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

System altered.

SQL>

Then the issue is gone.

 

DGMGRL> show database xnodbcnt

Database
Name:            xnodbcnt
Role:            PHYSICAL STANDBY
Enabled:         YES
Intended State:  APPLY-ON
Instance(s):
XNODBCNT

Current status for “xnodbcnt”:
SUCCESS

DGMGRL>

September 17, 2012

Warning: ORA-16607: one or more databases have failed Error: ORA-16816: incorrect database role

Filed under: Data Guard Broker,Database,Dataguard,Oracle — mani @ 10:01 pm
Tags:

I have Data guard broker configured on my Primary and Standby database servers.  For some reason we had to failover using SQL commnad (didn’t use DG manager).  Later on dgmgrl shows errors as below.

DGMGRL> show configuration

Configuration
Name:                CONTACT
Enabled:             YES
Protection Mode:     MaxPerformance
Databases:
XNODBCNT   – Primary database
XNODBCNTDG – Physical standby database

Fast-Start Failover: DISABLED

Current status for “CONTACT”:
Warning: ORA-16607: one or more databases have failed

DGMGRL> show database ‘XNODBCNT’

Database
Name:            XNODBCNT
Role:            PRIMARY
Enabled:         YES
Intended State:  TRANSPORT-ON
Instance(s):
XNODBCNT

Current status for “XNODBCNT”:
Error: ORA-16816: incorrect database role

Solution: I tried many thing like enabling/disabling configuration, database and other nothing helped,  I had reconfigure it again as below to make DG broker to work properly.

DGMGRL> create configuration CONTACT as primary database is XNODBCNTDG connect identifier is xnodbcntdg;
Configuration “contact” created with primary database “xnodbcntdg”
DGMGRL> add database XNODBCNT as connect identifier is xnodbcnt maintained as physical;
Database “xnodbcnt” added
DGMGRL> show configuration;

Configuration
Name:                contact
Enabled:             NO
Protection Mode:     MaxPerformance
Databases:
xnodbcntdg – Primary database
xnodbcnt   – Physical standby database

Fast-Start Failover: DISABLED

Current status for “contact”:
DISABLED

DGMGRL> enable configuration
Enabled.
DGMGRL> show configuration verbose

Configuration
Name:                contact
Enabled:             YES
Protection Mode:     MaxPerformance
Databases:
xnodbcntdg – Primary database
xnodbcnt   – Physical standby database

Fast-Start Failover: DISABLED

Current status for “contact”:
Warning: ORA-16610: command “EDIT DATABASE xnodbcnt SET PROPERTY” in progress

DGMGRL> show configuration verbose

Configuration
Name:                contact
Enabled:             YES
Protection Mode:     MaxPerformance
Databases:
xnodbcntdg – Primary database
xnodbcnt   – Physical standby database

Fast-Start Failover: DISABLED

Current status for “contact”:
SUCCESS

DGMGRL>

June 22, 2012

Warning: ORA-16607: one or more databases have failed

Filed under: Dataguard,Oracle — mani @ 12:15 am

After the Switchover, you may get a warning “ORA-16607: one or more databases have failed”.   It obviously means you new to restart the new standby database.

1. Display the configuration.

DGMGRL> show configuration verbose
Configuration
  Name:                PRETZEL
  Enabled:             YES
  Protection Mode:     MaxPerformance
  Databases:
    XNOPRTZ   - Primary database
    XNOPRTZDG - Physical standby database
Fast-Start Failover: DISABLED
Current status for "PRETZEL":
Warning: ORA-16607: one or more databases have failed
DGMGRL>
2. Restart the database.
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 2.6724E+10 bytes
Fixed Size          2160272 bytes
Variable Size        1.1543E+10 bytes
Database Buffers     1.5032E+10 bytes
Redo Buffers          146423808 bytes
Database mounted.
SQL>
3. Start the apply process.  Apply command may change based on type of standby.  This is applicable to active Dataguard.
SQL> alter database recover managed standby database cancel;
 
Database altered.
 
SQL> ALTER DATABASE OPEN READ ONLY;
 
Database altered.
 
SQL> alter database recover managed standby database using current logfile disconnect;
 
Database altered.
 
SQL>
4.  Display the config status, make sure the Warning message is gone.
DGMGRL> show configuration verbose
Configuration
  Name:                PRETZEL
  Enabled:             YES
  Protection Mode:     MaxPerformance
  Databases:
    XNOPRTZ   - Primary database
    XNOPRTZDG - Physical standby database
Fast-Start Failover: DISABLED
Current status for "PRETZEL":
SUCCESS
DGMGRL>

June 21, 2012

Setup Oracle Dataguard Broker and Switchover

Filed under: Dataguard,Oracle — mani @ 10:31 pm

Objective

Setting up DG Broker, which can be used to switchover and failover the Database to Physical Standby Database. DG Broker is easier to manage though you could do it from SQL Prompt.

Steps

  1. Check the current state of DG Broker on Primary Database
    SQL> show parameter DG_BROKER_START
    NAME                     TYPE                 VALUE
    ------------------------------------ -------------------------------- ------------------------------
    dg_broker_start              boolean                  FALSE
  2. Start the DG Broker On Primary Database
    SQL> alter system set DG_BROKER_START=true scope=both;
    System altered.
    SQL>
  3. Check the current state of DG Broker on Standby Database
    SQL> show parameter DG_BROKER_START
    NAME                     TYPE                 VALUE
    ------------------------------------ -------------------------------- ------------------------------
    dg_broker_start              boolean                  FALSE
  4. Start the DG Broker On Standby Database
    SQL> alter system set DG_BROKER_START=true scope=both;
    System altered.
    SQL>
  5. Configure Primary Database on Primary DB server.
    [oracle@xnodbprtz01 ~]$ dgmgrl
    DGMGRL for Linux: Version 11.1.0.7.0 - 64bit Production
    Copyright (c) 2000, 2005, Oracle. All rights reserved.
    Welcome to DGMGRL, type "help" for information.
    DGMGRL> connect sys/oracle
    Connected.
    DGMGRL> create configuration 'PRETZEL' as primary database is 'XNOPRTZ' connect identified is xnoprtz;
    create configuration 'PRETZEL' as primary database is 'XNOPRTZ' connect identified is xnoprtz;
                                                                            ^
    Syntax error before or at "identified"
    DGMGRL> create configuration 'PRETZEL' as primary database is 'XNOPRTZ' connect identifier is xnoprtz;
    Configuration "PRETZEL" created with primary database "XNOPRTZ"
    DGMGRL>
  6. Verify the configuration of Primary Database.
    DGMGRL> show configuration;
    Configuration
      Name:                PRETZEL
      Enabled:             NO
      Protection Mode:     MaxPerformance
      Databases:
        XNOPRTZ - Primary database
    Fast-Start Failover: DISABLED
    Current status for "PRETZEL":
    DISABLED
    DGMGRL>
  7. Add Standby DB info on Primary DB Server
    DGMGRL> add database 'XNOPRTZDG' as connect identifier is xnoprtzdg maintained as physical;
    Database "XNOPRTZDG" added
    DGMGRL>
  8. Verify the configuration
    DGMGRL> show configuration
    Configuration
      Name:                PRETZEL
      Enabled:             NO
      Protection Mode:     MaxPerformance
      Databases:
        XNOPRTZ   - Primary database
        XNOPRTZDG - Physical standby database
    Fast-Start Failover: DISABLED
    Current status for "PRETZEL":
    DISABLED
    DGMGRL>
  9. Enable DG Configuration
    DGMGRL> enable configuration
    Enabled.
    DGMGRL> show configuration
    Configuration
      Name:                PRETZEL
      Enabled:             YES
      Protection Mode:     MaxPerformance
      Databases:
        XNOPRTZ   - Primary database
        XNOPRTZDG - Physical standby database
    Fast-Start Failover: DISABLED
    Current status for "PRETZEL":
    Warning: ORA-16610: command "EDIT DATABASE XNOPRTZDG SET PROPERTY" in progress
  10. Restart the DG Broker to get rid of ORA-16610 Warning.
    SQL> ALTER SYSTEM SET DG_BROKER_START=FALSE;
    System altered.
    SQL> ALTER SYSTEM SET DG_BROKER_START=TRUE;
    System altered.
    SQL>
  11. Verify the configuration
    DGMGRL> show configuration
    Configuration
      Name:                PRETZEL
      Enabled:             YES
      Protection Mode:     MaxPerformance
      Databases:
        XNOPRTZ   - Primary database
        XNOPRTZDG - Physical standby database
    Fast-Start Failover: DISABLED
    Current status for "PRETZEL":
    SUCCESS
    DGMGRL>
  12. Get detailed status of the database
    DGMGRL> show database verbose 'XNOPRTZ'
    Database
      Name:            XNOPRTZ
      Role:            PRIMARY
      Enabled:         YES
      Intended State:  TRANSPORT-ON
      Instance(s):
        XNOPRTZ
      Properties:
        DGConnectIdentifier             = 'xnoprtz'
        ObserverConnectIdentifier       = ''
        LogXptMode                      = 'ASYNC'
        DelayMins                       = '0'
        Binding                         = 'OPTIONAL'
        MaxFailure                      = '0'
        MaxConnections                  = '1'
        ReopenSecs                      = '300'
        NetTimeout                      = '30'
        RedoCompression                 = 'DISABLE'
        LogShipping                     = 'ON'
        PreferredApplyInstance          = ''
        ApplyInstanceTimeout            = '0'
        ApplyParallel                   = 'AUTO'
        StandbyFileManagement           = 'AUTO'
        ArchiveLagTarget                = '0'
        LogArchiveMaxProcesses          = '30'
        LogArchiveMinSucceedDest        = '1'
        DbFileNameConvert               = ''
        LogFileNameConvert              = ''
        FastStartFailoverTarget         = ''
        StatusReport                    = '(monitor)'
        InconsistentProperties          = '(monitor)'
        InconsistentLogXptProps         = '(monitor)'
        SendQEntries                    = '(monitor)'
        LogXptStatus                    = '(monitor)'
        RecvQEntries                    = '(monitor)'
        HostName                        = 'xnodbprtz01.ningops.com'
        SidName                         = 'XNOPRTZ'
        StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xnodbprtz01.ningops.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=XNOPRTZ_DGMGRL.ningops.com)(INSTANCE_NAME=XNOPRTZ)(SERVER=DEDICATED)))'
        StandbyArchiveLocation          = '/arch/XNOPRTZ'
        AlternateLocation               = ''
        LogArchiveTrace                 = '0'
        LogArchiveFormat                = '%t_%s_%r.arc'
        LatestLog                       = '(monitor)'
        TopWaitEvents                   = '(monitor)'
    Current status for "XNOPRTZ":
    SUCCESS
    DGMGRL>

Switchover to Standby

  1. Switchover to Standby
    DGMGRL> show configuration verbose
    Configuration
      Name:                PRETZEL
      Enabled:             YES
      Protection Mode:     MaxPerformance
      Databases:
        XNOPRTZ   - Primary database
        XNOPRTZDG - Physical standby database
    Fast-Start Failover: DISABLED
    Current status for "PRETZEL":
    SUCCESS
    DGMGRL> switchover to XNOPRTZDG
    Site "xnoprtzdg" was not found
    DGMGRL> switchover to 'XNOPRTZDG'
    Performing switchover NOW, please wait...
    New primary database "XNOPRTZDG" is opening...
    Operation requires shutdown of instance "XNOPRTZ" on database "XNOPRTZ"
    Shutting down instance "XNOPRTZ"...
    ORA-01017: invalid username/password; logon denied
    You are no longer connected to ORACLE
    Please connect again.
    Unable to shut down instance "XNOPRTZ"
    You must shut down instance "XNOPRTZ" manually
    Operation requires startup of instance "XNOPRTZ" on database "XNOPRTZ"
    You must start instance "XNOPRTZ" manually
    Switchover succeeded, new primary is "XNOPRTZDG"
    DGMGRL>
    DGMGRL> show configuration verbose
    Error:
    ORA-16570: database needs restart
    ORA-06512: at "SYS.X$DBMS_DRS", line 228
    ORA-06512: at line 1
    DGMGRL> exit
  2. Original Primary DB need to be restarted as below
    [oracle@xnodbprtz01 ~]$ dba
    SQL*Plus: Release 11.1.0.7.0 - Production on Thu Jun 21 20:11:04 2012
    Copyright (c) 1982, 2008, Oracle.  All rights reserved.
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    SQL> shutdown immediate;
    ORA-01109: database not open
    Database dismounted.
    ORACLE instance shut down.
    SQL> startup mount
    ORACLE instance started.
    Total System Global Area 2.6724E+10 bytes
    Fixed Size          2160272 bytes
    Variable Size        1.1543E+10 bytes
    Database Buffers     1.5032E+10 bytes
    Redo Buffers          146423808 bytes
    Database mounted.
    SQL>
  3. Start the apply process
    SQL> alter database recover managed standby database cancel;
    Database altered.
    SQL> ALTER DATABASE OPEN READ ONLY;
    Database altered.
    SQL> alter database recover managed standby database using current logfile disconnect;
    Database altered.
    SQL>
  4. Verify Current Status
    DGMGRL> show configuration
    Configuration
      Name:                PRETZEL
      Enabled:             YES
      Protection Mode:     MaxPerformance
      Databases:
        XNOPRTZDG - Primary database
        XNOPRTZ   - Physical standby database
    Fast-Start Failover: DISABLED
    Current status for "PRETZEL":
    SUCCESS
    DGMGRL>

Switch Back to Primary Database

  1. Switch
    DGMGRL> switchover to 'XNOPRTZ'
    Performing switchover NOW, please wait...
    New primary database "XNOPRTZ" is opening...
    Operation requires shutdown of instance "XNOPRTZ" on database "XNOPRTZDG"
    Shutting down instance "XNOPRTZ"...
    ORA-01017: invalid username/password; logon denied
    You are no longer connected to ORACLE
    Please connect again.
    Unable to shut down instance "XNOPRTZ"
    You must shut down instance "XNOPRTZ" manually
    Operation requires startup of instance "XNOPRTZ" on database "XNOPRTZDG"
    You must start instance "XNOPRTZ" manually
    Switchover succeeded, new primary is "XNOPRTZ"
    DGMGRL>
  2. Verify the Status
    DGMGRL> show configuration verbose
    Configuration
      Name:                PRETZEL
      Enabled:             YES
      Protection Mode:     MaxPerformance
      Databases:
        XNOPRTZ   - Primary database
        XNOPRTZDG - Physical standby database
    Fast-Start Failover: DISABLED
    Current status for "PRETZEL":
    Warning: ORA-16607: one or more databases have failed
    DGMGRL>
  3. Restart the database on Standby box
    [oracle@xnodbprtzdg01 dbs]$ dba
    SQL*Plus: Release 11.1.0.7.0 - Production on Thu Jun 21 20:53:57 2012
    Copyright (c) 1982, 2008, Oracle.  All rights reserved.
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    SQL> shutdown immediate;
    ORA-01109: database not open
    Database dismounted.
    ORACLE instance shut down.
    SQL> startup mount
    ORACLE instance started.
    Total System Global Area 2.6724E+10 bytes
    Fixed Size          2160272 bytes
    Variable Size        1.1543E+10 bytes
    Database Buffers     1.5032E+10 bytes
    Redo Buffers          146423808 bytes
    Database mounted.
    SQL>
  4. Start the apply process
    SQL> alter database recover managed standby database cancel;
     
    Database altered.
     
    SQL> ALTER DATABASE OPEN READ ONLY;
     
    Database altered.
     
    SQL> alter database recover managed standby database using current logfile disconnect;
     
    Database altered.
     
    SQL>
  5. Verify the status finally
    DGMGRL> show configuration verbose
    Configuration
      Name:                PRETZEL
      Enabled:             YES
      Protection Mode:     MaxPerformance
      Databases:
        XNOPRTZ   - Primary database
        XNOPRTZDG - Physical standby database
    Fast-Start Failover: DISABLED
    Current status for "PRETZEL":
    SUCCESS
    DGMGRL>

Create a free website or blog at WordPress.com.