[20170302]關於activate standby database
[20170302]關於alter database activate standby database.txt
--//這是很久以前但是關於alter database activate standby database的討論:
--//連結: http://www.itpub.net/thread-2062967-1-1.html
--//ORACLE 11204
--//哪個檢視體現了此 ACTIVATE STANDBY DATABASE 和 普通的 DATABASE 區別？
--//我自己也把lz問的問題與active data guard搞混了.
--//實際上11g中加入了Snapshot Standby Database的新特性，其實也就是上面10g功能的一個包裝而已，唯一不同的是在轉換為讀寫模
--//式後任然可以繼續接受主庫過來的歸檔日誌。也就是activate standby database僅僅與Snapshot Standby Database相似.
--//透過測試說明問題:
1.環境:
SYS@book> @ &r/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環境已經搭建好
2.備庫:
SYS@bookdg> select flashback_on from v$database ;
FLASHBACK_ON
------------------
NO
SYS@bookdg> create restore point beforetest guarantee flashback database;
create restore point beforetest guarantee flashback database
*
ERROR at line 1:
ORA-38784: Cannot create restore point 'BEFORETEST'.
ORA-01153: an incompatible media recovery is active
SYS@bookdg> alter database recover managed standby database cancel;
Database altered.
SYS@bookdg> create restore point beforetest guarantee flashback database;
Restore point created.
SYS@bookdg> alter database activate standby database;
Database altered.
--//看看alert的內容:
alter database activate standby database
ALTER DATABASE ACTIVATE [PHYSICAL] STANDBY DATABASE (bookdg)
Killing 2 processes with pids 1169,1161 (all RFS) in order to disallow current and future RFS connections. Requested by OS process 1112
Begin: Standby Redo Logfile archival
Wed Mar 01 15:49:39 2017
Archived Log entry 11 added for thread 1 sequence 9 ID 0x522677de dest 1:
End: Standby Redo Logfile archival
RESETLOGS after complete recovery through change 13276933946
Archived Log entry 12 added for thread 1 sequence 695 ID 0x4fb7d86e dest 1:
Archived Log entry 13 added for thread 1 sequence 10 ID 0x522677de dest 1:
Resetting resetlogs activation ID 1378252766 (0x522677de)
Online log /mnt/ramdisk/book/redo01.log: Thread 1 Group 1 was previously cleared
Online log /mnt/ramdisk/book/redo02.log: Thread 1 Group 2 was previously cleared
Online log /mnt/ramdisk/book/redo03.log: Thread 1 Group 3 was previously cleared
Standby became primary SCN: 13276933944
Wed Mar 01 15:49:44 2017
Setting recovery target incarnation to 4
ACTIVATE STANDBY: Complete - Database mounted as primary
Completed: alter database activate standby database
Wed Mar 01 15:49:57 2017
ARC0: Becoming the 'no SRL' ARCH
SYS@bookdg> select * from v$database_incarnation ;
INCARNATION# RESETLOGS_CHANGE# RESETLOGS_TIME PRIOR_RESETLOGS_CHANGE# PRIOR_RESETLOGS_TIM STATUS RESETLOGS_ID PRIOR_INCARNATION# FLASHBACK_DATABASE_ALLOWED
------------ ----------------- ------------------- ----------------------- ------------------- ------- ------------ ------------------ --------------------------
1 1 2013-08-24 11:37:30 0 PARENT 824297850 0 NO
2 925702 2015-11-24 09:11:12 1 2013-08-24 11:37:30 PARENT 896605872 1 NO
3 13276911100 2017-03-01 10:49:10 925702 2015-11-24 09:11:12 PARENT 937478950 2 YES
4 13276933947 2017-03-01 15:49:42 13276911100 2017-03-01 10:49:10 CURRENT 937496982 3 YES
--//可以發生生成新的RESETLOGS_CHANGE#.
SYS@bookdg> select * from v$restore_point ;
SCN DATABASE_INCARNATION# GUA STORAGE_SIZE TIME RESTORE_POINT_TIME PRE NAME
------------ --------------------- --- ------------ --------------------------------- ------------------ --- ----------
13276933945 3 YES 52428800 01-MAR-17 03.49.18.000000000 PM YES BEFORETEST
SYS@bookdg> select database_role from v$database;
DATABASE_ROLE
----------------
PRIMARY
SYS@bookdg> alter database open ;
Database altered.
SYS@bookdg> alter system archive log current ;
System altered.
--//看看歸檔日誌的生成情況:
$ ls -ltr /u01/app/oracle/archivelog/book
total 18296
-rw-r----- 1 oracle oinstall 218624 2017-03-01 15:42:28 1_2_937478950.dbf
-rw-r----- 1 oracle oinstall 60416 2017-03-01 15:42:30 1_3_937478950.dbf
-rw-r----- 1 oracle oinstall 16752640 2017-03-01 15:42:33 1_4_937478950.dbf
-rw-r----- 1 oracle oinstall 133120 2017-03-01 15:42:35 1_5_937478950.dbf
-rw-r----- 1 oracle oinstall 408576 2017-03-01 15:42:38 1_6_937478950.dbf
-rw-r----- 1 oracle oinstall 70144 2017-03-01 15:42:40 1_7_937478950.dbf
-rw-r----- 1 oracle oinstall 541696 2017-03-01 15:43:40 1_1_937478950.dbf
-rw-r----- 1 oracle oinstall 84480 2017-03-01 15:43:51 1_8_937478950.dbf
-rw-r----- 1 oracle oinstall 58880 2017-03-01 15:49:39 1_9_937478950.dbf
-rw-r----- 1 oracle oinstall 79360 2017-03-01 15:49:43 1_695_896605872.dbf
-rw-r----- 1 oracle oinstall 165888 2017-03-01 15:49:43 1_10_937478950.dbf
-rw-r----- 1 oracle oinstall 1024 2017-03-01 15:53:00 1_1_937496982.dbf
-rw-r----- 1 oracle oinstall 69120 2017-03-01 15:53:02 1_2_937496982.dbf
--//注意看最後2個檔案就是當前的歸檔.如何可以看出這個資料庫是從ACTIVATE STANDBY DATABASE呢?這裡並沒有給出答案....
3.觀察:
SYS@bookdg> @ &r/vb
----oracle 11gr2 and Oracle 12c<<<<<<<<<<<<<<<
*********************************************
* D A T A B A S E I N F O R M A T I O N
*********************************************
COLUMN1 COLUMN2
---------------------------------------------------------------------- ------------------------------------------------------------
[DB Info] [DataGuard Information]
DB ID : 1337401710 Primary DB Unique Name : BOOK
DB Name : BOOK DataGuard Role : PRIMARY
DB Unique Name : bookdg Protection Mode : MAXIMUM PERFORMANCE
Platform : Linux x86 64-bit Protection Level : MAXIMUM PERFORMANCE
DB Created : 2015-11-24 09:11:10 DataGuard Broker : DISABLED
Open Mode : READ WRITE DataGuard Status : NONE
Open Resetlogs : NOT ALLOWED SwitchOver Status : FAILED DESTINATION
Flashback ON : RESTORE POINT ONLY Activation SCN : 1378293768
ArchiveLog Mode : ARCHIVELOG SwitchOver SCN : 1378293768
ArchiveLog Compression : DISABLED Standby Became Primary SCN : 13276933944
Force Logging : YES Supplemental Log Data MIN : NO
Remote Archive : ENABLED Supplemental Log Data PK : NO
Last Open Incarnation# : 4 Supplemental Log Data UI : NO
Recovery Target Inc# : 4 Supplemental Log Data PL : NO
[Timestamps] [Fast Start Failover Info]
DB Created : 2015-11-24 09:11:10 FS Failover Status : DISABLED
Controlfile Created : 2015-11-24 09:11:10 FS Failover Current Target :
Controlfile Time : 2017-03-01 15:53:03 FS Failover Threshold : 0
Version Time : 2015-11-24 09:11:10 FS Failover Observer Present:
Resetlogs Time : 2017-03-01 15:49:42 FS Failover Observer Host :
Prior Resetlogs Time : 2017-03-01 10:49:10
[System Change Number]
Current SCN : 13276934363
Resetlogs SCN : 13276933947
Prior Resetlogs SCN : 13276911100
Checkpoint SCN : 13276933951
Controlfile SCN : 13276934323
Archivelog Highest NextSCN : 13276934319
Force Archivelog SCN : 13276934315
Archivelog SCN : 13276934315
Standby Became Primary SCN : 13276933944
[Controlfile Info]
Controlfile Type : CURRENT
Controlfile Created : 2015-11-24 09:11:10
Controlfile Converted : NO
Controlfile SCN : 13276934323
Controlfile Sequence# : 937497026
Controlfile Time : 2017-03-01 15:53:03
--//說明:指令碼查詢的v$database.僅僅從Standby Became Primary欄位可以看出來.也就是standby_became_primary_scn欄位與database_role兩個欄位看出來.
--//來自activate standby database;.
4.看看是否啟用日誌傳輸:
SYS@bookdg> alter database recover managed standby database using current logfile disconnect ;
alter database recover managed standby database using current logfile disconnect
*
ERROR at line 1:
ORA-01665: control file is not a standby control file
--//這個也是activate standby database與Snapshot Standby Database的不同,Snapshot Standby Database日誌可以繼續傳輸並不應用.而
--//activate standby database沒有這個功能.
5.還原:
SYS@bookdg> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@bookdg> select * from v$flashback_database_log;
OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TI RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- ------------------- ---------------- -------------- ------------------------
13276933945 2017-03-01 15:49:10 1440 104857600 0
SYS@bookdg> flashback database to restore point beforetest;
Flashback complete.
SYS@bookdg> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name FROM v$datafile_header where file#=1;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ NAME TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- -------------------------------------------------- ------------------------------
1 13276933946 2017-03-01 15:49:11 7 13276911100 ONLINE 1 NO /mnt/ramdisk/book/system01.dbf SYSTEM
--//注意實際上scn=13276933945+1.
SYS@bookdg> flashback database to scn 13276933944;
flashback database to scn 13276933944
*
ERROR at line 1:
ORA-38726: Flashback database logging is not on.
SYS@bookdg> alter database convert to physical standby ;
Database altered.
SYS@bookdg> select open_mode from v$database;
select open_mode from v$database
*
ERROR at line 1:
ORA-01507: database not mounted
--//資料已經不再mount狀態.
SYS@bookdg> alter database mount standby database ;
alter database mount standby database
*
ERROR at line 1:
ORA-00750: database has been previously mounted and dismounted
SYS@bookdg> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SYS@bookdg> startup mount
ORACLE instance started.
Total System Global Area 634732544 bytes
Fixed Size 2255792 bytes
Variable Size 197133392 bytes
Database Buffers 427819008 bytes
Redo Buffers 7524352 bytes
Database mounted.
SYS@bookdg> select * from v$database_incarnation ;
INCARNATION# RESETLOGS_CHANGE# RESETLOGS_TIME PRIOR_RESETLOGS_CHANGE# PRIOR_RESETLOGS_TIM STATUS RESETLOGS_ID PRIOR_INCARNATION# FLASHBACK_DATABASE_ALLOWED
------------ ----------------- ------------------- ----------------------- ------------------- ------- ------------ ------------------ --------------------------
1 1 2013-08-24 11:37:30 0 PARENT 824297850 0 NO
2 925702 2015-11-24 09:11:12 1 2013-08-24 11:37:30 PARENT 896605872 1 NO
3 13276911100 2017-03-01 10:49:10 925702 2015-11-24 09:11:12 CURRENT 937478950 2 YES
4 13276933947 2017-03-01 15:49:42 13276911100 2017-03-01 10:49:10 ORPHAN 937496982 3 YES
--//現在還在INCARNATION#=3.舊的incarnation#不會清除.
--//主庫執行:
SYS@book> alter system set log_archive_dest_state_2=defer scope=memory;
System altered.
SYS@book> alter system set log_archive_dest_state_2=enable scope=memory;
System altered.
SYS@bookdg> alter database recover managed standby database using current logfile disconnect ;
Database altered.
SYS@bookdg> @ &r/dg/dg
PROCESS PID STATUS CLIENT_P GROUP# THREAD# SEQUENCE# BLOCK# BLOCKS DELAY_MINS
--------- ------- ------------ -------- ------ ------- ------------ ------------ ------------ ------------
RFS 1425 IDLE UNKNOWN N/A 0 0 0 0 0
RFS 1427 IDLE LGWR 1 1 13 2 2 0
ARCH 1394 CLOSING ARCH 4 1 12 1 159 0
MRP0 1413 WAIT_FOR_LOG N/A N/A 1 12 0 0 0
--//最後記住不要忘記清除儲存點,不然閃回區會撐爆.
SYS@bookdg> drop restore point beforetest;
Restore point dropped.
SYS@bookdg> @ &r/vb
----oracle 11gr2 and Oracle 12c<<<<<<<<<<<<<<<
*********************************************
* D A T A B A S E I N F O R M A T I O N
*********************************************
COLUMN1 COLUMN2
---------------------------------------------------------------------- ------------------------------------------------------------
[DB Info] [DataGuard Information]
DB ID : 1337401710 Primary DB Unique Name : BOOK
DB Name : BOOK DataGuard Role : PHYSICAL STANDBY
DB Unique Name : bookdg Protection Mode : MAXIMUM PERFORMANCE
Platform : Linux x86 64-bit Protection Level : MAXIMUM PERFORMANCE
DB Created : 2015-11-24 09:11:10 DataGuard Broker : DISABLED
Open Mode : MOUNTED DataGuard Status : NONE
Open Resetlogs : ALLOWED SwitchOver Status : NOT ALLOWED
Flashback ON : NO Activation SCN : 1378252766
ArchiveLog Mode : ARCHIVELOG SwitchOver SCN : 1378252766
ArchiveLog Compression : DISABLED Standby Became Primary SCN : 0
Force Logging : YES Supplemental Log Data MIN : NO
Remote Archive : ENABLED Supplemental Log Data PK : NO
Last Open Incarnation# : 4 Supplemental Log Data UI : NO
Recovery Target Inc# : 3 Supplemental Log Data PL : NO
[Timestamps] [Fast Start Failover Info]
DB Created : 2015-11-24 09:11:10 FS Failover Status : DISABLED
Controlfile Created : 2015-11-24 09:11:10 FS Failover Current Target :
Controlfile Time : 2017-03-01 16:18:57 FS Failover Threshold : 0
Version Time : 2015-11-24 09:11:10 FS Failover Observer Present:
Resetlogs Time : 2017-03-01 10:49:10 FS Failover Observer Host :
Prior Resetlogs Time : 2015-11-24 09:11:12
[System Change Number]
Current SCN : 13276936162
Resetlogs SCN : 13276911100
Prior Resetlogs SCN : 925702
Checkpoint SCN : 13276935041
Controlfile SCN : 13276936163
Archivelog Highest NextSCN : 13276936315
Force Archivelog SCN : 13276934315
Archivelog SCN : 13276934315
Standby Became Primary SCN : 0
[Controlfile Info]
Controlfile Type : STANDBY
Controlfile Created : 2015-11-24 09:11:10
Controlfile Converted : NO
Controlfile SCN : 13276936163
Controlfile Sequence# : 937497140
Controlfile Time : 2017-03-01 16:18:57
6.總結:
--//看來看去就是Standby Became Primary SCN : 0,其他還真看不出來.測試Snapshot Standby Database看看.
--//ACTIVATE STANDBY DATABASE就是透過建立閃回功能,建議儲存點,生成新的incarnation.缺點就是主庫日誌不能傳輸.
--//11g中加入了Snapshot Standby Database的新特性，其實也就是上面10g功能的一個包裝而已，唯一不同的是在轉換為讀寫模
--//式後任然可以繼續接受主庫過來的歸檔日誌。我覺得不再建議使用ACTIVATE STANDBY DATABASE模式.
--//另外記住在執行前一定建立儲存點或者開啟flashback on功能,不然回不去^_^.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2134546/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 恢復備庫 activate standby database 報錯找不到standby redo - ORA-00313Database
- standby databaseDatabase
- Convert a Physical Standby Database into a Snapshot Standby DatabaseDatabase
- standby database to primary database.Database
- Physical Standby Database 切換到 Snapshot Standby DatabaseDatabase
- Standby Database ---09Database
- standby database三種模式的相互關係Database模式
- [20230425]注意snapshot standby與activate standby的區別.txt
- Create RAC Standby Database for RAC Primary DatabaseDatabase
- standby database No RFS 程式Database
- Rman backup standby databaseDatabase
- Standby Database for reportDatabase
- 關於flashback databaseDatabase
- ORA-01679: database must be mounted EXCLUSIVE and not open to activateDatabase
- Standby Database的工作原理Database
- 建立 Logical Standby DatabaseDatabase
- manage logical standby databaseDatabase
- How a Standby Database Is Mounted (295)Database
- Setup Standby Database on One PC(轉)Database
- Oracle DG Standby Database型別OracleDatabase型別
- Oracle DG建立Physical Standby DatabaseOracleDatabase
- Creating a Physical Standby DatabaseDatabase
- rman 建 Standby Database 筆記Database筆記
- （轉）Standby Database的工作原理Database
- 監控Logical standby databaseDatabase
- 11g 新特性—— Active Database Duplication for A standby databaseDatabase
- Postgresql 關於級聯hot-standbySQL
- Performing a Failover to a Physical Standby DatabaseORMAIDatabase
- Oracle DG建立Logical Standby DatabaseOracleDatabase
- Data Guard - Snapshot Standby Database配置Database
- 物理standby database的日常維護Database
- PROTECTION_MODE is UNPROTECTED at standby database 分析Database
- standby database -- Compatibility and Operational RequirementsDatabaseUIREM
- 關於Oracle Database Vault介紹OracleDatabase
- 關於建立DataGuard Physical Standby資料庫資料庫
- Oracle10G Physical Standby Database setupOracleDatabase
- Logical Standby Database的配置步驟.Database
- Performing a Switchover to a Physical Standby Database and failoverORMDatabaseAI