Oracle 10g dataguard 物理备库日常维护

以下操作在物理备机实现

一、日志应用管理

启用日志应用

SQL> alter database recover managed standby database disconnect;

Database altered.

启用实时的日志应用 real time apply，开启日志的实时应用需要备库有备重做日志文件的存在。

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

Database altered.

停止日志应用服务

SQL> alter database recover managed standby database cancel;

Database altered.

判断当前备库是否启用了日志实时应用，使用如下语句：

SQL> select RECOVERY_MODE from v$archive_dest_status;

RECOVERY_MODE

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

MANAGED REAL TIME APPLY

MANAGED REAL TIME APPLY

MANAGED REAL TIME APPLY

MANAGED REAL TIME APPLY

MANAGED REAL TIME APPLY

MANAGED REAL TIME APPLY

MANAGED REAL TIME APPLY

MANAGED REAL TIME APPLY

MANAGED REAL TIME APPLY

MANAGED REAL TIME APPLY

MANAGED REAL TIME APPLY

11 rows selected.

 

如果是没有启用日志的实时应用，recovery_mode显示的是MANAGED

SQL>  alter database recover managed standby database disconnect;

Database altered.

SQL> select RECOVERY_MODE from v$archive_dest_status;

RECOVERY_MODE

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

MANAGED

MANAGED

MANAGED

MANAGED

MANAGED

MANAGED

MANAGED

MANAGED

MANAGED

MANAGED

MANAGED

11 rows selected.

在备机检查当前日志文件应用的情况，使用如下语句

SQL> col name for a50

SQL> set linesize 140

SQL> select name,sequence#,archived,applied from v$archived_log order by sequence#;

NAME                                                SEQUENCE# ARC APP

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

/u01/app/oracle/archdest/1_2_749662393.dbf                  2 YES YES

/u01/app/oracle/archdest/1_3_749662393.dbf                  3 YES YES

/u01/app/oracle/archdest/1_4_749662393.dbf                  4 YES YES

/u01/app/oracle/archdest/1_5_749662393.dbf                  5 YES YES

/u01/app/oracle/archdest/1_6_749662393.dbf                  6 YES YES

/u01/app/oracle/archdest/1_7_749662393.dbf                  7 YES YES

/u01/app/oracle/archdest/1_8_749662393.dbf                  8 YES YES

/u01/app/oracle/archdest/1_9_749662393.dbf                  9 YES YES

/u01/app/oracle/archdest/1_10_749662393.dbf                10 YES YES

9 rows selected.

查看dataguard 备机是否启用了日志应用，如果看不到mrp进程，说明没有应用日志。

SQL> select process,status from v$managed_standby;

PROCESS   STATUS

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

ARCH      CONNECTED

ARCH      CONNECTED

RFS       IDLE

RFS       IDLE

RFS       IDLE

二、oracle dataguard 数据库保护模式的变更

检查当前数据库是处在那种角色，以及现在使用的保护模式，使用如下的查询语句

在主机 primary 192.168.1.90，执行语句

SQL>select DATABASE_ROLE,PROTECTION_MODE,PROTECTION_LEVEL from v$database;

 

DATABASE_ROLE    PROTECTION_MODE      PROTECTION_LEVEL

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

PRIMARY          MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

在备机 standby 192.168.1.91，执行语句

SQL> select DATABASE_ROLE,PROTECTION_MODE,PROTECTION_LEVEL from v$database;

DATABASE_ROLE    PROTECTION_MODE      PROTECTION_LEVEL

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

PHYSICAL STANDBY MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

 

升级oracle数据库的保护模式 （MAXIMUM PERFORMANCE-->max availability-->max protection），

如果要升级oracle数据库的保护模式，必须在mount状态下来执行命令

在主机 primary 192.168.1.90，执行如下操作（以下操作只需要在主机操作）

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area  218103808 bytes

Fixed Size                  1218604 bytes

Variable Size              71305172 bytes

Database Buffers          142606336 bytes

Redo Buffers                2973696 bytes

Database mounted.

 

SQL> alter database set standby database to maximize availability;

Database altered.

 

SQL> alter database open;

Database altered.

 

SQL> select DATABASE_ROLE,PROTECTION_MODE,PROTECTION_LEVEL from v$database;

DATABASE_ROLE    PROTECTION_MODE      PROTECTION_LEVEL

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

PRIMARY          MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY

 

在备机 standby 192.168.1.91，执行语句，确认修改是否生效

SQL> show parameter db_un        

NAME                                 TYPE        VALUE

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

db_unique_name                       string      standby

 

SQL> select DATABASE_ROLE,PROTECTION_MODE,PROTECTION_LEVEL from v$database;

DATABASE_ROLE    PROTECTION_MODE      PROTECTION_LEVEL

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

PHYSICAL STANDBY MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY

注：如果降级数据库的保护模式，直接操作就可以，不需要到mount状态。

三.oracle dataguard 数据库切换（switchover）操作测试

切换过程中，主机会首先切换成备机，也就是在同一时间点，在dataguard 配置中有两台备机的存在

在主机 primary 192.168.1.90，执行如下操作

SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE    SWITCHOVER_STATUS

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

PRIMARY          SESSIONS ACTIVE

SQL> alter database commit to switchover to physical standby;

Database altered.

SQL> shutdown immediate

ORA-01507: database not mounted

ORACLE instance shut down.

 

SQL> startup mount

ORACLE instance started.

Total System Global Area  218103808 bytes

Fixed Size                  1218604 bytes

Variable Size              71305172 bytes

Database Buffers          142606336 bytes

Redo Buffers                2973696 bytes

Database mounted.

 

SQL> recover managed standby database disconnect;

Media recovery complete.

 

SQL> select database_role,switchover_status from v$database;

 

DATABASE_ROLE    SWITCHOVER_STATUS

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

PHYSICAL STANDBY NOT ALLOWED

 

在备机 standby 192.168.1.91，执行如下语句，完成角色转换

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;

Database altered.

 

SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE    SWITCHOVER_STATUS

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

PRIMARY          TO STANDBY

 

SQL> select status from v$instance;

STATUS

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

MOUNTED

 

SQL> alter database open;

Database altered.