[20230425]注意snapshot standby與activate standby的區別.txt
[20230425]注意snapshot standby與activate standby的區別.txt
--//同事想使用dg做一些讀寫測試,選擇的是alter database activate standby database,因為這樣操作沒有建立保證閃回點,導致切換
--//回去後,無法接受主庫的產生日誌.導致必須重新建立dg資料庫,特別提醒自己以後工作注意這個細節問題.
--//我以前寫過一篇
--//[20170302]關於alter database convert to snapshot standby.txt.
--//[20170301]關於alter database activate standby database.txt
--//實際上這個問題非常容易混淆,如果使用activate standby,要返回應用日誌,必須要建立一個保證儲存點或者開啟閃回功能.
--//在這個問題非常容易搞混!!而alter database convert to snapshot standby;執行後自動建立保證閃回點.
--//實際上在10g之前僅僅支援activate standby database,11g後加入了Snapshot Standby Database的新特性，也許我可能記憶發生錯
--//誤!!其實Snapshot Standby Database也就是上面10g功能的一個包裝而已，唯 一不同的是在轉換為讀寫模式後依舊可以繼續接受主庫
--//過來的歸檔日誌。也就是activate standby database僅僅與Snapshot Standby Database相似.
--//建議不要再使用activate standby方式.另外建議dg建立後馬上開啟flashback 功能,避免主庫出現業務操作錯誤時,可以利用閃回功
--//能,恢復丟失的資料或者操作錯誤.另外建議使用dgmgrl管理這類操作,減少不必要的錯誤.
--//簡單記錄一些操作過程.
--//convert to snapshot standby步驟如下:
alter database recover managed standby database cancel;
alter database convert to snapshot standby;
alter database open ;
--//convert to physical standby ;
shutdown immediate;
startup mount
alter database convert to physical standby ;
--//convert to activate standby步驟如下:
alter database recover managed standby database cancel;
create restore point beforetest guarantee flashback database;
alter database activate standby database;
alter database open ;
--//convert to physical standby ;
shutdown immediate;
startup mount
flashback database to restore point beforetest;
alter database convert to physical standby ;
--//drop restore point beforetest;
--//順便使用我的測試庫做一些測試說明問題,千萬不要拿生產系統的資料庫做這類測試!!
1.環境:
SYS@book> @ ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
--//簡單說明一下,我的測試環境本來存在一個dg,機器給淘汰了.現在沒有dg環境.不過我應該能模擬同事的操作錯誤.
2.測試1:
SYS@book> alter database convert to physical standby ;
alter database convert to physical standby
*
ERROR at line 1:
ORA-01126: database must be mounted in this instance and not open in any instance
--//必須在mount狀態執行.
SYS@book> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@book> startup mount
ORACLE instance started.
Total System Global Area 643084288 bytes
Fixed Size 2255872 bytes
Variable Size 205521920 bytes
Database Buffers 427819008 bytes
Redo Buffers 7487488 bytes
Database mounted.
SYS@book> select * from v$restore_point ;
no rows selected
SYS@book> alter database convert to physical standby ;
Database altered.
--//資料庫已經轉換為physical standby.
SYS@book> select * from v$restore_point ;
select * from v$restore_point
*
ERROR at line 1:
ORA-01507: database not mounted
SYS@book> shutdown immediate ;
ORA-01507: database not mounted
ORACLE instance shut down.
SYS@book> startup mount
ORACLE instance started.
Total System Global Area 643084288 bytes
Fixed Size 2255872 bytes
Variable Size 205521920 bytes
Database Buffers 427819008 bytes
Redo Buffers 7487488 bytes
Database mounted.
SYS@book> select * from v$restore_point ;
no rows selected
SYS@book> select CONTROLFILE_TYPE,DATABASE_ROLE from v$database;
CONTROL DATABASE_ROLE
------- ----------------
STANDBY PHYSICAL STANDBY
--//已經是PHYSICAL STANDBY資料庫
RMAN> list incarnation;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 BOOK 1337401710 PARENT 1 2013-08-24 11:37:30
2 2 BOOK 1337401710 CURRENT 925702 2015-11-24 09:11:12
RMAN> list restore point all;
SCN RSP Time Type Time Name
---------------- ------------------- ---------- ------------------- ----
SYS@book> alter database activate standby database;
Database altered.
SYS@book> select * from v$restore_point ;
no rows selected
--//activate standby database後並沒有建立儲存點.
SYS@book> select CONTROLFILE_TYPE,DATABASE_ROLE,open_mode from v$database;
CONTROL DATABASE_ROLE OPEN_MODE
------- ---------------- --------------------
CURRENT PRIMARY MOUNTED
--//資料庫變成PRIMARY.
RMAN> list incarnation;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 BOOK 1337401710 PARENT 1 2013-08-24 11:37:30
2 2 BOOK 1337401710 PARENT 925702 2015-11-24 09:11:12
3 3 BOOK 1337401710 CURRENT 13277830768 2023-04-27 10:56:28
--//建立新的incarnation.
RMAN> list restore point all;
SCN RSP Time Type Time Name
---------------- ------------------- ---------- ------------------- ----
--//這樣open開啟後無法返回舊的incarnation,繼續接收redo日誌的.
--//繼續操作:
SYS@book> alter database convert to physical standby ;
alter database convert to physical standby
*
ERROR at line 1:
ORA-16433: The database must be opened in read/write mode.
SYS@book> alter database open ;
Database altered.
SYS@book> alter database convert to physical standby ;
alter database convert to physical standby
*
ERROR at line 1:
ORA-01126: database must be mounted in this instance and not open in any instance
--//奇怪!!
SYS@book> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@book> startup mount
ORACLE instance started.
Total System Global Area 643084288 bytes
Fixed Size 2255872 bytes
Variable Size 205521920 bytes
Database Buffers 427819008 bytes
Redo Buffers 7487488 bytes
Database mounted.
SYS@book> select CONTROLFILE_TYPE,DATABASE_ROLE,open_mode from v$database;
CONTROL DATABASE_ROLE OPEN_MODE
------- ---------------- --------------------
CURRENT PRIMARY MOUNTED
SYS@book> alter database convert to physical standby ;
Database altered.
SYS@book> shutdown immediate ;
ORA-01507: database not mounted
ORACLE instance shut down.
SYS@book> startup mount
ORACLE instance started.
Total System Global Area 643084288 bytes
Fixed Size 2255872 bytes
Variable Size 205521920 bytes
Database Buffers 427819008 bytes
Redo Buffers 7487488 bytes
Database mounted.
SYS@book> select CONTROLFILE_TYPE,DATABASE_ROLE,open_mode from v$database;
CONTROL DATABASE_ROLE OPEN_MODE
------- ---------------- --------------------
STANDBY PHYSICAL STANDBY MOUNTED
RMAN> list incarnation;
using target database control file instead of recovery catalog
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 BOOK 1337401710 PARENT 1 2013-08-24 11:37:30
2 2 BOOK 1337401710 PARENT 925702 2015-11-24 09:11:12
3 3 BOOK 1337401710 CURRENT 13277830768 2023-04-27 10:56:28
--//當前的Incarnation已經是新的Incarnations,無法返回舊的Incarnations.
--//實際上你可以理解為alter database activate standby database;強制拉起資料庫為主庫模式.
--//要想返回必須建立閃回儲存點或者開啟閃回日誌.
3.測試2:
--//使用冷備份恢復.過程略.
SYS@book> startup mount
ORACLE instance started.
Total System Global Area 643084288 bytes
Fixed Size 2255872 bytes
Variable Size 205521920 bytes
Database Buffers 427819008 bytes
Redo Buffers 7487488 bytes
Database mounted.
SYS@book> select CONTROLFILE_TYPE,DATABASE_ROLE,open_mode from v$database;
CONTROL DATABASE_ROLE OPEN_MODE
------- ---------------- --------------------
CURRENT PRIMARY MOUNTED
SYS@book> alter database convert to physical standby ;
Database altered.
SYS@book> shutdown immediate ;
ORA-01507: database not mounted
ORACLE instance shut down.
SYS@book> startup mount
ORACLE instance started.
Total System Global Area 643084288 bytes
Fixed Size 2255872 bytes
Variable Size 205521920 bytes
Database Buffers 427819008 bytes
Redo Buffers 7487488 bytes
Database mounted.
SYS@book> select CONTROLFILE_TYPE,DATABASE_ROLE,open_mode from v$database;
CONTROL DATABASE_ROLE OPEN_MODE
------- ---------------- --------------------
STANDBY PHYSICAL STANDBY MOUNTED
SYS@book> select * from v$restore_point ;
no rows selected
SYS@book> select * from v$restore_point ;
no rows selected
SYS@book> alter database convert to snapshot standby;
Database altered.
SYS@book> set numw 12
SYS@book> select * from v$restore_point ;
SCN DATABASE_INCARNATION# GUA STORAGE_SIZE TIME RESTORE_POINT_TIME PRE NAME
------------ --------------------- --- ------------ ----------------------------- ------------------ --- ---------------------------------------------
13276934081 2 YES 52428800 2023-04-27 11:15:24.000000000 YES SNAPSHOT_STANDBY_REQUIRED_04/27/2023 11:15:24
--//可以發現自動建立一個保證儲存點.
RMAN> list incarnation;
using target database control file instead of recovery catalog
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 BOOK 1337401710 PARENT 1 2013-08-24 11:37:30
2 2 BOOK 1337401710 PARENT 925702 2015-11-24 09:11:12
3 3 BOOK 1337401710 CURRENT 13276934083 2023-04-27 11:15:25
RMAN> list restore point all;
SCN RSP Time Type Time Name
---------------- ------------------- ---------- ------------------- ----
13276934081 GUARANTEED 2023-04-27 11:15:24 SNAPSHOT_STANDBY_REQUIRED_04/27/2023 11:15:24
SYS@book> alter database open ;
Database altered.
--//一些DML操作省略....
SYS@book> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@book> startup mount
ORACLE instance started.
Total System Global Area 643084288 bytes
Fixed Size 2255872 bytes
Variable Size 205521920 bytes
Database Buffers 427819008 bytes
Redo Buffers 7487488 bytes
Database mounted.
SYS@book> select CONTROLFILE_TYPE,DATABASE_ROLE,open_mode from v$database;
CONTROL DATABASE_ROLE OPEN_MODE
------- ---------------- --------------------
CURRENT SNAPSHOT STANDBY MOUNTED
--//DATABASE_ROLE =SNAPSHOT STANDBY.
SYS@book> alter database convert to physical standby ;
Database altered.
SYS@book> select CONTROLFILE_TYPE,DATABASE_ROLE,open_mode from v$database;
select CONTROLFILE_TYPE,DATABASE_ROLE,open_mode from v$database
*
ERROR at line 1:
ORA-01507: database not mounted
SYS@book> shutdown immediate ;
ORA-01507: database not mounted
ORACLE instance shut down.
SYS@book> startup mount
ORACLE instance started.
Total System Global Area 643084288 bytes
Fixed Size 2255872 bytes
Variable Size 205521920 bytes
Database Buffers 427819008 bytes
Redo Buffers 7487488 bytes
Database mounted.
SYS@book> select CONTROLFILE_TYPE,DATABASE_ROLE,open_mode from v$database;
CONTROL DATABASE_ROLE OPEN_MODE
------- ---------------- --------------------
STANDBY PHYSICAL STANDBY MOUNTED
RMAN> list incarnation;
using target database control file instead of recovery catalog
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 BOOK 1337401710 PARENT 1 2013-08-24 11:37:30
2 2 BOOK 1337401710 CURRENT 925702 2015-11-24 09:11:12
3 3 BOOK 1337401710 ORPHAN 13276934083 2023-04-27 11:15:25
--//你可以發現切換為STANDBY後,使用incarnation是DB Key=2那行,也就是自動閃回到儲存點位置.
SYS@book> select current_scn from v$database;
CURRENT_SCN
------------
13276934081
--//當前的scn是13276934081,就是rman下list restore point all;顯示的scn.
--//寫的有點亂,還是基本能說明問題,收尾略.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2949170/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Convert a Physical Standby Database into a Snapshot Standby DatabaseDatabase
- 物理standby和邏輯standby的區別
- Physical Standby Database 切換到 Snapshot Standby DatabaseDatabase
- 【DG】之 Snapshot standby模式模式
- snapshot standby快照備庫角色
- Data Guard - Snapshot Standby Database配置Database
- Oracle 9i Logical Standby與Physical standby歸檔恢復區別Oracle
- [20170302]關於activate standby databaseDatabase
- 恢復備庫 activate standby database 報錯找不到standby redo - ORA-00313Database
- 11g Dataguard中的snapshot standby特性
- oracle 11g中的snapshot standby特性Oracle
- 聊聊Oracle 11g的Snapshot Standby Database（上）OracleDatabase
- 聊聊Oracle 11g的Snapshot Standby Database（下）OracleDatabase
- Data Guard之Snapshot Standby資料庫功能[轉]資料庫
- oracle snapshot standby資料庫的scheduler jobs不執行Oracle資料庫
- 通過Snapshot Standby來精確評估SQL效能SQL
- 【DataGuard】Oracle 11g DataGuard 新特性之 Snapshot Standby DatabaseOracleDatabase
- Oracle DG Standby Database型別OracleDatabase型別
- 物理Standby資料庫及邏輯Standby資料庫（Physical Standby & Logical Standby）資料庫
- standby_archive_dest和log_archive_dest_n區別Hive
- 【DATAGUARD】 將11g物理備庫轉換為Snapshot Standby
- 11gR2 Active Data Guard 閃回 - flashback database / snapshot standby - 3Database
- 11gR2 Active Data Guard 閃回 - flashback database / snapshot standby - 2Database
- 11g Using Snapshot Standby Database. (文件 ID 443720.1)Database
- 在oracle 10g中實現oracle 11g的snapshot standby特性Oracle 10g
- standby databaseDatabase
- Oracle的快照standbyOracle
- Oracle 10g 邏輯Standby 建立及注意點Oracle 10g
- 【DataGuard】Oracle 11g物理Data Guard之Snapshot Standby資料庫功能Oracle資料庫
- 邏輯 rac standby和物理 rac standby的switchover 和 failoverAI
- Oracle physical standbyOracle
- Standby Database ---09Database
- 【轉】【DataGuard】Oracle 11g物理Data Guard之Snapshot Standby資料庫功能Oracle資料庫
- standby redo log的理解
- Standby Database的工作原理Database
- Oracle Standby系統管理與維護Oracle
- ORACLE10G 物理standby轉為邏輯standbyOracle
- [20230425]CBO cost與行遷移關係.txt