11g RAC to Single Dataguard 配置方式(RMAN)

==================================================
一.基础准备工作
==================================================
1.1.配置网络解析
--------------------------------------------------
1).配置/etc/hosts文件
====================
# vi /etc/hosts
确保主机之间有互相解析，除回环地址外加入:


#public ip
129.1.1.124   phosta
129.1.1.125   phostb
129.1.51.102     phostt2


#private ip
1.1.1.9       phosta-priv
1.1.1.10      phostb-priv


#vip
129.1.1.224   phosta-vip
129.1.1.225   phostb-vip


#scanip
129.1.1.226    addr-cluster-scanip


注:确保主备节点都有对方的解析


====================
2).配置tns解析
====================
ADDRPROD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 129.1.1.226)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = addrprod)
    )
  )                                                                


ADDR11G1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 129.1.1.224)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = addrprod)
    )
  )


ADDR11G2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 129.1.1.225)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = addrprod)
    )
  )
                                                                    
ADDRPROD_STD =                                                               
  (DESCRIPTION =                                                    
    (ADDRESS_LIST =                                                 
      (ADDRESS = (PROTOCOL = TCP)(HOST = 129.1.51.102)(PORT = 1521))
    )                                                               
    (CONNECT_DATA =                                                 
      (SERVICE_NAME = addrprod_std)                                         
    )                                                               
  )


====================
3).配置静态监听(HOST建议调整成IP)
====================
--在单实例各个节点的监听文件中增加以下内容，参数需要根据实际情况进行替换。
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = addrprod_std)
      (SID_NAME = addr11g_stdb)
      (ORACLE_HOME = /orastg/app/oracle/product/11.2.0/db_1)
    )
  )


--------------------------------------------------
1.2.主库准备工作
--------------------------------------------------
1).日志配置
====================
a).检查是否强制产生日志
--------------------
SQL> select force_logging from v$database;


--调整
SQL> alter database force logging;
SQL> select force_logging from v$database;


--------------------
b).检查归档模式
--------------------
SQL> archive log list;
SQL> show parameter log_archive_format;          //确保此参数包含线程号:%t


--如果没有开归档，则进行修改
SQL> alter system set log_archive_dest_1 ='LOCATION=+ARCHDG VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=addrprod' scope=both sid='*';
SQL> alter system set log_archive_format='%r_%t_%s.arc' scope=spfile sid='*';
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;
SQL> archive log list;
SQL> alter database open;


--------------------
c).增加standby redo
--------------------
--获取日志组数和最大的文件大小:
SQL> select max(bytes/1024/1024) as "Size(MB)" from v$log;
SQL> select thread#, count(1) from v$log group by thread#;


--数量最好比online redo多一组:
SQL> alter database add standby logfile thread 1 group 11 size 100M;
SQL> alter database add standby logfile thread 1 group 12 size 100M;
SQL> alter database add standby logfile thread 1 group 13 size 100M;
SQL> alter database add standby logfile thread 1 group 14 size 100M;
SQL> alter database add standby logfile thread 1 group 15 size 100M;
SQL> alter database add standby logfile thread 1 group 16 size 100M;
SQL> alter database add standby logfile thread 1 group 17 size 100M;
SQL> alter database add standby logfile thread 1 group 18 size 100M;
SQL> alter database add standby logfile thread 1 group 19 size 100M;
SQL> alter database add standby logfile thread 2 group 31 size 100M;
SQL> alter database add standby logfile thread 2 group 32 size 100M;
SQL> alter database add standby logfile thread 2 group 33 size 100M;
SQL> alter database add standby logfile thread 2 group 34 size 100M;
SQL> alter database add standby logfile thread 2 group 35 size 100M;
SQL> alter database add standby logfile thread 2 group 36 size 100M;
SQL> alter database add standby logfile thread 2 group 37 size 100M;
SQL> alter database add standby logfile thread 2 group 38 size 100M;
SQL> alter database add standby logfile thread 2 group 39 size 100M;




====================
2).备份standby controlfile
====================
RMAN> backup current controlfile for standby format '/orastg/rmanbk/stdctl_%d_%T_%s_%p';


