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