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/wwwwwwRecovery Manager: Release 19.0.0.0.0 - Production on Thu Sep 30 16:49:31 2021 Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.connected to recovery catalog databaseRMAN> 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: TAG20210926T110827Piece Name: /u01/app/oracle/product/19.3.0/db/dbs/c-2538411202-20210926-00SPFILE Included: Modification time: 26-SEP-21SPFILE db_unique_name: CXMTDBControl File Included: Ckp SCN: 2361194 Ckp time: 26-SEP-21BS 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: TAG20210929T173959Piece Name: /u01/app/oracle/product/19.3.0/db/dbs/c-2538411202-20210929-00SPFILE Included: Modification time: 29-SEP-21SPFILE db_unique_name: CXMTDBControl File Included: Ckp SCN: 3775038 Ckp time: 29-SEP-21BS 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: TAG20210930T165211Piece Name: /u01/app/oracle/product/19.3.0/db/dbs/030acilr_1_1List of Datafiles in backup set 130File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name---- -- ---- ---------- --------- ----------- ------ ----1 Full 4097975 30-SEP-21 NO /oradata/CXMTDB/system01.dbf2 Full 4097975 30-SEP-21 NO /oradata/CXMTDB/rmants.dbf3 Full 4097975 30-SEP-21 NO /oradata/CXMTDB/sysaux01.dbf4 Full 4097975 30-SEP-21 NO /oradata/CXMTDB/undotbs01.dbf5 Full 4097975 30-SEP-21 NO /oradata/CXMTDB/ogg_tbs.dbf7 Full 4097975 30-SEP-21 NO /oradata/CXMTDB/users01.dbfBS 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: TAG20210930T165219Piece Name: /u01/app/oracle/product/19.3.0/db/dbs/c-2538411202-20210930-00SPFILE Included: Modification time: 30-SEP-21SPFILE db_unique_name: CXMTDBControl 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: TAG20210930T165211Piece Name: /u01/app/oracle/product/19.3.0/db/dbs/030acilr_1_1List of Datafiles in backup set 130File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name---- -- ---- ---------- --------- ----------- ------ ----1 Full 4097975 30-SEP-21 NO /oradata/CXMTDB/system01.dbf2 Full 4097975 30-SEP-21 NO /oradata/CXMTDB/rmants.dbf3 Full 4097975 30-SEP-21 NO /oradata/CXMTDB/sysaux01.dbf4 Full 4097975 30-SEP-21 NO /oradata/CXMTDB/undotbs01.dbf5 Full 4097975 30-SEP-21 NO /oradata/CXMTDB/ogg_tbs.dbf7 Full 4097975 30-SEP-21 NO /oradata/CXMTDB/users01.dbfBS 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: TAG20210930T165703Piece Name: /u01/app/oracle/product/19.3.0/db/dbs/050aciuv_1_1List of Datafiles in backup set 162File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name---- -- ---- ---------- --------- ----------- ------ ----1 0 Incr 4098708 30-SEP-21 NO /oradata/CXMTDB/system01.dbf2 0 Incr 4098708 30-SEP-21 NO /oradata/CXMTDB/rmants.dbf3 0 Incr 4098708 30-SEP-21 NO /oradata/CXMTDB/sysaux01.dbf4 0 Incr 4098708 30-SEP-21 NO /oradata/CXMTDB/undotbs01.dbf5 0 Incr 4098708 30-SEP-21 NO /oradata/CXMTDB/ogg_tbs.dbf7 0 Incr 4098708 30-SEP-21 NO /oradata/CXMTDB/users01.dbfBS 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 CXMTDBList 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.dbfList of Temporary Files ======================= File Size(MB) Tablespace Maxsize(MB) Tempfile Name ---- -------- -------------------- ----------- -------------------- 1 132 TEMP 32767 /oradata/CXMTDB/temp01.dbfRMAN> backup tablespace OGG_TBS;

6.备份控制文件

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

7.单命令和批命令

https://www.cndba.cn/hbhe0316/article/4782 https://www.cndba.cn/hbhe0316/article/4782 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 root30 23 * * 0 /u01/app/oracle/rmanbak/scripts/bakl030 23 * * 0 /u01/app/oracle/rmanbak/scripts/bakl230 23 * * 0 /u01/app/oracle/rmanbak/scripts/bakl230 23 * * 0 /u01/app/oracle/rmanbak/scripts/bakl130 23 * * 0 /u01/app/oracle/rmanbak/scripts/bakl230 23 * * 0 /u01/app/oracle/rmanbak/scripts/bakl230 23 * * 0 /u01/app/oracle/rmanbak/scripts/bakl25: service crond restart6: 附件-------------------bakl0脚本------------------#!/bin/shcd /u01/app/oracle/rmanbak/scriptssu - 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/shcd /u01/app/oracle/rmanbak/scriptssu - 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/shcd /u01/app/oracle/rmanbak/scriptssu - 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.0Copyright (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 startedTotal System Global Area 1073737792 bytesFixed Size 8904768 bytes Variable Size 318767104 bytes Database Buffers 738197504 bytes Redo Buffers 7868416 bytesRMAN> set dbid 1610869203executing command: SET DBIDRMAN> 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=DISKchannel 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:12RMAN> shutdown immediate;Oracle instance shut downRMAN> startupconnected to target database (not started) Oracle instance started database mounted database openedTotal System Global Area 2516581448 bytesFixed 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 sysdbaSQL*Plus: Release 19.0.0.0.0 - Production on Fri Oct 1 11:30:11 2021 Version 19.12.0.0.0Copyright (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.0SQL> 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.bakRMAN> startup nomount;connected to target database (not started) Oracle instance startedTotal System Global Area 2516581448 bytesFixed Size 9141320 bytes Variable Size 1174405120 bytes Database Buffers 1325400064 bytes Redo Buffers 7634944 bytesRMAN> 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=DISKchannel 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:27RMAN> alter database mount;released channel: ORA_DISK_1 Statement processedRMAN> recover database;Starting recover at 2021:10:0111:32:46 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=1216 device type=DISKstarting media recoveryarchived 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:50RMAN> 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.logSQL> 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.bakSQL> 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_TBS1. 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 ONLINE6 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

总结

以上是生活随笔为你收集整理的rman备份与恢复的全部内容，希望文章能够帮你解决所遇到的问题。

如果觉得生活随笔网站内容还不错，欢迎将生活随笔推荐给好友。