data guard 参考.doc

一、 环境配置primary：IP：20 CPU：2个Intel(R) Xeon(TM) CPU 2.80GHz （HT） Mem：2GSwap：4GDisk：130GDB：Oracle Database 10g Enterprise Edition Release .0 - ProdOS：Linux oracle 2.4.21-20.ELsmp #1 SMPstandby：IP：01 Cup：2个Intel(R) Xeon(TM) CPU 2.40GHz （HT） Mem：2GSwap：2GDisk：66GDB：Oracle Database 10g Enterprise Edition Release .0 - ProdOS：Linux boss-3 2.4.21-15.ELsmp #1 SMP Primary为正在使用的生产数据库，standby安装oracle软件，但不建立数据库。二、 建立物理备用数据库1 准备主库的oracle环境：编辑oracle用户的$HOME/.bash_profile文件，oracle相关环境变量如下：ORACLE_BASE=/u01/app/oracle; export ORACLE_BASEORACLE_SID=BOSS; export ORACLE_SIDORACLE_HOME=$ORACLE_BASE/product/10.1.0/Db_1; export ORACLE_HOMEexport PATH=$ORACLE_HOME/bin:$PATH:export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/libDISPLAY=9:0.0; export DISPLAYNLS_LANG=american_america.ZHS16CGB231280; export NLS_LANG2 修改主库为归档模式建立归档目录：mkdir -p /u02/oradata/BOSS/arch修改归档模式：archive log list;create pfile from spfile;编辑$ORACLE_HOME/dbs/initBOSS.ora添加下面一行log_archive_dest_1=location=/u02/oradata/BOSS/archsqlplus /nologconn sys as sysdbashutdown immediate;create spfile from pfile;startup nomount;alter database mount;alter database archivelog;alter database open;3. 对主数据库做一次完整热备份，获得备用数据库数据RMANconnect targetRMAN backup database format=/home/oracle/%U_%s.bak;RMAN sql Alter System Archive Log Current;RMAN Backup filesperset 10 ArchiveLog all format=/home/oracle/%U_%s.bak;cd /home/oraclescp *.bak 01:/home/oracle/4. 在standby服务器准备环境与primary相同编辑oracle用户的$HOME/.bash_profile文件，oracle相关环境变量如下：ORACLE_BASE=/u01/app/oracle; export ORACLE_BASEORACLE_SID=BOSS; export ORACLE_SIDORACLE_HOME=$ORACLE_BASE/product/10.1.0/Db_1; export ORACLE_HOMEexport PATH=$ORACLE_HOME/bin:$PATH:export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/libDISPLAY=9:0.0; export DISPLAYNLS_LANG=american_america.ZHS16CGB231280; export NLS_LANG5. 准备相应目录，如日志文件路径，归档路径，参数文件路径，数据文件准备存放路径等$mkdir -p /u02/oradata/BOSS$mkdir -p /u02/oradata/BOSS /arch$mkdir -p $ORACLE_BASE/admin/BOSS$mkdir -p $ORACLE_BASE/admin/BOSS/bdump$mkdir -p $ORACLE_BASE/admin/BOSS/cdump$mkdir -p $ORACLE_BASE/admin/BOSS/udump6. 建立备用数据库参数文件主库的参数如下：BOSS._db_cache_size=339738624BOSS._java_pool_size=33554432BOSS._large_pool_size=4194304BOSS._shared_pool_size=218103808*.background_dump_dest=/u01/app/oracle/admin/BOSS/bdump*.compatible=.0*.control_files=/u02/oradata/BOSS/control01.ctl,/u02/oradata/BOSS/control02.ctl,/u02/oradata/BOSS/control03.ctl*.core_dump_dest=/u01/app/oracle/admin/BOSS/cdump*.db_block_size=8192*.db_domain=*.db_file_multiblock_read_count=16*.db_name=BOSS*.db_recovery_file_dest=/u01/app/oracle/flash_recovery_area*.db_recovery_file_dest_size=2147483648*.db_writer_processes=4*.dispatchers=(PROTOCOL=TCP) (SERVICE=BOSSXDB)*.global_names=FALSE*.java_pool_size=32M*.job_queue_processes=10*.license_max_users=250*.log_archive_dest_1=location=/u02/oradata/BOSS/arch*.log_archive_dest_2=SERVICE=dbstandby LGWR*.open_cursors=300*.pga_aggregate_target=199229440*.processes=150*.remote_login_passwordfile=EXCLUSIVE*.sga_target=598736896*.undo_management=AUTO*.undo_tablespace=UNDOTBS1*.user_dump_dest=/u01/app/oracle/admin/BOSS/udump*.utl_file_dir=/u01/app/oracle/admin/BOSS/bdump与主数据库不一样的参数如下：#standby database parameterstandby_file_management=AUTOremote_archive_enable=TRUEstandby_archive_dest=/u02/oradata/BOSS/archfal_server=DBPRIMARYfal_client=DBSTANDBY7. 从主服务器拷贝口令文件到备用服务器$cd $ORACLE_HOME/dbs/$scp orapwBOSS 01: /u01/app/oracle/product/10.1.0/Db_1/dbs8. 配置网络连接修改主服务器的/u01/app/oracle/product/10.1.0/Db_1/network/admin/listener.ora文件如下：LISTENER =(DESCRIPTION_LIST =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC)(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = oracle)(PORT = 1521)SID_LIST_LISTENER =(SID_LIST =(SID_DESC =(SID_NAME = PLSExtProc)(ORACLE_HOME = /u01/app/oracle/product/10.1.0/Db_1)(PROGRAM = extproc)(SID_DESC =(GLOBAL_DBNAME = BOSS)(ORACLE_HOME = /u01/app/oracle/product/10.1.0/Db_1)(SID_NAME = BOSS)LISTENERDB =(DESCRIPTION_LIST =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 20)(PORT = 1522)SID_LIST_LISTENERDB =(SID_LIST =(SID_DESC =(GLOBAL_DBNAME = BOSS)(ORACLE_HOME = /u01/app/oracle/product/10.1.0/Db_1)(SID_NAME = BOSS)$lsnrctl start$lsnrctl status 查看监听状态.修改主服务器的/u01/app/oracle/product/10.1.0/Db_1/network/admin/tnsnames.ora文件如下：BOSS =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = oracle)(PORT = 1521)(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = BOSS)EXTPROC_CONNECTION_DATA =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC)(CONNECT_DATA =(SID = PLSExtProc)(PRESENTATION = RO)DBPRIMARY =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 20)(PORT = 1521)(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = BOSS)DBSTANDBY =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 01)(PORT = 1522)(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = BOSS)修改备用服务器的/u01/app/oracle/product/10.1.0/Db_1/network/admin/listener.ora文件如下：LISTENER =(DESCRIPTION_LIST =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 01)(PORT = 1521)SID_LIST_LISTENER =(SID_LIST =(SID_DESC =(GLOBAL_DBNAME = BOSS)(ORACLE_HOME = /u01/app/oracle/product/10.1.0/Db_1)(SID_NAME = BOSS)LISTENERDB =(DESCRIPTION_LIST =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 01)(PORT = 1522)SID_LIST_LISTENERDB =(SID_LIST =(SID_DESC =(GLOBAL_DBNAME = BOSS)(ORACLE_HOME = /u01/app/oracle/product/10.1.0/Db_1)(SID_NAME = BOSS)修改备用服务器的/u01/app/oracle/product/10.1.0/Db_1/network/admin/tnsnames.ora文件如下：DBPRIMARY =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 20)(PORT = 1521)(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = BOSS)DBSTANDBY =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 01)(PORT = 1522)(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = BOSS)在这里配置两个监听，一个用于主服务器到备用服务器的连接，端口是1522，另外一个用于日后的切换需要，默认端口1521。启动1522 的端口$lsnrctl start listenerdb$lsnrctl status listenerdb 查看1522 端口上监听的状态.测试:在主和备用机上分别执行tnsping dbprimarytnsping dbstandby9. 在主数据库创建备用服务器控制文件alter database create standby controlfile as /home/oracle/standby.ctl;创建后将控制文件cp(rcp or scp)到备用数据库所在的控制文件目录下。如$ scp control01.ctl 01:/u02/oradata/BOSS/cp /u02/oradata/BOSS/control01.ctl /u02/oradata/BOSS/control02.ctlcp /u02/oradata/BOSS/control01.ctl /u02/oradata/BOSS/control03.ctl10. 启动备用数据库conn sys as sysdbacreate spfile from pfile;startup nomount;alter database mount standby database;恢复数据库:RMAN connect target;RMAN restore database;RMAN restore archivelog all;如果有恢复的日志并想手工恢复，可以运行如下命令SQLrecover automatic standby database;如果过程中出现如下类似错误，则可以忽略ORA-00279: change 50775 generated at 06/08/2004 21:57:21 needed for thread 1ORA-00289: suggestion : /u01/oracle/oradata/tbdb/archive/1_5.dbfORA-00280: change 50775 for thread 1 is in sequence #5ORA-00278: log file /u01/oracle/oradata/tbdb/archive/1_5.dbf no longer neededfor this recoveryORA-00308: cannot open archived log /u01/oracle/oradata/tbdb/archive/1_5.dbfORA-27037: unable to obtain file statusLinux Error: 2: No such file or directoryAdditional information: 3因为最后需要的日志根本没有从主数据库送过来进入到后台管理恢复状态SQLalter database recover managed standby database disconnect from session;三、采用Lgwr进程传递联日志机的最大性能模式1. 在备用数据库上创建备用日志alter database recover managed standby database cancel;alter database add standby LOGFILE GROUP 5 (/u02/oradata/BOSS/stdy_redo05.log) size 10m;alter database add standby LOGFILE GROUP 6 (/u02/oradata/BOSS/stdy_redo06.log) size 10m;alter database add standby LOGFILE GROUP 7 (/u02/oradata/BOSS/stdy_redo07.log) size 10m;alter database add standby LOGFILE GROUP 8 (/u02/oradata/BOSS/stdy_redo08.log) size 10m;alter database recover managed standby database disconnect from session;2. 修改主库的归档路径alter system set LOG_ARCHIVE_DEST_2=SERVICE=dbstandby LGWR scope=both;另外，如果考虑到以后该库可能被切换到备用数据库，也可以创建同样的备用日志组:alter database add standby LOGFILE GROUP 5 (/u02/oradata/BOSS/stdy_redo05.log) size 10m;alter database add standby LOGFILE GROUP 6 (/u02/oradata/BOSS/stdy_redo06.log) size 10m;alter database add standby LOGFILE GROUP 7 (/u02/oradata/BOSS/stdy_redo07.log) size 10m;alter database add standby LOGFILE GROUP 8 (/u02/oradata/BOSS/stdy_redo08.log) size 10m;四、验证备用服务器是否工作在主库上：create user test identified by ftp123;grant connect,resource to test;conn test/ftp123primary;create table test(name varchar2(20);insert into test values(hi, Data Guard);commit;conn / as sysdba;alter system switch logfile;查看从库日志以只读方式打开从库查看 insert into test values(hi, Data Guard); 已经生效。conn / as sysdba;alter database recover managed standby database cancel;alter database open read only;conn test/ftp123select * from test;再次设置从库在恢复模式：alter database recover managed standby database disconnect from session;五、日常管理1. 备用服务器的管理模式与只读模式（1）启动到管理模式SQLshutdown immediate;SQLstartup nomount;SQLalter database mount standby database;SQLalter database recover managed standby database disconnect from session;（2）启动到只读方式SQLshutdown immediate;SQLstartup nomount;SQLalter database mount standby database;SQLalter database open read only;（3）如果在管理恢复模式下到只读模式SQLrecover managed standby database cancel;SQLalter database open read only;这个时候，可以给数据库增加临时数据文件（这个在热备份的时候是没有备份过来的）如alter tablespace temp add tempfile /u02/oradata/BOSS/temp01.dbf size 100M;（4）从只读方式到管理恢复方式SQLrecover managed standby database disconnect from session;2. 备用服务器日志删除备用服务器的日志删除也必须小心，因为如果有些日志还没有被备用服务器应用而该日志被删除的话，将引起备用数据库无法往下应用新的日志。删除备用服务器的日志的脚本为：#!/bin/sh# set envcd $HOME. .bash_profile# start removecd $HOME/dbbatgrep Media Recovery Log $ORACLE_BASE/admin/$ORACLE_SID/bdump/alert_$ORACLE_SID.log | awk print $4|sed -e s/rm / rmarchlog.shchmod +x ./rmarchlog.sh./rmarchlog.shcd $ORACLE_BASE/admin/$ORACLE_SID/bdumpcat alert_$ORACLE_SID.log alert_$ORACLE_SID.log.bakecho alert_$ORACLE_SID.logrm -f ./rmarchlog.sh3. 日志延迟检查备用服务器可能有这样的情况发生，因为日志块逻辑损坏，所以必须对日志应用进行检查，防止日志应用被停止，防患于未然，当然我们可以手工检查，但是以下脚本则可以实现自动检查（放到cron中）#!/bin/bash# set envcd $HOME. .bash_profile# start checkDATE=date +%Y-%m-%d:%H:%M:%Sfilepath=/u02/oradata/$ORACLE_SID/arch/logpath=$ORACLE_BASE/admin/$ORACLE_SID/bdumpremotefile=ssh oracle20 ls -t /u02/oradata/BOSS/arch/*|head -1|sed -e s/.*_1_/g |sed -e s/.arc$/gecho CHECK TIME:$DATEechoecho remote file : $remotefilecd $filepathvarfile=ls -t | head -1|sed -e s/.*_1_/g |sed -e s/.arc$/gecho archive file : $filepath$varfilecd $logpathvarlog=grep Media Recovery Log alert_$ORACLE_SID.log | awk print $4 | tail -1 |sed -e s/.*_1_/g | sed -e s/.arc$/gecho applice file : $varlogechoecho $HOME/dblog/check_DG_log.logecho CHECK TIME:$DATE $HOME/dblog/check_DG_log.logecho $HOME/dblog/check_DG_log.logecho remote file : $remotefile $HOME/dblog/check_DG_log.logecho archive file : $filepath$varfile $HOME/dblog/check_DG_log.logecho applice file : $varlog $HOME/dblog/check_DG_log.logecho $HOME/dblog/check_DG_log.log六、主库与备库的正常切换注意：Swithover时只能先从Primary切到Standby，再从Standby切到Primary.以下顺序不能颠倒，如果采用standby redo log的需要注意在切换前在主数据库创建同样的standby redo log。1切换之前先要准备init参数文件最简单的办法就是把两个数据库的文件互换，在一个机器上同时保留主数据库的初始化文件与备用数据库的初始化文件。2. 从Primary切换到standby的脚本：oracledb worksh$ more swithstandby.sh#!/bin/bashcd $HOME. .bash_profilesqlplus /nolog connect / as sysdbaalter database commit to switchover to physical standby with session shutdown;shutdown immediate;create spfile from /u01/app/oracle/product/10.1.0/Db_1/dbs/inittbdbsdby.ora;startup nomount;alter database mount standby database;recover managed standby database disconnect;exitEOFlsnrctl stoplsnrctl start listenerdb3. 修改主端的tnsnames.ora 将主库IP：20 与备库IP：01 对换(即120 与 101 对调即可)4. 从standby切换到primary的脚本：$ more switchprimary.sh#!/bin/bashcd $HOME. .bash_primarysqlplus /nolog connect / as sysdbaalter database commit to switchover to primary;shutdown immediate;create spfile from /u01/app/oracle/product/10.1.0/Db_1/dbs/inittbdbprim.ora;startup;exitEOFlsnrctl stop listenerdblsnrctl start5. 修改备用端的tnsnames.ora 将主库IP：20 与备库IP：01 对换(即 120 与 101 对调即可)这样切换的要求是主机和备机各有两个listener, listener 监听1521，listenerdb 监听1522（见上面的配置过程），任何一个节点，在primary期间启动listener, standby 期间启动listenerdb。连接data guard的客户端的tnsnames配置，这样就可以实现失败切换，对客户端是透明的：BOSS =(DESCRIPTION =(failover = on )(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 主)(PORT = 1521)(ADDRESS = (PROTOCOL = TCP)(HOST = 备)(PORT = 1521)(CONNECT_DATA =(SID = BOSS)七、备库的失败切换1. 失败切换一般指主服务器已经不能使用，必须切换到备用服务器，所以，只操作备用服务器这一端，以下提供一切换脚本$ more switchprimary.sh#!/bin/bashcd $HOME. .bash_profilesqlplus /nolog ALTER DATABASE REGISTER LOGFILE /u01/oracle/oradata/tbdb/archive/1_87.dbf;SQLrecover standby database;（2）如果有活动日志，必须用alter database recover managed standby database finish;否则用alter database recover managed