undo 表空间丢失之恢复(ORA-01548)

最新推荐文章于 2021-11-03 23:40:56 发布
最新推荐文章于 2021-11-03 23:40:56 发布
阅读量143 收藏
点赞数
分类专栏： oracle备份与恢复 文章标签： Oracle Flash Cache 活动

SYS@huiche>startup
ORACLE 例程已经启动。
Total System Global Area  264241152 bytes
Fixed Size                  1296208 bytes
Variable Size              92276912 bytes
Database Buffers          163577856 bytes
Redo Buffers                7090176 bytes
数据库装载完毕。
数据库已经打开。
SYS@huiche>desc test;
 名称                                                                                                                                               是否为空? 类型
 -------------------------------------------------------------------------------------------------------------------------------------------------- -------- ---------------------------------------------------------------------------------------------------
 ID                                                                                                                                                          NUMBER(10)
 NAME                                                                                                                                                        VARCHAR2(20)

SYS@huiche>select * from test;

未选定行

已用时间:  00: 00: 00.12
SYS@huiche>set wrap off;
SYS@huiche>select * from v$rollstat;
       USN      LATCH    EXTENTS     RSSIZE     WRITES      XACTS       GETS      WAITS    OPTSIZE    HWMSIZE    SHRINKS      WRAPS    EXTENDS  AVESHRINK  AVEACTIVE STATUS                                                           CUREXT     CURBLK
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------------------------------------------------------ ---------- ----------
         0          0          6     385024       5000          0         47          0                385024          0          0          0          0          0 ONLINE                                                                0          2
         1          1         17    2088960        670          0         13          0               2088960          0          0          0          0          0 ONLINE                                                               10        109
         2          2          4     253952        796          0         15          0                253952          0          0          0          0          0 ONLINE                                                                0          3
         3          0         17    2088960       2412          0         15          0               2088960          0          0          0          0          0 ONLINE                                                               10        126
         4          1         17    2088960        772          0         13          0               2088960          0          0          0          0          0 ONLINE                                                               11          6
         5          2          5     319488        358          0         13          0                319488          0          0          0          0          0 ONLINE                                                                2          4
         6          0         12    2744320       1022          0         17          0               2744320          0          0          0          0          0 ONLINE                                                               11        125
         7          1          4     253952        228          0         11          0                253952          0          0          0          0          0 ONLINE                                                                2          3
         8          2         18    2154496       1820          0         13          0               2154496          0          0          0          0          0 ONLINE                                                               13          5
         9          0          3     188416       1594          0         17          0                188416          0          0          0          0          0 ONLINE                                                                2          7
        10          1          5     319488        664          0         13          0                319488          0          0          0          0          0 ONLINE                                                                1          4

已选择11行。

已用时间:  00: 00: 00.06
SYS@huiche>insert into test values (1,'libin');

已创建 1 行。

已用时间:  00: 00: 00.03
SYS@huiche>select * from v$rollstat;
       USN      LATCH    EXTENTS     RSSIZE     WRITES      XACTS       GETS      WAITS    OPTSIZE    HWMSIZE    SHRINKS      WRAPS    EXTENDS  AVESHRINK  AVEACTIVE STATUS                                                           CUREXT     CURBLK
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------------------------------------------------------ ---------- ----------
         0          0          6     385024       5000          0         48          0                385024          0          0          0          0          0 ONLINE                                                                0          2
         1          1         17    2088960       1520          0         16          0               2088960          0          0          0          0          0 ONLINE                                                               10        109
         2          2          4     253952        796          0         16          0                253952          0          0          0          0          0 ONLINE                                                                0          3
         3          0         17    2088960       2542          0         18          0               2088960          0          0          0          0          0 ONLINE                                                               10        126
         4          1         17    2088960        772          0         14          0               2088960          0          0          0          0          0 ONLINE                                                               11          6
         5          2          5     319488        878          0         16          0                319488          0          0          0          0          0 ONLINE                                                                2          4
         6          0         12    2744320       1386          0         20          0               2744320          0          0          0          0          0 ONLINE                                                               11        125
         7          1          4     253952        582          0         14          0                253952          0          0          0          0          0 ONLINE                                                                2          3
         8          2         18    2154496       1926          1            15          0               2154496          0          0          0          0          0 ONLINE                                                               13          5
         9          0          3     188416       2346          0         20          0                188416          0          0          0          0          0 ONLINE                                                                2          7
        10          1          5     319488        878          0         16          0                319488          0          0          0          0          0 ONLINE                                                                1          4

已选择11行。

