Dataguard搭建灾备库操作手册

数据库：Oracle11gr2 主库 alter database force logging; alter system set db_unique_name='erpdb' scope=spfile; --我们让主库db_name=db_unique_name alter system set REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE scope=spfile; alter system set LOG_ARCHIVE

数据库：Oracle11gr2

主库
alter database force logging;
alter system set db_unique_name='erpdb' 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=(erpdb,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=erpdb' 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_erpdb =(SID_LIST =(SID_DESC =(ORACLE_HOME= /u01/erpdb/db/tech_st/11.2.0)(SID_NAME = erpdb)))
erpdb =(DESCRIPTION_LIST =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = erpdb.dji.com)(PORT = 1601))))
SID_LIST_erpdg=(SID_LIST=(SID_DESC=(SID_NAME=erpdg)(ORACLE_HOME=/u01/erpdg/db/tech_st/11.2.0)))
erpdg=(DESCRIPTION_LIST=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.1.251)(PORT=1601))))
TNS
erpdb=(description =(address = (protocol = tcp)(host =10.10.0.251)(port = 1601))(connect_data = (sid = erpdb)))
erpdg=(description =(address = (protocol = tcp)(host =10.10.1.251)(port = 1601))(connect_data = (sid = erpdg)))

创建主库的密码文件，传送到dg库。或者dg库直接创建密码文件，sys密码与主库一致。
cd $ORACLE_HOME/dbs && orapwd file=orapw$ORACLE_SID password=oracle force=y

cd $ORACLE_HOME/dbs && vi fwy.ora
#
db_name=erpdb
db_unique_name=erpdg
db_create_file_dest='/u01/erpdg/db/apps_st'
diagnostic_dest='/u01/erpdg/db/tech_st/11.2.0/admin/erpdg_erpdg'
LOG_ARCHIVE_CONFIG='DG_CONFIG=(erpdb,erpdg)'
fal_server=erpdb
fal_client=erpdg
#
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状态
sqlplus '/as sysdba' startup nomount pfile=?/dbs/fwy.ora
EOF

源端利用11g的duplicate from active技术
源端
rman target sys/oracle auxiliary sys/oracle@erpdg
DUPLICATE TARGET DATABASE for standby FROM ACTIVE DATABASE;

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

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

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

先不要open库，就算open也失败，因为此时可能数据文件不一致。所以，我们先recover一会儿，让其recover到一致状态。
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) ;