[20170302]关于activate standby database

本文涉及的产品
日志服务 SLS,月写入数据量 50GB 1个月
简介: [20170302]关于alter database activate standby database.txt --//这是很久以前但是关于alter database activate standby database的讨论: --//链接: http://www.

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

相关实践学习
【涂鸦即艺术】基于云应用开发平台CAP部署AI实时生图绘板
【涂鸦即艺术】基于云应用开发平台CAP部署AI实时生图绘板
目录
相关文章
|
关系型数据库 数据库 Oracle
ORACLE DG 激活 activate database
--在备库上停止redo apply recover managed standby database cancel; --以只读模式打开数据库 alter database open ...
964 0
|
SQL Oracle 关系型数据库
WARNING: Too Many Parse Errors With error=911 When Running a JDBC Application Connected to an Oracle 19c database
WARNING: Too Many Parse Errors With error=911 When Running a JDBC Application Connected to an Oracle 19c database (
403 2
|
Oracle 关系型数据库
19c 开启Oracle Database Vault
19c 开启Oracle Database Vault
272 1
|
SQL Oracle 关系型数据库
Connect to Autonomous Database Using Oracle Database Tools
Connect to Autonomous Database Using Oracle Database Tools
135 1
|
人工智能 Oracle 关系型数据库
一篇文章弄懂Oracle和PostgreSQL的Database Link
一篇文章弄懂Oracle和PostgreSQL的Database Link
|
Oracle 关系型数据库 Linux
Requirements for Installing Oracle Database/Client 19c on OL8 or RHEL8 64-bit (x86-64) (Doc ID 2668780.1)
Requirements for Installing Oracle Database/Client 19c on OL8 or RHEL8 64-bit (x86-64) (Doc ID 2668780.1)
147 0
|
SQL Oracle 安全
Oracle Database Vault Access Control Components
Oracle Database Vault Access Control Components
101 0
|
Oracle 安全 关系型数据库
What Is Oracle Database Vault?
The Oracle Database Vault security controls protect application data from unauthorized access, and helps you to comply with privacy and regulatory requirements. You can deploy controls to block privileged account access to application data and control sensitive operations inside the database using
90 0
|
Oracle 关系型数据库 Linux
服务器Centos7 静默安装Oracle Database 12.2
服务器Centos7 静默安装Oracle Database 12.2
536 0
|
Oracle 关系型数据库 数据库
windows Oracle Database 19c 卸载教程
打开任务管理器 ctrl+Shift+Esc可以快速打开任务管理器,找到oracle所有服务然后停止。 停止数据库服务 在开始卸载之前,确保数据库服务已经停止。你可以使用以下命令停止数据库服务: net stop OracleServiceORCL Universal Installer 卸载Oracle数据库程序 一般情况运行Oracle自带的卸载程序,如使用Universal Installer 工具卸载。 点击开始菜单找到Oracle,然后点击Oracle安装产品,再点击Universal Installer。 点击之后稍等一会然后会进入进入下图界面,点击卸载产品。 选中要删除的Orac
1279 1

热门文章

最新文章