Implementing Standby db's
General Concepts
Architecture
DataGuard Protection Modes
Physical Standby
Implementation
Quick
Steps
for creating the Physical Standby from a Hot backup
Logical Standby
Implementation
Logical Standby Sanity
Check
Troubleshooting a
Logical Standby
Logical Standby
Database Activation (Role Transition) - Switchover and
Failover
Suspend Physical
Standby Recovery
Monitoring
Physical
DataGuard (Detect Gap)
Activate Physical
Standby (on Read-Only Mode or PROD Mode)
Physical
Standby
Switchover Steps
Physical
Standby
Failover Steps
Implementation Tips
Applying Pacthes with
Standby
Resolving Problems
Synchronize a GAP on the
STANDBY when ARCH Logs are lost
Improvements
in 10g
Monitor Dataguard
More
Information
General Concepts
Oracle Data Guard is the management, monitoring, and automation
software that work with a production database and one or more
standby databases to protect data against failures, errors, and
corruption that might otherwise destroy your database.
Data Guard Components
Oracle Data Guard consists of the following components:
A primary database is a production database. The primary database is used to create a standby database. Every standby database is associated with one and only one primary database.
Standby Database:
A physical or logical standby database is a database replica created from a backup of a primary database.
- A physical
standby database is physically identical to the primary
database on a block-for-block basis. It's
maintained in managed recovery mode to remain current and can
be set to read only; archive logs are copied and applied.
- A logical standby database is logically identical to the primary database. It is updated using SQL statements.
Enables and controls the automated transfer of redo data within a Data Guard configuration from the primary site to each of its standby sites.
Log transport services also controls the level of data protection for your database. The DBA will configure log transport services to balance data protection and availability against database performance. Log transport services will also coordinate with log apply services and role management services for switchover and failover operations.
Network Configuration:
The primary database is connected to one or more remote standby database via Oracle Net.
Log Apply Services:
Log apply services apply the archived redo logs to the standby database.
Data Guard Broker:
Data Guard Broker is the management and monitoring component with which you configure, control, and monitor a fault tolerant system consisting of a primary database protected by one or more standby database.
A database can operate in one of the two mutually exclusive
roles: primary or standby database.
During a failover, one of the standby databases takes the primary database role.
Switchover
The primary database can switch the role to a standby database; and one of the standby databases can switch roles to become the primary.
Data Guard Interfaces
SQL*Plus and SQL Statements
Using SQL*Plus and SQL commands to manage Data Guard environment. The following SQL statement initiates a switchover operation:
SQL> alter database commit to switchover to physical standby;
Data Guard Broker GUI Interface (Data Guard Manager)
Data Guard Manger is a GUI version of Data Guard broker interface that allows you to automate many of the tasks involved in configuring and monitoring a Data Guard environment.
Data Guard Broker Command-Line Interface (CLI)
It is an alternative interface to using the Data Guard Manger. It is useful if you want to use the broker from batch programs or scripts. You can perform most of the activities required to manage and monitor the Data Guard environment using the CLI. The following example lists the available commands:
$ dgmgrl
Welcome to DGMGRL, type "help" for information.
DGMGRL> help
The following commands are available:
quit
exit
show See "help show" for syntax
enable See "help enable" for syntax
disable See "help disable" for syntax
help [<command>]
connect <user>/<password> [@<connect>]
alter See "help alter" for syntax
create See "help create" for syntax
remove See "help remove" for syntax
switchover See "help switchover" for syntax
failover See "help failover" for syntax
startup See "help startup" for syntax
shutdown See "help shutdown" for syntax
Note: The use of an SPFILE is required with Oracle Release 2 when using a Data Guard Broker Configuration.
DBAs have the option to set up
two different types of standby databases. They are a physical
standby database and a logical standby database.
Physical standby databases are physically identical to primary
databases, meaning all objects in the primary database are the
same as in standby database. Physical
Standby databases are traditionally standby databases,
identical to primary databases on a block for block basis. It
is updated by performing media recovery; imagine a DBA sitting
in the office and recovering the database constantly.
Logical Standby Databases are logically identical
to primary databases although the physical organization and
structure of the data can be different. Logical Standby
Databases are updated using SQL statements. The advantage of a
logical standby database is that it can be used for recovery
and reporting simultaneously. I am very interested in the
logical standby feature as it can be used for my disaster
recover project as well as it can be used by data warehouse
users for their reporting purpose.
The log transport services and log apply services use the
following processes to ship and apply redo logs to the physical
standby database.
A physical standby database is a byte for byte exact copy of the
primary database. This also means that rowids stay the same in a
physical standby database environment.
On the primary database site, the log writer
process (LGWR) collects transactions from the log buffer
and writes to the online redo logs. The
archiver process (ARCH) creates a copy of the
online redo logs, and writes to the local archive destination. Depending on the configuration, the
archiver process or log writer process can also transmit redo
logs to standby database. When
using the log writer process, you can specify synchronous or
asynchronous network transmission of redo logs to remote
destinations. Data Guard achieves synchronous
network I/O using LGWR process. Data Guard achieves asynchronous
network I/O using LGWR network server process (LNS). These network severs processes are
deployed by LOG_ARCHIVE_DEST_n initialization parameter. Data Guard’s asynchronous log transport (i.e. the
Maximum Performance mode) is recommended for a configuration
in which the network distance is up to thousands of miles,
providing continual maximum performance, while minimizing the
risks of transaction loss in the event of a disaster.
On the standby database site, the remote file
server process (RFS) receives archived redo logs from the
primary database. The primary site
launches the RFS process during the first log transfer. The redo logs information received by
the RFS process can be stored as either standby redo logs or
archived redo logs. Data Guard
introduces the concept of standby redo logs (separate pool of
log file groups). Standby redo logs
must be archived by the ARCH process to the
standby archived destination before the managed
recovery process (MRP) applies redo log information to the
standby database.
The fetch archive log (FAL) client is the MRP
process. The fetch
archive log (FAL) server is a foreground process that runs
on the primary database and services the fetch archive log
requests coming from the FAL client. A
separate FAL server is created for each incoming FAL client.
Thanks to the FAL_CLIENT and FAL_SERVER parameters, the
managed-recovery process in the physical database will
automatically check and resolve gaps at the time redo is
applied. This helps in the sense that you don't need to
perform the transfer of those gaps by yourselve.
FAL_CLIENT and FAL_SERVER only need to be defined in the
initialization parameter file for the standby database(s). It
is possible; however, to define these two parameters in the
initialization parameter for the primary database server to
ease the amount of work that would need to be performed if the
primary database were required to transition its role.
FAL_SERVER specifies the FAL (fetch archive log) server for a
standby database. The value is an Oracle Net service name,
which is assumed to be configured properly on the standby
database system to point to the desired FAL server.
FAL_CLIENT specifies the FAL (fetch archive log) client name
that is used by the FAL service, configured through the
FAL_SERVER parameter, to refer to the FAL client. The value is
an Oracle Net service name, which is assumed to be configured
properly on the FAL server system to point to the FAL client
(standby database).
Prior to Oracle 11g, Redo Apply only worked with the standby
database in the MOUNT state, preventing queries against
the physical standby whilst media recovery was in progress.
This has changed in Oracle 11g.
When using Data Guard Broker (DG_BROKER_START
= TRUE), the monitor agent process named Data Guard Broker
Monitor (DMON) is running on every site (primary and
standby) and maintain a two-way communication.
Logical
Standby Processes Architecture (redo logs converted to sql,
called SQL APPLY)
The major difference between the logical and physical standby
database architectures is in its log apply services. On Logical Standby, you can query it while
simultaneously applying transactions from the primary. This is
ideal for business that requires a near real-time copy of your
production DB for reporting.
The key advantage for logical standby databases is that they're opened read/write,
even while they're in applied mode. That is, they can be used
to generate reports and the like. It is indeed a fully
functional database. Also, additional indexes, materialized
views and so on can be created.
However (this being a disadvantage) not all datatypes are
supported.
Oracle (or more exactly the log apply services) uses the
primary database's redo log, transforms them into SQL
statements and replays them on the logical standby database.
SQL Apply uses LOGMINER technology to reconstruct
DML statements from the redo generated on the primary.
The logical standby process (LSP) is the
coordinator process for two groups of parallel
execution process (PX) that work concurrently to read,
prepare, build, and apply completed SQL transactions from the
archived redo logs sent from the primary database.
The first group of PX processes read
log files and extract the SQL statements by using
LogMiner technology; the second group of PX processes apply
these extracted SQL transactions to the logical standby
database. The mining and applying
process occurs in parallel. Logical
standby database does not use standby online redo logs. Logical standby database does not have
FAL capabilities in Oracle. All
gaps are resolved by the proactive gap resolution mechanism
running on the primary that polls the standby to see if they
have a gap.
* Reporting: Synchronization of the logical standby database
with the primary database is done using logminer technology,
which transforms standard archived redologs into SQL
statements and applies them to the logical stand by database.
Therefore, the logical standby database must remain open and
the tables that are maintained can be used simultaneously for
reporting.
* System Resources: Besides the efficient utilisation of
system resources, reporting tasks, summations and queries can
be optimized by creating additional indexes and materialised
views, since both primary and logical standby database can
have a different physical lay out by protecting switchover and
failover for the primary database.
Data
Protection Modes
Maximum
Protection: It offers the highest level of data
availability for the primary database.
In order to provide this level of protection, the redo data
needed to recover each transaction must be written to both the
local (online) redo log and to a standby redo log on at least
one standby database before the transaction can be committed. In
order to guarantee no loss of data can occur, the primary
database will shut down if a fault prevents it from writing its
redo data to at least one remote standby redo log.
Redo records are synchronously transmitted from
the primary database to the standby database using LGWR process. Transaction is not committed
on the primary database until it has been confirmed
that the transaction data is available on at least one standby
database. This mode is usually
configured with at least two standby databases.
If all standby databases become unavailable, it may
result in primary instance shutdown. This
ensures that no data is lost when the primary database loses
contact with all the standby databases. Standby online
redo logs are required in this mode.
Therefore, logical standby database cannot participate in
a maximum protection configuration. The
log_archive_des_n parameter needs to have the LGWR SYNC AFFIRM option,
for example:
log_archive_dest_2='service=testdb_standby
LGWR SYNC AFFIRM'
NOTE
= It is highly recommended that a Data Guard configuration that
is run in maximum protection mode contain at least two physical
standby databases that meets the requirements listed in the
table above. That way, the primary database can continue
processing if one of the physical standby databases cannot
receive redo data from the primary database. If only one standby
database is configured with the minimum requirements listed
above, the primary database will shut down when the physical
standby databases cannot receive redo data from the primary
database!
Maximum Availability: Provides the
highest level of data protection that is possible without
affecting the availability of the primary database. This
protection mode is very similar to maximum protection where a
transaction will not commit until the redo data needed to
recover that transaction is written to both the local (online)
redo log and to at least one remote standby redo log.
Redo records are synchronously transmitted from
the primary database to the standby database using LGWR process. Unlike maximum protection mode;
however, the primary database will not shut down if a fault
prevents it from writing its redo data to a remote standby redo
log. Instead, the primary database will operate in maximum
performance mode until the fault is corrected and all log gaps
have been resolved. After all log gaps have been resolved, the
primary database automatically resumes operating in maximum
availability mode. This protection mode supports both physical
and logical standby databases. Standby online redo logs are
required in this mode. The log_archive_des_n
parameter needs to have the LGWR SYNC AFFIRM option, for example:
log_archive_dest_2='service=testdb_standby
LGWR SYNC AFFIRM'
Maximum Performance: It is the default
protection mode. It offers slightly
less primary database protection than maximum availability mode
but with higher performance. Redo
logs are asynchronously shipped from the primary
database to the standby database using either LGWR or ARCH
process. When operating in this
mode, the primary database continues its transaction processing
without regard to data availability on any standby databases and
there is little or no effect on performance.
It
supports both physical and logical standby databases. The log_archive_dest_n parameter needs to have the LGWR ASYNC AFFIRM or NOAFFIRM option,
for example:
log_archive_dest_2='service=testdb_standby
ARCH NOAFFIRM'
or
log_archive_dest_2='service=testdb_standby LGWR ASYNC
NOAFFIRM'
|
Mode |
Log Writing Process |
Network Trans Mode |
Disk Write Option |
Redo Log Reception Option |
Supported on |
|
Maximum Protection |
LGWR |
SYNC |
AFFIRM |
Standby redo logs are required |
Physical standby databases |
|
Maximum Availability |
LGWR |
SYNC |
AFFIRM |
Standby redo logs |
Physical and logical standby databases |
|
Maximum Performance |
LGWR or ARCH |
ASYNC if LGWR |
NOAFFIRM |
Standby redo logs |
Physical and logical standby databases |
These can be found in the Oracle documentation in Chapter 5 of
the Data Guard Concepts and Administration Manual.
• AFFIRM assures
that archive logs are written to disk, primary or standby.
• MANDATORY assures
that redo logs are not overwritten until archive logs are
successfully created. This should only apply to the primary
database.
• REOPEN=30 means
that there will be a 30 second delay until LGWR process try
again on a MANDATORY destination which failed.
• DELAY is in
minutes and does not stop the copy of an archive log file to a
standby server but the application of redo on the standby
after copying the archive log to the standby. This will not
help primary database performance.
• Using ARCH instead
of LGWR for the
second standby database may help primary database performance
but smaller sized log files would probably be required.
SYNC=PARALLEL applies to LGWR only. Using ARCH waits for a
switch on the primary, LGWR copies entries to a standby
archive log, applied only at switch. ARCH will copy and apply
at switch. LGWR is more efficient since it writes redo entries
to all standby databases at once but a primary and two standby
databases could possibly cause a performance issue for the
primary database, possibly but unlikely! Additionally multiple
archiver processes can be created on the primary database.
Increase the value of the LOG_ARCHIVE_MAX_PROCESSES parameter
to start additional archiver processes. The default on my
machine appears to be 2 and not 1 as stated in the manuals;
probably because I have two standby databases.
• The ARCHIVE_LAG_TARGET
parameter could be used to increase the frequency of log
switches, thus sending less data to the standby databases more
often. Specifies the maximum number of seconds between each
log switch, so it will force a log switch when that number in
seconds is reached. Used on Physical Implementation Only.
Now let’s check for objects and attributes which are
unsupported on a logical standby database. Application objects
such as tables could be a problem.
SELECT * FROM
DBA_LOGSTDBY_UNSUPPORTED ORDER BY owner,table_name,column_name;
Now check for missing primary keys. Application tables without
unique primary keys will require them, as rows will not be
identifiable in the logical standby database for update by SQL
Apply. Drop any objects listed or create primary keys for
them.
SELECT OWNER, TABLE_NAME,
BAD_COLUMN FROM DBA_LOGSTDBY_NOT_UNIQUE;
Using
Data Guard Redo Apply in a LAN the following is recommended:
• Use Maximum Protection or Maximum Availability modes for zero
data loss; the performance impact was less than 3% in all
synchronous tests. With a single remote archive destination, use
the NOPARALLEL option (“lgwr sync=noparallel”).
• For very good performance and a minimal risk of transaction
loss in the event of a disaster, use Maximum Performance mode,
with LGWR ASYNC and a 10 MB async buffer (ASYNC=20480). LGWR
ASYNC performance degraded no more than 1% as compared to using
the ARCH transport. LGWR ASYNC also bounds the risk of potential
transaction loss much better than the ARCH transport. The 10 MB
async buffer outperformed smaller buffer sizes and reduced the
chance of network timeout errors in a high latency / low
bandwidth network.
Metropolitan
and Wide Area Network (WAN)
Data Guard is used across a metropolitan area networks (MAN) or
WANs to get complete disaster recovery protection.
Typically a MAN covers a large metropolitan area and has network
Round-Trip-Times (RTT) from 2-10 ms. For the MAN/WAN tests,
different network RTT’s were simulated during testing to measure
the impact of the RTT on the primary database performance. The
tests were conducted for the following RTT’s: 2 ms (MAN), 10 ms,
50 ms, and 100 ms (WAN) Additionally, tests using Secure Shell
(SSH) port forwarding with compression were also done for
different RTT’s.
Best practices recommendations are:
• Use Maximum Protection and Maximum Availability modes over a
MAN for zero data loss. For these modes, the network RTT
overhead over a WAN can impact response time and throughput of
the primary database. The performance impact was less than 6%
with a 10 ms network RTT and a high transaction rate.
• For very good performance and a minimal risk of transaction
loss in the event of a disaster, use Maximum Performance mode,
with LGWR ASYNC and a 10 MB async buffer (ASYNC=20480). LGWR
SYNC performance degraded no more than 2% as compared to remote
archiving. The 10 MB async buffer outperformed smaller buffer
sizes and reduced the chance of network timeout errors in a high
latency / low bandwidth network.
• For optimal primary database performance throughput, use
remote archiving (i.e. the ARCH process as the log transport).
This configuration is best used when network bandwidth is
limited and when your applications can risk some transaction
loss in the event of a disaster.
• If you have sufficient memory, then set the TCP send and
receive buffer sizes (these affect the advertised TCP window
sizes) to the bandwidth delay product, the bandwidth times the
network round trip time. This can improve transfer time to the
standby by as much as 10 times, especially with the ARCH
transport.
Best
Practices
for Network Configuration and Highest Network Redo Rates
• Set SDU=32768 (32K) for the Oracle Net connections between the
primary and standby. Setting the Oracle network services session
data unit (SDU) to its maximum setting of 32K resulted in a 5%
throughput improvement over the default setting of 2048 (2K) for
LGWR ASYNC transport services and a 10% improvement for the LGWR
SYNC transport service. SDU designates the size of the Oracle
Net buffer used to collect data before it is delivered to the
TCP network layer for transmission across the network. Oracle
internal testing of Oracle Data Guard has demonstrated that the
maximum setting of 32767 performs best. The gain in performance
is a result of the reduced number of system calls required to
pass the data from Oracle Net buffers to the operating system
TCP network layer. SDU can be set on a per connection basis with
the SDU parameter in the local naming configuration file
(tnsnames.ora) and the listener configuration file
(listener.ora), or SDU can be set for all Oracle Net connections
with the profile parameter DEFAULT_SDU_SIZE in the sqlnet.ora
file. This is specially true for WAN environment.
• Use SSH port forwarding with compression for WAN’s with a
large RTT when using maximum performance mode. Do not use SSH
with compression for Maximum Protection and Maximum Availability
modes since it adversely affected the primary throughput. Using
SSH port forwarding with compression reduced the network traffic
by 23-60% at a 3-6% increase in CPU usage. This also eliminated
network timeout errors. With the ARCH transport, using SSH also
reduced the log transfer time for RTT’s of 50 ms or greater. For
RTT’s of 10ms or less, the ARCH transport log transfer time was
increased when using SSH with compression.
• Ensure TCP.NODELAY is YES
To preempt delays in buffer flushing in the TCP protocol stack,
disable the TCP Nagle algorithm by setting TCP.NODELAY to YES in
the SQLNET.ORA file on both the primary and standby systems.
This is achieved by using the following command syntax
executed on the primary database.
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE
{ PROTECTION | AVAILABILITY | PERFORMANCE };
The protection mode can be found by executing this query.
PERFORMANCE is the default.
SELECT name,
protection_mode, protection_level FROM v$database;
--------- -------------------- --------------------
STBY MAXIMUM PERFORMANCE
MAXIMUM PERFORMANCE
-Minimal Data Loss. The AVAILABILITY mode prevents a
transaction committing on the primary until all redo entries
are written to at least one standby database. SYNC transport
is required and this option is available to both logical and
physical standby type databases. Unlike PROTECTION mode, which
shuts down the primary database in the event of failure to
pass redo entries to the standby, this mode simply lowers the
protection mode to PERFORMANCE until the error is corrected.
- No Data Divergence. PERFORMANCE mode is the default setting
and available for both physical and logical standby type
databases. A transaction will commit to the primary before all
redo entries are written to any standby database.
To ensure that minimal data loss will be encountered execute
this command on the primary database. The database must be in
mounted exclusive mode to execute this command.
ALTER DATABASE SET STANDBY
DATABASE TO MAXIMIZE AVAILABILITY;
PHysical
Standby Implementation
2. One primary database instance called FGUARD on host server_01; one physical standby database instance called FGUARD on host server_02.
3. Listener listener is on host server1, and pointed by TNS entry FGUARD
4. Listener listener is on host server2, and pointed by TNS entry FGUARD.
5. The purpose of TNS entry FGUARD and FGUARD are used for LGWR/ARCH process to ship redo logs to the standby site, and for FAL process to fetch redo logs from the primary site.
6. We will be implementing the configuration manually, but we will set dg_broker_start to true, so Data Guard broker can be used later.
7. The protection mode is set to best performance. Therefore, only local archive destination (log_archive_dest_1) is set to mandatory; the standby archive destination (log_archive_dest_2) is set to optional for LGWR process, with network transmission method of asynchronous and disk write option of no affirm.
8. The standby site is not using standby online redo logs. Therefore, the redo log reception option is archived logs.
Section 1: Site Information
Primary Site:
Database Name: FGUARD
Primary Server : server_01
Primary Instance Name: FGUARD
Primary Listener: LISTENER
Recovery Database: DR_FGUARD
Standby Site:
Database Name: FGUARD
Standby Server: server_02
Standby Instance name: FGUARD
Standby Listener: LISTENER
Production DB: PROD_FGUARD
Section 2: Oratab /etc/oratab entry:
| Primary Site: | Standby Site: |
| FGUARD:/u01/app/oracle/product/11.2.0:Y | FGUARD:/u01/app/oracle/product/11.2.0:N |
Section 3: Parameter file
Primary init.ora file:
db_name = FGUARD
db_unique_name = FGUARD
#fal_server = DR_FGUARD #PROD DB used on tnsnames.ora
#fal_client = FGUARD #this DB used on tnsnames.ora
log_archive_dest_1 = 'LOCATION=/u02/arch/PROD MANDATORY' #Local Location of Archive Log Files
log_archive_dest_2 = 'SERVICE=DR_FGUARD reopen=60' #Remote Service Name based on tnsnames.ora
log_archive_dest_state_1 = 'enable'
log_archive_dest_state_2 = 'enable'
log_archive_format = 'arch_t%t_s%s.dbf'
log_archive_start = true (not used on 10g)
standby_archive_dest = '/oracle/arch'
standby_file_management ='AUTO' #If auto, newly created tablespaces/datafiles must be created manually on the standby environment.
dg_broker_start = true
New Ones
service_names =FGUARD
instance_name =FGUARD
log_archive_config="dg_config=(FGUARD,DR_FGUARD)" -->This parameter is required by the Data Guard Broker
log_archive_max_processes=5
log_archive_dest_1='location=D:\oracle\product\10.1.0\flash_recovery_area\FGUARD\ARCHIVELOG valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=FGUARD'
log_archive_dest_2= 'service=standby LGWR SYNC AFFIRM valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DR_FGUARD'
standby_archive_dest=D:\oracle\product\10.1.0\flash_recovery_area\PRIMARY\ARCHIVELOG
Optional parameters:
log_archive_dest_2='service=stby lgwr sync affirm mandatory reopen=180'
LOG_ARCHIVE_DEST_2 - Specifies the net service name of the standby database (check tnsnames.ora on primary database).You can either per destination use LGWR or ARCH or both, due to network traffic it is advised to use LGWR for at most one remote destination. Also the network transmission mode (SYNC or ASYNC) has to be specified in case primary database modifications are propagated by the LGWR. The NO DATA LOSS situation demands the SYNC mode, control is not returned to the executing application or user until the redo information is received by the standby site (this can have impact on the performance as mentioned).
Standby init.ora file:
db_name = FGUARD
db_unique_name = DR_FGUARD --> MUST BE DIFFERENT FROM PRIMARY SITE
fal_server = PROD_FGUARD #PROD DB used on tnsnames.ora
fal_client = FGUARD #this DB used on tnsnames.ora
log_archive_dest_1 = 'LOCATION=/oracle/arch MANDATORY' #This parameter should always coincide with the standby_archive_dest parameter
log_archive_dest_state_1 = 'enable'
#log_archive_dest_2 = 'SERVICE=PROD_FGUARD reopen=60'
#log_archive_dest_state_2 = 'enable'
log_archive_format = 'arch_t%t_s%s.dbf'
log_archive_start = true (not used on 10g)
standby_archive_dest = '/oracle/arch' ##This parameter should always coincide with the log_archive_dest_1 parameter
standby_file_management ='AUTO'
dg_broker_start = true
New Ones
service_names='DR_FGUARD'
instance_name=DR_FGUARD
control_files='D:\oradata\PRIMARY_STDBY.CTL'
log_archive_config="dg_config=(FGUARD,DR_FGUARD)" -->This parameter is required by the Data Guard Broker
log_archive_max_processes=5
log_archive_dest_1='location=D:\oracle\product\10.1.0\flash_recovery_area\PRIMARY\ARCHIVELOG valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=DR_FGUARD'
log_archive_dest_2= 'service=FGUARD LGWR SYNC AFFIRM valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=FGUARD'
standby_archive_dest=D:\oracle\product\10.1.0\flash_recovery_area\PRIMARY\ARCHIVELOG
Optional Parameters:
db_file_name_convert=('/disk1/oracle/oradata/payroll/','/disk1/oracle/oradata/payroll/standby/')
or
db_file_name_convert=('/PROD2/','/PROD')
log_file_name_convert=('/disk1/oracle/oradata/payroll/','/disk1/oracle/oradata/payroll/standby/')
or
log_file_name_convert=('/PROD2/','/PROD')
DB_FILE_NAME_CONVERT - Specifies the location of datafiles on standby database.The two arguments that this parameter needs are: location of datafiles on primary database , location of datafiles on standby database. This parameter will convert the filename of the primary database datafiles to the filename of the standby datafile filenames. If the standby database is on the same system as the primary database or if the directory structure where the datafiles are located on the standby site is different from the primary site then this parameter is required. See Section 3.2.1 for the location of the datafiles on the primary database. Used on Physical Implementation ONLY.
LOG_FILE_NAME_CONVERT - Specifies the location of redo logfiles on standby database.The two arguments that this parameter needs are: location of redo logfiles on primary database , location of redo logfiles on standby database. This parameter will convert the filename of the primary database log to the filenames of the standby log. If the standby database is on the same system as the primary database or if the directory structure where the logs are located on the standby site is different from the primary site then this parameter is required. Used on Physical Implementation ONLY.
Section 4: Listener.ora file
| Primary Site: | Standby Site: |
| FGUARD = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = server1)(PORT = 1521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = FGUARD) ) ) DR_FGUARD = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = server2)(PORT = 1521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = FGUARD) ) ) |
FGUARD= (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = server2)(PORT = 1521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = FGUARD) ) ) PROD_FGUARD = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = server1)(PORT = 1521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SID = FGUARD) ) ) |
Step 1: The Preparation
If Archive Log Mode is not enabled for your primary database, enable it using the following. First, you will need to define the following instance variables:
alter system set log_archive_dest_1 = 'LOCATION=/oracle/arch MANDATORY' scope=both;
alter system set log_archive_dest_state_1 = 'enable' scope=both;
alter system set log_archive_format = 'arch_t%t_s%s.dbf' scope=both;
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;
archive log list;
- Enable Forced Logging. This option ensures that even in the event that a 'nologging' operation is done, force logging takes precedence and all operations are logged into the redo logs.
alter database force logging;
Standby redo logs are necessary for the higher protection levels such as Guaranteed, Instant, and Rapid. In these protection modes LGWR from the Primary host writes transactions directly to the standby redo logs. This enables no data loss solutions and reduces the amount of data loss in the event of failure. Standby redo logs are not necessary if you are using the delayed protection mode.
If you configure standby redo on the standby then you should also configure standby redo logs on the primary database. Even though the standby redo logs are not used when the database is running in the primary role, configuring the standby redo logs on the primary database is recommended in preparation for an eventual switchover operation
Standby redo logs must be archived before the data can be applied to the standby database. The standby archival operation occurs automatically, even if the standby database is not in ARCHIVELOG mode. However, the archiver process must be started on the standby database. Note that the use of the archiver process (performed by the LGWR process) is a requirement for selection of a standby redo log
You must have the same number of standby redo logs on the standby as you have online redo logs on production. They must also be exactly the same size.
select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ---------------------------------------------------------------------- ---
1 ONLINE /export/home/oracle/temp/oracle/data/redo01.log NO
2 ONLINE /export/home/oracle/temp/oracle/data/redo02.log NO
3 ONLINE /export/home/oracle/temp/oracle/data/redo03.log NO
select bytes from v$log;
BYTES
----------
52428800
52428800
52428800
The following syntax is used to create standby redo logs:
SQL> alter database add standby logfile GROUP 4 size 50m;
SQL> alter database add standby logfile GROUP 5 size 50m;
SQL> alter database add standby logfile GROUP 6 size 50m;
select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ---------------------------------------------------------------------- ---
1 ONLINE /export/home/oracle/temp/oracle/data/redo01.log NO
2 ONLINE /export/home/oracle/temp/oracle/data/redo02.log NO
3 ONLINE /export/home/oracle/temp/oracle/data/redo03.log NO
4 STANDBY /export/home/oracle/temp/oracle/data/standbyredo01.dbf NO
5 STANDBY /export/home/oracle/temp/oracle/data/standbyredo01.dbf NO
6 STANDBY /export/home/oracle/temp/oracle/data/standbyredo01.dbf NO
select * from v$standby_log;
GROUP# DBID THREAD# SEQUENCE# BYTES USED ARC STATUS FIRST_CHANGE# FIRST_TIM LAST_CHANGE# LAST_TIME
-------- ----------- ------- ---------- --------- ----- ------------------------------------------------------------------
4 UNASSIGNED 0 0 52428800 512 YES UNASSIGNED 0 0
5 UNASSIGNED 0 0 52428800 512 YES UNASSIGNED 0 0
6 UNASSIGNED 0 0 52428800 512 YES UNASSIGNED 0 0
· Setup the init.ora file for both primary and standby databases. (see section 3)
NOTE: In the above example db_file_name_convert and log_file_name_convert are not needed as the directory structure on the two hosts are the same. If the directory structure is not the same then setting of these parameters is recommended. Please reference ML notes 47325.1 and 47343.1 for further information.
Note here that the Primary init.ora on the Standby host to have log_archive_dest_2 use the alias that points to the Primary host. You must modify the Standby init.ora on the standby host to have fal_server and fal_client use the aliases when standby is running on the Primary host.
· Setup the tnsnames.ora and listener.ora file for both primary and standby databases. (see section 4)
Step 2: Backup the primary Database
Datafiles
· Backup the primary database datafiles and online redo logs. A backup of the online redo logs is necessary to facilitate switchover.
or
set pages 50000 lines 120 head off veri off flush off ti off
select 'cp ' || file_name || ' /oracle/BCKUP' from dba_data_files
UNION
select 'cp ' || file_name || ' /oracle/BCKUP' from dba_temp_files
UNION
select 'cp ' || member || ' /oracle/BCKUP' from v$logfile;
Step 3: Create the Physical standby
Database Control File
NOTE: The controlfile must be created after the last time stamp for the backup datafiles.
Step 4: Transfer the Datafiles and
Standby Control File to the Standby Site
cd $ORACLE_HOME/dbs
orapwd file=orapwFGUARD password=oracle force=y ignorecase=y
If needed perform the following on the other system:
chmod 6751 orapwSID
Step 5: Start the Listeners on both
Primary and Standby Site
WINNT> oradim -NEW -SID databaseid -INTPWD password -STARTMODE manual
· Start the the listener on the primary and standby database
Step 6: Start the Standby Database (Primary Database already
running)
If the standby is on a separate site with the same directory structure as the primary database then you can use the same path names for the standby files as the primary files. In this way, you do not have to rename the primary datafiles in the standby control file.
If the standby is on the same site as the primary database, or the standby database is on a separate site with a different directory structure the you must rename the primary datafiles in the standby control file after copying them to the standby site. This can be done using the db_file_name_convert and log_file_name_convert parameters or by manually using the alter database statements. If the directory structure is not the same then reference notes 47325.1 and 47343.1 for further information.
If you decided to rename them manually, you MUST use ALTER DATABASE RENAME FILE <oldname> TO <newname> after the standby database is mounted to rename the database files and redo log files..
If needed, copy the Standby Controlfile that your created FROM the production DB to the appropiate location on the standby DB according your init.ora file
$ cd
$ cp standby.ctl /u03/app/oradata/FGUARD/control01.ctl
$ cp standby.ctl /u04/app/oradata/FGUARD/control02.ctl
$ cp standby.ctl /u05/app/oradata/FGUARD/control03.ctl
· Connect as sysdba.
· Bring the database in nomount mode first.
Step 7: Place the Standby Database
in Managed Recovery Mode
Step 8: Monitor the Log Transport
services and Log Apply Services
or
SQL> alter system archive log current;
· Check the standby alert log file to see if the new logs have applied to the standby database.
from v$managed_standby;
or
select sequence#, first_time, next_time
from v$archived_log order by sequence#;
From the standby database, query the V$ARCHIVED_LOG view to verify the archived redo log was applied.
select sequence#, archived, applied
from v$archived_log order by sequence#;
SEQUENCE# ARCHIVED APPLIED
---------- -------- -------
115 YES YES
116 YES YES
Quick Steps for Creating the Physical Standby from Hot Backup
At PROD Site
put proper values on the primary db using "alter system set...... "
create pfile from spfile;
alter system switch logfile;
alter system archive log current;
perform hot backup
vi Quick_Hot_Backup.sql
set serveroutput on
set heading off
set feedback off
Set verify off
accept destination prompt 'Enter destination (like /home/dpafumi/) : '
Set Termout off
spool hotbackups.sql
declare
fname varchar2(80);
tname varchar2(80);
tname1 varchar2(80);
aux varchar2(100);
cursor cur1 is
select tablespace_name,file_name
from v$datafile,sys.dba_data_files
where enabled like '%WRITE%'
and file# = file_id
order by 1;
begin
dbms_output.enable(32000);
dbms_output.put_line('spool hotbackups.txt');
if cur1%ISOPEN
then
close cur1;
end if;
open cur1;
fetch cur1 into tname,fname;
tname1 := tname;
dbms_output.put_line('alter tablespace '||tname||' begin backup;');
while cur1%FOUND loop
if tname1 != tname then
dbms_output.put_line('alter tablespace '||tname1||' end backup;');
dbms_output.put_line('alter tablespace '||tname||' begin backup;');
tname1 := tname;
end if;
dbms_output.put_line('!cp '||fname||' &&destination');
fetch cur1 into tname,fname;
end loop;
dbms_output.put_line('alter tablespace '||tname1||' end backup;');
close cur1;
dbms_output.put_line('alter system switch logfile;');
dbms_output.put_line('!sleep 10');
-- dbms_output.put_line('!cp /oracle/oracle7/app/oracle/admin/DIE/ARCHIVE/*.arc '||' &&destination');
dbms_output.put_line('alter database backup controlfile to trace;');
dbms_output.put_line('alter database backup controlfile to '|| CHR(39)|| '&&destination' || '/control.'|| to_char(sysdate,'DDMMYYYYHH24MISS')|| CHR(39) ||';');
dbms_output.put_line('REM *** Copy Last file from udump ***' );
dbms_output.put_line('spool off');
end;
/
spool off
set heading on
set feedback on
set serveroutput off
-- Unremark/Uncomment the following line to run the script
-- or can be run from the sqlplus prompt.
-- @hotbackups
@Quick_Hot_Backup.sql
@hotbackups
--You can check your backup status with:
select substr(b.status,1,10) status,
substr(f.TABLESPACE_NAME,1,15) Ts_Name,
substr(d.NAME,1,50) Datafile
from v$backup b, dba_data_files f, v$datafile d
where b.file# = d.file#
and d.NAME = f.FILE_NAME;
- Compress files
- Copy compressed files to standby site
scp CC_*.Z oracle@10.10.10.10:/home/oracle/DBA_SCRIPTS/BCKP
.........
.........
- sqlplus "/ as sysdba"
alter database create standby controlfile as '/home/dpafumi/standby.ctl';
alter database backup controlfile to trace;
- transfer init.ora, passwd and ctrlfile.bin files to standby DB
scp standby.ctl oracle@10.10.10.10:/home/oracle/DBA_SCRIPTS/BCKP
-- transfer LATEST ARCH Log Files
alter system switch logfile;
alter system archive log current;
cd to arch location
cp ARCH*.dbf /home/dpafumi
scp ARCH*.dbf oracle@10.10.10.10:/home/oracle/DBA_SCRIPTS/BCKP
At standby site:
- Copy *.dbf, *log, standby.ctl and *.ora to proper locations
- If needed, copy the Standby Controlfile that your created FROM the production DB to the appropiate location on the standby DB according your init.ora file
$ cd
$ cp standby.ctl /u03/app/oradata/FGUARD/control01.ctl
$ cp standby.ctl /u04/app/oradata/FGUARD/control02.ctl
$ cp standby.ctl /u05/app/oradata/FGUARD/control03.ctl
If needed perform the following on the other system:
chmod 6751 orapwSID
- Modify init.ora file containing correct information (like udump, contrl file, etc)
Use if needed db_file_name_convert=('/opt/oracle/product/10.1.0/db_1/oradata/FGUARD/','/data/oradata/FGUARD/')
log_file_name_convert=('/opt/oracle/product/10.1.0/db_1/oradata/FGUARD/','/data/oradata/FGUARD/')
- sqlplus "/ as sysdba"
create spfile from pfile;
startup nomount;
alter database mount standby database;
recover standby database;
alter database recover managed standby database disconnect from session;
-- Test and Monitor Arch Log Files Transfer
-- On PROD
alter system switch logfile;
LOGICAL standby Implementation
Prior to creating the
Logical Standby ensure the following:
The physical organization
in a logical standby database is different from that of the
primary database, even though the logical standby database is
created from a backup copy of the primary database. Thus,
ROWIDs contained in the redo records generated by the primary
database cannot be used to identify the corresponding row in
the logical standby database.
Oracle uses primary-key or unique-key supplemental logging to
logically identify a modified row in the logical standby
database. When database-wide primary-key and unique-key
supplemental logging is enabled, each UPDATE statement also
writes the column values necessary in the redo log to uniquely
identify the modified row in the logical standby database.
- If a table has a primary key defined, then the primary key
is logged as part of the UPDATE statement to identify the
modified row.
- In the absence of a primary key, the shortest non-null
unique key is logged as part of the UPDATE statement to
identify the modified row.
- In the absence of both a primary key and a non-null unique
key, all columns of bounded size are logged as part of the
UPDATE statement to identify the modified row. In other words,
all columns except those with the following types are logged:
LONG, LOB, LONG ROW, object type, and collections.
SELECT
DISTINCT
OWNER,TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED
ORDER BY
OWNER,TABLE_NAME;
To view the column names and data types which are not
supported for one of the tables listed in the previous query,
run the following select:
col DATA_TYPE format a20
col COLUMN_NAME format
a20
SELECT
COLUMN_NAME,DATA_TYPE FROM DBA_LOGSTDBY_UNSUPPORTED WHERE
OWNER='XXX' AND TABLE_NAME = 'YYY';
- Ensure that table rows in the
primary database can be uniquely identified.
Oracle recommends that you add a primary key
or a non-null unique index to tables in the primary database,
whenever possible, to ensure that SQL Apply can efficiently
apply redo data updates to the logical standby database. Find
tables without unique logical identifier in the primary
database:
SELECT OWNER, TABLE_NAME
FROM DBA_LOGSTDBY_NOT_UNIQUE
WHERE (OWNER,
TABLE_NAME) NOT IN (SELECT DISTINCT OWNER, TABLE_NAME
FROM DBA_LOGSTDBY_UNSUPPORTED)
AND BAD_COLUMN = 'Y';
If on the previous command you get
a reference over the TSMSYS.SRS$ Table, you can safely ingore
it. TSMSYS is created by rdbms/admin/cattsm.sql for
transparent session migration. It’s expired & locked by
default
- Add a primary key to the tables that
do not have to improve performance.
If the table has a primary key or a unique index with a
non-null column, the amount of information added to the redo
log is minimal.
If your application ensures the rows in a table are unique,
you can create a disabled primary key RELY constraint on the
table. This avoids the overhead of maintaining a primary key
on the primary database.
ALTER TABLE
TableA ADD PRIMARY KEY (id, name) RELY DISABLE;
When you specify the RELY constraint, the system will assume
that rows are unique. Because you are telling the system to
rely on the information, but are not validating it on every
modification done to the table, you must be careful to select
columns for the disabled RELY constraint that will uniquely
identify each row in the table. If such uniqueness is not
present, then SQL Apply will not correctly maintain the table.
To improve the performance of SQL Apply, add an index to the
columns that uniquely identify the row on the logical standby
database. Failure to do so results in full table scans during
UPDATE or DELETE statements carried out on the table by SQL
Apply.
- Ensure that Primary database is in
archivelog mode.
SQL> archive log
list;
- Create a Physical Standby Database
For more information about how to create a physical standby
database click HERE.
Once the Physical Standby is created,
you can move on.
On STANDBY database
1. Make sure that all the sequences generated on primary side
has been applied on standby.
SELECT ARCH.THREAD#
"Thread", ARCH.SEQUENCE# "Last Sequence Received",
APPL.SEQUENCE# "Last Sequence Applied"
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;
2. Cancel the recovery on standby database
ALTER DATABASE RECOVER
MANAGED STANDBY DATABASE CANCEL;
On PRIMARY database
1. Review Destination Parameter
show parameters
log_archive_dest_1
log_archive_dest_1
string LOCATION=F:\oracle\oradata\ARCH MANDATORY
2. On primary set the VALID_FOR parameter to make destination
1 as valid for online redo logs and role as all roles.
This mean even if we change the role of primary server to
standby server, online redo logs will get archived to archive
destination
alter system set
log_archive_dest_1='location=F:\oracle\oradata\ARCH
valid_for=(ONLINE_LOGFILES,ALL_ROLES)' scope=both;
3. Create another archive destination log_archive_dest_3 which
will archive only standby redo logs and role should be set to
standby_role.
This means that in case if we make this primary as standby
then standby redo logs will be archived automatically at
archive destination 3.
mkdir
F:\oracle\oradata\ARCH\standby
alter system set
LOG_ARCHIVE_DEST_3='LOCATION=F:\oracle\oradata\ARCH\standby
VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE)' scope=both;
alter system set
LOG_ARCHIVE_DEST_STATE_3='enable' scope=both;
4. Run DBMS_LOGSTDBY.BUILD package to
create metadata for log miner to apply SQLs on logical standby
site.
A LogMiner dictionary must be built into the redo data so that
the LogMiner component of SQL Apply can properly interpret
changes it sees in the redo. Additionally, supplemental
logging is set up to log primary key and unique-index columns.
The supplemental logging information ensures each update
contains enough information to logically identify each row
that is modified by the statement.
EXECUTE
DBMS_LOGSTDBY.BUILD;
The DBMS_LOGSTDBY.BUILD procedure waits for all existing
transactions to complete. Long-running transactions executed
on the primary database will affect the timeliness of this
command.
The DBMS_LOGSTDBY.BUILD procedure uses Flashback Query to
obtain a consistent snapshot of the data dictionary that is
then logged in the redo stream. Oracle recommends setting the
UNDO_RETENTION initialization parameter to 3600 on both the
primary and logical standby databases.
After running this command, an entry will go in redo log file
and when that redo log file gets shipped to standby, from that
point onwards SQL apply will start on standby.
5. Supplemental logging should already be enabled on the
primary database as a result of building the LogMiner
Multiversioned Data Dictionary in the previous section.
select
supplemental_log_data_pk,supplemental_log_data_ui from
v$database;
SUP SUP
--- ---
YES YES
If the supplemental logging is not enabled, execute the
following
SQL> ALTER DATABASE
ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY,UNIQUE INDEX)
COLUMNS;
SQL> ALTER SYSTEM
SWITCH LOGFILE;
On STANDBY database
1. The redo logs contain the information necessary to convert your physical standby database to a logical standby database.
To continue applying redo data to the physical standby database until it is ready to convert to a logical standby database, issue the following SQL statement:
ALTER DATABASE RECOVER TO LOGICAL STANDBY NEW_NAME;
On a Physical Standby, we used the same DB_Name as the primary.
But now we have to change the actual DB Name of the standby so it can become a logical standby database.
DataGuard will change the database name (DB_NAME) and set a new database identifier (DBID) for the logical standby.
For db_name, specify a database name that is different from the primary database to identify the new logical standby database.
If you are using a server parameter file (SPFILE) at the time you issue this statement, then the database will update the DB_NAME parameter with appropriate information about the new logical standby database. If you are using a PFILE, then the database issues a message reminding you to set the name of the DB_NAME parameter after shutting down the database.
The statement waits, applying redo data until the LogMiner dictionary is found in the log files.
This may take several minutes, depending on how long it takes redo generated to be transmitted to the standby database, and how much redo data need to be applied. If a dictionary build is not successfully performed on the primary database, this command will never complete.
You can cancel the SQL statement by issuing the ALTER DATABASE RECOVER MANAGED standby database CANCEL statement from another SQL session.
2. Create a New Password File
In 11g you do not need to re-create the password file when converting your physical standby to a logical standby. If you do, it will not work.
Because the conversion process changes the database name (that was originally set with the DB_NAME initialization parameter) for the logical standby database, you must re-create the password file.
cd $ORACLE_HOME/dbs
rm orapwfguard
orapwd file=orapwfguard password=MySysPassword
3.Modifying LOG_ARCHIVE_DEST_n parameters
You need to perform this modification because, unlike physical standby databases, logical standby databases are open databases that generate redo data and have multiple log files (online redo log files, archived redo log files, and standby redo log files).
alter system set LOG_ARCHIVE_DEST_1='LOCATION=C:\oracle\product\10.2.0\oradata\FGUARD_ARCH VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES)' scope=both;
alter system set LOG_ARCHIVE_DEST_2='service=PROD_FGUARD LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)' scope=both;
alter system set LOG_ARCHIVE_DEST_3='LOCATION=C:\oracle\product\10.2.0\oradata\FGUARD_ARCH_stdby_redo_log VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE)' scope=both;
alter system set LOG_ARCHIVE_DEST_STATE_1='enable' scope=both;
alter system set LOG_ARCHIVE_DEST_STATE_2='enable' scope=both;
alter system set LOG_ARCHIVE_DEST_STATE_3='enable' scope=both;
Log_archive_dest_1 -> Archived redo log files that store redo data generated by the logical standby database. It will also work if the role of database is Primary or Standby
Log_archive_dest_2 -> This is for shipping the online redo logs to primary server, in case this becomes primary server and primary becomes standby server.
This is called auto role shifting. Till the time this database is standby it will not ship and redo logs to primary.
Log_archive_dest_3 -> Data received from the primary database (Arch Log Files generated on PROD will be shipped to this location).
This is for archiving all standby redo logs. This will work only when the role of database is Standby.
In case this becomes primary, it will not create any standby archive logs at this dest 3.
The following table show you various roles and redo log types that can be used in various situation.
| VALID_FOR Definition | Primary Role | Physical Standby Role | Logical Standby Role |
| ONLINE_LOGFILE, PRIMARY_ROLE | Active | Inactive | Invalid |
| ONLINE_LOGFILE,
STANDBY_ROLE |
Inactive | Invalid |
Active |
| ONLINE_LOGFILE, ALL_ROLES | Active | Invalid | Active |
| STANDBY_LOGFILE, PRIMARY_ROLE | Error |
Error |
Error |
| STANDBY_LOGFILE, STANDBY_ROLE | Invalid |
Active | Active |
| STANDBY_LOGFILE ALL_ROLES | Invalid | Active | Active |
| ALL_LOGFILES, PRIMARY_ROLE | Active |
Inactive | Invalid |
| ALL_LOGFILES, STANDBY_ROLE | Invalid | Active | Active |
| ALL_LOGFILES, ALL_ROLES | Active |
Active | Active |
The following table describes the archival processing defined by the initialization parameters shown in this example for the new STANDBY logical standby database.
| Parameter |
When the STANDBY Database Is Running in the Primary Role | When the STANDBY Database Is Running in the Logical Standby Role |
| LOG_ARCHIVE_DEST_1 | Directs archival of redo data generated by the primary database from the local online redo log files to the local archived redo log files in the Flash Recovery Area (USE_DB_RECOVERY_FILE_DEST). | Directs archival of redo data generated by the logical standby database from the local online redo log files to the local archived redo log files in the Flash Recovery Area (USE_DB_RECOVERY_FILE_DEST). |
| LOG_ARCHIVE_DEST_2 |
Directs transmission of redo data to the remote logical standby database FGUARD. | Is ignored; LOG_ARCHIVE_DEST_2 is valid only when turlock is running in the primary role. |
| LOG_ARCHIVE_DEST_3 | Is ignored; LOG_ARCHIVE_DEST_3 is valid only when FGUARD is running in the standby role. | Directs archival of redo
data received from the primary database to the local
archived redo log files in
C:\oracle\product\10.2.0\oradata\FGUARD_ARCH_stdby_redo_log |
NOTE
The DB_FILE_NAME_CONVERT initialization parameter is not honored once a physical standby database is converted to a logical standby database.
This can be a problem, for example, when adding a non-OMF datafile to the primary database and the datafile paths are different between the primary and standby.
If necessary, you should register a skip handler and provide SQL Apply with a replacement DDL string to execute by converting the path names of the primary database datafiles to the standby datafile path names. For more information check HERE.
4. Mounting the Stanby Database
On the logical standby database, shutdown the instance and issue the STARTUP MOUNT statement to start and mount the database.
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
5. Open the Stanby Database
The new database is logically the same as your primary database, but it is transactionally inconsistent with the primary database, and thus incompatible for recovery operations. To open the new logical standby database, you must open it with the RESETLOGS option by issuing the following statement:
ALTER DATABASE OPEN RESETLOGS;
Because this is the first time the database is being opened, the database's global name is adjusted automatically to match the new DB_NAME initialization parameter.
select * from global_name;
select name , PROTECTION_LEVEL,DATABASE_ROLE from v$database;
NAME PROTECTION_LEVEL DATABASE_ROLE
--------- -------------------- ----------------
TESTDB MAXIMUM PERFORMANCE LOGICAL STANDBY
select instance_name , STATUS , INSTANCE_ROLE from v$instance ;
INSTANCE_NAME STATUS INSTANCE_ROLE
---------------- ------------ ------------------
winoradb OPEN PRIMARY_INSTANCE
As you can see here, the INSTANCE Name is different that the DB Name. The instance Name is used by the OS to identify the logical standby.
6. Start Applying Process
Issue the following statement to begin applying redo data to the logical standby database:
ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
If we get:
ERROR at line 1:
ORA-16239: IMMEDIATE option not available without standby redo logs
We got the above error, because we didn't create standby redo log files
Create standby redo logs as shown below.
alter database add standby logfile group 4 ('/slot/ems6826/oracle/oradata/sitst02/redo4.dbf') size 100M;
alter database add standby logfile group 5 ('/slot/ems6826/oracle/oradata/sitst02/redo5.dbf') size 100M;
alter database add standby logfile group 6 ('/slot/ems6826/oracle/oradata/sitst02/redo6.dbf') size 100M;
ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
Logical Standby Sanity Check
Detect Database Role
select name, database_role from v$database;
Queries for PRIMARY Database
- Show Thread, Archived, Applied
select DEST_ID, THREAD#, SEQUENCE#, ARCHIVED, APPLIED, COMPLETION_TIME from v$archived_log where DEST_ID = 2 order by SEQUENCE#;
DEST_ID THREAD# SEQUENCE# ARC APP COMPLETION_TIME
--------- ---------- ---------- --- --- ------------------
2 1 1350 YES YES 17/JAN/11 21:35:13
2 1 1351 YES YES 17/JAN/11 22:03:06
2 1 1352 YES YES 17/JAN/11 22:17:30
2 1 1353 YES NO 17/JAN/11 22:37:10
2 1 1354 YES NO 17/JAN/11 22:37:11
2 1 1355 YES NO 17/JAN/11 22:44:21
2 1 1356 YES NO 18/JAN/11 07:45:09
On STANDBY database
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YY HH24:MI:SS';
- Verify the archived redo log files were registered.
SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME, DICT_BEGIN, DICT_END, TIMESTAMP FROM DBA_LOGSTDBY_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TIME NEXT_TIME DIC DIC TIMESTAMP
--------- ------------------ ------------------ --- --- ------------------
1353 17-JAN-11 22:17:14 17-JAN-11 22:36:33 NO NO 17-JAN-11 22:37:14
1354 17-JAN-11 22:36:33 17-JAN-11 22:37:06 NO NO 17-JAN-11 22:37:14
1355 17-JAN-11 22:37:06 17-JAN-11 22:44:19 NO NO 17-JAN-11 22:44:24
1356 17-JAN-11 22:44:19 18-JAN-11 07:44:23 NO NO 18-JAN-11 07:45:12
- Show Threads Progress, Determine how much progress was made through the available logs
SELECT L.SEQUENCE#, L.FIRST_TIME,
(CASE WHEN L.NEXT_CHANGE# < P.READ_SCN THEN 'YES'WHEN L.FIRST_CHANGE# < P.APPLIED_SCN THEN 'CURRENT' ELSE 'NO' END) APPLIED
FROM DBA_LOGSTDBY_LOG L, DBA_LOGSTDBY_PROGRESS P
ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TIME APPLIED
---------- ------------------ -------
1353 17-JAN-11 22:17:14 CURRENT
1354 17-JAN-11 22:36:33 NO
1355 17-JAN-11 22:37:06 NO
1356 17-JAN-11 22:44:19 NO
- Verify Currect SCN to see if all log file information was applied
SELECT APPLIED_SCN, NEWEST_SCN FROM DBA_LOGSTDBY_PROGRESS;
APPLIED_SCN NEWEST_SCN
----------- ----------
2998474027 2998474027
- Show File Location that I received and its status
SELECT FILE_NAME, SEQUENCE#, FIRST_CHANGE#, NEXT_CHANGE#, TIMESTAMP, DICT_BEGIN, DICT_END, THREAD# FROM
DBA_LOGSTDBY_LOG ORDER BY SEQUENCE#;
FILE_NAME
--------------------------------------------------------------------------------
SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# TIMESTAMP DIC DIC THREAD#
---------- ------------- ------------ ------------------ --- --- ----------
C:\ORACLE\PRODUCT\10.2.0\ORADATA\WINORADB_ARCH_STDBY_REDO_LOG\ARCH_001_01353_697041245.LOG
1353 2998447810 2998451537 17-JAN-11 22:37:14 NO NO 1
C:\ORACLE\PRODUCT\10.2.0\ORADATA\WINORADB_ARCH_STDBY_REDO_LOG\ARCH_001_01354_697041245.LOG
1354 2998451537 2998451552 17-JAN-11 22:37:14 NO NO 1
C:\ORACLE\PRODUCT\10.2.0\ORADATA\WINORADB_ARCH_STDBY_REDO_LOG\ARCH_001_01355_697041245.LOG
1355 2998451552 2998451783 17-JAN-11 22:44:24 NO NO 1
C:\ORACLE\PRODUCT\10.2.0\ORADATA\WINORADB_ARCH_STDBY_REDO_LOG\ARCH_001_01356_697041245.LOG
1356 2998451783 2998472682 18-JAN-11 07:45:12 NO NO 1
- Show what the Standby is doing, Inspect the process activity for SQL apply operations
column status format a50
column type format a12
select type, high_scn, status from v$logstdby;
SELECT substr(TYPE,1,10) Type, HIGH_SCN, substr(STATUS,1,55) FROM V$LOGSTDBY;
TYPE HIGH_SCN STATUS
------------ ---------- --------------------------------------------------
COORDINATOR 2998474028 ORA-16116: no work available
READER 2998474028 ORA-16240: Waiting for logfile (thread# 1, sequence# 1358)
BUILDER 2998474025 ORA-16116: no work available
PREPARER 2998474024 ORA-16116: no work available
ANALYZER 2998474025 ORA-16117: processing
APPLIER 2998474005 ORA-16116: no work available
APPLIER 2998474025 ORA-16116: no work available
APPLIER 2998473973 ORA-16116: no work available
APPLIER 2998473978 ORA-16116: no work available
- Show problematic Rows or Events
SELECT EVENT_TIME, STATUS, EVENT FROM DBA_LOGSTDBY_EVENTS ORDER BY EVENT_TIME, COMMIT_SCN;
- Check Coordinator Status
SELECT substr(NAME,1,20) Name, substr(VALUE,1,30) value
FROM V$LOGSTDBY_STATS
WHERE NAME LIKE 'coordinator%' or NAME LIKE 'transactions%';
NAME VALUE
-------------------- -------------------
coordinator state IDLE
transactions ready 1165
transactions applied 1165
coordinator uptime 285
Troubleshooting a Logical Standby
Setting up a Skip Handler for a DDL Statement
The DB_FILE_NAME_CONVERT initialization parameter is not honored once a physical standby database is converted to a logical standby database.
This can be a problem, for example, when adding a non-OMF datafile to the primary database and the datafile paths are different between the primary and standby. This section describes the steps necessary to register a skip handler and provide SQL Apply with a replacement DDL string to execute by converting the path names of the primary database datafiles to the standby datafile path names.
This may or may not be a problem for everyone. For example, if you are using Oracle Managed Files (OMF), SQL Apply will successfully execute DDL statements generated from the primary to CREATE and ALTER tablespaces and their associated system generated path name on the logical standby.
On PRIMARY Standby using Oracle Managed Files (OMF)
show parameter db_create_file_dest
NAME TYPE VALUE
-------------------------- ----------- ----------------
db_create_file_dest string /u02/oradata
create tablespace data2 datafile size 5m;
On LOGICAL Standby using Oracle Managed Files (OMF)
show parameter db_create_file_dest
NAME TYPE VALUE
-------------------------- ----------- ----------------
db_create_file_dest string /u02/oradata
----------------------- alert.log -----------------------
Wed Jan 12 18:45:28 EST 2011
Completed: create tablespace data2 datafile size 5m
---------------------------------------------------------
select tablespace_name, file_name
from dba_data_files
where tablespace_name = 'DATA2';
TABLESPACE_NAME FILE_NAME
------------------ ---------------------------------------------------------
DATA2 /u02/oradata/FGUARD/datafile/o1_mf_data2_6lwh8q9d_.dbf
On PRIMARY Standby using Oracle Managed Files (OMF)
alter tablespace data2 add datafile '/u05/oradata/MODESTO/data02.dbf' size 5m;
On LOGICAL Standby WITHOUT Oracle Managed Files (OMF)
If on the other hand, you attempt to specify a physical path name in the CREATE/ALTER tablespace statement that does not exist on the logical standby, SQL Apply will not succeed in processing the statement and will fail. Whenever SQL Apply encounters an error while applying a SQL statement, it will stop and provide the DBA with an opportunity to correct the statement and restart SQL Apply.
----------------------- alert.log -----------------------
Wed Jan 12 19:59:36 EST 2011
alter tablespace data2 add datafile '/u05/oradata/MODESTO/data02.dbf' size 5m
Wed Jan 12 19:59:36 EST 2011
ORA-1119 signalled during: alter tablespace data2 add datafile '/u05/oradata/MODESTO/data02.dbf' size 5m...
LOGSTDBY status: ORA-01119: error in creating database file '/u05/oradata/MODESTO/data02.dbf'
ORA-27040: file create error, unable to create file
Linux Error: 2: No such file or directory
LOGSTDBY Apply process P004 pid=31 OS id=28497 stopped
Wed Jan 12 19:59:36 EST 2011
Errors in file /u01/app/oracle/admin/turlock/bdump/turlock_lsp0_28465.trc:
ORA-12801: error signaled in parallel query server P004
ORA-01119: error in creating database file '/u05/oradata/MODESTO/data02.dbf'
ORA-27040: file create error, unable to create file
Linux Error: 2: No such file or directory
LOGSTDBY Analyzer process P003 pid=30 OS id=28495 stopped
LOGSTDBY Apply process P005 pid=32 OS id=28499 stopped
LOGSTDBY Apply process P006 pid=33 OS id=28501 stopped
LOGSTDBY Apply process P007 pid=34 OS id=28503 stopped
LOGSTDBY Apply process P008 pid=35 OS id=28505 stopped
---------------------------------------------------------
select event_timestamp, event, status from dba_logstdby_events;
EVENT_TIMESTAMP EVENT Status
----------------------------- ------------------------------ ------------------------------
12-JAN-11 07.59.36.134349 PM alter tablespace data2 add dat ORA-01119: error in creating d
afile '/u05/oradata/MODESTO/da atabase file '/u05/oradata/MOD
ta02.dbf' size 5m ESTO/data02.dbf'
ORA-27040: file create error,
unable to create file
Linux Error: 2: No such file o
r directory
Fixing it
1. Disable the database guard for this session so we can modify the logical standby.
alter session disable guard;
2. Issue a compensating transaction or statement on the logical standby. For this example, issue the following command with the correct path:
alter tablespace data2 add datafile '/u05/oradata/FGUARD/data02.dbf' size 5m;
3. Re-enable the database guard for this session.
alter session enable guard;
4. Restart logical apply with a clause that will cause the failed transaction to be automatically skipped.
alter database start logical standby apply immediate skip failed transaction;
5. Verify results.
select tablespace_name, file_name
from dba_data_files
where tablespace_name = 'DATA2';
TABLESPACE_NAME FILE_NAME
------------------ ---------------------------------------------------------
DATA2 /u02/oradata/FGUARD/datafile/o1_mf_data2_6lwh8q9d_.dbf
DATA2 /u05/oradata/FGUARD/data02.dbf
NOTE
It is possible to avoid errors of this nature on the logical standby database by registering a skip handler and provide SQL Apply with a replacement DDL string to execute by converting the path names of the primary database datafiles to the standby datafile path names. The steps to perform this are presented below. The actions below should be run on the logical standby database.
1. First, create the PL/SQL 'skip procedure' to handle tablespace DDL transactions.
create or replace procedure sys.handle_tbs_ddl (
old_stmt in varchar2
, stmt_typ in varchar2
, schema in varchar2
, name in varchar2
, xidusn in number
, xidslt in number
, xidsqn in number
, action out number
, new_stmt out varchar2
) as
begin
-- --------------------------------------------------------
-- All primary file specification that contain a directory
-- '/u05/oradata/FGUARD' should be changed to the
-- '/u01/another_location/oradata/FGUARD' directory specification.
-- --------------------------------------------------------
new_stmt := replace(old_stmt, '/u05/oradata/FGUARD', '/u01/another_location/oradata/FGUARD');
action := dbms_logstdby.skip_action_replace;
exception
when others then
action := dbms_logstdby.skip_action_error;
new_stmt := null;
end handle_tbs_ddl;
/
2. Stop SQL Apply.
alter database stop logical standby apply;
3.Register the skip procedure with SQL Apply.
execute dbms_logstdby.skip(stmt => 'TABLESPACE', proc_name => 'sys.handle_tbs_ddl');
4.Start SQL Apply.
alter database start logical standby apply immediate;
5. Perform a test.
On PRIMARY Standby
alter tablespace data2 add datafile '/u05/oradata/FGUARD/data03.dbf' size 5m;
ON Logical Standby
----------------------- alert.log -----------------------
Wed Jan 12 20:51:58 EST 2011
LOGSTDBY status: ORA-16110: user procedure processing of logical standby apply DDL
LOGSTDBY status: ORA-16202: Skip procedure requested to replace statement
Wed Jan 12 20:51:58 EST 2011
alter tablespace data2 add datafile '/u01/another_location/oradata/FGUARD/data03.dbf' size 5m
Completed: alter tablespace data2 add datafile '/u01/another_location/oradata/FGUARD/data03.dbf' size 5m
---------------------------------------------------------
select tablespace_name, file_name
from dba_data_files
where tablespace_name = 'DATA2';
TABLESPACE_NAME FILE_NAME
------------------ ---------------------------------------------------------
DATA2 /u01/another_location/oradata/FGUARD/data03.dbf
Logical Standby Database Activation (Role Transition)
A database can operate in one of two mutually exclusive modes in an Oracle Data Guard configuration: primary or standby.
Whenever the role is changed between the primary and standby, this is referred to as a role transition.
Role transition plays an important part in Data Guard by providing an interface that allows DBA's to activate a standby database to take over as the primary production database or vice versa.
There are two types of role transitions supported in Oracle 10g Data Guard: switchover and failover.
Switchover
1- Issue the following statement on the production database to enable receipt of redo from the current standby database:
ALTER DATABASE PREPARE TO SWITCHOVER TO LOGICAL STANDBY;
2- On the current logical standby database, build the LogMiner dictionary and transmit this dictionary to the current production database::
ALTER DATABASE PREPARE TO SWITCHOVER TO PRIMARY;
Depending on the work to be done and the size of the database, the prepare statement may take some time to complete.
3- Verify the LogMiner Multiversioned Data Dictionary was received by the production database by querying the SWITCHOVER_STATUS column of the V$DATABASE fixed view on the production database
Initially, the SWITCHOVER_STATUS column shows PREPARING DICTIONARY while the LogMiner Multiversioned Data Dictionary is being recorded in the redo stream. Once this has completed successfully, the column shows PREPARING SWITCHOVER. When the query returns the TO LOGICAL STANDBY value, you can proceed to the next step.
4- When the SWITCHOVER_STATUS column of the V$DATABASE view returns TO LOGICAL STANDBY, convert the production database to a standby by issuing:
ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL STANDBY with SESSION SHUTDOWN;
5- Issue the following statement on the old standby database:
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
Failover
Copy and register any missing archived redo logs from PROD. Execute the following query on Standby:
COLUMN FILE_NAME FORMAT a55;
SELECT THREAD#, SEQUENCE#, FILE_NAME
FROM DBA_LOGSTDBY_LOG L
WHERE NEXT_CHANGE# NOT IN (SELECT FIRST_CHANGE#
FROM DBA_LOGSTDBY_LOG
WHERE L.THREAD# = THREAD#)
ORDER BY THREAD#,SEQUENCE#;
Register any log:
ALTER DATABASE REGISTER LOGICAL LOGFILE '/disk1/oracle/dbs/log-1292880008_7.arc';
Copy and register the online redo logs from the primary database (if possible).
You can safely ignore the error ORA-01289: cannot add duplicate logfile
ALTER DATABASE REGISTER LOGICAL LOGFILE '/disk1/oracle/dbs/online_log1.log';
Turn off the apply delay interval.
ALTER DATABASE STOP LOGICAL STANDBY APPLY;
EXECUTE DBMS_LOGSTDBY.APPLY_UNSET('APPLY_DELAY');
ALTER DATABASE START LOGICAL STANDBY APPLY;
Initiate the failover by issuing the following on the target standby database:
ALTER DATABASE ACTIVATE LOGICAL STANDBY DATABASE FINISH APPLY;
This statement stops the RFS process, applies any remaining redo data, stops SQL Apply, and activates the logical standby database in the production role.
Note: To avoid waiting for the redo in the standby redo log file to be applied prior to performing the failover, omit the FINISH APPLY clause on the statement. Although omitting the FINISH APPLY clause will accelerate failover, omitting the clause will cause the loss of any unapplied redo data in the standby redo log. To gauge the amount of redo that will be lost, query the V$LOGSTDBY_PROGRESS view.
The LATEST_SCN column value indicates the last SCN received from the production database, and the APPLIED_SCN column value indicates the last SCN applied to the standby database. All SCNs between these two values will be lost.
select * from
v$archive_gap;
If you get results from this query, it means
there is a gap. You can easily detect what is the problem by
checking the alert.log file on your Primary DB.
Potential data loss window for a physical
standby database (Archived logs not
applied on a physical standby database)
--On the standby, Get the sequence number
of the last applied archive log.
select
max(sequence#) Last_applied_arch_log from v$archived_log where applied='YES';
-- On the standby, Get the sequence
number of the
-- This is the last log the standby can apply without
receiving additional archive logs from the primary.
SELECT
min(sequence#) Last_archive_log_received FROM v$archived_log
WHERE
(sequence#+1) NOT IN (SELECT sequence# FROM v$archived_log)
AND sequence# > &Last_applied_arch_log;
--Connect to the primary database and
obtain the sequence number of the current online
log:
select sequence# from
v$log where status='CURRENT';
-- The difference between
2nd query and 1st query should be 0
-- The difference between 3d
query and 1st query is the
number of archive logs that the standby
-- database would not be able to recover if the
primary host become unavailable
Some GOOD queries to detect GAPS
select
process,status,client_process,sequence#,block#,active_agents,known_agents
from v$managed_standby;
PROCESS STATUS
CLIENT_P SEQUENCE# BLOCK#
ACTIVE_AGENTS KNOWN_AGENTS
------- ------------ -------- ---------- ----------
------------- ------------
ARCH CONNECTED
ARCH
0
0
0
0
ARCH CONNECTED
ARCH
0
0
0
0
MRP0 WAIT_FOR_GAP
N/A
5134
0
0
0
RFS RECEIVING
ARCH
5454
1637652
0
0
RFS
ATTACHED
ARCH
5456
819100
0
0
Run this at Primary
set pages 1000
set lines 120
column DEST_NAME format
a20
column DESTINATION format
a35
column ARCHIVER format a10
column TARGET format a15
column status format a10
column error format a15
select
DEST_ID,DEST_NAME,DESTINATION,TARGET,STATUS,ERROR from
v$archive_dest
where DESTINATION is NOT
NULL
/
select
ads.dest_id,max(sequence#) "Current Sequence",
max(log_sequence) "Last
Archived"
from v$archived_log al,
v$archive_dest ad, v$archive_dest_status ads
where
ad.dest_id=al.dest_id and al.dest_id=ads.dest_id group by
ads.dest_id;
Run this at Standby
select max(al.sequence#)
"Last Seq Recieved" from v$archived_log al
/
select max(al.sequence#)
"Last Seq Apllied" from v$archived_log al
where applied ='YES'
/
select
process,status,sequence# from v$managed_standby
/
select * from v$archive_gap;
THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
1
5134
5222
Another
Method
Use the following SQL on the standby database (the
database must be mounted).
SELECT high.thread#, "LowGap#", "HighGap#"
FROM
( SELECT thread#, MIN(sequence#)-1
"HighGap#"
FROM
(
SELECT a.thread#, a.sequence#
FROM
(
SELECT *
FROM v$archived_log
) a,
(
SELECT thread#, MAX(sequence#)gap1
FROM v$log_history
GROUP BY thread#
) b
WHERE
a.thread# = b.thread#
AND a.sequence# > gap1
)
GROUP BY thread#
) high,
( SELECT thread#, MIN(sequence#)
"LowGap#"
FROM
(
SELECT thread#, sequence#
FROM v$log_history, v$datafile
WHERE
checkpoint_change# <= next_change#
AND checkpoint_change# >= first_change#
)
GROUP BY thread#
) low
WHERE low.thread# = high.thread#;
If no rows are retunred, you are Fine
Suspend Physical Standby Recovery
To stop managed standby recovery:The database can subsequently be switched back to recovery mode as follows:-- Cancel protected mode on primary
CONNECT sys/password@primary1 AS SYSDBA
ALTER DATABASE SET STANDBY DATABASE UNPROTECTED;
-- Cancel recovery if necessary
CONNECT sys/password@standby1 AS SYSDBA
RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE OPEN READ ONLY;
-- Startup managed recovery
CONNECT sys/password@standby1 AS SYSDBA
SHUTDOWN IMMEDIATE
STARTUP NOMOUNT;
ALTER DATABASE MOUNT STANDBY DATABASE;
RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
-- Protect primary database
CONNECT sys/password@primary1 AS SYSDBA
ALTER DATABASE SET STANDBY DATABASE PROTECTED;
Activating A Physical Standby Database
Procedures to Open the DR Database in Query ModeOnce there, cancel recovery with (you could get an error message that you can ignore):
RECOVER MANAGED STANDBY DATABASE CANCEL;
Open the DB in Read Mode with:
ALTER DATABASE OPEN READ ONLY;
alter tablespace TEMP add tempfile '/oracle/DBA_SCRIPTS/temp.dbf' size 1000m reuse;
Procedures to Put the Database back in DR Mode
Close the DB:
SHUTDOWN IMMEDIATE;
Mount the DB with:
startup nomount;
alter database mount standby database;
Open the DB in Recovery mode with:
recover standby database;
alter database recover managed standby database disconnect from session;
Procedures to Activate a DR Database as a PROD database
If the primary database is not available the standby database can be activated as a primary database using the following statements:
Since the standby database is now the primary database it should be backed up immediately. The previous primary database can then be configured as a standby.--Startup the DB in Mount Mode (if that is not already on place)
STARTUP NOMOUNT
ALTER DATABASE MOUNT STANDBY DATABASE;
--Try to receive and apply the latest Arch Logs from PROD
RECOVER MANAGED STANDBY DATABASE;
or
RECOVER STANDBY DATABASE;
-- Cancel recovery if necessary
RECOVER MANAGED STANDBY DATABASE CANCEL;
or
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
shutdown immediate;
startup nomount;
alter database mount standby database;
--You may need to rename log files like:
ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/CCOM/redo1.log' to '/opt/app/oracle/oradata/CCOM/redo01.log';
ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/CCOM/redo2.log' to '/opt/app/oracle/oradata/CCOM/redo02.log';
ALTER DATABASE ACTIVATE STANDBY DATABASE;
SHUTDOWN IMMEDIATE
STARTUP
Create or Add a tempfile to the temporary tablespace: Ex:
create temporary tablespace TEMP2 TEMPFILE '/oracle/oradata/V901/temp2.dbf' size 100M;
or
alter tablespace TEMP add tempfile ‘/oracle/app/product/9.2/admin/oradata/temp01.dbf’ size 100M ;
Physical standby
Switchover Steps
NOTE = Standby must be mounted before
starting the switchover!!!!
QUICK
GUIDE
The Current PROD Site,
that will become Standby
select database_role, switchover_status from
v$database; -- Here we would like to see
"PRIMARY TO STANDBY"
alter database commit to switchover to physical standby with
session shutdown;
shutdown immediate
startup nomount
alter database mount standby database;
alter system set log_archive_dest_state_2=defer;
ALTER SYSTEM SET fal_client='THIS_STANDBY'
SCOPE=BOTH --This should be DR DB
(Denver)
ALTER SYSTEM SET fal_server='PROD' SCOPE=BOTH;
--This
should
be PROD (Falcon)
**** At this point we have 2 standby Databases ******
The Current STDBY
Site, that will become primary
select database_role, switchover_status from
v$database; -- Here we would like to see
"PHYSICAL STANDBY TO PRIMARY"
alter database commit to switchover to primary;
shutdown immediate
startup
alter system set fal_client=NULL scope=both;
alter system set fal_server=NULL scope=both;
alter system set log_archive_dest_2='SERVICE=STANDBY reopen=60'
scope=both;
alter system set log_archive_dest_state_2=enable scope=both;
OLD PRIMARY SITE
recover managed standby database disconnect
In this FULL Example, the
standby
database (STDBY)
becomes the new primary, and the primary (PROD) becomes
the new standby database.
· End all activities on the primary and standby database
· Check primary database switchover status
------------------------- -----------------------------------
PRIMARY TO STANDBY
The SWITCHOVER_STATUS column of v$database can have the following values:
NOT ALLOWED - Either this is a standby database and the primary database has not been switched first, or this is a primary database and there are no standby databases.
SESSIONS ACTIVE - Indicates that there are active SQL sessions attached to the primary or standby database that need to be disconnected before the switchover operation is permitted.
SWITCHOVER PENDING - This is a standby database and the primary database switchover request has been received but not processed.
SWITCHOVER LATENT - The switchover was in pending mode, but did not complete and went back to the primary database.
TO PRIMARY - This is a standby database, with no active sessions, that is allowed to switch over to a primary database.
TO STANDBY - This is a primary database, with no active sessions, that is allowed to switch over to a standby database.
RECOVERY NEEDED - This is a standby database that has not received the switchover request.
During normal operations it is acceptable to see the following values for SWITCHOVER_STATUS on the primary to be SESSIONS ACTIVE or TO STANDBY.
During normal operations on the standby it is acceptable to see the values of NOT ALLOWED or SESSIONS ACTIVE.
If SWITCHOVER_STATUS returns SESSIONS ACTIVE then you should either disconnect all sessions manually or you can use the following statement to close those sessions:
alter database commit to switchover to standby with session shutdown;
SQL> alter database commit to switchover to physical standby;
or if you have current sessions connected, you can perform:
SQL> alter database commit to switchover to physical standby with session shutdown;
Step 2: Shutdown the primary database and bring IT up as the new standby database
· Shutdown the primary database normally
- fal_server = “STDBY”
or
ALTER SYSTEM SET fal_client='PROD' SCOPE=BOTH; #This DB used on tnsnames.ora
ALTER SYSTEM SET fal_server='STDBY' SCOPE=BOTH; #The new PROD DB used on tnsnames.ora
ALTER SYSTEM SET log_archive_dest_2='' SCOPE=BOTH;
SQL> alter database mount standby database;
Step 3: Switchover preparation for the former standby database
· At this time, we have 2 standby Databases, now we will prepare the original standby and convert it to primary. Check standby database switchover status, if we see the "SESSIONS ACTIVE", we need to act as we did it before.
DATABASE_ROLE SWITCHOVER_STATUS
------------------------- -----------------------------------
PHYSICAL STANDBY TO PRIMARY
or
SQL> alter database commit to switchover to primary with session shutdown;
Step 4: Shutdown the standby database and bring IT up as the new primary database
· Shutdown the standby database
SQL> startup;
- fal_server = “PROD”
- Add parameters log_archive_dest_2 and log_archive_dest_state_2
alter system set log_archive_dest_2='SERVICE=PROD reopen=60' scope=both;
alter system set log_archive_dest_state_2=enable scope=both;
ALTER SYSTEM SET fal_client='STBY' SCOPE=BOTH; #This box when is in Standby
ALTER SYSTEM SET fal_server='PROD' SCOPE=BOTH; #The "original" PROD box
Step 5: Add Temp Tablespace
· Issue the following command to add TEMP tablespace
Step 6: Put the new standby (OLD PROD database) in managed recovery mode
· Issue the following command on the new standby database.
Step 7: SWITCH THE LOG FILES A COUPLE OF TIMES on the new primary db
· Issue the following commands:
SQL> alter system switch logfile;
SQL> alter system switch logfile;
Step 8: Change TNS Entry for the New Primary Database
· Change the TNS entry on all application hosts to point to the new primary
(description =
(address = (protocol = tcp) (host = server_02) (port = 1522)
(connect_data = (sid = stdby))
)
Here is a nice Video about the steps:
The old primary (prod_01) has to be discarded and can not be used as the new standby database. You need to create a new standby database by backing up the new primary and restore it on host server_01. The time to create a new standby database exposes the risk of having no standby database for protection.
After failover operation, you need to modify TNS entry for ‘prod’ to point to the new instance and host name.
Steps
1- Initiate the failover by issuing the following on the target standby database:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH FORCE;
Note: Include the FORCE keyword to ensure that the RFS processes on the standby database will fail over without waiting for the network connections to time out through normal TCP timeout processing before shutting down.
2- Convert the physical standby database to the production role:
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
3- If the standby database was never opened read-only since the last time it was started, then open the new production database by issuing the following statement:
ALTER DATABASE OPEN;
If the physical standby database has been opened in read-only mode since the last time it was started, shut down the target standby database and restart it:
SHUTDOWN IMMEDIATE;
STARTUP;
Note: In rare circumstances, administrators may wish to avoid waiting for the standby database to complete applying redo in the current standby redo log file before performing the failover. (note: use of Data Guard real-time apply will avoid this delay by keeping apply up to date on the standby database).
If so desired, administrators may issue the ALTER DATABASE ACTIVATE STANDBY DATABASE statement to perform an immediate failover. This statement converts the standby database to the production database, creates a new resetlogs branch, and opens the database.
Create or Add a tempfile to the temporary tablespace: Ex:
create temporary tablespace TEMP2 TEMPFILE '/oracle/oradata/V901/temp2.dbf' size 100M;
or
alter tablespace TEMP add tempfile ‘/oracle/app/product/9.2/admin/oradata/temp01.dbf’ size 100M ;
Tip #1: Primary Online Redo Logs
The number of redo groups and the size of redo logs are two key factors in configuring online redo logs. In general, you try to create the fewest groups possible without hampering the log writer process’s ability to write redo log information. In a Data Guard environment, LGWR process may take longer to write to the remote standby sites, you may need to add additional groups to guarantee that a recycled group is always available to the log writer process. Otherwise, you may receive incomplete logs on the standby sites. The size of redo log is determined by the amount of transaction needed to be applied to a standby database during database failover operation. A small size of redo will minimize the standby database lag time; however, it may cause more frequent log switches and require more redo groups for log switches to occur smoothly. On the other hand, large size redo logs may require few groups and less log switches, but it may increase standby database lag time and potential for more data loss. The best way to determine if the current configuration is satisfactory is to examine the contents of the log writer process’s trace file and the database’s alert log.
For example, the following message from the alert log may indicate a need for more log groups.
ORA-00394: online log reused while attempting to archive it
Tip #2: Standby Online Redo Logs vs. Standby Archived Redo logs
Online redo logs transferred from the primary database are stored as either standby redo logs or archived redo logs. Which redo log reception option should we choose? Here is the comparison chart:
| |
Standby Online Redo Logs | Standby Archived Redo Logs |
| Advantages | - Pre-allocated
files - Can place on raw devices - Can be duplexed for more protection - Improve redo data availability - No Data Loss capable |
-
No extra ARCH process - Reduce lag time |
Tip #3: Enforce Logging
It is recommended that you set the FORCE LOGGING clause to force redo log to be generated for individual database objects set to NOLOGGING. This is required for a no data loss strategy.
Here is the SQL command to set FORCE LOGGING:
SQL> select force_logging from v$database;
FORCE_LOGGING
--------------
NO
SQL> alter database force logging;
Tip #4: RMAN Backup
A failover operation reset logs for the new primary. If you use RMAN to backup your database, you need to create a new incarnation of the target database. Otherwise, your RMAN backup will fail.
RMAN> reset database;
Tip #5: Disable Log Transport Services When Standby Database is down
When a standby database or host is down for maintenance, it is advisable to temporarily disable the log transport services for that site. Especially during a heavily transaction period, one behavior observed in Oracle is that when one of the standby database is down for maintenance, it can temporarily freeze the primary database even the data protection mode is set to rapid mode. To avoid such problem, you can issue this command on the primary database before bring down the standby database:
SQL> alter system set log_archive_dest_state_2 = defer;
When the standby database is up again, issue:
SQL> alter system set log_archive_dest_state_2 = enable;
Tip #6: Standby Database Upgrade
Steps to upgrade standby database to newer database version:
Step 1: Shutdown both primary and standby databases
Step 2: Install Oracle software on both primary and standby hosts
Step 3: Upgrade the primary database
Step 4: Rebuild standby database from the upgraded primary
Tip #7: Data Guard Broker
Starting on Oracle9i R2 broker has made great improvements. The new configuration now support up to nine standby sites (including logical standby database). Both Data Guard Manager and CLI support switchover and failover operations.
Tip #8: Using ‘Delay’ Option to Protect Logical/Physical Corruptions
You may utilize the delay option (if you have multiple standby sites) to prevent physical/logical corruption of your primary. For instance, your standby #1 may not have ‘Delay’ on to be your disaster recovery standby database. However, you may opt to implement a delay of minutes or hours on your standby #2 to allow recover from a possible physical or logical corruption on your primary database.
SQL> alter database recover managed standby database delay 5 disconnect;
Tip #9: Always monitor log apply services and Check Alert. log file for errors.
If you are not using Data Guard broker, here is a script to help you to monitor your standby database recover process:
$ cat ckalertlog.sh
####################################################################
## ckalertlog.sh ##
####################################################################
#!/bin/ksh
export EDITOR=vi
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0
export ORACLE_HOME LD_LIBRARY_PATH=$ORACLE_HOME/lib
export TNS_ADMIN=/var/opt/oracle
export ORATAB=/var/opt/oracle/oratab
PATH=$PATH:$ORACLE_HOME:$ORACLE_HOME/bin:/usr/ccs/bin:/bin:/usr/bin:/usr/sbin:/
sbin:/usr/openwin/bin:/opt/bin:.; export PATH DBALIST="primary.dba@company.com,another.dba@company.com";export
for SID in `cat $ORACLE_HOME/sidlist`
do
cd $ORACLE_BASE/admin/$SID/bdump
if [ -f alert_${SID}.log ]
then
mv alert_${SID}.log alert_work.log
touch alert_${SID}.log
cat alert_work.log >> alert_${SID}.hist
grep ORA- alert_work.log > alert.err
fi
if [ `cat alert.err|wc -l` -gt 0 ]
then
mailx -s "${SID} ORACLE ALERT ERRORS" $DBALIST < alert.err
fi
rm -f alert.err
rm -f alert_work.log
done
Place the script in a crontab:
#########################################################
# Check Standby log file
#########################################################
9,19,29,39,49,59 7-17 * * 1-5 /dba/scripts/ckalertlog.sh
Applying Pacthes with Standby
For Physical Standby use note ML Note 187242.1
For Logical Standby use note ML Note 210989.1
Procedure to Apply a Patch Set with Physical Standby Database in Place (ML Note 187242.1)
NOTE: If you are using the Data Guard Broker, you should either disable the Data Guard Broker Configuration
DGMGRL> disable configuration;
or stop the Data Guard Broker (set db_broker_start=false) during the Upgrade.
You can enable the Data Guard Broker Configuration
DGMGRL> enable configuration;
or restart the Data Guard Broker (set db_broker_start=true) again once the Upgrade completed successfully.
1. Log in to the oracle account on both the PRIMARY and STANDBY hosts and make sure the environment is set to the correct ORACLE_HOME and ORACLE_SID.
2. On both the PRIMARY and STANDBY host uncompress and untar the downloaded patch set / interim patch file into a new directory.
3. Shut down the existing Oracle Server instance on the PRIMARY host with immediate priority.
Stop all listeners, agents and other processes running against the ORACLE_HOME. If using Real Application Clusters perform this step on all nodes.
shutdown immediate
% agentctl stop
% lsnrctl stop
4. Cancel managed recovery on the STANDBY database.
recover managed standby database cancel;
5. Shutdown the STANDBY instance on the STANDBY host. Stop all listeners, agents and other processes running against the ORACLE_HOME.
If using Real Application Clusters perform this step on all nodes.
shutdown immediate
% agentctl stop
% lsnrctl stop
6. Run the Installer and install the patchset on both PRIMARY and STANDBY host.
% ./runInstaller
If this is an interim patch, run opatch per the patch README.
If using Real Application Clusters, be sure the install has propagated to the other nodes if using private ORACLE_HOMEs.
Please see the Patch readme for specific instructions.
7. Once the patchset/patch has been installed on on all hosts/nodes startup the STANDBY listener on STANDBY host.
% lsnrctl start
8. Startup nomount the STANDBY database.
% sqlplus "/ as sysdba"
startup nomount
9. Mount the STANDBY database.
alter database mount standby database;
10. Place the STANDBY database in managed recovery mode.
recover managed standby database nodelay disconnect;
11. Startup the PRIMARY instance on the primary host.
% sqlplus "/ as sysdba"
startup migrate
12. Ensure that remote archiving to the STANDBY database is functioning correctly by switching logfiles on the PRIMARY and verifying that v$archive_dest.status is valid.
If you are not performing remote archiving make note of the current archive log sequence.
alter system archive log current;
select dest_id, status from v$archive_dest;
13. On the PRIMARY instance run the following script:
@?/rdbms/admin/catpatch.sql
For the interim patch, run any scripts as outlined in the README.
14. Once the catpatch.sql script / patch SQL scripts completes make note of the current log sequence and issue the following command:
alter system archive log current;
15. Verify the STANDBY database has been recovered to the log sequence from step 12.
select max(sequence#) from v$log_history;
16. On the PRIMARY instance run the following command:
alter system disable restricted session;
17. Complete the remainder of the "Post Install Actions" from the Patch Set readme on the primary host.
Please note that it is not necessary to shudown the STANDBY in conjuction with the PRIMARY during the "Post Install Actions".
18. Once all "Post Install Actions" have been completed verify the STANDBY database has been recovered to the last archive log produced by the PRIMARY . On the PRIMARY :
select max(sequence#) from v$archived_log;
On the STANDBY :
select max(sequence#) from v$log_history;
Resolving Problems
After adding a datafile to primary database, recovery of the standby database fails with the following error messages:
ORA-01157: cannot identify/lock data file 16 - see DBWR trace file
ORA-01110: data file 16: '/oracle/oradata/FGUARD/undotbs02.dbf'
ORA-27037: unable to obtain file status
Problem Explanation:
The datafiles do not exist on the standby database.
Solution Description:
Create the datafile(s) on the standby database. When the files exist, recovery can continue.
The datafiles are not automatically created on the standby site. For example, the redo does not create a new datafile for you.
Then create datafile command from startup mount is:
alter database create datafile '/home/orahome/data/721/users02.dbf';
Pausing/Starting from PROD
alter system set log_archive_dest_state_2='defer';
alter system set log_archive_dest_state_2='enable';
Getting 'Restarting dead background process QMN0' on Alert Log File
If you get many of this messages, just perform the following:
alter system set aq_tm_processes=0 scope=both;
Gap Detected
If there is a gap on the arch log files, then you need to perform the following:
1- Copy the arch logs that doesn't exist on the DR box
2- Apply them by using the following command:
SQL> alter database recover managed standby database disconnect from session;
If you see errors on the Alert.log file like:
Fetching gap sequence for thread 1, gap sequence 5007-5060
Trying FAL server: PROD_FGUARD
Wed May 31 10:19:41 2006
Failed to request gap sequence. Thread #: 1, gap sequence: 5007-5060
All FAL server has been attempted.
Wed May 31 10:21:28 2006
Restarting dead background process QMN0
Then try with :
RECOVER AUTOMATIC STANDBY DATABASE;
If you get :
ORA-01153: an incompatible media recovery is active
Then stop/restart DR and try the last command again:
startup nomount;
alter database mount standby database;
RECOVER AUTOMATIC STANDBY DATABASE;
After recovery is done, then:
alter database recover managed standby database disconnect from session;
Recovering After a Network Failure
The primary database may eventually stall if the network problem is not fixed in a timely manner, because the primary database will not be able to switch to an online redo log that has not been archived. You can issue the following SQL query to determine whether the primary database stalled because it was not able to switch to an online redo log:
SELECT decode(COUNT(*),0,'NO','YES') "switch_possible"
FROM V$LOG
WHERE ARCHIVED='YES';
If the output from the query displays "Yes," a log switch is possible; if the output displays "No," a log switch is not possible.
The V$ARCHIVE_DEST view contains the network error and identifies which standby database cannot be reached. On the primary database, issue the following SQL statement for the archived log destination that experienced the network failure. For example:
SELECT DEST_ID, STATUS, ERROR FROM V$ARCHIVE_DEST WHERE DEST_ID = 2;
DEST_ID STATUS ERROR
---------- --------- ----------------------------
2 ERROR ORA-12224: TNS:no listener
The query results show there are errors archiving to the standby database, and the cause of the error as TNS:no listener. You should check whether the listener on the standby site is started. If the listener is stopped, then start it
If you cannot solve the network problem quickly, and if the physical standby database is specified as a mandatory destination, try to prevent the database from stalling by doing one of the following:
# Disable the mandatory archive destination:
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2 = DEFER;
When the network problem is resolved, you can enable the archive destination again:
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2 = ENABLE;
# Change the archive destination from mandatory to optional:
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2 = 'SERVICE=standby1 OPTIONAL REOPEN=60';
When the network problem is resolved, you can change the archive destination from optional back to mandatory:
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2 = 'SERVICE=standby1 MANDATORY REOPEN=60';
DataGuard Scenarios
Synchronyze a GAP on the STANDBY when the required archived logs are lost
Scenario:
All archived logs were removed from primary database. The standby had lagged far behind the primary, many required archived logs to close the gap were removed and no backup of them was available.
In order to close the gap you need to create an incremental backup that will contain all transactions since the last scn recorded on the standby database.
Implementation Steps
1- Cancel Recovery on Standby
SQL> alter database recover managed standby database cancel;
If you try to recover from the standby you will get:
SQL> recover standby database;
ORA-00279: change 4146871739 generated at 12/31/2008 11:39:03 needed for thread 1
ORA-00289: suggestion : Z:\ORACLE\ORADATA\SATI\ARCHIVE\1_205_674755717.ARC
ORA-00280: change 4146871739 for thread 1 is in sequence #205
2-Check Standby Database current_scn
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
4146871738
3- Create a Primary Database Incremental Backup FROM this SCN and a Control File for Standby
rman target sys/pass@PROD
backup incremental from scn 4146871738 database FORMAT 'Z:\BACKUP\FOR_STANDBY_%U' tag 'FORSTANDBY';
backup current controlfile for standby format 'Z:\BACKUP\FORSTDBYCTRL.bck';
4- Transfer The Incremental Backup Sets to the Standby Server
5-Restore controlfile on the Standby
rman target sys/pass@STANDBY
RESTORE STANDBY CONTROLFILE FROM 'Z:\BACKUP\FORSTDBYCTRL.BCK';
6-Catalog the Incremental Backups on The Standby Server
Note that for the catalog command to succeed you will need to move the backups to be within the Flash Recovery Area.
When you execute the catalog command, RMAN will ask you if you want to catalog the new files, you will need to say YES.
catalog start with 'Z:\FRA\SATISTD\BACKUPSET';
searching for all files that match the pattern Z:\FRA\SATISTD\BACKUPSET
List of Files Unknown to the Database
=====================================
File Name: Z:\FRA\SATISTD\BACKUPSET\FOR_STANDBY_A7K471DJ_1_1
File Name: Z:\FRA\SATISTD\BACKUPSET\FOR_STANDBY_A8K471DK_1_1
File Name: Z:\FRA\SATISTD\BACKUPSET\FOR_STANDBY_A9K471EF_1_1
File Name: Z:\FRA\SATISTD\BACKUPSET\FOR_STANDBY_AAK471GL_1_1
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
7-Recover the Database and Cleanup Redologs
RMAN> recover database noredo;
…
…
SQL> alter database flashback off;
Database altered.
SQL> alter database flashback on;
Database altered.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
If more archived logs were created on the primary since the finish of the SCN based incremental backup then you can copy tehm over and recover the standby database using the command : “recover standby database"
8- Enable the broker at both sites and check
When enabling the broker again it will take over the responsibility of managing the site and will resynchronize both sites
SQL> alter system set dg_broker_start=true scope=both;
Improvements in Oracle Data Guard in Oracle 10gr2
Automatic Deletion of applied archive logs: Once primary database Archived logs are applied to a Logical Standby Database, they are deleted automatically without DBA intervention. This makes it easier to maintain both primary and logical standby databases. Physical standby databases have had this functionality since Oracle 10g Release 1, by using Flash Recovery Area option.
- No downtime required to generate Logical Standby: The primary database is no longer required to shutdown or be put in QUIESCING state, as we can create the logical standby database from a hotbackup of the primary database just like the physical standby database.
- Online upgrades: A lot of DBAs have dreamed about this for long time, the DBA no longer required to shutdown the primary database to upgrade from Oracle 10g release 2 with Data Guard option. First, upgrade the logical standby database to the next release, test and validate the upgrade, do a role reversal by switching over to the upgraded database, and then finally upgrade the old primary database.
- New Datatypes Supported. I always used to hesitate whenever I thought of logical standby databases, as some of my databases never meet the pre-requisite conditions. In 10g relase2, Oracle supports most of the datatypes, such as NCLOB, LONG, LONGRAW,BINARY_FLOAT,BINARY_DOUBLE,IOTs.
- Fast-Start
Failover. This capability allows Data Guard to
automatically, and quickly fail over to a previously chosen,
synchronized standby database in the event of loss of the
primary database, without requiring any manual steps to invoke
the failover, and without incurring any data loss. Following a
fast-start failover, once the old primary database is
repaired, Data Guard automatically reinstates it to be a
standby database. This act restores high availability to the
Data Guard configuration.
- Easy conversion of a physical standby database to a reporting database A physical standby database can be activated as a primary database, opened read/write for reporting purposes, and then flashed back to a point in the past to be easily converted back to a physical standby database. At this point, Data Guard automatically synchronizes the standby database with the primary database. This allows the physical standby database to be utilized for read/write reporting and cloning activities.
- Automatic deletion of applied archived redo log files in logical standby databases Archived logs, once they are applied on the logical standby database, are automatically deleted, reducing storage consumption on the logical standby and improving Data Guard manageability. Physical standby databases have already had this functionality since Oracle Database 10g Release 1, with Flash Recovery Area.
Monitor Dataguard
select 'Last applied : ' Logs, to_char(next_time,'DD-MON-YY:HH24:MI:SS') Time
from v$archived_log
where sequence# = (select max(sequence#) from v$archived_log where applied='YES')
union
select 'Last received : ' Logs, to_char(next_time,'DD-MON-YY:HH24:MI:SS') Time
from v$archived_log
where sequence# = (select max(sequence#) from v$archived_log);
LOGS TIME
---------------- ------------------
Last applied : 16-JUL-09:09:24:16
Last received : 16-JUL-09:09:28:36
select NAME Name, VALUE Value, UNIT Unit
from v$dataguard_stats
union
select null,null,' ' from dual
union
select null,null,'Time Computed: '||MIN(TIME_COMPUTED)
from v$dataguard_stats;
NAME VALUE UNIT
---------------------- ---------------------- -----------------------------------
apply finish time +00 00:02:07.2 day(2) to second(1) interval
apply lag +00 00:01:59 day(2) to second(0) interval
estimated startup time 16 second
standby has been open N
transport lag +00 00:00:00 day(2) to second(0) interval
Time Computed: 16-JUL-2009 09:33:16
select to_char(max(last_time),'DD-MON-YYYY HH24:MI:SS') "Redo onsite"
from v$standby_log
Redo onsite
--------------------
16-JUL-2009 09:42:44
More Information
Data Guard related Metalink Notes
734862.1 Step By Step Guide On How To Recreate Standby Control File When Datafiles Are On ASM And Using Oracle Managed Files
749947.1 Enabling Encryption for Data Guard Redo Transport using the Advanced Security Option
751600.1 Data Guard Physical Standby Switchover/Failover Detailed Best Practices, 10.2.0.1 - 10.2.0.4 - Data Guard Broker, EM, or SQL*Plus
729551.1 Redo Transport Compression in a Data Guard Environment, 11.1.0.6 - 11.1.0.7
751528.1 Oracle Data Guard and SSH
754065.1 Installing Database Vault in a Data Guard Environment, 10.2.0.3 - 11.1.0.7
737460.1 Changing Storage Definition in a Logical Standby Database
565535.1 Flashback Database Best Practices & Performance
559353.1 SQL Apply and Extended Datatype Support
756732.1 Potentially Longer Instance Recovery Timings in a MAA Configuration When Using Logical Standby
603361.1 Developer and DBA Tips for Pro-Actively Optimizing SQL Apply
454848.1 Installing and Using Standby Statspack in 11gR1
434164.1 Data Guard Redo Log Repository Example
416314.1 Reinstating a Logical Standby Using Backups Instead of Flashback Database
416310.1 Reinstating a Physical Standby Using Backups Instead of Flashback
413696.1 Data Guard Broker does not support Cold Failover Clusters
413484.1 Data Guard Support for Heterogeneous Primary and Standby Systems in Same Data Guard Configuration
414043.1 Role Transitions for Data Guard Configurations Using Mixed Oracle Binaries
409013.1 Cascaded Standby Database
395982.1 Data Guard Support for Mixed HP PA-RISC 64-bit and HP Itanium Environments
370434.1 How to make CRS aware of the role change in Data Guard environment?
368276.1 Steps to workaround issue described in Alert 308698.1
364290.1 Global Customer Service Escalation Process
331924.1 RMAN backups in Max Performance/Max Availability Data Guard Environment
312434.1 Oracle10g Data Guard SQL Apply Troubleshooting
305360.1 Data Guard and Oracle Standard Edition
304488.1 Using standby_file_management with Raw Devices
304061.1 Oracle Data Guard Readme for SQL Apply Release 10.1.0.4
304059.1 Oracle Data Guard Readme for SQL Apply Release 10.1.0.3
303421.1 Creating a Logical Standby with a Different Block Size Than Primary
300479.1 Rolling Upgrades with Logical Standby
290814.1 Rolling a Standby Forward using an RMAN Incremental Backup in 10g
278643.1 Applying Patchset with a 10g Logical Standby in Place
278641.1 Applying Patchset with a 10g Physical Standby in Place
278521.1 Upgrading to 10g with a Physical Standby in Place
278371.1 Creating a Logical Standby with Minimal Production Downtime
278108.1 Upgrading to 10g with a Logical Standby in Place
276636.1 Data Guard GUI ASM restrictions
273177.1 Removing Archived Redo Log Files No Longer Needed By SQL Apply
273015.1 Migrating to RAC using Data Guar
271463.1 Handling Problematic DDL with Data Guard SQL Apply
271455.1 Synchronizing tables in a Logical Standby Database
271448.1 Oracle Data Guard Switchover & Failover Best Practices
269954.1 Script to Collect Data Guard Logical Standby Table Information
257341.1 Example Usage of the DBMS_LOGSTDBY Package
243709.1 Monitoring Physical Standby Progress
241512.1 Script to Collect Data Guard Logical Standby Diagnostic Information
241438.1 Script to Collect Data Guard Physical Standby Diagnostic Information
241374.1 Script to Collect Data Guard Primary Site Diagnostic Information
239100.1 Data Guard Protection Modes Explained
234631.1 Creating a Logical Standby from a Hot Backup
233519.1 Known Issues with Logical Standby
233491.1 Data Guard Wait Events
233261.1 Tuning SQL Apply Operations for Logical Standby
232649.1 Data Guard Gap Detection and Resolution
232240.1 Performing Switchover in a Data Guard Configuration
180031.1 Creating a Data Guard Configuration
All Docs in ML for DataGuard
http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=185750.1