在Oracle中，数据文件OFFLINE之后必须要做的一件事是什么？
👉 本文共约488个字，系统预计阅读时间或需2分钟。
数据文件OFFLINE之后必须要做的一件事就是立刻执行一次RECOVER操作，这样在无论过了多久之后，在ONLINE该数据文件的时候就不需要执行RECOVER操作了。
下面通过两个实验来对比验证该结论。
实验环境如下表所示：
| 项目 | source db |
|---|---|
| db 类型 | 单实例 |
| db version | 11.2.0.3.4 |
| db 存储 | ASM |
| OS版本及kernel版本 | AIX 64位 7.1.0.0 |
实验一：数据文件OFFLINE后没有立刻执行RECOVER操作
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 | SYS@lhrdb> COL NAME FOR A60 SYS@lhrdb> SELECT FILE#,NAME,STATUS FROM V$DATAFILE; FILE# NAME STATUS ---------- ------------------------------------------------------------ ------- 1 +DATA/lhrdb/datafile/system.347.916601927 SYSTEM 2 +DATA/lhrdb/datafile/sysaux.340.916601927 ONLINE 3 +DATA/lhrdb/datafile/undotbs1.353.916601927 ONLINE 4 +DATA/lhrdb/datafile/users.445.916601927 ONLINE 5 +DATA/lhrdb/datafile/example.416.916602001 ONLINE 6 +DATA/lhrdb/datafile/ts_mig_chain_lhr.471.919677645 ONLINE 6 rows selected. SYS@lhrdb> ALTER DATABASE DATAFILE 6 OFFLINE; ALTER DATABASE DATAFILE 6 OFFLINE * ERROR at line 1: ORA-01145: offline immediate disallowed unless media recovery enabled ====>>>>> 数据库必须归档才可以OFFLINE SYS@lhrdb> ARCHIVE LOG LIST; Database log mode No Archive Mode Automatic archival Disabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 265 Current log sequence 267 SYS@lhrdb> SHUTDOWN IMMEDIATE Database closed. Database dismounted. ORACLE instance shut down. SYS@lhrdb> STARTUP MOUNT ORACLE instance started. Total System Global Area 1720328192 bytes Fixed Size 2247072 bytes Variable Size 486540896 bytes Database Buffers 1224736768 bytes Redo Buffers 6803456 bytes Database mounted. SYS@lhrdb> ALTER DATABASE ARCHIVELOG; Database altered. SYS@lhrdb> ALTER DATABASE OPEN; Database altered. SYS@lhrdb> ALTER DATABASE DATAFILE 6 OFFLINE; Database altered. SYS@lhrdb> SELECT FILE#,NAME,STATUS FROM V$DATAFILE; FILE# NAME STATUS ---------- ------------------------------------------------------------ ------- 1 +DATA/lhrdb/datafile/system.347.916601927 SYSTEM 2 +DATA/lhrdb/datafile/sysaux.340.916601927 ONLINE 3 +DATA/lhrdb/datafile/undotbs1.353.916601927 ONLINE 4 +DATA/lhrdb/datafile/users.445.916601927 ONLINE 5 +DATA/lhrdb/datafile/example.416.916602001 ONLINE 6 +DATA/lhrdb/datafile/ts_mig_chain_lhr.471.919677645 RECOVER 6 rows selected. SYS@lhrdb> SELECT FILE#,ONLINE_STATUS,CHANGE#,ERROR FROM V$RECOVER_FILE; FILE# ONLINE_ CHANGE# ERROR ---------- ------- ---------- ----------------------------------------------------------------- 6 OFFLINE 7485831 SYS@lhrdb> ALTER SYSTEM SWITCH LOGFILE; System altered. SYS@lhrdb> ALTER SYSTEM SWITCH LOGFILE; System altered. SYS@lhrdb> 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/lhrdb/datafile/ts_mig_chain_lhr.471.919677645' SYS@lhrdb> RECOVER DATAFILE 6; Media recovery complete. SYS@lhrdb> ALTER DATABASE DATAFILE 6 ONLINE;<<<<<<<<<----- 可以看到6号文件必须先执行recover操作后才能执行ONLINE Database altered. |
实验二：数据文件OFFLINE后立刻执行一次RECOVER操作
本人提供Oracle、MySQL、PG等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | SYS@lhrdb> ALTER DATABASE DATAFILE 6 OFFLINE; Database altered. SYS@lhrdb> recover datafile 6;<<<<<<<<<----- OFFLINE后接着执行recover操作 Media recovery complete. SYS@lhrdb> SELECT FILE#,ONLINE_STATUS,CHANGE#,ERROR FROM V$RECOVER_FILE;<<<<<<<<<--该视图查不到数据 no rows selected SYS@lhrdb> SELECT FILE#,NAME,STATUS FROM V$DATAFILE; FILE# NAME STATUS ---------- ------------------------------------------------------------ ------- 1 +DATA/lhrdb/datafile/system.347.916601927 SYSTEM 2 +DATA/lhrdb/datafile/sysaux.340.916601927 ONLINE 3 +DATA/lhrdb/datafile/undotbs1.353.916601927 ONLINE 4 +DATA/lhrdb/datafile/users.445.916601927 ONLINE 5 +DATA/lhrdb/datafile/example.416.916602001 ONLINE 6 +DATA/lhrdb/datafile/ts_mig_chain_lhr.471.919677645 OFFLINE 6 rows selected. SYS@lhrdb> ALTER SYSTEM SWITCH LOGFILE; System altered. SYS@lhrdb> ALTER SYSTEM SWITCH LOGFILE; System altered. SYS@lhrdb> ALTER SYSTEM SWITCH LOGFILE; System altered. SYS@lhrdb> alter database datafile 6 online;<<<<<<<<<-----切换日志后让数据文件做ONLINE操作并不需要执行RECOVER操作 Database altered. SYS@lhrdb> select file#,name,status from v$datafile; FILE# NAME STATUS ---------- ------------------------------------------------------------ ------- 1 +DATA/lhrdb/datafile/system.347.916601927 SYSTEM 2 +DATA/lhrdb/datafile/sysaux.340.916601927 ONLINE 3 +DATA/lhrdb/datafile/undotbs1.353.916601927 ONLINE 4 +DATA/lhrdb/datafile/users.445.916601927 ONLINE 5 +DATA/lhrdb/datafile/example.416.916602001 ONLINE 6 +DATA/lhrdb/datafile/ts_mig_chain_lhr.471.919677645 ONLINE 6 rows selected. SYS@lhrdb> |
实验结束，根据实验过程可以知道，如果执行了数据文件的OFFLINE操作，那么需要接着执行一次RECOVER操作。这样做的好处是，在以后的数据库维护中，随时想将数据文件ONLINE都可以，而不用担心归档文件是否存在的情况了。
