oracel 运用scn闪回数据库

注意：无备份
1.查看必要参数设置
查看归档是否开启
SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     5
Current log sequence           7
查看flash_recovery_area的大小及位置
SQL> show parameter db_recover
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/app/oracle/flash_recovery                                                        _area
db_recovery_file_dest_size           big integer 3882M
查看是否启用flashback database功能
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
查看能够恢复的最早的时间
SQL> show parameter db_flashback_retention_target
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target        integer     1440
查看force logging
SQL> select force_logging from v$database;
FOR
---
NO

2.修改参数
启动到mount
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area  914440192 bytes
Fixed Size                  2218712 bytes
Variable Size             562038056 bytes
Database Buffers          343932928 bytes
Redo Buffers                6250496 bytes
Database mounted.

开启flashback database功能
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.
出错，想开启flashback database功能，必须要在归档模式下

启用归档、flashback database、force logging
SQL> alter database archivelog;
Database altered.
SQL> alter database flashback on;
Database altered.
SQL> alter database force logging;
Database altered.
SQL> alter database open;
Database altered.
3.查看状态
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     5
Next log sequence to archive   7
Current log sequence           7

SQL> select flashback_on,force_logging from v$database;
FLASHBACK_ON       FOR
------------------ ---
YES                YES
4.查看当前scn
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
    1059370
5.误删除表
SQL> conn scott/tiger
Connected.
SQL> select count(*) from emp;
  COUNT(*)
----------
        14
SQL> drop table emp purge;
Table dropped.
SQL> select count(*) from emp;
select count(*) from emp
                     *
ERROR at line 1:
ORA-00942: table or view does not exist
6.创建一张新表
SQL> create table yss(x int);
Table created.
SQL> insert into yss values(1);
1 row created.
SQL> commit;
Commit complete.
7.启动到mount
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area  914440192 bytes
Fixed Size                  2218712 bytes
Variable Size             562038056 bytes
Database Buffers          343932928 bytes
Redo Buffers                6250496 bytes
Database mounted.
8.闪回数据库
SQL> flashback database to scn 1059370;
Flashback complete.

在这里有两种方式修复数据库
（1）直接alter database open resetlogs
     以这种方式修复后，表yss丢失
（2）先执行alter database open read only
     然后导出表
     之后recover database
     最后导入表
     以这种方式回复后，表yss不会丢失。
9.以read only方式打开数据库
SQL> alter database open read only;
Database altered.
10.导出emp表
[oracle@songs ~]$ exp scott/tiger tables=emp file=/u01/emp.dmp log=/u01/emp.log;
Export: Release 11.2.0.1.0 - Production on Tue Jun 3 15:45:05 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses ZHS16GBK character set (possible charset conversion)
About to export specified tables via Conventional Path ...
. . exporting table                            EMP         14 rows exported
Export terminated successfully without warnings.
11.启动到mount，recover database，然后打开数据库
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area  914440192 bytes
Fixed Size                  2218712 bytes
Variable Size             562038056 bytes
Database Buffers          343932928 bytes
Redo Buffers                6250496 bytes
Database mounted.
SQL> recover database;
Media recovery complete.
SQL> alter database open;
Database altered.
12.查询表
SQL> conn scott/tiger
Connected.
SQL> select * from emp;
select * from emp
              *
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select * from yss;
         X
----------
         1
13.导入表emp
[oracle@songs ~]$ imp scott/tiger full=y file=/u01/emp.dmp log=/u01/iemp.log;
Import: Release 11.2.0.1.0 - Production on Tue Jun 3 15:59:16 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses ZHS16GBK character set (possible charset conversion)
. importing SCOTT's objects into SCOTT
. importing SCOTT's objects into SCOTT
. . importing table                          "EMP"         14 rows imported
About to enable constraints...
Import terminated successfully without warnings.
14.查询emp表
SQL> select count(*) from emp;
  COUNT(*)
----------
        14