在我的博客的基础上,进行手工切换测试
当前环境介绍
primary数据库 10.1.1.21
standby数据库 10.1.1.23
当前dataguard主要配置如下
切换测试开始
一、在primary数据库执行
1、查看当前数据库角色状态
SQL> select switchover_status,database_role from v$database;
当前的standby数据库状态
2、在主数据库执行切换命令
alter database commit to switchover to physical standby;
3、在主数据库执行以下命令
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 830930944 bytes
Fixed Size 2217912 bytes
Variable Size 574621768 bytes
Database Buffers 247463936 bytes
Redo Buffers 6627328 bytes
Database mounted.
SQL> alter database open read only;
Database altered.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
4、查询切换后的角色,已经成为standby数据库了。
二、在standby数据库执行
1、查询switchover状态,目前已是to primary状态,满足切换条件了
select switchover_status,database_role from v$database;
2、执行切换为primary数据库
SQL> alter database commit to switchover to primary;
Database altered.
3、查询当前角色
4、重启数据库实例,由于之前原standby数据库是read only打开的重启一下数据库实例
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 830930944 bytes
Fixed Size 2217912 bytes
Variable Size 490735688 bytes
Database Buffers 331350016 bytes
Redo Buffers 6627328 bytes
Database mounted.
Database opened.
三、切换后测试
修改一张表内容,测试切换后是否正常
在新的primary实例上创建执行以下操作测试
查询当前dba_users表中用户
SQL> select username from dba_users;
USERNAME
------------------------------
SYS
SYSTEM
DBSNMP
SYSMAN
TESTTEST
QWE
DBATEST
HR
TTE
OUTLN
FLOWS_FILES
USERNAME
------------------------------
MDSYS
ORDSYS
EXFSYS
WMSYS
APPQOSSYS
APEX_030200
OWBSYS_AUDIT
ORDDATA
CTXSYS
ANONYMOUS
XDB
USERNAME
------------------------------
ORDPLUGINS
OWBSYS
SI_INFORMTN_SCHEMA
OLAPSYS
SCOTT
ORACLE_OCM
XS$NULL
BI
PM
MDDATA
IX
USERNAME
------------------------------
SH
DIP
OE
APEX_PUBLIC_USER
SPATIAL_CSW_ADMIN_USR
SPATIAL_WFS_ADMIN_USR
39 rows selected.
创建一个新用户
SQL> create user liuzhen identified by lz;
User created.
查询当前用户40个
SQL> select username from dba_users;
USERNAME
------------------------------
SYS
SYSTEM
DBSNMP
SYSMAN
TESTTEST
QWE
LIUZHEN
DBATEST
HR
TTE
OUTLN
USERNAME
------------------------------
FLOWS_FILES
MDSYS
ORDSYS
EXFSYS
WMSYS
APPQOSSYS
APEX_030200
OWBSYS_AUDIT
ORDDATA
CTXSYS
ANONYMOUS
USERNAME
------------------------------
XDB
ORDPLUGINS
OWBSYS
SI_INFORMTN_SCHEMA
OLAPSYS
SCOTT
ORACLE_OCM
XS$NULL
BI
PM
MDDATA
USERNAME
------------------------------
IX
SH
DIP
OE
APEX_PUBLIC_USER
SPATIAL_CSW_ADMIN_USR
SPATIAL_WFS_ADMIN_USR
40 rows selected.
切换日志组
SQL> alter system switch logfile;
System altered.
在新的standby上面查询dba_tables
SQL> select username from dba_users;
USERNAME
------------------------------
SYS
SYSTEM
DBSNMP
SYSMAN
TESTTEST
QWE
LIUZHEN
DBATEST
HR
TTE
OUTLN
USERNAME
------------------------------
FLOWS_FILES
MDSYS
ORDSYS
EXFSYS
WMSYS
APPQOSSYS
APEX_030200
OWBSYS_AUDIT
ORDDATA
CTXSYS
ANONYMOUS
USERNAME
------------------------------
XDB
ORDPLUGINS
OWBSYS
SI_INFORMTN_SCHEMA
OLAPSYS
SCOTT
ORACLE_OCM
XS$NULL
BI
PM
MDDATA
USERNAME
------------------------------
IX
SH
DIP
OE
APEX_PUBLIC_USER
SPATIAL_CSW_ADMIN_USR
SPATIAL_WFS_ADMIN_USR
40 rows selected.
查看新的standby数据库alert日志,以下表明,日志应用服务是正常的。
RFS[4]: Assigned to RFS process 6598
RFS[4]: Identified database type as 'physical standby': Client is ARCH pid 12180
Tue Sep 23 06:02:31 2014
Archived Log entry 116 added for thread 1 sequence 81 ID 0xca43b088 dest 1:
Tue Sep 23 06:02:31 2014
RFS[2]: Selected log 4 for thread 1 sequence 82 dbid -919775302 branch 840741181
Tue Sep 23 06:02:31 2014
Media Recovery Log /home/oracle/app/oracle/flash_recovery_area/SAP/archivelog/1_81_840741181.dbf
Media Recovery Waiting for thread 1 sequence 82 (in transit)
手动切换data guard完成。总结步骤如下
primary数据库执行以下几步
1、select
switchover_status,database_role from v$database; //显示 TO STANDBY 、PRIMARY
2、alter database
commit to switchover to physical standby;
如果有活动的session使用alter database commit to switchover to
physical standby with
session shutdown
3、重启实例,启动到read only
4、alter database
recover managed standby database disconnect from session;
5、select
database_role from v$database; //显示PHYSICAL STANDBY
standby数据库执行以下几步
1、select
switchover_status,database_role from v$database; //显示TO
PRIMARY、 PHYSICAL STANDBY
2、alter database
commit to switchover to primary;
3、重启实例,到open状态