Tales From A Lazy Fat DBA

Den of an Oracle DB Performance freak & a fan of PostgreSQL & Cassandra …. \,,/

  • Likes

    • 192,398
  • Archives

  • ŗ•ź

  • Categories

  • Subscribe

  • Prashant Dixit is the FatDBA

  • Follow Tales From A Lazy Fat DBA on WordPress.com
  • My Twitter Feeds

  • Disclaimer!

    FatDBA or Oracle ‘Ant’ is an independent web-blog/site.The experiences, Test cases, views, and opinions expressed in this website are my own and does not reflect the views or opinions of my employer.

    This site is independent of and does not represent Oracle Corporation in any way. Oracle does not officially sponsor, approve, or endorse this site or its content.
    Product and company names mentioned in this website may be the trademarks of their respective owners.

Posts Tagged ‘standby’

How to check/Identify archival gaps in Standby Environment.

Posted by FatDBA on March 2, 2015

ON PRIMARY DATABASE
=====================

SQL> SELECT THREAD# “Thread”,SEQUENCE# “Last Sequence Generated”
FROM V$ARCHIVED_LOG
WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)
ORDER BY 1;  2    3    4

Thread Last Sequence Generated
———- ———————–
1                  105334
1                  105334
1                  105334

SQL> set time on
06:26:03 SQL>

06:28:04 SQL> alter system switch logfile;

System altered.

06:28:15 SQL> /.

System altered.

06:28:17 SQL> /

System altered.

06:28:17 SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /vol5/oracle/dixit_db_arc/dixitdb/arch
Oldest online log sequence     105334
Next log sequence to archive   105338
Current log sequence           105338

ON STANDBY DATABASE
======================

SQL> select process, status, THREAD#,SEQUENCE#,DELAY_MINS from v$managed_standby;

PROCESS   STATUS          THREAD#  SEQUENCE# DELAY_MINS
——— ———— ———- ———- ———-
ARCH      CLOSING               1     105298          0
ARCH      CLOSING               1     105299          0
ARCH      CLOSING               1     105326          0
ARCH      CLOSING               1     105327          0
ARCH      CLOSING               1     105328          0
ARCH      CLOSING               1     105329          0
ARCH      CLOSING               1     105330          0
ARCH      CLOSING               1     105334          0
ARCH      CLOSING               1     105306          0
ARCH      CLOSING               1     105307          0
ARCH      CLOSING               1     105308          0

PROCESS   STATUS          THREAD#  SEQUENCE# DELAY_MINS
——— ———— ———- ———- ———-
ARCH      CLOSING               1     105309          0
ARCH      CLOSING               1     105310          0
ARCH      CLOSING               1     105311          0
ARCH      CLOSING               1     105312          0
ARCH      CLOSING               1     105285          0
ARCH      CLOSING               1     105313          0
ARCH      CLOSING               1     105314          0
ARCH      CLOSING               1     105315          0
ARCH      CLOSING               1     105316          0
ARCH      CLOSING               1     105317          0
ARCH      CLOSING               1     105318          0

PROCESS   STATUS          THREAD#  SEQUENCE# DELAY_MINS
——— ———— ———- ———- ———-
ARCH      CLOSING               1     105319          0
ARCH      CLOSING               1     105320          0
ARCH      CLOSING               1     105321          0
ARCH      CLOSING               1     105322          0
ARCH      CLOSING               1     105323          0
ARCH      CLOSING               1     105324          0
ARCH      CONNECTED             0          0          0
ARCH      CLOSING               1     105325          0
RFS       IDLE                  1     105335          0
RFS       IDLE                  0          0          0
RFS       IDLE                  0          0          0

PROCESS   STATUS          THREAD#  SEQUENCE# DELAY_MINS
——— ———— ———- ———- ———-
RFS       IDLE                  0          0          0
RFS       IDLE                  0          0          0
RFS       IDLE                  0          0          0
RFS       IDLE                  0          0          0
MRP0      WAIT_FOR_LOG          1     105335          0
RFS       IDLE                  0          0          0
RFS       IDLE                  0          0          0

40 rows selected.

SQL> SELECT ARCH.THREAD# “Thread”, ARCH.SEQUENCE# “Last Sequence Received”, APPL.SEQUENCE# “Last Sequence Applied”, (ARCH.SEQUENCE# – APPL.SEQUENCE#) “Difference”
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;

Thread Last Sequence Received Last Sequence Applied Difference
———- ———————- ——————— ———-
1                 105334                105334          0

SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

no rows selected

SQL> set time on
06:26:06 SQL> select name, open_mode, database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
——— ——————– —————-
dixitdb     READ ONLY WITH APPLY PHYSICAL STANDBY

06:28:20 SQL> SELECT ARCH.THREAD# “Thread”, ARCH.SEQUENCE# “Last Sequence Received”, APPL.SEQUENCE# “Last Sequence Applied”, (ARCH.SEQUENCE# – APPL.SEQUENCE#) “Difference”
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;

Thread Last Sequence Received Last Sequence Applied Difference
———- ———————- ——————— ———-
1                 105337                105337          0

06:28:32 SQL>

06:29:35 SQL> select process, status, THREAD#,SEQUENCE#,DELAY_MINS from v$managed_standby;

PROCESS   STATUS          THREAD#  SEQUENCE# DELAY_MINS
——— ———— ———- ———- ———-
ARCH      CLOSING               1     105298          0
ARCH      CLOSING               1     105299          0
ARCH      CLOSING               1     105326          0
ARCH      CLOSING               1     105327          0
ARCH      CLOSING               1     105328          0
ARCH      CLOSING               1     105329          0
ARCH      CLOSING               1     105330          0
ARCH      CLOSING               1     105334          0
ARCH      CLOSING               1     105306          0
ARCH      CLOSING               1     105335          0
ARCH      CLOSING               1     105336          0

PROCESS   STATUS          THREAD#  SEQUENCE# DELAY_MINS
——— ———— ———- ———- ———-
ARCH      CLOSING               1     105337          0
ARCH      CLOSING               1     105310          0
ARCH      CLOSING               1     105311          0
ARCH      CLOSING               1     105312          0
ARCH      CLOSING               1     105285          0
ARCH      CLOSING               1     105313          0
ARCH      CLOSING               1     105314          0
ARCH      CLOSING               1     105315          0
ARCH      CLOSING               1     105316          0
ARCH      CLOSING               1     105317          0
ARCH      CLOSING               1     105318          0

PROCESS   STATUS          THREAD#  SEQUENCE# DELAY_MINS
——— ———— ———- ———- ———-
ARCH      CLOSING               1     105319          0
ARCH      CLOSING               1     105320          0
ARCH      CLOSING               1     105321          0
ARCH      CLOSING               1     105322          0
ARCH      CLOSING               1     105323          0
ARCH      CLOSING               1     105324          0
ARCH      CONNECTED             0          0          0
ARCH      CLOSING               1     105325          0
RFS       IDLE                  1     105338          0
RFS       IDLE                  0          0          0
RFS       IDLE                  0          0          0

PROCESS   STATUS          THREAD#  SEQUENCE# DELAY_MINS
——— ———— ———- ———- ———-
RFS       IDLE                  0          0          0
RFS       IDLE                  0          0          0
RFS       IDLE                  0          0          0
RFS       IDLE                  0          0          0
MRP0      WAIT_FOR_LOG          1     105338          0
RFS       IDLE                  0          0          0
RFS       IDLE                  0          0          0

Thanks
Prashant Dixit

Posted in Uncategorized | Tagged: | Leave a Comment »

Standby/Dataguard:– RFS Process not working.

Posted by FatDBA on November 12, 2013

Not feeling well today!! ūüė¶ , but as¬† It’s been a long time since i wrote my last article on Oracle database issues … here i am back again with one of the problem that we faced in our production database of our erstwhile customer, some time ago.
It was a typical Physical Standby environment with Maximum Performance model set. This is a story happened on one fine day when we started receiving some issues after a small network outage which blocked the redo stream to transfer logs from Production to standby server. ** * The bad part was  Рthe issue had happened during peak hours where we had confined amount of time to rectify the case.

Issue: RFS Process not working
Problem Description:
The filesystem containing the archive destination in DR server was not accessible. As a result the log shipping got stopped. We deferred the log shipping in the production server. After the filesystem was back, we enabled the log shipping but the RFS process in DR server was not running…The problem occurred when the log 24717 was being shipped… When we queried

SQL> Select status, sequence# from v$managed_standby;

The status for MRP showed that it was waiting for gap…We then manually shipped the log file and then applied… When we enabled the shipping we found that the RFS process was still not started…..There were no error in the alert log of DR…We found a trace file in the production server with the following message..

tkcrrsarc: (WARN) Failed to find ARCH for message (message:0xa)
tkcrrpa: (WARN) Failed initial attempt to send ARCH message (message:0xa)

So we thought that the issue is with the archiver process….

Solution:
Check whether the archiver process is available for shipping log files. You can identify this by querying V$ARCHIVE_PROCESSES view.
SQL> Select * from v$archive_processes;

The output have the following columns:
Process: Indicates the process number.
Status: This should be ACTIVE
Log Sequence: Log sequence number of the log that is being shipped by the archiver. If it is not shipping any log then it should be 0.
State: This should be IDLE if the archiver is not shipping any log. If it is shipping any log then its state is BUSY.

In our case we had two archiver process running.

The status of both the arch process is ACTIVE.
The log sequence of First arch process is 0 and its state is IDLE. Hence it is healthy. However the log sequence of 2nd arch process is 24717 and its state is BUSY.

This was interesting because the problem occurred when the arch process was transferring the log 24717. This log was then manually shipped and applied. But the process still shows that it was shipping the 24717 log…
So we thought of increasing the arch processes. We increased the arch process from two to four.
SQL> alter system set log_archive_max_processes=4 scope=both;

We queried the v$archive_processes, the 3rd and 4th arch process was ready to ship the log files 24718 and 24719 logs respectively with their corresponding state as IDLE…
We enabled the log shipping and the RFS process in DR was started and the log shipping went smoothly.

However the 2nd arch process still was showing the same log sequence (24717) and state (BUSY)…. We then killed that archiver process…. And we were all good!

Hope That Helped
Prashant Dixit

Posted in Advanced | Tagged: , | 2 Comments »

Questions ??

Posted by FatDBA on March 7, 2013

Let’s discover more about Data Guard 11g and discuss some of the topics out of the¬† ocean named Data Guard.

Query Scn in case of Active Data Guard ?
Fast-Start when using Maximum Performance Model … ?
Split-Brain Scenarios in DG 11g and how Fast-Start resolves the problem ?

Posted in Advanced | Tagged: , | Leave a Comment »

Snapshot Standby ?

Posted by FatDBA on February 22, 2013

Writing …… Will Soon Update.

Posted in Advanced | Tagged: , | Leave a Comment »

Oracle Data Guard Evolution.

Posted by FatDBA on February 21, 2013

Oracle Data Guard’s Improvements from Version 8i (Introduced in same version) till now:

ORACLE 8i
————————-
* Read-Only Standby Database
* Managed recovery
* Remote archiving redo log files

ORALCE 9i
————————-
* ‚ÄúZero Data Loss‚ÄĚ Integration
* Data Guard Broker and Data Guard Manager GUI
* Swithcover and Failover operations
* Automatical synchronous
* Logical Standby Database
* Maximum Protection

