Implementing Data Guard

General Concepts
Architecture
DataGuard Protection Modes
DataGuard Physical Implementation
DataGuard Logical Implementation
Quick Steps for creating the Physical Standby from a Hot backup
Cancel Managed Standby Recovery
Activate Standby
DataGuard Monitoring
Failover Steps
Switchover Steps
Implementation Tips
More Information
Applying Pacthes with Standby
Resolving Problems

General Concepts
Oracle9i 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
Oracle9i Data Guard consists of the following components:

Primary Database:
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.

Log Transport Services:
Log transport services control the automated transfer of archived redo from the primary database to one or more standby sites.

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.

 Data Guard Roles
A database can operate in one of the two mutually exclusive roles: primary or standby database. 

Failover
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

Oracle provides three ways to manage a Data Guard environment:
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 Oracle9i Release 2 when using a Data Guard Broker Configuration.

 

Process Architecture

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.

Physical Standby Processes Architecture (Apply Redo Logs)
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.
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)
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.
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 Oracle9i.  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.  

Note: Logical Standby database is an Oracle9i Release 2 feature.  In 9.2, the LGWR SYNC actually does use the LNS as well.  Only SYNC=NOPARALLEL goes directly from the LGWR.  The default SYNC mode is SYNC=PARALLEL.

When to choose Logical Standby database?
* 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.  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.  This mode is similar to 9iR1’s guaranteed mode. LGWR SYNC AFFIRM option

Maximum Availability: It offers the next highest level of data availability for the primary database. Redo records are synchronously transmitted from the primary database to the standby database using LGWR process.  The transaction is not complete on the primary database until it has been confirmed that the transaction data is available on the standby database.  If standby database becomes unavailable, it will not shut down the primary database.  Instead, the protection mode is temporarily switched to maximum performance mode until the fault has been corrected and the standby database will re-synchronize with the primary database.  This protection mode supports both physical and logical standby databases, and only available in Oracle9i release 2.  LGWR SYNC AFFIRM option

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.  This protection mode is similar to the combination of 9iR1’s Instance, Rapid, and Delay modes.  It supports both physical and logical standby databases.  LGWR ASYNC AFFIRM or NOAFFIRM option

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

 

Terms or Options to know
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 ARCn and/or LGWR processes 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.

Check for Unsupported Objects
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.

Setting the Level of Protection
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;

NAME      PROTECTION_MODE      PROTECTION_LEVEL
--------- -------------------- --------------------
STBY      MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

-No Data Loss Mode. The PROTECTION mode applies only to physical standby databases, using LGWR SYNC and will shutdown the primary database if no standby database can be written to.
-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 DataGuard Implementation

There are two ways to configure a Data Guard environment.

• Using Data Guard Broker.
• Manual configuration of a Data Guard environment.

The best way to understand Data Guard implementation is to setup one manually.
The following shows how to set up Data Guard in this given environment:

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/9.2.0:Y	FGUARD:/u01/app/oracle/product/9.2.0:N

Section 3:  Parameter file

Primary init.ora file:
archive_lag_target = 1800  #specifies how often in seconds we will foce a switch log
db_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 not auto, newly created tablespaces in a standby environment must be recreated manually and newly added datafiles must be copied to the standby servers as well.
dg_broker_start           = true

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
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

Optional Parameters:
db_file_name_convert=('/disk1/oracle/oradata/payroll/','/disk1/oracle/oradata/payroll/standby/')
log_file_name_convert=('/disk1/oracle/oradata/payroll/','/disk1/oracle/oradata/payroll/standby/')
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)     )   )

Steps to Set up a PHYSICAL Data Guard Environment
The following steps show how to set up a Physical Data Guard environment:

Step 1: The Preparation

Create standby redo log files, if necessary:
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 (ARCn) 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


·         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

·         Shut down the primary database.
·         Backup the primary database datafiles and online redo logs. A backup of the online redo logs is necessary to facilitate switchover.

Step 3: Create the Physical standby Database Control File

·         Startup the Primary database and issue the following command to create the standby control file (it must be done AFTER the backup):

Step 4: Transfer the Datafiles and Control File to the Standby Site

·         Transfer the backuped datafiles, redo log files and archived redo logs to the standby site.
·         Transfer the standby control file to the standby site

Step 5: Start the Listeners on both Primary and Standby Site

·         Start the primary database listener.
·         Start the standby database listener.

Step 6: Start the Standby Database (Primary Database already running)

·         Set the correct Oracle environment and copy all the files to each location
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.
·         Mount the standby database.
If you decided to rename the database files manually, you MUST use ALTER DATABASE RENAME FILE <oldname> TO <newname> after the standby database is mounted.

Step 7: Place the Standby Database in Managed Recovery Mode

·         Issue the following command to bring the standby database in managed recover mode (start log apply services).
NOTE: The example includes the DISCONNECT FROM SESSION option so that log apply services run in a background session. This option is new in Oracle9i

Step 8: Monitor the Log Transport services and Log Apply Services

·         With the protection mode we are using, Maximum Performance, archiving of redo logs to the remote standby location do not occur until after a log switch. A log switch occurs, by default, when an online redo log becomes full. Issue a few log switches on the primary database.
·         Confirm the log files received on the standby archive destination.
·         Check the standby alert log file to see if the new logs have applied to the standby database.
·         Monitor the managed recovery.
·         Verify that the new archived redo log was applied

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



LOGICAL Data Guard Implementation

Complete Documentation here

Prior to creating the Logical Standby please ensure the following:
- Determine if the primary database contains tables and datatypes that were not supported by a logical stand by database. If the primary database contains tables that were unsupported, log apply services will exclude the tables applying to the logical stand by database:
Select substr(owner,1,20) OWNER,substr(TABLE_NAME,1,15) TABLE_NAME, substr(COLUMN_NAME,1,15) COLUMN_NAME,
       substr(DATA_TYPE,1,20) DATA_TYPE
   from dba_logstdby_unsupported;

- Ensure that table rows in the primary database can be uniquely identified.
select owner, table_name, bad_column from dba_logstdby_not_unique;

- Ensure that Primary database is in archivelog mode.
SQL> archive log list;

- Ensure supplemental logging is enabled and log parallelism is enabled on the primary database. Supplemental logging must be enabled because the logical standby database cannot use archived redo logs that contain both supplemental log data and no supplemental log data.
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;

- Ensure LOG_PARALLELISM init.ora parameter is set to 1 (default value).
If log parallelism is not enabled, execute the following:
SQL> ALTER SYSTEM SET LOG_PARALLELISM=1 SCOPE=BOTH;

- If you plan to be performing switchover operations with the logical standby then you must create an alternate tablespace in the primary database for logical standby system tables. Use the DBMS_LOGMNR_D.SET_TABLESPACE procedure to move the tables into the new tablespace.  For example:
EXECUTE DBMS_LOGMNR_D.SET_TABLESPACE('logical_tblsp');

Step 1. Enable logging. On your primary database, instruct Oracle Database to force all logging of changes to the redo, even if nologging or unrecoverable data loads are performed:

SQL> alter database force logging;

Verify that forced logging has been enabled on your primary database, by issuing the following:

SQL> select force_logging from v$database;

Step 2. Enable archiving. Ensure that your primary database is in archive log mode:

SQL> archive log list;

If archiving hasn't been enabled on your primary database, run the following:

SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;

Note that the LOG_ARCHIVE_START initialization parameter is obsolete in Oracle Database 10g. Archiving is automatically enabled when you put your database into archive log mode.

Step 3. Put a primary key on every replicated table. The SQL Apply process must be able to match rows changing in the primary database to the corresponding rows changing in the standby database. SQL Apply can't use a rowid, because it can be different between the two databases. Therefore, each table being replicated to a logical standby database must have a primary or unique key defined.

To identify tables that have rows that cannot be uniquely identified, query the DBA_LOGSTDBY_NOT_UNIQUE view.

Step 4. Enable supplemental logging. Enabling supplemental logging will direct Oracle Database to add a small amount of extra information to the redo stream. The SQL Apply process uses this additional information to maintain tables being replicated. On your primary database, enable supplemental logging as follows:

SQL> alter database add supplemental log data (primary key, unique index) columns;
SQL> alter system archive log current;

You can verify that supplemental logging has been enabled, by issuing the following on your primary database:

SQL> select supplemental_log_data_pk, supplemental_log_data_ui from v$database;

Step 5. Create a password file for the primary database. Every database in a Data Guard environment must use a password file. Additionally, the password used by SYS must be the same for all primary and standby databases. On your primary database server, navigate to ORACLE_HOME/dbs and issue the following command:

$ orapwd file=orapw<sid_name> password=top_secret

Also, instruct Oracle Database to use the newly created password file, by setting the init.ora/spfile remote_login_ passwordfile parameter to either EXCLUSIVE or SHARED.

Step 6. Take a backup of your primary database. Take an offline backup of your primary database. Also in mount state create a backup controlfile.

Step 7. Create a logical standby controlfile. You must create a special logical standby database controlfile and then copy it to your standby machine. On your primary database, issue the following SQL:

SQL> alter database create logical standby controlfile as '/ora01/oradata/BRDSTN/sb.ctl';

Note the use of the keyword logical; it's critical to use the correct syntax.
After creating the logical standby controlfile, copy it to your standby machine. In this example, the standby controlfile must be placed in the /ora01/ oradata/BRDSTN directory on the standby machine

Also create a logminer dictionary by running
SQL> EXECUTE DBMS_LOGSTDBY.BUILD;

· Archive the current online redo log.
SQL> ALTER SYSTEM archive log current;

· Identify the archived redo log that contains the logminer dictionary for use in the standby creation process.
SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE DICTIONARY_BEGIN='YES' and STANDBY_DEST='NO';

Step 8. Copy the files to the Standby server.  Copy backup datafiles, backup control file and the latest archived redo log that was identified in the previous steps, and a copy of the primary initialization parameter file to the standby host.

Step 9. Configure the primary database init.ora/spfile. If you are using an spfile, you may find it easier to switch to using an init.ora file while implementing your logical standby. After implementation, you can easily switch back to using an spfile.  In this example, BRDSTN is the database name of both the primary and the standby. Primarydb is the Oracle Net service name of the primary database, and standbydb is the Oracle Net service name of the standby database.

db_name=BRDSTN
db_unique_name=primarydb

# dg_config specifies unique Oracle Net service names in Data Guard environment
log_archive_config='dg_config=(primarydb, standbydb)'
log_archive_dest_1='LOCATION=/orarchive/BRDSTN db_unique_name=primarydb'
log_archive_dest_2='service=standbydb valid_for=(online_logfiles,primary_role) db_unique_name=standbydb'
log_archive_dest_state_1=enable
log_archive_dest_state_2=enable
remote_login_password=exclusive
parallel_max_servers=9
standby_archive_dest=/orarchive/BRDSTN

# Enable automatic propagation of data file operations
standby_file_management=auto

Step 10. Create init.ora for logical standby. Copy the primary init.ora file to the standby machine, and then make the necessary modifications for your logical standby database, as shown HERE:

#  Change db_unique_name to standby Oracle Net name
db_unique_name=standbydb

# In the archive destination, change db_unique_name to standby Oracle Net name
log_archive_dest_1='location=/orarchive/BRDSTN db_unique_name=standbydb'

# Specify where arriving archive redo should be placed
standby_archive_dest=/orarchive/BRDSTN

# If you have a new controlfile name, change it here
control_files=/ora01/oradata/BRDSTN/sb.ctl

# FAL parameters facilitate initial LS setup, but are not required after setup
fal_server=primarydb
fal_client=standbydb

# Oracle recommends setting this minimally to 9
parallel_max_servers=9

Step 11. Create a password file for the logical standby database. As noted in Step 2, every Oracle Data Guard-enabled database needs a password file using the same password. On your standby machine, go to ORACLE_HOME/dbs and issue the following command:

$ orapwd file=orapw<sid_name> password=top_secret

Step 12. Configure Oracle Net for primary and standby databases. The primary and logical standby databases need to communicate with each other via Oracle Net. Ensure that both the primary and the logical standby databases have listeners and that the appropriate Oracle Net service information is in place. Here are examples of the entries in the tnsnames.ora file on both the primary and the standby servers:

primarydb =
(DESCRIPTION=
 (ADDRESS=(PROTOCOL=tcp) (PORT=1521) (HOST=primary_host))
 (CONNECT_DATA=(SERVICE_NAME=BRDSTN)))

standbydb=
(DESCRIPTION=
 (ADDRESS=(PROTOCOL=tcp) (PORT=1521) (HOST=standby_host))
 (CONNECT_DATA=(SERVICE_NAME=BRDSTN)))

Also, Oracle recommends enabling dead connection detection by the setting of sqlnet.expire_time to one minute in your sqlnet.ora file, as shown:

sqlnet.expire_time=1

Step 13. Start up and activate the logical standby database. On your logical standby server, start up and activate your logical standby database, as follows:

SQL> startup mount;
SQL> alter database recover managed standby database;

You may need to give the above ALTER command a few minutes to complete. When it is finished, you can activate your standby database as follows:

SQL> alter database activate standby database;

Step 13. Rename your logical standby database. Renaming your logical standby database is not a required step. However, Oracle recommends renaming your logical standby database to ensure that the logical standby is never confused with the primary database.

Use the nid utility to reset the name of your logical standby database. Before running nid, shut down and start up your database in mount mode.

SQL> shutdown immediate;
SQL> startup mount;

$ nid target=sys/top_secret dbname=BRDLS

In this example, BRDLS is the new name of your logical standby database. You should now see the following line prompting you:

Change database ID and database name BRDSTN to BRDLS? (Y/[N]) =>

Enter Y and a return. At this point, you should see at the bottom of the message text:

DBNEWID - Completed successfully.

Step 14. Change the logical standby db_name in the init.ora file. Now you need to change the db_name initialization parameter. For example, in your logical standby database init.ora file, make this change:

db_name=BRDLS

Step 15. Re-create the password file for the logical standby database. After running the nid utility, you need to re-create your logical standby password file. To do this, navigate to ORACLE_HOME/dbs, remove the old password file, and issue the following OS command:

$ orapwd file=orapw<sid_name> password=top_secret

Step 16. Open the logical standby database with resetlogs. You can now make your logical standby database accessible. Start up your database, and open it with the RESETLOGS command, as follows:

SQL> startup mount;
SQL> alter database open resetlogs;

Step 17. Add temp files to the logical standby database. You'll need a temp tablespace in your logical database if you plan to do any reporting or if you ever transition the logical standby database to a primary database role. Add the temp file(s) to the logical standby as they existed on the primary database:

SQL> alter session disable guard;
SQL> alter tablespace temp add tempfile '/ora01/oradata/BRDSTN/temp01.dbf' 
size 500M reuse;
SQL> alter session enable guard;

Step 18. Restart the logical standby database SQL Apply process. All you need to do now is restart the SQL Apply process on your logical standby database:

SQL> alter database start logical standby apply;

You now have a fully functioning logical standby database.
More information on Note 186150.1 and 278371.1

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/d