Thursday, April 02, 2015

One Cause of ORA-01092

I saw the following error in a job which had just recreated the control file of a test database after cloning:

SQL>   2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28   29   30   31   3
2   33   34   35   36   37   38   39   40   41   42   43   44   45   46   47   48   49   50   51   52   53   54   55   56   57   58   59   60   61   62   63
  64   65   66   67   68   69   70   71   72   73   74   75   76   77   78   79   80   81   82   83   84   85   86   87   88   89   90   91   92   93   94
95   96   97   98   99  100  101  102  103  104  105  106  107  108  109  110  111  112  113  114  115  116  117  118  119  120  121  122  123  124  125  126
  127  128  129  130  131  132  133  134  135  136  137
Control file created.
 
SQL> ALTER DATABASE OPEN RESETLOGS
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
 
SQL>

I looked in the alert log and noticed the errors below:

Wed Apr  1 19:30:46 2015
Errors in file /oracle/app/oracle/product/10.2.0/admin/mrmdpt1/udump/mrmdpt1_ora_8712.trc:
ORA-00600: internal error code, arguments: [25025], [115], [], [], [], [], [], []
Wed Apr  1 19:30:47 2015
Errors in file /oracle/app/oracle/product/10.2.0/admin/mrmdpt1/udump/mrmdpt1_ora_8712.trc:
ORA-00600: internal error code, arguments: [25025], [115], [], [], [], [], [], []
Wed Apr  1 19:30:47 2015
Error 600 happened during db open, shutting down database
USER: terminating instance due to error 600
Instance terminated by USER, pid = 8712
ORA-1092 signalled during: ALTER DATABASE OPEN RESETLOGS...

I checked the SQL which had recreated the control file. I saw that one of the datafiles from the source database was missing. The missing file was called '/agasprd/mrmprod/mrm_tables3/undo_1a.dbf'. I checked its file_id in the source database:

SQL> l
  1  select file_id from dba_data_files
  2  where file_name =
  3* '/agasprd/mrmprod/mrm_tables3/undo_1a.dbf'
SQL> /
 
   FILE_ID
----------
       115
 
SQL>

I noticed that it matched the 2nd parameter after the ORA-00600 (i.e. [115]). If this happens to you, you should correct the SQL so that it includes all the source database’s datafiles then repeat the cloning process from the start.

1 comment:

  1. Thanks Andrew , This came in as much needed help for me

    ReplyDelete