Oracle备份恢复案例1-使用冷备份数据文件实现恢复

发表于 | 分类于

场景: 数据库关闭时的恢复(system,sysaux,undo,whole database):user表空间为例(冷备)

step 1: 查看有哪些数据文件

1
2
3
4
5
6
7
8
select * from v$dbfile;
     FILE NAME
---------- ------------------------------------------------------------
         4 /home/oracle/app/oracle/oradata/mmpdb3/users01.dbf
         3 /home/oracle/app/oracle/oradata/mmpdb3/undotbs01.dbf
         2 /home/oracle/app/oracle/oradata/mmpdb3/sysaux01.dbf
         1 /home/oracle/app/oracle/oradata/mmpdb3/system01.dbf
         5 /home/oracle/app/oracle/oradata/mmpdb3/tab01.dbf

step 2: 冷备整个数据文件

1
2
3
4
5
6
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
[root@hzvscmdb mmpdb3]# cp -a -r /home/oracle/app/oracle/oradata/mmpdb3/ /home/oracle/dbbak/cold/

step 3: 删除users01数据文件，模拟发送事故

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
SQL> startup;
ORACLE instance started.
Total System Global Area 6664212480 bytes
Fixed Size                  2239072 bytes
Variable Size            3456107936 bytes
Database Buffers         3187671040 bytes
Redo Buffers               18194432 bytes
Database mounted.
Database opened.
SQL> insert into mp.obj(OBJECT_ID, OBJECT_NAME) values(88888, 'test888');
1 row created.
SQL> commit;
Commit complete.
SQL> select count(*) from mp.obj;
  COUNT(*)
----------
     72395
SQL> alter system switch logfile;
System altered.
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup;
SQL> startup;
ORACLE instance started.
Total System Global Area 6664212480 bytes
Fixed Size                  2239072 bytes
Variable Size            3456107936 bytes
Database Buffers         3187671040 bytes
Redo Buffers               18194432 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/home/oracle/app/oracle/oradata/mmpdb3/users01.dbf'

step 4: copy冷备份的users01.dbf，并采用介质恢复

1
2
3
4
5
6
7
8
9
10
11
12
13
14
[root@hzvscmdb mmpdb3]# cp -a /home/oracle/dbbak/cold/mmpdb3/users01.dbf ./
SQL> recover datafile 4;
Media recovery complete.
SQL> alter database open;
Database altered.
SQL> select count(*) from mp.obj;
  COUNT(*)
----------
     72395