exceed limit of datafile numbers

Hi Guys,
I tried to create a new tablspace and of course a datafile but i got a error :
cannot add any more database files : limit of 30 exeeded.
Please tell me what i should do? Oracle version 7.1.4 and Unix os. Thanks in advance.

Jeanie :)

Increase the parameter, db_files in init.ora and bounce the db.

That limit is setup when you create your database. I think the only way to change it is to recreate your control file with a larger MAXDATAFILES entry.

Ah, I was assuming db_files had already topped out...

Hmm, strange. I did that once before.

You may have to recreate the control file with more number of datafiles.

I guess you can issue "alter database backup controlfile to trace noresetlogs" and edit the .trc file created accordingly.
(the .trc file is usually created in the UDUMP directory)

Startup the DB in NOMOUNT and execute the edited tracefile.

Hi guys,
I appreciate your help very much. I'd like to ask you this:
I found out that there are 2 unused datafiles (contains no file) on our database. Could they be deleted or removed? If so, please tell me how. Thanks thousands times.

Jn1971 :)

I don't see any easy way of accomplishing this.

If the tablespace has just the datafiles you want to remove and/or you do not care about the data in that tablespace you can drop the tablespace and recreate it.
(in such a situation, you may use "drop tablespace table_space including contents")

The otherway is to do an export of the objects from the tablespace where the datafile resides, drop the tablespace, recreate it accordingly, import the object/data.

Note:

By
"I found out that there are 2 unused datafiles (contains no file) on our database", do you mean that the files are not available on the OS filesystem but the entry is there in the DB?

I would advise you to go and change your control file with new maxdatafiles limit than try dropping unused datafiles. The pain you will be taking for dropping datafiles(unused) will be much more than recreating controlfile, even from the risk perspective and flexibility.

What if tomorrow you run outta space again ? that time you will not be left with unused datafiles to drop and accomadate new datafiles. So, Best bet is change maxdatafiles limit by recreating control file.

If you want steps search for 'rename database', you have bunch of postings on how to recreate controlfile.

Remember! you don't want to rename database but redefine maxdatafiles limit while following the steps in postings...

Hi guys,
I tried to recreate our controlfile but i got an error: can not reuse the old controlfile b'cause its size (100) is smaller than needed (130). Then i bounced the database successfully but when i tried to connect to it, i got the error:

ORA-01033:ORACLE initialization or shutdown in progress.

I waited and waited and tried to access the database again but unsuccessfully. Please tell what i did wrong and what i should do to fix the problem. Thanks alot in advance.

