建立 Logical Standby Database[@more@]

一、確認主庫包含邏輯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 RAW
BFILE
ROWID
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來查詢：
SQL> 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

確認主資料庫是否包含不支援的物件能夠查詢資料字典表dba_logstdby_unsupported:
SQL> select distinct owner,table_name from dba_logstdby_unsupported
order by owner,table_name;

用以下的方式檢視上面所得出的表的欄位名和資料型別:
SQL> select column_name,data_type from dba_logstdby_unsupported
where owner=oe and table_name = customers;

二、確認主庫中所有表都具備唯一約束

邏輯STANDBY需要確保主庫中每一行每一列的更新都能被日誌應用到邏輯備用庫裡面，因此最好主庫中的表都具備唯一約束。可以透過檢視DBA_LOGSTDBY_NOT_UNIQUE來查詢主庫中不具備唯一約束的表。

找出沒有主鍵的表:
SQL> select owner, table_name, bad_column from dba_logstdby_not_unique
where table_name not in (select table_name from dba_logstdby_unsupported);

選擇出來看bad_column欄位值.假如此值為n表示這個表關於沒有主鍵欄位列有足夠的列資訊傳到備用資料庫中.假如此值為y表示資訊不夠必須對這個表加一個disable rely constraint以使關於這個表的log能夠apply到邏輯備用庫中.
選擇出來只有system使用者下的表repcat$_runtime_parms需要做修改,然後檢視這個表的結構.然後做修改如下:
SQL> desc system.repcat$_runtime_parms
SQL> alter table system.repcat$_runtime_parms add primary key (runtime_parm_id,parameter_name) rely disable;

修改完以後再做上面的查詢以確認沒有表需要再做修改:
SQL> select owner, table_name, bad_column from dba_logstdby_not_unique
where table_name not in (select table_name from dba_logstdby_unsupported);
owner table_name b
------------------------------ ------------------------------ -
outln ol$hints n
outln ol$nodes n
system mview$_adv_basetable n
system mview$_adv_sqldepend n
system mview$_adv_filterinstance n
system def$_origin n
system repcat$_snapgroup n
system repcat$_ddl n
system repcat$_resolution_statistics n
system mview$_adv_index n

如果表不具備唯一約束的話，建議建立一個RELY的唯一約束：
SQL> 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

三、主庫其他檢查事項

1、確保主庫是歸檔模式

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oraclelog
Oldest online log sequence 12800
Next log sequence to archive 12804
Current log sequence 12804

2、 log_parallelism引數必須設定為1：
SQL> show parameter log_parallelism
NAME TYPE VALUE
---------------- ----------- -------
log_parallelism integer 1

3、建立一個logminer所用的表空間：

建立邏輯STANDBY物件的預設表空間是SYSTEM表空間，為了減少對系統表空間的影響，我們建立一個專門的表空間用於邏輯STANDBY：
SQL> CREATE TABLESPACE logminer DATAFILE ‘d:/oracle/oradata/xue9/logminer01.dbf’ size 50M;
SQL> EXECUTE DBMS_LOGMNR_D.SET_TABLESPACE(‘logminer’);

四、邏輯STANDBY配置主庫端操作：

1、用rman全庫遷移。

2、MOUNT主庫，生成備用庫控制檔案：
SQL> startup mount
ORACLE instance started.
Total System Global Area 80813392 bytes
Fixed Size 453968 bytes
Variable Size 54525952 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.

Sql> ALTER DATABASE BACKUP CONTROLFILE TO 'd:/oracle/oradata/standby/control01.ctl';
Database altered.

4、開啟主庫，執行DBMS_LOGSTDBY.BUILD生成邏輯STANDBY所需LOGMINER字典資訊：建立logminer字典(logical standby database在分解redo log為sql語句時需要logminer工具,而這個工具在使用之前需要建立logminer字典):

SQL> alter database open;
Database altered.

SQL> EXECUTE DBMS_LOGSTDBY.BUILD;
PL/SQL procedure successfully completed.

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

SQL> alter system switch logfile;
System altered.

SQL> select name from v$archived_log where dictionary_begin='yes' and standby_dest='no';

6、生成STANDBY初始化引數檔案：

SQL> create pfile=’d:/init.ora’ from spfile

五、邏輯STANDBY配置：

1、更改初始化引數檔案：

注意更改control_files、background_dump_dest等指定的相應目錄，這些目錄必須預先建立好，注意新增以下幾個引數：
standby_archive_dest：指定備用庫的歸檔路徑
parallel_max_servers：設定為9，必須設定，因為STANDBY日誌恢復里程會啟動相應的並行程式：
LOGMINER: Fetch Slave process P003 started with pid=17 OS id=3632
LOGMINER: Apply Slave process P004 started with pid=18 OS id=1888
LOGMINER: Apply Slave process P005 started with pid=19 OS id=2292
LOGMINER: Apply Slave process P006 started with pid=20 OS id=3336
LOGMINER: Apply Slave process P007 started with pid=21 OS id=3824
LOGMINER: Apply Slave process P008 started with pid=22 OS id=1788

instance_name：如果是同機配置STANDBY的話，必須指定和主庫不同的例項名

