Remote Oracle Net connections hang -Doc ID 1302367.1

Symptoms:  Something may occur where there is a connectivity problem between clients or servers and a remote database such as standby or DR site.  What happens with a SQLPLUS test hang.  For example:  sqlplus username/password@remote_sid--> hangs

Cause determination:  One potential cause of this condition, is that a TCP/IP network device (firewall, router, etc.) located between the client and server is set to restrict or limit communication through it when nay MTU or packet size is exceeded.  This is an MTU setting at the network level, so the systems or network admin needs to analyze and correct the MTU setting.  The MTU setting problem is typical issue that caused the sqlplus in the remote site/standby site hang.

To enable traces for the hanging sqplus session:

sqlnet.ora, you need to add
DIAG_ADR_ENABLED=OFF
TRACE_LEVEL_CLIENT=16
TRACE-DIECTORY_CLIENT= <location> is the directory to write trace file to

Solution:  Verify the MTU settings from the network devices.

Changing the Broker Configuration Properties

You can modify the configuration, database, and instance using the EDIT command.  See several examples below

Changing the Broker Configuration Properties
DGMGRL> show database verbose 'DBATOOLS'
Database - DBATOOLS
  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    DBATOOLS
  Properties:
    DGConnectIdentifier             = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=pi01.an.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DBATOOLS)(SERVER=DEDICATED)))'
    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          = '4'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = ''
    LogFileNameConvert              = ''
    FastStartFailoverTarget         = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    SidName                         = 'DBATOOLS'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=pi01.an.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DBATOOLS_DGMGRL)(INSTANCE_NAME=DBATOOLS)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'
Database Status:
SUCCESS
DGMGRL> show database 'DBATOOLS' statusreport
STATUS REPORT
       INSTANCE_NAME   SEVERITY ERROR_TEXT
      
DGMGRL> show database 'DBATOOLS' logshipping;
  LogShipping = 'OFF' 
DGMGRL> edit database 'DBATOOLS' set Property Logshipping='ON';
Property "logshipping" updated
DGMGRL> show database 'DBATOOLS' logshipping;
  LogShipping = 'ON'
DGMGRL> show database 'DBATOOLS' lOgXptMode;
  LogXptMode = 'ASYNC'
DGMGRL> edit database 'DBATOOLS' set Property LogXptMode='SYNC';
Property "logxptmode" updated
DGMGRL> show database 'DBATOOLS' LogXptMode
  LogXptMode = 'SYNC'

To turn transport on / off using TRANSPORT-ON/TRANSPORT-OFF
DGMGRL> edit database 'DBATOOLS' SET STATE=TRANSPORT-OFF;
ALTER SYSTEM SET log_archive_dest_state_2='RESET' SCOPE=BOTH;

DGMGRL> edit database 'DBATOOLS' SET STATE=TRANSPORT-ON';
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;
To turn the apply services on or off on the standby database
DGMGRL> show configuration
Configuration - DBATOOLS
  Protection Mode: MaxPerformance
  Databases:
    DBATOOLS - Primary database
    STBYTOOL - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS

Changing The State of A Database
DGMGRL> edit database 'DBATOOLS' set STATE=APPLY-OFF;
Error: ORA-16516: current state is invalid for the attempted operation
Failed.
DGMGRL> edit database 'STBYTOOL' set STATE=APPLY-OFF;
In the alert log, you should see the below line
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL

SQL> alter database open read only;
Database altered.

DGMGRL> edit database 'STBYTOOL' set STATE=APPLY-ON
In the alert log, you should see this line
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  THROUGH ALL SWITCHOVER DISCONNECT  USING CURRENT LOGFILE

Monitor Standby Site

**SCN and additional information stored in the Standby's data file headers


