#=====================第一部分========================#
----------------------前期配置-------------------------
#=======================begin=========================#
--=======Primary端创建Standby数据库的参数文件、密码文件
sqlplus '/as sysdba'
SQL> alter database force logging;
SQL> create pfile='/tmp/init$ORACLE_SID.ora.bak' from spfile; --这里注意使用绝对路径,以防把其他的init文件覆盖
cp $ORACLE_HOME/dbs/orapw$ORACLE_SID /tmp
--======上传参数文件、密码文件到Standby端:
su - soracle
scp poracle@primary_hostname:/tmp/init$ORACLE_SID.ora.bak /tmp
scp poracle@primary_hostname:/tmp/orapw$ORACLE_SID /tmp
cd $ORACLE_HOME/dbs
cp /tmp/init$ORACLE_SID.ora.bak ./
cp /tmp/orapw$ORACLE_SID ./
--修改参数文件使之适应当前standby端环境:HA架构standby端的primary host和standby host都需配置
cd $ORACLE_HOME/dbs
vi init$ORACLE_SID.ora.bak --修改具体参数值并保存退出
--=====Standby端创建灾备环境的基本目录路径,HA架构standby端的primary host和standby host都需配置
cd $ORACLE_BASE
mkdir -p admin/$ORACLE_SID
cd $ORACLE_BASE
mkdir diag diag/rdbms diag/tnslsnr
cd admin/$ORACLE_SID
mkdir adump pfile bdump cdump udump
--====创建spfile,分filesystem和asm两种情况:
--for filesystem
cd $ORACLE_HOME/dbs
cp init$ORACLE_SID.ora.bak init$ORACLE_SID.ora
sqlplus '/as sysdba'
SQL> create spfile='$ORACLE_HOME/dbs/spfile$ORACLE_SID.ora' from pfile='$ORACLE_HOME/dbs/init$ORACLE_SID.ora.bak';
--for ASM
su - grid
asmcmd
asmcmd> cd +ORACLE_SID_DATA
asmcmd> mkdir $ORACLE_SID
asmcmd> exit;
cd $ORACLE_HOME/dbs
vi init$ORACLE_SID.ora内容如下:
SPFILE='+$ORACLE_SID_DATA/$ORACLE_SID/spfile$ORACLE_SID.ora'
su - soracle
sqlplus '/as sysdba'
SQL> create spfile='+ORACLE_SID_DATA/$ORACLE_SID/spfile$ORACLE_SID.ora' from pfile='$ORACLE_HOME/dbs/init$ORACLE_SID.ora.bak';
--====Standby端nomount:
sqlplus '/as sysdba'
SQL> startup nomount
#====================第三部分==========================#
-------------------stanby端配置------------------------
#=====================begin===========================#
--使用STANDBY控制文件替换当前控制文件
sqlplus '/as sysdba'
SQL> alter database backup controlfile to '$ORACLE_HOME/tmp/$ORACLE_SID_ctl.bak'; --替换路径、实例名
SYS@core1> select database_role from v$database;
DATABASE_ROLE
----------------
PRIMARY
SQL> alter database convert to physical standby;
SQL> shutdown immediate
SQL> startup mount
SQL> select database_role from v$database; --[说明]应该显示为“PHYSICAL STANDBY”模式
--在DG环境配置standby redo logs
注:standby redo log的组数至少比online redo log多一组,且大小和online redo log保持一致,根据主库的实际日志数量进行修改。
sqlplus '/as sysdba'
SQL> select group#,bytes/1024/1024 from v$log; --记录group#, bytes
SQL> alter database add standby logfile thread 1 group 7 size xxxM;
SQL> alter database add standby logfile thread 1 group 8 size xxxM;
…… …… ……
SQL> alter database add standby logfile thread 1 group n size xxxM;
--如:
alter database add standby logfile thread 1 group 7 size 512M;
alter database add standby logfile thread 1 group 8 size 512M;
alter database add standby logfile thread 1 group 9 size 512M;
alter database add standby logfile thread 1 group 10 size 512M;
alter database add standby logfile thread 1 group 11 size 512M;
alter database add standby logfile thread 1 group 12 size 512M;
alter database add standby logfile thread 1 group 13 size 512M;
--复核standby redo logs
select group#,status,bytes/1024/1024 from v$standby_log;
--配置 log_archive_config 参数,括号里面替换为具体的db_unique_name
alter system set log_archive_config = 'DG_CONFIG=($ORACLE_SID,l$ORACLE_SID,r$ORACLE_SID)' scope = both;
--配置log_archive_dest_n:
alter system set log_archive_dest_2 = 'SERVICE=$ORACLE_SID lgwr async noaffirm reopen=60 valid_for=(online_logfiles,primary_role) db_unique_name=$ORACLE_SID' scope = both;
alter system set log_archive_dest_3 = 'SERVICE=r$ORACLE_SID lgwr async noaffirm reopen=60 valid_for=(online_logfiles,primary_role) db_unique_name=r$ORACLE_SID' scope = both;
--配置监听
注:RAC中所有节点用相同的listner名称,如$ORACLE_SID的RAC的同城dg所有节点用l$ORACLE_SID,VIP、端口填写本身节点。
cd $ORACLE_HOME/network/admin
vi listener.ora
#添加如下内容:
l$ORACLE_SID =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = $VIP)(PORT = 15XX))
)
)
)
--====配置listener参数:
sqlplus '/as sysdba'
--for single instance:
SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=$VIP)(PORT=15XX))))' scope=both;
SQL> exit;
--for RAC:
RAC的dg还需为各节点设置remote_listener、cluster_interconnects:
SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=$VIP1)(PORT=$PORT1))))' scope=both sid='xxx1';
SQL>alter system set remote_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=$VIP2)(PORT=$PORT2))))' scope=both sid='xxx1';
SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=$VIP2)(PORT=$PORT2))))' scope=both sid='xxx2';
SQL>alter system set remote_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=$VIP1)(PORT=$PORT1))))' scope=both sid='xxx1';
SQL>alter system set cluster_interconnects=’4.4.46.67' scope=both sid='xxx1';;
SQL>alter system set cluster_interconnects=’4.4.46.69' scope=both sid='xxx2';;
--=========验证listener参数:
sqlplus '/as sysdba'
SQL>show parameter listene
--启动监听:单实例和RAC
lsnrctl status l$ORACLE_SID
lsnrctl start l$ORACLE_SID
--====配置必要的tnsnames.ora,若dg库为HA架构,需在dg库primary host、standby host都配置
cd $ORACLE_HOME/network/admin
vi tnsnames.ora
#添加如下内容(主库、dg库的主备机都必须添加):
#for single instance:
CAT11G=(ADDRESS=(PROTOCOL=TCP)(HOST=10.200.8.35)(PORT=1528))(CONNECT_DATA=(SERVICE_NAME=cat11g)))
$ORACLE_SID =(description =(address = (protocol = tcp)(host = $VIP)(port = 15XX))(connect_data = (sid = $ORACLE_SID)))
l$ORACLE_SID =(description =(address = (protocol = tcp)(host = $VIP)(port = 15XX))(connect_data = (sid = $ORACLE_SID)))
r$ORACLE_SID =(description =(address = (protocol = tcp)(host = $VIP)(port = 15XX))(connect_data = (sid = $ORACLE_SID)))
#for RAC:
CAT11G=(ADDRESS=(PROTOCOL=TCP)(HOST=10.200.8.35)(PORT=1528))(CONNECT_DATA=(SERVICE_NAME=cat11g)))
$ORACLE_SID1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=$VIP1)(PORT=15XX)))(CONNECT_DATA=(SERVER=DEDICATED)(sid=$ORACLE_SID1)))
$ORACLE_SID2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=$VIP2)(PORT=15XX)))(CONNECT_DATA=(SERVER=DEDICATED)(sid=$ORACLE_SID2)))
$ORACLE_SID=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=$VIP1)(PORT=15XX))(ADDRESS=(PROTOCOL=TCP)(HOST=$VIP2)(PORT=15XX))(LOAD_BALANCE=on)(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME
=$ORACLE_SID)(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=180)(DELAY=5))))
l$ORACLE_SID1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=$VIP1)(PORT=15XX)))(CONNECT_DATA=(SERVER=DEDICATED)(sid=$ORACLE_SID1)))
l$ORACLE_SID2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=$VIP2)(PORT=15XX)))(CONNECT_DATA=(SERVER=DEDICATED)(sid=$ORACLE_SID2)))
l$ORACLE_SID=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=$VIP1)(PORT=15XX))(ADDRESS=(PROTOCOL=TCP)(HOST=$VIP2)(PORT=15XX))(LOAD_BALANCE=on)(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME
=$ORACLE_SID)(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=180)(DELAY=5))))
--验证tns配置是否正常:
tnsping $ORACLE_SID
tnsping l$ORACLE_SID
tnsping r$ORACLE_SID
--配置FAL:
alter system set fal_server = $ORACLE_SID scope = both;
alter system set fal_client = l$ORACLE_SID scope = both;
--修改standby_file_management = auto
alter system set standby_file_management = auto scope = both;
--====修改DG库db_unique_name
--for同城dg:
alter system set db_unique_name = 'l$ORACLE_SID' scope= spfile;
--for远程dg:
alter system set db_unique_name = 'r$ORACLE_SID' scope= spfile;
--====指定DG环境下的service_name
--for同城dg:
alter system set service_names = '$ORACLE_SID' scope= spfile;
--for远城dg:
alter system set service_names = '$ORACLE_SID' scope= spfile;
--====配置FRA大小,至少可以存放7天的归档日志文件,根据实际情况计算相关值,最小不能小于20G。
alter system set db_recovery_file_dest_size = 204800M scope=spfile;
--====配置FRA路径:
--for filesystem
alter system set db_recovery_file_dest= '/crbank/$ORACLE_SID/fra' scope=spfile;
--for ASM
alter system set db_recovery_file_dest= ' +$ORACLE_SID_FRA' scope=spfile;
--====取消原有的standby_archive_dest:
alter system set standby_archive_dest ='' scope=spfile;
--取消原有的log_archive_dest_1:
alter system set log_archive_dest_1 = '' scope=spfile;
--====对dg库做相关初始化设置:
alter system set sec_case_sensitive_logon = false scope=both; --10g版本不用配置该参数
alter system set open_links=0 scope=spfile;
alter system set open_links_per_instance=0 scope=spfile;
alter system set parallel_execution_message_size=32768 scope=spfile;
alter system set disk_asynch_io=TRUE scope=spfile;
alter system set db_writer_processes=4 scope=spfile;
--====重启DG使相关参数生效
alter database recover managed standby database cancel;
shutdown immediate;
startup mount;
alter database open;
alter database recover managed standby database using current logfile disconnect;
--====屏蔽log_archive_dest_state_n:
alter system set log_archive_dest_state_2 = defer scope = both;
alter system set log_archive_dest_state_3 = defer scope = both;
--====配置归档日志清理策略
rman target /
--如果备份在主库:
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
RMAN> show archivelog deletion policy;
--如果备份在同城dg库:
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY BACKED UP 1 TIMES TO 'SBT_TAPE';
RMAN> show archivelog deletion policy;
--====配置crontab:
su - poracle
vi .cron_file内容如下:
0 6 * * sun find $ORACLE_BASE/admin/$ORACLE_SID/adump -name '*.aud' -mtime +30 -delete
使crontab生效:
crontab .cron_file
crontab -l
#===================第四部分===========================#
------------------主库配置部分--------------------------
#=====================begin============================#
--====在生产主库配置standby redo logs,standby redo log的组数至少比online redo log多一组且大小和online redo log保持一致
sqlplus '/as sysdba'
SQL> select group#,bytes/1024/1024 from v$log; --记录group#, bytes
SQL> alter database add standby logfile thread 1 group 7 size xxxM;
SQL> alter database add standby logfile thread 1 group 8 size xxxM;
…… …… ……
SQL> alter database add standby logfile thread 1 group n size xxxM;
select group#,status,bytes/1024/1024 from v$standby_log;
--====配置log_archive_config:
alter system set log_archive_config = 'DG_CONFIG=($ORACLE_SID,l$ORACLE_SID,r$ORACLE_SID)' scope = both;
--====配置log_archive_dest_n:
alter system set log_archive_dest_2 = 'SERVICE=l$ORACLE_SID lgwr async noaffirm reopen=60 valid_for=(online_logfiles,primary_role) db_unique_name=l$ORACLE_SID' scope = both;
alter system set log_archive_dest_3 = 'SERVICE=r$ORACLE_SID lgwr async noaffirm reopen=60 valid_for=(online_logfiles,primary_role) db_unique_name=r$ORACLE_SID' scope = both;
--====配置必要的tnsnames.ora,若dg库为HA架构,需在dg库primary host、standby host都配置
cd $ORACLE_HOME/network/admin
vi tnsnames.ora
#添加如下内容(主库、dg库的主备机都必须添加):
#for single instance:
CAT11G=(ADDRESS=(PROTOCOL=TCP)(HOST=10.200.8.35)(PORT=1528))(CONNECT_DATA=(SERVICE_NAME=cat11g)))
$ORACLE_SID =(description =(address = (protocol = tcp)(host = $VIP)(port = 15XX))(connect_data = (sid = $ORACLE_SID)))
l$ORACLE_SID =(description =(address = (protocol = tcp)(host = $VIP)(port = 15XX))(connect_data = (sid = $ORACLE_SID)))
r$ORACLE_SID =(description =(address = (protocol = tcp)(host = $VIP)(port = 15XX))(connect_data = (sid = $ORACLE_SID)))
#for RAC:
$ORACLE_SID1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=$VIP1)(PORT=15XX)))(CONNECT_DATA=(SERVER=DEDICATED)(sid=$ORACLE_SID1)))
$ORACLE_SID2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=$VIP2)(PORT=15XX)))(CONNECT_DATA=(SERVER=DEDICATED)(sid=$ORACLE_SID2)))
$ORACLE_SID=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=$VIP1)(PORT=15XX))(ADDRESS=(PROTOCOL=TCP)(HOST=$VIP2)(PORT=15XX))(LOAD_BALANCE=on)(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME
=$ORACLE_SID)(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=180)(DELAY=5))))
l$ORACLE_SID1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=$VIP1)(PORT=15XX)))(CONNECT_DATA=(SERVER=DEDICATED)(sid=$ORACLE_SID1)))
l$ORACLE_SID2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=$VIP2)(PORT=15XX)))(CONNECT_DATA=(SERVER=DEDICATED)(sid=$ORACLE_SID2)))
l$ORACLE_SID=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=$VIP1)(PORT=15XX))(ADDRESS=(PROTOCOL=TCP)(HOST=$VIP2)(PORT=15XX))(LOAD_BALANCE=on)(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME
=$ORACLE_SID)(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=180)(DELAY=5))))
r$ORACLE_SID1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=$VIP1)(PORT=15XX)))(CONNECT_DATA=(SERVER=DEDICATED)(sid=$ORACLE_SID1)))
r$ORACLE_SID2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=$VIP2)(PORT=15XX)))(CONNECT_DATA=(SERVER=DEDICATED)(sid=$ORACLE_SID2)))
r$ORACLE_SID=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=$VIP1)(PORT=15XX))(ADDRESS=(PROTOCOL=TCP)(HOST=$VIP2)(PORT=15XX))(LOAD_BALANCE=on)(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME
=$ORACLE_SID)(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=180)(DELAY=5))))
--====配置fal:
alter system set fal_server = l$ORACLE_SID scope = both;
alter system set fal_client = $ORACLE_SID scope = both;
--====修改standby_file_management = auto
alter system set standby_file_management = auto scope = both;
备用角色时有效
#=================第五部分===================#
-------------确认dg同步是否正常---------------
#===================begin====================#
--========primary端============:
sqlplus '/as sysdba'
--归档当前日志并检查dest_name状态,status 状态必须为VALID
SQL> alter system archive log current;
SQL> select dest_name,status,error from v$archive_dest;
--========standby端============:
sqlplus '/as sysdba'
--确保RFS正常接收日志,MRP正常应用
SQL> select process,status,thread#,sequence#,block#,blocks from v$managed_standby where process!='ARCH';
--====主库配置归档日志清理策略
rman target /
--如果备份在主库:
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO ALL STANDBY BACKED UP 1 TIMES TO 'SBT_TAPE';
RMAN> show archivelog deletion policy;
--如果备份在同城dg库:
RMAN>CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
RMAN> show archivelog deletion policy;
--==============the end========================--