概述，

学习Oracle手动下线（offline）了一个文件，在进行恢复文件，现记录笔记。

1、创建了测试的表空间、账号，参考：Oracle 11g 创建表空间&授权

账号: fid_test1

表空间：test_data，test_index

2、使用创建的账号，创建测试表.

create table student(id int);

insert into student(id) values(1);
insert into student(id) values(2);
insert into student(id) values(3);

3、查询（dba_data_files）文件的信息，如下:

  1* select file_id, file_name, tablespace_name, status, online_status from dba_data_files where tablespace_name in('TEST_DATA','TEST_INDEX')
SQL> /

   FILE_ID FILE_NAME                                               TABLESPACE_NAME STATUS    ONLINE_
---------- ------------------------------------------------------- --------------- --------- -------
        21 /oradata/GBKDB/datafile/o1_mf_test_dat_jg6ry4kt_.dbf    TEST_DATA       AVAILABLE ONLINE
        22 /oradata/GBKDB/datafile/o1_mf_test_ind_jg6ryloh_.dbf    TEST_INDEX      AVAILABLE ONLINE

SQL>

此时，切换至 fid_test1账号，进行查询数据库，可以正常操作。

SQL> conn fid_test1/testpwd
Connected.
SQL> 
SQL> 
SQL> select * from student;

        ID
----------
         1
         2
         3

SQL>

4、下线数据文件，file_id 为：21、22

SQL> conn /as sysdba
Connected.

SQL> alter database datafile 21 offline;
Database altered.

SQL> alter database datafile 22 offline;
Database altered.

  1* select file_id, file_name, tablespace_name, status, online_status from dba_data_files where tablespace_name in('TEST_DATA','TEST_INDEX')
SQL> /

   FILE_ID FILE_NAME                                               TABLESPACE_NAME STATUS    ONLINE_
---------- ------------------------------------------------------- --------------- --------- -------
        21 /oradata/GBKDB/datafile/o1_mf_test_dat_jg6ry4kt_.dbf    TEST_DATA       AVAILABLE RECOVER
        22 /oradata/GBKDB/datafile/o1_mf_test_ind_jg6ryloh_.dbf    TEST_INDEX      AVAILABLE RECOVER

SQL>

文件已下线，此时在使用 fid_test1 账号查询数据，会报错，如下：

SQL> select * from student;
select * from student
*
ERROR at line 1:
ORA-00376: file 21 cannot be read at this time
ORA-01110: data file 21: '/oradata/GBKDB/datafile/o1_mf_test_dat_jg6ry4kt_.dbf'


SQL>

5、现进行恢复操作。首先登录rman，在执行恢复指令。

[oracle@localhost ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Jul 6 04:42:56 2021

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

connected to target database: GBKDB (DBID=824073160)

RMAN> 
RMAN> recover datafile 21;

Starting recover at 06-JUL-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=35 device type=DISK

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

Finished recover at 06-JUL-21

RMAN>

在使用oracle账号登录到数据库服务器，进行恢复。

[oracle@localhost ~]$ sqlplus / as sysdba
SQL> alter database datafile 21 online;
Database altered.

SQL>

查询文件的状态，file_id为21的文件已经恢复了：

SQL> /

   FILE_ID FILE_NAME                                               TABLESPACE_NAME STATUS    ONLINE_
---------- ------------------------------------------------------- --------------- --------- -------
        21 /oradata/GBKDB/datafile/o1_mf_test_dat_jg6ry4kt_.dbf    TEST_DATA       AVAILABLE ONLINE
        22 /oradata/GBKDB/datafile/o1_mf_test_ind_jg6ryloh_.dbf    TEST_INDEX      AVAILABLE RECOVER

SQL>

如果，上面的步骤没有rman那步操作，直接恢复，是会报错的。比如：file_id为22的文件还没恢复，现没有rman操作，直接恢复，报错如下：

SQL> alter database datafile 22 online;
alter database datafile 22 online
*
ERROR at line 1:
ORA-01113: file 22 needs media recovery
ORA-01110: data file 22: '/oradata/GBKDB/datafile/o1_mf_test_ind_jg6ryloh_.dbf'


SQL>

通过raman操作，再次进行恢复，步骤如下：

-- Rman操作
RMAN> recover datafile 22;

Starting recover at 06-JUL-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK

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

Finished recover at 06-JUL-21

RMAN> 


-- ### 再次恢复操作
SQL> alter database datafile 22 online;

Database altered.

数据均以恢复，再次执行SQL语句查询，正常：

SQL> conn fid_test1/testpwd;
Connected.
SQL> 
SQL> select * from student;

        ID
----------
         1
         2
         3

SQL>

ITPub博客

Oracle 非在线文件恢复

最新文章