Dividing the Backup of a Large Datafile into Sections

If you specify the SECTION SIZE parameter on the BACKUP command, then RMAN creates a backup set in which each backup piece contains the blocks from one file section. A file section is a contiguous range of blocks in a file. This type of backup is called a multi-section backup.

Note:
You cannot specify SECTION SIZE in conjunction with MAXPIECESIZE.
The purpose of multisection backups is to enable RMAN channels to back up a single large file in parallel. RMAN divides the work among multiple channels, with each channel backing up one file section in a file. Backing up a file in separate sections can improve the performance of backups of large datafiles.

If a multisection backup completes successfully, then none of the backup sets generated during the backup contain a partial datafile. If a multisection backup is unsuccessful, then it is possible for the RMAN metadata to contain a record for a partial backup set. RMAN does not consider partial backups for restore and recovery. You must use the DELETE command to delete the partial backup set.

If you specify a section size that is larger than the size of the file, then RMAN does not use multisection backup for the file. If you specify a small section size that would produce more than 256 sections, then RMAN increases the section size to a value that results in exactly 256 sections.

To make a multisection backup:

Start RMAN and connect to a target database and recovery catalog (if used).

If necessary, configure channel parallelism so that RMAN can parallelize the backup.

Execute BACKUP with the SECTION SIZE parameter.

For example, suppose that the users tablespace contains a single datafile of 900 MB. Also assume that three SBT channels are configured, with the parallelism setting for the SBT device set to 3. You can break up the datafile in this tablespace into file sections as shown in the following example:

BACKUP
  SECTION SIZE 300M
  TABLESPACE users; 

In this example, each of the three SBT channels backs up a 300 MB file section of the users datafile.

RMAN script to take full database backup

RMAN script to take full database backup

Before taking a backup using RMAN , First we have to decide which type of backup you want to take. You can take online or offline backup using RMAN. For online backups, database archiving must be turned ON. If archiving of you database is not ON, database must be in MOUNT stage to take backup using RMAN. Oracle recommends you to turn ON archiving to protect your data. This example is tested with online backup option.

 First step is to start RMAN, click Start->Run and type RMAN and press Enter key.
 Second step is to connect RMAN to database. In this example, RMAN metadata will be saved in control

 file of target database. Now, issue following commands to connect database.
  RMAN> Connect target /
  Here note down DBID of database. This DBID will be used in recovery process
Third step is to Create a directory name as “D:\RMANBACKUP”. Now type following script to take full database backup.

RMAN> run{
2> configure controlfile autobackup format for device type disk to 'd:/rmanbackup/%F';
3> configure controlfile autobackup on;
4> allocate channel d1 type disk;
5> backup tag FULL_DB format 'd:/rmanbackup/db_%t_%s.bk' (database);
6> release channel d1;
7> }
After the procedure is competed, you will find backupup files in “D:\RMANBACKUP” directory.These backup files can be used to recover any datafile or all datafiles, control file or SPFILE.

In production database, you will have several backups. What if you want to restore a particular backup? To handle this issue, RMAN supports a TAG with each backup. In above script tag FULL_DB is used at line 3. At the time of recovery this tag can be used to restore a particular backup.
Following is the snapshot of all above process.

When do I use Active Data Guard and GoldenGate Together

When do I use Active Data Guard and GoldenGate Together

Active Data Guard and GoldenGate are not mutually exclusive. The following examples of high availability architectures that include the simultaneous use of both technologies:

• An Active Data Guard standby is utilized for disaster protection and database rolling upgrades for a mission critical OLTP database. GoldenGate is used to extract data from the Data Guard primary database (or from the standby database using GoldenGate ALO mode) for ETL update of an enterprise data warehouse.
• GoldenGate subset replication is used extract, transforms, and aggregate data from numerous data sources into a central operational data store (ODS). The ODS supports mission critical application systems that generate significant revenue for the company. An Active Data Guard standby database is used to protect the ODS, providing optimal data protection and availability.
• GoldenGate multi-master replication is utilized to synchronize several databases, each located in different geographies. Each GoldenGate copy has its own local synchronous Data Guard standby database that enables zero data loss failover should an outage occur. GoldenGate capture and apply processes are easily restarted on the new primary database following a failover since primary and standby are an exact, up-to-date replica of each other.
• Reducing planned downtime for various planned maintenance activities not supported by Data Guard – such as cross-endian platform migration or application upgrades that modify back-end database objects. In many cases customers wish to isolate a current mission critical production environment having a Data Guard primary and standby database from being impacted by the maintenance activity. A parallel environment (new primary and standby) is deployed on the new release or new platform and GoldenGate one-way or bi-directional replication is used to keep old and new environments synchronized. Production is moved to the new environment once sufficiently tested. GoldenGate can continue to replicate to the previous environment for fast fall-back until the stability of the new version is no longer in question, at which time the old environment can be de-commissioned.

More Links :

Oracle PL/SQL Interview Questions       When Use ADG (Active Data Guard)

Oracle GoldenGate    Oracle Active Data Guard and Oracle GoldenGate
GO TO ORACLE Technology Network

When do I use Oracle Golden-Gate

When do I use Oracle GoldenGate?

Use Oracle GoldenGate when a replica database must be open read-write while replication is active, or for advanced replication requirements beyond what is addressed by Active Data Guard:

• Any requirement where the replica database must be open read-write while synchronizing with the primary database
• Any advanced replication requirements such as: multimaster and bidirectional replication, subset replication, many to one replication, cross endian replication, and data transformations
• Maintenance and migrations where zero downtime using bi-directional replication is required
• Application upgrades that modify back-end database objects. GoldenGate maintains availability and reduces planned downtime by replicating between old and new versions of the database (the user implements mapping between differences in database objects in old and new versions of the database
• Any cross platform migration not supported by Data Guard (e.g. cross endian platform migration)
• Any replication requirement where you replicate from a more recent version of Oracle Database to an earlier version of Oracle Database (e.g. from Oracle Database 11g to Oracle Database 10g)

More Links :

Oracle PL/SQL Interview Questions       When Use ADG (Active Data Guard)

Oracle GoldenGate    Oracle Active Data Guard and Oracle GoldenGate

When do I use Oracle Active Data Guard?

100$ Question,When do I use Oracle Active Data Guard?

Use Active Data Guard where emphasis is on simplicity, best data protection, data availability and highest performance:

• Secure physical replication, standby database is open read-only, it is impossible to modify standby data independent of primary transactions
• Simplest, fastest, one-way replication of a complete Oracle Database. No requirements for supplemental logging or performance implications for tables having no primary key or unique index. Little if any performance tuning required at the standby database, the default configuration handles most workloads.
• No restrictions - Oracle Data Guard Redo Apply supports all Oracle features and transparently replicates all data and storage types, PL/SQL packages and DDL without special considerations
• Best data protection – Replication direct from memory isolates the standby from I/O corruption that can occur at the primary database. Detect silent lost-write corruption that can occur independently on primary or standby. Automatically detect and repair physical block corruption that can occur independently on primary or standby.
• Choice of synchronous with zero data loss, or asynchronous with near-zero data loss protection
• Simple to improve ROI by offloading read-only workload and/or backups to a synchronized physical standby
• Transparency of backups - an Oracle Data Guard primary and standby are physically exact copies of each other, RMAN backups are completely interchangeable
• Minimize planned downtime and risk using Data Guard standby; standby first patching, database rolling upgrades, and select platform migrations (see My Oracle Support Note 413484.1 for mixed platform combinations)
• A single command will convert a physical standby database as a test system open read-write. A second command will convert it back to a physical standby database and resynchronize it with the primary; primary data is protected at all times.
• Integrated management of a complete configuration - Oracle Data Guard Broker command line or Oracle Enterprise Manager Cloud Control, integrated automatic database and client failover

Active Data Guard is a superset of Data Guard capabilities included with Oracle Enterprise Edition and can be purchased as the Active Data Guard Option for Oracle Database Enterprise Edition. It is also included with every Oracle GoldenGate license, offering customers the ability to acquire the complete set of advanced Oracle replication capabilities with a single purchase. Basic Data Guard functionality does not require a separate license, it is included with Oracle Enterprise Edition.

More Links :

Oracle PL/SQL Interview Questions               Oracle GoldenGate 
When Use Oracle GoldenGate

Oracle ADG (Active Data Guard) and Oracle GoldenGate

Oracle ADG (Active Data Guard) and Oracle GoldenGate

Oracle Active Data Guard and Oracle Golden Gate are strategic capabilities within Oracle's software portfolio. While they generally fall into the category of replication technologies, each has a very different area of focus.
Oracle Active Data Guard provides the best data protection and availability for Oracle Database in the simplest most economical manner by maintaining an exact physical replica of the production copy at a remote location that is open read-only while replication is active. Active Data Guard eliminates compromise inherent in storage remote-mirroring or logical replication solutions though deep integration with Oracle Database and through the simplicity achieved by complete focus on providing real-time data protection and availability for Oracle data.
GoldenGate is an advanced logical replication product that supports multi-master replication, hub and spoke deployment and data transformation, providing customers very flexible options to address the complete range of replication requirements. GoldenGate also supports replication between a broad range of heterogeneous hardware platforms and database management systems. Unlike Active Data Guard, GoldenGate captures primary database changes by reading redo records from disk, transforming those records into a platform independent trail file format, and transmitting the trail file to the target database. GoldenGate maintains a logical replica by converting the trail file into SQL and applying SQL to the target database. The target database is open read-write while synchronization occurs.

More Links :
Oracle Golden-Gate
Oracle PL/SQL Interview Questions

Oracle GoldenGate

Oracle GoldenGate

Oracle GoldenGate is a comprehensive software package for real-time data integration and replication in heterogeneous IT environments. The product set enables high availability solutions, real-time data integration, transactional change data capture, data replication, transformations, and verification between operational and analytical enterprise systems. Oracle GoldenGate 12c brings extreme performance with simplified configuration and management, tighter integration with Oracle Database, support for cloud environments, expanded heterogeneity, and enhanced security.

In addition to the Oracle GoldenGate core platform for real-time data movement, Oracle provides the Management Pack for Oracle GoldenGate—a visual management and monitoring solution for Oracle GoldenGate deployments—as well as Oracle GoldenGate Veridata, which allows high-speed, high-volume comparison between two in-use databases.

To Know More CLICK HERE

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;