SQL>  select status, to_char(checkpoint_change#, '999999999999999') as checkpoint_change#, to_char(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time,count(*), fuzzy 
from v$datafile_header
group by status,checkpoint_change#,checkpoint_time, fuzzy;

STATUS  CHECKPOINT_CHANG CHECKPOINT_TIME        COUNT(*) FUZ
------- ---------------- -------------------- ---------- ---
ONLINE     5976304015881 07-AUG-2011 04:31:06         44 NO

**Distinct SCNs stored in the Standby's data file headers

SQL> select distinct to_char(fhscn, '99999999999999999999') as Stby_datafile_hdr_SCN
from X$KCVFH;
STBY_DATAFILE_HDR_SCN
---------------------
        5976304015881

**Next archived log sequence number the Standby needs to apply

SQL> select distinct inst_id, fhrba_seq, count(*) from X$KCVFH group by INST_ID,FHRBA_SEQ;
   INST_ID  FHRBA_SEQ   COUNT(*)
---------- ---------- ----------
         1      16683         44

**Use the LOWEST SCNs stored in the Standby's data file headers to extract this backup from the Primary database

SQL> select min(to_char(fhscn, '99999999999999999999')) as SCN_to_be_used from X$KCVFH;
SCN_TO_BE_USED
---------------------
        5976304015881

Monitor Logs From Primary and Standby Databases

Query 1:  - Primary

SQL> select thread#, max(sequence#) "Last Primary Seq Generated"

  2  from v$archived_log val, v$database vdb

  3  where val.resetlogs_change# = vdb.resetlogs_change#

  4  group by thread# order by 1;

   THREAD# Last Primary Seq Generated

---------- --------------------------

         1                      21893

         2                      16200

         3                      14331

         4                      16682

Query2:  - Physical Standby:

SQL> select thread#, max(sequence#) "Last Standby Seq Received"

  2  from v$archived_log val, v$database vdb

  3  where val.resetlogs_change# = vdb.resetlogs_change#

  4  group by thread# order by 1;

   THREAD# Last Standby Seq Received

---------- -------------------------

         1                     21893

         2                     16200

         3                     14331

         4                     16682

Compare the values from Query 1 and Query 2, if there is a difference between the values of "Last Primary Seq Generated" and "Last Standby Seq Received”, the Primary site has isses of shipping logs to the Standby server.

Query 3:  - Physical Standby:

SQL> select thread#, max(sequence#) "Last Standby Seq Applied"

  2  from v$archived_log val, v$database vdb

  3  where val.resetlogs_change# = vdb.resetlogs_change#

  4  and val.applied='YES'

  5  group by thread# order by 1;

   THREAD# Last Standby Seq Applied

---------- ------------------------

         1                    21892

         2                    16199

         3                    14330

         4                    16682

Compare the values from Query 2 and Query 3, if there is a difference between the values of "Last Standby Seq Received" and "Last Standby Seq Applied", the standby has issue or logs have not yet applied.

Data Guard Command-Line

si01:/home/oracle
DGLABS - oracle: dgmgrlDGMGRL for Linux: Version 11.2.0.2.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect /
Connected.
DGMGRL> show configuration
Configuration - DGLABS
  Protection Mode: MaxPerformance
  Databases:
    DGLABS - Primary database
    STBYLAB - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS

DGMGRL> show database 'DGLABS'
Database - DGLABS
  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    DGLABS
Database Status:
SUCCESS

DGMGRL> edit database 'DGLABS' set property logshipping='OFF';
DGMGRL> edit database 'DGLABS' set property logshipping='ON';
DGMGRL> show database 'DGLABS' logshipping;
DGMGRL> edit database STBYLAB set state='APPLY-OFF';
DGMGRL> edit database 'STBYLAB' set state='APPLY-ON';

....

Implement Oracle Data Guard using EM Grid Control.

If you want to use the Grid Control to create and manage Data Guard, you need the following components: Oracle Management Server (OMS), repository database installed, and Grid Control Agent installed on the servers in your Data Guard Configuration - both primary and standby database systems

The remaining steps are just navigating along and answering the questions.  Once you finish answering questions, just click Finish. 

Below are the example of configurations that the answers I made to all the questions.

Primary Database              

Target Name                      CHICAGO               

Database Name                  chicago   

Instance Name                   CHICAGO               

Database Version               11.2.0.2.0               

Oracle Home                      /oracle/product/11.2.0/RDBMS   

Host                                 si01.an.com          

Operating System               Red Hat Enterprise Linux Server release 5.6 (Tikanga) 2.6.18          

Host Username                   oracle   

Standby Database             

Target Name                      NEWYORK             

Database Name                  CHICAGO               

Instance Name                   CHICAGO               

Oracle Server Version          11.2.0.2.0               

Oracle Home                      /oracle/product/11.2.0/RDBMS   

Host                                 pi01.an.com         

Operating System:              Red Hat Enterprise Linux Server release 5.6 (Tikanga) 2.6.18          

Host Username                   oracle      

Backup Type                      New backup         

File Transfer Method            RMAN duplicate                 

Database Unique Name        NEWYORK             

Database Storage                Automatic Storage Management                

ASM Instance                    +ASM_pi01.an.com  

Standby Type                      Physical Standby                

Database Area                    DG_DATA01         

Flash Recovery Area            DG_FLASH01        

Flash Recovery Area Size (MB)   10061M                  

Automatically Delete Archived Redo Log Files     Yes           

Configure Standby Database with Oracle Restart               No

                 

Standby Database Storage             

Database Area                                           DG_DATA01  

Flash Recovery Area                                    DG_FLASH01  

Multiplex Redo Log Files and Control Files      No  

Tablespaces         

Name Size (MB)                   Status           Type      Standby Location               

SYSAUX                781         ONLINE         PERMANENT      DG_DATA01         

SYSTEM                731         ONLINE         PERMANENT      DG_DATA01         

TEMP2                  1025       ONLINE         TEMPORARY      DG_DATA01         

TOOLS                  101         ONLINE        PERMANENT      DG_DATA01         

UNDOTBS1            71          ONLINE         UNDO              DG_DATA01         

USERS                  54           ONLINE        PERMANENT      DG_DATA01      

  

Directory Objects               

Directory             NamePrimary Directory Path      Standby Directory Path   

MY_EXP                        /tmp/dba/CHICAGO           /tmp/dba/CHICAGO         

DATA_PUMP_DIR           /admin/CHICAGO/dpdump/ /admin/CHICAGO/dpdump/  

ERROR: NMO not setuid-root (Unix-only)

When setting up the standby database uing setup control, you could run into ERROR: NMO not setuid-root (Unix-only).  This error means that the permission of $AGENT_HOME/bin/nmo and $AGENT_HOME/bin/nmb are not correct.  The root cause could be that root.sh script was not run after the Agent installation.

Solution:

 

chown root nmo

chmod 6750

 chown root nmb

 chmod 6750 nmb

Manage and Monitor Standby Database

Stop data guard:

alter database recover managed standby database cancel;

Check DG:

select process, status, thread#, sequence#, block#, blocks from   v$managed_standby; 

check DB role

col db_unique_name format a10

select database_role, db_unique_name, open_mode, protection_mode, protection_level, switchover_status from v$database

DG standby proc

set time on

set lines 132

set pagesize 9999

col client_pid format a12

select pid, process, status, client_process, client_pid, thread#, sequence#, block#, blocks, delay_mins from v$managed_standby

;