Monday, September 6, 2010

Remove segment error

Kiểm tra xem có tableSpace nào offline không !
select tablespace_name,status from dba_tablespaces;

-- Tạo mới 1 Tablespce Undu mới undotbs02
create undo tablespace undotbs2 datafile
'E:\Oracle\product\10.2.0\oradata\CPWEB\undotbs02. dbf' size 50M reuse autoextend on;
-- Set là default = undotbs2
alter system set undo_tablespace=undotbs2;

SQL> select segment_name, status, tablespace_name from dba_rollback_segs where status='NEEDS RECOVERY';

SEGMENT_NAME STATUS TABLESPACE_NAME
------------------------------ ---------------- ------------------------------
_SYSSMU1$ NEEDS RECOVERY UNDOTBS1
_SYSSMU2$ NEEDS RECOVERY UNDOTBS1
_SYSSMU3$ NEEDS RECOVERY UNDOTBS1
_SYSSMU4$ NEEDS RECOVERY UNDOTBS1
_SYSSMU5$ NEEDS RECOVERY UNDOTBS1
_SYSSMU6$ NEEDS RECOVERY UNDOTBS1
_SYSSMU7$ NEEDS RECOVERY UNDOTBS1
_SYSSMU8$ NEEDS RECOVERY UNDOTBS1
_SYSSMU9$ NEEDS RECOVERY UNDOTBS1
_SYSSMU10$ NEEDS RECOVERY UNDOTBS1


10 rows selected.

SQL> show parameter undo;

NAME TYPE VALUE
------------------------------------ ----------- ---------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS2

-- Tạo pfile để startup
SQL> create pfile='init20_01_2010.ora' from spfile;
File created.
-- Sửa lại và thêm vào pfile "E:\Oracle\product\10.2.0\db_1\database\init20_01_ 2010.ora"
*.undo_tablespace='UNDOTBS2'
*._corrupted_rollback_segments=('_SYSSMU1$','_SYSS MU2$','_SYSSMU3$','_SYSSMU4$',
'_SYSSMU4$','_SYSSMU5$','_SYSSMU6$','_SYSSMU7$','_ SYSSMU8$','_SYSSMU9$','_SYSSMU10$')

-- Shutdown database
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

-- Startup với Pfile
SQL> startup restrict mount pfile="E:\Oracle\product\10.2.0\db_1\database\init 20_01_2010.ora"
ORACLE instance started.
Total System Global Area 612368384 bytes
Fixed Size 1298160 bytes
Variable Size 314573072 bytes
Database Buffers 289406976 bytes
Redo Buffers 7090176 bytes
Database mounted.

--- Kiểm tra xem có đúng làm tablespace Undo có phải là UNDOTBS1
SQL> show parameter undo;
NAME TYPE VALUE
------------------------------------ ----------- ---------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1

-- Mở DB
SQL> alter database open;
Database altered.

drop rollback segment "_SYSSMU1$";
drop rollback segment "_SYSSMU2$";
drop rollback segment "_SYSSMU3$";
drop rollback segment "_SYSSMU4$";
drop rollback segment "_SYSSMU5$";
drop rollback segment "_SYSSMU6$";
drop rollback segment "_SYSSMU7$";
drop rollback segment "_SYSSMU8$";
drop rollback segment "_SYSSMU9$";
drop rollback segment "_SYSSMU10$";
drop rollback segment "_SYSSMU21$";
drop rollback segment "_SYSSMU22$";

-- Kiểm tra lại.
SQL> select segment_name, tablespace_name,status from dba_rollback_segs;
SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE
_SYSSMU11$ UNDOTBS2 OFFLINE
_SYSSMU12$ UNDOTBS2 OFFLINE
_SYSSMU13$ UNDOTBS2 OFFLINE
_SYSSMU14$ UNDOTBS2 OFFLINE
_SYSSMU15$ UNDOTBS2 OFFLINE
_SYSSMU16$ UNDOTBS2 OFFLINE
_SYSSMU17$ UNDOTBS2 OFFLINE
_SYSSMU18$ UNDOTBS2 OFFLINE
_SYSSMU19$ UNDOTBS2 OFFLINE
_SYSSMU20$ UNDOTBS2 OFFLINE

11 rows selected.

-- Nếu không còn thấy UNDOTBS1 là có thề xóa được.
SQL> drop tablespace undotbs1;
Tablespace dropped.

-- Tạo lại UNDOTBS1
SQL> create undo tablespace undotbs1 datafile
2 'E:\Oracle\product\10.2.0\oradata\CPWEB\undotbs01. dbf' size 50M reuse autoextend on;
Tablespace created.

SQL> alter system set undo_tablespace=undotbs1;
System altered.

-- Shutdown và startup lại !

No comments: