闪回（关于闪回数据库）

--整理以前的学习笔记

1、闪回数据库的相关配置及确认
SQL> archive log list;--处于archivelog模式下
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/arch
Oldest online log sequence     3
Next log sequence to archive   6
Current log sequence           6
SQL> show parameter recovery_file --确认配置了闪回恢复区

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/flash_recovery_area
db_recovery_file_dest_size           big integer 3852M
SQL> show parameter db_flashback_retention_target --闪回数据库的拟定目标，实际还要看闪回恢复区的大小等

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target        integer     1440
SQL> select flashback_on from v$database;--查询数据库是否处于“闪回数据库”下

FLASHBACK_ON
------------------
NO

SQL> alter database flashback on;--启用“闪回数据库”

Database altered.

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

2、闪回数据库
下面对数据库做一些更改，创建一张表tmp
SQL> conn hr/hr
Connected.
SQL> create table tmp as select * from employees;

Table created.

SQL> select count(*) from tmp;

  COUNT(*)
----------
       107

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY
-------------------
2012-11-22 21:32:06

下面删除tmp这张表
SQL> drop table emp;

Table dropped.

然后把数据库闪回到2012-11-22 21:32:06这个时间点
SQL> flashback database to timestamp to_timestamp('2012-11-22 21:32:06','yyyy-mm-dd hh24:mi:ss');--只能在mount模式下进行flashback
flashback database to timestamp to_timestamp('2012-11-22 21:32:06','yyyy-mm-dd hh24:mi:ss')
*
ERROR at line 1:
ORA-38757: Database must be mounted and not open to FLASHBACK.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area  497995776 bytes
Fixed Size                  1337464 bytes
Variable Size             381683592 bytes
Database Buffers          109051904 bytes
Redo Buffers                5922816 bytes
Database mounted.
SQL> flashback database to timestamp to_timestamp('2012-11-22 21:32:06','yyyy-mm-dd hh24:mi:ss');

Flashback complete.

SQL> alter database open;--闪回数据库之后属于不完全恢复，需要以resetlogs打开数据库
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open read only;

Database altered.

SQL> conn hr/hr
Connected.
SQL> select count(*) from tmp;

  COUNT(*)
----------
       107

SQL> shutdown immediate
ORA-01031: insufficient privileges
SQL> conn /as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area  497995776 bytes
Fixed Size                  1337464 bytes
Variable Size             381683592 bytes
Database Buffers          109051904 bytes
Redo Buffers                5922816 bytes
Database mounted.
SQL> alter database open resetlogs;

Database altered.
--闪回数据库完成

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

转载于:http://blog.itpub.net/26524307/viewspace-1061399/