ORA-600 complete resolution

Resolution of ORA-600 error ;

This is one of the solution using which you can correct ORA-600 error.

1) check SGA_TARGET parameter
SQL> show parameter sga_target
NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------
sga_target                           big integer 40G
----- change value from 40g to 60g

SQL> ALTER SYSTEM SET SGA_TARGET=60G SCOPE=SPFILE;
SQL> ALTER SYSTEM SET SGA_TARGET=15G SCOPE=SPFILE;


2) check parameter pga_aggregate_target.
SQL> show parameter pga_aggregate_target
NAME                                 TYPE        VALUE
------------------------------------ ----------- -------
pga_aggregate_target                 big integer 60G

----- change the value from 60g to 40g
SQL> ALTER SYSTEM SET PGA_AGGREGATE_TARGET=40G SCOPE=SPFILE;
SQL> ALTER SYSTEM SET PGA_AGGREGATE_TARGET=5G SCOPE=SPFILE;

SQL> show parameter spfile
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      D:\ORACLE\PRODUCT\11.2.0\DB_1\DATABASE\SPFILEDEVL.ORA

SQL> create pfile='D:\oracle\product\11.2.0\db_1\database\init_devl1.ora' from spfile='D:\oracle\product\11.2.0\db_1\database\SPFILEDEVL.ORA';

*.pga_aggregate_target=42949672960
*.processes=600
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=660
*.sga_max_size=64424509440
*.sga_target=64424509440
*.standby_file_management='AUTO'
*.undo_retention=3600
*.undo_tablespace='UNDOTBS1'



3) Now shutdown the Production database.

4) Now Create pfile from spfile.
SQL> create pfile='D:\oracle\product\11.2.0\db_1\database\init_prod1.ora' from spfile='D:\oracle\product\11.2.0\db_1\database\SPFILEPROD.ORA';

5) Remove *._pga_max_size=10737418240 parameter from pfile.

6) Now start database using pfile.
c:\> set ORACLE_SID=prod
c:\> sqlplus / as sysdba
SQL> startup pfile='D:\oracle\product\11.2.0\db_1\database\init_prod1.ora'

7) Now create spfile from pfile.
SQ> create spfile='D:\oracle\product\11.2.0\db_1\database\SPFILEPROD.ORA' from pfile='D:\oracle\product\11.2.0\db_1\database\init_prod1.ora';
File Created.

8) If database open successfully , then shutdown the database.
SQL> shut immediate;

9) Now start database again using spfile.
SQL> startup
Database open.

10) Now check following two views, if both view show same result then ensure the database is open with spfile.
SQL> show parameter pfile
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      D:\ORACLE\PRODUCT\11.2.0\DB_1\DATABASE\SPFILEPROD.ORA
                                                
SQL> show parameter spfile
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      D:\ORACLE\PRODUCT\11.2.0\DB_1\DATABASE\SPFILEPROD.ORA
                                                

ORA-2050 resolution

Error :
ORA-02050: transaction 4.55.150856 rolled back, some remote DBs may be in-doubt
ORA-02051: another session or branch in same transaction failed or finalized
Tue Mar 20 11:21:32 2012

Reason :
For some reason, the distributed transaction did not finish properly,
it ended with "collecting" status. The reco process tried to recover
the transaction periodicly but failed, thus error logged into alert log
and trace file. The best way to clean it is using above command.
=========================================================
Before you begin, make note of the local transaction ID, <local_tran_id>, from
the error message reported.
1. Determine if you can attempt a commit or rollback of this
transaction. You can do the following select to help determine what
action to take:
SQL> select state, tran_comment, advice from dba_2pc_pending
where local_tran_id = '4.55.150856';
Review the TRAN_COMMENT column as this could give more information
as to the origin of the transaction or what type of transaction it was.
Review the ADVICE column as well. Many applications prescribe advice
about whether to force commit or force rollback the distributed
transaction upon failure.
2. Commit or rollback the transaction.
To commit:
SQL> commit force '<local_tran_id>';
To rollback:
SQL> rollback force '<local_tran_id>';