Jn1971 :(

I guess, your create control file didn't go succesfully.

(check whether ur original conrol fle is overwritten)

Shutdown the DB with abort option, if you cannot shutdown it with "immedite".

startup the DB; --needed only if u use "shutdown ABORT"

Shutdown the DB in Normal/Immediate mode; -- needed only if the last shutdown was not normal/immediate

Move/rename the old Control file (OS command) --just in case needed

Startup nomount ;

issue the create controlfile ;

Alter database mount ;

alter database open ;

The problem is with the REUSE clause of the create controlfile statement. Increasing maxdatafiles increases the size of the new controlfile and it cannot reuse the file already on disk.
Kill the instance
Do a cold backup
Delete the old control file at OS level
Delete the reuse clause from your trace file/script.
Run the script.

Hi guys,
I followed these steps:
Kill the instance (test & prod)
Do a cold backup
Delete the old control file at OS level
Delete the reuse clause from your trace file/script.
Run the script.
And i got these errors:
SQLDBA> connect internal
Connected.
setenv oracle_sid test
sqldba lmode=y
SQLDBA> connect internal
SQLDBA> shutdown immediate
SQLDBA> @recreate_arc_ctrl.sql
ORACLE instance started.
CREATE CONTROLFILE USE DATABASE "TEST" NORESETLOGS NOARCHIVELOG
*
ORA-01967: invalid option for CREATE CONTROLFILE
ORA-01507: database not mounted
ORA-01507: database not mounted

SQLDBA> alter database mount;
Statement processed.
SQLDBA> alter database open;
Statement processed.
SQLDBA> select * from v$database;
NAME CREATED LOG_MODE CHECKPOINT ARCHIVE_CH
--------- -------------------- ------------ ---------- ----------
PROD 02/08/96 15:46:15 NOARCHIVELOG 1.9260E+12 1.9260E+12
1 row selected.

I guess my server does not recognize my test instance anymore. Is it crashed? Please help.

Thanks,
Jn1971 :(

Delete the word "USE" from your create controlfile command.
Also edit the script to add a direct reference to your inittest.ora parameter file as follows:
startup nomount pfile=.................

Hi Jdoyle,
I did what you suggested but i still get an error:
error in identifying file '....../dbora.dbf'.

I think my test instance is crashed. I have daily backup is sent to server's tape. I have not done restoring bk before. Could you tell me how i retract it out? Your help is appreciated very much.

Jn1971

do you have the particular file .../dbora.dbf in the particular location as pointed by the error message?

If you move the file somewhere else, edit the controlfile accordingly to have that file. (Make sure u r not using the file from another DB)

Some clarification is in order here.
Remember, always, ONE THING AT A TIME.
First, you have two databases, PROD and TEST. The problem with the maxdatafiles exceeded was, I believe, with your PROB db, yes? Assuming this is your production database it must be your first priority.
Re: PROD
Is this database working?
Have you done the alterations to the controlfile trace dump, and is your specified parameter file path correct and pointing to an existing file?

Re: TEST; several questions:
Is this DB on the same machine as PROD?
Is it running in ARCHIVELOG mode?
What is your environment, OS, etc.
What exactly are you trying to do with test? We cannot deal with recovery issues just yet, and perhpas may not need to. You probably won't need to restore from tape.

Get back to me on this.

Hi Jdoyle,


Re: PROD
Is this database working?
Have you done the alterations to the controlfile trace dump, and is your specified parameter file path correct and pointing to an existing file?


Yes my prod instance is on and working fine.
I altered the controlfile trace dump as suggested. My parameter file path correct and pointing to an existing file.


Re: TEST; several questions:
Is this DB on the same machine as PROD?
Is it running in ARCHIVELOG mode?
What is your environment, OS, etc.
What exactly are you trying to do with test? We cannot deal with recovery issues just yet, and perhpas may not need to. You probably won't need to restore from tape.

Yes the test db is on the same machine as PROD.
It is not running in ARCHIVELOG mode.
my OS is unix. Oracle version is 7.1.4
Since i have a exceed maxdatafile number, I am trying to modify the maxdatafile number on the test instance 1st and if it works i'll do it on the PROD one.

I'd like to tell you this:

I checked audit files and trace files of the test instance: i note that : the test instance is off @ 9am this morning when i ran the recreate_controlfile_script first time.
Everytime i try to connect to the test db i got an error:
ora-01033: oracle initialization or shutdown in progress.
Everytime i try to bounce test db (after setenv oracle_sid test)
server always shutdown and startup the PROD db not the TEST. Its seem to me that the TEST instance is hanging now.

I hope you get the pictrue of my problem.

Thanks,
Jn1971

Have you been able in the past to run both instances on the same machine?
Kill the test instance using OS commands. I don't think you'll be able to shut it down using Oracle commands such as SVRMGRL>SHUTDOWN ABORT, but you can try this first. Use OS commands to verify that all TEST processes are down.
BTW, I did all of the following for my own database and it worked for me.

1.) Move all control files for the test database to another location for temporary storage. Make sure no control files exist in the locations specified in the parameter file.
2.) edit the trace dump file, remove all the garbage at the top and remove the startup nomount command. The first operational command in the file should be the CREATE CONTROLFILE statement
3.) SVRMGRL>Startup nomount pfile=inittest.ora (or whatever the complete path and filename for your TEST initialization parameter file is.)
Does this work?
4.) svrmgrl>@recreate_control_file_script

This should do it for you.

if each shutdown brings down the prod, that seems like the environment variable is not getting set properly.

Depends on the "shell" you may have to use "set" or "export" ORACLE_SID. Once it is set, echo $ORACLE_SID before starting the instance and see the value is displaying properly (whatever you set)

Since u r duplicating the PROD db, u have to change the filenames in ur control file to point to the new datafiles and also you may have to copy the files from the PROD to the new location before creating the control file. (I believe u already did that).

Hi guys,
I looked at my test instance's alert log and believe that i did make a mess on my test instance. Please look at my today test's alert message and please suggest what i should do:

