RECOVERING FROM A LOST DATAFILE IN A UNDO TABLESPACE (文档 ID 1013221.6)

 the range of your online logs.  Otherwise, the
last full backup must be restored.  If a backup of the datafile is not
available, please contact Oracle Support Services.

These are the steps:

1. Restore the lost file from a backup.

2. Mount the database:
 
   SQL> STARTUP MOUNT;

3. Issue the following query:

   SQL> SELECT FILE#, NAME, STATUS FROM V$DATAFILE;

If the status of the file you just restored is "OFFLINE," you must
online it before proceeding:

   SQL> ALTER DATABASE DATAFILE '' ONLINE;

4. Issue the following query:

   SQL> SELECT V1.GROUP#, MEMBER, SEQUENCE#, FIRST_CHANGE#
FROM V$LOG V1, V$LOGFILE V2
WHERE V1.GROUP# = V2.GROUP# ;

This will list all your online redolog files and their respective
sequence and first change numbers.

5. If the database is in NOARCHIVELOG mode, issue the query:

   SQL> SELECT FILE#, CHANGE# FROM V$RECOVER_FILE;

   If the CHANGE# is GREATER than the minimum FIRST_CHANGE# of your
   logs, the datafile can be recovered.  Just keep in mind that all the
   logs to be applied will be online logs, and move on to step 6.

   If the CHANGE# is LESSER than the minimum FIRST_CHANGE# of your logs,
   the file cannot be recovered.  Your options at this point include
   restoring a full backup if one is available or forcing the database
   to open in an inconsistent state to get a full export out of it.
   For further details and to assist you in your decision, please contact
   Oracle Support Services.

6. Recover the datafile:

   SQL> RECOVER DATAFILE ''

7. Confirm each of the  logs that you are prompted for until you receive
   the message "Media recovery complete".  If you are prompted for a
   non-existing archived log, Oracle probably needs one or more of the
   online logs to proceed with the recovery.  Compare the sequence number
   referenced in the ORA-280 message with the sequence numbers of your online
   logs.  Then enter the full path name of one of the members of the redo group
   whose sequence number matches the one you are being asked for.  Keep entering
   online logs as requested until you receive the message "Media recovery
   complete".

8. Open the database:

   SQL> ALTER DATABASE OPEN;


II. THE DATABASE IS UP
----------------------

If you have detected the loss of the rollback datafile and the database
is still up and running, DO NOT SHUT IT DOWN.  In most cases, it is
simpler to solve this problem with the database up than with it down.

Two approaches are possible in this scenario:

A) The first approach involves creating a new UNDO (or rollback segment)
   tablespace, altering the system to use the new and dropping the old.

B) The other approach involves offlining the lost datafile, restoring it from
   backup, and then applying media recovery to it to make it consistent
   with the rest of the database.  This method can only be used if
   the database is in ARCHIVELOG mode.

In general, approach IIA is simpler to try first, however, depending on
activity and undo usage, the drop of the older tablespace may result
in error regarding active transactions.  There are a few things to try to
get the older tablespace to drop, but if all fail, approach IIB may be required. 

APPROACH II.A: RECREATING THE ROLLBACK TABLESPACE
-------------------------------------------------

This approach can be used regardless of the archival mode of the database.
The steps are:

For undo tablespaces:
.....................

1. Create a new undo tablespace:

   SQL> CREATE UNDO TABLESPACE  
        DATAFILE '' SIZE ....;

2. Alter the system to use the new undo tablespace:

   SQL> ALTER SYSTEM SET UNDO_TABLESPACE='';

At this point, all new undo will be allocated in undo segments managed within
the UNDO_TBS2 tablespace.

3. Try dropping older undo tablespace:

   SQL> DROP TABLESPACE INCLUDING CONTENTS;

If this succeeds, you are done.  If it returns an error, please see
"Handling errors dropping undo/rollback segments".

For rollback segment tablespaces:
.................................

1. Create new tablespace to house rollback segments.  For spacing and extent
sizing, see information in dba_tablespaces for old rollback segment tablespace. 

   SQL> CREATE TABLESPACE .....

2. Create rollback segments in this tablespace

   SQL> CREATE ROLLBACK SEGMENT.....

