[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
- [20180423]表空間閃回與snapshot standby
- [20181113]Logical Standby建立2.txt
- [20230110]sql profile run standby database.txtSQLDatabase
- Oracle DG Standby Database型別OracleDatabase型別
- Oracle的快照standbyOracle
- 2 新增standby masterAST
- Performing a Failover to a Physical Standby DatabaseORMAIDatabase
- 邏輯STANDBY建立中碰到ORA-16146: standby destination control file enqueue unavailableENQAI
- [20230425]CBO cost與行遷移關係.txt
- 主庫到standby報錯解決：Error 12154 received logging on to the standby ORA-12154Error
- 【DG】Data Guard搭建（physical standby）
- Setup Standby Database on One PC(轉)Database
- Oracle DG建立Physical Standby DatabaseOracleDatabase
- Oracle DG建立Logical Standby DatabaseOracleDatabase
- 【等待事件】standby query scn advance事件
- Oracle 19C CBD Active DataGuard Standby passwd file 注意事項 ORA-01017Oracle
- [20211123]sqlplus @與@@的區別.txtSQL
- 判斷standby日誌是否同步primary
- DATAGUARD手記（PRIMARY+2STANDBY）（一）
- DG -- READ ONLY模式開啟物理Standby模式
- DATAGUARD手記（PRIMARY+2STANDBY）（二）
- DataGuard---->物理StandBy的角色切換之switchover
- standby新增檔案錯誤的解決方法
- 含有replication環境的sqlserver切換到standbySQLServer
- Oracle dataguard報錯：Error 1017 received logging on to the standbyOracleError
- +3.3V_Standby和+3.3V_Normal的關係ORM
- Oracle 12.2 How to Generate AWRs in Active Data Guard Standby DatabasesOracleDatabase
- Oracle 12.2 使用Database Link優化Standby Database WorkloadOracleDatabase優化
- Oracle 12.2 physical standby備庫收集AWR報告Oracle
- 10GR2下建立物理standby STEP BY STEP
- 4 Creating a Logical Standby Database 建立邏輯備庫Database
- Hadoop3.2.1 【 HDFS 】原始碼分析 : Standby Namenode解析Hadoop原始碼
- standby_file_management為manual造成dataguard延遲
- Oracle RAC+DG 調整redo/standby log fileOracle
- 邏輯STANDBY負載高，應用緩慢的解決負載
- 邏輯STANDBY上的ORA-00600: internal error code, arguments: [krvtadc], [], [], [], [], []Error
- 搭建windows到linux的oracle 12c physical standby備庫WindowsLinuxOracle