Dataguard搭建灾备库操作

DJI erpdb库搭建DG 

数据库：Oracle11gr2 

主库 （下面打井号的不用执行）
alter database force logging; 
alter system set db_unique_name='erp' scope=spfile; --我们让主库db_name=db_unique_name 
alter system set REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE scope=spfile; 
alter system set LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' scope=spfile; 
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(erp,erpdg)' scope=both; 
#alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=erp' scope=both; 
alter system set LOG_ARCHIVE_DEST_2='SERVICE=erpdg ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=erpdg' scope=both; 
#alter system set LOG_ARCHIVE_DEST_2='SERVICE=10.10.1.251:1601/erpdg ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=erpdg' scope=both; 
alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=both; 
alter system set LOG_ARCHIVE_DEST_STATE_2=defer scope=both; 
alter system set LOG_ARCHIVE_MAX_PROCESSES=30 scope=both; 

重启库 
shutdown immediate; 
startup mount 
开启归档： 
alter database archivelog; 
alter database open; 

将两边库的监听、tns都配好，能互相tnsping通。 
监听参考
SID_LIST_dg1=

(SID_LIST=(SID_DESC=(SID_NAME=dg)(ORACLE_HOME=/dg1/product/11.2.0)))

dg1=(DESCRIPTION_LIST=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.1.251)(PORT=1800))))
连接串参考：
dg2=(description =(address = (protocol = tcp)(host =10.10.1.251)(port = 1801))(connect_data = (sid = dg)))

创建主库的密码文件，传送到dg库。

[dg1]  cp $ORACLE_HOME/dbs/orapw${ORACLE_SID} /tmp

[dg1] chmod 777 /tmp/ orapw${ORACLE_SID}

[dg2] mv /tmp/ orapw${ORACLE_SID} $ORACLE_HOME/dbs

或者dg库直接创建密码文件，sys密码与主库一致。 

cd $ORACLE_HOME/dbs && orapwd file=orapw$ORACLE_SID password=password force=y 


下面是创建备库的参数文件的  [dg2]
cd $ORACLE_HOME/dbs && vi fwy.ora 
# 
db_name=erp
db_unique_name=erp
db_create_file_dest='/erpdg_bk/dg' 
diagnostic_dest='/u01/erp/db/tech_st/11.2.0/admin/erpdg_erpdg' 
LOG_ARCHIVE_CONFIG='DG_CONFIG=(erp,erpdg)' 
fal_server=erp
fal_client=erpdg 

db_recovery_file_dest_size=200G

db_recovery_file_dest='/erpdg_bk/dg/fra'
# 
utl_file_dir='/tmp' 
log_buffer=15728640 #15m 
pga_aggregate_target=1073741824 #1G 
java_pool_size=157286400 #150m 
large_pool_size=157286400 #150m 
sga_target=0 
shared_pool_size=1073741824 #1G 
db_cache_size=1073741824 #1G 
parallel_max_servers = 8 
_b_tree_bitmap_plans=FALSE 
_fast_full_scan_enabled=FALSE 
O7_DICTIONARY_ACCESSIBILITY=FALSE 
_like_with_bind_as_equality=TRUE 
_optimizer_autostats_job=FALSE 
_sort_elimination_cost_ratio=5 
_system_trig_enabled=TRUE 
_trace_files_public=true 
plsql_code_type='NATIVE' 
plsql_optimize_level=2 
compatible=11.2.0 
cursor_sharing=EXACT 
db_block_checking=false 
db_block_checksum=true 
db_files=5120 
dml_locks=30000 
log_checkpoint_interval=100000 
log_checkpoint_timeout=1200 
log_checkpoints_to_alert=TRUE 
nls_territory=america 
olap_page_pool_size=4194304 
optimizer_secure_view_merging=FALSE 
parallel_min_servers=0 
sec_case_sensitive_logon=false 
undo_management=auto 
undo_retention=21600 #6小时 
undo_tablespace=APPS_UNDOTS1 
remote_login_passwordfile=EXCLUSIVE 
query_rewrite_enabled=true 
db_block_size=8192 
db_file_multiblock_read_count=64 
db_writer_processes=10 
resource_manager_plan='' 
_resource_manager_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;          --备库