标签云
asm 恢复 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 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)
- 操作系统 (100)
- 数据库 (1,589)
- DB2 (22)
- MySQL (70)
- Oracle (1,459)
- Data Guard (49)
- EXADATA (7)
- GoldenGate (21)
- ORA-xxxxx (158)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (13)
- ORACLE 21C (3)
- Oracle ASM (65)
- Oracle Bug (7)
- Oracle RAC (47)
- Oracle 安全 (6)
- Oracle 开发 (27)
- Oracle 监听 (27)
- Oracle备份恢复 (526)
- Oracle安装升级 (83)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (75)
- PostgreSQL (13)
- PostgreSQL恢复 (3)
- SQL Server (27)
- SQL Server恢复 (8)
- TimesTen (7)
- 达梦数据库 (2)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (36)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (19)
-
最近发表
- ORA-00600: internal error code, arguments: [16703], [1403], [4] 原因
- 最近遇到几起ORA-600 16703故障(tab$被清空),请引起重视
- ORA-600 2662快速恢复之Patch scn工具
- TNS-12518: TNS:listener could not hand off client connection
- ora.storage无法启动报ORA-12514故障处理
- 断电引起文件scn异常数据库恢复
- ORA-16188: LOG_ARCHIVE_CONFIG settings inconsistent with previously started instance
- .[hudsonL@cock.li].mkp勒索加密数据库完美恢复
- 模拟带库实现rman远程备份
- 又一例:ORA-600 kclchkblk_4和2662故障
- Oracle误删除数据文件恢复
- Oracle 19C 备库DML重定向—DML Redirection
- ORA-01595/ORA-600 4194处理
- 从ORA-00283 ORA-16433报错开始恢复
- 近期又遇到ORA-600 16703和ORA-702故障
- RECOVER_YOUR_DATA勒索恢复
- ORA-01033: ORACLE initialization or shutdown in progress 故障处理
- Oracle 19c/21c最新patch信息-202401
- 存储故障,强制拉库报ORA-600 kcbzib_kcrsds_1处理
- ORA-600 kcrf_resilver_log_1故障处理
标签归档: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.
这样的直接修改基表的做法,在一般的情况下非常不建议使用,可能带来系统不稳定.但是在数据库异常处理过程中,可能将成为一个法宝