Difference between logical & physical standby database?

Oracle Tips by Burleson Consulting
October 21, 2008

Question: What is the difference between logical standby and physical standby in Oracle Data Guard?

Answer: In Oracle Data Guard you, Oracle transfers data from the main database to a standby database, and in case of failures, Oracle will switch over to the standby database. We have two ways to create a standby database, logical standby and physical standby:

Physical standby differs from logical standby:

Physical standby schema matches exactly the source database.
Archived redo logs and FTP'ed directly to the standby database which is always running in "recover" mode. Upon arrival, the archived redo logs are applied directly to the standby database.

Logical standby is different from physical standby:

Logical standby database does not have to match the schema structure of the source database.
Logical standby uses LogMiner techniques to transform the archived redo logs into native DML statements (insert, update, delete). This DML is transported and applied to the standby database.
Logical standby tables can be open for SQL queries (read only), and all other standby tables can be open for updates.
Logical standby database can have additional materialized views and indexes added for faster performance.

Installing Physical standbys offers these benefits:

An identical physical copy of the primary database
Disaster recovery and high availability
High Data protection
Reduction in primary database workload
Performance Faster

Installing Logical standbys offer:

Simultaneous use for reporting, summations and queries
Efficient use of standby hardware resources
Reduction in primary database workload
Some limitations on the use of certain datatypes

For more complete differences between logical and physical standby databases, see the book "Oracle Data Guard", by Bipul Kumar.

Oracle Concepts - Creating a Resource Plan

Oracle Tips by Burleson Consulting

Creating a Resource Plan

Rather than have a simple CREATE RESOURCE PLAN command, Oracle8i has a series of packages which must be run in a specific order to create a proper resource plan. All resource plans are created in a pending area before being validated and committed to the database. The requirements for a valid resource plan are outlined in the definition of the DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA procedure below.

Resource plans can have up to 32 levels with 32 groups per level allowing the most complex resource plan to be easily grouped. Multiple plans, sub-plans and groups can all be tied together into an application spanning CPU resource utilization rule set. This rule set is known as a set of directives. An example would be a simple 2-tier plan like that shown in Figure 1.

Figure 1 Example Resource Plan

An example of how this portioning out of CPU resources works would be to examine what happens in the plan shown in Figure 1. In figure 1 we have a top level called MASTER which can have up to 100% of the CPU if it requires it. The next level of the plan creates two sub-plans, USERS and REPORTS which will get maximums of 60 and 20 percent of the CPU respectively (we also have the required plan OTHER_GROUPS to which we have assigned 20 percent, if a user is not assigned to a specific group, they get OTHERS). Under USERS we have two groups, ONLINE_USERS and BATCH_USERS.

ONLINE_USERS gets 70 percent of USERS 60 percent or an overall percent of CPU of 42 percent while the other sub-group, BATCH_USERS gets 30 percent of the 60 percent for a total overall percent of 18.

The steps for creating a resource plan, its directives and its groups is shown in Figure 2.

One thing to notice about Figure 2 is that the last step shows several possible packages which can be run to assign or change the assignment of resource groups. The first package listed, DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP must be run the first time a user is assigned to a resource group or you won't be able to assign the user to the group. After the user has been given the SWITCH_CONSUMER_GROUP system privilege you don't have to re-run the package.

Figure 2 Steps to Create a Resource Plan

Figure 3 shows the code to create the resource plan in Figure 1. Figure 4 shows the results from running the source in figure 3.

Figure 3 Script to create example resource plan

set echo on
spool test_resource_plan.doc
-- Grant system privilege to plan administrator
--
execute
dbms_resource_manager_privs.grant_system_privilege('SYSTEM','ADMINISTER_RESOURCE_MANAGER',TRUE);
--
--connect to plan administrator
--
CONNECT system/system_test@ortest1.world
--
-- Create Plan Pending Area
--
EXECUTE dbms_resource_manager.create_pending_area();
--
-- Create plan
--
execute dbms_resource_manager.create_plan('MASTER','Example Resource Plan','EMPHASIS');
execute dbms_resource_manager.create_plan('USERS','Example Resource Sub Plan','EMPHASIS');
execute dbms_resource_manager.create_plan('REPORTS','Example Resource Sub Plan','EMPHASIS');
--
--Create tiers of groups in plan
--
EXECUTE dbms_resource_manager.create_consumer_group('ONLINE_USERS','3rd level group','ROUND-ROBIN');
EXECUTE dbms_resource_manager.create_consumer_group('BATCH_USERS','3rd level group','ROUND-ROBIN');
EXECUTE dbms_resource_manager.create_consumer_group('ONLINE_REPORTS','2rd level group','ROUND-ROBIN');
EXECUTE dbms_resource_manager.create_consumer_group('BATCH_REPORTS','2rd level group','ROUND-ROBIN');
--
-- Create plan directives
--
EXECUTE dbms_resource_manager.create_plan_directive('MASTER', 'USERS',
0,60,0,0,0,0,0,0,NULL);
EXECUTE dbms_resource_manager.create_plan_directive('MASTER', 'REPORTS',
0,20,0,0,0,0,0,0,NULL);
EXECUTE dbms_resource_manager.create_plan_directive('MASTER','OTHER_GROUPS', 0,20,0,0,0,0,0,0,NULL);
EXECUTE dbms_resource_manager.create_plan_directive('USERS', 'ONLINE_USERS',
0,0,70,0,0,0,0,0,NULL);
EXECUTE dbms_resource_manager.create_plan_directive('USERS', 'BATCH_USERS',   0,0,30,0,0,0,0,0,NULL);
EXECUTE
dbms_resource_manager.create_plan_directive('REPORTS','ONLINE_REPORTS',0,0,70,0,0,0,0,0,NULL);
EXECUTE dbms_resource_manager.create_plan_directive('REPORTS','BATCH_REPORTS', 0,0,30,0,0,0,0,0,NULL);
--
-- Verify Plan
--
EXECUTE dbms_resource_manager.validate_pending_area;
--
-- Submit Plan
--
EXECUTE dbms_resource_manager.submit_pending_area;
spool off
set echo off

Notice how the script in figure 3 follows the chart in Figure 2. These are the proper steps to create a resource plan. Figure 4 shows the results from running the script in Figure 3.

Figure 4 Example run of script to create example resource plan

SQL> -- Grant system privilege to plan administrator
SQL> --
SQL> execute
dbms_resource_manager_privs.grant_system_privilege('SYSTEM','ADMINISTER_RESOURCE_MANAGER',TRUE);