闪回数据库

闪回主要有闪回表、闪回查询、闪回数据库(数据来自闪回日志)三种，这里主要了解闪回数据库

闪回数据库的最佳适用场景：当发生Drop table tablename purge或Truncate table或UNDO不足导致无法闪回表，需要找回丢失的表，但是丢失表之后的数据也不能丢， 做闪回数据库到丢失表之前的时刻点，做完后开启到open read only模式下去，去查看是否达到要求，达到后再exp导出丢失的表，再shutdown immediate，再startup mount，再SQL> recover database;，再alter database open，再imp这张丢失的表

很重要的一点：如果执行完flashback database后 还没有open resetlgos，但是后悔执行falshback database了，可以直接recover database再alter database open，这样和重启了一次一样。

Use the FLASHBACK DATABASE statement to return the database to a past time or system change number (SCN). This statement provides a fast alternative to performing  incomplete database recovery.
Following a FLASHBACK DATABASE operation, in order to have write access to the flashed back database, you must reopen it with an ALTER DATABASE OPEN RESETLOGS  statement.
You must have the SYSDBA system privilege. A fast recovery area must have been prepared for the database. The database must have been put in FLASHBACK mode with an  ALTER DATABASE FLASHBACK ON statement unless you are flashing the database back to a guaranteed restore point. The database must be mounted but not open. In addition:
The database must run in ARCHIVELOG mode.
The database must be mounted, but not open, with a current control file. The control file cannot be a backup or re-created. When the database control file is restored  from backup or re-created, all existing flashback log information is discarded.
The database must contain no online tablespaces for which flashback functionality was disabled with the SQL statement ALTER TABLESPACE ... FLASHBACK OFF.

使用FLASHBACK DATABASE语句将数据库返回到过去的时间或系统更改编号（SCN）。 此语句提供了执行不完整数据库恢复的快速替代方法。
在进行FLASHBACK DATABASE操作之后，为了对闪回的数据库进行写入访问，必须使用ALTER DATABASE OPEN RESETLOGS语句重新打开它。
您必须具有SYSDBA系统权限。 必须为数据库准备快速恢复区域。 必须使用ALTER DATABASE FLASHBACK ON语句将数据库置于FLASHBACK模式，除非将数据库闪回到保证还原点。 做闪回操作时 数据库必须在MOUNT状态下，不能在OPEN状态下。此外有如下限制
数据库必须以ARCHIVELOG模式运行。
必须使用当前的控制文件把数据库打开至MOUNT状态而非OPNE状态。 控制文件不能是备份或重新创建。 当数据库控制文件从备份或重新创建恢复时，所有现有的闪回日志信息都将被丢弃。
数据库不包含禁用闪回功能的联机表空间。
上面的MOUNT状态指的是做闪回操作时，数据库必须是MOUNT状态，而不是指Alter database flashback on必须在MOUNT状态下执行，Alter database flashback on可以在MOUNT/OPEN两种状态下执行

V$database.flashback_on值为YES就代表开启了闪回数据库
Select flashback_on from v$database;
Flashback on的前提条件：archivelog模式+db_recovery_file_dest不为空
Archive log list限定的Archive destination可以不是USE_DB_RECOVERY_FILE_DEST

SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38707: Media recovery is not enabled.


开启闪回数据库除了产生归档日志，也产生闪回日志(不是产生一个归档日志就会同时产生一个闪回日志)
闪回日志的两个视图V$FLASHBACK_DATABASE_LOG、V$FLASHBACK_DATABASE_LOGFILE
闪回日志的相关参数db_flashback_retention_target，控制闪回日志存放时间




闪回数据库的一次案例
1、创建test1、test2
2、记录当前时间date
3、drop test1、truncate test2、create test3
4、要求恢复test1、test2的数据，并且不能造成test3丢失。

1、
SQL> conn t1/123456
Connected.
SQL> create table test1 as select * from dba_users;
Table created.
SQL> create table test2 as select * from dba_data_files;
Table created.

2、
[oracle@localhost flashback]$ date
2017年 08月 17日 星期四 15:00:09 CST

3、
[oracle@localhost flashback]$ sqlplus t1/123456
SQL> drop table test1;
Table dropped.
SQL> truncate table test2;
Table truncated.
SQL> create table test3 as select * from dba_tablespaces;
Table created.
SQL> select count(*) from test1;
select count(*) from test1
                     *
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select count(*) from test2;
  COUNT(*)
----------
         0
SQL> select count(*) from test3;
  COUNT(*)
----------
         6

4、
[oracle@localhost flashback]$ sqlplus  / as sysdba
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area  321548288 bytes
Fixed Size                  2252824 bytes
Variable Size             264245224 bytes
Database Buffers           50331648 bytes
Redo Buffers                4718592 bytes
Database mounted.
SQL>flashback database to timestamp to_timestamp('2017-08-17 15:00:09','yyyy-mm-dd hh24:mi:ss');

--expdp会创建job，所以read only状态下是无法expdp的，但是可以使用exp
--闪回后，15:00:09之前的test1、test2回来了，但是之后的test3没有了
SQL> alter database open read only;
Database altered.
SQL> conn t1/123456
Connected.
SQL> select count(*) from test1;
  COUNT(*)
----------
        37
SQL> select count(*) from test2;
  COUNT(*)
----------
         5
SQL> select count(*) from test3;
select count(*) from test3
                     *
ERROR at line 1:
ORA-00942: table or view does not exist

--exp导出test1、test2表数据
[oracle@localhost flashback]$exp t1/123456 file=/home/oracle/test1_2.dmp tables=test1,test2

--关闭数据库启动到mount再recover在open，相当于重启了一次数据库，flashback没有起任何作用
[oracle@localhost ~]$ sqlplus / as sysdba
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area  321548288 bytes
Fixed Size                  2252824 bytes
Variable Size             264245224 bytes
Database Buffers           50331648 bytes
Redo Buffers                4718592 bytes
Database mounted.
SQL> recover database;
Media recovery complete.
SQL> alter database open;
Database altered.
SQL> conn t1/123456
Connected.
SQL> select count(*) from test1;
select count(*) from test1
                     *
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select count(*) from test2;
  COUNT(*)
----------
         0
SQL> select count(*) from test3;
  COUNT(*)
----------
         6

--imp导入test1、test2，这样恢复了test1、test2的数据，test3也没丢失
[oracle@localhost flashback]$imp t1/123456 file=/home/oracle/test1_2.dmp full=y ignore=y
SQL> conn t1/123456
SQL> select count(*) from test1;
  COUNT(*)
----------
        37
SQL> select count(*) from test2;
  COUNT(*)
----------
         5
SQL> select count(*) from test3;
  COUNT(*)
----------
         6





继续上面的步骤，再次闪回到上面第二步即未drop test1之前的时刻点，不用resetlogs看可以吗。答案是不可以这样的。
[oracle@localhost ~]$ sqlplus / as sysdba
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area  321548288 bytes
Fixed Size                  2252824 bytes
Variable Size             264245224 bytes
Database Buffers           50331648 bytes
Redo Buffers                4718592 bytes
Database mounted.
SQL> flashback database to timestamp to_timestamp('2017-08-17 15:00:09','yyyy-mm-dd hh24:mi:ss');
Flashback complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;
Database altered.
SQL> conn t1/123456
Connected.
SQL> select count(*) from test1;
  COUNT(*)
----------
        37
SQL> select count(*) from test2;
  COUNT(*)
----------
         5
SQL> select count(*) from test3;
select count(*) from test3
                     *
ERROR at line 1:
ORA-00942: table or view does not exist

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30126024/viewspace-2143699/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30126024/viewspace-2143699/