ORACLE 10g
————————-
* Real-Time Apply
* Fast-Start Failover
* Asynchronous redo transfer
* Flashback Database

ORACLE 11g
————————
* Active Standby Database (Active Data Guard)
* Snapshot Standby
* Heterogeneous platform support (Production ‚ÄďLinux, Standby ‚Äď Windows)

Posted in Advanced | Tagged: , | Leave a Comment »

What are Standby Redo log Files in Data Guard Environment ?

Posted by FatDBA on February 21, 2013

What are Standby Redo Log Files and why they are important ?

Recently asked by one of my good Fried Sowmya about some internals of Standby Redo Log Files and soon i found myself in bit of soreness. So, here i decided to pt some light over this very basic thing if one has to start with Oracle Data Guard.

Standby Redo Logs: They are very similar to Online Redo but with the only difference that S’by Redo Logs are used to contain redo data from Primary DB. S’by Redo Logs are only used if you are using Log Writer as the log transport medium to Standby Site.

sbydb042
When we use Standby Redo Log Files:
*  When using Real-Time Apply service.
*  If using Maximum Protection or Maximum Availability Models.

Points To Remember:
* You should create standby redo logs on both the standby and the primary database which is helpful in case of switch-overs (But is not mandatory for Primary Database)
* The standby redo logs should be at least as big as the largest online redo log .
* You can create Standby Logs after creating Standby Environment.

* In case of disaster and complete loss of the primary database, the entire redo log worth of data will not be lost because the standby redo log file on standby site will contain some or all of the transactions contained in the lost redo log file. This has been one of the most useful enhancements in Data Guard technology, as it minimizes the loss of data while operating in MAXIMUM PERFORMANCE mode.

Example:
Creating Standby Redo log Files on
ALTER DATABASE ADD STANDBY LOGFILE (‘/u01/app/oracle/oradata/DB11G/standby_redo01.log’) SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE (‘/u01/app/oracle/oradata/DB11G/standby_redo02.log’) SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE (‘/u01/app/oracle/oradata/DB11G/standby_redo03.log’) SIZE 50M;

SQL> select group#, member, type from v$logfile;

GROUP#                          MEMBER                                                                            TYPE
‚ÄĒ‚ÄĒ‚ÄĒ-¬†¬†¬† ‚ÄĒ‚ÄĒ‚ÄĒ‚ÄĒ‚ÄĒ‚ÄĒ‚ÄĒ‚ÄĒ‚ÄĒ‚ÄĒ‚ÄĒ‚ÄĒ‚ÄĒ‚ÄĒ‚ÄĒ‚ÄĒ‚ÄĒ‚ÄĒ‚ÄĒ‚ÄĒ‚ÄĒ‚ÄĒ‚ÄĒ‚ÄĒ‚ÄĒ‚ÄĒ‚ÄĒ‚ÄĒ‚ÄĒ‚ÄĒ‚ÄĒ‚ÄĒ‚ÄĒ‚ÄĒ‚ÄĒ‚ÄĒ‚ÄĒ‚ÄĒ‚ÄĒ‚ÄĒ‚ÄĒ‚ÄĒ‚ÄĒ‚Äď—
4       /u01/app/oracle/oradata/DB11G/standby_redo01.log                      STANDBY
3       /u01/app/oracle/oradata/DB11G/standby_redo02.log                      STANDBY
5       /u01/app/oracle/oradata/DB11G/standby_redo03.log                      STANDBY

Here i’ve added a standby logfile on Std DB end:

ALTER DATABASE ADD LOGFILE (‘/u01/app/oracle/oradata/DB11G/online_redo01.log’) SIZE 50M;
ALTER DATABASE ADD LOGFILE (‘/u01/app/oracle/oradata/DB11G/online_redo02.log’) SIZE 50M;
ALTER DATABASE ADD LOGFILE (‘/u01/app/oracle/oradata/DB11G/online_redo03.log’) SIZE 50M;

Pic Credits: Google/Oracle.com

Posted in Advanced | Tagged: , | 1 Comment »

Data Guard Configuration Attributes (LOG_ARCHIVE_DEST_n)

Posted by FatDBA on January 29, 2013

Today i will try to explain some of the Data Guard Configuration attributes that we use along with LOG_ARCHIVE_DEST_n parameter or entry:

AFFIRM and NOAFFIRM  |    ALTERNATE    |    COMPRESSION    |     DELAY        |       LOCATION and SERVICE         |          MANDATORY       |        MAX_CONNECTIONS        |         MAX_FAILURE       |        REOPEN       |        SYNC and ASYNC         |         VALID_FOR       |      NOREGISTER

AFFIRM and NOAFFIRM

Controls whether a redo transport destination acknowledges received redo data before or after writing it to the standby redo log:

AFFIRM‚ÄĒspecifies that a redo transport destination acknowledges received redo data after writing it to the standby redo log.
NOAFFIRM‚ÄĒspecifies that a redo transport destination acknowledges received redo data before writing it to the standby redo log.

  • ¬†If neither the AFFIRM nor the NOAFFIRM attribute is specified, the default is AFFIRM when the SYNC attribute is specified and NOAFFIRM when the ASYNC attribute is specified.

Examples
The following example shows the AFFIRM attribute for a remote destination.

LOG_ARCHIVE_DEST_3=’SERVICE=stby1 SYNC AFFIRM’
LOG_ARCHIVE_DEST_STATE_3=ENABLE

ALTERNATE
Specifies an alternate archiving destination to be used when the original destination fails.

  • The ALTERNATE attribute is optional. If an alternate destination is not specified, then redo transport services do not automatically change to another destination if the original destination fails.
  • You can specify only one alternate destination for each LOG_ARCHIVE_DEST_n parameter, but several enabled destinations can share the same alternate destination.
  • Any destination can be designated as an alternate destination, given the following restrictions:

– At least one local mandatory destination is enabled.
– The number of enabled destinations must meet the defined LOG_ARCHIVE_MIN_SUCCEED_DEST parameter value.

  • A destination cannot be its own alternate.

Examples
In the sample initialization parameter file, LOG_ARCHIVE_DEST_1 automatically fails over to LOG_ARCHIVE_DEST_2 on the next archival operation if an error occurs or the device becomes full.

Example –¬† Automatically Failing Over to an Alternate Destination

LOG_ARCHIVE_DEST_1=’LOCATION=/disk1 MANDATORY ALTERNATE=LOG_ARCHIVE_DEST_2′
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_2=’LOCATION=/disk2 MANDATORY’
LOG_ARCHIVE_DEST_STATE_2=ALTERNATE

COMPRESSION
The COMPRESSION attribute is used to specify whether redo data is transmitted to a redo transport destination in compressed form or uncompressed form when resolving redo data gaps.
* Redo transport compression is a feature of the Oracle Advanced Compression option. You must purchase a license for this option before using the redo transport compression feature.
The following example shows the COMPRESSION attribute with the LOG_ARCHIVE_DEST_n parameter.
LOG_ARCHIVE_DEST_3=’SERVICE=denver SYNC COMPRESSION=ENABLE’
LOG_ARCHIVE_DEST_STATE_3=ENABLE

DELAY
Specifies a time lag between when redo data is archived on a standby site and when the archived redo log file is applied to the standby database.

Valid values     >=0 minutes
Default Value 30 minutes
Requires attributes      SERVICE

  • ¬†The DELAY attribute indicates the archived redo log files at the standby destination are not available for recovery until the specified time interval has expired. The time interval is expressed in minutes, and it starts when the redo data is successfully transmitted to, and archived at, the standby site.
  • The DELAY attribute may be used to protect a standby database from corrupted or erroneous primary data. However, there is a tradeoff because during failover it takes more time to apply all of the redo up to the point of corruption.
  • If you have real-time apply enabled, any delay that you set will be ignored.
  • You can override the specified delay interval at the standby site, as follows:

For a physical standby database:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY;

For a logical standby database:
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY NODELAY;

The following example shows how to specify the DELAY attribute for this configuration:
LOG_ARCHIVE_DEST_1=’LOCATION=/oracle/dbs/’
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_2=’SERVICE=stbyB SYNC AFFIRM’
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_DEST_3=’SERVICE=stbyC DELAY=120′
LOG_ARCHIVE_DEST_STATE_3=ENABLE

LOCATION and SERVICE
Each destination must specify either the LOCATION or the SERVICE attribute to identify either a local disk directory or a remote database destination where redo transport services can transmit redo data.

  • Either the LOCATION or the SERVICE attribute must be specified. There is no default.
  • You can specify up to nine additional local or remote destinations.
  • For the LOCATION attribute, you can specify one of the following:

LOCATION=local_disk_directory

This specifies a unique directory path name for a disk directory on the system that hosts the database. This is the local destination for archived redo log files.
LOCATION=USE_DB_RECOVERY_FILE_DEST

  • When you specify a SERVICE attribute:

You identify remote destinations by specifying the SERVICE attribute with a valid Oracle Net service name (SERVICE=net_service_name) that identifies the remote Oracle database instance to which the redo data will be sent.

Example 1   Specifying the LOCATION Attribute

LOG_ARCHIVE_DEST_2=’LOCATION=/disk1/oracle/oradata/payroll/arch/’
LOG_ARCHIVE_DEST_STATE_2=ENABLE

Example 2   Specifying the SERVICE Attribute

LOG_ARCHIVE_DEST_3=’SERVICE=stby1′
LOG_ARCHIVE_DEST_STATE_3=ENABLE

MANDATORY
Specifies that filled online log files must be successfully archived to the destination before they can be reused.

  • The LOG_ARCHIVE_MIN_SUCCEED_DEST=n parameter (where n is an integer from 1 to 10) specifies the number of destinations that must archive successfully before online redo log files can be overwritten.
  • You must have at least one local destination, which you can declare MANDATORY or leave as optional.
  • At least one local destination is operationally treated as mandatory, because the minimum value for the LOG_ARCHIVE_MIN_SUCCEED_DEST parameter is 1.

Examples
The following example shows the MANDATORY attribute:

LOG_ARCHIVE_DEST_1=’LOCATION=/arch/dest MANDATORY
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_3=’SERVICE=denver MANDATORY’
LOG_ARCHIVE_DEST_STATE_3=ENABLE

MAX_CONNECTIONS
Enables multiple network connections to be used when sending an archived redo log file to a redo transport destination. Using multiple network connections can improve redo transport performance over high-latency network links.

Valid values     1 to
Default value   1

  • The MAX_CONNECTIONS attribute is optional. If it is specified, it is only used when redo transport services use ARCn processes for archival.
  • If MAX_CONNECTIONS is set to 1 (the default), redo transport services use a single ARCn process to transmit redo data to the remote destination.
  • If MAX_CONNECTIONS is set to a value greater than 1, redo transport services use multiple ARCn processes to transmit redo in parallel to archived redo log files at the remote destination. Each archiver (ARCn) process uses a separate network connection.
  • With multiple ARCn processes, redo transmission occurs in parallel, thus increasing the speed at which redo is transmitted to the remote destination.
  • Any standby database using archiver (ARCn) processes will not use standby redo logs if the MAX_CONNECTIONS attribute is specified. Thus, such destinations:

–¬† Cannot use real-time apply
– Cannot be configured as a redo forwarding destination

Example:

LOG_ARCHIVE_DEST_3=’SERVICE=denver MAX_CONNECTIONS=3′
LOG_ARCHIVE_DEST_STATE_3=ENABLE

MAX_FAILURE
Controls the consecutive number of times redo transport services attempt to reestablish communication and transmit redo data to a failed destination before the primary database gives up on the destination.

  • ¬†The MAX_FAILURE attribute is optional. By default, there are an unlimited number of archival attempts to the failed destination.
  • This attribute is useful for providing failure resolution for destinations to which you want to retry transmitting redo data after a failure, but not retry indefinitely.
  • When you specify the MAX_FAILURE attribute, you must also set the REOPEN attribute. Once the specified number of consecutive attempts is exceeded, the destination is treated as if the REOPEN attribute was not specified.
  • Once the failure count is greater than or equal to the value set for the MAX_FAILURE attribute, the REOPEN attribute value is implicitly set to zero, which causes redo transport services to transport redo data to an alternate destination (defined with the ALTERNATE attribute) on the next archival operation.

The following example allows redo transport services up to three consecutive archival attempts, tried every 5 seconds, to the arc_dest destination. If the archival operation fails after the third attempt, the destination is treated as if the REOPEN attribute was not specified.

LOG_ARCHIVE_DEST_1=’LOCATION=/arc_dest REOPEN=5 MAX_FAILURE=3′
LOG_ARCHIVE_DEST_STATE_1=ENABLE

REOPEN
Specifies the minimum number of seconds before redo transport services should try to reopen a failed destination.

  • Default Value 300 seconds
  • The REOPEN attribute is optional.
  • Redo transport services attempt to reopen failed destinations at log switch time.
  • Redo transport services check if the time of the last error plus the REOPEN interval is less than the current time. If it is, redo transport services attempt to reopen the destination.
  • REOPEN applies to all errors, not just connection failures. These errors include, but are not limited to, network failures, disk errors, and quota exceptions.

Example:

LOG_ARCHIVE_DEST_3=’SERVICE=stby1 MANDATORY REOPEN=60′
LOG_ARCHIVE_DEST_STATE_3=ENABLE

VALID_FOR
Specifies whether redo data will be written to a destination, based on the following factors:
–¬†¬† Whether the database is currently running in the primary or the standby role
–¬†¬† Whether online redo log files, standby redo log files, or both are currently being archived on the database at this destination

  • ¬†Default Value VALID_FOR=(ALL_LOGFILES, ALL_ROLES)
  • The VALID_FOR attribute is optional. However, Oracle recommends that the VALID_FOR attribute be specified for each redo transport destination at each database in a Data Guard configuration so that redo transport continues after a role transition to any standby database in the configuration.
  • To configure these factors for each LOG_ARCHIVE_DEST_n destination, you specify this attribute with a pair of keywords: VALID_FOR=(redo_log_type,database_role):

The redo_log_type keyword identifies the destination as valid for archiving one of the following:

ONLINE_LOGFILE‚ÄĒThis destination is valid only when archiving online redo log files.
STANDBY_LOGFILE‚ÄĒThis destination is valid only when archiving standby redo log files.
ALL_LOGFILES‚ÄĒ This destination is valid when archiving either online redo log files or standby redo log files.
The database_role keyword identifies the role in which this destination is valid for archiving:

PRIMARY_ROLE‚ÄĒThis destination is valid only when the database is running in the primary role.
STANDBY_ROLE‚ÄĒThis destination is valid only when the database is running in the standby role.
ALL_ROLES‚ÄĒThis destination is valid when the database is running in either the primary or the standby role.

LOG_ARCHIVE_DEST_1=’LOCATION=/disk1/oracle/oradata VALID_FOR=(ALL_LOGFILES, ALL_ROLES)’

NOREGISTER
Indicates that the location of the archived redo log file should not be recorded at the corresponding destination.

  • The NOREGISTER attribute is optional if the standby database destination is a part of a Data Guard configuration.
  • The NOREGISTER attribute is required if the destination is not part of a Data Guard configuration.

LOG_ARCHIVE_DEST_5=’NOREGISTER’

Posted in Advanced | Tagged: , | Leave a Comment »

Standby: Quick Switchover with Physical Database.

Posted by FatDBA on January 27, 2013

Oracle 10g Data Guard ‚Äď Quick Switchover with Physical Standby Database

You have a Standby database which is setup using Data Guard and works properly, you may want to test switchover, or perform switchover to reduce primary database downtime during OS upgrades or hardware upgrades.  A switchover allows the primary database to switch roles with its standby database. There is no data loss during a switchover. You can switch back to the original Primary database later by performing another switchover.

In case of primary database failure, you will need to perform failover to transition the standby database to the primary role. After a failover, the original primary database can no longer participate in the Data Guard configuration. So if the original Primary database is still accessible, you should always consider a switchover first.

This document only talks about switchover involving physical standby database. In this example, the original primary data is called PRIM and the original standby database is called STAN.

I. Before Switchover:

1. As I always recommend, test the Switchover first on your testing systems before working on Production.

2. Verify the primary database instance is open and the standby database instance is mounted.

3. Verify there are no active users connected to the databases.

4. Make sure the last redo data transmitted from the Primary database was applied on the standby database. Issue the following commands on Primary database and Standby database to find out:

SQL>select sequence#, applied from v$archvied_log;
Perform SWITCH LOGFILE if necessary.

In order to apply redo data to the standby database as soon as it is received, use Real-time apply.

II. Quick Switchover Steps

1. Initiate the switchover on the primary database PRIM:
SQL>connect /@PRIM as sysdba
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;

2. After step 1 finishes, Switch the original physical standby db STAN to primary role;
Open another prompt and connect to SQLPLUS:
SQL>connect /@STAN as sysdba
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

3. Immediately after issuing command in step 2, shut down and restart the former primary instance PRIM:
SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP MOUNT;

4. After step 3 completes:
– If you are using Oracle Database 10g release 1, you will have to Shut down and restart the new primary database STAN.
SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP;

– If you are using Oracle Database 10g release 2, you can open the new Primary database STAN:
SQL>ALTER DATABASE OPEN;

STAN is now transitioned to the primary database role.

5. On the new primary database STAN, perform a SWITCH LOGFILE to start sending redo data to the standby database PRIM.
SQL>ALTER SYSTEM SWITCH LOGFILE;

 

FAST-START Failover:
Other way to perform Switchovers automatically is using Data Guard Broker (DGMGRL). FAST-START Failover is one of the solution available and widely accepted to perform Auto Switchovers in case of Production faults/errors.

It requires:
1. Protection Mode should be Maximum Availability.
2. FAST-START Failover Observer.
3. A Production and Standby Database)

Untitled

 

Read More:

faststartfailoverbestp-131997

Posted in Advanced | Tagged: , | Leave a Comment »

Standby Scenario: Recovering After a Network Failure

Posted by FatDBA on December 9, 2012

http://docs.oracle.com/cd/A84870_01/doc/server.816/a76995/standbys.htm#30520

Posted in Advanced | Tagged: , | Leave a Comment »

Standby: Protection Modes.

Posted by FatDBA on October 30, 2012

The protection modes run in the order from highest (most data protection) to the lowest (least data protection):

Minimum Requirements for Data Protection Modes
Maximum Protection Maximum Availability Maximum Performance
Redo Archival Process LGWR LGWR LGWR or ARCH
Network Transmission Mode SYNC SYNC ASYNC when using LGWR process. Not applicable when using ARCH process.
Disk Write Option AFFIRM AFFIRM NOAFFIRM
Standby Redo Logs Required? Yes Required for physical standby databases only. Standby redo logs are not supported for logical standby databases. Required for physical standby databases using the LGWR process.
Database Type Physical only Physical and Logical Physical and Logical

Posted in Advanced | Tagged: , | Leave a Comment »

 
%d bloggers like this: