Data Guard Benefits

Data Guard offers these benefits:

• Disaster recovery, data protection, and high availability
  Data Guard provides an efficient and comprehensive disaster recovery and high availability solution. Easy-to-manage switchover and failover capabilities allow role reversals between primary and standby databases, minimizing the downtime of the primary database for planned and unplanned outages.
  
• Complete data protection
  Data Guard can ensure zero data loss, even in the face of unforeseen disasters. A standby database provides a safeguard against data corruption and user errors. Because the redo data received from a primary database is validated at a standby database, 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.
  
• Efficient use of system resources
  The standby database tables that are updated with redo data received from the primary database can be used for other tasks such as backups, reporting, summations, and queries, thereby reducing the primary database workload necessary to perform these tasks, saving valuable CPU and I/O cycles.
  
• Flexibility in data protection to balance availability against performance requirements
  Oracle Data Guard offers maximum protection, maximum availability, and maximum performance modes to help enterprises balance data availability against system performance requirements.
  
• Automatic gap detection and resolution
  If connectivity is lost between the primary and one or more standby databases (for example, due to network problems), redo data being generated on the primary database cannot be sent to those standby databases. Once a connection is reestablished, the missing archived redo log files (referred to as a gap) are automatically detected by Data Guard, which then automatically transmits the missing archived redo log files to the standby databases. The standby databases are synchronized with the primary database, without manual intervention by the DBA.
  
• Centralized and simple management
  The Data Guard broker provides a graphical user interface and a command-line interface to automate management and operational tasks across multiple databases in a Data Guard configuration. The broker also monitors all of the systems within a single Data Guard configuration.
  
• Integration with Oracle Database
  Data Guard is a feature of Oracle Database Enterprise Edition and does not require separate installation.
  
• Automatic role transitions
  When fast-start failover is enabled, the Data Guard broker automatically fails over to a synchronized standby site in the event of a disaster at the primary site, requiring no intervention by the DBA. In addition, applications are automatically notified of the role transition.
  

Data Guard Benefits

Data Guard Benefits

Data Guard offers these benefits:

• Disaster recovery, data protection, and high availability
  Data Guard provides an efficient and comprehensive disaster recovery and high availability solution. Easy-to-manage switchover and failover capabilities allow role reversals between primary and standby databases, minimizing the downtime of the primary database for planned and unplanned outages.
  
• Complete data protection
  Data Guard can ensure zero data loss, even in the face of unforeseen disasters. A standby database provides a safeguard against data corruption and user errors. Because the redo data received from a primary database is validated at a standby database, 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.
  
• Efficient use of system resources
  The standby database tables that are updated with redo data received from the primary database can be used for other tasks such as backups, reporting, summations, and queries, thereby reducing the primary database workload necessary to perform these tasks, saving valuable CPU and I/O cycles.
  
• Flexibility in data protection to balance availability against performance requirements
  Oracle Data Guard offers maximum protection, maximum availability, and maximum performance modes to help enterprises balance data availability against system performance requirements.
  
• Automatic gap detection and resolution
  If connectivity is lost between the primary and one or more standby databases (for example, due to network problems), redo data being generated on the primary database cannot be sent to those standby databases. Once a connection is reestablished, the missing archived redo log files (referred to as a gap) are automatically detected by Data Guard, which then automatically transmits the missing archived redo log files to the standby databases. The standby databases are synchronized with the primary database, without manual intervention by the DBA.
  
• Centralized and simple management
  The Data Guard broker provides a graphical user interface and a command-line interface to automate management and operational tasks across multiple databases in a Data Guard configuration. The broker also monitors all of the systems within a single Data Guard configuration.
  
• Integration with Oracle Database
  Data Guard is a feature of Oracle Database Enterprise Edition and does not require separate installation.
  
• Automatic role transitions
  When fast-start failover is enabled, the Data Guard broker automatically fails over to a synchronized standby site in the event of a disaster at the primary site, requiring no intervention by the DBA. In addition, applications are automatically notified of the role transition.
  

Data Guard Configurations

Data Guard Configurations

A Data Guard configuration consists of one production database and one or more 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 they can communicate with each other. For example, you can have a standby database on the same system as the production database, along with two standby databases on other systems at remote locations.
You can manage primary and standby databases using the SQL command-line interfaces or the Data Guard broker interfaces, including a command-line interface (DGMGRL) and a graphical user interface that is integrated in Oracle Enterprise Manager.

1.1.1 Primary Database

A Data Guard configuration contains one production database, also referred to as the primary database, that functions in the primary role. This is the database that is accessed by most of your applications.
The primary database can be either a single-instance Oracle database or an Oracle Real Application Clusters (Oracle RAC) database.

1.1.2 Standby Databases

A standby database is a transactionally consistent copy of the primary database. Using a backup copy of the primary database, you can create up to thirty standby databases and incorporate them in a Data Guard configuration. Once created, Data Guard automatically maintains each standby database by transmitting redo data from the primary database and then applying the redo to the standby database.
Similar to a primary database, a standby database can be either a single-instance Oracle database or an Oracle RAC database.
The types of standby databases are as follows:

• 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 schema, including indexes, are the same. A physical standby database is kept synchronized with the primary database, through Redo Apply, which recovers the redo data received from the primary database and applies the redo to the physical standby database.
  As of Oracle Database 11g release 1 (11.1), a physical standby database can receive and apply redo while it is open for read-only access. A physical standby database can therefore be used concurrently for data protection and reporting.
  
• 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 logical standby database is kept synchronized with the primary database through SQL Apply, which transforms the data in the redo received from the primary database into SQL statements and then executes the SQL statements on the standby database.
  A logical standby database can be used for other business purposes in addition to disaster recovery requirements. This allows users to access a logical standby database for queries and reporting purposes at any time. Also, using a logical standby database, you can upgrade Oracle Database software and patch sets with almost no downtime. Thus, a logical standby database can be used concurrently for data protection, reporting, and database upgrades.
  
• Snapshot Standby Database
  A snapshot standby database is a fully updatable standby database.
  Like a physical or logical standby database, a snapshot standby database receives and archives redo data from a primary database. Unlike a physical or logical standby database, a snapshot standby database does not apply the redo data that it receives. The redo data received by a snapshot standby database is not applied until the snapshot standby is converted back into a physical standby database, after first discarding any local updates made to the snapshot standby database.
  A snapshot standby database is best used in scenarios that require a temporary, updatable snapshot of a physical standby database. Note that because redo data received by a snapshot standby database is not applied until it is converted back into a physical standby, the time needed to recover from a primary database failure is directly proportional to the amount of redo data that needs to be applied.
  

1.1.3 Configuration Example

Figure 1-1 shows a typical Data Guard configuration that contains a primary database that transmits redo data to a standby database. The standby database is remotely located from the primary database for disaster recovery and backup operations. You can configure the standby database at the same location as the primary database. However, for disaster recovery purposes, Oracle recommends you configure standby databases at remote locations.

Figure 1-1 Typical Data Guard Configuration

This illustration shows a Data Guard configuration consisting of a primary database and a physical standby database.
From the primary database, redo is being transmitted and applied to the standby database. Log apply services apply the redo out of the standby redo log files to the standby database.

Oracle Data-Guard

 Oracle Data Guard

Oracle Data Guard ensures high availability, data protection, and disaster recovery for enterprise data. Data Guard provides a comprehensive set of services that create, maintain, manage, and monitor one or more standby databases to enable production Oracle databases to survive disasters and data corruptions. Data Guard maintains these standby databases as copies of the production database. Then, 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, minimizing the downtime associated with the outage. Data Guard can be used with traditional backup, restoration, and cluster techniques to provide a high level of data protection and data availability.
With Data Guard, administrators can optionally improve production database performance by offloading resource-intensive backup and reporting operations to standby systems.

Utility / Math related queries

 Utility / Math related queries

Convert number to words SELECT TO_CHAR (TO_DATE (1526, 'j'), 'jsp') FROM DUAL; Output: one thousand five hundred twenty-six Find string in package source code Below query will search for string ‘FOO_SOMETHING’ in all package source. This query comes handy when you want to find a particular procedure or function call from all the source code. --search a string foo_something in package source code SELECT *   FROM dba_source  WHERE UPPER (text) LIKE '%FOO_SOMETHING%' AND owner = 'USER_NAME'; Convert Comma Separated Values into Table The query can come quite handy when you have comma separated data string that you need to convert into table so that you can use other SQL queries like IN or NOT IN. Here we are converting ‘AA,BB,CC,DD,EE,FF’ string to table containing AA, BB, CC etc. as each row. Once you have this table you can join it with other table to quickly do some useful stuffs. WITH csv      AS (SELECT 'AA,BB,CC,DD,EE,FF'                    AS csvdata            FROM DUAL)     SELECT REGEXP_SUBSTR (csv.csvdata, '[^,]+', 1, LEVEL) pivot_char       FROM DUAL, csv CONNECT BY REGEXP_SUBSTR (csv.csvdata,'[^,]+', 1, LEVEL) IS NOT NULL; Find the last record from a table This ones straight forward. Use this when your table does not have primary key or you cannot be sure if record having max primary key is the latest one. SELECT *   FROM employees  WHERE ROWID IN (SELECT MAX (ROWID) FROM employees); (OR) SELECT * FROM employees MINUS SELECT *   FROM employees  WHERE ROWNUM < (SELECT COUNT (*) FROM employees); Row Data Multiplication in Oracle WITH tbl      AS (SELECT -2 num FROM DUAL          UNION          SELECT -3 num FROM DUAL          UNION          SELECT -4 num FROM DUAL),      sign_val      AS (SELECT CASE MOD (COUNT (*), 2) WHEN 0 THEN 1 ELSE -1 END val            FROM tbl           WHERE num < 0)   SELECT EXP (SUM (LN (ABS (num)))) * val     FROM tbl, sign_val GROUP BY val; Generating Random Data In Oracle SELECT LEVEL empl_id,            MOD (ROWNUM, 50000) dept_id,            TRUNC (DBMS_RANDOM.VALUE (1000, 500000), 2) salary,            DECODE (ROUND (DBMS_RANDOM.VALUE (1, 2)),  1, 'M',  2, 'F') gender,            TO_DATE (                  ROUND (DBMS_RANDOM.VALUE (1, 28))               || '-'               || ROUND (DBMS_RANDOM.VALUE (1, 12))               || '-'               || ROUND (DBMS_RANDOM.VALUE (1900, 2010)),               'DD-MM-YYYY')               dob,            DBMS_RANDOM.STRING ('x', DBMS_RANDOM.VALUE (20, 50)) address       FROM DUAL CONNECT BY LEVEL < 10000; Random number generator in Oracle Plain old random number generator in Oracle. This ones generate a random number between 0 and 100. Change the multiplier to number that you want to set limit for. --generate random number between 0 and 100 SELECT ROUND (DBMS_RANDOM.VALUE () * 100) + 1 AS random_num FROM DUAL; Check if table contains any data This one can be written in multiple ways. You can create count(*) on a table to know number of rows. But this query is more efficient given the fact that we are only interested in knowing if table has any data. SELECT 1   FROM TABLE_NAME  WHERE ROWNUM = 1;