oracle alter database switch,ORACLE 11g dataguard系列，手工切换测试

在我的博客的基础上，进行手工切换测试

当前环境介绍

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状态