ORACLE 11g dataguard系列，手工切換測試

在我的部落格的基礎上，進行手工切換測試

《 ORACLE 11g dataguard配置練習》http://blog.itpub.net/27771627/viewspace-1276914/

當前環境介紹

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狀態