Tuesday, May 16, 2017

ORA-04024: self-deadlock detected while trying to mutex pin cursor 0xC1F2B19A8, ORA-01092: ORACLE instance terminated. Disconnection forced


I was performing a clone from a cold backup in a 12c (12.1.0.2) environment and was stuck with the errors (ORA-04024 and ORA-01092). The database restore was completed but while performing the ‘alter database open resetlogs’ it was giving the errors mentioned above. Below are the steps followed and resolution for the same.
Issue:
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-04024: self-deadlock detected while trying to mutex pin cursor 0x3BBEFC1C8
Process ID: 10432
Session ID: 441 Serial number: 38548




Cause:
It is a bug in 12.1.0.2 version of DB
Resolution:
1. Shutdown the database (from mount state)
2. Recreated the controlfile
SQL> @recreate_control_file.trc
Control file created.
3. Recovered the database using the redolog
RECOVER DATABASE USING BACKUP CONTROLFILE until cancel ;
SQL> RECOVER DATABASE USING BACKUP CONTROLFILE until cancel ;
ORA-00279: change 10493059653949 generated at 10/25/2016 22:38:24 needed for
thread 1
ORA-00289: suggestion : +DATA_SIT
ORA-15173: entry ‘ARCHIVELOG’ does not exist in directory ‘SIT’
ORA-00280: change 10493059653949 for thread 1 is in sequence #1
Applied the redolog to do the recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
+DATA_SIT/sit/onlinelog/group_8.264.903647441
ORA-00308: cannot open archived log
‘+DATA_SIT/SIT/onlinelog/group_8.264.903647441’
ORA-17503: ksfdopn:2 Failed to open file
+DATA_SIT/SIT/onlinelog/group_8.264.903647441
ORA-15012: ASM file ‘+DATA_SIT/SIT/onlinelog/group_8.264.903647441’ does not
exist
Media recovery was completed
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
+DATA_SIT/SIT/ONLINELOG/group_1.460.926203011
Log applied.
Media recovery complete.
4. Tried to open the database using ‘resetlogs’, but stuck with the same issue
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-04024: self-deadlock detected while trying to mutex pin cursor 0xC1F2B19A8
Process ID: 25670
Session ID: 1895 Serial number: 9147
5. So, after investigating in Oracle site, performed the below fix and it went fine,
SQL> shut immediate
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 5.1540E+10 bytes
Fixed Size 3725224 bytes
Variable Size 6845106264 bytes
Database Buffers 4.4426E+10 bytes
Redo Buffers 264708096 bytes
Database mounted.




So finally we can fix control it. and solve the issue.
 SQL> alter system set “_fix_control”=’9550277:ON’;

System altered.
SQL> alter database open;
Database altered.

1 comment:

  1. hi Piyush, I really appreciate your posting of this solution. It helped me out when I had a serious time constraint switching on DR and couldn't get past it. That parameter value was a life saver. Thanks again for posting :)
    Christine.

    ReplyDelete