?tablespace offline是將tablespace 離線有normal等選項

?

，命令為 alter tablespace users offline normal;

?

SQL> select tablespace_name, status from dba_tablespaces;

?

TABLESPACE_NAME ? ? ? ? ? ? ? ?STATUS

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

SYSTEM ? ? ? ? ? ? ? ? ? ? ? ? ONLINE

SYSAUX ? ? ? ? ? ? ? ? ? ? ? ? ONLINE

UNDOTBS1 ? ? ? ? ? ? ? ? ? ? ? ONLINE

USERS ? ? ? ? ? ? ? ? ? ? ? ? ?OFFLINE

EXAMPLE ? ? ? ? ? ? ? ? ? ? ? ?ONLINE

UNDO_NOGUARANTEE ? ? ? ? ? ? ? ONLINE

UNDO_GUARANTEE ? ? ? ? ? ? ? ? ONLINE

NONCRIT ? ? ? ? ? ? ? ? ? ? ? ?ONLINE

TEST121217 ? ? ? ? ? ? ? ? ? ? ONLINE

SMALL ? ? ? ? ? ? ? ? ? ? ? ? ?ONLINE

TEMP02 ? ? ? ? ? ? ? ? ? ? ? ? ONLINE

INDEX_TEST1 ? ? ? ? ? ? ? ? ? ?ONLINE

NEW_TBS ? ? ? ? ? ? ? ? ? ? ? ?OFFLINE

?

將datafile offline，需要注意當(dāng)online時需要進行meida recovery；

11:02:37 SYS@TEST>ALTER DATABASE DATAFILE 13 OFFLINE;

?

Database altered.

?

11:13:56 SYS@TEST>ALTER DATABASE DATAFILE 13 ONLINE;

ALTER DATABASE DATAFILE 13 ONLINE

*

ERROR at line 1:

ORA-01113: file 13 needs media recovery

ORA-01110: data file 13: 'D:\ORACLE\ORADATA\TEST\NEW_TBS.DBF'

11:02:37 SYS@TEST>ALTER DATABASE DATAFILE 13 OFFLINE; Database altered. 11:13:56 SYS@TEST>ALTER DATABASE DATAFILE 13 ONLINE; ALTER DATABASE DATAFILE 13 ONLINE * ERROR at line 1: ORA-01113: file 13 needs media recovery ORA-01110: data file 13: 'D:\ORACLE\ORADATA\TEST\NEW_TBS.DBF'

RMAN> RESTORE DATAFILE 13;

Starting restore at 07-MAY-13

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=148 device type=DISK

creating datafile file number=13 name=D:\ORACLE\ORADATA\TEST\NEW_TBS.DBF

restore not done; all files read only, offline, or already restored

Finished restore at 07-MAY-13

?

需要注意的是只是需要進行recover，并不需要restore，因為文件本身存在未損壞，recover是要將歸檔日志應(yīng)用到離線的文件

RMAN> RECOVER DATAFILE 13;

?

Starting recover at 07-MAY-13

using channel ORA_DISK_1

?

starting media recovery

?

archived log for thread 1 with sequence 15 is already on disk as file D:\ORACLE\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2013_01_24\O1_MF_1_15_8J17J7Y8_.ARC

archived log for thread 1 with sequence 16 is already on disk as file D:\ORACLE\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2013_01_24\O1_MF_1_16_8J2FXDRL_.ARC

archived log for thread 1 with sequence 17 is already on disk as file D:\ORACLE\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2013_01_24\O1_MF_1_17_8J2HFHJY_.ARC

archived log for thread 1 with sequence 18 is already on disk as file D:\ORACLE\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2013_01_24\O1_MF_1_18_8J2HS7PW_.ARC

archived log for thread 1 with sequence 19 is already on disk as file D:\ORACLE\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2013_01_25\O1_MF_1_19_8J4CP21K_.ARC

archived log for thread 1 with sequence 20 is already on disk as file D:\ORACLE\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2013_02_11\O1_MF_1_20_8KKQFN9C_.ARC

archived log for thread 1 with sequence 21 is already on disk as file D:\ORACLE\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2013_02_11\O1_MF_1_21_8KKQFS1Q_.ARC

archived log for thread 1 with sequence 22 is already on disk as file D:\ORACLE\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2013_02_11\O1_MF_1_22_8KKQFYK6_.ARC

archived log for thread 1 with sequence 23 is already on disk as file D:\ORACLE\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2013_02_11\O1_MF_1_23_8KKQG35K_.ARC

archived log for thread 1 with sequence 24 is already on disk as file D:\ORACLE\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2013_02_11\O1_MF_1_24_8KKQG7WO_.ARC

archived log for thread 1 with sequence 25 is already on disk as file D:\ORACLE\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2013_02_11\O1_MF_1_25_8KKQGDNP_.ARC

archived log for thread 1 with sequence 26 is already on disk as file D:\ORACLE\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2013_02_19\O1_MF_1_26_8L6FQ13F_.ARC

