Oracle 11g rac adg 主备库切换
一、 环境描述
|
序号 |
名称 |
数据唯一名称 |
实例名称 |
服务名称 |
地址 |
|
1 |
主库 |
orcl_st |
orcl1/orcl2 |
Orcl_st |
10.10.10.15 |
|
2 |
备库 |
orcl |
orcl |
orcl |
10.10.10.20 |
1、 检查主库状态
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
ORCL READ WRITE
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +FLA/orcl/
Oldest online log sequence 204
Next log sequence to archive 206
Current log sequence 206
SQL> show parameter db_unique;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string orcl_st
SQL> show parameter service;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string ORCL
SQL> select status,error from v$archive_dest where dest_id=2;
STATUS ERROR
--------- -----------------------------------------------------------------
VALID
SQL> select database_role,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PRIMARY TO STANDBY
2、 检查备库状态
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
ORCL READ ONLY WITH APPLY
SQL> select process,sequence#,status from v$managed_standby;
PROCESS SEQUENCE# STATUS
--------- ---------- ------------
ARCH 205 CLOSING
ARCH 0 CONNECTED
ARCH 0 CONNECTED
ARCH 80 CLOSING
RFS 0 IDLE
RFS 0 IDLE
RFS 81 IDLE
RFS 206 IDLE
RFS 0 IDLE
RFS 0 IDLE
MRP0 81 APPLYING_LOG
11 rows selected.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /flash_recovery/orcl/
Oldest online log sequence 0
Next log sequence to archive 0
Current log sequence 206
SQL> select database_role,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY NOT ALLOWED
二、 切换主库到备库
1 、备注：主库正常情况是有用户连接的，所有在切换时采用以下语句：
SQL> alter database commit to switchover to standby with session shutdown ;
Database altered.
SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
[oracle@rac1 admin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sun Apr 13 23:01:12 2025
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
[oracle@rac1 admin]$ srvctl status database -d orcl
Instance orcl1 is not running on node rac1
Instance orcl2 is not running on node rac2
[oracle@rac1 admin]$ srvctl start database -d orcl
[oracle@rac1 admin]$ srvctl status database -d orcl
Instance orcl1 is running on node rac1
Instance orcl2 is running on node rac2
[oracle@rac1 admin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sun Apr 13 23:02:37 2025
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> select instance_name,status from gv$instance;
INSTANCE_NAME STATUS
---------------- ------------
orcl1 OPEN
orcl2 OPEN
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
ORCL READ ONLY
三、 切换备库到主库
1 、检查备库情况，并切换到主库
SQL> select database_role,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY TO PRIMARY
2 、备库切换到主库
SQL> alter database commit to switchover to primary;
Database altered.
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
ORCL MOUNTED
SQL> alter database open;
Database altered.
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
ORCL READ WRITE
3、 检查现在主库状态
SQL> select status,error from v$archive_dest where dest_id=2;
STATUS ERROR
--------- -----------------------------------------------------------------
VALID
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /flash_recovery/orcl/
Oldest online log sequence 207
Next log sequence to archive 209
Current log sequence 209
检查主库日志：（正常，无报错！）
Setting Resource Manager plan SCHEDULER[0x32DF]:DEFAULT_MAINTENANCE_PLAN via scheduler window
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
Sun Apr 13 23:07:07 2025
Starting background process VKRM
Sun Apr 13 23:07:07 2025
VKRM started with pid=30, OS id=12378
Thread 1 advanced to log sequence 209 (LGWR switch)
Current log# 3 seq# 209 mem# 0: /oradata/ORCL/onlinelog/o1_mf_3_mzqjmxmy_.log
Current log# 3 seq# 209 mem# 1: /flash_recovery/ORCL/onlinelog/o1_mf_3_mzqjmxz3_.log
Sun Apr 13 23:07:08 2025
Archived Log entry 23 added for thread 1 sequence 208 ID 0x66e2ba3b dest 1:
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
LNS: Standby redo logfile selected for thread 1 sequence 209 for destination LOG_ARCHIVE_DEST_2
ARC3: Standby redo logfile selected for thread 1 sequence 208 for destination LOG_ARCHIVE_DEST_2
4、 检查现在备库状态（rac集群）
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +FLA/orcl/
Oldest online log sequence 205
Next log sequence to archive 0
Current log sequence 209
# 检查日志（无报错！）
Sun Apr 13 23:07:03 2025
Using STANDBY_ARCHIVE_DEST parameter default value as +FLA/orcl/
RFS[1]: Assigned to RFS process 57506
RFS[1]: Selected log 4 for thread 1 sequence 207 dbid 1725403206 branch 1197622472
Sun Apr 13 23:07:03 2025
Archived Log entry 363 added for thread 1 sequence 207 ID 0x66e2ba3b dest 1:
RFS[1]: Opened log for thread 2 sequence 82 dbid 1725403206 branch 1197622472
Archived Log entry 364 added for thread 2 sequence 82 rlc 1197622472 ID 0x0 dest 2:
Sun Apr 13 23:07:08 2025
Primary database is in MAXIMUM PERFORMANCE mode
RFS[2]: Assigned to RFS process 57512
RFS[2]: Selected log 5 for thread 1 sequence 209 dbid 1725403206 branch 1197622472
# 应用同步日志
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
# 备库日志（无报错！）
Completed: alter database recover managed standby database using current logfile disconnect from session
Media Recovery Log +FLA/orcl/1_208_1197622472.dbf
Media Recovery Waiting for thread 1 sequence 209 (in transit)
Recovery of Online Redo Log: Thread 1 Group 5 Seq 209 Reading mem 0
Mem# 0: +DATA/orcl_st/onlinelog/group_5.276.1197724689
Mem# 1: +FLA/orcl_st/onlinelog/group_5.432.1197724689
四、 验证主备库数据同步测试
1、 主库创建表空间、表
SQL> select tablespace_name,status from dba_data_files;
TABLESPACE_NAME STATUS
------------------------------ ---------
UNDOTBS1 AVAILABLE
USERS AVAILABLE
UNDOTBS2 AVAILABLE
SYSAUX AVAILABLE
SYSTEM AVAILABLE
DATA AVAILABLE
6 rows selected.
SQL> create tablespace data99 datafile '/oradata/orcl/data99.dbf' size 100m autoextend on maxsize 2g;
Tablespace created.
SQL> select tablespace_name,status from dba_data_files;
TABLESPACE_NAME STATUS
------------------------------ ---------
UNDOTBS1 AVAILABLE
USERS AVAILABLE
UNDOTBS2 AVAILABLE
SYSAUX AVAILABLE
SYSTEM AVAILABLE
DATA AVAILABLE
DATA99 AVAILABLE
7 rows selected.
SQL> select *from it;
ID
----------
1000
9999
SQL> insert into it values(999999);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from it;
ID
----------
1000
9999
999999
# 检查日志情况（正常，无报错！）
Sun Apr 13 23:14:22 2025
create tablespace data99 datafile '/oradata/orcl/data99.dbf' size 100m autoextend on maxsize 2g
Completed: create tablespace data99 datafile '/oradata/orcl/data99.dbf' size 100m autoextend on maxsize 2g
2、 备库验证表空间、表
SQL> select tablespace_name,status from dba_data_files;
TABLESPACE_NAME STATUS
------------------------------ ---------
UNDOTBS1 AVAILABLE
USERS AVAILABLE
UNDOTBS2 AVAILABLE
SYSAUX AVAILABLE
SYSTEM AVAILABLE
DATA AVAILABLE
DATA99 AVAILABLE
7 rows selected.
SQL> select * from it;
ID
----------
1000
9999
999999
# 检查备库日志（正常，无报错！）
Media Recovery Waiting for thread 1 sequence 209 (in transit)
Recovery of Online Redo Log: Thread 1 Group 5 Seq 209 Reading mem 0
Mem# 0: +DATA/orcl_st/onlinelog/group_5.276.1197724689
Mem# 1: +FLA/orcl_st/onlinelog/group_5.432.1197724689
Sun Apr 13 23:14:23 2025
Successfully added datafile 7 to media recovery
Datafile #7: '+DATA/orcl_st/datafile/data99.290.1198365263'
总结：至此，oracle 11g rac到单库 dg切换完成，互相切换测试无异常，本实验结束。
