备份与恢复：
第一章：备份恢复概述
1、备份的意义？
1)保护数据，避免因为各种故障而丢失数据
2）MTBF:平均故障间隔时间2012/11/20
3）MTTR:平均恢复时间
2、数据库故障的类型：
1）statement failure
2）user process failure：pmon 自动处理
3） user errors ：必须由dba通过备份恢复
4） instance failure: instance recover smon 自动处理
5）media recover：通过备份恢复
3、制定你的备份和恢复的计划
1）根据生产环境的恢复周期，制定详细的备份计划，然后严格执行
2）对备份，要在一定的时间内利用测试环境，进行故障恢复的练习
第二章：备份恢复原理
1、Oracle server ，Instance、oracle database、user process、server process、session、sga 、pga 的定义
2、share pool、data buffer、log buffer
large pool 的功能：在做备份和恢复时需要large pool的支持
3、redo 日志文件的管理（redo log group） ，归档（备份历史日志）和非归档模式（不备份历史日志）
4、checkpoint 概念：recover的起点
1） full checkpoint ：所有的脏块都写完，再将scn 写入到控制文件和datafile、redo file-----------正常关闭实例或 手工生成检查点（alter system checkpoint)
09:55:26 SQL> select file#,checkpoint_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1086165
2 1086165
3 1086165
4 1086165
5 1086165
6 1086172
7 1086165
8 1086165
9 1086165
10 1086165
11 1086165
11 rows selected.
09:55:28 SQL> alter system checkpoint;
System altered.
09:55:41 SQL> select file#,checkpoint_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1086203
2 1086203
3 1086203
4 1086203
5 1086203
6 1086203
7 1086203
8 1086203
9 1086203
10 1086203
11 1086203
2） incremental checkpoint（增量检查点）： 每过3s ，检查checkpoint 队列，查看脏块的写入情况，并记录之前最后一个脏块的scn 写入到controlfile
3） partial checkpoint： 当对tablespace 做以下操作时：如offline、readonly 、backup 时，在tablespace 对应的数据文件上建立检查点（写入scn）
09:54:38 SQL> alter tablespace test offline;
Tablespace altered.
09:54:46 SQL> select file#,checkpoint_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1082763
2 1082763
3 1082763
4 1082763
5 1082763
6 0
7 1082763
8 1082763
9 1082763
10 1082763
11 1082763
11 rows selected.
09:54:54 SQL> alter system checkpoint;
System altered.
09:55:13 SQL> select file#,checkpoint_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1086165
2 1086165
3 1086165
4 1086165
5 1086165
6 0
7 1086165
8 1086165
9 1086165
10 1086165
11 1086165
11 rows selected.
09:55:15 SQL> alter tablespace test online;
Tablespace altered.
09:55:26 SQL> select file#,checkpoint_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1086165
2 1086165
3 1086165
4 1086165
5 1086165
6 1086172
7 1086165
8 1086165
9 1086165
10 1086165
11 1086165
11 rows selected.
09:55:28 SQL> alter system checkpoint;
System altered.
09:55:41 SQL> select file#,checkpoint_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1086203
2 1086203
3 1086203
4 1086203
5 1086203
6 1086203
7 1086203
8 1086203
9 1086203
10 1086203
11 1086203
11 rows selected.
-------------设置表空间为read only 模式，会在数据文件上写入检查点信息
09:56:20 SQL> alter tablespace test read only;
Tablespace altered.
09:56:23 SQL> select file#,checkpoint_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1086203
2 1086203
3 1086203
4 1086203
5 1086203
6 1086219
7 1086203
8 1086203
9 1086203
10 1086203
11 1086203
11 rows selected.
09:56:27 SQL> alter system checkpoint;
System altered.
09:56:37 SQL> select file#,checkpoint_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1086231
2 1086231
3 1086231
4 1086231
5 1086231
6 1086219
7 1086231
8 1086231
9 1086231
10 1086231
11 1086231
11 rows selected.
09:57:02 SQL> alter tablespace test read write;
Tablespace altered.
09:57:04 SQL> select file#,checkpoint_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1086231
2 1086231
3 1086231
4 1086231
5 1086231
6 1086252
7 1086231
8 1086231
9 1086231
10 1086231
11 1086231
11 rows selected.
09:57:06 SQL> alter system checkpoint;
System altered.
09:57:13 SQL> select file#,checkpoint_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1086260
2 1086260
3 1086260
4 1086260
5 1086260
6 1086260
7 1086260
8 1086260
9 1086260
10 1086260
11 1086260
11 rows selected.
09:57:14 SQL>
5、dbwr、lgwr、ckpt、smon、pmon、arch 的功能
1）ckpt：检查点事件发生时，会启动ckpt ，然后ckpt通知dbwn 写脏块，在写脏块之前，通知lgwr 写redo entries；
并将未提交的事务回滚，完成后会在controlfile、data file 以及redo file 写入scn。
-----------database synchronization 数据库同步
SCN ：system change number
--------system scn（记录在controlfile）
10:02:11 SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
1086260
-------------datafile scn （记录在controlfile）
10:02:59 SQL> select file#,checkpoint_change# from v$datafile;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1086260
2 1086260
3 1086260
4 1086260
5 1086260
6 1086260
7 1086260
8 1086260
9 1086260
10 1086260
11 1086260
11 rows selected.
----------datafile stop scn
（记录在controlfile）
10:03:02 SQL> select file#,checkpoint_change#,last_change# from v$datafile;
FILE# CHECKPOINT_CHANGE# LAST_CHANGE#
---------- ------------------ ------------
1 1086260
2 1086260
3 1086260
4 1086260
5 1086260
6 1086260
7 1086260
8 1086260
9 1086260
10 1086260
11 1086260
11 rows selected.
-------------正常关库
10:10:31 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
10:10:57 SQL> startup mount
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 1219184 bytes
Variable Size 96470416 bytes
Database Buffers 213909504 bytes
Redo Buffers 2973696 bytes
Database mounted.
10:11:06 SQL> select file#,checkpoint_change# ,last_change# from v$datafile;
FILE# CHECKPOINT_CHANGE# LAST_CHANGE#
---------- ------------------ ------------
1 1087187 1087187
2 1087187 1087187
3 1087187 1087187
4 1087187 1087187
5 1087187 1087187
6 1087187 1087187
7 1087187 1087187
8 1087187 1087187
9 1087187 1087187
10 1087187 1087187
11 1087187 1087187
11 rows selected.
----------非正常关库
10:12:00 SQL> select file#,checkpoint_change# ,last_change# from v$datafile;
FILE# CHECKPOINT_CHANGE# LAST_CHANGE#
---------- ------------------ ------------
1 1087188
2 1087188
3 1087188
4 1087188
5 1087188
6 1087188
7 1087188
8 1087188
9 1087188
10 1087188
11 1087188
11 rows selected.
10:12:05 SQL> show parameter alert
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_checkpoints_to_alert boolean FALSE
10:12:16 SQL> alter system set log_checkpoints_to_alert=true;
System altered.
10:12:26 SQL> shutdown abort
ORACLE instance shut down.
10:12:31 SQL> startup mount
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 1219184 bytes
Variable Size 96470416 bytes
Database Buffers 213909504 bytes
Redo Buffers 2973696 bytes
Database mounted.
10:12:41 SQL> select file#,checkpoint_change# ,last_change# from v$datafile;
FILE# CHECKPOINT_CHANGE# LAST_CHANGE#
---------- ------------------ ------------
1 1087188
2 1087188
3 1087188
4 1087188
5 1087188
6 1087188
7 1087188
8 1087188
9 1087188
10 1087188
11 1087188
11 rows selected.
10:12:49 SQL> alter database open;
Database altered.
10:13:01 SQL> select file#,checkpoint_change# ,last_change# from v$datafile;
FILE# CHECKPOINT_CHANGE# LAST_CHANGE#
---------- ------------------ ------------
1 1108038
2 1108038
3 1108038
4 1108038
5 1108038
6 1108038
7 1108038
8 1108038
9 1108038
10 1108038
11 1108038
11 rows selected.
10:13:35 SQL>
---------last_change# 在database open 状态下是一个null 或无穷大的值
当database 正常关闭时，last_change#会和start scn 保持一致；如果非正常关闭，仍然是一个空值或无穷大的值，这时候在启动instance，smon 需要做instance recover。
----------datafile scn（记录在datafile 头部的scn ，也叫start scn）
10:03:53 SQL> select file#,checkpoint_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1086260
2 1086260
3 1086260
4 1086260
5 1086260
6 1086260
7 1086260
8 1086260
9 1086260
10 1086260
11 1086260
11 rows selected.
---------database的一致性，指的是在open database 时，记录在controlfile的system scn 和 datafile scn 以及在数据文件头部的start scn 应该保持一致，在一致的情况下
database可以正常打开，如不不一致，需要做media recover。
6、Instance Recover 的参数：
1）fast_start_mttr_target ：设置生成检查点的间隔时间，实现instance 的快速recover。
2）recovery_parallelism：recover的并行度
10:17:38 SQL> show parameter recover
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/flash_recovery
_area
db_recovery_file_dest_size big integer 2G
recovery_parallelism integer 0
10:18:59 SQL> alter system set recovery_parallelism=2 scope=spfile;
System altered.
10:19:04 SQL> startup force;
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 1219184 bytes
Variable Size 96470416 bytes
Database Buffers 213909504 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
10:19:19 SQL> show parameter recover
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/flash_recovery
_area
db_recovery_file_dest_size big integer 2G
recovery_parallelism integer 2
----------提高并行度，加快roll forward的速度
3） fast_start_parallel_rollback ：roll back的并行度
10:19:23 SQL> show parameter fast
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fast_start_io_target integer 0
fast_start_mttr_target integer 0
fast_start_parallel_rollback string LOW
---------low 启动的slave process 数是cpu 个数的 2倍
10:21:11 SQL> alter system set fast_start_parallel_rollback=high;
System altered.
10:22:08 SQL> show parameter fast
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fast_start_io_target integer 0
fast_start_mttr_target integer 0
fast_start_parallel_rollback string HIGH
10:22:09 SQL>
----------high 启动的slave process 是cpu 个数的 4倍，加快roll back 的速度
第三章：设置日志归档模式
1、归档和非归档的区别及应用环境
1）归档模式一般用于OLTP
2) 非归档模式一般用于OLAP 和 DSS
2、更改数据库的归档模式 :1) SHUTDOWN immediate 正常关库 2 ） startup mount 3） alter database archivelog 4） alter database open
查看归档模式： archive log list
3、归档参数的设置：
1）log_archive_max_processes 启动的归档进程数
2）log_archive_dest_n：最多支持10个位置（本地：location ；远程：service）
3）log_archive_format ：归档数据文件名（%t ：thread ；%s ：sequence；%r：resetlogs）
4、如何手工归档
1）alter system switch logfile
2） alter system archive log current
5、关于redo日志的视图：
1）v$log 查看日志成员属性
2）V$logfile 查看日志成员位置
3） v$archived_log 查看已经归档日志信息
4）v$log_history 查看历史日志信息
5）v$recovery_log 在做recovery 时需要的日志信息
第四章：手工备份
1、备份的分类:
物理备份：备份database的数据文件、控制文件、参数文件(spfile)等（备份database的物理结构），可以用于任何形式的failure。
1) 手工备份：通过OS 的命令，对要备份的文件拷贝,备份数据文件中所有的块，不能做增量备份。
2） rman 备份：利用oracle 的备份工具rman 备份（或其他备份软件），只备份datafile 里已使用的块，可以做增量备份。
逻辑备份：只备份database 的object的数据结构和数据（如对单个表的备份），一般用于用户的误操作的恢复，不能用于media failure ，只能恢复到备份点。
1) exp/imp
2)expd/impd
-------完整的备份应该以物理备份为主，逻辑备份辅助（用于备份一些重要的表）
2、手工备份：
1）数据库全备：备份database的所有数据文件和控制文件（datafiles、controlfile）
2）部分备份：只备份单个表空间或datafile(archivelog 模式）
3）一致性备份（冷备份）：在数据库正常关闭情况下做备份，数据库处于一致性状态。（可以用于archive和noarchive）
4）非一致性备份（热备份）：database 在open状态下备份（用于archive 模式）
3、一致性备份和非一致性备份的区别
1)一致性备份（冷备份）：数据库在正常关闭下进行备份，数据文件和控制文件处于一致性状态；缺点：数据库需要关闭
2）非一致性备份（热备份）：数据库在open状态下，进行备份，因为数据库处于使用状态，备份相对比较复杂。优点：数据库不需要关闭，用于7X24 事务处理的数据库
4、手工备份和恢复的命令
1）备份用os 命令
2）恢复用sql命令:recover
5、备份之前对数据库的检查:v$datafile\v$controlfile\v$logfile\dba_tablespaces\dba_data_files
1)检查需要备份的数据文件
10:50:10 SQL> select name from v$datafile;
NAME
------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/prod/system01.dbf
/u01/app/oracle/oradata/users01.dbf
/u01/app/oracle/oradata/prod/sysaux01.dbf
/u01/app/oracle/oradata/prod/users01.dbf
/u01/app/oracle/oradata/prod/example01.dbf
/u01/app/oracle/oradata/prod/test01.dbf
/u01/app/oracle/oradata/prod/undo_tbs01.dbf
/u01/app/oracle/oradata/users02.dbf
/u01/app/oracle/oradata/users03.dbf
/u01/app/oracle/oradata/users04.dbf
/u01/app/oracle/oradata/prod/index01.dbf
11 rows selected.
10:49:47 SQL> col file_name for a50
10:49:57 SQL> select file_id,file_name,tablespace_name from dba_data_files;
FILE_ID FILE_NAME TABLESPACE_NAME
---------- -------------------------------------------------- ------------------------------
4 /u01/app/oracle/oradata/prod/users01.dbf USERS
3 /u01/app/oracle/oradata/prod/sysaux01.dbf SYSAUX
2 /u01/app/oracle/oradata/users01.dbf USER01
1 /u01/app/oracle/oradata/prod/system01.dbf SYSTEM
5 /u01/app/oracle/oradata/prod/example01.dbf EXAMPLE
6 /u01/app/oracle/oradata/prod/test01.dbf TEST
7 /u01/app/oracle/oradata/prod/undo_tbs01.dbf UNDO_TBS
8 /u01/app/oracle/oradata/users02.dbf USER02
9 /u01/app/oracle/oradata/users03.dbf USER03
10 /u01/app/oracle/oradata/users04.dbf USER04
11 /u01/app/oracle/oradata/prod/index01.dbf INDEXES
11 rows selected.
2） 检查要备份控制文件
11:00:07 SQL> select name from v$controlfile;
NAME
------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/prod/control02.ctl
/u01/app/oracle/oradata/prod/control03.ctl
-------redo日志不需要做备份
------- 查看日志信息
11:00:43 SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 32 52428800 1 NO CURRENT 1128307 15-AUG-11
2 1 30 52428800 1 YES INACTIVE 1082762 15-AUG-11
3 1 31 52428800 1 YES INACTIVE 1108037 15-AUG-11
11:01:20 SQL> select member from v$logfile;
MEMBER
------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/prod/redo03.log
/u01/app/oracle/oradata/prod/redo02.log
/u01/app/oracle/oradata/prod/redo01.log
3）查看database模式
11:01:26 SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /disk1/arch/prod
Oldest online log sequence 30
Next log sequence to archive 32
Current log sequence 32
11:01:53 SQL>
6、归档和非归档模式备份的区别
1）归档模式可以做一致性备份和非一致性备份，恢复时可做完全恢复和不完全恢复。
2）非归档模式只能用于一致性完全备份，恢复时，只能恢复到最后一次完全备份状态
7、非一致性备份（热备份）的执行方式及热备份的监控（v$backup）
---------对只读的表空间不能做热备份，临时表空间不需要备份
1）在备份前执行begin backup （在数据文件上生成检查点，写入scn ，将来恢复的时候以此scn 为起点）
11:01:26 SQL> atler database begin backup ； //对整个库做热备份
alter database end backup；
alter tablespace users begin backup；//对表空间做备份（不能用于read only 的表空间）
alter tablespace users end backup；
2）备份期间利用v$backup 监控
11:14:33 SQL> alter tablespace test begin backup;
Tablespace altered.
11:14:42 SQL> select file#,checkpoint_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1128308
2 1128308
3 1128308
4 1128308
5 1128308
6 1130194 //在备份期间 ，scn 不发生变化
7 1128308
8 1128308
9 1128308
10 1128308
11 1128308
11 rows selected.
11:14:56 SQL> desc v$backup;
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
FILE# NUMBER
STATUS VARCHAR2(18)
CHANGE# NUMBER
TIME DATE
11:15:04 SQL> select * from v$backup;
FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- ---------
1 NOT ACTIVE 0
2 NOT ACTIVE 0
3 NOT ACTIVE 0
4 NOT ACTIVE 0
5 NOT ACTIVE 0
6 ACTIVE 1130194 15-AUG-11
7 NOT ACTIVE 0
8 NOT ACTIVE 0
9 NOT ACTIVE 0
10 NOT ACTIVE 0
11 NOT ACTIVE 0
11 rows selected.
11:15:08 SQL>
-----------备份完毕，执行end backup
11:15:08 SQL> alter tablespace test end backup;
Tablespace altered.
11:15:54 SQL> select * from v$backup;
FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- ---------
1 NOT ACTIVE 0
2 NOT ACTIVE 0
3 NOT ACTIVE 0
4 NOT ACTIVE 0
5 NOT ACTIVE 0
6 NOT ACTIVE 1130194 15-AUG-11
7 NOT ACTIVE 0
8 NOT ACTIVE 0
9 NOT ACTIVE 0
10 NOT ACTIVE 0
11 NOT ACTIVE 0
11 rows selected.
11:15:57 SQL>
8、控制文件备份
1） trace 文件：用于控制文件的重建（记录database的物理架构）
11:15:57 SQL> alter database backup controlfile to trace;
Database altered.
--------生成的trace 文件保存在udump的目录下
2） 二进制文件：用于database不完全恢复（或控制文件的恢复）
11:20:16 SQL> alter database backup controlfile to '/home/oracle/prod_control.bak';
Database altered.
9、spfile 备份 （pfile）
1）手工时可以不做备份，可以通过pfile 生成
23:16:11 SQL> create pfile from spfile;
File created.
2）rman 自动备份
10、dbv检查数据文件是否有坏块
1）在手工备份前，应该检查datafile 是否有坏块，备份完后对备份也做检查
[oracle@work ~]$ dbv
DBVERIFY: Release 10.2.0.1.0 - Production on Mon Aug 15 11:23:45 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Keyword Description (Default)
----------------------------------------------------
FILE File to Verify (NONE)
START Start Block (First Block of File)
END End Block (Last Block of File)
BLOCKSIZE Logical Block Size (8192)
LOGFILE Output Log (NONE)
FEEDBACK Display Progress (0)
PARFILE Parameter File (NONE)
USERID Username/Password (NONE)
SEGMENT_ID Segment ID (tsn.relfile.block) (NONE)
HIGH_SCN Highest Block SCN To Verify (NONE)
(scn_wrap.scn_base OR scn)
[oracle@work ~]$ dbv file=/u01/app/oracle/oradata/prod/system01.dbf
DBVERIFY: Release 10.2.0.1.0 - Production on Mon Aug 15 11:24:21 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/prod/system01.dbf
DBVERIFY - Verification complete
Total Pages Examined : 61440
Total Pages Processed (Data) : 36837
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 6859
Total Pages Failing (Index): 0
Total Pages Processed (Other): 1695
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 16049
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Highest block SCN : 1130761 (0.1130761)
[oracle@work ~]$
2）rman备份时，会自动检查
11、手工备份脚本
1）一致性备份（冷备份）
#cold backcup
remark set sql*plus variable to manipulate output
set feedback off heading off verify off trimspool off echo off time off timing off
set pagesize 0 linesize 200
remark set sql*plus user variable used in this script
define bkdir='/disk1/backup/prod/cold_bak' //备份文件的存放位置
define bkscp='/disk1/backup/prod/close_cmd.sql' //执行备份的脚本，自动生成
prompt *** Spooling to &bkscp
remark create a command file with file backup commands
spool &bkscp
select 'host cp '|| name ||' &bkdir ' from v$datafile order by 1;
select 'host cp '|| name ||' &bkdir ' from v$controlfile order by 1;
spool off;
remark shutdown the database cleanly
shutdown immediate;
remark run the copy file commands form the operating system
@&bkscp
remark start the database again
startup;
2）非一致性备份（热备份）
set feedback off pagesize 0 heading off verify off linesize 100 trimspool on echo off time off timing off
define bakdir='/disk1/backup/prod/hot_bak'
define bakscp='/disk1/backup/prod/hot_cmd.sql'
define spo='&bakdir/hot_bak.lst'
prompt ***spooling to &bakscp
set serveroutput on
spool &bakscp
prompt spool &spo
prompt alter system switch logfile;;
declare
cursor cur_tablespace is
select tablespace_name from dba_tablespaces where status <>'READ ONLY' and contents not like '%TEMP%';
cursor cur_datafile (tn varchar2) is
select file_name from dba_data_files where tablespace_name=tn;
begin
for ct in cur_tablespace loop
dbms_output.put_line('alter tablespace '||ct.tablespace_name ||' begin backup; ');
for cd in cur_datafile(ct.tablespace_name) loop
dbms_output.put_line('host cp '||cd.file_name||' &bakdir');
end loop;
dbms_output.put_line('alter tablespace '||ct.tablespace_name||' end backup;');
end loop;
end;
/
prompt archive log list;;
prompt spool off;;
spool off;
@&bakscp
------手工备份会备份database里datafile的所有数据块
06:37:33 SQL> select file#,name,bytes/1024/1024 "Size" from v$datafile;
FILE# NAME Size
---------- -------------------------------------------------- ----------
1 /u01/app/oracle/oradata/prod/system01.dbf 480
2 /u01/app/oracle/oradata/prod/users01.dbf 100
3 /u01/app/oracle/oradata/prod/sysaux01.dbf 250
4 /u01/app/oracle/oradata/prod/index01.dbf 100
5 /u01/app/oracle/oradata/prod/example01.dbf 100
6 /u01/app/oracle/oradata/prod/test01.dbf 10
7 /u01/app/oracle/oradata/prod/undo_tbs01.dbf 100
8 /u01/app/oracle/oradata/prod/test02.dbf 10
9 /u01/app/oracle/oradata/prod/cuug01.dbf 10
9 rows selected.
06:37:47 SQL> !
[oracle@work ~]$ ls -lth /disk1/backup/prod/close_bak
total 1.2G
-rw-r----- 1 oracle oinstall 6.8M Aug 17 04:38 control02.ctl
-rw-r----- 1 oracle oinstall 6.8M Aug 17 04:38 control03.ctl
-rw-r----- 1 oracle oinstall 6.8M Aug 17 04:38 control01.ctl
-rw-r----- 1 oracle oinstall 101M Aug 17 04:38 users01.dbf
-rw-r----- 1 oracle oinstall 101M Aug 17 04:38 undo_tbs01.dbf
-rw-r----- 1 oracle oinstall 11M Aug 17 04:38 test01.dbf
-rw-r----- 1 oracle oinstall 11M Aug 17 04:38 test02.dbf
-rw-r----- 1 oracle oinstall 481M Aug 17 04:38 system01.dbf
-rw-r----- 1 oracle oinstall 251M Aug 17 04:37 sysaux01.dbf
-rw-r----- 1 oracle oinstall 101M Aug 17 04:37 index01.dbf
-rw-r----- 1 oracle oinstall 101M Aug 17 04:37 example01.dbf
-rw-r----- 1 oracle oinstall 11M Aug 17 04:37 cuug01.dbf
[oracle@work ~]$
第五章：手工完全恢复
1、完全恢复；通过备份、归档日志、current redo 日志 ，将database恢复到failure 前的最后一次commit 状态。
media recover的原因？
----------由于media failure 导致数据文件或controlfile 丢失
media recover的分类？
------------归档模式
1）完全恢复
2）不完全恢复
--------------非归档模式
1)恢复到最后一次备份
2、instance recover 和 media recover 区别：
-------instance recover ：instance 没有正常关闭 ，由smon 执行
-------media recover：因为介质failure，文件丢失，需dba 通过备份和redo 来恢复
3、media recover的步骤：
1、restore 转储：将备份恢复到丢失文件的原位置
2、recover 恢复： 利用redo 日志，将备份点后的数据块通过redo 日志进行重做
4、如何restore 和 recover
1）restore：手工恢复用的是os 下的拷贝命令。如cp
2）recover： sql 命令
5、非归档模式下的数据恢复
1）转储所有的datafile 和controlfile
2）如果日志以切换，历史日志被覆盖，只能恢复到最近备份；如果日志没有发生切换，可以恢复到最后commit 状态
6、归档模式下的数据恢复
1）完全恢复
2）不完全恢复
7、完全恢复和不完全恢复的区别
1）完全恢复：需要所有的备份和redo 日志，可以将datafile恢复到failure前得最后一次commit，不会出现数据丢失
2）不完全恢复：通过备份和日志将database恢复到过去的某个时间点，有数据丢失。（尽量避免）
8、完全恢复的步骤
1）restore ：转储datafile
2）recover：利用归档日志和当前的redo 做recover
9、recover database：当大部分datafile丢失，只能mount状态下(system 表空间数据文件被破坏
recover tablespace：tablespace 的数据文件都丢失了，在open状态
recover datafile ：当单个datafile丢失，可以在mount 或 open 状态
10、恢复过程查看的视图：
1）v$recover_file：查看需要恢复的datafile
2）v$recovery_log： 查看recover 需要的redo 日志
3）v$archvied_log：查看已经归档的日志
案例1：recover database
1、 media failure 丢失大部分数据文件
1）模拟环境
05:45:49 SQL> select * from test;
ID
----------
1
2
3
05:45:52 SQL> insert into test values (4);
1 row created.
05:46:01 SQL> commit;
Commit complete.
05:46:02 SQL> insert into test values (5);
1 row created.
05:46:32 SQL> commit;
Commit complete.
05:46:34 SQL> insert into test values (6);
1 row created.
05:46:48 SQL> commit;
Commit complete.
05:46:49 SQL> insert into test values (7);
1 row created.
05:47:15 SQL> commit;
Commit complete.
05:46:08 SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 38 52428800 1 NO CURRENT 1187992 16-AUG-11
2 1 36 52428800 1 YES INACTIVE 1184326 16-AUG-11
3 1 37 52428800 1 YES INACTIVE 1187989 16-AUG-11
05:46:13 SQL> alter system switch logfile;
System altered.
05:46:43 SQL> alter system archive log current;
System altered.
05:46:58 SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 38 52428800 1 YES ACTIVE 1187992 16-AUG-11
2 1 39 52428800 1 YES ACTIVE 1188675 16-AUG-11
3 1 40 52428800 1 NO CURRENT 1188689 16-AUG-11
05:47:03 SQL> alter system archive log current;
System altered.
05:47:25 SQL>
05:47:16 SQL> insert into test values (8);
1 row created.
05:47:29 SQL> commit;
Commit complete.
05:47:30 SQL> insert into test values (9);
1 row created.
05:47:32 SQL> select * from test;
ID
----------
1
2
3
4
5
6
7
8
9
9 rows selected.
05:47:38 SQL>
2）模拟介质失败
[oracle@work ~]$ rm /u01/app/oracle/oradata/prod/*.dbf
3）启动database
05:48:57 SQL> startup
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 1219184 bytes
Variable Size 79693200 bytes
Database Buffers 230686720 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/u01/app/oracle/oradata/prod/system01.dbf'
05:49:03 SQL> select file#,error from v$recover_file;
FILE# ERROR
---------- -----------------------------------------------------------------
1 FILE NOT FOUND
3 FILE NOT FOUND
5 FILE NOT FOUND
6 FILE NOT FOUND
7 FILE NOT FOUND
4） 启动失败，需要做介质恢复 ，首先restore
[oracle@work ~]$ cp /disk1/backup/prod/close_bak/*.dbf /u01/app/oracle/oradata/prod/
--------recover database
05:51:48 SQL> select * from v$recovery_log;
THREAD# SEQUENCE# TIME
---------- ---------- ---------
ARCHIVE_NAME
------------------------------------------------------------------------------------------------------------------------
1 38 16-AUG-11
/disk1/arch/prod/arch_38_1_758481658.log
---------查看恢复需要的归档日志
05:51:58 SQL> select file#,checkpoint_change# from v$datafile;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1188700
2 1188700
3 1188700
4 1188700
5 1188700
6 1188700
7 1188700
7 rows selected.
05:52:42 SQL> select file#,checkpoint_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1188419
2 1188700
3 1188419
4 1188700
5 1188419
6 1188419
7 1188419
7 rows selected.
-----------控制文件记录的scn 应大于需恢复的数据文件头部的scn
5） recover database
05:52:49 SQL> recover database;
ORA-00279: change 1188419 generated at 08/16/2011 05:43:18 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_38_1_758481658.log
ORA-00280: change 1188419 for thread 1 is in sequence #38
05:53:46 Specify log: {
auto
Log applied.
Media recovery complete.
查看告警日志：
ALTER DATABASE RECOVER database
Tue Aug 16 05:53:46 2011
Media Recovery Start
ORA-279 signalled during: ALTER DATABASE RECOVER database ...
Tue Aug 16 05:54:13 2011
ALTER DATABASE RECOVER CONTINUE DEFAULT
Tue Aug 16 05:54:13 2011
Media Recovery Log /disk1/arch/prod/arch_38_1_758481658.log
Tue Aug 16 05:54:14 2011
Recovery of Online Redo Log: Thread 1 Group 2 Seq 39 Reading mem 0
Mem# 0 errs 0: /u01/app/oracle/oradata/prod/redo02.log
Tue Aug 16 05:54:14 2011
Recovery of Online Redo Log: Thread 1 Group 3 Seq 40 Reading mem 0
Mem# 0 errs 0: /u01/app/oracle/oradata/prod/redo03.log
Tue Aug 16 05:54:14 2011
Recovery of Online Redo Log: Thread 1 Group 1 Seq 41 Reading mem 0
Mem# 0 errs 0: /u01/app/oracle/oradata/prod/redo01.log
Tue Aug 16 05:54:14 2011
Media Recovery Complete (prod)
Completed: ALTER DATABASE RECOVER CONTINUE DEFAULT
6）验证：
05:54:17 SQL> alter database open;
Database altered.
05:55:31 SQL> select * from scott.test;
ID
----------
1
2
3
4
5
6
7
8
8 rows selected.
05:55:40 SQL> select file#,checkpoint_change# from v$datafile;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1208722
2 1208722
3 1208722
4 1208722
5 1208722
6 1208722
7 1208722
7 rows selected.
05:57:58 SQL> select file#,checkpoint_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1208722
2 1208722
3 1208722
4 1208722
5 1208722
6 1208722
7 1208722
7 rows selected.
05:58:03 SQL>
案例2： recover tablespace
2、恢复表空间（删除了tablespace的所有的datafile）
1）模拟环境
SQL> conn scott/tiger
Connected.
06:05:36 SQL>
06:05:36 SQL> select * from tab;
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
TEST TABLE
06:05:39 SQL> create table t01 (id int ) tablespace test;
06:06:13 SQL> insert into t01 values (1) ;
06:06:23 SQL> insert into t01 values (2) ;
06:06:25 SQL> insert into t01 values (3) ;
06:06:27 SQL> commit;
06:06:28 SQL> select * from t01;
1
2
3
06:06:32 SQL>
06:06:55 SQL> shutdown abort
ORACLE instance shut down.
06:06:59 SQL> !
[oracle@work ~]$ exit
exit
06:07:05 SQL> !
[oracle@work ~]$ rm /u01/app/oracle/oradata/prod/test*.dbf
[oracle@work ~]$
2）启动数据库
06:07:34 SQL> startup
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 1219184 bytes
Variable Size 79693200 bytes
Database Buffers 230686720 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/u01/app/oracle/oradata/prod/test01.dbf'
06:07:41 SQL> select file#,error from v$recover_file;
FILE# ERROR
---------- -----------------------------------------------------------------
6 FILE NOT FOUND
8 FILE NOT FOUND
2）转储数据文件
[oracle@work ~]$ cp /disk1/backup/prod/close_bak/test*.dbf /u01/app/oracle/oradata/prod/
3）数据文件offline
06:09:10 SQL> alter database datafile 6,8 offline;
Database altered.
06:09:40 SQL> alter database open;
Database altered.
06:09:49 SQL>
4） recover tablespace
06:09:49 SQL> recover tablespace test;
Media recovery complete.
查看告警日志：
ALTER DATABASE RECOVER tablespace test
Tue Aug 16 06:10:33 2011
Media Recovery Start
Tue Aug 16 06:10:33 2011
Recovery of Online Redo Log: Thread 1 Group 2 Seq 45 Reading mem 0
Mem# 0 errs 0: /u01/app/oracle/oradata/prod/redo02.log
Tue Aug 16 06:10:33 2011
Recovery of Online Redo Log: Thread 1 Group 3 Seq 46 Reading mem 0
Mem# 0 errs 0: /u01/app/oracle/oradata/prod/redo03.log
Tue Aug 16 06:10:34 2011
Media Recovery Complete (prod)
Completed: ALTER DATABASE RECOVER tablespace test
5）验证：
06:10:36 SQL> alter database datafile 6,8 online;
Database altered.
06:10:46 SQL> select * from scott.t01;
ID
----------
1
2
3
06:10:52 SQL>
案例4：（recover tablespace ，database open状态）
--------------database在open 状态下恢复数据文件（除了system tablespace）
1） 模拟环境：
06:10:52 SQL> insert into scott.t01 values (4);
1 row created.
06:13:12 SQL> insert into scott.t01 values (5);
1 row created.
06:13:13 SQL> insert into scott.t01 values (6);
1 row created.
06:13:15 SQL> commit;
Commit complete.
06:13:17 SQL> select * from scott.t01;
ID
----------
1
2
3
4
5
6
6 rows selected.
--------在open 状态下删除datafile
[oracle@work ~]$ rm /u01/app/oracle/oradata/prod/test*.dbf
[oracle@work ~]$
06:14:57 SQL> alter system flush buffer_cache; //清除data buffer
System altered.
06:15:09 SQL> select * from scott.t01;
select * from scott.t01
*
ERROR at line 1:
ORA-01116: error in opening database file 8
ORA-01110: data file 8: '/u01/app/oracle/oradata/prod/test02.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
2）查看datafile信息
06:16:59 SQL> select a.name,b.file#,b.name from v$tablespace a,v$datafile b
06:17:15 2 where a.ts#=b.ts#;
06:17:21 SQL> col name for a50
06:17:25 SQL> /
NAME FILE# NAME
-------------------------------------------------- ---------- --------------------------------------------------
SYSTEM 1 /u01/app/oracle/oradata/prod/system01.dbf
SYSAUX 3 /u01/app/oracle/oradata/prod/sysaux01.dbf
USERS 2 /u01/app/oracle/oradata/prod/users01.dbf
EXAMPLE 5 /u01/app/oracle/oradata/prod/example01.dbf
TEST 8 /u01/app/oracle/oradata/prod/test02.dbf
TEST 6 /u01/app/oracle/oradata/prod/test01.dbf
UNDO_TBS 7 /u01/app/oracle/oradata/prod/undo_tbs01.dbf
INDEXES 4 /u01/app/oracle/oradata/prod/index01.dbf
8 rows selected.
---------对数据文件脱机
06:17:39 SQL> alter database datafile 6,8 offline;
Database altered.
3）转储datafile
[oracle@work ~]$ cp /disk1/backup/prod/close_bak/test*.dbf /u01/app/oracle/oradata/prod/
4）recover datafile 或 recover tablespace
06:19:39 SQL> recover datafile 6,8;
Media recovery complete.
告警日志信息：
ALTER DATABASE RECOVER datafile 6,8
Tue Aug 16 06:19:44 2011
Media Recovery Start
Tue Aug 16 06:19:44 2011
Recovery of Online Redo Log: Thread 1 Group 2 Seq 45 Reading mem 0
Mem# 0 errs 0: /u01/app/oracle/oradata/prod/redo02.log
Tue Aug 16 06:19:44 2011
Recovery of Online Redo Log: Thread 1 Group 3 Seq 46 Reading mem 0
Mem# 0 errs 0: /u01/app/oracle/oradata/prod/redo03.log
Tue Aug 16 06:19:44 2011
Media Recovery Complete (prod)
Completed: ALTER DATABASE RECOVER datafile 6,8
Tue Aug 16 06:19:55 2011
alter database datafile 6,8 online
Tue Aug 16 06:19:55 2011
Completed: alter database datafile 6,8 online
4）验证
06:19:45 SQL> alter database datafile 6,8 online;
Database altered.
06:19:55 SQL> select * from scott.t01;
ID
----------
1
2
3
4
5
6
7
7 rows selected.
06:19:59 SQL>
案例4：recover datafile
---------------新建的表空间，没有备份，datafile被删除
1)模拟环境
06:21:47 SQL> create tablespace cuug
06:21:55 2 datafile '/u01/app/oracle/oradata/prod/cuug01.dbf' size 10m;
Tablespace created.
06:22:06 SQL> conn scott/tiger
Connected.
06:22:11 SQL>
06:22:22 SQL> create table t02 (id int) tablespace cuug;
Table created.
06:22:25 SQL> insert into t02 values (1);
1 row created.
06:22:33 SQL> insert into t02 values (2);
1 row created.
06:22:34 SQL> insert into t02 values (3);
1 row created.
06:22:36 SQL> commit;
Commit complete.
06:22:38 SQL> select * from t02;
ID
----------
1
2
3
06:22:44 SQL> conn /as sysdba
Connected.
06:23:34 SQL>
06:23:34 SQL> shutdown abort
ORACLE instance shut down.
06:23:38 SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@work ~]$ rm /u01/app/oracle/oradata/prod/cuug01.dbf
[oracle@work ~]$
2）启动 database
06:24:07 SQL> startup
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 1219184 bytes
Variable Size 79693200 bytes
Database Buffers 230686720 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 9 - see DBWR trace file
ORA-01110: data file 9: '/u01/app/oracle/oradata/prod/cuug01.dbf'
06:24:13 SQL> select file# ,error from v$recover_file;
FILE# ERROR
---------- -----------------------------------------------------------------
9 FILE NOT FOUND
3）恢复
06:24:26 SQL> alter database datafile 9 offline;
Database altered.
06:24:46 SQL> alter database open;
Database altered.
06:24:57 SQL>
-----没有备份，不能做restore
06:24:57 SQL> alter database create datafile '/u01/app/oracle/oradata/prod/cuug01.dbf';
Database altered.
-------重建数据文件（通过os 删除，在controlfile文件仍然有datafile 记录），然后recover datafile
06:25:45 SQL> recover datafile 9;
Media recovery complete.
告警日志信息：
ALTER DATABASE RECOVER datafile 9
Tue Aug 16 06:26:05 2011
Media Recovery Start
Tue Aug 16 06:26:05 2011
Recovery of Online Redo Log: Thread 1 Group 3 Seq 46 Reading mem 0
Mem# 0 errs 0: /u01/app/oracle/oradata/prod/redo03.log
Tue Aug 16 06:26:05 2011
Recovery of Online Redo Log: Thread 1 Group 1 Seq 47 Reading mem 0
Mem# 0 errs 0: /u01/app/oracle/oradata/prod/redo01.log
Tue Aug 16 06:26:05 2011
Media Recovery Complete (prod)
Completed: ALTER DATABASE RECOVER datafile 9
4）验证
06:26:09 SQL> alter database datafile 9 online;
Database altered.
06:26:16 SQL> select * from scott.t02;
ID
----------
1
2
3
06:26:21 SQL>
---------将数据文件恢复到新的位置
1、模拟环境
[oracle@work ~]$ sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.1.0 - Production on Sat Oct 22 23:41:12 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
23:41:13 SQL>
23:41:20 SQL> insert into scott.lxtb2 values (3);
1 row created.
23:41:25 SQL> insert into scott.lxtb2 values (4);
1 row created.
23:41:26 SQL> insert into scott.lxtb2 values (5);
1 row created.
23:41:28 SQL> commit;
Commit complete.
23:41:29 SQL> shutdown abort
ORACLE instance shut down.
[oracle@work ~]$ rm /u01/app/oracle/oradata/test/lxtbs01.dbf
23:41:35 SQL> startup
ORACLE instance started.
Total System Global Area 440401920 bytes
Fixed Size 1219904 bytes
Variable Size 171967168 bytes
Database Buffers 264241152 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/u01/app/oracle/oradata/test/lxtbs01.dbf'
23:42:01 SQL> select file#,error from v$recover_file;
FILE# ERROR
---------- -----------------------------------------------------------------
6 FILE NOT FOUND
2、对数据文件进行恢复，并恢复到新的位置
23:43:50 SQL> alter database datafile 6 offline;
Database altered.
[oracle@work ~]$ cp /disk1/backup/test/close_bak/lxtbs01.dbf /disk1/oradata/test/
23:43:53 SQL> alter database open;
Database altered.
23:44:01 SQL> alter database rename file '/u01/app/oracle/oradata/test/lxtbs01.dbf' to '/disk1/oradata/test/lxtbs01.dbf';
Database altered.
23:44:47 SQL> recover datafile 6;
Media recovery complete.
23:44:56 SQL> alter database datafile 6 online;
Database altered.
23:45:03 SQL> select * from scott.lxtb2;
ID
----------
1
2
3
4
5
23:45:10 SQL> col file_name for a50
23:45:15 SQL> select file_id,file_name,tablespace_name from dba_data_files;
FILE_ID FILE_NAME TABLESPACE_NAME
---------- -------------------------------------------------- ------------------------------
5 /u01/app/oracle/oradata/test/lob_16k01.dbf LOB_16K
4 /u01/app/oracle/oradata/test/users01.dbf USERS
3 /u01/app/oracle/oradata/test/sysaux01.dbf SYSAUX
2 /u01/app/oracle/oradata/test/rtbs01.dbf RTBS
1 /u01/app/oracle/oradata/test/system01.dbf SYSTEM
6 /disk1/oradata/test/lxtbs01.dbf LXTBS1
9 /u01/app/oracle/oradata/test/undotbs1.dbf UNDOTBS1
14 /u01/app/oracle/oradata/test/indx01.dbf INDX
8 rows selected.
3、将数据文件迁移到原来的位置
23:45:26 SQL> alter tablespace lxtbs1 offline;
Tablespace altered.
23:46:15 SQL> alter database rename file '/disk1/oradata/test/lxtbs01.dbf' to '/u01/app/oracle/oradata/test/lxtbs01.dbf' ;
Database altered.
23:47:08 SQL> alter tablespace lxtbs online;
alter tablespace lxtbs online
*
ERROR at line 1:
ORA-00959: tablespace 'LXTBS' does not exist
23:47:15 SQL> alter tablespace lxtbs1 online;
Tablespace altered.
23:47:19 SQL> select file_id,file_name,tablespace_name from dba_data_files;
FILE_ID FILE_NAME TABLESPACE_NAME
---------- -------------------------------------------------- ------------------------------
5 /u01/app/oracle/oradata/test/lob_16k01.dbf LOB_16K
4 /u01/app/oracle/oradata/test/users01.dbf USERS
3 /u01/app/oracle/oradata/test/sysaux01.dbf SYSAUX
2 /u01/app/oracle/oradata/test/rtbs01.dbf RTBS
1 /u01/app/oracle/oradata/test/system01.dbf SYSTEM
6 /u01/app/oracle/oradata/test/lxtbs01.dbf LXTBS1
9 /u01/app/oracle/oradata/test/undotbs1.dbf UNDOTBS1
14 /u01/app/oracle/oradata/test/indx01.dbf INDX
8 rows selected.
[oracle@work close_bak]$ rm /disk1/oradata/test/lxtbs01.dbf
-------------非归档模式
案例1： 历史日志没有被覆盖
1）切换到非归档模式
06:58:57 SQL>
06:58:57 SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /disk1/arch/prod
Oldest online log sequence 45
Next log sequence to archive 47
Current log sequence 47
06:59:04 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
07:00:15 SQL> startup mount
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 1219184 bytes
Variable Size 79693200 bytes
Database Buffers 230686720 bytes
Redo Buffers 2973696 bytes
Database mounted.
07:00:27 SQL> alter database noarchivelog;
Database altered.
07:00:33 SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /disk1/arch/prod
Oldest online log sequence 45
Current log sequence 47
07:00:35 SQL> alter database open;
Database altered.
07:00:45 SQL> !
[oracle@work ~]$ ls /disk1/backup/prod/close_bak/*
/disk1/backup/prod/close_bak/control02.ctl /disk1/backup/prod/close_bak/sysaux01.dbf /disk1/backup/prod/close_bak/undo_tbs01.dbf
/disk1/backup/prod/close_bak/control03.ctl /disk1/backup/prod/close_bak/system01.dbf /disk1/backup/prod/close_bak/users01.dbf
/disk1/backup/prod/close_bak/example01.dbf /disk1/backup/prod/close_bak/test01.dbf
/disk1/backup/prod/close_bak/index01.dbf /disk1/backup/prod/close_bak/test02.dbf
[oracle@work ~]$ rm /disk1/backup/prod/close_bak/*
[oracle@work ~]$ rm /disk1/arch/prod/*
[oracle@work ~]$ exit
exit
2）重新做数据库的全备（一致性备份-冷备份）
-------备份所有的datafile 和 controlfile
3）模拟环境
SQL> select * from v$log
2 ;
1 1 47 52428800 1 NO CURRENT 1250545 16-AUG-11
2 1 45 52428800 1 YES INACTIVE 1209278 16-AUG-11
3 1 46 52428800 1 YES INACTIVE 1229885 16-AUG-11
SQL> select * from scott.test;
1
2
3
4
5
6
7
8
SQL> set heading on
SQL> insert into scott.test values (9);
SQL> insert into scott.test values (10);
SQL> insert into scott.test values (11);
SQL> commit;
SQL> select * from v$log;
1 1 47 52428800 1 NO CURRENT 1250545 16-AUG-11
2 1 45 52428800 1 YES INACTIVE 1209278 16-AUG-11
3 1 46 52428800 1 YES INACTIVE 1229885 16-AUG-11
QL> select segment_name,tablespace_NAME from dba_segments
2 where segment_name='TEST';
TEST USERS
SQL> SHUTDOWN ABORT
ORACLE instance shut down.
2) 删除数据文件
[oracle@work ~]$ rm /u01/app/oracle/oradata/prod/users01.dbf
3）启动数据库
07:06:22 SQL> startup
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 1219184 bytes
Variable Size 79693200 bytes
Database Buffers 230686720 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/u01/app/oracle/oradata/prod/users01.dbf'
07:06:41 SQL> select file#,error from v$recover_file;
FILE# ERROR
---------- -----------------------------------------------------------------
2 FILE NOT FOUND
4）恢复
-----------restore datafile
[oracle@work ~]$ cp /disk1/backup/prod/close_bak/users01.dbf /u01/app/oracle/oradata/prod/
---------recover datafile
07:07:37 SQL> recover datafile 2;
告警日志信息：
ALTER DATABASE RECOVER datafile 2
Tue Aug 16 07:07:56 2011
Media Recovery Start
Tue Aug 16 07:07:56 2011
Recovery of Online Redo Log: Thread 1 Group 1 Seq 47 Reading mem 0
Mem# 0 errs 0: /u01/app/oracle/oradata/prod/redo01.log
Tue Aug 16 07:07:57 2011
Media Recovery Complete (prod)
Completed: ALTER DATABASE RECOVER datafile 2
Media recovery complete.
5）验证
07:08:00 SQL> alter database open;
Database altered.
07:08:08 SQL> select * from scott.test;
ID
----------
9
10
11
1
2
3
4
5
6
7
8
11 rows selected.
07:08:14 SQL>
案例2：日志发生切换，历史日志已经被覆盖
1）模拟环境
07:08:14 SQL> insert into scott.test values (12);
1 row created.
07:10:17 SQL> insert into scott.test values (13);
1 row created.
07:10:20 SQL> insert into scott.test values (14);
1 row created.
07:10:22 SQL> insert into scott.test values (15);
1 row created.
07:10:24 SQL> commit;
Commit complete.
07:10:25 SQL> alter system switch logfile;
System altered.
07:10:34 SQL> /
System altered.
07:10:39 SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 50 52428800 1 NO INACTIVE 1272776 16-AUG-11
2 1 51 52428800 1 NO ACTIVE 1272779 16-AUG-11
3 1 52 52428800 1 NO CURRENT 1272781 16-AUG-11
07:10:43 SQL> alter system switch logfile;
System altered.
07:10:45 SQL> alter system switch logfile;
System altered.
07:10:51 SQL> select * from scott.test;
ID
----------
9
10
11
1
2
3
12
13
14
15
4
5
6
7
8
15 rows selected.
07:10:57 SQL>
07:10:57 SQL> shutdown abort
ORACLE instance shut down.
07:11:35 SQL> !
[oracle@work ~]$ rm /u01/app/oracle/oradata/prod/users01.dbf
2）启动数据库
07:11:54 SQL> startup
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 1219184 bytes
Variable Size 79693200 bytes
Database Buffers 230686720 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/u01/app/oracle/oradata/prod/users01.dbf'
07:12:02 SQL> select file#,error from v$recover_file;
FILE# ERROR
---------- -----------------------------------------------------------------
2 FILE NOT FOUND
3）恢复
--------------restore datafile
cp /disk1/backup/prod/close_bak/users01.dbf /u01/app/oracle/oradata/prod/
---------recover datafile
07:13:11 SQL> recover datafile 2;
ORA-00279: change 1252332 generated at 08/16/2011 07:02:01 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_47_1_758481658.log
ORA-00280: change 1252332 for thread 1 is in sequence #47
07:13:15 Specify log: {
auto
ORA-00308: cannot open archived log '/disk1/arch/prod/arch_47_1_758481658.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00308: cannot open archived log '/disk1/arch/prod/arch_47_1_758481658.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
-------需要归档日志。。。。。。。。。。。。。。。。
-----------恢复需要转储所有的控制文件和datafile
07:13:20 SQL> select name from v$controlfile;
NAME
------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/prod/control02.ctl
/u01/app/oracle/oradata/prod/control03.ctl
07:14:12 SQL> shutdown
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
07:14:21 SQL> !
[oracle@work ~]$ cp /disk1/backup/prod/close_bak/control* /u01/app/oracle/oradata/prod/
[oracle@work ~]$ cp /disk1/backup/prod/close_bak/*.dbf /u01/app/oracle/oradata/prod/
-------------启动数据库到mount
07:15:58 SQL> startup mount
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 1219184 bytes
Variable Size 79693200 bytes
Database Buffers 230686720 bytes
Redo Buffers 2973696 bytes
Database mounted.
07:16:11 SQL> select file#,checkpoint_change# from v$datafile;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1252332
2 1252332
3 1252332
4 1252332
5 1252332
6 1252332
7 1252332
8 1252332
9 1252332
9 rows selected.
07:16:30 SQL> select file#,checkpoint_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1252332
2 1252332
3 1252332
4 1252332
5 1252332
6 1252332
7 1252332
8 1252332
9 1252332
9 rows selected.
07:16:35 SQL>
07:16:35 SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00314: log 1 of thread 1, expected sequence# doesn't match
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/prod/redo01.log'
---------如果此刻直接打开库，因为redo log 和controlfile、datafile 不同步，不能直接打开
07:17:07 SQL> recover database until cancel;
Media recovery complete.
------做不完全恢复
07:17:17 SQL> alter database open resetlogs;
Database altered.
------对database进行resetlogs 方式打开
查看告警日志信息：
ALTER DATABASE RECOVER database until cancel
Tue Aug 16 07:17:17 2011
Media Recovery Start
Media Recovery Not Required
Completed: ALTER DATABASE RECOVER database until cancel
Tue Aug 16 07:17:22 2011
alter database open resetlogs
RESETLOGS after complete recovery through change 1252332
Resetting resetlogs activation ID 170334582 (0xa271976)
Tue Aug 16 07:17:27 2011
Setting recovery target incarnation to 3
Tue Aug 16 07:17:27 2011
Assigning activation ID 171172278 (0xa33e1b6)
Thread 1 advanced to log sequence 2
Thread 1 opened at log sequence 2
Current log# 2 seq# 2 mem# 0: /u01/app/oracle/oradata/prod/redo02.log
Successful open of redo thread 1
07:17:38 SQL>
-------验证
07:17:38 SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 1 52428800 1 NO INACTIVE 1252333 16-AUG-11
2 1 2 52428800 1 NO CURRENT 1252334 16-AUG-11
3 1 0 52428800 1 YES UNUSED 0
07:19:22 SQL>
---------数据库被resetlog ，建议立刻做一个数据库的全备。
07:19:22 SQL> select * from scott.test;
ID
----------
1
2
3
4
5
6
7
8
8 rows selected.
----------只能恢复到最后一次备份
11、控制文件和redo 日志文件恢复
控制文件恢复
单个文件丢失：
[oracle@oracle dbs]$ rm /disk2/lx02/oradata/control03.ctl
[oracle@oracle dbs]$ sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Aug 1 06:14:54 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
06:14:54 SQL> startup
ORACLE instance started.
Total System Global Area 176160768 bytes
Fixed Size 1218364 bytes
Variable Size 88082628 bytes
Database Buffers 83886080 bytes
Redo Buffers 2973696 bytes
ORA-00205: error in identifying control file, check alert log for more info
通过告警日志获得信息：
ALTER DATABASE MOUNT
Mon Aug 1 06:14:57 2011
ORA-00202: control file: '/disk2/lx02/oradata/control03.ctl'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
06:14:57 SQL> shutdown
ORA-01507: database not mounted
ORACLE instance shut down.
06:15:14 SQL> !
[oracle@oracle dbs]$ cp /disk1/lx02/oradata/control02.ctl /disk2/lx02/oradata/control03.ctl
[oracle@oracle dbs]$ sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Aug 1 06:15:36 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
06:15:37 SQL> startup
ORACLE instance started.
Total System Global Area 176160768 bytes
Fixed Size 1218364 bytes
Variable Size 88082628 bytes
Database Buffers 83886080 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
06:15:47 SQL> select name from v$controlfile;
NAME
------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/lx02/control01.ctl
/disk1/lx02/oradata/control02.ctl
/disk2/lx02/oradata/control03.ctl
06:16:00 SQL>
所有的文件丢失：
06:16:00 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
06:17:22 SQL> !
[oracle@oracle dbs]$ rm /u01/app/oracle/oradata/lx02/control01.ctl
[oracle@oracle dbs]$ rm /disk1/lx02/oradata/control02.ctl
[oracle@oracle dbs]$ rm /disk2/lx02/oradata/control03.ctl
[oracle@oracle dbs]$ sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Aug 1 06:17:51 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
06:17:51 SQL> startup
ORACLE instance started.
Total System Global Area 176160768 bytes
Fixed Size 1218364 bytes
Variable Size 88082628 bytes
Database Buffers 83886080 bytes
Redo Buffers 2973696 bytes
ORA-00205: error in identifying control file, check alert log for more info
告警日志：
ALTER DATABASE MOUNT
Mon Aug 1 06:17:54 2011
ORA-00202: control file: '/u01/app/oracle/oradata/lx02/control01.ctl'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Mon Aug 1 06:17:54 2011
利用trace 文件重建
在nomount 状态
06:19:51 SQL>CREATE CONTROLFILE REUSE DATABASE "LX02" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 30
MAXINSTANCES 1
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/lx02/redo01a.log' SIZE 10M,
GROUP 2 '/u01/app/oracle/oradata/lx02/redo02a.log' SIZE 10M
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/lx02/system01.dbf',
'/u01/app/oracle/oradata/lx02/rtbs01.dbf',
'/u01/app/oracle/oradata/lx02/sysaux01.dbf',
'/u01/app/oracle/oradata/lx02/user01.dbf',
'/u01/app/oracle/oradata/lx02/example01.dbf',
'/u01/app/oracle/oradata/lx02/indx01.dbf',
'/u01/app/oracle/oradata/lx02/OLTP01.DBF'
CHARACTER SET ZHS16GBK
06:21:23 20 ;
Control file created.
06:21:27 SQL> alter database open resetlogs;
Database altered.
06:21:39 SQL>
用trace里SQL重建控制文件时，缺少数据文件
SQL> create tablespace new_ts
2 datafile '/oradata/test/new_ts01.dbf' size 20m;
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> ! mv /oradata/test/*.ctl /tmp/
SQL> startup ;
ORACLE instance started.
Total System Global Area 444596224 bytes
Fixed Size 1219904 bytes
Variable Size 125829824 bytes
Database Buffers 314572800 bytes
Redo Buffers 2973696 bytes
ORA-00205: error in identifying control file, check alert log for more info
-- 重建时 缺少 /oradata/test/new_ts01.dbf
SQL> CREATE CONTROLFILE SET DATABASE "TEST" RESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 '/oradata/test/redo01.log' SIZE 50M,
9 GROUP 2 '/oradata/test/redo02.log' SIZE 50M,
10 GROUP 3 '/oradata/test/redo03.log' SIZE 50M
11 -- STANDBY LOGFILE
12 DATAFILE
13 '/oradata/test/system01.dbf',
14 '/oradata/test/undotbs01.dbf',
15 '/oradata/test/sysaux01.dbf',
16 '/oradata/test/undotbs02.dbf',
17 '/oradata/test/example01.dbf',
18 '/oradata/test/myts01.dbf'
19 CHARACTER SET WE8ISO8859P1
20 ;
Control file created.
SQL> alter database open resetlogs;
Database altered.
SQL> select file_name, tablespace_name from dba_data_files;
FILE_NAME TABLESPACE_NAME
---------------------------------------- ------------------------------
/oradata/test/myts01.dbf MYTS
/oradata/test/example01.dbf EXAMPLE
/oradata/test/undotbs02.dbf UNDOTBS1
/oradata/test/sysaux01.dbf SYSAUX
/oradata/test/undotbs01.dbf UNDOTBS1
/oradata/test/system01.dbf SYSTEM
/u01/app/oracle/product/10.2/db1/dbs/MIS NEW_TS
SING00007
7 rows selected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 444596224 bytes
Fixed Size 1219904 bytes
Variable Size 125829824 bytes
Database Buffers 314572800 bytes
Redo Buffers 2973696 bytes
Database mounted.
SQL> alter database rename file '/u01/app/oracle/product/10.2/db1/dbs/MISSING00007' to '/oradata/test/new_ts01.dbf';
Database altered.
SQL> alter database open;
用backup control 恢复 （历史备份的控制文件 缺失当前一些 tablespace的信息）
SQL> alter database backup controlfile to '/tmp/control.bak' reuse;
Database altered.
SQL> create tablespace bak_cont_ts
2 datafile '/oradata/test/bak_cont_ts01.dbf' size 20m;
Tablespace created.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> ! mv /oradata/test/*.ctl /tmp/
SQL> startup;
ORACLE instance started.
Total System Global Area 444596224 bytes
Fixed Size 1219904 bytes
Variable Size 125829824 bytes
Database Buffers 314572800 bytes
Redo Buffers 2973696 bytes
ORA-00205: error in identifying control file, check alert log for more info
SQL> ! cp /tmp/control.bak /oradata/test/control01.ctl
SQL> ! cp /tmp/control.bak /oradata/test/control02.ctl
SQL> ! cp /tmp/control.bak /oradata/test/control03.ctl
SQL> alter database mount;
Database altered.
SQL> col name for a40
SQL> select name from v$datafile;
NAME
----------------------------------------
/oradata/test/system01.dbf
/oradata/test/undotbs01.dbf
/oradata/test/sysaux01.dbf
/oradata/test/undotbs02.dbf
/oradata/test/example01.dbf
/oradata/test/myts01.dbf
/oradata/test/new_ts01.dbf
7 rows selected.
SQL> recover database using backup controlfile;
ORA-00279: change 633436 generated at 11/10/2012 09:04:31 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/product/10.2/db1/dbs/arch/1_1_798973471.dbf
ORA-00280: change 633436 for thread 1 is in sequence #1
Specify log: {
auto
ORA-00308: cannot open archived log '/u01/app/oracle/product/10.2/db1/dbs/arch/1_1_798973471.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00308: cannot open archived log '/u01/app/oracle/product/10.2/db1/dbs/arch/1_1_798973471.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
SQL> select group#, sequence# from v$log;
GROUP# SEQUENCE#
---------- ----------
1 1
3 0
2 0
3 rows selected.
SQL> select member
2 from v$logfile
3 where group# = 1;
MEMBER
------------------------------------------------------------------------------------------------------------------------------------------------------
/oradata/test/redo01.log
1 row selected.
SQL> recover database using backup controlfile;
ORA-00279: change 633436 generated at 11/10/2012 09:04:31 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/product/10.2/db1/dbs/arch/1_1_798973471.dbf
ORA-00280: change 633436 for thread 1 is in sequence #1
Specify log: {
/oradata/test/redo01.log
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 8: '/oradata/test/bak_cont_ts01.dbf'
ORA-01112: media recovery not started
SQL> alter database create datafile 8 as '/oradata/test/bak_cont_ts01.dbf';
Database altered.
SQL> recover database using backup controlfile;
ORA-00279: change 633475 generated at 11/10/2012 09:16:20 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/product/10.2/db1/dbs/arch/1_1_798973471.dbf
ORA-00280: change 633475 for thread 1 is in sequence #1
Specify log: {
/oradata/test/redo01.log
Log applied.
Media recovery 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.
日志恢复
1、多元化成员中，单个成员丢失
05:10:06 SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 9 10485760 2 NO INACTIVE 384007 02-AUG-11
3 1 8 10485760 2 NO INACTIVE 384005 02-AUG-11
2 1 10 10485760 2 NO CURRENT 385481 02-AUG-11
05:10:12 SQL> !
[oracle@oracle ~]$ ls /disk2/lx01/oradata/
control03.ctl redo01a.log redo02a.log redo03a.log redo04a.log redo05a.log
[oracle@oracle ~]$ exit
exit
05:14:31 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
05:14:41 SQL> !
[oracle@oracle ~]$ rm /disk2/lx02/oradata/redo01a.log
[oracle@oracle ~]$ !sql
sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Aug 2 05:15:02 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
05:15:02 SQL> startup
ORACLE instance started.
Total System Global Area 251658240 bytes
Fixed Size 1218820 bytes
Variable Size 125830908 bytes
Database Buffers 121634816 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
05:15:12 SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 9 10485760 2 NO INACTIVE 384007 02-AUG-11
3 1 8 10485760 2 NO INACTIVE 384005 02-AUG-11
2 1 10 10485760 2 NO CURRENT 385481 02-AUG-11
05:15:24 SQL> desc v$logfile;
Name Null? Type
----------------------------------------------------------------------------------- -------- --------------------------------------------------------
GROUP# NUMBER
STATUS VARCHAR2(7)
TYPE VARCHAR2(7)
MEMBER VARCHAR2(513)
IS_RECOVERY_DEST_FILE VARCHAR2(3）
05:15:43 SQL> col member for a50
05:15:48 SQL> r
1* select group#,member ,status from v$logfile
GROUP# MEMBER STATUS
---------- -------------------------------------------------- -------
2 /disk2/lx02/oradata/redo02a.log
1 /disk2/lx02/oradata/redo01a.log INVALID
3 /disk2/lx02/oradata/redo03a.log
1 /disk1/lx02/oradata/redo01b.log
2 /disk1/lx02/oradata/redo02b.log
3 /disk1/lx02/oradata/redo03b.log
6 rows selected.
05:15:48 SQL>
告警日志：
Errors in file /u01/app/oracle/admin/lx02/bdump/lx02_lgwr_9105.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/disk2/lx02/oradata/redo01a.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
解决：
05:15:48 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
05:17:47 SQL> !
[oracle@oracle ~]$ cp /disk1/lx02/oradata/redo01b.log /disk2/lx02/oradata/redo01a.log
[oracle@oracle ~]$ !sql
sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Aug 2 05:18:02 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
05:18:02 SQL> startup
ORACLE instance started.
Total System Global Area 251658240 bytes
Fixed Size 1218820 bytes
Variable Size 125830908 bytes
Database Buffers 121634816 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
05:18:14 SQL> col member for a50
05:18:26 SQL> select group#,member ,status from v$logfile
05:18:29 2 ;
GROUP# MEMBER STATUS
---------- -------------------------------------------------- -------
2 /disk2/lx02/oradata/redo02a.log
1 /disk2/lx02/oradata/redo01a.log INVALID
3 /disk2/lx02/oradata/redo03a.log
1 /disk1/lx02/oradata/redo01b.log
2 /disk1/lx02/oradata/redo02b.log
3 /disk1/lx02/oradata/redo03b.log
6 rows selected.
05:18:31 SQL> alter system switch logfile;
System altered.
05:18:37 SQL> /
System altered.
05:18:39 SQL> select group#,member ,status from v$logfile
05:18:40 2 ;
GROUP# MEMBER STATUS
---------- -------------------------------------------------- -------
2 /disk2/lx02/oradata/redo02a.log
1 /disk2/lx02/oradata/redo01a.log
3 /disk2/lx02/oradata/redo03a.log
1 /disk1/lx02/oradata/redo01b.log
2 /disk1/lx02/oradata/redo02b.log
3 /disk1/lx02/oradata/redo03b.log
6 rows selected.
05:18:42 SQL>
2、非当前日志组所有成员丢失
05:19:42 SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 12 10485760 2 NO CURRENT 386507 02-AUG-11
3 1 11 10485760 2 NO INACTIVE 386505 02-AUG-11
2 1 10 10485760 2 NO INACTIVE 385481 02-AUG-11
05:19:45 SQL>
05:19:45 SQL> shutdown
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
05:19:59 SQL> !
[oracle@oracle ~]$ rm /disk2/lx02/oradata/redo02a.log
[oracle@oracle ~]$ rm /disk1/lx02/oradata/redo02b.log
[oracle@oracle ~]$ !sql
sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Aug 2 05:20:21 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
05:20:22 SQL> startup
ORACLE instance started.
Total System Global Area 251658240 bytes
Fixed Size 1218820 bytes
Variable Size 125830908 bytes
Database Buffers 121634816 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/disk2/lx02/oradata/redo02a.log'
ORA-00312: online log 2 thread 1: '/disk1/lx02/oradata/redo02b.log'
05:20:29 SQL> alter database clear logfile group 2;
Database altered.
05:21:00 SQL> alter database open;
Database altered.
05:21:08 SQL>
3、当前日志组丢失
05:22:16 SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 12 10485760 2 NO INACTIVE 386507 02-AUG-11
3 1 14 10485760 2 NO CURRENT 386751 02-AUG-11
2 1 13 10485760 2 NO ACTIVE 386654 02-AUG-11
05:22:17 SQL>
05:22:17 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
05:22:36 SQL> !
[oracle@oracle ~]$ rm /disk2/lx02/oradata/redo03a.log
[oracle@oracle ~]$ rm /disk1/lx02/oradata/redo03b.log
[oracle@oracle ~]$ !sql
sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Aug 2 05:23:03 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
05:23:03 SQL> startup
ORACLE instance started.
Total System Global Area 251658240 bytes
Fixed Size 1218820 bytes
Variable Size 125830908 bytes
Database Buffers 121634816 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/disk2/lx02/oradata/redo03a.log'
ORA-00312: online log 3 thread 1: '/disk1/lx02/oradata/redo03b.log'
05:23:10 SQL>
告警日志：
Errors in file /u01/app/oracle/admin/lx02/bdump/lx02_lgwr_9314.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/disk1/lx02/oradata/redo03b.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 3 thread 1: '/disk2/lx02/oradata/redo03a.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Tue Aug 2 05:23:10 2011
Errors in file /u01/app/oracle/admin/lx02/bdump/lx02_lgwr_9314.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/disk1/lx02/oradata/redo03b.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 3 thread 1: '/disk2/lx02/oradata/redo03a.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-313 signalled during: ALTER DATABASE OPEN...
解决：
05:23:10 SQL> alter database clear logfile group 3;
alter database clear logfile group 3
*
ERROR at line 1:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/disk1/lx02/oradata/redo03b.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 3 thread 1: '/disk2/lx02/oradata/redo03a.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
--------对于当前日志组不能clear
05:24:04 SQL> recover database until cancel;
Media recovery complete.
05:24:23 SQL> alter database open resetlogs;
Database altered.
05:24:41 SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 2 10485760 2 NO CURRENT 386892 02-AUG-11
3 1 1 10485760 2 NO INACTIVE 386891 02-AUG-11
2 1 0 10485760 2 YES UNUSED 0
05:24:44 SQL> alter system switch logfile;
System altered.
05:26:28 SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 2 10485760 2 NO ACTIVE 386892 02-AUG-11
3 1 1 10485760 2 NO INACTIVE 386891 02-AUG-11
2 1 3 10485760 2 NO CURRENT 387003 02-AUG-11
05:26:29 SQL>
数据文件和当点日志组全部丢失：
1、模拟案例
06:13:25 SQL> select count(*) from scott.emp2;
COUNT(*)
----------
20
06:13:27 SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 6 10485760 2 NO CURRENT 642483 14-JUN-12
2 1 5 10485760 2 YES INACTIVE 642481 14-JUN-12
3 1 3 10485760 2 YES INACTIVE 642243 14-JUN-12
4 1 4 10485760 2 YES INACTIVE 642245 14-JUN-12
06:13:32 SQL> insert into scott.emp2 select * from scott.emp2 where rownum <5;
4 rows created.
06:14:27 SQL> commit;
Commit complete.
06:14:29 SQL> select count(*) from scott.emp2;
COUNT(*)
----------
24
06:14:32 SQL>
-------数据库正常关闭
06:15:06 SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
06:15:49 SQL> !
2、数据文件和当前日志组文件都丢失
[oracle@RH54 hot_bak]$ ls /disk3/arch/test3/
arch_1_4_785916372.log arch_1_5_785916372.log
[oracle@RH54 hot_bak]$ rm /u01/app/oracle/oradata/test3/*.dbf
[oracle@RH54 hot_bak]$ rm /disk1/oradata/test3/redo01a.log
[oracle@RH54 hot_bak]$ rm /disk2/oradata/test3/redo01b.log
[oracle@RH54 hot_bak]$
06:17:43 SQL> startup
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 62916176 bytes
Database Buffers 218103808 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/u01/app/oracle/oradata/test3/system01.dbf'
06:17:52 SQL> select file# ,error from v$recover_file;
FILE# ERROR
---------- -----------------------------------------------------------------
1 FILE NOT FOUND
2 FILE NOT FOUND
3 FILE NOT FOUND
4 FILE NOT FOUND
5 FILE NOT FOUND
6 FILE NOT FOUND
9 FILE NOT FOUND
7 rows selected.
06:18:05 SQL> !
[oracle@RH54 hot_bak]$ cp /disk1/backup/test3/cold_bak/*.dbf /u01/app/oracle/oradata/test3/
[oracle@RH54 hot_bak]$ exit
exit
06:19:39 SQL> select file# ,checkpoint_change# from v$datafile;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 642873
2 642873
3 642873
4 642873
5 642873
6 642873
9 642873
7 rows selected.
06:19:57 SQL> select file# ,checkpoint_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 642589
2 642589
3 642589
4 642589
5 642589
6 642589
9 642589
7 rows selected.
06:20:03 SQL>
3、recover database 报错，缺少当前redo log （不能实现完全恢复）
06:20:03 SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/disk2/oradata/test3/redo01b.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 1 thread 1: '/disk1/oradata/test3/redo01a.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
06:21:11 SQL> recover database until cancel;
ORA-00279: change 642589 generated at 06/14/2012 06:11:34 needed for thread 1
ORA-00289: suggestion : /disk3/arch/test3/arch_1_6_785916372.log
ORA-00280: change 642589 for thread 1 is in sequence #6
06:21:44 Specify log: {
auto
ORA-00308: cannot open archived log '/disk3/arch/test3/arch_1_6_785916372.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00308: cannot open archived log '/disk3/arch/test3/arch_1_6_785916372.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
06:21:50 SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 6 10485760 2 NO CURRENT 642483 14-JUN-12
4 1 4 10485760 2 YES INACTIVE 642245 14-JUN-12
3 1 3 10485760 2 YES INACTIVE 642243 14-JUN-12
2 1 5 10485760 2 YES INACTIVE 642481 14-JUN-12
5、通过基于cancel 的不完全恢复来恢复数据库
06:22:09 SQL> recover database until cancel;
ORA-00279: change 642589 generated at 06/14/2012 06:11:34 needed for thread 1
ORA-00289: suggestion : /disk3/arch/test3/arch_1_6_785916372.log
ORA-00280: change 642589 for thread 1 is in sequence #6
06:22:16 Specify log: {
cancel
Media recovery cancelled.
06:22:20 SQL> alter database open resetlogs;
Database altered.
06:22:47 SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 1 10485760 2 NO CURRENT 642590 14-JUN-12
2 1 0 10485760 2 YES UNUSED 0
3 1 0 10485760 2 YES UNUSED 0
4 1 0 10485760 2 YES UNUSED 0
06:22:58 SQL> alter system switch logfile;
System altered.
06:23:03 SQL> /
System altered.
06:23:04 SQL> /
System altered.
06:23:05 SQL> select count(*) from scott.emp2;
COUNT(*)
----------
20
06:23:14 SQL>
--------只能恢复部分数据，由于datafile 和 current redolog 丢失，在recover时只能恢复到最后的archive log 所记录的数据。
第六章：不完全恢复(归档模式）
1、不完全恢复的特点：让整个database 回到过去某个时间点，不能避免数据丢失
2、不完全恢复（Incomplete recover） 使用环境：
1）过去的某个时间点重要的table被破坏
2）在做完全恢复时，丢失了归档日志或当前 online redo log
3）当误删除了表空间（有备份）
4）丢失了所有的控制文件，用备份的控制文件恢复
3、不完全恢复的类型：
1）基于时间点或 基于change （scn）的不完全恢复：用于恢复过去时间点误操作的table
2）基于cancel ：用于归档日志或当前redo log 丢失，不能做完全恢复
3）基于备份的controlfile：用于表空间的恢复
4、不完全恢复的操作步骤：
1）先对现在的database做全备
2）通过logmnr 找到误操作的时间点
3）转储所有的datafile
4）在mount状态下，对database做recover，恢复到过去的时间点或 scn 或cancel
--------通过alter database open resetlogs 打开库
5）将恢复出来的table做逻辑备份
6）再对database做完全恢复
5、logminer 工具的使用
-------对redo log 进行挖掘，找出在某个时间点所作的DDL 或DML 操作（包括：时间点、datablock scn 、sql语句）
1）对DML 分析
04:56:30 SQL> select * from test;
ID
----------
1
2
3
4
5
6
7
8
8 rows selected.
04:56:40 SQL> delete from test;
8 rows deleted.
04:56:48 SQL> commit;
Commit complete.
04:56:50 SQL> insert into test values (111);
1 row created.
04:57:44 SQL> insert into test values (222);
1 row created.
04:57:47 SQL> insert into test values (333);
1 row created.
04:57:49 SQL> commit;
Commit complete.
04:57:51 SQL> select * from test;
ID
----------
111
222
333
04:57:57 SQL>
04:57:01 SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 4 52428800 1 NO CURRENT 1255988 16-AUG-11
2 1 2 52428800 1 YES INACTIVE 1252334 16-AUG-11
3 1 3 52428800 1 YES INACTIVE 1255986 16-AUG-11
04:57:06 SQL> alter system archive log current;
System altered.
04:57:21 SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 4 52428800 1 YES ACTIVE 1255988 16-AUG-11
2 1 5 52428800 1 NO CURRENT 1259738 17-AUG-11
3 1 3 52428800 1 YES INACTIVE 1255986 16-AUG-11
04:57:22 SQL>
2）启用logmnr
-------添加database补充日志
04:59:50 SQL> alter database add supplemental log data;
Database altered.
----------查询日志（归档和当前日志）
04:57:21 SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 4 52428800 1 YES ACTIVE 1255988 16-AUG-11
2 1 5 52428800 1 NO CURRENT 1259738 17-AUG-11
3 1 3 52428800 1 YES INACTIVE 1255986 16-AUG-11
05:00:46 SQL> select member from v$logfile;
MEMBER
------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/prod/redo03.log
/u01/app/oracle/oradata/prod/redo02.log
/u01/app/oracle/oradata/prod/redo01.log
05:00:47 SQL> select name from v$archived_log;
NAME
------------------------------------------------------------------------------------------------------------------------
/disk1/arch/prod/arch_38_1_758481658.log
/disk1/arch/prod/arch_39_1_758481658.log
/disk1/arch/prod/arch_40_1_758481658.log
/disk1/arch/prod/arch_41_1_758481658.log
/disk1/arch/prod/arch_42_1_758481658.log
/disk1/arch/prod/arch_43_1_758481658.log
/disk1/arch/prod/arch_44_1_758481658.log
/disk1/arch/prod/arch_45_1_758481658.log
/disk1/arch/prod/arch_46_1_758481658.log
/disk1/arch/prod/arch_2_1_759309442.log
/disk1/arch/prod/arch_3_1_759309442.log
/disk1/arch/prod/arch_4_1_759309442.log
46 rows selected.
----------添加日志，分析
05:00:56 SQL> execute dbms_logmnr.add_logfile(logfilename=>'/disk1/arch/prod/arch_4_1_759309442.log',options=>dbms_logmnr.new);
PL/SQL procedure successfully completed.
05:02:58 SQL> execute dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/oradata/prod/redo02.log',options=>dbms_logmnr.addfile);
PL/SQL procedure successfully completed.
----------执行logmnr 分析
05:02:58 SQL>execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
PL/SQL procedure successfully completed.
--------查询分析结果
05:23:33 SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
05:06:13 SQL> COL SQL_REDO FOR A50
05:06:21 SQL> R
1* select username,scn,timestamp,sql_redo from v$logmnr_contents where seg_name='TEST'
USERNAME SCN TIMESTAMP SQL_REDO
------------------------------ ---------- ------------------- --------------------------------------------------
1259691 2011-08-17 04:56:50 delete from "SCOTT"."TEST" where "ID" = '4' and RO
WID = 'AAAM3bAACAAAAA/AAA';
1259691 2011-08-17 04:56:50 delete from "SCOTT"."TEST" where "ID" = '5' and RO
WID = 'AAAM3bAACAAAAA/AAB';
1259691 2011-08-17 04:56:50 delete from "SCOTT"."TEST" where "ID" = '6' and RO
WID = 'AAAM3bAACAAAAA/AAC';
1259691 2011-08-17 04:56:50 delete from "SCOTT"."TEST" where "ID" = '7' and RO
WID = 'AAAM3bAACAAAAA/AAD';
1259691 2011-08-17 04:56:50 delete from "SCOTT"."TEST" where "ID" = '8' and RO
WID = 'AAAM3bAACAAAAA/AAE';
05:06:25 SQL>
---------结束日志分析
05:06:25 SQL> execute dbms_logmnr.end_logmnr;
PL/SQL procedure successfully completed.
2）对DDL 操作分析
04:57:57 SQL> drop table test purge;
Table dropped.
05:09:39 SQL> create table test (id int ) tablespace test;
Table created.
05:09:55 SQL> insert into test values (1) ;
1 row created.
05:10:01 SQL> commit;
Commit complete.
---------设置logmnr 参数，存放数据字典文件
[oracle@work prod]$ mkdir /home/oracle/logmnr
05:11:28 SQL> show parameter utl
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
create_stored_outlines string
utl_file_dir string
05:11:31 SQL> alter system set utl_file_dir='/home/oracle/logmnr' scope=spfile;
System altered.
05:11:48 SQL> startup force
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 1219184 bytes
Variable Size 79693200 bytes
Database Buffers 230686720 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
05:12:08 SQL> show parameter utl
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
create_stored_outlines string
utl_file_dir string /home/oracle/logmnr
05:12:13 SQL>
---------建立数据字典文件dict.ora
05:12:13 SQL> execute dbms_logmnr_d.build('dict.ora','/home/oracle/logmnr',dbms_logmnr_d.store_in_flat_file);
PL/SQL procedure successfully completed.
---------查看日志信息
05:13:46 SQL> col name for a50
05:13:52 SQL> r
1* select name,sequence# from v$archived_log
NAME SEQUENCE#
-------------------------------------------------- ----------
/disk1/arch/prod/arch_38_1_758481658.log 38
/disk1/arch/prod/arch_39_1_758481658.log 39
/disk1/arch/prod/arch_40_1_758481658.log 40
/disk1/arch/prod/arch_41_1_758481658.log 41
/disk1/arch/prod/arch_42_1_758481658.log 42
/disk1/arch/prod/arch_43_1_758481658.log 43
/disk1/arch/prod/arch_44_1_758481658.log 44
/disk1/arch/prod/arch_45_1_758481658.log 45
/disk1/arch/prod/arch_46_1_758481658.log 46
/disk1/arch/prod/arch_2_1_759309442.log 2
/disk1/arch/prod/arch_3_1_759309442.log 3
/disk1/arch/prod/arch_4_1_759309442.log 4
/disk1/arch/prod/arch_5_1_759309442.log 5
47 rows selected.
05:14:24 SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 4 52428800 1 YES INACTIVE 1255988 16-AUG-11
2 1 5 52428800 1 YES INACTIVE 1259738 17-AUG-11
3 1 6 52428800 1 NO CURRENT 1280754 17-AUG-11
05:14:38 SQL> col member for a60
05:14:43 SQL>
1* select group#,member from v$logfile
GROUP# MEMBER
---------- ------------------------------------------------------------
3 /u01/app/oracle/oradata/prod/redo03.log
2 /u01/app/oracle/oradata/prod/redo02.log
1 /u01/app/oracle/oradata/prod/redo01.log
05:14:43 SQL>
-------------添加日志分析
05:14:43 SQL> execute dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/oradata/prod/redo03.log',options=>dbms_logmnr.new);
PL/SQL procedure successfully completed.
05:20:59 SQL> execute dbms_logmnr.add_logfile(logfilename=>'/disk1/arch/prod/arch_5_1_759309442.log',options=>dbms_logmnr.addfile);
PL/SQL procedure successfully completed.
------------执行分析
05:16:08 SQL> execute dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/logmnr/dict.ora',options=>dbms_logmnr.ddl_dict_tracking);
PL/SQL procedure successfully completed.
----------查看分析结果
05:23:33 SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
05:22:55 SQL> select username,scn,timestamp,sql_redo from v$logmnr_contents
05:22:57 2 WHERE USERNAME ='SCOTT' and lower(sql_redo) like '%table%';
USERNAME SCN TIMESTAMP SQL_REDO
------------------------------ ---------- ------------------- --------------------------------------------------
SCOTT 1260664 2011-08-17 05:09:38 drop table test purge;
SCOTT 1260698 2011-08-17 05:09:55 create table test (id int ) tablespace test;
05:23:33 SQL>
05:06:25 SQL> execute dbms_logmnr.end_logmnr;
PL/SQL procedure successfully completed.
不完全恢复案例：
案例1：
------------恢复过去某个时间点误操作的table
1）基于时间点
05:06:13 SQL> COL SQL_REDO FOR A50
05:06:21 SQL> R
1* select username,scn,timestamp,sql_redo from v$logmnr_contents where seg_name='TEST'
USERNAME SCN TIMESTAMP SQL_REDO
------------------------------ ---------- ------------------- --------------------------------------------------
1259691 2011-08-17 04:56:50 delete from "SCOTT"."TEST" where "ID" = '4' and RO
WID = 'AAAM3bAACAAAAA/AAA';
1259691 2011-08-17 04:56:50 delete from "SCOTT"."TEST" where "ID" = '5' and RO
WID = 'AAAM3bAACAAAAA/AAB';
1259691 2011-08-17 04:56:50 delete from "SCOTT"."TEST" where "ID" = '6' and RO
WID = 'AAAM3bAACAAAAA/AAC';
1259691 2011-08-17 04:56:50 delete from "SCOTT"."TEST" where "ID" = '7' and RO
WID = 'AAAM3bAACAAAAA/AAD';
1259691 2011-08-17 04:56:50 delete from "SCOTT"."TEST" where "ID" = '8' and RO
WID = 'AAAM3bAACAAAAA/AAE';
------通过以上logmnr 分析，将test表恢复到delete 之前（time：2011-08-17 04:56:50）
05:33:23 SQL> select * from test;
ID
----------
1
----------test 表现有的数据
----------将database启动到mount ，进行restore 和recover
05:35:38 SQL> conn /as sysdba
Connected.
05:35:46 SQL>
05:35:46 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
05:36:14 SQL>
05:36:14 SQL> startup mount
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 1219184 bytes
Variable Size 71304592 bytes
Database Buffers 239075328 bytes
Redo Buffers 2973696 bytes
Database mounted.
05:37:18 SQL>
----------restore 所有的datafile
[oracle@work ~]$ cp /disk1/backup/prod/close_bak/*.dbf /u01/app/oracle/oradata/prod/
---------基于时间的恢复（时间点为logmnr查询的时间）
05:40:13 SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
05:41:05 SQL> recover database until time '2011-08-17 04:56:50';
Media recovery complete.
查看告警日志：
ALTER DATABASE RECOVER database until time '2011-08-17 04:56:50'
Wed Aug 17 05:41:10 2011
Media Recovery Start
Wed Aug 17 05:41:10 2011
Recovery of Online Redo Log: Thread 1 Group 1 Seq 4 Reading mem 0
Mem# 0 errs 0: /u01/app/oracle/oradata/prod/redo01.log
Wed Aug 17 05:41:12 2011
Incomplete Recovery applied until change 1259690
Wed Aug 17 05:41:12 2011
Media Recovery Complete (prod)
Completed: ALTER DATABASE RECOVER database until time '2011-08-17 04:56:50'
Wed Aug 17 05:41:59 2011
-----------验证
05:41:13 SQL> alter database open resetlogs;
Database altered.
05:42:23 SQL> select * from scott.test;
ID
----------
1
2
3
4
5
6
7
8
8 rows selected.
案例2：
-----------恢复过去某个时间点误操作的表
1）基于change （scn）
05:45:41 SQL> truncate table scott.test;
Table truncated.
05:45:52 SQL> insert into scott.test values (11);
1 row created.
05:46:05 SQL> insert into scott.test values (22);
1 row created.
05:46:07 SQL> insert into scott.test values (33);
1 row created.
05:46:09 SQL> commit;
Commit complete.
05:46:11 SQL> select * from scott.test;
ID
----------
11
22
33
05:46:16 SQL>
--------通过logmr 分析出误操作的scn
05:52:30 SQL> select current_scn from v$database; //datablock 记录的scn
CURRENT_SCN
-----------
1260285
------------test 表里的记录
05:52:12 SQL> select * from scott.test;
ID
----------
1
2
3
4
5
6
7
8
8 rows selected
05:52:47 SQL> truncate table scott.test;
Table truncated
05:54:18 SQL> insert into scott.test values (11);
1 row created.
05:54:22 SQL> insert into scott.test values (22);
1 row created.
05:54:24 SQL> insert into scott.test values (33);
1 row created.
05:54:25 SQL> commit;
Commit complete.
05:54:26 SQL>
进行基于change的恢复：
---------在mount状态，进行restore 和recover
-------restore 所有的datafile
05:46:16 SQL> startup force mount
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 1219184 bytes
Variable Size 71304592 bytes
Database Buffers 239075328 bytes
Redo Buffers 2973696 bytes
Database mounted.
05:48:07 SQL>
[oracle@work ~]$ cp /disk1/backup/prod/close_bak/*.dbf /u01/app/oracle/oradata/prod/
05:55:07 SQL> select file#,checkpoint_change# from v$datafile;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1260061
2 1260061
3 1260061
4 1260061
5 1260061
6 1260061
7 1260061
8 1260061
9 1260061
9 rows selected.
05:57:00 SQL> select file#,checkpoint_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1258960
2 1258960
3 1258960
4 1258960
5 1258960
6 1258960
7 1258960
8 1258960
9 1258960
9 rows selected.
----------基于change 的recover
05:57:03 SQL> recover database until change 1260285;
ORA-00279: change 1258960 generated at 08/17/2011 04:37:14 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_4_1_759309442.log
ORA-00280: change 1258960 for thread 1 is in sequence #4
05:57:33 Specify log: {
auto
ORA-00279: change 1259691 generated at 08/17/2011 05:41:59 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_1_1_759390119.log
ORA-00280: change 1259691 for thread 1 is in sequence #1
ORA-00279: change 1260023 generated at 08/17/2011 05:44:31 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_2_1_759390119.log
ORA-00280: change 1260023 for thread 1 is in sequence #2
ORA-00278: log file '/disk1/arch/prod/arch_1_1_759390119.log' no longer needed for this recovery
ORA-00279: change 1260025 generated at 08/17/2011 05:44:32 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_3_1_759390119.log
ORA-00280: change 1260025 for thread 1 is in sequence #3
ORA-00278: log file '/disk1/arch/prod/arch_2_1_759390119.log' no longer needed for this recovery
Log applied.
Media recovery complete.
05:57:44 SQL>
查看告警日志：
ALTER DATABASE RECOVER database until change 1260285
Wed Aug 17 05:57:32 2011
Media Recovery Start
Media Recovery start incarnation depth : 2, target inc# : 5, irscn : 1259690
ORA-279 signalled during: ALTER DATABASE RECOVER database until change 1260285 ...
Wed Aug 17 05:57:38 2011
ALTER DATABASE RECOVER CONTINUE DEFAULT
Wed Aug 17 05:57:38 2011
Media Recovery Log /disk1/arch/prod/arch_4_1_759309442.log
ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
Wed Aug 17 05:57:40 2011
ALTER DATABASE RECOVER CONTINUE DEFAULT
Wed Aug 17 05:57:40 2011
Media Recovery Log /disk1/arch/prod/arch_1_1_759390119.log
ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
Wed Aug 17 05:57:40 2011
ALTER DATABASE RECOVER CONTINUE DEFAULT
Wed Aug 17 05:57:40 2011
Media Recovery Log /disk1/arch/prod/arch_2_1_759390119.log
ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
Wed Aug 17 05:57:41 2011
ALTER DATABASE RECOVER CONTINUE DEFAULT
Wed Aug 17 05:57:41 2011
Media Recovery Log /disk1/arch/prod/arch_3_1_759390119.log
Wed Aug 17 05:57:41 2011
Recovery of Online Redo Log: Thread 1 Group 2 Seq 1 Reading mem 0
Mem# 0 errs 0: /u01/app/oracle/oradata/prod/redo02.log
Wed Aug 17 05:57:41 2011
Recovery of Online Redo Log: Thread 1 Group 1 Seq 2 Reading mem 0
Mem# 0 errs 0: /u01/app/oracle/oradata/prod/redo01.log
Wed Aug 17 05:57:41 2011
Recovery of Online Redo Log: Thread 1 Group 3 Seq 3 Reading mem 0
Mem# 0 errs 0: /u01/app/oracle/oradata/prod/redo03.log
Wed Aug 17 05:57:41 2011
Incomplete Recovery applied until change 1260286
Wed Aug 17 05:57:41 2011
Media Recovery Complete (prod)
Completed: ALTER DATABASE RECOVER CONTINUE DEFAULT
--------验证
05:57:44 SQL> alter database open resetlogs;
Database altered.
05:59:46 SQL> select * from scott.test;
ID
----------
1
2
3
4
5
6
7
8
8 rows selected.
案例3：
---------在做完全恢复时，丢失了部分归档日志
1）基于cancel 的不完全恢复
-------模拟环境
06:01:59 SQL> select table_name,tablespace_name from user_tables;
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
DEPT USERS
EMP USERS
BONUS USERS
SALGRADE USERS
TEST USERS
T01 TEST
T02 CUUG
7 rows selected.
06:02:11 SQL> conn /as sysdba
Connected.
06:02:32 SQL>
06:02:32 SQL> select * from scott.test;
ID
----------
1
2
3
4
5
6
7
8
8 rows selected.
06:02:39 SQL> select name from v$archived_log;
NAME
--------------------------------------------------
/disk1/arch/prod/arch_1_1_759390714.log
/disk1/arch/prod/arch_2_1_759390714.log
/disk1/arch/prod/arch_3_1_759390714.log
/disk1/arch/prod/arch_1_1_759391164.log
/disk1/arch/prod/arch_2_1_759391164.log
06:03:06 SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 2 52428800 1 YES ACTIVE 1260553 17-AUG-11
2 1 3 52428800 1 NO CURRENT 1260555 17-AUG-11
3 1 1 52428800 1 YES ACTIVE 1260287 17-AUG-11
06:03:34 SQL> insert into scott.test values (9);
1 row created.
06:03:45 SQL> commit;
Commit complete.
06:03:46 SQL> alter system archive log current;
System altered.
06:03:53 SQL> insert into scott.test values (10);
1 row created.
06:03:57 SQL> commit;
Commit complete.
06:03:58 SQL> alter system archive log current;
System altered.
06:04:01 SQL> insert into scott.test values (12);
1 row created.
06:04:04 SQL> commit;
Commit complete.
06:04:09 SQL> alter system archive log current;
System altered.
06:04:11 SQL> insert into scott.test values (11);
1 row created.
06:04:13 SQL> commit;
Commit complete.
06:04:15 SQL> alter system archive log current;
System altered.
06:04:17 SQL> insert into scott.test values (13);
1 row created.
06:04:21 SQL> commit;
Commit complete.
06:04:23 SQL> insert into scott.test values (14);
1 row created.
06:04:25 SQL> commit;
Commit complete.
06:04:26 SQL>
06:05:03 SQL> select name from v$archived_log;
NAME
--------------------------------------------------
/disk1/arch/prod/arch_2_1_759391164.log
/disk1/arch/prod/arch_3_1_759391164.log
/disk1/arch/prod/arch_4_1_759391164.log
/disk1/arch/prod/arch_5_1_759391164.log
/disk1/arch/prod/arch_6_1_759391164.log
06:05:10 SQL> select * from scott.test;
ID
----------
1
2
3
9
10
12
11
13
14
4
5
6
7
8
14 rows selected.
--------------users 表空间datafile被误删除
[oracle@work ~]$ rm /u01/app/oracle/oradata/prod/users01.dbf
[oracle@work ~]$ mv /disk1/arch/prod/arch_4_1_759391164.log /disk1/arch/prod/arch_4_1_759391164.log.bak
[oracle@work ~]$ mv /disk1/arch/prod/arch_5_1_759391164.log /disk1/arch/prod/arch_5_1_759391164.log.bak
[oracle@work ~]$
--------------做完全恢复
06:07:27 SQL> startup
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 1219184 bytes
Variable Size 71304592 bytes
Database Buffers 239075328 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/u01/app/oracle/oradata/prod/users01.dbf'
06:07:36 SQL> select file#,error from v$recover_file;
FILE# ERROR
---------- -----------------------------------------------------------------
2 FILE NOT FOUND
-------启动database 失败，restore datafile
[oracle@work ~]$ cp /disk1/backup/prod/close_bak/users01.dbf /u01/app/oracle/oradata/prod/
---------recover datafile
06:09:07 SQL> recover datafile 2;
ORA-00279: change 1258960 generated at 08/17/2011 04:37:14 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_4_1_759309442.log
ORA-00280: change 1258960 for thread 1 is in sequence #4
06:09:15 Specify log: {
auto
ORA-00279: change 1259691 generated at 08/17/2011 05:41:59 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_1_1_759390119.log
ORA-00280: change 1259691 for thread 1 is in sequence #1
ORA-00279: change 1260023 generated at 08/17/2011 05:44:31 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_2_1_759390119.log
ORA-00280: change 1260023 for thread 1 is in sequence #2
ORA-00278: log file '/disk1/arch/prod/arch_1_1_759390119.log' no longer needed for this recovery
ORA-00279: change 1260025 generated at 08/17/2011 05:44:32 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_3_1_759390119.log
ORA-00280: change 1260025 for thread 1 is in sequence #3
ORA-00278: log file '/disk1/arch/prod/arch_2_1_759390119.log' no longer needed for this recovery
ORA-00279: change 1260060 generated at 08/17/2011 05:51:54 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_1_1_759390714.log
ORA-00280: change 1260060 for thread 1 is in sequence #1
ORA-00279: change 1260278 generated at 08/17/2011 05:52:29 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_2_1_759390714.log
ORA-00280: change 1260278 for thread 1 is in sequence #2
ORA-00278: log file '/disk1/arch/prod/arch_1_1_759390714.log' no longer needed for this recovery
ORA-00279: change 1260280 generated at 08/17/2011 05:52:30 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_3_1_759390714.log
ORA-00280: change 1260280 for thread 1 is in sequence #3
ORA-00278: log file '/disk1/arch/prod/arch_2_1_759390714.log' no longer needed for this recovery
ORA-00279: change 1260287 generated at 08/17/2011 05:59:24 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_1_1_759391164.log
ORA-00280: change 1260287 for thread 1 is in sequence #1
ORA-00278: log file '/disk1/arch/prod/arch_3_1_759390714.log' no longer needed for this recovery
ORA-00279: change 1260553 generated at 08/17/2011 06:00:05 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_2_1_759391164.log
ORA-00280: change 1260553 for thread 1 is in sequence #2
ORA-00278: log file '/disk1/arch/prod/arch_1_1_759391164.log' no longer needed for this recovery
ORA-00279: change 1260555 generated at 08/17/2011 06:00:06 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_3_1_759391164.log
ORA-00280: change 1260555 for thread 1 is in sequence #3
ORA-00278: log file '/disk1/arch/prod/arch_2_1_759391164.log' no longer needed for this recovery
ORA-00279: change 1260697 generated at 08/17/2011 06:03:53 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_4_1_759391164.log
ORA-00280: change 1260697 for thread 1 is in sequence #4
ORA-00278: log file '/disk1/arch/prod/arch_3_1_759391164.log' no longer needed for this recovery
ORA-00308: cannot open archived log '/disk1/arch/prod/arch_4_1_759391164.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
----------完全恢复失败，缺少归档日志：（/disk1/arch/prod/arch_4_1_759391164.log）
06:09:22 SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 2 needs media recovery
ORA-01110: data file 2: '/u01/app/oracle/oradata/prod/users01.dbf
---------------只能做基于cancel的不完全恢复
---转储所有的datafile
[oracle@work ~]$ cp /disk1/backup/prod/close_bak/*.dbf /u01/app/oracle/oradata/prod/
06:10:16 SQL> recover database until cancel;
ORA-00279: change 1258960 generated at 08/17/2011 04:37:14 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_4_1_759309442.log
ORA-00280: change 1258960 for thread 1 is in sequence #4
06:11:54 Specify log: {
auto
ORA-00279: change 1259691 generated at 08/17/2011 05:41:59 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_1_1_759390119.log
ORA-00280: change 1259691 for thread 1 is in sequence #1
ORA-00279: change 1260023 generated at 08/17/2011 05:44:31 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_2_1_759390119.log
ORA-00280: change 1260023 for thread 1 is in sequence #2
ORA-00278: log file '/disk1/arch/prod/arch_1_1_759390119.log' no longer needed for this recovery
ORA-00279: change 1260025 generated at 08/17/2011 05:44:32 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_3_1_759390119.log
ORA-00280: change 1260025 for thread 1 is in sequence #3
ORA-00278: log file '/disk1/arch/prod/arch_2_1_759390119.log' no longer needed for this recovery
ORA-00279: change 1260060 generated at 08/17/2011 05:51:54 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_1_1_759390714.log
ORA-00280: change 1260060 for thread 1 is in sequence #1
ORA-00279: change 1260278 generated at 08/17/2011 05:52:29 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_2_1_759390714.log
ORA-00280: change 1260278 for thread 1 is in sequence #2
ORA-00278: log file '/disk1/arch/prod/arch_1_1_759390714.log' no longer needed for this recovery
ORA-00279: change 1260280 generated at 08/17/2011 05:52:30 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_3_1_759390714.log
ORA-00280: change 1260280 for thread 1 is in sequence #3
ORA-00278: log file '/disk1/arch/prod/arch_2_1_759390714.log' no longer needed for this recovery
ORA-00279: change 1260287 generated at 08/17/2011 05:59:24 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_1_1_759391164.log
ORA-00280: change 1260287 for thread 1 is in sequence #1
ORA-00278: log file '/disk1/arch/prod/arch_3_1_759390714.log' no longer needed for this recovery
ORA-00279: change 1260553 generated at 08/17/2011 06:00:05 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_2_1_759391164.log
ORA-00280: change 1260553 for thread 1 is in sequence #2
ORA-00278: log file '/disk1/arch/prod/arch_1_1_759391164.log' no longer needed for this recovery
ORA-00279: change 1260555 generated at 08/17/2011 06:00:06 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_3_1_759391164.log
ORA-00280: change 1260555 for thread 1 is in sequence #3
ORA-00278: log file '/disk1/arch/prod/arch_2_1_759391164.log' no longer needed for this recovery
ORA-00279: change 1260697 generated at 08/17/2011 06:03:53 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_4_1_759391164.log
ORA-00280: change 1260697 for thread 1 is in sequence #4
ORA-00278: log file '/disk1/arch/prod/arch_3_1_759391164.log' no longer needed for this recovery
ORA-00308: cannot open archived log '/disk1/arch/prod/arch_4_1_759391164.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
--------在执行一次
06:12:01 SQL> recover database until cancel;
ORA-00279: change 1260697 generated at 08/17/2011 06:03:53 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_4_1_759391164.log
ORA-00280: change 1260697 for thread 1 is in sequence #4
06:12:03 Specify log: {
cancel
Media recovery cancelled.
------选择cancel ，在丢失的归档日志前终止recover
查看告警日志：
ALTER DATABASE RECOVER database until cancel
Wed Aug 17 06:11:53 2011
Media Recovery Start
Media Recovery start incarnation depth : 3, target inc# : 6, irscn : 1259690
ORA-279 signalled during: ALTER DATABASE RECOVER database until cancel ...
Wed Aug 17 06:11:56 2011
ALTER DATABASE RECOVER CONTINUE DEFAULT
Wed Aug 17 06:11:56 2011
Media Recovery Log /disk1/arch/prod/arch_4_1_759309442.log
ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
Wed Aug 17 06:11:58 2011
ALTER DATABASE RECOVER CONTINUE DEFAULT
Wed Aug 17 06:11:58 2011
Media Recovery Log /disk1/arch/prod/arch_1_1_759390119.log
ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
Wed Aug 17 06:11:58 2011
ALTER DATABASE RECOVER CONTINUE DEFAULT
Wed Aug 17 06:11:58 2011
Media Recovery Log /disk1/arch/prod/arch_2_1_759390119.log
ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
Wed Aug 17 06:11:58 2011
ALTER DATABASE RECOVER CONTINUE DEFAULT
Wed Aug 17 06:11:58 2011
Media Recovery Log /disk1/arch/prod/arch_3_1_759390119.log
ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
Wed Aug 17 06:11:58 2011
ALTER DATABASE RECOVER CONTINUE DEFAULT
Wed Aug 17 06:11:58 2011
Media Recovery Log /disk1/arch/prod/arch_1_1_759390714.log
ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
Wed Aug 17 06:11:59 2011
ALTER DATABASE RECOVER CONTINUE DEFAULT
Wed Aug 17 06:11:59 2011
Media Recovery Log /disk1/arch/prod/arch_2_1_759390714.log
ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
Wed Aug 17 06:11:59 2011
ALTER DATABASE RECOVER CONTINUE DEFAULT
Wed Aug 17 06:11:59 2011
Media Recovery Log /disk1/arch/prod/arch_3_1_759390714.log
ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
Wed Aug 17 06:11:59 2011
ALTER DATABASE RECOVER CONTINUE DEFAULT
Wed Aug 17 06:11:59 2011
Media Recovery Log /disk1/arch/prod/arch_1_1_759391164.log
ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
Wed Aug 17 06:11:59 2011
ALTER DATABASE RECOVER CONTINUE DEFAULT
Wed Aug 17 06:11:59 2011
Media Recovery Log /disk1/arch/prod/arch_2_1_759391164.log
ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
Wed Aug 17 06:11:59 2011
ALTER DATABASE RECOVER CONTINUE DEFAULT
Wed Aug 17 06:11:59 2011
Media Recovery Log /disk1/arch/prod/arch_3_1_759391164.log
ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
Wed Aug 17 06:12:00 2011
ALTER DATABASE RECOVER CONTINUE DEFAULT
Wed Aug 17 06:12:00 2011
Media Recovery Log /disk1/arch/prod/arch_4_1_759391164.log
Errors with log /disk1/arch/prod/arch_4_1_759391164.log
ORA-308 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
Wed Aug 17 06:12:00 2011
ALTER DATABASE RECOVER CANCEL
Wed Aug 17 06:12:01 2011
Media Recovery Canceled
Completed: ALTER DATABASE RECOVER CANCEL
Wed Aug 17 06:12:03 2011
ALTER DATABASE RECOVER database until cancel
Media Recovery Start
ORA-279 signalled during: ALTER DATABASE RECOVER database until cancel ...
Wed Aug 17 06:12:06 2011
ALTER DATABASE RECOVER CANCEL
Wed Aug 17 06:12:07 2011
Media Recovery Canceled
Completed: ALTER DATABASE RECOVER CANCEL
Wed Aug 17 06:12:13 2011
alter database open resetlogs
----------验证
06:12:07 SQL> alter database open resetlogs;
Database altered.
06:12:36 SQL> select * from scott.test;
ID
----------
1
2
3
9
4
5
6
7
8
9 rows selected
---------只恢复到sequence 为3的日志所记录的data block
案例4：
----------------------误删除表空间（有备份）
1）基于backup control 的不完全恢复
06:24:21 SQL> select file_id,file_name ,tablespace_name from dba_data_files;
FILE_ID FILE_NAME TABLESPACE_NAME
---------- -------------------------------------------------- ------------------------------
8 /u01/app/oracle/oradata/prod/test02.dbf TEST
3 /u01/app/oracle/oradata/prod/sysaux01.dbf SYSAUX
2 /u01/app/oracle/oradata/prod/users01.dbf USERS
1 /u01/app/oracle/oradata/prod/system01.dbf SYSTEM
5 /u01/app/oracle/oradata/prod/example01.dbf EXAMPLE
6 /u01/app/oracle/oradata/prod/test01.dbf TEST
7 /u01/app/oracle/oradata/prod/undo_tbs01.dbf UNDO_TBS
4 /u01/app/oracle/oradata/prod/index01.dbf INDEXES
9 /u01/app/oracle/oradata/prod/cuug01.dbf CUUG
9 rows selected.
06:24:38 SQL> select table_name,tablespace_name from user_tables;
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
DEPT USERS
EMP USERS
BONUS USERS
SALGRADE USERS
TEST USERS
T01 TEST
T02 CUUG
7 rows selected.
6:24:24 SQL> conn scott/tiger
Connected.
06:24:38 SQL>
06:24:38 SQL> select table_name,tablespace_name from user_tables;
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
DEPT USERS
EMP USERS
BONUS USERS
SALGRADE USERS
TEST USERS
T01 TEST
T02 CUUG
7 rows selected.
06:24:47 SQL> select * from t02;
ID
----------
1
2
3
06:25:03 SQL> insert into t02 select * from t02;
3 rows created.
06:25:13 SQL> commit;
Commit complete.
06:25:15 SQL> select * from t02;
ID
----------
1
2
3
1
2
3
6 rows selected.
06:25:17 SQL> conn /as sysdba
Connected.
06:25:22 SQL>
06:25:22 SQL> alter database backup controlfile to '/disk1/backup/prod_control.bak';
Database altered.
---------生成控制文件备份
06:25:42 SQL> insert into scott.t02 values (4);
1 row created.
06:25:56 SQL> insert into scott.t02 values (5);
1 row created.
06:25:58 SQL> commit;
Commit complete.
06:25:59 SQL>
------------误删除了cuug的表空间
06:25:59 SQL> drop tablespace cuug including contents and datafiles;
Tablespace dropped.
06:26:35 SQL>
06:26:52 SQL> select file_id,file_name ,tablespace_name from dba_data_files;
FILE_ID FILE_NAME TABLESPACE_NAME
---------- -------------------------------------------------- ------------------------------
8 /u01/app/oracle/oradata/prod/test02.dbf TEST
3 /u01/app/oracle/oradata/prod/sysaux01.dbf SYSAUX
2 /u01/app/oracle/oradata/prod/users01.dbf USERS
1 /u01/app/oracle/oradata/prod/system01.dbf SYSTEM
5 /u01/app/oracle/oradata/prod/example01.dbf EXAMPLE
6 /u01/app/oracle/oradata/prod/test01.dbf TEST
7 /u01/app/oracle/oradata/prod/undo_tbs01.dbf UNDO_TBS
4 /u01/app/oracle/oradata/prod/index01.dbf INDEXES
8 rows selected.
查看告警日志信息：
---------查看 tablespace的删除的时间点
Wed Aug 17 06:26:33 2011
drop tablespace cuug including contents and datafiles
Wed Aug 17 06:26:35 2011
Deleted file /u01/app/oracle/oradata/prod/cuug01.dbf
Completed: drop tablespace cuug including contents and datafiles
----------关闭数据库，启动到no mount状态
6:26:56 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
06:28:54 SQL> startup nomount
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 1219184 bytes
Variable Size 67110288 bytes
Database Buffers 243269632 bytes
Redo Buffers 2973696 bytes
06:29:07 SQL> show parameter control
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /u01/app/oracle/oradata/prod/c
ontrol01.ctl, /u01/app/oracle/
oradata/prod/control02.ctl, /u
01/app/oracle/oradata/prod/con
trol03.ctl
06:29:17 SQL>
-----------用备份的控制文件覆盖当前的controlfile
[oracle@work ~]$ cp /disk1/backup/prod_control.bak /u01/app/oracle/oradata/prod/control01.ctl
[oracle@work ~]$ cp /disk1/backup/prod_control.bak /u01/app/oracle/oradata/prod/control02.ctl
[oracle@work ~]$ cp /disk1/backup/prod_control.bak /u01/app/oracle/oradata/prod/control03.ctl
----------启动到mount状态，转储所有的数据文件
[oracle@work prod]$ cp /disk1/backup/prod/close_bak/*.dbf /u01/app/oracle/oradata/prod/
------------基于backup controlfile的不完全恢复
06:30:44 SQL> recover database until time '2011-08-17 06:26:33' using backup controlfile;
ORA-00279: change 1258960 generated at 08/17/2011 04:37:14 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_4_1_759309442.log
ORA-00280: change 1258960 for thread 1 is in sequence #4
06:33:21 Specify log: {
auto
ORA-00279: change 1259691 generated at 08/17/2011 05:41:59 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_1_1_759390119.log
ORA-00280: change 1259691 for thread 1 is in sequence #1
ORA-00279: change 1260023 generated at 08/17/2011 05:44:31 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_2_1_759390119.log
ORA-00280: change 1260023 for thread 1 is in sequence #2
ORA-00278: log file '/disk1/arch/prod/arch_1_1_759390119.log' no longer needed for this recovery
ORA-00279: change 1260025 generated at 08/17/2011 05:44:32 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_3_1_759390119.log
ORA-00280: change 1260025 for thread 1 is in sequence #3
ORA-00278: log file '/disk1/arch/prod/arch_2_1_759390119.log' no longer needed for this recovery
ORA-00279: change 1260060 generated at 08/17/2011 05:51:54 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_1_1_759390714.log
ORA-00280: change 1260060 for thread 1 is in sequence #1
ORA-00279: change 1260278 generated at 08/17/2011 05:52:29 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_2_1_759390714.log
ORA-00280: change 1260278 for thread 1 is in sequence #2
ORA-00278: log file '/disk1/arch/prod/arch_1_1_759390714.log' no longer needed for this recovery
ORA-00279: change 1260280 generated at 08/17/2011 05:52:30 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_3_1_759390714.log
ORA-00280: change 1260280 for thread 1 is in sequence #3
ORA-00278: log file '/disk1/arch/prod/arch_2_1_759390714.log' no longer needed for this recovery
ORA-00279: change 1260287 generated at 08/17/2011 05:59:24 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_1_1_759391164.log
ORA-00280: change 1260287 for thread 1 is in sequence #1
ORA-00279: change 1260553 generated at 08/17/2011 06:00:05 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_2_1_759391164.log
ORA-00280: change 1260553 for thread 1 is in sequence #2
ORA-00278: log file '/disk1/arch/prod/arch_1_1_759391164.log' no longer needed for this recovery
ORA-00279: change 1260555 generated at 08/17/2011 06:00:06 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_3_1_759391164.log
ORA-00280: change 1260555 for thread 1 is in sequence #3
ORA-00278: log file '/disk1/arch/prod/arch_2_1_759391164.log' no longer needed for this recovery
ORA-00279: change 1260698 generated at 08/17/2011 06:12:13 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_1_1_759391933.log
ORA-00280: change 1260698 for thread 1 is in sequence #1
ORA-00279: change 1261012 generated at 08/17/2011 06:15:08 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_2_1_759391933.log
ORA-00280: change 1261012 for thread 1 is in sequence #2
ORA-00278: log file '/disk1/arch/prod/arch_1_1_759391933.log' no longer needed for this recovery
ORA-00279: change 1261014 generated at 08/17/2011 06:15:09 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_3_1_759391933.log
ORA-00280: change 1261014 for thread 1 is in sequence #3
ORA-00278: log file '/disk1/arch/prod/arch_2_1_759391933.log' no longer needed for this recovery
ORA-00308: cannot open archived log '/disk1/arch/prod/arch_3_1_759391933.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
06:33:28 SQL> recover database until time '2011-08-17 06:26:33' using backup controlfile;
ORA-00279: change 1261014 generated at 08/17/2011 06:15:09 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_3_1_759391933.log
ORA-00280: change 1261014 for thread 1 is in sequence #3
06:33:34 Specify log: {
cancel
Media recovery cancelled.
查看告警日志：
ALTER DATABASE RECOVER database until time '2011-08-17 06:26:33' using backup controlfile ；
Wed Aug 17 06:33:20 2011
Media Recovery Start
Media Recovery start incarnation depth : 4, target inc# : 7, irscn : 1259690
ORA-279 signalled during: ALTER DATABASE RECOVER database until time '2011-08-17 06:26:33' using backup controlfile ...
Wed Aug 17 06:33:23 2011
ALTER DATABASE RECOVER CONTINUE DEFAULT
Wed Aug 17 06:33:23 2011
Media Recovery Log /disk1/arch/prod/arch_4_1_759309442.log
ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
Wed Aug 17 06:33:25 2011
ALTER DATABASE RECOVER CONTINUE DEFAULT
Wed Aug 17 06:33:25 2011
Media Recovery Log /disk1/arch/prod/arch_1_1_759390119.log
ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
Wed Aug 17 06:33:25 2011
ALTER DATABASE RECOVER CONTINUE DEFAULT
Wed Aug 17 06:33:25 2011
Media Recovery Log /disk1/arch/prod/arch_2_1_759390119.log
ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
Wed Aug 17 06:33:25 2011
ALTER DATABASE RECOVER CONTINUE DEFAULT
Wed Aug 17 06:33:25 2011
Media Recovery Log /disk1/arch/prod/arch_3_1_759390119.log
ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
Wed Aug 17 06:33:26 2011
ALTER DATABASE RECOVER CONTINUE DEFAULT
Wed Aug 17 06:33:26 2011
Media Recovery Log /disk1/arch/prod/arch_1_1_759390714.log
ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
Wed Aug 17 06:33:26 2011
ALTER DATABASE RECOVER CONTINUE DEFAULT
Wed Aug 17 06:33:26 2011
Media Recovery Log /disk1/arch/prod/arch_2_1_759390714.log
ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
Wed Aug 17 06:33:26 2011
ALTER DATABASE RECOVER CONTINUE DEFAULT
Wed Aug 17 06:33:26 2011
Media Recovery Log /disk1/arch/prod/arch_3_1_759390714.log
ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
Wed Aug 17 06:33:26 2011
ALTER DATABASE RECOVER CONTINUE DEFAULT
Wed Aug 17 06:33:26 2011
Media Recovery Log /disk1/arch/prod/arch_1_1_759391164.log
ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
Wed Aug 17 06:33:26 2011
ALTER DATABASE RECOVER CONTINUE DEFAULT
Wed Aug 17 06:33:26 2011
Media Recovery Log /disk1/arch/prod/arch_2_1_759391164.log
ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
Wed Aug 17 06:33:26 2011
ALTER DATABASE RECOVER CONTINUE DEFAULT
Wed Aug 17 06:33:26 2011
Media Recovery Log /disk1/arch/prod/arch_3_1_759391164.log
ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
Wed Aug 17 06:33:27 2011
ALTER DATABASE RECOVER CONTINUE DEFAULT
Wed Aug 17 06:33:27 2011
Media Recovery Log /disk1/arch/prod/arch_1_1_759391933.log
ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
Wed Aug 17 06:33:27 2011
ALTER DATABASE RECOVER CONTINUE DEFAULT
Wed Aug 17 06:33:27 2011
Media Recovery Log /disk1/arch/prod/arch_2_1_759391933.log
ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
Wed Aug 17 06:33:27 2011
ALTER DATABASE RECOVER CONTINUE DEFAULT
Wed Aug 17 06:33:27 2011
Media Recovery Log /disk1/arch/prod/arch_3_1_759391933.log
Errors with log /disk1/arch/prod/arch_3_1_759391933.log
ORA-308 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
Wed Aug 17 06:33:27 2011
ALTER DATABASE RECOVER CANCEL
Wed Aug 17 06:33:28 2011
Media Recovery Canceled
Completed: ALTER DATABASE RECOVER CANCEL
Wed Aug 17 06:33:34 2011
ALTER DATABASE RECOVER database until time '2011-08-17 06:26:33' using backup controlfile
Media Recovery Start
ORA-279 signalled during: ALTER DATABASE RECOVER database until time '2011-08-17 06:26:33' using backup controlfile ...
Wed Aug 17 06:33:38 2011
ALTER DATABASE RECOVER CANCEL
Wed Aug 17 06:33:40 2011
Media Recovery Canceled
Completed: ALTER DATABASE RECOVER CANCEL
Wed Aug 17 06:33:46 2011
alter database open resetlogs
RESETLOGS after incomplete recovery UNTIL CHANGE 1261014
Resetting resetlogs activation ID 171222051 (0xa34a423)
-----------验证：
6:33:40 SQL> alter database open resetlogs;
Database altered.
06:34:06 SQL> col file_name for a50
06:35:02 SQL> select file_id,file_name,tablespace_name from dba_data_files;
FILE_ID FILE_NAME TABLESPACE_NAME
---------- -------------------------------------------------- ------------------------------
8 /u01/app/oracle/oradata/prod/test02.dbf TEST
3 /u01/app/oracle/oradata/prod/sysaux01.dbf SYSAUX
2 /u01/app/oracle/oradata/prod/users01.dbf USERS
1 /u01/app/oracle/oradata/prod/system01.dbf SYSTEM
5 /u01/app/oracle/oradata/prod/example01.dbf EXAMPLE
6 /u01/app/oracle/oradata/prod/test01.dbf TEST
7 /u01/app/oracle/oradata/prod/undo_tbs01.dbf UNDO_TBS
4 /u01/app/oracle/oradata/prod/index01.dbf INDEXES
9 /u01/app/oracle/oradata/prod/cuug01.dbf CUUG
9 rows selected.
06:35:13 SQL> select * from scott.t02;
ID
----------
1
2
3
06:35:20 SQL>
误删除表空间（有备份），利用备份的控制文件恢复
一、模拟环境
07:59:14 SQL> select count(*) from scott.dept2;
COUNT(*)
----------
12
07:59:50 SQL> drop tablespace lxtbs1 including contents and datafiles;
Tablespace dropped.
07:59:56 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
08:00:58 SQL> !
二、转储所有数据文件
[oracle@cuug14 ~]$ cp /orabak/orcl/cold_bak/*.dbf /disk1/oradata/orcl
08:03:26 SQL> recover database until time '2012-02-12 07:59:53' using backup controlfile;
ORA-01034: ORACLE not available
08:04:12 SQL> startup mount
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 75499764 bytes
Database Buffers 88080384 bytes
Redo Buffers 2973696 bytes
Database mounted.
三、recover database
08:04:36 SQL> recover database until time '2012-02-12 07:59:53' using backup controlfile;
ORA-00279: change 831098 generated at 02/12/2012 06:32:28 needed for thread 1
ORA-00289: suggestion : /arch/orcl/arch_1_6_775023202.log
ORA-00280: change 831098 for thread 1 is in sequence #6
08:04:45 Specify log: {
auto
ORA-00279: change 832010 generated at 02/12/2012 07:55:37 needed for thread 1
ORA-00289: suggestion : /arch/orcl/arch_1_1_775036537.log
ORA-00280: change 832010 for thread 1 is in sequence #1
ORA-00279: change 832995 generated at 02/12/2012 07:58:39 needed for thread 1
ORA-00289: suggestion : /arch/orcl/arch_1_2_775036537.log
ORA-00280: change 832995 for thread 1 is in sequence #2
ORA-00278: log file '/arch/orcl/arch_1_1_775036537.log' no longer needed for this recovery
ORA-00279: change 832997 generated at 02/12/2012 07:58:40 needed for thread 1
ORA-00289: suggestion : /arch/orcl/arch_1_3_775036537.log
ORA-00280: change 832997 for thread 1 is in sequence #3
ORA-00278: log file '/arch/orcl/arch_1_2_775036537.log' no longer needed for this recovery
ORA-00279: change 833000 generated at 02/12/2012 07:58:43 needed for thread 1
ORA-00289: suggestion : /arch/orcl/arch_1_4_775036537.log
ORA-00280: change 833000 for thread 1 is in sequence #4
ORA-00278: log file '/arch/orcl/arch_1_3_775036537.log' no longer needed for this recovery
ORA-00279: change 833017 generated at 02/12/2012 07:59:13 needed for thread 1
ORA-00289: suggestion : /arch/orcl/arch_1_5_775036537.log
ORA-00280: change 833017 for thread 1 is in sequence #5
ORA-00278: log file '/arch/orcl/arch_1_4_775036537.log' no longer needed for this recovery
ORA-00279: change 833019 generated at 02/12/2012 07:59:14 needed for thread 1
ORA-00289: suggestion : /arch/orcl/arch_1_6_775036537.log
ORA-00280: change 833019 for thread 1 is in sequence #6
ORA-00278: log file '/arch/orcl/arch_1_5_775036537.log' no longer needed for this recovery
ORA-00308: cannot open archived log '/arch/orcl/arch_1_6_775036537.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
四、利用当前日志组恢复
08:04:52 SQL> select name from v$archived_log;
NAME
------------------------------------------------------------------------------------------------------------------------
/arch/orcl/arch_1_9_771838300.log
/arch/orcl/arch_1_10_771838300.log
/arch/orcl/arch_1_11_771838300.log
/arch/orcl/arch_1_12_771838300.log
/arch/orcl/arch_1_13_771838300.log
/arch/orcl/arch_1_14_771838300.log
/arch/orcl/arch_1_15_771838300.log
/arch/orcl/arch_1_16_771838300.log
/arch/orcl/arch_1_17_771838300.log
/arch/orcl/arch_1_18_771838300.log
/arch/orcl/arch_1_19_771838300.log
/arch/orcl/arch_1_20_771838300.log
/arch/orcl/arch_1_21_771838300.log
/arch/orcl/arch_1_4_775023202.log
/arch/orcl/arch_1_5_775023202.log
/arch/orcl/arch_1_1_775036537.log
/arch/orcl/arch_1_2_775036537.log
NAME
------------------------------------------------------------------------------------------------------------------------
/arch/orcl/arch_1_3_775036537.log
/arch/orcl/arch_1_4_775036537.log
/arch/orcl/arch_1_5_775036537.log
20 rows selected.
08:05:06 SQL> select member from v$logfile;
MEMBER
------------------------------------------------------------------------------------------------------------------------
/disk2/oradata/orcl/redo03.log
/disk2/oradata/orcl/redo02.log
/disk2/oradata/orcl/redo01.log
08:05:25 SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
1 1 5 52428800 1 YES INACTIVE 833017 2012-02-12 07:59:13
3 1 6 52428800 1 NO CURRENT 833019 2012-02-12 07:59:14
2 1 4 52428800 1 YES INACTIVE 833000 2012-02-12 07:58:43
08:05:59 SQL> recover database until time '2012-02-12 07:59:53' using backup controlfile;
ORA-00279: change 833019 generated at 02/12/2012 07:59:14 needed for thread 1
ORA-00289: suggestion : /arch/orcl/arch_1_6_775036537.log
ORA-00280: change 833019 for thread 1 is in sequence #6
08:06:07 Specify log: {
/disk2/oradata/orcl/redo03.log
Log applied.
Media recovery complete.
08:06:13 SQL> alter database open resetlogs;
Database altered.
08:06:40 SQL> select name from v$datafile;
NAME
------------------------------------------------------------------------------------------------------------------------
/disk1/oradata/orcl/system01.dbf
/disk1/oradata/orcl/undotbs01.dbf
/disk1/oradata/orcl/sysaux01.dbf
/disk1/oradata/orcl/users01.dbf
/disk1/oradata/orcl/example01.dbf
/u01/app/oracle/product/10.2.0/db_1/dbs/MISSING00006
6 rows selected.
08:06:48 SQL> !
[oracle@cuug14 ~]$ ls /u01/app/oracle/product/10.2.0/db_1/dbs/
hc_orcl.dat initdw.ora init.ora initorcl.ora lkORCL orapworcl spfileorcl.ora
[oracle@cuug14 ~]$ ls -a /u01/app/oracle/product/10.2.0/db_1/dbs/
. .. hc_orcl.dat initdw.ora init.ora initorcl.ora lkORCL orapworcl spfileorcl.ora
08:08:29 SQL> col file_name for a50
08:08:35 SQL> select file_id,file_name,tablespace_name from dba_data_files;
FILE_ID FILE_NAME TABLESPACE_NAME
---------- -------------------------------------------------- ------------------------------
4 /disk1/oradata/orcl/users01.dbf USERS
3 /disk1/oradata/orcl/sysaux01.dbf SYSAUX
2 /disk1/oradata/orcl/undotbs01.dbf UNDOTBS1
1 /disk1/oradata/orcl/system01.dbf SYSTEM
6 /u01/app/oracle/product/10.2.0/db_1/dbs/MISSING000 LXTBS1
06
5 /disk1/oradata/orcl/example01.dbf EXAMPLE
6 rows selected.
五、利用备份的datafile 再做完全恢复
08:08:59 SQL> alter tablespace lxtbs1 offline;
alter tablespace lxtbs1 offline
*
ERROR at line 1:
ORA-01191: file 6 is already offline - cannot do a normal offline
ORA-01111: name for data file 6 is unknown - rename to correct file
ORA-01110: data file 6: '/u01/app/oracle/product/10.2.0/db_1/dbs/MISSING00006'
08:09:58 SQL> alter database datafile 6 offline;
Database altered.
08:10:05 SQL> !
[oracle@cuug14 ~]$ cp /orabak/orcl/cold_bak/lxtbs01.dbf /disk1/oradata/orcl/
08:11:32 SQL> alter tablespace lxtbs1 rename datafile '/u01/app/oracle/product/10.2.0/db_1/dbs/MISSING00006' to '/disk1/oradata/orcl/lxtbs01.dbf' ;
Tablespace altered.
08:11:44 SQL> alter tablespace lxtbs1 online;
alter tablespace lxtbs1 online
*
ERROR at line 1:
ORA-01190: control file or data file 6 is from before the last RESETLOGS
ORA-01110: data file 6: '/disk1/oradata/orcl/lxtbs01.dbf'
08:11:58 SQL> recover datafile 6;
ORA-00279: change 831098 generated at 02/12/2012 06:32:28 needed for thread 1
ORA-00289: suggestion : /arch/orcl/arch_1_6_775023202.log
ORA-00280: change 831098 for thread 1 is in sequence #6
08:12:19 Specify log: {
auto
ORA-00279: change 832010 generated at 02/12/2012 07:55:37 needed for thread 1
ORA-00289: suggestion : /arch/orcl/arch_1_1_775036537.log
ORA-00280: change 832010 for thread 1 is in sequence #1
ORA-00279: change 832995 generated at 02/12/2012 07:58:39 needed for thread 1
ORA-00289: suggestion : /arch/orcl/arch_1_2_775036537.log
ORA-00280: change 832995 for thread 1 is in sequence #2
ORA-00278: log file '/arch/orcl/arch_1_1_775036537.log' no longer needed for this recovery
ORA-00279: change 832997 generated at 02/12/2012 07:58:40 needed for thread 1
ORA-00289: suggestion : /arch/orcl/arch_1_3_775036537.log
ORA-00280: change 832997 for thread 1 is in sequence #3
ORA-00278: log file '/arch/orcl/arch_1_2_775036537.log' no longer needed for this recovery
ORA-00279: change 833000 generated at 02/12/2012 07:58:43 needed for thread 1
ORA-00289: suggestion : /arch/orcl/arch_1_4_775036537.log
ORA-00280: change 833000 for thread 1 is in sequence #4
ORA-00278: log file '/arch/orcl/arch_1_3_775036537.log' no longer needed for this recovery
ORA-00279: change 833017 generated at 02/12/2012 07:59:13 needed for thread 1
ORA-00289: suggestion : /arch/orcl/arch_1_5_775036537.log
ORA-00280: change 833017 for thread 1 is in sequence #5
ORA-00278: log file '/arch/orcl/arch_1_4_775036537.log' no longer needed for this recovery
ORA-00279: change 833019 generated at 02/12/2012 07:59:14 needed for thread 1
ORA-00289: suggestion : /arch/orcl/arch_1_6_775036537.log
ORA-00280: change 833019 for thread 1 is in sequence #6
ORA-00278: log file '/arch/orcl/arch_1_5_775036537.log' no longer needed for this recovery
Log applied.
Media recovery complete.
08:12:35 SQL> alter database datafile 6 online;
Database altered.
08:12:42 SQL> select * from scott.dept2;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
12 rows selected.
6、flashback 的功能：利用flashback log 或 undo data 对database 可以恢复到过去某个点，可以作为不完恢复的补充
7、flashback分类：
1）flashback database
2）flashback table
3)flashback drop
4）flashback query flashback version
5) flashback archive ( 11g )
8、flashback 的应用
1） flashback drop :除了system表空间，可以恢复被drop 的table
06:52:29 SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
TEST TABLE
T01 TABLE
T02 TABLE
7 rows selected.
06:52:31 SQL> drop table t01;
Table dropped.
06:52:38 SQL> show recycle;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
T01 BIN$qrJLbL74ZgvgQKjA8Agb/A==$0 TABLE 2011-08-17:06:52:38
06:52:44 SQL>
--------除了system 表空间，其余表空间都有一个类似windows 回收站，在drop table，实际上把table 改名后放入recyclebin。
06:52:44 SQL> flashback table t01 to before drop;
Flashback complete.
06:54:05 SQL> show recycle;
06:54:07 SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
TEST TABLE
T01 TABLE
T02 TABLE
7 rows selected.
06:54:11 SQL> drop table t02 purge; //purge 会彻底的删除table
Table dropped.
06:54:40 SQL> show recycle;
-----------清空recyclebin
06:54:43 SQL> drop table t01;
Table dropped.
06:55:49 SQL> show recycle;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
T01 BIN$qrJLbL75ZgvgQKjA8Agb/A==$0 TABLE 2011-08-17:06:55:49
06:55:51 SQL> purge recyclebin;
Recyclebin purged.
06:55:57 SQL> show recycle;
06:55:59 SQL>
--------------如何恢复同一个schema 下同名的table
06:56:32 SQL> drop table test;
Table dropped.
06:56:42 SQL> create table test as select * from emp;
Table created.
06:56:46 SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
BIN$qrJLbL76ZgvgQKjA8Agb/A==$0 TABLE
TEST TABLE
6 rows selected.
06:56:50 SQL> show recycle;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST BIN$qrJLbL76ZgvgQKjA8Agb/A==$0 TABLE 2011-08-17:06:56:36
06:56:58 SQL> flashback table test to before drop;
flashback table test to before drop
*
ERROR at line 1:
ORA-38312: original name is used by an existing object
06:57:09 SQL> flashback table test to before drop rename to test_old;
Flashback complete.
06:57:32 SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
TEST_OLD TABLE
TEST TABLE
6 rows selected.
06:57:36 SQL>
------------system 表空间不存在recyclebin ，表直接被删除
06:57:36 SQL> conn /as sysdba
Connected.
06:58:33 SQL>
06:58:33 SQL> create table test as select * from user_tables;
Table created.
06:58:42 SQL> drop table test;
Table dropped.
06:58:46 SQL> show recycle;
06:58:48 SQL>
2）flashback query：（用于DML 误操作）
利用在undo tablespace 里已经被提交的undo block（未被覆盖），可以通过查询的方式将表里面的记录回到过去某个时间点。
-------模拟环境
07:01:37 SQL> conn scott/tiger
Connected.
07:01:41 SQL>
07:01:41 SQL> select * from 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.
07:01:45 SQL> delete from test ;
14 rows deleted.
07:01:59 SQL> commit;
Commit complete.
07:02:00 SQL> rollback;
Rollback complete.
07:02:03 SQL> select * from test;
no rows selected
07:02:05 SQL> insert into test select * from emp where rownum <3;
2 rows created.
07:02:35 SQL> select * from 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
07:02:37 SQL> commit;
Commit complete.
07:02:38 SQL>
-----------利用logmnr 找到DML操作的时间点
07:03:03 SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 0 52428800 1 YES UNUSED 0
2 1 1 52428800 1 NO CURRENT 1261015 17-AUG-11
3 1 0 52428800 1 YES UNUSED 0
07:03:20 SQL> col member for a50
07:03:23 SQL> r
1* select group#,member from v$logfile
GROUP# MEMBER
---------- --------------------------------------------------
3 /u01/app/oracle/oradata/prod/redo03.log
2 /u01/app/oracle/oradata/prod/redo02.log
1 /u01/app/oracle/oradata/prod/redo01.log
07:03:24 SQL>
11:19:31 SQL> conn /as sysdba
Connected.
11:19:35 SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 12 52428800 2 YES INACTIVE 823116 29-SEP-11
2 1 14 52428800 2 NO CURRENT 828692 29-SEP-11
3 2 9 52428800 2 YES INACTIVE 824371 29-SEP-11
4 2 11 52428800 2 NO CURRENT 828868 29-SEP-11
5 1 13 52428800 2 YES INACTIVE 828670 29-SEP-11
6 2 10 52428800 2 YES INACTIVE 828817 29-SEP-11
6 rows selected.
11:19:41 SQL> col member for a50
11:19:57 SQL> select group# ,member from v$logfile;
GROUP# MEMBER
---------- --------------------------------------------------
2 +DG1/prod/onlinelog/group_2.262.762877491
2 +RECOVERY/prod/onlinelog/group_2.258.762877501
1 +DG1/prod/onlinelog/group_1.261.762877473
1 +RECOVERY/prod/onlinelog/group_1.257.762877479
3 +DG1/prod/onlinelog/group_3.266.762877849
3 +RECOVERY/prod/onlinelog/group_3.259.762877855
4 +DG1/prod/onlinelog/group_4.267.762877859
4 +RECOVERY/prod/onlinelog/group_4.260.762877867
6 +DG1/prod/onlinelog/group_6.272.763037401
6 +RECOVERY/prod/onlinelog/group_6.262.763037407
5 +DG1/prod/onlinelog/group_5.271.763037441
GROUP# MEMBER
---------- --------------------------------------------------
5 +RECOVERY/prod/onlinelog/group_5.261.763037613
12 rows selected.
11:20:07 SQL> execute dbms_logmnr.add_logfile(logfilename=>'+DG1/prod/onlinelog/group_2.262.762877491',options=>dbms_logmnr.new);
PL/SQL procedure successfully completed.
11:20:57 SQL> alter session set nls_date_format='yyyy-mm-dd';
Session altered.
11:21:32 SQL> execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
PL/SQL procedure successfully completed.
11:23:11 SQL> select username,scn,timestamp,sql_redo from v$logmnr_contents where seg_name='EMP1';
USERNAME SCN TIMESTAMP SQL_REDO
------------------------------ ---------- ---------- --------------------------------------------------
830293 2011-09-29 delete from "SCOTT"."EMP1" where "EMPNO" = '7369'
and "ENAME" = 'SMITH' and "JOB" = 'CLERK' and "MGR
" = '7902' and "HIREDATE" = TO_DATE('1980-12-17',
'yyyy-mm-dd') and "SAL" = '800' and "COMM" IS NULL
and "DEPTNO" = '20' and ROWID = 'AAAM01AAEAAAAGEA
AA';
07:03:24 SQL> execute dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/oradata/prod/redo02.log',options=>dbms_logmnr.new);
PL/SQL procedure successfully completed.
07:04:34 SQL> execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
PL/SQL procedure successfully completed.
07:04:41 SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
07:04:56 SQL> select username,scn,timestamp,sql_redo from v$logmnr_contents where seg_name='TEST';
SCOTT 1263006 2011-08-17 07:01:59 delete from "SCOTT"."TEST" where "EMPNO" = '7369'
and "ENAME" = 'SMITH' and "JOB" = 'CLERK' and "MGR
" = '7902' and "HIREDATE" = TO_DATE('1980-12-17 00
:00:00', 'yyyy-mm-dd hh24:mi:ss') and "SAL" = '800
' and "COMM" IS NULL and "DEPTNO" = '20' and ROWID
= 'AAAM39AACAAAABEAAA';
07:05:21 SQL> execute dbms_logmnr.end_logmnr;
PL/SQL procedure successfully completed.
---利用flashback query 查询
07:08:42 SQL> conn scott/tiger
Connected.
07:08:48 SQL>
07:08:48 SQL> select * from test as of timestamp to_timestamp('2011-08-17 07:01:59','yyyy-mm-dd hh24:mi:ss');
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.
07:08:50 SQL> insert into test (select * from test as of timestamp to_timestamp('2011-08-17 07:01:59','yyyy-mm-dd hh24:mi:ss'));
14 rows created.
07:09:10 SQL> select * from 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
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
16 rows selected.
07:09:13 SQL>
----------基于scn
07:09:13 SQL> conn /as sysdba
Connected.
07:10:28 SQL>
07:10:28 SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1263945
07:10:39 SQL> conn scott/tiger
Connected.
07:13:44 SQL> delete from test;
16 rows deleted.
07:13:51 SQL> commit;
Commit complete.
07:13:56 SQL> select * from test as of scn 1263945 ;
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
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
16 rows selected.
flashback table :对表进行闪回（类似flashback query）（同样的是对DML误操作）
Flashback?Table也是使用UNDO tablespace的内容来实现对数据的回退。
07:16:18 SQL> select * from 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
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
16 rows selected.
07:16:23 SQL> delete from test;
16 rows deleted.
07:16:50 SQL> commit;
Commit complete.
07:16:52 SQL> select * from test;
no rows selected
07:16:57 SQL> insert into test select * from emp where rownum=1;
1 row created.
07:17:17 SQL> commit;
Commit complete.
07:17:19 SQL> select * from test;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
07:17:21 SQL> flashback table test to scn 1264179;
flashback table test to scn 1264179
*
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled
07:17:41 SQL> alter table test enable row movement;
Table altered.
07:18:01 SQL> flashback table test to scn 1264179;
Flashback complete.
07:18:05 SQL> select * from 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
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
16 rows selected.
07:18:09 SQL>
-------------基于时间点（通过logmnr 找出误操作的时间点）
05:43:31 SQL> delete from scott.emp1;
14 rows deleted.
05:44:25 SQL> flashback table scott.emp1 to timestamp to_timestamp('2011-03-18 04:50:00','yyyy-mm-dd hh24:mi:ss');
Flashback complete.
05:44:32 SQL> select * from scott.emp1;
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 database：利用flashback log 对整个database 做回退到过去的某个时间点（用于DDL 的误操作如drop 和 truncate）
1）查看flashback database
07:21:27 SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
07:21:33 SQL> show parameter recover
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/flash_recovery
_area
db_recovery_file_dest_size big integer 2G
recovery_parallelism integer 2
07:22:08 SQL> !
[oracle@work ~]$ mkdir -p /disk1/recovery/prod
[oracle@work ~]$ !sql
sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Aug 17 07:22:49 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
07:22:49 SQL>
07:22:49 SQL> alter system set db_recovery_file_dest='/disk1/recovery/prod' scope=spfile;
System altered.
-------存放flashback log（闪回日志）
--------启用flashback database 功能（database 必须是归档模式）
07:24:22 SQL> startup mount
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 1219184 bytes
Variable Size 71304592 bytes
Database Buffers 239075328 bytes
Redo Buffers 2973696 bytes
Database mounted.
07:24:35 SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /disk1/arch/prod
Oldest online log sequence 0
Next log sequence to archive 1
Current log sequence 1
07:25:06 SQL> alter database flashback on;
Database altered.
07:25:29 SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
07:25:38 SQL> alter database open;
Database altered.
07:25:49 SQL>
------------flashback database 恢复DDL 误操作
1)模拟环境
07:26:30 SQL> select * from 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
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
16 rows selected.
07:26:36 SQL> drop table test purge;
Table dropped.
07:27:20 SQL> create table test as select * from emp where rownum=1;
Table created.
07:27:25 SQL> select * from test;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
07:27:29 SQL>
--------flashback 日志
[oracle@work ~]$ ls /disk1/recovery/prod/PROD/flashback/
o1_mf_74q999lb_.flb
[oracle@work ~]$
--------在mount 下闪回
07:29:22 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
07:29:53 SQL> startup mount
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 1219184 bytes
Variable Size 71304592 bytes
Database Buffers 239075328 bytes
Redo Buffers 2973696 bytes
Database mounted.
07:30:02 SQL> flashback database to scn 1264788;
Flashback complete.
---------把database 以read only 方式打开，先验证下恢复是否成功，如果不成功，再从新进入mount ，恢复
07:31:29 SQL> alter database open read only;
Database altered.
07:31:34 SQL> select * from scott.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
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
16 rows selected.
---------恢复成功，重新以resetlogs的方式open database
07:31:40 SQL> shutdown immedaite
SP2-0717: illegal SHUTDOWN option
07:31:45 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
07:32:02 SQL> startup mount
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 1219184 bytes
Variable Size 71304592 bytes
Database Buffers 239075328 bytes
Redo Buffers 2973696 bytes
Database mounted.
07:32:10 SQL> alter database open resetlogs;
Database altered.
--------验证：
07:32:25 SQL> select * from scott.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
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
16 rows selected.
--------------基于timestamp 的flashback database
1)查看flashback 参数
03:43:58 SQL> show parameter db_recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string
db_recovery_file_dest_size big integer 0
2）设置flashback 参数
03:44:09 SQL> alter system set db_recovery_file_dest='/disk1/flash_area' scope=spfile;
System altered.
03:45:00 SQL> alter system set db_recovery_file_dest_size=1G scope=spfile;
System altered.
3）激活fashback （必须要database 干净的关闭才行）
03:45:20 SQL> startup force
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 1219184 bytes
Variable Size 180356496 bytes
Database Buffers 130023424 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
03:45:36 SQL> show parameter db_recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /disk1/flash_area
db_recovery_file_dest_size big integer 1G
03:46:06 SQL> startup force mount
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 1219184 bytes
Variable Size 180356496 bytes
Database Buffers 130023424 bytes
Redo Buffers 2973696 bytes
Database mounted.
03:46:21 SQL> alter database flashback on; ----非正常关库，无法打开flashback
alter database flashback on
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38714: Instance recovery required.
03:47:03 SQL> show parameter db_recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /disk1/flash_area
db_recovery_file_dest_size big integer 1G
03:47:39 SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
03:49:33 SQL> startup
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 1219184 bytes
Variable Size 180356496 bytes
Database Buffers 130023424 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
03:49:43 SQL> show parameter db_recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /disk1/flash_area
db_recovery_file_dest_size big integer 1G
03:49:52 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
03:50:37 SQL> startup mount
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 1219184 bytes
Variable Size 180356496 bytes
Database Buffers 130023424 bytes
Redo Buffers 2973696 bytes
Database mounted.
03:51:20 SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /disk1/arch
Oldest online log sequence 46
Next log sequence to archive 51
Current log sequence 51
03:51:25 SQL> alter database flashback on; ----干净关闭下，开启flashback。
Database altered.
03:51:43 SQL> select name,current_scn ,flashback_on from v$database;
NAME CURRENT_SCN FLASHBACK_ON
--------- ----------- ------------------
ORCL 0 YES
03:52:46 SQL> show parameter flashback
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 1440
03:53:47 SQL> alter database open;
Database altered.
4） flashback 验证
04:04:28 SQL> show user;
USER is "SYS"
04:08:04 SQL> conn scott/tiger
Connected.
04:08:13 SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2011-03-18 04:08:42
04:09:02 SQL> conn /as sysdba
Connected.
04:09:07 SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1437597
04:09:10 SQL> conn scott/tiger
Connected.
04:09:29 SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
EMP TABLE
DEPT TABLE
BONUS TABLE
SALGRADE TABLE
QUEST_SL_TEMP_EXPLAIN1 TABLE
EMP1 TABLE
ERRLOG TABLE
PART_SALES TABLE
T01 TABLE
DEPT1 TABLE
10 rows selected.
select * from t01;
ID NA
---------- --
1 TM
04:10:07 SQL> insert into t01 values(2,'aa');
1 row created.
04:10:17 SQL> insert into t01 values(3,'bb');
1 row created.
04:10:26 SQL> commit;
Commit complete.
04:10:30 SQL> drop table t01;
Table dropped.
04:10:41 SQL> shutdown immediate
ORA-01031: insufficient privileges
04:10:59 SQL> conn /as sysdba
Connected.
04:11:03 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
04:11:28 SQL> startup mount
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 1219184 bytes
Variable Size 184550800 bytes
Database Buffers 125829120 bytes
Redo Buffers 2973696 bytes
Database mounted.
04:12:07 SQL> flashback database to timestamp to_timestamp('2011-03-18 04:10:26','yyyy-mm-dd hh24:mi:ss');
Flashback complete.
04:13:34 SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
04:14:10 SQL> alter database open noresetlogs;
alter database open noresetlogs
*
ERROR at line 1:
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
04:14:18 SQL> alter database open resetlogs;
Database altered.
04:15:35 SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
1 1 0 52428800 2 YES UNUSED 0
2 1 0 52428800 2 YES UNUSED 0
3 1 0 52428800 2 YES UNUSED 0
4 1 0 52428800 2 YES UNUSED 0
5 1 0 52428800 2 YES UNUSED 0
6 1 1 52428800 2 NO CURRENT 1437637 2011-03-18 04:14:27
6 rows selected.
04:15:59 SQL> conn scott/tiger
Connected.
04:16:18 SQL> select * from t01;
ID NA
---------- --
1 TM
04:34:35 SQL> desc v$flashback_database_log;
Name Null? Type
----------------------------------------------------------------------------------- -------- - OLDEST_FLASHBACK_SCN NUMBER
OLDEST_FLASHBACK_TIME DATE
RETENTION_TARGET NUMBER
FLASHBACK_SIZE NUMBER
ESTIMATED_FLASHBACK_SIZE NUMBER
04:34:37 SQL> select * from v$flashback_database_log;
OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TI RETENTION_TARGET FLASHBACK_SIZE TIMATED_FLASHBACK_SIZE
-------------------- ------------------- ---------------- -------------- ----------------------
1436931 2011-03-18 03:51:43 1440 8192000 115703808
5) flashback archive (11g)
创建flashback archive
SQL> create flashback archive flash1
2 tablespace users
3 quota 200m
4 retention 2 year;
tablespace users
*
ERROR at line 2:
ORA-55627: Flashback Archive tablespace must be ASSM tablespace
SQL> create tablespace flash_ts datafile '/oradata/testdb/flash_ts01.dbf' size 300m segment space management auto;
Tablespace created.
SQL> create flashback archive flash1
2 tablespace flash_ts
3 quota 200m
4 retention 2 year;
Flashback archive created.
SQL> grant flashback archive on flash1 to scott;
Grant succeeded.
对表启用flashback archive
SQL> create table test (id number, name varchar2(20)) flashback archive flash1;
Table created. -- 新建表 启用 flashback archive
SQL> alter table emp flashback archive flash1;
Table altered. -- 把已有的表加到 flashback archive
检查flashback archive
SQL> col table_name for a10
SQL> col owner_name for a10
SQL> col FLASHBACK_ARCHIVE_NAME for a10
SQL> col ARCHIVE_TABLE_NAME for a20
SQL> col status for a10
SQL> select * from dba_flashback_archive_tables;
TABLE_NAME OWNER_NAME FLASHBACK_ ARCHIVE_TABLE_NAME STATUS
---------- ---------- ---------- -------------------- ----------
TEST SCOTT FLASH1 SYS_FBA_HIST_13186 ENABLED
EMP SCOTT FLASH1 SYS_FBA_HIST_13109 ENABLED
关闭 flashback archive
需要 FLASHBACK ARCHIVE ADMINISTER 权限
SQL> alter table test no flashback archive;
Table altered.