下面的是logical standby database的配置步驟.
(下面用到的一些文件的位置都是臨時性的,還得做一定的修改)
將主數據庫置為force logging模式.在主數據庫創建之后做如下操作:
sql>alter database force logging;
確認主數據庫是歸檔的并定義好本地歸檔.如下:
sql >alter system set log_archive_dest_1='location=e:/oracle/oradata/orcl/archive mandatory' scope=both;
確認log_parallelism初始化參數值,logical standby database要求此參數的值為1,是個缺省值.如果查出來的參數值為1則不需要做修改,如果不是1值則要用alter system set命令做修改.具體操作如下:
sql>show parameter log_parameter------查看參數的當前值
sql>alter system set log_parallelism=1 scope=both;
修改完以后要關閉數據庫并重新啟動此參數才有效.
sql>shutdown immediate
sql>startup
確認支持的數據類型和表:
在建立logical standby database時,有些數據類型和表是支持的而有些是不支持的.
支持的數據類型有:
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
不支持的數據類型有:
nclob
long
long raw
bfile
rowid
urowid
user-defined types
object types refs
varrays
nested tables
不支持的對象類型有:
user-defined tables and sequences in the sys schema
tables with unsupported datatypes
tables using data segment compression
index-organized tables
確認主數據是否包含不支持的對象可以查詢數據字典表dba_logstdby_unsupported:
sql>select distinct owner,table_name from dba_logstdby_unsupported
2> order by owner,table_name;
用以下的方式查看上面所得出的表的字段名和數據類型:
sql> select column_name,data_type from dba_logstdby_unsupported
2> where owner='oe' and table_name = 'customers';
logical standby database會過濾的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
確保主數據庫中的表行是可以唯一確認的(即有表中有主鍵)
找出沒有主鍵的表:
sql> select owner, table_name, bad_column from dba_logstdby_not_unique
2> 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 repcat$_runtime_parms y
system mview$_adv_index n
選擇出來看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
2> 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
確認主數據庫可以補足日志(supplemental logging)
sql> select supplemental_log_data_pk, supplemental_log_data_ui from v$database;
sup sup
--- ---
no no (no值表示此時主數據庫是不支持補足日志的)
使主數據庫可以補足日志
sql> alter database add supplemental log data (primary key, unique index) columns;(這條語句將有關由主數據修改的行唯一信息可以通過日志傳到備用數據庫中并做log apply)
在主數據庫中做新的日志切換.因為在上一步做完enable supplemental logging之后.當前日志可能包含了有補足的日志和沒有補足的日志,而邏輯備用數據庫的log apply是不支持兩者一起的日志的.所以在創建邏輯備用數據庫之前要確保要從主數據庫傳到備用數據庫的所有日志都只包含補足日志
sql>alter system archive log current;
確認一下主數據庫此時支持補足日志
sql> select supplemental_log_data_pk, supplemental_log_data_ui from v$database;
sup sup
--- ---
yes yes (yes值表示此時主數據庫是支持補足日志的)
supplemental_log_data_pk:支持補足日志,其信息是primary key
supplemental_log_data_ui:支持補足日志,其信息是unique index
在主數據庫創建一個交替的表空間.并用dbms_logmnr_d.set_tablespace將某些表放到這個表空間中.因為在邏輯備用數據庫中有一些表是屬于sys和system用戶的,而這些是放在表空間的.這些表可能在一段時間后記錄猛增.為了使system表空間不會被用滿而導致數據庫down機,建立這個交替的表空間來存放這些表.
sql>create tablespace logmnrts datafile '/disk1/oracle/dbs/logmnrts.dbf'
2> size 25m autoextend on maxsize 2048m
3>segment space management auto;
sql> execute dbms_logmnr_d.set_tablespace('logmnrts');
(上面一步execute dbms_logmnr_d.set_tablespace('logmnrts');可以在以后的維護中再做相應的維護)
創建邏輯備用數據庫
確認主數據庫的數據文件和日志文件
在主數據庫中通過查詢v$datafile獲得數據文件的位置
sql> select name from v$datafile;
name
-----------------------------------------------------------
e:/oracle/ora92/orcl ystem01.dbf
e:/oracle/ora92/orcl/undotbs01.dbf
e:/oracle/ora92/orcl/example01.dbf
e:/oracle/ora92/orcl/indx01.dbf
e:/oracle/ora92/orcl/tools01.dbf
e:/oracle/ora92/orcl/users01.dbf
e:/oracle/ora92/orcl/logmnrts.dbf
e:/oracle/ora92/orcl/xdb01.dbf
在主數據庫中通過查詢v$logfile獲得日志文件的位置
sql> col member format a35
sql> select group#,type,member from v$logfile;
group# type member
---------- ------- -----------------------------------
1 online e:/oracle/ora92/orcl/redo01.log
2 online e:/oracle/ora92/orcl/redo02.log
3 online e:/oracle/ora92/orcl/redo03.log
對主數據庫做一次冷備份(全備份,拷貝所有需要的文件:數據文件,控制文件,參數文件)具體做法如下:
關閉數據庫:sql>shutdown immediate
將前面得到的數據文件拷貝到一個臨時的位置 f:/oracle
啟動數據庫到mount狀態下:sql>startup mount
為邏輯備用數據庫創建一個備份的控制文件:
sql> alter database backup controlfile to
2> 'f:/oracle/bkcontrol.ora';
將主數據庫放在restricted session模式下(在打開數據庫時以免用戶對數據庫進行dml和ddl操作): sql> alter system enable restricted session;
建立logminer字典(logical standby database在分解redo log為sql語句時需要logminer工具,而這個工具在使用之前需要建立logminer字典):
sql> alter database open;
sql> execute dbms_logstdby.build;
取消主數據庫的restricted session模式使用戶可以執行dml和ddl語句:
sql> alter system disable restricted session;
查看最近的歸檔日志(在后面的邏輯備用數據庫的創建過程中需要用到)
sql> alter system archive log current;(對當前日志做歸檔)
sql> select name from v$archived_log
2> where (sequence#=(select max(sequence#) from v$archived_log
3> where dictionary_begin = 'yes' and standby_dest= 'no'));
name
-----------------------------------------------------------------
e:/oracle/oradata/orcl/archive/1_19.dbf
上面的查詢其中where子句的dictionary_begin指明是要找出新建的字典, standby_dest指明是本地歸檔目錄而不是遠程歸檔.
為備用數據庫創建準備初始化參數:
sql> create pfile='f:/oracle/initstdby.ora' from spfile;
將前面三步所得到的數據文件,控制文件和參數文件從主數據庫的服務器拷貝到備用數據庫的服務器.(拷貝到f:/oracle目錄)
在備用數據庫服務器修改初始化參數(剛拷貝過來的初始化參數文件)修改后的值大概如下:
*.aq_tm_processes=1
*.background_dump_dest='e:/oracle/admin tandby/bdump'
*.compatible='9.2.0.0.0'
*.control_files='e:/oracle/oradata/controlfile/bkcontrol.ora'
*.core_dump_dest='e:/oracle/admin tandbyl/cdump'
*.db_block_size=16384
*.db_cache_size=137363456
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='orcl'
*.dispatchers='(protocol=tcp) (service=orclxdb)'
*.fast_start_mttr_target=300
*.hash_join_enabled=true
*.instance_name='standby'
*.java_pool_size=33554432
*.job_queue_processes=10
*.large_pool_size=27262976
*.log_archive_dest_1='location=e:/oracle/oradata/archive mandatory'
*.log_archive_dest_state_1='enable'
*.log_archive_format='arch%d_%s.dbf'
*.log_archive_start=true
*.open_cursors=300
*.pga_aggregate_target=80000000
*.processes=150
*.query_rewrite_enabled='false'
*.remote_login_passwordfile='exclusive'
*.shared_pool_size=45088768
*.sort_area_size=524288
*.sql_trace=false
*.star_transformation_enabled='false'
*.timed_statistics=true
*.undo_management='auto'
*.undo_retention=10800
*.undo_tablespace='undotbs1'
*.user_dump_dest='e:/oracle/admin tandby/udump'
*.workarea_size_policy='auto'
*.lock_name_space=standby
*.standby_file_management='auto'
*.standby_archive_dest='e:/oracle/oradata tdarch'
*.log_parallelism=1
在備用數據庫一端創建一個新的實例.如下操作:
c:/>oradim –new –sid standby –startmode auto
在主數據配置listener.ora,tnsnames.ora和sqlnet.ora文件.配置完之后分別如下:
listener =
(description_list =
(description_list =
(description =
(address = (protocol = tcp)(host = 10.100.0.122)(port = 1521))
)
)
(description_list =
(description =
(address = (protocol = ipc)(key = extproc1))
)
)
)
sid_list_listener =
(sid_list =
(sid_desc =
(sid_name = plsextproc)
(oracle_home = e:/oracle/ora92)
(program = extproc)
)
(sid_desc =
(global_dbname = orcl)
(oracle_home = e:/oracle/ora92)
(sid_name = orcl)
)
)
tnsnames.ora文件為:
orcl =
(description =
(address_list =
(address = (protocol = tcp)(host = 10.100.0.122)(port = 1521))
)
(connect_data =
(server = dedicated)
(service_name = orcl)
)
)
standby =
(description =
(address_list =
(address = (protocol = tcp)(host = 10.100.0.222)(port = 1521))
)
(connect_data =
(server = dedicated)
(service_name = orcl)
)
)
extproc_connection_data =
(description =
(address_list =
(address = (protocol = ipc)(key = extproc1))
)
(connect_data =
(sid = plsextproc)
(presentation = ro)
)
)
sqlnet.ora文件為:
sqlnet.authentication_services= (nts)
names.directory_path= (hostname,tnsnames, onames)
在備用數據庫配置listener.ora,tnsnames.ora和sqlnet.ora.配置后的文件內容分別如下:
其中配置sqlnet.ora文件中的參數sqlnet.expire_time是enable死連接偵測
listener =
(description_list =
(description =
(address_list =
(address = (protocol = ipc)(key = extproc))
)
(address_list =
(address = (protocol = tcp)(host = 10.100.0.222)(port = 1521))
)
)
)
sid_list_listener =
(sid_list =
(sid_desc =
(global_dbname = orcl2)
(oracle_home = e:/oracle/ora92)
(sid_name = orcl)
)
(sid_desc =
(program = extproc)
(sid_name = plsextproc)
(oracle_home = e:/oracle/ora92)
)
)
tnsnames.ora文件為:
orcl =
(description =
(address_list =
(address = (protocol = tcp)(host = 10.100.0.122)(port = 1521))
)
(connect_data =
(server = dedicated)
(service_name = orcl)
)
)
standby =
(description =
(address_list =
(address = (protocol = tcp)(host = 10.100.0.222)(port = 1521))
)
(connect_data =
(server = dedicated)
(service_name = orcl)
)
)
extproc_connection_data =
(description =
(address_list =
(address = (protocol = ipc)(key = extproc0))
)
(connect_data =
(sid = plsextproc)
(presentation = ro)
)
)
sqlnet.ora文件為:
sqlnet.authentication_services= (nts)
names.directory_path= (hostname,tnsnames, onames)
sqlnet.expire_time=2
在主數據庫和備用數據庫用lsnrctl start和lsnrctl stop啟動和關閉監聽器.然后在主數據庫用tnsping standby和在備用數據庫用tnsping orcl試看兩個庫是否是通的.
用初始化參數文件啟動數據庫到mount狀態下.
sql> startup mount pfile= f:/oracle/admin tandby/pfile/initstdby.ora;
rename數據文件.如果拷貝過來的數據文件的位置與原來在主庫中的位置不一樣.而我們現在使用的是主庫備份過來的控制文件,所以要將數據文件rename到新的位置.做如下操作(如果位置是一樣的可以不做操作):
sql> alter database rename file 'e:/oracle/ora92/orcl ystem01.dbf'
2> to 'f:/oracle tandby ystem01.dbf';
sql> alter database rename file 'e:/oracle/ora92/orcl/undotbs01.dbf '
2> to 'f:/oracle tandby/ undotbs01.dbf ';
sql> alter database rename file 'e:/oracle/ora92/orcl/example01.dbf'
2> to 'f:/oracle tandby/ example01.dbf';
sql> alter database rename file 'e:/oracle/ora92/orcl/indx01.dbf'
2> to 'f:/oracle tandby/ indx01.dbf';
sql> alter database rename file 'e:/oracle/ora92/orcl/tools01.dbf'
2> to 'f:/oracle tandby/ tools01.dbf ';
sql> alter database rename file 'e:/oracle/ora92/orcl/users01.dbf'
2> to 'f:/oracle tandby/ users01.dbf';
sql> alter database rename file 'e:/oracle/ora92/orcl/xdb01.dbf'
2> to 'f:/oracle tandby/ xdb01.dbf';
sql> alter database rename file 'e:/oracle/ora92/orcl/logmnrts.dbf'
2> to 'f:/oracle tandby/ logmnrts.dbf';
rename日志文件.這一步只是將控制文件中日志文件的位置信息進行更改,此時并沒有真正的日志文件生成,也沒有從主庫拷貝日志文件到備用庫.做如下:
sql> alter database rename file ' e:/oracle/ora92/orcl/redo01.log '
2> to ' e:/oracle/radata/redo01.log';
sql> alter database rename file ' e:/oracle/ora92/orcl/redo02.log '
2> to ' e:/oracle/radata /redo02.log';
sql> alter database rename file ' e:/oracle/ora92/orcl/redo03.log '
2> to ' e:/oracle/radata/redo03.log';
在備用數據庫打開data guard(使用戶不能在logical standby database做更新操作),做如下:
sql> alter database guard all;
sql> alter database open resetlogs;
重設logical standby database的數據庫名(這一步的目的性暫時沒完全搞清楚)
使用dbnewid(nid)工具來重設數據庫名.要先關閉數據庫并把數據庫啟動為mount狀態:
sql> shutdown immediate;
sql> startup mount pfile= f:/oracle/initstdby.ora;
用dbnewid工具,操作如下(這個是在命令行下操作):
nid target=sys/[email protected] dbname=standby
然后再關閉數據庫:sql>shutdown immediate
改變初始化參數: db_name=standby
啟動數據庫為mount狀態:sql>startup mount;
為備用數據庫創建server parameter file:
sql>create spfile from pfile= f:/oracle/initstdby.ora;
關閉數據庫然后用spfile重啟動數據庫:
sql>shutdown immediate
sql>startup mount
sql>alter database open resetlogs;
給備用數據庫創建一個新的臨時文件(屬于臨時表空間)
先查看備用庫中是否有臨時文件,如果沒有就直接創建,如果有要刪除重新創建.(因為從主庫的冷備份拷貝過來的臨時文件在備用庫中是不能用的.所以得重建)相應操作如下:
sql> select * from v$tempfile;-------查看是否有臨時文件
no rows selected
sql> alter database tempfile 'tempfilename' drop;------刪除重建前的臨時文件
sql> select tablespace_name from dba_tablespaces where
2> contents ='temporary';--------查看臨時文件所屬的臨時表空間名
tablespace_name
--------------------------------
temp
sql> alter tablespace temp add tempfile
2> 'f:/oracle tandby/temp01.dbf'
3> size 40m reuse;--------給這個臨時表空間重建一個臨時文件
注冊歸檔日志和啟動sql apply services
sql> alter database register logical logfile
2> ' e:/oracle/oradata/orcl/arc0004.001';------將最近的歸檔日志注冊到logical standby database.這里的e:/oracle/oradata tdarch/ arch1069826922_21.dbf是在前面得到的.
sql> alter database start logical standby apply initial;-----啟動redo logs apply,用執行sql語句的方式來apply redo logs.就是sql apply services.到于initial是第一次啟動sql apply services時要寫的.后面的可以直接用如下操作
sql> alter database stop logical standby apply;----停止
sql> alter database start logical standby apply;----啟動
在主數據庫配置遠程歸檔.
sql> alter system set log_archive_dest_3='service=payroll3' scope=both;
--------配置遠程歸檔目錄
sql> alter system set log_archive_dest_state_3=enable scope=both;
--------配置這個歸檔目錄的狀態為可用
sql> alter system archive log current;----對當前日志進行歸檔,啟動遠程歸檔.
檢查logical standby database
在建立好logical standby database后,log transport service 和 log apply service服務也都啟動.需要檢查一下日志是否正由主數據庫傳到備用數據庫并apply.要做到這個目的,用下面的步驟來做.
檢查日志是否已經被注冊到logical standby database中.連接到備用數據庫并查詢dba_logstdby_log視圖.
sql> alter session set nls_date_format = 'dd-mon-yy hh24:mi:ss';
session altered.
sql> select sequence#, first_time, next_time, dict_begin, dict_end
2> from dba_logstdby_log order by sequence#;
sequence# first_time next_time dic dic
---------- ------------------- ------------------- --- ---
22 23-11月-04 14:00:17 23-11月-04 14:26:58 no no
1 rows selected.
連接到主數據庫并歸檔部分日志.
sql> alter system archive log current;
system altered.
sql> alter system archive log current;
system altered.
再查詢一下dba_logstdby_log視圖.
sql> alter session set nls_date_format = 'dd-mon-yy hh24:mi:ss';
session altered.
sql> select sequence#, first_time, next_time, dict_begin, dict_end
2 from dba_logstdby_log order by sequence#;
sequence# first_time next_time dic dic
---------- ------------------- ------------------- --- ---
22 23-11月-04 14:00:17 23-11月-04 14:26:58 no no
23 23-11月-04 14:26:58 23-11月-04 14:33:33 no no
2 rows selected.
(可以看到多出來一個新的歸檔日志)
檢查日志中的數據是否apply到邏輯備用數據庫中
在邏輯備用數據庫中查詢dba_logstdby_stats視圖來檢查日志中的數據是否正被正確地appled.
sql> column name format a30
sql> column value format a30
sql> select name, value from v$logstdby_stats where name = 'coordinator state';
name value
------------------------------ ------------------------------
coordinator state initializing
在上面的輸出中,coordinator進程正在初始化,這表明log apply service正準備apply sql.但是日志中的數據還沒有開始被applied到邏輯備用數據庫中.
查v$logstdby看當前的sql apply活動狀態.連接到邏輯備用數據庫中,查詢v$logstdby視圖.
sql> column status format a50
sql> column type format a12
sql> select type, high_scn, status from v$logstdby;
type high_scn status
------------ ---------- --------------------------------------------------
coordinator ora-16115: loading log miner dictionary data
reader ora-16127: stalled waiting for additional transact
ions to be applied
builder ora-16117: processing
preparer ora-16116: no work available
sql> select type, high_scn, status from v$logstdby;
type high_scn status
------------ ---------- --------------------------------------------------
coordinator ora-16126: loading table or sequence object number
reader ora-16116: no work available
builder ora-16116: no work available
preparer ora-16116: no work available
如果redo data開始apply到備用數據庫中時,在v$logstdby視圖中coordinator 進程的狀態會顯示applying
檢查日志中的數據是否apply完成可以在邏輯備用數據庫中從dba_logstdby_process視圖中獲得.
sql> select applied_scn, newest_scn from dba_logstdby_progress;
applied_scn newest_scn
----------- ----------
180702 180702
其中,如果applied_scn和newest_scn兩列的值是一樣的,表示coordinator process進程已經apply完成. 日志中的數據也apply完成.
在log_archive_dest_n 初始參數后指定delay=n (minutes) 的意思是由primary database傳到standby database的redo log data將在n minutes之后才開始apply 到standby database. default是nodelay,如果指定了delay而沒有指定值的話則缺省為30分鐘.
