关于alter database datafile offline和alter database datafile offline drop 的区别

转载

mb5fe55c8710ded 2019-01-30 16:56:00

文章标签 sql oracle hive 数据库数据文件 文章分类 Java 后端开发

-----------------------2015-07-16---------------------------------------------

发现记忆力确实不如以前了，一些东西总是记不住，不出三天便忘得一干二净。

关于alter database datafile offline和alter database datafile offline drop

之前遇到了几次，也专门看了下，不久又忘了啥区别。

只好专门列个文档，再次记录一番：

对于归档模式:

alter database datafile 'file_name' offline 和 offline drop 没有什么区别。

对于非归档模式：

如果是非归档模式，只能是offline drop.

如果对于非归档模式下，进行offline将会报错：

SQL> alter database datafile 'c:\test.dbf' offline;

alter database datafile 'c:\test.dbf' offline

第 1 行出现错误:

ORA-01145: 除非启用了介质恢复, 否则不允许立即脱机

SQL> alter database datafile 'c:\test.dbf' offline drop;

数据库已更改。

SQL>

----查看下数据文件的状态

SQL> select file#,status from v$datafile;

FILE# STATUS

---------- -------

1 SYSTEM

2 ONLINE

3 ONLINE

4 ONLINE

5 ONLINE

6 RECOVER

已选择6行。

文件6就是我刚刚offline drop的数据文件，现在状态是recover，说明我online之前需要进行恢复么？

实验来说明：

SQL> alter database datafile 6 online;

alter database datafile 6 online

第 1 行出现错误:

ORA-01113: 文件 6 需要介质恢复

ORA-01110: 数据文件 6: 'C:\TEST.DBF'

果然需要先进行恢复：

SQL> recover datafile 6;

完成介质恢复。

SQL> alter database datafile 6 online;

数据库已更改。

这样看是不是很完美，那么问题来了，现在之所以能够恢复过来是因为当前的redo log仍然存在，如果redo log切换后且木有归档日志存在，

那么这个数据文件则无法online

-----------------------------------------------------

再次实验：

---查看当前日志文件：日志组3

SQL> select group#,status from v$log;

GROUP# STATUS

---------- ----------------

1 INACTIVE

2 INACTIVE

3 CURRENT

----再次offline 然后再切换日志，接着进行recover操作

SQL> alter database datafile 6 offline drop;

数据库已更改。

SQL> alter system switch logfile;

系统已更改。

SQL> alter system switch logfile;

系统已更改。

SQL> alter system switch logfile;

系统已更改。

SQL>

---切换了三次日志后，再次进行介质恢复:

SQL> recover datafile 6;

ORA-00279: 更改 1294577 (在 07/16/2015 15:31:12 生成) 对于线程 1 是必需的

ORA-00289: 建议: C:\ORACLE\ARCHIVE\ARC00078_0880122604.001

ORA-00280: 更改 1294577 (用于线程 1) 在序列 #78 中

指定日志: {<RET>=suggested | filename | AUTO | CANCEL}

--看看，完蛋了吧，对于这种没有归档日志的情况，只能靠之前的冷备份来解决了，数据将面临丢失。

---------------------------------------------------------------------------------------------------

下面来看下在归档模式下，offline和offline drop的区别

SQL> startup mount

ORACLE 例程已经启动。

Total System Global Area 585879552 bytes

Fixed Size 1348844 bytes

Variable Size 218106644 bytes

Database Buffers 360710144 bytes

Redo Buffers 5713920 bytes

数据库装载完毕。

SQL> alter database archivelog;

数据库已更改。

SQL> alter database open;

数据库已更改。

SQL>

----创建个表空间

SQL> create tablespace test datafile 'c:\test.dbf' size 2m;

表空间已创建。

---offline

SQL> alter database datafile 6 offline;

数据库已更改。

SQL> select file#,status from v$datafile;

FILE# STATUS

---------- -------

1 SYSTEM

2 ONLINE

3 ONLINE

4 ONLINE

5 ONLINE

6 RECOVER

已选择6行。

SQL> recover datafile 6;

完成介质恢复。

SQL> alter database datafile 6 online;

数据库已更改。

---offline drop

SQL> alter database datafile 6 offline drop;

数据库已更改。

SQL> select file#,status from v$datafile;

FILE# STATUS

---------- -------

1 SYSTEM

2 ONLINE

3 ONLINE

4 ONLINE

5 ONLINE

6 RECOVER

已选择6行。

SQL> recover datafile 6;

完成介质恢复。

SQL> alter database datafile 6 online;

数据库已更改。

----------------------------------------------------------------------------------------------------------------------------------------------

可以看出，在归档模式下面，offline和offline drop是没啥区别的，在offline后都需要进行recover才能online。

----------------------------------------------------------------------------------------------------------------------------------------------

---测试下offline后，多次切换归档日志，看看recover命令是否能够自动读取归档日志？

SQL> alter database datafile 6 offline;

数据库已更改。

SQL> alter system switch logfile;

系统已更改。

SQL> alter system switch logfile;

系统已更改。

SQL> alter system switch logfile;

系统已更改。

----日志切换三次后，进行recover

SQL> recover datafile 6;

ORA-00279: 更改 1295761 (在 07/16/2015 15:44:40 生成) 对于线程 1 是必需的

ORA-00289: 建议: C:\ORACLE\ARCHIVE\ARC00081_0880122604.001

ORA-00280: 更改 1295761 (用于线程 1) 在序列 #81 中

指定日志: {<RET>=suggested | filename | AUTO | CANCEL}

已应用的日志。

完成介质恢复。

SQL>

SQL> alter database datafile 6 online;

数据库已更改。

oracle还是可以自动找到归档日志进行recover。

---------------------

作者：killvoon

=================================================================================================

如下自我测试：：

[root@prod ~]# su - oracle

[oracle@prod ~]$

