Oracle 11G Active DataGuard角色切換

1.切換前準備：

1.1主庫檢查：

1.1.1 FAL檢查

SQL> show parameter fal;

NAME                                 TYPE        VALUE

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

fal_client                           string

fal_server                           string

SQL> alter system set fal_client='pri_1522';

SQL> alter system set fal_server='std_1522';

SQL> show parameter fal;

NAME                                 TYPE        VALUE

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

fal_client                           string      pri_1522

fal_server                           string      std_1522

1.1.2 Standby文件自動管理

SQL> show parameter standby_file_management;

NAME                                 TYPE        VALUE

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

standby_file_management              string      MANUAL

SQL> alter system set standby_file_management='auto';

SQL> show parameter standby_file_management;

NAME                                 TYPE        VALUE

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

standby_file_management              string      auto

1.1.3 Convert參數設定

設定數據文件、重做日志轉換目錄

SQL> show parameter convert;

NAME                                 TYPE        VALUE

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

db_file_name_convert                 string      /u01/app/oradata/testdb, /u01/

                                                 app/oradata/testdb

log_file_name_convert                string      /u01/app/archivelog/testdb, /u

                                                 01/app/archivelog/testdb

1.1.4 Log_archive_dest參數

SQL> show parameter log_archive_dest_1;

NAME                                 TYPE        VALUE

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

log_archive_dest_1                   string      location=/u01/app/archivelog/t

                                                 estdb valid_for=(all_logfiles,

                                                 all_roles) db_unique_name=pri_

                                                 db

SQL> show parameter log_archive_dest_2;

NAME                                 TYPE        VALUE

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

log_archive_dest_2                   string      service=std_1522 lgwr sync val

                                                 id_for=(online_logfiles,primar

                                                 y_role) db_unique_name=std_db

1.2備庫檢查

1.2.1 FAL檢查

SQL> show parameter fal;

NAME                                 TYPE        VALUE

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

fal_client                           string      std_1522

fal_server                           string      pri_1522

1.2.2 Standby文件自動管理

SQL> show parameter standby_file_management;

NAME                                 TYPE        VALUE

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

standby_file_management              string      auto

1.2.3 Convert參數設定

SQL> show parameter convert;

NAME                                 TYPE        VALUE

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

db_file_name_convert                 string      /u01/app/oradata/testdb, /u01/

                                                 app/oradata/testdb

log_file_name_convert                string      /u01/app/archivelog/testdb, /u

                                                 01/app/archivelog/testdb

1.2.4 Log_archive_dest參數

SQL> show parameter log_archive_dest_1;

NAME                                 TYPE        VALUE

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

log_archive_dest_1                   string      location=/u01/app/archivelog/t

                                                 estdb valid_for=(all_logfiles,

                                                 all_roles) db_unique_name=std_

                                                 db

SQL> show parameter log_archive_dest_2;

NAME                                 TYPE        VALUE

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

log_archive_dest_2                   string      service=pri_1522 lgwr sync val

                                                 id_for=(online_logfiles,primar

                                                 y_role) db_unique_name=pri_db

1.2.5 確定日志歸檔、force logging打開

SQL> SELECT log_mode, force_logging, open_mode FROM v$database;

LOG_MODE     FOR OPEN_MODE

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

ARCHIVELOG   YES READ ONLY

2.主庫切換成物理備庫

2.1檢查主庫狀態

2.1.1切換前查看主庫是否有GAP

SQL> SELECT database_role, switchover_status, open_mode, protection_mode, protection_level FROM v$database;

DATABASE_ROLE    SWITCHOVER_STATUS    OPEN_MODE            PROTECTION_MODE      PROTECTION_LEVEL

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

PRIMARY          RESOLVABLEGAP        READ WRITE           MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

SQL> SELECT status, gap_status, dest_id FROM v$archive_dest_status WHERE dest_id=2;

STATUS    GAP_STATUS               DEST_ID

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

VALID     RESOLVABLE GAP                    2

2.1.2有GAP，進行一次日志切換

SQL> alter system switch logfile;

SQL> SELECT status, gap_status, dest_id FROM v$archive_dest_status WHERE dest_id=2;

STATUS    GAP_STATUS               DEST_ID

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

VALID     NO GAP                            2

SQL> SELECT database_role, switchover_status, protection_mode, protection_level FROM v$database;

DATABASE_ROLE    SWITCHOVER_STATUS    PROTECTION_MODE      PROTECTION_LEVEL

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

PRIMARY          TOSTANDBY            MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

2.2備庫的狀態

SQL> SELECT switchover_status, database_role, open_mode, protection_mode, protection_level FROM v$database;

DATABASE_ROLE    SWITCHOVER_STATUS    OPEN_MODE            PROTECTION_MODE      PROTECTION_LEVEL

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

PHYSICAL STANDBY NOT ALLOWED          READ ONLY WITH APPLY MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

2.3備庫取消應用日志

SQL> alter database recover managed standby database cancel;

SQL> SELECT database_role, switchover_status, open_mode, protection_mode, protection_level FROM v$database;

DATABASE_ROLE    SWITCHOVER_STATUS    OPEN_MODE            PROTECTION_MODE      PROTECTION_LEVEL

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

PHYSICAL STANDBY SWITCHOVER PENDING   READ ONLY            MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

2.4主庫切換成物理備庫

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

SQL> SELECT database_role, switchover_status, open_mode, protection_mode, protection_level FROM v$database;

SELECT database_role, switchover_status, open_mode, protection_mode, protection_level FROM v$database

                                                                                           *

ERROR at line 1:

ORA-01507: database not mounted

2.5原備庫切換成新主庫

2.5.1查看狀態

SQL> SELECT database_role, switchover_status, open_mode, protection_mode, protection_level FROM v$database;

DATABASE_ROLE    SWITCHOVER_STATUS    OPEN_MODE            PROTECTION_MODE      PROTECTION_LEVEL

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

PHYSICAL STANDBY SWITCHOVER PENDING   READ ONLY            MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

2.5.2原備庫需要應用日志（因原主庫已發生切換產生了日志）

SQL> alter database recover managed standby database disconnect;

SQL> SELECT database_role, switchover_status, open_mode, protection_mode, protection_level FROM v$database;

DATABASE_ROLE    SWITCHOVER_STATUS    OPEN_MODE            PROTECTION_MODE      PROTECTION_LEVEL

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

PHYSICAL STANDBY TO PRIMARY           READ ONLY            MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

2.5.3原備庫切換成新主庫

SQL> alter database commit to switchover to primary;

SQL> SELECT database_role, switchover_status, open_mode, protection_mode, protection_level FROM v$database;

DATABASE_ROLE    SWITCHOVER_STATUS    OPEN_MODE            PROTECTION_MODE      PROTECTION_LEVEL

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

PRIMARY          NOTALLOWED           MOUNTED              MAXIMUM PERFORMANCE  UNPROTECTED

2.5.4新主庫（原備庫）置為open狀態：

SQL> alter database open;

SQL> SELECT database_role, switchover_status, open_mode, protection_mode, protection_level FROM v$database;

DATABASE_ROLE    SWITCHOVER_STATUS    OPEN_MODE            PROTECTION_MODE      PROTECTION_LEVEL

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

PRIMARY          FAILED DESTINATION   READ WRITE           MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

到此原備庫已經切換成新的主庫了，原主庫已切換成新備庫并關閉。

3.新備庫管理

3.1啟動新備庫，并置于mount狀態

由于原主庫切換時已經關閉，需要重啟實例，并置于mount狀態：

SQL> ! ps -ef | grep ora_                                      

oracle   28136     1  011:10 ?        00:00:01 ora_pmon_testdb

oracle   28138     1  011:10 ?        00:00:00 ora_vktm_testdb

oracle   28142     1  011:10 ?        00:00:00 ora_gen0_testdb

oracle   28144     1  011:10 ?        00:00:00 ora_diag_testdb

oracle   28146     1  011:10 ?        00:00:00 ora_dbrm_testdb

oracle   28148     1  011:10 ?        00:00:00 ora_psp0_testdb

oracle   28150     1  011:10 ?        00:00:01 ora_dia0_testdb

oracle   28152     1  011:10 ?        00:00:02 ora_mman_testdb

oracle   28154     1  011:10 ?        00:00:00 ora_dbw0_testdb

oracle   28156     1  011:10 ?        00:00:01 ora_lgwr_testdb

oracle   28158     1  011:10 ?        00:00:01 ora_ckpt_testdb

oracle   28160     1  011:10 ?        00:00:01 ora_smon_testdb

oracle   28162     1  011:10 ?        00:00:00 ora_reco_testdb

oracle   28164     1  011:10 ?        00:00:01 ora_mmon_testdb

oracle   28166     1  011:10 ?        00:00:00 ora_mmnl_testdb

oracle   28290     1  011:25 ?        00:00:00 ora_s001_testdb

oracle   28292     1  011:25 ?        00:00:00 ora_d000_testdb

