Oracle started shipping Standby Database features with the release of Oracle 8.0.4. Standby Database features were enhanced with the help of user feedback in subsequent releases of Oracle. Although there are several ways to set up highly available (HA) databases, including Oracle 10g features like RAC, Veritas Cluster software (VCS), HP data guard and Sun cluster (SC) etc., these clustered systems avoid having single points-of-failure by having software and hardware redundancy. In the case of failure, tasks being performed by the failed component are taken over by the backup component. Although these redundant features are good for high availability and scalability, they do not protect from user mistakes, data corruptions and other disasters that may destroy the database itself. That is where Oracle 10g Data Guard and Standby Database features protect your mission critical databases.

If you are using Oracle Enterprise edition, both Data Guard broker and Standby Database features are included at no cost. The term "Data Guard" is synonymous to Standby Database in many ways, as Oracle renamed the Standby Database feature to "Oracle Data Guard" in Oracle release 9.0.1.

DBAs have the option to set up two different types of standby databases. They are a physical standby database and a logical standby database. Physical standby databases are physically identical to primary databases, meaning all objects in the primary database are the same as in standby database. Logical Standby Databases are logically identical to primary databases although the physical organization and structure of the data can be different. Physical Standby databases are traditionally standby databases, identical to primary databases on a block for block basis. It is updated by performing media recovery; imagine a DBA sitting in the office and recovering the database constantly.

Logical Standby Databases are updated using SQL statements. The advantage of a logical standby database is that it can be used for recovery and reporting simultaneously. I am very interested in the logical standby feature as it can be used for my disaster recover project as well as it can be used by data warehouse users for their reporting purpose.

In this article, I opted to discuss the concept and setup of the logical standby database. Please note that troubleshooting and tuning of the logical databases are not in the scope of this article. You can refer to Oracle Data Guard Concepts and Administration guide and Oracle metalink notes for this.

When to choose Logical Standby database?

• Reporting:

  Synchronization of the logical standby database with the primary database is done using logminer technology, which transforms standard archived redologs into SQL statements and applies them to the logical stand by database. Therefore, the logical standby database must remain open and the tables that are maintained can be used simultaneously for reporting.

• System Resources:

  Besides the efficient utilisation of system resources, reporting tasks, summations and queries can be optimized by creating additional indexes and materialised views, since both primary and logical standby database can have a different physical lay out by protecting switchover and failover for the primary database.

Prerequisite Conditions for creating a Logical Standby Database:

1.  Determine if the primary database contains tables and datatypes that were not supported by a logical stand by database. If the primary database contains tables that were unsupported, log apply services will exclude the tables applying to the logical stand by database.

     
SQL> Select * from dba_logstdby_unsupported;

OWNER                 TABLE_NAME                    COLUMN_NAME         DATA_TYPE          
--------------------- --------------------------    ----------------    --------------- 
WMSYS                 WM$UDTRIG_INFO                TRIG_CODE           LONG
WMSYS                 WM$VERSIONED_TABLES           UNDO_CODE           WM$ED_UNDO_CODE_TABLE_TYPE
2 rows selected.

2.  To maintain data in a logical stand by database, SQL Apply operations must be able to identify the columns that uniquely identify each row that has been updated in the primary database. Tables that do not have primary keys or non-null unique indexes are identified by enabling supplemental logging.

SQL> select owner, table_name, bad_column from dba_logstdby_not_unique;
      OWNER			TABLE_NAME	B
        VCSUSER		VCS	N

Bad column ‘N’ indicates that the table contains enough column information to maintain the table in the logical standby database, where as ‘Y’ indicates the table column is defined using an unbounded data type, such as LONG.

Add a primary key to the tables that do not have to improve performance.

If the table has a primary key or a unique index with a non-null column, the amount of information added to the redo log is minimal.

3.  Ensure that Primary database is in archivelog mode.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /opt/app/oracle/admin/myDB/arch
Oldest online log sequence     345
Next log sequence to archive   347
Current log sequence           347

4.  Ensure supplemental logging is enabled and log parallelism is enabled on the primary database. Supplemental logging must be enabled because the logical standby database cannot use archived redo logs that contain both supplemental log data and no supplemental log data.

SQL> select supplemental_log_data_pk,supplemental_log_data_ui from v$database;


SUP SUP
--- ---
YES YES

If the supplemental logging is not enabled, execute the following

SQL> ALTER  DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY,UNIQUE INDEX) COLUMNS;
SQL> ALTER SYSTEM SWITCH LOGFILE;

If log parallelism is not enabled, execute the following:

