rman备份与恢复

74 阅读 0 评论 49 点赞

我是靠谱客的博主害羞小霸王，最近开发中收集的这篇文章主要介绍rman备份与恢复，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

1.查看数据库是否设置归档

sys@CXMTDB 16:51:05> archive log list;
Database log mode
Archive Mode
Automatic archival
Enabled
Archive destination
/archivelog
Oldest online log sequence
27
Next log sequence to archive
29
Current log sequence
29

2.rman连接数据库

[oracle@node02:/home/oracle]$ rman catalog rman/wwwwww
Recovery Manager: Release 19.0.0.0.0 - Production on Thu Sep 30 16:49:31 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates.
All rights reserved.
connected to recovery catalog database
RMAN> connect target /
connected to target database: CXMTDB (DBID=2538411202)

3.对整库备份

RMAN> backup database;

4.查看备份集

RMAN> list backupset;
List of Backup Sets
===================
BS Key
Type LV Size
Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
58
Full
10.20M
DISK
00:00:01
26-SEP-21
BP Key: 59
Status: AVAILABLE
Compressed: NO
Tag: TAG20210926T110827
Piece Name: /u01/app/oracle/product/19.3.0/db/dbs/c-2538411202-20210926-00
SPFILE Included: Modification time: 26-SEP-21
SPFILE db_unique_name: CXMTDB
Control File Included: Ckp SCN: 2361194
Ckp time: 26-SEP-21
BS Key
Type LV Size
Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
118
Full
10.20M
DISK
00:00:00
29-SEP-21
BP Key: 121
Status: AVAILABLE
Compressed: NO
Tag: TAG20210929T173959
Piece Name: /u01/app/oracle/product/19.3.0/db/dbs/c-2538411202-20210929-00
SPFILE Included: Modification time: 29-SEP-21
SPFILE db_unique_name: CXMTDB
Control File Included: Ckp SCN: 3775038
Ckp time: 29-SEP-21
BS Key
Type LV Size
Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
130
Full
1.48G
DISK
00:00:02
30-SEP-21
BP Key: 133
Status: AVAILABLE
Compressed: NO
Tag: TAG20210930T165211
Piece Name: /u01/app/oracle/product/19.3.0/db/dbs/030acilr_1_1
List of Datafiles in backup set 130
File LV Type Ckp SCN
Ckp Time
Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1
Full 4097975
30-SEP-21
NO
/oradata/CXMTDB/system01.dbf
2
Full 4097975
30-SEP-21
NO
/oradata/CXMTDB/rmants.dbf
3
Full 4097975
30-SEP-21
NO
/oradata/CXMTDB/sysaux01.dbf
4
Full 4097975
30-SEP-21
NO
/oradata/CXMTDB/undotbs01.dbf
5
Full 4097975
30-SEP-21
NO
/oradata/CXMTDB/ogg_tbs.dbf
7
Full 4097975
30-SEP-21
NO
/oradata/CXMTDB/users01.dbf
BS Key
Type LV Size
Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
144
Full
10.20M
DISK
00:00:00
30-SEP-21
BP Key: 151
Status: AVAILABLE
Compressed: NO
Tag: TAG20210930T165219
Piece Name: /u01/app/oracle/product/19.3.0/db/dbs/c-2538411202-20210930-00
SPFILE Included: Modification time: 30-SEP-21
SPFILE db_unique_name: CXMTDB
Control File Included: Ckp SCN: 4098008
Ckp time: 30-SEP-21

其中，如下信息为整库备份的信息。

https://www.cndba.cn/hbhe0316/article/4782

130
Full
1.48G
DISK
00:00:02
30-SEP-21
BP Key: 133
Status: AVAILABLE
Compressed: NO
Tag: TAG20210930T165211
Piece Name: /u01/app/oracle/product/19.3.0/db/dbs/030acilr_1_1
List of Datafiles in backup set 130
File LV Type Ckp SCN
Ckp Time
Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1
Full 4097975
30-SEP-21
NO
/oradata/CXMTDB/system01.dbf
2
Full 4097975
30-SEP-21
NO
/oradata/CXMTDB/rmants.dbf
3
Full 4097975
30-SEP-21
NO
/oradata/CXMTDB/sysaux01.dbf
4
Full 4097975
30-SEP-21
NO
/oradata/CXMTDB/undotbs01.dbf
5
Full 4097975
30-SEP-21
NO
/oradata/CXMTDB/ogg_tbs.dbf
7
Full 4097975
30-SEP-21
NO
/oradata/CXMTDB/users01.dbf
BS Key
Type LV Size
Device Type Elapsed Time Completion Time

0级增量备份和1及增量备份
全备份和0级增量备份。全备份和0级增量备份几乎是一样的。唯一的区别，0级增量备份能作为增量备份的基础，而全备份不能作为增量备份的基础。其它方面完全一致
RMAN> backup incremental level=0 database;(增量为0的备份)
RMAN> backup incremental level=1 database;(增量为1的备份)

可以看到Incr 0为0级增量备份

RMAN> list backupset;
162
Incr 0
1.48G
DISK
00:00:02
30-SEP-21
BP Key: 165
Status: AVAILABLE
Compressed: NO
Tag: TAG20210930T165703
Piece Name: /u01/app/oracle/product/19.3.0/db/dbs/050aciuv_1_1
List of Datafiles in backup set 162
File LV Type Ckp SCN
Ckp Time
Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1
0
Incr 4098708
30-SEP-21
NO
/oradata/CXMTDB/system01.dbf
2
0
Incr 4098708
30-SEP-21
NO
/oradata/CXMTDB/rmants.dbf
3
0
Incr 4098708
30-SEP-21
NO
/oradata/CXMTDB/sysaux01.dbf
4
0
Incr 4098708
30-SEP-21
NO
/oradata/CXMTDB/undotbs01.dbf
5
0
Incr 4098708
30-SEP-21
NO
/oradata/CXMTDB/ogg_tbs.dbf
7
0
Incr 4098708
30-SEP-21
NO
/oradata/CXMTDB/users01.dbf
BS Key
Type LV Size
Device Type Elapsed Time Completion Time

5.备份表空间https://www.cndba.cn/hbhe0316/article/4782

RMAN> report schema;
Report of database schema for database with db_unique_name CXMTDB
List of Permanent Datafiles
===========================
File Size(MB) Tablespace
RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1
940
SYSTEM
YES
/oradata/CXMTDB/system01.dbf
2
1024
RMAN_TS
NO
/oradata/CXMTDB/rmants.dbf
3
870
SYSAUX
NO
/oradata/CXMTDB/sysaux01.dbf
4
340
UNDOTBS1
YES
/oradata/CXMTDB/undotbs01.dbf
5
5120
OGG_TBS
NO
/oradata/CXMTDB/ogg_tbs.dbf
7
5
USERS
NO
/oradata/CXMTDB/users01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace
Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1
132
TEMP
32767
/oradata/CXMTDB/temp01.dbf
RMAN> backup tablespace OGG_TBS;

6.备份控制文件

RMAN> backup current controlfile；
RMAN> backup database include current controlfile；

7.单命令和批命令

https://www.cndba.cn/hbhe0316/article/4782

单命令: backup database;
批命令:
run
{
allocate channel c1 device type disk maxpiecesize 1500m;
backup database plus archivelog delete all input;
release channel c1;
}

表示一个备份片中包含一个文件,即使没有达到 1500M,也生成新的备份片,如我的有五个数据文件,还有 SPFILE和 CONTROLFILE 一个备份片,一共生成六个备份片.如果加上plus archivelog delete all input 这个备份选项之后,那 FIELSPERSET 这个参数就会被IGNORE 掉。

8.RMAN一周典型备份方案
1.星期天晚上 -level 0 backup performed(全备份)
2.星期一晚上 -level 2 backup performed
3.星期二晚上 -level 2 backup performed
4.星期三晚上 -level 1 backup performed
5.星期四晚上 -level 2 backup performed
6.星期五晚上 -level 2 backup performed
7.星期六晚上 -level 2 backup performedhttps://www.cndba.cn/hbhe0316/article/4782

如果星期二需要恢复的话，只需要1+2,
如果星期四需要恢复的话，只需要1+4,
如果星期五需要恢复的话，只需要1+4+5,
如果星期六需要恢复的话，只需要1+4+5+6.

1: mkdir -p /u01/app/oracle/rmanbak/scripts/
(放备份脚本目录)
2: mkdir -p /u01/app/oracle/rmanbak/logs/
(放日志目录)
3: mkdir -p /u01/app/oracle/rmanbak/bak/
(放备份文件的目录)
4: crontab -e -u root
30 23 * * 0 /u01/app/oracle/rmanbak/scripts/bakl0
30 23 * * 0 /u01/app/oracle/rmanbak/scripts/bakl2
30 23 * * 0 /u01/app/oracle/rmanbak/scripts/bakl2
30 23 * * 0 /u01/app/oracle/rmanbak/scripts/bakl1
30 23 * * 0 /u01/app/oracle/rmanbak/scripts/bakl2
30 23 * * 0 /u01/app/oracle/rmanbak/scripts/bakl2
30 23 * * 0 /u01/app/oracle/rmanbak/scripts/bakl2
5: service crond restart
6: 附件
-------------------bakl0脚本------------------
#!/bin/sh
cd /u01/app/oracle/rmanbak/scripts
su - oracle -c "rman target / msglog=/u01/app/oracle/rmanbak/logs/bakl0.log < /u01/app/oracle/rmanbak/scripts/bakl0.bak"
-------------------bakl0.bak脚本--------------
run {
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
allocate channel c4 device type disk;
backup incremental level 0 format '/u01/app/oracle/rmanbak/bak/inc0_%u_%T' tag sunday_inc0 database;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}
-------------------bakl1脚本------------------
#!/bin/sh
cd /u01/app/oracle/rmanbak/scripts
su - oracle -c "rman target / msglog=/u01/app/oracle/rmanbak/logs/bakl1.log < /u01/app/oracle/rmanbak/scripts/bakl1.bak"
-------------------bakl1.bak脚本--------------
run {
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
allocate channel c4 device type disk;
backup incremental level 1 format '/u01/app/oracle/rmanbak/bak/inc1_%u_%T' tag inc1 database;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}
-------------------bakl2脚本------------------
#!/bin/sh
cd /u01/app/oracle/rmanbak/scripts
su - oracle -c "rman target / msglog=/u01/app/oracle/rmanbak/logs/bakl2.log < /u01/app/oracle/rmanbak/scripts/bakl2.bak"
-------------------bakl2.bak脚本--------------
run {
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
allocate channel c4 device type disk;
backup incremental level 2 format '/u01/app/oracle/rmanbak/bak/inc2_%u_%T' tag inc2 database;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}

————————————rman恢复测试—————————————————-
使用下来命令对control文件自动备份。
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

1.模拟spfile文件丢失

https://www.cndba.cn/hbhe0316/article/4782

[root@db01:/]$ cd /u01/app/oracle/product/19.3.0/db/dbs/
[root@db01:/u01/app/oracle/product/19.3.0/db/dbs]$ mv spfileorcl.ora spfileorcl.ora.bak
[oracle@db01 dbs]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Fri Oct 1 11:24:51 2021
Version 19.12.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates.
All rights reserved.
connected to target database (not started)
RMAN> startup nomount;
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/19.3.0/dbhome_1/dbs/initorcl.ora'
starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started
Total System Global Area
1073737792 bytes
Fixed Size
8904768 bytes
Variable Size
318767104 bytes
Database Buffers
738197504 bytes
Redo Buffers
7868416 bytes
RMAN> set dbid 1610869203
executing command: SET DBID
RMAN> restore spfile from autobackup;
Starting restore at 2021:10:0111:26:08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=778 device type=DISK
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20211001
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20210930
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20210929
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20210928
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20210927
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20210926
channel ORA_DISK_1: AUTOBACKUP found: c-1610869203-20210926-00
channel ORA_DISK_1: restoring spfile from AUTOBACKUP c-1610869203-20210926-00
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 2021:10:0111:26:12
RMAN> shutdown immediate;
Oracle instance shut down
RMAN> startup
connected to target database (not started)
Oracle instance started
database mounted
database opened
Total System Global Area
2516581448 bytes
Fixed Size
9141320 bytes
Variable Size
1174405120 bytes
Database Buffers
1325400064 bytes
Redo Buffers
7634944 bytes

2.controlfile丢失
注意:在做了alter database open resetlogs;会把online redelog file清空，数据文件丢失.所以这个时候要做一个全备份。https://www.cndba.cn/hbhe0316/article/4782

[oracle@db01 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Oct 1 11:30:11 2021
Version 19.12.0.0.0
Copyright (c) 1982, 2021, Oracle.
All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/control01.ctl
/u01/app/oracle/oradata/ORCL/control02.ctl
[root@db01 ORCL]# mv control01.ctl control01.ctl.bak
[root@db01 ORCL]# mv control02.ctl control02.ctl.bak
RMAN> startup nomount;
connected to target database (not started)
Oracle instance started
Total System Global Area
2516581448 bytes
Fixed Size
9141320 bytes
Variable Size
1174405120 bytes
Database Buffers
1325400064 bytes
Redo Buffers
7634944 bytes
RMAN> restore controlfile from autobackup;
Starting restore at 2021:10:0111:32:24
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=2429 device type=DISK
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20211001
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20210930
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20210929
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20210928
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20210927
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20210926
channel ORA_DISK_1: AUTOBACKUP found: c-1610869203-20210926-00
channel ORA_DISK_1: restoring control file from AUTOBACKUP c-1610869203-20210926-00
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/u01/app/oracle/oradata/ORCL/control01.ctl
output file name=/u01/app/oracle/oradata/ORCL/control02.ctl
Finished restore at 2021:10:0111:32:27
RMAN> alter database mount;
released channel: ORA_DISK_1
Statement processed
RMAN> recover database;
Starting recover at 2021:10:0111:32:46
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1216 device type=DISK
starting media recovery
archived log for thread 1 with sequence 19 is already on disk as file /u01/app/oracle/oradata/ORCL/redo01.log
archived log for thread 1 with sequence 20 is already on disk as file /u01/app/oracle/oradata/ORCL/redo02.log
archived log file name=/u01/app/oracle/oradata/ORCL/redo01.log thread=1 sequence=19
archived log file name=/u01/app/oracle/oradata/ORCL/redo02.log thread=1 sequence=20
media recovery complete, elapsed time: 00:00:02
Finished recover at 2021:10:0111:32:50
RMAN> alter database open resetlogs;
Statement processed

3.redo日志丢失

SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/redo03.log
/u01/app/oracle/oradata/ORCL/redo02.log
/u01/app/oracle/oradata/ORCL/redo01.log
SQL> shutdown immeidate;
SP2-0717: illegal SHUTDOWN option
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
[oracle@db01 ORCL]$ mv redo01.log redo01.log.bak
[oracle@db01 ORCL]$ mv redo02.log redo02.log.bak
[oracle@db01 ORCL]$ mv redo03.log redo03.log.bak
SQL> startup mount;
ORACLE instance started.
Total System Global Area 2516581448 bytes
Fixed Size
9141320 bytes
Variable Size
1174405120 bytes
Database Buffers
1325400064 bytes
Redo Buffers
7634944 bytes
Database mounted.
SQL> recover database until cancel;
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.

4.数据文件丢失

SQL> select file_name,file_id,tablespace_name from dba_data_files;
FILE_NAME
FILE_ID TABLESPACE_NAME
-------------------------------------------------- ------- ------------------------------
/u01/app/oracle/oradata/ORCL/users01.dbf
7 USERS
/u01/app/oracle/oradata/ORCL/undotbs01.dbf
4 UNDOTBS1
/u01/app/oracle/oradata/ORCL/system01.dbf
1 SYSTEM
/u01/app/oracle/oradata/ORCL/sysaux01.dbf
3 SYSAUX
/oradata/ORCL/ogg01.dbf
13 OGG_TBS
1. sql "alter database datafile 13 offline";
2. restore datafile 13
3. recover datafile 13
4. sql "alter database datafile 13 online";
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME
STATUS
------------------------------ ---------------------------
SYSTEM
ONLINE
SYSAUX
ONLINE
UNDOTBS1
ONLINE
TEMP
ONLINE
USERS
ONLINE
OGG_TBS
ONLINE
6 rows selected.

5.表空间丢失https://www.cndba.cn/hbhe0316/article/4782https://www.cndba.cn/hbhe0316/article/4782

表空间丢失:
1. sql "alter tablespace OGG_TBS offline";//如果文件不存在，则用 sql "alter tablespace users offline immeidate";
2. restore tablespace OGG_TBS;
3. recover tablespace OGG_TBS; //与online redolog file 信息一致
4. sql "alter tablespace OGG_TBS online";

oracle,linux