3. Online the rollback segments in this new tablespace:

   SQL> ALTER ROLLBACK SEGMENT ONLINE;

   Repeat for all rollback segments.

4. Try to offline all the rollback segments in the tablespace to which
   the lost datafile belongs.

   SQL> ALTER ROLLBACK SEGMENT OFFLINE;

   Repeat this statement for all rollbacks in the tablespace.

5. Check the status of the rollbacks.

    They must all be offline before they can be dropped. Issue the query:

   SQL> SELECT SEGMENT_NAME, STATUS FROM DBA_ROLLBACK_SEGS
WHERE TABLESPACE_NAME = '';

6. Drop all offlined rollback segments.

For each rollback returned by the query in step 2 with status
"OFFLINE," issue the statement:

   SQL> DROP ROLLBACK SEGMENT ;

7. Drop the rollback segment tablespace:

   SQL> DROP TABLESPACE INCLUDING CONTENTS;

   If this succeeds, you are done.  If it returns an error, please see
   "Handling errors dropping undo/rollback segments".

HANDLING ERRORS DROPPING UNDO/ROLLBACK SEGMENTS:
-----------------------------------------------

NOTE, although automatic undo and undo tablespace is used by the database,
the following procedure is the same.  The only difference is that the segment
names are assigned by Oracle.

1. Check for any rollback segments online. 

   SQL> SELECT SEGMENT_NAME, STATUS FROM DBA_ROLLBACK_SEGS
        WHERE TABLESPACE_NAME = '';

   If any of the rollbacks you tried to offline still has an "ONLINE" status,
   this is usually an indication that this segment contains active transactions.
  
2. Check for active transactions with the following query:

   SQL> SELECT SEGMENT_NAME, XACTS ACTIVE_TX, V.STATUS
FROM V$ROLLSTAT V, DBA_ROLLBACK_SEGS
WHERE TABLESPACE_NAME = '' AND SEGMENT_ID = USN;

   If the above query returns no rows, it means all the rollbacks in
   the affected tablespace are already offline.  Repeat the query in
   step 1 to retrieve the names of the rollbacks that just became
   offline and attempt to drop the undo tablespace or individual
   rollback segments as described above.  

   If the above query returns one or more rows, they should show
   status "PENDING OFFLINE".  Next, check the ACTIVE_TX
   column for each rollback.  If it has a value of 0, it implies
   there are no pending transactions left in the rollback, and it
   should go offline shortly.  Repeat the query in step 1 a few
   more times until it shows the rollback segments being offline and
   then attempt the drop again.  Once the drop is successful, you are done.

   If any of the "pending offline" rollbacks has a value of 1 or
   greater in the ACTIVE_TX column, move on to step 3.

3. Force rollbacks with active transactions to go offline.

   At this point, the only way to move forward is to have the
   "pending offline" rollbacks released.  The active transactions
   in these rollbacks must either be committed or rolled back.
   The following query shows which users have transactions assigned
   to which rollbacks:

   SQL> SELECT S.SID, S.SERIAL#, S.USERNAME, R.NAME "ROLLBACK"
FROM V$SESSION S, V$TRANSACTION T, V$ROLLNAME R
WHERE R.NAME IN ('', ... , '')
AND S.TADDR = T.ADDR AND T.XIDUSN = R.USN;

   You may directly contact the users with transactions in the
   "pending offline" rollbacks and ask them to commit (preferably)
   or rollback immediately.  If that is not feasible, you can force
   that to happen by killing their sessions.  For each of the entries
   returned by the above query, issue the statement:

   SQL> ALTER SYSTEM KILL SESSION ', ';

   where and are those returned by the previous
   query.  After the sessions are killed, it may take a few minutes
   before Oracle finishes rolling back and doing cleanup work.  Go back
   to step 1 and repeat the query in there periodically until all
   rollbacks in the affected tablespace are offline and ready to be
   dropped.

   If you are unable to drop the older undo tablespace or rollback segments,
   try to restore the file from backup and recover it fully (approach II.B). 
   Once the file is recovered try to drop the older undo tablespace.  If this
   is not possible, contact Oracle Support Services. 

APPROACH II.B: RESTORING THE DATAFILE FROM BACKUP
-------------------------------------------------

As mentioned before, this approach can only be followed if the database is
in ARCHIVELOG mode.  Here are the steps:

1. Offline the lost datafile.

   SQL> ALTER DATABASE DATAFILE '' OFFLINE;

   NOTE: Depending on the current amount of database activity,
   you may have to create additional rollback segments in a different
   tablespace to keep the database going while you take care of the problem.

2. Restore the datafile from a backup.

3. Issue the following query:

   SQL> SELECT V1.GROUP#, MEMBER, SEQUENCE#
FROM V$LOG V1, V$LOGFILE V2
WHERE V1.GROUP# = V2.GROUP# ;

   This will list all your online redolog files and their respective
   sequence numbers.

4. Recover the datafile:

   SQL> RECOVER DATAFILE ''

5. Confirm each of the  logs that you are prompted for until you receive
   the message "Media recovery complete".  If you are prompted for a
   non-existing archived log, Oracle probably needs one or more of the
   online logs to proceed with the recovery.  Compare the sequence number
   referenced in the ORA-280 message with the sequence numbers of your online
   logs.  Then enter the full path name of one of the members of the redo group
   whose sequence number matches the one you are being asked for.  Keep
   entering online logs as requested until you receive the message
   "Media recovery complete".

6. Bring the datafile back online.

   SQL> ALTER DATABASE DATAFILE '' ONLINE;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/13750068/viewspace-1165059/,如需转载,请注明出处,否则将追究法律责任。

转载于:http://blog.itpub.net/13750068/viewspace-1165059/

自制JS某课脚本 根据实际需求自制网页讯网刷题脚本,绕过人脸,弹窗,答题,实现自动化运行 阅读详情

相关推荐

MinIO vs 华为云OBS:成本、性能与兼容性深度对比(含实测数据)

本文深度对比了开源对象存储MinIO与华为云OBS在成本、性能、兼容性及运维方面的核心差异。通过实测数据与全生命周期成本分析,为技术决策者提供选型依据。文章指出,MinIO在长期成本控制、性能深度优化及S3 API兼容性方面优势显著,而华为云OBS则提供开箱即用的便利性。

kmeans3miner的博客 597

如何收缩undo数据文件(文档ID 268870.1)

APPLIES TO:Oracle Database - Enterprise Edition - Version 9.2.0.1 and laterOracle Database - Standard Edition - Version 9.2.0.7 and laterInformation in this document applies to any platform.***Checked...

逝鸿的博客 378

手把手教你用XC7A35T FPGA搭建低成本机器视觉系统(含开发板选型指南)

本文详细介绍了如何利用AMD(原Xilinx)Artix-7系列中的XC7A35T FPGA芯片,搭建一套低成本的机器视觉系统。内容涵盖从芯片选型深度剖析、开发板(如黑金AX7035B)对比指南,到使用Vivado创建工程、编写图像处理模块(如RGB转灰度),以及资源优化和与上位机(如OpenCV)协同工作的完整流程,旨在为开发者提供一套可实践的高性价比硬件加速视觉解决方案。

beta5的博客 357

How to Recover from a Lost or Deleted Datafile with Different Scenarios [ID 198640.1]

Applies to: Oracle Server - Personal Edition - Version 10.2.0.1 and later Oracle Server - Enterprise Edition - Version 10.2.0.1 and later Oracle Server - Standard Edition - Version 10.2.0.1 and la

weiwang的专栏 1032

TMS320F280049C 学习笔记11 控制率加速器 CLA 例程

总结了TI官方在C2000Ware_3_01_00_00版本中提供的7个与CLA相关的例程。

whyorwhnt的专栏 1万+

光耦继电器电路分析

光耦电路的分析笔记

MingYue_SSS的博客 6893

RECOVERING FROM A LOST DATAFILE IN A UNDO TABLESPACE [ID 1013221.6]

RECOVERING FROM A LOST DATAFILE IN A UNDO TABLESPACE [ID 1013221.6]Problem Description: =============...

164

通过ssh进行宇树g1机器人进行连接

原因:就是g1机器人输出接口损坏,无法连接外部显示器。前期准备:本文电脑是联想Y7000p(windows)2.进入页面后:3.出现是一个压缩包,解压后:4.安装时:要注意,需要在文件夹中找到这个文件目录,然后在此鼠标右键开启终端进行安装:接着按照提示next下去即可,可以自定义安装路径。