已用时间:  00: 00: 00.04
SYS@huiche>shutdown abort
ORACLE 例程已经关闭。
删除D:\ORACLE\PRODUCT\10.2.0\ORADATA\HUICHE\UNDOTBS01.DBF
SYS@huiche>startup
ORACLE 例程已经启动。
Total System Global Area  264241152 bytes
Fixed Size                  1296208 bytes
Variable Size              92276912 bytes
Database Buffers          163577856 bytes
Redo Buffers                7090176 bytes
数据库装载完毕。
ORA-01157: 无法标识/锁定数据文件 2 - 请参阅 DBWR 跟踪文件
ORA-01110: 数据文件 2: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\HUICHE\UNDOTBS01.DBF'


SYS@huiche>alter database datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\HUICHE\UNDOTBS01.DBF' offline drop;

数据库已更改。

已用时间:  00: 00: 00.07
SYS@huiche>alter database open;

数据库已更改。

已用时间:  00: 00: 08.12
SYS@huiche>select * from dba_data_files;
在列 MAXBLOCKS 前截断 (按要求)

在列 INCREMENT_BY 前截断 (按要求)

在列 USER_BYTES 前截断 (按要求)

在列 USER_BLOCKS 前截断 (按要求)

FILE_NAME                                                       FILE_ID TABLESPACE_NAME                                                   BYTES     BLOCKS STATUS                                                       RELATIVE_FNO AUTOEX   MAXBYTES ONLINE_ST
------------------------------------------------------------ ---------- ------------------------------------------------------------ ---------- ---------- ------------------------------------------------------------ ------------ ------ ---------- ---------
D:\ORACLE\PRODUCT\10.2.0\ORADATA\HUICHE\USERS01.DBF                   4 USERS                                                         106168320      12960 AVAILABLE                                                               4 YES    3.4360E+10 ONLINE
D:\ORACLE\PRODUCT\10.2.0\ORADATA\HUICHE\SYSAUX01.DBF                  3 SYSAUX                                                        262144000      32000 AVAILABLE                                                               3 YES    3.4360E+10 ONLINE
D:\ORACLE\PRODUCT\10.2.0\ORADATA\HUICHE\UNDOTBS01.DBF                 2 UNDOTBS1                                                                           AVAILABLE                                                               2                   RECOVER
D:\ORACLE\PRODUCT\10.2.0\ORADATA\HUICHE\SYSTEM01.DBF                  1 SYSTEM                                                        681574400      83200 AVAILABLE                                                               1 YES    3.4360E+10 SYSTEM

已用时间:  00: 00: 00.12
SYS@huiche>create undo tablespace undotbs02 datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\HUICHE\UNDOTBS02.DBF' size 25m;

表空间已创建。

已用时间:  00: 00: 01.32
SYS@huiche>show parameter undo
NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
undo_management                      string                 AUTO
undo_retention                       integer                900
undo_tablespace                      string                 UNDOTBS1
SYS@huiche>alter system set undo_tablespace=undotbs02;

系统已更改。

已用时间:  00: 00: 00.10
SYS@huiche>select segment_name,status from dba_rollback_segs;
SEGMENT_NAME                                                 STATUS
------------------------------------------------------------ ------------------------------------------------------------
SYSTEM                                                       ONLINE
_SYSSMU10$                                                   NEEDS RECOVERY
_SYSSMU9$                                                    NEEDS RECOVERY
_SYSSMU8$                                                    NEEDS RECOVERY
_SYSSMU7$                                                    NEEDS RECOVERY
_SYSSMU6$                                                    NEEDS RECOVERY
_SYSSMU5$                                                    NEEDS RECOVERY
_SYSSMU4$                                                    NEEDS RECOVERY
_SYSSMU3$                                                    NEEDS RECOVERY
_SYSSMU2$                                                    NEEDS RECOVERY
_SYSSMU1$                                                    NEEDS RECOVERY
_SYSSMU20$                                                   ONLINE
_SYSSMU19$                                                   ONLINE
_SYSSMU18$                                                   ONLINE
_SYSSMU17$                                                   ONLINE
_SYSSMU16$                                                   ONLINE
_SYSSMU15$                                                   ONLINE
_SYSSMU14$                                                   ONLINE
_SYSSMU13$                                                   ONLINE
_SYSSMU12$                                                   ONLINE
_SYSSMU11$                                                   ONLINE

已选择21行。

已用时间:  00: 00: 00.07
SYS@huiche>drop tablespace undotbs1 including contents and datafiles;
drop tablespace undotbs1 including contents and datafiles
*
第 1 行出现错误:
ORA-01548: 已找到活动回退段 '_SYSSMU1$', 终止删除表空间


