閃回（關於閃回資料庫）

--整理以前的學習筆記

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/，如需轉載，請註明出處，否則將追究法律責任。