Discoverer Preferences Issue

Scope:

Oracle Discoverer - Version: 10.1.2.1 to 10.1.2.2

Issue:

After Discoverer 10g server has been started, users are unable to run any Discoverer reports. From user browser screen, they see a message "Request Progress - processing your request" without any futher activity.



opmnctl status shows all processes are up and running.

Analysis:

On checking the system performance, we see that the Discoverer Preference Server process dis51pr is consuming very high CPU. On investigating further, we stumble upon a bug “6388696 - DISCOVERER TAKES MORE THEN AN HOUR TO START WORKING AFTER STARTUP”.

The bug states that the Preference server takes a really long time to initialize as it reads all the preferences from the file .reg_key.dc. In our case, this file was more than 3 GB!!

Solution:

As always suggested by support, since we were on the base version, the permanent fix was to apply the cumulative patch. But we chose to go with the workaround, which is to clear the preferences.

Steps to follow:

1. Stop services using opmnctl stopall
2. Back up .reg_key.dc file
3. Run dis51pr –clean
4. Apply preferences
5. Start services using opmnctl startall

DataBroker Demystified

Assumptions:

Primary IP – 10.0.0.1
Standby IP – 10.0.0.2

Changes/Additions to Primary Listener.ora:

SID_LIST_PRIMARY =
  (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME= /mnt/primarydb/10.2.0)
      (SID_NAME = PRIMARY)
      (GLOBAL_DBNAME = PRIMARY)
    )
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /mnt/primarydb/10.2.0)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = PRIMARY)
      (GLOBAL_DBNAME = PRIMARY_DGMGRL)
      (ORACLE_HOME = /mnt/primarydb/10.2.0)
    )
    (SID_DESC =
      (SID_NAME = PRIMARY)
      (GLOBAL_DBNAME = PRIMARY_XPT)
      (ORACLE_HOME = /mnt/primarydb/10.2.0)
    )
  )

Changes/Additions to Standby Listener.ora:

SID_LIST_PRIMARY =
  (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME= /mnt/standbydb/10.2.0)
      (SID_NAME = PRIMARY)
    )
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /mnt/standbydb/10.2.0)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = PRIMARY)
      (GLOBAL_DBNAME = STANDBY_DGMGRL)
      (ORACLE_HOME = /mnt/standbydb/10.2.0)
    )
    (SID_DESC =
      (SID_NAME = PRIMARY)
      (GLOBAL_DBNAME = STANDBY_XPT)
      (ORACLE_HOME = /mnt/standbydb/10.2.0)
    )
  )

To Enable DG Broker Service:

alter system set dg_broker_start=true;

To Bounce DG Broker Service:

alter system set dg_broker_start=false;
alter system set dg_broker_start=true;

Steps to create DRC(Disaster Recovery Configuration):

At Primary –

DGMGRL> CONNECT sys/manager@primary
Connected.
DGMGRL> CREATE CONFIGURATION PRIMARY AS
> PRIMARY DATABASE IS primary
> CONNECT IDENTIFIER IS primary;

Configuration "primary" created with primary database "primary"

DGMGRL> ADD DATABASE standby AS
> CONNECT IDENTIFIER IS standby
> MAINTAINED AS PHYSICAL;

Database "standby" added

DGMGRL> enable configuration;
Enabled.

DGMGRL> show configuration;

Configuration
  Name:                primary
  Enabled:             YES
  Protection Mode:     MaxPerformance
  Fast-Start Failover: DISABLED
  Databases:
    primary - Primary database
    standby - Physical standby database

Current status for "primary":
SUCCESS

DGMGRL> show database verbose standby;

Database
  Name:            standby
  Role:            PHYSICAL STANDBY
  Enabled:         YES
  Intended State:  ONLINE
  Instance(s):
    PRIMARY

  Properties:
    InitialConnectIdentifier        = 'standby'
    LogXptMode                      = 'ARCH'
    Dependency                      = ''
    DelayMins                       = '0'
    Binding                         = 'OPTIONAL'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '180'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'AUTO'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '5'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = ''
    LogFileNameConvert              = ''
    FastStartFailoverTarget         = ''
    StatusReport                    = '(monitor)'
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    HostName                        = 'hostname'
    SidName                         = 'PRIMARY'
    LocalListenerAddress            = '(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.0.2)(PORT=1521))'
    StandbyArchiveLocation          = '/mnt/standbydb/10.2.0/dbs/arch/'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    LatestLog                       = '(monitor)'
    TopWaitEvents                   = '(monitor)'

Current status for "standby":
SUCCESS

DGMGRL> show database verbose primary;

Database
  Name:            primary
  Role:            PRIMARY
  Enabled:         YES
  Intended State:  ONLINE
  Instance(s):
    PRIMARY

  Properties:
    InitialConnectIdentifier        = 'primary'
    LogXptMode                      = 'ARCH'
    Dependency                      = ''
    DelayMins                       = '0'
    Binding                         = 'OPTIONAL'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '180'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'manual'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '5'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = ''
    LogFileNameConvert              = ''
    FastStartFailoverTarget         = ''
    StatusReport                    = '(monitor)'
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    HostName                        = 'hostname'
    SidName                         = 'PRIMARY'
    LocalListenerAddress            = '(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.0.1)(PORT=1521))'
    StandbyArchiveLocation          = '/mnt/primarydb/10.2.0/dbs/arch/'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    LatestLog                       = '(monitor)'
    TopWaitEvents                   = '(monitor)'

Current status for "primary":
SUCCESS

In case some database property is not set properly, use the following example to set it:

DGMGRL> edit database "standby" set property 'LocalListenerAddress'='(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.0.2)(PORT=1521))';
Error: ORA-16703: cannot set property while the database is enabled

Failed.
DGMGRL> disable database standby;
Disabled.
DGMGRL> edit database "standby" set property 'LocalListenerAddress'='(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.0.2)(PORT=1521))';
Property "LocalListenerAddress" updated

Important Logs:

1. $ORACLE_HOME/admin//bdump/drc.log 
2. $ORACLE_HOME/admin//bdump/alter_.log 

Dataguard Configurations Simplified

Assumptions:

Primary IP – 10.0.0.1
Standby IP – 10.0.0.2

Changes/Additions to Primary PFILE (initPRIMARY.ora):

*.fal_server=STANDBY
*.fal_client=PRIMARY
*.log_archive_config='DG_CONFIG=(PRIMARY,STANDBY)'
*.log_archive_dest_1='location=/mnt/primarydb/10.2.0/dbs/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=PRIMARY'
*.log_archive_dest_2='SERVICE=standby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STANDBY'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.LOG_ARCHIVE_MAX_PROCESSES=5 [optional]

Changes/Additions to Standby PFILE (initSTANDBY.ora):

*.fal_server=' PRIMARY'
*.fal_client='STANDBY'
*.log_archive_config='DG_CONFIG=( PRIMARY,STANDBY)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/mnt/standbydb/10.2.0/dbs/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=STANDBY'
*.LOG_ARCHIVE_DEST_2='SERVICE=primary LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRIMARY'

Changes/Additions to Primary Tnsnames.ora:

STANDBY=
        (DESCRIPTION=
                (ADDRESS=(PROTOCOL=tcp)(HOST=10.0.0.2)(PORT=1521))
            (CONNECT_DATA=
                (SID= PRIMARY)
            )
        )

Changes/Additions to Standby Tnsnames.ora:

PRIMARY=
        (DESCRIPTION=
                (ADDRESS=(PROTOCOL=tcp)(HOST=10.0.0.1)(PORT=1521))
            (CONNECT_DATA=
                (SERVICE_NAME= PRIMARY)
                (INSTANCE_NAME= PRIMARY)
            )
        )

STANDBY=
        (DESCRIPTION=
                (ADDRESS=(PROTOCOL=tcp)(HOST=10.0.0.2)(PORT=1521))
            (CONNECT_DATA=
                (SERVICE_NAME= PRIMARY)
                (INSTANCE_NAME= PRIMARY)
            )
        )