时间格式nls_date_format的设置

nls_date_format参数用于设置日期显示格式,设置的方式有多种,不同的方式也会带来不同的结果。参数的设置是有优先级的,日期格式的参数设置也不例外。优先级如下(低到高):初始化参数 < 系统环境变量 < 会话级(session)< 函数
下面来演示参数设置的优先级
1.用初始化参数来设置nls_date_format
检查是否设置了nls_date_format环境变量,从下面的结果可知并没有设置

[oracle@oracle11g ~]$ export nls_date_format
SQL>show parameter nls_date_format

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_date_format                      string

修改参数nls_date_format

SQL> alter system set nls_date_format='yyyy-mm-dd hh24:mi:ss' scope=spfile;

System altered.

SQL> shutdow immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  327155712 bytes
Fixed Size                  1273516 bytes
Variable Size             138412372 bytes
Database Buffers          184549376 bytes
Redo Buffers                2920448 bytes
Database mounted.
Database opened.
SQL> show parameter nls_date_format

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_date_format                      string      yyyy-mm-dd hh24:mi:ss

SQL> select sysdate from dual;

SYSDATE
------------
04-FEB-15

在修改参数nls_date_format后,我们查询系统时间并没有以’yyyy-mm-dd hh24:mi:ss’这种格式显式,因为NLS_LANG环境变量同样会影响到nls_date_format参数,只要存在NLS_LANG环境变量,Oracle就会使用环境变量的值(即使nls_date_format),根据上面我们提到的优先级,所以初始化参数文件设置的值会被忽略,但由于环境变量中nls_date_format并没有设置,所以Oracle还是使用了默认的格式。

现在设置NLS_LANG环境变量

