环境：DataGuard physical standby

主库：Oracle 11g RAC

备库：Oracle 11g 单实例

 

主备切换

主库切换为备库

 

查询主库状态

SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE     SWITCHOVER_STATUS

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

PRIMARY  TO STANDBY

 

查询备库状态

SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE     SWITCHOVER_STATUS

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

PHYSICAL STANDBY NOT ALLOWED

 

关闭主库另外一个节点实例

[root@rac2 ~]# srvctl stop instance -d enmo -n rac2

 

[root@rac2 ~]# srvctl status database -d enmo

Instance enmo1 is running on node rac1

Instance enmo2 is not running on node rac2

 

查询备库是否启用最大性能模式

SQL> select protection_mode,protection_level from v$database;

PROTECTION_MODE      PROTECTION_LEVEL

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

MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

如果不是执行：

SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;

 

查询备库日志是否全部应用

SQL> select thread#,sequence#,first_time,next_time,applied from v$archived_log where applied='NO';

no rows selected

 

主库切换为备库

SQL> alter database commit to switchover to physical standby with session shutdown;

Database altered.

 

查询新备库状态

SQL>  select database_role,switchover_status from v$database;

DATABASE_ROLE     SWITCHOVER_STATUS

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

PHYSICAL STANDBY RECOVERY NEEDED

 

将新备库启动到mount状态

SQL> shutdown abort;

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area  835104768 bytes

Fixed Size            2232960 bytes

Variable Size           620760448 bytes

Database Buffers    209715200 bytes

Redo Buffers          2396160 bytes

Database mounted.

 

备库切换为主库

查询备库状态

SQL>  select database_role,switchover_status from v$database;

DATABASE_ROLE     SWITCHOVER_STATUS

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

PHYSICAL STANDBY SWITCHOVER PENDING

 

说明：SWITCHOVER_STATUS正常应该是to_primary或session active。

 

查询日志应用情况

SQL> select thread#,sequence#,first_time,next_time,applied from v$archived_log where applied='NO';

   THREAD#  SEQUENCE# FIRST_TIM NEXT_TIME APPLIED

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

     1     144 31-JUL-14 31-JUL-14 NO

     1     145 31-JUL-14 31-JUL-14 NO

 

说明：日志没有完全应用。开启日志应用

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

 

查询末应用日志

SQL>  select thread#,sequence#,first_time,next_time,applied from v$archived_log where applied='NO';

no rows selected

 

查询备库状态

SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE     SWITCHOVER_STATUS

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

PHYSICAL STANDBY TO PRIMARY

 

备库切换为主库

SQL> alter database commit to switchover to primary with session shutdown;

Database altered.

 

查询新主库状态

SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE     SWITCHOVER_STATUS

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

PRIMARY  NOT ALLOWED

 

Open新的主库

SQL> alter database open;

Database altered.

 

在新的备库（原来的主库）开启日志应用

SQL>  alter database recover managed standby database using current logfile disconnect from session;

Database altered.

 

查询新主库状态

SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE     SWITCHOVER_STATUS

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

PRIMARY  TO STANDBY

 

查询新备库状态

SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE     SWITCHOVER_STATUS

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

PHYSICAL STANDBY NOT ALLOWED

 

日志应用验证

备库查询日志应用情况

SQL> select THREAD#,SEQUENCE#,FIRST_TIME,NEXT_TIME,APPLIED from v$archived_log order by 2 ;

THREAD#  SEQUENCE# FIRST_TIM NEXT_TIME APPLIED

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

     1     146 03-AUG-14 03-AUG-14 YES

     1     147 03-AUG-14 03-AUG-14 YES

     1     148 03-AUG-14 03-AUG-14 YES

 

 

主库切换日志

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

 

备库查询日志应用情况
SQL> select THREAD#,SEQUENCE#,FIRST_TIME,NEXT_TIME,APPLIED from v$archived_log order by 2 ;

THREAD#  SEQUENCE# FIRST_TIM NEXT_TIME APPLIED

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

     1     146 03-AUG-14 03-AUG-14 YES

     1     147 03-AUG-14 03-AUG-14 YES

     1     148 03-AUG-14 03-AUG-14 YES

     1     149 03-AUG-14 03-AUG-14 YES

     1     150 03-AUG-14 03-AUG-14 YES

 

主库创建用户

SQL> create user test01 identified by test ;

User created.

 

备库查询此用户

SQL> select * from dba_user where username='TEST01';

select * from dba_user where username='TEST01'

              *

ERROR at line 1:

ORA-01219: database not open: queries allowed on fixed tables/views only

 

说明：备库没有open read only。

 

备库开启read only 模式

SQL> alter database open read only;

alter database open read only

*

ERROR at line 1:

ORA-10456: cannot open standby database; media recovery session may be in

Progress

 

说明：需要重启实例。

 

备库重启到read only

SQL> shutdown abort;

ORACLE instance shut down.

SQL> startup open read only;

ORACLE instance started.

Total System Global Area  835104768 bytes

Fixed Size            2232960 bytes

Variable Size           620760448 bytes

Database Buffers    209715200 bytes

Redo Buffers          2396160 bytes

Database mounted.

Database opened.

备库开启日志应用

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

重新查询用户是否创建

SQL> select username from dba_users where username='TEST01';

USERNAME

---------

TEST01               

 

重新切换回原主备库

主库切换为备库

 

查询主库状态

SQL> select database_role,switchover_status,open_mode from v$database;

DATABASE_ROLE     SWITCHOVER_STATUS    OPEN_MODE

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

PRIMARY  TO STANDBY        READ WRITE

 

查询备库状态

SQL> select database_role,switchover_status,open_mode from gv$database;

DATABASE_ROLE     SWITCHOVER_STATUS    OPEN_MODE

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

PHYSICAL STANDBY NOT ALLOWED         READ ONLY WITH APPLY

PHYSICAL STANDBY NOT ALLOWED         READ ONLY WITH APPLY

 

关闭备库另一个实例

[root@rac2 ~]# srvctl stop instance -d enmo -n rac2

 

查询是否最大性能模式

SQL> select protection_mode,protection_level from v$database;

 

PROTECTION_MODE      PROTECTION_LEVEL

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

MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

 

查询备库日志是否全部应用

SQL> select thread#,sequence#,first_time,next_time,applied from v$archived_log where applied='NO';

   THREAD#  SEQUENCE# FIRST_TIM NEXT_TIME APPLIED

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

     2      14 28-JUL-14 28-JUL-14 NO

     1     144 31-JUL-14 31-JUL-14 NO

     1     145 31-JUL-14 31-JUL-14 NO

 

主库切换成备库并启动到mount状态

SQL> alter database commit to switchover to physical standby with session shutdown;

Database altered.

 

查询新备库状态

SQL> select database_role,switchover_status,open_mode from v$database;

DATABASE_ROLE     SWITCHOVER_STATUS    OPEN_MODE

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

PHYSICAL STANDBY RECOVERY NEEDED      READ WRITE

 

新备库启动到mount状态

SQL> shutdown abort;

ORACLE instance shut down.

SQL> startup mount;

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area  835104768 bytes

Fixed Size            2232960 bytes

Variable Size           524291456 bytes

Database Buffers    306184192 bytes

Redo Buffers          2396160 bytes

Database mounted.

 

查询新备库状态

SQL> select database_role,switchover_status,open_mode from v$database;

DATABASE_ROLE     SWITCHOVER_STATUS    OPEN_MODE

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

PHYSICAL STANDBY RECOVERY NEEDED      MOUNTED

 

 

备库切换为主库

查询备库状态

SQL> select database_role,switchover_status,open_mode from gv$database;

DATABASE_ROLE     SWITCHOVER_STATUS    OPEN_MODE

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

PHYSICAL STANDBY TO PRIMARY          READ ONLY WITH APPLY

 

将备库切换为主库

SQL> alter database commit to switchover to primary with session shutdown;

Database altered.

 

查询新主库状态

SQL> select database_role,switchover_status,open_mode from gv$database;

DATABASE_ROLE     SWITCHOVER_STATUS    OPEN_MODE

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

PRIMARY  NOT ALLOWED       MOUNTED

 

open新主库

SQL> alter database open ;

Database altered.

 

新备库开启read only

SQL> alter database open read only;

Database altered.

 

新备库开启日志应用

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.


------------------end--------------------
            
                DBA_建瑾
                2014.8.19