Increase parameter db_files for the production instance

[Former Member]

Hello All,

We need add to more data files to Tablespace PSAPPP1, currently we

have 247 data files in the database. As per the parameter db_files set

in system as 254, we have to increase the same as early as possible. We

need to know what should be the new value of this parameter for our

System PP1 as well as any other parameter need to be changes at AIX /

ORACLE /SAP level along with that so that there will not be any

problems of Tablespace getting full.

Thanks & regards

Abhay

Attaching my initPP1.ora here

-

---

########################################################################

1. This init.ora profile is a transparent copy of Oracle spfile. #

2. It was created by BRARCHIVE at 2009-04-13 17.30.01. #

3. Please do not change this file! Maintain only the spfile. #

4. You can use BRSPACE for the altering of database parameters: #

5. brspace -f dbparam #

########################################################################

PP1.__db_cache_size=4511728926

*._b_tree_bitmap_plans=FALSE

*._fix_control='4728348:OFF'

*._in_memory_undo=FALSE

*._index_join_enabled=FALSE

*._optim_peek_user_binds=FALSE

*._optimizer_mjc_enabled=FALSE

*._sort_elimination_cost_ratio=10

*.background_dump_dest='/oracle/PP1/saptrace/background'

*.compatible='10.2.0'

*.control_file_record_keep_time=30

*.control_files='/oracle/PP1/origlogA/cntrl/cntlrPP1.dbf','/oracle/PP1/sapdata1/cntrl/cntrlPP1.dbf','/oracle/PP1/origlogB/cntrl/cntrlPP1.dbf'

*.core_dump_dest='/oracle/PP1/saptrace/background'

*.db_block_size=8192

*.db_cache_size=4511728926

*.db_files=254

*.db_name='PP1'

*.db_writer_processes=4

*.dbwr_io_slaves=4

*.dml_locks=4000

*.event='10162 trace name context forever, level 1','38087 trace name context forever, level 1'

*.fal_client='PP1'

*.fal_server='STDBYPP1'

*.fast_start_mttr_target=900

*.filesystemio_options='SETALL'

*.java_pool_size=67108864

*.large_pool_size=8388608

*.local_listener='LISTENER_PP1'

*.log_archive_dest_1='LOCATION=/oracle/PP1/oraarch/PP1arch MANDATORY'

*.log_archive_dest_2='SERVICE=STDBYPP1 delay=60 reopen=60'

*.log_archive_dest_state_1='ENABLE'

*.log_archive_dest_state_2='ENABLE'

*.log_buffer=1048576

*.log_checkpoint_interval=0

*.log_checkpoints_to_alert=TRUE

*.max_dump_file_size='20000'

*.open_cursors=800

*.optimizer_index_caching=50

*.optimizer_index_cost_adj=20

*.parallel_execution_message_size=16384

*.parallel_max_servers=10

*.parallel_threads_per_cpu=1

*.pga_aggregate_target=6015638568

*.processes=300

*.query_rewrite_enabled='FALSE'

*.recyclebin='off'

*.remote_archive_enable='TRUE'

*.remote_login_passwordfile='EXCLUSIVE'

*.remote_os_authent=TRUE

*.replication_dependency_tracking=FALSE

*.session_max_open_files=20

*.sessions=300

*.sga_max_size=9023457853

*.shared_pool_size=4511728926

*.sort_area_retained_size=0

*.sort_area_size=2097152

*.standby_archive_dest='/oracle/PP1/stdarch/PP1arch'

*.standby_file_management='AUTO'

*.star_transformation_enabled='TRUE'

*.statistics_level='typical'

*.streams_pool_size=50331648

*.timed_statistics=TRUE

*.undo_management='AUTO'

*.undo_retention=43200

*.undo_tablespace='PSAPUNDO'

*.user_dump_dest='/oracle/PP1/saptrace/usertrace'

*.workarea_size_policy='AUTO'

-

---

[Former Member]

Hello Abhay

Please go like this we have faced this problem in our production server

maximum number of DB_FILES exceeded

we just need to increase this parameter

and startup our database

SQL> show parameter db_files

NAME TYPE VALUE

-

---

-

---

db_files integer 6

SQL> create tablespace test

2 datafile 'd:\test01.dbf' size 1m;

create tablespace test

*

ERROR at line 1:

ORA-00059: maximum number of DB_FILES exceeded

alter system set db_files = 200 scope = spfile;

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> create pfile from spfile;

File created.

SQL> --change db_files parameter in init<db>.ora file

SQL> create spfile from pfile;

File created.

SQL> startup

ORACLE instance started.

Total System Global Area 171966464 bytes

Fixed Size 787988 bytes

Variable Size 145488364 bytes

Database Buffers 25165824 bytes

Redo Buffers 524288 bytes

Database mounted.

Database opened.

SQL> show parameter db_files

NAME TYPE VALUE

-

---

-

---

db_files integer 200

SQL>

[Former Member]

Thanks Shishir,

I followed your procedure and it really works.

Issue resolved.

Regards.

Mohammad Shoaib

[Former Member]

Hi,

Here's what I found, best and easy way of changing the maxdatafile limit:

1. Shutdown database; Backup database

2. Start up database

3. From sqlplus as sysdba, type: alter database backup controlfile to trace;

4. Type: shutdown immediate:

5. Go to the operating system and go to the USER_DUMP_DEST directory

6. Find the newest trace file

7. Edit the trace file and change MAXDATAFILES to the new value. You will also need to delete all of the lines prior to the line that begins: STARTUP NOMOUNT. See sample text below (the example is for a database using ARCHIVELOG):

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "ORACLE" RESETLOGS ARCHIVELOG

MAXLOGFILES 32 MAXLOGMEMBERS 2

_+MAXDATAFILES 32+_

MAXINSTANCES 16

MAXLOGHISTORY 1600

LOGFILE

GROUP 1 'D:\ORAWIN95\DATABASE\LOG2ORCL.ORA' SIZE 200K,

GROUP 2 'D:\ORAWIN95\DATABASE\LOG1ORCL.ORA' SIZE 200K DATAFILE 'D:\ORAWIN95\DATABASE\SYS1ORCL.ORA', 'D:\ORAWIN95\DATABASE\USR1ORCL.ORA', 'D:\ORAWIN95\DATABASE\RBS1ORCL.ORA', 'D:\ORAWIN95\DATABASE\TMP1ORCL.ORA' ;

1. Database can now be opened normally. ALTER DATABASE OPEN RESETLOGS;

8. From sqlplus as sysdba, run the edited trace file from step 7.

9. Shutdown database and backup database

[Former Member]

Hi AB,

So you are using spfile...then i suggest the parameter change as below:

open your pfile, increase the parameter value

shutdown the database

open your database with pfile

then recreate spfile from pfile

Regards

Nick Loy

[sunny_pahuja2]

Hi,

Check your parameter as per SAP note 830576 and change your parameter as per this note.

Thanks

Sunny