11.2.0.4 Dataguard臨時讀寫三種方法
包含以下三部分內容，都可以實現物理備庫臨時讀寫以及再恢復為physical standby：
-
flashback database閃回physical standby
-
activate standby database
-
snapshot standby
1. 在physical standby閃回恢復主庫誤運算元據
只要Flashback dest空間跟歸檔在，可以任意閃回。
1.1 查詢當前primary SCN
SYS@honor1 > select to_char(current_scn) from v$database; TO_CHAR(CURRENT_SCN) ---------------------------------------- 17031266
1.2 primary建立測試表
HR@honor1 > create table test_flashback parallel as select * from user_objects; HR@honor1 > insert into test_flashback parallel select * from user_objects; HR@honor1 > commit;
1.3 備庫查詢測試表，閃回到建立該表前
SYS@honordg > desc hr.test_flashback; # 檢查，備庫已經同步該表 SYS@honordg > alter database recover managed standby database cancel; SYS@honordg > shutdown immediate; SYS@honordg > startup mount; SYS@honordg > flashback database to scn 17031266; Flashback complete. 21:03:11 SYS@honordg > alter database open read only; Database altered. HR@honordg > desc test_flashback; # 可以看到已經閃回到建立該表前。 ERROR: ORA-04043: object test_flashback does not exist
1.4 恢復physical standby
# 檢查備庫當前狀態
SYS@honordg > select GUARD_STATUS,OPEN_MODE,STANDBY_BECAME_PRIMARY_SCN,SWITCHOVER_STATUS,DATABASE_ROLE from v$database; GUARD_S OPEN_MODE STANDBY_BECAME_PRIMARY_SCN SWITCHOVER_STATUS DATABASE_ROLE ------- -------------------- ---------------------------------------- -------------------- ---------------- NONE READ ONLY 0 NOT ALLOWED PHYSICAL STANDBY SYS@honordg > select to_char(current_scn) from v$database; TO_CHAR(CURRENT_SCN) ---------------------------------------- 17031266
# 直接開啟恢復，備庫即可利用歸檔
SYS@honordg > alter database recover managed standby database disconnect from session; Database altered.
1.5 透過alert詳解閃回以及恢復physical standby過程
（1）可以看到physical standby已經恢復sequence 914日誌
Archived Log entry 39 added for thread 1 sequence 914 rlc 984508005 ID 0x150ac660 dest 2: RFS[2]: No standby redo logfiles created RFS[2]: Opened log for thread 1 sequence 915 dbid 353046371 branch 984508005 Tue Jun 09 20:30:02 2020 Media Recovery Log +DGFRA/honordg/archivelog/2020_06_09/thread_1_seq_914.493.1042661953 RFS[1]: Opened log for thread 1 sequence 915 dbid 353046371 branch 984508005 RFS[2]: Assigned to RFS process 14109 RFS[2]: No standby redo logfiles created Archived Log entry 40 added for thread 1 sequence 915 rlc 984508005 ID 0x150ac660 dest 2: RFS[2]: Opened log for thread 1 sequence 916 dbid 353046371 branch 984508005 Tue Jun 09 21:02:12 2020
（2）執行閃回，可以看到利用閃回日誌以及歸檔閃回到指定scn
flashback database to scn 17031266 Flashback Restore Start Flashback Restore Complete Flashback Media Recovery Start Serial Media Recovery started Flashback Media Recovery Log +DGFRA/honordg/archivelog/2020_06_09/thread_1_seq_912.498.1042661497 Flashback Media Recovery Log +DGFRA/honordg/archivelog/2020_06_09/thread_1_seq_913.467.1042661953 Flashback Media Recovery Log +DGFRA/honordg/archivelog/2020_06_09/thread_1_seq_914.493.1042661953 Incomplete Recovery applied until change 17031267 time 06/18/2020 10:47:12 Flashback Media Recovery Complete Completed: flashback database to scn 17031266
（3）恢復physical standby
# 可以看到會再次利用sequence 914恢復physical standby
Physical standby database opened for read only access. Completed: alter database open read only Tue Jun 09 21:03:28 2020 db_recovery_file_dest_size of 9216 MB is 17.72% used. This is a user-specified limit on the amount of space that will be used by this database for recovery-related files, and does not reflect the amount of space available in the underlying filesystem or ASM diskgroup. Tue Jun 09 21:06:41 2020 alter database recover managed standby database disconnect from session Attempt to start background Managed Standby Recovery process (honordg) Tue Jun 09 21:06:41 2020 MRP0 started with pid=34, OS id=14273 MRP0: Background Managed Standby Recovery process started (honordg) Serial Media Recovery started Managed Standby Recovery not using Real Time Apply Waiting for all non-current ORLs to be archived... All non-current ORLs have been archived. Media Recovery Log +DGFRA/honordg/archivelog/2020_06_09/thread_1_seq_914.493.1042661953 Completed: alter database recover managed standby database disconnect from session Media Recovery Log +DGFRA/honordg/archivelog/2020_06_09/thread_1_seq_915.494.1042664529 Media Recovery Waiting for thread 1 sequence 916 (in transit)
2. 臨時啟用轉換為讀寫庫
2.1 取消日誌應用
SYS@honordg > alter database recover managed standby database cancel; Database altered.
2.2 建立guarantee還原點
SYS@honordg > create restore point rst_guar guarantee flashback database; Restore point created. SYS@honordg > select scn,name,guarantee_flashback_database,preserved from v$restore_point; SCN NAME GUA PRE ---------------------------------------- ---------------------------------------------------------------------- --- --- 17517039 RST_GUAR YES YES
2.3 啟用備庫，啟用後庫由read only轉化為mount
SYS@honordg > ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE; Database altered. SYS@honordg > select database_role,open_mode from v$database; DATABASE_ROLE OPEN_MODE ---------------- -------------------- PRIMARY MOUNTED SYS@honordg > select scn,name,guarantee_flashback_database,preserved from v$restore_point; SCN NAME GUA PRE ---------------------------------------- ---------------------------------------------------------------------- --- --- 17517039 RST_GUAR YES YES
2.4 開啟資料庫，建立測試資料
SYS@honordg > alter database open; Database altered. SYS@honordg > select database_role,open_mode from v$database; DATABASE_ROLE OPEN_MODE ---------------- -------------------- PRIMARY READ WRITE SYS@honordg > create table hr.test_guarantee as select * from dba_tables; Table created. SYS@honordg > select count(1) from hr.test_guarantee; COUNT(1) ---------------------------------------- 3024
2.5 還原physical standby
（1）關閉資料庫，啟動到mount
SYS@honordg > shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SYS@honordg > startup mount; ORACLE instance started. Total System Global Area 521936896 bytes Fixed Size 2254824 bytes Variable Size 377489432 bytes Database Buffers 138412032 bytes Redo Buffers 3780608 bytes Database mounted.
# 閃回還原點
SYS@honordg > flashback database to restore point RST_GUAR; Flashback complete.
# 檢視測試資料
SYS@honordg > alter database open; #閃回之後，需要執行convert語句轉換為physical standby alter database open * ERROR at line 1: ORA-01589: must use RESETLOGS or NORESETLOGS option for database open SYS@honordg > select database_role,open_mode from v$database; DATABASE_ROLE OPEN_MODE ---------------- -------------------- PRIMARY MOUNTED SYS@honordg > alter database convert to physical standby; Database altered.
# 轉換後資料庫處於nomount階段
SYS@honordg > select status from v$instance; STATUS ---------- STARTED SYS@honordg > shutdown immediate; ORA-01507: database not mounted ORACLE instance shut down. SYS@honordg > startup; ORACLE instance started. Total System Global Area 521936896 bytes Fixed Size 2254824 bytes Variable Size 377489432 bytes Database Buffers 138412032 bytes Redo Buffers 3780608 bytes Database mounted. Database opened.
# 開啟日誌應用
SYS@honordg > alter database recover managed standby database disconnect from session; Database altered.
# 檢查資料庫狀態角色
SYS@honordg > select database_role,open_mode from v$database; DATABASE_ROLE OPEN_MODE ---------------- -------------------- PHYSICAL STANDBY READ ONLY WITH APPLY
3. 利用snapshot database，臨時讀寫備庫
3.1 檢視physical狀態，是否開啟閃回
SYS@honordg > select * from v$restore_point; no rows selected SYS@honordg > show parameter recovery; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string +DGFRA db_recovery_file_dest_size big integer 9G recovery_parallelism integer 0 SYS@honordg > select flashback_on from v$database; FLASHBACK_ON ------------------ YES SYS@honordg > select database_role,open_mode from v$database; DATABASE_ROLE OPEN_MODE ---------------- -------------------- PHYSICAL STANDBY READ ONLY WITH APPLY
3.2 取消日誌應用， 轉換為snapshot standby database
SYS@honordg > alter database recover managed standby database cancel; Database altered. SYS@honordg > alter database convert to snapshot standby; Database altered. SYS@honordg > select database_role,open_mode from v$database; DATABASE_ROLE OPEN_MODE ---------------- -------------------- SNAPSHOT STANDBY MOUNTED SYS@honordg > alter database open; Database altered.
3.3 檢查physical standby當前狀態
SYS@honordg > select database_role,open_mode from v$database; DATABASE_ROLE OPEN_MODE ---------------- -------------------- SNAPSHOT STANDBY READ WRITE SYS@honordg > select scn,name,guarantee_flashback_database,preserved from v$restore_point; SCN NAME GUA PRE -------------- ---------------------------------------------------------------------- --- --- 17034110 SNAPSHOT_STANDBY_REQUIRED_06/09/2020 21:36:15 YES YES
3.4 臨時讀寫
SYS@honordg > create table hr.test_snapshot as select * from dba_users; Table created. SYS@honordg > select count(*) from hr.test_snapshot; COUNT(*) ---------------------------------------- 44
3 .5 恢復physical standby
（1）檢查狀態，轉換為physical standby
SYS@honordg > shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SYS@honordg > startup mount; ORACLE instance started. Total System Global Area 521936896 bytes Fixed Size 2254824 bytes Variable Size 377489432 bytes Database Buffers 138412032 bytes Redo Buffers 3780608 bytes Database mounted. SYS@honordg > select database_role,open_mode from v$database; DATABASE_ROLE OPEN_MODE ---------------- -------------------- SNAPSHOT STANDBY MOUNTED Elapsed: 00:00:00.02 SYS@honordg > select flashback_on from v$database; FLASHBACK_ON ------------------ YES Elapsed: 00:00:00.00 SYS@honordg > alter database convert to physical standby; Database altered. SYS@honordg > select status from v$instance; # 可以看到轉換完成資料庫處於nomount狀態 STATUS ---------- STARTED SYS@honordg > shutdown immediate; ORA-01507: database not mounted ORACLE instance shut down. SYS@honordg > startup; ORACLE instance started. Total System Global Area 521936896 bytes Fixed Size 2254824 bytes Variable Size 377489432 bytes Database Buffers 138412032 bytes Redo Buffers 3780608 bytes Database mounted. Database opened. SYS@honordg > alter database recover managed standby database disconnect from session; Database altered.
# 檢視之前讀寫資料，發現已經清除
SYS@honordg > select count(*) from hr.test_snapshot; select count(*) from hr.test_snapshot * ERROR at line 1: ORA-00942: table or view does not exist
# 檢查alert日誌，可以發現，snapshot standby是利用還原點restore point完成恢復
Flashback Restore Start Flashback Restore Complete Drop guaranteed restore point Guaranteed restore point dropped
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31439444/viewspace-2707018/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle 11.2.0.4 physical dataguard和snapshot dataguard切換Oracle
- Oracle 11.2.0.4 Dataguard兩則故障處理Oracle
- Python|讀、寫Excel檔案(三種模組三種方式)PythonExcel
- Python中檔案的讀寫、寫讀和追加寫讀三種模式的特點Python模式
- oracle 11.2.0.4 DataGuard Broker配置過程中可能遇到的問題及解決方法Oracle
- dataguard主備延遲多長時間的2種查詢方法
- Redis-6-三種快取讀寫策略Redis快取
- dataguard驗證是否正常同步的2種方法
- Python逐行讀取檔案常用的三種方法！Python
- python讀取檔案指定行的三種方法Python
- 在.net中讀寫config檔案的各種方法
- 臨時讀原始碼的函式原始碼函式
- 解決代理超時問題的三種方法
- c語言 - 交換兩個變數（不建立臨時變數）兩種方法C語言變數
- Oracle臨時表空間檢視、新增臨時表空間資料檔案、修改預設臨時表空間 方法！Oracle
- 執法者三種方法
- 請寫出幾種匿名自執行的寫法（至少三種）
- Solaris下Oracle RAC 11.2.0.4 安裝方法Oracle
- Python影像讀寫方法對比Python
- 消除臨時表空間暴漲的方法
- python讀取文字檔案內容的方法主要分為哪三種？Python
- js解析url的三種方法JS
- Go 中的三種排序方法Go排序
- CSS的三種使用方法CSS
- 解析Json字串的三種方法JSON字串
- WP_Image_Editor_Imagick 漏洞臨時解決方法
- 單重繼承缺陷的臨時解決方法繼承
- SSH遠端連結超時斷開的三種解決方法
- 基於多種場景DataGuard切換方案
- 三種方法實現CSS三欄佈局CSS
- 臨時物件物件
- python selenium的三種等待方式解讀Python
- 【DATAGUARD】Oracle 通過Dataguard指定恢復時間用於找回丟失資料Oracle
- 學習筆記 ProgressBar三種style 水平兩種寫法筆記
- 解決Make時，“/usr/bin/ld: 找不到 -lXXX”問題的三種方法
- 一種提升SQL改寫效率的方法SQL
- wget 命令提示 “use ‘--no-check-certificate’” 臨時解決方法wget
- 再談檔案讀寫：判斷檔案的幾種方法及其優劣對比