oracle datafile offline,130506datafile和tablespace offline区别

最新推荐文章于 2021-08-05 11:43:16 发布
最新推荐文章于 2021-08-05 11:43:16 发布
阅读量384 收藏
点赞数
文章标签： oracle datafile offline

tablespace offline是将tablespace 离线有normal等选项

66e15b77088e178918f4d599f713d6d2.gif

，命令为 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，需要注意当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是要将归档日志应用到离线的文件

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

冰川思想库
关注
oracle 数据文件offline
ckth47350的博客
01-26 603
=============================== 按表空间---移动数据文件(数据库打开，归档模式) =============================== SQL> create tabl...
oracle datafile offline,alter database datafile offline drop 与 alter ...
weixin_30844865的博客
04-04 1713
以前整理的：Oracle表空间基本操作这篇Blog里真的是最基本的操作。其实今天写的内容，也可以放到那里面去。但是还是打算分开来讲。引起一个重视吧。1. alter database datafile 'file_name' offline drop该命令不会删除数据文件，只是将数据文件的状态更改为recover。 offline drop命令相当于把一个数据文件至于离线状态，并且需要恢复，并非删...
oracle datafile offline drop
loamier的专栏
11-16 375
[root@hp6200 tmp]#   su - oracle [oracle@hp6200 ~]$ [oracle@hp6200 ~]$ [oracle@hp6200 ~]$ sqlplus /nolog                                                                                  ...
oracle datafile offline,Automatic datafile offline due to write error on
weixin_36489344的博客
04-04 213
由于存储突然掉线导致数据文件无法访问,导致部分数据文件被自动offlineThu May 17 14:49:03 2018KCF: read, write or open error, block=0xe93b8 online=1Thu May 17 14:49:03 2018KCF: read, write or open error, block=0x24eb65 online=1file=2...
offline datafile,offline tablespace
cuicuntu1021的博客
02-19 119
参考：http://www.itpub.net/showthread.php?threadid=244134&pagenumber= http://www.itpub.net/thread-248076-1-1.htm...
oracle tablespace offline datafile offline的区别是什么
最新发布
06-10
在 Oracle 数据库中，tablespace 和 datafile 是两个不同的概念。 tablespace 是逻辑存储结构，用于存储数据库中的表、索引等对象。每个 tablespace 包含多个数据文件，可以跨多个物理磁盘存储数据。 datafile 是...
ORACLE表空间与数据文件OFFLINE区别
彦祖的小号的博客
08-05 697
（一）OFFLINE TABLESPACE ALTER TABLESAPCE OFFLINE可用选项包括NORMAL,TEMPORARY,IMMEDIATE,FOR RECOVERY OFFLINE NORMAL时该表空间的所有数据文件均要ONLINE状态，ONLINE不用RECOVER SpecifyNORMAL to flush all blocks in all data files in the tablespace out of the system global area (SG...
oracle add datafile ora-01900,[读书笔记]ORACLE数据库文件管理
weixin_33603844的博客
04-08 1141
数据库文件包括数据文件、日志文件、控制文件和密码文件、参数文件等，对于这些文件的管理可以看作是增删改查的过程。1.数据文件管理增：新增表空间时增加数据文件createtablespacetest_2016datafile'/u01/app/oracle/oradata/orcl/test_01.dbf'size10M autoextendon next10M maxsize100...
oracle-datafile管理
天行健，君子以自强不息
04-01 2179
数据文件，这一节还是比较简单的，都是一些简单的操作实验。 A physical operating system file on disk that was created by Oracle and contains data structures such as tables and indexes. A datafile can only belong to one database.
oracle里tablespace offline和datafile offline的区别
朝着梦想 渐行前进
10-21 3289
网上搜了几篇关于tablespace offline 和 datafile offline区别的文章，小总结了一下，记下来，方便以后学习。==================================================================================There is a big difference between:Taking the
利用offline datafile文件方式迁移数据
cuilin2183的博客
06-10 433
我们可能经常遇到这样的情况：1. 因为存储需要调整，所以需要暂时把部分数据文件从一个存储迁移到另一个存储上2. 随时时间的转移，原来的热点数据变成历史数据，需要这部分数据从好的设备转移到归档设备上我们的选择有多种：1. move...
oracle数据库offline,oracle 数据文件offline
weixin_29189373的博客
04-02 942
===============================按表空间---移动数据文件(数据库打开，归档模式)===============================SQL> create tablespace t1 datafile '/u01/app/oracle/oradata/orcl/t1.dbf' size 1M;SQL> create table test(id ...
对一个datafile执行offline和offline drop的区别
08-11 538
From: http://dbsnake.com/2010/11/offline-offline-drop-diff.html   1、对一个datafile执行offline或offline drop本质上是一回事，但对一个datafile执行offline只能在归档模
Oracle删除数据文件
abc123098098的博客
11-03 1174
在我们详细介绍之前，我们必须说清楚一点：Oracle不提供如删除表、视图一样删除数据文件的方法，数据文件是表空间的一部分，所以不能“移走”表空间。 一、使用offline数据文件的方法 非归档模式使用：alter database datafile '...' offline drop; 归档模式使用： alter database datafile '...' offli...
Oracle Tablespace Offline Mode
jzzw的博客
02-26 380
表空间的offline的用法： (在本实验中，tablespace test1 由datafile 6\7 组成，每次offline tablespace之前都先将datafile 7 offline，版本11.2.0.4)     归档状态下： 1.offline normal：默认的offline模式，offline的时候会在表空间内部进行Check Point动作，保证表空间内部各个文件...
存在datafile offline，如何进行异机恢复？
aocu40057610的博客
09-27 295
详见原文博客链接地址：存在datafile offline，如何进行异机恢复？ 转载于:https://www.cnblogs.com/lovewifelovelife/archive/2012/10/21/3295013.html
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值