生产上要做灾备系统的应急切换演练,灾备端是由OGG搭建的。由于生产库都比较大10多T,不想演练后重新初始化灾备库,生产库版本为10.2.0.4,于是想到10g的新特性flashback database。演练开始前记录一个还原点,演练结束后闪回到这个还原点,重新同步ogg即可。
测试闪加数据库。
一、开启FLASHBACK DATABASE
数据库版本:10.2.0.4
启用FLASHBACK DATABASE数据库必须为ARCHIVELOG模式,还需要有一个闪回恢复区用于存储闪回日志,RAC中闪回恢复区必须在集群文件系统或ASM中。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
|
SQL> select flashback_on,log_mode from v$ database ;
FLASHBACK_ON LOG_MODE
NO ARCHIVELOG
SQL> alter system set db_recovery_file_dest_size=3G scope=spfile;
System altered.
SQL> alter system set db_recovery_file_dest= '/u01/app/oracle/flashback_area/' scope=spfile;
System altered.
SQL> alter system set db_flashback_retention_target=2880 scope=spfile;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 599785472 bytes
Fixed Size 2085776 bytes
Variable Size 192941168 bytes
Database Buffers 398458880 bytes
Redo Buffers 6299648 bytes
Database mounted.
SQL> alter database flashback on ;
Database altered.
SQL> alter database open ;
Database altered.
SQL> select flashback_on,log_mode from v$ database ;
FLASHBACK_ON LOG_MODE
YES ARCHIVELOG
|
官方文档:http://docs.oracle.com/cd/B19306_01/backup.102/b14192/rpfbdb003.htm#sthref509
db_recovery_file_dest_size参数表示闪回恢复区的大小
db_recovery_file_dest参数表示闪回恢复区目录
db_flashback_retention_target表是闪回日志保留的时间,单位为分,默认1440(1天)
查看闪回恢复区里已生成闪回日志
1
2
3
4
|
[oracle@rhel5 flashback_area]$ cd /u01/app/oracle/flashback_area/MYDB/flashback/
[oracle@rhel5 flashback]$ ls -l
total 8020
-rw-r
|
二、创建测试表
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
SQL> conn zx/zx
Connected.
SQL> create table flash_t as select * from all_tables;
Table created.
SQL> insert into flash_t select * from flash_t;
1559 rows created.
SQL> /
3118 rows created.
SQL> commit ;
Commit complete.
SQL> select count (*) from flash_t;
COUNT (*)
6236
|
三、查看数据库可以闪回的时间点
1
2
3
4
5
|
SQL> select * from V$FLASHBACK_DATABASE_LOG;
OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_ RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
7762668 20170109 14:37:32 2880 14467072 590954496
|
四、测试闪回数据库到一个时间点
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
|
SQL> SELECT CURRENT_SCN FROM V$ DATABASE ;
CURRENT_SCN
7765990
SQL> conn / as sysdba
Connected.
SQL> select count (*) from zx.flash_t;
COUNT (*)
6236
SQL> drop table zx.flash_t;
Table dropped.
SQL> select count (*) from zx.flash_t;
select count (*) from zx.flash_t
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 599785472 bytes
Fixed Size 2085776 bytes
Variable Size 192941168 bytes
Database Buffers 398458880 bytes
Redo Buffers 6299648 bytes
Database mounted.
SQL> flashback database to scn 7765990;
Flashback complete.
SQL> alter database open resetlogs;
Database altered.
SQL> select count (*) from zx.flash_t;
COUNT (*)
6236
|
闪回还可以指定时间或还原点
FLASHBACK DATABASE TO RESTORE POINT BEFORE_CHANGES;
FLASHBACK DATABASE TO TIME "TO_DATE('09/20/00','MM/DD/YY')";官方文档:http://docs.oracle.com/cd/B19306_01/backup.102/b14192/flashptr005.htm#BGBDCAFA
本文转自hbxztc 51CTO博客,原文链接:http://blog.51cto.com/hbxztc/1890433,如需转载请自行联系原作者