Dataguard搭建灾备库操作 - 数据库编程

r_always_on = false
disk_asynch_io=false
open_cursors=7000
recyclebin =off
_system_trig_enabled = true
O7_DICTIONARY_ACCESSIBILITY = false
nls_language = american
nls_territory = america
nls_date_format='DD-MON-RR'
nls_numeric_characters='.,'
nls_sort=binary
nls_comp=binary
nls_length_semantics=BYTE
max_dump_file_size=51200
timed_statistics = true
processes=3000
sessions = 6000
aq_tm_processes = 2
job_queue_processes = 30
_sqlexec_progression_cost = 2147483647
workarea_size_policy = AUTO
olap_page_pool_size = 4194304
optimizer_mode =FIRST_ROWS
LOG_ARCHIVE_FORMAT='%t_%s_%r.arc'
STANDBY_FILE_MANAGEMENT = auto

备库启动到nomount状态 [dg2]
sqlplus '/as sysdba'
startup nomount pfile= /dbs/fwy.ora

源端利用11g的duplicate from active技术
源端 [dg1] 下面绿色的是连到源库，粉色代表连到目标库。
rman target sys/oracle auxiliary sys/oracle@dg2
DUPLICATE TARGET DATABASE for standby FROM ACTIVE DATABASE;
或者如果你想从目标端发起rman也可以[dg2] rman target sys/oracle@dg1 auxiliary sys/oracle

duplicate完毕后，备库： [dg2]
create spfile=' /dbs/fwy2.ora' from memory;
cd $ORACLE_HOME/dbs && cp fwy2.ora spfile${ORACLE_SID}.ora

备库增加日志组，执行4次增加4组。 [dg2]
alter database add standby logfile size 50m;

主库开启传送日志 [dg1]
alter system set LOG_ARCHIVE_DEST_STATE_2=enable scope=both;

先不要open库，就算open也失败，因为此时可能数据文件不一致。所以，我们先recover一会儿，让其recover到一致状态。
[dg2] alter database recover managed standby database using current logfile disconnect from session parallel 8;

一会儿后，停掉MRP进程：
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
然后可以开启open read only模式了。
alter database open read only;
alter database recover managed standby database using current logfile disconnect from session parallel 8;

观察mrp进程是否正常工作。
select PROCESS,STATUS,CLIENT_PROCESS,GROUP#,THREAD#,SEQUENCE# , BLOCK# from v$managed_standby where process='MRP0';

--比较主库备库的应用日志最大时间
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
col max(first_time) for a30
select max(first_time) from v$log_history;

--对于主库就是写完了的最大日志，对于备库，就是应用完了的最大日志。
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select max(sequence#) from v$log_history where resetlogs_time>=( select max(resetlogs_time ) from v$log_history) ;
主库修改归档日志删除方式： CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO ALL STANDBY; --主库 CONFIGURE ARCHIVELOG DELETION POLICY TO applied on all standby; --备库

Dataguard搭建灾备库操作(二)