3.rman 几个常用恢复命令实验记录

小小年纪净学坏

已于 2024-01-19 16:53:07 修改

阅读量981

点赞数 23

分类专栏： #Oracle# 备份恢复模块文章标签：数据库 oracle

于 2024-01-18 16:04:07 首次发布

本文链接：https://blog.csdn.net/weixin_42005129/article/details/135675615

版权

#Oracle# 备份恢复模块专栏收录该内容

4 篇文章 0 订阅

订阅专栏

1、本文概述

本文主要记录RMAN 以下几个常用命令的作用和恢复程度、及其适用场景。同时顺便测试哪些场景下必须使用resetlogs打开
recover database;
recover database until cancel
recover database until cancel using backup controlfil
recover database using backup controlfile;
recover database using backup controlfile until cancel;

2、操作前知识科普

3、实验过程

1、recover database;

测试场景：控制文件丢失数据文件、归档文件、online redo log 无丢失

测试结论：1）使用recover database 恢复会自动使用归档日志和online redo log 进行一致性恢复。所以，其恢复终点并不是控制文件所记录的SCN版本为终点，而是以redo log或者归档的redo record 所记录的SCN为准直到日志结束。但是测试可见redo record的版本必须比还原的控制版本大不然 open db 会报错：某文件need 一致性恢复

2）因为使用备份的控制文件还原恢复，所以所记录的版本跟当前DB所记录的版本不一致，所以必须得使用 open resetlogs 开启数据库。

3）open resetlogs 后会重置一个数据库的化身（incarnation）

测试过程：

1.1 创建一个测试账号和表空间
create user cat identified by oracle;
create tablespace cat datafile '/data/app/oracle/oradata/CAT/datafile/cat01.dbf' size 100m;
grant create session to cat;
grant dba to cat;
grant unlimited tablespace to cat;
alter user cat default tablespace cat;
1.2 构造测试数据
conn cat/oracle
create table t (id number,date1 date);

SQL> begin
2 for i in 1 .. 10 loop
3 insert into t values(i,sysdate);
4 commit;
5 end loop;
6 end;
7 /

PL/SQL procedure successfully completed.

SQL> select sequence#,status,group# from v$log;

SEQUENCE# STATUS GROUP#
---------- ---------------- ----------
34 INACTIVE 1
35 INACTIVE 2
36 CURRENT 3

1.3 让数据落盘
alter system checkpoint ;
SQL> alter system checkpoint ; ##不执行多次检查点，日志不一定会切换

System altered.

SQL> select sequence#,status,group# from v$log;

SEQUENCE# STATUS GROUP#
---------- ---------------- ----------
34 INACTIVE 1
35 INACTIVE 2
36 CURRENT 3

1.4 执行一次全库备份，此时t表有10条数，列出此时的化身

RMAN> list incarnation ;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 CAT 971058628 PARENT 1 24-AUG-13
2 2 CAT 971058628 CURRENT 925702 18-JAN-24

1.5 执行全库备份

backup database current controlfile format '/data/backup/rman_%d_%t_%I_%p_%s' tag='full' ;

1.6 再插入十条数据。同时让这10条数据在归档中
SQL> begin
2 for i in 1 .. 10 loop
3 insert into t values(i,sysdate);
4 commit;
5 end loop;
6 end;
7 /

PL/SQL procedure successfully completed.

SQL> select count(*) from t ;

COUNT(*)
----------
20
SQL> select sequence#,status,group# from v$log;

SEQUENCE# STATUS GROUP#
---------- ---------------- ----------
34 INACTIVE 1
35 INACTIVE 2
36 CURRENT 3

SQL> alter system switch logfile ;

System altered.

SQL> select sequence#,status,group# from v$log;

SEQUENCE# STATUS GROUP#
---------- ---------------- ----------
37 CURRENT 1
35 INACTIVE 2
36 ACTIVE 3

此时10条记录已经在归档中。

备份归档

backup archivelog all ;

RMAN> backup archivelog all ;

Starting backup at 18-JAN-24
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=172 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=35 RECID=1 STAMP=1158579304
input archived log thread=1 sequence=36 RECID=2 STAMP=1158590731
input archived log thread=1 sequence=37 RECID=3 STAMP=1158590916
channel ORA_DISK_1: starting piece 1 at 18-JAN-24
channel ORA_DISK_1: finished piece 1 at 18-JAN-24
piece handle=/data/app/oracle/product/11.2.0.4/db_1/dbs/042gtce5_1_1 tag=TAG20240118T144837 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 18-JAN-24

RMAN> exit

1.7 再插入10条数据让其他 current redo 中

SQL> select sequence#,status,group# from v$log;

SEQUENCE# STATUS GROUP#
---------- ---------------- ----------
37 ACTIVE 1
38 CURRENT 2 ===>可见备份自动将日志文件归档了
36 ACTIVE 3

SQL> begin
2 for i in 1 .. 10 loop
3 insert into t values(i,sysdate);
4 commit;
5 end loop;
6 end;
7 /

PL/SQL procedure successfully completed.

SQL> select sequence#,status,group# from v$log;

SEQUENCE# STATUS GROUP#
---------- ---------------- ----------
37 INACTIVE 1
38 CURRENT 2
36 INACTIVE 3

此时如果使用全备时的控制文件恢复则其版本：有10条数据在datafile中，则有10条数据在归档日志中，有10条数据在current redo log中

1.8 模拟控制文件丢失

RMAN> list backup of controlfile ;

using target database control file instead of recovery catalog

List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 9.36M DISK 00:00:02 18-JAN-24
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20240118T120054
Piece Name: /data/app/oracle/product/11.2.0.4/db_1/dbs/022gt2jp_1_1
Control File Included: Ckp SCN: 2037677 Ckp time: 18-JAN-24

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3 Full 9.33M DISK 00:00:01 18-JAN-24
BP Key: 3 Status: AVAILABLE Compressed: NO Tag: FULL
Piece Name: /data/backup/rman_CAT_1158580860_971058628_1_3
Control File Included: Ckp SCN: 2037678 Ckp time: 18-JAN-24
强制关闭。直接使用旧的控制文件版本替换当前版本，模拟控制文件丢失

RMAN> restore controlfile from '/data/backup/rman_CAT_1158580860_971058628_1_3' ;

