Implement BI requirements through Oracle11g logical standby

Logical standby is rarely used, especially at 11 GB. Physical standby can be opened only and applied in real time. Physical standby can also be upgraded in a rolling manner. However, a user needs

Logical standby is rarely used, especially at 11 GB. Physical standby can be opened only and applied in real time. Physical standby can also be upgraded in a rolling manner. However, a user needs

Logical standby is rarely used, especially in 11 GB. Physical standby can be opened only and applied in real time. Physical standby can also be upgraded in a rolling manner. however, there is a user requirement recently. To create a report system database, you can try logical standby to synchronize the basic data users in the production database, and establish BI-related users on the logical standby for data statistics. This avoids the impact of data grabbing through dblink or materialized views on the performance of the production database, and Billy uses goldengate to achieve synchronization in maintainability.

1. Create official documentation and notes for logical standby:
Step by Step Guide on How to Create Logical Standby (Doc ID 738643.1)
Applies:
Oracle Database-Enterprise Edition-Version 10.2.0.1 to 12.1.0.2 [Release 10.2 to 12.1]
Information in this document applies to any platform.
* ** Checked for relevance on 21-Sep-2012 ***
* ** Reviewed for Relevance 16-Jul-2015 ***

Goal

Step by Step Guide on How to Create Logical Standby

Solution

Prerequisite
-- Prerequisites
1: Before setting up a logical standby database, ensure the logical standby database can maintain the data types and tables in your primary database. see Appendix C of the specified uard documentation for a complete list of data type and storage type considerations.
-- Determine the data type and storage type support in the database
2: Ensure Table Rows in the Primary Database Can Be Uniquely Identified.
-- Determine the uniqueness of each row in the table. Try to have a primary key or a unique index.
2.1: Find Tables Without Unique Logical Identifier in the Primary Database.

Use following query to display a list of tables that SQL Apply may not be able to uniquely identify:

SQL> SELECT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_NOT_UNIQUE WHERE (OWNER, TABLE_NAME) NOT IN (SELECT DISTINCT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED) AND BAD_COLUMN = 'y'

2.2: If your application ensures the rows in a table are unique, you can create a disabled primary key RELY constraint on the table. use alter table command to add a disabled primary-key RELY constraint.
The following example creates a disabled RELY constraint on a table named mytab, for which rows can be uniquely identified using the id and name columns:

SQL> ALTER TABLE mytab ADD PRIMARY KEY (id, name) RELY DISABLE;

Creating a Logical Standby Database:
-- Create logical standby
Step 1 Create a Physical Standby Database
-- To create a logical standby, you must first create a physical standby and then perform the conversion.
Create a Physical Standby Database and make sure that there is no error in remote archiving to Standby from Primary Database.

Please refer following documentations for creating physical standby database:

For 10.2:
Oracle? Data Guard Concepts and Administration 10g Release 2 (10.2)

For 11.1:
Oracle? Data Guard Concepts and Administration 11g Release 1 (11.1)

Step 2 Make Sure that Physical Standby is in Sync with Primary Database
-- Run the command on the physical standby to check the synchronization with the master database.
Use following query on Standby to check:

SQL> SELECT ARCH. THREAD # "Thread", ARCH. SEQUENCE # "Last Sequence Received", APPL. SEQUENCE # "Last Sequence Applied"
FROM
(Select thread #, SEQUENCE # from v $ ARCHIVED_LOG WHERE (THREAD #, FIRST_TIME) IN (select thread #, MAX (FIRST_TIME) from v $ ARCHIVED_LOG group by thread #) ARCH,
(Select thread #, SEQUENCE # from v $ LOG_HISTORY WHERE (THREAD #, FIRST_TIME) IN (select thread #, MAX (FIRST_TIME) from v $ LOG_HISTORY group by thread #) APPL
WHERE
ARCH. THREAD # = APPL. THREAD #
Order by 1;
/*
Thread Last Sequence Received Ed Last Sequence Applied
1 60 60
*/

There shoshould not be any difference in Last Seq passed Ed and Last Seq Applied on Physical Standby.

Step 3 Stop Redo Apply on the Physical Standby Database
-- Stop the redo application of physical standby

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Step 4 Set Parameters for Logical Standby in Primary
-- Set the log archiving directory of the master database. LOG_ARCHIVE_DEST_3 is set to be used after the master database is switched
4.1. Change VALID_FOR in LOG_ARCHIVE_DEST_1 on Primary to (ONLINE_LOGFILES, ALL_ROLES)

LOG_ARCHIVE_DEST_1 = 'location =/u01/arch/online/VALID_FOR = (ONLINE_LOGFILES, ALL_ROLES) DB_UNIQUE_NAME = prim1'

4.2. Set LOG_ARCHIVE_DEST_3 for logs which will validate ed on Standby from Primary

LOG_ARCHIVE_DEST_3 = 'location =/u01/arch/standby/VALID_FOR = (STANDBY_LOGFILES, STANDBY_ROLE) DB_UNIQUE_NAME = prim1'
LOG_ARCHIVE_DEST_STATE_3 = ENABLE

Note: LOG_ARCHIVE_DEST_3 only takes effect when the primary database is transitioned to the logical standby role.

Step 5 Build a Dictionary in the Redo Data on Primary Database
-- Generate logminer dictionary information on the master database

SQL> EXECUTE DBMS_LOGSTDBY.BUILD;

The DBMS_LOGSTDBY.BUILD procedure waits for all existing transactions to complete. Long-running transactions executed on the primary database will affect the timeliness of this command.

Step 6 Convert to a Logical Standby Database
-- Convert physical standby to logical standby. db_name is the db_name to specify a new logical standby.

SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY ;