数据文件的备份和恢复

离线冷备——拷贝文件

时间点	标记
备份数据文件的操作时间点	A
数据文件损坏或丢失时间点	B
还原后数据时间点	A

备份：

停服务shutdown immediate
拷贝数据!cp <data file path> <backup data file path>
启服务startup

select 'cp -v '||name||' /home/oracle/coldbk/'
name from v$datafile;

恢复：

停服务shutdown immediate
导数据!cp <data file path> <data file path>
启服务startup
验证

在线热备——SQL命令

时间点	标记
备份数据文件的操作时间点	A
数据文件损坏或丢失时间点	B
还原后数据时间点	B

备份：

查看scott用户使用的表空间 select username,default_tablespace from dba_users where username ='SCOTT';
查看表空间对应的表空间文件select file_name,tablespace_name from dba_data_files;
开始在线热备表空间alter tablespace <tablespace_name> begin backup;
拷贝表空间文件到备份目录!cp -v <tablespace_file> /home/oracle/hotbk/
结束在线热备表空间alter tablespace <tablespace_name> end backup;

恢复：

sysdba用户尝试正常启动数据库startup；
将缺失的表空间文件设置为offline状态alter database datafile <FILE#> offline;，打开数据库alter database open;
查看需要恢复的文件编号select * from v$recover_file;
拷贝备份表空间文件到数据库表空间文件位置!cp <backup_tablespace_file> <tablespace_file>
执行恢复命令recover datafile 4;
由于备份的表空间文件已经落后，需要应用日志来补数据，将数据库恢复到最后一个事务提交的点
恢复成功后将数据文件onlinealter database datafile <FILE#> online;
校验数据

在线热备——RMAN工具

时间点	标记
备份数据文件的操作时间点	A
数据文件损坏或丢失时间点	B
还原后数据时间点	B

备份：

查看可备份的schema对象RMAN> report schema;
备份表空间RMAN> copy datafile 4 to '/home/oracle/rmanbk/users01.dbf';
查看备份文件RMAN> list copy of datafile 4;

恢复：

将表空间下线SQL> ALTER TABLESPACE users OFFLINE IMMEDIATE;
还原数据文件RMAN> restore datafile 4;
恢复数据文件RMAN> recover datafile 4;
打开数据库SQL> ALTER TABLESPACE users online;

用户表空间损坏后的恢复实践

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name TESTDB

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    4190     SYSTEM               ***     /alidata/oracle/oradata/testdb/system01.dbf
2    820      SYSAUX               ***     /alidata/oracle/oradata/testdb/sysaux01.dbf
3    325      UNDOTBS1             ***     /alidata/oracle/oradata/testdb/undotbs01.dbf
4    5        USERS                ***     /alidata/oracle/oradata/testdb/users01.dbf
5    100      DATA01               ***     /alidata/oracle/oradata/testdb/data01_01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    254      TEMP                 32767       /alidata/oracle/oradata/testdb/temp01.dbf

RMAN> copy datafile 4 to '/home/oracle/users01.dbf';

Starting backup at 26-OCT-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=215 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/alidata/oracle/oradata/testdb/users01.dbf
output file name=/home/oracle/users01.dbf tag=TAG20201026T152814 RECID=2 STAMP=1054826895
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 26-OCT-20

Starting Control File and SPFILE Autobackup at 26-OCT-20
piece handle=/alidata/oracle/flash_recovery_area/TESTDB/autobackup/2020_10_26/o1_mf_s_1054826896_hsdynkkd_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 26-OCT-20

RMAN> list copy of datafile 4;

List of Datafile Copies
=======================

Key     File S Completion Time Ckp SCN    Ckp Time
------- ---- - --------------- ---------- ---------------
2       4    A 26-OCT-20       164681024  26-OCT-20
        Name: /home/oracle/users01.dbf
        Tag: TAG20201026T152814


# 模拟误操作将用户表空间删除
SYS@testdb>select host_name,version,status from v$instance;

HOST_NAME							 VERSION	   STATUS
---------------------------------------------------------------- ----------------- ------------
NB-flexgw1							 11.2.0.1.0	   OPEN
SYS@testdb>!rm -rf /alidata/oracle/oradata/testdb/users01.dbf

SYS@testdb>select FILE#,name,status from v$datafile;

     FILE#
----------
NAME
----------------------------------------------------------------------------------------------------
STATUS
-------
	 1
/alidata/oracle/oradata/testdb/system01.dbf
SYSTEM

	 2
/alidata/oracle/oradata/testdb/sysaux01.dbf
ONLINE

	 3
/alidata/oracle/oradata/testdb/undotbs01.dbf
ONLINE

	 4
/alidata/oracle/oradata/testdb/users01.dbf
ONLINE

	 5
/alidata/oracle/oradata/testdb/data01_01.dbf
ONLINE


# 登陆到普通用户scott
SYS@testdb>conn scott/tiger;
Connected.
SCOTT@testdb>select * from tab;

TNAME			       TABTYPE	CLUSTERID
------------------------------ ------- ----------
123			       TABLE
BIN$nBsJv4MgoCrgUMgKNQYMIQ==$0 TABLE
BIN$nBsJv4MhoCrgUMgKNQYMIQ==$0 TABLE
BIN$nBsJv4MioCrgUMgKNQYMIQ==$0 TABLE
BIN$sFrr2JVeiX/gUMgKNQZ/Tg==$0 TABLE
DEPT			       TABLE
EMP			       TABLE
PROMOTIONS		       TABLE
T1			       TABLE
TEMP_EMP		       TABLE
TEST02			       TABLE
ZYADMIN 		       TABLE

12 rows selected.

SCOTT@testdb>select * from t1 where rownum < 10;
select * from t1 where rownum < 10
              *
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/alidata/oracle/oradata/testdb/users01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

# 开始恢复数据
SCOTT@testdb>conn / as sysdba
Connected.
# 使用默认的normal方式无法成功offline。
SYS@testdb>ALTER TABLESPACE users OFFLINE;
ALTER TABLESPACE users OFFLINE
*
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/alidata/oracle/oradata/testdb/users01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
# 使用IMMEDIATE方式才能进行offline
SYS@testdb>ALTER TABLESPACE users OFFLINE IMMEDIATE;

Tablespace altered.
# 检查表空间状态和实例状态
SYS@testdb>select host_name,version,status from v$instance;

HOST_NAME							 VERSION	   STATUS
---------------------------------------------------------------- ----------------- ------------
NB-flexgw1							 11.2.0.1.0	   OPEN
SYS@testdb>select FILE#,name,status from v$datafile;

     FILE#
----------
NAME
----------------------------------------------------------------------------------------------------
STATUS
-------
	 1
/alidata/oracle/oradata/testdb/system01.dbf
SYSTEM

	 2
/alidata/oracle/oradata/testdb/sysaux01.dbf
ONLINE

	 3
/alidata/oracle/oradata/testdb/undotbs01.dbf
ONLINE

	 4
/alidata/oracle/oradata/testdb/users01.dbf
RECOVER

	 5
/alidata/oracle/oradata/testdb/data01_01.dbf
ONLINE

# 使用Rman进行恢复

[oracle@NB-flexgw1 ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Oct 26 15:40:48 2020

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TESTDB (DBID=2803324215)

RMAN> restore datafile 4;

Starting restore at 26-OCT-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=82 device type=DISK

channel ORA_DISK_1: restoring datafile 00004
input datafile copy RECID=2 STAMP=1054826895 file name=/home/oracle/users01.dbf
destination for restore of datafile 00004: /alidata/oracle/oradata/testdb/users01.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00004
output file name=/alidata/oracle/oradata/testdb/users01.dbf RECID=0 STAMP=0
Finished restore at 26-OCT-20

RMAN> recover datafile 4;

Starting recover at 26-OCT-20
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:02

Finished recover at 26-OCT-20

# ALTER TABLESPACE users online;
SYS@testdb>select FILE#,name,status from v$datafile;

     FILE#
----------
NAME
----------------------------------------------------------------------------------------------------
STATUS
-------
	 1
/alidata/oracle/oradata/testdb/system01.dbf
SYSTEM

	 2
/alidata/oracle/oradata/testdb/sysaux01.dbf
ONLINE

	 3
/alidata/oracle/oradata/testdb/undotbs01.dbf
ONLINE

	 4
/alidata/oracle/oradata/testdb/users01.dbf
OFFLINE

	 5
/alidata/oracle/oradata/testdb/data01_01.dbf
ONLINE


SYS@testdb>ALTER TABLESPACE users online;

Tablespace altered.

SYS@testdb>select FILE#,name,status from v$datafile;

     FILE#
----------
NAME
----------------------------------------------------------------------------------------------------
STATUS
-------
	 1
/alidata/oracle/oradata/testdb/system01.dbf
SYSTEM

	 2
/alidata/oracle/oradata/testdb/sysaux01.dbf
ONLINE

	 3
/alidata/oracle/oradata/testdb/undotbs01.dbf
ONLINE

	 4
/alidata/oracle/oradata/testdb/users01.dbf
ONLINE

	 5
/alidata/oracle/oradata/testdb/data01_01.dbf
ONLIN

ToBeRoot

冷备总结-数据文件的备份和恢复

数据文件的备份和恢复

离线冷备——拷贝文件

在线热备——SQL命令

在线热备——RMAN工具

用户表空间损坏后的恢复实践