extended datafile

[Former Member]

Dear Team,

After restoring my Test server. When I am extended datafile . It give below error messege

Tablespace extension main menu

1 = Extend tablespace

2 - Show tablespaces

3 - Show data files

4 - Show disk volumes

5 * Exit program

6 - Reset program status

Standard keys: c - cont, b - back, s - stop, r - refr, h - help

-

---

BR0662I Enter your choice:

2

BR0280I BRSPACE time stamp: 2010-08-11 20.57.50

BR0663I Your choice: '2'

BR0280I BRSPACE time stamp: 2010-08-11 20.57.51

BR0301E SQL error -1157 at location BrTspListGet-15, SQL statement:

'OPEN curs_36 CURSOR FOR'

'SELECT TABLESPACE_NAME, BYTES FROM DBA_FREE_SPACE UNION ALL SELECT TABLESPACE_NAME, BYTES_FREE + BYTES_USED FROM V$TEMP_SPACE_HEADER UNION ALL SELECT TABLESPACE_NAME, NVL(BYTES_USED, 0) * -1 FROM GV$TEMP_EXTENT_POOL ORDER BY 1'

ORA-01157: cannot identify/lock data file 255 - see DBWR trace file

ORA-01110: data file 255: '/oracle/PRD/sapdata2/temp_1/temp.data1'

BR0669I Cannot continue due to previous warnings or errors - you can go back to repeat the last action

BR0280I BRSPACE time stamp: 2010-08-11 20.57.51

BR0671I Enter 'b[ack]' to go back, 's[top]' to abort:

why this give this messge .how to extended datafile.

[Former Member]

Hi all,

i am also facing the same issue...

please provide the solution ASAP i have to increase the the tablespace in PRD ...

regards,

Ashish

[former_member206552]

hi ashish

are you facing the exact same error where it is complaining about a temp datafile after the system was restored ?

if yes then recreate the temp tablespace

best regards

marius

[former_member204746]

OK, this might be a bug.

update BR*Tools and try again.

[former_member204746]

I moved this from from "SAP on Unix" forum.

error: cannot identify/lock data file 255 - see DBWR trace file ORA-0111

go in /oracle/SID/saptrace/usertrace and find the latest log. it contains more info.

It looks like you might have more than 255 data files. chedck the FILES parameter in initSID.ora

[Former Member]

Dear ,

I have checked /oracle/SID/saptrace/usertrace .

But in this it does not show any log.

I hace check Space Overview via DB02 its show size 0.00 GB Free space 0.00 GB

When I check Tablespaces---> Overview

In main data its show nothing.

why data space is not showing in DB02

It will be very helpfull for me if any body will help me.

regards

Sunil

[former_member204746]

DB02 not showing anything? schedule all jobs from SAP note 16083.

in file initsid.ora, what is the value for FILES ?

[Former Member]

Dear

I have schedule All house keeping job via SM36.

I checked initPRD.ora file its show below db_files = 254.

1. number of processes

2. sessions = 1.2 * processes

processes = 80

sessions = 96

1. AUDITING AND STATISTICS

1. sql_trace=TRUE

2. audit_trail = true

3. db_block_lru_extended_statistics = 1000

4. db_block_lru_statistics = true

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

1. PART III, STATIC PARAMETERS #

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

1. DB-NAME

db_name = PRD

1. DB-BLOCKSIZE

db_block_size = 8192

1. DB-FILES

db_files = 254

1. OPTIMIZER MODE

#optimizer_mode = choose

#optimizer_search_limit = 3

1. PATHS / DESTINATIONS / TRACES

1. /oracle/PRD/saptrace/background: trace files of the background

2. processes

3. /oracle/PRD/saptrace/usertrace: trace files of the user processes

4. log_archive_dest is a destination, not a path.

5. The archivefiles get the name

6. /oracle/PRD/oraarch/PRDarch<thread#>_<log#>

background_dump_dest = /oracle/PRD/saptrace/background

user_dump_dest = /oracle/PRD/saptrace/usertrace

core_dump_dest = /oracle/PRD/saptrace/background

log_archive_dest = /oracle/PRD/oraarch/PRDarch

#log_archive_format = %t_%s

Regards

[former_member524429]

Hi,

Please increase db_files value to higher value.

Please check that the File system on which temp files are located are mounted properly or not .

Also check the status of affected data-file by executing the following commands.

SELECT * FROM v$recover_file;

SELECT * FROM DBA_DATA_FILES WHERE FILE_ID=255;

Regards,

Bhavik G. Shroff

[former_member204746]

Thsi is exactly what I told you earlier, you have set your Oracle system to a limit of 254 files and you are trying to add a 255th file.

set db_files to 600 in file initPRD.ora and restart your database, this will fix your issue.

good luck.

[Former Member]

Dear,

Now I have update db_files 255 to 600 ,But now its give below messege

BR0280I BRSPACE time stamp: 2010-08-13 22.26.49

BR0656I Choice menu 301 - please make a selection

-

---

Tablespace extension main menu

1 = Extend tablespace

2 - Show tablespaces

3 - Show data files

4 - Show disk volumes

5 * Exit program

6 - Reset program status

Standard keys: c - cont, b - back, s - stop, r - refr, h - help

-

---

BR0662I Enter your choice:

1

BR0280I BRSPACE time stamp: 2010-08-13 22.26.53

BR0663I Your choice: '1'

BR0280I BRSPACE time stamp: 2010-08-13 22.26.53

BR0301E SQL error -1157 at location BrTspListGet-15, SQL statement:

'OPEN curs_36 CURSOR FOR'

'SELECT TABLESPACE_NAME, BYTES FROM DBA_FREE_SPACE UNION ALL SELECT TABLESPACE_NAME, BYTES_FREE + BYTES_USED FROM V$TEMP_SPACE_HEADER UNION ALL SELECT TABLESPACE_NAME, NVL(BY

ORA-01157: cannot identify/lock data file 601 - see DBWR trace file

ORA-01110: data file 601: '/oracle/PRD/sapdata2/temp_1/temp.data1'

BR0669I Cannot continue due to previous warnings or errors - you can go back to repeat the last action

BR0280I BRSPACE time stamp: 2010-08-13 22.26.53

BR0671I Enter 'b[ack]' to go back, 's[top]' to abort:

Now what should i check