[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功能,不然回不去^_^.