m0_72207463的博客 1292

177.Agent-LangGraph-MCP的构成说明

MCP架构通过MCP主机、客户端与服务器的协同,实现工具、资源与提示词的统一管理。MCP服务器提供Tools、Resources、Prompts能力,MCP客户端则负责发现、调用与读取,使Agent可按需获取上下文。其核心在于将工具从Agent中抽离,封装为可复用的服务,提升灵活性与可维护性,推动AI应用高效协作。

计算机王的博客 315

RGB渐变颜色转换公式及例程

设Step=51,将RGB(255,0,0)颜色渐变为RGB(0,255,0),即RGB(255,0,0)经过51次颜色渐变转为RGB(0,255,0);设Step=51,将RGB(0,255,0)颜色渐变为RGB(0,0,255),即RGB(0,255,0)经过51次颜色渐变转为RGB(0,0,255);设Step=51,将RGB(0,0,255)颜色渐变为RGB(255,0,0),即RGB(0,0,255)经过51次颜色渐变转为RGB(255,0,0);分步骤的方式,使其渐变色循环整个颜色值;

1万+

18006.STM32通过SPI读取LAN9253数据

【代码】18006.STM32通过SPI读取LAN9253数据。

写给自己的笔记 557

Docker Compose:搭建本地多服务开发环境

从一个 Java API、PostgreSQL 和 Redis 开始,配置服务名、端口、环境变量、健康检查、数据卷和依赖关系。理解 Compose 文件如何被解析、项目如何隔离,以及哪些数据不能依赖“重建容器”保留,能够把个人开发环境整理成团队可复用的启动入口。

m0_62221377的博客 478

Unity之ASE+ShaderGraph+VFX入门到精通 目录

这里是ASE+ShaderGraph+VFX从入门到精通专栏的目录,不停更新中,有问题随时留言或者私信。

TxNet 2842

AI全栈实战 | 3.9-01 爬虫入门:requests 加 BeautifulSoup 三分钟跑通,但 90% 的人栽在这 4 个“礼貌“问题上

⚠️:本文内容仅用于。请遵守目标站点的服务条款与相关法律法规,不得对目标服务造成负担,不得采集、传播个人隐私数据。技术是中性的,边界在你手里。爬虫的入门技术(requests 发请求 + BeautifulSoup 解析)三分钟就能学会。真正区分"会用爬虫"和"会做爬虫的人"的,是这篇我们跑通"请求→解析→存储"的最小闭环,然后重点把"合规与礼貌"这根弦给你绷上。

源码获取 | 运行部署 | 定制二开修改,如果回复不及时请耐心等待,请见谅。 442

visio安装包

Microsoft Office Visio 是一款专业的制图软件。利用经过更新的形状、协作工具和数据链接图表,创建专业级图表以简化复杂的信息。

Outlook Express 6 (OE6) for Win7/Win8/Win10

英文版 Outlook Express6.0, 兼容Win7/Win10/WinXP,解压就可用。

spring boot 4集成SQLLite,及其SQLLite的建库

spring boot 4集成SQLLite,及其SQLLite的建库

weixin_41463944的博客 231

汉书HE8,好用的汇编代码编写工具

汉书HE8,好用的汇编代码编写工具早期编写汇编代码所用功能强大

【基于 WebSocket 的多用户网页五子棋】—— 数据管理模块

但行好事,莫问前程

qq_42996461的博客 260

最新的sdio 4.0 spec 协议标准 带书签 高清完整PDF版

Part-E1-SDIO-Specification-Ver4.00-Final-120220.pdf:最新的sdio 4.0 spec 协议标准 带书签 高清完整PDF版,包括SDIO 接口的外设,SDIO 接口官方协议等。

OpenClaw 深度调查:把 AI 助手做成你自己的控制平面

对 openclaw/openclaw 的完整技术尽调:Gateway 架构、Agent 运行时、工具与沙箱、30 个消息通道、181 个插件、安全信任边界、工程代价与采用建议。17 章 / 10 张图,全部数字标注口径,数据截至 2026-10-31。

厚积薄发 349

IDEA 简介:从零认识 IntelliJ IDEA

IDEA 全称,是由 JetBrains 公司开发的一款主流 Java 集成开发环境(IDE)。它集代码编辑、编译、调试、测试、版本控制、构建管理等功能于一体,被大量 Java 开发者视为日常开发的首选工具。所谓 IDE(Integrated Development Environment,集成开发环境),就是把写代码所需的各类工具整合到同一个界面中。相比只用文本编辑器加命令行编译,IDE 能显著减少重复操作,让开发者把精力集中在业务逻辑上。

ztI10的博客 185

TransactionInactiveError:await 之后事务已经不活跃了

TransactionInactiveError 出现于 IndexedDB 事务已结束(非 active 状态)时调用 put、get 等操作。常见于 await 后或 setTimeout 中执行请求,因任务已完成,事务已提交或进入 finished 状态。请求必须在创建事务的同一任务中挂起,否则无效。解决方法:在 onsuccess 回调内同步发起后续操作,或提前将所有请求挂入事务。避免在异步上下文中使用事务操作,确保请求在事务活跃期内完成。

qq_40822000的博客 159

Docker部署linkding:HTTPS与数据备份

部署私人书签库,除了让页面能够打开,还需要考虑几个实际问题:重建容器后数据是否保留、登录和保存是否正常、升级前怎样备份,以及出现 502 或 CSRF 错误时从哪里检查。本文使用 Docker Compose 部署 linkding,采用默认 SQLite 数据库,通过 Caddy 提供 HTTPS,并完成账户创建、数据持久化验证和备份。如果重建后账户或书签消失,应先检查是否换了项目目录、改了挂载路径,或者启动了另一套实例,不要急着重新初始化。遇到权限问题,应根据日志确认具体目录和运行用户,再调整权限。

tedcloud123的博客 194

云计算课程小组PPT汇报任务书

36人分为6组,每组6人;形式:生活案例调研 + 理论解读;汇报时长7‑9min;PPT建议10‑14页。

Michelle8023的博客 226

企业官网源码与数据资产交接:四项权利、五组自动检查

企业官网源码交接不仅要看压缩包,还要核域名、云资源、构建脚本、数据库恢复、权限与密钥。

sweetofmine1的博客 187

软件项目交付时,源代码之外还应该交付哪些技术资料?

软件项目交付时,除可运行程序外,还应核对源代码、数据库结构、接口文档、部署运维、测试验收和账号资产。本文从实际交付环节拆解完整资料清单,并说明合同如何约定交付范围、知识产权与维护边界。

chengduyoushu的博客 308

Java 项目实战:交友项目后台系统——网关统一鉴权与 B 端管理后台

本次改造通过引入SpringCloud Gateway实现网关统一鉴权与路由,新建mf-admin管理端复用C端域服务(Dubbo调用),实现管理员登录与用户管理四件查询。核心套路:网关白名单+双体系JWT(同工具不同payload)、管理端零直连业务库、分页形态区分(MySQL自动count/Mongo手动count)、浏览器端独立聚合。踩坑12条,附自检清单与后续改造方向,可复用于多角色后台系统。

Wang的专栏 64

大表 ALTER TABLE 怎么做?NineData OnlineDDL 配置与执行实践

介绍NineData OnlineDDL大表变更实践,涵盖执行策略、切表控制、拷贝批次与可视化进度追踪。

dbasenewbie的博客 295

Axum 初步入门学习笔记

)自定义错误实现后,可以直接作为Result的ErrAxum 是一个构建在 Tokio、Tower、Hyper 之上的 Rust Web 框架,核心能力是:Router 路由、Extractor 提取器、Tower 中间件、IntoResponse 响应机制、State 共享状态。那张分层架构图是“地图”不是“说明书”:它帮你建立全局观,但 Service、Repository、数据库、Redis、MQ 都是你自己在 Handler 内部调用的业务/基础设施层,不是 Axum 自带的。

weixin_43219667的博客 205
上一篇: Monitor ASM DG IO
下一篇: How To Restore 12cR1DB to New Host File System using RMAN(一)
cppg67031
博客等级 码龄12年 2粉丝 0原创
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符  | 博主筛选后可见
 
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明:

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

余额充值