1 开启闪回数据库功能
闪回数据库就是当出现逻辑错误时,可以把整个数据库回退到出错前的时间点。
1.1、闪回原理
闪回数据库是有recover writer (RVWR)后台进程、flashback database log日志和flashback recovery area共同完成。闪回数据库是利用闪回日志实现数据库闪回功能,只要配置开启闪回数据库功能就在flashback recovery area内产生闪回日志。
通过v$database试图查看数据库是否处于归档模式以及是否开启数据库闪回功能。
SYS@INDS SQL> select name,FLASHBACK_ON,LOG_MODE from v$database;
NAME FLASHBACK_ON LOG_MODE
------------------ --------------------------- --------------------
INDS YES ARCHIVELOG
SYS@INDS SQL>
SYS@INDS SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 14
Next log sequence to archive 16
Current log sequence 16
SYS@INDS SQL>
1.2、闪回数据库相关参数
对于没有开启闪回数据库的,需要在mount状态开启数据库闪回功能(归档模式也在mount状态开启),闪回数据库功能必须运行在归档模式下。
与数据库闪回功能相关的参数
SYS@INDS SQL>show parameter db_recover
NAME TYPE VALUE
----------------------------- --------- -------------------------
db_recovery_file_dest string /oracle/database/fast_recovery_area
db_recovery_file_dest_size big integer 4G
SYS@INDS SQL> show parameter flashback
NAME TYPE VALUE
------------------------------ ----------------- ----------------
db_flashback_retention_target integer 1440
db_recovery_file_dest:设置闪回恢复区的目录,它可以存放controlfile\online redo log\archived redo log\flashback log\RMAN backupsets
db_recovery_file_dest_size:指定闪回恢复区在系统文件中的配置大小
db_flashback_retention_target:指定数据库可以闪回的最大时间点,单位是minute
1.3、配置闪回数据库
(1)保存一致性,先关闭数据库
SYS@INDS SQL> shutdown immediate ;
(2)启动到mount阶段
SYS@INDS SQL> startup mount;
(3)启动闪回功能
SYS@INDS SQL> alter database flashback on; --alter database
(4)切换到open阶段
SYS@INDS SQL> alter database open;
1.4、闪回数据库注意事项
一下几种情况下无法使用闪回数据库功能:
1、已恢复或是重建控制文件
2、表空间被删除
3、数据文件以及被缩小尺寸resize
一旦进行了闪回数据库操作,一定在只读模式下(read only)验证时间点或是SCN。最后使用resetlogs open数据库,进行DML操作。
确定最近一次flashback database的时间以及SCN.
SYS@INDS SQL> SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME FROM V$FLASHBACK_DATABASE_LOG;
OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TI
-------------------- -------------------
1180276 2017-04-06 11:32:59
1.5、闪回数据库
1.5.1查询当前scn或是时间点
SYS@INDS SQL> select CURRENT_SCN from v$database;
CURRENT_SCN
-----------
1315518
SYS@INDS SQL> select dbms_flashback.get_system_change_number from dual ;
GET_SYSTEM_CHANGE_NUMBER
------------------------
1315519
SYS@INDS SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as "time" from dual;
time
--------------------------------------
2017-04-08 18:09:31
SCOTT@INDS SQL> select 'drop table '||tname||';' from tab;
'DROPTABLE'||TNAME||';'
-----------------------------------------------------
drop table BONUS;
drop table DEPT;
drop table EMP;
drop table INVOICE;
drop table PRODUCT;
drop table PROGRAMS;
drop table SALGRADE;
我们执行drop table操作;
SCOTT@INDS SQL> select * from tab;
TNAME TABTYPE CLUSTERID
-------------------------------------------- -------------- ----------
BIN$TKX1/Hc/EyPgU2UCqMCQlw==$0 TABLE
BIN$TKX1/Hc6EyPgU2UCqMCQlw==$0 TABLE
BIN$TKX1/Hc9EyPgU2UCqMCQlw==$0 TABLE
BIN$TKX1/HdAEyPgU2UCqMCQlw==$0 TABLE
BIN$TKX1/HdDEyPgU2UCqMCQlw==$0 TABLE
BIN$TKX1/HdEEyPgU2UCqMCQlw==$0 TABLE
DEPT TABLE
1.5.2闪回数据库
RMAN> shutdown immediate
using target database control file instead of recovery catalog
database closed
database dismounted
Oracle instance shut down
RMAN> startup mount;
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 901914624 bytes
Fixed Size 2258480 bytes
Variable Size 293603792 bytes
Database Buffers 599785472 bytes
Redo Buffers 6266880 bytes
RMAN> FLASHBACK DATABASE TO SCN 1315519;
Starting flashback at 2017-04-08 18:22:05
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=133 device type=DISK
starting media recovery
archived log for thread 1 with sequence 16 is already on disk as file /oracle/database/fast_recovery_area/INDS/archivelog/2017_04_08/o1_mf_1_16_dgkf7yxb_.arc
archived log for thread 1 with sequence 17 is already on disk as file /oracle/database/fast_recovery_area/INDS/archivelog/2017_04_08/o1_mf_1_17_dgkfcsz0_.arc
archived log for thread 1 with sequence 18 is already on disk as file /oracle/database/fast_recovery_area/INDS/archivelog/2017_04_08/o1_mf_1_18_dgkg56sz_.arc
media recovery complete, elapsed time: 00:00:03
Finished flashback at 2017-04-08 18:22:11
RMAN>SQL 'ALTER DATABASE OPEN READ ONLY';
sql statement: ALTER DATABASE OPEN READ ONLY
我们必须使数据库处于read only模式,验证我们闪回的数据是否是自己想要的数据库。
1.5.3闪回数据库到错误时间
我们把数据库闪回到一个错误的时间点,如何操作?比如闪回的时间早于或是晚于current_time。
这个时候,我们需要使用recover database命令重新应用redo日志,使数据库处于做新的SCN号 - the most recent SCN.然后在重新闪回数据。
SYS@INDS SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@INDS SQL>
SYS@INDS SQL> startup mount;
ORACLE instance started.
Total System Global Area 901914624 bytes
Fixed Size 2258480 bytes
Variable Size 293603792 bytes
Database Buffers 599785472 bytes
Redo Buffers 6266880 bytes
Database mounted.
SYS@INDS SQL> recover database;
Media recovery complete.
SYS@INDS SQL> alter database open;
Database altered.
SYS@INDS SQL> conn scott/tiger
Connected.
SCOTT@INDS SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------------------------------------ -------------- ----------
BIN$TKX1/Hc/EyPgU2UCqMCQlw==$0 TABLE
BIN$TKX1/Hc6EyPgU2UCqMCQlw==$0 TABLE
BIN$TKX1/Hc9EyPgU2UCqMCQlw==$0 TABLE
BIN$TKX1/HdAEyPgU2UCqMCQlw==$0 TABLE
BIN$TKX1/HdDEyPgU2UCqMCQlw==$0 TABLE
BIN$TKX1/HdEEyPgU2UCqMCQlw==$0 TABLE
DEPT TABLE
7 rows selected.
1.5.4验证完成,resetlogs open
在验证数据库已经恢复到“自己“需要的时间点后,以resetlogs方式打开数据库。
RMAN> SHUTDOWN IMMEDIATE
database closed
database dismounted
Oracle instance shut down
RMAN> STARTUP MOUNT;
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 901914624 bytes
Fixed Size 2258480 bytes
Variable Size 293603792 bytes
Database Buffers 599785472 bytes
Redo Buffers 6266880 bytes
RMAN> ALTER DATABASE OPEN RESETLOGS;
database opened
1.6 To Before resetlogs子句闪回
FLASHBACK DATABASE TO BEFORE RESETLOGS它使把数据库闪回到最近一个使用resetlogs时间操作
使用该子句需要确认下两个参数值,resetlogs_change#、oldest_flashback_scn
SYS@INDS SQL> select resetlogs_change# from v$database;
RESETLOGS_CHANGE#
-----------------
1315521
SYS@INDS SQL> selectoldest_flashback_scn from v$flashback_database_log;
OLDEST_FLASHBACK_SCN
--------------------
1180276
只有V$DATABASE.RESETLOGS_CHANGE# 值大于
V$FLASHBACK_DATABASE_LOG.OLDEST_FLASHBACK_SCN时才可以使用该子句。
最后,以”read only”模式打开数据库并验证,验证正确后重启以”resetlogs open”打开数据库
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/27039319/viewspace-2136849/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/27039319/viewspace-2136849/