Logical vs. Physical Standby databases

Logical vs. Physical Standby databases
Submitted By Rama Subramoniam on www.orafaq.com

A Quick Primer

Standby databases, in a nutshell, acts as a failover for our mission critical production databases. When production database crashes, applications can quickly switchover to the stand by databases.

Oracle provides two types of standby databases:

1. Physical Standby Database

Standby database is called “physical” if the physical structure of stand by exactly matches with stand by structure. Archived redo log transferred from primary database will be directly applied to the stand by database.

2. Logical Standby Database

Stand by database is called “logical”, the physical structure of both
databases do not match and from the archived redo log we create SQL statements then these statements will be applied to stand by database.

Administer Standby Databases

I would say there are three ways we can administer standby database.

1. SQL/Shell Scripts
This is the basic and simple method to create standby database, monitor the log transport and log apply services.
2. DGMGRL
DGMGRL stands for Data Guard Line Mode, a tool provided by oracle to administer standby database.
How it works?
In any typical standby db environment there will be only one primary database and one or more standby databases. With dgmgrl, we need to create one object for primary db called “Configuration” and for each stand by database we create one object alled“site”.Theseobjects“Configuration” and “sites” are interrelated.
Things to remember when using DGMGRL

* You can not create standby databases thru dgmgrl.You can only use this tool to monitor or switch over dbs.
* You need to have “sp file” created for all primary/stand by dbs.
* From dgmgrl, you need to always connect with primary db to perform any activities otherwise its not going work.
* The parameter dg_broker_start must be set to TRUE

3. OEM

We can use Oracle Enterprise Manager (OEM) to administer both physical and logical standby databases.

How to Create Physical Standby Database

Before you go thru the steps listed below in our example I call primary database as “TEST” and standby database as “TEST_S1”.But keep in mind when you create standby database the environment variable ORACLE_SID is going to be same as primary db name (TEST in our case).

1) Ensure the Primary database is in ARCHIVELOG mode:

SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /export/home/oracle/temp/oracle/arch
Oldest online log sequence 7
Current log sequence 9
SQL> alter database close;

Database altered.

SQL> alter database archivelog;

Database altered.

SQL> shutdown immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.

Modify the Primary database init.ora so that log_archive_start=true and restart the instance. Verify that database is in archive log mode and that automatic archiving is enabled.

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /export/home/oracle/temp/oracle/arch
Oldest online log sequence 7
Next log sequence to archive 9
Current log sequence 9

2) Create a backup of the Primary database

You can use an existing backup of the Primary database as long as you have the archive logs that have been generated since that backup. You may also take a hot backup as long as you have all archive logs through the end of the backup of the last table space. To create a cold backup do the following:

SQL> select name from v$datafile;

NAME
----------------------------------------------------------------
/export/home/oracle/temp/oracle/data/sys.dbf

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit

Create a backup of all data files and online redo logs using an OS command or utility. A backup of the online redo logs is necessary to facilitate switchover.

Once complete startup the instance:

SQL> startup
ORACLE instance started.

Total System Global Area 80512156 bytes
Fixed Size 279708 bytes
Variable Size 71303168 bytes
Database Buffers 8388608 bytes
Redo Buffers 540672 bytes
Database mounted.
Database opened.

3) Connect to the primary database and create the standby control file:

SQL> alter database create standby control file as
'/export/home/oracle/temp/oracle/data/backup/standby.ctl';

Database altered.

4) Copy files to the Standby host

Copy the backup data files, standby control file, all available archived redo logs, and online redo logs from the primary site to the standby site. Copying of the online redo logs is necessary to facilitate switchover. If the standby is on a separate site with the same directory structure as the primary database then you can use the same path names for the standby files as the primary files. In this way, you do not have to rename the primary data files in the standby control file. If the standby is on the same site as the primary database, or the standby database is on a separate site with a different directory structure the you must rename the primary data files in the standby control file after copying them to the standby site. This can be done using the db_file_name_convert and log_file_name_convert parameters or by manually using the alert database statements.

5) Set the initialization parameters for the primary database

It is suggested that you maintain two init.ora files on both the primary and the standby databases. This allows you to facilitate role reversal during switchover operations more easily.

For primary database ========

1. initTEST.ora
2. switch_to_stdby_initTEST

For Standby database =======

1. initTEST_s1.ora
2. switch_to_primary_initTEST

On primary initTEST.ora file add the following lines:

log_archive_dest_1='LOCATION=/export/home/oracle/temp/oracle/arch’
log_archive_dest_2='SERVICE=TEST_s1 reopen=60'
log_archive_dest_state_1=enable
log_archive_dest_state_2=enable
log_archive_format=%t_%s.dbf
log_archive_start=true
remote_archive_enable=true

Create the standby initialization parameter file and set the parameters for the standby database. Depending on your configuration, you may need to set filename conversion parameters.

Standby init.ora on Primary host:

log_archive_dest_1='LOCATION=/export/home/oracle/temp/oracle/arch'
log_archive_dest_state_1=enable
log_archive_format=%t_%s.dbf
log_archive_start=true
standby_archive_dest=/export/home/oracle/temp/oracle/arch
standby_file_management=auto
fal_server=TEST_s1
fal_client=TEST
remote_arhive_enable=true

NOTE: In the above example, db_file_name_convert and log_file_name_convert are not needed as the directory structure on the two hosts are the same. If the directory structure is not the same then setting of these parameters is recommended.

6) Configure networking components

On the Primary host create a net service name that the Primary database can use to connect to the Standby database. On the primary host creates a net service name that Standby, when running on the Primary host, can use to connect to the Primary, when it is running on the Standby host. Example from
Primary’s host tnsnames.ora:

TEST =
(DESCRIPTION =
(ADDRESS= (PROTOCOL= TCP) (Host= 172.20.69.83) (Port= 1523))
(CONNECT_DATA = (SID= TEST))
)

TEST_s1 =
(DESCRIPTION =
(ADDRESS= (PROTOCOL= TCP) (Host= 172.20.89.7) (Port= 1530))
(CONNECT_DATA = (SID= TEST))
)

The above two net service names must exist in the Standby hosts tnsnames.ora also. You must also configure a listener on the standby database. If you plan to manage this standby database using the Data Guard broker, you must configure the listener to use the TCP/IP protocol and statically register the standby database service using its SID.

7) Start up and mount the stand by database

oracle@hasunclu2:/export/home/oracle/temp/oracle> sqlplus "/ as sysdba"

SQL*Plus: Release 9.0.1.0.0 - Production on Thu Mar 14 18:00:57 2002 (c) Copyright 2001 Oracle Corporation. All rights reserved.
Connected to an idle instance.

SQL> startup nomount pfile=?/dbs/initTEST_s1.ora
ORACLE instance started.

Total System Global Area 80512156 bytes
Fixed Size 279708 bytes
Variable Size 71303168 bytes
Database Buffers 8388608 bytes
Redo Buffers 540672 bytes
SQL> alter database mount standby database;

Database altered.

SQL>

8) Create standby redo log files, if necessary

Standby redo logs are necessary for the higher protection levels such as Guaranteed, Instant, and Rapid. In these protection modes LGWR from the Primary host writes transactions directly to the standby redo logs. This enables no data loss solutions and reduces the amount of data loss in the event of failure. Standby redo logs are not necessary if you are using the delayed protection mode.

If you configure standby redo on the standby then you should also configure standby redo logs on the primary database. Even though the standby redo logs are not used when the database is running in the primary role, configuring the standby redo logs on the primary database is recommended in preparation for an eventual switchover operation.

Standby redo logs must be archived before the data can be applied to the standby database. The standby archival operation occurs automatically, even if the standby database is not in ARCHIVELOG mode. However, the archive process must be started on the standby database. Note that the use of the archiver process (ARCn) is a requirement for selection of a standby redo log.

You must have the same number of standby redo logs on the standby as you have online redo logs on production. They must also be exactly the same size.

The following syntax is used to create standby redo logs:

