Home Database Mysql Tutorial 通过Oracle 11g 逻辑standby实现BI的需求

通过Oracle 11g 逻辑standby实现BI的需求

Jun 07, 2016 pm 03:10 PM

逻辑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 ;

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

Reduce the use of MySQL memory in Docker Reduce the use of MySQL memory in Docker Mar 04, 2025 pm 03:52 PM

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

How to solve the problem of mysql cannot open shared library How to solve the problem of mysql cannot open shared library Mar 04, 2025 pm 04:01 PM

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

How do you alter a table in MySQL using the ALTER TABLE statement? How do you alter a table in MySQL using the ALTER TABLE statement? Mar 19, 2025 pm 03:51 PM

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

Run MySQl in Linux (with/without podman container with phpmyadmin) Run MySQl in Linux (with/without podman container with phpmyadmin) Mar 04, 2025 pm 03:54 PM

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

What is SQLite? Comprehensive overview What is SQLite? Comprehensive overview Mar 04, 2025 pm 03:55 PM

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

Running multiple MySQL versions on MacOS: A step-by-step guide Running multiple MySQL versions on MacOS: A step-by-step guide Mar 04, 2025 pm 03:49 PM

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

How do I configure SSL/TLS encryption for MySQL connections? How do I configure SSL/TLS encryption for MySQL connections? Mar 18, 2025 pm 12:01 PM

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]

What are some popular MySQL GUI tools (e.g., MySQL Workbench, phpMyAdmin)? What are some popular MySQL GUI tools (e.g., MySQL Workbench, phpMyAdmin)? Mar 21, 2025 pm 06:28 PM

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

See all articles