oracle   28299 18875  0 11:29 pts/0    00:00:00 /bin/bash -c  ps -ef | grep ora_

oracle   28301 28299  0 11:29 pts/0    00:00:00 grep ora_

SQL> shutdown immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup mount;

3.2新備庫應用日志：

SQL> alter database recover managed standby database using current logfile disconnect from session;

SQL> SELECT database_role, switchover_status, open_mode, protection_mode, protection_level FROM v$database;

DATABASE_ROLE    SWITCHOVER_STATUS    OPEN_MODE            PROTECTION_MODE      PROTECTION_LEVEL

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

PHYSICAL STANDBY TO PRIMARY           MOUNTED              MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

3.3新備庫狀態置為open：

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-10456: cannotopen standby database; media recovery session may be in progress

SQL> alter database recover managed standby database cancel;

SQL> alter database open;

此處報錯是因為備庫在應用日志

3.4新備庫應用日志：

3.4.1創建standby日志組

如果不創建standby日志組，應用日志會報錯。

SQL> alter database recover managed standby database using current logfile disconnect from session;

alter database recover managed standby database using current logfile disconnect from session

*

ERROR at line 1:

ORA-38500: USING CURRENT LOGFILE option not available without standby redo logs

建立Standby log來應用來自主庫（原備庫）日志（11G新特性redo only with apply）

SQL> alter database add standby logfile thread 1 group 4 '/u01/app/oradata/testdb/stdredo04.rdo' size 50m;

SQL> alter database add standby logfile thread 1 group 5 '/u01/app/oradata/testdb/stdredo05.rdo' size 50m;

SQL> alter database add standby logfile thread 1 group 6 '/u01/app/oradata/testdb/stdredo06.rdo' size 50m;

SQL> alter database add standby logfile thread 1 group 7 '/u01/app/oradata/testdb/stdredo07.rdo' size 50m;

SQL> column member format a50;

SQL> SELECT * FROM v$logfile order by group#;

    GROUP# STATUS  TYPE    MEMBER                                        IS_

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

         1         ONLINE  /u01/app/oradata/testdb/redo01.rdo            NO

         2         ONLINE  /u01/app/oradata/testdb/redo02.rdo            NO

         3         ONLINE  /u01/app/oradata/testdb/redo03.rdo            NO

         4         STANDBY /u01/app/oradata/testdb/stdredo04.rdo         NO

         5         STANDBY /u01/app/oradata/testdb/stdredo05.rdo         NO

         6         STANDBY /u01/app/oradata/testdb/stdredo06.rdo         NO

         7         STANDBY /u01/app/oradata/testdb/stdredo07.rdo         NO

3.4.2新備庫應用日志

SQL> alter database recover managed standby database using current logfile disconnect from session;

SQL> SELECT database_role, switchover_status, open_mode, protection_mode, protection_level FROM v$database;

DATABASE_ROLE    SWITCHOVER_STATUS    OPEN_MODE            PROTECTION_MODE      PROTECTION_LEVEL

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

PHYSICAL STANDBY NOT ALLOWED          READ ONLY WITH APPLY MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

4狀態檢查及數據驗證

4.1新主庫檢查

當新備庫（原主庫）啟動正常時，新主庫狀態就正常了。

4.1.1檢查新主庫是否有GAP

SQL> SELECT database_role, switchover_status, open_mode, protection_mode, protection_level FROM v$database;

DATABASE_ROLE    SWITCHOVER_STATUS    OPEN_MODE            PROTECTION_MODE      PROTECTION_LEVEL

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

PRIMARY          RESOLVABLE GAP       READ WRITE           MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

SQL> alter system switch logfile;

SQL> SELECT database_role, switchover_status, open_mode, protection_mode, protection_level FROM v$database;

DATABASE_ROLE    SWITCHOVER_STATUS    OPEN_MODE            PROTECTION_MODE      PROTECTION_LEVEL

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

PRIMARY          TOSTANDBY            READ WRITE           MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

4.2數據驗證

4.2.1新主庫建表，并插入數據

SQL> SELECT username, account_status FROM dba_users WHERE username='HR';

USERNAME                       ACCOUNT_STATUS

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

HR                             OPEN

SQL> create table hr.table2(id int, name varchar2(20));

SQL> insert into hr.table2 values (01, 'Active DataGuard');

1 row created.

SQL> SELECT * FROM hr.table2;

ID         NAME

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

         1 Active DataGuard

SQL> commit;

4.2.2新備庫（原主庫）驗證

SQL> SELECT * FROM hr.table2;

ID         NAME

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

         1 Active DataGuard