SQL> alter database add standby log file
'/export/home/oracle/temp/oracle/data/srl_1.dbf' size 20m;

Database altered.
SQL> alter database add standby log file
2 '/export/home/oracle/temp/oracle/data/srl_2.dbf' size 20m;

Database altered.

SQL> alter database add standby log file
2 ‘/export/home/oracle/temp/oracle/data/srl_3.dbf' size 20m;

Database altered.

SQL>

9) Copy database files

Manually change the names of the primary data files and redo logs in the standby control file for all files not automatically renamed using DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT as noted in step 5.Datafile names can be changed on the standby at a mounted state with the following syntax:

SQL> alter database rename file
'/export/home/oracle/temp/oracle/data/sys.dbf'
to
'/export/home/oracle/temp/oracle/data2/sys.dbf';

10) Restart the listeners

On the primary database, and start the listener on the standby database so that changes made to the listener.ora can be implemented.

11) Activate parameter changes

Manually enable initialization parameter changes on the primary database so that it can initiate archiving to the standby site.
At runtime, the LOG_ARCHIVE_DEST_n initialization parameter can be changed using ALTER SYSTEM and ALTER SESSION statements. Activate the changes made to these parameters by either bouncing the instance or activating via alter system.

For example:

SQL> alter system set log_archive_dest_2='SERVICE=TEST_s1 reopen=60';

System altered.

12) Verify Auto Archive

On the Primary database switch a log and verify that it has been shipped properly using the v$archive_dest view.

SQL> alter system switch logfile;

System altered.

SQL> select status, error from v$archive_dest where dest_id=2;



STATUS ERROR
--------- ------------------------------------------------------
VALID

SQL>

13) Put Standby database in managed standby database

SQL> recover managed standby database disconnect;
Media recovery complete.
SQL> exit

14)Apply redo logs in the standby database

Remember archived logs will be transferred to stand by database. But we need to apply the archived logs to standby database. We can apply these archived logs either thru SQL or data guard command line tool (DGMGRL)

Let us see how we can apply thru SQL.

Go to standby database environment:

SQL> startup nomount pfile=initTEST_s1.ora

SQL> alter database mount standby database;

SQL> recover managed standby database disconnect;

Warning!!!After executing the above statement you will get the sql prompt. But that does not mean the recovery is complete. This statement will kick off “Disaster Recovery Service”.

SQL> recover managed standby database cancel;

The above statement will complete media recovery. Now all archive redo logs applied to standby database.

15) Switchover databases

In real time environment, if primary database is crashed or unavailable for some reason you need to make standby database as primary database.

• Switchover Standby to Primary

In order to do switchover standby have to be in mount state.

SQL> alter database commit to switch over to primary;

Sometimes, this command will ask to do media recovery on stand by instance. Apply logs as mentioned above and rerun the command.

SQL> shutdown immediate;
SQL> startup pfile=switch_to_primary_initTEST.ora

• Switchover Primary to Standby

SQL>alter database commit to switch over to standby;
SQL> shutdown immediate;
SQL> startup pfile= switch_to_stdby_initTEST.ora

How to Create Logical Standby Database

Now, let us talk about how we can create one logical standby database and administer logical standby database.

Before we create logical database perform the following checks to make sure the primary database qualify to have logical standby database.

• Determine whether primary database contains data types like LONG, NCLOB, LONG RAW, BFILE those are not supported by standby database.
• Ensure that the tables in primary database can be uniquely identified.
• Ensure that the primary database is in ARCHIVELOG mode and that archiving is enabled.
• Ensure supplemental logging is enabled on the primary database. To see whether supplemental logging is enabled, start a SQL session and query the V$DATABASE fixed view. For example, enter:

SQL> SELECT SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI
FROM V$DATABASE;

SUP SUP
--- ---
YES YES

If supplemental logging is not enabled, execute the following statements:

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE INDEX) COLUMNS;
SQL> ALTER SYSTEM SWITCH LOGFILE;
• Ensure LOG_PARALLELISM init.ora parameter is set to 1 (default value).If you plan to be performing switchover operations with the logical standby then you must create an alternate tablespace in the primary database for logical standby system tables. Use the DBMS_LOGMNR_D.SET_TABLESPACE procedure to move the tables into the new tablespace. For example:

