RAC+單例項DG的切換

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. 

 

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: