Oracle 9i R2 配置 Logical Standby

最近因業務量越來大，正在考慮將資料庫將OLTP和OLAP分離的方案。分案的基本如下

主庫 負責OLTP 業務，對外以供一些即時的查詢。例如：當天充值明細查詢

備庫 和主庫進行同步，對外提供一些歷史明細資料查詢。例如：歷史充值明細查詢。從主庫的歸檔日誌中獲取SQL語句，然後執行，從而實現和主庫的同步。很多書例如pinner 的Oracle高可用那本都建議用Oracle10g 的 Logical Standby實現同步。但是對於我們來說升級到Oracle10g 代價比較大，系統割接比較麻煩。綜合考慮，我們還是在Oracle9i 的基礎上實現 Logical Standby

分析庫 從備庫中獲取資料，生成彙總資料，進行資料分析，對外提供彙總報表查詢功能。例如：充值彙總報表，酬金結算報表等等

現在將在Linux Red Hat 4 Update 5 上的案例和大家進行分享

1.主庫設定成歸檔模式
並檢查

2. 確認主庫中所有表都具備唯一約束

邏輯STANDBY需要確保主庫中每一行每一列的更新都能被日誌應用到邏輯備用庫裡
面，因此最好主庫中的表都具備唯一約束。可以透過檢視DBA_LOGSTDBY_NOT_UNIQUE來查詢主庫中不具備唯一約束的表。如果表不具備唯一約束的話，建議建立一個RELY的唯一約束：ALTER TABLE mytab ADD PRIMARY KEY (id, name) RELY DISABLE;
如果不具備唯一約束的話，那麼ORACLE的supplemental logging特性可以自動產生用於標識主庫中每一行更改的資訊，使得邏輯STANDBY能夠同步這些變更。
檢視是否啟用了supplemental logging特性：

SQL> SELECT SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI FROM V$DATABASE;
SUP SUP
--- ---
NO NO

如果未啟用的話，那麼開啟supplemental logging特性：

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE INDEX) COLUMNS;

SQL> SELECT SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI FROM V$DATABASE;
SUP SUP
--- ---
YES YES

4. 確保主庫是歸檔模式： archive log list

5. 主庫log_parallelism引數必須設定為1：
show parameter log_parallelism

6. 主庫 建立一個logminer所用的表空間,用於日誌分析
CREATE TABLESPACE logminer DATAFILE '/u02/oradata/devdb1/logminer01.dbf' size 50M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;

用 sys 使用者 以SYSDBA登入

EXECUTE DBMS_LOGMNR_D.SET_TABLESPACE('logminer');

7. 執行DBMS_LOGSTDBY.BUILD生成邏輯STANDBY所需LOGMINER字典資訊：
EXECUTE DBMS_LOGSTDBY.BUILD;

8. 歸檔當前日誌，並記錄包含建立邏輯STANDBY字典資訊的最新歸檔日誌，將最新歸檔COPY至STANDBY的相應歸檔目錄：

alter system switch logfile;

查詢到這個歸檔日誌檔案，並複製到備庫的 standby_archive_dest 引數指定的目錄中
SELECT NAME FROM V$ARCHIVED_LOG WHERE DICTIONARY_BEGIN='YES' and STANDBY_DEST='NO';

9.主庫 shutdown immediate

10.複製主庫的表空間檔案、Redo、控制檔案複製到備庫的對應目錄(採用冷備份方式)，如果是做物理Standby就不用複製控制檔案了

11.複製主庫的引數檔案到備庫（這種情況在主庫和備庫硬體環境相似的情況下成立，如果差異比較大應重新調整引數檔案）

12.備庫生成密碼檔案
/u01/app/oracle/ora920/bin/orapwd file=/u01/app/oracle/product/10.2.0/db_1/dbs/orapwdevdb1 password=install

13.修改備用資料庫引數檔案，新增下列引數

utl_file_dir='/u01/app/oracle'
standby_archive_dest='/u02/oradata/devdb1/stdarch'
fal_server='PRIMARY'
fal_client='STANDBY'
standby_file_management='AUTO'

#設定為9，必須設定，因為STANDBY日誌恢復里程會啟動相應的並行程式：
parallel_max_servers=9

14.修改主庫及備用資料庫的TNSNAMES.ora新增下列內容

PRIMARY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.131)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = devdb1.freelynet.com)
)
)

STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.132)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = devdb1.freelynet.com)
)
)

主庫備庫啟動 lsnrctl start
主庫測試 TNS
tnsping primary
tnsping standby
備庫測試 TNS
tnsping primary
tnsping standby

15. 啟動備用資料庫
startup mount pfile='/u01/app/oracle/admin/devdb1/scripts/initstandby.ora';

16. 啟用STANDBY保護模式並開啟資料庫：
ALTER DATABASE GUARD ALL;

17、STANDBY建立一個新的預設臨時表空間，然後Drop掉原來的那個，在按原來的樣子重新建立一個，再把新建的這個表控制去除
目的只有一個：複製過來的臨時表空間中有主庫的臨時檔案資訊，必須清除否則要不STANDBY將無法配置成功。

18、手工註冊COPY過來的歸檔日誌檔案：

ALTER DATABASE REGISTER LOGICAL LOGFILE '/u02/oradata/devdb1/stdarch/1_48.dbf';

19. 啟動STANDBY日誌應用模式：
ALTER DATABASE START LOGICAL STANDBY APPLY INITIAL;

監控 Alter 檔案如果成功的話有下列輸出
....
Wed May 7 17:10:22 2008
ALTER DATABASE START LOGICAL STANDBY APPLY INITIAL
ALTER DATABASE START LOGICAL STANDBY APPLY
with optional part
INITIAL
Attempt to start background Logical Standby process
LSP0 started with pid=12, OS id=3957
Wed May 7 17:10:22 2008
Completed: ALTER DATABASE START LOGICAL STANDBY APPLY INITIAL
Wed May 7 17:10:22 2008
LOGSTDBY event: ORA-16111: log mining and apply setting up
LOGMINER: reader process P000 started with pid=13 OS id=3959
LOGMINER: builder process P001 started with pid=14 OS id=3961
LOGMINER: preparer process P002 started with pid=15 OS id=3963
LOGMINER: Apply Slave process P004 started with pid=17 OS id=3967
LOGMINER: Apply Slave process P005 started with pid=18 OS id=3969
LOGMINER: Fetch Slave process P003 started with pid=16 OS id=3965
LOGMINER: Apply Slave process P007 started with pid=20 OS id=3973
LOGMINER: Apply Slave process P008 started with pid=21 OS id=3975
LOGMINER: Apply Slave process P006 started with pid=19 OS id=3971

然後 ps -ef |grep oracle 多了下列這些程式
oracle 3957 1 2 17:10 ? 00:00:28 ora_lsp0_devdb1
oracle 3959 1 0 17:10 ? 00:00:07 ora_p000_devdb1
oracle 3961 1 1 17:10 ? 00:00:19 ora_p001_devdb1
oracle 3963 1 0 17:10 ? 00:00:04 ora_p002_devdb1
oracle 3965 1 1 17:10 ? 00:00:23 ora_p003_devdb1
oracle 3967 1 0 17:10 ? 00:00:05 ora_p004_devdb1
oracle 3969 1 0 17:10 ? 00:00:00 ora_p005_devdb1
oracle 3971 1 0 17:10 ? 00:00:00 ora_p006_devdb1
oracle 3973 1 0 17:10 ? 00:00:00 ora_p007_devdb1
oracle 3975 1 0 17:10 ? 00:00:00 ora_p008_devdb1

20. 啟動主庫,設定歸檔路徑
startup

#採用最大效能模式，在日誌切換時將歸檔日誌檔案傳送到備用庫。
alter system set log_archive_dest_2='service=standby mandatory reopen=60';

21. 主庫執行一下DDL操作和DML操作，但是有些DDL操作、DML操作和資料型別不能被Logical Standby 支援

邏輯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

邏輯STANDBY不支援的資料型別：NCLOB, LONG, LONG RAW, BFILE, ROWID, and UROWID
邏輯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;

邏輯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

22。測試如下：
在主庫的 scott 使用者下，
drop table emp;
create table t as select * from all_objects;

執行日誌切換
alter system switch logfile;

主庫主庫 alter 日誌輸出

Thread 1 advanced to log sequence 53
Current log# 3 seq# 53 mem# 0: /u02/oradata/devdb1/redo03.log
Wed May 7 23:04:15 2008
ARC1: Evaluating archive log 2 thread 1 sequence 52
ARC1: Beginning to archive log 2 thread 1 sequence 52
Creating archive destination LOG_ARCHIVE_DEST_2: 'standby'
Creating archive destination LOG_ARCHIVE_DEST_1: '/u02/oradata/devdb1/archive/1_52.dbf'
ARC1: Completed archiving log 2 thread 1 sequence 52

備庫 alter 日誌輸出

Wed May 7 17:41:38 2008
LOGSTDBY event: ORA-16204: DDL successfully applied
LOGSTDBY stmt: drop table EMP
LOGSTDBY event: ORA-16204: DDL successfully applied
LOGSTDBY stmt: create table t as select * from all_objects

登入 備庫發現，備庫裡面 emp表已經消失，然後多了一個T表

備用資料庫啟動過程
startup open pfile='/u01/app/oracle/admin/devdb1/scripts/initstandby.ora';

啟動 Logical Standby Apply
alter database start logical standby apply

停止 Logical Standby Apply
alter database stop logical standby apply;

[@more@]