DATAGUARD failover切换及配置范例

来源：互联网发布：淘宝supreme正品吧编辑：程序博客网时间：2024/05/01 11:14

今天给客户演示了虚拟机上的switchover和failover的切换过程。客户关心的重点自然是failover，我们直接关掉虚拟存储的开关来模拟阵列故障。failover步骤如下

select NAME,RESETLOGS_TIME,LOG_MODE,OPEN_MODE,PROTECTION_MODE,REMOTE_ARCHIVE, DATABASE_ROLE,SWITCHOVER_STATUS,FORCE_LOGGING from v$database;

2. 停止日志应用

alter database recover managed standby database cancel;

3. 关闭standby日志传输

alter database recover managed standby database finish force;

4. 切换到primary

alter database commit to switchover to primary with session shutdown;

做这一步的时候，若存在gap，则会报ORA-16139 :Switchover: Media recovery required - standby not in limbo 错误。做测试的时候，若先起主库再起备库，且未等待备库相关日志传输完毕，就会出现这个问题。此时需要强制切换

alter database activate physical standby database;

5. 重启数据库到open状态

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

附上单机到单机的linux下dataguard实施过程。

准备环境

PRIMARY： RHEL 4.7 + oracle 10.2.0.4单机+文件系统主机名:zhbqdb1

STANDBY： RHEL 4.7 + oracle 10.2.0.4单机 + 文件系统主机名:zhbqdb2

主机上创建一个库名为zhdydb1的数据库，开启归档模式。备机装好数据软件，版本升级到与主机一致

在主机和备机上配好/etc/hosts并创建数据存放路径

# Do not remove thefollowing line, or various programs
#that require network functionality will fail.
127.0.0.1               localhost.localdomain localhost
172.17.42.29            zhbqdb1
172.17.42.30            zhbqdb2

创建存放数据文件和归档的目录

mkdir /u01/flash_recovery_areamkdir /u01/oradatachown oracle:oinstall/u01/flash_recovery_areachown oracle:oinstall/u01/oradata

主机的配置

打开force logging

SQL> alter database force logging;

查看当前数据库是否置于force logging 下：

selectforce_logging from v$database;

创建归档目录

 mkdir /u01/flash_recovery_area/ZHDYDB1/arch mkdir/u01/flash_recovery_area/ZHDYDB2 mkdir/u01/flash_recovery_area/ZHDYDB2/arch mkdir/u01/flash_recovery_area/ZHDYDB2/archivelog

配置tnsnames

在zhbqdb1上

vi$ORACLE_HOME/network/admin/tnsnames.ora

ZHDYDB1 =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST =zhbqdb1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = zhdydb1)
    )
)

ZHDYDB2 =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST =zhbqdb2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = zhdydb2)
    )
)

添加standby redo log file

这里primary库中有7个重做日志组，对应的，需要创建7+1个standby logfilegroup

ALTER DATABASE ADD STANDBY LOGFILE GROUP 8('/u01/oradata/ZHDYDB1/onlinelog/sdbylog8a.log', '/u01/flash_recovery_area/ZHDYDB1/onlinelog/sdbylog8a.log') SIZE 100M; ALTERDATABASE ADD STANDBY LOGFILE GROUP 9('/u01/oradata/ZHDYDB1/onlinelog/sdbylog9a.log', '/u01/flash_recovery_area/ZHDYDB1/onlinelog/sdbylog9a.log') SIZE 100M; ALTERDATABASE ADD STANDBY LOGFILE GROUP 10('/u01/oradata/ZHDYDB1/onlinelog/sdbylog10a.log', '/u01/flash_recovery_area/ZHDYDB1/onlinelog/sdbylog10a.log') SIZE 100M; ALTERDATABASE ADD STANDBY LOGFILE GROUP 11('/u01/oradata/ZHDYDB1/onlinelog/sdbylog11a.log', '/u01/flash_recovery_area/ZHDYDB1/onlinelog/sdbylog11a.log') SIZE 100M; ALTERDATABASE ADD STANDBY LOGFILE GROUP 12('/u01/oradata/ZHDYDB1/onlinelog/sdbylog12a.log', '/u01/flash_recovery_area/ZHDYDB1/onlinelog/sdbylog12a.log') SIZE 100M; ALTERDATABASE ADD STANDBY LOGFILE GROUP 13('/u01/oradata/ZHDYDB1/onlinelog/sdbylog13a.log', '/u01/flash_recovery_area/ZHDYDB1/onlinelog/sdbylog13a.log') SIZE 100M; ALTERDATABASE ADD STANDBY LOGFILE GROUP 14('/u01/oradata/ZHDYDB1/onlinelog/sdbylog14a.log', '/u01/flash_recovery_area/ZHDYDB1/onlinelog/sdbylog14a.log') SIZE 100M; ALTERDATABASE ADD STANDBY LOGFILE GROUP 15('/u01/oradata/ZHDYDB1/onlinelog/sdbylog15a.log', '/u01/flash_recovery_area/ZHDYDB1/onlinelog/sdbylog15a.log') SIZE 100M;

查看状态

SELECTGROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG; columnmember  format a50selectgroup#,member from v$logfile order by group#;

创建密码文件及初始化文件

确定primary和standby中各有一个路径用于rman备份： /bak/dgbak

若primary中不存在密码文件，创建一个

orapwdfile=$ORACLE_HOME/dbs/orapwzhdydb1 password=aaa entries=5 cp$ORACLE_HOME/dbs/orapwzhdydb1 /bak/dgbakmv/bak/dgbak/orapwzhdydb1 /bak/dgbak/orapwzhdydb2

SQL> create pfile='/bak/dgbak/initzhdydb2.ora' from spfile;

修改初始化参数

alter system setdb_unique_name=zhdydb1 scope=spfile;alter system setLOG_ARCHIVE_CONFIG='DG_CONFIG=(zhdydb1,zhdydb2)'  scope=spfile; alter system setLOG_ARCHIVE_DEST_1='LOCATION=/u01/flash_recovery_area/ZHDYDB1/archivelog  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)DB_UNIQUE_NAME=zhdydb1'scope=spfile; alter system setLOG_ARCHIVE_DEST_2='SERVICE=zhdydb2 LGWR SYNC AFFIRM  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)DB_UNIQUE_NAME=zhdydb2'  scope=spfile;alter system setLOG_ARCHIVE_DEST_STATE_1=ENABLE  scope=spfile;alter system setLOG_ARCHIVE_DEST_STATE_2=ENABLE scope=spfile; alter system setLOG_ARCHIVE_MAX_PROCESSES=10 scope=spfile;alter system setlog_archive_format='%t_%s_%r.dbf' scope=spfile; alter system setSTANDBY_ARCHIVE_DEST='LOCATION=/u01/flash_recovery_area/ZHDYDB1/arch'  scope=spfile;  alter system setremote_login_passwordfile='EXCLUSIVE' scope=spfile; alter system set FAL_SERVER=zhdydb2  scope=spfile;alter system set FAL_CLIENT=zhdydb1  scope=spfile; alter system set db_file_name_convert='/u01/oradata/ZHDYDB1/','/u01/oradata/ZHDYDB2/'scope=spfile; alter system set log_file_name_convert='/u01/flash_recovery_area/ZHDYDB1/','/u01/flash_recovery_area/ZHDYDB2/'scope=spfile;  alter system set STANDBY_FILE_MANAGEMENT=AUTO  scope=spfile;

重启到mount 状态后

ALTERDATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY; --将保护级别升级为最大可用

selectdatabase_role,protection_mode,protection_level,switchover_status fromv$database;

DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL SWITCHOVER_STATUS

------------------------------------ -------------------- --------------------

PRIMARY MAXIMUM AVAILABILITY UNPROTECTED TO STANDBY

重启数据库

RMAN备份到/bak/dgbak下并将相关文件传输到standby中

rman target / <<ENDcrosscheck archivelog all;run{backup format '/bak/dgbak/db_%u_%d_%s'  database;}crosscheck archivelog all;run{ sql"alter system archive log current"; backupformat '/bak/dgbak/ar_%t_%s_%p' archivelog all;}run{backup as compressed backupsetformat='/bak/dgbak/standby.ctl'tag='standby'current controlfile for standby reuse;}crosscheck archivelog all;run{ sql"alter system archive log current"; backupformat '/bak/dgbak/ar_%t_%s_%p' archivelog all ;}exitEND

-----------------------------------------------------------------------------------------------

cd/bak/dgbakscp *zhbqdb2:/bak/dgbak scp$ORACLE_HOME/network/admin/tnsnames.ora zhbqdb2:$ORACLE_HOME/network/admin

备机的配置

创建必要的目录

mkdir /u01/oradata/ZHDYDB2mkdir /u01/flash_recovery_area/ZHDYDB2mkdir /u01/oradata/ZHDYDB2/onlinelogmkdir/u01/flash_recovery_area/ZHDYDB2/onlinelog mkdir/u01/flash_recovery_area/ZHDYDB2/arch mkdir/u01/flash_recovery_area/ZHDYDB2/archivelog mkdir/u01/flash_recovery_area/ZHDYDB1 mkdir/u01/flash_recovery_area/ZHDYDB1/arch mkdir/u01/flash_recovery_area/ZHDYDB1/archivelog mkdir -p /opt/app/adminmkdir -p/opt/app/admin/zhdydb1cd/opt/app/admin/zhdydb1mkdir adump  bdump cdump  dpdump  pfile udump

将相关文件移到对应的位置

cd/bak/dgbak/

mv initzhdydb2.ora orapwzhdydb2 $ORACLE_HOME/dbs/

编辑初始化参数

vi$ORACLE_HOME/dbs/initzhdydb2.ora

去掉参数：

*.remote_listener='LISTENERS_ORADB'

*.local_listener='LISTENERS_ORADB'

修改以下参数

*.cluster_database=false

确定此时standby环境变量里的ORACLE_SID为zhdydb2

sqlplus / assysdba

startup nomount

create spfilefrom pfile;shutdownimmediatestartup nomount altersystem set LOG_ARCHIVE_CONFIG='DG_CONFIG=(zhdydb1,zhdydb2)'  scope=spfile; altersystem set db_unique_name=zhdydb2 scope=spfile;altersystem setLOG_ARCHIVE_DEST_1='LOCATION=/u01/flash_recovery_area/ZHDYDB2/archivelog  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)DB_UNIQUE_NAME=zhdydb2'scope=spfile;  altersystem setLOG_ARCHIVE_DEST_2='SERVICE=zhdydb1LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=zhdydb1' scope=spfile; altersystem setSTANDBY_ARCHIVE_DEST='LOCATION=/u01/flash_recovery_area/ZHDYDB2/arch'  scope=spfile sid='*'; alter system set db_file_name_convert='/u01/oradata/ZHDYDB2/','/u01/oradata/ZHDYDB1/'scope=spfile; alter system set log_file_name_convert='/u01/flash_recovery_area/ZHDYDB2/','/u01/flash_recovery_area/ZHDYDB1/'scope=spfile; altersystem set FAL_SERVER=zhdydb1 scope=spfile;altersystem set FAL_CLIENT=zhdydb2 scope=spfile;alter system set STANDBY_FILE_MANAGEMENT=AUTO  scope=spfile;

shutdown immediate

克隆数据库

前面已经将zhdydb1中的tnsnames.ora复制到standby中，此时再创建一个到客户端的软连接：

ln -s$ORACLE_HOME/network/admin/tnsnames.ora $MY_CLIENT/network/admin/tnsnames.orals -l$MY_CLIENT/network/admin/

SQL> startup nomount;

rman target sys/aaa@zhdydb1 auxiliary / <<EOF CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO'/bak/dgbak/%F'; DUPLICATE TARGET DATABASE FOR STANDBY NOFILENAMECHECK  DORECOVER; EOF

重建standby的redo logfile group

alter database drop standby logfile group 8/alter database drop standby logfile group 9/alter database drop standby logfile group 10/alter database drop standby logfile group 11/alter database drop standby logfile group 12/alter database drop standby logfile group 13/alter database drop standby logfile group 14/alter database drop standby logfile group 15/

