创建logical standby database的一些前提条件

1. 数据类型

1.1 Logical standby database 支持的数据类型

CHAR

NCHAR

VARCHAR2 and VARCHAR

NVARCHAR2

NUMBER

DATE

TIMESTAMP

TIMESTAMP WITH TIMEZONE

TIMESTAMP WITH LOCAL TIMEZONE

INTERVAL YEAR TO MONTH

INTERVAL DAY TO SECOND

RAW

CLOB and NCLOB

BLOB

LONG

LONG RAW

BINARY_FLOAT

BINARY_DOUBLE

1.2 Logical standby database 不支持的数据类型

BFILE

ROWID, UROWID

User-defined types

Collections (including VARRAYS and nested tables)

XML type

Encrypted columns

Multimedia data types (including Spatial, Image, and Context)

1. 不支持的表、序列和视图

在创建一个logical standby database前，需要识别出主库中那些不被支持的数据库对象。这些主库中不被支持的数据库对象（数据类型，表，序列，视图）的变化将不能被正确地反应到logical standby database，而且没有任何报错。下面有4个方面的对象不被支持（或者自动跳过）

■ 一些被跳过的schema，可以在主库通过查询DBA_LOGSTDBY_SKIP视图，获得详细信息

SQL>SELECT OWNER FROM DBA_LOGSTDBY_SKIP WHERE STATEMENT_OPT = 'INTERNAL SCHEMA';

■ 那些包含不支持数据类型的字段的表

■ 压缩的表

■ 加密的表

我们可以通过在主库上查询DBA_LOGSTDBY_UNSUPPORTED 视图，来确定到底那些对象是不被支持的。

SQL> SELECT DISTINCT OWNER,TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED

ORDER BY OWNER,TABLE_NAME;

OWNER TABLE_NAME

----------- --------------------------

HR COUNTRIES

OE ORDERS

OE CUSTOMERS

OE WAREHOUSES

可以通过查询DBA_LOGSTDBY_UNSUPPORTED 视图，确定到到底那些列不被支持，列的数据类型是什么？

SQL> SELECT COLUMN_NAME,DATA_TYPE FROM DBA_LOGSTDBY_UNSUPPORTED

WHERE OWNER='OE' AND TABLE_NAME = 'CUSTOMERS';

COLUMN_NAME DATA_TYPE

------------------------------- -------------------

CUST_ADDRESS CUST_ADDRESS_TYP

PHONE_NUMBERS PHONE_LIST_TYP

CUST_GEO_LOCATION SDO_GEOMETRY

我们在确定data guard备库的类型时一定要慎重，通过上面的查询可以知道主库的那些对象不能被logical stdby db支持，如果这些对象中有一些是核心的表，那我们只能采用physical standby database。

1. 在主库中运行下面语句，在Logical Stdby db中将被跳过（SKIP)

ALTER DATABASE

ALTER SESSION

ALTER MATERIALIZED VIEW

ALTER MATERIALIZED VIEW LOG

ALTER SYSTEM

CREATE CONTROL FILE

CREATE DATABASE

CREATE DATABASE LINK

CREATE PFILE FROM SPFILE

CREATE SCHEMA AUTHORIZATION

CREATE MATERIALIZED VIEW

CREATE MATERIALIZED VIEW LOG

CREATE SPFILE FROM PFILE

DROP DATABASE LINK

DROP MATERIALIZED VIEW

DROP MATERIALIZED VIEW LOG

EXPLAIN

LOCK TABLE

SET CONSTRAINTS

SET ROLE

SET TRANSACTION

1. Logical Stdby DB所支持的DDL语句

4.1 DBMS_LOGSTDBY.SKIP 过程的stmt参数及其关联的SQL语句

Keyword Associated SQL Statements

NON_SCHEMA_DDL All DDL that does not pertain to a particular schema

SCHEMA_DDL All DDL statements that create, modify, or drop schema objects (for example: tables, indexes, and columns)

DML Includes DML statements on a table (for example: INSERT,UPDATE, and DELETE)

CLUSTER CREATE CLUSTER

AUDIT CLUSTER

DROP CLUSTER

TRUNCATE CLUSTER

CONTEXT CREATE CONTEXT

DROP CONTEXT

DATABASE LINK CREATE DATABASE LINK

DROP DATABASE LINK

DIMENSION CREATE DIMENSION

ALTER DIMENSION

DROP DIMENSION

DIRECTORY CREATE DIRECTORY

DROP DIRECTORY

ROLLBACK STATEMENT CREATE ROLLBACK SEGMENT

ALTER ROLLBACK SEGMENT

DROP ROLLBACK SEGMENT

SEQUENCE CREATE SEQUENCE

DROP SEQUENCE

SESSION Log-ons

SYNONYM CREATE SYNONYM

DROP SYNONYM

SYSTEM AUDIT AUDIT SQL_statements

NOAUDIT SQL_statements

SYSTEM GRANT GRANT system_privileges_and_roles

REVOKE system_privileges_and_roles

TABLE CREATE TABLE

DROP TABLE

TRUNCATE TABLE

TABLESPACE CREATE TABLESPACE

DROP TABLESPACE

TRUNCATE TABLESPACE

TRIGGER CREATE TRIGGER

ALTER TRIGGER with ENABLE and DISABLE clauses

DROP TRIGGER

ALTER TABLE with ENABLE ALL TRIGGERS clause

ALTER TABLE with DISABLE ALL TRIGGERS clause

TYPE CREATE TYPE

CREATE TYPE BODY

ALTER TYPE

DROP TYPE

DROP TYPE BODY

USER CREATE USER

ALTER USER

DROP USER

VIEW CREATE VIEW

DROP VIEW

4.2 跳过DDL SQL 语句的语句选项（条件）

Statement Option SQL Statements and Operations

ALTER SEQUENCE ALTER SEQUENCE

ALTER TABLE ALTER TABLE

COMMENT TABLE COMMENT ON TABLE table, view, materialized view

COMMENT ON COLUMN table.column, view.column,materialized_view.column

DELETE TABLE DELETE FROM table, view

EXECUTE PROCEDURE CALL

Execution of any procedure or function or access to any variable,library, or cursor inside a package.

GRANT DIRECTORY GRANT privilege ON directory

REVOKE privilege ON directory

GRANT PROCEDURE GRANT privilege ON procedure, function, package

REVOKE privilege ON procedure, function, package

GRANT SEQUENCE GRANT privilege ON sequence

REVOKE privilege ON sequence

GRANT TABLE GRANT privilege ON table, view, materialized view

REVOKE privilege ON table, view, materialized view

GRANT TYPE GRANT privilege ON TYPE

REVOKE privilege ON TYPE

INSERT TABLE INSERT INTO table, view

LOCK TABLE LOCK TABLE table, view

SELECT SEQUENCE Any statement containing sequence.CURRVAL or

SELECT TABLE SELECT FROM table, view, materialized view

REVOKE privilege ON table, view, materialized view

UPDATE TABLE UPDATE table, view