Oracle Undo TTablespace recovery
oracle@apt-amd-02:/opt/app/oracle/oradata/orcl> !sql
sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Nov 3 14:46:15 2011
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Sql > update scott.emp set sal=99999;
14 rows updated.
Sql > !mv undotbs01.dbf undotbs01.orig
Sql > select * from scott.emp ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 99999 20
7499 ALLEN SALESMAN 7698 20-FEB-81 99999 300 30
7521 WARD SALESMAN 7698 22-FEB-81 99999 500 30
7566 JONES MANAGER 7839 02-APR-81 99999 20
7654 MARTIN SALESMAN 7698 28-SEP-81 99999 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 99999 30
7782 CLARK MANAGER 7839 09-JUN-81 99999 10
7788 SCOTT ANALYST 7566 19-APR-87 99999 20
7839 KING PRESIDENT 17-NOV-81 99999 10
7844 TURNER SALESMAN 7698 08-SEP-81 99999 0 30
7876 ADAMS CLERK 7788 23-MAY-87 99999 20
7900 JAMES CLERK 7698 03-DEC-81 99999 30
7902 FORD ANALYST 7566 03-DEC-81 99999 20
7934 MILLER CLERK 7782 23-JAN-82 99999 10
14 rows selected.
Sql > alter system flush sharepool;
alter system flush sharepool
*
ERROR at line 1:
ORA-02000: missing SHARED_POOL/BUFFER_CACHE/GLOBAL CONTEXT keyword
Sql > alter system flush SHARED_POOL ;
System altered.
Sql > alter system flush BUFFER_CACHE ;
14 rows selected.
Sql > rollback;
^C
^C
^Crollback
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
ORA-01116: error in opening database file 3
ORA-01110: data file 3: '/opt/app/oracle/oradata/orcl/undotbs01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-01116: error in opening database file 3
ORA-01110: data file 3: '/opt/app/oracle/oradata/orcl/undotbs01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Process ID: 20893
Session ID: 47 Serial number: 347
Sql > shu immediate;
ORA-01116: error in opening database file 3
ORA-01110: data file 3: '/opt/app/oracle/oradata/orcl/undotbs01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
/opt/app/oracle/oradata/orcl
create undo tablespace UNDOTBR datafile '/opt/app/oracle/oradata/orcl/undotbsR.dbf' size 500m autoextend on;
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
alter database datafile '/opt/app/oracle/oradata/orcl/undotbs01.dbf' offline drop;
Sql > alter database datafile '/opt/app/oracle/oradata/orcl/undotbs01.dbf' offline drop;
Database altered.
create undo tablespace UNDOTBS2
datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\UNDOTBS2' size 200M;
Sql > create undo tablespace UNDOTBR datafile '/opt/app/oracle/oradata/orcl/undotbsR.dbf' size 500m autoextend on;
create undo tablespace UNDOTBR datafile '/opt/app/oracle/oradata/orcl/undotbsR.dbf' size 500m autoextend on
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01552: cannot use system rollback segment for non-system tablespace 'USERS'
ORA-06512: at line 957
ORA-01552: cannot use system rollback segment for non-system tablespace 'USERS'
alter system set UNDO_TABLESPACE='UNDOTBR' scope=spfile;
Sql > alter system set UNDO_TABLESPACE='UNDOTBR' scope=spfile;
System altered.
Sql > create undo tablespace UNDOTBR datafile '/opt/app/oracle/oradata/orcl/undotbsR.dbf' size 500m autoextend on ;
create undo tablespace UNDOTBR datafile '/opt/app/oracle/oradata/orcl/undotbR.dbf' size 500m autoextend on
*
ERROR at line 1:
ORA-01109: database not open
Sql > select * from dba_rollback_segs;
SEGMENT_NAME OWNER TABLESPACE_NAME SEGMENT_ID FILE_ID BLOCK_ID INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE STATUS
------------------------------ ------ ------------------------------ ---------- ---------- ---------- -------------- ----------- ----------- ----------- ------------ ----------------
INSTANCE_NUM RELATIVE_FNO
---------------------------------------- ------------
SYSTEM SYS SYSTEM 0 1 128 114688 57344 1 32765 ONLINE
1
_SYSSMU1_3780397527$ PUBLIC UNDOTBS1 1 3 128 131072 65536 2 32765 NEEDS RECOVERY
3
_SYSSMU2_2232571081$ PUBLIC UNDOTBS1 2 3 144 131072 65536 2 32765 NEEDS RECOVERY
3
_SYSSMU3_2097677531$ PUBLIC UNDOTBS1 3 3 160 131072 65536 2 32765 NEEDS RECOVERY
3
_SYSSMU4_1152005954$ PUBLIC UNDOTBS1 4 3 176 131072 65536 2 32765 NEEDS RECOVERY
3
_SYSSMU5_1527469038$ PUBLIC UNDOTBS1 5 3 192 131072 65536 2 32765 NEEDS RECOVERY
3
_SYSSMU6_2443381498$ PUBLIC UNDOTBS1 6 3 208 131072 65536 2 32765 NEEDS RECOVERY
3
_SYSSMU7_3286610060$ PUBLIC UNDOTBS1 7 3 224 131072 65536 2 32765 NEEDS RECOVERY
3
_SYSSMU8_2012382730$ PUBLIC UNDOTBS1 8 3 240 131072 65536 2 32765 NEEDS RECOVERY
3
_SYSSMU9_1424341975$ PUBLIC UNDOTBS1 9 3 256 131072 65536 2 32765 NEEDS RECOVERY
3
_SYSSMU10_3550978943$ PUBLIC UNDOTBS1 10 3 272 131072 65536 2 32765 NEEDS RECOVERY
3
Sql > select tablespace_name,status from dba_tablespaces ;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
ANUJTEST ONLINE
TSAPEXF ONLINE
TSAPEXU ONLINE
TEST ONLINE
RMAN ONLINE
DROP1 ONLINE
PERFSTAT ONLINE
13 rows selected.
Sql > select file#,status from v$datafile;
FILE# STATUS
---------- -------
1 SYSTEM
2 ONLINE
3 RECOVER
4 ONLINE
5 ONLINE
6 ONLINE
7 ONLINE
8 ONLINE
9 ONLINE
10 ONLINE
11 ONLINE
12 ONLINE
12 rows selected.
Sql > select segment_name, tablespace_name, initial_extent,status from dba_rollback_segs;
SEGMENT_NAME TABLESPACE_NAME INITIAL_EXTENT STATUS
------------------------------ ------------------------------ -------------- ----------------
SYSTEM SYSTEM 114688 ONLINE
_SYSSMU10_3550978943$ UNDOTBS1 131072 NEEDS RECOVERY
_SYSSMU9_1424341975$ UNDOTBS1 131072 NEEDS RECOVERY
_SYSSMU8_2012382730$ UNDOTBS1 131072 NEEDS RECOVERY
_SYSSMU7_3286610060$ UNDOTBS1 131072 NEEDS RECOVERY
_SYSSMU6_2443381498$ UNDOTBS1 131072 NEEDS RECOVERY
_SYSSMU5_1527469038$ UNDOTBS1 131072 NEEDS RECOVERY
_SYSSMU4_1152005954$ UNDOTBS1 131072 NEEDS RECOVERY
_SYSSMU3_2097677531$ UNDOTBS1 131072 NEEDS RECOVERY
_SYSSMU2_2232571081$ UNDOTBS1 131072 NEEDS RECOVERY
_SYSSMU1_3780397527$ UNDOTBS1 131072 NEEDS RECOVERY
11 rows selected.
select segment_name,status,tablespace_name from dba_rollback_segs where status not in ('ONLINE','OFFLINE') ;
Select status ,name,file# from v$datafile where ts# in (Select ts# from v$tablespace where name='UNDOTBS1');
col NAME format a70
Select status ,name,file# from v$datafile where ts# in (Select ts# from v$tablespace where name='UNDOTBS1')
STATUS NAME FILE#
------- ---------------------------------------------------------------------- ----------
RECOVER /opt/app/oracle/oradata/orcl/undotbs01.dbf 3
Select checkpoint_change# from v$datafile_header where file_id=3
DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS;
Sql > DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS;
DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01552: cannot use system rollback segment for non-system tablespace 'USERS'
ORA-06512: at line 957
ORA-01552: cannot use system rollback segment for non-system tablespace 'USERS'
Sql > show parameters rollback
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fast_start_parallel_rollback string LOW
rollback_segments string
transactions_per_rollback_segment integer 5
Sql > show parameters undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string MANUAL
undo_retention integer 900
undo_tablespace string UNDOTBR
Sql > alter system set undo_management=auto scope=spfile;
System altered.
Sql > alter system set undo_management=manual scope=spfile;
System altered.
Sql > startup force;
_corrupted_rollback_segments=('_SYSSMU10_3550978943$','_SYSSMU9_1424341975$','_SYSSMU8_2012382730$','_SYSSMU7_3286610060$','_SYSSMU6_2443381498$','_SYSSMU5_1527469038$','_SYSSMU4_1152005954$','_SYSSMU3_2097677531$','_SYSSMU2_2232571081$','_SYSSMU1_3780397527$')
Sql > create pfile='/tmp/anuj.txt' from spfile;
File created.
Sql > ed /tmp/anuj.txt
drop rollback segment "_SYSSMU10_3550978943$";
create undo tablespace UNDOTBR datafile '/opt/app/oracle/oradata/orcl/undotbR.dbf' size 500m autoextend on ;
drop tablespace UNDOTBS1
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01552: cannot use system rollback segment for non-system tablespace 'USERS'
ORA-06512: at line 957
ORA-01552: cannot use system rollback segment for non-system tablespace 'USERS'
Sql > select segment_name, status, tablespace_name from dba_rollback_segs where status='NEEDS RECOVERY';
SEGMENT_NAME STATUS TABLESPACE_NAME
------------------------------ ---------------- ------------------------------
_SYSSMU1_3780397527$ NEEDS RECOVERY UNDOTBS1
_SYSSMU2_2232571081$ NEEDS RECOVERY UNDOTBS1
_SYSSMU3_2097677531$ NEEDS RECOVERY UNDOTBS1
_SYSSMU4_1152005954$ NEEDS RECOVERY UNDOTBS1
_SYSSMU5_1527469038$ NEEDS RECOVERY UNDOTBS1
_SYSSMU6_2443381498$ NEEDS RECOVERY UNDOTBS1
_SYSSMU7_3286610060$ NEEDS RECOVERY UNDOTBS1
_SYSSMU8_2012382730$ NEEDS RECOVERY UNDOTBS1
_SYSSMU9_1424341975$ NEEDS RECOVERY UNDOTBS1
_SYSSMU10_3550978943$ NEEDS RECOVERY UNDOTBS1
10 rows selected.
drop undo segment "_SYSSMU1_3780397527$";
drop rollback segment "_SYSSMU1_3780397527$";
drop tablespace UNDOTBS1 including contents and datafiles;
ALTER TABLESPACE UNDOTBS1 offline ;
ALTER SYSTEM SET undo_tablespace='UNDOTBR' ;
startup pfile='/tmp/anuj.txt' ;
CREATE ROLLBACK SEGMENT rbs_dummy
TABLESPACE system
STORAGE (INITIAL 10k NEXT 10k MINEXTENTS 2);
_corrupted_rollback_segments =('_SYSSMU1$','_SYSSMU2$','_SYSSMU3$','_SYSSMU4$',
'_SYSSMU5$','_SYSSMU6$','_SYSSMU7$','_SYSSMU8$','_SYSSMU9$','_SYSSMU10$')
_OFFLINE_ROLLBACK_SEGMENTS=('_SYSSMU1$','_SYSSMU2$','_SYSSMU3$','_SYSSMU4$','_SYSSMU5$','_SYSSMU6$','_S
if you get this error the set
*._system_trig_enabled=false
Sql > DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01552: cannot use system rollback segment for non-system tablespace 'USERS'
ORA-06512: at line 957
ORA-01552: cannot use system rollback segment for non-system tablespace 'USERS'
=================
orcl.__db_cache_size=205520896
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__oracle_base='/opt/app/oracle'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=293601280
orcl.__sga_target=549453824
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=322961408
orcl.__streams_pool_size=4194304
*.audit_file_dest='/opt/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/opt/app/oracle/oradata/orcl/control01.ctl','/opt/app/oracle/flash_recovery_area/orcl/control02.ctl'
*.db_block_size=8192
*.db_domain='XXXXX.co.uk'
*.db_name='orcl'
*.db_recovery_file_dest_size=4070572032
*.db_recovery_file_dest=''
*.diagnostic_dest='/opt/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.event='10468 trace name context forever, level 4'
*.fast_start_mttr_target=30
*.log_archive_dest='/opt/app/oracle/admin/arch'
*.memory_target=843055104
*.open_cursors=300
*.processes=150
*.recyclebin='OFF'
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace=UNDOTBS2
*.undo_management='MANUAL'
*._offline_rollback_segments=('_SYSSMU10_3550978943$','_SYSSMU9_1424341975$','_SYSSMU8_2012382730$','_SYSSMU7_3286610060$','_SYSSMU6_2443381498$','_SYSSMU5_1527469038$','_SYSSMU4_1152005954$','_SYSSMU3_2097677531$','_SYSSMU2_2232571081$','_SYSSMU1_3780397527$')
*._corrupted_rollback_segments=('_SYSSMU10_3550978943$','_SYSSMU9_1424341975$','_SYSSMU8_2012382730$','_SYSSMU7_3286610060$','_SYSSMU6_2443381498$','_SYSSMU5_1527469038$','_SYSSMU4_1152005954$','_SYSSMU3_2097677531$','_SYSSMU2_2232571081$','_SYSSMU1_3780397527$')
*._system_trig_enabled=false
~
Sql > startup pfile='/tmp/anuj.txt' ;
ORACLE instance started.
Total System Global Area 839282688 bytes
Fixed Size 2217992 bytes
Variable Size 629147640 bytes
Database Buffers 205520896 bytes
Redo Buffers 2396160 bytes
Database mounted.
Database opened.
Sql > drop rollback segment "_SYSSMU1_3780397527$" ;
Rollback segment dropped.
Sql > drop rollback segment "_SYSSMU2_2232571081$";
Rollback segment dropped.
Sql > drop rollback segment "_SYSSMU3_2097677531$" ;
Rollback segment dropped.
Sql > drop rollback segment "_SYSSMU4_1152005954$" ;
Rollback segment dropped.
Sql > drop rollback segment "_SYSSMU5_1527469038$" ;
Rollback segment dropped.
Sql > drop rollback segment "_SYSSMU6_2443381498$" ;
Rollback segment dropped.
Sql > drop rollback segment "_SYSSMU7_3286610060$" ;
Rollback segment dropped.
Sql > drop rollback segment "_SYSSMU8_2012382730$" ;
Rollback segment dropped.
Sql > drop rollback segment "_SYSSMU9_1424341975$" ;
Rollback segment dropped.
Sql > drop rollback segment "_SYSSMU10_3550978943$" ;
Rollback segment dropped.
Sql > select segment_name, status, tablespace_name from dba_rollback_segs where status='NEEDS RECOVERY';
View Description
V$UNDOSTAT Contains statistics for monitoring and tuning undo space. Use this view to help estimate the amount of undo
space required for the current workload. Oracle uses this view information to tune undo usage in the system.
V$ROLLSTAT For automatic undo management mode, information reflects behavior of the undo segments in the undo tablespace.
V$TRANSACTION Contains undo segment information.
DBA_UNDO_EXTENTS Shows the status and size of each extent in the undo tablespace.
WRH$_UNDOSTAT Contains statistical snapshots of V$UNDOSTAT information.
WRH$_ROLLSTAT Contains statistical snapshots of V$ROLLSTAT information.
Undo Space = UNDO_RETENTION in seconds * undo blocks for each second + overhead
No comments:
Post a Comment