通过Oracle 11g 逻辑standby实现BI的需求
逻辑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

Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

This article explores optimizing MySQL memory usage in Docker. It discusses monitoring techniques (Docker stats, Performance Schema, external tools) and configuration strategies. These include Docker memory limits, swapping, and cgroups, alongside

This article addresses MySQL's "unable to open shared library" error. The issue stems from MySQL's inability to locate necessary shared libraries (.so/.dll files). Solutions involve verifying library installation via the system's package m

The article discusses using MySQL's ALTER TABLE statement to modify tables, including adding/dropping columns, renaming tables/columns, and changing column data types.

This article compares installing MySQL on Linux directly versus using Podman containers, with/without phpMyAdmin. It details installation steps for each method, emphasizing Podman's advantages in isolation, portability, and reproducibility, but also

This article provides a comprehensive overview of SQLite, a self-contained, serverless relational database. It details SQLite's advantages (simplicity, portability, ease of use) and disadvantages (concurrency limitations, scalability challenges). C

This guide demonstrates installing and managing multiple MySQL versions on macOS using Homebrew. It emphasizes using Homebrew to isolate installations, preventing conflicts. The article details installation, starting/stopping services, and best pra

Article discusses configuring SSL/TLS encryption for MySQL, including certificate generation and verification. Main issue is using self-signed certificates' security implications.[Character count: 159]

Article discusses popular MySQL GUI tools like MySQL Workbench and phpMyAdmin, comparing their features and suitability for beginners and advanced users.[159 characters]
