Tuesday, January 22, 2013

Re-Configure data guard broker after re-create physical standby database

SYS@DGS> startup mount
ORACLE instance started.

Total System Global Area  680607744 bytes
Fixed Size                  2229600 bytes
Variable Size             197135008 bytes
Database Buffers          473956352 bytes
Redo Buffers                7286784 bytes
Database mounted.

SYS@DGS> show parameter name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string
db_name                              string      DG
db_unique_name                       string      DGS
global_names                         boolean     FALSE
instance_name                        string      DGS
lock_name_space                      string
log_file_name_convert                string      /u01/oradata/DG/, /u01/oradata
                                                 /DG/
service_names                        string      DGS.POC


orarac2poc:DGS:/u01/app/oracle/product/11.2.0/db_2/dbs> sqlplus sys/oracle123@DGS as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue Jan 22 13:39:37 2013

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SYS@DGS> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options




orarac1poc:DG:/u01/app/oracle/product/11.2.0/db_2/network/admin> dgmgrl
DGMGRL 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 sys/oracle123
Connected.
DGMGRL> show configuration;

Configuration - DGConfig1

  Protection Mode: MaxPerformance
  Databases:
    dg - Primary database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED


DGMGRL> help

The following commands are available:

add            Adds a standby database to the broker configuration
connect        Connects to an Oracle database instance
convert        Converts a database from one type to another
create         Creates a broker configuration
disable        Disables a configuration, a database, or fast-start failover
edit           Edits a configuration, database, or instance
enable         Enables a configuration, a database, or fast-start failover
exit           Exits the program
failover       Changes a standby database to be the primary database
help           Displays description and syntax for a command
quit           Exits the program
reinstate      Changes a database marked for reinstatement into a viable standby
rem            Comment to be ignored by DGMGRL
remove         Removes a configuration, database, or instance
show           Displays information about a configuration, database, or instance
shutdown       Shuts down a currently running Oracle database instance
sql            Executes a SQL statement
start          Starts the fast-start failover observer
startup        Starts an Oracle database instance
stop           Stops the fast-start failover observer
switchover     Switches roles between a primary and standby database

Use "help " to see syntax for individual commands

DGMGRL> help add

Adds a standby database to the broker configuration

Syntax:

  ADD DATABASE
    [AS CONNECT IDENTIFIER IS ]
    [MAINTAINED AS {PHYSICAL|LOGICAL}];

DGMGRL> show configuration;

Configuration - DGConfig1

  Protection Mode: MaxPerformance
  Databases:
    dg - Primary database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

DGMGRL> add database DGS as connect identifier is 'DGS';
Error: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Failed.
DGMGRL> show configuration;

Configuration - DGConfig1

  Protection Mode: MaxPerformance
  Databases:
    dg - Primary database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

DGMGRL> exit
orarac1poc:DG:/u01/app/oracle/product/11.2.0/db_2/network/admin> tnsping DGS

TNS Ping Utility for Linux: Version 11.2.0.2.0 - Production on 22-JAN-2013 13:48:27

Copyright (c) 1997, 2010, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = orarac2poc)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DGS)))
OK (0 msec)
orarac1poc:DG:/u01/app/oracle/product/11.2.0/db_2/network/admin> vi tnsnames.ora

# changed SERVICE_NAME = DGS to SERVICE_NAME = DGS.POC in tnsnames.ora


orarac1poc:DG:/u01/app/oracle/product/11.2.0/db_2/network/admin> tnsping DGS

TNS Ping Utility for Linux: Version 11.2.0.2.0 - Production on 22-JAN-2013 13:48:47

Copyright (c) 1997, 2010, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = orarac2poc)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DGS.POC)))
OK (10 msec)
orarac1poc:DG:/u01/app/oracle/product/11.2.0/db_2/network/admin> dgmgrl
DGMGRL 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 sys/oracle123
Connected.
DGMGRL> show configuration;

