在归档模式下恢复非关键数据文件,只要有有效的备份文件,恢复起来还是
不太复杂的。以下是测试的过程:
----归档模式下丢失非关键数据文件并恢复:
--准备信息:
scott@PROD>select table_name,tablespace_name
2 from user_tables;
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
DEPT USERS
BONUS USERS
SALGRADE USERS
MYLOADER USERS
HISLOADER USERS
EMP USERS
T1 USERS
DEPT_CHANGE USERS
8 rows selected.
--数据文件信息:
sys@PROD>select file_name,file_id,tablespace_name
2 from dba_data_files;
FILE_NAME FILE_ID TABLESPACE_NAME
-------------------------------------------------- ---------- ------------------------------
/u01/app/oracle/oradata/PROD/ts_ctl01.dbf 7 TS_CTL
/u01/app/oracle/oradata/PROD/ts_xxf_01.dbf 6 TS_XXF
/u01/app/oracle/oradata/PROD/example01.dbf 5 EXAMPLE
/u01/app/oracle/oradata/PROD/users01.dbf 4 USERS
/u01/app/oracle/oradata/PROD/undotbs01.dbf 3 UNDOTBS1
/u01/app/oracle/oradata/PROD/sysaux01.dbf 2 SYSAUX
/u01/app/oracle/oradata/PROD/system01.dbf 1 SYSTEM
/u01/app/oracle/oradata/myspace_01.dbf 8 MYSPACE
8 rows selected.
---查看数据库的归档模式:
sys@PROD>archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 1
Current log sequence 1
---创建测试表:
scott@PROD>create table test(x int,y int);
Table created.
---通过rman备份users表空间:
[oracle@enmo ~]$ rman target/
Recovery Manager: Release 11.2.0.4.0 - Production on Tue Nov 22 13:57:41 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD (DBID=338469376)
RMAN> backup tablespace users;
Starting backup at 22-NOV-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=38 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00004 name=/u01/app/oracle/oradata/PROD/users01.dbf
channel ORA_DISK_1: starting piece 1 at 22-NOV-16
channel ORA_DISK_1: finished piece 1 at 22-NOV-16
piece handle=/u01/app/backup/db_0prlibih_1_1.rmn tag=TAG20161122T140401 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 22-NOV-16
Starting Control File and SPFILE Autobackup at 22-NOV-16
piece handle=/u01/app/FRA/PROD/autobackup/2016_11_22/o1_mf_s_928591444_d37qyo2v_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 22-NOV-16
#备份完成。
---往测试表中插入测试数据:
scott@PROD>insert into test values(1,10);
1 row created.
scott@PROD>insert into test values(2,20);
1 row created.
scott@PROD>commit;
Commit complete.
scott@PROD>select * from test;
X Y
---------- ----------
1 10
2 20
#共插入两条测试数据。
---删除users表空间的数据文件:
sys@PROD>!rm /u01/app/oracle/oradata/PROD/users01.dbf
sys@PROD>!ls /u01/app/oracle/oradata/PROD/users01.dbf
ls: /u01/app/oracle/oradata/PROD/users01.dbf: No such file
#已经成功删除users表空间数据文件:
---尝试再往表test中插入两条测试数据:
scott@PROD>insert into test values(3,30);
insert into test values(3,30)
*
ERROR at line 1:
ORA-03135: connection lost contact
Process ID: 10753
Session ID: 18 Serial number: 23
#数据文件被删除,不能完成插入动作。
scott@PROD>conn / as sysdba
Connected.
sys@PROD>conn scott/tiger
Connected.
scott@PROD>
scott@PROD>
scott@PROD>show user
USER is "SCOTT"
scott@PROD>
scott@PROD>insert into test values(3,30);
insert into test values(3,30)
*
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/u01/app/oracle/oradata/PROD/users01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
#发现报错没有users数据文件:
---致性关库并尝试重新打开数据库:
sys@PROD>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@PROD>startup
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 507513936 bytes
Database Buffers 322961408 bytes
Redo Buffers 2371584 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/u01/app/oracle/oradata/PROD/users01.dbf'
---通过rman备份重载数据文件4并恢复数据:
--目前数据库的状态:
sys@PROD>select status from v$instance;
STATUS
------------
MOUNTED
#数据库已经处于mount状态。
[oracle@enmo ~]$ rman target/
Recovery Manager: Release 11.2.0.4.0 - Production on Tue Nov 22 14:21:36 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD (DBID=338469376, not open)
RMAN> restore tablespace users;
Starting restore at 22-NOV-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 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 00004 to /u01/app/oracle/oradata/PROD/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/backup/db_0prlibih_1_1.rmn
channel ORA_DISK_1: piece handle=/u01/app/backup/db_0prlibih_1_1.rmn tag=TAG20161122T140401
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 22-NOV-16
RMAN> recover tablespace users;
Starting recover at 22-NOV-16
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 22-NOV-16
---尝试打开数据库:
RMAN> alter database open;
database opened
sys@PROD>select status from v$instance;
STATUS
------------
OPEN
#恢复已经完成,并成功打开数据库:
---尝试连接到Scott用户并查看数据丢失情况:
scott@PROD>conn / as sysdba
Connected.
sys@PROD>conn scott/tiger
Connected.
scott@PROD>
scott@PROD>select * from test;
X Y
---------- ----------
1 10
2 20
#数据没有丢失。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31392094/viewspace-2128968/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/31392094/viewspace-2128968/