[oracle@oracle11g ~]$ export NLS_LANG
[oracle@oracle11g ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Wed Feb 4 10:00:05 2015

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter nls_date_format

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_date_format                      string      yyyy-mm-dd hh24:mi:ss
SQL> select sysdate from dual;

SYSDATE
-------------------
2015-02-04 10:01:26

从上面的结果可以看到当我们去掉环境变量nls_lang后,查询系统时间就是以初始化参数nls_date_format的格式(yyyy-mm-dd hh24:mi:ss)来显示的

2.我们将参数nls_date_format设置为’yyyy-mm-dd hh24:mi:ss’,将环境变量设置为’yy-mm-dd hh24:mi:ss’来观察它们的优先级

[oracle@oracle11g ~]$ export nls_date_format='yy-mm-dd hh24:mi:ss'
[oracle@oracle11g ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Wed Feb 4 09:44:07 2015

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter nls_date_format

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_date_format                      string      yyyy-mm-dd hh24:mi:ss
SQL> select sysdate from dual;

SYSDATE
-----------------
15-02-04 09:44:31

可以看到查询系统时间以是环境变量所设置的格式(’yy-mm-dd hh24:mi:ss’)来显示的。这就说明nls_date_format在环境变量中的设置比初始化参数的优先级高。

3.我们将参数nls_date_format设置为’yyyy-mm-dd hh24:mi:ss’,环境变量设置为’yy-mm-dd hh24:mi:ss’,并在会话级将nls_date_format设置为’dd-mm-yy hh24:mi:ss’

[oracle@oracle11g ~]$ export nls_date_format='yy-mm-dd hh24:mi:ss'
[oracle@oracle11g ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Wed Feb 4 09:44:07 2015

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


SQL> alter session set nls_date_format='dd-mm-yy hh24:mi:ss';

Session altered.

SQL> show parameter nls_date_format

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_date_format                      string      yyyy-mm-dd hh24:mi:ss
SQL> select sysdate from dual;

SYSDATE
-----------------
04-02-15 09:50:01

从上面的结果可以看到查询系统时间是以会话级参数nls_date_format的格式(=’dd-mm-yy hh24:mi:ss’)来显示的,这就说明了nls_date_format参数的优先级为:会话级>环境变量>初始化参数

4.我们将参数nls_date_format设置为’yyyy-mm-dd hh24:mi:ss’,环境变量设置为’yy-mm-dd hh24:mi:ss’,并在会话级将nls_date_format设置为’dd-mm-yy hh24:mi:ss’,使用函数来转换日期格式为’mm-dd-yy hh24:mi:ss’

[oracle@oracle11g ~]$ export nls_date_format='yy-mm-dd hh24:mi:ss'
[oracle@oracle11g ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Wed Feb 4 09:44:07 2015

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


SQL> alter session set nls_date_format='dd-mm-yy hh24:mi:ss';

Session altered.

SQL> show parameter nls_date_format

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_date_format                      string      yyyy-mm-dd hh24:mi:ss

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

TO_CHAR(SYSDATE,'
-----------------
02-04-15 10:11:44

从上面的结果可以看到函数的优先级最高,这就证明了日期格式的优先级为:初始化参数 < 系统环境变量 < 会话级(session)< 函数

scn_to_timestamp ORA-00904

oracle中scn与时间之间可以进行相互转换,朋友的数据库是10.2.0.5,之前通过scn_to_timestamp将scn转换为相对应的时间时一切正常,但今天执行却报错了.

SQL>select to_char(scn_to_timestamp(3111823),'yyyy-mm-dd hh24:mi:ss') from dual;
select to_char(scn_to_timestamp(3111823),'yyyy-mm-dd hh24:mi:ss') from dual;
       *
ERROR at line 1:
ORA-00904: "SCN_TO_TIMESTAMP": invalid identifier

很奇怪,scn_to_timestamp函数的状态通过下面的查询语句来查询

SQL> select owner,object_name,object_type,last_ddl_time,status from dba_objects where object_name='SCN_TO_TIMESTAMP';

OWNER        OBJECT_NAME           OBJECT_TYPE         LAST_DDL_TIME STATUS
------------ --------------------- ------------------- ------------- -------
SYS          SCN_TO_TIMESTAMP      FUNCTION            2014/11/26 12 VALID
PUBLIC       SCN_TO_TIMESTAMP      SYNONYM             2014/11/26 12 VALID

后来我把scn_to_timestamp函数的创建语句重新执行一次。

create or replace function scn_to_timestamp(query_scn IN NUMBER)
return TIMESTAMP
IS EXTERNAL
NAME "ktfexscntot"
WITH CONTEXT
PARAMETERS(context,
           query_scn OCINUMBER,
           RETURN)
LIBRARY DBMS_TRAN_LIB;

在执行完后执行对scn_to_timestamp函数的查询

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

TO_CHAR(SCN_TO_TIMESTAMP(31118
------------------------------
2015-02-03 16:26:27

但是却新创建了一个scn_to_timestamp函数并没有替换掉原来的

SQL> select owner,object_name,object_type,last_ddl_time,status from dba_objects where object_name='SCN_TO_TIMESTAMP';

OWNER       OBJECT_NAME              OBJECT_TYPE         LAST_DDL_TIME STATUS
----------- ------------------------ ------------------- ------------- -------
SYS         SCN_TO_TIMESTAMP         FUNCTION            2014/11/26 12 VALID
PUBLIC      SCN_TO_TIMESTAMP         SYNONYM             2014/11/26 12 VALID
SYS         SCN_TO_TIMESTAMP         FUNCTION            2015/2/3 16:5 VALID

这个问题还没找到原因,也许是oracle的bug,因为我在oracle 10.2.0.4中重新创建scn_to_timestamp函数后,查询dba_objects并没有显示生成的scn_to_timestamp函数

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
Connected as SYS

SQL>
SQL> create or replace function scn_to_timestamp(query_scn IN NUMBER)
  2  return TIMESTAMP
  3  IS EXTERNAL
  4  NAME "ktfexscntot"
  5  WITH CONTEXT
  6  PARAMETERS(context,
  7             query_scn OCINUMBER,
  8             RETURN)
  9  LIBRARY DBMS_TRAN_LIB;
 10  /

Function created

SQL> select owner,object_name,object_type,last_ddl_time,status from dba_objects where object_name='SCN_TO_TIMESTAMP';

OWNER      OBJECT_NAME              OBJECT_TYPE         LAST_DDL_TIME STATUS
---------- ------------------------ ------------------- ------------- -------
SYS        SCN_TO_TIMESTAMP         FUNCTION            2015/2/3 21:2 VALID
PUBLIC     SCN_TO_TIMESTAMP         SYNONYM             2008/4/23 12: INVALID

并且在10.2.0.4中重建scn_to_timestamp函数后,对应的同义词失效了,这才是正确而在10.2.0.5中重建scn_to_timestamp函数后,却产生了一个新的同名对象,且对应的同义仍然为有效状态。

按时间点恢复

按时间点恢复
数据库按时间点恢复,可以从要恢复的目标时间之前的备份中还的数据库,然后使用增量备份和重做日志来将数据库前滚到目标时间点,按时间点恢复也叫不完全恢复因为不使用所有的日志或者不完全恢复对数据库的所有改变。

数据库按时间点恢复所要满足的条件
1.数据库必须运行在archivelog模式下
2.必须要有恢复目标时间点之前所有数据文件的备份和在备份SCN与目标SCN之间所有的归档重做日志

每次以resetlogs选项打开数据库时一个新的数据库incarnation就会被创建。执行open resetlogs操作时就会对当前的联机重做日志文件进行归档。incarnation会将重做日志序列号设置为1,并且指联机重做日志一个新的时间戳。它也会增加incarnation的序号,它被用来唯一标记和识另重做日志流。

incarnation可能存在的几种关系
1.current incarnation是由那个incarnation执行open resetlog操作产生的,那个incarnation就是current incarnation的parent incarnation

2.parent incarnation和它parent incarnation的incarnation就叫作current incarnation的ancestor incarnations

3.如果两个incarnation共享相同的ancestor那么它们就是sibling incarnations

SQL> select * from v$database_incarnation;

INCARNATION# RESETLOGS_CHANGE# RESETLOGS_TI PRIOR_RESETLOGS_CHANGE# PRIOR_RESETL STATUS  RESETLOGS_ID PRIOR_INCARNATION# FLASHBACK_DATABASE_ALLOWED
------------ ----------------- ------------ ----------------------- ------------ ------- ------------ ------------------ --------------------------
           1                 1 30-JUN-05                          0              PARENT     562360180                  0 NO
           2            446075 05-SEP-14                          1 30-JUN-05    PARENT     857466832                  1 NO
           3           2849317 27-JAN-15                     446075 05-SEP-14    PARENT     870102602                  2 NO
           4           2880152 27-JAN-15                    2849317 27-JAN-15    PARENT     870133266                  3 NO
           5           3017109 01-FEB-15                    2880152 27-JAN-15    PARENT     870550288                  4 NO
           6           3041066 01-FEB-15                    3017109 01-FEB-15    PARENT     870563157                  5 NO
           7           3041350 01-FEB-15                    3041066 01-FEB-15    PARENT     870564201                  6 YES
           8           3111834 03-FEB-15                    3041350 01-FEB-15    ORPHAN     870724654                  7 YES
           9           3111834 03-FEB-15                    3041350 01-FEB-15    ORPHAN     870726369                  7 YES
          10           3114665 03-FEB-15                    3041350 01-FEB-15    ORPHAN     870726883                  7 YES
          11           3114664 03-FEB-15                    3041350 01-FEB-15    CURRENT    870729934                  7 YES

11 rows selected.

RMAN> list incarnation;


List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       TEST     2155613261       PARENT  1          30-JUN-05
2       2       TEST     2155613261       PARENT  446075     05-SEP-14
3       3       TEST     2155613261       PARENT  2849317    27-JAN-15
4       4       TEST     2155613261       PARENT  2880152    27-JAN-15
5       5       TEST     2155613261       PARENT  3017109    01-FEB-15
6       6       TEST     2155613261       PARENT  3041066    01-FEB-15
7       7       TEST     2155613261       PARENT  3041350    01-FEB-15
8       8       TEST     2155613261       ORPHAN  3111834    03-FEB-15
9       9       TEST     2155613261       ORPHAN  3111834    03-FEB-15
11      11      TEST     2155613261       CURRENT 3114664    03-FEB-15
10      10      TEST     2155613261       ORPHAN  3114665    03-FEB-15

要执行按时间点恢复需要准备好以下两个条件:
1.决定要恢复到的目标时间,SCN,还原点或者日志序列号。闪回查询,闪回版本查询和闪回事务查询可能帮助你来识别逻辑错误。也可以检查alert.log的信息来帮助你判断恢复的目标时间点。另外也可以判断包含目标SCN的日志序列号然后通过日志进行恢复。例如,查询v$log_history来查看已经归档的日志信息。

SQL> select * from v$log_history;

     RECID      STAMP    THREAD#  SEQUENCE# FIRST_CHANGE# FIRST_TIME   NEXT_CHANGE# RESETLOGS_CHANGE# RESETLOGS_TI
---------- ---------- ---------- ---------- ------------- ------------ ------------ ----------------- ------------
       231  870563592          1          2       3041294 01-FEB-15         3041343           3041066 01-FEB-15
       232  870564201          1          3       3041343 01-FEB-15         3041349           3041066 01-FEB-15
       233  870597597          1          1       3041350 01-FEB-15         3063719           3041350 01-FEB-15
       234  870684680          1          2       3063719 02-FEB-15         3097923           3041350 01-FEB-15
       235  870724659          1          3       3097923 03-FEB-15         3114664           3041350 01-FEB-15
       236  870726371          1          1       3111834 03-FEB-15         3112739           3111834 03-FEB-15
       237  870726883          1          1       3111834 03-FEB-15         3114664           3111834 03-FEB-15
       238  870729935          1          1       3114665 03-FEB-15         3116367           3114665 03-FEB-15
       239  870769788          1          1       3114664 03-FEB-15         3135728           3114664 03-FEB-15

例如,如果你发在上午10点1分一个用户意外删除了一个表空间,那么可以将数据库恢复到上午10点,就是在删除表空间前的时间点。在恢复之后在上午10点之后的所有改变都会丢失.

2.如果使用目标时间表达式代替目标SCN,那么在使用RMAN之前要确保时间格式的环境变量设置合适。
NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
NLS_DATE_FORMAT=’YYYY-MM-DD HH24:MI:SS’

使用current incarnation来执行按时间点恢复
使用current incarnation执行按时间点恢复时是使用的当前版本的控制文件。当执行按时间点恢复时,可以使用set until命令来设置恢复的目标时间,而不用对restotre和recover命令单独设置until子句从而避免出错。这能确保从备份中还原的数据文件的时间戳早于后续的recover操作。

按时间点恢复的过程如下:
我们把scott用户下的表emp中的所有记录删除,并且在删除之前记录了当前系统的SCN,然后执行按时间点恢复来恢复表中的记录。

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    3142264

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

TO_CHAR(SCN_TO_TIME
-------------------
2015-02-04 11:22:29

SQL>  select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------
         1          1          4   52428800          1 NO  CURRENT                3142228 04-FEB-15
         3          1          3   52428800          1 YES INACTIVE               3142176 04-FEB-15
         2          1          2   52428800          1 YES INACTIVE               3135728 04-FEB-15


SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80           800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81          1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81          1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81          2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81          1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81          2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81          2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87          3000                    20
      7839 KING       PRESIDENT            17-NOV-81          5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81          1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87          1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81           950                    30
      7902 FORD       ANALYST         7566 03-DEC-81          3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82          1300                    10

14 rows selected.

SQL> delete from emp;

14 rows deleted.

SQL> commit;

Commit complete.

SQL> select * from emp;

no rows selected

1.连接到目标数据库或恢复目录(如果有),将数据库启动到mount状态:

[oracle@oracle11g ~]$ rman target/

Recovery Manager: Release 10.2.0.5.0 - Production on Wed Feb 4 10:25:34 2015

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database (not started)

RMAN> startup mount

Oracle instance started
database mounted

Total System Global Area     327155712 bytes

Fixed Size                     1273516 bytes
Variable Size                138412372 bytes
Database Buffers             184549376 bytes
Redo Buffers                   2920448 bytes

2.运行RUN块来执行按时间点恢复。在RUN块中使用set until来指定恢复的目标时间,还原点,SCN或日志序列号。如果指定的是目标时间,那么使用NLS_LANG和NLS_DATE_FORMAT环境变量所指定的格式。如果自动通道没有配置,那么要为访问的磁盘或磁带分配通道。


RMAN> run
2> {
3>  set until scn 3142264;
4>  restore database;
5>  recover database;
6> }

executing command: SET until clause

Starting restore at 04-FEB-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oracle/oradata/test/system01.dbf
restoring datafile 00002 to /u01/app/oracle/oradata/test/undotbs01.dbf
restoring datafile 00003 to /u01/app/oracle/oradata/test/sysaux01.dbf
restoring datafile 00004 to /u01/app/oracle/oradata/test/users01.dbf
restoring datafile 00005 to /u01/app/oracle/oradata/test/example01.dbf
restoring datafile 00006 to /u01/app/oracle/oradata/test/test01.dbf
restoring datafile 00007 to /u01/app/oracle/oradata/test/testbak.dbf
channel ORA_DISK_1: reading from backup piece /u02/test_df870779983_s135_s1
channel ORA_DISK_1: restored backup piece 1
piece handle=/u02/test_df870779983_s135_s1 tag=TAG20150204T111943
channel ORA_DISK_1: restore complete, elapsed time: 00:02:29
Finished restore at 04-FEB-15

Starting recover at 04-FEB-15
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:02

Finished recover at 04-FEB-15

从alert日志文件中可以看到如下信息:

The input backup piece /u02/test_df870779983_s135_s1 is in compressed format.
Full restore complete of datafile 6 /u01/app/oracle/oradata/test/test01.dbf.  Elapsed time: 0:00:01
  checkpoint is 3142189
Full restore complete of datafile 7 /u01/app/oracle/oradata/test/testbak.dbf.  Elapsed time: 0:00:06
  checkpoint is 3142189
Full restore complete of datafile 4 /u01/app/oracle/oradata/test/users01.dbf.  Elapsed time: 0:00:09
  checkpoint is 3142189
  last deallocation scn is 3111848
Wed Feb 04 11:25:47 CST 2015
Full restore complete of datafile 2 /u01/app/oracle/oradata/test/undotbs01.dbf.  Elapsed time: 0:00:37
  checkpoint is 3142189
  last deallocation scn is 3106509
Wed Feb 04 11:25:58 CST 2015
Full restore complete of datafile 5 /u01/app/oracle/oradata/test/example01.dbf.  Elapsed time: 0:00:46
  checkpoint is 3142189
  last deallocation scn is 2526488
Wed Feb 04 11:26:57 CST 2015
Full restore complete of datafile 3 /u01/app/oracle/oradata/test/sysaux01.dbf.  Elapsed time: 0:01:47
  checkpoint is 3142189
  last deallocation scn is 3099893
Wed Feb 04 11:27:32 CST 2015
Full restore complete of datafile 1 /u01/app/oracle/oradata/test/system01.dbf.  Elapsed time: 0:02:20
  checkpoint is 3142189
  last deallocation scn is 3101877
Wed Feb 04 11:27:39 CST 2015
alter database recover datafile list clear
Wed Feb 04 11:27:39 CST 2015
Completed: alter database recover datafile list clear
Wed Feb 04 11:27:39 CST 2015
alter database recover datafile list
 1 , 2 , 3 , 4 , 5 , 6 , 7
Completed: alter database recover datafile list
 1 , 2 , 3 , 4 , 5 , 6 , 7
Wed Feb 04 11:27:39 CST 2015
alter database recover if needed
 start until change 3142264
Media Recovery Start
Wed Feb 04 11:27:40 CST 2015
Recovery of Online Redo Log: Thread 1 Group 3 Seq 3 Reading mem 0
  Mem# 0: /u01/app/oracle/oradata/test/redo03.log
Wed Feb 04 11:27:40 CST 2015
Recovery of Online Redo Log: Thread 1 Group 1 Seq 4 Reading mem 0
  Mem# 0: /u01/app/oracle/oradata/test/redo01.log
Wed Feb 04 11:27:40 CST 2015
Incomplete Recovery applied until change 3142277
Wed Feb 04 11:27:40 CST 2015
Media Recovery Complete (test)
Completed: alter database recover if needed
 start until change 3142264

从上面的恢复过程可以看到,首先从备份中还原数据文件,各个数据文件的checkpoint scn是3142189比我们的恢复目标SCN小,然后应用重做日志文件将数据库恢复到目标SCN所对应的时间点。

set until还可以使用时间表达式,还原点或者日志序列事情作为它的恢复目标时间点
set until time ‘2015-02-04 11:22:29’;
set until sequence 4;
set until restore point before_delete;

如果按时间点恢复成功。可以以只读方式打开数据库来检查表emp的数据是否恢复回来了。如果表emp的记录没有恢复回来,可能我们选错了恢复目标SCN。在这种情况下,可以使用新的恢复目标SCN然后重新执行按时间点恢复。

RMAN> sql 'alter database open read only';

sql statement: alter database open read only

SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80           800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81          1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81          1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81          2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81          1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81          2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81          2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87          3000                    20
      7839 KING       PRESIDENT            17-NOV-81          5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81          1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87          1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81           950                    30
      7902 FORD       ANALYST         7566 03-DEC-81          3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82          1300                    10

14 rows selected.

从上面的结果可知表emp的记录通过按时间点恢复已经找回来了。

如果按时间点恢复经过验证后达到了恢复目标,可以有如下选择:
1.使用oracle导出工具将恢复的表emp进行逻辑导出。然后将数据库恢复到当前时间点后,再导入导出的数据。这样在不会丢失数据库的其它改变而又恢复了表emp的数据。

2.以读写方式打开数据库,这样在恢复目标SCN之后的所有改变将会丢失。当前的联机重做日志文件会被归档,日志序列号会被设置为1,并且所有的联机重做日志会被指定新的时间戳和SCN。

RMAN> alter database open resetlogs;

database opened

使用ancestor incarnation执行按时间点恢复
使用ancestor incarnation执行按时间点恢复与使用current incarnation的不同之处在于需要设置数据库的incarnation.并且必须从包含恢复目标SCN的incarnation中还原控制文件。

不使用recover catalog的情况
比如我们要将数据库恢复到scott用户的emp表被删除之后的时间点,对older incarnation执行按时间点恢复的过程如下:

1.判断要使用的incarnation。可以使用list incarnation命令来找到恢复目标时间所对应的incarnation

RMAN> list incarnation;


List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       TEST     2155613261       PARENT  1          2005-06-30 19:09:40
2       2       TEST     2155613261       PARENT  446075     2014-09-05 09:13:52
3       3       TEST     2155613261       PARENT  2849317    2015-01-27 15:10:02
4       4       TEST     2155613261       PARENT  2880152    2015-01-27 23:41:06
5       5       TEST     2155613261       PARENT  3017109    2015-02-01 19:31:28
6       6       TEST     2155613261       PARENT  3041066    2015-02-01 23:05:57
7       7       TEST     2155613261       PARENT  3041350    2015-02-01 23:23:21
8       8       TEST     2155613261       ORPHAN  3111834    2015-02-03 19:57:34
9       9       TEST     2155613261       ORPHAN  3111834    2015-02-03 20:26:09
11      11      TEST     2155613261       PARENT  3114664    2015-02-03 21:25:34
10      10      TEST     2155613261       ORPHAN  3114665    2015-02-03 20:34:43
12      12      TEST     2155613261       PARENT  3142278    2015-02-04 11:40:02
13      13      TEST     2155613261       PARENT  3144077    2015-02-04 13:09:03
14      14      TEST     2155613261       CURRENT 3144537    2015-02-04 13:32:41

当前的incarnation的Inc Key为14.通过下面的查询可以找到它之前的incarnation的Inc Key为13:

SQL> select prior_incarnation# from v$database_incarnation where status ='CURRENT';

PRIOR_INCARNATION#
------------------
                13

2.将数据库启动到mount状态

RMAN> startup  mount

Oracle instance started
database mounted

Total System Global Area     327155712 bytes

Fixed Size                     1273516 bytes
Variable Size                138412372 bytes
Database Buffers             184549376 bytes
Redo Buffers                   2920448 bytes

3.将数据库test的incarnation设置为incarnation号为13,也就是current incarnation的parent incarnation。

RMAN> reset database to incarnation 13;

database reset to incarnation 13

4.执行还原与恢复,设置恢复目标时间,如果没有配置相关通道设置通道。将数据库恢复到表emp删除之后的时间点(2015-02-04 13:30:01):

RMAN> run
2> {
3> set until time '2015-02-04 13:30:01';
4> restore database;
5> recover database;
6> }

executing command: SET until clause

Starting restore at 2015-02-04 13:54:37
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oracle/oradata/test/system01.dbf
restoring datafile 00002 to /u01/app/oracle/oradata/test/undotbs01.dbf
restoring datafile 00003 to /u01/app/oracle/oradata/test/sysaux01.dbf
restoring datafile 00004 to /u01/app/oracle/oradata/test/users01.dbf
restoring datafile 00005 to /u01/app/oracle/oradata/test/example01.dbf
restoring datafile 00006 to /u01/app/oracle/oradata/test/test01.dbf
restoring datafile 00007 to /u01/app/oracle/oradata/test/testbak.dbf
channel ORA_DISK_1: reading from backup piece /u02/test_df870779983_s135_s1
channel ORA_DISK_1: restored backup piece 1
piece handle=/u02/test_df870779983_s135_s1 tag=TAG20150204T111943
channel ORA_DISK_1: restore complete, elapsed time: 00:02:37
Finished restore at 2015-02-04 13:57:14

Starting recover at 2015-02-04 13:57:14
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 3 is already on disk as file /u02/1_3_870729934.dbf
archive log thread 1 sequence 4 is already on disk as file /u02/1_4_870729934.dbf
archive log thread 1 sequence 1 is already on disk as file /u02/1_1_870781202.dbf
archive log filename=/u02/1_3_870729934.dbf thread=1 sequence=3
archive log filename=/u02/1_4_870729934.dbf thread=1 sequence=4
archive log filename=/u02/1_1_870781202.dbf thread=1 sequence=1
archive log filename=/u02/1_1_870786543.dbf thread=1 sequence=1
media recovery complete, elapsed time: 00:00:03
Finished recover at 2015-02-04 13:57:18

RMAN> alter database open resetlogs;

database opened


RMAN> list incarnation;


List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       TEST     2155613261       PARENT  1          2005-06-30 19:09:40
2       2       TEST     2155613261       PARENT  446075     2014-09-05 09:13:52
3       3       TEST     2155613261       PARENT  2849317    2015-01-27 15:10:02
4       4       TEST     2155613261       PARENT  2880152    2015-01-27 23:41:06
5       5       TEST     2155613261       PARENT  3017109    2015-02-01 19:31:28
6       6       TEST     2155613261       PARENT  3041066    2015-02-01 23:05:57
7       7       TEST     2155613261       PARENT  3041350    2015-02-01 23:23:21
8       8       TEST     2155613261       ORPHAN  3111834    2015-02-03 19:57:34
9       9       TEST     2155613261       ORPHAN  3111834    2015-02-03 20:26:09
11      11      TEST     2155613261       PARENT  3114664    2015-02-03 21:25:34
10      10      TEST     2155613261       ORPHAN  3114665    2015-02-03 20:34:43
12      12      TEST     2155613261       PARENT  3142278    2015-02-04 11:40:02
13      13      TEST     2155613261       PARENT  3144077    2015-02-04 13:09:03
14      14      TEST     2155613261       ORPHAN  3144537    2015-02-04 13:32:41
15      15      TEST     2155613261       CURRENT 3144674    2015-02-04 13:58:43

使用recover catalog的情况
比如我们要将数据库恢复到scott用户的emp表被删除之后的时间点,对older incarnation执行按时间点恢复的过程如下:

1.判断要使用的incarnation。可以使用list incarnation命令来找到恢复目标时间所对应的incarnation

RMAN> list incarnation;


List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       8       TEST     2168949517       PARENT  1          2010-04-19 10:22:46
1       2       TEST     2168949517       PARENT  383537     2015-02-04 17:44:49
1       102     TEST     2168949517       PARENT  415176     2015-02-04 18:22:16
1       188     TEST     2168949517       CURRENT 415481     2015-02-04 18:33:17



当前的incarnation的Inc Key为188.通过下面的查询可以找到它之前的incarnation的Inc Key为102:我们将数据库恢复到2015-02-04 18:22:30,也就是在SCN:415176和SCN:415481之间。

RMAN> list backup of controlfile;


List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
75      Full    6.80M      DISK        00:00:01     2015-02-04 18:11:38
        BP Key: 77   Status: AVAILABLE  Compressed: NO  Tag: TAG20150204T181137
        Piece Name: /u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_02_04/o1_mf_s_870804697_bf3w2t62_.bkp
  Control File Included: Ckp SCN: 415111       Ckp time: 2015-02-04 18:11:37

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
139     Full    6.80M      DISK        00:00:02     2015-02-04 18:22:45
        BP Key: 144   Status: AVAILABLE  Compressed: NO  Tag: TAG20150204T182243
        Piece Name: /u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_02_04/o1_mf_s_870805363_bf3wqnyv_.bkp
  Control File Included: Ckp SCN: 415288       Ckp time: 2015-02-04 18:22:43

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
236     Full    6.80M      DISK        00:00:03     2015-02-04 18:33:39
        BP Key: 242   Status: AVAILABLE  Compressed: NO  Tag: TAG20150204T183336
        Piece Name: /u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_02_04/o1_mf_s_870806016_bf3xd2wl_.bkp
  Control File Included: Ckp SCN: 415765       Ckp time: 2015-02-04 18:33:36

从上面的控制文件备份信息可以看到要恢复到2015-02-04 18:22:30这个时间点应该使用控制文件备份是o1_mf_s_870805363_bf3wqnyv_.bkp

2.将数据库强制启动到nomount状态

RMAN> startup force nomount

Oracle instance started

Total System Global Area     327155712 bytes

Fixed Size                     1273516 bytes
Variable Size                138412372 bytes
Database Buffers             184549376 bytes
Redo Buffers                   2920448 bytes

3.将数据库test的incarnation设置为incarnation号为102,也就是current incarnation的parent incarnation。

RMAN> reset database to incarnation 102;

database reset to incarnation 102

RMAN> list incarnation;


List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       8       TEST     2168949517       PARENT  1          2010-04-19 10:22:46
1       2       TEST     2168949517       PARENT  383537     2015-02-04 17:44:49
1       102     TEST     2168949517       CURRENT 415176     2015-02-04 18:22:16
1       188     TEST     2168949517       ORPHAN  415481     2015-02-04 18:33:17

4.执行还原与恢复,设置恢复目标时间,如果没有配置相关通道设置通道。还原控制文件,将数据库恢复到表emp删除之后的时间点(2015-02-04 18:22:30):

RMAN> restore controlfile;

Starting restore at 2015-02-04 18:44:23
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_02_04/o1_mf_s_870805363_bf3wqnyv_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_02_04/o1_mf_s_870805363_bf3wqnyv_.bkp tag=TAG20150204T182243
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output filename=/u01/app/oracle/oradata/test/control01.ctl
output filename=/u01/app/oracle/oradata/test/control02.ctl
output filename=/u01/app/oracle/oradata/test/control03.ctl
Finished restore at 2015-02-04 18:44:29

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> restore database until time '2015-02-04 18:22:30';

Starting restore at 2015-02-04 18:47:15
Starting implicit crosscheck backup at 2015-02-04 18:47:15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Crosschecked 4 objects
Finished implicit crosscheck backup at 2015-02-04 18:47:17

Starting implicit crosscheck copy at 2015-02-04 18:47:17
using channel ORA_DISK_1
Finished implicit crosscheck copy at 2015-02-04 18:47:17

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_02_04/o1_mf_s_870805363_bf3wqnyv_.bkp
File Name: /u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_02_04/o1_mf_s_870806016_bf3xd2wl_.bkp

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oracle/oradata/test/system01.dbf
restoring datafile 00002 to /u01/app/oracle/oradata/test/undotbs01.dbf
restoring datafile 00003 to /u01/app/oracle/oradata/test/sysaux01.dbf
restoring datafile 00004 to /u01/app/oracle/oradata/test/users01.dbf
restoring datafile 00005 to /u01/app/oracle/oradata/test/example01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/TEST/backupset/2015_02_04/o1_mf_nnndf_TAG20150204T181037_bf3w0y1f_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/flash_recovery_area/TEST/backupset/2015_02_04/o1_mf_nnndf_TAG20150204T181037_bf3w0y1f_.bkp tag=TAG20150204T181037
channel ORA_DISK_1: restore complete, elapsed time: 00:01:15
Finished restore at 2015-02-04 18:48:32

将数据文件还原到2015-02-04 18:22:30这个时间点,下面执行恢复,要注意的是在执行恢复操作之前还需要执行reset database to incarnation 102否则会报错:

RMAN> recover database until time '2015-02-04 18:22:30';

Starting recover at 2015-02-04 18:49:05
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/04/2015 18:49:05
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20011: target database incarnation is not current in recovery catalog

再次执行 reset database to incarnation 102;

RMAN> reset database to incarnation 102;

database reset to incarnation 102

RMAN> recover database until time '2015-02-04 18:22:30';

Starting recover at 2015-02-04 18:49:21
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 3 is already on disk as file /u02/1_3_870803089.dbf
archive log thread 1 sequence 4 is already on disk as file /u02/1_4_870803089.dbf
archive log thread 1 sequence 1 is already on disk as file /u02/1_1_870805336.dbf
archive log filename=/u02/1_3_870803089.dbf thread=1 sequence=3
archive log filename=/u02/1_4_870803089.dbf thread=1 sequence=4
archive log filename=/u02/1_1_870805336.dbf thread=1 sequence=1
media recovery complete, elapsed time: 00:00:01
Finished recover at 2015-02-04 18:49:24

RMAN> alter database open resetlogs;

database opened
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN> list incarnation;


List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       8       TEST     2168949517       PARENT  1          2010-04-19 10:22:46
1       2       TEST     2168949517       PARENT  383537     2015-02-04 17:44:49
1       102     TEST     2168949517       PARENT  415176     2015-02-04 18:22:16
1       308     TEST     2168949517       CURRENT 415183     2015-02-04 18:49:41
1       188     TEST     2168949517       ORPHAN  415481     2015-02-04 18:33:17

可以看到恢复完成之后当前的incarnation对应的reset scn号在415176与415481之间,达到了我们所期待的结果。

闪回数据库

闪回数据库
如果数据库启用了闪回日志,那么可以使用闪回数据库将数据库的内容回退到闪回窗口中的某一时间点也可以使用flashback database将数据库回退到之前定义的受保护还原点所对应的时间点。

闪回数据库使用的场景
在大多数情况下执行闪回数据库使用时间表达式,正常或受保护还原点或SCN来指定所要回退到的过去时间点。

使用RMAN执行闪回数据库的过程如下:
1.决定一个能代表flashback database命令要将数据库回退到过去时间点的SCN,还原点或时间表达式
创建一个表emp_test

SQL> create table emp_test as select * from emp;

Table created.

SQL> select count(*) from emp_test;

  COUNT(*)
----------
        14

查看当前数据库的SCN号

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    3111823

永久删除表emp_test

SQL> drop table emp_test purge;

Table dropped.

2.使用RMAN连接到目标数据库

[oracle@oracle11g ~]$ rman target/

Recovery Manager: Release 10.2.0.5.0 - Production on Tue Feb 3 16:27:32 2015

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: TEST (DBID=2155613261)

3.完全干净地关闭数据库后并将数据库启动到mount状态:

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     327155712 bytes

Fixed Size                     1273516 bytes
Variable Size                138412372 bytes
Database Buffers             184549376 bytes
Redo Buffers                   2920448 bytes

4.执行查询来判断执行闪回数据库的时间窗口。如果有些闪回数据库日志因为闪回区空间压力而被删除了,那么可能不能将数据库回退到所有指定的SCN所对应的时间点。如果flashback database指定的目标SCN超出过闪回窗口,那么flashback database命令将会报ora-38729错误。在执行闪回数据库操作时,RMAN可能需要从备份中还原一些归档重做日志。如果备份存储在SBT上并且对于要访问的SBT设备没有配置必要的通道,那么可以在RUN块中使用allocate channel命令来允许RMAN检索磁盘或磁带上的这些日志文件。

SQL> select oldest_flashback_scn,to_char(oldest_flashback_time,'YYYY-MM-DD HH24:MI:SS') from v$flashback_database_log;

OLDEST_FLASHBACK_SCN TO_CHAR(OLDEST_FLAS
-------------------- -------------------
             3069719 2015-02-02 15:38:40

这里我们的闪回窗口能将数据库回退到SCN号为3069719的时间点,但我们的表emp_test是在
2015-02-02 15:38:40以后创建,且删除的时间在SCN:3111823以后,我们想要通过闪回数据库来恢复表emp_test话恢复的窗口应该在SCN:3111823以后。SCN:3111823对应的时间为2015-02-03 16:26:27

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

TO_CHAR(SCN_TO_TIME
-------------------
2015-02-03 16:26:27

5.使用RMAN来执行flashback database命令,并指定一个目标时间点:

RMAN> flashback database to scn 3111823;

Starting flashback at 03-FEB-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK


starting media recovery
media recovery complete, elapsed time: 00:00:15

Finished flashback at 03-FEB-15

也可以执行如下命令:

RMAN> flashback database to  time "to_date('2015-02-03 16:26:27','yyyy-mm-dd hh24:mi:ss') ";

Starting flashback at 03-FEB-15
using channel ORA_DISK_1


starting media recovery
media recovery complete, elapsed time: 00:00:15

Finished flashback at 03-FEB-15

当flashback database命令执行完成后,数据库仍然处于mount状态并且恢复到了指定的时间点。

通过将数据库收只读模式打开来执行某些查询来检查数据库的内容来判断是否将数据库恢复到了你所期望的状态。

RMAN> sql 'alter database open read only';

sql statement: alter database open read only

查询表emp_test是否存在且是否有14条记录

SQL> select * from emp_test;

     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80           800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81          1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81          1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81          2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81          1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81          2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81          2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87          3000                    20
      7839 KING       PRESIDENT            17-NOV-81          5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81          1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87          1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81           950                    30
      7902 FORD       ANALYST         7566 03-DEC-81          3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82          1300                    10

14 rows selected.

这说明恢复到了我们所期望的状态。

在闪回数据库操作将数据库回退到你所期望的时间点后,可以有两个选择:
1.以open resetlogs选项打开数据库,但在目标SCN号之后的改变将会丢失
RMAN>alter database open resetlogs;

2.使用Oracle的导出工具将被删除的对象导出。然后将数据库恢复到当前时间点:
RMAN>recover database;
这步操作将会撤消闪回数据库的影响,通过对数据库应用重做日志中的所有改变,将数据库恢复到最近的SCN时间点。在以读写模式打开数据库后,可以将导出的表导入数据库。

如果将数据库闪回到一个错误的时间点之后的选择
如果我们将上面的数据库使用闪回回退到表emp_test创建之前SCN:3111000,那么在闪回数据库之后表emp_test是不存在的。

 RMAN> startup mount

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area     327155712 bytes

Fixed Size                     1273516 bytes
Variable Size                138412372 bytes
Database Buffers             184549376 bytes
Redo Buffers                   2920448 bytes

RMAN> flashback database to scn 3111000;

Starting flashback at 03-FEB-15
using channel ORA_DISK_1


starting media recovery
media recovery complete, elapsed time: 00:00:15

Finished flashback at 03-FEB-15

RMAN>  sql 'alter database open read only';

sql statement: alter database open read only


SQL> select * from emp_test;
select * from emp_test
              *
ERROR at line 1:
ORA-00942: table or view does not exist

确实闪回成功后表emp_test不存在。

1.如果选择的闪回目标时间回退的不够,那么可以再次执行flashback database命令将数据库回退到比当前更前的时间

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     327155712 bytes

Fixed Size                     1273516 bytes
Variable Size                138412372 bytes
Database Buffers             184549376 bytes
Redo Buffers                   2920448 bytes

RMAN> flashback database to scn 3111993;

Starting flashback at 03-FEB-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK


starting media recovery
media recovery complete, elapsed time: 00:00:15

Finished flashback at 03-FEB-15

RMAN>  sql 'alter database open read only';

sql statement: alter database open read only

SQL> select * from emp_test;
select * from emp_test
              *
ERROR at line 1:
ORA-00942: table or view does not exist

回退的时间点比所期待的时间晚,所以表emp_test还是被删除了。

2.如果选择的目标SCN将数据库回退到比期望的时间点更早的时间,那么可以加载数据库,使用recover database until scn命令来恢复到所期望的时间点:

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     327155712 bytes

Fixed Size                     1273516 bytes
Variable Size                138412372 bytes
Database Buffers             184549376 bytes
Redo Buffers                   2920448 bytes

RMAN> flashback database to scn 3110000;

Starting flashback at 03-FEB-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK


starting media recovery
media recovery complete, elapsed time: 00:00:15

Finished flashback at 03-FEB-15

RMAN>  sql 'alter database open read only';

sql statement: alter database open read only

SQL> select * from emp_test;
select * from emp_test
              *
ERROR at line 1:
ORA-00942: table or view does not exist

回退的时间点比所期待的时间早,所以表emp_test还没有被创建,执行recover database until scn命令将数据库恢复到表emp_test删除之前(因为表emp_test删除之前系统的SCN为:3111823)

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     327155712 bytes

Fixed Size                     1273516 bytes
Variable Size                138412372 bytes
Database Buffers             184549376 bytes
Redo Buffers                   2920448 bytes

RMAN> recover database until scn 3111823;

Starting recover at 03-FEB-15
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:04

Finished recover at 03-FEB-15

RMAN> sql 'alter database open read only';

sql statement: alter database open read only

SQL> select * from emp_test;

     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80           800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81          1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81          1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81          2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81          1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81          2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81          2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87          3000                    20
      7839 KING       PRESIDENT            17-NOV-81          5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81          1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87          1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81           950                    30
      7902 FORD       ANALYST         7566 03-DEC-81          3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82          1300                    10

从查询结果可以看到表emp_test被恢复回来了

3.如果想完全撤消flashback database命令的影响,可以使用recover database命令对数据库执行完全恢复。

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     327155712 bytes

Fixed Size                     1273516 bytes
Variable Size                138412372 bytes
Database Buffers             184549376 bytes
Redo Buffers                   2920448 bytes

RMAN> recover database;

Starting recover at 03-FEB-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:05

Finished recover at 03-FEB-15

RMAN> sql 'alter database open';

sql statement: alter database open

SQL> select * from emp_test;
select * from emp_test
              *
ERROR at line 1:
ORA-00942: table or view does not exist

可以看到对数据库执行完全恢复后,表emp_test不存在,这也就撤消了闪回数据库的影响。

执行闪回数据库来撤消open resetlogs的影响,将数据库回退到表emp_test被删除之前.

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     327155712 bytes

Fixed Size                     1273516 bytes
Variable Size                138412372 bytes
Database Buffers             184549376 bytes
Redo Buffers                   2920448 bytes

RMAN> flashback database to scn 3111823;

Starting flashback at 03-FEB-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK


starting media recovery
media recovery complete, elapsed time: 00:00:15

Finished flashback at 03-FEB-15

RMAN> sql 'alter database open resetlogs';

sql statement: alter database open resetlogs

SQL> select * from emp_test;

     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80           800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81          1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81          1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81          2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81          1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81          2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81          2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87          3000                    20
      7839 KING       PRESIDENT            17-NOV-81          5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81          1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87          1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81           950                    30
      7902 FORD       ANALYST         7566 03-DEC-81          3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82          1300                    10

验证闪回窗口开始时间是否比最近open resetlogs的时间早

SQL> select resetlogs_change# from v$database;

RESETLOGS_CHANGE#
-----------------
          3111834

SQL> select oldest_flashback_scn from v$flashback_database_log;

OLDEST_FLASHBACK_SCN
--------------------
             3074835

如果v$database.resetlogs_change#比v$flashback_database_log.oldest_flashback_scn大,那么可以使用闪回数据库来撤消open resetlogs操作对数据库的改变。

关闭数据库后将数据库置于mount状态,并重新检查闪回窗口。如果resetlogs的SCN仍然在闪回窗口中,那么可以执行flashback database命令来撤消open resetlogs操作对数据库的改变。

SQL> select resetlogs_change# from v$database;

RESETLOGS_CHANGE#
-----------------
          3111834

SQL> select oldest_flashback_scn from v$flashback_database_log;

OLDEST_FLASHBACK_SCN
--------------------
             3074835



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     327155712 bytes

Fixed Size                     1273516 bytes
Variable Size                138412372 bytes
Database Buffers             184549376 bytes
Redo Buffers                   2920448 bytes

RMAN> flashback database to before resetlogs;

Starting flashback at 03-FEB-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=154 devtype=DISK


starting media recovery
media recovery complete, elapsed time: 00:00:03

Finished flashback at 03-FEB-15

RMAN> sql 'alter database open read only';

sql statement: alter database open read only

SQL> select * from emp_test;

     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80           800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81          1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81          1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81          2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81          1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81          2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81          2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87          3000                    20
      7839 KING       PRESIDENT            17-NOV-81          5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81          1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87          1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81           950                    30
      7902 FORD       ANALYST         7566 03-DEC-81          3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82          1300                    10

如果目标SCN在闪回数据库窗口的开始时间之前,那么flashback database命令会报错。如果闪回数据库执行成功,那么数据库还处于mount状态,并将数据库恢复到了之前数据库incarnation对应的open resetlogs操作之前最后的SCN所对应的时间点。因为数据库现在对应的resetlogs_change#为3111834,上面的闪回数据库将数据库回退到SCN为3111834之前的状态,所以以只读模式将数据库打开后表emp_test应该是存在的,来验证数据库是否恢复到你所期望的状态。为了使用数据库可以进行更新,使用alter database open resetlogs命令来打开数据库。

RMAN> alter database open resetlogs;

database opened

将数据库闪回到正确的open resetlogs状态
在有些情况下,可能需要将数据库回退到parent incarnation所对应的时间。可以使用RMAN的reset database to incarnation命令来指定flashback database to scn来引用的current incarnation

操作过程如下:
1.验证闪回日志所包含的信息能将数据库回退到的SCN:

SQL> select oldest_flashback_scn from v$flashback_database_log;

OLDEST_FLASHBACK_SCN
--------------------
             3077255

2.判断闪回数据库要使用的目标incarnation

SQL> select * from v$database_incarnation;

INCARNATION# RESETLOGS_CHANGE# RESETLOGS_TI PRIOR_RESETLOGS_CHANGE# PRIOR_RESETL STATUS  RESETLOGS_ID PRIOR_INCARNATION# FLASHBACK_DATABASE_ALLOWED
------------ ----------------- ------------ ----------------------- ------------ ------- ------------ ------------------ --------------------------
           1                 1 30-JUN-05                          0              PARENT     562360180                  0 NO
           2            446075 05-SEP-14                          1 30-JUN-05    PARENT     857466832                  1 NO
           3           2849317 27-JAN-15                     446075 05-SEP-14    PARENT     870102602                  2 NO
           4           2880152 27-JAN-15                    2849317 27-JAN-15    PARENT     870133266                  3 NO
           5           3017109 01-FEB-15                    2880152 27-JAN-15    PARENT     870550288                  4 NO
           6           3041066 01-FEB-15                    3017109 01-FEB-15    PARENT     870563157                  5 NO
           7           3041350 01-FEB-15                    3041066 01-FEB-15    PARENT     870564201                  6 YES
           8           3111834 03-FEB-15                    3041350 01-FEB-15    ORPHAN     870724654                  7 YES
           9           3111834 03-FEB-15                    3041350 01-FEB-15    ORPHAN     870726369                  7 YES
          10           3114665 03-FEB-15                    3041350 01-FEB-15    CURRENT    870726883                  7 YES


SQL> select prior_incarnation# from v$database_incarnation where status ='CURRENT';

PRIOR_INCARNATION#
------------------
                 7

从上面的信息可以知道当前的incarnation号为10,它的parent incarnation号是7

3.在RMAN在关闭数据库,并mount数据库:

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     327155712 bytes

Fixed Size                     1273516 bytes
Variable Size                138412372 bytes
Database Buffers             184549376 bytes
Redo Buffers                   2920448 bytes

4.将数据库的incarnation设置为它的parent incarnation:

RMAN> reset database  to incarnation 7;

database reset to incarnation 7

5.运行flashback database命令:
RMAN> flashback database to scn 3077255;

Starting flashback at 03-FEB-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK


starting media recovery

archive log thread 1 sequence 2 is already on disk as file /u02/1_2_870564201.dbf
media recovery complete, elapsed time: 00:00:01
Finished flashback at 03-FEB-15

RMAN> sql 'alter database open read only';

sql statement: alter database open read only

当闪回操作成功后,可以验证闪回数据库的结果,如果数据库回退到你所期望的状态就以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     327155712 bytes

Fixed Size                     1273516 bytes
Variable Size                138412372 bytes
Database Buffers             184549376 bytes
Redo Buffers                   2920448 bytes

RMAN> alter database open resetlogs;

database opened

闪回表

闪回表
oracle闪回表给予了DBA将一个或一组表快速恢复到过去的指定时间点并且不需要将数据库脱机。在大多数情况下闪回表与按时间点恢复操作相比消除了大量的操作。闪回表还原表时会自动维护与表相关的属性比如,当前索引,触发器和约束,并且不需要DBA找出和还原相关的属性。使用闪回表能将表回退到之前SCN或时间所对应的状态。

闪回表使用undo表空间中的信息来还原表。不需要从备份中还原任何数据,且在执行闪回表操作时数据库仍然可以使用。

使用闪回表的条件
使用闪回表有以下条件:
1.必须对表启用行移动。可以执行下面的语句来对表启用行移动:
alter table tablename enable row movement;

2.用户必须被授予了flashback any table的系统权限或者对特定的表有flashback对象权限

3.用户对于特定的表必须有select,insert,delete和alter权限

4.undo表空间中保留的undo信息足够能满足flashback table操作回退到所指定的时间点或SCN

下面测试闪回表
1.对表emp启用行移动

SQL> alter table emp enable row movement;

Table altered.


SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80           800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81          1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81          1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81          2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81          1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81          2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81          2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87          3000                    20
      7839 KING       PRESIDENT            17-NOV-81          5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81          1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87          1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81           950                    30
      7902 FORD       ANALYST         7566 03-DEC-81          3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82          1300                    10

14 rows selected.

2.在删除表emp的记录之前记录一下时间或者SCN

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    3077391

SQL> select sysdate from dual;

SYSDATE
-------------------
2015-02-02 20:50:39

3.删除表emp的记录

SQL> delete from emp;

14 rows deleted.

SQL> commit;

Commit complete.

SQL> select * from emp;

no rows selected

4.执行闪回表将表emp的内容回退到删除之前
使用flashback table tablename to scn的命令如下:

SQL> flashback table emp to scn 3077391;

Flashback complete.

SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20
      7499 ALLEN      SALESMAN        7698 1981-02-20 00:00:00       1600        300         30
      7521 WARD       SALESMAN        7698 1981-02-22 00:00:00       1250        500         30
      7566 JONES      MANAGER         7839 1981-04-02 00:00:00       2975                    20
      7654 MARTIN     SALESMAN        7698 1981-09-28 00:00:00       1250       1400         30
      7698 BLAKE      MANAGER         7839 1981-05-01 00:00:00       2850                    30
      7782 CLARK      MANAGER         7839 1981-06-09 00:00:00       2450                    10
      7788 SCOTT      ANALYST         7566 1987-04-19 00:00:00       3000                    20
      7839 KING       PRESIDENT            1981-11-17 00:00:00       5000                    10
      7844 TURNER     SALESMAN        7698 1981-09-08 00:00:00       1500          0         30
      7876 ADAMS      CLERK           7788 1987-05-23 00:00:00       1100                    20
      7900 JAMES      CLERK           7698 1981-12-03 00:00:00        950                    30
      7902 FORD       ANALYST         7566 1981-12-03 00:00:00       3000                    20
      7934 MILLER     CLERK           7782 1982-01-23 00:00:00       1300                    10

14 rows selected.

使用flashback table tablename to timestamp的命令如下:

SQL> flashback table emp to timestamp to_timestamp('2015-02-02 20:50:39','yyyy-mm-dd hh24:mi:ss');

Flashback complete.

SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20
      7499 ALLEN      SALESMAN        7698 1981-02-20 00:00:00       1600        300         30
      7521 WARD       SALESMAN        7698 1981-02-22 00:00:00       1250        500         30
      7566 JONES      MANAGER         7839 1981-04-02 00:00:00       2975                    20
      7654 MARTIN     SALESMAN        7698 1981-09-28 00:00:00       1250       1400         30
      7698 BLAKE      MANAGER         7839 1981-05-01 00:00:00       2850                    30
      7782 CLARK      MANAGER         7839 1981-06-09 00:00:00       2450                    10
      7788 SCOTT      ANALYST         7566 1987-04-19 00:00:00       3000                    20
      7839 KING       PRESIDENT            1981-11-17 00:00:00       5000                    10
      7844 TURNER     SALESMAN        7698 1981-09-08 00:00:00       1500          0         30
      7876 ADAMS      CLERK           7788 1987-05-23 00:00:00       1100                    20
      7900 JAMES      CLERK           7698 1981-12-03 00:00:00        950                    30
      7902 FORD       ANALYST         7566 1981-12-03 00:00:00       3000                    20
      7934 MILLER     CLERK           7782 1982-01-23 00:00:00       1300                    10

14 rows selected.

注意:timestamp到SCN之间的映射不总是精确的,当使用timestamp作为flashback table语句的时间点,闪回表的真实时间点与由to_timestamp所指定的时间点大约有三秒钟的差异。

默认情况下,在执行flashback table操作之前数据库对受影响的表禁用了触发器,将表闪回到所指定的时间点后,在对表执行操作之前启用触发器。如果想让表在执行flashback操作时启用触发器,那么可以在flashback table语句中加入enable triggers子句。

SQL> flashback table emp to timestamp to_timestamp('2015-02-02 20:50:39','yyyy-mm-dd hh24:mi:ss') enable triggers;

Flashback complete.

SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20
      7499 ALLEN      SALESMAN        7698 1981-02-20 00:00:00       1600        300         30
      7521 WARD       SALESMAN        7698 1981-02-22 00:00:00       1250        500         30
      7566 JONES      MANAGER         7839 1981-04-02 00:00:00       2975                    20
      7654 MARTIN     SALESMAN        7698 1981-09-28 00:00:00       1250       1400         30
      7698 BLAKE      MANAGER         7839 1981-05-01 00:00:00       2850                    30
      7782 CLARK      MANAGER         7839 1981-06-09 00:00:00       2450                    10
      7788 SCOTT      ANALYST         7566 1987-04-19 00:00:00       3000                    20
      7839 KING       PRESIDENT            1981-11-17 00:00:00       5000                    10
      7844 TURNER     SALESMAN        7698 1981-09-08 00:00:00       1500          0         30
      7876 ADAMS      CLERK           7788 1987-05-23 00:00:00       1100                    20
      7900 JAMES      CLERK           7698 1981-12-03 00:00:00        950                    30
      7902 FORD       ANALYST         7566 1981-12-03 00:00:00       3000                    20
      7934 MILLER     CLERK           7782 1982-01-23 00:00:00       1300                    10

14 rows selected.
Proudly powered by WordPress | Indrajeet by Sus Hill.