Configuration - DGConfig1

  Protection Mode: MaxPerformance
  Databases:
    dg - Primary database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED


DGMGRL> add database DGS as connect identifier is 'DGS';
Database "dgs" added
DGMGRL> show configuration;

Configuration - DGConfig1

  Protection Mode: MaxPerformance
  Databases:
    dg  - Primary database
    dgs - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

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

Configuration - DGConfig1

  Protection Mode: MaxPerformance
  Databases:
    dg  - Primary database
      Error: ORA-16797: database is not using a server parameter file

    dgs - Physical standby database
      Error: ORA-16797: database is not using a server parameter file

Fast-Start Failover: DISABLED

Configuration Status:
ERROR

DGMGRL> disable configuration;
Disabled.
DGMGRL> show configuration;

Configuration - DGConfig1

  Protection Mode: MaxPerformance
  Databases:
    dg  - Primary database
    dgs - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

DGMGRL> exit
orarac1poc:DG:/u01/app/oracle/product/11.2.0/db_2/network/admin> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue Jan 22 13:51:26 2013

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SYS@DG> create spfile from pfile;

File created.

SYS@DG> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

#crete spfile for standby database too .


orarac1poc:DG:/u01/app/oracle/product/11.2.0/db_2/network/admin> dgmgrl
DGMGRL 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 sys/oracle123
Connected.
DGMGRL> enable configuration;
Enabled.
DGMGRL> show configuration;

Configuration - DGConfig1

  Protection Mode: MaxPerformance
  Databases:
    dg  - Primary database
      Error: ORA-16797: database is not using a server parameter file

    dgs - Physical standby database
      Error: ORA-16797: database is not using a server parameter file

Fast-Start Failover: DISABLED

Configuration Status:
ERROR

DGMGRL> exit

orarac1poc:DG:/u01/app/oracle/product/11.2.0/db_2/network/admin> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue Jan 22 13:52:23 2013

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SYS@DG> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@DG> startup
ORACLE instance started.

Total System Global Area  680607744 bytes
Fixed Size                  2229600 bytes
Variable Size             419433120 bytes
Database Buffers          251658240 bytes
Redo Buffers                7286784 bytes
Database mounted.
Database opened.
SYS@DG> alter system switch logfile;

System altered.

SYS@DG> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE#
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------
NEXT_TIME
---------
         1          1         22   52428800        512          1 YES ACTIVE                 3653744 22-JAN-13      3653906
22-JAN-13

         2          1         23   52428800        512          1 YES ACTIVE                 3653906 22-JAN-13      3654034
22-JAN-13

         3          1         24   52428800        512          1 NO  CURRENT                3654034 22-JAN-13   2.8147E+14



SYS@DG> alter system switch logfile;

System altered.

SYS@DG> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

orarac1poc:DG:/u01/app/oracle/product/11.2.0/db_2/network/admin> dgmgrl
DGMGRL 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 sys/oracle123
Connected.
DGMGRL> show configuration;

Configuration - DGConfig1

  Protection Mode: MaxPerformance
  Databases:
    dg  - Primary database
    dgs - Physical standby database
      Error: ORA-16700: the standby database has diverged from the primary database

Fast-Start Failover: DISABLED

Configuration Status:
ERROR


#The ORA-16700 error disappeared after I start LOG APPLY in standby database.
SYS@DGS> alter database recover managed standby database using current logfile disconnect from session;



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

Configuration - DGConfig1

  Protection Mode: MaxPerformance
  Databases:
    dg  - Primary database
    dgs - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS



DGMGRL> help  sql

Executes a SQL statement

Syntax:

  SQL "";

DGMGRL> sql "alter system switch logfile; "
ORA-00911: invalid character

Failed.
DGMGRL> sql "alter system switch logfile "
Succeeded.
DGMGRL> sql "alter system checkpoint"
Succeeded.