Warning: You are no longer connected to ORACLE" while converting database using DGMGRL.

As part of my OCM Exam preparation while practicing the Data Guard scenarios such as converting the database from physical standby tosnapshot standby and from snapshot standby to physical standbyusing Data Guard Broker, the following warning messages were generated:

Unable to connect to database
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Failed.
Warning: You are no longer connected to ORACLE.

Please complete the following steps and reissue the CONVERT command:
        start up and mount instance "stby" of database "stby"

Here is how, I was testing:

DGMGRL> connect sys@PRIMARY
Password: <enter the password here>
Connected.

Show the configuration:

DGMGRL> show configuration;

Configuration - DGConfig01

  Protection Mode: MaxPerformance
  Databases:
    PRIMARY - Primary database
    stby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

Convert the Physical Standby Database to Snapshot Standby Database:


DGMGRL> convert database 'stby' to snapshot standby;
Converting database "stby" to a Snapshot Standby database, please wait...
Database "stby" converted successfully

Note: Converting the database from Physical Standby to Snapshot Standby database was succeeded, but it failed while reverting from Snapshot Standby to Physical Standby.

DGMGRL> convert database 'stby' to physical standby;
Converting database "stby" to a Physical Standby database, please wait...
Operation requires shutdown of instance "stby" on database "stby"
Shutting down instance "stby"...
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "stby" on database "stby"
Starting instance "stby"...
Unable to connect to database
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Failed.
Warning: You are no longer connected to ORACLE.

Please complete the following steps and reissue the CONVERT command:
        start up and mount instance "stby" of database "stby"

Solution A:

First of all this is just a warning message, all you need to do is startup mount the database as instructed in the above warning message to resolve the issue.

But, the issue is that the Data Guard Broker was attempting to connect to a service called < db_unique_name>_DGMGRL i.e.stby_DGMGRL. On the server, the service name stby_DGMGRL.standbydb in the listener.ora file was configured as follows

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = stby_DGMGRL.standbydb)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = stby)
    )

To overcome from this warning message being generated and to fix the issue permanently,  add a static registration for stby_DGMGRL to your $ORACLE_HOME/network/admin/listener.ora. Below is an example.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = stby_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = stby)
    )
  )

Or Solution B:

Check the value of StaticConnectIdentifier property for stby database:


DGMGRL> show database 'stby' 'StaticConnectIdentifier';
  StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=standby.mydomain)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=stby_DGMGRL)(INSTANCE_NAME=stby)(SERVER=DEDICATED)))'

Here the SERVICE_NAME is stby_DGMGRL (by default it was set like this when Data Guard Broker configured).

Change the SERVICE_NAME from stby_DGMGRL to stby_DGMGRL.standbydb using Data Guard Broker 


DGMGRL> connect sys@stby
Password: <enter the password here>
Connected.

Show the current value for StaticConnectIdentifier property


DGMGRL> show database 'stby' 'StaticConnectIdentifier';
  StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=standby.mydomain)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=stby_DGMGRL)(INSTANCE_NAME=stby)(SERVER=DEDICATED)))'

Edit the value for StaticConnectIdentifier property

DGMGRL> edit database 'stby' set property 'StaticConnectIdentifier'='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=standby.mydomain)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=stby_DGMGRL.standbydb)(INSTANCE_NAME=stby)(SERVER=DEDICATED)))';
Property "StaticConnectIdentifier" updated

Note: Above is a one line command


DGMGRL> show database 'stby' 'StaticConnectIdentifier';
  StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=standby.mydomain)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=stby_DGMGRL.standbydb)(INSTANCE_NAME=stby)(SERVER=DEDICATED)))'


Now start conversion:

DGMGRL> convert database 'stby' to physical standby;
Converting database "stby" to a Physical Standby database, please wait...
Error: ORA-16541: database is not enabled

Failed.
Failed to convert database "stby"

Note: Here we have to first start the standby database in mount mode. Then try the 'convert database' command.

[oracle@ocm01 ~]$ export ORACLE_SID=stby

