# 事件簡介

本以為 DG 是個很簡單的工作，結果因為環境的原因，踩了很多坑，所以總結了一下經驗，特意整理了一份完整版操作手冊

# 環境檢查

### 資料庫版本

```sql
opatch lsinventory
SQL> SELECT PATCH_ID, ACTION, ACTION_TIME FROM DBA_REGISTRY_SQLPATCH;
```

### 引數檔案

```
show parameter LOG_ARCHIVE_CONFIG;
show parameter LOG_ARCHIVE_DEST;
show parameter LOG_ARCHIVE_DEST_STATE;
show parameter FAL;
show parameter CONVERT;
show parameter STANDBY_FILE_MANAGEMENT;
```

# network

- TNS

```shell
  cat $ORACLE_HOME/network/admin/tnsnames.ora
  ```

- sqlnet

```shell
  cat $ORACLE_HOME/network/admin/sqlnet.ora
  ```

### Log File

- Standby Redo

```sql
  col member for a60
  SELECT L.THREAD#,
         L.GROUP#,
         L.BYTES / 1024 / 1024 SIZE_M,
         L.STATUS,
         LF.MEMBER,
         LF.STATUS
    FROM V$STANDBY_LOG L
   INNER JOIN V$LOGFILE LF
      ON L.GROUP# = LF.GROUP#
   ORDER BY L.THREAD#, L.GROUP#;
  ```

- Redo

- 檢視檔案是否存在

```sql
    col member for a60
    SELECT L.THREAD#,
           L.GROUP#,
           L.BYTES / 1024 / 1024 SIZE_M,
           L.STATUS,
           LF.MEMBER,
           L.MEMBERS,
           LF.STATUS
      FROM V$LOG L
     INNER JOIN V$LOGFILE LF
        ON L.GROUP# = LF.GROUP#
     ORDER BY L.THREAD#, L.GROUP#;
    ```

- redo 檔案重新命名
    避免路徑不存在，還可以規範命名

```sql
    alter database rename file '/oracle/app/oracle/fast_recovery_area/SIPDS/onlinelog/o1_mf_1_b1p5k81l_.log' to '/oracle/app/oracle/fast_recovery_area/SIPDS/onlinelog/redo01.log';
    ```

### TempFile

- 檢視檔案是否存在

```sql
  col "TableSpace Name" for a30
  col "File Name" for a55
  col "Size (MB)" for a20
  col "Used (MB)" for a20
  SELECT A.TABLESPACE_NAME "TableSpace Name",
         A.FILE_NAME "File Name",
         A.STATUS "Status",
         A.AUTOEXTENSIBLE "Auto",
         TO_CHAR(NVL(A.BYTES / 1024 / 1024, 0), '99G999G990D900') "Size (MB)",
         TO_CHAR(NVL(A.BYTES - NVL(F.BYTES, 0), 0) / 1024 / 1024,
                 '99G999G990D900') "Used (MB)",
         TO_CHAR(NVL(A.BYTES - NVL(F.BYTES, 0), 0) / 1024 / 1024,
                 '99G999G990D900') "Max (MB)",
         TO_CHAR(NVL((A.BYTES - NVL(F.BYTES, 0)) / A.BYTES * 100, 0),
                 '990D00') "Used%"
    FROM DBA_TEMP_FILES A,
         (SELECT FILE_ID, SUM(BYTES) BYTES
            FROM DBA_FREE_SPACE
           GROUP BY FILE_ID) F
   WHERE A.FILE_ID = F.FILE_ID(+)
   order by "TableSpace Name", "File Name";
  ```

- temp 表空間路徑修改

```sql
  alter tablespace temp add tempfile '' size 2000M autoextend on maxsize 31G;
  alter tablespace temp drop tempfile '';
  ```

### RAC

- 資料庫啟動狀態和服務列表

```shell
  srvctl config database -db <db_unique_name>
  ```

- 檢視服務

```shell
  srvctl config service -db <db_unique_name> -s <service_name>
  ```

### Object

- DB Link

```sql
  COLUMN owner        FORMAT a15    HEADING 'Owner'
  COLUMN db_link      FORMAT a25    HEADING 'DB Link Name'   
  COLUMN username     format a15    HEADING 'Username'       
  COLUMN host         format a130   trunc HEADING 'Host'           
  COLUMN created      FORMAT a20    HEADING 'Created'
  SELECT OWNER OWNER,
         DB_LINK,
         USERNAME,
         TO_CHAR(CREATED, 'mm/dd/yyyy HH24:MI:SS') CREATED,
         HOST
    FROM DBA_DB_LINKS
   ORDER BY OWNER, DB_LINK;
  ```

- JOB

```sql
  select * from DBA_JOBS_RUNNING;
  ```

- SCHEDULE

```sql
  select * from DBA_SCHEDULER_RUNNING_JOBS;
  ```

### 同步狀態

- 檢查current sequence#

```sql
  select thread#, 
         sequence#, 
         status
    from v$log;
  ```

- 備庫檢查正在應用的 sequence#

```sql
  SELECT PROCESS,
         THREAD#,
         SEQUENCE#,
         STATUS
    FROM V$MANAGED_STANDBY;
  ```

- 備庫檢查是否存在 GAP

```sql
  SELECT THREAD#,
         LOW_SEQUENCE#,
         HIGH_SEQUENCE#
    FROM V$ARCHIVE_GAP;
  ```

- 檢視日誌同步情況

```sql
  select sequence#,
         applied 
    from dba_logstdby_log;
  ```

- 延遲

```sql
  select * from v$dataguard_stats
  ```

### CDB

驗證是否可以切換

```sql
ALTER DATABASE SWITCHOVER TO <standbydb_unique_name> VERIFY; 
```

# 操作執行

### 斷開應用

- 關閉應用

- 關閉監聽或者鎖掉所有使用者

```sql
  select 'ALTER USER ' || USERNAME || 'ACCOUNT LOCK;'
    from dba_users
   where ACCOUNT_STATUS ='OPEN'
     and USERNAME not in ('SYS','SYSTEM');
  ```

- 殺掉所有非本地連結

```shell
  ps -ef|grep LOCAL=NO |grep <instance_name> |awk '{print $2}' |xargs kill -9
  ```

- 殺掉剩餘會話

```sql
  SELECT 'alter system disconnect session ''' || sid || ',' || serial# ||
         ''' immediate;'
    FROM v$session
   WHERE TYPE = 'USER';
  ```

### 切換

##### 手動切換

- 主庫操作
  如果是rac，關閉除操作節點外的其它資料庫
  保證session只有此一個會話連結

- 關閉主庫

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

- 開啟standby模式

```sql
    startup nomount;
    alter database mount standby database;
    alter database recover managed standby database disconnect from session;
    ```

- 備庫操作

- 切換備庫為主庫

```sql
    alter database commit to switchover to primary WITH SESSION SHUTDOWN;
    ```

- 開啟資料庫
    如果是第一次切換，需做如下操作

##### Broker

切換備機工作

```
DGMGRL> switchover to <standbydb_unique_name>;
```

##### CDB

- 主庫操作，進行切換

```sql
  ALTER DATABASE SWITCHOVER TO <standbydb_unique_name>;
  ```

- 備庫操作

- 啟動至 mount

```sql
    SQL> shutdown immediate;
    SQL> STARTUP MOUNT;
    ```

- 開啟資料庫

```sql
    SQL> ALTER DATABASE OPEN;
    SQL> alter pluggable database all open instances=all;
    ```

- 主庫應用日誌

```sql
  SQL> startup;
  SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
  ```

# 結果確認

1. 驗證角色

```sql
   select database_role,switchover_status from v$database;
   ```

2. 應用模式

```sql
   SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID=2;
   ```

3. 同步延遲

```sql
   select * from v$dataguard_stats
   ```

4. 歸檔確認

```sql
   alter system switch logfile;
   select status,error from v$archive_dest;
   ```

5. 服務確認

```shell
   lsnrctl status
   ```

6. 如果是rac，需要確認啟動狀態

```shell
   srvctl status database -db <standbydb_unique_name>
   crsctl stat res -t
   ```

7. 後臺日誌</standbydb_unique_name></standbydb_unique_name></standbydb_unique_name></instance_name></standbydb_unique_name></service_name></db_unique_name></db_unique_name>

Oracle：DG 的 switchover

相關文章