dataguard主備switchover互切實驗及理解

SWITCHOVER是人為計劃的互相切換主備，FAILOVER是主庫故障的情況下，把備庫切換為主庫



SWITHOVER大致步驟就是下面幾步（主庫先切換為standby，備庫再切換為primary）
主庫
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
SQL> startup mount;
備庫
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
SQL> alter database open
現備庫原主庫
SQL> alter database recover managed standby database disconnect from session;
SQL>alter database recover managed standby database cancel;
SQL>alter database open read only;
SQL>alter database recover managed standby database disconnect from session;






實驗環境介紹
主庫（已經DBCA建立了DB）
192.168.128.37
Red Hat Enterprise Linux Server release 6.3 (Santiago)
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
Service/service_names/db_name/db_unique_name/instance_name=TDB


備庫（沒有建立DB，只安裝了ORACLE軟體，和主庫目錄結構一致）
192.168.128.47
Red Hat Enterprise Linux Server release 6.3(Santiago)
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
Service/service_names/db_unique_name=TDG
db_name/instance_name=TDB


主庫備庫的tns都如下
master =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.128.37)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TDB)
    )
  )


slave =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.128.47)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TDG)
    )
  )


DATAGUARD搭建實驗步驟
1.主庫確保處於ARCHIVELOG、force logging模式
2.主庫線上修改一下幾個引數，並create pfile from spfile，主庫把剛建立的pfile（$ORACLE_HOME/dbs/initTDB.ora）和
密碼檔案（$ORACLE_HOME/dbs/orapwTDB）複製到備庫的$ORACLE_HOME/dbs目錄
fal_client='master'
fal_server='slave'
standby_file_management='AUTO'
log_archive_config='DG_CONFIG=(TDB,TDG)'
remote_login_passwordfile='EXCLUSIVE'
log_archive_dest_1='location=/u01/app/oracle/archivelog
log_archive_dest_2='service=slave lgwr async VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=TDG'
3.主庫備份資料檔案和當前控制檔案
rman>backup database format '/u01/backup/full_%U.bak';
rman>backup current controlfile for standby format '/u01/backup/control_%U.bak';
4.主庫把剛生成的備份複製到備庫/u01/backup/目錄
5.備庫修改從主庫複製過來的pfile（$ORACLE_HOME/dbs/initTDB.ora）檔案
增加*.db_unique_name='TDG'
修改如下三個引數為
*.fal_client='slave'
*.fal_server='master'
*.log_archive_dest_2='service=master lgwr async VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=TDB'
6.備庫順序執行如下
ORACLE_SID=TDB
sqlplus / as sysdba
SQL>startup nomount
SQL>exit
rman target /
RMAN>restore standby controlfile from '/u01/backup/control_%U.bak';
RMAN>alter database mount
RMAN>restore database;
RMAN>exit;
sqlplus / as sysdba
SQL>alter database recover managed standby database disconnect from session;
SQL>select sequence#,applied from v$archived_log;
（主庫執行alter system archive log current後，執行上面這個語句看主庫的日誌是否已經在備庫應用了，應用了的話再執行如下語句）
SQL>alter database recover managed standby database cancel;
SQL>alter database open read only;
SQL>alter database recover managed standby database disconnect from session;







SWITHOVER切換實驗過程

1.檢視主備庫都正常的情況下，如下語句的結果
主SQL> select database_role, switchover_status from v$database;
DATABASE_ROLE    SWITCHOVER_STATUS
---------------- --------------------
PRIMARY          SESSIONS ACTIVE

備SQL> select database_role, switchover_status from v$database;
DATABASE_ROLE    SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY NOT ALLOWED


2.主庫執行
上面第1步知道主庫的SWITCHOVER_STATUS是SESSIONS ACTIVE，執行如下
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
官方文件記錄：A value of TO STANDBY or SESSIONS ACTIVE indicates that the primary database can be switched to the standby role.


3.再執行第1步的兩個語句看什麼情況
主SQL> select database_role, switchover_status from v$database;
select database_role, switchover_status from v$database
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 13039
Session ID: 36 Serial number: 7505

備SQL> select database_role, switchover_status from v$database;
DATABASE_ROLE    SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY TO PRIMARY


4. 主庫啟動資料庫：
SQL> startup mount;


5.再執行第1步的兩個語句看什麼情況
主SQL> select database_role, switchover_status from v$database;
DATABASE_ROLE    SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY RECOVERY NEEDED

備SQL> select database_role, switchover_status from v$database;
DATABASE_ROLE    SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY TO PRIMARY


6.備庫執行
上面第5步知道備庫的SWITCHOVER_STATUS是TO PRIMARY，執行如下
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
官方文件記錄：A value of TO PRIMARY or SESSIONS ACTIVE indicates that the standby database is ready to be switched to the primary role


7.再執行第1步的兩個語句看什麼情況
主SQL> select database_role, switchover_status from v$database;
DATABASE_ROLE    SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY RECOVERY NEEDED

備SQL> select database_role, switchover_status from v$database;
DATABASE_ROLE    SWITCHOVER_STATUS
---------------- --------------------
PRIMARY          NOT ALLOWED
 

8.備庫開啟到open狀態（上面第6步執行後備庫的v$database.open_mode會從read only狀態到mounted狀態）
SQL> alter database open


9.再執行第1步的兩個語句看什麼情況
原主SQL> select database_role, switchover_status from v$database;
DATABASE_ROLE    SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY RECOVERY NEEDED

原備SQL> select database_role, switchover_status from v$database;
DATABASE_ROLE    SWITCHOVER_STATUS
---------------- --------------------
PRIMARY          TO STANDBY|SESSIONS ACTIVE(在兩者之間切來切去)


10.原主庫即目前備庫，執行
SQL> alter database recover managed standby database disconnect from session;
SQL>alter database recover managed standby database cancel;
SQL>alter database open read only;
SQL>alter database recover managed standby database disconnect from session;


11.再執行第1步的兩個語句看什麼情況
現備，原主
SQL> select database_role, switchover_status from v$database;
DATABASE_ROLE    SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY          NOT ALLOWED


現主，原備
SQL> select database_role, switchover_status from v$database;
DATABASE_ROLE    SWITCHOVER_STATUS
---------------- --------------------
PRIMARY               TO STANDBY|SESSIONS ACTIVE(在兩者之間切來切去)