[oracle@ocm01 ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Wed Apr 10 23:34:47 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

SQL> connect /as sysdba
Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area  680607744 bytes
Fixed Size                  2216464 bytes
Variable Size             406851056 bytes
Database Buffers          264241152 bytes
Redo Buffers                7299072 bytes
Database mounted.

Now start conversion:


DGMGRL> connect sys@stby
Password: <enter the password here>
Connected.

DGMGRL> convert database 'stby' to physical standby;
Converting database "stby" to a Physical Standby database, please wait...
Operation requires shutdown of instance "stby" on database "stby"
Shutting down instance "stby"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "stby" on database "stby"
Starting instance "stby"...
ORACLE instance started.
Database mounted.
Database "stby" converted successfully

Data Guard Broker Configure

Configuration of 10g Data Guard Broker and Observer for Switchover

 Configuring Data Guard Broker for Switchover, General Review.

 On a previous document, 10g Data Guard, Physical Standby Creation, step by step I did describe how to implement a Data Guard
configuration; on this document I'm adding how to configure the broker and observer, setup the database to Maximum Availability and
managing switchover from Data Guard Manager, DGMGRL.
Data Guard Broker permit to manage a Data Guard Configuration, from both the Enterprise Manager Grid Control console, or from a
terminal in command line mode. In this document I will explore command line mode.
Pre requisites include the use of 10g Oracle server, using spfile on both the primary and standby and a third server for the Observer,
and configure the listeners to include a service for the Data Guard Broker.

The Enviroment
     • 2 Linux servers, Oracle Distribution 2.6.9-55 EL i686 i386 GNU/Linux, the Primary and Standby databases are located on these
       servers.
     • 1 Linux server, RH Linux 2.6.9-42.ELsmp x86_64 GNU/Linux, The Data Guard Broker Observer is located on this server
     • Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
       ssh is configured for user oracle on both nodes
     • Oracle Home is on identical path on both nodes
     • Primary database PROD
     • Standby database STDBY

 Step by Step Implementation of Data Guard Broker

Enable Data Guard Broker Start on the Primary and Standby databases
    SQL> ALTER SYSTEM SET DG_BROKER_START=TRUE SCOPE=BOTH;
    System altered.

Setup the Local_Listener parameter on both the Primary and Standby databases
    SQL> ALTER SYSTEM SET LOCAL_LISTENER='(ADDRESS = (PROTOCOL = TCP)(HOST = rac1.localdomain)(PORT = 1521)(IP = FIRST))' SCOPE=BOTH;
    System altered.

Setup the tnsnames to enable communication with both the Primary and Standby databases
The listener.ora should include a service named global_db_nameDGMGRL to enable the broker to start the databases on the event of
switchover. This configuration needs to be included on both servers.

Listener.ora on Node 1

    LISTENER_VMRACTEST =
     (DESCRIPTION_LIST =
       (DESCRIPTION =
         (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.localdomain)(PORT = 1521)(IP = FIRST))
       )
     )
    SID_LIST_LISTENER_VMRACTEST =
     (SID_LIST =
       (SID_DESC =
         (GLOBAL_DBNAME = PROD)
         (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1  )
         (SID_NAME = PROD)
       )
       (SID_DESC =
         (SID_NAME= PROD)
         (GLOBAL_DBNAME = PROD_DGMGRL)
         (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1 )
       )
     )

Listener.ora on Node 2

   LISTENER_VMRACTEST =
     (DESCRIPTION_LIST =
       (DESCRIPTION =
         (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.localdomain)(PORT = 1521)(IP = FIRST))
       )
     )
    SID_LIST_LISTENER_VMRACTEST =
     (SID_LIST =
       (SID_DESC =
         (GLOBAL_DBNAME = STDBY)
         (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1  )
         (SID_NAME = STDBY)
       )
       (SID_DESC =
         (SID_NAME= STDBY)
         (GLOBAL_DBNAME = STDBY_DGMGRL)
         (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1 )
       )
     )

Tnsnames.ora on Node 1, 2 and the observer node

   PROD =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.localdomain)(PORT = 1521))
      (CONNECT_DATA =
        (SERVER = DEDICATED)
        (SERVICE_NAME = PROD_DGMGRL)
      )
    )
   STDBY =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST =  rac2.localdomain)(PORT = 1521))
      (CONNECT_DATA =
        (SERVER = DEDICATED)
        (SERVICE_NAME = STDBY_DGMGRL)
      )
 )

Setup the Broker configuration files

   The broker configuration files are automatically created when the broker is started using ALTER SYSTEM SET
   DG_BROKER_START=TRUE.
   The default destination can be modified using the parameters DG_BROKER_CONFIG_FILE1 and DG_BROKER_CONFIG_FILE2
