标签云
asm恢复 bbed bootstrap$ dul In Memory kcbzib_kcrsds_1 kccpb_sanity_check_2 kfed MySQL恢复 ORA-00312 ORA-00607 ORA-00704 ORA-01110 ORA-01555 ORA-01578 ORA-08103 ORA-600 2131 ORA-600 2662 ORA-600 2663 ORA-600 3020 ORA-600 4000 ORA-600 4137 ORA-600 4193 ORA-600 4194 ORA-600 16703 ORA-600 kcbzib_kcrsds_1 ORA-600 KCLCHKBLK_4 ORA-15042 ORA-15196 ORACLE 12C oracle dul ORACLE PATCH Oracle Recovery Tools oracle加密恢复 oracle勒索 oracle勒索恢复 oracle异常恢复 Oracle 恢复 ORACLE恢复 ORACLE数据库恢复 oracle 比特币 OSD-04016 YOUR FILES ARE ENCRYPTED 勒索恢复 比特币加密文章分类
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (102)
- 数据库 (1,670)
- DB2 (22)
- MySQL (73)
- Oracle (1,532)
- Data Guard (52)
- EXADATA (8)
- GoldenGate (21)
- ORA-xxxxx (159)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (14)
- ORACLE 21C (3)
- Oracle 23ai (7)
- Oracle ASM (65)
- Oracle Bug (8)
- Oracle RAC (52)
- Oracle 安全 (6)
- Oracle 开发 (28)
- Oracle 监听 (28)
- Oracle备份恢复 (560)
- Oracle安装升级 (91)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (78)
- PostgreSQL (18)
- PostgreSQL恢复 (6)
- SQL Server (27)
- SQL Server恢复 (8)
- TimesTen (7)
- 达梦数据库 (2)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (37)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (20)
-
最近发表
- ORA-600 krse_arc_complete.4
- Oracle 19c 202410补丁(RUs+OJVM)
- ntfs MFT损坏(ntfs文件系统故障)导致oracle异常恢复
- .mkp扩展名oracle数据文件加密恢复
- 清空redo,导致ORA-27048: skgfifi: file header information is invalid
- A_H_README_TO_RECOVER勒索恢复
- 通过alert日志分析客户自行对一个数据库恢复的来龙去脉和点评
- ORA-12514: TNS: 监听进程不能解析在连接描述符中给出的SERVICE_NAME
- ORA-01092 ORA-00604 ORA-01558故障处理
- ORA-65088: database open should be retried
- Oracle 19c异常恢复—ORA-01209/ORA-65088
- ORA-600 16703故障再现
- 数据库启动报ORA-27102 OSD-00026 O/S-Error: (OS 1455)
- .[metro777@cock.li].Elbie勒索病毒加密数据库恢复
- 应用连接错误,初始化mysql数据库恢复
- RAC默认服务配置优先节点
- Oracle 19c RAC 替换私网操作
- 监听报TNS-12541 TNS-12560 TNS-00511错误
- drop tablespace xxx including contents恢复
- Linux 8 修改网卡名称
标签归档:ORA-01561
因非常规操作导致删除表空间提示ORA-01561解决办法
今天测试手工通过bbed修改undo$中回滚段状态(从status$=5修改为1)[NEEDS RECOVERY 修改为 DELETE],然后尝试删除表空间,发现不能删除
删除表空间提示ORA-01561
SQL> drop tablespace undotbs; drop tablespace undotbs * ERROR at line 1: ORA-01561: failed to remove all objects in the tablespace specified SQL> drop tablespace undotbs including contents; drop tablespace undotbs including contents * ERROR at line 1: ORA-01561: failed to remove all objects in the tablespace specified
查询相关信息
SQL> select ts#,name from v$tablespace; TS# NAME ---------- ------------------------------ 0 SYSTEM 1 SYSAUX 4 USERS 6 UNDOTBS 3 TEMP SQL> select name,ts#,status$ from undo$; NAME TS# STATUS$ ------------------------------ ---------- ---------- SYSTEM 0 3 _SYSSMU1_3138885392$ 2 1 _SYSSMU2_4228238222$ 2 1 _SYSSMU3_2210742642$ 2 1 _SYSSMU4_1455318006$ 2 1 _SYSSMU5_3787622316$ 2 1 _SYSSMU6_2460248069$ 2 1 _SYSSMU7_1924883037$ 2 1 _SYSSMU8_1909280886$ 2 1 _SYSSMU9_3593450615$ 2 1 _SYSSMU10_2490256178$ 2 1 NAME TS# STATUS$ ------------------------------ ---------- ---------- _SYSSMU11_253524401$ 6 1 _SYSSMU12_842775869$ 6 1 _SYSSMU13_2794767139$ 6 1 _SYSSMU14_2067649841$ 6 1 _SYSSMU15_3270221471$ 6 1 _SYSSMU16_4094338609$ 6 1 _SYSSMU17_709661646$ 6 1 _SYSSMU18_699588262$ 6 1 _SYSSMU19_718640828$ 6 1 _SYSSMU20_3516920665$ 6 1 _SYSSMU21_793796797$ 6 1 NAME TS# STATUS$ ------------------------------ ---------- ---------- _SYSSMU22_3988785920$ 6 1 _SYSSMU23_1828333848$ 6 1 _SYSSMU24_1223218862$ 6 1 _SYSSMU25_2939844199$ 6 1 _SYSSMU26_1317300205$ 6 1 _SYSSMU27_1654033223$ 6 1 _SYSSMU28_3748619502$ 6 1 _SYSSMU29_1868765904$ 6 1 _SYSSMU30_3379578723$ 6 1 31 rows selected. SQL> select segment_name,status from dba_rollback_segs; SEGMENT_NAME STATUS ------------------------------ ---------------- SYSTEM ONLINE
通过这里可以看出,通过bbed的修改,除system回滚段外,其他均已经被标志为delete状态,对于这样的情况,很本能的怀疑是extent或者segment未被清理掉导致
查询EXTENT和SEGMENT
SQL> select SEGMENT_NAME from dba_extents where TABLESPACE_NAME='UNDOTBS'; no rows selected SQL> select segment_name from dba_segments where TABLESPACE_NAME='UNDOTBS'; no rows selected SQL> select count(*) from seg$ where ts#=6; COUNT(*) ---------- 10 SQL> select count(*) from seg$ where ts#=2; COUNT(*) ---------- 0 SQL> select file#,type# from seg$ where ts#=6; FILE# TYPE# ---------- ---------- 3 10 3 10 3 10 3 10 3 10 3 10 3 10 3 10 3 10 3 10 10 rows selected.
通过查询我们发现SEG$中含有10条记录,而通过dbms_metadata.get_ddl分析DBA_SEGMENTS是的,得出type为10恰好是TYPE2 UNDO信息.
解决办法
删除掉这些因为手工修改undo$信息导致遗留下来的后遗症对象
SQL> delete from seg$ where ts#=6; 10 rows deleted. SQL> commit; Commit complete. SQL> drop tablespace undotbs ; Tablespace dropped.
这样的直接修改基表的做法,在一般的情况下非常不建议使用,可能带来系统不稳定.但是在数据库异常处理过程中,可能将成为一个法宝