Share the content if you found it is useful (You can share using 300 community websites) click "share" at the end of the post.

You are encouraged to leave a comment.

Friday, December 26, 2008

Dataguard: Physical Standby Database

In this post I described in detail the way in which we can configure a Dataguard for Oracle Databases. Here the instance is of 11g.

Oracle Database 11g RMAN introduces the FROM ACTIVE DATABASE capability to the DUPLICATE FOR STANDBY command. This alleviates the previous need for interim storage on both the Primary and Standby systems, and the limitation of single stream network traffic.

Now when taking the backup of the Primary database you can simultaneously create and restore the standby database over the network in parallel streams. Apart from some simple Oracle Net setup, and creating a couple of directories and an interim password file, the whole standby creation can be done in one RMAN script.

RMAN will automatically copy the server parameter file to the standby host, start the auxiliary instance with the server parameter file, restore a backup control file, and copy all necessary database files and archived redo logs over the network to the standby host.



Perform an Oracle Database 11g installation (software and db installed) for a single server environment OR Oracle Database 11g installation (software and db installed) on 1 server and Oracle Database 11g software only on 2nd server for a dual server environment.

Please Save the following script as we need to run during this process.

1. Script for RMAN:

run {
allocate channel prmy1 type disk;

allocate channel prmy2 type disk;

allocate channel prmy3 type disk;

allocate channel prmy4 type disk;

allocate auxiliary channel stby type disk;

duplicate target database for standby from active database


parameter_value_convert 'orcl','orclsby1'

set db_unique_name='orclsby1'

set db_file_name_convert='/orcl/','/orclsby1/'

set log_file_name_convert='/orcl/','/orclsby1/'

set control_files='/u01/app/oracle/oradata/orclsby1.ctl'

set log_archive_max_processes='5'

set fal_client='orclsby1'

set fal_server='orcl'

set standby_file_management='AUTO'

set log_archive_config='dg_config=(orcl,orclsby1)'

set log_archive_dest_1='service=orcl ASYNC
valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=orcl'


2. Create Standby logfile (cr_sby_redologs.sql)

alter database add standby logfile
size 52428800


3. Query for Archived Logs (query_archived_log.sql)

SELECT sequence#, first_time, next_time

FROM v$archived_log
ORDER BY sequence#

Preparing the Primary Database for Standby Database Creation

You verify that the primary database is configured correctly to support a physical standby database.

You only need to perform these preparatory tasks once. After you complete these steps, the database is prepared to serve as the primary database for one or more standby databases. You should perform the following steps:


Determine if FORCE LOGGING is enabled. If it is not enabled, enable FORCE LOGGING mode. This statement may take some time to complete, because it waits for all unlogged direct write I/O to finish. You use the following commands:




Configure redo transport authentication.

Data Guard uses Oracle Net sessions to transport redo data and control messages between the members of a Data Guard configuration. These redo transport sessions are authenticated using either the Secure Sockets Layer (SSL) protocol or a remote login password file.

In this blog, we will be using a remote login password file which will be created in a subsequent step.


Configure the primary database to receive redo data, by adding the standby logfiles to the primary. You can use the cr_sby_redologs.sql script, after validating the directory paths reflect your environment.

It is highly recommended that you have one more standby redo log group than you have online redo log groups as the primary database. The files must be the same size or larger than the primary database’s online redo logs.

SQL> alter database add standby logfile
2 '/u01/app/oracle/oradata/orcl/srl01.log' size 52428800
3 /
Database altered.
SQL> alter database add standby logfile
2 '/u01/app/oracle/oradata/orcl/srl02.log' size 52428800
3 /
Database altered.
SQL> alter database add standby logfile
2 '/u01/app/oracle/oradata/orcl/srl03.log' size 52428800
3 /
Database altered.
SQL> alter database add standby logfile
2 '/u01/app/oracle/oradata/orcl/srl04.log' size 52428800
3 /
Database altered.


Set primary database initialization parameters

On the primary database, you define initialization parameters that control redo transport services while the database is in the primary role. You use SQL commands similar to the following to verify these settings:

Ensure the following parameters are set to reflect the Data Guard configuration.

DB_NAME Specifies the database name. Must be orcl.
DB_UNIQUE_NAME Specify a unique name for each database. Does not change even if DG roles change. Must be orcl.
CONTROL_FILES Specifies the local path name for the control files on the primary database.
LOG_ARCHIVE_CONFIG Uses the DG_CONFIG attribute to list the DB_UNIQUE_NAME of the primary and standby databases.
LOG_ARCHIVE_DEST_1 Defaults to archive destination for the local archived redo log files.
LOG_ARCHIVE_DEST_2 Valid only for the primary role, this destination transmits redo data to the remote physical standby destination orclsby1.
REMOTE_LOGIN_PASSWORDFILE Must be EXCLUSIVE or SHARED if a remote login password file is used (default = EXCLUSIVE)

Use the following commands to set the LOG_ARCHIVE_CONFIG and LOG_ARCHIVE_DEST_2 indicating the primary and standby databases.


Issue the following statements to determine your database's archival state, and then put the primary database in ARCHIVELOG mode to enable automatic archiving.

In this practice, you configure the network environment and create a physical standby database.

NOTE: In this blog, you configure the network for a single server environment. If you are configuring an environment on two different servers, you would need to configure Net Manager on both servers accordingly.

A. Use Oracle Net Manager to create an Oracle Net service name for your physical standby database.


Launch Net Manager.


Expand Local. Select Service Naming and click the green plus sign


Enter your (i.e. orclsby1) in the Net Service Name field and click Next


Select TCP/IP (Internet Protocol) and click Next


Enter your fully qualified and click Next.


Enter your in the Service Name field and click Next


Click Finish.


Click File -> Save Network Configuration to save the information to the tnsnames.ora file.

B. Use Oracle Net Manager to configure an entry for your standby database in the listener.ora file.


Expand Listeners




Select Database Services in the drop-down list.


Click Add Database.


Enter your in the Global Database Name field. Enter your and your in the SID field.


Select File -> Save Network Configuration.


Select File -> Exit.

C. Creating the standby database over the network


Reload the listener.

$lsnrctl reload


Navigate to ORACLE_HOME/dbs folder to perform this steps.

Copy the remote login password file (orapworcl) from the primary database system to the standby database system, renaming it to orapworclsby1.

The password file must be re-copied each time the SYSDBA or SYSOPER privilege is granted or revoked and whenever the login password of a user with these privileges is changed. You may need FTP, or some other remote file transfer mechanism, if you are using differenet servers.


In the ORACLE_HOME/dbs folder, for the standby system, create an initialization parameter file named initorclsby1.ora containing a single parameter: DB_NAME=


For the standby system, change to the /u01/app/oracle/admin directory. Create your (i.e. orclsby1) directory. Change to your (i.e. orclsby1) directory and create the adump directory.


For the standby system, create your (i.e. orclsby1) directory in $ORACLE_BASE/oradata for the data files.

NOTE: Depending on how you installed Oracle Database 11g, you may need to also add the following directory paths: $ORACLE_BASE/flash_recovery_area and $ORACLE_BASE/oradata


On the standby system, set the ORACLE_SID environment variable to your (i.e. orclsby1) and start the instance in NOMOUNT mode with the text initialization parameter file.


On the primary system, ensure the ORACLE_SID environment variable is set to your primary DB (i.e. orcl).


On the primary system, invoke RMAN and connect as SYSDBA to the target database. Connect to the auxiliary database.


Execute the script no.1 from prerequisite of this blog for RMAN on the primary system. When this script finishes you will have a new standby database that was created over the network without any interim storage.


Perform a log switch on the primary database and redo will start being sent to the standby.


On the standby system, ensure the ORACLE_SID environment variable is set to your (i.e. orclsby1) and start the MRP process.

Verify that the Physical Standby Database is Performing Correctly

Once you create the physical standby database and set up redo transport services, you may want to verify database modifications are being successfully transmitted from the primary database to the standby database. To see that redo data is being received on the standby database, you should first identify the existing archived redo log files on the standby database, force a log switch and archive a few online redo log files on the primary database, and then check the standby database again. The following steps show how to perform these tasks.


On the standby database, identify the existing archived redo log files by querying the V$ARCHIVED_LOG view.

You can use the query_archived_log.sql file.

On the primary database, issue the ALTER SYSTEM SWITCH LOGFILE statement to force a log switch and archive the current
online redo log file group.


On the standby database, re-query the V$ARCHIVED_LOG view to verify the redo data was received and archived on the standby database:


On the standby database, query the V$ARCHIVED_LOG view to verify the archived redo log files were actually applied. Please requery until you see a YES in the APPLIED column.

At the completion of this step, the physical standby database is running and provides the maximum performance level of data protection.

1 comment:

Anonymous said...


Thanks so much for your valued post.
The only comment that I have for now is that all images does not appear correctly.
Appreciate if you can check this.


Related Posts Plugin for WordPress, Blogger...

Let us be Friends...

Share |

Popular Posts


11.5.1. to 11.5.5 Cloning Procedure. 11g 11i 11i and R12 11i Autoconfig 11i Cloning 12.1.1 SSL 2 Node RAC errors out adop Advanced Replication Apache version Apex Apex for Oracle database Apex Installation Apex Installation on Oracle E Business Suite. 11i with Apex Apex on 11i Apex on Oracle 11i Apex402 apexins.sql APPL_TOP Character Application Express Application server Issue APPLSYS password Apps Password Asynchronous Replication Autoconfig Autoconfig Managed Beehive Bit of operating system Block block media corrupt BPEL Central Inventory Character Set Conversion Cloning of 11i Cloning of Oracle Applications Clusterware Concurrent CPU 2011 CPU 2012 April CPU 2012 January. CPU 2012 October CRS custom top Custom Top Creation Database . Database Query Dataguard DBMS_REPAIR Demantra . Domain Name E Business Suite Migration EBS 12.2 EPM exp/imp Oracle Applications Flashback table flows_020200 FND_TOP/resource FNDCPASS FNDCPUCF Forms server upgrade Forms startup FRM-92101 Fusion Middleware Hostname Hot Backup Hot cloning HotBackup Hotbackup Cloning of Oracle Apps hrglobal.drv. NLS Saudi Arabian HRMS hyperion Import and Export Oracle Applications Install Oracle Installation of R12 Installation Steps integration Oracle EBS Issues Database issues Oracle EBS Issues with Upgrade 12.1.1 Java Mission Control Java Upgrade java/sql/SavePoint JInitiator Junk Characters Language Translation Linux Linux Migration listener trace Listner version Local Inventory Login Page Master-Master Replication Memory Tuning Multiple Language NLS Non-Autoconfig Enabled OBIEE 11g Installation OBIEE Issues OIM Opatch version OpenSSL ora-01031 Oracle oracle 11g Oracle Announcement Oracle Application servers Oracle Application services Oracle Applications Oracle Applications 11i oracle applications 12.2 Oracle Applications Cloning Oracle Applications Upgrade Oracle Applications. Oracle Apps cloning Oracle BPEL oracle certifications oracle database Oracle Database RMAN Oracle Database upgradation Oracle DB oracle E Business suite 12.2 Oracle EBS oracle EPM Oracle Forms Logo oracle Fusion Applications Oracle Inventory oracle news Oracle OUI Oracle R12 oracle Sun Solaris Oracle Tuning Oracle Virtual Box Oracle Webgate Oracle WMA configuration orcladmin password for Oracle Applications Pasta Configuration pasta.cfg patch PDF Performance Physical Standby Platform migration Printer Configuration Profile Option R12 RAC Rapid Install Rapidwiz Real Application Clusters 10g Recovery RMAN RMAN Recovery SA Gosi Service Oriented Architecture SOA SQLTXPLAIN SSL Configuration step-by-step upgrade Sun solaris Swap Tablespace Trace Enable Trace file Translation Synchronization Patch transport tablespace tuning Tuning SGA uifont.ali Underscore Universal Content Management Upgradation of 12.0.5 to 12.1.1 Upgrade Upgrade 11g Upgrade Apps Upgrade DB Upgrade to 11g UTF8 version of oracle applications versions Web server upgrade weblogic windows workflow version XML Publisher version