下面的是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分钟.