====================
3).调整参数
====================
SQL> alter system set log_archive_dest_2 = 'service=ADDRPROD_STD arch async valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) reopen=60 db_unique_name=ADDRPROD_STD' scope=both sid='*';
SQL> alter system set log_archive_dest_state_2 = 'defer' scope=both sid='*';
SQL> alter system set log_archive_config= 'dg_config=(ADDRPROD,ADDRPROD_STD)' scope=both sid='*';
SQL> alter system set log_archive_max_processes = 8 scope=both sid='*';
SQL> alter system set db_unique_name='ADDRPROD' scope=spfile sid='*';
SQL> alter system set standby_file_management=auto scope=both sid='*';
SQL> alter system set fal_server='ADDRPROD_STD' scope=both sid='*';
SQL> alter system set fal_client='ADDRPROD' scope=both sid='*';


Note:当primary与standby之间的网络带宽较小时建议将归档进程数量设置带2~3即可(包含归档到本地的arc0和到远端的1~2个进程)，如此一来可以防止归档产生速度过快时引起的arc进程抢占不到网络资源而hang住然后被kill最终导致目标端与远端数据版本差距越来越大。在网络带宽不足且一个primary和多个standby关联时建议将网络不好的级联在网络较好的standby后面，防止出现网络不佳的standby占用完arc进程而网络较好的standby无法抢占到资源而产生gap.（案例:）
dg_config:此参数里面写的是在此dg配置框架中的所有数据库的db_unique_name,值得注意的是:如果fal_server/fal_client配置时不包含在db_config中(或者大小写不一致)都会在trace文件中出现: FAL[server] :  Returning NULL frb entry.等报错。故建议:配置时最好将TNS和db_unique_name对应，dg_config中的名字和fal_server/fal_client等参数大小写匹配
注:调整完成后重启数据库，确认所有参数是否生效(以grid用户执行)
# su - grid
$ srvctl stop database -d addrprod
$ srvctl start database d addrprod
$ crsctl status res -t


====================
4).准备参数文件
====================
SQL> create pfile='/orastg/rmanbk/initaddr.ora' from spfile;


