|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 ?.|
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 22.214.171.124.0 - Production Copyright (c) Oracle Corporation 1994, 1995. All rights reserved. Oracle7 Server Release 126.96.36.199.1 - Production PL/SQL Release 188.8.131.52.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 184.108.40.206.1 - Production PL/SQL Release 220.127.116.11.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.
Oracle 8 Server Administrator's Guide, section 6, Managing control files.
Back to top
Back to index of questions