Fri Jan 26 08:36:13 2001
Shutting down instance (immediate)
License high water mark = 1
Fri Jan 26 08:36:13 2001
alter database close normal
Fri Jan 26 08:36:13 2001
SMON: disabling tx recovery
SMON: disabling cache recovery
Fri Jan 26 08:36:14 2001
Thread 1 closed at log sequence 999
Current log# 3 seq# 999 mem# 0: /usr2/oracle/arc/dblogrtp3.dbf
Fri Jan 26 08:36:14 2001
Completed: alter database close normal
Fri Jan 26 08:36:14 2001
alter database dismount
Completed: alter database dismount
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
LICENSE_MAX_USERS = 0
Starting up ORACLE RDBMS Version: 7.1.4.1.0.
System parameters with non-default values:
processes = 200
shared_pool_size = 6000000
control_files = $ORACLE_HOME/dbs/c1arc.ctl, /usr2/oracle/arc/c2arc.ctl, /usr3/oracle/arc/c3arc.ctl
db_block_buffers = 1200
log_archive_start = TRUE
log_buffer = 163840
log_checkpoint_interval = 10000
db_files = 20
rollback_segments = rb_1, rb_2, rb_3, rb_4, rb_5, rb_6, rb_7, rb_8
sequence_cache_entries = 100
sequence_cache_hash_buckets= 89
remote_login_passwordfile= NONE
mts_service = arc
mts_servers = 0
mts_max_servers = 0
mts_max_dispatchers = 0
audit_trail = NONE
sort_area_retained_size = 65536
db_name = arc
open_cursors = 200
ifile = $ORACLE_HOME/dbs/cfarc.ora
background_dump_dest = $ORACLE_HOME/rdbms/log
user_dump_dest = $ORACLE_HOME/rdbms/log
max_dump_file_size = 10240
core_dump_dest = $ORACLE_HOME/dbs
PMON started
DBWR started
ARCH started
LGWR started
RECO started
Fri Jan 26 08:47:04 2001
alter database mount exclusive
Fri Jan 26 08:47:04 2001
Completed: alter database mount exclusive
Fri Jan 26 08:47:04 2001
alter database open
Fri Jan 26 08:47:05 2001
Thread 1 opened at log sequence 999
Current log# 3 seq# 999 mem# 0: /usr2/oracle/arc/dblogrtp3.dbf
Fri Jan 26 08:47:06 2001
SMON: enabling cache recovery
SMON: enabling tx recovery
Fri Jan 26 08:47:06 2001
Completed: alter database open
Fri Jan 26 08:50:33 2001
alter database backup controlfile to trace noresetlogs
Completed: alter database backup controlfile to trace noreset...
Fri Jan 26 08:53:40 2001
Shutting down instance (immediate)
License high water mark = 1
Fri Jan 26 08:53:40 2001
alter database close normal
Fri Jan 26 08:53:40 2001
SMON: disabling tx recovery
SMON: disabling cache recovery
Fri Jan 26 08:53:42 2001
Thread 1 closed at log sequence 999
Current log# 3 seq# 999 mem# 0: /usr2/oracle/arc/dblogrtp3.dbf
Fri Jan 26 08:53:42 2001
Completed: alter database close normal
Fri Jan 26 08:53:42 2001
alter database dismount
Completed: alter database dismount
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
LICENSE_MAX_USERS = 0
Starting up ORACLE RDBMS Version: 7.1.4.1.0.
System parameters with non-default values:
(info is deleted by jn1971)
PMON started
DBWR started
ARCH started
LGWR started
RECO started
Fri Jan 26 08:54:49 2001
CREATE CONTROLFILE REUSE DATABASE "ARC" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 50
MAXINSTANCES 1
MAXLOGHISTORY 100
LOGFILE
GROUP 1 '/usr2/oracle/arc/dblogrtp1.dbf' SIZE 2M,
GROUP 2 '/usr2/oracle/arc/dblogrtp2.dbf' SIZE 2M,
GROUP 3 '/usr2/oracle/arc/dblogrtp3.dbf' SIZE 2M,
GROUP 4 '/usr2/oracle/arc/dblogrtp4.dbf' SIZE 2M
DATAFILE
'/usr3/oracle/arc/dborartp.dbf' SIZE 30M,
'/usr2/oracle/arc/rtprb01.dbf' SIZE 200M,
'/usr3/oracle/arc/rtpdat01.dbf' SIZE 150M,
'/usr2/oracle/arc/rtptemp01.dbf' SIZE 30M,
'/usr3/oracle/arc/rtpidx01.dbf' SIZE 30M,
'/usr2/oracle/arc/rtptool01.dbf' SIZE 10M,
'/usr3/oracle/arc/prestdata1.dbf' SIZE 90M,
'/usr2/oracle/arc/rtpuser01.dbf' SIZE 50M,
'/usr2/oracle/arc/rtptempu01.dbf' SIZE 50M,
'/usr3/oracle/arc/prestdata2.dbf' SIZE 90M,
'/usr3/oracle/arc/rtpdat02.dbf' SIZE 50M,
'/usr3/oracle/arc/rtpdat04.dbf' SIZE 100M,
'/usr3/oracle/arc/rtpidx02.dbf' SIZE 30M,
'/usr3/oracle/arc/rtpidx03.dbf' SIZE 60M,
'/usr3/oracle/arc/rtpidx04.dbf' SIZE 30M,
'/usr3/oracle/arc/rtpidx05.dbf' SIZE 100M,
'/usr3/oracle/arc/rtpidx06.dbf' SIZE 100M,
'/usr2/oracle/arc/rtptemp02.dbf' SIZE 50M,
'/usr3/oracle/arc/rtpdat03.dbf' SIZE 100M,
'/usr2/oracle/arc/rtptempu02.dbf' SIZE 50M
Fri Jan 26 08:54:50 2001
ORA-1503 signalled during: CREATE CONTROLFILE REUSE DATABASE "ARC" NORESETLOG...
Fri Jan 26 08:54:50 2001
ALTER DATABASE OPEN
ORA-1507 signalled during: ALTER DATABASE OPEN...
Fri Jan 26 09:01:58 2001
Starting ORACLE instance (normal)
Fri Jan 26 09:01:58 2001
CREATE CONTROLFILE REUSE DATABASE "ARC" NORESETLOGS NOARCHIVELOG
ORA-1503 signalled during: CREATE CONTROLFILE REUSE DATABASE "ARC" NORESETLOG...
(detial is deleted by jn1971)
Fri Jan 26 09:01:58 2001
alter database recover DATABASE
ORA-1507 signalled during: alter database recover DATABASE...
Fri Jan 26 09:01:58 2001
alter database recover cancel
ORA-1507 signalled during: alter database recover cancel...
Fri Jan 26 09:01:58 2001
ALTER DATABASE OPEN
ORA-1507 signalled during: ALTER DATABASE OPEN...

