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: Oracle@bountifulsolutions.co.uk

Date written: 26/06/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 ?.

Back to index of questions


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 :

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.


Back to top

Back to index of questions