已用时间:  00: 00: 00.04
SYS@huiche>create pfile from spfile;

 

pfile内容如下:

huiche.__db_cache_size=163577856
huiche.__java_pool_size=4194304
huiche.__large_pool_size=4194304
huiche.__shared_pool_size=83886080
huiche.__streams_pool_size=0

*.audit_file_dest='D:\oracle\product\10.2.0\admin\HUICHE\adump'
*.background_dump_dest='D:\oracle\product\10.2.0\admin\HUICHE\bdump'
*.compatible='10.2.0.3.0'
*.control_files='D:\oracle\product\10.2.0\oradata\HUICHE\control01.ctl','D:\oracle\product\10.2.0\oradata\HUICHE\control02.ctl','D:\oracle\product\10.2.0\oradata\HUICHE\control03.ctl'
*.core_dump_dest='D:\oracle\product\10.2.0\admin\HUICHE\cdump'
*.db_block_size=8192
*.db_cache_size=113246208
*.db_domain='COM'
*.db_file_multiblock_read_count=16
*.db_name='HUICHE'
*.db_recovery_file_dest='D:\oracle\product\10.2.0\flash_recovery_area'
*.db_recovery_file_dest_size=4294967296
*.dispatchers='(PROTOCOL=TCP) (SERVICE=HUICHEXDB)'
*.java_pool_size=4194304
*.job_queue_processes=10
*.large_pool_size=4194304
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.open_cursors=300
*.pga_aggregate_target=69206016
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_max_size=262144000
*.sga_target=264241152
*.shared_pool_size=79691776
*.streams_pool_size=0
*.undo_management='auto'
*.undo_tablespace='UNDOTBS02'
*.user_dump_dest='D:\oracle\product\10.2.0\admin\HUICHE\udump'
_corrupted_rollback_segments=(_SYSSMU10$,_SYSSMU9$,_SYSSMU8$,_SYSSMU7$,_SYSSMU6$,_SYSSMU5$,_SYSSMU4$,_SYSSMU3$,_SYSSMU2$,_SYSSMU1$)
_offline_rollback_segments=true

 

SYS@huiche>startup pfile=D:\oracle\product\10.2.0\db_1\database\INIThuiche.ORA
ORACLE 例程已经启动。
Total System Global Area  264241152 bytes
Fixed Size                  1296208 bytes
Variable Size              92276912 bytes
Database Buffers          163577856 bytes
Redo Buffers                7090176 bytes
数据库装载完毕。
数据库已经打开。
SYS@huiche>select segment_name,status from dba_rollback_segs ;
SEGMENT_NAME                                                 STATUS
------------------------------------------------------------ ------------------------------------------------------------
SYSTEM                                                       ONLINE
_SYSSMU10$                                                   NEEDS RECOVERY
_SYSSMU9$                                                    NEEDS RECOVERY
_SYSSMU8$                                                    NEEDS RECOVERY
_SYSSMU7$                                                    NEEDS RECOVERY
_SYSSMU6$                                                    NEEDS RECOVERY
_SYSSMU5$                                                    NEEDS RECOVERY
_SYSSMU4$                                                    NEEDS RECOVERY
_SYSSMU3$                                                    NEEDS RECOVERY
_SYSSMU2$                                                    NEEDS RECOVERY
_SYSSMU1$                                                    NEEDS RECOVERY
_SYSSMU20$                                                   ONLINE
_SYSSMU19$                                                   ONLINE
_SYSSMU18$                                                   ONLINE
_SYSSMU17$                                                   ONLINE
_SYSSMU16$                                                   ONLINE
_SYSSMU15$                                                   ONLINE
_SYSSMU14$                                                   ONLINE
_SYSSMU13$                                                   ONLINE
_SYSSMU12$                                                   ONLINE
_SYSSMU11$                                                   ONLINE

已选择21行。

已用时间:  00: 00: 00.31
SYS@huiche>drop tablespace undotbs1 including contents and datafiles;

表空间已删除。

已用时间:  00: 00: 01.39
SYS@huiche>select segment_name,status from dba_rollback_segs;
SEGMENT_NAME                                                 STATUS
------------------------------------------------------------ ------------------------------------------------------------
SYSTEM                                                       ONLINE
_SYSSMU20$                                                   ONLINE
_SYSSMU19$                                                   ONLINE
_SYSSMU18$                                                   ONLINE
_SYSSMU17$                                                   ONLINE
_SYSSMU16$                                                   ONLINE
_SYSSMU15$                                                   ONLINE
_SYSSMU14$                                                   ONLINE
_SYSSMU13$                                                   ONLINE
_SYSSMU12$                                                   ONLINE
_SYSSMU11$                                                   ONLINE

已选择11行。

已用时间:  00: 00: 00.04
SYS@huiche>show parameter undo
NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
undo_management                      string                 AUTO
undo_retention                       integer                900
undo_tablespace                      string                 UNDOTBS02
SYS@huiche>create spfile from pfile;

文件已创建。

已用时间:  00: 00: 00.12
SYS@huiche>shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SYS@huiche>startup
ORACLE 例程已经启动。
Total System Global Area  264241152 bytes
Fixed Size                  1296208 bytes
Variable Size              92276912 bytes
Database Buffers          163577856 bytes
Redo Buffers                7090176 bytes
数据库装载完毕。
数据库已经打开。
SYS@huiche>show parameter undo
NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
undo_management                      string                 MANUAL
undo_retention                       integer                900
undo_tablespace                      string                 UNDOTBS02
SYS@huiche>alter system set undo_management=auto scope=spfile;

系统已更改。

已用时间:  00: 00: 00.03

 

lb517290079
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
总结 ？ORA-01548: 已找到活动回退段 '_SYSSMU1$', 终止删除表空间？
mengyan_0808的专栏
12-09 1775
<br /><br />总结 ？ORA-01548: 已找到活动回退段 '_SYSSMU1$', 终止删除表空间？<br />出现的原因：由于服务器自动重新启动造成回滚事务没有及时提交造成数据库块的损坏,及后面造成一系列的问题。<br /> <br /> /*  ：<br /> Wed Dec 08 15:20:03 2010<br />Errors in file d:/oracle/product/10.2.0/admin/taxdev/bdump/taxdev_smon_2572.trc:<br />
【Oracle问题集6】ORA-01548: active rollback segment XX found, terminate dropping tablespace
梦&dream
06-20 2392
说明：【oracle问题集】是博主平时操作数据库遇到的问题以及解决方案。 执行语句：表示操作数据库过程 出错现象：表示操作数据库过程出现错误现象 出错原因：表示出现错误的原因 解决方法：表示解决错误的方法 相关知识：表示与这个主题相关的知识 ============================================================= 执行语句
一次ORA-01548错误的处理
cuge240620的博客
02-03 231
背景：客户生产系统数据库因为断电导致数据库启来后台ALERT中出现大量undo needs recovery报错操作系统版本:AIX 5.3数据库版本: 10.0.2.3 RAC+ASM 问题： 后台ALERT中出现...
ORA-01548: 已找到活动回退段‘_SYSSMU1$‘，终止删除表空间 的解决办法
weixin_50800763的博客
11-03 1189
最近在操作数据库删除还原表空间时遇到此问题，通过查询网上方法没的有一次性很好解决，又或是操作步骤不够明白，无从下手，因此将自己解决的步骤记录下来，方便后续查看。 1 删除损坏数据文件所在表空间 1.1 故障现象 SQL> drop tablespace undotbs1 including contents and datafiles; drop tablespace undotbs1 including contents and datafiles * ERROR ...
undo表空间恢复
09-28
标题和描述均提到了“undo表...综上所述，undo表空间的管理和恢复是Oracle数据库管理员必须掌握的关键技能之一，它直接影响到数据的完整性和系统的稳定性。通过合理配置和及时维护，可以有效提升数据库的性能和可靠性。
Oracle undo_management参数不一致错误
09-10
3. UNDO_MANAGEMENT参数：此参数控制是否自动管理undo空间。设置为"AUTO"表示使用自动管理的undo表空间，Oracle会自动分配和管理undo段。如果设置为"MANUAL"，则需要手动管理undo表空间。 4. AUTOEXTEND选项：当...
一次简单的Oracle恢复Case实战记录
12-16
5. **重建UNDO表空间**：如果回滚段是问题根源，重建UNDO可以解决问题，但需谨慎操作，避免数据丢失。 6. **检查数据库状态**：数据库打开后，定期监控alert log，确保没有新的错误出现。 7. **执行DBCC检查**：使用...
oracle 错误一览表
08-12
- **解决方法**：检查撤销表空间的状态，并采取措施恢复其有效性。 #### ORA-00034: Unable to open current PL/SQL undo tablespace - **描述**：无法打开当前的PL/SQL撤销表空间。 - **解决方法**：确认撤销表...
ORA-01548: 已找到活动回退段 '_SYSSMU6_1294219927$', 终止删除表空间
pinklotus的专栏
10-26 1206
我想重建一个重做日志表空间，于是新增了一个UNDO表空间UNDOTBS2，想删除原有的UNDO表空间UNDOTBS1，不料在删除过程中碰到了ORA-01548异常，问题解决如下。 1、查看日志： SQL> SELECT SEGMENT_NAME,STATUS FROM DBA_ROLLBACK_SEGS where tablespace_name='UNDOTBS1' ----------结
解决删除undo表空间时的ORA-01548错误
cuiji5713的博客
02-21 465
今天有个同学碰到这个问题，帮他解决了下 它的undotbs1表空间有问题，想要删除它，碰到问题。 SQL>droptablespaceundotbs1includingcontent...
ORA-01548: 已找到活动回退段'_SYSSMU1$'，终止删除表空间 的解决办法
hotye393的专栏
02-09 8373
<br />出现原因：<br /> 在oracle的服务期控制台直接进行了数据文件的脱离的操作，提示如下：ORA-01145:除非启用了介质恢复,否则不允许紧急脱机<br /> ALTER DATABASE <br />     DATAFILE 'E:/ORACLE/ORADATA/SHAOMF/UNDOTBS01.DBF' OFFLINE DROP;<br /><br /> 症状：<br /> 删除回滚段表空间（drop tablespace undotbs1 including conten
Oracle删除表空间报ORA01548
weixin_30940783的博客
07-18 148
由于undo表空间设置了自动增长，导致替换了好几个undo表空间，就想把原先的undo表空间删掉腾出空间 但删的时候报错 SQL> drop tablespace undotbs1 including contents and datafiles;drop tablespace undotbs1 including contents and datafiles*ERROR at line...
ORA-01548: 已找到活动回退段'_SYSSMU1$'，终止删除表空间 解决办法
05-25 241
ORA-01548: 已找到活动回退段'_SYSSMU1$'，终止删除表空间 解决办法[@more@]验证过程：1 查看所有回滚段信息：select segment_name,status,tablespace_name fro...
oracle 快照过旧:回退段号,Oracle错误代码大全(8)
weixin_39894932的博客
04-15 2944
ORA-01530:例程已安装数据库ORA-01531:例程已打开数据库ORA-01532:无法创建数据库；例程在他处启动ORA-01533:无法重命名文件''；文件不属于表空间ORA-01534:回退段''不存在ORA-01535:回退段''已经存在ORA-01536:超出表空间''的空间限量ORA-01537:无法添加数据文件''-文件已是数据库的一部分ORA-01538:...
ORA-01548删除undo表空间处理
qingfengfeixin的专栏
03-24 510
在新建了表空间jinlian_undo之后，并切换为默认undo表空间，不小心删除了创建的dbf文件，但是无法删除jinlian_undo表空间。 查询相关资料进行以下方式解决： 1.删除表空间报错 SQL> drop tablespace jinlian_undo including contents   2  ; drop tablespace jinlian_undo includ
删除undo表空间报ORA-01548
weiwang的专栏
04-08 5418
回归段中活动的事物删除undo表空间报ORA-01548 问题解决如下： 用expdp导出数据报错: C:\Documents and Settings\TEMP>expdp scott/tiger DUMPFILE=scott.dmp DIRECTORY=du mp SCHEMAS=scott Export: Release 11.2.0.1.0 - Production on 星期一
Oracle中的ORA-01548: active rollback segment '_SYSSMU1$' found
weixin_34331102的博客
06-26 106
Oracle中的ORA-01548: active rollback segment '_SYSSMU1$' found接上文《Oracle Undo tablespace恢复（无备份）》http://tiany.blog.51cto.com/513694/1431287 1、在创建新的undo tablesapce “undotbs2”后，删除旧的undo tablesp...
删除归档表空间、删除归档文件导致的报错 ORA-08881 ORA-01548
不再疯要傻
05-08 1852
 郑重声明：由于本人水平有限，如有不对的地方请大神指正不甚感激（327568824@qq.com） 1.1.1删除归档表空间、删除归档文件导致的报错 1.1.1.1 问题及现象   RMAN> backup database;   Starting backup at 08-MAY-15 using channel ORA_DISK_1
更改tbm 10G undo表空间出现ora-00604 ora-00376 ora-01110错误(二)
最新发布
05-13
1. 首先备份该 undo 表空间对应的数据文件，以防止数据丢失。 2. 尝试在线修复该 undo 表空间，你可以执行以下 SQL 语句： ``` ALTER TABLESPACE <undo_tablespace_name> BEGIN BACKUP; ALTER TABLESPACE <undo...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值