Re: dataguard and operators

From: Andrew Kerber <andrew.kerber_at_gmail.com>
Date: Mon, 28 Sep 2015 11:57:35 -0500
Message-ID: <CAJvnOJare7jD0w_p3A7M==jzNSERK8F9DcLCm=epTDPYTA9MZw_at_mail.gmail.com>

This is from a larger document I wrote on the subject some time ago:

On the primary node, configure a static listener entry for the use of Dataguard, similar to this, When using ASM this should be in $GRID_HOME/network/admin/listener.ora: SID_LIST_LISTENER = (SID_LIST = (SID_DESC =
(GLOBAL_DBNAME = prims_DGMGRL) -- DB unique name followed by _DGMGRL
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = prims) -- SID
) )

LISTENER =
   (DESCRIPTION_LIST =
    (DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER))
(ADDRESS = (PROTOCOL = TCP)(HOST = primsnode)(PORT = 1521))
    )
  )

2. On the standby node, configure a static listener entry for the standby instance. When using ASM this should be in $GRID_HOME/network/admin/listener.ora:
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
(GLOBAL_DBNAME = primsdr_DGMGRL) -- DB unique name followed by
_DGMGRL
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = prims) -- SID
    )
  )

LISTENER =
   (DESCRIPTION_LIST =
    (DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER))
(ADDRESS = (PROTOCOL = TCP)(HOST = drsnode)(PORT = 1521))
    )
  )

3. On the primary and standby server, create tnsnames entries pointing to the DGMGRL service for the primary and standby instance. To avoid confusion, we recommend putting all tns entries in all tnsnames.ora files: PRIMS_DGMGRL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = primsnode)(PORT = 1521))
(CONNECT_DATA =

        (SERVER = DEDICATED)
        (SERVICE_NAME = prims_DGMGRL)
        (UR=A)
     )

)

PRIMSDR_DGMGRL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = drsnode)(PORT = 1521))
(CONNECT_DATA =

        (SERVER = DEDICATED)
        (SERVICE_NAME = primsdr_DGMGRL)
        (UR=A)
     )

)

Enabling Dataguard Broker
The steps for enabling Dataguard broker are the same for all scenarios in this document. Note that prior to beginning this process, Oracle Dataguard should be running properly in an unmanaged state. This is, the standby database should be in managed recovery mode, a Log file switch on the primary should be quickly reflected on the standby, and the .Media Recovery. message should show up in the alert log of the standby instance. Oracle Dataguard can run successfully at this point in a manually managed mode, though we do not recommend doing this, as Dataguard Broker gives much more flexibility, control, and ease of switchover and failover. 1. Set the environment to the primary database on the primary server, and connect to dgmgrl using the sys username and password. The 'as sysdba' is not used:
dgmgrl sys/password

2. Define the primary database information using the create configuration command. The primary database is named using the database unique name and the connect identifier is the name of the entry in the tnsnames.ora file that defines the primary database: DGMGRL> create configuration 'prims_config' as primary database is 'prims' connect identifier is prims_dgmgrl;

3. Define the standby database using the add command. The database name in is the database unique name and the connect identifier is the tnsnames.ora entry used to connect to the standby database: add database 'primsdr' as connect identifier is primsdr_dgmgrl maintained as physical;

4. Enter 'show configuration' to show that it has been correctly configured:
DGMGRL> create configuration 'prims_config' as primary database is 'prims' connect identifier is prims_dgmgrl;
Configuration "prims_config" created with primary database "prims" DGMGRL> add database 'primsdr' as connect identifier is primsdr_dgmgrl maintained as physical;
Database "primsdr" added
DGMGRL> show configuration;

Configuration - prims_config

  Protection Mode: MaxPerformance
  Databases:
    prims - Primary database
    primsdr - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED 5. Enable the configuration:
DGMGRL> enable configuration;
Enabled.
DGMGRL> show configuration

Configuration - prims_config

  Protection Mode: MaxPerformance
  Databases:
    prims - Primary database
    primsdr - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS 6. The word success indicates that the configuration is set up properly and that Dataguard broker expects to be able to successfully switch over to the standby.

7. Verify that the configuration works properly by switching over and switching back. Note that the switchover operation must always be run from the primary server:
[oracle_at_primsnode ~]$ dgmgrl sys/password DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production

Welcome to DGMGRL, type "help" for information. Connected.
DGMGRL> switchover to 'primsdr'
Performing switchover NOW, please wait... Operation requires a connection to instance "prims" on database "primsdr" Connecting to instance "prims"...
Connected.
New primary database "primsdr" is opening... Operation requires startup of instance "prims" on database "prims" Starting instance "prims"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "primsdr" DGMGRL> [oracle_at_drsnode admin]$ dgmgrl sys/password DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production

Welcome to DGMGRL, type "help" for information. Connected.
DGMGRL> show configuration

Configuration - prims_config

  Protection Mode: MaxPerformance
  Databases:
    primsdr - Primary database
    prims - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS DGMGRL> switchover to prims
Performing switchover NOW, please wait... Operation requires a connection to instance "prims" on database "prims" Connecting to instance "prims"...
Connected.
New primary database "prims" is opening... Operation requires startup of instance "prims" on database "primsdr" Starting instance "prims"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "prims" DGMGRL> 8. If switchovers from both nodes work properly then configuration is complete.
9. Optional but highly recommended, turn on flashback database on both primary and standby. The commands are the same on both the primary and the standby:

a.      SQL> shutdown immediate;
b.      SQL>startup mount;
c.      SQL>alter database flashback on;
d.      SQL>alter database open;