On Primary:
SQL>SHOW PARAMETERS DG_BROKER_CONFIG

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      /u01/app/oracle/product/10.2.0
                                                 /db_1/dbs/dr1PROD.dat
dg_broker_config_file2               string      /u01/app/oracle/product/10.2.0
                                                 /db_1/dbs/dr2PROD.dat

On standby:
SQL> SHOW PARAMETERS DG_BROKER_CONFIG

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      /u01/app/oracle/product/10.2.0
                                                 /db_1/dbs/dr1STDBY.dat
dg_broker_config_file2               string      /u01/app/oracle/product/10.2.0
                                                 /db_1/dbs/dr2STDBY.dat

Next create from within the DGMGRL the configuration
[oracle@rac1 ~]$ dgmgrl
DGMGRL for Linux: Version 10.2.0.1.0 - Production

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

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/oracle@PROD
Connected.
DGMGRL> create configuration PROD AS
> PRIMARY DATABASE IS PROD
> CONNECT IDENTIFIER IS PROD;

Configuration "PROD" created with primary database "PROD"


Add the standby to the configuration and check it

DGMGRL> ADD DATABASE STDBY AS
> CONNECT IDENTIFIER IS STDBY
> MAINTAINED AS PHYSICAL;
Database "STDBY" added

DGMGRL> SHOW CONFIGURATION;

Configuration
  Name:                PROD
  Enabled:             NO
  Protection Mode:     MaxPerformance
  Fast-Start Failover: DISABLED
  Databases:
    PROD - Primary database
    STDBY   - Physical standby database

Current status for "PROD":
DISABLED

DGMGRL> SHOW DATABASE VERBOSE STDBY;

Database
  Name:            STDBY
  Role:            PHYSICAL STANDBY
  Enabled:         NO
  Intended State:  OFFLINE
  Instance(s):
    STDBY

  Properties:
    InitialConnectIdentifier        = 'STDBY'
    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          = '30'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = '/u01/app/oracle/oradata/PROD/, /u01/app /oracle/oradata/STDBY/'
    LogFileNameConvert              = '/u01/app/oracle/oradata/PROD/, /u01/app /oracle/oradata/STDBY/'
    FastStartFailoverTarget         = ''
    StatusReport                    = '(monitor)'
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    HostName                        = 'rac2'
    SidName                         = 'STDBY'
    LocalListenerAddress            = '(ADDRESS=(PROTOCOL=TCP)(HOST=rac2.localdo main)(PORT=1521))'
    StandbyArchiveLocation          = '/u01/app/oracle/oradata/STDBY/arch/'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.arc'
    LatestLog                       = '(monitor)'
    TopWaitEvents                   = '(monitor)'

Current status for "STDBY":
DISABLED

DGMGRL> show database verbose PROD;

Database
  Name:            PROD
  Role:            PRIMARY
  Enabled:         NO
  Intended State:  OFFLINE
  Instance(s):
    PROD

  Properties:
    InitialConnectIdentifier        = 'PROD'
    LogXptMode                      = 'ASYNC'
    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          = '30'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = '/u01/app/oracle/oradata/STDBY/, /u01/app/o racle/oradata/PROD/'
    LogFileNameConvert              = '/u01/app/oracle/oradata/STDBY/, /u01/app/o racle/oradata/PROD/'
    FastStartFailoverTarget         = ''
    StatusReport                    = '(monitor)'
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    HostName                        = 'rac1'
    SidName                         = 'PROD'
    LocalListenerAddress            = '(ADDRESS=(PROTOCOL=TCP)(HOST=rac1.localdo main)(PORT=1521))'
    StandbyArchiveLocation          = '/u01/app/oracle/oradata/PROD/arch/'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.arc'
    LatestLog                       = '(monitor)'
    TopWaitEvents                   = '(monitor)'

Current status for "PROD":
DISABLED

DGMGRL> enable configuration;
Enabled.
DGMGRL> show configuration;

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

Current status for "PROD":
SUCCESS

