The Oracle (tm) Users' Co-Operative FAQ
How do I increase the number of files in the database.
| Author's name: Norman Dunbar; Author's Email: NormanDunbar@cwcom.net |
Date written: 26 June 2001 Oracle version(s): 8.1.7 |
| I am getting error ORA-01520 / ORA-00059 when trying to add a new tablespace / data file, what should I do ?. |
Starting with the easy case first. Error ORA-00059 means that you have exceeded the initSID.ora parameter db_files when you added or tried to add a new data file. To cure this problem, edit the db_files parameter and 'bounce' the instance. When it is back up again, you can add the data file with no further problems, except possibly the ORA-01520 one.
ORA-01520 means that the number of files in the database would exceed the maxdatafiles parameter specified when the database was created. This is different from the initSID.ora parameter db_files as above and specifies the absolute maximum number of files that the database is allowed to have. To cure this problem, some more work is required. The steps involved are :
- Create a control file trace using the command alter database backup controlfile to trace;
- Shutdown the instance. You could now take a full cold backup - just in case anything bad happens.
- Edit the trace file - found in user_dump_dest
to :
- remove all the comments from the top and bottom of the script and change the value for maxdatafiles to a new suitable value.
- remove the startup nomount and recover database commands from the script
- Still in the editor, remove the word reuse from the create controlfile ... command, then save the file with a meaningful name - controlfile.sql for example.
- Edit the initSID.ora file and set the value for db_files to be 5 less than the new maxdatafiles value. This gives you a small amount of leeway if it ever happens again. (This is simply my preference.)
- From the initSID.ora file, obtain the names and locations of all the current control files, then rename or delete them. (I always get uncomfortable deleting a control file !)
- Startup nomount.
- Run the script file - controlfile.sql - created above to create new control file(s) with an updated value for maxdatafiles.
- Open the instance with alter database open;
And that is all there is to it. Some comments may be in order.
Why did I remove the startup nomount and alter database open commands from the script if I have to run them myself anyway? The answer is that I prefer to be in control of starting and opening my databases, especially if I am doing and work like this which I could get wrong. Leaving it all up to the script could, in some circumstances, cause more problems.
Why did I take a cold backup before starting? For my own sanity and because I would be able to recover back to a known good database if anything went wrong. I'm not really sure what could go wrong, but as I am deleting all the control files, this worries me. I think that only a backup of the control files would really be required, but I've never had it go wrong yet to find out!
Why set db_files to be 5 less than the maxdatafiles value? Well, it means that if I ever run out of datafiles again, I can at least add an extra 5 datafiles without having to take a backup of the database. This reduces the amount of down time for this instance.
The following is an example session on an Oracle 7.3.4 instance that I use for testing and playing with.
First of all, use svrmgrl to create a trace file and close down the instance :
dbadmin@hp015> svrmgrl Oracle Server Manager Release 2.3.4.0.0 - Production Copyright (c) Oracle Corporation 1994, 1995. All rights reserved. Oracle7 Server Release 7.3.4.4.1 - Production PL/SQL Release 2.3.4.4.1 - Production SVRMGR> connect internal Connected. SVRMGR> alter database backup controlfile to trace; Statement processed. SVRMGR> shutdown Database closed. Database dismounted. ORACLE instance shut down.
Shell out to Unix to do the editing etc :
SVRMGR> host $ cd udump $ ls ora_18842.trc $ mv ora_18842.trc controlfile.sql $ vi controlfile.sql
The following is how the original trace file looks when first opened.
Dump file /app/oracle734/admin/dbadmin/udump/ora_18842.trc Oracle7 Server Release 7.3.4.4.1 - Production PL/SQL Release 2.3.4.4.1 - Production ORACLE_HOME = /app/oracle734/product/ System name: HP-UX Node name: hp015 Release: B.10.20 Version: A Machine: 9000/800 Instance name: DBADMIN Redo thread mounted by this instance: 1 Oracle process number: 9 Unix process pid: 18842, image: oracleDBADMIN *** SESSION ID:(8.9) 2001.06.26.13.19.28.979 *** 2001.06.26.13.19.28.979 # The following commands will create a new control file and use it # to open the database. # No data other than log history will be lost. Additional logs may # be required for media recovery of offline data files. Use this # only if the current version of all online logs are available. STARTUP NOMOUNT CREATE CONTROLFILE REUSE DATABASE "DBADMIN" NORESETLOGS NOARCHIVELOG MAXLOGFILES 32 MAXLOGMEMBERS 2 MAXDATAFILES 30 MAXINSTANCES 8 MAXLOGHISTORY 800 LOGFILE GROUP 1 ( '/data1/DBADMIN/redoDBADMIN1a.log', '/data2/DBADMIN/redoDBADMIN1b.log' ) SIZE 1000K, GROUP 2 ( '/data1/DBADMIN/redoDBADMIN2a.log', '/data2/DBADMIN/redoDBADMIN2b.log' ) SIZE 1000K, GROUP 3 ( '/data1/DBADMIN/redoDBADMIN3a.log', '/data2/DBADMIN/redoDBADMIN3b.log' ) SIZE 1000K DATAFILE '/data3/DBADMIN/system01.dbf', '/data3/DBADMIN/rbs01.dbf', '/data4/DBADMIN/temp01.dbf', '/data5/DBADMIN/cc01.dbf', '/data5/DBADMIN/users01.dbf', '/data5/DBADMIN/cc01indx.dbf' ; # Recovery is required if any of the datafiles are restored backups, # or if the last shutdown was not normal or immediate. RECOVER DATABASE # Database can now be opened normally. ALTER DATABASE OPEN;
Everything down to the create controlfile command will have to be removed, remove the word reuse from that command as well, then get rid of everything after the closing semi-colon terminating the list of datafiles. The value for maxdatafiles has been increased from 30 to 50. The script now looks like the following :
CREATE CONTROLFILE DATABASE "DBADMIN" NORESETLOGS NOARCHIVELOG MAXLOGFILES 32 MAXLOGMEMBERS 2 MAXDATAFILES 50 MAXINSTANCES 8 MAXLOGHISTORY 800 LOGFILE GROUP 1 ( '/data1/DBADMIN/redoDBADMIN1a.log', '/data2/DBADMIN/redoDBADMIN1b.log' ) SIZE 1000K, GROUP 2 ( '/data1/DBADMIN/redoDBADMIN2a.log', '/data2/DBADMIN/redoDBADMIN2b.log' ) SIZE 1000K, GROUP 3 ( '/data1/DBADMIN/redoDBADMIN3a.log', '/data2/DBADMIN/redoDBADMIN3b.log' ) SIZE 1000K DATAFILE '/data3/DBADMIN/system01.dbf', '/data3/DBADMIN/rbs01.dbf', '/data4/DBADMIN/temp01.dbf', '/data5/DBADMIN/cc01.dbf', '/data5/DBADMIN/users01.dbf', '/data5/DBADMIN/cc01indx.dbf' ; :wq!
Edit the initSID.ora file and change the db_files parameter to be 45 which is 5 less than the new value for maxdatafiles:
$ vi $ORACLE_HOME/dbs/initDBADMIN.ora DB_FILES = 45 :wq!
Remove the controlfiles after backing up the entire database :
$ ./secure_DBADMIN.sh All data files, controlfiles & redo files secured for DMADMIN. $ rm /data1/DBADMIN/control_1.ctl $ rm /data2/DBADMIN/control_1.ctl $ rm /data3/DBADMIN/control_1.ctl
Exit from the shell back into svrmgrl to finish off the job :
$exit SVRMGR> startup nomount ORACLE instance started. Total System Global Area 92262392 bytes Fixed Size 38984 bytes Variable Size 10237872 bytes Database Buffers 81920000 bytes Redo Buffers 65536 bytes SVRMGR> @controlfile Statement processed. SVRMGR> alter database open; Statement processed.
Further reading:
Oracle 8 Server Administrator's Guide, section 6, Managing control files.
