rman不完全恢复(时间点、SCN、CANCEL)、logmnr日志挖掘
目录
1.不完全恢复(Point-In-Time Recovery)
不完全恢复仅仅是将数据恢复到某一个特定的时间点或特定的SCN，而不是当前时间点。不完全恢复会整个CDB和非CDB会影响整个数据库，需要在MOUNT状态下进行。在不完全恢复成功之后，通常需要使用 resetlogs 选项来打开数据库。当使用resetlogs后，SCN 计数器不会被重置，原来的日志序号 log sequence 会结束，从新开始新的日志序列号。在Oracle里称之为产生一个新的incarnation。同时Oracle还会重置联机重做日志内容，因此resetlogs之后建议重新全备数据库。
1.1不完全恢复的情形:
-
介质故障(media failure)导致部分或全部联机重做日志(online redo log)损坏。
-
用户操作失误(user error)导致数据丢失,例如,用户由于疏忽而移除了表,提交了无效的数据到表。
-
由于归档重做日志(archived redo log)丢失而无法进行完全恢复(complete recovery) 。
-
当前控制文件(control file)丢失，必须使用备份的控制文件打开(open)数据库。
1.2不完全恢复的一些类型：
-
基于时间点恢复，recover database until time "to_date('2020/04/10 13:00:00','YYYY/MM/DD HH24:MI:SS')";
-
基于SCN恢复，recover database until scn 10000;
-
基于CANCEL恢复，
-
基于备份控制文件恢复
-
基于重做日志序列号的恢复 (Log sequence recovery)将数据恢复到指定的重做日志序号(仅使用RMAN时有效)
recover database until sequence 100 thread 1;
-
基于还原点的恢复
Create restore point rest_01;
Recover database until restore point rest_01;
-
TSPITR
常用的有：
#基于TIME 参数不完全恢复
run {
shutdown immediate;
startup mount;
set until time "to_date('20201127 10:09:53','yyyymmdd hh24:mi:ss')";
restore database;
recover database;
alter database open resetlogs;
}
#基于SCN 参数不完全恢复
run {
shutdown immediate;
startup mount;
set until scn 5436760;
restore database;
recover database;
alter database open resetlogs;
}
#基于SEQUENCE 参数不完全恢复:
run {
shutdown immediate;
startup mount;
set until sequence 1;
restore database;
recover database;
alter database open resetlogs;
}
1.3pdb的不完全恢复
无论是CDB还是非CDB步骤大致一样：
-
关闭数据库并备份数据库(以防止恢复失败)
-
启动数据库到mount 状态
-
还原所有数据文件，同时可以选择还原控制文件(注意需要还原所有数据文件，而不仅仅是受损文件)
-
将数据库恢复至某个时间点、序列、或系统改变号
-
使用RESETLOGS关键字打开数据库
区别在于，PDB的恢复上，oracle可以使用辅助实例，就和RECOVER TABLE一样.
所需空间确保是源PDB一倍
1) 关闭PDB
RMAN> alter pluggable database orclpdb close;
2) 还原PDB
restore pluggable database orclpdb until time "to_date('2022/05/17 10:42:00','YYYY/MM/DD HH24:MI:SS')";
recover pluggable database orclpdb until time "to_date('2022/05/17 10:42:00','YYYY/MM/DD HH24:MI:SS')";
3) 打开PDB
alter pluggable database orclpdb open resetlogs;
2.logmnr日志挖掘
当前会话有效
2.1DML日志挖掘步骤
DML误操作优先级：
1. DML更优先使用闪回，`flashback query` `flashback table`使用undo，但时间长了可能undo被覆盖
2. 使用逻辑备份（expdp|impdp）
3. TSPITR基于表空间时间点
4. flashback database
5. rman不完全恢复
rman不完全恢复前，为了安全，一定要先全库备份。
rman不完全恢复最慢,要shutdown、restore and recover到一个时间点，消耗太大，所以尽量不选择使用
1）添加 database 补充日志
SQL>ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
注意：为了避免日志遗漏，这步要先执行。
2）确定要分析的日志范围，添加日志，分析
SQL>execute dbms_logmnr.add_logfile(logfilename=>'日志',options=>dbms_logmnr.new);
提供第一个要加载的日志文件
SQL>execute dbms_logmnr.add_logfile(logfilename=>'追加日志',options=>dbms_logmnr.addfile);
可以反复添加补充多个日志文件
3）执行 logmnr 分析
SQL>execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
4）查询分析结果，
SQL>select username,scn,timestamp,sql_redo from v$logmnr_contents where seg_name='表名';
可以设置时间格式，也可以在显示方式里再确定格式.
#alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';设置日期格式
5）关闭日志分析
SQL>execute dbms_logmnr.end_logmnr;
2.1DDL日志挖掘步骤
11g之前到12c部分版本都能使用utl_file_dir
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
1）建立一个存放 dict.oral 的目录，设置参数 utl_file_dir 指定该目录，
$ mkdir /home/oracle/logmnr
SQL> alter system set utl_file_dir='/home/oracle/logmnr' scope=spfile;
2）建立数据字典文件 dict.ora
SQL> execute dbms_logmnr_d.build('dict.ora','/home/oracle/logmnr',dbms_logmnr_d.store_in_flat_file);
3）添加日志分析
SQL> execute dbms_logmnr.add_logfile(logfilename=>'日志文件',options=>dbms_logmnr.new);
SQL> execute dbms_logmnr.add_logfile(logfilename=>'追加日志',options=>dbms_logmnr.addfile);
4）执行分析
SQL>
execute dbms_logmnr.start_logmnr(dictfilename=>
'/home/oracle/logmnr/dict.ora',options=>dbms_logmnr.ddl_dict_tracking);
5）查看分析结果
SQL> select username,scn,to_char(timestamp,'yyyy-mm-dd hh24:mi:ss'),sql_redo from v$logmnr_contents WHERE USERNAME
='SCOTT' and lower(sql_redo) like '%table%';
6）关闭日志分析
SQL> execute dbms_logmnr.end_logmnr;
19c之后：
后utl_file_dir参数被抛弃，不再使用,当然也不用费事创建，使用更加简单
ddl挖掘示例：
按照官方文档的使用方法：
步骤与之前相似，可以不用创建数据字典，直接在线即可
开启补充日志、添加文件、执行分析、查看结果
ddl区别在于参数：DBMS_LOGMNR.DDL_DICT_TRACKING
官方文档提供的一些选项：
EXECUTE DBMS_LOGMNR.START_LOGMNR(-
OPTIONS => DBMS_LOGMNR.DICT_FROM_REDO_LOGS + -
DBMS_LOGMNR.DDL_DICT_TRACKING + -
DBMS_LOGMNR.COMMITTED_DATA_ONLY + -
DBMS_LOGMNR.PRINT_PRETTY_SQL);
dbms_logmnr.start_logmnr(options=>DBMS_LOGMNR.DDL_DICT_TRACKING);
例子：------------------------------------------
SQL> truncate table t1;
Table truncated.
SQL> commit;
Commit complete.
测试环境没有数据变动，没有切换日志，这条ddl肯定在当前redo,通过sql找到当前日志为redo02.log
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
execute dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/oradata/ORCL/redo02.log',options=>dbms_logmnr.new);
execute dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/oradata/ORCL/redo01.log',options=>dbms_logmnr.addfile);
SQL> exec dbms_logmnr.start_logmnr(options=>DBMS_LOGMNR.DDL_DICT_TRACKING);
PL/SQL procedure successfully completed.
col sql_redo for a60
col username for a10
set linesize 200
set pagesize 100
#找到了这个时间点
SQL> select username,scn,timestamp,sql_redo from v$logmnr_contents where seg_name='T1';
USERNAME SCN TIMESTAMP SQL_REDO
---------- ---------- --------- ------------------------------------------------------------
SYS 4093801 17-MAY-22 truncate table t1;
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
SQL> select username,scn,timestamp,sql_redo from v$logmnr_contents where seg_name='T1';
USERNAME SCN TIMESTAMP SQL_REDO
---------- ---------- ------------------- ------------------------------------------------------------
SYS 4093801 2022-05-17 15:28:53 truncate table t1;
3.基于时间点的恢复示例(logmnr dml找到时间点)
3.1模拟dml操作
sqlplus abc/abc
#创建一张表
create table t1 as select * from all_objects;
SQL> select count(*) from tab1;
COUNT(*)
----------
67101
#插入数据
SQL> insert into tab1 select * from all_objects;
已创建 67102 行。
SQL> select count(*) from tab1;
COUNT(*)
----------
134203
#产生归档
alter system switch logfile;
insert into tab1 select * from all_objects;
alter system switch logfile;
SQL> select count(*) from tab1;
COUNT(*)
----------
201305
#delete操作
SQL> delete from tab1;
已删除 201305 行。
SQL> commit;
提交完成。
#delete后在进行insert、update
SQL> insert into tab1 select * from all_objects where OBJECT_ID=10;
已创建 1 行。
SQL> update tab1 set OBJECT_ID=333;
已更新 1 行。
SQL> commit;
3.2dml日志分析，找到准确时间点
#找到归档
SQL> select name from v$archived_log where name is not null;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/product/19.3/dbhome_1/dbs/arch1_19_1104360443.dbf
/u01/app/oracle/product/19.3/dbhome_1/dbs/arch1_20_1104360443.dbf
/u01/app/oracle/product/19.3/dbhome_1/dbs/arch1_21_1104360443.dbf
#找到在线日志
SQL> col member for a50
SQL> select group#,member from v$logfile;
GROUP# MEMBER
---------- --------------------------------------------------
3 /u01/app/oracle/oradata/ORCL/redo03.log
2 /u01/app/oracle/oradata/ORCL/redo02.log
1 /u01/app/oracle/oradata/ORCL/redo01.log
#查看当前日志组
SQL> select group#,sequence#,status from v$log;
GROUP# SEQUENCE# STATUS
---------- ---------- ----------------
1 22 CURRENT
2 20 INACTIVE
3 21 INACTIVE
#添加database补充日志
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
#添加第一个日志，分析。先添加一个归档日志分析（可以结合归档生成时间来判断）
execute dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/product/19.3/dbhome_1/dbs/arch1_20_1104360443.dbf',options=>dbms_logmnr.new);
#添加后续的日志，ogmnr.new变为logmnr.addfile
execute dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/product/19.3/dbhome_1/dbs/arch1_21_1104360443.dbf',options=>dbms_logmnr.addfile);
#添加在线日志
execute dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/oradata/ORCL/redo01.log',options=>dbms_logmnr.addfile);
#执行 logmnr 分析
execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
#查询结果，通过v$logmnr_contents视图查看,结果往往很多，根据适合条件限制
#alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';设置日期格式
select username,scn,timestamp,sql_redo from v$logmnr_contents where seg_name='TAB1' and rownum<=3;
col sql_redo for a60
col username for a10
set linesize 200
set pagesize 100
SQL> select username,scn,timestamp,sql_redo from v$logmnr_contents where seg_name='TAB1' and rownum<8;
USERNAME SCN TIMESTAMP SQL_REDO
---------- ---------- ------------------- ------------------------------------------------------------
UNKNOWN 5163131 2022-05-16 20:47:02 create table tab1 as select * from all_objects;
UNKNOWN 5164356 2022-05-16 20:48:45 insert into "ABC"."TAB1"("OWNER","OBJECT_NAME","SUBOBJECT_NA
ME","OBJECT_ID","DATA_OBJECT_ID","OBJECT_TYPE","CREATED","LA
ST_DDL_TIME","TIMESTAMP","STATUS","TEMPORARY","GENERATED","S
ECONDARY","NAMESPACE","EDITION_NAME","SHARING","EDITIONABLE"
,"ORACLE_MAINTAINED","APPLICATION","DEFAULT_COLLATION","DUPL
ICATED","SHARDED","CREATED_APPID","CREATED_VSNID","MODIFIED_
APPID","MODIFIED_VSNID") values ('SYS','I_HH_OBJ#_INTCOL#',N
ULL,'70','70','INDEX',TO_DATE('2019-04-17 00:56:15', 'yyyy-m
m-dd hh24:mi:ss'),TO_DATE('2019-04-17 00:56:15', 'yyyy-mm-dd
hh24:mi:ss'),'2019-04-17:00:56:15','VALID','N','N','N','4',
NULL,'NONE',NULL,'Y','N',NULL,'N','N',NULL,NULL,NULL,NULL);
UNKNOWN 5164356 2022-05-16 20:48:45 insert into "ABC"."TAB1"("OWNER","OBJECT_NAME","SUBOBJECT_NA
ME","OBJECT_ID","DATA_OBJECT_ID","OBJECT_TYPE","CREATED","LA
ST_DDL_TIME","TIMESTAMP","STATUS","TEMPORARY","GENERATED","S
ECONDARY","NAMESPACE","EDITION_NAME","SHARING","EDITIONABLE"
,"ORACLE_MAINTAINED","APPLICATION","DEFAULT_COLLATION","DUPL
ICATED","SHARDED","CREATED_APPID","CREATED_VSNID","MODIFIED_
APPID","MODIFIED_VSNID") values ('SYS','FIXED_OBJ$',NULL,'71
','71','TABLE',TO_DATE('2019-04-17 00:56:15', 'yyyy-mm-dd hh
24:mi:ss'),TO_DATE('2019-04-17 00:56:15', 'yyyy-mm-dd hh24:m
i:ss'),'2019-04-17:00:56:15','VALID','N','N','N','1',NULL,'M
ETADATA LINK',NULL,'Y','N','USING_NLS_COMP','N','N',NULL,NUL
L,NULL,NULL);
UNKNOWN 5164356 2022-05-16 20:48:45 insert into "ABC"."TAB1"("OWNER","OBJECT_NAME","SUBOBJECT_NA
ME","OBJECT_ID","DATA_OBJECT_ID","OBJECT_TYPE","CREATED","LA
ST_DDL_TIME","TIMESTAMP","STATUS","TEMPORARY","GENERATED","S
ECONDARY","NAMESPACE","EDITION_NAME","SHARING","EDITIONABLE"
,"ORACLE_MAINTAINED","APPLICATION","DEFAULT_COLLATION","DUPL
ICATED","SHARDED","CREATED_APPID","CREATED_VSNID","MODIFIED_
APPID","MODIFIED_VSNID") values ('SYS','I_FIXED_OBJ$_OBJ#',N
ULL,'72','72','INDEX',TO_DATE('2019-04-17 00:56:15', 'yyyy-m
m-dd hh24:mi:ss'),TO_DATE('2019-04-17 00:56:15', 'yyyy-mm-dd
hh24:mi:ss'),'2019-04-17:00:56:15','VALID','N','N','N','4',
NULL,'NONE',NULL,'Y','N',NULL,'N','N',NULL,NULL,NULL,NULL);
#查看delete操作，加条件 lower(sql_redo) like 'delete%';
SQL> select username,scn,timestamp,sql_redo from v$logmnr_contents where seg_name='TAB1' and lower(sql_redo) like 'delete from%' and rownum<3;
USERNAME SCN TIMESTAMP SQL_REDO
---------- ---------- ------------------- ------------------------------------------------------------
UNKNOWN 5164635 2022-05-16 20:52:28 delete from "ABC"."TAB1" where "OWNER" = 'SYS' and "OBJECT_N
AME" = 'I_FILE#_BLOCK#' and "SUBOBJECT_NAME" IS NULL and "OB
JECT_ID" = '9' and "DATA_OBJECT_ID" = '9' and "OBJECT_TYPE"
= 'INDEX' and "CREATED" = TO_DATE('2019-04-17 00:56:14', 'yy
yy-mm-dd hh24:mi:ss') and "LAST_DDL_TIME" = TO_DATE('2019-04
-17 00:56:14', 'yyyy-mm-dd hh24:mi:ss') and "TIMESTAMP" = '2
019-04-17:00:56:14' and "STATUS" = 'VALID' and "TEMPORARY" =
'N' and "GENERATED" = 'N' and "SECONDARY" = 'N' and "NAMESP
ACE" = '4' and "EDITION_NAME" IS NULL and "SHARING" = 'NONE'
and "EDITIONABLE" IS NULL and "ORACLE_MAINTAINED" = 'Y' and
"APPLICATION" = 'N' and "DEFAULT_COLLATION" IS NULL and "DU
PLICATED" = 'N' and "SHARDED" = 'N' and "CREATED_APPID" IS N
ULL and "CREATED_VSNID" IS NULL and "MODIFIED_APPID" IS NULL
and "MODIFIED_VSNID" IS NULL and ROWID = 'AAAR0xAAMAAAACDAA
A';
UNKNOWN 5164635 2022-05-16 20:52:28 delete from "ABC"."TAB1" where "OWNER" = 'SYS' and "OBJECT_N
AME" = 'I_OBJ3' and "SUBOBJECT_NAME" IS NULL and "OBJECT_ID"
= '38' and "DATA_OBJECT_ID" = '38' and "OBJECT_TYPE" = 'IND
EX' and "CREATED" = TO_DATE('2019-04-17 00:56:14', 'yyyy-mm-
dd hh24:mi:ss') and "LAST_DDL_TIME" = TO_DATE('2019-04-17 00
:56:14', 'yyyy-mm-dd hh24:mi:ss') and "TIMESTAMP" = '2019-04
-17:00:56:14' and "STATUS" = 'VALID' and "TEMPORARY" = 'N' a
nd "GENERATED" = 'N' and "SECONDARY" = 'N' and "NAMESPACE" =
'4' and "EDITION_NAME" IS NULL and "SHARING" = 'NONE' and "
EDITIONABLE" IS NULL and "ORACLE_MAINTAINED" = 'Y' and "APPL
ICATION" = 'N' and "DEFAULT_COLLATION" IS NULL and "DUPLICAT
ED" = 'N' and "SHARDED" = 'N' and "CREATED_APPID" IS NULL an
d "CREATED_VSNID" IS NULL and "MODIFIED_APPID" IS NULL and "
MODIFIED_VSNID" IS NULL and ROWID = 'AAAR0xAAMAAAACDAAB';
#找到delete时间点
SCN TIMESTAMP
5164635 2022-05-16 20:52:28
execute dbms_logmnr.end_logmnr;
3.3恢复
恢复之后控制文件新的，而日志文件时旧的，所以不完全恢复要resetlogs
shutdown immediate;
startup mount;
run {
allocate channel ch1 type disk ;
allocate channel ch2 type disk;
sql'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"';
set until time '2022-05-16 20:52:28';
restore database;
recover database;
alter database open resetlogs;
}
#restore database orclpdb until time "to_date('2022/05/17 10:42:00','YYYY/MM/DD HH24:MI:SS')";
#set until time "to_date('2022/05/17 10:42:00','YYYY/MM/DD HH24:MI:SS')";
#恢复到过去时间点，但是过去时间点到现在的数据没了
OBJECT_ID=333不存在
4.基于SCN的恢复
SCN转换成时间
select scn_to_timestamp(current_scn) from v$database;
时间转换成SCN
select timestamp_to_scn(sysdate) from v$database;
确定scn后，恢复
在ddl挖掘示例中得到scn
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
SQL> select username,scn,timestamp,sql_redo from v$logmnr_contents where seg_name='T1';
USERNAME SCN TIMESTAMP SQL_REDO
---------- ---------- ------------------- ------------------------------------------------------------
SYS 4093801 2022-05-17 15:28:53 truncate table t1;
shutdown immediate;
startup mount;
run {
allocate channel ch1 type disk ;
allocate channel ch2 type disk;
set until scn 4093801;
restore database;
recover database;
alter database open resetlogs;
}
5.基于CANCEL的恢复
基于终止的恢复
例如想要完全恢复时丢失了所需要的一些日志（可能是归档，也可能时redo）
最多只能恢复到丢失之前
run {
restore database;
recover database until cancel;
alter database open resetlogs;
}