ALTER DATABASE ADD STANDBY LOGFILE GROUP 8('/u01/oradata/ZHDYDB2/onlinelog/sdbylog8a.log', '/u01/flash_recovery_area/ZHDYDB2/onlinelog/sdbylog8a.log') SIZE 100M; ALTER DATABASE ADD STANDBY LOGFILE GROUP 9('/u01/oradata/ZHDYDB2/onlinelog/sdbylog9a.log', '/u01/flash_recovery_area/ZHDYDB2/onlinelog/sdbylog9a.log') SIZE 100M; ALTER DATABASE ADD STANDBY LOGFILE GROUP 10('/u01/oradata/ZHDYDB2/onlinelog/sdbylog10a.log', '/u01/flash_recovery_area/ZHDYDB2/onlinelog/sdbylog10a.log') SIZE 100M; ALTER DATABASE ADD STANDBY LOGFILE GROUP 11('/u01/oradata/ZHDYDB2/onlinelog/sdbylog11a.log', '/u01/flash_recovery_area/ZHDYDB2/onlinelog/sdbylog11a.log') SIZE 100M; ALTER DATABASE ADD STANDBY LOGFILE GROUP 12('/u01/oradata/ZHDYDB2/onlinelog/sdbylog12a.log', '/u01/flash_recovery_area/ZHDYDB2/onlinelog/sdbylog12a.log') SIZE 100M; ALTER DATABASE ADD STANDBY LOGFILE GROUP 13('/u01/oradata/ZHDYDB2/onlinelog/sdbylog13a.log', '/u01/flash_recovery_area/ZHDYDB2/onlinelog/sdbylog13a.log') SIZE 100M; ALTER DATABASE ADD STANDBY LOGFILE GROUP 14('/u01/oradata/ZHDYDB2/onlinelog/sdbylog14a.log', '/u01/flash_recovery_area/ZHDYDB2/onlinelog/sdbylog14a.log') SIZE 100M; ALTER DATABASE ADD STANDBY LOGFILE GROUP 15('/u01/oradata/ZHDYDB2/onlinelog/sdbylog15a.log', '/u01/flash_recovery_area/ZHDYDB2/onlinelog/sdbylog15a.log') SIZE 100M;

查看状态

SELECTGROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG; columnmember  format a50selectgroup#,member from v$logfile order by group#;

启动standby

添加一个监听，将数据库名zhdydb1和实例名zhdydb2注册到监听中

netca

netmgr

启动监听

重新启动standby数据库

SQL> STARTUP NOMOUNT;

SQL> ALTER DATABASE MOUNT STANDBY DATABASE;

ALTERDATABASE RECOVER MANAGED STANDBY DATABASE [ using current logfile ] DISCONNECT FROMSESSION;

关闭standby

ALTERDATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

SHUTDOWNIMMEDIATE;

lsnrctl stop

检查dg的同步情况

分别在primary和standby中执行如下sql语句

SELECT THREAD#, MAX(SEQUENCE#) AS"LAST_APPLIED_LOG" FROM V$LOG_HISTORY GROUP BY THREAD#;

查询从Primary接收到的所有归档(Standby 端)
SQL> SELECT REGISTRAR, CREATOR, THREAD#, SEQUENCE#, FIRST_CHANGE#,NEXT_CHANGE# FROM V$ARCHIVED_LOG;

查看alert日志

tail -f$ORACLE_BASE/admin/zhdydb1/bdump/alert_zhdydb1.log

tail -f$ORACLE_BASE/admin/zhdydb1/bdump/alert_zhdydb2.log

GAP处理

1）查看是否有日志GAP：

SQL> SELECT UNIQUE THREAD#,MAX(SEQUENCE#) OVER(PARTITION BY THREAD#) LAST FROM V$ARCHIVED_LOG;

　SQL> SELECTTHREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

2）如果有，则拷贝过来

3）手工的注册这些日志：

SQL> ALTERDATABASE REGISTER LOGFILE '路径';

switchover

主库切成备库：

1. 查询当前的状态：

SQL> selectswitchover_status from v$database;

若状态为sessions active，把连到主库的会话关闭掉。或切换时加上with sessionshutdown子句来断开这些会话

2. 切换到physical standby

SQL> alterdatabase commit to switchover to physical standby with session shutdown;

3. 关闭数据库

SQL> shutdownimmediate

4. 启动数据库

SQL> startupnomount;

SQL> alterdatabase mount standby database;

SQL> alterdatabase recover managed standby database using current logfile disconnect fromsession;

备库切成主库

1. 执行完上面的步骤以后，把原备库切成主库

SQL> alterdatabase commit to switchover to primary;

2. 把数据库启动到open状态

SQL> alterdatabase open;

failover

当primary库遇到故障的时候，此时我们需要做一个failover(故障切换)，把备库切到主库顶上应用

步骤如下(在备库中操作)：

1. 结束apply进程

SQL> alter database recover managed standby database finish force;

2. 切换成主库

SQL> alter database commit to switchover to primary;

3. 启动到open状态

SQL> alter database open;