ARC is sid of my test instance. Please help.

Thanks,
jn1971 :(

Check your alert log! see what it says. We can proceed from there.

This is what you have to do:


. Shutdown your instance
. Make sure that there is no background processing running for this instance
(run ps -ef | grep -i oracle, kill if required or do a shutdown abort)
. Its good idea to move the existing redologs to a new location
sometimes you might need it for recovery purposes.
. move or rename your control files (you can also use the reuse clause)
. Verify that your create control file is something like this :

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "PM1" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 30
MAXINSTANCES 1
MAXLOGHISTORY 226
LOGFILE
GROUP 1 '/oradata/pm1/vg01_01/redo_01.log' SIZE 126M,
GROUP 2 '/oradata/pm1/vg01_01/redo_02.log' SIZE 126M,
GROUP 3 '/oradata/pm1/vg01_01/redo_03.log' SIZE 126M
DATAFILE
'/oradata/pm1/vg01_01/system_01.dbf',
'/oradata/pm1/vg01_01/rbs1_01.dbf',
'/oradata/pm1/vg01_01/rbs2_01.dbf',
'/oradata/pm1/vg01_01/users_01.dbf',
'/oradata/pm1/vg01_01/tools_01.dbf',
'/oradata/pm1/vg01_01/pm1_med01.dbf'
;

I am assuming you have a script that does the above eg :makecontrol.sql
. you may have to use resetlogs (its been a while since I did this)
. make sure your sid is set accordingly (. oraenv)
. start a server mamager session :
. svrmgrl
. connect internal
. @makecontrol.sql
. you can now try an alter database open command to see if the database will come up
. if it says it needs recovery, do the recovery (recover database or
recover database until cancel).
. you should not have any problems bringing your database back up!.

Hi guys And Gram2000,
My instance is fine know and i did recreate the controlfiles successfully. The prolem was that I typed

setenv oracle_sid test
not
setenv ORACLE_SID test

Unix does take serious about case sensitivity.

I could shut down the instance and follow your suggestions to create controlfiles.

THANKS VERY MUCH ALL OF YOU OUT THERE FOR HELPING ME.

THANKS,
jn1971