Note: It may be necessary to turn off the Dataguard broker on the standby, turn on flashback, and then turn the Dataguard broker back on:

a.      alter system set dg_broker_start=false;
b.      alter database flashback on;
c.      alter system set dg_broker_start=true;
Common Errors From Dataguard Broker
1.      ORA-12154 - Most often this means that the static listener entry is

not correct. Other common possibilities are that the UR=A option is not specified in the tns entry or there is simply a typo in the tnsnames entry. 2. ORA-12170 - Most often this indicates that there is a firewall between the primary and the standby. We recommend that there be no firewall enabled at all between the two. If company policy requires a firewall, all ephemeral ports between the primary and standby must be opened in both directions for the TCP and UDP protocols. This is not optional. The listener may hand off the connection to the standby on any port in the ephemeral port range.

On Mon, Sep 28, 2015 at 8:10 AM, Laimutis Nedzinskas < laimutis.nedzinskas_at_statoilfuelretail.com> wrote:

> > If properly configured, data guard broker is absolutely the way to go.
>
>
>
>
>
> Second that.
>
> Just do not forget that reinstate the former primary as a new standby is
> another story.
>
>
>
> That part is easy anyway. It’s pretty interesting how the rest is managed:
> all kinds of scheduled (crontab) jobs, scripts if any, backups.
>
> Last but the biggest: application failover, hehehe. Database can switch,
> np. What about applications, hm?
>
>
>
> /Laimis N
>
>
>
>
>
>
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Andrew Kerber
> *Sent:* Tuesday, September 22, 2015 3:50 PM
> *To:* nmjamaleddin_at_multiservice.com
> *Cc:* giantpanda_at_gmx.net; howard.latham_at_gmail.com; oracle-l_at_freelists.org
> *Subject:* Re: dataguard and operators
>
>
>
> If properly configured, data guard broker is absolutely the way to go. The
> commands are simply 'switchover to standbyname;', 'failover to
> standbyname;'   I don't have a good email here at the office, but I have
> doc I put together on this that I can send this evening if you want.
>
> Sent from my iPhone
>
>
> On Sep 22, 2015, at 7:16 AM, Nabil Jamaleddin <
> nmjamaleddin_at_multiservice.com> wrote:
>
> 1st level support doing a DG switchover….RAC might be a better solution.
> And I am pretty sure that if I was the DBA I would want to be awake for
> this unless 1st level support really knows what they are doing, but if
> they did, would they still be 1st level support?
>
>
>
>
>
>
>
> Performing a Database Switchover.  A switchover provides the ability for
> the primary to go back and forth
>
>
>
>
>
> -- On Primary Database
>
> CONNECT / AS SYSDBA
>
> ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY;
>
> SHUTDOWN IMMEDIATE;
>
> STARTUP NOMOUNT;
>
> ALTER DATABASE MOUNT STANDBY DATABASE;
>
> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
>
>
>
>
>
>
>
> On the original standby database issue the following commands to convert
> standby database to primary
>
> CONNECT / AS SYSDBA
>
> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
>
> SHUTDOWN IMMEDIATE;
>
> STARTUP;
>
>
>
>
>
>
>
> OR can do a failover, but this is not as forgiving as a switchover.
>
>
>
>
>
>
>
> Failover.  Just like it sounds, failover because the primary is not
> responding.
>
> --On the standby database
>
> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
>
> ALTER DATABASE ACTIVATE STANDBY DATABASE;
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> *From:* oracle-l-bounce_at_freelists.org [
> mailto:oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org>] *On
> Behalf Of *Ingrid Voigt
> *Sent:* Tuesday, September 22, 2015 6:58 AM
> *To:* howard.latham_at_gmail.com; oracle-l_at_freelists.org
> *Subject:* Aw: dataguard and operators
>
>
>
> Hi,
>
>
>
> I'd like to second this request. My company is currently introducing 7x24
> service hours
>
> for the first level support people, they are supposed to be able to solve
> as many
>
> things as possibly using critical problem checklists without waking up the
> DBAs.
>
> This will include general database troubleshooting and DG switchover (we
> are still
>
> fighting...)
>
>
>
> Thanks
>
> Ingrid Voigt
>
>
>
>
>
> *Gesendet:* Dienstag, 22. September 2015 um 12:10 Uhr
> *Von:* "Howard Latham" <howard.latham_at_gmail.com>
> *An:* ORACLE-L <oracle-l_at_freelists.org>
> *Betreff:* dataguard and operators
>
> Oracle 11 Redhat 4E
>
> My boss wants scripts /instructions so 'any fool ' can do a dataguard
> switchover. I think that's hard if not impossible as you can't
> predict what is going to happen How many of you have successfully
> handed this over to an operator? And if you have can I see example
> scripts please?
>
> --
> Howard A. Latham
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
> -- http://www.freelists.org/webpage/oracle-l
>
>
>
> ------------------------------------------------------------------
>
> This email is intended solely for the use of the addressee and may
>
> contain information that is confidential, proprietary, or both.
>
> If you receive this email in error please immediately notify the
>
> sender and delete the email.
>
> ------------------------------------------------------------------
>
>

-- 
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Sep 28 2015 - 18:57:35 CEST

This message: [ Message body ]
Next message: John Thomas: "Re: write complete waits"
Previous message: John Thomas: "Re: write complete waits"
In reply to: Laimutis Nedzinskas: "RE: dataguard and operators"
Next in thread: Stefan Koehler: "Re: dataguard and operators"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message