自己oracle 實驗筆記(physiaclstandby的程式和view,轉換)
主要研究幾個管理physicalstandby的程式檢視
v$managed_standby
此view 對 physical standby 與logical standby 很 重要
主要是監控~重做的應用，及重做的傳輸服務活動
SQL> desc v$managed_standby;
名稱 是否為空? 型別
----------------------------------------- -------- ----------------------------
PROCESS VARCHAR2(9)
PID NUMBER
STATUS VARCHAR2(12)
CLIENT_PROCESS VARCHAR2(8)
CLIENT_PID VARCHAR2(40)
CLIENT_DBID VARCHAR2(40)
GROUP# VARCHAR2(40)
RESETLOG_ID NUMBER
THREAD# NUMBER
SEQUENCE# NUMBER
BLOCK# NUMBER
BLOCKS NUMBER
DELAY_MINS NUMBER
KNOWN_AGENTS NUMBER
ACTIVE_AGENTS NUMBER
SQL> select process,client_process from v$managed_standby;
PROCESS CLIENT_P
--------- --------
ARCH ARCH
ARCH ARCH
RFS UNKNOWN
RFS UNKNOWN
MRP0 N/A
rfs對應的 client_process 為unknown表示用的arch (log_archive_dest_n) lgwr，表示用的LGRW
SQL> show parameter log_archive_dest_2;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string service=xhtest valid_for=(onli
ne_logfiles,primary_role) db_u
nique_name=xhtest 現在的 為arch
改下
*.log_archive_dest_2='service=standby1 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=standby1' 主庫的
SQL> select process,client_process from v$managed_standby;
PROCESS CLIENT_P
--------- --------
ARCH ARCH
ARCH ARCH
RFS LGWR 顯示出來了
RFS UNKNOWN
MRP0 N/A
SQL> select process,status from v$managed_standby;
PROCESS STATUS
--------- ------------
ARCH CONNECTED
ARCH CONNECTED
RFS RECEIVING
RFS RECEIVING
一些重要的程式
RFS:
當開啟動physical standby redo應用
SQL> alter database recover managed standby database disconnect from session;
資料庫已更改。
SQL> select process,status from v$managed_standby;
PROCESS STATUS
--------- ------------
ARCH CONNECTED
ARCH CONNECTED
RFS RECEIVING
RFS RECEIVING
MRP0 WAIT_FOR_LOG 多了一個MRP0程式
SQL>
幾個重要檢視
SQL> desc v$archive_dest
名稱 是否為空? 型別
----------------------------------------- -------- ---------------------------
DEST_ID NUMBER
DEST_NAME VARCHAR2(256)
STATUS VARCHAR2(9)
BINDING VARCHAR2(9)
NAME_SPACE VARCHAR2(7)
TARGET VARCHAR2(7)
ARCHIVER VARCHAR2(10)
SCHEDULE VARCHAR2(8)
DESTINATION VARCHAR2(256)
LOG_SEQUENCE NUMBER
REOPEN_SECS NUMBER
DELAY_MINS NUMBER
NET_TIMEOUT NUMBER
PROCESS VARCHAR2(10)
REGISTER VARCHAR2(3)
FAIL_DATE DATE
FAIL_SEQUENCE NUMBER
FAIL_BLOCK NUMBER
FAILURE_COUNT NUMBER
MAX_FAILURE NUMBER
ERROR VARCHAR2(256)
ALTERNATE VARCHAR2(256)
DEPENDENCY VARCHAR2(256)
REMOTE_TEMPLATE VARCHAR2(256)
QUOTA_SIZE NUMBER
QUOTA_USED NUMBER
MOUNTID NUMBER
TRANSMIT_MODE VARCHAR2(12)
ASYNC_BLOCKS NUMBER
AFFIRM VARCHAR2(3)
TYPE VARCHAR2(7)
VALID_NOW VARCHAR2(16)
VALID_TYPE VARCHAR2(15)
VALID_ROLE VARCHAR2(12)
DB_UNIQUE_NAME VARCHAR2(30)
VERIFY VARCHAR2(3)
EXPEDITE VARCHAR2(3)
SQL>
v$archive_dest_status
SQL> desc v$archive_dest_status;
名稱 是否為空? 型別
----------------------------------------- -------- ----------------------------
DEST_ID NUMBER
DEST_NAME VARCHAR2(256)
STATUS VARCHAR2(9)
TYPE VARCHAR2(14)
DATABASE_MODE VARCHAR2(15)
RECOVERY_MODE VARCHAR2(23)
PROTECTION_MODE VARCHAR2(20)
DESTINATION VARCHAR2(256)
STANDBY_LOGFILE_COUNT NUMBER
STANDBY_LOGFILE_ACTIVE NUMBER
ARCHIVED_THREAD# NUMBER
ARCHIVED_SEQ# NUMBER
APPLIED_THREAD# NUMBER
APPLIED_SEQ# NUMBER
ERROR VARCHAR2(256)
SRL VARCHAR2(3)
DB_UNIQUE_NAME VARCHAR2(30)
檢視一些重要資訊
例在standby上 查詢
SQL> select dest_name,status,database_mode,recovery_mode from v$archive_dest_sta
tus;
DEST_NAME STATUS DATABASE_MODE RECOVERY_MODE
-------------------- --------- --------------- -----------------------
LOG_ARCHIVE_DEST_1 VALID MOUNTED-STANDBY MANAGED REAL TIME APPLY~~~~~~~~~~~~~~~有效的路徑
LOG_ARCHIVE_DEST_2 VALID MOUNTED-STANDBY MANAGED REAL TIME APPLY~~~~~~~~~~~~~~~有效的路徑
LOG_ARCHIVE_DEST_3 INACTIVE MOUNTED-STANDBY MANAGED REAL TIME APPLY
LOG_ARCHIVE_DEST_4 INACTIVE MOUNTED-STANDBY MANAGED REAL TIME APPLY
LOG_ARCHIVE_DEST_5 INACTIVE MOUNTED-STANDBY MANAGED REAL TIME APPLY
LOG_ARCHIVE_DEST_6 INACTIVE MOUNTED-STANDBY MANAGED REAL TIME APPLY
LOG_ARCHIVE_DEST_7 INACTIVE MOUNTED-STANDBY MANAGED REAL TIME APPLY
LOG_ARCHIVE_DEST_8 INACTIVE MOUNTED-STANDBY MANAGED REAL TIME APPLY
LOG_ARCHIVE_DEST_9 INACTIVE MOUNTED-STANDBY MANAGED REAL TIME APPLY
LOG_ARCHIVE_DEST_10 INACTIVE MOUNTED-STANDBY MANAGED REAL TIME APPLY
STANDBY_ARCHIVE_DEST VALID MOUNTED-STANDBY MANAGED REAL TIME APPLY~~~~~~~~~~~~~~~有效的路徑
裡面重要的資訊包括 recovery_mode 恢復的模式
MANAGED REAL TIME APPLY 是因為我們開了 實時應用
SQL> alter database recover managed standby database cancel; 關閉
資料庫已更改。
SQL> select dest_name,status,database_mode,recovery_mode from v$archive_dest_sta
tus;
DEST_NAME STATUS DATABASE_MODE RECOVERY_MODE
-------------------- --------- --------------- -----------------------
LOG_ARCHIVE_DEST_1 VALID MOUNTED-STANDBY IDLE
LOG_ARCHIVE_DEST_2 VALID MOUNTED-STANDBY IDLE
LOG_ARCHIVE_DEST_3 INACTIVE MOUNTED-STANDBY IDLE
LOG_ARCHIVE_DEST_4 INACTIVE MOUNTED-STANDBY IDLE
LOG_ARCHIVE_DEST_5 INACTIVE MOUNTED-STANDBY IDLE 模式為空閒
LOG_ARCHIVE_DEST_6 INACTIVE MOUNTED-STANDBY IDLE
LOG_ARCHIVE_DEST_7 INACTIVE MOUNTED-STANDBY IDLE
LOG_ARCHIVE_DEST_8 INACTIVE MOUNTED-STANDBY IDLE
LOG_ARCHIVE_DEST_9 INACTIVE MOUNTED-STANDBY IDLE
LOG_ARCHIVE_DEST_10 INACTIVE MOUNTED-STANDBY IDLE
STANDBY_ARCHIVE_DEST VALID MOUNTED-STANDBY IDLE
已選擇11行。
SQL> alter database recover managed standby database disconnect from session; 開啟應用
資料庫已更改。
SQL> select dest_name,status,database_mode,recovery_mode from v$archive_dest_sta
tus;
DEST_NAME STATUS DATABASE_MODE RECOVERY_MODE~~~~~~~~~~~~~~~~~~~~檢視恢復方式
-------------------- --------- --------------- -----------------------
LOG_ARCHIVE_DEST_1 VALID MOUNTED-STANDBY MANAGED
LOG_ARCHIVE_DEST_2 VALID MOUNTED-STANDBY MANAGED
LOG_ARCHIVE_DEST_3 INACTIVE MOUNTED-STANDBY MANAGED
LOG_ARCHIVE_DEST_4 INACTIVE MOUNTED-STANDBY MANAGED 恢復為管理模式 就是應用standby歸檔日誌
LOG_ARCHIVE_DEST_5 INACTIVE MOUNTED-STANDBY MANAGED
LOG_ARCHIVE_DEST_6 INACTIVE MOUNTED-STANDBY MANAGED
LOG_ARCHIVE_DEST_7 INACTIVE MOUNTED-STANDBY MANAGED
LOG_ARCHIVE_DEST_8 INACTIVE MOUNTED-STANDBY MANAGED
LOG_ARCHIVE_DEST_9 INACTIVE MOUNTED-STANDBY MANAGED
LOG_ARCHIVE_DEST_10 INACTIVE MOUNTED-STANDBY MANAGED
STANDBY_ARCHIVE_DEST VALID MOUNTED-STANDBY MANAGED
已選擇11行。
SQL>
SQL> select protection_mode from v$archive_dest_status; 查詢standby 的保護模式 預設是最高效能
PROTECTION_MODE
--------------------
MAXIMUM PERFORMANCE
MAXIMUM PERFORMANCE
MAXIMUM PERFORMANCE
MAXIMUM PERFORMANCE
MAXIMUM PERFORMANCE
MAXIMUM PERFORMANCE
MAXIMUM PERFORMANCE
MAXIMUM PERFORMANCE
MAXIMUM PERFORMANCE
MAXIMUM PERFORMANCE
MAXIMUM PERFORMANCE
已選擇11行。
SQL> select protection_mode,applied_seq# from v$archive_dest_status;~~~~~~~~~~~~~~~~~~~檢視恢復方式
PROTECTION_MODE APPLIED_SEQ#
-------------------- ------------
MAXIMUM PERFORMANCE 0
MAXIMUM PERFORMANCE 0
MAXIMUM PERFORMANCE 0
MAXIMUM PERFORMANCE 0
MAXIMUM PERFORMANCE 0
MAXIMUM PERFORMANCE 0
MAXIMUM PERFORMANCE 0
MAXIMUM PERFORMANCE 0
MAXIMUM PERFORMANCE 0
MAXIMUM PERFORMANCE 0
MAXIMUM PERFORMANCE 280 應用到的 日誌序號
已選擇11行。
SQL> alter database recover managed standby database using current logfile disco
nnect from session;
alter database recover managed standby database using current logfile disconnect 使用實時應用前 ，要關閉 歸檔日誌的應用
from session
*
第 1 行出現錯誤:
ORA-01153: 啟用了不相容的介質恢復
SQL> alter database recover managed standby database cancel; 關閉
資料庫已更改。
SQL> alter database recover managed standby database using current logfile disco
nnect from session;
資料庫已更改。
SQL> select protection_mode,applied_seq# from v$archive_dest_status;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PROTECTION_MODE APPLIED_SEQ#
-------------------- ------------
MAXIMUM PERFORMANCE 0
MAXIMUM PERFORMANCE 0
MAXIMUM PERFORMANCE 0
MAXIMUM PERFORMANCE 0
MAXIMUM PERFORMANCE 0
MAXIMUM PERFORMANCE 0
MAXIMUM PERFORMANCE 0
MAXIMUM PERFORMANCE 0
MAXIMUM PERFORMANCE 0
MAXIMUM PERFORMANCE 0
MAXIMUM PERFORMANCE 280
已選擇11行。
切換時實驗
SQL> show parameter log_archive_dest_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string service=standby1 lgwr sync val
id_for=(online_logfiles,primar 實驗環境
y_role) db_unique_name=standby
1
v$standby_log
SQL> select sequence#,status from v$log;(primary)
SEQUENCE# STATUS
---------- ----------------
285 CURRENT
283 INACTIVE
284 INACTIVE
SQL>
SQL> select group#,sequence#,status,first_change#,last_change#,archived from v$s (standby)
tandby_log;
GROUP# SEQUENCE# STATUS FIRST_CHANGE# LAST_CHANGE# ARC 2組standbylogfile
---------- ---------- ---------- ------------- ------------ ---
4 285 ACTIVE 1492038 1492982 YES
5 0 UNASSIGNED 0 0 YES
SQL> select current_scn from v$database;
CURRENT_SCN
----------- 主庫當前SCN(primary)
1492998
我們可以看到 當前SCN 未寫進logfile(primary) 所以未在 standbylogfile中
SQL> commit; 提交寫進primary online logfile
提交完成。
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1493165~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL> select group#,sequence#,status,first_change#,last_change#,archived from v$s
tandby_log;
GROUP# SEQUENCE# STATUS FIRST_CHANGE# LAST_CHANGE# ARC
---------- ---------- ---------- ------------- ------------ ---
4 285 ACTIVE 1492038 1493230 YES~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~包含進來了 並且包含了最新的SCN 這個SCN雖然不是我們強制commit提交
5 0 UNASSIGNED 0 0 YES 的但是lgwr的啟動制度是 每3秒自動觸發一次
例
SQL> select group#,sequence#,status,first_change#,last_change#,archived from v$s
tandby_log;
GROUP# SEQUENCE# STATUS FIRST_CHANGE# LAST_CHANGE# ARC
---------- ---------- ---------- ------------- ------------ ---
4 285 ACTIVE 1492038 1493755 YES
5 0 UNASSIGNED 0 0 YES
SQL> select group#,sequence#,status,first_change#,last_change#,archived from v$s
tandby_log;
GROUP# SEQUENCE# STATUS FIRST_CHANGE# LAST_CHANGE# ARC
---------- ---------- ---------- ------------- ------------ ---
4 285 ACTIVE 1492038 1493755 YES
5 0 UNASSIGNED 0 0 YES
SQL> select group#,sequence#,status,first_change#,last_change#,archived from v$s
tandby_log;
GROUP# SEQUENCE# STATUS FIRST_CHANGE# LAST_CHANGE# ARC
---------- ---------- ---------- ------------- ------------ ---
4 285 ACTIVE 1492038 1493757 YES
5 0 UNASSIGNED 0 0 YES
主庫沒做任何操作，大概過了3秒中，主庫自動觸發LGWR 同時由LSNN給 STANDBY RFS 作用在standbylogfile中
網路延遲之類可能晚幾秒
主庫每3秒中自動觸發LGWR
再查 standby logfile 發現 last_change#列已經包含了剛才最新的SCN，而且是現在最新的SCN 這樣 就是跟prmary的 onlinelogfile是同步的（LGWR SYNC）
LGWR ASYNC
SQL> alter system set log_archive_dest_2='service=standby1 lgwr async valid_for=
(online_logfiles,primary_role) db_unique_name=standby1';
系統已更改。
主庫沒做任何操作
SQL> select group#,sequence#,status,first_change#,last_change#,archived from v$s
tandby_log;
GROUP# SEQUENCE# STATUS FIRST_CHANGE# LAST_CHANGE# ARC
---------- ---------- ---------- ------------- ------------ ---
4 285 ACTIVE 1492038 1493899 YES
5 0 UNASSIGNED 0 0 YES
SQL> select group#,sequence#,status,first_change#,last_change#,archived from v$s
tandby_log;
GROUP# SEQUENCE# STATUS FIRST_CHANGE# LAST_CHANGE# ARC
---------- ---------- ---------- ------------- ------------ ---
4 285 ACTIVE 1492038 1493899 YES
5 0 UNASSIGNED 0 0 YES
SQL> select group#,sequence#,status,first_change#,last_change#,archived from v$s
tandby_log;
GROUP# SEQUENCE# STATUS FIRST_CHANGE# LAST_CHANGE# ARC
---------- ---------- ---------- ------------- ------------ ---
4 285 ACTIVE 1492038 1493899 YES
5 0 UNASSIGNED 0 0 YES
SQL> select group#,sequence#,status,first_change#,last_change#,archived from v$s
tandby_log;
GROUP# SEQUENCE# STATUS FIRST_CHANGE# LAST_CHANGE# ARC
---------- ---------- ---------- ------------- ------------ ---
4 285 ACTIVE 1492038 1493901 YES
5 0 UNASSIGNED 0 0 YES
大概6秒的時間，中間的網路問題會有影響
也沒有任何變化與lgwr sync
作用到 STANDBYLOGFILE的速度可能稍微比lgwr sync慢了幾秒，由於先寫進primary onlinelogfile ,然後由lsnn 給standby rfs作用在standbylogfile中
實時傳輸速度
主庫3秒種自動LGWR 由於要傳到STANDBY 到STANDBY時間可能會>3秒
要是async可能會更加多少幾秒 因為primary lgwr要先寫到 onlinelogfile中，由onlinelogfile 發給lsnn 不象sync一樣 lgwr寫onlinelogfile 同時 也發給了lsnn
SQL> alter system switch logfile;
系統已更改。
SQL> alter system switch logfile;
系統已更改。
SQL> alter system switch logfile;
系統已更改。
SQL> select group#,sequence#,status,first_change#,last_change#,archived from v$s
tandby_log;
GROUP# SEQUENCE# STATUS FIRST_CHANGE# LAST_CHANGE# ARC
---------- ---------- ---------- ------------- ------------ ---
4 286 ACTIVE 1494114 1494114 YES
5 0 UNASSIGNED 0 0 YES
SQL> select group#,sequence#,status,first_change#,last_change#,archived from v$s
tandby_log;
GROUP# SEQUENCE# STATUS FIRST_CHANGE# LAST_CHANGE# ARC
---------- ---------- ---------- ------------- ------------ ---
4 0 UNASSIGNED 0 0 NO
5 287 ACTIVE 1494118 1494114 YES
SQL> select group#,sequence#,status,first_change#,last_change#,archived from v$s
tandby_log;
GROUP# SEQUENCE# STATUS FIRST_CHANGE# LAST_CHANGE# ARC
---------- ---------- ---------- ------------- ------------ ---
4 288 ACTIVE 1494125 1494123 YES
5 0 UNASSIGNED 0 0 NO
當主庫交換時候可以看出他們在交替使用 每次只有一個似乎active 狀態
檢視v$archived_log
SQL> desc v$archived_log;
名稱 是否為空? 型別
----------------------------------------- -------- ----------------------------
RECID NUMBER
STAMP NUMBER
NAME VARCHAR2(513)
DEST_ID NUMBER
THREAD# NUMBER
SEQUENCE# NUMBER
RESETLOGS_CHANGE# NUMBER
RESETLOGS_TIME DATE
RESETLOGS_ID NUMBER
FIRST_CHANGE# NUMBER
FIRST_TIME DATE
NEXT_CHANGE# NUMBER
NEXT_TIME DATE
BLOCKS NUMBER
BLOCK_SIZE NUMBER
CREATOR VARCHAR2(7)
REGISTRAR VARCHAR2(7)
STANDBY_DEST VARCHAR2(3)
ARCHIVED VARCHAR2(3)
APPLIED VARCHAR2(3)
DELETED VARCHAR2(3)
STATUS VARCHAR2(1)
COMPLETION_TIME DATE
DICTIONARY_BEGIN VARCHAR2(3)
DICTIONARY_END VARCHAR2(3)
END_OF_REDO VARCHAR2(3)
BACKUP_COUNT NUMBER
ARCHIVAL_THREAD# NUMBER
ACTIVATION# NUMBER
IS_RECOVERY_DEST_FILE VARCHAR2(3)
COMPRESSED VARCHAR2(3)
FAL VARCHAR2(3)
SQL> select name,creator,applied from v$archived_log;
NAME CREATOR APP
-------------------------------------------------- ------- ---
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00467_0661538341.00 ARCH NO
1
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00468_0661538341.00 ARCH NO
1
D:\STANDBYPHYSICAL\ARCHIVE\ARC00469_0661538341.001 LGWR NO
已選擇331行。
name 是歸檔過來的檔名稱路徑
applied是否應用~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~`creator比較重要
creator 分好幾種
arch: 當執行alter system swtich logfile 時候 表示用後臺程式arcn來出來~~~~~~~~~~~~~~~~自動也是arcn
lgwr：是配置了 lgwr由primary 傳輸過來的 (STAnDBy)
FGRD:前臺執行 alter system archive log current
RMAN：rman中執行sql'alter system switch logfile '，catalog archivelog '/data/dg/arch/1_59.arc' 。
SRMAN：在standby上catalog archivelog '/data/dg/arch/1_59.arc' 。 產生的是srman
~~~~~~~~~~~一個關於v$archived_log補充，查詢看看那些日誌沒有傳到standby
SQL> SELECT LOCAL.THREAD#, LOCAL.SEQUENCE# FROM
2> (SELECT THREAD#, SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=1)~~~~~~~~~~~~~~~~~~~~~~~~~~~~~`在主庫上查
LOCAL
3> WHERE LOCAL.SEQUENCE# NOT IN
5> (SELECT SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND
6> THREAD# = LOCAL.THREAD#);
SQL> desc v$database;
名稱 是否為空? 型別
----------------------------------------- -------- ----------------------------
DBID NUMBER
NAME VARCHAR2(9)
CREATED DATE
RESETLOGS_CHANGE# NUMBER
RESETLOGS_TIME DATE
PRIOR_RESETLOGS_CHANGE# NUMBER
PRIOR_RESETLOGS_TIME DATE
LOG_MODE VARCHAR2(12)
CHECKPOINT_CHANGE# NUMBER
ARCHIVE_CHANGE# NUMBER
CONTROLFILE_TYPE VARCHAR2(7)
CONTROLFILE_CREATED DATE
CONTROLFILE_SEQUENCE# NUMBER
CONTROLFILE_CHANGE# NUMBER
CONTROLFILE_TIME DATE
OPEN_RESETLOGS VARCHAR2(11)
VERSION_TIME DATE
OPEN_MODE VARCHAR2(10)
PROTECTION_MODE VARCHAR2(20)
PROTECTION_LEVEL VARCHAR2(20)
REMOTE_ARCHIVE VARCHAR2(8)
ACTIVATION# NUMBER
SWITCHOVER# NUMBER
DATABASE_ROLE VARCHAR2(16)
ARCHIVELOG_CHANGE# NUMBER
ARCHIVELOG_COMPRESSION VARCHAR2(8)
SWITCHOVER_STATUS VARCHAR2(20)
DATAGUARD_BROKER VARCHAR2(8)
GUARD_STATUS VARCHAR2(7)
SUPPLEMENTAL_LOG_DATA_MIN VARCHAR2(8)
SUPPLEMENTAL_LOG_DATA_PK VARCHAR2(3)
SUPPLEMENTAL_LOG_DATA_UI VARCHAR2(3)
FORCE_LOGGING VARCHAR2(3)
PLATFORM_ID NUMBER
PLATFORM_NAME VARCHAR2(101)
RECOVERY_TARGET_INCARNATION# NUMBER
LAST_OPEN_INCARNATION# NUMBER
CURRENT_SCN NUMBER
FLASHBACK_ON VARCHAR2(3)
SUPPLEMENTAL_LOG_DATA_FK VARCHAR2(3)
SUPPLEMENTAL_LOG_DATA_ALL VARCHAR2(3)
DB_UNIQUE_NAME VARCHAR2(30)
STANDBY_BECAME_PRIMARY_SCN NUMBER
SQL> select protection_mode,protection_level,switchover_status from v$database;~~~~~~~~~~~~~
PROTECTION_MODE PROTECTION_LEVEL SWITCHOVER_STATUS
-------------------- -------------------- --------------------
MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE SESSIONS ACTIVE
檢視了primary的 模式,primary能否轉為standby swtichover_status session active表示可以，但要加上WITH SESSION SHUTDOWN
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY witch session shutdown ;
SQL> select protection_mode,protection_level,switchover_status from v$database;(standby上查)
PROTECTION_MODE PROTECTION_LEVEL SWITCHOVER_STATUS
-------------------- -------------------- --------------------
MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE NOT ALLOWED~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~是否跟應用沒應用完有關係
standby顯示為不允許
SQL> select name,creator,applied from v$archived_log;
NAME CREATOR APP
-------------------------------------------------- ------- ---
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00467_0661538341.00 ARCH NO
1
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00468_0661538341.00 ARCH NO
1
D:\STANDBYPHYSICAL\ARCHIVE\ARC00469_0661538341.001 LGWR NO
已選擇331行。
SQL> alter database recover managed standby database disconnect from session;
資料庫已更改。
SQL> select name,creator,applied from v$archived_log;
NAME CREATOR APP
-------------------------------------------------- ------- ---
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00467_0661538341.00 ARCH YES
1
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00468_0661538341.00 ARCH YES
1~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~都應用完了
D:\STANDBYPHYSICAL\ARCHIVE\ARC00469_0661538341.001 LGWR YES
已選擇331行。
SQL> select protection_mode,protection_level,switchover_status from v$database;
PROTECTION_MODE PROTECTION_LEVEL SWITCHOVER_STATUS
-------------------- -------------------- --------------------
MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE NOT ALLOWED~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~還是不行
SQL> alter database commit to switchover to physical standby with session shutdo
wn ;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~主庫執行這個了~~~~~切換成standby
SQL> select protection_mode,protection_level,switchover_status from v$database;
PROTECTION_MODE PROTECTION_LEVEL SWITCHOVER_STATUS
-------------------- -------------------- --------------------
MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE TO PRIMARY~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~結論只有當pramary先切換成standby，standby的切換狀態才顯示to primary
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY; standby轉換成primary
現在都轉換完了 來驗證下結果
SQL> select db_unique_name,database_role,open_mode from v$database;
DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE
------------------------------ ---------------- ----------
xhtest PHYSICAL STANDBY READ ONLY
SQL> select db_unique_name,database_role,open_mode from v$database;
DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE
------------------------------ ---------------- ----------
standby1 PRIMARY READ WRITE
在standby1上執行alter system switch logfile
SQL> select name,creator,applied from v$archived_log;（standby1 上查）
NAME CREATOR APP
-------------------------------------------------- ------- ---
D:\STANDBYPHYSICAL\ARCHIVE1\ARC00472_0661538341.00 ARCH NO~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~已經變為主庫 歸檔到自己的本地位置
1
xhtest ARCH NO
SQL> show parameter log_archive_dest_1
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1 string location=D:\standbyphysical\ar~~~~~~~~~~~~~~~~~~~~~~自己的本地位置
chive1 valid_for=(all_logfiles
,all_roles) db_unique_name=sta
ndby1
SQL> select process,client_process,status,sequence# from v$managed_standby;（xhtest上查，現在已經是physiacal standby了）
PROCESS CLIENT_P STATUS SEQUENCE#
--------- -------- ------------ ----------
ARCH ARCH CONNECTED 0
ARCH ARCH CONNECTED 0
RFS UNKNOWN RECEIVING 0
SQL> show parameter standby_archive_dest（xhtest上查，現在已經是physiacal standby了）
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest string F:\歸檔備份
SQL>
SQL> show parameter log_archive_dest_2（standby1 也就是現在的主庫）
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string service=xhtest valid_for=(onli
ne_logfiles,primary_role) db_u
nique_name=xhtest
QL> select name,creator,applied from v$archived_log;（xhtest上，也就是現在的physical standby）
F:\歸檔備份\ARC00472_0661538341.001 ARCH NO
已選擇937行。 很清楚的看到 歸檔到了standby_archive_dest位置
oracle dataguard的三中保護模式
maximum protection最高保護模式：
要求
要求
redo寫程式：LGWR
網路傳輸:sync
,磁碟i/0 ：affirm 預設（noaffirm）,
需要standbylogfile
設定為這個後:
當主庫primary寫redo日誌的時候 必須同時寫入standby standbylogfile，若故障導致主資料庫無法寫重做流到至少一個事務一致性備資料庫的備重做日誌時，全完後事務才會提交，主資料庫會關閉。
maximum availability 最高實用性
要求：
redo寫程式：LGWR
網路傳輸 sync
磁碟i/o affirm
需要 standbylogfile
設定後:當主庫primary寫redo日誌時候，必須同時寫入standby standbylogfile ，完成後事務才可以提交，當有故障導致主庫無法寫重做流到備用庫的standby logfile時候，主庫會自動轉為‘最大效能’模式繼續執行 知道故障消除時候~自動轉為 最高可用性~~~~~，不會shutdown 主庫
Maximum performance 最大效能
要求
rodo寫程式： lgwr,arcn
網路傳輸：SYNC ，ASYNC
磁碟i/o affirm ,noaffirm
standbylogfile ，可以要，也可以不要
設定後：primary寫redo時候，事務就可以提交，寫入也可是不同步的，有故障導致無法寫入時候，不會shutdown 主庫
實驗3種模式
實驗環境
先交換過來xhtest為 primary
standby1 為 standby
SQL> select protection_mode,db_unique_name,open_mode,database_role from v$datab
ase;
PROTECTION_MODE DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE
-------------------- ------------------------------ ---------- ----------------
MAXIMUM PERFORMANCE xhtest READ WRITE PRIMARY xhtest primary
SQL> select protection_mode,db_unique_name,open_mode,database_role from v$databa
se;
PROTECTION_MODE DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE
-------------------- ------------------------------ ---------- ----------------
MAXIMUM PERFORMANCE standby1 MOUNTED PHYSICAL STANDBY
SQL> select process,client_process,status,sequence# from v$managed_standby;（standby） 現在少一個程式
PROCESS CLIENT_P STATUS SEQUENCE#
--------- -------- ------------ ----------
ARCH ARCH CONNECTED 0
ARCH ARCH CONNECTED 0
SQL> show parameter standby_archive_dest（standby）
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest string D:\STANDBYPHYSICAL\ARCHIVE
SQL> show parameter log_archive_dest_2~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(primary)
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string service=standby1 lgwr async va
lid_for=(online_logfiles,prima
ry_role) db_unique_name=standb
y1
SQL> alter system set log_archive_dest_2='service=standby1 affirm lgwr sync vali
d_for=(online_logfiles,primary_role) db_unique_name=standby1'
2 ;
系統已更改。
SQL> alter database set standby database to maximize protectio
alter database set standby database to maximize protection
*
第 1 行出現錯誤:
ORA-01126: 資料庫必須已裝載到此例程並且不在任何例程中開啟
SQL> startup force mount;
ORACLE 例程已經啟動。
Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145750508 bytes
Database Buffers 25165824 bytes
Redo Buffers 262144 bytes
資料庫裝載完畢。
SQL> alter database set standby database to maximize protection;~~~~~~~~~~~~~~~~~~~~~~~~必須在mount下 設定~~~~~~~~~~注意都是在主庫設定
資料庫已更改。
SQL> select protection_mode,db_unique_name,open_mode,database_role from v$databa
se;
PROTECTION_MODE DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE
-------------------- ------------------------------ ---------- ----------------
MAXIMUM PROTECTION xhtest MOUNTED PRIMARY
SQL> alter database set standby database to maximize availability;(open 下可以直接)
SQL> select protection_mode,db_unique_name,protection_level,database_role from
$database;
PROTECTION_MODE DB_UNIQUE_ PROTECTION_LEVEL DATABASE_ROLE
-------------------- ---------- -------------------- ----------------
MAXIMUM AVAILABILITY xhtest RESYNCHRONIZATION PRIMARY~
~~~~~~~~~~~~~~~~~~~~這個時候PROTECTION_LEVE 為RESYNCHRONIZATION 因為 有個歸檔日誌還未傳動到standby~，所以為再同步狀態
SQL> alter system switch logfile;~~~~~~~~~~當主庫執行 時候，剛才沒傳過去的被自動傳過去補上了
系統已更改。
SQL> select protection_mode,db_unique_name,protection_level,database_role from v
$database;
PROTECTION_MODE DB_UNIQUE_ PROTECTION_LEVEL DATABASE_ROLE
-------------------- ---------- -------------------- ----------------
MAXIMUM AVAILABILITY xhtest MAXIMUM AVAILABILITY PRIMARY
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~全部傳過去了 狀態正確
當standby,shutdown 後也為再同步狀態
SQL> startup mount;
ORACLE 例程已經啟動。
Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145750508 bytes
Database Buffers 25165824 bytes
Redo Buffers 262144 bytes
資料庫裝載完畢。
SQL> select process,client_process,status,sequence# from v$managed_standby;
PROCESS CLIENT_P STATUS SEQUENCE#
--------- -------- ------------ ----------
ARCH ARCH CONNECTED 0
ARCH ARCH CONNECTED 0
SQL>\
SQL> select protection_mode,db_unique_name,protection_level,database_role from v
$database;
PROTECTION_MODE DB_UNIQUE_ PROTECTION_LEVEL DATABASE_ROLE
-------------------- ---------- -------------------- ----------------
MAXIMUM AVAILABILITY xhtest RESYNCHRONIZATION PRIMARY
SQL> alter system switchi logfile;
alter system switchi logfile
*
第 1 行出現錯誤:
ORA-02065: 非法的 ALTER SYSTEM 選項
SQL> alter system switch logfile;
系統已更改。
~~~~~~~~~~~~~08.08.19日實驗
alter database activate standby database~~~~~~~~~~~~~這句的作用
SQL> select db_unique_name ,database_role from v$database;
DB_UNIQUE_NAME DATABASE_ROLE
------------------------------ ----------------
standby1 PHYSICAL STANDBY
SQL> alter database activate standby database;
alter database activate standby database
*
第 1 行出現錯誤:
ORA-01154: 資料庫忙。現在不允許開啟, 關閉, 裝載和解除安裝
SQL> select PROCESS,CLIENT_PROCESS,STATUS,SEQUENCE# from v$managed_standby;
PROCESS CLIENT_P STATUS SEQUENCE#
--------- -------- ------------ ----------
ARCH ARCH CONNECTED 0
ARCH ARCH CONNECTED 0
MRP0 N/A WAIT_FOR_LOG 499
RFS UNKNOWN RECEIVING 0
RFS UNKNOWN RECEIVING 0
SQL> select recovery_mode,database_mode from v$archive_dest_status;
RECOVERY_MODE DATABASE_MODE
----------------------- ---------------
MANAGED MOUNTED-STANDBY
MANAGED MOUNTED-STANDBY
MANAGED MOUNTED-STANDBY
MANAGED MOUNTED-STANDBY
MANAGED MOUNTED-STANDBY
MANAGED MOUNTED-STANDBY
MANAGED MOUNTED-STANDBY
MANAGED MOUNTED-STANDBY
MANAGED MOUNTED-STANDBY
MANAGED MOUNTED-STANDBY
MANAGED MOUNTED-STANDBY
已選擇11行。
SQL> alter database recover managed standby database cancel;
資料庫已更改。
SQL> select db_unique_name ,database_role,open_mode from v$database;
DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE
------------------------------ ---------------- ----------
standby1 PRIMARY READ WRITE
SQL> select db_unique_name ,database_role,open_mode from v$database;
DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE
------------------------------ ---------------- ----------
xhtest PRIMARY READ WRITE
小結：是單專案變更以前的必須先主庫轉成 stanby ,standby 才可以轉成primary否則hang住
而在standby 用alter database active standby database 直接就變成 primary庫了，這時候產生2個primary，一般在主庫出問題，已經不能轉成standby時候用
,這個是單項的 轉不回去~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL> alter database convert to physical standby;
alter database convert to physical standby
*
第 1 行出現錯誤:
ORA-00933: SQL 命令未正確結束
SQL> alter database commit to switchover to physical standby;
alter database commit to switchover to physical standby
*
第 1 行出現錯誤:
ORA-16038: 日誌 2 序列號 2 無法歸檔
ORA-16009: 遠端歸檔日誌目標必須為備用資料庫
ORA-00312: 聯機日誌 2 執行緒 1: 'D:\STANDBYPHYSICAL\REDO02.LOG'
SQL> alter system set log_archive_dest_state_2=defer;
系統已更改。
SQL> alter database convert to physical standby;
alter database convert to physical standby
*
第 1 行出現錯誤:
ORA-00933: SQL 命令未正確結束
SQL> alter database commit to switchover to physical standby;
alter database commit to switchover to physical standby
*
第 1 行出現錯誤:
ORA-01109: 資料庫未開啟
SQL> shutdown immediate;
ORA-01109: 資料庫未開啟
已經解除安裝資料庫。
ORACLE 例程已經關閉。
SQL> startup
ORACLE 例程已經啟動。
Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145750508 bytes
Database Buffers 25165824 bytes
Redo Buffers 262144 bytes
資料庫裝載完畢。
資料庫已經開啟。
SQL> alter database commit to switchover to physical standby;
alter database commit to switchover to physical standby
*
第 1 行出現錯誤:
ORA-01154: 資料庫忙。現在不允許開啟, 關閉, 裝載和解除安裝
SQL> select db_unique_name ,database_role,open_mode from v$database;
DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE
------------------------------ ---------------- ----------
standby1 PRIMARY READ WRITE
SQL> alter database recover managed standby database cancel;
alter database recover managed standby database cancel
*
第 1 行出現錯誤:
ORA-01665: 控制檔案不是一個備用控制檔案
SQL> startup force mount
ORACLE 例程已經啟動。
Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145750508 bytes
Database Buffers 25165824 bytes
Redo Buffers 262144 bytes
資料庫裝載完畢。
SQL> alter database commit to switchover to physical standby;
alter database commit to switchover to physical standby
*
第 1 行出現錯誤:
ORA-01109: 資料庫未開啟
SQL> alter database open read only;
alter database open read only
*
第 1 行出現錯誤:
ORA-16005: 資料庫需要恢復
SQL> recover database ;
完成介質恢復。
SQL> alter database open read only;
alter database open read only
*
第 1 行出現錯誤:
ORA-16005: 資料庫需要恢復
SQL> alter database commit to switchover to physical standby;
alter database commit to switchover to physical standby
*
第 1 行出現錯誤:
ORA-01109: 資料庫未開啟
SQL> alter database open;
SQL> select db_unique_name ,database_role,open_mode from v$database;
DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE
------------------------------ ---------------- ----------
xhtest PRIMARY MOUNTED
SQL> alter system set log_archive_dest_state_2=defer;~~~~~~~~~~~~~~~~~主庫的(xhtest)
系統已更改。
SQL> alter database open;
資料庫已更改。
SQL> alter database commit to switchover to physical standby;
alter database commit to switchover to physical standby
*
第 1 行出現錯誤:
ORA-16014: 日誌 2 的序列號 5 未歸檔, 沒有可用的目的地
ORA-00312: 聯機日誌 2 執行緒 1: 'D:\STANDBYPHYSICAL\REDO02.LOG'
SQL> show parameter db_recover_file_dest
SQL> show parameter db_recovery_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string D:\STANDBYPHYSICAL\ARCHIVE
db_recovery_file_dest_size big integer 2G
SQL> show parameter log_archive_dest_1
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1 string location=D:\standbyphysical\ar
chive1 valid_for=(all_logfiles
,all_roles) db_unique_name=sta
ndby1
log_archive_dest_10 string
SQL> alter database convert to physical standby;
alter database convert to physical standby
*
第 1 行出現錯誤:
ORA-00933: SQL 命令未正確結束
SQL> alter database commit to switchover to physical standby;
alter database commit to switchover to physical standby
*
第 1 行出現錯誤:
ORA-01109: 資料庫未開啟
SQL> startup force ;
ORACLE 例程已經啟動。
Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145750508 bytes
Database Buffers 25165824 bytes
Redo Buffers 262144 bytes
資料庫裝載完畢。
資料庫已經開啟。
SQL> alter database commit to switchover to physical standby;
alter database commit to switchover to physical standby
*
第 1 行出現錯誤:
ORA-16014: 日誌 3 的序列號 6 未歸檔, 沒有可用的目的地
ORA-00312: 聯機日誌 3 執行緒 1: 'D:\STANDBYPHYSICAL\REDO03.LOG'
SQL>alter database clear unarchived logfile group 3;
做了這個操作後
ORA-00600: internal error code, arguments: [3712], [1], [1], [0], [1790983], [0], [1790982], []
hang住了 已經沒有別的辦法了 重新建立standby1吧
SQL> alter system set log_archive_dest_state_2=enable;（xhtest）
系統已更改。
重新建立步驟不詳細介紹了~
SQL> select PROCESS,CLIENT_PROCESS,STATUS,SEQUENCE# from v$managed_standby;
PROCESS CLIENT_P STATUS SEQUENCE#
--------- -------- ------------ ----------
ARCH ARCH CONNECTED 0
ARCH ARCH CONNECTED 0
RFS UNKNOWN RECEIVING 0
SQL> select db_unique_name ,database_role,open_mode from v$database;
DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE
------------------------------ ---------------- ----------
standby1 PHYSICAL STANDBY MOUNTED
下面在實驗下active
SQL> startup force mount;
ORACLE 例程已經啟動。
Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145750508 bytes
Database Buffers 25165824 bytes
Redo Buffers 262144 bytes
資料庫裝載完畢。
SQL> alter database flashback on;
資料庫已更改。
SQL> alter system switch logfile;
系統已更改。
SQL> select PROCESS,CLIENT_PROCESS,STATUS,SEQUENCE# from v$managed_standby;
PROCESS CLIENT_P STATUS SEQUENCE#
--------- -------- ------------ ----------
ARCH ARCH CONNECTED 0
ARCH ARCH CONNECTED 0
RFS UNKNOWN ATTACHED 0
RFS UNKNOWN RECEIVING 0
SQL> alter database recover managed standby database disconnect from session;
資料庫已更改。
SQL> alter database recover managed standby database cancel
2 ;
資料庫已更改。
SQL> alter database activate standby database;
資料庫已更改。
SQL> select db_unique_name ,database_role,open_mode,switchover_status from v$dat
abase;
select db_unique_name ,database_role,open_mode,switchover_status from v$database
*
第 1 行出現錯誤:
ORA-01507: 未裝載資料庫
SQL> startup force mount
ORACLE 例程已經啟動。
Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145750508 bytes
Database Buffers 25165824 bytes
Redo Buffers 262144 bytes
資料庫裝載完畢。
SQL> select db_unique_name ,database_role,open_mode,switchover_status from v$dat
abase;
DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE SWITCHOVER_STATUS
------------------------------ ---------------- ---------- --------------------
standby1 PRIMARY MOUNTED TO STANDBY
SQL>
SQL> alter database convert to physical standby;
alter database convert to physical standby
*
第 1 行出現錯誤:
ORA-00933: SQL 命令未正確結束
SQL> alter database commit to switchover to physical standby;
alter database commit to switchover to physical standby
*
第 1 行出現錯誤:
ORA-01109: 資料庫未開啟
SQL> alter database open;
資料庫已更改。
SQL> alter database commit to switchover to physical standby;
資料庫已更改。
SQL> select db_unique_name ,database_role,open_mode,switchover_status from v$dat
abase;
select db_unique_name ,database_role,open_mode,switchover_status from v$database
*
第 1 行出現錯誤:
ORA-01507: 未裝載資料庫
SQL> startup force mount
ORACLE 例程已經啟動。
Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145750508 bytes
Database Buffers 25165824 bytes
Redo Buffers 262144 bytes
資料庫裝載完畢。
SQL>
SQL> select db_unique_name ,database_role,open_mode,switchover_status from v$dat
abase;
DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE SWITCHOVER_STATUS
------------------------------ ---------------- ---------- --------------------
standby1 PHYSICAL STANDBY MOUNTED TO PRIMARY 轉變回來了
SQL> show parameter log_archive_dest_state_2(standby1)
NAME TYPE VALUE
------------------------------------ ----------- ----------------------------
log_archive_dest_state_2 string ENABLE
SQL> alter database activate standby database;
資料庫已更改。
SQL> alter database open
2 ;
alter database open
*
第 1 行出現錯誤:
ORA-01507: 未裝載資料庫
SQL> startup force
ORACLE 例程已經啟動。
Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145750508 bytes
Database Buffers 25165824 bytes
Redo Buffers 262144 bytes
資料庫裝載完畢。
資料庫已經開啟。
SQL> select db_unique_name ,database_role,open_mode,switchover_status from v$dat
abase;
DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE SWITCHOVER_STATUS
------------------------------ ---------------- ---------- --------------------
standby1 PRIMARY READ WRITE SESSIONS ACTIVE
SQL> alter database commit to switchover to physical standby;（在standby1 上）
alter database commit to switchover to physical standby
*
第 1 行出現錯誤:
ORA-16038: 日誌 2 序列號 1 無法歸檔
ORA-16009: 遠端歸檔日誌目標必須為備用資料庫
ORA-00312: 聯機日誌 2 執行緒 1: 'D:\STANDBYPHYSICAL\REDO02.LOG'
SQL> alter database convert to physical standby;（在standby 上）
alter database convert to physical standby
*
第 1 行出現錯誤:
ORA-00933: SQL 命令未正確結束
SQL> alter database commit to switchover to physical standby;（在xhtest 上）
alter database commit to switchover to physical standby
*
第 1 行出現錯誤:
ORA-16038: 日誌 2 序列號 504 無法歸檔
ORA-16009: 遠端歸檔日誌目標必須為備用資料庫
ORA-00312: 聯機日誌 2 執行緒 1:
'F:\ORACLE\PRODUCT\10.1.0\ORADATA\XHTEST\REDO02.LOG'
08.08.20~~~~~~~~~~~~~~~~~~~~繼續上面的實驗
SQL> select db_unique_name ,database_role,open_mode,switchover_status from v$dat
abase;
DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE SWITCHOVER_STATUS
------------------------------ ---------------- ---------- --------------------
standby1 PRIMARY READ WRITE SESSIONS ACTIVE
SQL> alter system set log_archive_dest_state_2=defer;
系統已更改。
SQL> alter database commit to switchover to physical standby;
alter database commit to switchover to physical standby
*
第 1 行出現錯誤:
ORA-01109: 資料庫未開啟
SQL> startup force mount
ORACLE 例程已經啟動。
Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145750508 bytes
Database Buffers 25165824 bytes
Redo Buffers 262144 bytes
資料庫裝載完畢。
SQL> alter database commit to switchover to physical standby;
alter database commit to switchover to physical standby
*
第 1 行出現錯誤:
ORA-01109: 資料庫未開啟
SQL> alter database open;
資料庫已更改。
SQL> alter database commit to switchover to physical standby;
alter database commit to switchover to physical standby
*
第 1 行出現錯誤:
ORA-16014: 日誌 3 的序列號 3 未歸檔, 沒有可用的目的地
ORA-00312: 聯機日誌 3 執行緒 1: 'D:\STANDBYPHYSICAL\REDO03.LOG'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL> alter system set log_archive_dest_state_2=enable;
系統已更改。
SQL> alter database create standby controlfile as 'd:\os備份\CONTROL01.CTL' reus
e;
資料庫已更改。
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~有一個種方法說 在primary重新建立控制檔案考回原standby然後，在變換 發現是不行的 是錯誤的理論
SQL> shutdown immediate;
ORA-01109: 資料庫未開啟
已經解除安裝資料庫。
ORACLE 例程已經關閉。
SQL> startup
ORACLE 例程已經啟動。
Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145750508 bytes
Database Buffers 25165824 bytes
Redo Buffers 262144 bytes
資料庫裝載完畢。
ORA-16004: 備份資料庫需要恢復
ORA-01190: 控制檔案或資料檔案 1 來自於最後一個 RESETLOGS 之前
ORA-01110: 資料檔案 1: 'D:\STANDBYPHYSICAL\SYSTEM01.DBF'
SQL> recover database;
ORA-00283: 恢復會話因錯誤而取消
ORA-01610: 使用 BACKUP CONTROLFILE 選項的恢復必須已完成
SQL> recover database using backup controlfile;
ORA-00283: 恢復會話因錯誤而取消
ORA-01666: 控制檔案用於備用資料庫
SQL> recover database until cancel using backup controlfile;
ORA-00283: 恢復會話因錯誤而取消
ORA-01666: 控制檔案用於備用資料庫
~~~小結：alter alter database activate standby database 啟用了備用資料庫，在open後 這個過程將自動 resetlogs。因此，在 startup 之前要做一次 cold backup，因為以往的 backup 最多隻能 recover 到 standby database 被activated 這一點。 因此 這個是個單項過程
所以一般是 在啟用後 利用 現在standby（已經成primary庫） 來重建原來的primary~~~~~~~把primary先建成主庫 ，然後切換，oracle推薦用alter database commit to switchover to 這樣可以儘可能的少丟失資料(主要指lgwr 後standby logfile中的資料)
實驗~~~~哪幾種辦法可以能切換過來
先重建立standby
SQL> select db_unique_name ,database_role,open_mode,switchover_status from v$dat
abase;
DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE SWITCHOVER_STATUS
------------------------------ ---------------- ---------- --------------------
standby1 PHYSICAL STANDBY MOUNTED TO PRIMARY
SQL> create restore point restore_point guarantee flashback database;
SQL> select PROCESS from v$managed_standby;
PROCESS
---------
ARCH
ARCH
RFS
SQL> alter system switch logfile;
系統已更改。
SQL> alter system switch logfile;
系統已更改。
SQL> alter system switch logfile;
系統已更改。
SQL> select PROCESS, CLIENT_PROCESS,STATUS,SEQUENCE# from v$managed_standby;
PROCESS CLIENT_P STATUS SEQUENCE#
--------- -------- ------------ ----------
ARCH ARCH CONNECTED 0
ARCH ARCH CONNECTED 0
RFS UNKNOWN ATTACHED 0
RFS UNKNOWN RECEIVING 0
SQL> select name from v$archived_log;
NAME
--------------------------------------------------------------------------------
D:\STANDBYPHYSICAL\ARCHIVE\ARC00507_0661538341.001
D:\STANDBYPHYSICAL\ARCHIVE\ARC00508_0661538341.001
D:\STANDBYPHYSICAL\ARCHIVE\ARC00506_0661538341.001
D:\STANDBYPHYSICAL\ARCHIVE\ARC00509_0661538341.001
SQL> alter database recover managed standby database disconnect from session;
資料庫已更改。
SQL> select PROCESS, CLIENT_PROCESS,STATUS,SEQUENCE# from v$managed_standby;
PROCESS CLIENT_P STATUS SEQUENCE#
--------- -------- ------------ ----------
ARCH ARCH CONNECTED 0
ARCH ARCH CONNECTED 0
RFS UNKNOWN ATTACHED 0
RFS UNKNOWN RECEIVING 0
MRP0 N/A APPLYING_LOG 507
SQL> select name,applied,creator from v$archived_log;
NAME APP CREATOR
-------------------------------------------------- --- -------
D:\STANDBYPHYSICAL\ARCHIVE\ARC00507_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00508_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00506_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00509_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00505_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00510_0661538341.001 YES ARCH
已選擇6行。
SQL> alter database recover managed standby database cancel;
SQL> shutdown immediate;
ORA-01109: 資料庫未開啟
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~做了一個冷備份
已經解除安裝資料庫。
ORACLE 例程已經關閉。
SQL> startup mount
ORACLE 例程已經啟動。
Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145750508 bytes
Database Buffers 25165824 bytes
Redo Buffers 262144 bytes
資料庫裝載完畢。
SQL> alter database activate standby database;
資料庫已更改。
SQL> select db_unique_name ,database_role,open_mode,switchover_status from v$dat
abase;
select db_unique_name ,database_role,open_mode,switchover_status from v$database
*
第 1 行出現錯誤:
ORA-01507: 未裝載資料庫
SQL> startup force
ORACLE 例程已經啟動。
Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145750508 bytes
Database Buffers 25165824 bytes
Redo Buffers 262144 bytes
資料庫裝載完畢。
資料庫已經開啟。
SQL> select db_unique_name ,database_role,open_mode,switchover_status from v$dat
abase;
DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE SWITCHOVER_STATUS
------------------------------ ---------------- ---------- --------------------
standby1 PRIMARY READ WRITE SESSIONS ACTIVE
SQL> create restore point restore_point guarantee flashback database;
create restore point restore_point guarantee flashback database
*
第 1 行出現錯誤:
ORA-00901: 無效 CREATE 命令
SQL> alter database commit to switchover to physical standby;
alter database commit to switchover to physical standby
*
第 1 行出現錯誤:
ORA-16038: 日誌 1 序列號 1 無法歸檔
ORA-16009: 遠端歸檔日誌目標必須為備用資料庫
ORA-00312: 聯機日誌 1 執行緒 1: 'D:\STANDBYPHYSICAL\REDO01.LOG'
SQL> alter database convert to standby database;
alter database convert to standby database
*
第 1 行出現錯誤:
ORA-00933: SQL 命令未正確結束
SQL>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~把剛才冷備份的 檔案考回standby1目錄
SQL> startup
ORACLE 例程已經啟動。
Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145750508 bytes
Database Buffers 25165824 bytes
Redo Buffers 262144 bytes
資料庫裝載完畢。
資料庫已經開啟。
SQL> alter database commit to switchover to physical standby;
資料庫已更改。
SQL> select db_unique_name ,database_role,open_mode,switchover_status from v$dat
abase;
DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE SWITCHOVER_STATUS
------------------------------ ---------------- ---------- --------------------
standby1 PHYSICAL STANDBY READ ONLY SWITCHOVER PENDING~~~~~~~~~~~~~~~~~~~~~~~~~~~靠轉變回來了
SQL> select PROCESS, CLIENT_PROCESS,STATUS,SEQUENCE# from v$managed_standby;
PROCESS CLIENT_P STATUS SEQUENCE#
--------- -------- ------------ ----------
ARCH ARCH CONNECTED 0
ARCH ARCH CONNECTED 0
RFS UNKNOWN RECEIVING 0
SQL> alter system switch logfile;（XHTEST）
系統已更改。
SQL>
SQL> select name,applied,creator from v$archived_log;
NAME APP CREATOR
-------------------------------------------------- --- -------
D:\STANDBYPHYSICAL\ARCHIVE\ARC00507_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00508_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00506_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00509_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00505_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00510_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00511_0661538341.001 NO ARCH
已選擇7行。
SQL> alter database recover managed standby database disconnect from session;
資料庫已更改。
SQL> select name,applied,creator from v$archived_log;
NAME APP CREATOR
-------------------------------------------------- --- -------
D:\STANDBYPHYSICAL\ARCHIVE\ARC00507_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00508_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00506_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00509_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00505_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00510_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00511_0661538341.001 YES ARCH
已選擇7行。
SQL> select db_unique_name ,database_role,open_mode,switchover_status from v$dat
abase;
DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE SWITCHOVER_STATUS
------------------------------ ---------------- ---------- --------------------
standby1 PHYSICAL STANDBY MOUNTED SWITCHOVER PENDING
用以上這個方法我們~~~讓啟用的standby又變了回去,其實這個方法 就跟重新建立standby沒 區別
推薦的轉換
SQL> select db_unique_name,database_role,switchover_status from v$database;
DB_UNIQUE_NAME DATABASE_ROLE SWITCHOVER_STATUS
------------------------------ ---------------- --------------------
xhtest PRIMARY SESSIONS ACTIVE
SQL> alter database commit to switchover to physical standby with session shut (xhtest)
down ;
資料庫已更改。
SQL> select db_unique_name,database_role,switchover_status from v$database;
DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE SWITCHOVER_STATUS
------------------------------ ---------------- ---------- --------------------
standby1 PHYSICAL STANDBY MOUNTED TO PRIMARY
SQL> select db_unique_name,database_role,switchover_status from v$database;
DB_UNIQUE_NAME DATABASE_ROLE SWITCHOVER_STATUS
------------------------------ ---------------- --------------------
xhtest PHYSICAL STANDBY TO PRIMARY
SQL>
SQL> alter database commit to switchover to primary;
資料庫已更改。
SQL> select db_unique_name,database_role,switchover_status from v$database;
select db_unique_name,database_role,switchover_status from v$database
*
第 1 行出現錯誤:
ORA-01507: 未裝載資料庫
SQL> shutdown immedaite;
SP2-0717: 非法的 SHUTDOWN 選項
SQL> shutdown immediate;
ORA-01507: 未裝載資料庫
ORACLE 例程已經關閉。
SQL> startup;
ORACLE 例程已經啟動。
Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145750508 bytes
Database Buffers 25165824 bytes
Redo Buffers 262144 bytes
資料庫裝載完畢。
資料庫已經開啟。
SQL> select db_unique_name,database_role,switchover_status from v$database;
DB_UNIQUE_NAME DATABASE_ROLE SWITCHOVER_STATUS
------------------------------ ---------------- --------------------
xhtest PRIMARY SESSIONS ACTIVE
SQL>
SQL> select db_unique_name ,database_role,open_mode,switchover_status from v$dat
abase;
DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE SWITCHOVER_STATUS
------------------------------ ---------------- ---------- --------------------
standby1 PHYSICAL STANDBY MOUNTED TO PRIMARY~~~~~~~~~~~~~~~~~~~~~~~~
SQL> select PROCESS, CLIENT_PROCESS,STATUS,SEQUENCE# from v$managed_standby;
PROCESS CLIENT_P STATUS SEQUENCE#
--------- -------- ------------ ----------
ARCH ARCH CONNECTED 0
ARCH ARCH CONNECTED 0
RFS UNKNOWN RECEIVING 0
SQL> alter database commit to switchover to primary;
資料庫已更改。
SQL> select db_unique_name ,database_role,open_mode,switchover_status from v$dat
abase;
select db_unique_name ,database_role,open_mode,switchover_status from v$database
*
第 1 行出現錯誤:
ORA-01507: 未裝載資料庫
SQL> shutdown immedaite;
SP2-0717: 非法的 SHUTDOWN 選項
SQL> shutdown immediate;
ORA-01507: 未裝載資料庫
ORACLE 例程已經關閉。
SQL> startup
ORACLE 例程已經啟動。
Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145750508 bytes
Database Buffers 25165824 bytes
Redo Buffers 262144 bytes
資料庫裝載完畢。
資料庫已經開啟。
SQL> select db_unique_name ,database_role,open_mode,switchover_status from v$dat
abase;
DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE SWITCHOVER_STATUS
------------------------------ ---------------- ---------- --------------------
standby1 PRIMARY READ WRITE SESSIONS ACTIVE
在primary沒有轉變成standby的情況下,standby1轉為了primary
SQL> alter database commit to switchover to physical standby with session shutdo
wn ;
alter database commit to switchover to physical standby with session shutdown~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~靠 轉不回去了
*
第 1 行出現錯誤:
ORA-16038: 日誌 3 序列號 514 無法歸檔
ORA-16009: 遠端歸檔日誌目標必須為備用資料庫
ORA-00312: 聯機日誌 3 執行緒 1: 'D:\STANDBYPHYSICAL\REDO03.LOG'
SQL> shutdown immediate;
ORA-01109: 資料庫未開啟
已經解除安裝資料庫。
ORACLE 例程已經關閉。
SQL>
SQL> shutdown immediate;
ORA-01109: 資料庫未開啟
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~把第一次的冷備份考回來（包含控制檔案）
已經解除安裝資料庫。
ORACLE 例程已經關閉。
SQL> startup
ORACLE 例程已經啟動。
Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145750508 bytes
Database Buffers 25165824 bytes
Redo Buffers 262144 bytes
資料庫裝載完畢。
資料庫已經開啟。
SQL> alter database commit to switchover to physical standby with session shutdo
wn ;
資料庫已更改。
SQL>
SQL> select db_unique_name,database_role,switchover_status from v$database;
DB_UNIQUE_NAME DATABASE_ROLE SWITCHOVER_STATUS
------------------------------ ---------------- --------------------
standby1 PHYSICAL STANDBY SWITCHOVER PENDING
SQL> select db_unique_name ,database_role,open_mode,switchover_status from v$dat
abase;
DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE SWITCHOVER_STATUS
------------------------------ ---------------- ---------- --------------------
standby1 PHYSICAL STANDBY READ ONLY SWITCHOVER PENDING
SQL> alter database recover managed standby database disconnect from session;
資料庫已更改。
SQL> select name,applied,creator from v$archived_log;
NAME APP CREATOR
-------------------------------------------------- --- -------
D:\STANDBYPHYSICAL\ARCHIVE\ARC00507_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00508_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00506_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00509_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00505_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00510_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00511_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00512_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00513_0661538341.001 NO ARCH
已選擇9行。
SQL> select db_unique_name ,database_role,open_mode,switchover_status from v$dat
abase;
DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE SWITCHOVER_STATUS
------------------------------ ---------------- ---------- --------------------
standby1 PHYSICAL STANDBY MOUNTED SWITCHOVER LATENT
SQL> select PROCESS, CLIENT_PROCESS,STATUS,SEQUENCE# from v$managed_standby;
PROCESS CLIENT_P STATUS SEQUENCE#
--------- -------- ------------ ----------
ARCH ARCH CONNECTED 0
ARCH ARCH CONNECTED 0
RFS UNKNOWN RECEIVING 0
RFS UNKNOWN RECEIVING 0~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~程式沒了
SQL> alter database recover managed standby database disconnect from session;~~~~~~~~~~~~~~~~~~需要再開一次
資料庫已更改。
SQL> select name,applied,creator from v$archived_log;
NAME APP CREATOR
-------------------------------------------------- --- -------
D:\STANDBYPHYSICAL\ARCHIVE\ARC00507_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00508_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00506_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00509_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00505_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00510_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00511_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00512_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00513_0661538341.001 NO ARCH
已選擇9行。
SQL> select name,applied,creator from v$archived_log;
NAME APP CREATOR
-------------------------------------------------- --- -------
D:\STANDBYPHYSICAL\ARCHIVE\ARC00507_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00508_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00506_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00509_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00505_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00510_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00511_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00512_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00513_0661538341.001 YES ARCH
已選擇9行。
SQL>
SQL> select db_unique_name ,database_role,open_mode,switchover_status from v$dat
abase;
DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE SWITCHOVER_STATUS
------------------------------ ---------------- ---------- --------------------
standby1 PHYSICAL STANDBY MOUNTED SWITCHOVER PENDING
SQL> alter database commit to switchover to physical standby with session shut
down ;
資料庫已更改。
SQL> shutdown immediate
ORA-01507: 未裝載資料庫
ORACLE 例程已經關閉。
SQL>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL> select db_unique_name ,database_role,open_mode,switchover_status from v$da
abase;
DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE SWITCHOVER_STATUS
------------------------------ ---------------- ---------- --------------------
standby1 PHYSICAL STANDBY MOUNTED TO PRIMARY
SQL> select db_unique_name,database_role,switchover_status from v$database;
DB_UNIQUE_NAME DATABASE_ROLE SWITCHOVER_STATUS
------------------------------ ---------------- --------------------
xhtest PHYSICAL STANDBY TO PRIMARY
SQL> alter database commit to switchover to primary;
資料庫已更改。
SQL> select db_unique_name,database_role,switchover_status from v$database;
DB_UNIQUE_NAME DATABASE_ROLE SWITCHOVER_STATUS
------------------------------ ---------------- --------------------
xhtest PRIMARY SESSIONS ACTIVE
SQL> select db_unique_name ,database_role,open_mode,switchover_status from v$dat
abase;
DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE SWITCHOVER_STATUS
------------------------------ ---------------- ---------- --------------------
standby1 PHYSICAL STANDBY MOUNTED TO PRIMARY
SQL> select PROCESS, CLIENT_PROCESS,STATUS,SEQUENCE# from v$managed_standby;
PROCESS CLIENT_P STATUS SEQUENCE#
--------- -------- ------------ ----------
ARCH ARCH CONNECTED 0
ARCH ARCH CONNECTED 0
RFS UNKNOWN RECEIVING 0
SQL> alter system switch logfile;(xhtest)
系統已更改。
SQL> select db_unique_name ,database_role,open_mode,switchover_status from v$dat
abase;
DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE SWITCHOVER_STATUS
------------------------------ ---------------- ---------- --------------------
standby1 PHYSICAL STANDBY MOUNTED RECOVERY NEEDED
SQL> alter database recover managed standby database disconnect from session;
資料庫已更改。
SQL> select db_unique_name ,database_role,open_mode,switchover_status from v$dat
abase;
DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE SWITCHOVER_STATUS
------------------------------ ---------------- ---------- --------------------
standby1 PHYSICAL STANDBY MOUNTED NOT ALLOWED
SQL> select name,applied,creator from v$archived_log;
NAME APP CREATOR
-------------------------------------------------- --- -------
D:\STANDBYPHYSICAL\ARCHIVE\ARC00507_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00508_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00506_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00509_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00505_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00510_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00511_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00512_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00513_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00514_0661538341.001 YES FGRD
D:\STANDBYPHYSICAL\ARCHIVE\ARC00515_0661538341.001 YES ARCH
已選擇11行。
SQL> alter database commit to switchover to primary ~~~~~~~~~~~~~~~~~~~~~~~~~一直hang著
2 ;
Wed Aug 20 17:05:12 2008
alter database commit to switchover to primary
Wed Aug 20 17:05:12 2008
If media recovery active, switchover will wait 900 seconds~~~~~~~~~~~~~~~~~~~~~~~~看看能hang多久
大約hang了6分鐘
SQL> alter database commit to switchover to physical standby with session shut (xhtest)
down ;
SQL> alter database commit to switchover to primary 主庫變過來後standby1也立刻變成primary
2 ;
資料庫已更改。
SQL> select db_unique_name ,database_role,open_mode,switchover_status from v$dat
abase;
DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE SWITCHOVER_STATUS
------------------------------ ---------------- ---------- --------------------
standby1 PRIMARY READ WRITE SESSIONS ACTIVE
SQL> select db_unique_name,database_role,switchover_status from v$database;
DB_UNIQUE_NAME DATABASE_ROLE SWITCHOVER_STATUS
------------------------------ ---------------- --------------------
xhtest PHYSICAL STANDBY TO PRIMARY
alter system switch logfile;(standby1)
SQL> select PROCESS, CLIENT_PROCESS,STATUS,SEQUENCE# from v$managed_standby;
PROCESS CLIENT_P STATUS SEQUENCE#
--------- -------- ------------ ----------
ARCH ARCH CONNECTED 0
ARCH ARCH CONNECTED 0
RFS UNKNOWN RECEIVING 0
SQL> alter database recover managed standby database disconnect from session;
QL> select db_unique_name,database_role,switchover_status from v$database;
dB_UNIQUE_NAME DATABASE_ROLE SWITCHOVER_STATUS
----------------------------- ---------------- --------------------
xhtest PHYSICAL STANDBY NOT ALLOWED
08.08.21實驗 ~~~~~~~~~~~~~~~~~~~~~~
看看 啟用 standby後能閃回回去不
SQL> select OPEN_MODE,database_role,db_unique_name,switchover_status from v$data
base;
OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME SWITCHOVER_STATUS
---------- ---------------- ------------------------------ --------------------
READ WRITE PRIMARY xhtest SESSIONS ACTIVE
SQL> select OPEN_MODE,database_role,db_unique_name,switchover_status from v$data
base;
OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME SWITCHOVER_STATUS
---------- ---------------- ------------------------------ --------------------
MOUNTED PHYSICAL STANDBY standby1 NOT ALLOWED
SQL> select flashback_on from v$database;
FLA
---
NO
SQL> alter database flashback on;
alter database flashback on
*
第 1 行出現錯誤:
ORA-01153: 啟用了不相容的介質恢復
SQL> alter database recover managed standby database cancel;
資料庫已更改。
SQL> alter database flashback on;
資料庫已更改。
SQL> select flashback_on from v$database;
FLA
---
YES
SQL>
SQL> select current_scn from v$database;(standby)
CURRENT_SCN
-----------
1835282
SQL> alter database activate standby database;
資料庫已更改。
SQL> select OPEN_MODE,database_role,db_unique_name,switchover_status from v$data
base;
select OPEN_MODE,database_role,db_unique_name,switchover_status from v$database
*
第 1 行出現錯誤:
ORA-01507: 未裝載資料庫
SQL> shutdown immediate;
ORA-01507: 未裝載資料庫
ORACLE 例程已經關閉。
SQL> startup
ORACLE 例程已經啟動。
Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145750508 bytes
Database Buffers 25165824 bytes
Redo Buffers 262144 bytes
資料庫裝載完畢。
資料庫已經開啟。
SQL> select OPEN_MODE,database_role,db_unique_name,switchover_status from v$data
base;
OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME SWITCHOVER_STATUS
---------- ---------------- ------------------------------ --------------------
READ WRITE PRIMARY standby1 SESSIONS ACTIVE
SQL>
SQL> alter database commit to switchover to physical standby;
alter database commit to switchover to physical standby
*
第 1 行出現錯誤:
ORA-16038: 日誌 2 序列號 1 無法歸檔
ORA-16009: 遠端歸檔日誌目標必須為備用資料庫
ORA-00312: 聯機日誌 2 執行緒 1: 'D:\STANDBYPHYSICAL\REDO02.LOG'
SQL> select sequence# ,status from v$log;
SEQUENCE# STATUS
---------- ----------------
0 UNUSED~~~~~~~~~~~~~~~~~~~~~`resetlogs了
1 CURRENT
0 UNUSED
SQL> flashback database to scn 1835282;
flashback database to scn 1835282
*
第 1 行出現錯誤:
ORA-38729: 執行 FLASHBACK 的閃回資料庫日誌資料不足。
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prod
PL/SQL Release 10.1.0.2.0 - Production
CORE 10.1.0.2.0 Production
TNS for 32-bit Windows: Version 10.1.0.2.0 - Production
NLSRTL Version 10.1.0.2.0 - Production
注意 ：10.1.0.2.0 版本不行 ~~~~~~~~~~需要 10.2.0.1 版本可以
所以實驗失敗~~~~~~~~~~~~~~理論很簡單
在standby 啟用前~~~做一個 閃回點create restore point name
然後啟用~~~~~standby 成readwrite 然後，變會standby前 先閃回到 restore point
然後alter database convert to physical standby;(需要 10.2.0.1 以上版本可以) 建立閃回點也是10.2.0.1以上才可以10GR2以上
這之中，我們還需要關掉primary傳遞給stadby的遠端歸檔位置log_archive_dest_state_2=defer都先關了 避免primary 還繼續給這個遠端位置傳遞，這個位置已經成主庫了
~~~~~~~~~~~~~~~~~~~~~~10.2.0.1 後flashback 可以 閃回到resetlogs前
重新建立standby的分析
剛才啟用後，在這個版本是轉換不過去了~~~~~~~~~~~沒法用flashback
重新建立用 8.20好的備份，8.20日的建立的standby controlfile
SQL> startup
ORACLE 例程已經啟動。
Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145750508 bytes
Database Buffers 25165824 bytes
Redo Buffers 262144 bytes
資料庫裝載完畢。
資料庫已經開啟。
SQL> select OPEN_MODE,database_role,db_unique_name,switchover_status from v$data
base;
OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME SWITCHOVER_STATUS
---------- ---------------- ------------------------------ --------------------
READ ONLY PHYSICAL STANDBY standby1 SWITCHOVER PENDING
把 冷備份直接覆蓋過來後
SQL> select name,applied,creator from v$archived_log;
NAME APP CREATOR
-------------------------------------------------- --- -------
D:\STANDBYPHYSICAL\ARCHIVE\ARC00507_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00508_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00506_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00509_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00505_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00510_0661538341.001 YES ARCH
已選擇6行。
這幾個是 20號的歸檔日誌
SQL> select PROCESS,CLIENT_PROCESS,STATUS,SEQUENCE# from v$managed_standby;
PROCESS CLIENT_P STATUS SEQUENCE#
--------- -------- ------------ ----------
ARCH ARCH CONNECTED 0
ARCH ARCH CONNECTED 0
RFS UNKNOWN ATTACHED 0
SQL> alter system switch logfile ;
系統已更改。
SQL>
SQL> select name,applied,creator from v$archived_log;
NAME APP CREATOR
-------------------------------------------------- --- -------
D:\STANDBYPHYSICAL\ARCHIVE\ARC00507_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00508_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00506_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00509_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00505_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00510_0661538341.001 YES ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00511_0661538341.001 NO ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00512_0661538341.001 NO ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00513_0661538341.001 NO ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00514_0661538341.001 NO ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00515_0661538341.001 NO ARCH
NAME APP CREATOR
-------------------------------------------------- --- -------
D:\STANDBYPHYSICAL\ARCHIVE\ARC00516_0661538341.001 NO ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00517_0661538341.001 NO ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00518_0661538341.001 NO ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00519_0661538341.001 NO ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00520_0661538341.001 NO ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00521_0661538341.001 NO ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00522_0661538341.001 NO ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00523_0661538341.001 NO ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00524_0661538341.001 NO ARCH
D:\STANDBYPHYSICAL\ARCHIVE\ARC00525_0661538341.001 NO ARCH
已選擇21行。
把之前的日誌給補回來了~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
結論用以前的 備份是可以的
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
故障轉換角色
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
08.08.25~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~`實驗環境
SQL> select db_unique_name,SWITCHOVER_STATUS,database_role,PROTECTION_MODE from
v$database;
DB_UNIQUE_NAME SWITCHOVER_STATUS DATABASE_ROLE PROTECTION_MODE
--------------- -------------------- ---------------- --------------------
standby1 NOT ALLOWED PHYSICAL STANDBY MAXIMUM PERFORMANCE
SQL> col db_unique_name format a15
SQL> select db_unique_name,SWITCHOVER_STATUS,database_role,PROTECTION_MODE from
v$database;
DB_UNIQUE_NAME SWITCHOVER_STATUS DATABASE_ROLE PROTECTION_MODE
--------------- -------------------- ---------------- --------------------
xhtest SESSIONS ACTIVE PRIMARY MAXIMUM PERFORMANCE
SQL>
SQL> select PROCESS,CLIENT_PROCESS,SEQUENCE#,STATUS from v$managed_standby;
PROCESS CLIENT_P SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
RFS UNKNOWN 0 RECEIVING
MRP0 N/A 3 WAIT_FOR_LOG
SQL> show parameter log_archive_dest_2（xhtest）
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string service=standby1 valid_for=(
online_logfiles,primary_role)
db_unique_name=standby1
~~~~ V$ARCHIVE_GAP;~~~~~~~~~~~~對 故障轉換很重要的一個檢視
QL> desc v$archive_gap;
名稱 是否為空? 型別
----------------------------------------- -------- ----------------------------
THREAD# NUMBER
LOW_SEQUENCE# NUMBER
HIGH_SEQUENCE# NUMBER
SQL> select * from v$archive_gap;~~~~~~~~~檢視的目的就是查詢哪個 歸檔沒有傳過來
未選定行
沒有就是沒有缺失
製造一次 故障
SQL> select name from v$archived_log;
F:\歸檔備份\ARC00019_0663689986.001~~~~~~~~~~~~~~~~~~~主庫在這個日誌時候崩潰了~~~~~~~~~~最新 歸檔日誌為 19
SQL> select name,creator,applied from v$archived_log;
D:\STANDBYPHYSICAL\ARCHIVE\ARC00016_0663689986.001 ARCH NO
D:\STANDBYPHYSICAL\ARCHIVE\ARC00017_0663689986.001 ARCH NO~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~standby沒有收到 最新19 這個日誌
D:\STANDBYPHYSICAL\ARCHIVE\ARC00018_0663689986.001 ARCH NO
由於實驗環境問題
SQL> select * from v$archive_gap;
未選定行~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~還為空，在同一臺機器上，若2臺機器上回顯示出來
SQL> select db_unique_name,SWITCHOVER_STATUS,database_role,PROTECTION_MODE from
v$database;
DB_UNIQUE_NAME SWITCHOVER_STATUS DATABASE_ROLE PROTECTION_MODE
--------------- -------------------- ---------------- --------------------~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~不允許轉換成primary
standby1 NOT ALLOWED PHYSICAL STANDBY MAXIMUM PERFORMANCE
下面 我們從primary將 沒收到 中斷的日誌0017拷貝到standby 並且手動註冊
alter database register physical logfile 'D:\standbyphysical\archive\ARC00019_06~~~~~~~~~~~~~~~~~~註冊上這個日誌 靠到 standby目錄下
63689986.001'
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE …
FINISH FORCE~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~執行後一旦成功 就可以
在standby執行切換了
alter database commit to switchover to primary;~~~~~~~~~~~~~~~~~~~~不會hang住了
FORCE 關鍵字將會停止當前活動的RFS 程式，以便立刻執行failover。
如果我們主庫設定的是實時應用lgwr 並且 standby 有 standby logfile ；~~~
那麼 alter database recover managed standby database finish FINISH SKIP STANDBY LOGFILE;
或者應用完畢後 強行啟用~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
alter database activate standby database;~~~~~~~~~~~~~~~~~~~~~~~~~~讓他直接變成primary
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12020513/viewspace-607595/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle實驗(02)：轉換 & 轉譯Oracle
- Activity轉換為View和把圖片轉換為ViewView
- oracle實驗記錄 關於記憶體的幾個viewOracle記憶體View
- oracle實驗記錄 (oracle 10G dataguard(7)physical dg轉換)Oracle
- 試驗Oracle中實現行列轉換的方法（轉）Oracle
- Oracle 查詢轉換-02 View MergingOracleView
- 【安卓筆記】view.getX和view.getTranslationX區別安卓筆記View
- scala學習筆記：理解stream和view筆記View
- haproxy實驗筆記筆記
- oracle和mysql的行列轉換OracleMySql
- oracle實驗記錄 （管理outlines） 轉Oracle
- Solidity語言學習筆記————11、隱式轉換和顯式轉換Solid筆記
- View分發筆記View筆記
- lfs 6.3實驗筆記筆記
- Python——格式轉換的學習筆記Python筆記
- 【轉】Oracle學習筆記：INITRANS和MAXTRANS引數的作用Oracle筆記
- 微信小程式text元件和view元件不換行微信小程式元件View
- 筆記：隱式轉換規則筆記
- vsftpd筆記後的實驗薦FTP筆記
- oracle實驗記錄 （flashback）Oracle
- oracle實驗記錄 （OMF）Oracle
- oracle實驗記錄 （NET）Oracle
- oracle實驗記錄 （audit）Oracle
- 開發：隨筆記錄之 Json字串和物件的相互轉換筆記JSON字串物件
- MySQL和Oracle中的隱式轉換MySqlOracle
- Struts資料驗證筆記 (轉)筆記
- oracle實驗記錄 (oracle reset parameter)Oracle
- oracle 後臺程式的理解筆記Oracle筆記
- Cris 的 Scala 筆記整理（十）：隱式轉換筆記
- Oracle和JDE日曆轉換Oracle
- oracle程式和記憶體轉儲說明Oracle記憶體
- 自學PHP筆記 (三) 型別轉換PHP筆記型別
- 自學PHP筆記(三) 型別轉換PHP筆記型別
- expdp 和impdp的筆記[轉帖]筆記
- ORACLE IMP和EXP的使用實驗Oracle
- oracle直方圖筆記-轉Oracle直方圖筆記
- oracle實驗記錄 （CKPT的觸發）Oracle
- .NET 6學習筆記（6）——SSL證書的匯出和格式轉換筆記