DROP TABLESPACE UNDOTBS1 ERROR

原创

西西梅丽 2011-08-10 11:26:41 ©著作权

文章标签 职场 ERROR 休闲 DROP TABLESPACE 文章分类 Oracle 数据库

©著作权归作者所有：来自51CTO博客作者西西梅丽的原创作品，请联系作者获取转载授权，否则将追究法律责任

1、发现无法导数，估计是UNDOTBS表空间数据问题

DROP TABLESPACE UNDOTBS1 ERROR_休闲

2、创建UNDOTBS2

[oracle@node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Aug 10 10:43:01 2011

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> alter SYSTEM SET undo_tablespace ='UNDOTBS2';

System altered.

SQL> drop tablespace UNDOTBS1;
drop tablespace UNDOTBS1
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU6_2443381498$' found, terminate
dropping tablespace

SQL> SELECT a.name,b.status FROM v$rollname a, v$rollstat b
2 WHERE
3 a.name IN ( SELECT segment_name FROM dba_segments WHERE tablespace_name = 'UNDOTBS1')
4 AND a.usn = b.usn;

no rows selected

SQL> SELECT * FROM v$rollname;

USN NAME
---------- ------------------------------
0 SYSTEM
11 _SYSSMU11_665579228$
12 _SYSSMU12_3663355600$
13 _SYSSMU13_241260903$
14 _SYSSMU14_3792537020$
15 _SYSSMU15_1966240586$
16 _SYSSMU16_325510734$
17 _SYSSMU17_1498374909$
18 _SYSSMU18_1669641130$
19 _SYSSMU19_1665810980$
20 _SYSSMU20_623058522$

USN NAME
---------- ------------------------------
21 _SYSSMU21_2555004577$
22 _SYSSMU22_3119866114$

13 rows selected.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1670369280 bytes
Fixed Size 2213856 bytes
Variable Size 570427424 bytes
Database Buffers 1073741824 bytes
Redo Buffers 23986176 bytes
Database mounted.

SQL> alter database datafile '+DATA01/ncprd/datafile/undotbs1.270.757091107' offline drop;

Database altered.

SQL> alter database open;

Database altered.

SQL> drop tablespace UNDOTBS1;
drop tablespace UNDOTBS1
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU6_2443381498$' found, terminate
dropping tablespace

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

File created.

SQL> host
[oracle@node1 ~]$ vi pf.ora

ncprd1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
*.audit_file_dest='/u01/app/oracle/admin/ncprd/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.0.0'
*.control_files='+DATA01/ncprd/controlfile/current.266.757091201','+DATA01/ncprd/controlfile/current.267.757091201'
*.db_block_size=8192
*.db_cache_size=1073741824
*.db_create_file_dest='+DATA01'
*.db_domain=''
*.db_name='ncprd'
*.db_recovery_file_dest='/u01/app/oracle/oradata'
*.db_recovery_file_dest_size=10737418240
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ncprdXDB)'
ncprd1.instance_number=1
*.java_pool_size=20971520
*.large_pool_size=157286400
*.open_cursors=300
*.pga_aggregate_target=12582912000
*.processes=600
*.remote_listener='scan.ncprdgns.yuchai.com:1521'
*.remote_login_passwordfile='exclusive'
*.sessions=665
*.shared_pool_size=356515840
ncprd1.thread=1
ncprd1.undo_tablespace='UNDOTBS2'
*.undo_tablespace='UNDOTBS2'
*.rollback_segments='SYSTEM'
*.undo_management=manual
~
~
~
~
~
~
"pf.ora" 30L, 1017C written
[oracle@node1 ~]$ exit
exit

SQL> shutdown immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> statup pfile='/home/oracle/pf.ora'
SP2-0734: unknown command beginning "statup pfi..." - rest of line ignored.
SQL> startup pfile='/home/oracle/pf.ora'
ORACLE instance started.

Total System Global Area 1670369280 bytes
Fixed Size 2213856 bytes
Variable Size 570427424 bytes
Database Buffers 1073741824 bytes
Redo Buffers 23986176 bytes
Database mounted.
Database opened.
SQL> drop tablespace UNDOTBS1;
drop tablespace UNDOTBS1
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU6_2443381498$' found, terminate
dropping tablespace

SQL> select segment_name,status,tablespace_name from dba_rollback_segs;

SEGMENT_NAME STATUS TABLESPACE_NAME
------------------------------ ---------------- ------------------------------
SYSTEM ONLINE SYSTEM
_SYSSMU10_3550978943$ NEEDS RECOVERY UNDOTBS1
_SYSSMU9_1424341975$ OFFLINE UNDOTBS1
_SYSSMU8_2012382730$ NEEDS RECOVERY UNDOTBS1
_SYSSMU7_3286610060$ NEEDS RECOVERY UNDOTBS1
_SYSSMU6_2443381498$ NEEDS RECOVERY UNDOTBS1
_SYSSMU22_3119866114$ OFFLINE UNDOTBS2
_SYSSMU21_2555004577$ OFFLINE UNDOTBS2
_SYSSMU20_623058522$ OFFLINE UNDOTBS2
_SYSSMU19_1665810980$ OFFLINE UNDOTBS2
_SYSSMU18_1669641130$ OFFLINE UNDOTBS2

SEGMENT_NAME STATUS TABLESPACE_NAME
------------------------------ ---------------- ------------------------------
_SYSSMU17_1498374909$ OFFLINE UNDOTBS2
_SYSSMU16_325510734$ OFFLINE UNDOTBS2
_SYSSMU15_1966240586$ OFFLINE UNDOTBS2
_SYSSMU14_3792537020$ OFFLINE UNDOTBS2
_SYSSMU13_241260903$ OFFLINE UNDOTBS2
_SYSSMU12_3663355600$ OFFLINE UNDOTBS2
_SYSSMU11_665579228$ OFFLINE UNDOTBS2

18 rows selected.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> host
[oracle@node1 ~]$ vi pf.ora

ncprd1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
*.audit_file_dest='/u01/app/oracle/admin/ncprd/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.0.0'
*.control_files='+DATA01/ncprd/controlfile/current.266.757091201','+DATA01/ncprd/controlfile/current.267.757091201'
*.db_block_size=8192
*.db_cache_size=1073741824
*.db_create_file_dest='+DATA01'
*.db_domain=''
*.db_name='ncprd'
*.db_recovery_file_dest='/u01/app/oracle/oradata'
*.db_recovery_file_dest_size=10737418240
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ncprdXDB)'
ncprd1.instance_number=1
*.java_pool_size=20971520
*.large_pool_size=157286400
*.open_cursors=300
*.pga_aggregate_target=12582912000
*.processes=600
*.remote_listener='scan.ncprdgns.yuchai.com:1521'
*.remote_login_passwordfile='exclusive'
*.sessions=665
*.shared_pool_size=356515840
ncprd1.thread=1
ncprd1.undo_tablespace='UNDOTBS2'
*.undo_tablespace='UNDOTBS2'
*.rollback_segments='SYSTEM'
*.undo_management=manual
*._CORRUPTED_ROLLBACK_SEGMENTS =(_SYSSMU10_3550978943$,_SYSSMU9_1424341975$,_SYSSMU8_2012382730$,_SYSSMU7_3286610060$,_SYSSMU6_2443381498$)
~
~
~
~
~
"pf.ora" 31L, 1157C written
[oracle@node1 ~]$ exit
exit