通過Oracle 11g 邏輯standby實現BI的需求

Applies to:

Goal

Step by Step Guide on How to Create Logical Standby

Solution

Prerequisite
--必要條件
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 dataguard documentation for a complete list of data type and storage type considerations.
--確定資料庫中的資料型別和儲存型別支援
2 : Ensure Table Rows in the Primary Database Can Be Uniquely Identified.
--確定表中各行的唯一性,儘量有主鍵或唯一索引
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:

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:

Creating a Logical Standby Database:
--建立邏輯standby
Step 1 Create a Physical Standby Database
--建邏輯standby,要先建一個物理standby,然後再進行轉換
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)
http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/create_ps.htm#i63561

For 11.1:
Oracle? Data Guard Concepts and Administration 11g Release 1 (11.1)
http://download.oracle.com/docs/cd/B28359_01/server.111/b28294/create_ps.htm#i63561

Step 2 Make Sure that Physical Standby is in Sync with Primary Database
--在物理standby上執行,檢視跟主庫的同步情況
Use following query on Standby to check:

There should not be any difference in Last Seq Received and Last Seq Applied on Physical Standby.

Step 3 Stop Redo Apply on the Physical Standby Database
--停止物理standby的redo應用

Step 4 Set Parameters for Logical Standby in Primary
--設定主庫的日誌歸檔目錄,LOG_ARCHIVE_DEST_3的設定是為了主庫切換後使用
4.1. Change VALID_FOR in LOG_ARCHIVE_DEST_1 on Primary to (ONLINE_LOGFILES,ALL_ROLES)

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

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
--在主庫上生成logminer字典資訊

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
--轉換物理standby到邏輯standby,db_name是要指定一個新的邏輯standby 的db_name

For db_name, specify a database name to identify the new logical standby database. If you are using a spfile for standby, then command will update the db_name parameter otherwise it will issues a message reminding you to set the name of the DB_NAME parameter after shutting down the database.

Step 7 Create a New Password File for Logical Standby Database
--新建密碼檔案,這個在10g需要執行,11g原有的就可以

This step is required in 10.2 only and should not be performed in 11g.

Step 8 Shutdown and Startup Logical Standby Database in Mount Stage
--關閉邏輯standby並開戶到mount狀態

Step 9 Adjust Initialization Parameter on Logical Standby Database
--轉換為邏輯standby後,修改原有的日誌歸檔目錄

Step 10 Open the Logical Standby Database
--以resetlogs方式開啟資料庫

Step 11 Start Logical Apply on Standby
--開啟sql apply,這裡的immediate是要實時應用,需要有standby redo log支援,如果不需要實時應用,可不加immediate.