DATABASE RECOVERY: NOBACKUP NOARCHIVELOGS
Background & Overview
Some sites seem to feel their development environments are expendable. They do not require a backup, or it costs too much to backup all of the development environments. Comments like 'we can't afford the SAN space' or "the tapes will cost too much" or even, "we cannot afford the backup infrastructure" and the old chestnut "we don't need the data, we can recreate it".
But when the inevitable happens and the database goes bang, those same people that said they did not need a backup are the same people hammering on your door asking the DBA to recover the database.
Description of Problem
A site recently suffered a SAN hardware failure and had, for all of the above reason, decided not to take database backups. What is more (because that SAN space cost money you know) they had also turned off archiving. But thanks to a particularly diligent DBA and a big dollop of luck, the database was successfully recovered and brought back to a usable state.
Recovery of a Corrupt Database due to SAN Failure (No backups, No archive logs).
Once the SAN failure was rectified the following process was followed to recover the database.
Inital Findings
The following were the issues identified when trying to start the database.
- Control file older than the datafile’s
- Media recovery for system.dbf datafiles
- UNDO tablespace corruption
Attempting to start the database produced the following error
SQL> startup mount;
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed Size 2085360 bytes
Variable Size 721423888 bytes
Database Buffers 1409286144 bytes
Redo Buffers 14688256 bytes
Database mounted
SQL>
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01122: database file 3 failed verification check
ORA-01110: data file 3: '/u03/oradata/JSDRPW01/sysaux/d1/sysaux_01.dbf'
ORA-01207: file is more recent than control file - old control file
Issuing a recover database caused the following
SQL> reco
ver database;
ORA-00283: recovery session cancelled due to errors
ORA-01122: database file 3 failed verification check
ORA-01110: data file 3: '/u03/oradata/JSDRPW01/sysaux/d1/sysaux_01.dbf'
ORA-01207: file is more recent than control file - old control file
A select from v$recover_file produced the following output.
SQL> select * from v$recover_file;
FILE# ONLINE_Status ERROR CHANGE# TIME
---- ------------- ------------- --------- -------------
3 ONLINE UNKNOWN ERROR 1000561381 10/05/2010 08:53:02
7 ONLINE UNKNOWN ERROR 1000561381 10/05/2010 08:53:02
8 ONLINE UNKNOWN ERROR 1000494902 10/05/2010 08:48:04
10 ONLINE UNKNOWN ERROR 1000494902 10/05/2010 08:48:04
15 ONLINE UNKNOWN ERROR 1000494902 10/05/2010 08:48:04
16 ONLINE UNKNOWN ERROR 1000494902 10/05/2010 08:48:04
22 ONLINE UNKNOWN ERROR 1000516345 10/05/2010 08:49:53
23 ONLINE UNKNOWN ERROR 1000516345 10/05/2010 08:49:53
24 ONLINE UNKNOWN ERROR 1000561381 10/05/2010 08:53:00
28 ONLINE UNKNOWN ERROR 1000494902 10/05/2010 08:48:04
32 ONLINE UNKNOWN ERROR 1000494902 10/05/2010 08:48:04
35 ONLINE UNKNOWN ERROR 1000561381 10/05/2010 08:53:02
38 ONLINE UNKNOWN ERROR 1000494902 10/05/2010 08:48:04
39 ONLINE UNKNOWN ERROR 1000494902 10/05/2010 08:48:04
40 ONLINE UNKNOWN ERROR 1000494902 10/05/2010 08:48:04
41 ONLINE UNKNOWN ERROR 1000494902 10/05/2010 08:48:04
43 ONLINE UNKNOWN ERROR 1000494902 10/05/2010 08:48:04
It seems the all the datafiles listed above were out of sync of controlfile and control file needed to be rebuild.
The Recovery
At a high level the following steps were performned.
- Recreate database controlfile
- Recover database using Backup controlfile.
- Open database using Resetlogs option
- Open database after UNDO corruption
- Dropping UNDO with active _SYS rollback segment
- Run DBVerify utility
Recreate database controlfile
Use the following procedure to rebuild the controlfile.
Sqlplus / as sysdba
SQL> alter database backup controlfile to trace;
Database altered.
Find the controlfile trace file and edit it we are going to recreate controlfile with restlogs options.
Backing up current control file which will be used for recovery.
SQL> alter database backup controlfile to '/u03/oradata/JSDRPW01/users/d1/control05.ctl';
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL>
Move/rename the existing current controlfiles.
mv /u03/oradata/JSDRPW01/users/d1/control01.ctl /u03/oradata/JSDRPW01/users/d1/control01.ctl.org
mv /u03/oradata/JSDRPW01/tools/d1/control02.ctl /u03/oradata/JSDRPW01/tools/d1/control02.ctl.org
mv /u03/oradata/JSDRPW01/indx/d1/control03.ctl /u03/oradata/JSDRPW01/indx/d1/control03.ctl.org
SQL>
oracle@stevux1185:JSDRPW01> sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Mon May 10 19:50:47 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed Size 2085360 bytes
Variable Size 721423888 bytes
Database Buffers 1409286144 bytes
Redo Buffers 14688256 bytes
SQL>
Create PFILE from SPFILE
SQL> create pfile from spfile;
File created.
SQL> stutdown abort;
Recreate Control file with Resetlogs option -
vi cre_ctlReset.sql
"cre_ctlReset.sql" 74 lines, 3643 characters
CREATE CONTROLFILE REUSE DATABASE "JSDRPW01" RESETLOGS NOARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 4
MAXDATAFILES 1024
MAXINSTANCES 1
MAXLOGHISTORY 4672
LOGFILE
GROUP 1 '/u02/oradata/JSDRPW01/redo/d1/redo001a.dbf' SIZE 50M,
GROUP 2 '/u02/oradata/JSDRPW01/redo/d1/redo002a.dbf' SIZE 50M,
GROUP 3 '/u02/oradata/JSDRPW01/redo/d1/redo003a.dbf' SIZE 50M,
GROUP 4 '/u02/oradata/JSDRPW01/redo/d1/redo004a.dbf' SIZE 50M
— STANDBY LOGFILE
DATAFILE
'/u03/oradata/JSDRPW01/system/d1/system_01.dbf',
'/u04/oradata/JSDRPW01/undo/d1/undo_01.dbf',
'/u03/oradata/JSDRPW01/sysaux/d1/sysaux_01.dbf',
'/u03/oradata/JSDRPW01/tools/d1/tools_01.dbf',
'/u03/oradata/JSDRPW01/users/d1/users_01.dbf',
'/u03/oradata/JSDRPW01/indx/d1/indx_01.dbf',
'/u07/oradata/JSDRPW01/data/d1/wms_data_01.dbf',
'/u07/oradata/JSDRPW01/archive/d1/wms_archive_01.dbf',
'/u07/oradata/JSDRPW01/audit/d1/wms_audit_01.dbf',
'/u08/oradata/JSDRPW01/indexes/d1/wms_indexes_01.dbf',
'/u07/oradata/JSDRPW01/tblsync/d1/wms_tblsync_01.dbf',
'/u07/oradata/JSDRPW01/media/d1/wms_media_01.dbf',
'/u07/oradata/JSDRPW01/report/d1/wms_report_01.dbf',
'/u07/oradata/JSDRPW01/archive/d1/wms_archive_02.dbf',
'/u07/oradata/JSDRPW01/data/d1/wms_data_02.dbf',
'/u08/oradata/JSDRPW01/indexes/d1/wms_indexes_02.dbf',
'/u07/oradata/JSDRPW01/archive/d1/wms_archive_03.dbf',
'/u07/oradata/JSDRPW01/archive/d1/wms_archive_04.dbf',
'/u07/oradata/JSDRPW01/archive/d1/wms_archive_05.dbf',
'/u07/oradata/JSDRPW01/archive/d1/wms_archive_06.dbf',
'/u07/oradata/JSDRPW01/archive/d1/wms_archive_07.dbf',
'/u07/oradata/JSDRPW01/data/d1/wms_data_03.dbf',
'/u07/oradata/JSDRPW01/data/d1/wms_data_04.dbf',
'/u08/oradata/JSDRPW01/indexes/d1/wms_indexes_03.dbf',
'/u07/oradata/JSDRPW01/archive/d1/wms_archive_08.dbf',
'/u07/oradata/JSDRPW01/archive/d1/wms_archive_09.dbf',
'/u07/oradata/JSDRPW01/archive/d1/wms_archive_10.dbf',
'/u07/oradata/JSDRPW01/archive/d1/wms_archive_11.dbf',
'/u04/oradata/JSDRPW01/undo/d1/undo_02.dbf',
'/u03/oradata/JSDRPW01/tools/d1/streamstbs01.dbf',
'/u03/oradata/JSDRPW01/tools/d1/strmslogmnr_tbs01.dbf',
'/u07/oradata/JSDRPW01/archive/d1/wms_archive_13.dbf',
'/u07/oradata/JSDRPW01/archive/d1/wms_archive_12.dbf',
'/u07/oradata/JSDRPW01/archive/d1/wms_archive_14.dbf',
'/u07/oradata/JSDRPW01/data/d1/wms_DATA_05.dbf',
'/u07/oradata/JSDRPW01/archive/d1/wms_archive_15.dbf',
'/u07/oradata/JSDRPW01/archive/d1/wms_archive_16.dbf',
'/u07/oradata/JSDRPW01/archive/d1/wms_archive_17.dbf',
'/u07/oradata/JSDRPW01/archive/d1/wms_archive_18.dbf',
'/u07/oradata/JSDRPW01/data/d1/wms_DATA_06.dbf',
'/u07/oradata/JSDRPW01/data/d1/wms_DATA_07.dbf',
'/u07/oradata/JSDRPW01/archive/d1/wms_archive_19.dbf',
'/u07/oradata/JSDRPW01/archive/d1/wms_archive_20.dbf'
CHARACTER SET AL32UTF8
;
— Commands to re-create incarnation table
— Below log names MUST be changed to existing filenames on
— disk. Any one log file from each branch can be used to
— re-create incarnation records.
— ALTER DATABASE REGISTER LOGFILE '/u06/oradata/JSDRPW01/arch/d1/arch_661967745_1_1.log';
— Recovery is required if any of the datafiles are restored backups,
— or if the last shutdown was not normal or immediate.
— RECOVER DATABASE USING BACKUP CONTROLFILE
— Database can now be opened zeroing the online logs.
— ALTER DATABASE OPEN RESETLOGS;
— Commands to add tempfiles to temporary tablespaces.
— Online tempfiles have complete space information.
— Other tempfiles may require adjustment.
— ALTER TABLESPACE TEMP ADD TEMPFILE '/u05/oradata/JSDRPW01/temp/d1/temp_01.dbf' REUSE;
— End of tempfile additions.
—
"cre_ctlReset.sql" 74 lines, 3649 characters
oracle@stevux1185:JSDRPW01> sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Mon May 10 20:35:33 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to an idle instance.
Start the database in nomount mode.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed Size 2085360 bytes
Variable Size 721423888 bytes
Database Buffers 1409286144 bytes
Redo Buffers 14688256 bytes
SQL> @cre_ctlReset.sql
Control file created.
Try to open the database
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u03/oradata/JSDRPW01/system/d1/system_01.dbf'
Database open fails with the media recovery needed for system_01.dbf errors.
2. Recover database using Backup controlfile.
Recover database using backup controlfile option and provide On Line REDO log file names when prompted for Archived logs. Find the REDO logfiles with complete path which will be used instead of Archived Logs when prompted.
At prompt for archive logs specific REDO log filenames with complete path.
SQL> recover database until cancel using backup controlfile;
ORA-00279: change 1000681834 generated at 05/10/2010 20:22:18 needed for thread
1
ORA-00289: suggestion :
/u06/oradata/JSDRPW01/arch/d1/arch_661967745_1_50800.log
ORA-00280: change 1000681834 for thread 1 is in sequence #50800
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u02/oradata/JSDRPW01/redo/d1/redo001a.dbf
ORA-00310: archived log contains sequence 50797; sequence 50800 required
ORA-00334: archived log: '/u02/oradata/JSDRPW01/redo/d1/redo001a.dbf'
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u03/oradata/JSDRPW01/system/d1/system_01.dbf'
SQL> recover database until cancel using backup controlfile;
ORA-00279: change 1000681834 generated at 05/10/2010 20:22:18 needed for thread
1
ORA-00289: suggestion :
/u06/oradata/JSDRPW01/arch/d1/arch_661967745_1_50800.log
ORA-00280: change 1000681834 for thread 1 is in sequence #50800
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u02/oradata/JSDRPW01/redo/d1/redo002a.dbf
ORA-00310: archived log contains sequence 50798; sequence 50800 required
ORA-00334: archived log: '/u02/oradata/JSDRPW01/redo/d1/redo002a.dbf'
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u03/oradata/JSDRPW01/system/d1/system_01.dbf'
SQL> recover database until cancel using backup controlfile;
ORA-00279: change 1000681834 generated at 05/10/2010 20:22:18 needed for thread
1
ORA-00289: suggestion :
/u06/oradata/JSDRPW01/arch/d1/arch_661967745_1_50800.log
ORA-00280: change 1000681834 for thread 1 is in sequence #50800
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u02/oradata/JSDRPW01/redo/d1/redo003a.dbf
ORA-00310: archived log contains sequence 50799; sequence 50800 required
ORA-00334: archived log: '/u02/oradata/JSDRPW01/redo/d1/redo003a.dbf'
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u03/oradata/JSDRPW01/system/d1/system_01.dbf'
SQL> recover database until cancel using backup controlfile;
ORA-00279: change 1000681834 generated at 05/10/2010 20:22:18 needed for thread
1
ORA-00289: suggestion :
/u06/oradata/JSDRPW01/arch/d1/arch_661967745_1_50800.log
ORA-00280: change 1000681834 for thread 1 is in sequence #50800
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u02/oradata/JSDRPW01/redo/d1/redo004a.dbf
Log applied.
Media recovery complete.
3. Open database using Resetlogs option
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
oracle@stevux1185:JSDRPW01> sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Mon May 10 20:43:27 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed Size 2085360 bytes
Variable Size 721423888 bytes
Database Buffers 1409286144 bytes
Redo Buffers 14688256 bytes
Database mounted.
ORA-00603: ORACLE server session terminated by fatal error
ALERT LOG ERRORS
Parallel Transaction recovery caught error 600
error 600 detected in background process
ORA-00600: internal error code, arguments: [4193], [46679], [28124], [], [], [], [],
Errors in file /u01/app/oracle/JSDRPW01/admin/JSDRPW01/udump/jsdrpw01_ora_25078.trc:
ORA-00600: internal error code, arguments: [4193], [46679], [28124], [], [], [], [],
ORA-00600: internal error code, arguments: [4193], [46679], [28124], [], [], [], [],
4. Open database after UNDO corruption
The media recovery is completed successfully and now controlfile and datafiles are in sync but still when the database is tried to be opened the instance crashes due to the SYS undo rollback segment corruption. To resolve this issue follow the underline workaround steps.
Create and Edit pfile - *.undo_management='MANUAL'
Open the database with UNDO management set to MANUAL
Create rollback temporary rollback segments
Create new UNDO tablespace
Drop the OLD UNDO tablespace
Restart the database
Create PFILE
SQL> startup nomount
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed Size 2085360 bytes
Variable Size 721423888 bytes
Database Buffers 1409286144 bytes
Redo Buffers 14688256 bytes
SQL> create pfile from spfile;
SQL> shutdown abort;
Open the database with UNDO management set to MANUAL
SETTING UNDO TO MANUAL
vi initJSDRPW01.ora
"initJSDRPW01.ora" 33 lines, 1154 characters
JSDRPW01.db_cache_size=1409286144
JSDRPW01.java_pool_size=16777216
JSDRPW01.large_pool_size=16777216
JSDRPW01.shared_pool_size=469762048
JSDRPW01.__streams_pool_size=218103808
*.aq_tm_processes=0
*.audit_file_dest='/u01/app/oracle/JSDRPW01/admin/JSDRPW01/adump'
*.background_dump_dest='/u01/app/oracle/JSDRPW01/admin/JSDRPW01/bdump'
*.control_files='/u03/oradata/JSDRPW01/users/d1/control01.ctl'
*.core_dump_dest='/u01/app/oracle/JSDRPW01/admin/JSDRPW01/cdump'
*.db_block_size=8192
*.db_file_multiblock_read_count=16
*.db_name='JSDRPW01'
*.global_names=TRUE
*.job_queue_processes=2
*.log_archive_dest='/u06/oradata/JSDRPW01/arch/d1'
*.log_archive_format='arch_%r_%t_%s.log'
*.log_checkpoint_timeout=1200
*.log_checkpoints_to_alert=TRUE
*.nls_length_semantics='CHAR'
*.open_cursors=500
*.optimizer_mode='choose'
*.pga_aggregate_target=1048576000
*.processes=1000
*.remote_login_passwordfile='EXCLUSIVE'
*.resource_manager_plan=''
*.sga_max_size=2147483648
*.sga_target=2147483648
*.streams_pool_size=218103808
*.undo_management='MANUAL'
*.undo_retention=6000
*.undo_tablespace='UNDO'
*.user_dump_dest='/u01/app/oracle/JSDRPW01/admin/JSDRPW01/udump'
Open the database using PFILE option
oracle@stevux1185:JSDRPW01> sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Mon May 10 21:07:07 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup mount pfile='/u01/app/oracle/JSDRPW01/product/10.2.0/dbs/initJSDRPW01.ora'
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed Size 2085360 bytes
Variable Size 721423888 bytes
Database Buffers 1409286144 bytes
Redo Buffers 14688256 bytes
Database mounted.
SQL> Show parameter undo
NAME TYPE VALUE
-------- --- ------
undo_management string MANUAL
undo_retention integer 6000
undo_tablespace string UNDO
SQL>
SQL> Alter database open ;
Database altered.
Create temporary rollback segment to create new UNDO tablespace.
SQL> Create rollback segment r01 ;
Rollback segment created.
SQL> Alter rollback segment r01 online ;
Create a new undo tablespace
SQL> CREATE SMALLFILE UNDO TABLESPACE "UNDOTP" DATAFILE '/u02/oradata/JSDRPW01/redo/d1/undotp_01.dbf' SIZE 100M REUSE ;
Tablespace created.
SQL> drop tablespace undo including contents and datafiles;
drop tablespace undo including contents and datafiles
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU2$' found, terminate dropping
Tablespace
Restart the database with UNDO management AUTO and UNDO tablespace set to ‘UNDOTP’ tablespace.
SQL> create spfile from pfile;
SQL> alter system set undo_management='AUTO' scope=SPFILE;
SQL> Alter system set undo_tablespace=UNDOTP scope=spfile;
system altered.
SQL> Shutdown immediate ;
SQL> startup
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed Size 2085360 bytes
Variable Size 721423888 bytes
Database Buffers 1409286144 bytes
Redo Buffers 14688256 bytes
Database mounted.
Database opened.
SQL> show parameter undo
NAME TYPE VALUE
-------- --- ------
undo_management string AUTO
undo_retention integer 600
undo_tablespace string UNDOTP
SQL>
Note : The database has opened successfully with alternate UNDO tablespace this proves the point the instance was crashing after media recovery due to corrupt UNDO tablespace.
Now the objective is to drop the corrupt UNDO tablespace and recreate it but the trouble is you cannot drop the UNDO tablespace due to active SYS rollback segment expecting media recovery.
Force UNDO offline does not work
Drop UNDO tablespace does not work
Drop SYS _SYS roll
Now try to drop the old UNDO tablespace
SQL>
SQL> ALTER TABLESPACE undo OFFLINE;
Tablespace altered.
SQL> DROP TABLESPACE UNDO INCLUDING CONTENTS and datafiles;
DROP TABLESPACE UNDO INCLUDING CONTENTS and datafiles
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU2$' found, terminate dropping
Tablespace
SQL> select segment_name,status,tablespace_name from dba_rollback_segs where
status='NEEDS RECOVERY';
SEGMENT_NAME STATUS TABLESPACE_NAME
------ ---- ------
_SYSSMU2$ NEEDS RECOVERY UNDO
SQL> select SEGMENT_NAME,STATUS,TABLESPACE_NAME from dba_rollback_segs;
SEGMENT_NAME STATUS TABLESPACE_NAME
------ ---- ------
SYSTEM ONLINE SYSTEM
_SYSSMU2$ NEEDS RECOVERY UNDO
_SYSSMU3$ OFFLINE UNDO
_SYSSMU4$ OFFLINE UNDO
_SYSSMU5$ OFFLINE UNDO
_SYSSMU6$ OFFLINE UNDO
_SYSSMU7$ OFFLINE UNDO
_SYSSMU8$ OFFLINE UNDO
_SYSSMU9$ OFFLINE UNDO
_SYSSMU10$ OFFLINE UNDO
_SYSSMU11$ OFFLINE UNDO
_SYSSMU12$ OFFLINE UNDO
_SYSSMU13$ OFFLINE UNDO
_SYSSMU14$ OFFLINE UNDO
_SYSSMU15$ OFFLINE UNDO
_SYSSMU16$ OFFLINE UNDO
_SYSSMU17$ OFFLINE UNDO
_SYSSMU18$ OFFLINE UNDO
_SYSSMU19$ OFFLINE UNDO
_SYSSMU20$ OFFLINE UNDO
_SYSSMU21$ OFFLINE UNDO
R01 ONLINE SYSTEM
_SYSSMU23$ ONLINE UNDOTP
_SYSSMU24$ ONLINE UNDOTP
_SYSSMU25$ ONLINE UNDOTP
_SYSSMU26$ ONLINE UNDOTP
_SYSSMU27$ ONLINE UNDOTP
_SYSSMU28$ ONLINE UNDOTP
_SYSSMU29$ ONLINE UNDOTP
_SYSSMU30$ ONLINE UNDOTP
_SYSSMU31$ ONLINE UNDOTP
_SYSSMU32$ ONLINE UNDOTP
_SYSSMU33$ ONLINE UNDOTP
_SYSSMU34$ ONLINE UNDOTP
_SYSSMU35$ ONLINE UNDOTP
_SYSSMU36$ ONLINE UNDOTP
_SYSSMU37$ ONLINE UNDOTP
_SYSSMU38$ ONLINE UNDOTP
_SYSSMU39$ ONLINE UNDOTP
_SYSSMU40$ ONLINE UNDOTP
_SYSSMU41$ ONLINE UNDOTP
_SYSSMU42$ ONLINE UNDOTP
SQL> alter tablespace UNDO offline immediate;
alter tablespace UNDO offline immediate
*
ERROR at line 1:
ORA-01145: offline immediate disallowed unless media recovery enabled
Try Force UNDO offline – doesn’t work
Offline normal
SQL> alter tablespace UNDO offline;
Tablespace altered.
Try to drop UNDO tablespace
SQL> DROP tablespace UNDO;
DROP tablespace UNDO
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU2$' found, terminate dropping
tablespace
SQL> DROP rollback segment '_SYSSMU2$';
DROP rollback segment '_SYSSMU2$'
ERROR at line 1:
ORA-02175: invalid rollback segment name
Try to drop SYS rollback segment which is not possible.
5. Dropping UNDO with active _SYS rollback segment
To successfully drop the corrupt UNDO tablespace with its active SYS ‘_SYSSMU2$’ undo rollback segment follow the workaround:
1) Create PFILE if you started with database with spfile.
SQL> CREATE PFILE FROM SPFILE;
File created.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
2) Edit pfile and set undo management to manual and to offline SYS rollback segment put the entries of the undo segments in the pfile by using the following statement in the pfile:
undo_management = manual
_offline_rollback_segments=(_SYSSMU2$)
OFFLINE _SYS ROLLBACKSEGMENT
oracle@stevux1185:JSDRPW01> vi initJSDRPW01.ora
"initJSDRPW01.ora" 33 lines, 1155 characters
JSDRPW01.db_cache_size=1409286144
JSDRPW01.java_pool_size=16777216
JSDRPW01.large_pool_size=16777216
JSDRPW01.shared_pool_size=469762048
JSDRPW01.__streams_pool_size=218103808
*.aq_tm_processes=0
*.audit_file_dest='/u01/app/oracle/JSDRPW01/admin/JSDRPW01/adump'
*.background_dump_dest='/u01/app/oracle/JSDRPW01/admin/JSDRPW01/bdump'
*.control_files='/u03/oradata/JSDRPW01/users/d1/control01.ctl'
*.core_dump_dest='/u01/app/oracle/JSDRPW01/admin/JSDRPW01/cdump'
*.db_block_size=8192
*.db_file_multiblock_read_count=16
*.db_name='JSDRPW01'
*.global_names=TRUE
*.job_queue_processes=2
*.log_archive_dest='/u06/oradata/JSDRPW01/arch/d1'
*.log_archive_format='arch_%r_%t_%s.log'
*.log_checkpoint_timeout=1200
*.log_checkpoints_to_alert=TRUE
*.nls_length_semantics='CHAR'
*.open_cursors=500
*.optimizer_mode='choose'
*.pga_aggregate_target=1048576000
*.processes=1000
*.remote_login_passwordfile='EXCLUSIVE'
*.resource_manager_plan=''
*.sga_max_size=2147483648
*.sga_target=2147483648
*.streams_pool_size=218103808
*.undo_management='MANUAL'
*.undo_retention=600
*.undo_tablespace='UNDOTP'
*.user_dump_dest='/u01/app/oracle/JSDRPW01/admin/JSDRPW01/udump'
*._offline_rollback_segments=(_SYSSMU2$)
3) Start the database in mount mode with PFILE
SQL> startup mount pfile='/u01/app/oracle/JSDRPW01/product/10.2.0/dbs/initJSDRPW01.ora'
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed Size 2085360 bytes
Variable Size 721423888 bytes
Database Buffers 1409286144 bytes
Redo Buffers 14688256 bytes
Database mounted.
4) Now drop the datafiles for old UNDO tablespace
SQL> Alter Database datafile '&filename' offline drop;
Enter value for filename: /u04/oradata/JSDRPW01/undo/d1/undo_01.dbf
old 1: Alter Database datafile '&filename' offline drop
new 1: Alter Database datafile '/u04/oradata/JSDRPW01/undo/d1/undo_01.dbf' offline drop
Database altered.
SQL> /
Enter value for filename: /u04/oradata/JSDRPW01/undo/d1/undo_02.dbf
old 1: Alter Database datafile '&filename' offline drop
new 1: Alter Database datafile '/u04/oradata/JSDRPW01/undo/d1/undo_02.dbf' offline drop
Database altered.
5) Open the database
SQL> Alter Database Open;
Database altered.
6) Try to drop the SYS undo segment
SQL> Drop Rollback Segment '_SYSSMU2$';
Drop Rollback Segment '_SYSSMU2$'
ERROR at line 1:
ORA-00911: invalid character
(Still Cannot drop no problem)
7) Force offline UNDO tablespace and then drop the UNDO tablespace
SQL> alter tablespace UNDO offline immediate;
Tablespace altered.
Now the old UNDO tablespace can be dropped without getting SYS undo segment error
SQL> DROP TABLESPACE UNDO INCLUDING CONTENTS and datafiles;
Tablespace dropped.
SQL> select SEGMENT_NAME,STATUS,TABLESPACE_NAME from dba_rollback_segs;
SEGMENT_NAME STATUS TABLESPACE_NAME
------ ---- ------
SYSTEM ONLINE SYSTEM
R01 ONLINE SYSTEM
_SYSSMU23$ OFFLINE UNDOTP
_SYSSMU24$ OFFLINE UNDOTP
_SYSSMU25$ OFFLINE UNDOTP
_SYSSMU26$ OFFLINE UNDOTP
_SYSSMU27$ OFFLINE UNDOTP
_SYSSMU28$ OFFLINE UNDOTP
_SYSSMU29$ OFFLINE UNDOTP
_SYSSMU30$ OFFLINE UNDOTP
_SYSSMU31$ OFFLINE UNDOTP
_SYSSMU32$ OFFLINE UNDOTP
_SYSSMU33$ OFFLINE UNDOTP
_SYSSMU34$ OFFLINE UNDOTP
_SYSSMU35$ OFFLINE UNDOTP
_SYSSMU36$ OFFLINE UNDOTP
_SYSSMU37$ OFFLINE UNDOTP
_SYSSMU38$ OFFLINE UNDOTP
_SYSSMU39$ OFFLINE UNDOTP
_SYSSMU40$ OFFLINE UNDOTP
_SYSSMU41$ OFFLINE UNDOTP
_SYSSMU42$ OFFLINE UNDOTP
22 rows selected.
8) Recreate dropped UNDO tablespace
SQL> CREATE SMALLFILE UNDO TABLESPACE "UNDO" DATAFILE '/u04/oradata/JSDRPW01/undo/d1/undo_01.dbf' SIZE 5120M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 32767M;
Tablespace created.
SQL> alter tablespace UNDO add datafile '/u04/oradata/JSDRPW01/undo/d1/undo_02.dbf' size 500M reuse autoextend on next 10M maxsize 5120M;
Tablespace altered.
9) Add Tempfile (which we couldn’t add after controlfile recreate )
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u05/oradata/JSDRPW01/temp/d1/temp_01.dbf' REUSE;
Tablespace altered.
SQL>
SQL> select * from v$recover_file;
no rows selected
SQL> show parameter undo
NAME TYPE VALUE
-------- --- ------
undo_management string MANUAL
undo_retention integer 600
undo_tablespace string UNDOTP
SQL> select SEGMENT_NAME,STATUS,TABLESPACE_NAME from dba_rollback_segs;
SEGMENT_NAME STATUS TABLESPACE_NAME
------ ---- ------
SYSTEM ONLINE SYSTEM
_SYSSMU23$ OFFLINE UNDOTP
_SYSSMU24$ OFFLINE UNDOTP
_SYSSMU25$ OFFLINE UNDOTP
_SYSSMU26$ OFFLINE UNDOTP
_SYSSMU27$ OFFLINE UNDOTP
_SYSSMU28$ OFFLINE UNDOTP
_SYSSMU29$ OFFLINE UNDOTP
_SYSSMU30$ OFFLINE UNDOTP
_SYSSMU31$ OFFLINE UNDOTP
_SYSSMU32$ OFFLINE UNDOTP
_SYSSMU33$ OFFLINE UNDOTP
_SYSSMU34$ OFFLINE UNDOTP
_SYSSMU35$ OFFLINE UNDOTP
_SYSSMU36$ OFFLINE UNDOTP
_SYSSMU37$ OFFLINE UNDOTP
_SYSSMU38$ OFFLINE UNDOTP
_SYSSMU39$ OFFLINE UNDOTP
_SYSSMU40$ OFFLINE UNDOTP
_SYSSMU41$ OFFLINE UNDOTP
_SYSSMU42$ OFFLINE UNDOTP
10) Change the PFILE parameters back to “undo_management=AUTO” and modify the parameter “undo_tablespace=UNDO” and remove the “_offline_rollback_segments” parameter.
SQL> Shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
oracle@stevux1185:JSDRPW01> vi initJSDRPW01.ora
"initJSDRPW01.ora" 33 lines, 1155 characters
JSDRPW01.db_cache_size=1409286144
JSDRPW01.java_pool_size=16777216
JSDRPW01.large_pool_size=16777216
JSDRPW01.shared_pool_size=469762048
JSDRPW01.__streams_pool_size=218103808
*.aq_tm_processes=0
*.audit_file_dest='/u01/app/oracle/JSDRPW01/admin/JSDRPW01/adump'
*.background_dump_dest='/u01/app/oracle/JSDRPW01/admin/JSDRPW01/bdump'
*.control_files='/u03/oradata/JSDRPW01/users/d1/control01.ctl'
*.core_dump_dest='/u01/app/oracle/JSDRPW01/admin/JSDRPW01/cdump'
*.db_block_size=8192
*.db_file_multiblock_read_count=16
*.db_name='JSDRPW01'
*.global_names=TRUE
*.job_queue_processes=2
*.log_archive_dest='/u06/oradata/JSDRPW01/arch/d1'
*.log_archive_format='arch_%r_%t_%s.log'
*.log_checkpoint_timeout=1200
*.log_checkpoints_to_alert=TRUE
*.nls_length_semantics='CHAR'
*.open_cursors=500
*.optimizer_mode='choose'
*.pga_aggregate_target=1048576000
*.processes=1000
*.remote_login_passwordfile='EXCLUSIVE'
*.resource_manager_plan=''
*.sga_max_size=2147483648
*.sga_target=2147483648
*.streams_pool_size=218103808
*.undo_management='AUTO'
*.undo_retention=600
*.undo_tablespace='UNDO'
*.user_dump_dest='/u01/app/oracle/JSDRPW01/admin/JSDRPW01/udump'
11) Start the database with updated PFILE.
SQL> startup pfile='/u01/app/oracle/JSDRPW01/product/10.2.0/dbs/initJSDRPW01.ora'
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed Size 2085360 bytes
Variable Size 721423888 bytes
Database Buffers 1409286144 bytes
Redo Buffers 14688256 bytes
Database mounted.
Database opened.
SQL>
12) Create SPFILE
SQL> create spfile from pfile;
File created.
Drop temporary rollback segment which we created earlier.
SQL> drop rollback segment R01;
Rollback segment dropped.
SQL> select SEGMENT_NAME,STATUS,TABLESPACE_NAME from dba_rollback_segs;
SEGMENT_NAME STATUS TABLESPACE_NAME
------ ---- ------
SYSTEM ONLINE SYSTEM
_SYSSMU1$ ONLINE UNDO
_SYSSMU2$ ONLINE UNDO
_SYSSMU3$ ONLINE UNDO
_SYSSMU4$ ONLINE UNDO
_SYSSMU5$ ONLINE UNDO
_SYSSMU6$ ONLINE UNDO
_SYSSMU7$ ONLINE UNDO
_SYSSMU8$ ONLINE UNDO
_SYSSMU9$ ONLINE UNDO
_SYSSMU10$ ONLINE UNDO
_SYSSMU11$ ONLINE UNDO
_SYSSMU12$ ONLINE UNDO
_SYSSMU13$ ONLINE UNDO
_SYSSMU14$ ONLINE UNDO
_SYSSMU15$ ONLINE UNDO
_SYSSMU16$ ONLINE UNDO
_SYSSMU17$ ONLINE UNDO
_SYSSMU18$ ONLINE UNDO
_SYSSMU19$ ONLINE UNDO
_SYSSMU20$ ONLINE UNDO
_SYSSMU23$ OFFLINE UNDOTP
_SYSSMU24$ OFFLINE UNDOTP
_SYSSMU25$ OFFLINE UNDOTP
_SYSSMU26$ OFFLINE UNDOTP
_SYSSMU27$ OFFLINE UNDOTP
_SYSSMU28$ OFFLINE UNDOTP
_SYSSMU29$ OFFLINE UNDOTP
_SYSSMU30$ OFFLINE UNDOTP
_SYSSMU31$ OFFLINE UNDOTP
_SYSSMU32$ OFFLINE UNDOTP
_SYSSMU33$ OFFLINE UNDOTP
_SYSSMU34$ OFFLINE UNDOTP
_SYSSMU35$ OFFLINE UNDOTP
_SYSSMU36$ OFFLINE UNDOTP
_SYSSMU37$ OFFLINE UNDOTP
_SYSSMU38$ OFFLINE UNDOTP
_SYSSMU39$ OFFLINE UNDOTP
_SYSSMU40$ OFFLINE UNDOTP
_SYSSMU41$ OFFLINE UNDOTP
_SYSSMU42$ OFFLINE UNDOTP
41 rows selected.
13) Drop the temporary UNDOTP tablespace
SQL> alter tablespace UNDOTP offline;
Tablespace altered.
SQL> DROP TABLESPACE UNDOTP INCLUDING CONTENTS and datafiles;
Tablespace dropped.
SQL> select SEGMENT_NAME,STATUS,TABLESPACE_NAME from dba_rollback_segs;
SEGMENT_NAME STATUS TABLESPACE_NAME
------ ---- ------
SYSTEM ONLINE SYSTEM
_SYSSMU1$ ONLINE UNDO
_SYSSMU2$ ONLINE UNDO
_SYSSMU3$ ONLINE UNDO
_SYSSMU4$ ONLINE UNDO
_SYSSMU5$ ONLINE UNDO
_SYSSMU6$ ONLINE UNDO
_SYSSMU7$ ONLINE UNDO
_SYSSMU8$ ONLINE UNDO
_SYSSMU9$ ONLINE UNDO
_SYSSMU10$ ONLINE UNDO
_SYSSMU11$ ONLINE UNDO
_SYSSMU12$ ONLINE UNDO
_SYSSMU13$ ONLINE UNDO
_SYSSMU14$ ONLINE UNDO
_SYSSMU15$ ONLINE UNDO
_SYSSMU16$ ONLINE UNDO
_SYSSMU17$ ONLINE UNDO
_SYSSMU18$ ONLINE UNDO
_SYSSMU19$ ONLINE UNDO
_SYSSMU20$ ONLINE UNDO
21 rows selected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
6. RUN DBVerify utility
Run DBVerify for all the datafiles to ensure the datafile’s do not have any corruptions.
oracle@stevux1185:JSDRPW01> dbv file=/u03/oradata/JSDRPW01/system/d1/system_01.dbf blocksize=8192 feedback=100
DBVERIFY: Release 10.2.0.4.0 - Production on Tue May 11 12:31:01 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
DBVERIFY - Verification starting : FILE = /u03/oradata/JSDRPW01/system/d1/system_01.dbf
……………………………………………………………………..
……………………………………………………………………..
……………………………………………………………………..
……………………………………………………………………..
……………………………………………………………………..
……………………………………………………………………..
……………………………………………………………………..
………………………………………………………………..
DBVERIFY - Verification complete
Total Pages Examined : 64000
Total Pages Processed (Data) : 21734
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 8196
Total Pages Failing (Index): 0
Total Pages Processed (Other): 9244
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 24826
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Highest block SCN : 254681191 (0.254681191)
oracle@stevux1185:JSDRPW01> dbv file=/u03/oradata/JSDRPW01/system/d1/system_01.dbf logfile=dbvsystem01dbf.log blocksize=8192 feedback=100
oracle@stevux1185:JSDRPW01> dbv file=/u07/oradata/JSDRPW01/data/d1/wms_data_01.dbf logfile=datadbv01dbf.log blocksize=8192 feedback=100
oracle@stevux1185:JSDRPW01> dbv file=/u07/oradata/JSDRPW01/data/d1/wms_data_02.dbf logfile=datadbv02dbf.log blocksize=8192 feedback=100
oracle@stevux1185:JSDRPW01> dbv file=/u07/oradata/JSDRPW01/data/d1/wms_data_03.dbf logfile=datadbv03dbf.log blocksize=8192 feedback=100
oracle@stevux1185:JSDRPW01> dbv file=/u07/oradata/JSDRPW01/data/d1/wms_data_04.dbf logfile=datadbv04dbf.log blocksize=8192 feedback=100
oracle@stevux1185:JSDRPW01> dbv file=/u07/oradata/JSDRPW01/data/d1/wms_DATA_05.dbf logfile=datadbv05dbf.log blocksize=8192 feedback=100
oracle@stevux1185:JSDRPW01> dbv file=/u03/oradata/JSDRPW01/sysaux/d1/sysaux_01.dbf logfile=sysaux01dbvdbf.log blocksize=8192 feedback=100
oracle@stevux1185:JSDRPW01> dbv file=/u07/oradata/JSDRPW01/tblsync/d1/wms_tblsync_01.dbf logfile=tblsync1dbvdbf.log blocksize=8192 feedback=100
