oracle 10g logical standby (by otn)

Make Recovery Logical
By Darl Kuhn

Use Data Guard's logical standby database for recovery and 24/7 availability.

Many enterprises have conflicting requirements for transaction processing and business reporting. For example, decision reporting cannot be allowed to degrade the performance of online transaction processing. Additionally, mission-critical systems are required to be highly available, with minimal downtime and no data loss. Logical standby databases resolve these disparate requirements by providing fault-tolerant disaster recovery protection while also making available a 24/7 reporting replica of real-time production data.

Data Guard is Oracle's integrated disaster recovery solution. Data Guard ships with two types of disaster recovery standby databases: physical and logical. Physical standby technology has been available since Oracle7 Release 7.3. Data Guard keeps the physical standby database in sync with production data by automatically copying and applying primary redo data to the standby database. This results in a standby database that is an exact block-for-block copy of your primary production database.

Logical standby differs from physical standby in two significant ways: the manner in which redo is applied and uptime availability for reporting. The logical standby database uses LogMiner and SQL Apply technology to transform the primary database redo data into SQL that is then applied to the logical standby database. This allows your logical standby database to be available 24/7 for querying, while simultaneously applying data manipulation language (DML) and data definition language (DDL) statements from the primary database.

This article describes key improvements to the logical standby database feature in Oracle Database 10g and how to implement a logical standby database.

Logical Standby in Oracle 10g

The logical standby feature was first released in Oracle9i Release 2 and was greatly enhanced in Oracle Database 10g. In addition to a greater ease of implementation, the following are significant improvements to Data Guard's logical standby feature in Oracle Database 10g:


No downtime required to implement logical standby feature
Real-time application of DML/DDL (SQL Apply) to logical standby database
New support for more datatypes

These enhancements reduce the cost of implementation and improve functionality while still providing you an integrated no-data-loss disaster recovery solution.

Implementing Oracle Database 10g Logical Standby

If you struggled with implementing logical standby in Oracle9i, you'll be pleased with the streamlined Oracle 10g install. Documented next are the steps for setting up a logical standby in the default maximum performance mode in a UNIX environment. This example assumes that you have identical mount points and directory structures on your primary and standby servers.

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


SQL> alter database force logging;


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


SQL> select force_logging
from v$database;


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


$ orapwd file=orapw<sid_name>
password=top_secret


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

Step 3. Configure the primary database init.ora/spfile. If you are using an spfile, you may find it easier to switch to using an init.ora file while implementing your logical standby. After implementation, you can easily switch back to using an spfile. The examples in this article use an init.ora file.

In this example, BRDSTN is the database name of both the primary and the standby. Primarydb is the Oracle Net service name of the primary database, and standbydb is the Oracle Net service name of the standby database. Include the contents of Listing 1 in the init.ora file on your primary database.

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


SQL> archive log list;


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


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


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

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

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

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


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


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


SQL> select supplemental_log_data_pk, supplemental_log_data_ui
from v$database;


Step 7. Take a backup of your primary database, and move it to the standby machine. Take an offline or online backup of your primary database and copy it to your standby server. You need to back up and copy only data files, not online redo files or controlfiles.

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


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


Note the use of the keyword logical; it's critical to use the correct syntax.

After creating the logical standby controlfile, copy it to your standby machine. In this example, the standby controlfile must be placed in the /ora01/ oradata/BRDSTN directory on the standby machine.

Step 9. Create init.ora for logical standby. Copy the primary init.ora file to the standby machine, and then make the necessary modifications for your logical standby database, as shown in Listing 2.

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


$ orapwd file=orapw<sid_name>
password=top_secret


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


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

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


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


sqlnet.expire_time=1


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


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


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


SQL> alter database activate standby database;


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

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


SQL> shutdown immediate;
SQL> startup mount;

$ nid target=sys/top_secret dbname=BRDLS


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


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


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


DBNEWID - Completed successfully.


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


db_name=BRDLS


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


$ orapwd file=orapw<sid_name>
password=top_secret


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


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


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


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


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


SQL> alter database start logical standby apply;


You now have a fully functioning logical standby database.

Postimplementation Checks

I recommend continuously viewing, during the previous setup process and postimplementation, what's being written to the alert.log file for both the primary and the logical databases. The information that Data Guard writes to the alert.log file is fairly informative and will help you quickly isolate and resolve problems. To monitor the installation progress in a UNIX environment, navigate to your background dump directory destination and issue this command:


