Re: ORA-01118 error

Sylvain Leclerc wrote:
>
> Inceasing the db_files wont do, it also depends on your MAXDATAFILES at
> creation time. The size of the control file is based on this. Thats
> why you need to recreate the database.
>
> > -----Original Message-----
> > From: Pete Etten [SMTP:ettenp_at_reston.btna.com]
> > Posted At: Wednesday, April 22, 1998 10:14 AM
> > Posted To: server
> > Conversation: ORA-01118 error
> > Subject: Re: ORA-01118 error
> >
> > Try increasing the db_files parameter in the init<SID>.ora.
> >
> > Italo Bozzi Feuereisen wrote in message
> > <6hkrbm$nu8$1_at_aconcagua.ctcreuna.cl>...
> > >Dear friends:
> > >
> > >We have the next problem. Yesterday I tried to create a new
> > tablespace,
> > the
> > >command was terminated with the next Oracle error:
> > >
> > > ORA-01118 cannot add any more datafiles: limit of 30 exceeded
> > >
> > >The Oracle Manuals recommend to recreate the database. Exist any
> > >possibility to not recreate the database?
> > >
> > >We have Oracle 7.3
> > >
> > >Thanks,
> > >
> > >
> > >Italo.
> > >
> > >
> > >
> >

This is the scenario. When you try to add a datafile to your database and get ORA-1118 (with ORA-00059 you can correct by bumping up the db_files parameter in init<SID>.ora), it is time to set a higher value for the MAXDATAFILES paramater (which is not in init<SID>.ora). The default value for MAXDATFILES is 30 when the database is first created. The message for ORA-0118 suggests "export the database and re-create it with a higher limit for datafiles[MAXDATAFILE]."

However, there is a much easier way to get around the problem, that is, to create a new control file from the existing one. The steps are:

1. Get a backup trace of the existing control file (by "alter database backup control file to trace;")
2. Modify the trace file and use it as a script in Step 7. (You can keep the REUSE option. You do not need to provide size for the datafiles.)
3. Shutdown the database.
4. If you do not have a backup, a full databse backup is recommended.
5. Rename the existing control file(s) to different names (at the OS level).
6. Connect internal
7. Run the script modified in Step 2.

If you get the "Statement processed" message, the database will be open with the new control file.

If your database is 7.3.x, but not 7.3.2.3.0, you would have no trouble creating a new control file in about 10 minutes.

If your database is 7.3.2.3.0 and you have resized any one of your datafiles smaller (e.g., 80M) than the original create size (e.g., 100M), you would encounter error at Step 7. At this point, you could call Oracle support to request a patch or you could bypass the problem (a bug) by opening the database with the original control file(s), resizing the datafile in question to 100M or larger, and repeating Steps 1-7.

Good luck.

Ying-fen Hu

When you try to add a datafile to your database and get the ORA-01118 (not ORA-00059) error, it is time to set a higher value for the MAXDATAFILES parameter, which defaults to 30 at database creation time. The message for ORA-01118 suggests "export the database and re-create it with a higher limit for datafiles[MAXDATAFILES]."

However, there is a much easier way to get around the problem, that is, to create a new control file from the existing one. The steps are:

1. Get a backup trace of the existing control file.
2. Modify the trace file and use it as a script. You can keep the REUSE option (for VMS you have to keep the REUSE option). Except for 7.1.6, you do not need to provide size for the datafiles.
3. Shutdown the database.
4. Full database backup is recommended.
5. Rename the existing control file(s) to backup.
6. Connect internal.
7. Run the script

If you get the "Statement processed" message, the database will be open with the new control file.

I am not sure when to take out the REUSE option and whether I need to remove the existing control files if I do not use the REUSE option.

At work, the 7 steps just described work for 7.1.3.2 on VMS and a 7.3.2.3 test database on Digital Unix.

Hope this helps.
Ying-fen Hu Received on Fri May 08 1998 - 16:23:45 CDT