archived log for thread 1 with sequence 27 is already on disk as file D:\ORACLE\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2013_02_27\O1_MF_1_27_8LVD2GR3_.ARC

archived log for thread 1 with sequence 28 is already on disk as file D:\ORACLE\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2013_02_27\O1_MF_1_28_8LVD2N14_.ARC

archived log for thread 1 with sequence 29 is already on disk as file D:\ORACLE\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2013_03_19\O1_MF_1_29_8NHZR642_.ARC

archived log for thread 1 with sequence 30 is already on disk as file D:\ORACLE\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2013_03_21\O1_MF_1_30_8NO5VRRQ_.ARC

archived log for thread 1 with sequence 31 is already on disk as file D:\ORACLE\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2013_03_25\O1_MF_1_31_8NZT0GN7_.ARC

archived log for thread 1 with sequence 32 is already on disk as file D:\ORACLE\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2013_04_09\O1_MF_1_32_8P71V8S6_.ARC

archived log for thread 1 with sequence 33 is already on disk as file D:\ORACLE\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2013_04_09\O1_MF_1_33_8P78BBX3_.ARC

archived log for thread 1 with sequence 34 is already on disk as file D:\ORACLE\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2013_04_11\O1_MF_1_34_8PDOSG43_.ARC

archived log for thread 1 with sequence 35 is already on disk as file D:\ORACLE\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2013_04_13\O1_MF_1_35_8PKLTO7H_.ARC

archived log for thread 1 with sequence 36 is already on disk as file D:\ORACLE\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2013_04_13\O1_MF_1_36_8PKM1G58_.ARC

archived log for thread 1 with sequence 37 is already on disk as file D:\ORACLE\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2013_05_07\O1_MF_1_37_8RJRK8J7_.ARC

archived log file name=D:\ORACLE\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2013_01_24\O1_MF_1_15_8J17J7Y8_.ARC thread=1 sequence=15

archived log file name=D:\ORACLE\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2013_01_24\O1_MF_1_16_8J2FXDRL_.ARC thread=1 sequence=16

archived log file name=D:\ORACLE\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2013_01_24\O1_MF_1_17_8J2HFHJY_.ARC thread=1 sequence=17

archived log file name=D:\ORACLE\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2013_01_24\O1_MF_1_18_8J2HS7PW_.ARC thread=1 sequence=18

archived log file name=D:\ORACLE\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2013_01_25\O1_MF_1_19_8J4CP21K_.ARC thread=1 sequence=19

archived log file name=D:\ORACLE\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2013_02_11\O1_MF_1_20_8KKQFN9C_.ARC thread=1 sequence=20

archived log file name=D:\ORACLE\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2013_02_11\O1_MF_1_21_8KKQFS1Q_.ARC thread=1 sequence=21

archived log file name=D:\ORACLE\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2013_02_11\O1_MF_1_22_8KKQFYK6_.ARC thread=1 sequence=22

archived log file name=D:\ORACLE\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2013_02_11\O1_MF_1_23_8KKQG35K_.ARC thread=1 sequence=23

archived log file name=D:\ORACLE\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2013_02_11\O1_MF_1_24_8KKQG7WO_.ARC thread=1 sequence=24

archived log file name=D:\ORACLE\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2013_02_11\O1_MF_1_25_8KKQGDNP_.ARC thread=1 sequence=25

archived log file name=D:\ORACLE\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2013_02_19\O1_MF_1_26_8L6FQ13F_.ARC thread=1 sequence=26

archived log file name=D:\ORACLE\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2013_02_27\O1_MF_1_27_8LVD2GR3_.ARC thread=1 sequence=27

archived log file name=D:\ORACLE\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2013_02_27\O1_MF_1_28_8LVD2N14_.ARC thread=1 sequence=28

archived log file name=D:\ORACLE\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2013_03_19\O1_MF_1_29_8NHZR642_.ARC thread=1 sequence=29

archived log file name=D:\ORACLE\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2013_03_21\O1_MF_1_30_8NO5VRRQ_.ARC thread=1 sequence=30

archived log file name=D:\ORACLE\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2013_03_25\O1_MF_1_31_8NZT0GN7_.ARC thread=1 sequence=31

archived log file name=D:\ORACLE\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2013_04_09\O1_MF_1_32_8P71V8S6_.ARC thread=1 sequence=32

archived log file name=D:\ORACLE\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2013_04_09\O1_MF_1_33_8P78BBX3_.ARC thread=1 sequence=33

archived log file name=D:\ORACLE\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2013_04_11\O1_MF_1_34_8PDOSG43_.ARC thread=1 sequence=34

archived log file name=D:\ORACLE\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2013_04_13\O1_MF_1_35_8PKLTO7H_.ARC thread=1 sequence=35

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

Finished recover at 07-MAY-13

轉(zhuǎn)載于:https://blog.51cto.com/1310976/1194737

總結(jié)

以上是生活随笔為你收集整理的130506datafile和tablespace offline区别的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。