$ tail -f alert_<sid>.log


There are also many Data Guard views that you can use to monitor the status. On the logical standby database, issue this query to view which archive redo logs have arrived and whether they've been applied or not:


SQL> select sequence#, applied
from dba_logstdby_log order by 1;


To monitor the status of the SQL Apply process, run this statement:


SQL> select name, value
from v$logstdby_stats
where name = 'coordinator state';

NAME                                  VALUE
------------------                ------------
coordinator state                    INITIALIZING


When you first set up a logical standby database, the coordinator state will have the value of INITIALIZING. Depending on the number of objects replicated, it may be in this mode for several hours. This initialization delay occurs only when you first set up your logical standby.

After initialization, the coordinator state value will change to APPLYING. When this occurs, redo is now being mined on the primary database and the resulting SQL statements are being applied to the logical standby database.

Tuning Your Logical Standby

This section discusses the following methods to enhance performance in your logical standby database:


Adding indexes/materialized views
Analyzing objects
Tuning the shared pool
Setting PARALLEL_MAX_SERVERS

If you need to add indexes or materialized views to your logical standby database to improve performance, you can do so independent of your primary database objects. When you make changes, however, you must first disable the guard mechanism that prevents changes to objects maintained by the SQL Apply process. For example, if you want to add an index to the CUSTOMER_NAME column of your CUSTOMERS table, issue the following:


SQL> alter session disable guard;
SQL> create index customer_i1
   on customer(customer_name);
SQL> alter session enable guard;


If you analyze your primary database objects, the statistics generated are not propagated to your logical standby database. Therefore, it's critical that you analyze your logical standby objects as required. For example, after a large percentage of the data has changed in your APPS schema, issue the following command on your logical standby database:


SQL> exec -
dbms_stats.gather_schema_stats(
ownname=>'APPS',-
estimate_percent=>
dbms_stats.auto_sample_size,-
cascade=>true);


The SQL Apply process makes heavy use of the shared pool, so as a general guideline, allocating more memory to the shared pool should improve the SQL Apply performance. By default, the SQL Apply process will use as much as 30MB of the memory in the shared pool. You can limit the amount of shared pool resources that the SQL Apply process uses by running the DBMS_LOGSTDBY.APPLY_SET procedure. For example, to limit the amount used in the shared pool to 20MB, issue the following SQL:


SQL> exec dbms_logstdby.apply_set('MAX_SGA',20);


Next Steps

READ more about Data Guard
Oracle Data Guard Concepts and Administration
"Oracle9i Data Guard: SQL Apply Best Practices"
Oracle Data Guard Overview
"Data Guard SQL Apply: Back to the Future"

The SQL Apply mechanism was engineered to use parallel processes to transform and apply SQL. It will use the PARALLEL_MAX_SERVERS initialization parameter to determine the maximum number of parallel processes to spawn. Oracle recommends that you set this value to at least 5 on a logical standby database, and for best results set it to 9. You can limit the number of processes used, by calling the DBMS_LOGSTDBY.APPLY_SET procedure. The following example shows how to limit the number of processes to 8:


SQL> exec dbms_logstdby.apply_set('MAX_SERVERS',
8);


Skipping Application of SQL

It's fairly easy to configure your logical standby database not to apply unwanted SQL statements. You must first disable the logical standby apply process, specify changes, and then reenable the apply process. This example shows how to stop DML and DDL for the APPS schema's INVOICES table:


SQL> alter database stop logical standby apply;
SQL> execute dbms_logstdby.skip('SCHEMA_DDL', 'APPS', 'INVOICES');
SQL> execute dbms_logstdby.skip('DML', 'APPS', 'INVOICES');
SQL> alter database start logical standby apply;


To revert back to applying SQL on objects, use the dbms_logstbdy.unskip procedure. This example demonstrates how to resume application of SQL to the INVOICES table:


SQL> alter database stop logical standby apply;
SQL> execute dbms_logstdby.unskip('SCHEMA_DDL', 'APPS', 'INVOICES');
SQL> execute dbms_logstdby.unskip('DML', 'APPS', 'INVOICES');
SQL> alter database start logical standby apply;


The Logical Conclusion

Oracle Data Guard logical standby databases are configurable for guaranteed no-data-loss protection, with the benefit of being continuously available for reporting on a real-time copy of production data. This allows you to use one disaster recovery tool as a solution for many different business requirements. The Oracle Database 10g edition of the logical standby database is fairly easy to implement, with minimal impact on the primary production database.