Oracle11g Concepts,通过Oracle11g逻辑standby实现BI的需求

5268f80b9b1e01f982625ef6fac83ca1.png

逻辑standby用的很少,特别是在11g,物理standby也可以只读打开并实时应用,物理standby也可以做滚动升级.不过,最近有一个用户需

逻辑standby用的很少,特别是在11g,物理standby也可以只读打开并实时应用,物理standby也可以做滚动升级.不过,最近有一个用户需求,要建一个报表系统数据库,想到可以试试逻辑standby去实现,同步生产库的基础数据用户,并在逻辑standby上建BI相关的用户,,用做数据统计。这样可以避免直接通过dblink或物化视图抓取数据对生产库的性能影响,又比利用goldengate实现同步在维护性上方便。

一.创建逻辑standby官方文档及注释:

Step by Step Guide on How to Create Logical Standby (文档 ID 738643.1)

Applies to:

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

--必要条件

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:

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:

--创建逻辑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)

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

--在物理standby上执行,查看跟主库的同步情况

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 Last Sequence Applied

1 60 60

*/

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应用

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

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)

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

--在主库上生成logminer字典信息

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

--转换物理standby到逻辑standby,db_name是要指定一个新的逻辑standby 的db_name

SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY ;

1428d0e076c3959ab11d28a39bc84fab.png

5268f80b9b1e01f982625ef6fac83ca1.png

本条技术文章来源于互联网,如果无意侵犯您的权益请点击此处反馈版权投诉

本文系统来源:php中文网

  • 0
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值