[20170302]关于alter database convert to snapshot standby.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相似.
--//通过测试说明问题建立snapshot standby:
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> alter database convert to snapshot standby;
alter database convert to snapshot standby
*
ERROR at line 1:
ORA-38784: Cannot create restore point 'SNAPSHOT_STANDBY_REQUIRED_03/01/2017 16:34:31'.
ORA-01153: an incompatible media recovery is active
--//在日志传输与应用状态,不能转换到snapshot standby.从提示也可以发现实际上封装了建立存储点的命令.
SYS@bookdg> alter database convert to snapshot standby;
Database altered.
--//看看alert的内容:
Wed Mar 01 16:36:41 2017
alter database convert to snapshot standby
Starting background process RVWR
Wed Mar 01 16:36:42 2017
RVWR started with pid=21, OS id=1436
Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_03/01/2017 16:36:41
Killing 2 processes with pids 1429,1427 (all RFS) in order to disallow current and future RFS connections. Requested by OS process 1397
Begin: Standby Redo Logfile archival
End: Standby Redo Logfile archival
RESETLOGS after complete recovery through change 13276937294
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: 13276937292
Wed Mar 01 16:36:47 2017
Setting recovery target incarnation to 5
CONVERT TO SNAPSHOT STANDBY: Complete - Database mounted as snapshot standby
Completed: alter database convert to snapshot standby
Wed Mar 01 16:36:53 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 ORPHAN 937496982 3 NO
5 13276937295 2017-03-01 16:36:46 13276911100 2017-03-01 10:49:10 CURRENT 937499806 3 YES
--//可以发生生成新的RESETLOGS_CHANGE#=5.
SYS@bookdg> select * from v$restore_point ;
SCN DATABASE_INCARNATION# GUA STORAGE_SIZE TIME RESTORE_POINT_TIME PRE NAME
------------ --------------------- --- ------------ --------------------------------- ------------------ --- -----------------------------------------------
13276937293 3 YES 52428800 01-MAR-17 04.36.41.000000000 PM YES SNAPSHOT_STANDBY_REQUIRED_03/01/2017 16:36:41
SYS@bookdg> select database_role from v$database;
DATABASE_ROLE
----------------
SNAPSHOT STANDBY
--//数据库角色发生了变化,变成了SNAPSHOT STANDBY.
--//主库执行:
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> @ &r/dg/dg
PROCESS PID STATUS CLIENT_P GROUP# THREAD# SEQUENCE# BLOCK# BLOCKS DELAY_MINS
--------- ------- ------------ -------- ------ ------- ------------ ------------ ------------ ------------
RFS 1447 IDLE UNKNOWN N/A 0 0 0 0 0
RFS 1449 IDLE LGWR 2 1 14 3 1 0
ARCH 1394 CLOSING ARCH 4 1 13 1 1334 0
--//可以发现进程RFS的BLOCK#在变化.
SYS@bookdg> alter database open ;
Database altered.
SYS@bookdg> @ &r/dg/dg
PROCESS PID STATUS CLIENT_P GROUP# THREAD# SEQUENCE# BLOCK# BLOCKS DELAY_MINS
--------- ------- ------------ -------- ---------------------------------------- ------------ ------------ ------------ ------------ ------------
RFS 1451 IDLE ARCH N/A 0 0 0 0 0
ARCH 1394 CLOSING ARCH 1 1 1 1 107 0
RFS 1449 IDLE LGWR 2 1 14 129 1 0
--//可以发现进程RFS的BLOCK#在变化.也就是即使open状态下,数据依旧可以接收日志.
SYS@bookdg> alter system archive log current ;
System altered.
--//看看归档日志的生成情况:
$ ls -ltr /u01/app/oracle/archivelog/book
total 21388
-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 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
-rw-r----- 1 oracle oinstall 551936 2017-03-01 16:21:07 1_10_937478950.dbf
-rw-r----- 1 oracle oinstall 1902080 2017-03-01 16:21:08 1_11_937478950.dbf
-rw-r----- 1 oracle oinstall 81920 2017-03-01 16:21:10 1_12_937478950.dbf
-rw-r----- 1 oracle oinstall 683520 2017-03-01 16:41:43 1_13_937478950.dbf
-rw-r----- 1 oracle oinstall 55296 2017-03-01 16:43:44 1_1_937499806.dbf
-rw-r----- 1 oracle oinstall 33792 2017-03-01 16:44:59 1_2_937499806.dbf
--//注意看最后2个文件就是当前的归档.
3.观察:
--//如何知道SNAPSHOT STANDBY.查询v$database.database_role就可以知道.
SYS@bookdg> select database_role from v$database;
DATABASE_ROLE
----------------
SNAPSHOT STANDBY
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 : SNAPSHOT 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 : READ WRITE DataGuard Status : NONE
Open Resetlogs : NOT ALLOWED SwitchOver Status : NOT ALLOWED
Flashback ON : RESTORE POINT ONLY Activation SCN : 1378327336
ArchiveLog Mode : ARCHIVELOG SwitchOver SCN : 1378327336
ArchiveLog Compression : DISABLED Standby Became Primary SCN : 13276937292
Force Logging : YES Supplemental Log Data MIN : NO
Remote Archive : ENABLED Supplemental Log Data PK : NO
Last Open Incarnation# : 5 Supplemental Log Data UI : NO
Recovery Target Inc# : 5 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:45:02 FS Failover Threshold : 0
Version Time : 2015-11-24 09:11:10 FS Failover Observer Present:
Resetlogs Time : 2017-03-01 16:36:46 FS Failover Observer Host :
Prior Resetlogs Time : 2017-03-01 10:49:10
[System Change Number]
Current SCN : 13276937890
Resetlogs SCN : 13276937295
Prior Resetlogs SCN : 13276911100
Checkpoint SCN : 13276937298
Controlfile SCN : 13276937708
Archivelog Highest NextSCN : 13276937705
Force Archivelog SCN : 13276937701
Archivelog SCN : 13276937701
Standby Became Primary SCN : 13276937292
[Controlfile Info]
Controlfile Type : CURRENT
Controlfile Created : 2015-11-24 09:11:10
Controlfile Converted : NO
Controlfile SCN : 13276937708
Controlfile Sequence# : 937499852
Controlfile Time : 2017-03-01 16:45:02
--//说明:脚本查询的v$database.从DataGuard Role: SNAPSHOT STANDBY 可以看出来.
4.看看是否激活日志传输:
SYS@bookdg> @ &r/dg/dg
PROCESS PID STATUS CLIENT_P GROUP# THREAD# SEQUENCE# BLOCK# BLOCKS DELAY_MINS
--------- ------- ------------ -------- ------ ------- ------------ ------------ ------------ ------------
RFS 1451 IDLE ARCH N/A 0 0 0 0 0
ARCH 1394 CLOSING ARCH 1 1 1 1 107 0
RFS 1449 IDLE LGWR 2 1 14 709 1 0
-//SNAPSHOT STANDBY,日志可以一直传输.
5.还原:
SYS@bookdg> shutdown immediate;
Database closed.
Database dismounted.
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> alter database convert to physical standby ;
Database altered.
--//查看alert:
Wed Mar 01 16:54:37 2017
alter database convert to physical standby
ALTER DATABASE CONVERT TO PHYSICAL STANDBY (bookdg)
Killing 1 processes with pids 1537 (all RFS) in order to disallow current and future RFS connections. Requested by OS process 1533
Flashback Restore Start
Flashback Restore Complete
Drop guaranteed restore point
Stopping background process RVWR
Deleted Oracle managed file /u01/app/oracle/fast_recovery_area/BOOKDG/flashback/o1_mf_dcf20ts8_.flb
Deleted Oracle managed file /u01/app/oracle/fast_recovery_area/BOOKDG/flashback/o1_mf_dcf20xtt_.flb
Guaranteed restore point dropped
Clearing standby activation ID 1378327336 (0x52279b28)
The primary database controlfile was created using the
'MAXLOGFILES 16' clause.
There is space for up to 13 standby redo logfiles
Use the following SQL commands on the standby database to create
standby redo logfiles that match the primary database:
ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 52428800;
Shutting down archive processes
Archiving is disabled
Wed Mar 01 16:54:42 2017
ARCH shutting down
ARC0: Archival stopped
Completed: alter database convert to physical standby
--//自动找到存储点,然后flashback,在drop restore point.
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 NO
4 13276933947 2017-03-01 15:49:42 13276911100 2017-03-01 10:49:10 ORPHAN 937496982 3 NO
5 13276937295 2017-03-01 16:36:46 13276911100 2017-03-01 10:49:10 ORPHAN 937499806 3 NO
--//现在还在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 1603 IDLE UNKNOWN N/A 0 0 0 0 0
RFS 1601 IDLE LGWR 2 1 17 5 1 0
ARCH 1584 CLOSING ARCH 5 1 16 1 190 0
MRP0 1587 APPLYING_LOG N/A N/A 1 17 5 102400 0
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# : 5 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:55:46 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 : 13276938514
Resetlogs SCN : 13276911100
Prior Resetlogs SCN : 925702
Checkpoint SCN : 13276938385
Controlfile SCN : 13276938515
Archivelog Highest NextSCN : 13276938708
Force Archivelog SCN : 13276937701
Archivelog SCN : 13276937701
Standby Became Primary SCN : 0
[Controlfile Info]
Controlfile Type : STANDBY
Controlfile Created : 2015-11-24 09:11:10
Controlfile Converted : NO
Controlfile SCN : 13276938515
Controlfile Sequence# : 937499960
Controlfile Time : 2017-03-01 16:55:46
6.总结:
--//看来看去就是Standby Became Primary SCN :0.
--//ACTIVATE STANDBY DATABASE就是通过建立闪回功能,建议存储点,生成新的incarnation.缺点就是主库日志不能传输.
--//11g中加入了Snapshot Standby Database的新特性，其实也就是上面10g功能的一个包装而已，唯一不同的是在转换为读写模
--//式后任然可以继续接受主库过来的归档日志。我觉得不再建议使用ACTIVATE STANDBY DATABASE模式.
--//从测试可以看出 STANDBY_BECAME_PRIMARY_SCN与DATABASE_ROLE 结合起来可以判断是使用ACTIVATE STANDBY DATABASE还是snapshot
--//standby database.
--//综合以上来判断:
--//STANDBY_BECAME_PRIMARY_SCN与DATABASE_ROLE结合起来也是就可以判断.
--//ACTIVATE STANDBY DATABASE过来的STANDBY_BECAME_PRIMARY_SCN不是0,DATABASE_ROLE=PRIMARY.
--//snapshot standby database 过来的STANDBY_BECAME_PRIMARY_SCN不是0,DATABASE_ROLE=SNAPSHOT STANDBY.