更改後的引數檔案如下：
*.aq_tm_processes=1
*.background_dump_dest='$ORACLE_BASE'
*.compatible='10.2.0.3.0'
*.control_files='$ORACLE_BASE/oradata/STANDBY/control01.ctl','$ORACLE_BASE/oradata/STANDBY/control02.ctl','$ORACLE_BASE/oradata/STANDBY/control03.ctl'
*.core_dump_dest='$ORACLE_BASE /admin/STANDBY/cdump'
*.db_block_size=8192
*.db_cache_size=25165824
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='gpicdb0'
*.fast_start_mttr_target=300
*.hash_join_enabled=TRUE
*.instance_name='gpicdb0'
*.java_pool_size=8388608
*.job_queue_processes=10
*.large_pool_size=8388608
*.log_archive_dest_1='location=/orarach'
*.log_archive_start=TRUE
*.open_cursors=300
*.pga_aggregate_target=12582912
*.processes=150
*.query_rewrite_enabled='FALSE'
*.remote_login_passwordfile='EXCLUSIVE'
*.shared_pool_size=20971520
*.sort_area_size=524288
*.star_transformation_enabled='FALSE'
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_retention=5400
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='$ORACLE_BASE /admin/STANDBY/udump'
*.STANDBY_ARCHIVE_DEST='$ORACLE_BASE /oradata/STANDBY/archive'
*.LOG_ARCHIVE_FORMAT=ARC%S.%T
*.lock_name_space=STANDBY
*.standby_file_management='AUTO'
*.parallel_max_servers=9
*.log_parallelism=1

3、將備用庫的控制檔案按引數檔案中的設定COPY 3份並更名，MOUNT資料庫，更改資料檔案和日誌檔案和路徑

4、更改資料庫名字並啟動資料庫（這步可做可不做）：

5、STANDBY建立臨時檔案，如果v$tempfile有記錄主庫的臨時檔案資訊，必須先offline drop掉後再建立。這步必須要做，要不STANDBY將無法配置成功。

idle> select * from v$tempfile;
no rows selected
Elapsed: 00:00:00.01
idle> alter tablespace temp add tempfile 'd:/oracle/oradata/standby/temp01.dbf' size 50M reuse;
Tablespace altered

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

idle> ALTER DATABASE REGISTER LOGICAL LOGFILE 'D:/oracle/oradata/STANDBY/archive
/ARC00094.001';
Database altered

７、啟動STANDBY日誌應用模式：

idle> ALTER DATABASE START LOGICAL STANDBY APPLY INITIAL;
Database altered

六、配置listener.ora和tnsnames.ora：

listener.ora新增STANDBY的配置資訊：
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = //oracle/app/oracle10g)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = gpicdb0)
(ORACLE_HOME = /oracle/app/oracle10g)
(SID_NAME = gpicdb0)
)
(SID_DESC =
(GLOBAL_DBNAME = standby)
(ORACLE_HOME = /oracle/app/oracle10g)
(SID_NAME = standby)
)
(SID_DESC =
(GLOBAL_DBNAME = XUE9)
(ORACLE_HOME = /oracle/app/oracle10g)
(SID_NAME = gpicdb0)
)
)

Tnsnames.ora新增STANDBY的連線字：
primary =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 100.250.128.21)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME =gpicdb0)
)
)
STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 100.250.128.24)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = standby)
)
)

配置完成後重啟監聽：lsnrctl reload

七、主庫配置STANDBY的歸檔資訊：

SQL> alter system set log_archive_dest_2='service=standby lgwr';
SQL> alter system set log_archive_dest_state_2=enable;

至此，邏輯STANDBY配置完成。

八、測試日誌應用：

主庫端操作：
SQL> create user roby identified by roby;
SQL> grant dba to roby;
SQL> alter user roby default tablespace users;
SQL> alter system switch logfile
SQL> conn roby/roby
Connected.

SQL> create table t(a int);
SQL> insert into t values(1);
SQL> insert into t values(1);
SQL> insert into t values(1);
SQL> insert into t values(1);
SQL> commit;
SQL> alter system switch logfile;
SQL> alter system switch logfile;
SQL> alter system switch logfile;
SQL> select * from t;
A
----------
1
1
1
1
Elapsed: 00:00:00.00

備用庫端檢查：

Alert log記錄的資訊：

程式碼:
LOGSTDBY stmt: create user roby identified by VALUES '3937FA8E626D1ADE'
LOGSTDBY event: ORA-16204: DDL successfully applied
LOGSTDBY stmt: grant dba to roby
LOGSTDBY event: ORA-16204: DDL successfully applied
LOGSTDBY stmt: alter user roby default tablespace users
Thu Jun 21 13:58:15 2007
LOGSTDBY event: ORA-16204: DDL successfully applied
LOGSTDBY stmt: create table t(a int)
Thu Jun 21 14:05:16 2007
LOGSTDBY event: ORA-16204: DDL successfully applied

SQLPLUS登入進去，可以看到建立的表已經成功同步了：
idle> conn roby/roby
Connected.

idle> select * from t;
A
----------
1
1
1
1

啟用STANDBY保護模式並開啟資料庫：
idle> ALTER DATABASE GUARD ALL;
Database altered.

idle> ALTER DATABASE OPEN RESETLOGS;
Database altered．

建立 Logical Standby Database

相關文章