Oracle Point, Oracle Life.

Due to limitation of disk capacity, I was asked to resize one of our databases. Our investigation shows that the size of undo tablespace has been set to unnecessarily large, say 8G. Our junior DBA was trying to switch to newly created undo tablespace and have placed the datafile for default undo tablespace offline. After doing that, he got error code Ora-01157 and Ora-01110 when he tried to drop the prior default undo tablespace. To figure out the problem and perform the switch, we followed the following steps to have it done.

Step 1: Query to discover how many rollback segments were corrupted.

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

Step 2: Add the following line to pfile

_corrupted_rollback_segments =('_SYSSMU1$','_SYSSMU2$','_SYSSMU3$','_SYSSMU4$','_SYSSMU5$','_SYSSMU6$','_SYSSMU7$','_SYSSMU8$','_SYSSMU9$','_SYSSMU21$')

Step 3: Shutdown database clearly 

$ sqlplus “/ as sysdba”

SQL> connect sys@dbname as sysdba

SQL> shutdown immediate

Step 4: Start database with newly updated pfile

SQL> startup pfile=/oracle/db10g/dbs/initDBNAME.ora

Step 5: Drop all of the corrupted rollback segments

SQL> drop rollback segment “_SYSSMU1”;

……………..

SQL> drop rollback segment “_SYSSMU10”;

Step 6: Now drop bad undo tablespace

SQL> drop tablespace UNDOTBS1;

Done!

Once finishing above steps, we went back to OEM console and confirmed that bad undo tablespace has been removed. The new undo tablespace is the one our Junior DBA created.

Popularity: 61% [?]

In versions before Oracle 10g, statspack is useful utility to monitor database performance and workload issues. I just came across a shell script which can automate snapshot report create and mail to DBA.

The script is re-posted as below without change.

[oracle@www1 sql]$ more backup/auto_send_perf.sh
#!/bin/sh
. ~oracle/.bash_profile

/home/oracle/product/9.2.0/bin/sqlplus -s aaa/bbb@ccc<<!
set head off
set timing off
spool /home/oracle/sql/backup/snap_begin.lst
select min(snap_id) snap_id
from stats$snapshot
where snap_time between trunc(sysdate) and trunc(sysdate)+1;
spool off
spool /home/oracle/sql/backup/snap_end.lst
select max(snap_id) snap_id
from stats$snapshot
where snap_time between trunc(sysdate) and trunc(sysdate)+1;
spool off
exit
!

BEGIN_SNAP=`cat /home/oracle/sql/backup/snap_begin.lst | tail -n 2`
END_SNAP=`cat /home/oracle/sql/backup/snap_end.lst | tail -n 2`
#END_SNAP=`expr $BEGIN_SNAP + 13`
REPORT_NAME=/home/oracle/sql/report/sp`date +%m%d`_ac

/home/oracle/product/9.2.0/bin/sqlplus -s aaa/bbb@ccc<<!
define begin_snap=$BEGIN_SNAP
define end_snap=$END_SNAP
define report_name=$REPORT_NAME
@?/rdbms/admin/spreport
exit
!

mail -s “perfstat report” ddd@eee.fff < /home/oracle/sql/report/sp`date +%m%d`_ac.lst

[oracle@www1 sql]$crontab -l
* 21 * * * /home/oracle/sql/backup/auto_send_perf.sh >> /home/oracle/sql/backup/perf.lst 2>&1

Note: The snapshot report is created from 8:00 AM to 8:00 PM daily and the job of crontab is called at 9:00 AM.

Popularity: 26% [?]