There are times when it may become necessary to force a database open. This
document identifies one way to attempt to get the database open.
You may need to use this procedure because something happened to the database
that required you to perform recovery. The recovery completed, but you receive
the following error message:
ORA-01194 – file 1 needs more media recovery to be consistent
You have determined that there is nothing else that can be done to get the
database to a consistent state. You tried opening the database using
RESETLOGS, but that also failed.
Procedure:
==========
To ATTEMPT to force the database open, perform the following steps:
1. Add the parameter _ALLOW_RESETLOGS_CORRUPTION = TRUE to the init.ora file
2. STARTUP MOUNT the database
3. Issue the appropriate RECOVER DATABASE command:
a) RECOVER DATABASE UNTIL CANCEL
or
b) RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL
4. Enter CANCEL to cancel recovery – there are NO archive logs applied.
5. Enter ALTER DATABASE OPEN RESETLOGS
6. Try selecting from a table – for example, SELECT SYSDATE FROM DUAL
If you get a row back, the database is open and “functional” – you
could try to select from a couple of other tables just to make sure.
If the instance crashed, check for trace files in the background dump
destination. If you find a trace file, check to see if the trace file
has an ORA-00600 [2662] error in it – this may also show up in the
alert.log file. If this is true, then perform the following steps:
7. SHUTDOWN ABORT
8. STARTUP MOUNT
9. ALTER SESSION SET EVENTS ’10015 TRACE NAME ADJUST_SCN LEVEL 1′;
10. ALTER DATABASE OPEN
11. Trying selecting rows from a table
12. If the instance crashes again, check the trace file for another
ORA-00600 [2662] error. If so, go back to step 9 and increment the
LEVEL by 1 and repeat steps 9 through 12 until you can successfully
select rows. If you need to go beyond level 6 or 7, then the database
is probably too far gone to continue.
*************************************************************************
* *
* CAUTION: Once the database is open, it is imperative that you export, *
* rebuild the database, and import. *
* *
* By forcing open the database in this fashion, there is a strong *
* likelihood of logical corruption, possibly affecting the data *
* dictionary. Oracle does not guarantee that all of the data will be *
* accessible nor will it support a database that has been opened by *
* this method and users allowed to continue work. All this does is *
* provide a way to get at the contents of the database for extraction, *
* usually by export. It is up to you to determine the amount of lost *
* data and to correct any logical corruption issues. *
* *
*************************************************************************
© 2010, www.oracledatabase12g.com. 版权所有.文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.
相关文章 | Related posts:
- 9iRAC Tuning Best Practices (INTERNAL ONLY)
- Lost Redo Logs And Archive Logs And Need To Apply Media Recovery ** INTERNAL ONLY **
- Understanding Bootstrap Of Oracle Database
- ORA-00600: INTERNAL ERROR CODE, ARGUMENTS: [729], [10992], [SPACE LEAK] Example
- TECH: Database Block Checking Features
- How to recover and open the database if the archivelog required for recovery is either missing, lost or corrupted?
- How to Perform a Health Check on the Database [ID 122669.1]
- ORA-600 internal error[kqrfrpo]一例
- RMAN 11G : Data Recovery Advisor – RMAN command line example
- Oracle Block Cache Layer And Its Corresponding Corruption Error Messages.
TECH: Summary For Forcing The Database Open With `_ALLOW_RESETLOGS_CORRUPTION` with Automatic Undo Management
***
This article is being delivered in Draft form and may contain errors. Please
use the MetaLink “Feedback” button to advise Oracle of any issues related to
this article.
***
Warning
~~~~~~~
The following instructions should only be used under the explicit direction
of Oracle Support. These steps should only be used when all other conventional
means of recovering the database have failed. Please note that there is no
guarantee that this method will succeed.
IF THE STEPS BELOW DO ALLOW YOU TO OPEN YOUR DATABASE THEN IT IS ESSENTIAL THAT
THE DATABASE BE REBUILT AS IT IS NO LONGER SUPPORTED. FAILURE TO DO SO MAY LEAD
TO DATA DICTIONARY INCONSISTENCIES, INTERNAL ERRORS AND CORRUPTIONS.
** Note: The steps here apply to Oracle 9i or higher and only and when Automatic
Undo Management is being used. **
Steps to attempt to force the database open:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1) Backup the database while the database is closed.
THE INSTRUCTIONS HERE ARE DESTRUCTIVE. YOU ARE STRONGLY ADVISED TO BACKUP THE
DATABASE BEFORE PROCEEDING. IF YOU DO NOT DO THIS YOU MAY LOSE THE CHANCE TO
TRY OTHER OPTIONS.
2) If your datafiles are from different points in time, it is best to try to
use system tablespace datafiles at a similar timestamp to the OLDEST files
you have online in the database. This reduces the chance that you will get
problems during the bootstrap phase of opening the database.
3) Edit your *init.ora file to change undo_management and add two parameters.
* Change UNDO_MANAGEMENT=AUTO to
UNDO_MANAGEMENT=MANUAL
* Remove or comment out UNDO_TABLESPACE and UNDO_RETENTION.
* Add
_ALLOW_RESETLOGS_CORRUPTION = TRUE
_CORRUPTED_ROLLBACK_SEGMENTS =(comma separated list of Automatic Undo segments)
Example:
_CORRUPTED_ROLLBACK_SEGMENTS = (_SYSSMU1$, _SYSSMU2$, _SYSSMU3$, _SYSSMU4$,
_SYSSMU5$, _SYSSMU6$, _SYSSMU7$, _SYSSMU8$, _SYSSMU9$, _SYSSMU10$)
Note, sometimes the alert log will tell you what Automatic Undo segments are in
use. Search the alert log for SYSS. If the alert log does not contain that
information then use _SYSSMU1$ through _SYSSMU10$ as shown in the example above.
In UNIX you can issue this command to get the undo segment names:
$ strings system01.dbf | grep _SYSSMU | cut -d $ -f 1 | sort -u
From the output of the strings command above, add a $ to end of each _SYSSMU
undo segment name.
* If you only have a spfile available, you can from the closed, nomount or the
mount stage create an init.ora file as follows:
SQL> CREATE PFILE FROM SPFILE;
Do NOT edit the SPFILE.
4) Invoke SQL*Plus, startup mount, check that correct init.ora was used and
all datafiles are in the status of online or system.
$ sqlplus “/as sysdba”
SQL> startup mount pfile = (full path / file name to init.ora)
Confirm that the hidden parameters from step 3 were used:
SQL> show parameters corrupt
You should see both hidden parameters listed. If not, the wrong init.ora
may have been modified. Do not continue until “show parameters corrupt” shows
both hidden parameters.
SQL> show parameters undo
You should see undo management set to manual. If not, the wrong init.ora
may have been modified. Do not continue until “show parameters undo” shows
undo management as manual.
Check that all files you want to open with are listed as ONLINE or as SYSTEM.
SQL> select name, file#, status from v$datafile where status not in
(‘SYSTEM’, ‘ONLINE’);
If any rows are returned from the query above, bring the file(s) online with:
SQL> ALTER DATABASE DATAFILE file# ONLINE;
and repeat until there are no files in an OFFLINE status. If any file remains or
changes into “recover” status after you try to online the file proceed to step 5.
5) Perform a fake incomplete recovery then open the database with resetlogs.
SQL> recover database until cancel;
or
SQL> recover database using backup controlfile until cancel;
WHEN PROMPTED FOR AN ARCHIVELOG FILE TYPE cancel THEN PRESS ENTER.
SQL> ALTER DATABASE OPEN RESETLOGS;
6) If the database opens try selecting from a table. For example:
SQL> SELECT SYSDATE FROM DUAL;
If you get a row back the database is open and “functional”. If you wish, you
may try to select from a other tables to make sure the database is functional
enough for the required export.
With the database open and functional you should attempt to export the database
IMMEDIATELY. Once you have an export the database MUST be recreated from scratch.
This means dropping and deleting ALL datafiles and creating a new database from
scratch.
A database which has been opened in this way but not rebuilt will not be
supported by Oracle. Any delay in exporting the contents or any attempt to
use the system may cause irreparable damage.
NOTE: BE SURE TO REVERSE / REMOVE THE INIT.ORA PARAMETERS ADDED IN STEP 3
OTHERWISE YOU MAY ACCIDENTALLY CORRUPT ANY NEW DATABASE CREATED USING THE SAME
INIT.ORA FILE.
7) If the instance crashed in the open phase of step 5, check for trace files
in the background dump destination. If you find a trace file, check to see if
the trace file has an ORA-00600 [2662] or ORA-00600 [4000] error in it.
Either of these errors may also be seen in the alert.log file.
If you see the ORA-00600 [2662] or ORA-00600 [4000] error, provide Oracle Support
Services the full error message. Oracle Support Services will provide steps to advance
the SCN using a hidden parameter.
NOTE: BE SURE TO REVERSE / REMOVE THE INIT.ORA PARAMETERS ADDED IN STEP 3
OTHERWISE YOU MAY ACCIDENTALLY CORRUPT ANY NEW DATABASE CREATED USING THE SAME
INIT.ORA FILE.
*************************************************************************
* *
* CAUTION: Once the database is open, it is imperative that you export, *
* rebuild the database, and import. *
* *
* By forcing open the database in this fashion, there is a strong *
* likelihood of logical corruption, possibly affecting the data *
* dictionary. Oracle does not guarantee that all of the data will be *
* accessible nor will it support a database that has been opened by *
* this method and that the database users will be allowed to continue *
* work. All this does is provide a way to get at the contents of the *
* database for extraction, usually by export. It is up to you to *
* determine the amount of lost data and to correct any logical *
* corruption issues. *
* *
*************************************************************************
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Related Documents:
Note 41399.1 (Limited) TECH: Summary For Forcing The Database Open With
`_ALLOW_RESETLOGS_CORRUPTION`
(Use note 41399.1 for versions previous to 9i or with an Oracle version not
using Automatic Undo Management but that is instead using rollback segments)
RECOVER A DATAFILE WITH MISSING ARCHIVELOGS
Applies to:
Oracle Server – Enterprise Edition – Version: 8.1.7.4 to 10.2.0.4
This problem can occur on any platform.
Symptoms
Database cannot be opened, because a datafile checkpoint is lagging behind from the rest of the datafiles.
Cause
A datafile was restored from a previous backup, but archivelogs required to recover the said datafile are missing.
Solution
There are 3 options available, as shown below:
Option#1: Restore the database from the same backupset, and then recover it by applying up to the last available archivelog to roll it forward, but any updates to the database after the point-in-time of recovery will be lost.
Option#2: Force open the database by setting the _ALLOW_RESETLOGS_CORRUPTION=TRUE in the init.ora. But there is no 100% guarantee that we can open the database. However, once the database is opened, then you must immediately rebuild the database. Database rebuild means doing the following, namely: (1) perform a full-database export, (2) create a new and separate database, and finally (3) import the recent export dump.
Note: When exporting after opening the database with the allow corruption parameters, you should set the Event 10231 before export to extract data from all non-corrupt > blocks in order to be able to import the data successfully. For additional information about Event 10231, please review Note 21205.1 : EVENT: 10231 “skip corrupted blocks on _table_scans_”.
Option#3: Manually extract the data using the Oracle’s Data Unloader (DUL), which is performed by Oracle Field Support on-site for an extra charge.
EVENT: 10231 “skip corrupted blocks on _table_scans_”
Event: 10231
Text: skip corrupted blocks on _table_scans_
——————————————————————————-
Cause:
Action: Corrupt blocks are skipped in table scans, and listed in trace files.
Explanation:
This is NOT an error but is a special EVENT code.
It should *NOT* be used unless explicitly requested by ST support.
8.1 onwards:
~~~~~~~~~~~~
The “7.2 onwards” notes below still apply but in Oracle8i
there is a PL/SQL which can be used
to check corrupt blocks. See .
It is possible to simulate 10231 on a table using
DBMS_REPAIR.SKIP_CORRUPT_BLOCKS(‘schema’,'table’).
The SKIP_CORRUPT column of DBA_TABLES shows tables which
have been marked to allow skipping of corrupt blocks.
7.2 onwards:
~~~~~~~~~~~~
Event 10231 causes SOFTWARE CORRUPT or MEDIA corrupt blocks
to be skipped on FULL TABLE SCANS only. (E.g: on export)
Software corrupt blocks are defined below. Media corrupt
blocks are Oracle blocks where the header field information
is not what was expected. These can now be skipped with
the 10231 event.
Before 7.2:
~~~~~~~~~~~
Event 10231 causes SOFTWARE CORRUPT blocks to be skipped on
FULL TABLE SCANS only. (E.g: on export).
A ‘software corrupt’ block is a block that has a SEQ number of ZERO.
This raises an ORA-1578 error.
NB: Blocks may be internally corrupt and still cause problems or
raise ORA-1578. If a block is physically corrupt and the SEQ
is not set to ZERO, you cannot use 10231 to skip it. You have
to try to scan around the block instead.
To manually corrupt a block and cause it to be skipped you
must: Set SEQ to ZERO.
Set the INCSEQ at the end of the block to match.
You can set event numbers 10210, 10211, and 10212 to check blocks
at the data level and mark them software corrupt if they are found
to be corrupt. You CANNOT use these events to mark a physically
corrupt block as software corrupt because the block never reaches
the data layer.
When a block is skipped, any data in the block is totally ignored.
Usage: Event=”10231 trace name context forever, level 10″.
This should be removed from the instance parameters immediately after
it has been used.
Alternatively it can be set at session level:
alter session set events ’10231 trace name context forever, level 10′
Hdr: 2519067 8.1.7.2.0 RDBMS 8.1.7.2.0 RECOVERY PRODID-5 PORTID-453 ORA-600
Abstract: ORA-600[2662] OCCURS SEVERAL TIMES
*** 08/19/02 02:03 am ***
TAR:
—-
PROBLEM:
——–
ORA-600[2662] occurs several times.
DIAGNOSTIC ANALYSIS:
——————–
- 8.1.7.2 (32bit) on SPARC Solaris 8.
- NON-OPS
– buffer dump
odcm_ora_23006.trc file is traced by executing following command.
alter session set events ‘immediate trace name BUFFERS level 3′;
The size of this file is 2G bytes. So following files are split
per-10M bytes and contain ‘scn: 0x01a5.00000000′
xbo xdd xbz xde xcu xcu xei xge xgp
– block dump
Errors in file odcm_ora_20198.trc.
ORA-600[2662],[0],[291907790],[421],[0],[1619145004],[],[]
6th argument is 1619145004.
1619145004(0x6082312c) = File#386,Block#143660
odcm_ora_5536.trc file is traced by following command.
alter system dump datafile 386 block 143660;
– odcm_ora_20198.trc
kscn kcrds_p [1A5C3F8, 1A5C400) = 00000000 01A50182
4th argument of ORA-600 is 421(0x1a5).
I understand this variable is stored in UGA.
I don't know whether this variable is valid or not.
Are there any relations between this variable and ORA-600?
Some process crushed with ORA-600[2662]. So I think there are
corruptions not only in UGA but also in SGA.
Are there any relations between ORA-600 and heap corruption
(e.g. Bug:1711803)
Both of this problem and Bug:1711803 have same function(kcrfwr)
on stack trace.
WORKAROUND:
———–
N/A
RELATED BUGS:
————-
None
REPRODUCIBILITY:
—————-
several times.
There are no ORA-600[2662] errors after restarting the database.
TEST CASE:
———-
N/A
STACK TRACE:
————
– odcm_ora_20198.trc
ksedmp kgeriv kgesiv ksesic5 kcsgbsn kcrfwr kcrfasd kcbzib kcbget
ktugnb ktugur ktuchg ktbchg2 kddchg kdddrp kdddel kauMovRow kauupd
updrow qerupFetch updaul updexe opiexe opiall0 kpoal8 opiodr ttcpip
opitsk opiino opiodr opidrv sou2o main
SUPPORTING INFORMATION:
———————–
alert_oDCM.log
initodcm.ora
odcm_ora_12414.trc — ORA-600[2662]
odcm_ora_12813.trc — ORA-600[2662]
odcm_ora_17044.trc — ORA-600[2662]
odcm_ora_17108.trc — ORA-600[2662]
odcm_ora_20198.trc — ORA-600[2662]
odcm_ora_22251.trc — ORA-600[2662]
odcm_ora_26746.trc — ORA-600[2662]
odcm_ora_5536.trc — block dump(File#386,Block#143660)
part of buffer dump trace per-10M.
xbo xdd xbz xde xcu xcu xei xge xgp
#These files contain multi-bytes characters
-> These files are archived and compress in [BUG#]_tar.Z in ess30.
24 HOUR CONTACT INFORMATION FOR P1 BUGS:
—————————————-
N/A
DIAL-IN INFORMATION:
——————–
N/A
IMPACT DATE:
————
I understand that it is too difficult to investigate the root cause
of this problem. But we must resolve this problem until occurring
ORA-600[2662] next time at least.
If we cannot resolve this problem, we have to provide the solution
for resolving the problem within a week.
If we cannot resolve this problem, we have to provide the solution
for investigation futher within a week.
e.g. 1. taking the following information during ORA-600[2662] errors
are occurring next time.
alert,trace,buffer dump,block dump.
Are there any other informations you need for investigation?
2. Diag patch will be provided like as Bug:2197150.
*** 08/19/02 02:28 am ***
*** 08/19/02 07:20 pm *** (CHG: Asg->NEW OWNER OWNER)
*** 08/19/02 07:22 pm ***
*** 08/20/02 03:56 am *** (CHG: Sta->10)
*** 08/20/02 03:56 am ***
Some questions.
* Does this problem reproduced before ?
* Does Ct get freed from this problem ? If so, what they did ?
* Does ct run any particular(unusual) transaction before this
problem occur ?
You should tell us what ct did when they get this problem.
* Does ct used _allow_resetlogs_corruption hidden parameter before ?
*** 08/20/02 10:06 pm *** (CHG: Sta->16)
*** 08/20/02 10:06 pm ***
1. Does this problem reproduced before ?
No.
2. Does Ct get freed from this problem ? If so, what they did ?
Yes. Ct re-start the database.
There are no ORA-600[2662] errors after restarting the database.
3. Does ct run any particular(unusual) transaction before this
problem occur ?
Ct executes a similar huge transaction(DML) every night.
Ct executed the huge transaction as usual when the problem occurred.
Ct applied following patches on Aug 3.
– Bug:1956911 (Base Bug:1820448)
– Bug:1964696 (Base Bug:1858632)
– Bug:2195797 (MERGE OF BUGS 1836101 AND 1869512)
4. Does ct used _allow_resetlogs_corruption hidden parameter before ?
No.
Rollback Segment Needs Recovery
OVERVIEW
——–
This bulletin addresses why a rollback segment has the status of
“needs recovery”, what the status means, and how to resolve it.
INTRODUCTION
————
Rollback segments can be monitored through the data dictionary view,
dba_rollback_segs. There is a status column that describes what state the
rollback segment is currently in. Normal states are either online or offline.
Occasionally, the status of “needs recovery” will appear.
When a rollback segment is in this state, bringing the rollback segment
offline or online either through the alter rollback segment command or
removing it from the rollback_segments parameter in the init.ora usually
has no effect.
UNDERSTANDING
————-
A rollback segment falls into this status of needs recovery whenever
Oracle tries to roll back an uncommitted transaction in its transaction
table and fails.
Here are some examples of why a transaction may need to rollback:
1-A user may do a dml transaction and decides to issue rollback
2-A shutdown abort occurs and the database needs to do an instance recovery
in which case, Oracle has to roll back all uncommitted transactions.
When a rollback of a transaction occurs, undo must be applied to the
data block the modified row/s are in. If for whatever reason, that data
block is unavailable, the undo cannot be applied. The result is a ‘corrupted’
rollback segment with the status of needs recovery.
What could be some reasons a datablock is unaccessible for undo?
1-If a tablespace or a datafile is offline or missing.
2-If the object the datablock belongs to is corrupted.
3-If the datablock that is corrupt is actually in the rollback segment
itself rather than the object.
HOW TO RESOLVE IT
—————–
1-MAKE sure that all tablespaces are online and all datafiles are
online. This can be checked through v$datafile, under the
status column. For tablespaces associated with the datafiles,
look in dba_tablespaces.
If that still does not resolve the problem then
2-PUT the following in the init.ora-
event = “10015 trace name context forever, level 10″
Setting this event will generate a trace file that will reveal the
necessary information about the transaction Oracle is trying to roll
back and most importantly, what object Oracle is trying to apply
the undo to.
3-SHUTDOWN the database (if normal does not work, immediate, if that does
not work, abort) and bring it back up.
Note: An ora-1545 may be encountered, or other errors. If the database
cannot startup, contact customer support at this point.
4-CHECK in the directory that is specified by the user_dump_dest parameter
(in the init.ora or show parameter command) for a trace file that was
generated at startup time.
5-IN the trace file, there should be a message similar to-
error recovery tx(#,#) object #.
TX(#,#) refers to transaction information.
The object # is the same as the object_id in sys.dba_objects.
6-USE the following query to find out what object Oracle is trying to
perform recovery on.
select owner, object_name, object_type, status
from dba_objects where object_id = ;
7-THIS object must be dropped so the undo can be released. An export or relying
on a backup may be necessary to restore the object after the corrupted
rollback segment goes away.
8-AFTER dropping the object, put the rollback segment back in the init.ora
parameter rollback_segments, removed the event, and shutdown and startup
the database.
In most cases, the above steps will resolve the problematic rollback segment.
If this still does not resolve the problem, it may be likely that the
corruption is in the actual rollback segment.
At this point, if the problem has not been resolved, please contact
customer support.
How To Resolve Stranded DBA_2PC_PENDING Entries
Applies to:
Oracle Server – Enterprise Edition – Version: 8.1.5.0 to 11.1.0.6
Information in this document applies to any platform.
Purpose
The purpose of this bulletin is to assist support analysts in understanding and
resolving the stranded dba_2pc_entries.
Scope and Application
The intended audience are support analysts having good experience on distributed
databases.
How To Resolve Stranded DBA_2PC_PENDING Entries
Contents
1. Problem Description
2. Solutions
2.1 Dba_2pc entries without a corresponding transaction
2.2 Distributed transaction without corresponding dba_2pc entries
2.3 How to PURGE the DISTRIBUTED transaction in PREPARED state, when COMMIT
or ROLLBACK FORCE hangs ?, where we have entries for both Distributed
transaction and dba_2pc entries.
1. Problem Description:
=======================
As a result of a failed commit of a distributed transaction, some entries can
be left in dba_2pc views, i.e. dba_2pc_pending and dba_2pc_neighbors. The RECO
process checks these views to recover the failed txn. However, in some cases
RECO cannot perform the recovery. One cause is that all sites involved in the
transaction not being accessible at the same time. Another cause is dba_2pc
views being inconsistent with the transaction table, which is the topic of
this article. This cause can further be classified as follows:
1. dba_2pc views have entries for a non-existent distributed transaction
2. There is a distributed transaction for which there are no entries in
dba_2pc views
3. How to PURGE the DISTRIBUTED transaction in PREPARED state, when COMMIT
or ROLLBACK FORCE hangs ?, where we have entries for both Distributed
transaction and dba_2pc entries.
Solutions to each subclass is provided in the rest of the article.
2. Solutions:
2.1 Dba_2pc entries without a corresponding transaction
In this case dba_2pc views show distributed transactions but there are no txns
in reality. If the state of the transaction is committed, rollback forced or
commit forced then this is normal and it can be cleaned up using
dbms_transaction.purge_lost_db_entry
However, if the state of the transaction is PREPARED and there is no entry in
the transaction table for it then this entry can be cleaned up manually as
follows:
set transaction use rollback segment SYSTEM;
delete from sys.pending_trans$ where local_tran_id = ;
delete from sys.pending_sessions$ where local_tran_id = ;
delete from sys.pending_sub_sessions$ where local_tran_id = ;
commit;
Example:
——–
The following query reports a dist. txn. in prepared state
select local_tran_id, state from dba_2pc_pending;
LOCAL_TRAN_ID STATE
———————- —————-
1.92.66874 prepared
Given that a transaction id is composed of triple,
’1.92.66874′ is located in rollback segment# 1. To find out the list of
active transactions in that rollback segment, use:
SELECT KTUXEUSN, KTUXESLT, KTUXESQN, /* Transaction ID */
KTUXESTA Status,
KTUXECFL Flags
FROM x$ktuxe
WHERE ktuxesta!=’INACTIVE’
AND ktuxeusn= 1; <== this is the rollback segment#
no rows selected
It is not possible to rollback force or commit force this transaction.
rollback force '1.92.66874';
ORA-02058: no prepared transaction found with ID 1.92.66874
Hence, we have to manually cleanup that transaction:
set transaction use rollback segment SYSTEM;
delete from sys.pending_trans$
where local_tran_id = '1.92.66874';
delete from sys.pending_sessions$ where local_tran_id = '1.92.66874';
delete from sys.pending_sub_sessions$ where local_tran_id = '1.92.66874';
commit;
2.2 Distributed transaction without corresponding dba_2pc entries
In this case dba_2pc views are empty but users are receiving distributed txn
related errors, e.g. ORA-2054, ORA-1591. Normally such a case should not appear
and if it is reproducible a bug should be filed. Here is the list of several
alternative solutions that can be used in this case:
a. Perform incomplete recovery
b. Truncate the objects referenced by that transaction and import them
c. Use _corrupted_rollback_segments parameter to drop that rollback segment
d. Insert dummy entries into dba_2pc views and either commit or rollback
force the distributed transaction
The first three solutions are discussed in Backup and Recovery manuals and in
the notes referred above. In the 4th solution a dummy entry is inserted into
the dictionary so that the transaction can be manually committed or rolled back.
Note that RECO will not be able to process this txn and distributed txn recovery
should be disabled before using this method. Furthermore, please take a BACKUP
of your database before using this method.
The following example describes how to diagnose and resolve this case. Suppose
that users are receiving
ORA-1591: lock held by in-doubt distributed transaction 1.92.66874
and the following query returns no rows:
select local_tran_id, state from dba_2pc_pending
where local_tran_id='1.92.66874';
no rows selected
Furthermore querying the rollback segment shows that 1.92.66874 remains in
prepared state
SELECT KTUXEUSN, KTUXESLT, KTUXESQN, /* Transaction ID */
KTUXESTA Status,
KTUXECFL Flags
FROM x$ktuxe
WHERE ktuxesta!='INACTIVE'
AND ktuxeusn= 1; /* <== Replace this value with your txn undo seg#
Which is displayed in the first part of
the transaction ID */
KTUXEUSN KTUXESLT KTUXESQN STATUS FLAGS
———- ———- ———- —————- ————————
1 92 66874 PREPARED SCO|COL|REV|DEAD
Trying to manually commit or rollback this transaction
commit force '1.92.66874';
ORA-02058: no prepared transaction found with ID 1.92.66874
raises ORA-02058 since dba_2pc views are empty. In order to use commit force or
rollback force a dummy record should be inserted into pending_trans$ as follows:
alter system disable distributed recovery;
insert into pending_trans$ (
LOCAL_TRAN_ID,
GLOBAL_TRAN_FMT,
GLOBAL_ORACLE_ID,
STATE,
STATUS,
SESSION_VECTOR,
RECO_VECTOR,
TYPE#,
FAIL_TIME,
RECO_TIME)
values( '1.92.66874', /* <== Replace this with your local tran id */
306206, /* */
'XXXXXXX.12345.1.2.3', /* These values can be used without any */
'prepared','P', /* modification. Most of the values are */
hextoraw( '00000001' ), /* constant. */
hextoraw( '00000000' ), /* */
0, sysdate, sysdate );
insert into pending_sessions$
values( '1.92.66874',/* SELECT LOCAL_TRAN_ID,STATE FROM DBA_2PC_PENDING;
LOCAL_TRAN_ID STATE
—————– ———–
44.88.85589 prepared
SQL> SELECT KTUXEUSN, KTUXESLT, KTUXESQN, /* Transaction ID */
KTUXESTA Status,
KTUXECFL Flags
FROM x$ktuxe
WHERE ktuxesta!=’INACTIVE’
AND ktuxeusn= 44; /* Commit force 44.88.85589;
SQL> rollback force 44.88.85589;
Executing COMMIT or ROLLBACK FORCE hangs
The wait event is “”free global transaction table entry”
Purging the transaction should fail with below error:
EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY(’44.88.85589′);
BEGIN DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY(’44.88.85589′); END;
*
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at “SYS.DBMS_TRANSACTION”, line 94
ORA-06512: at line 1
Solution:
——–
You have to implement both the solution :
2.1 Dba_2pc entries without a corresponding transaction
2.2 Distributed transaction without corresponding dba_2pc entries
1.
delete from sys.pending_trans$ where local_tran_id = ’44.88.85589′;
delete from sys.pending_sessions$ where local_tran_id = ’44.88.85589′;
delete from sys.pending_sub_sessions$ where local_tran_id =’44.88.85589′;
commit;
2. Now insert the dummy record as explained in section:
2.2 Distributed transaction without corresponding dba_2pc entries
commit;
3. Commit force ’44.88.85589′
4. Purge the transaction:
exec dbms_transaction.purge_lost_db_entry(’44.88.85589′);
Note:126069.1 Manually Resolving In-Doubt Transactions: Different Scenario’s