4、数据文件的备份与恢复-阿里云开发者社区

1、概念

备份数据文件可以使用copy和backup命令，两者的区别在于：

copy命令备份时，备份数据文件中的所有数据块（包括空白块），而

backup命令，则备份高水位以下的块（忽略空白块）。

使用copy命令备份数据文件

输入report schema获取目标数据库的模式结构;

RMAN> report schema;

Report of database schema for database with db_unique_name ORCL

List of Permanent Datafiles

===========================

File Size(MB) Tablespace RB segs Datafile Name

---- -------- -------------------- ------- ------------------------

1 930 SYSTEM YES /app/oracle/oradata/ORCL/system01.dbf

2 512 TEST NO /app/oracle/oradata/ORCL/test01.DBF

3 1170 SYSAUX NO /app/oracle/oradata/ORCL/sysaux01.dbf

4 335 UNDOTBS1 YES /app/oracle/oradata/ORCL/undotbs01.dbf

5 2424 WMS NO /app/oracle/oradata/ORCL/wms01.DBF

7 5 USERS NO /app/oracle/oradata/ORCL/users01.dbf

List of Temporary Files

=======================

File Size(MB) Tablespace Maxsize(MB) Tempfile Name

---- -------- -------------------- ----------- --------------------

1 32 TEMP 32767 /app/oracle/oradata/ORCL/temp01.dbf

2.1 备份数据文件

使用copy datafile 备份数据文件

RMAN> copy datafile 1 to '/app/rmanbak/system01.dbf';

备份数据文件完成后，我们查询备份列表

RMAN> list copy of datafile 1;

2.2 恢复数据文件

我们模拟给数据库插入1条数据，并删除1号数据文件

SQL> insert into t1 values(3,'Jerry');

SQL> commit;

SQL> select file_id,file_name from dba_data_files order by 1;

FILE_ID FILE_NAME

---------- --------------------------------------------------------------------------------

1 /app/oracle/oradata/ORCL/system01.dbf

2 /app/oracle/oradata/ORCL/test01.DBF

3 /app/oracle/oradata/ORCL/sysaux01.dbf

4 /app/oracle/oradata/ORCL/undotbs01.dbf

5 /app/oracle/oradata/ORCL/wms01.DBF

7 /app/oracle/oradata/ORCL/users01.dbf

6 rows selected

SQL> host rm /app/oracle/oradata/ORCL/system01.dbf

由于缺少数据文件，数据重启只能启动到mount状态。

SQL> startup force;

ORACLE instance started.

Total System Global Area 8002730448 bytes

Fixed Size 8915408 bytes

Variable Size 1308622848 bytes

Database Buffers 6677331968 bytes

Redo Buffers 7860224 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 1 - see DBWR trace file

ORA-01110: data file 1: '/app/oracle/oradata/ORCL/system01.dbf'

SQL> select status from v$instance;

STATUS

------------

MOUNTED

使用RMAN还原数据文件

RMAN> restore datafile 1;

2.3 还原数据文件

数据库恢复完成，我们还原数据文件

RMAN> recover datafile 1;

2.4 验证数据库

我们打开数据库，查询数据是否正确

RMAN> alter database open;

SQL> select * from t1;

ID NAME

---------- --------------------------------------------------

1 Tome

2 Jerry

3 Jerry

3、使用backup命令备份数据文件

输入report schema获取目标数据库的模式结构

RMAN> report schema;

3.1 备份数据文件SYSTEM

RMAN> backup datafile 1 format '/app/rmanbak/DATAFILE_SYSTEM_%d_%s_%p_%T';

查看备份信息

RMAN> list backup of datafile 1;

3.2 备份时限制备份片的大小

RMAN> RUN{

CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE 100M;

BACKUP datafile 1 FORMAT '/app/rmanbak/DATAFILE_SYSTEM_%d_%s_%p_%T';

CONFIGURE CHANNEL DEVICE TYPE DISK clear;

};

查看备份信息：注意观察247

RMAN> list backup of datafile 1;

List of Backup Sets

===================

BS Key Type LV Size Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ -------------------

244 Full 815.38M DISK 00:00:01 2023-02-14 11:33:18

BP Key: 269 Status: AVAILABLE Compressed: NO Tag: TAG20230214T113317

Piece Name: /app/rmanbak/DATAFILE_SYSTEM_ORCL_312_1_20230214

List of Datafiles in backup set 244

File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name

---- -- ---- ---------- ------------------- ----------- ------ ----

1 Full 10309789 2023-02-14 11:33:17 NO /app/oracle/oradata/ORCL/system01.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ -------------------

247 Full 815.63M DISK 00:00:08 2023-02-14 13:00:10

List of Datafiles in backup set 247

File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name

---- -- ---- ---------- ------------------- ----------- ------ ----

1 Full 10312942 2023-02-14 13:00:02 NO /app/oracle/oradata/ORCL/system01.dbf

Backup Set Copy #1 of backup set 247

Device Type Elapsed Time Completion Time Compressed Tag

----------- ------------ ------------------- ---------- ---

DISK 00:00:08 2023-02-14 13:00:10 NO TAG20230214T130002

List of Backup Pieces for backup set 247 Copy #1

BP Key Pc# Status Piece Name

------- --- ----------- ----------

1723 1 AVAILABLE /app/rmanbak/DATAFILE_SYSTEM_ORCL_315_1_20230214

1724 2 AVAILABLE /app/rmanbak/DATAFILE_SYSTEM_ORCL_315_2_20230214

1725 3 AVAILABLE /app/rmanbak/DATAFILE_SYSTEM_ORCL_315_3_20230214

1726 4 AVAILABLE /app/rmanbak/DATAFILE_SYSTEM_ORCL_315_4_20230214

1727 5 AVAILABLE /app/rmanbak/DATAFILE_SYSTEM_ORCL_315_5_20230214

1728 6 AVAILABLE /app/rmanbak/DATAFILE_SYSTEM_ORCL_315_6_20230214

1729 7 AVAILABLE /app/rmanbak/DATAFILE_SYSTEM_ORCL_315_7_20230214

1730 8 AVAILABLE /app/rmanbak/DATAFILE_SYSTEM_ORCL_315_8_20230214

1731 9 AVAILABLE /app/rmanbak/DATAFILE_SYSTEM_ORCL_315_9_20230214

3.3 备份时压缩备份集

RMAN> backup as compressed backupset datafile 1 format='/app/rmanbak/DATAFILE_SYSTEM_%d_%s_%p_%T.dbf';

查看备份信息：注意观察249