Dataguard category

Archive for the ‘Dataguard’ Category

Bug#6035495 in 10g standby environment

Today i got a bug in one of my oracle 10g(10.2.0.3) standby database.
following are the details

Error Number : ORA-19909
Error Message : datafile 1 belongs to an orphan incarnation
Cause: it is due to bug 6035495.
Action : Rebuild Standby database
Permanent Fix : Apply 10.2.0.4 patch to both primary and standby databases.

For more information refer to metalink document 6035495.8.

P.S: The above error may also occur when an incomplete recovery was performed on the primary database.

Also, the following are the main things which are fixed & added when upgrading to 10.2.0.4 Patch

New additions

It synchronizes the cpu performance with the database performance (This feature is usually not included in all the patch set.
      But in this patch, this feature is inherited from all the patch sets up to Jan 2008 and is included as a whole in this patch set)


Oracle Configuration Manager 10.2.7.1 – This OCM will create a schema Oracle_OCM in our database which will collect the database
      configuration information on a monthly basis.


Bug Fixes:

·         ORA-12996 / ORA-12998 / corruption from ALTER TABLE DROP UNUSED COLUMNS CHECKPOINT
·         ORA-0600 [keltnfy-ldminit] – This ORA-600 error happens when resources are not available for oracle. (In our case, this error has occurred only
             once two times . We can confirm the exact root cause only based on the number of occurrences. With just one time, we can’t determine which
        resource is not enough for Oracle) 
·         Slow performance of data pump during Export/Import
·         ALTER TABLE DDL can fail with ORA-2050 / ORA-60 if the table has
-          a TRIGGER using %type variable of the other TABLE and DBLINK.
-          a materialized view (the master table is the target table of ALTER TABLE.)
·         The performance of some queries which involve large sorts can be CPU intensive due to the memory allocation pattern used by the sort.

Configuration of Oracle 10g Data guard

Primary Database Requirements for Data Guard:

FORCE LOGGING must be enabled:

SQL> select force_logging from v$database;
SQL> alter database force logging;
ARCHIVELOG mode and automatic archiving must be enabled:
SQL> archive log list
MAXLOGFILES >= (2 * Current Redo Log Groups) + 1:
SQL> select  records_used “Current Groups”,records_total “Max Groups” from v$controlfile_record_section  where type = ‘REDO LOG’;
listener.ora Additions:

Define the standby database SID on the standby site:
(SID_DESC=
 (SID_NAME=PROD2)
 (ORACLE_HOME=/pgms/oracle/product/v9204)
)
(in $ORACLE_HOME/network/admin/listener.ora)
tnsnames.ora Additions:

Define the standby database connect string on the primary site:
myserver_prod2 =
   (DESCRIPTION =
   (ADDRESS_LIST =
       (ADDRESS =
         (PROTOCOL = TCP)
         (Host = 123.45.67.89) — whatever host IP has PROD2
         (Port = 1521)
       )
   )
   (CONNECT_DATA = (SID = PROD2)
   )
 )
(define myserver_prod and myserver_prod2 on both
primary and standby sites for quick switchovers)

sqlnet.ora and /etc/oratab Additions:

Enable dead connection detection on the primary and standby sites:
sqlnet.expire_time=2
 (in $ORACLE_HOME/network/admin/sqlnet.ora)
Add the standby database’s entry to /etc/oratab on the standby site:
PROD2:/pgms/oracle/product/v9204:N
Standby Database Parameter File:

Create the initPROD2.ora parameter file to be used for the standby database (done from primary database):
If your primary is using an spfile:
 sqlplus “/ as sysdba”
SQL> create pfile=’$ORACLE_HOME/dbs/initPROD2.ora’from spfile;
Else, if your primary is using a pfile:
cp -p $ORACLE_HOME/dbs/initPROD.ora
$ORACLE_HOME/dbs/initPROD2.ora
Note: We will be modifying both the primary and standby parameter files to handle being in either the primary or the standby mode for quick switchovers.
Standby Database Parameters (changes in copy of primary’s values):

Change pathnames, such as control_files, background_dump_dest,
core_dump_dest, user_dump_dest, and audit_file_dest, and add:
# log_archive_dest = /orcl/oradata/PROD2/archivelogs
log_archive_dest_1 = ‘LOCATION=/orcl/oradata/PROD2/archivelogs MANDATORY’  # for switchover
log_archive_dest_state_1 = ENABLE  # for switchover
log_archive_dest_2 = ‘SERVICE=myserver_prod LGWR SYNC’  # for switchover
log_archive_dest_state_2 = ENABLE  # for switchover
standby_archive_dest = /orcl/oradata/PROD2/archivelogs
standby_file_management = AUTO  # or MANUAL for raw devices
remote_archive_enable = TRUE  # TRUE or RECEIVE, change RECEIVE to SEND on switchover
instance_name = PROD2
lock_name_space = PROD2  # use when primary and standby on same system; same as instance_name
fal_server = myserver_prod  # “fal” is Fetch Archive Log, for log gap resolution
fal_client = myserver_prod2
db_file_name_convert = (‘/PROD/’,'/PROD2/’)
log_file_name_convert = (‘/PROD/’,'/PROD2/’)

Primary Database Parameters (changes in primary’s values):

#log_archive_dest = /orcl/oradata/PROD/archivelogs
log_archive_dest_1 = ‘LOCATION=/orcl/oradata/PROD/archivelogs MANDATORY’
log_archive_dest_state_1 = ENABLE
log_archive_dest_2 = ‘SERVICE=myserver_prod2 LGWR SYNC’
log_archive_dest_state_2 = ENABLE
standby_archive_dest = /orcl/oradata/PROD/archivelogs  # for switchover
standby_file_management = AUTO  # for switchover; or MANUAL for raw devices
remote_archive_enable = TRUE  # TRUE or SEND, change SEND to RECEIVE on switchover
instance_name = PROD
lock_name_space = PROD  # use when primary and standby on same system; same as instance_name
fal_server = myserver_prod2  # for switchover
fal_client = myserver_prod  # for switchover
db_file_name_convert = (‘/PROD2/’,'/PROD/’)  # for switchover
log_file_name_convert = (‘/PROD2/’,'/PROD/’)  # for switchover
(If primary uses spfile, wait until after the standby database
files are copied/created to make these parameter changes.)
Standby Database Datafiles, etc.

Create the standby control file from the primary database:
SQL> alter database create standby controlfile as ‘/orcl/oradata/PROD2/ctrl_PROD_01.ctl’;
Shut down the primary database and copy or FTP its datafiles, redo log files, and the just-created standby parameter file and standby control file, to the standby site.
Copy the standby control file on the standby site to the other file names listed in the control_files init.ora parameter.
Create the standby’s password file, if needed, on the standby site:
$ orapwd file=$ORACLE_HOME/dbs/orapwPROD2  password=<sys password> entries=5
Reload the listener on the primary and standby sites:
$ lsnrctl reload
Standby Database Startup:

Start the standby database in nomount mode, create the spfile if wanted, mount the standby database, and change to managed recovery:
$ . oraenv   PROD2
$ sqlplus “/ as sysdba”
SQL> create spfile from pfile;
SQL> startup nomount
SQL> alter database mount standby database;
SQL> alter database recover managed standby database disconnect from session;
SQL> exit
Primary Database Startup:

If your primary is using an spfile, set the primary database parameters in the spfile as listed earlier.
Sample “alter system” commands are shown below:

SQL> startup nomount
SQL> alter system reset log_archive_dest scope=spfile sid=’*';
SQL> alter system set log_archive_dest_1 = ‘LOCATION=/orcl/oradata/PROD/archivelogs MANDATORY’ scope=spfile;… etc
SQL> shutdown
Start up the primary database with the new parameters:
SQL> startup
Start archiving to the standby database by issuing a log switch:
SQL> alter system switch logfile;
Congratulations!!!  You now have a working standby database for your primary database
Add Standby Redo Log Groups to Standby Database

Create standby redo log groups on standby database (start with next group number; create one more group than current number of groups) after switching out of managed recovery mode:
SQL> sqlplus “/ as sysdba”
SQL> alter database recover managed standby database cancel;
SQL> alter database open read only;
SQL> select max(group#) maxgroup from v$logfile;
SQL> select max(bytes) / 1024 “size (K)” from v$log;
SQL> alter database add standby logfile group 4 (‘/orcl/oradata/PROD2/stby_log_PROD_4A.rdo’,'/orcl/oradata/PROD2/stby_log_PROD_4B.rdo’) size 4096K; … etc …
SQL> column member format a55
SQL> select vs.group#,vs.bytes,vl.member from v$standby_log vs,v$logfile vl where vs.group# = vl.group# order by vs.group#,vl.member;

Add Tempfile To Standby
Add a tempfile to the standby database for switchover or read-only access, then, switch back to managed recovery:
SQL> alter tablespace temp add tempfile ‘/data/oradata/PROD2/temp_PROD_01.dbf’ size 400064K reuse;
SQL> alter database recover managed standby database disconnect from session;
SQL> select * from v$tempfile;
SQL> exit
Add Standby Redo Log Groups to Primary Database
Create standby logfile groups on the primary database for switchovers (start with next group number; create one more group than current number of groups):
$ sqlplus “/ as sysdba”
SQL> select max(group#) maxgroup from v$logfile;
SQL> select max(bytes) / 1024 “size (K)” from v$log;
SQL> alter database add standby logfile group 4 (‘/orcl/oradata/PROD/stby_log_PROD_4A.rdo’, ‘/orcl/oradata/PROD/stby_log_PROD_4B.rdo’) size 4096K; … etc …
SQL> column member format a55
SQL> select vs.group#,vs.bytes,vl.member from v$standby_log vs, v$logfile vl where vs.group# = vl.group# order by vs.group#,vl.member;

Switch To Maximum Availability Protection Mode
Switch to the desired “maximum availability” protection mode on the primary database (from the default “maximum performance”):
SQL> select value from v$parameter where name = ‘log_archive_dest_2′;  — must show LGWR SYNC
SQL> shutdown normal
SQL> startup mount
SQL> alter database set standby database to maximize availability;
SQL> alter database open;
SQL> select protection_mode from v$database;
Test Updates Propagating to Standby
Try some edits on the primary and check to see that the changes made it to the standby:
On the primary:
SQL> update spriden set spriden_first_name = ‘James’ where spriden_pidm = 1234 and spriden_change_ind is null;
SQL> commit;
SQL> alter system switch logfile;
On the standby (wait a few seconds first):
SQL> alter database recover managed standby database cancel;
SQL> alter database open read only;
SQL> select * from spriden where spriden_pidm = 1234 and spriden_change_ind is null;
SQL> alter database recover managed standby database disconnect from session;

Running Reports with a Standby
Set standby to Read Only to run reports:
SQL> alter database recover managed standby database cancel;
SQL> alter database open read only;
SQL> @myreport.sql
SQL> alter database recover managed standby database disconnect from session;

Shutdown and Startup for Standby Database
To shut down a standby database:
If in read-only access, switch back to managed recovery (after terminating any other active sessions):
SQL> alter database recover managed standby database disconnect from session;
Cancel managed recovery and shutdown:
SQL> alter database recover managed standby database cancel;
SQL> shutdown immediate

To start up a standby database:
SQL> startup nomount
SQL> alter database mount standby database;
SQL> alter database recover managed standby database disconnect from session;
Switchover – Swapping Primary and Standby
End all activities on the primary and standby database.
On the primary (switchover status should show “TO STANDBY”):
SQL> select database_role,switchover_status from v$database;
SQL> alter database commit to switchover to physical standby;
SQL> shutdown immediate
SQL> startup nomount
SQL> alter database mount standby database;
On the standby (switchover status should show “SWITCHOVER PENDING”):
SQL> select database_role,switchover_status from v$database;
SQL> alter database commit to switchover to primary;
SQL> shutdown normal
SQL> startup
On the primary:SQL> alter database recover managed standby database disconnect from session;
On the standby:SQL> alter system archive log current;
Change tnsnames.ora entry on all servers to swap the connect strings (myserver_prod and myserver_prod2).
Failover – Standby Becomes Primary

End all activities on the standby database.
May need to resolve redo log gaps (not shown here).
On the standby: SQL> alter database recover managed standby database finish;
SQL> alter database commit to switchover to primary;
SQL> shutdown immediate
SQL> startup
Change tnsnames.ora entry on all servers to point the primary connect string to the standby database.
New standby needs to be created.  Old primary is no longer functional.
Monitoring Standby Database
select count(*) from v$archive_gap; 
This query detects gaps in the logs that have been received.  If any rows are returned by
this query then there is a gap in the sequence numbers of the logs that have been received.
This gap must be resolved before logs can be applied.

SELECT decode(count(*),0,0,1) FROM v$managed_standby WHERE (PROCESS=’ARCH’ AND STATUS NOT
IN (‘CONNECTED’)) OR (PROCESS=’MRP0′ AND STATUS NOT IN (‘WAIT_FOR_LOG’,'APPLYING_LOG’))
OR (PROCESS=’RFS’ AND STATUS NOT IN (‘IDLE’,'RECEIVING’));
This query detects bad statuses.  When a bad status is present this query will return a “1″.
The ‘ARCH’ process should always be ‘CONNECTED’.  The ‘MRP0′ process should always be waiting for a log or applying a log, and when this is not true it will report the error in the status. The ‘RFS’ process exists when the Primary is connected to the Standby and should always be ‘IDLE’ or ‘RECEIVING’.

SELECT DECODE(COUNT(DISTINCT PROCESS),3,0,1)
FROM v$managed_standby;
This query detects missing processes.  If we do not have exactly 3 distinct processes then there is a problem, and this query will return a “1″.
The most likely process to be missing is the ‘RFS’ which is the connection to the Primary database. You must resolve the problem preventing the Primary from connecting to the Standby before this process will start running again.
Verify all STANDBY PROCESSES are running normally on the STANDBY database.

SELECT PROCESS,STATUS,RESETLOG_ID,SEQUENCE#,ACTIVE_AGENTS
FROM V$MANAGED_STANDBY ;
A query with good results follows proving all processes are connected
with normal statuses.

SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED
FROM V$ARCHIVED_LOG
WHERE FIRST_TIME > TRUNC(SYSDATE)
ORDER BY SEQUENCE#;

Data guard Related View
V$DATABASE
PROTECTION_LEVEL: current protection mode setting.
FS_FAILOVER_STATUS: synchronization status
DBA_LOGSTDBY_UNSUPPORTED: unsupported tables.
DBA_LOGSTDBY_EVENTS: monitor transaction activity.
V$LOG: Redo log changed.
V$MANAGED_STANDBY : Recovery progress.

Oracle Data Guard Concept

Oracle Data Guard Concept

Oracle Data Guard is one of the most effective and comprehensive data availability, data protection and disaster recovery solutions available today for enterprise data.

Oracle Data Guard is the management, monitoring, and automation software infrastructure that creates, maintains, and monitors one or more standby databases to protect enterprise data from failures, disasters, errors, and corruptions. 

Data Guard maintains these standby databases as transitional consistent copies of the production database. These standby databases can be located at remote disaster recovery sites thousands of miles away from the production data center, or they may be located in the same city, same campus, or even in the same building. If the production database becomes unavailable because of a planned or an unplanned outage, Data Guard can switch any standby database to the production role, thus minimizing the downtime associated with the outage, and preventing any data loss.

Available as a feature of the Enterprise Edition of the Oracle Database, Data Guard can be used in combination with other Oracle High Availability (HA) solutions such as Real Application Clusters (RAC), Oracle Flashback and Oracle Recovery Manager (RMAN), to provide a very high level of data protection and data availability that is unprecedented in the industry.

The following diagram presents a hi-level overview of Oracle Data Guard.

  

 

Overview of Oracle Data Guard Functional Components

Data Guard Configuration:

A Data Guard configuration consists of one production (or primary) database and up to nine standby databases. The databases in a Data Guard configuration are connected by Oracle Net and may be dispersed geographically. There are no restrictions on where the databases are located, provided that they can communicate with each other. However, for disaster recovery, it is recommended that the standby databases are hosted at sites that are geographically separated from the primary site.

Redo Apply and SQL Apply:

A standby database is initially created from a backup copy of the primary database. Once created, Data Guard automatically maintains the standby database as a transactional consistent copy of the primary database by transmitting primary database redo data to the standby system and then applying the redo logs to the standby database.

Data Guard provides two methods to apply this redo data to the standby database and keep it transactional consistent with the primary, and these methods correspond to the two types of standby databases supported by Data Guard.

• Redo Apply, used for physical standby databases
• SQL Apply, used for logical standby databases

A physical standby database provides a physically identical copy of the primary database, with on-disk database structures that are identical to the primary database on a block-for-block basis. The database schemas, including indexes are the same. The Redo Apply technology applies redoes data on the physical standby database using standard Oracle media recovery techniques. 

A logical standby database contains the same logical information as the production database, although the physical organization and structure of the data can be different. The SQL apply technology keeps the logical standby database synchronized with the primary database by transforming the data in the redo logs received from the primary database into SQL statements and then executing the SQL statements on the standby database. This makes it possible for the logical standby database to be accessed for queries and reporting purposes at the same time the SQL is being applied to it. Thus, a logical standby database can be used concurrently for data protection and reporting.

Role Management:

Using Data Guard, the role of a database can be switched from a primary role to a standby role and vice versa, ensuring no data loss in the process, and minimizing downtime. There are two kinds of role transitions – a switchover and a failover. A switchover is a role reversal between the primary database and one of its standby databases. This is typically done for planned maintenance of the primary system. During a switchover, the primary database transitions to a standby role and the standby database transitions to the primary role. The transition occurs without having to re-create either database. A failover is an irreversible transition of a standby database to the primary role. This is only done in the event of a catastrophic failure of the primary database, which is assumed to be lost and to be used again in the Data Guard configuration, it must be re-instantiated as a standby from the new primary.

Data Guard Protection Modes:

In some situations, a business cannot afford to lose data at any cost. In other situations, some applications require maximum database performance and can tolerate a potential loss of data. Data Guard provides three distinct modes of data protection to satisfy these varied requirements:

• Maximum Protection— This mode offers the highest level of data protection. Data is synchronously transmitted to the standby database from the primary database and transactions are not committed on the primary database unless the redo data is available on at least one standby database configured in this mode. If the last standby database configured in this mode becomes unavailable, processing stops on the primary database. This mode ensures no-data-loss.
• Maximum Availability— This mode is similar to the maximum protection mode, including zero data loss. However, if a standby database becomes unavailable (for example, because of network connectivity problems), processing continues on the primary database. When the fault is corrected, the standby database is automatically resynchronized with the primary database.
• Maximum Performance— This mode offers slightly less data protection on the primary database, but higher performance than maximum availability mode. In this mode, as the primary database processes transactions, redo data is asynchronously shipped to the standby database. The commit operation of the primary database does not wait for the standby database to acknowledge receipt of redo data before completing write operations on the primary database. If any standby destination becomes unavailable, processing continues on the primary database and there is little effect on primary database performance.

Data Guard Broker:

The Oracle Data Guard Broker is a distributed management framework that automates and centralizes the creation, maintenance, and monitoring of Data Guard configurations. All management operations can be performed either through Oracle Enterprise Manager, which uses the Broker, or through the Broker’s specialized command-line interface (DGMGRL).

Data Guard Architecture Diagram

The following diagram shows an overview of the Oracle Data Guard architecture.

What’s New in Oracle Data Guard 10g Release 2?

This section will highlight some of the key new features of Oracle Data Guard 10g Release 2. For details into these features, please refer to the following:

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. 

Improved Redo Transmission

Several enhancements have been made in the redo transmission architecture to make sure redo data generated on the primary database can be transmitted as quickly and efficiently as possible to the standby database(s). 

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. 

Fine-grained monitoring of Data Guard configurations

Oracle Enterprise Manager has been enhanced to provide granular, up-to-date monitoring of Data Guard configurations, so that administrators may make an informed and expedient decision regarding managing this configuration. 

What’s New in Oracle Data Guard 10g Release 1?

This section will highlight some of the key new features of Oracle Data Guard 10g Release 1. For details into these features, please refer to the following:

General New Features:

Real Time Apply:

With this feature, redo data can be applied on the standby database (whether Redo Apply or SQL Apply) as soon as they have written to a Standby Redo Log (SRL). Prior releases of Data Guard require this redo data to be archived at the standby database in the form of archivelogs before they can be applied. 

The Real Time Apply feature allows standby databases to be closely synchronized with the primary database, enabling up-to-date and real-time reporting (especially for Data Guard SQL Apply). This also enables faster switchover and failover times, which in turn reduces planned and unplanned downtime for the business.

The impact of a disaster is often measured in terms of Recovery Point Objective (RPO – i.e. how much data can a business afford to lose in the event of a disaster) and Recovery Time Objective (RTO – i.e. how much time a business can afford to be down in the event of a disaster). With Oracle Data Guard, when Maximum Protection is used in combination with Real Time Apply, businesses get the benefits of both zero data loss as well as minimal downtime in the event of a disaster and this makes Oracle Data Guard the only solution available today with the best RPO and RTO benefits for a business.

Integration with Flashback Database:

Data Guard in 10g has been integrated with the Flashback family of features to bring the Flashback feature benefits to a Data Guard configuration.

One such benefit is human error protection. In Oracle9i, administrators may configure Data Guard with an apply delay to protect standby databases from possible logical data corruptions that occurred on the primary database. The side-effects of such delays are that any reporting that gets done on the standby database is done on old data, and switchover/failover gets delayed because the accumulated logs have to be applied first. In Data Guard 10g, with the Real Time Apply feature, such delayed-reporting or delayed-switchover/failover issues do not exist, and – if logical corruptions do land up affecting both the primary and standby database, the administrator may decide to use Flashback Database on both the primary and standby databases to quickly revert the databases to an earlier point-in-time to back out such user errors. 

Another benefit that such integration provides is during failovers. In releases prior to 10g, following any failover operation, the old primary database must be recreated (as a new standby database) from a backup of the new primary database, if the administrator intends to bring it back in the Data Guard configuration. This may be an issue when the database sizes are fairly large, and the primary/standby databases are hundreds/thousands of miles away. However, in Data Guard 10g, after the primary server fault is repaired, the primary database may simply be brought up in mounted mode, “flashed back” (using flashback database) to the SCN at which the failover occurred, and then brought back as a standby database in the Data Guard configuration. No re-instantiation is required.

SQL Apply New Features:

Zero Downtime Instantiation:

Logical standby database can now be created from an online backup of the primary database, without shutting down or quiescing the primary database, as was the case in prior releases. No shutdown of the primary system implies production downtime is eliminated, and no quiesce implies no waiting for quiescing to take effect and no dependence on Resource Manager.

Rolling Upgrades:

Oracle Database 10g supports database software upgrades (from Oracle Database 10g Patchset 1 onwards) in a rolling fashion, with near zero database downtime, by using Data Guard SQL Apply. The steps involve upgrading the logical standby database to the next release, running in a mixed mode to test and validate the upgrade, doing a role reversal by switching over to the upgraded database, and then finally upgrading the old primary database. While running in a mixed mode for testing purpose, the upgrade can be aborted and the software downgraded, without data loss. For additional data protection during these steps, a second standby database may be used.

By supporting rolling upgrades with minimal downtimes, Data Guard reduces the large maintenance windows typical of many administrative tasks, and enables the 24×7 operation of the business.

Additional Datatypes:

SQL Apply now supports the following additional data types.

• NCLOB
• LONG
• LONG RAW
• BINARY_FLOAT
• BINARY_DOUBLE
• IOT-s (without overflows and without LOB columns)

This support for additional datatypes allows logical standby databases to recover and protect a wider variety of data, thus increasing the overall database protection and recovery options for Data Guard.

Enterprise Manager / Data Guard Broker New Features:

RAC Support:

It is now possible to use the Data Guard Broker, and the Broker’s Command Line Interface (DGMGRL), as well as Enterprise Manager, to create and manage Data Guard configurations that contain RAC primary and RAC standby databases. In Oracle9i, such administration is possible only through SQL*Plus. In Data Guard 10g, Data Guard Broker interfaces with Oracle Clusterware such that it has control over critical operations during specific Data Guard state transitions, such as switchovers, failovers, protection mode changes and state changes.

Simplified Browser-based Interface

Administration of a Data Guard configuration can be done through the new streamlined browser-based HTML interface of Enterprise Manager that enables complete standby database lifecycle management. The focus of such streamlined administration is on:

• Ease of use.
• Management based on best practices.
• Pre-built integration with other HA features.

Data Guard Benefits

1. Disaster recovery and high availability
   Data Guard provides an efficient and comprehensive disaster recovery and high availability solution. Automatic failover and easy-to-manage switchover capabilities allow quick role reversals between primary and standby databases, minimizing the downtime of the primary database for planned and unplanned outages.

1. Complete data protection
   A standby database also provides an effective safeguard against data corruptions and user errors. Storage level physical corruptions on the primary database do not propagate to the standby database. Similarly, logical corruptions or user errors that cause the primary database to be permanently damaged can be resolved. Finally, the redo data is validated at the time it is received at the standby database and further when applied to the standby database.

1. Efficient utilization of system resources
   A physical standby database can be used for backups and read-only reporting, thereby reducing the primary database workload and saving valuable CPU and I/O cycles. In Oracle Database 10g Release 2, a physical standby database can also be easily converted back and forth between being a physical standby database and an open read/write database. A logical standby database allows its tables to be simultaneously available for read-only access while they are updated from the primary database. A logical standby database also allows users to perform data manipulation operations on tables that are not updated from the primary database. Finally, additional indexes and materialized views can be created in the logical standby database for better reporting performance.

1. Flexibility in data protection to balance availability against performance requirements
   Oracle Data Guard offers the maximum protection, maximum availability, and maximum performance modes to help enterprises balance data availability against system performance requirements.

1. Protection from communication failures
   If network connectivity is lost between the primary and one or more standby databases, redo data cannot be sent from the primary to those standby databases. Once connectivity is re-established, the missing redo data is automatically detected by Data Guard and the necessary archive logs are automatically transmitted to the standby databases. The standby databases are resynchronized with the primary database, with no manual intervention by the administrator. 

1. Centralized and simple management
   Data Guard Broker automates the management and monitoring tasks across the multiple databases in a Data Guard configuration. Administrators may use either Oracle Enterprise Manager or the Broker’s own specialized command-line interface (DGMGRL) to take advantage of this integrated management framework.

Integrated with Oracle databaseData Guard is available as an integrated feature of the Oracle Database (Enterprise Edition) at no extra cost.

Configuration of Oracle 10g Data guard

 Primary Database Requirements for Data Guard:
FORCE LOGGING must be enabled:
SQL> select force_logging from v$database;
SQL> alter database force logging;
ARCHIVELOG mode and automatic archiving must be enabled:
SQL> archive log list
MAXLOGFILES >= (2 * Current Redo Log Groups) + 1:
SQL> select  records_used “Current Groups”,records_total “Max Groups” from v$controlfile_record_section  where type = ‘REDO LOG’;
listener.ora Additions:
Define the standby database SID on the standby site:
(SID_DESC=
 (SID_NAME=PROD2)
 (ORACLE_HOME=/pgms/oracle/product/v9204)
)
(in $ORACLE_HOME/network/admin/listener.ora)
 tnsnames.ora Additions:
Define the standby database connect string on the primary site:
myserver_prod2 =
   (DESCRIPTION =
   (ADDRESS_LIST =
       (ADDRESS =(PROTOCOL = TCP)  (Host = 123.45.67.89) — whatever host IP has PROD2(Port = 1521)
       ) )
   (CONNECT_DATA = (SID = PROD2) ) )
(define myserver_prod and myserver_prod2 on both primary and standby sites for quick switchovers)
 sqlnet.ora and /etc/oratab Additions:
Enable dead connection detection on the primary and standby sites:
sqlnet.expire_time=2
 (in $ORACLE_HOME/network/admin/sqlnet.ora)
Add the standby database’s entry to /etc/oratab on the standby site:
PROD2:/pgms/oracle/product/v9204:N
Standby Database Parameter File:
Create the initPROD2.ora parameter file to be used for the standby database (done from primary database):
If your primary is using an spfile:
sqlplus “/ as sysdba”
SQL> create pfile=’$ORACLE_HOME/dbs/initPROD2.ora’from spfile;
Else, if your primary is using a pfile:
cp -p $ORACLE_HOME/dbs/initPROD.ora
$ORACLE_HOME/dbs/initPROD2.ora
Note: We will be modifying both the primary and standby parameter files to handle being in either the primary or the standby mode for quick switchovers.
Standby Database Parameters (changes in copy of primary’s values):
Change pathnames, such as control_files, background_dump_dest,core_dump_dest, user_dump_dest, and audit_file_dest, and add:
# log_archive_dest = /orcl/oradata/PROD2/archivelogs
log_archive_dest_1 = ‘LOCATION=/orcl/oradata/PROD2/archivelogs MANDATORY’  # for switchover
log_archive_dest_state_1 = ENABLE  # for switchover
log_archive_dest_2 = ‘SERVICE=myserver_prod LGWR SYNC’  # for switchover
log_archive_dest_state_2 = ENABLE  # for switchover
standby_archive_dest = /orcl/oradata/PROD2/archivelogs
standby_file_management = AUTO  # or MANUAL for raw devices
remote_archive_enable = TRUE  # TRUE or RECEIVE, change RECEIVE to SEND on switchover
instance_name = PROD2
lock_name_space = PROD2  # use when primary and standby on same system; same as instance_name
fal_server = myserver_prod  # “fal” is Fetch Archive Log, for log gap resolution
fal_client = myserver_prod2
db_file_name_convert = (‘/PROD/’,'/PROD2/’)
log_file_name_convert = (‘/PROD/’,'/PROD2/’)
Primary Database Parameters (changes in primary’s values):
#log_archive_dest = /orcl/oradata/PROD/archivelogs
log_archive_dest_1 = ‘LOCATION=/orcl/oradata/PROD/archivelogs MANDATORY’
log_archive_dest_state_1 = ENABLE
log_archive_dest_2 = ‘SERVICE=myserver_prod2 LGWR SYNC’
log_archive_dest_state_2 = ENABLE
standby_archive_dest = /orcl/oradata/PROD/archivelogs  # for switchover
standby_file_management = AUTO  # for switchover; or MANUAL for raw devices
remote_archive_enable = TRUE  # TRUE or SEND, change SEND to RECEIVE on switchover
instance_name = PROD
lock_name_space = PROD  # use when primary and standby on same system; same as instance_name
fal_server = myserver_prod2  # for switchover
fal_client = myserver_prod  # for switchover
db_file_name_convert = (‘/PROD2/’,'/PROD/’)  # for switchover
log_file_name_convert = (‘/PROD2/’,'/PROD/’)  # for switchover
(If primary uses spfile, wait until after the standby databasefiles are copied/created to make these parameter changes.)
Standby Database Datafiles, etc.
Create the standby control file from the primary database:
SQL> alter database create standby controlfile as ‘/orcl/oradata/PROD2/ctrl_PROD_01.ctl’;
Shut down the primary database and copy or FTP its datafiles, redo log files, and the just-created standby parameter file and standby control file, to the standby site.

Copy the standby control file on the standby site to the other file names listed in the control_files init.ora parameter.

Create the standby’s password file, if needed, on the standby site:
$ orapwd file=$ORACLE_HOME/dbs/orapwPROD2  password=<sys password> entries=5

Reload the listener on the primary and standby sites:
$ lsnrctl reload

Standby Database Startup:
Start the standby database in nomount mode, create the spfile if wanted, mount the standby database, and change to managed recovery:
$ . oraenv   PROD2
$ sqlplus “/ as sysdba”
SQL> create spfile from pfile;
SQL> startup nomount
SQL> alter database mount standby database;
SQL> alter database recover managed standby database disconnect from session;
SQL> exit
Primary Database Startup:
If your primary is using an spfile, set the primary database parameters in the spfile as listed earlier. Sample “alter system” commands are shown below:
SQL> startup nomount
SQL> alter system reset log_archive_dest scope=spfile sid=’*';
SQL> alter system set log_archive_dest_1 = ‘LOCATION=/orcl/oradata/PROD/archivelogs MANDATORY’ scope=spfile;… etc
SQL> shutdown
Start up the primary database with the new parameters:

SQL> startup

Start archiving to the standby database by issuing a log switch:

SQL> alter system switch logfile;

Congratulations!!!  You now have a working standby database for your primary database

Add Standby Redo Log Groups to Standby Database

Create standby redo log groups on standby database (start with next group number; create one more group than current number of groups) after switching out of managed recovery mode:

SQL> sqlplus “/ as sysdba”

SQL> alter database recover managed standby database cancel;

SQL> alter database open read only;

SQL> select max(group#) maxgroup from v$logfile;

SQL> select max(bytes) / 1024 “size (K)” from v$log;

SQL> alter database add standby logfile group 4 (‘/orcl/oradata/PROD2/stby_log_PROD_4A.rdo’,'/orcl/oradata/PROD2/stby_log_PROD_4B.rdo’) size 4096K; … etc …

SQL> column member format a55

SQL> select vs.group#,vs.bytes,vl.member from v$standby_log vs,v$logfile vl where vs.group# = vl.group# order by vs.group#,vl.member;

Add Tempfile To Standby

Add a tempfile to the standby database for switchover or read-only access, then, switch back to managed recovery:

SQL> alter tablespace temp add tempfile ‘/data/oradata/PROD2/temp_PROD_01.dbf’ size 400064K reuse;

SQL> alter database recover managed standby database disconnect from session;

SQL> select * from v$tempfile;

SQL> exit

Add Standby Redo Log Groups to Primary Database

Create standby logfile groups on the primary database for switchovers (start with next group number; create one more group than current number of groups):

$ sqlplus “/ as sysdba”

SQL> select max(group#) maxgroup from v$logfile;
SQL> select max(bytes) / 1024 “size (K)” from v$log;

SQL> alter database add standby logfile group 4 (‘/orcl/oradata/PROD/stby_log_PROD_4A.rdo’, ‘/orcl/oradata/PROD/stby_log_PROD_4B.rdo’) size 4096K; … etc …

SQL> column member format a55
SQL> select vs.group#,vs.bytes,vl.member from v$standby_log vs, v$logfile vl where vs.group# = vl.group# order by vs.group#,vl.member;

Switch To Maximum Availability Protection Mode

Switch to the desired “maximum availability” protection mode on the primary database (from the default “maximum performance”):

SQL> select value from v$parameter where name = ‘log_archive_dest_2′;  — must show LGWR SYNC

SQL> shutdown normal

SQL> startup mount

SQL> alter database set standby database to maximize availability;

SQL> alter database open;

SQL> select protection_mode from v$database;

Test Updates Propagating to Standby

Try some edits on the primary and check to see that the changes made it to the standby:

On the primary:

SQL> update spriden set spriden_first_name = ‘James’ where spriden_pidm = 1234 and spriden_change_ind is null;

SQL> commit;

SQL> alter system switch logfile;

On the standby (wait a few seconds first):

SQL> alter database recover managed standby database cancel;

SQL> alter database open read only;

SQL> select * from spriden where spriden_pidm = 1234 and spriden_change_ind is null;

SQL> alter database recover managed standby database disconnect from session;

Running Reports with a Standby

Set standby to Read Only to run reports:

SQL> alter database recover managed standby database cancel;

SQL> alter database open read only;

SQL> @myreport.sql

SQL> alter database recover managed standby database disconnect from session;

Shutdown and Startup for Standby Database

To shut down a standby database:

If in read-only access, switch back to managed recovery (after terminating any other active sessions):

SQL> alter database recover managed standby database disconnect from session;

Cancel managed recovery and shutdown:

SQL> alter database recover managed standby database cancel;

SQL> shutdown immediate

To start up a standby database:

SQL> startup nomount

SQL> alter database mount standby database;

SQL> alter database recover managed standby database disconnect from session;

Switchover – Swapping Primary and Standby

End all activities on the primary and standby database.

On the primary (switchover status should show “TO STANDBY”):

SQL> select database_role,switchover_status from v$database;

SQL> alter database commit to switchover to physical standby;

SQL> shutdown immediate

SQL> startup nomount

SQL> alter database mount standby database;

On the standby (switchover status should show “SWITCHOVER PENDING”):

SQL> select database_role,switchover_status from v$database;

SQL> alter database commit to switchover to primary;

SQL> shutdown normal

SQL> startup

On the primary:SQL> alter database recover managed standby database disconnect from session;

On the standby:SQL> alter system archive log current;

Change tnsnames.ora entry on all servers to swap the connect strings (myserver_prod and myserver_prod2).

Failover – Standby Becomes Primary

End all activities on the standby database.

May need to resolve redo log gaps (not shown here).

On the standby: SQL> alter database recover managed standby database finish;

SQL> alter database commit to switchover to primary;

SQL> shutdown immediate

SQL> startup

Change tnsnames.ora entry on all servers to point the primary connect string to the standby database.

New standby needs to be created.  Old primary is no longer functional.

Monitoring Standby Database

select count(*) from v$archive_gap;  

This query detects gaps in the logs that have been received.  If any rows are returned by

this query then there is a gap in the sequence numbers of the logs that have been received.

This gap must be resolved before logs can be applied.

SELECT decode(count(*),0,0,1) FROM v$managed_standby WHERE (PROCESS=’ARCH’ AND STATUS NOT

IN (‘CONNECTED’)) OR (PROCESS=’MRP0′ AND STATUS NOT IN (‘WAIT_FOR_LOG’,'APPLYING_LOG’))

OR (PROCESS=’RFS’ AND STATUS NOT IN (‘IDLE’,'RECEIVING’));

This query detects bad statuses.  When a bad status is present this query will return a “1″.

The ‘ARCH’ process should always be ‘CONNECTED’.  The ‘MRP0′ process should always be waiting for a log or applying a log, and when this is not true it will report the error in the status. The ‘RFS’ process exists when the Primary is connected to the Standby and should always be ‘IDLE’ or ‘RECEIVING’.

SELECT DECODE(COUNT(DISTINCT PROCESS),3,0,1)

FROM v$managed_standby;

This query detects missing processes.  If we do not have exactly 3 distinct processes then there is a problem, and this query will return a “1″. 

The most likely process to be missing is the ‘RFS’ which is the connection to the Primary database. You must resolve the problem preventing the Primary from connecting to the Standby before this process will start running again.

Verify all STANDBY PROCESSES are running normally on the STANDBY database.

SELECT PROCESS,STATUS,RESETLOG_ID,SEQUENCE#,ACTIVE_AGENTS

FROM V$MANAGED_STANDBY ;

A query with good results follows proving all processes are connected

with normal statuses.

SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED

FROM V$ARCHIVED_LOG

WHERE FIRST_TIME > TRUNC(SYSDATE)

ORDER BY SEQUENCE#;

Data guard Related View

V$DATABASE

PROTECTION_LEVEL: current protection mode setting.

FS_FAILOVER_STATUS: synchronization status

DBA_LOGSTDBY_UNSUPPORTED: unsupported tables.

DBA_LOGSTDBY_EVENTS: monitor transaction activity.

V$LOG: Redo log changed.

V$MANAGED_STANDBY : Recovery progress.

Oracle Data Guard Concept

Oracle Data Guard is one of the most effective and comprehensive data availability, data protection and disaster recovery solutions available today for enterprise data.

Oracle Data Guard is the management, monitoring, and automation software infrastructure that creates, maintains, and monitors one or more standby databases to protect enterprise data from failures, disasters, errors, and corruptions. 

Data Guard maintains these standby databases as transitional consistent copies of the production database. These standby databases can be located at remote disaster recovery sites thousands of miles away from the production data center, or they may be located in the same city, same campus, or even in the same building. If the production database becomes unavailable because of a planned or an unplanned outage, Data Guard can switch any standby database to the production role, thus minimizing the downtime associated with the outage, and preventing any data loss.

Available as a feature of the Enterprise Edition of the Oracle Database, Data Guard can be used in combination with other Oracle High Availability (HA) solutions such as Real Application Clusters (RAC), Oracle Flashback and Oracle Recovery Manager (RMAN), to provide a very high level of data protection and data availability that is unprecedented in the industry.

The following diagram presents a hi-level overview of Oracle Data Guard.

  

Overview of Oracle Data Guard Functional Components

Data Guard Configuration:

A Data Guard configuration consists of one production (or primary) database and up to nine standby databases. The databases in a Data Guard configuration are connected by Oracle Net and may be dispersed geographically. There are no restrictions on where the databases are located, provided that they can communicate with each other. However, for disaster recovery, it is recommended that the standby databases are hosted at sites that are geographically separated from the primary site.

Redo Apply and SQL Apply:

A standby database is initially created from a backup copy of the primary database. Once created, Data Guard automatically maintains the standby database as a transactional consistent copy of the primary database by transmitting primary database redo data to the standby system and then applying the redo logs to the standby database.

Data Guard provides two methods to apply this redo data to the standby database and keep it transactional consistent with the primary, and these methods correspond to the two types of standby databases supported by Data Guard.

• Redo Apply, used for physical standby databases
• SQL Apply, used for logical standby databases

A physical standby database provides a physically identical copy of the primary database, with on-disk database structures that are identical to the primary database on a block-for-block basis. The database schemas, including indexes are the same. The Redo Apply technology applies redoes data on the physical standby database using standard Oracle media recovery techniques. 

A logical standby database contains the same logical information as the production database, although the physical organization and structure of the data can be different. The SQL apply technology keeps the logical standby database synchronized with the primary database by transforming the data in the redo logs received from the primary database into SQL statements and then executing the SQL statements on the standby database. This makes it possible for the logical standby database to be accessed for queries and reporting purposes at the same time the SQL is being applied to it. Thus, a logical standby database can be used concurrently for data protection and reporting.

Role Management:

Using Data Guard, the role of a database can be switched from a primary role to a standby role and vice versa, ensuring no data loss in the process, and minimizing downtime. There are two kinds of role transitions – a switchover and a failover. A switchover is a role reversal between the primary database and one of its standby databases. This is typically done for planned maintenance of the primary system. During a switchover, the primary database transitions to a standby role and the standby database transitions to the primary role. The transition occurs without having to re-create either database. A failover is an irreversible transition of a standby database to the primary role. This is only done in the event of a catastrophic failure of the primary database, which is assumed to be lost and to be used again in the Data Guard configuration, it must be re-instantiated as a standby from the new primary.

Data Guard Protection Modes:

In some situations, a business cannot afford to lose data at any cost. In other situations, some applications require maximum database performance and can tolerate a potential loss of data. Data Guard provides three distinct modes of data protection to satisfy these varied requirements:

• Maximum Protection— This mode offers the highest level of data protection. Data is synchronously transmitted to the standby database from the primary database and transactions are not committed on the primary database unless the redo data is available on at least one standby database configured in this mode. If the last standby database configured in this mode becomes unavailable, processing stops on the primary database. This mode ensures no-data-loss.
• Maximum Availability— This mode is similar to the maximum protection mode, including zero data loss. However, if a standby database becomes unavailable (for example, because of network connectivity problems), processing continues on the primary database. When the fault is corrected, the standby database is automatically resynchronized with the primary database.
• Maximum Performance— This mode offers slightly less data protection on the primary database, but higher performance than maximum availability mode. In this mode, as the primary database processes transactions, redo data is asynchronously shipped to the standby database. The commit operation of the primary database does not wait for the standby database to acknowledge receipt of redo data before completing write operations on the primary database. If any standby destination becomes unavailable, processing continues on the primary database and there is little effect on primary database performance.

Data Guard Broker:

The Oracle Data Guard Broker is a distributed management framework that automates and centralizes the creation, maintenance, and monitoring of Data Guard configurations. All management operations can be performed either through Oracle Enterprise Manager, which uses the Broker, or through the Broker’s specialized command-line interface (DGMGRL).

Data Guard Architecture Diagram

The following diagram shows an overview of the Oracle Data Guard architecture.

 

What’s New in Oracle Data Guard 10g Release 2?

This section will highlight some of the key new features of Oracle Data Guard 10g Release 2. For details into these features, please refer to the following:

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. 

Improved Redo Transmission

Several enhancements have been made in the redo transmission architecture to make sure redo data generated on the primary database can be transmitted as quickly and efficiently as possible to the standby database(s). 

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. 

Fine-grained monitoring of Data Guard configurations

Oracle Enterprise Manager has been enhanced to provide granular, up-to-date monitoring of Data Guard configurations, so that administrators may make an informed and expedient decision regarding managing this configuration. 

What’s New in Oracle Data Guard 10g Release 1?

This section will highlight some of the key new features of Oracle Data Guard 10g Release 1. For details into these features, please refer to the following:

General New Features:

Real Time Apply:

With this feature, redo data can be applied on the standby database (whether Redo Apply or SQL Apply) as soon as they have written to a Standby Redo Log (SRL). Prior releases of Data Guard require this redo data to be archived at the standby database in the form of archivelogs before they can be applied. 

The Real Time Apply feature allows standby databases to be closely synchronized with the primary database, enabling up-to-date and real-time reporting (especially for Data Guard SQL Apply). This also enables faster switchover and failover times, which in turn reduces planned and unplanned downtime for the business.

The impact of a disaster is often measured in terms of Recovery Point Objective (RPO – i.e. how much data can a business afford to lose in the event of a disaster) and Recovery Time Objective (RTO – i.e. how much time a business can afford to be down in the event of a disaster). With Oracle Data Guard, when Maximum Protection is used in combination with Real Time Apply, businesses get the benefits of both zero data loss as well as minimal downtime in the event of a disaster and this makes Oracle Data Guard the only solution available today with the best RPO and RTO benefits for a business.

Integration with Flashback Database:

Data Guard in 10g has been integrated with the Flashback family of features to bring the Flashback feature benefits to a Data Guard configuration.

One such benefit is human error protection. In Oracle9i, administrators may configure Data Guard with an apply delay to protect standby databases from possible logical data corruptions that occurred on the primary database. The side-effects of such delays are that any reporting that gets done on the standby database is done on old data, and switchover/failover gets delayed because the accumulated logs have to be applied first. In Data Guard 10g, with the Real Time Apply feature, such delayed-reporting or delayed-switchover/failover issues do not exist, and – if logical corruptions do land up affecting both the primary and standby database, the administrator may decide to use Flashback Database on both the primary and standby databases to quickly revert the databases to an earlier point-in-time to back out such user errors. 

Another benefit that such integration provides is during failovers. In releases prior to 10g, following any failover operation, the old primary database must be recreated (as a new standby database) from a backup of the new primary database, if the administrator intends to bring it back in the Data Guard configuration. This may be an issue when the database sizes are fairly large, and the primary/standby databases are hundreds/thousands of miles away. However, in Data Guard 10g, after the primary server fault is repaired, the primary database may simply be brought up in mounted mode, “flashed back” (using flashback database) to the SCN at which the failover occurred, and then brought back as a standby database in the Data Guard configuration. No re-instantiation is required.

SQL Apply New Features:

Zero Downtime Instantiation:

Logical standby database can now be created from an online backup of the primary database, without shutting down or quiescing the primary database, as was the case in prior releases. No shutdown of the primary system implies production downtime is eliminated, and no quiesce implies no waiting for quiescing to take effect and no dependence on Resource Manager.

Rolling Upgrades:

Oracle Database 10g supports database software upgrades (from Oracle Database 10g Patchset 1 onwards) in a rolling fashion, with near zero database downtime, by using Data Guard SQL Apply. The steps involve upgrading the logical standby database to the next release, running in a mixed mode to test and validate the upgrade, doing a role reversal by switching over to the upgraded database, and then finally upgrading the old primary database. While running in a mixed mode for testing purpose, the upgrade can be aborted and the software downgraded, without data loss. For additional data protection during these steps, a second standby database may be used.

By supporting rolling upgrades with minimal downtimes, Data Guard reduces the large maintenance windows typical of many administrative tasks, and enables the 24×7 operation of the business.

Additional Datatypes:

SQL Apply now supports the following additional data types.

• NCLOB
• LONG
• LONG RAW
• BINARY_FLOAT
• BINARY_DOUBLE
• IOT-s (without overflows and without LOB columns)

This support for additional datatypes allows logical standby databases to recover and protect a wider variety of data, thus increasing the overall database protection and recovery options for Data Guard.

Enterprise Manager / Data Guard Broker New Features:

RAC Support:

It is now possible to use the Data Guard Broker, and the Broker’s Command Line Interface (DGMGRL), as well as Enterprise Manager, to create and manage Data Guard configurations that contain RAC primary and RAC standby databases. In Oracle9i, such administration is possible only through SQL*Plus. In Data Guard 10g, Data Guard Broker interfaces with Oracle Clusterware such that it has control over critical operations during specific Data Guard state transitions, such as switchovers, failovers, protection mode changes and state changes.

Simplified Browser-based Interface

Administration of a Data Guard configuration can be done through the new streamlined browser-based HTML interface of Enterprise Manager that enables complete standby database lifecycle management. The focus of such streamlined administration is on:

• Ease of use.
• Management based on best practices.
• Pre-built integration with other HA features.

Data Guard Benefits

1. Disaster recovery and high availability
   Data Guard provides an efficient and comprehensive disaster recovery and high availability solution. Automatic failover and easy-to-manage switchover capabilities allow quick role reversals between primary and standby databases, minimizing the downtime of the primary database for planned and unplanned outages. 

1. Complete data protection
   A standby database also provides an effective safeguard against data corruptions and user errors. Storage level physical corruptions on the primary database do not propagate to the standby database. Similarly, logical corruptions or user errors that cause the primary database to be permanently damaged can be resolved. Finally, the redo data is validated at the time it is received at the standby database and further when applied to the standby database. 

1. Efficient utilization of system resources
   A physical standby database can be used for backups and read-only reporting, thereby reducing the primary database workload and saving valuable CPU and I/O cycles. In Oracle Database 10g Release 2, a physical standby database can also be easily converted back and forth between being a physical standby database and an open read/write database. A logical standby database allows its tables to be simultaneously available for read-only access while they are updated from the primary database. A logical standby database also allows users to perform data manipulation operations on tables that are not updated from the primary database. Finally, additional indexes and materialized views can be created in the logical standby database for better reporting performance.

1. Flexibility in data protection to balance availability against performance requirements
   Oracle Data Guard offers the maximum protection, maximum availability, and maximum performance modes to help enterprises balance data availability against system performance requirements.

1. Protection from communication failures
   If network connectivity is lost between the primary and one or more standby databases, redo data cannot be sent from the primary to those standby databases. Once connectivity is re-established, the missing redo data is automatically detected by Data Guard and the necessary archive logs are automatically transmitted to the standby databases. The standby databases are resynchronized with the primary database, with no manual intervention by the administrator. 

1. Centralized and simple management
   Data Guard Broker automates the management and monitoring tasks across the multiple databases in a Data Guard configuration. Administrators may use either Oracle Enterprise Manager or the Broker’s own specialized command-line interface (DGMGRL) to take advantage of this integrated management framework.

Integrated with Oracle databaseData Guard is available as an integrated feature of the Oracle Database (Enterprise Edition) at no extra cost.

Standby database on same machine

The below are 100% practical steps I followed. Just implement them and enjoy “standby database” )
Note: The operating system i used is windows
——————————————————————————–
Assumptions:
A. Oracle version used: 9.x
B. Both the primary and the standby database are in the same machine.
C. The primary database name will be db1 and the secondary database name will be db2
D. ORACLE_HOME=D:\oracle\ora92
E. The drive going to be used (here D) has atleast 1.2 GB free space
Steps:
——————————————————————————–
PRIMARY DATABASE CONFIGURATION
——————————————————————————–
1. Create an initialization parameter file i.e. initdb1.ora with the following entries and place it ORACLE_HOME\database:
##########################################################
*.compatible=’9.2.0.0.0′
*.background_dump_dest=’D:\oracle\ora92\admin\db1\bdump’
*.core_dump_dest=’D:\oracle\ora92\admin\db1\cdump’
*.user_dump_dest=’D:\oracle\ora92\admin\db1\udump’
*.control_files=’d:\oracle\oradata\db1\control01.ctl’,'d:\oracle\oradata\db1\control02.ctl’
*.db_domain=”
*.DB_NAME=’db1′
*.instance_name=’db1′
*.service_names=’db1′
*.db_block_size=8192
*.db_cache_size=33554432
*.db_file_multiblock_read_count=16
*.dispatchers=’(PROTOCOL=TCP) (SERVICE=db1XDB)’
*.java_pool_size=33554432
*.large_pool_size=8388608
*.log_archive_start=TRUE
*.open_cursors=300
*.pga_aggregate_target=25165824
*.processes=200
*.query_rewrite_enabled=’FALSE’
*.sga_max_size=157286400
*.shared_pool_size=50331648
*.sort_area_size=524288
*.timed_statistics=TRUE
*.undo_management=’AUTO’
*.undo_retention=10800
*.undo_tablespace=’UNDOTBS1′
##########################################################
2. Create the folders- bdump, cdump, udump, pfile (under D:\oracle\ora92\admin\db1) and the folder for the control files (d:\oracle\oradata\db1) as mentioned in the initdb1.ora file

3. In the command prompt:
oradim -new -sid db1
set ORACLE_SID=db1
sqlplus /nolog
conn / as sysdba
startup nomount pfile=’D:\oracle\ora92\database\initdb1.ora’

4. Create a database with the name db1. This will be the primary database. In the SQL Plus prompt:

CREATE DATABASE db1
CONTROLFILE REUSE
LOGFILE
GROUP 1 ‘d:\oracle\oradata\db1\LOG01.LOG’ SIZE 5M REUSE,
GROUP 2 ‘d:\oracle\oradata\db1\LOG02.LOG’ SIZE 5M REUSE
DATAFILE ‘d:\oracle\oradata\db1\SYSTEM01.DBF’ SIZE 400M REUSE
DEFAULT TEMPORARY TABLESPACE TEMP
TEMPFILE ‘d:\oracle\oradata\db1\TEMP01.DBF’ SIZE 50M REUSE
UNDO TABLESPACE UNDOTBS1
DATAFILE ‘d:\oracle\oradata\db1\UNDO01.DBF’ SIZE 50M REUSE

5. In the SQL Plus prompt:

ALTER DATABASE FORCE LOGGING;

6. Run catalog.sql and cataproc.sql

7. CONN SYSTEM/MANAGER and run pupbld.sql

8. Execute the following:

CONN / as SYSDBA
SHUTDOWN IMMEDIATE

9. In the listener.ora file make the following entry:

(SID_DESC =
(GLOBAL_DBNAME = db1)
(ORACLE_HOME = D:\oracle\ora92)
(SID_NAME = db1)
)

10. In the tnsnames.ora file make the following entry (change the host name appropriately):

db1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.24.43.151)(PORT = 1521))
(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = db1)
)
)

11. In the initdb1.ora file set

*.REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
*.log_archive_dest_1=’LOCATION=D:\oracle\oradata\db1 MANDATORY’

12. In the command promt run: ORAPWD FILE=D:\oracle\ora92\database\pwddb1.ora PASSWORD=DB1 ENTRIES=5

13. In the SQL Plus prompt:

CONN / AS SYSDBA
STARTUP MOUNT
ALTER DATABASE ARCHIVELOG;
CREATE SPFILE FROM PFILE;
ALTER DATABASE OPEN;
ALTER DATABASE CREATE STANDBY CONTROLFILE AS ‘D:\oracle\oradata\db1\STBY_DB1.CTL’;
##########################################################
############Configuring the Standby Database:#############
##########################################################

1. Shutdown the primary database:

SHUTDOWN IMMEDIATE

2. Create the folders- bdump, cdump, udump, pfile (under D:\oracle\ora92\admin\db2) and the folder for the control files (d:\oracle\oradata\db2) as mentioned.

3. Create an initialization parameter file i.e. initdb2.ora with the following entries and place it ORACLE_HOME\database:

##########################################################
*.compatible=’9.2.0.0.0′
*.db_block_size=8192
*.db_cache_size=33554432
*.db_domain=”
*.db_file_multiblock_read_count=16
*.dispatchers=’(PROTOCOL=TCP) (SERVICE=db2XDB)’
*.java_pool_size=33554432
*.large_pool_size=8388608
*.log_archive_dest_1=”
*.log_archive_start=TRUE
*.open_cursors=300
*.pga_aggregate_target=25165824
*.processes=200
*.query_rewrite_enabled=’FALSE’
*.sga_max_size=157286400
*.shared_pool_size=50331648
*.sort_area_size=524288
*.timed_statistics=TRUE
*.undo_management=’AUTO’
*.undo_retention=10800
*.undo_tablespace=’UNDOTBS1′
*.REMOTE_LOGIN_PASSWORDFILE=’EXCLUSIVE’
*.background_dump_dest=’D:\oracle\ora92\admin\db2\bdump’
*.core_dump_dest=’D:\oracle\ora92\admin\db2\cdump’
*.user_dump_dest=’D:\oracle\ora92\admin\db2\udump’
*.DB_FILE_NAME_CONVERT=’D:\oracle\oradata\db1\’,'D:\oracle\oradata\db2\’
*.DB_NAME=’db1′
*.instance_name=’db2′
*.service_names=’db2′
*.FAL_CLIENT=’db2′
*.FAL_SERVER=’db1′
*.LOCK_NAME_SPACE=’db2′
*.LOG_FILE_NAME_CONVERT=’D:\oracle\oradata\db1\’,'D:\oracle\oradata\db2\’
*.STANDBY_ARCHIVE_DEST=’D:\oracle\oradata\db2′
*.log_archive_dest=’D:\oracle\oradata\db2′
*.control_files=’D:\oracle\oradata\db2\STBY_DB1.CTL’
##########################################################
4. Copy all datafiles and controlfiles of db1 as shown below to the location D:\oracle\oradata\db2.should be there:
D:\oracle\oradata\db1\LOG02.LOG
D:\oracle\oradata\db1\LOG01.LOG
D:\oracle\oradata\db1\STBY_DB1.CTL
D:\oracle\oradata\db1\SYSTEM01.DBF
D:\oracle\oradata\db1\TEMP01.DBF
D:\oracle\oradata\db1\UNDO01.DBF

5. In the command prompt:

oradim -new -sid db2
set ORACLE_SID=db2
orapwd FILE=D:\oracle\ora92\database\pwddb2.ora PASSWORD=db2 ENTRIES=5
sqlplus /nolog
conn / as sysdba
startup nomount pfile=’D:\oracle\ora92\database\INITdb2.ORA’;
ALTER DATABASE MOUNT STANDBY DATABASE;
CREATE SPFILE FROM PFILE;

6. Startup the primary database and then change the following parameter in the spfile:

conn / as sysdba
startup OPEN
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2=’SERVICE=db2 MANDATORY REOPEN=60′ SCOPE=SPFILE;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=’ENABLE’ SCOPE=SPFILE;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=’ENABLE’ SCOPE=SPFILE;
ALTER SYSTEM SET LOG_ARCHIVE_MIN_SUCCEED_DEST=2 SCOPE=SPFILE;

7. In the listener.ora file add the following entry and reload the listener:

(SID_DESC =
(GLOBAL_DBNAME = db2)
(ORACLE_HOME = D:\oracle\ora92)
(SID_NAME = db2)
)

8. In the tnsnames.ora file add the following entry (change the host name appropriately):

db2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.24.43.151)(PORT = 1521))
(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = db2)
)
)

9. In the command prompt, shutdown and Startup the primary database:

SHUTDOWN IMMEDIATE;
STARTUP OPEN;
CREATE PFILE FROM SPFILE;

10. In the command prompt opened for standby database (step-5):

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

11. You can check the status of the standby database by:

select process,status,sequence#,thread#,block#,blocks from v$managed_standby;

#########Verifying Standby Database is working:###########

1. Create an user in the primary database and create a table in it:
create user test identified by test;
grant connect, resource to test;
create table test.temp
(name varchar2(10),
value number(3));
insert into test.temp values(‘a’,100);
insert into test.temp values(‘b’,200);
insert into test.temp values(‘c’,300);
commit;
alter system archive log current;

2. Cancel the recovery mode of the standby database and open it in read only mode:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE OPEN READ ONLY;
SELECT * FROM TEST.TEMP;

If you are able to see the records inserted in the temp table under test schema in the primary database in this secondary database your standby is working fine.
-=========================
USEFULL QUERIES
——————————————————————————–
SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#; –IN PRIMARY DB

SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#; –IN STANDBY DB

select status, error from v$archive_dest where dest_id=2; –BOTH

SELECT process, SEQUENCE#, block#, thread#,status FROM V$managed_standby order by SEQUENCE#;

######Switchover of primary and secondary databases#######

db1 will be standby
db2 will be primary

Before going through the following steps make a backup copy of the following files of both db1 and db2:

1. Initialization parameter file.
2. Control file.
——————————————————————————–

STEPS:

Change the old primary to secondary

——————————————————————————–

1. Open the primary database (db1).

2. Open the secondary database (db2) in read only mode.

3. In primary execute:

Select max (sequence#) from v$archived_log;

4. In secondary execute:

Select max (al.sequence#) “Last Seq Recieved”, max(lh.sequence#) “Last Seq Applied”
from v$archived_log al, v$log_history lh;

The results of the queries fired in step-3 and 4 should be identical

5. In initialization parameter file of db1:

Comment out the existing control_files parameter as following:

#*.control_files=’d:\oracle\oradata\db1\control01.ctl’,'d:\oracle\oradata\db1\control02.ctl’

Add the following parameters:

*.FAL_CLIENT=’db1′
*.FAL_SERVER=’db2′
*.control_files=’D:\oracle\oradata\db1\STBY_DB1.CTL’
*.STANDBY_ARCHIVE_DEST=’D:\oracle\oradata\db1\dest_1′

6. Change the primary database (db1) to secondary as below:

alter database commit to switchover to standby with session shutdown;

shutdown immediate;

7. Startup the old primary database (db1) as secondary:

startup nomount pfile=’D:\oracle\ora92\database\initdb1.ora’

alter database mount standby database;

8. Start log apply process:

alter database recover managed standby database disconnect from session;

9. Re-create the spfile:

create spfile from pfile;

10. Reload the listener using the lsnrctl utility.

Change the old secondary to primary
——————————————————————————–

1. Execute the following statement in the old secondary database (db2):

alter database commit to switchover to primary;

If asked for recovery, then recover it using:

recover managed standby database;

If recovered then again execute:

alter database commit to switchover to primary;

2. Shutdown the old secondary database (db2):

shutdown immediate;

3. Make the following changes to the initdb2.ora file:

comment out the following lines as:

#*.FAL_CLIENT=’db2′
#*.FAL_SERVER=’db1′

#*.STANDBY_ARCHIVE_DEST=’D:\oracle\oradata\db2′
#*.log_archive_dest=’D:\oracle\oradata\db2′
4. Add the following lines:

*.log_archive_dest_1=’LOCATION=D:\oracle\oradata\db2 MANDATORY’
*.log_archive_dest_2=’SERVICE=db1 MANDATORY REOPEN=60′
*.log_archive_dest_state_1=’ENABLE’
*.log_archive_dest_state_2=’ENABLE’
*.log_archive_min_succeed_dest=2

5. Startup the new primary database (db2):

startup open pfile=D:\oracle\ora92\database\INITdb2.ORA;

6. Reload the listener using the lsnrctl utility.
Now you can verify the role of the new primary(db2) and secondary(db1) in exactly the same way
you did it while testing earlier by making some changes in the “test” table.
The new secondary (db1) may ask recovery.

##########################################################
##########################################################

######Switchback of primary and secondary databases#######

##########################################################
##########################################################

——————————————————————————–

db2 will be standby
db1 will be primary
STEPS:

Change the database (db2) from primary to secondary

——————————————————————————–

1. Open the primary database (db2).

2. Open the secondary database (db1) in read only mode.

3. In primary execute:

Select max (sequence#) from v$archived_log;

4. In secondary execute:

Select max (al.sequence#) “Last Seq Recieved”, max(lh.sequence#) “Last Seq Applied”
from v$archived_log al, v$log_history lh;

The results of the queries fired in step-3 and 4 should be identical

5. In initialization parameter file of db2 uncomment the following lines which were commented:

#*.FAL_CLIENT=’db2′
#*.FAL_SERVER=’db1′
#*.STANDBY_ARCHIVE_DEST=’D:\oracle\oradata\db2′
#*.log_archive_dest=’D:\oracle\oradata\db2′

and remove the following parameters:

*.log_archive_dest_1=’LOCATION=D:\oracle\oradata\db2 MANDATORY’
*.log_archive_dest_2=’SERVICE=db1 MANDATORY REOPEN=60′
*.log_archive_dest_state_1=’ENABLE’
*.log_archive_dest_state_2=’ENABLE’
*.log_archive_min_succeed_dest=2

6. Change the primary database (db2) to secondary as below:

alter database commit to switchover to standby with session shutdown;

shutdown immediate;

7. Startup the database (db2) as secondary:

startup nomount pfile=’D:\oracle\ora92\database\initdb2.ora’

alter database mount standby database;

8. Start log apply process:

alter database recover managed standby database disconnect from session;

9. Re-create the spfile:

create spfile from pfile;

Change the database (db1) from secondary to primary

——————————————————————————–

1. In initialization parameter file of db1:

The following lines should be there:

*.control_files=’D:\oracle\oradata\db1\STBY_DB1.CTL’
*.STANDBY_ARCHIVE_DEST=’D:\oracle\oradata\db1\dest_1′

and remove/comment the following lines:

*.FAL_CLIENT=’db1′
*.FAL_SERVER=’db2′

2. Change the secondary database (db1) to primary as below:

alter database commit to switchover to primary;

If asked for recovery, then recover it using:

recover managed standby database;

If recovered then again execute:

alter database commit to switchover to primary;

3. Shutdown the database (db1):

shutdown immediate;

4. Startup the primary database (db1):

startup open pfile=’D:\oracle\ora92\database\initdb1.ora’

5. Re-create the spfile:

create spfile from pfile;

6. Verify the roles of the primary and standby database by making some change in the

##########################################################
##########################################################

############Failover to secondary databases###############

##########################################################
##########################################################

Failover is performed when the primary is completely crashed
and there is no option other then activating the standby as
primary.

——————————————————————————–

1. Comment/remove the following lines from the initialization file of db2 i.e. initdb2.ora

*.FAL_CLIENT=’db2′
*.FAL_SERVER=’db1′

2. Execute the following:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

ALTER DATABASE ACTIVATE STANDBY DATABASE;

SHUTDOWN IMMEDIATE;

3. Startup and open the database with the updated initialization file:

startup open pfile=’D:\oracle\ora92\database\initdb2.ora’

4. Recreate the spfile

CREATE SPFILE FROM PFILE;

NOTE: ONCE THE DATABASE IS FAILEDOVER WITH THE “ALTER….FINISH” STATEMENT
IT CANNOT BE ROLLEDBACK TO PREVIOUS STAGE OF STANDBY BECAUSE THE LOG SEQUENCE
NUMBERS WILL BE RESET.