Oracle資料庫主從切換

Oracle主從切換​

1. 主從檢視打開狀态：[root@odb1 ~]# su - oracle

   export NLS_LANG="AMERICAN_AMERICA.ZHS16GBK"

   [oracle@odb1 ~]$ source .bash_profile

   [oracle@odb1 ~]$ sqlplus / as sysdba

   SQL> select open_mode from v$database;

2. 校驗主資料庫執行切換的前提條件：主庫執行：

   ALTER SYSTEM ARCHIVE LOG CURRENT;

   ALTER SYSTEM ARCHIVE LOG CURRENT;

   主備庫檢視日志狀态：

   SQL> select THREAD#,sequence#,FIRST_TIME, NEXT_TIME from v$archived_log where DEST_ID=1 order by FIRST_TIME;

   備庫檢視日志狀态：

   SQL> Set linesize 200

   SQL> select THREAD#,sequence#,FIRST_TIME, NEXT_TIME from v$archived_log where DEST_ID=1 order by FIRST_TIME;

核對主備輸出結果，關閉主庫的一個RAC執行個體​

1. 在主庫上執行：（在此之前關閉是以資料庫連接配接）SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

   期望輸出結果為：TO STANDBY 或 SESSIONS ACTIVE

說明：如果該列值為"TO STANDBY"則表示primary 資料庫支援轉換為standby 角色，否則的話你就需要重新檢查一下Data Guard 配置，比如看看LOG_ARCHIVE_DEST_n 之類參數值是否正确有效等等​

1. 在備庫執行：SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

   期望輸出結果：NOT ALLOWED

2. 在主庫上發起切換：首先将PRIMARY轉換為standby 的角色SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;

說明：PRIMARY進行轉換完畢後，檢視狀态會變成RECOVERY NEEDED;​

1. 關閉主庫執行個體并重新開機到SQL> shutdown abort;

   SQL> startup nomount;

   SQL> alter database mount standby database;

2. 主備庫檢視狀态資訊SQL> select database_role,switchover_status from v$database;
3. 備庫檢視切換狀态：期望結果為：TO PRIMARYSQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
4. 備庫切換資料庫角色到主角色，将standby 切換到 primary 角色​SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;​

5. 備庫檢視日志​[oracle@odb2 trace]$ cd /u01/app/oracle/diag/rdbms/odb2/orcl/trace​

   [oracle@odb2 trace]$ tail -f alert_orcl.log -n 100​

6. 主備檢視日志情況SQL> show parameter log_archive_dest_1;

SQL> show parameter log_archive_dest_2;​

1. 主備庫檢視連接配接的名稱：SQL> show parameter fal;

   alter system set fal_server=’odb1’

alter system set fal_client=’odb2’​

1. 備庫打開，确認備庫接收日志SQL> alter database open;

   SQL> ALTER SYSTEM SET log_archive_dest_2='SERVICE=odb1 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=odb1';

   SQL> alter system switch logfile;

2. 原主庫啟動恢複模式，并啟動追加資料：SQL> alter database open;

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

附：取消日志應用SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; ​

在open狀态下執行日志即使運用SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;​

1. 現在的主資料庫啟動​SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;​
2. 确認資料庫日志應用到備資料庫​

現主庫​

SQL> select database_role,switchover_status from v$database;​

現備庫​

SQL> select database_role,switchover_status from v$database;​

附加主從切換驗證：​

主從庫狀态檢視：現主庫為：READ WRITE/ 現從庫為：READ ONLY WITH APPLY​

SQL> select open_mode from v$database;​

建立測試使用者測試：​

SQL> create user aa22 identified by 123456;​

原主庫因為改為備庫而無法寫入資料​

SQL> select * from all_users;​

主備同步狀态檢視：新主庫略大于備庫，并處于實時更新狀态​

SQL> col current_scn for 9999999999999999​

SQL> select scn_to_timestamp(current_scn),current_scn from v$database;​

第3台切換主庫​

cd $ORACLE_BASE​

cd diag/rdbms/orcl/trace​

less alert_oracle.log​

lqlplus / as sysdba​

show parameter fal;​

show parameter db_uni​