[oracle@prod ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Oct 23 03:42:01 2018

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter create

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

create_bitmap_area_size integer 8388608

create_stored_outlines string

db_create_file_dest string

db_create_online_log_dest_1 string

db_create_online_log_dest_2 string

db_create_online_log_dest_3 string

db_create_online_log_dest_4 string

db_create_online_log_dest_5 string

SQL> alter system set db_create_file_dest='/data/datafile/prod';

System altered.

非归档模式下进行测试：

SQL> archive log list ;

Database log mode No Archive Mode

Automatic archival Disabled

Archive destination /u01/app/oracle/product/11.2.0/db_1/dbs/arch

Oldest online log sequence 7

Current log sequence 9

SQL>

创建测试表空间：

SQL> create tablespace test1 datafile size 10m ;

Tablespace created.

查看创建的文件

SQL>

SQL> select file_name from dba_data_files ;

FILE_NAME

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

/data/datafile/prod/users01.dbf

/data/datafile/prod/undotbs01.dbf

/data/datafile/prod/sysaux01.dbf

/data/datafile/prod/system01.dbf

/data/datafile/prod/PROD/datafile/o1_mf_test1_fwwb2zh1_.dbf

在非归档模式下只能 offine drop ，否则会报错，如下：

SQL> alter database datafile '/data/datafile/prod/PROD/datafile/o1_mf_test1_fwwb2zh1_.dbf' offline ;

alter database datafile '/data/datafile/prod/PROD/datafile/o1_mf_test1_fwwb2zh1_.dbf' offline

ERROR at line 1:

ORA-01145: offline immediate disallowed unless media recovery enabled

数据库只有在归档模式下才能够直接对数据文件进行offline

SQL> alter database datafile '/data/datafile/prod/PROD/datafile/o1_mf_test1_fwwb2zh1_.dbf' offline drop;

Database altered.

再次查看状态：

select file_name ,ONLINE_STATUS from dba_data_files

FILE_NAME ONLINE_

-------------------------------------------------------------------------------- -------

/data/datafile/prod/users01.dbf ONLINE

/data/datafile/prod/undotbs01.dbf ONLINE

/data/datafile/prod/sysaux01.dbf ONLINE

/data/datafile/prod/system01.dbf SYSTEM

/data/datafile/prod/PROD/datafile/o1_mf_test1_fwwb2zh1_.dbf RECOVER

SQL>

SQL> 1

1* select file_name ,ONLINE_STATUS from dba_data_files

SQL> alter database datafile '/data/datafile/prod/PROD/datafile/o1_mf_test1_fwwb2zh1_.dbf' online ;

alter database datafile '/data/datafile/prod/PROD/datafile/o1_mf_test1_fwwb2zh1_.dbf' online

ERROR at line 1:

ORA-01113: file 5 needs media recovery

ORA-01110: data file 5: '/data/datafile/prod/PROD/datafile/o1_mf_test1_fwwb2zh1_.dbf'

由于没有日志切换，数据还在redo中，所以可以用recover 进行恢复。

SQL> recover datafile 5;

Media recovery complete.

如下，恢复成功：

SQL> alter database datafile '/data/datafile/prod/PROD/datafile/o1_mf_test1_fwwb2zh1_.dbf' online;

Database altered.

SQL>

SQL> select file_name ,ONLINE_STATUS from dba_data_files;

FILE_NAME ONLINE_

-------------------------------------------------------------------------------- -------

/data/datafile/prod/users01.dbf ONLINE

/data/datafile/prod/undotbs01.dbf ONLINE

/data/datafile/prod/sysaux01.dbf ONLINE

/data/datafile/prod/system01.dbf SYSTEM

/data/datafile/prod/PROD/datafile/o1_mf_test1_fwwb2zh1_.dbf ONLINE

再次进行日志切换进行测试：

SQL>

再次 offline drop

SQL> alter database datafile '/data/datafile/prod/PROD/datafile/o1_mf_test1_fwwb2zh1_.dbf' offline drop;

Database altered.

SQL> select file_name ,ONLINE_STATUS from dba_data_files;

FILE_NAME ONLINE_

-------------------------------------------------------------------------------- -------

/data/datafile/prod/users01.dbf ONLINE

/data/datafile/prod/undotbs01.dbf ONLINE

/data/datafile/prod/sysaux01.dbf ONLINE

/data/datafile/prod/system01.dbf SYSTEM

/data/datafile/prod/PROD/datafile/o1_mf_test1_fwwb2zh1_.dbf RECOVER

切换4次日志：

SQL> alter system switch logfile ;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> alter database datafile 5 online ;

alter database datafile 5 online

ERROR at line 1:

ORA-01113: file 5 needs media recovery

ORA-01110: data file 5: '/data/datafile/prod/PROD/datafile/o1_mf_test1_fwwb2zh1_.dbf'

这次redo中已经没有了日志，无法进行 recover 恢复。所以面临数据丢失问题

SQL> recover datafile 5;

ORA-00279: change 965377 generated at 10/23/2018 03:48:52 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/product/11.2.0/db_1/dbs/arch1_5_990243348.dbf

ORA-00280: change 965377 for thread 1 is in sequence #5

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

auto

ORA-00308: cannot open archived log '/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_5_990243348.dbf'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

ORA-00308: cannot open archived log '/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_5_990243348.dbf'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

SQL> alter database datafile 5 online ;

alter database datafile 5 online

ERROR at line 1:

ORA-01113: file 5 needs media recovery

ORA-01110: data file 5: '/data/datafile/prod/PROD/datafile/o1_mf_test1_fwwb2zh1_.dbf'

====================================================================================================

归档模式测试：

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 1185853440 bytes

Fixed Size 2252664 bytes

Variable Size 754974856 bytes

Database Buffers 419430400 bytes

Redo Buffers 9195520 bytes

Database mounted.

SQL> alter database archivelog ;

Database altered.

SQL> alter database open ;

Database altered.

查看归档模式已经开启：

SQL> archive log list ;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /u01/app/oracle/product/11.2.0/db_1/dbs/arch

Oldest online log sequence 7

Next log sequence to archive 9

Current log sequence 9

SQL>

创建表空间进行测试：

SQL> create tablespace test2 datafile size 5m;

Tablespace created.

SQL>

查看状态信息,已经创建新的数据文件 6

SQL> select file_id,file_name,ONLINE_STATUS from dba_data_files ;

FILE_ID FILE_NAME ONLINE_

---------- -------------------------------------------------------------------------------- -------

4 /data/datafile/prod/users01.dbf ONLINE

3 /data/datafile/prod/undotbs01.dbf ONLINE

2 /data/datafile/prod/sysaux01.dbf ONLINE

1 /data/datafile/prod/system01.dbf SYSTEM

5 /data/datafile/prod/PROD/datafile/o1_mf_test1_fwwb2zh1_.dbf RECOVER

6 /data/datafile/prod/PROD/datafile/o1_mf_test2_fwwbpr87_.dbf ONLINE

6 rows selected.

归档模式下可以直接进行 offline操作

SQL> alter database datafile 6 offline ;

Database altered.

SQL> alter database datafile 6 online ;

alter database datafile 6 online

ERROR at line 1:

ORA-01113: file 6 needs media recovery

ORA-01110: data file 6: '/data/datafile/prod/PROD/datafile/o1_mf_test2_fwwbpr87_.dbf'

需要recover ，因为redo中还有信息，所以可以直接recover 成功。

SQL> recover datafile 6;

Media recovery complete.

可以重新online ，数据没有丢失

SQL> alter database datafile 6 online ;

Database altered.

再次进行offline 进行操作。这次进行4次日志切换。

SQL> alter database datafile 6 offline ;

Database altered.

SQL> alter system switch logfile ;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> alter database datafile 6 online ;

alter database datafile 6 online

ERROR at line 1:

ORA-01113: file 6 needs media recovery

ORA-01110: data file 6: '/data/datafile/prod/PROD/datafile/o1_mf_test2_fwwbpr87_.dbf'

进行恢复recover ，因为 redo已经归档到归档日志里面，所以这里可以恢复成功。

SQL> recover datafile 6;

ORA-00279: change 966347 generated at 10/23/2018 03:55:26 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/product/11.2.0/db_1/dbs/arch1_9_990243348.dbf

ORA-00280: change 966347 for thread 1 is in sequence #9

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

auto

Log applied.

Media recovery complete.

重新online成功

SQL> alter database datafile 6 online ;

Database altered.

SQL> select file_id,file_name,ONLINE_STATUS from dba_data_files ;

FILE_ID FILE_NAME ONLINE_

---------- -------------------------------------------------------------------------------- -------

4 /data/datafile/prod/users01.dbf ONLINE

3 /data/datafile/prod/undotbs01.dbf ONLINE

2 /data/datafile/prod/sysaux01.dbf ONLINE

1 /data/datafile/prod/system01.dbf SYSTEM

5 /data/datafile/prod/PROD/datafile/o1_mf_test1_fwwb2zh1_.dbf RECOVER

6 /data/datafile/prod/PROD/datafile/o1_mf_test2_fwwbpr87_.dbf ONLINE

6 rows selected.

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：ENQ: KO - FAST OBJECT CHECKPOINT tips

下一篇：极端恢复时用到的几个隐藏参数 Oracle hidden corruption recovery parameters

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯

关于alter database datafile offline和alter database datafile offline drop 的区别

关于alter database datafile offline和alter database datafile offline drop 的区别

51CTO博客