ORA-00280: change 12613586372514 for thread 2 is in sequence #1170

Recovering a datafile from the archivelogs:

SQL> select name from v$recover_file;
'+DBNAME_DATA_01/dbname/datafile/undotbs2.449.755961101'




SQL> alter database datafile '+DBNAME_DATA_01/dbname/datafile/undotbs2.449.755961101' online;
alter database datafile '+DBNAME_DATA_01/dbname/datafile/undotbs2.449.755961101' online
*
ERROR at line 1:
ORA-01113: file 39 needs media recovery
ORA-01110: data file 39: '+DBNAME_DATA_01/dbname/datafile/undotbs2.449.755961101'


Start Manual recovery:


SQL> recover datafile '+DBNAME_DATA_01/dbname/datafile/undotbs2.449.755961101'
ORA-00279: change 12613586221711 generated at 02/27/2012 22:27:23 needed for
thread 2
ORA-00289: suggestion :
+DBNAME_FRA_01/dbname/archivelog/2012_02_28/thread_2_seq_1167.882.776418021
ORA-00280: change 12613586221711 for thread 2 is in sequence #1167


Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 12613586221711 generated at 02/26/2012 12:00:46 needed for
thread 1
ORA-00289: suggestion : +DBNAME_FRA_01
ORA-00280: change 12613586221711 for thread 1 is in sequence #782


** I don't have this log in the FRA ********
ORA-00308: cannot open archived log '+DBNAME_FRA_01'
ORA-17503: ksfdopn:2 Failed to open file +DBNAME_FRA_01
ORA-15045: ASM file name '+DBNAME_FRA_01' is not in reference form

Restore the log from the backup:

RMAN> connect target /

connected to target database: DBNAME (DBID=1279696963)

RMAN> restore archivelog sequence 782 thread 1;

Starting restore at 28-FEB-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=7898 instance=DBNAME1 device type=DISK

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=782
channel ORA_DISK_1: reading from backup piece /ora_backup/u0002/DBNAME1/archivelog/ARC_DBNAME1_20120227_222702_s1048_p1_0on4dcav
channel ORA_DISK_1: piece handle=/ora_backup/u0002/DBNAME1/archivelog/ARC_DBNAME1_20120227_222702_s1048_p1_0on4dcav tag=TAG20120227T222741
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 28-FEB-12


Now start the recovery again.

SQL> recover datafile '+DBNAME_DATA_01/dbname/datafile/undotbs2.449.755961101'
ORA-00279: change 12613586221711 generated at 02/27/2012 22:27:23 needed for
thread 2
ORA-00289: suggestion :
+DBNAME_FRA_01/dbname/archivelog/2012_02_28/thread_2_seq_1167.882.776418021
ORA-00280: change 12613586221711 for thread 2 is in sequence #1167


Specify log: {=suggested | filename | AUTO | CANCEL}
'+DBNAME_FRA_01/dbname/archivelog/2012_02_28/thread_2_seq_1167.882.776418021'
ORA-00279: change 12613586221711 generated at 02/26/2012 12:00:46 needed for
thread 1
ORA-00289: suggestion : +DBNAME_FRA_01
ORA-00280: change 12613586221711 for thread 1 is in sequence #782




Specify log: {=suggested | filename | AUTO | CANCEL}
'+DBNAME_FRA_01/dbname/archivelog/2012_02_28/thread_2_seq_1167.882.776418021'
ORA-00279: change 12613586221711 generated at 02/26/2012 12:00:46 needed for
thread 1
ORA-00289: suggestion : +DBNAME_FRA_01
ORA-00280: change 12613586221711 for thread 1 is in sequence #782


Specify log: {=suggested | filename | AUTO | CANCEL}
'+DBNAME_FRA_01/dbname/archivelog/2012_02_28/thread_1_seq_782.866.776423291'
ORA-00279: change 12613586221719 generated at 02/27/2012 22:27:24 needed for
thread 1
ORA-00289: suggestion :
+DBNAME_FRA_01/dbname/archivelog/2012_02_28/thread_1_seq_783.348.776418019
ORA-00280: change 12613586221719 for thread 1 is in sequence #783


Specify log: {=suggested | filename | AUTO | CANCEL}
auto *********Now all the archive logs are in the FRA so I am starting the auto recovery***
ORA-00279: change 12613586349995 generated at 02/28/2012 07:40:17 needed for
thread 1
ORA-00289: suggestion :
+DBNAME_FRA_01/dbname/archivelog/2012_02_28/thread_1_seq_784.374.776418019
ORA-00280: change 12613586349995 for thread 1 is in sequence #784


ORA-00288: to continue recovery type ALTER DATABASE RECOVER CONTINUE
ORA-00278: log file
'+DBNAME_FRA_01/dbname/archivelog/2012_02_28/thread_1_seq_784.374.776418019' no
longer needed for this recovery


ORA-00279: change 12613586350043 generated at 02/28/2012 07:40:20 needed for
thread 2
ORA-00289: suggestion :
+DBNAME_FRA_01/dbname/archivelog/2012_02_28/thread_2_seq_1168.489.776418183
ORA-00280: change 12613586350043 for thread 2 is in sequence #1168


ORA-00279: change 12613586350420 generated at 02/28/2012 07:40:54 needed for
thread 1
ORA-00289: suggestion :
+DBNAME_FRA_01/dbname/archivelog/2012_02_28/thread_1_seq_785.1231.776418929
ORA-00280: change 12613586350420 for thread 1 is in sequence #785


ORA-00279: change 12613586371543 generated at 02/28/2012 07:43:02 needed for
thread 2
ORA-00289: suggestion :
+DBNAME_FRA_01/dbname/archivelog/2012_02_28/thread_2_seq_1169.431.776418183
ORA-00280: change 12613586371543 for thread 2 is in sequence #1169


ORA-00288: to continue recovery type ALTER DATABASE RECOVER CONTINUE
ORA-00278: log file
'+DBNAME_FRA_01/dbname/archivelog/2012_02_28/thread_2_seq_1169.431.776418183' no
longer needed for this recovery


ORA-00279: change 12613586372514 generated at 02/28/2012 07:47:37 needed for
thread 2
ORA-00289: suggestion :
+DBNAME_FRA_01/dbname/archivelog/2012_02_28/thread_2_seq_1170.335.776418927
ORA-00280: change 12613586372514 for thread 2 is in sequence #1170


Log applied.
Media recovery complete.
SQL> alter database datafile '+DBNAME_DATA_01/dbname/datafile/undotbs2.449.755961101' online;

Database altered.

ORA-25152: TEMPFILE cannot be dropped at this time

While trying to drop the tempfile, I was getting below error.


SQL> create temporary tablespace temp1;

Tablespace created.

SQL> alter database default temporary tablespace temp1;

Database altered.


SQL>select username,temporary_tablespace from dba_users where temporary_tablespace='TEMP3'
no rows selected




SQL> alter database tempfile '+ICMQA_DATA_01/icmqa/tempfile/temp3.473.774200997' drop including datafiles;
alter database tempfile '+DBNAME_DATA_01/dbname/tempfile/temp3.473.774200997' drop including datafiles
*
ERROR at line 1:
ORA-25152: TEMPFILE cannot be dropped at this time

find out which sessions are still using the old temp tablespace.

SELECT a.INST_ID,b.TABLESPACE
, b.segfile#
, b.segblk#
, ROUND ( ( ( b.blocks * p.VALUE ) / 1024 / 1024 ), 2 ) size_mb
, a.SID
, a.serial#
, a.username
, a.osuser
, a.program
, a.status
FROM gv$session a
, gv$sort_usage b
, gv$process c
, gv$parameter p
WHERE p.NAME = 'db_block_size'
AND a.saddr = b.session_addr
AND a.paddr = c.addr
ORDER BY b.TABLESPACE
, b.segfile#
, b.segblk#
, b.blocks;



Now kill all those sessions
SQL> alter system kill session '7618,4566' IMMEDIATE;

System altered.

or if there are more sessions like in the screenshot create the script to kill all the sessions as below:


SELECT 'ALTER SYSTEM KILL SESSION '''||A.SID||','||A.SERIAL#||''' IMMEDIATE;'
FROM gv$session a
, gv$sort_usage b
, gv$process c
, gv$parameter p
WHERE p.NAME = 'db_block_size'
AND a.saddr = b.session_addr
AND a.paddr = c.addr
and b.tablespace='TEMP3' <-- change the tablespace name
and a.inst_id=2 <-- change the instance name
ORDER BY b.TABLESPACE
, b.segfile#
, b.segblk#
, b.blocks;




SQL> alter database tempfile '+DBNAME_DATA_01/dbname/tempfile/temp3.473.774200997' drop including datafiles;

Database altered.

SQL>

drop bad disk from the ASM

1>Please check with your system/Storage administrator on this disk level issue ,make sure not a single block get overwritten.

2>If you have enough space on other disks to hold data of this effected disk,drop that disk ,seems there is some OS level corruption at this disk level ,so it is not able to read that block.

sql> alter diskgroup PROD_DATA_01 drop disk PROD_DATA_01_0045 rebalance power 5;

Wait for rebalance to complete .

from ASM sqlplus till below query returns "no rows selected"

sql> select * from v$asm_operation ;

If you does not have enough space then add a new disk to this diskgroup and the drop this disk ,

Remember ,both separate operation will cause rebalance operation to kick-off,so you have to allow both of them complete.


3> Then start the database on mount stage,

sql> startup mount;

Then check any datafile needs recovery or not,

select * from v$recover_file;

4> If required recovery then ,do recovery of those specific datafiles.

5> Then open the database .


Note:: If after dropping you face any issue related to spfile ,then please recreate the spfile (As it is in same effected diskgroup).
Then refer to below document ,

Recreating the Spfile for RAC Instances Where the Spfile is Stored in ASM (Doc ID 554120.1)

FRM-40735: ON-ERROR Trigger raised unhandled exception ORA-01001

Issue: click on any form in the apps env and users were getting the below message.

Followed by:

Seems session is getting killed.

No useful information from the forms logs except the session dump file after session got killed.

No errors were reported in the database parameter file.

Changes implemented:

1) Didn’t work.

Db:

processes integer 200

SQL> select count(*) from v$session;

COUNT(*)

----------

194

Raised the process & sessions in the DB but this didn’t help

2) As per ORA-01001 When Clicking Cancel-Query Button [ID 436537.1]

profile option is not set in our environment. so this is not the issue.

For NON-RAC environments perform the following actions:

1. Add SDU=32767 in the listener.ora of db tier, and the

2. Add SDU=32767 in the tnsnames.ora at middle tier, then bounce the db and listener after that.

Added the SDU in the listener and tnsnames files on both dbserver & app server and this fixed the issue. ( I didn’t bounce anything after adding the SDU).

Now we are not getting any exception error messages but the jobs are going to pending state.

Here I brought apps down and ran the cmclean.sql in the database and this helped clear the conc hung state and jobs were going to completion.