Oracle误删Undo后恢复
来源：互联网 发布：美国合法公开海关数据 编辑：程序博客网 时间：2024/05/17 09:00
undo 表空间丢失之恢复(ORA-01548)
误删undo表空间，或者undo表空间无限增大最后需要删除 undo表空间，解决方法
1. $ startup
ORACLE 例程已经启动。
Total System Global Area 264241152 bytes
Fixed Size 1296208 bytes
Variable Size 92276912 bytes
Database Buffers 163577856 bytes
Redo Buffers 7090176 bytes
数据库装载完毕。
数据库已经打开。
已用时间: 00: 00: 00.04
2. $ shutdown abort
ORACLE 例程已经关闭。
3. 删除D:\ORACLE\PRODUCT\10.2.0\ORADATA\HUICHE\UNDOTBS01.DBF
4. $ 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'
5. $ alter database datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\HUICHE\UNDOTBS01.DBF' offline
drop;
数据库已更改。
已用时间: 00: 00: 00.07
$ alter database open;
数据库已更改。
已用时间: 00: 00: 08.12
6. $ select FILE_NAME, BYTES from dba_data_files;
FILE_NAME BYTES
------------------------------------------------------------ ----------
D:\ORACLE\PRODUCT\10.2.0\ORADATA\HUICHE\USERS01.DBF 106168320
D:\ORACLE\PRODUCT\10.2.0\ORADATA\HUICHE\SYSAUX01.DBF 262144000
D:\ORACLE\PRODUCT\10.2.0\ORADATA\HUICHE\UNDOTBS01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\HUICHE\SYSTEM01.DBF 681574400
已用时间: 00: 00: 00.12
7. $ create undo tablespace undotbs02 datafile 'D:\ORACLE\PRODUCT\10.2.0
\ORADATA\HUICHE\UNDOTBS02.DBF' size 25m;
表空间已创建。
已用时间: 00: 00: 01.32
8. $ show parameter undo
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
9. $ alter system set undo_tablespace=undotbs02;
系统已更改。
已用时间: 00: 00: 00.10
10. $ 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
11. $ drop tablespace undotbs1 including contents and datafiles;
drop tablespace undotbs1 including contents and datafiles
*
第 1 行出现错误:
ORA-01548: 已找到活动回退段 '_SYSSMU1$', 终止删除表空间
已用时间: 00: 00: 00.04
12. $ 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
13. $ 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
数据库装载完毕。
数据库已经打开。
14. $ 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
15. $ drop tablespace undotbs1 including contents and datafiles;
表空间已删除。
已用时间: 00: 00: 01.39
16. $ 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
17. $ show parameter undo
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS02
$ create spfile from pfile;
文件已创建。
已用时间: 00: 00: 00.12
18. $ shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
19. $ startup
ORACLE 例程已经启动。
Total System Global Area 264241152 bytes
Fixed Size 1296208 bytes
Variable Size 92276912 bytes
Database Buffers 163577856 bytes
Redo Buffers 7090176 bytes
数据库装载完毕。
数据库已经打开。
20. $ show parameter undo
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
undo_management string MANUAL
undo_retention integer 900
undo_tablespace string UNDOTBS02
$ alter system set undo_management=auto scope=spfile;
系统已更改。
已用时间: 00: 00: 00.03
误删undo表空间，或者undo表空间无限增大最后需要删除 undo表空间，解决方法
1. $ startup
ORACLE 例程已经启动。
Total System Global Area 264241152 bytes
Fixed Size 1296208 bytes
Variable Size 92276912 bytes
Database Buffers 163577856 bytes
Redo Buffers 7090176 bytes
数据库装载完毕。
数据库已经打开。
已用时间: 00: 00: 00.04
2. $ shutdown abort
ORACLE 例程已经关闭。
3. 删除D:\ORACLE\PRODUCT\10.2.0\ORADATA\HUICHE\UNDOTBS01.DBF
4. $ 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'
5. $ alter database datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\HUICHE\UNDOTBS01.DBF' offline
drop;
数据库已更改。
已用时间: 00: 00: 00.07
$ alter database open;
数据库已更改。
已用时间: 00: 00: 08.12
6. $ select FILE_NAME, BYTES from dba_data_files;
FILE_NAME BYTES
------------------------------------------------------------ ----------
D:\ORACLE\PRODUCT\10.2.0\ORADATA\HUICHE\USERS01.DBF 106168320
D:\ORACLE\PRODUCT\10.2.0\ORADATA\HUICHE\SYSAUX01.DBF 262144000
D:\ORACLE\PRODUCT\10.2.0\ORADATA\HUICHE\UNDOTBS01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\HUICHE\SYSTEM01.DBF 681574400
已用时间: 00: 00: 00.12
7. $ create undo tablespace undotbs02 datafile 'D:\ORACLE\PRODUCT\10.2.0
\ORADATA\HUICHE\UNDOTBS02.DBF' size 25m;
表空间已创建。
已用时间: 00: 00: 01.32
8. $ show parameter undo
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
9. $ alter system set undo_tablespace=undotbs02;
系统已更改。
已用时间: 00: 00: 00.10
10. $ 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
11. $ drop tablespace undotbs1 including contents and datafiles;
drop tablespace undotbs1 including contents and datafiles
*
第 1 行出现错误:
ORA-01548: 已找到活动回退段 '_SYSSMU1$', 终止删除表空间
已用时间: 00: 00: 00.04
12. $ 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
13. $ 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
数据库装载完毕。
数据库已经打开。
14. $ 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
15. $ drop tablespace undotbs1 including contents and datafiles;
表空间已删除。
已用时间: 00: 00: 01.39
16. $ 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
17. $ show parameter undo
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS02
$ create spfile from pfile;
文件已创建。
已用时间: 00: 00: 00.12
18. $ shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
19. $ startup
ORACLE 例程已经启动。
Total System Global Area 264241152 bytes
Fixed Size 1296208 bytes
Variable Size 92276912 bytes
Database Buffers 163577856 bytes
Redo Buffers 7090176 bytes
数据库装载完毕。
数据库已经打开。
20. $ show parameter undo
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
undo_management string MANUAL
undo_retention integer 900
undo_tablespace string UNDOTBS02
$ alter system set undo_management=auto scope=spfile;
系统已更改。
已用时间: 00: 00: 00.03
0 0
- Oracle误删Undo后恢复
- 丢失undo表空间后恢复
- Oracle Undo tablespace恢复（无备份）
- Oracle误删TABLE后，恢复的解决方法
- 怎么恢复损坏后的undo 表空间（一）
- 重装系统后，Oracle恢复
- 【Oracle】undo损坏，无备份非常规恢复
- Oracle中利用undo进行数据的恢复操作
- Oracle Undo
- Oracle undo
- 重装系统后恢复oracle数据库
- 重装系统后ORACLE的恢复
- oracle 删除后，数据恢复
- oracle update commit 后恢复
- 重装系统后ORACLE的恢复
- 重装系统后ORACLE数据库恢复
- 重装系统后恢复oracle数据
- Oracle DG failover 后恢复
- AOP思想
- 下载安装CheckStyle-IDEA插件
- apue第三版学习环境的配置
- WebForms UnobtrusiveValidationMode 需要“jquery”ScriptResourceMapping。请添加一个名为 jquery (区分大小写)的 ScriptRes
- Android动画3-属性动画(PropertyAnimation)
- Oracle误删Undo后恢复
- [H5]如何减少网页的内存与CPU占用
- Intersection of Two Linked Lists
- 51NOD 1434 区间LCM（素数筛）
- IOS-定时器的创建
- codeforce之Bear and Two Paths
- 用BufferedReader或者writer时要注意close
- [Android]反射的使用及如何防止反射
- 推荐 | 电信业网络质量实时分析模型解决方案