SQL> ALTER SYSTEM SET LOG_PARALLELISM=1 SCOPE=BOTH;

Start Resource manager if you plan to create a logical standby database using hot backup. If you do not have a resource_manager plan, you can use one of the system defined plans and restart the primary database to make sure it is using the defined plan.

SQL> ALTER SYSTEM SET RESOURCE_MANAGER_PLAN=SYSTEM_PLAN SCOPE=BOTH;
SQL> SHUTDOWN
SQL> STARTUP

Improvements in Oracle Data Guard in Oracle 10gr2:

• Automatic Deletion of applied archive logs: Once primary database Archived logs are applied to a Logical Standby Database, they are deleted automatically without DBA intervention. This makes it easier to maintain both primary and logical standby databases. Physical standby databases have had this functionality since Oracle 10g Release 1, by using Flash Recovery Area option. 
• No downtime required: The primary database is no longer required to shutdown or be put in QUIESCING state, as we can create the logical standby database from a hotbackup of the primary database just like the physical standby database.
• Online upgrades: A lot of DBAs have dreamed about this for long time: just like IBM’s DB2 or Microsoft SQL Server, the DBA no longer required to shutdown the primary database to upgrade from Oracle 10g release 2 with Data Guard option. First, upgrade the logical standby database to the next release, test and validate the upgrade, do a role reversal by switching over to the upgraded database, and then finally upgrade the old primary database.
• New Datatypes Supported: I always used to hesitate whenever I thought of logical standby databases, as some of my databases never meet the pre-requisite conditions. In 10g relase2, Oracle supports most of the datatypes, such as NCLOB, LONG, LONGRAW,BINARY_FLOAT,BINARY_DOUBLE,IOTs.

Steps in Creating Logical standby Database:

1.  Take a hotbackup of the primary database. Bring a tablespace to backup mode, copy the datafiles of that tablespace using an OS command, and bring the tablespace back online.

An Example of taking a backup of the SYSTEM tablespace:

SQL> ALTER TABLESPACE SYSTEM BEGIN BACKUP; 
! cp /opt/oracle/oradata/MYDB/system01.dbf /backup/MYDB/HOT/ 
SQL> ALTER TABLESPACE SYSTEM END BACKUP; 

2.  Create a backup copy of the control file.

SQL> ALTER DATABASE BACKUP CONTROLFILE TO 2> backup/MYDB/HOT/MYDB_backup.ctl; 

3.  Bring the database to a quiesced state and configure Database Resource Manger for the same. Skip this step only if you are using 10g relase 2.

SQL> ALTER SYSTEM QUIESCE RESTRICTED;

4.  Build the LogMiner dictionary.

SQL> EXECUTE DBMS_LOGSTDBY.BUILD;

5. Identify the archived redo log that contains the LogMiner dictionary and the starting SCN.

You need to switch the log file, to create the archived log, and then query from V$ARCHIVED_LOG.

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT; 

SQL> SELECT NAME FROM V$ARCHIVED_LOG 
	WHERE (SEQUENCE#=(SELECT MAX(SEQUENCE#) 
	FROM V$ARCHIVED_LOG   
	WHERE DICTIONARY_BEGIN = 'YES' AND STANDBY_DEST='NO')); 

NAME 
----------------------------------------------------------- 
/opt/oracle/ARC/MYFN/MYFN_0001_0000000005.arc 

SQL> SELECT MAX(FIRST_CHANGE#) FROM V$ARCHIVED_LOG   

     WHERE DICTIONARY_BEGIN='YES'; 

MAX(FIRST_CHANGE#) 
------------------ 
                 2856516 

Note: Remember to record the name of the archived redo log for use later in the creation process.

6. Bring the database back to normal.

SQL> ALTER SYSTEM UNQUIESCE; 
SQL> ALTER SYSTEM SWITCH LOGFILE; 

7. Create the parameter file from spfile in the Primary database. The pfile created will be used to create the pfile of the standby database.

SQL > CREATE PFILE=’/backup/MYDB/HOT/Primary_init.ora’ FROM  SPFILE; 

8. Copy Files from the Primary Database Location to the Standby Location.

Use an operating system copy utility to copy the following binary files from the primary database site to the standby site:

• Backup data files
• Backup of control files
• Latest archived redo log
• init.ora file

    9. Set the init.ora Parameters on the Logical standby site.

DB_NAME='MYDB' 
INSTANCE_NAME='MYDB_H' 
LOG_ARCHIVE_DEST_1='LOCATION=/OPT/ORACLE/ARC/MYDB   MANDATORY' 
LOG_ARCHIVE_DEST_STATE_1=ENABLE 
LOG_ARCHIVE_FORMAT='MYDB_%T_%S.ARC' 
REMOTE_ARCHIVE_ENABLE=RECEIVE 
LOG_ARCHIVE_START=TRUE 
LOG_PARALLELISM=1 
PARALLEL_MAX_SERVERS=9 
STANDBY_FILE_MANAGEMENT='AUTO' 
STANDBY_ARCHIVE_DEST='/OPT/ORACLE/ARC/MYDB/STDBY' 
# The following parameter is required only if the primary and standby databases 
# are located on the same system. 
LOCK_NAME_SPACE=MYDB_H 

10. Configure the Listener for Both the Primary and Standby Databases and Restart/reload the listener(s).

11. Because the online logs were not copied from the primary system, the redo logs will have to be cleared. To clear the redo logs, use the following statement for all the groups:

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1; 
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 2; 

12. Recover the Logical Standby Database up to the SCN recorded in step 5 above.

Use the command below to recover the database:

SQL> ALTER DATABASE RECOVER AUTOMATIC FROM '/opt/oracle/ARC/MYDB_H/' 
  UNTIL CHANGE 2856516 USING BACKUP CONTROLFILE; 

If error 'ORA-279: change %s generated at %s needed for thread %s' comes the recovery will have to be canceled and recover it manually using the following command.

SQL> ALTER DATABASE RECOVER LOGFILE 2>'/opt/oracle/ARC/MYDB_H/MYDB_0001_0000000004.arc' 

13. Turn on Data Guard on the Logical Standby Database.

SQL> ALTER DATABASE GUARD ALL; 

14. Open the Logical Standby Database:

SQL> ALTER DATABASE OPEN RESETLOGS; 

15. Reset the Database Name of the Logical Standby Database:

Run the Oracle DBNEWID (nid) utility to change the database name of the logical standby database. This will change the database name in the control file. Until now, the dbname in the control file is the primary db name.

SQL> SHUTDOWN IMMEDIATE; 
SQL> STARTUP MOUNT PFILE='' 
SQL> EXIT 
$> export ORACLE_SID=MYDB_H 

Modify the init.ora file and set parameter DB_NAME=MYDB_H, and if password file is used then delete and recreate the password file.

SQL> SHUTDOWN IMMEDIATE; 

16. Create a server parameter file for the standby database:

SQL> CREATE SPFILE FROM PFILE=<pfile name with full path>; 

17. Restart the Logical Standby Database:

SQL> STARTUP MOUNT; 
SQL> ALTER DATABASE OPEN RESETLOGS; 

18. Create a New Temporary File for the Logical Standby Database:

The temporary files are not included as a part of the closed backup operation, so the temporary file will have to be recreated manually.

To create a Temporary file:

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE 2> '/opt/oracle/oradata/MYDB_H/temp01.dbf' SIZE 40M REUSE; 

19. Register the Archived Redo Log and Start SQL Apply Operations:

To register the most recently archived redo log and begin applying data from the redo logs to the standby database, perform the following steps:

SQL> ALTER DATABASE REGISTER LOGICAL LOGFILE 
'/opt/oracle/ARC/MYDB_H/MYDB_0001_0000000005.arc’; 

Specify the following SQL statement to begin applying redo logs to the logical standby database using the SCN number:

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY INITIAL 2856516; 

Note: The INITIAL keyword has to be used only for the first time. To apply redo logs thereafter, the following statements should be used.

SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY; 
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY; 

20. Enable Archiving in Logical Standby Database:

This step has to be performed in the Primary Database to enable archiving to the Logical Standby Database. Use the following statements to start archiving and remote archiving:

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_3='SERVICE=payroll3 lgwr NO AFFIRM' 2 > SCOPE=BOTH; 
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3=ENABLE SCOPE=BOTH; 

To start remote archiving either of the following statements can be used:

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT; 
SQL> ALTER SYSTEM SWITCH LOGFILE; 

This completes the configuration of Logical Standby Database from Hot Backup.

Conclusion:

SQL Apply with Logical Standby Database is a viable option for customers who need to implement a disaster recovery solution or maximum/high availability solution and use the same resources for reporting and decision support operations. The success in creating a Logical Standby Database depends a lot on how the tasks are executed and on the version is being used. It is very important, before starting the creation of a Logical Standby Database, to make sure that all the Initialization Parameters are set correctly, that all the steps are followed in the correct order and the appropriate parameters are used. If everything is done properly then you should be able to do a clean configuration of the Logical Standby Database in the first go.

Back to DBAsupport.com