热备份 所有数据文件损坏的恢复

1.创建表空间

SQL> create tablespace app1 datafile '/u01/app/oracle/oradata/demo1/app1_01.dbf' size 100M extent management local uniform segment space management auto;

 

2.创建用户user1,默认表空间为app1

 

3.用user1创建表tt,并插入数据

SQL> insert into tt values(1,'yjg');

 

4.关闭数据库，冷备份

 

5.用user1用户再次插入数据

SQL> insert into tt values(1,'zh');

 

6.热备份表空间app1

SQL> alter tablespace app1 begin backup;

Tablespace altered.

SQL> !
[oracle@asm ~]$ cp /u01/app/oracle/oradata/demo1/app1_01.dbf /u01/ubackup/hot/
[oracle@asm ~]$ exit
exit

SQL> alter tablespace app1 end backup;

Tablespace altered.

 

7.用户user1再次插入数据

SQL> insert into tt values(2,'zmh');
SQL> commit;

 

8.切换日志

SQL> alter system switch logfile;

 

9.删除所有dbf文件

rm -f *.dbf

10.关闭数据库

SQL> shutdown abort

 

11.从冷备份中拷贝

cp /u01/ubackup/cold/*.dbf .

 

12.启动数据库到mount状态

startup mount

 

13.查看要恢复的数据文件

SQL> desc v$recover_file;

 

14.恢复数据库

SQL> set autorecovery off;

SQL> recover datafile 1;

SQL> recover database;

SQL> select * from v$recover_file;

no rows selected

15.启动数据库到open状态

SQL> alter database open;

 

16.验证

SQL> conn user1/user1
Connected.
SQL> select * from tt;