Oracle DataGuard 之--Physical DG转换Logical DG

OracleDataGuard之--PhysicalDG转换LogicalDG一、PhysicalDG和LogicalDGPhysicalStandby使用的是MediaRecovery技术，在数据块级..

二、物理备库升级逻辑备库准备

1） 确认主库包含逻辑STANDBY支持的表和数据类型

1、 逻辑STANDBY支持的数据类型：

CHAR
NCHAR
VARCHAR2 and VARCHAR
NVARCHAR2
NUMBER
DATE
TIMESTAMP
TIMESTAMP WITH TIME ZONE
TIMESTAMP WITH LOCAL TIME ZONE
INTERVAL YEAR TO MONTH
INTERVAL DAY TO SECOND
RAW
CLOB
BLOB

2、逻辑STANDBY不支持的数据类型：NCLOB, LONG, LONG RAW, BFILE, ROWID, and UROWID
3、逻辑STANDBY不支持的表和序列：

Tables and sequences in the SYS schema
Tables with unsupported datatypes
Tables used to support functional indexes
Tables used to support materialized views
Global temporary tables

逻辑STANDBY不支持的表和列可以通过视图dba_logstdby_unsupported来查询：

SELECT * FROM DBA_LOGSTDBY_UNSUPPORTED;

4、逻辑STANDBY不支持的SQL语句操作：

ALTER DATABASE
ALTER SESSION
ALTER SNAPSHOT
ALTER SNAPSHOT LOG
ALTER SYSTEM SWITCH LOG
CREATE CONTROL FILE
CREATE DATABASE
CREATE DATABASE LINK
CREATE PFILE FROM SPFILE
CREATE SCHEMA AUTHORIZATION
CREATE SNAPSHOT
CREATE SNAPSHOT LOG
CREATE SPFILE FROM PFILE
CREATE TABLE AS SELECT FROM A CLUSTER TABLE
DROP DATABASE LINK
DROP SNAPSHOT
DROP SNAPSHOT LOG
EXPLAIN
LOCK TABLE
RENAME
SET CONSTRAINTS
SET ROLE
SET TRANSACTION

2）通过物理备库升级到逻辑备库

1、查看数据库角色

16:03:27 SYS@ prod>select name,database_role from v$database;

NAME DATABASE_ROLE
--------- ----------------
PROD PRIMARY

Elapsed: 00:00:00.01

------在logical standby 包含不支持的数据库类型而无法复制的表

11:50:13 SYS@ prod>select distinct owner,table_name from dba_logstdby_unsupported;

OWNER TABLE_NAME
------------------------------ ------------------------------
PM ONLINE_MEDIA
IX AQ$_ORDERS_QUEUETABLE_H
IX AQ$_STREAMS_QUEUE_TABLE_T
IX AQ$_STREAMS_QUEUE_TABLE_G
OE CATEGORIES_TAB
OE CUSTOMERS
IX ORDERS_QUEUETABLE
IX AQ$_STREAMS_QUEUE_TABLE_C
IX AQ$_ORDERS_QUEUETABLE_I
IX STREAMS_QUEUE_TABLE
IX AQ$_STREAMS_QUEUE_TABLE_H
OE WAREHOUSES
IX AQ$_ORDERS_QUEUETABLE_G
IX AQ$_ORDERS_QUEUETABLE_S
IX AQ$_ORDERS_QUEUETABLE_L
IX AQ$_STREAMS_QUEUE_TABLE_I
PM PRINT_MEDIA