以下操作在物理备机实现
一、日志应用管理
启用日志应用
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>
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
-------------------------------------------------- ---------- --- ---
/u01/app/oracle/archdest/1_2_749662393.dbf
/u01/app/oracle/archdest/1_3_749662393.dbf
/u01/app/oracle/archdest/1_4_749662393.dbf
/u01/app/oracle/archdest/1_5_749662393.dbf
/u01/app/oracle/archdest/1_6_749662393.dbf
/u01/app/oracle/archdest/1_7_749662393.dbf
/u01/app/oracle/archdest/1_8_749662393.dbf
/u01/app/oracle/archdest/1_9_749662393.dbf
/u01/app/oracle/archdest/1_10_749662393.dbf
9 rows selected.
查看dataguard 备机是否启用了日志应用,如果看不到mrp进程,说明没有应用日志。
SQL> select process,status from v$managed_standby;
PROCESS
--------- ------------
ARCH
ARCH
RFS
RFS
RFS
二、oracle dataguard 数据库保护模式的变更
检查当前数据库是处在那种角色,以及现在使用的保护模式,使用如下的查询语句
在主机 primary 192.168.1.90,执行语句
SQL>select DATABASE_ROLE,PROTECTION_MODE,PROTECTION_LEVEL from v$database;
DATABASE_ROLE
---------------- -------------------- --------------------
PRIMARY
在备机 standby 192.168.1.91,执行语句
SQL> select DATABASE_ROLE,PROTECTION_MODE,PROTECTION_LEVEL from v$database;
DATABASE_ROLE
---------------- -------------------- --------------------
PHYSICAL STANDBY 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
Fixed Size
Variable Size
Database Buffers
Redo Buffers
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
---------------- -------------------- --------------------
PRIMARY
在备机 standby 192.168.1.91,执行语句,确认修改是否生效
SQL> show parameter db_un
NAME
------------------------------------ ----------- ------------------------------
db_unique_name
SQL> select DATABASE_ROLE,PROTECTION_MODE,PROTECTION_LEVEL from v$database;
DATABASE_ROLE
---------------- -------------------- --------------------
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
---------------- --------------------
PRIMARY
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
Fixed Size
Variable Size
Database Buffers
Redo Buffers
Database mounted.
SQL> recover managed standby database disconnect;
Media recovery complete.
SQL> select database_role,switchover_status from v$database;
DATABASE_ROLE
---------------- --------------------
PHYSICAL STANDBY NOT ALLOWED
在备机 standby 192.168.1.91,执行如下语句,完成角色转换
SQL>
DATABASE_ROLE
---------------- --------------------
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
---------------- --------------------
PRIMARY
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL> alter database open;
Database altered.