建立Data guard 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

[@more@]

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。

2. 在主庫中執行下面語句，在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

3. 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