rman单个数据文件丢失与恢复
一,用的是之间的备份
(1)切换用户
SQL> conn hai/hai
Connected.
(2)复制表
SQL> create table feng3 as select * from hai.feng;
Table created.
SQL> select * from feng3;
ID NAME
---------- ------------------------------------------------------------
2 haifeng
3 haifeng2
4 haifeng3
5 haifeng4
6 haifeng5
7 haifeng5
8 haifeng6
9 haifeng8
10 haifeng8
9 rows selected.
(3)切换日志
SQL> conn / as sysdba
Connected.
SQL> alter system switch logfile;
System altered.
SQL> alter system checkpoint;
System altered.
(4)关闭数据库
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> exit
(5) 删除数据文件
Disconnected from Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
[oracle@oracle1 ~]$ cd /oracle/app/oradata/
[oracle@oracle1 shpog1]$ rm -rf hai01.dbf
[oracle@oracle1 shpog1]$
[oracle@oracle1 shpog1]$
[oracle@oracle1 shpog1]$ ll
total 9817148
-rw-r----- 1 oracle oinstall 9748480 Apr 2 23:13 control01.ctl
-rw-r----- 1 oracle oinstall 9748480 Apr 2 23:13 control02.ctl
-rw-r----- 1 oracle oinstall 104865792 Apr 2 23:13 example01.dbf
-rw-r----- 1 oracle oinstall 52429312 Apr 2 20:53 redo01.log
-rw-r----- 1 oracle oinstall 52429312 Apr 2 20:53 redo02.log
-rw-r----- 1 oracle oinstall 52429312 Apr 2 23:12 redo03.log
-rw-r----- 1 oracle oinstall 52429312 Apr 2 23:13 redo04.log
-rw-r----- 1 oracle oinstall 52429312 Apr 2 20:53 redo05.log
-rw-r----- 1 oracle oinstall 5368717312 Apr 2 23:13 rman_tbs.dbf
-rw-r----- 1 oracle oinstall 1073750016 Apr 2 23:13 sysaux01.dbf
-rw-r----- 1 oracle oinstall 1073750016 Apr 2 23:13 system01.dbf
-rw-r----- 1 oracle oinstall 40902656 Apr 2 15:35 temp01.dbf
-rw-r----- 1 oracle oinstall 1073750016 Apr 2 23:13 undotbs01.dbf
-rw-r----- 1 oracle oinstall 1073750016 Apr 2 23:13 users01.dbf
(6)启动数据库报错
[oracle@oracle1 shpog1]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sun Apr 2 23:15:01 2017
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup;
ORACLE instance started.
Total System Global Area 1586708480 bytes
Fixed Size 2213736 bytes
Variable Size 1191184536 bytes
Database Buffers 385875968 bytes
Redo Buffers 7434240 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 8 - see DBWR trace file
ORA-01110: data file 8: '/oracle/app/oradata/shpog1/hai01.dbf'
SQL> exit
Disconnected from Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
二 ,
rman进行恢复
[oracle@oracle1 shpog1]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Sun Apr 2 23:16:34 2017
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: SHPOG1 (DBID=799280029, not open)
(1)restore 从上次备份的表空间里恢复
RMAN> restore datafile 8;
Starting restore at 02-APR-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=572 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00008 to /oracle/app/oradata/shpog1/hai01.dbf
channel ORA_DISK_1: reading from backup piece /oracle.bacup/tablespace/0gs0mvs0.bak
channel ORA_DISK_1: piece handle=/oracle.bacup/tablespace/0gs0mvs0.bak tag=TAG20170402T201351 #从上次备份的表空间里恢复
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 02-APR-17
(2)recover
Recovery Manager complete.
[oracle@oracle1 shpog1]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sun Apr 2 23:17:39 2017
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
(3)打开数据库报错 要recover
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 8 needs media recovery
ORA-01110: data file 8: '/oracle/app/oradata/shpog1/hai01.dbf'
SQL> recover datafile 8;
Media recovery complete.
SQL> alter database open;
Database altered.
SQL> conn hai/hai
Connected.
SQL> select * from hai.feng3;
ID NAME
---------- ------------------------------------------------------------
2 haifeng
3 haifeng2
4 haifeng3
5 haifeng4
6 haifeng5
7 haifeng5
8 haifeng6
9 haifeng8
10 haifeng8
9 rows selected.
SQL> exit
Disconnected from Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
[oracle@oracle1 shpog1]$ ll
total 9919560
-rw-r----- 1 oracle oinstall 9748480 Apr 2 23:19 control01.ctl
-rw-r----- 1 oracle oinstall 9748480 Apr 2 23:19 control02.ctl
-rw-r----- 1 oracle oinstall 104865792 Apr 2 23:18 example01.dbf
-rw-r----- 1 oracle oinstall 104865792 Apr 2 23:18 hai01.dbf
-rw-r----- 1 oracle oinstall 52429312 Apr 2 23:18 redo01.log
-rw-r----- 1 oracle oinstall 52429312 Apr 2 23:18 redo02.log
-rw-r----- 1 oracle oinstall 52429312 Apr 2 23:18 redo03.log
-rw-r----- 1 oracle oinstall 52429312 Apr 2 23:19 redo04.log
-rw-r----- 1 oracle oinstall 52429312 Apr 2 23:18 redo05.log
-rw-r----- 1 oracle oinstall 5368717312 Apr 2 23:18 rman_tbs.dbf
-rw-r----- 1 oracle oinstall 1073750016 Apr 2 23:18 sysaux01.dbf
-rw-r----- 1 oracle oinstall 1073750016 Apr 2 23:18 system01.dbf
-rw-r----- 1 oracle oinstall 40902656 Apr 2 15:35 temp01.dbf
-rw-r----- 1 oracle oinstall 1073750016 Apr 2 23:18 undotbs01.dbf
-rw-r----- 1 oracle oinstall 1073750016 Apr 2 23:18 users01.dbf
[oracle@oracle1 shpog1]$
一,用的是之间的备份
(1)切换用户
SQL> conn hai/hai
Connected.
(2)复制表
SQL> create table feng3 as select * from hai.feng;
Table created.
SQL> select * from feng3;
ID NAME
---------- ------------------------------------------------------------
2 haifeng
3 haifeng2
4 haifeng3
5 haifeng4
6 haifeng5
7 haifeng5
8 haifeng6
9 haifeng8
10 haifeng8
9 rows selected.
(3)切换日志
SQL> conn / as sysdba
Connected.
SQL> alter system switch logfile;
System altered.
SQL> alter system checkpoint;
System altered.
(4)关闭数据库
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> exit
(5) 删除数据文件
Disconnected from Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
[oracle@oracle1 ~]$ cd /oracle/app/oradata/
[oracle@oracle1 shpog1]$ rm -rf hai01.dbf
[oracle@oracle1 shpog1]$
[oracle@oracle1 shpog1]$
[oracle@oracle1 shpog1]$ ll
total 9817148
-rw-r----- 1 oracle oinstall 9748480 Apr 2 23:13 control01.ctl
-rw-r----- 1 oracle oinstall 9748480 Apr 2 23:13 control02.ctl
-rw-r----- 1 oracle oinstall 104865792 Apr 2 23:13 example01.dbf
-rw-r----- 1 oracle oinstall 52429312 Apr 2 20:53 redo01.log
-rw-r----- 1 oracle oinstall 52429312 Apr 2 20:53 redo02.log
-rw-r----- 1 oracle oinstall 52429312 Apr 2 23:12 redo03.log
-rw-r----- 1 oracle oinstall 52429312 Apr 2 23:13 redo04.log
-rw-r----- 1 oracle oinstall 52429312 Apr 2 20:53 redo05.log
-rw-r----- 1 oracle oinstall 5368717312 Apr 2 23:13 rman_tbs.dbf
-rw-r----- 1 oracle oinstall 1073750016 Apr 2 23:13 sysaux01.dbf
-rw-r----- 1 oracle oinstall 1073750016 Apr 2 23:13 system01.dbf
-rw-r----- 1 oracle oinstall 40902656 Apr 2 15:35 temp01.dbf
-rw-r----- 1 oracle oinstall 1073750016 Apr 2 23:13 undotbs01.dbf
-rw-r----- 1 oracle oinstall 1073750016 Apr 2 23:13 users01.dbf
(6)启动数据库报错
[oracle@oracle1 shpog1]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sun Apr 2 23:15:01 2017
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup;
ORACLE instance started.
Total System Global Area 1586708480 bytes
Fixed Size 2213736 bytes
Variable Size 1191184536 bytes
Database Buffers 385875968 bytes
Redo Buffers 7434240 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 8 - see DBWR trace file
ORA-01110: data file 8: '/oracle/app/oradata/shpog1/hai01.dbf'
SQL> exit
Disconnected from Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
二 ,
rman进行恢复
[oracle@oracle1 shpog1]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Sun Apr 2 23:16:34 2017
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: SHPOG1 (DBID=799280029, not open)
(1)restore 从上次备份的表空间里恢复
RMAN> restore datafile 8;
Starting restore at 02-APR-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=572 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00008 to /oracle/app/oradata/shpog1/hai01.dbf
channel ORA_DISK_1: reading from backup piece /oracle.bacup/tablespace/0gs0mvs0.bak
channel ORA_DISK_1: piece handle=/oracle.bacup/tablespace/0gs0mvs0.bak tag=TAG20170402T201351 #从上次备份的表空间里恢复
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 02-APR-17
(2)recover
Recovery Manager complete.
[oracle@oracle1 shpog1]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sun Apr 2 23:17:39 2017
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
(3)打开数据库报错 要recover
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 8 needs media recovery
ORA-01110: data file 8: '/oracle/app/oradata/shpog1/hai01.dbf'
SQL> recover datafile 8;
Media recovery complete.
SQL> alter database open;
Database altered.
SQL> conn hai/hai
Connected.
SQL> select * from hai.feng3;
ID NAME
---------- ------------------------------------------------------------
2 haifeng
3 haifeng2
4 haifeng3
5 haifeng4
6 haifeng5
7 haifeng5
8 haifeng6
9 haifeng8
10 haifeng8
9 rows selected.
SQL> exit
Disconnected from Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
[oracle@oracle1 shpog1]$ ll
total 9919560
-rw-r----- 1 oracle oinstall 9748480 Apr 2 23:19 control01.ctl
-rw-r----- 1 oracle oinstall 9748480 Apr 2 23:19 control02.ctl
-rw-r----- 1 oracle oinstall 104865792 Apr 2 23:18 example01.dbf
-rw-r----- 1 oracle oinstall 104865792 Apr 2 23:18 hai01.dbf
-rw-r----- 1 oracle oinstall 52429312 Apr 2 23:18 redo01.log
-rw-r----- 1 oracle oinstall 52429312 Apr 2 23:18 redo02.log
-rw-r----- 1 oracle oinstall 52429312 Apr 2 23:18 redo03.log
-rw-r----- 1 oracle oinstall 52429312 Apr 2 23:19 redo04.log
-rw-r----- 1 oracle oinstall 52429312 Apr 2 23:18 redo05.log
-rw-r----- 1 oracle oinstall 5368717312 Apr 2 23:18 rman_tbs.dbf
-rw-r----- 1 oracle oinstall 1073750016 Apr 2 23:18 sysaux01.dbf
-rw-r----- 1 oracle oinstall 1073750016 Apr 2 23:18 system01.dbf
-rw-r----- 1 oracle oinstall 40902656 Apr 2 15:35 temp01.dbf
-rw-r----- 1 oracle oinstall 1073750016 Apr 2 23:18 undotbs01.dbf
-rw-r----- 1 oracle oinstall 1073750016 Apr 2 23:18 users01.dbf
[oracle@oracle1 shpog1]$