DGMGRL> enable database STDBY;
Enabled.
DGMGRL> SHOW DATABASE VERBOSE STDBY;

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

  Properties:
    InitialConnectIdentifier        = 'STDBY'
    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          = '30'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = '/u01/app/oracle/oradata/PROD/, /u01/app/oracle/oradata/STDBY/'
    LogFileNameConvert              = '/u01/app/oracle/oradata/PROD/, /u01/app/oracle/oradata/STDBY/'
    FastStartFailoverTarget         = ''
    StatusReport                    = '(monitor)'
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    HostName                        = 'rac2'
    SidName                         = 'STDBY'
    LocalListenerAddress            = '(ADDRESS=(PROTOCOL=TCP)(HOST=rac2.localdomain)(PORT=1521))'
    StandbyArchiveLocation          = '/u01/app/oracle/oradata/STDBY/arch/'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.arc'
    LatestLog                       = '(monitor)'
    TopWaitEvents                   = '(monitor)'

Current status for "STDBY":
SUCCESS

Enabling the configuration and databases
DGMGRL> enable configuration;
Enabled.
DGMGRL> show configuration;

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

Current status for "PROD":
SUCCESS

DGMGRL> enable database STDBY;
Enabled.
DGMGRL> SHOW DATABASE VERBOSE STDBY;

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

  Properties:
    InitialConnectIdentifier        = 'STDBY'
    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          = '30'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = '/u01/app/oracle/oradata/PROD/, /u01/app/oracle/oradata/STDBY/'
    LogFileNameConvert              = '/u01/app/oracle/oradata/PROD/, /u01/app/oracle/oradata/STDBY/'
    FastStartFailoverTarget         = ''
    StatusReport                    = '(monitor)'
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    HostName                        = 'rac2'
    SidName                         = 'STDBY'
    LocalListenerAddress            = '(ADDRESS=(PROTOCOL=TCP)(HOST=rac2.localdomain)(PORT=1521))'
    StandbyArchiveLocation          = '/u01/app/oracle/oradata/STDBY/arch/'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.arc'
    LatestLog                       = '(monitor)'
    TopWaitEvents                   = '(monitor)'

Current status for "STDBY":
SUCCESS

Enabling Fast Start Failover and the Observer

These are the steps required to enable and check Fast Start Failover and the Observer:

1. Ensure standby redologs are configured on all databases.

on primary:

SQL> SELECT TYPE,MEMBER FROM V$LOGFILE;

TYPE    MEMBER
------- --------------------------------------------------
ONLINE  /u01/app/oracle/oradata/PROD/redo03.log
ONLINE  /u01/app/oracle/oradata/PROD/redo02.log
ONLINE  /u01/app/oracle/oradata/PROD/redo01.log
STANDBY /u01/app/oracle/oradata/PROD/redoby04.log
STANDBY /u01/app/oracle/oradata/PROD/redoby05.log
STANDBY /u01/app/oracle/oradata/PROD/redoby06.log

On standby:

SQL> SELECT TYPE,MEMBER FROM V$LOGFILE;

TYPE       MEMBER
---------- --------------------------------------------------
ONLINE     /u01/app/oracle/oradata/STDBY/redo03.log
ONLINE     /u01/app/oracle/oradata/STDBY/redo02.log
ONLINE     /u01/app/oracle/oradata/STDBY/redo01.log
STANDBY    /u01/app/oracle/oradata/STDBY/redoby04.log
STANDBY    /u01/app/oracle/oradata/STDBY/redoby05.log
STANDBY    /u01/app/oracle/oradata/STDBY/redoby06.log

2. Ensure the LogXptMode Property is set to SYNC.
Note: These commands will succeed only if database is configured with standby redo logs.
DGMGRL> EDIT DATABASE PROD SET PROPERTY 'LogXptMode'='SYNC';
Property "LogXptMode" updated
DGMGRL> EDIT DATABASE STDBY SET PROPERTY 'LogXptMode'='SYNC';
Property "LogXptMode" updated

3.Specify the FastStartFailoverTarget property

DGMGRL> EDIT DATABASE PROD SET PROPERTY FastStartFailoverTarget='STDBY';
Property "faststartfailovertarget" updated
DGMGRL> EDIT DATABASE STDBY SET PROPERTY FastStartFailoverTarget='PROD';
Property "faststartfailovertarget" updated

4.Upgrade the protection mode to MAXAVAILABILITY, if necessary.

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;
Operation requires shutdown of instance "PROD" on database "PROD"
Shutting down instance "PROD"...
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "PROD" on database "PROD"
Starting instance "PROD"...
ORACLE instance started.
Database mounted.

note: if 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.

you must start instance (primary database) manually
SQL> conn / as sysdba
SQL> startup mount;

5. Enable Flashback Database on the Primary and Standby Databases.

 On Both databases
 To enter the standby into Flashback mode you must shutdown the both databases, then while the primary is down execute the
 following commands on the standby:
   SQL> ALTER SYSTEM SET UNDO_RETENTION=3600 SCOPE=SPFILE;
   System altered.
   SQL> ALTER SYSTEM SET UNDO_MANAGEMENT='AUTO' SCOPE=SPFILE;
   System altered.
   SQL> startup mount;

SQL> ALTER DATABASE FLASHBACK ON;

Enable fast start failover

[oracle@rac1 ~]$ dgmgrl
DGMGRL for Linux: Version 10.2.0.1.0 - Production

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

Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys/oracle@PROD;
Connected.

DGMGRL> show configuration verbose;

Configuration
  Name:                PROD
  Enabled:             YES
  Protection Mode:     MaxAvailability
  Fast-Start Failover: DISABLED
  Databases:
  PROD - Primary database
  STDBY   - Physical standby database

Current status for "PROD":
SUCCESS


DGMGRL> show database STDBY;
Database
  Name:            STDBY
  Role:            PHYSICAL STANDBY
  Enabled:         YES
  Intended State:  ONLINE
  Instance(s):    STDBY

Current status for "STDBY":
SUCCESS

DGMGRL> ENABLE FAST_START FAILOVER;
Enabled.

start the observer

Start the observer from a third server on background. You may use a script like this:

---------------- script start on next line --------------------
#!/bin/ksh
# startobserver
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
export BASE_PATH=/u01/app/oracle/oracle/scripts/general:/opt/CTEact/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/bin:/etc:/usr/local/maint/oracle:/usr/ccs/bin:/usr/openwin/bin:/usr/dt/bin:/usr/local/bin:.
export PATH=$ORACLE_HOME/bin:$BASE_PATH
dgmgrl << eof
connect sys/oracle@PROD
START OBSERVER;
eof
---------------- script end on previous line --------------------

[oracle@rac3 ~]$ nohup ./startobserver &
nohup: appending output to `nohup.out'
[1] 27392

Verify the fast-start failover configuration.
[oracle@rac3 ~]$ dgmgrl
DGMGRL for Linux: Version 10.2.0.1.0 - Production

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

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/oracle@PROD
Connected.

DGMGRL> show configuration verbose
Configuration
  Name:                PROD
  Enabled:             YES
  Protection Mode:     MaxAvailability
  Fast-Start Failover: ENABLED
  Databases:
    PROD - Primary database
    STDBY   - Physical standby database
           - Fast-Start Failover target

Fast-Start Failover
  Threshold: 30 seconds
  Observer:  rac1

Current status for "PROD":
SUCCESS

Check that primary and standby are healthy
This check must return 'SUCCESS' as the status for both databases, otherwise it means there is a configuration problem.

DGMGRL> show database PROD
Database
  Name:            PROD
  Role:              PRIMARY
  Enabled:         YES
  Intended State:  ONLINE
  Instance(s):    PROD

Current status for "PROD":SUCCESS

DGMGRL> show database STDBY

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

Current status for "STDBY":SUCCESS

DGMGRL>

EXECUTE THE SWITCHOVER:

 DGMGRL> SWITCHOVER TO STDBY;
Performing switchover NOW, please wait...
Operation requires shutdown of instance "PROD" on database "PROD"
Shutting down instance "PROD"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires shutdown of instance "STDBY" on database "STDBY"
Shutting down instance "STDBY"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "PROD" on database "PROD"
Starting instance "PROD"...
ORACLE instance started.
Database mounted.
Operation requires startup of instance "STDBY" on database "STDBY"
Starting instance "STDBY"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "STDBY"
DGMGRL>


DGMGRL> show configuration verbose

Configuration
  Name:                PROD
  Enabled:             YES
  Protection Mode:     MaxAvailability
  Fast-Start Failover: ENABLED
  Databases:
    PROD - Physical standby database
           - Fast-Start Failover target
    STDBY   - Primary database

Fast-Start Failover
  Threshold: 30 seconds
  Observer:  rac1

Current status for "PROD":
SUCCESS

DGMGRL> show database STDBY

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

Current status for "STDBY":
SUCCESS

DGMGRL> show database PROD
Database
  Name:            PROD
  Role:              PHYSICAL STANDBY
  Enabled:         YES
  Intended State:  ONLINE
  Instance(s):     PROD
Current status for "PROD":
SUCCESS

----------------- PROBLEMS --------------------

DGMGRL> connect sys/oracle@PROD

Connected.

DGMGRL> show configuration stdby

Error: ORA-16606: unable to find property "stdby"

Error: ORA-16501: the Data Guard broker operation failed

DGMGRL> show configuration PROD

Error: ORA-16606: unable to find property "prod"

Error: ORA-16501: the Data Guard broker operation failed

------------------ SOLUTIONS -----------------------

SQL> alter system set log_archive_dest_2=defer scope=spfile;

alter system set log_archive_dest_2=defer scope=spfile

*

ERROR at line 1:

ORA-32017: failure in updating SPFILE

ORA-16179: incremental changes to "log_archive_dest_2" not allowed with SPFILE

SQL> alter system set log_archive_dest_2='';

System altered.

SQL> show parameter log_archive_dest_2

NAME                                 TYPE        VALUE

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

log_archive_dest_2                   string

alter system set log_archive_dest_2='service=stdby LGWR SYNC AFFIRM  db_unique_name=stdby valid_for=(online_logfile,primary_role)' scope=both;

-----  /app/oracle/product/10.2.0/db_1/dbs/ ------

dr1stdby.dat

dr2stdby.dat  ------- not available .then rename and recreate configuration file.

RELATED TOPIC:

http://oraclegurukul.blogspot.in/2014/07/warning-you-are-no-longer-connected-to_18.html
http://oraclegurukul.blogspot.in/2014/07/creating-dataguard-broker-configuration.html
http://oraclegurukul.blogspot.in/2014/07/applying-cpupsuopatch-with-dgmgrl-in.html
http://oraclegurukul.blogspot.in/2014/07/converting-physical-standby-to-snapshot.html
http://oraclegurukul.blogspot.in/2014/07/configuration-protection-mode-with.html
http://oraclegurukul.blogspot.in/2014/07/enabling-fast-start-failover-and.html
http://oraclegurukul.blogspot.in/2014/07/performing-manual-failover-operation.html
http://oraclegurukul.blogspot.in/2014/07/reinstating-failed-primary-database.html

Enable & Disable a scheduled job

Using the package DBMS_SCHEDULER one can enable/disable jobs.

To disable job: This disables the job from running

SQL> exec dbms_scheduler.disable(‘GATHER_STATS_JOB’);

PL/SQL procedure successfully completed.

– check job status

SQL> select job_name, enabled from DBA_SCHEDULER_JOBS WHERE job_name = ‘GATHER_STATS_JOB’;

JOB_NAME ENABL
————————- —–
GATHER_STATS_JOB FALSE

To enable job:

SQL> exec dbms_scheduler.enable(‘GATHER_STATS_JOB’);

PL/SQL procedure successfully completed.

– check job status
SQL> select job_name, enabled from DBA_SCHEDULER_JOBS WHERE job_name = 'GATHER_STATS_JOB’;

JOB_NAME ENABL
————————- —–
GATHER_STATS_JOB TRUE

DMBS_SCHEDULER ( Small Demonstration With Executables )

Note: this Demonstration using when you have not executes scripts via the OS cron Job then using Oracle Scheduler One Advantage of Oracle Scheduler Getting a Log & Status of Schedules.

1. METHOD With Create Job

execute shell script USING DBMS_SCHEDULER

--- create shell scrip as below

[oracle@server1 ~]$ cat vmstat.sh

#!/bin/bash

/usr/bin/vmstat 2 10 >> /home/oracle/x.txt

--------create job of VMSTAT, set arguments and enable

begin

 dbms_scheduler.create_job

(

job_name => 'My_job',

job_type => 'EXECUTABLE',

job_action => '/bin/bash',

start_date =>sysdate ,

number_of_arguments => 1,

repeat_interval => 'FREQ=secondly; INTERVAL=5',

enabled => false,

comments => 'shell script test'

);

dbms_scheduler.set_job_argument_value(job_name=>'My_job', argument_position=>1, argument_value=>'/home/oracle/vmstat.sh');

dbms_scheduler.enable(name=>'My_job');

end;

/

--------Schedule DD VIEW

select * from dba_scheduler_job_run_details x where x.job_name='MY_JOB';

SELECT JOB_NAME, STATE FROM DBA_SCHEDULER_JOBS;

 select LOG_ID,LOG_DATE,OWNER,STATUS from DBA_SCHEDULER_JOB_LOG where job_name='MY_JOB' order by 2;

~~~~~~~~~~~~~~~~~~~~~~~~~~~~

My Small Simple Demonstrations

~~~~~~~~~~~~~~~~~~~~~~~~~~~~

[oracle@server1 ~]$ cat script.sh

#!/bin/bash

ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE

ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1; export ORACLE_HOME

ORACLE_SID=sarathi; export ORACLE_SID

ORACLE_TERM=xterm; export ORACLE_TERM

PATH=/usr/sbin:$PATH; export PATH

PATH=$ORACLE_HOME/bin:$PATH; export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH

CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib;

$ORACLE_HOME/bin/sqlplus /nolog <<EOF

connect sys/jpdldimts53 as sysdba

@/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/analyzetable.sql

exit;

EOF

----------SQL SCRIPT  analyzetable.sql

spool /home/oracle/tanalyze.out

Analyze table HR.ABBREV Validate Structure cascade;

Analyze table TEST.ACCOUNT Validate Structure cascade;

Analyze table TEST.ACCOUNT_CANCEL Validate Structure cascade;

Analyze table TEST.ACCOUNT_FEE_PERIOD Validate Structure cascade;

Analyze table HR.ACTION Validate Structure cascade;

Analyze table TEST.ACTION Validate Structure cascade;

Analyze table TEST.ACTION_HEAD Validate Structure cascade;

Analyze table TEST.ACTION_HEAD_DESC Validate Structure cascade;

Analyze table TEST.ACTION_VCH_CATG Validate Structure cascade;

Analyze table TEST.ADD_HIST Validate Structure cascade;

Analyze table HR.ADMINMAST Validate Structure cascade;

Analyze table HR.ADMINTEMP Validate Structure cascade;

Analyze table HR.ADMINTLOG Validate Structure cascade;

Analyze table TEST.ALLTABS Validate Structure cascade;

Analyze table HR.ANSWERBANK Validate Structure cascade;

Analyze table HR.APPLICANT Validate Structure cascade;

spool off;

-------Create Jobs------

begin

dbms_scheduler.create_job

(

job_name => 'My_job',

job_type => 'EXECUTABLE',

job_action => '/bin/bash',

start_date =>sysdate ,

number_of_arguments => 1,

repeat_interval=>'FREQ=DAILY;BYHOUR=11;BYMINUTE=35',

enabled => false,

comments => 'shell script test'

);

dbms_scheduler.set_job_argument_value(job_name=>'My_job', argument_position=>1, argument_value=>'/home/oracle/script.sh');

dbms_scheduler.enable(name=>'My_job');

end;

/

--------Schedule DD VIEW-----------

select * from dba_scheduler_job_run_details x where x.job_name='MY_JOB';

SELECT JOB_NAME, STATE FROM DBA_SCHEDULER_JOBS;

select LOG_ID,LOG_DATE,OWNER,STATUS from DBA_SCHEDULER_JOB_LOG where job_name='MY_JOB' order by 2;

2. METHOD With Program

----- DB Verify Script

Script Name :   dbv_on_all_files.sh

#!/bin/bash

ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE

ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1; export ORACLE_HOME

ORACLE_SID=sarathi; export ORACLE_SID

ORACLE_TERM=xterm; export ORACLE_TERM

PATH=/usr/sbin:$PATH; export PATH

PATH=$ORACLE_HOME/bin:$PATH; export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH

CLASSPATH=$ORACLE_HOME/jre:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

dbv file=/u01/app/oracle/oradata/system01.dbf logfile=/u01/file1.log blocksize=8192

dbv file=/u01/app/oracle/oradata/undotbs01.dbf logfile=/u01/file2.log blocksize=8192

dbv file=/u01/app/oracle/oradata/sysaux01.dbf logfile=/u01/file3.log blocksize=8192

dbv file=/u01/app/oracle/oradata/users01.dbf logfile=/u01/file4.log blocksize=8192

dbv file=/u03/datafs/bio01.dbf logfile=/u01/file5.log blocksize=8192

dbv file=/u02/datafs/imagestsp01.dbf logfile=/u01/file6.log blocksize=8192

dbv file=/u03/datafs/SARA.DBF logfile=/u01/file7.log blocksize=8192

dbv file=/u01/app/oracle/oradata/sarathi_sc logfile=/u01/file8.log blocksize=8192

dbv file=/u02/datafs/sara01 logfile=/u01/file9.log blocksize=8192

dbv file=/u03/datafs/sara02.dbf logfile=/u01/file10.log blocksize=8192

dbv file=/u01/app/oracle/oradata/sara03.dbf logfile=/u01/file11.log blocksize=8192

dbv file=/u04/datafs/SARI.DBF logfile=/u01/file12.log blocksize=8192

dbv file=/u04/datafs/SARIN.DBF logfile=/u01/file13.log blocksize=8192

dbv file=/u02/datafs/STAL.DBF logfile=/u01/file14.log blocksize=8192

dbv file=/u03/datafs/users02.dbf logfile=/u01/file15.log blocksize=8192

dbv file=/u04/rcdata/vah.dbf logfile=/u01/file16.log blocksize=8192

----- CREATE A PROGRAM

begin

dbms_scheduler.create_program

(

program_name => 'db_verify',

program_type => 'EXECUTABLE',

program_action => '/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/dbv_on_all_files.sh',

number_of_arguments => 0,

enabled => TRUE,

comments => 'Check the Time'

);

end;

/

------ CREATE A SCHEDULE

begin

dbms_scheduler.create_schedule

(

schedule_name => 'EVERY_12_HOURS',

start_date=> SYSDATE,

repeat_interval=>'FREQ=DAILY;BYHOUR=12;BYMINUTE=45',

/*repeat_interval => 'FREQ=HOURLY; INTERVAL=12?,*/

comments => 'Every 12 Hours'

);

end;

/

-- Display the schedule details.

SELECT owner, schedule_name FROM dba_scheduler_schedules;

OWNER                          SCHEDULE_NAME

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

SYS                            DAILY_PURGE_SCHEDULE

SYS                            TEST_HOURLY_SCHEDULE

-- DROP_SCHEDULE

BEGIN

  DBMS_SCHEDULER.drop_schedule (schedule_name => 'TEST_HOURLY_SCHEDULE');

END;

/

----- CREATE A JOB

begin

dbms_scheduler.create_job

(

job_name => 'My_job',

schedule_name => 'EVERY_12_HOURS',

program_name => 'db_verify',

enabled => TRUE,

comments => 'shell script test'

);

end;

/

col PROGRAM_ACTION for a80

select OWNER,PROGRAM_NAME,PROGRAM_TYPE,PROGRAM_ACTION,ENABLED from DBA_SCHEDULER_PROGRAMS;

select LOG_ID,LOG_DATE,OWNER,STATUS from DBA_SCHEDULER_JOB_LOG where job_name='MY_JOB' order by 2;

select OWNER,JOB_NAME,JOB_TYPE,STATE from dba_scheduler_jobs where job_name='MY_JOB';

col log_date for a20

col job_name for a20

col additional_info for a50

select log_id, log_date, job_name, status, error#, additional_info

from dba_scheduler_job_run_details

where job_name='MY_JOB' order by log_date;

----- RUN THE JOB MANUALLY

1. exec dbms_scheduler.run_job('RUN_UPDATE_DB_VERIFY');

2. BEGIN  -- Run job synchronously.

  DBMS_SCHEDULER.run_job (job_name  => 'test_full_job_definition',  use_current_session => TRUE);

-- STOP JOBS

  DBMS_SCHEDULER.stop_job (job_name => 'test_full_job_definition, test_prog_sched_job_definition');

END;

/

-----Check the job run status and other details    

select log_id, log_date, job_name, status, error#, additional_info

from dba_scheduler_job_run_details

where job_name like 'RUN_UPDATE_DB_VERIFY';

Enjoy ...........  :-)

RELATED TOPIC:

http://oracleeducom.blogspot.in/2014/07/enable-disable-scheduled-job.html

sqlnet.ora / tnsname.ora / listener.ora not Access

sqlnet.ora / tnsname.ora / listener.ora  not Access and not editing on Unix system with root and anybody user, Only follows some points.



1. login root

2. go location network admin through root

3. chmod 400 -R *

4. Bellow major command.

--You can set the "immutable" attribute with most filesystems in Linux.
chattr +i sqlnet.ora

--To remove the immutable attribute, you use - instead of +:
chattr -i sqlnet.ora

--To see the current attributes for a file, you can use lsattr:
lsattr sqlnet.ora