--------------------------------------------------
1.3.备节点准备工作
--------------------------------------------------
1).获取主库的参数文件、控制文件、密码文件
====================
$ scp oracle@phosta:/orastg/rmanbk/stdctl* /orastg/rmanbk/
$ scp oracle@phosta:/orastg/rmanbk/*.ora /orastg/rmanbk/
$ scp oracle@phosta:/orastg/app/oracle/product/11.2.0/db_1/dbs/orapwaddrprod $ORACLE_HOME/dbs/orapwaddr11g_stdb


====================
2).调整参数文件(增加DG相关参数)
====================
a).原有参数文件值
--------------------
*._b_tree_bitmap_plans=FALSE
*._gc_undo_affinity=FALSE
*.archive_lag_target=1800
*.cluster_database_instances=2
*.cluster_database=TRUE
addr11g2.cluster_interconnects='1.1.1.10'
addr11g1.cluster_interconnects='1.1.1.9'
*.compatible='10.2.0.1.0'
*.CONTROL_FILE_RECORD_KEEP_TIME=14
*.control_files='+datadg/addrprod/controlfile/current.335.843512833','+datadg/addrprod/controlfile/current.336.843512827','+datadg/addrprod/controlfile/current.337.843512821'
*.db_block_size=8192
*.db_cache_size=16G
*.db_create_file_dest='+DATADG'
*.db_create_online_log_dest_1='+DATADG'
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='addrprod'
*.db_unique_name='addrprod'
*.diagnostic_dest='/orastg/app/oracle'
*.event='31156 trace name context forever, level 0x400:10298 trace name context forever, level 32'
addr11g1.instance_name='addr11g1'
addr11g2.instance_name='addr11g2'
addr11g1.instance_number=1
addr11g2.instance_number=2
*.java_pool_size=512M
*.job_queue_processes=0
*.large_pool_size=512M
*.local_listener='LOCAL_LISTENER'
addr11g1.log_archive_dest_1='LOCATION=+ARCHDG VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=addrprod'
addr11g2.log_archive_dest_1='LOCATION=+ARCHDG VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=addrprod'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_format='%r_%t_%s.arc'
*.log_archive_trace=128
*.log_buffer=10072000
*.O7_DICTIONARY_ACCESSIBILITY=TRUE
*.open_cursors=2048
*.optimizer_secure_view_merging=FALSE
*.pga_aggregate_target=8G
*.processes=1500
*.remote_listener='REMOTE_LISTENER'
*.remote_login_passwordfile='EXCLUSIVE'
*.resource_limit=true
*.session_cached_cursors=100
*.sessions=1600
*.sga_max_size=32G
*.sga_target=32G
*.shared_pool_reserved_size=80m
*.shared_pool_size=1024m
*.sort_area_size=134217728
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.streams_pool_size=256M
addr11g1.thread=1
addr11g2.thread=2
*.undo_management='AUTO'
addr11g1.undo_tablespace='UNDOTBS1'
addr11g2.undo_tablespace='UNDOTBS2'
*.utl_file_dir='/files'


--------------------
b).调整后如下
--------------------
*._b_tree_bitmap_plans=FALSE
*._gc_undo_affinity=FALSE
*.archive_lag_target=1800
*.cluster_database_instances=2
#*.cluster_database=TRUE
*.cluster_database=false
#addr11g2.cluster_interconnects='1.1.1.10'
#addr11g1.cluster_interconnects='1.1.1.9'
*.compatible='10.2.0.1.0'
*.CONTROL_FILE_RECORD_KEEP_TIME=14
#*.control_files='+datadg/addrprod/controlfile/current.335.843512833','+datadg/addrprod/controlfile/current.336.843512827','+datadg/addrprod/controlfile/current.337.843512821'
*.db_block_size=8192
*.db_cache_size=16G
*.db_create_file_dest='/orastg/app/oracle/oradata'
*.db_create_online_log_dest_1='/orastg/app/oracle/oradata/'
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='addrprod'
*.db_unique_name='addrprod_std'
*.diagnostic_dest='/orastg/app/oracle'
*.event='31156 trace name context forever, level 0x400:10298 trace name context forever, level 32'
#addr11g1.instance_name='addr11g1'
#addr11g2.instance_name='addr11g2'
#addr11g1.instance_number=1
#addr11g2.instance_number=2
*.java_pool_size=512M
#*.job_queue_processes=70
*.job_queue_processes=0
*.large_pool_size=512M
*.local_listener='(ADDRESS = (PROTOCOL = TCP)(HOST = 129.1.51.102)(PORT = 1521))'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=( addrprod, addrprod_stb)'
#addr11g1.log_archive_dest_1='LOCATION=+ARCHDG VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=addrprod'
#addr11g2.log_archive_dest_1='LOCATION=+ARCHDG VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=addrprod'
*.log_archive_dest_1='LOCATION=/orastg/app/oracle/arch/addrprod_std VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=addrprod_std'
*.log_archive_dest_2 = 'service=addrprod arch async valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) reopen=60 db_unique_name=addrprod'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='enable'
*.log_archive_format='%r_%t_%s.arc'
*.log_archive_trace=128
*.log_archive_max_processes = 8
*.FAL_CLIENT='addrprod_stb'
*.fal_server='ADDR11G1','ADDR11G2'
*.log_buffer=10072000
*.O7_DICTIONARY_ACCESSIBILITY=TRUE
*.open_cursors=2048
*.optimizer_secure_view_merging=FALSE
*.pga_aggregate_target=8G
*.processes=1500
#*.remote_listener='REMOTE_LISTENER'
*.remote_login_passwordfile='EXCLUSIVE'
*.resource_limit=true
*.session_cached_cursors=100
*.sessions=1600
*.sga_max_size=32G
*.sga_target=32G
*.shared_pool_reserved_size=80m
*.shared_pool_size=1024m
*.sort_area_size=134217728
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.streams_pool_size=256M
#addr11g1.thread=1
#addr11g2.thread=2
*.undo_management='AUTO'
#addr11g1.undo_tablespace='UNDOTBS1'
#addr11g2.undo_tablespace='UNDOTBS2'
*.undo_tablespace='UNDOTBS1'
*.utl_file_dir='/files'


--以下是主要需要调整内容，已经将job进程数恢复成70
*.db_create_file_dest='/orastg/app/oracle/oradata/'
*.db_create_online_log_dest_1='/orastg/app/oracle/oradata/'
*.diagnostic_dest='/orastg/app/oracle'
*.log_archive_config= 'dg_config=(ADDRPROD,ADDRPROD_STD)'
*.log_archive_dest_1='LOCATION=/orastg/app/oracle/arch/addrprod_std VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=addrprod_std'
*.log_archive_dest_2 = 'service=addrprod arch async valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) reopen=60 db_unique_name=addrprod'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='enable'
*.log_archive_format='%r_%t_%s.arc'
*.log_archive_max_processes = 8
*.db_unique_name='ADDRPROD_STD'
*.standby_file_management=auto
*.FAL_CLIENT='addrprod_stb'
*.fal_server='ADDR11G1','ADDR11G2'


--以下参数不针对OMF,是否打开OMF确认参数(db_create_online_log_dest_1,db_create_file_dest)
*.db_file_name_convert='+DATA10G/addrprod/datafile/','/u02/addrprod/'
*.log_file_name_convert='+DATA10G/addrprod/onlinelog/','/u02/addrprod/'


====================
3).恢复控制文件
====================
a).控制文件恢复
--------------------
$ env
注：确保环境变量正确
SQL> startup nomount pfile='/orastg/rmanbk/initaddr.ora';
RMAN> restore standby controlfile from '/orastg/rmanbk/stdctl_ADDRPROD_20140401_10379_1';
RMAN> restore standby controlfile from '/orastg/rmanbk/stdctl_ADDRPROD_20140401_10379_1';
RMAN> restore standby controlfile from '/orastg/rmanbk/stdctl_ADDRPROD_20140401_10379_1';
RMAN> shutdown immediate;


--------------------
b).调整参数文件
--------------------
到<db_create_file_dest>/db_unique_name/controlfile下确认控制文件名，再更新到参数文件中。


--------------------
c).重新开库到Mount
--------------------
SQL> startup nomount pfile='/orastg/rmanbk/initaddr.ora';
SQL> alter database mount standby database;


==================================================
二、主库备份
==================================================
2.1.准备备份脚本
--------------------------------------------------
$ vi /home/oracle/upgrdtools/backup_pri_database.sh
#
ORACLE_BASE=/orastg/app/oracle
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
ORACLE_SID=addr11g1
export ORACLE_BASE ORACLE_HOME ORACLE_SID
export NLS_LANG='american_america.zhs16gbk'
export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
export PATH=$ORACLE_HOME/OPatch:$ORACLE_HOME/bin:$JAVA_HOME/bin:$PATH


#--execute rman command
rman target / nocatalog log /home/oracle/upgrdtools/backup_pri_database.log<<EOF
run
{allocate channel c1 type disk maxpiecesize 65G;
allocate channel c2 type disk maxpiecesize 65G;
allocate channel c3 type disk maxpiecesize 65G;
backup as compressed backupset maxsetsize 70G database format '/orastg/rmanbk/full_%d_%T_%s_%p';
sql 'alter system archive log current';
sql 'alter system archive log current';
sql 'alter system archive log current';
sql 'alter system archive log current';
sql 'alter system archive log current';
sql 'alter system archive log current';
sql 'alter system archive log current';
sql 'alter system archive log current';
sql 'alter system archive log current';
crosscheck archivelog all;
backup as compressed backupset maxsetsize 70G archivelog all format '/orastg/rmanbk/arch_%d_%T_%s_%p';
}
exit;
EOF


--------------------------------------------------
2.2.数据库备份
--------------------------------------------------
$ chmod +x /home/oracle/upgrdtools/backup_pri_database.sh
$ nohup /home/oracle/upgrdtools/backup_pri_database.sh &
[1]     6422544
$ tail -f nohup.out
$ tail -f /home/oracle/upgrdtools/backup_pri_database.log


--------------------------------------------------
2.3.传送备份到目标端
--------------------------------------------------
$ scp /orastg/rmanbk/*_1 oracle@phostt2:/orastg/rmanbk/


==================================================
三.备库恢复
==================================================
3.1.备份集信息处理
--------------------------------------------------
$ rman target / nocatalog log /home/oracle/upgrdtools/rman_pre_restore.log
RMAN> catalog start with '/orastg/rmanbk/full'  noprompt;
RMAN> catalog start with '/orastg/rmanbk/arch' noprompt;
RMAN> crosscheck backup;
RMAN> crosscheck archivelog all;
RMAN> delete noprompt expired archivelog all;
RMAN> delete noprompt expired backup;
RMAN> delete noprompt obsolete backup redundancy 1 device type disk;
RMAN> report need backup database;


--------------------------------------------------
3.2.恢复数据库
--------------------------------------------------
1).准备restore脚本
====================
$ vi /home/oracle/upgrdtools/restore_std_database.sh
#
ORACLE_BASE=/orastg/app/oracle
ORACLE_HOME=/orastg/app/oracle/product/11.2.0/db_1
ORACLE_SID=addr11g_stdb
export ORACLE_BASE ORACLE_HOME ORACLE_SID
export NLS_LANG='american_america.zhs16gbk'
export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
export PATH=$ORACLE_HOME/OPatch:$ORACLE_HOME/bin:$JAVA_HOME/bin:$PATH


#--execute rman command
rman target / nocatalog log /home/oracle/upgrdtools/restore_std_database.log<<EOF
run
{allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
restore database;
switch tempfile 1 to '/orastg/app/oracle/oradata/addrprod/tempfile/temp.256.843646099';
}
exit;
EOF


====================
2).进行数据库restore
====================
$ chmod +x /home/oracle/upgrdtools/restore_std_database.sh
$ nohup /home/oracle/upgrdtools/restore_std_database.sh &
[1]     6422544
$ tail -f nohup.out
$ tail -f /home/oracle/upgrdtools/restore_std_database.log


====================
3).进行数据库recover
====================
RMAN> reocver database;
--恢复到日志不够的报错无需处理


==================================================
四.配置工作
==================================================
4.1.日志文件处理
--------------------------------------------------
1).初始化standby logfile
====================
SQL> alter database clear logfile group 11;
SQL> alter database clear logfile group 12;
SQL> alter database clear logfile group 13;
SQL> alter database clear logfile group 14;
SQL> alter database clear logfile group 15;
SQL> alter database clear logfile group 16;
SQL> alter database clear logfile group 17;
SQL> alter database clear logfile group 18;
SQL> alter database clear logfile group 19;
SQL> alter database clear logfile group 31;
SQL> alter database clear logfile group 32;
SQL> alter database clear logfile group 33;
SQL> alter database clear logfile group 34;
SQL> alter database clear logfile group 35;
SQL> alter database clear logfile group 36;
SQL> alter database clear logfile group 37;
SQL> alter database clear logfile group 38;
SQL> alter database clear logfile group 39;


====================
2).初始化online log
====================
SQL> alter database clear logfile group  1;
SQL> alter database clear logfile group  2;
SQL> alter database clear logfile group  3;
SQL> alter database clear logfile group  4;
SQL> alter database clear logfile group  5;
SQL> alter database clear logfile group  6;
SQL> alter database clear logfile group  7;
SQL> alter database clear logfile group  8;
SQL> alter database clear logfile group 21;
SQL> alter database clear logfile group 22;
SQL> alter database clear logfile group 23;
SQL> alter database clear logfile group 24;
SQL> alter database clear logfile group 25;
SQL> alter database clear logfile group 26;
SQL> alter database clear logfile group 27;
SQL> alter database clear logfile group 28;


--------------------------------------------------
4.2.备库以standy database方式重启
--------------------------------------------------
1).转换参数文件并关库
====================
SQL> create spfile from pfile='/orastg/rmanbk/initaddr.ora';
SQL> shutdown immediate;


====================
2).以standby database启动(如果有节点不能正常连接，可能是密码文件问题，先尝试同步密码文件)
====================
SQL> startup nomount;
SQL> create pfile from spfile;
SQL> alter database mount standby database;


--------------------------------------------------
4.3.启用主库归档路径
--------------------------------------------------
SQL> alter system set log_archive_dest_state_2='enable' scope=both sid='*';


--------------------------------------------------
4.4.确认日志传送是否正常进行
--------------------------------------------------
1).主库切换日志
====================
SQL> alter system archive log current;


====================
2).备库确认归档日志数量是否有增加
====================
SQL> select count(1) from v$archived_log;


--------------------------------------------------
4.5.备库启用恢复
--------------------------------------------------
1).启动
====================
SQL> alter database recover managed standby database disconnect from session;


====================
2.确认恢复是否正常
====================
--确认还没应用的日志有多少，是否满足要求
SQL> select thread#,sequence#,applied from v$archived_log where applied='NO';


--确认进程状态，以及进程的处理进度
SQL> select process,status,thread#,sequence#,block#,blocks from v$managed_standby;


--在主库确认standby应用到哪个redo(以redo的next_change#标示在APPLIED_SCN)
SQL> SELECT DEST_ID, APPLIED_SCN FROM V$ARCHIVE_DEST WHERE TARGET='STANDBY';


====================
3).停止应用日志(不用做)
====================
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;