SQL> EXECUTE LOGMNR_D.SET_TABLESPACE
'logical_tblsp');

Steps to create Logical Standby Database

1. On primary database, perform cold backup.
2. Bring the primary database to mount state to create backup of control file.
3. Open the primary database and build the log miner dictionary.

SQL> ALTER system enable restricted session;
SQL> ALTER DATABASE OPEN;
SQL> EXECUTE DBMS_LOGSTDBY.BUILD;
4. Archive the current online redo log and disable the restricted session.
5. Identify the archived redo log that contains the log miner dictionary

SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE DICTIONARY_BEGIN='YES'
AND STANDBY_DEST='NO';
6. . Copy all these files with init parameter file to the stand by database.
7. On standby system, modify the copied primary init file to support the logical standby feature. Some parameters affected are control_files,standby_archive_dest,parallel_max_servers,instance_name.
8. Start and mount the standby database in exclusive mode.
9. Turn on the database guard.

ALTER DATABASE GUARD ALL;
10. Open the logical standby followed by a shutdown immediate or normal.
SQL> ALTER DATABASE OPEN RESETLOGS;
SQL> SHUTDOWN IMMEDIATE;
11. Open the logical standby database in exclusive mode.
12. Create temporary table space for the standby database.
13. On logical standby database, register the archived log identified in step 5 and run the following command.
SQL> ALTER DATABASE REGISTER LOGICAL LOGFILE
'/u01/oradata/stby/arch/arc1_28.arc';
14. Run the following ALTER DATABASE statement and include the INITIAL keyword to begin SQL apply operations for the first time on the logical standby.

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY INITIAL;
15. Configure listener on the standby database.
16. Once listeners running on both primary and standby databases run the following command.

ALTER SYSTEM RESGITER;
17. Create tns names entry for primary database in standby host. Similarly, create tns names entry for standby database in primary host.
18. Enable archiving in the logical standby database.
19. Start archive the current online redo log file. Verify that the remote archiving succeeded by running the following select statement.
SQL> SELECT STATUS, ERROR FROM V$ARCHIVE_DEST;
20. Verifying archived redo logs are being applied. To verify that the archived redo logs are being applied, query the V$LOGSTDBY view. This view provides information about the processes that are reading redo log information and applying it to the logical standby databases. You can also query the DBA_LOGSTDBY_PROGRESS view to find out the progress of SQL apply operations. The V$LOGSTDBY_STATS view shows the state of the coordinator process and information about the SQL transactions that have been applied to the logical standby database.

Logical or Physical, What is your Choice?

You ask you DBA to implement “stand by “database for your production database. Guess what? You will get “physical standby “database implemented for your production database. Because implementing physical standby database would be first choice for many DBAs as most of us would not realize the benefits we get from having logical standby database.

Let us take close look at the major bottleneck with physical stand by database.
We need to keep the physical standby database in “recovery mode” in order to apply the received archive logs from the primary database. We can open “physical stand by database to “read only” and make it available to the applications users (Only select is allowed during this period).Once the database is opened in “Read only” mode then we can not apply redo logs received from primary database.

We do not see such issues with logical standby database. We can open up the database in normal mode and make it available to the users. At the same time, we can apply archived logs received from primary database.

Real World Scenario

One of my clients was facing critical performance issue with their primary database. The primary database needed to support pretty large user community for the OLTP system and pretty large “Reporting Group”. The database could not cope up with these two large user groups. So I have configured “logical stand by” database for the primary database and I have opened up the database available to the reporting users group. Now the database load is kind of distributed between primary and standby database.

Conclusion

I will hazard a guess that you and many users now have a newfound respect for standby databases. In future, if you need to create standby databases ask yourself the following questions.

• Do I need just one failover database? Then you can choose physical standby database.
• Do I need failover database? Also I need to see whether I can make use of this failover database rather than sitting idle on the server. Then choose logical standby database.