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

转： https://blog.csdn.net/killvoon/article/details/46913183

-----------------------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
来源：CSDN
原文：https://blog.csdn.net/killvoon/article/details/46913183
版权声明：本文为博主原创文章，转载请附上博文链接！

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

如下自我测试：：

[root@prod ~]# su - oracle
[oracle@prod ~]$
[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>
SQL> select file_name ,ONLINE_STATUS from dba_data_files;

再次进行日志切换进行测试：
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;

切换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> 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.

进行恢复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 ;

6 rows selected.

秒客网

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

相关文章