RAC+单实例DG的切换

https://blog.csdn.net/xxzhaobb/article/details/79109155

RAC+单实例DG的搭建过程

https://blog.csdn.net/xxzhaobb/article/details/79108963

之前切换不成功，和参数设置有关。注意的参数是sid=* 之类的，刚搭建好的环境

racdbdg是单实例的，是备库，rac节点是主库。

搭建完毕，切换了一次，刚好主库是单实例的racdbdg，rac节点是备库了。

进行后续的切换

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

primary ：racdbdg 单实例

SYS@racdbdg>select database_role,switchover_status from v$database;

DATABASE_ROLE SWITCHOVER_STATUS

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

PRIMARY TO STANDBY

SYS@racdbdg>

Standby ：是RAC节点

SYS@racdb2>select database_role,switchover_status from v$database;

DATABASE_ROLE SWITCHOVER_STATUS

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

PHYSICAL STANDBY NOT ALLOWED

SYS@racdb1>select database_role,switchover_status from v$database;

DATABASE_ROLE SWITCHOVER_STATUS

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

PHYSICAL STANDBY NOT ALLOWED

或者这样查询

SYS@racdb2>select inst_id,database_role,switchover_status from gv$database;

INST_ID DATABASE_ROLE SWITCHOVER_STATUS

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

2 PHYSICAL STANDBY NOT ALLOWED

1 PHYSICAL STANDBY NOT ALLOWED

--------开始切换

查看主库的状态在主机 racdbdg上查看。是to standby ,可以切换

SYS@racdbdg>select database_role,switchover_status from v$database;

DATABASE_ROLE SWITCHOVER_STATUS

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

PRIMARY TO STANDBY

SYS@racdbdg>

-- 将主库切换到备库状态

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

SYS@racdbdg>alter database commit to switchover to physical standby with session shutdown;

Database altered.

将新的备库启动到mount状态，这个时候新的备库是关闭状态，启动后，状态是备库

SYS@racdbdg>archive log list

ORA-01012: not logged on

SYS@racdbdg>conn / as sysdba

Connected to an idle instance.

SYS@racdbdg>startup mount

ORACLE instance started.

Total System Global Area 1235959808 bytes

Fixed Size 2252784 bytes

Variable Size 922746896 bytes

Database Buffers 301989888 bytes

Redo Buffers 8970240 bytes

Database mounted.

SYS@racdbdg>

SYS@racdbdg>select database_role from v$database;

DATABASE_ROLE

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

PHYSICAL STANDBY

SYS@racdbdg>

-- 将原备库切换为主库。在rac 任意一个节点上执行

-- 查看rac节点的状态，是可以切换的

SYS@racdb1>select database_role,switchover_status from v$database;

DATABASE_ROLE SWITCHOVER_STATUS

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

PHYSICAL STANDBY TO PRIMARY

SYS@racdb2>select inst_id,database_role,switchover_status from gv$database;

INST_ID DATABASE_ROLE SWITCHOVER_STATUS

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

1 PHYSICAL STANDBY SESSIONS ACTIVE

2 PHYSICAL STANDBY SESSIONS ACTIVE

SYS@racdb2>select database_role,switchover_status from v$database;

DATABASE_ROLE SWITCHOVER_STATUS

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

PHYSICAL STANDBY TO PRIMARY

SYS@racdb2>

-- 进行切换，在任何一个节点执行。其中另一个节点，会自动切换为mount状态

--在节点2上执行：

SYS@racdb2>alter database commit to switchover to primary with session shutdown ;

Database altered.

SYS@racdb2>select switchover_status ,open_mode,database_role from v$database;

SWITCHOVER_STATUS OPEN_MODE DATABASE_ROLE

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

NOT ALLOWED MOUNTED PRIMARY

SYS@racdb2>alter database open;

Database altered.

SYS@racdb2>

--另一个节点状态

SYS@racdb1>archive log list

ORA-03135: connection lost contact

SYS@racdb1>conn / as sysdba

Connected.

SYS@racdb1>

-- 这个时候，主库是rac。备库是单实例的racdbdg了在主库上插入数据，备库查询

SYS@racdb2>select count(*) from t;

COUNT(*)

----------

6

SYS@racdb2>insert into t values(7);

1 row created.

SYS@racdb2>commit;

Commit complete.

SYS@racdbdg>select * from t;

ID

----------

1

2

3

4

5

6

7

7 rows selected.

------- 再切换一次，主备切换

现在的主库是双节点的rac，备库是单实例的racdbdg 。再切换一次，把主库切换成单节点，备库切换成rac

SYS@racdb1>select database_role,switchover_status from v$database;

DATABASE_ROLE SWITCHOVER_STATUS

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

PRIMARY TO STANDBY

SYS@racdb1>alter database commit to switchover to physical standby with session shutdown;

Database altered.

SYS@racdb1>

切换为备库后，原来的双节点的主库变成备库，两个节点都被关闭。需要mount

SYS@racdb1>select open_mode ,database_role from v$database;

select open_mode ,database_role from v$database

*

ERROR at line 1:

ORA-01034: ORACLE not available

Process ID: 9039

Session ID: 32 Serial number: 19

SYS@racdb1>conn / as sysdba

Connected to an idle instance.

SYS@racdb1>startup mount;

ORACLE instance started.

Total System Global Area 1235959808 bytes

Fixed Size 2252784 bytes

Variable Size 1090519056 bytes

Database Buffers 134217728 bytes

Redo Buffers 8970240 bytes

Database mounted.

SYS@racdb1>select open_mode ,database_role from v$database;

OPEN_MODE DATABASE_ROLE

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

MOUNTED PHYSICAL STANDBY

SYS@racdb1>

-- 切换单实例的备库为主库：

SYS@racdbdg>select database_role,switchover_status from v$database;

DATABASE_ROLE SWITCHOVER_STATUS

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

PHYSICAL STANDBY TO PRIMARY

SYS@racdbdg>

SYS@racdbdg>select database_role,switchover_status from v$database;

DATABASE_ROLE SWITCHOVER_STATUS

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

PHYSICAL STANDBY TO PRIMARY

SYS@racdbdg>alter database commit to switchover to primary with session shutdown ;

Database altered.

SYS@racdbdg>conn / as sysdba

Connected.

SYS@racdbdg>select database_role,switchover_status from v$database;

DATABASE_ROLE SWITCHOVER_STATUS

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

PRIMARY NOT ALLOWED

SYS@racdbdg>alter database open;

Database altered.

SYS@racdbdg>

--- 验证主库插入数据

SYS@racdbdg>select count(*) from t;

COUNT(*)

----------

8

SYS@racdbdg>insert into t values(9);

1 row created.

SYS@racdbdg>commit;

Commit complete.

SYS@racdbdg>

-- 备库查询

SYS@racdb2>archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination +FRA

Oldest online log sequence 0

Next log sequence to archive 0

Current log sequence 0

SYS@racdb2>archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination +FRA

Oldest online log sequence 0

Next log sequence to archive 0

Current log sequence 0

SYS@racdb2>select count(*) from t;

COUNT(*)

----------

9

SYS@racdb2>

SYS@racdb1>archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination +FRA

Oldest online log sequence 23

Next log sequence to archive 0

Current log sequence 23

SYS@racdb1>

SYS@racdbdg>archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /u01/archivelog

Oldest online log sequence 22

Next log sequence to archive 23

Current log sequence 23

SYS@racdbdg>

从上面可以看到，切换成功了。

从alert log中看到的一些信息。说明，在搭建的时候，要考虑sid

Archived Log entry 91 added for thread 2 sequence 16 ID 0x396d1acf dest 1:

Fri Jan 19 15:44:15 2018

ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=MEMORY SID='*';

Fri Jan 19 15:45:43 2018

Thread 1 cannot allocate new log, sequence 23

Checkpoint not complete

Current log# 1 seq# 22 mem# 0: /u01/app/oracle/oradata/racdbdg/group_1.257.965769287

******************************************************************

LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2

******************************************************************

LGWR: Standby redo logfile selected for thread 1 sequence 23 for destination LOG_ARCHIVE_DEST_2

Thread 1 advanced to log sequence 23 (LGWR switch)

Current log# 2 seq# 23 mem# 0: /u01/app/oracle/oradata/racdbdg/group_2.258.965769287

Fri Jan 19 15:45:45 2018

Archived Log entry 94 added for thread 1 sequence 22 ID 0x396d1acf dest 1: