Re: ora_reco process throwing ORA-12154 TNS error

On 5/13/22 18:20, Sandra Becker wrote:
> I tried rolling back the transactions, but got the error that there
> were no prepared transactions.  I also tried the purge, which also
> failed.  Since the clone was a dev database that we didn't want
> touching production, I did not want to commit anything.  The end users
> had already handled those transactions from the source production
> database.
>
> I was told by our support vendor that all I had to do was create a new
> UNDO tablespace and that would fix everything. It did not.  Got rid
> the of the rollback segment that needed recovery, but I still had RECO
> spitting out TNS errors.  I finally found an article that walked me
> through various scenarios to ascertain the best way to fix a variety
> of issues with pending 2PC.  The ultimate solution was to delete the
> LOCAL_TRAN_IDs from the sys.pending% tables.  I was then able to
> proceed with my post-duplicate procedures without error.
>
> Thank you to everyone for your responses.
>
> Sandy
>
> On Fri, May 13, 2022 at 2:35 PM Mladen Gogala
> <gogala.mladen_at_gmail.com> wrote:
>
> On 5/12/22 11:46, Sandra Becker wrote:
>> Forgot to include the errors from the alert log.
>>
>> Thu May 12 01:26:39 2022
>> SMON: about to recover undo segment 16
>> SMON: mark undo segment 16 as needs recovery
>> Thu May 12 01:28:02 2022
>> Errors in file /apps/oracle/admin/findev/bdump/findev_reco_6298.trc:
>> ORA-12154: TNS:could not resolve service name
>> ORA-12154: TNS:could not resolve service name
>> ORA-12154: TNS:could not resolve service name
>>
> Hi Sandy,
> RECO process is global recovery process, it recovers global
> transactions. Your RECO is complaining that it cannot find one of
> the delinquents participating in the aborted global transaction.
> You can update tnsnames.ora and help RECO find the wanted
> database, dead or alive, or you can use
> DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY to murder the problematic
> transaction manually. You can find all the necessary data in
> DBA_2PC_PENDING.
> Have a great weekend.
>
>
>
> --
> Mladen Gogala
> Database Consultant
> Tel: (347) 321-1217
> https://dbwhisperer.wordpress.com
>
> -- http://www.freelists.org/webpage/oracle-l
>
>
>
> --
> Sandy B.
>
Hmmm,  I don't recollect purge_lost_db_entry ever failing. It's meant as "kill unconditionally". You may need to get Oracle Support involved here. Are the in-doubt transactions still visible in the DBA_2PC_PENDING? As for creating the new UNDO tablespace, it will not work. Oracle will copy the content of the needed UNDO segment into the SYSTEM tablespace and keep it there until the transaction outcome is certain.

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com



--
http://www.freelists.org/webpage/oracle-l