Step by Step Guide on How to Create Logical Standby [ID 738643.1]
Goal
Step by Step Guide on How to Create Logical StandbySolution
Prerequisite1 : 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:
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:
Step 1 Create a Physical Standby Database 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
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;
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
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Step 4 Set Parameters for Logical Standby in Primary
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 received 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
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
SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY ;
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
$ORAPWD FILE= PASSWORD= ENTRIES=
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
SQL> SHUTDOWN;
SQL> STARTUP MOUNT;
Step 9 Adjust Initialization Parameter on Logical Standby Database
LOG_ARCHIVE_DEST_1= 'LOCATION=/u01/arch/online/ VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=logstd1'
LOG_ARCHIVE_DEST_2= 'SERVICE=prim1 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prim1'
LOG_ARCHIVE_DEST_3= 'LOCATION=/u01/arch/standby/ VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=logstd1'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_DEST_STATE_3=ENABLE
Step 10 Open the Logical Standby Database
SQL> ALTER DATABASE OPEN RESETLOGS;
Step 11 Start Logical Apply on Standby
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/1933/viewspace-1049313/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 10GR2下建立物理standby STEP BY STEP
- React Step by StepReact
- Command 模式 Step by Step模式
- Sitecore10 Demo演示環境Azure一鍵部署(Step By Step Guide to installing Sitecore10 in Azure Paas)GUIIDE
- Promise的實現(step by step)Promise
- Shell Step by Step (4) —— Cron & Echo
- Step by Step 安裝 BizTalk Server 2009Server
- Step by Step Data Replication Using Oracle GoldenGateOracleGo
- TIDB DM資料同步step by stepTiDB
- 單步除錯 step into/step out/step over 區別詳解除錯
- 實時 Linux 抖動分析 Step by stepLinux
- ABP應用開發(Step by Step)-下篇
- ABP應用開發(Step by Step)-上篇
- Linkerd 2.10(Step by Step)—多叢集通訊
- Sql Server Linux(Redhat) Distributed Availability Group Setup — step by stepSQLServerLinuxRedhatAI
- Step-by-step,打造屬於自己的vue ssrVue
- Oracle DG建立Logical Standby DatabaseOracleDatabase
- How to Optimize PostgreSQL Logical ReplicationSQL
- Adaboost Algorithm StepGo
- Linkerd 2.10(Step by Step)—使用 Kustomize 自定義 Linkerd 的配置
- 深度學習之step by step搭建神經網路深度學習神經網路
- Linkerd 2.10(Step by Step)—設定服務配置檔案
- [20181113]Logical Standby建立2.txt
- 使用Eclipse 安裝 構建Maven專案 (step-by-step)EclipseMaven
- Linkerd 2.10(Step by Step)—4. 如何配置外部 Prometheus 例項Prometheus
- HTML step 屬性HTML
- 【Step-By-Step】高頻面試題深入解析 / 週刊06面試題
- 【Step-By-Step】高頻面試題深入解析 / 週刊07面試題
- 【Step-By-Step】高頻面試題深入解析 / 週刊04面試題
- 【Step-By-Step】高頻面試題深入解析 / 週刊05面試題
- 【Step-By-Step】高頻面試題深入解析 / 週刊03面試題
- 【Step-By-Step】高頻面試題深入解析 / 週刊01面試題
- 【Step-By-Step】高頻面試題深入解析 / 週刊02面試題
- 阿里雲數倉Dataworks資料匯出到檔案step by step阿里
- Linkerd 2.10(Step by Step)—1. 將您的服務新增到 Linkerd
- Linkerd 2.10(Step by Step)—將 GitOps 與 Linkerd 和 Argo CD 結合使用GitGo
- 2.4.8 Step 7: 建立spfile
- step1 補充
- Vue.js SSR Step by Step (2) – 一個簡單的同構DEMOVue.js