Starting restore at 18-JAN-24
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=191 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/data/app/oracle/oradata/CAT/controlfile/o1_mf_ltk4l4qz_.ctl
Finished restore at 18-JAN-24
Recovery Manager complete.
[oracle@cat ~]$ ll /data/app/oracle/oradata/CAT/controlfile/o1_mf_ltk4l4qz_.ctl
-rw-r----- 1 oracle oinstall 9748480 Jan 18 14:59 /data/app/oracle/oradata/CAT/controlfile/o1_mf_ltk4l4qz_.ctl
[oracle@cat ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 18 15:01:21 2024

Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select sequence#,status,group# from v$log; //v$log 的信息来自于控制文件，所以在恢复到全备时候的控制文件后，日志信息跟备份时间点的数据一致了

SEQUENCE# STATUS GROUP#
---------- ---------------- ----------
34 INACTIVE 1
36 CURRENT 3
35 INACTIVE 2

使用recover database 命令恢复
RMAN> recover database ;

Starting recover at 18-JAN-24
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=191 device type=DISK

starting media recovery

archived log for thread 1 with sequence 36 is already on disk as file /data/app/oracle/oradata/CAT/onlinelog/o1_mf_3_ltk4l6z4_.log
archived log for thread 1 with sequence 37 is already on disk as file /data/app/oracle/oradata/CAT/onlinelog/o1_mf_1_ltk4l6v6_.log
archived log for thread 1 with sequence 38 is already on disk as file /data/app/oracle/oradata/CAT/onlinelog/o1_mf_2_ltk4l6x5_.log
archived log file name=/data/app/oracle/oradata/CAT/onlinelog/o1_mf_3_ltk4l6z4_.log thread=1 sequence=36
archived log file name=/data/app/oracle/oradata/CAT/onlinelog/o1_mf_1_ltk4l6v6_.log thread=1 sequence=37
archived log file name=/data/app/oracle/oradata/CAT/onlinelog/o1_mf_2_ltk4l6x5_.log thread=1 sequence=38
media recovery complete, elapsed time: 00:00:00
Finished recover at 18-JAN-24 //可以看出自动使用了归档日志和online 日志恢复数据库。但是这里的归档日志用不到因为跟online redo log的文件内容所重复了

RMAN>

[oracle@cat ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 18 15:06:47 2024

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 noresetlogs;
alter database open noresetlogs
*
ERROR at line 1:
ORA-01588: must use RESETLOGS option for database open
SQL> alter database open RESETLOGS;

Database altered.

SQL> select count(*) from t;

COUNT(*)
----------
30

1.9 核对 open resetlogs后化身
connected to target database: CAT (DBID=971058628)

RMAN> list incarnation ;

using target database control file instead of recovery catalog

2、RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL ;

以下步骤因为3.1中测试open resetlogs 所以下面测试也需要重新备份和日志序列从1 开始

测试结论：

1）同mos给到的结论一样：

if you use a "BACKUP CONTROLFILE", or previously used a CANCEL based recover command, then we need to recover, and finally apply the online current redolog.Example:

假如使用 BACKUP CONTROLFILE 子句,或者以前使用过基于CANCEL的恢复命令,那么我们需要进行恢复,并最终应用在线重做日志 ===>使用backup controlfile 子句（recover database using backup controlfile; recover database using backup controlfile until cancel;）或者使用过基于cancel的恢复，必须进行恢复而且应用完归档后最终会应用online current redolog 。这里只测试了USING BACKUP CONTROLFILE UNTIL CANCEL ，其他的语句后续测试，不过MOS这么说，基本就是跟USING BACKUP CONTROLFILE UNTIL CANCEL一样的效果

2）recover 带任何using 语句的都是在SQLPLUS中执行，不是在rman中执行。

3)USING BACKUP CONTROLFILE UNTIL CANCEL 会自动提示需要的归档，根据提示依次输入所有归档即可（确保需要的归档存在），但是最后报归档路径不存在的时候就需要手动指定online redo log 了这点要注意。特别细节

1.构造测试数据

conn cat/oracle

drop table t;
create table t (id number,date1 date);

SQL> conn cat/oracle
Connected.
SQL> begin
2 for i in 1 .. 10 loop
3 insert into t values(i,sysdate);
4 commit;
5 end loop;
6 end;
7 /

PL/SQL procedure successfully completed.

2.核对此时的日志序列
SQL> select sequence#,status,group# from v$log;

SEQUENCE# STATUS GROUP#
---------- ---------------- ----------
1 INACTIVE 1
2 CURRENT 2
0 UNUSED 3

3.让数据落盘
alter system checkpoint ;

SQL> alter system checkpoint ; ##不执行多次检查点，日志不一定会切换

System altered.

SQL> select sequence#,status,group# from v$log;

SEQUENCE# STATUS GROUP#
---------- ---------------- ----------
1 INACTIVE 1
2 CURRENT 2
0 UNUSED 3

执行一次备份，此时t表有10条数据
4.列出此时的化身

RMAN> list incarnation ;

5.执行全库备份

backup database include current controlfile format '/data/backup/rman_%d_%t_%I_%p_%s' tag='full' ;

6.再插入十条数据。同时让这10条数据在归档中
SQL> begin
2 for i in 1 .. 10 loop
3 insert into t values(i,sysdate);
4 commit;
5 end loop;
6 end;
7 /

PL/SQL procedure successfully completed.

SQL> select count(*) from t ;

COUNT(*)
----------
20
SQL> select sequence#,status,group# from v$log;

SEQUENCE# STATUS GROUP#
---------- ---------------- ----------
1 INACTIVE 1
2 CURRENT 2
0 UNUSED 3

SQL> alter system switch logfile ;

System altered.

SQL> select sequence#,status,group# from v$log;

SEQUENCE# STATUS GROUP#
---------- ---------------- ----------
1 INACTIVE 1
2 ACTIVE 2
3 CURRENT 3

此时10条记录已经在归档中。

7.备份归档

backup archivelog all ;

8.再插入10条数据让其他 current redo 中

SQL> select sequence#,status,group# from v$log;

SEQUENCE# STATUS GROUP#
---------- ---------------- ----------
4 CURRENT 1 ===>可见备份自动将日志文件归档了
2 ACTIVE 2
3 ACTIVE 3

SQL> begin
2 for i in 1 .. 10 loop
3 insert into t values(i,sysdate);
4 commit;
5 end loop;
6 end;
7 /

PL/SQL procedure successfully completed.

SQL> select sequence#,status,group# from v$log;

SEQUENCE# STATUS GROUP#
---------- ---------------- ----------
4 CURRENT 1
2 ACTIVE 2
3 ACTIVE 3

此时根据先前备份的控制文件版本：10条数据在数据库全备中。10条记录在新的归档备份中 10条还在online redo log 中还未归档

模拟控制文件丢失，然后使用备份的控制文件恢复 + recover database using backup controlfile until cancel;命令

9.强制关闭并还原控制文件模拟控制文件丢失

shutdown abort;

restore controlfile from '/data/backup/rman_CAT_1158679942_971058628_1_9' ;

RMAN> restore controlfile from '/data/backup/rman_CAT_1158679942_971058628_1_9' ;

Starting restore at 18-JAN-24
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=191 device type=DISK

RMAN> exit

10.恢复前检查

根据Open Database failed - DATAFILE NEEDS MORE RECOVERY TO BE CONSISTENT ORA-1194 ORA-1547 ORA-1110 (Doc ID 1528788.1) 文档给出的结论：

Once all datafiles are consistent and fuzzy=NO, the database can be opened and the ORA-01547 should no longer be returned.

一旦所有数据文件一致且模糊=否,就可以打开数据库,并且不再返回ORA-01547。

1)一致性检查和模糊状态
set numwidth 30;
set pagesize 50000 line 200 ;
alter session set nls_date_format = 'DD-MON-RRRR HH24:MI:SS';
select status,checkpoint_change#,checkpoint_time, resetlogs_change#, resetlogs_time, count(*), fuzzy from v$datafile_header group by
status,checkpoint_change#,checkpoint_time, resetlogs_change#, resetlogs_time, fuzzy;

STATUS CHECKPOINT_CHANGE# CHECKPOINT_TIME RESETLOGS_CHANGE# RESETLOGS_TIME COUNT(*) FUZ
------- ------------------------------ -------------------- ------------------------------ -------------------- ------------------------------ ---
ONLINE 2095481 19-JAN-2024 15:32:32 2049999 18-JAN-2024 17:43:13 5 YES

2）控制文件状态
select name, controlfile_type from v$database ;
SQL> select name, controlfile_type from v$database ;

NAME CONTROL
--------- -------
CAT BACKUP
3）检查数据文件一致性

select status,
resetlogs_change#,
resetlogs_time,
checkpoint_change#,
to_char(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time,
count(*)
from v$datafile_header
group by status, resetlogs_change#, resetlogs_time, checkpoint_change#, checkpoint_time
order by status, checkpoint_change#, checkpoint_time ;

STATUS RESETLOGS_CHANGE# RESETLOGS_TIME CHECKPOINT_CHANGE# CHECKPOINT_TIME COUNT(*)
------- ------------------------------ -------------------- ------------------------------ ----------------------- ------------------------------
ONLINE 2049999 18-JAN-2024 17:43:13 2095481 19-JAN-2024 15:32:32 5 --数据文件是一致了

4）-- Check for datafile status, and fuzziness 检查所有数据文件的状态和模糊性
select STATUS, ERROR, FUZZY, count(*) from v$datafile_header group by STATUS, ERROR, FUZZY ;

SQL> select STATUS, ERROR, FUZZY, count(*) from v$datafile_header group by STATUS, ERROR, FUZZY ;

STATUS ERROR FUZ COUNT(*)
------- ----------------------------------------------------------------- --- ------------------------------
ONLINE YES 5 ---虽然一致性没问题,但是模糊状态还是YES 所有打开会报错

5）-- Check for MIN, and MAX SCN in Datafiles 检查文件的最大SCN和最小SCN
select min(CHECKPOINT_CHANGE#), max(CHECKPOINT_CHANGE#) from v$datafile_header ;

SQL> select min(CHECKPOINT_CHANGE#), max(CHECKPOINT_CHANGE#) from v$datafile_header ;

MIN(CHECKPOINT_CHANGE#) MAX(CHECKPOINT_CHANGE#)
------------------------------ ------------------------------
2095481 2095481

6）-- Checking for Datafile(s) which needs recovery
select * from v$recover_file ;

SQL> select * from v$recover_file ;

no rows selected

SQL> select min(FHSCN) "LOW FILEHDR SCN"
2 , max(FHSCN) "MAX FILEHDR SCN"
3 , max(FHAFS) "Min PITR ABSSCN"
4 from X$KCVFH ;

LOW FILEHDR SCN MAX FILEHDR SCN Min PITR ABSSCN
---------------- ---------------- ----------------
2095557 2095557 0

-- "LOW FILEHDR SCN" - this is the SCN at which recovery process starts
-- "MAX FILEHDR SCN" - this is the SCN we must recover to to get all datafiles consistent
-- IF "Min PITR ABSSCN" != 0 AND > "MAX FILEHDR SCN"
-- THEN "Min PITR ABSSCN" is the SCN we must recover to to get all datafiles consistent

11. 执行恢复

if you use a "BACKUP CONTROLFILE", or previously used a CANCEL based recover command, then we need to recover, and finally apply the online current redolog.Example:

假如使用 BACKUP CONTROLFILE 子句,或者以前使用过基于CANCEL的恢复命令,那么我们需要进行恢复,并最终应用在线重做日志 ===>所以需要恢复到online 日志为止。

SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL ;
ORA-00279: change 2446300 generated at 02/13/2013 15:09:44 needed for thread 1
ORA-00289: suggestion : /<path>/o1_mf_1_450_8kqbn929_.arc
ORA-00280: change 2446300 for thread 1 is in sequence #450
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 2451694 generated at 02/13/2013 16:00:25 needed for thread 1
ORA-00289: suggestion : /<path>/o1_mf_1_451_8kqbnbmh_.arc
ORA-00280: change 2451694 for thread 1 is in sequence #451
ORA-00278: log file '/<path>/o1_mf_1_450_8kqbn929_.arc' no longer needed for this recovery
...
< all required logs applied >

。。。。。

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log '/data/arch/1_4_1158601393.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00308: cannot open archived log '/data/arch/1_4_1158601393.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

SQL> select STATUS, ERROR, FUZZY, count(*) from v$datafile_header group by STATUS, ERROR, FUZZY ;

STATUS ERROR FUZ COUNT(*)
------- ----------------------------------------------------------------- --- ------------------------------
ONLINE YES 5 ---核对模糊状态
SQL> select member from v$logfile ;

MEMBER
--------------------------------------------------------------------------------
/data/app/oracle/oradata/CAT/onlinelog/o1_mf_3_ltk4l6z4_.log
/data/app/oracle/oradata/CAT/onlinelog/o1_mf_2_ltk4l6x5_.log
/data/app/oracle/oradata/CAT/onlinelog/o1_mf_1_ltk4l6v6_.log

SQL> recover database using backup controlfile until cancel ;
ORA-00279: change 2095557 generated at 01/19/2024 15:34:12 needed for thread 1
ORA-00289: suggestion : /data/arch/1_4_1158601393.dbf
ORA-00280: change 2095557 for thread 1 is in sequence #4
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/data/app/oracle/oradata/CAT/onlinelog/o1_mf_1_ltk4l6v6_.log
Log applied.
Media recovery complete.

-- 继续检查所有数据文件的状态和模糊性
select STATUS, ERROR, FUZZY, count(*) from v$datafile_header group by STATUS, ERROR, FUZZY ;

STATUS ERROR FUZ COUNT(*)
------- ----------------------------------------------------------------- --- ----------
ONLINE NO 5 --在恢复了online redo log 后就将模糊性变为了 NO。满足了打开数据库必须保持一致性和模式性为NO的条件

打开

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 NORESETLOGS;
alter database open NORESETLOGS
*
ERROR at line 1:
ORA-01588: must use RESETLOGS option for database open ==>可以看出使用备份控制文件恢复丢失控制文件必须open resetlogs
SQL> select name, controlfile_type from v$database ;

NAME CONTROL
--------- -------
CAT BACKUP

SQL> alter database open RESETLOGS;

Database altered.

小小年纪净学坏

关注

23
点赞
踩
25

收藏

觉得还不错? 一键收藏
0
评论
3.rman 几个常用恢复命令实验记录

本文主要记录RMAN 以下几个常用命令的作用和恢复程度、及其适用场景。同时顺便测试哪些场景下必须使用resetlogs打开2、操作前知识科普3、实验过程测试场景：控制文件丢失数据文件、归档文件、online redo log 无丢失测试结论：1）使用recover database 恢复会自动使用归档日志和online redo log 进行一致性恢复。
复制链接

扫一扫

专栏目录