SWITCHOVER是人为计划的互相切换主备,FAILOVER是主库故障的情况下,把备库切换为主库
SWITHOVER大致步骤就是下面几步(主库先切换为standby,备库再切换为primary)
主库
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
SQL> startup mount;
备库
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
SQL> alter database open
现备库原主库
SQL> alter database recover managed standby database disconnect from session;
SQL>alter database recover managed standby database cancel;
SQL>alter database open read only;
SQL>alter database recover managed standby database disconnect from session;
实验环境介绍
主库(已经DBCA建立了DB)
192.168.128.37
Red Hat Enterprise Linux Server release 6.3 (Santiago)
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
Service/service_names/db_name/db_unique_name/instance_name=TDB
备库(没有建立DB,只安装了ORACLE软件,和主库目录结构一致)
192.168.128.47
Red Hat Enterprise Linux Server release 6.3(Santiago)
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
Service/service_names/db_unique_name=TDG
db_name/instance_name=TDB
主库备库的tns都如下
master =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.128.37)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TDB)
)
)
slave =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.128.47)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TDG)
)
)
DATAGUARD搭建实验步骤
1.主库确保处于ARCHIVELOG、force logging模式
2.主库在线修改一下几个参数,并create pfile from spfile,主库把刚创建的pfile($ORACLE_HOME/dbs/initTDB.ora)和
密码文件($ORACLE_HOME/dbs/orapwTDB)拷贝到备库的$ORACLE_HOME/dbs目录
fal_client='master'
fal_server='slave'
standby_file_management='AUTO'
log_archive_config='DG_CONFIG=(TDB,TDG)'
remote_login_passwordfile='EXCLUSIVE'
log_archive_dest_1='location=/u01/app/oracle/archivelog
log_archive_dest_2='service=slave lgwr async VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=TDG'
3.主库备份数据文件和当前控制文件
rman>backup database format '/u01/backup/full_%U.bak';
rman>backup current controlfile for standby format '/u01/backup/control_%U.bak';
4.主库把刚生成的备份拷贝到备库/u01/backup/目录
5.备库修改从主库拷贝过来的pfile($ORACLE_HOME/dbs/initTDB.ora)文件
增加*.db_unique_name='TDG'
修改如下三个参数为
*.fal_client='slave'
*.fal_server='master'
*.log_archive_dest_2='service=master lgwr async VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=TDB'
6.备库顺序执行如下
ORACLE_SID=TDB
sqlplus / as sysdba
SQL>startup nomount
SQL>exit
rman target /
RMAN>restore standby controlfile from '/u01/backup/control_%U.bak';
RMAN>alter database mount
RMAN>restore database;
RMAN>exit;
sqlplus / as sysdba
SQL>alter database recover managed standby database disconnect from session;
SQL>select sequence#,applied from v$archived_log;
(主库执行alter system archive log current后,执行上面这个语句看主库的日志是否已经在备库应用了,应用了的话再执行如下语句)
SQL>alter database recover managed standby database cancel;
SQL>alter database open read only;
SQL>alter database recover managed standby database disconnect from session;
SWITHOVER切换实验过程
1.查看主备库都正常的情况下,如下语句的结果
主SQL> select database_role, switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PRIMARY SESSIONS ACTIVE
备SQL> select database_role, switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY NOT ALLOWED
2.主库执行
上面第1步知道主库的SWITCHOVER_STATUS是SESSIONS ACTIVE,执行如下
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
官方文档记录:A value of TO STANDBY or SESSIONS ACTIVE indicates that the primary database can be switched to the standby role.
3.再执行第1步的两个语句看什么情况
主SQL> select database_role, switchover_status from v$database;
select database_role, switchover_status from v$database
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 13039
Session ID: 36 Serial number: 7505
备SQL> select database_role, switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY TO PRIMARY
4. 主库启动数据库:
SQL> startup mount;
5.再执行第1步的两个语句看什么情况
主SQL> select database_role, switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY RECOVERY NEEDED
备SQL> select database_role, switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY TO PRIMARY
6.备库执行
上面第5步知道备库的SWITCHOVER_STATUS是TO PRIMARY,执行如下
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
官方文档记录:A value of TO PRIMARY or SESSIONS ACTIVE indicates that the standby database is ready to be switched to the primary role
7.再执行第1步的两个语句看什么情况
主SQL> select database_role, switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY RECOVERY NEEDED
备SQL> select database_role, switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PRIMARY NOT ALLOWED
8.备库开启到open状态(上面第6步执行后备库的v$database.open_mode会从read only状态到mounted状态)
SQL> alter database open
9.再执行第1步的两个语句看什么情况
原主SQL> select database_role, switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY RECOVERY NEEDED
原备SQL> select database_role, switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PRIMARY TO STANDBY|SESSIONS ACTIVE(在两者之间切来切去)
10.原主库即目前备库,执行
SQL> alter database recover managed standby database disconnect from session;
SQL>alter database recover managed standby database cancel;
SQL>alter database open read only;
SQL>alter database recover managed standby database disconnect from session;
11.再执行第1步的两个语句看什么情况
现备,原主
SQL> select database_role, switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY NOT ALLOWED
现主,原备
SQL> select database_role, switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PRIMARY TO STANDBY|SESSIONS ACTIVE(在两者之间切来切去)
SWITHOVER大致步骤就是下面几步(主库先切换为standby,备库再切换为primary)
主库
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
SQL> startup mount;
备库
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
SQL> alter database open
现备库原主库
SQL> alter database recover managed standby database disconnect from session;
SQL>alter database recover managed standby database cancel;
SQL>alter database open read only;
SQL>alter database recover managed standby database disconnect from session;
实验环境介绍
主库(已经DBCA建立了DB)
192.168.128.37
Red Hat Enterprise Linux Server release 6.3 (Santiago)
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
Service/service_names/db_name/db_unique_name/instance_name=TDB
备库(没有建立DB,只安装了ORACLE软件,和主库目录结构一致)
192.168.128.47
Red Hat Enterprise Linux Server release 6.3(Santiago)
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
Service/service_names/db_unique_name=TDG
db_name/instance_name=TDB
主库备库的tns都如下
master =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.128.37)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TDB)
)
)
slave =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.128.47)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TDG)
)
)
DATAGUARD搭建实验步骤
1.主库确保处于ARCHIVELOG、force logging模式
2.主库在线修改一下几个参数,并create pfile from spfile,主库把刚创建的pfile($ORACLE_HOME/dbs/initTDB.ora)和
密码文件($ORACLE_HOME/dbs/orapwTDB)拷贝到备库的$ORACLE_HOME/dbs目录
fal_client='master'
fal_server='slave'
standby_file_management='AUTO'
log_archive_config='DG_CONFIG=(TDB,TDG)'
remote_login_passwordfile='EXCLUSIVE'
log_archive_dest_1='location=/u01/app/oracle/archivelog
log_archive_dest_2='service=slave lgwr async VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=TDG'
3.主库备份数据文件和当前控制文件
rman>backup database format '/u01/backup/full_%U.bak';
rman>backup current controlfile for standby format '/u01/backup/control_%U.bak';
4.主库把刚生成的备份拷贝到备库/u01/backup/目录
5.备库修改从主库拷贝过来的pfile($ORACLE_HOME/dbs/initTDB.ora)文件
增加*.db_unique_name='TDG'
修改如下三个参数为
*.fal_client='slave'
*.fal_server='master'
*.log_archive_dest_2='service=master lgwr async VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=TDB'
6.备库顺序执行如下
ORACLE_SID=TDB
sqlplus / as sysdba
SQL>startup nomount
SQL>exit
rman target /
RMAN>restore standby controlfile from '/u01/backup/control_%U.bak';
RMAN>alter database mount
RMAN>restore database;
RMAN>exit;
sqlplus / as sysdba
SQL>alter database recover managed standby database disconnect from session;
SQL>select sequence#,applied from v$archived_log;
(主库执行alter system archive log current后,执行上面这个语句看主库的日志是否已经在备库应用了,应用了的话再执行如下语句)
SQL>alter database recover managed standby database cancel;
SQL>alter database open read only;
SQL>alter database recover managed standby database disconnect from session;
SWITHOVER切换实验过程
1.查看主备库都正常的情况下,如下语句的结果
主SQL> select database_role, switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PRIMARY SESSIONS ACTIVE
备SQL> select database_role, switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY NOT ALLOWED
2.主库执行
上面第1步知道主库的SWITCHOVER_STATUS是SESSIONS ACTIVE,执行如下
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
官方文档记录:A value of TO STANDBY or SESSIONS ACTIVE indicates that the primary database can be switched to the standby role.
3.再执行第1步的两个语句看什么情况
主SQL> select database_role, switchover_status from v$database;
select database_role, switchover_status from v$database
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 13039
Session ID: 36 Serial number: 7505
备SQL> select database_role, switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY TO PRIMARY
4. 主库启动数据库:
SQL> startup mount;
5.再执行第1步的两个语句看什么情况
主SQL> select database_role, switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY RECOVERY NEEDED
备SQL> select database_role, switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY TO PRIMARY
6.备库执行
上面第5步知道备库的SWITCHOVER_STATUS是TO PRIMARY,执行如下
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
官方文档记录:A value of TO PRIMARY or SESSIONS ACTIVE indicates that the standby database is ready to be switched to the primary role
7.再执行第1步的两个语句看什么情况
主SQL> select database_role, switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY RECOVERY NEEDED
备SQL> select database_role, switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PRIMARY NOT ALLOWED
8.备库开启到open状态(上面第6步执行后备库的v$database.open_mode会从read only状态到mounted状态)
SQL> alter database open
9.再执行第1步的两个语句看什么情况
原主SQL> select database_role, switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY RECOVERY NEEDED
原备SQL> select database_role, switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PRIMARY TO STANDBY|SESSIONS ACTIVE(在两者之间切来切去)
10.原主库即目前备库,执行
SQL> alter database recover managed standby database disconnect from session;
SQL>alter database recover managed standby database cancel;
SQL>alter database open read only;
SQL>alter database recover managed standby database disconnect from session;
11.再执行第1步的两个语句看什么情况
现备,原主
SQL> select database_role, switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY NOT ALLOWED
现主,原备
SQL> select database_role, switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PRIMARY TO STANDBY|SESSIONS ACTIVE(在两者之间切来切去)
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30126024/viewspace-2128272/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/30126024/viewspace-2128272/