Friday, October 09, 2015

ORA-01166

We have some jobs which copy the datafiles from 1 database to another then recreate the control file to give the output database a new name. Some of these jobs are intelligent i.e. they query the input database to dynamically create the SQL to do the rename. This particular job is not. Part of it is shown below and you can see that the MAXDATAFILES parameter was set to 120:

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE SET DATABASE "MRMDPT1" RESETLOGS  NOARCHIVELOG
--  SET STANDBY TO MAXIMIZE PERFORMANCE
    MAXLOGFILES 10
    MAXLOGMEMBERS 3
    MAXDATAFILES 120
    MAXINSTANCES 1
    MAXLOGHISTORY 452
 
Somebody added an extra datafile to the input database so it then had 121:
 
SQL> select count(*) from dba_data_files
  2  /
 
  COUNT(*)
----------
       121
 
SQL>
 
However, he forgot to update the script so the rename failed as follows:
 
ORA-01503: CREATE CONTROLFILE failed
ORA-01166: file number 121 is larger than MAXDATAFILES (120)
ORA-01110: data file 121: '/cisdpt/mrmdpt1/mrm_tables3/sysauxMRMPROD_2.dbf'
 
I updated the script like this, changing MAXDATAFILES to 150:
 
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE SET DATABASE "MRMDPT1" RESETLOGS  NOARCHIVELOG
--  SET STANDBY TO MAXIMIZE PERFORMANCE
    MAXLOGFILES 10
    MAXLOGMEMBERS 3
    MAXDATAFILES 150
    MAXINSTANCES 1
    MAXLOGHISTORY 452
 
... and when I ran the job again, it worked:
 
SQL*Plus: Release 10.2.0.3.0 - Production on Thu Oct 8 12:16:47 2015
 
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
 
Connected to an idle instance.
 
SQL> ORACLE instance started.
 
Total System Global Area  524288000 bytes
Fixed Size                  2031416 bytes
Variable Size             171966664 bytes
Database Buffers          343932928 bytes
Redo Buffers                6356992 bytes
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   32   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  138  139  140  141  142  143  144
Control file created.
 
SQL>

No comments: