CANNOT DROP TABLESPACE PSAPSR3700 AFTER UPGRADE EHP4

[jorge_velasquez]

Hi Experts,

After the Upgrade,to EHP4 the Tablespace PSAPSR3700 was empty so I used BRTOOLS to dropped but it shows warnings about 1 segment.

I put the TS offline via SQLPLUS and after shutdown / startup the DB starts with status open. But STARTSAP doesnt work.

R3trans -x

4 ETW000 [ dev trc,00000] ORA-00376: file 11 cannot be read at this time 23 0.058

927

4 ETW000 ORA-01110: data file 11: '/oracle/SID/sapdata4/sr3700_2/sr3700.data2'

4 ETW000 [ dev trc,00000] Disconnecting from connection 0 ... 35 0.058

962

4 ETW000 [ dev trc,00000] Rolling back transaction ... 30 0.058

992

4 ETW000 [ dev trc,00000] Closing user session (con_hdl=0,svchp=0x11107f9d8,usrhp=0x11106bb40)

4 ETW000 136 0.059

128

4 ETW000 [ dev trc,00000] Disconnected (con=0) from ORACLE. 389 0.059

517

4 ETW000 [ dev trc,00000] SAPUSER or connect error 99=DBSL_ERR_DB, oerr=376. 25 0.059

542

*4 ETW000 [ dblink ,00433] **LOG BY2=>sql error 376 performing CON [dblink#4 @ 433] 46 0.059

588

*4 ETW000 [ dblink ,00433] **LOG BY0=>ORA-00376: file 11 cannot be read at this time 27 0.059

615

4 ETW000 ORA-01110: data file 11: '/oracle/SID/sapdata4/sr3700_2/sr3700.data2' [dblink#4 @ 4

Regards

[former_member188883 · Accepted solution]

Hi,

You need to use following command to drop a particular tablespace.

DROP TABLESPACE PSAPSR3700

INCLUDING CONTENTS AND DATAFILES;

This will ensure that all its datafiles are also dropped from the database. Thus making your database clean and consistent.

Hope this helps.

Regards,

Deepak Kori

[Former Member]

Hallo,

during my upgrades "brspace -f tsdrop" did the job.

If it doesn't like to be dropped (eg not empty), it will complain.

Regards,

Thomas.

[former_member189725]

Please find out if the tablespace has no segments .

select SEGMENT_NAME from dba_segments where TABLESPACE_NAME='PSAPSR3700';

You can then drop them and then drop the tablespace along with its datafiles.

[volker_borowski2]

Please find out if the tablespace has no segments .
> 
> select SEGMENT_NAME from dba_segments where TABLESPACE_NAME='PSAPSR3700';
> 
> You can then drop them and then drop the tablespace along with its datafiles.

True up to version 10.

In version 11, defered created segments will not show up in DBA_SEGMENTS until the first record is inserted,

so with this check you still might drop Objects belonging to SAP allthough they will not contain any data.

But you might get errors if you want to use theses tables later.

Had this just after a compressreorg. Tried to drop the old TS, but brspace did complain "segments left"

Checked DB02 (which checks dba_segments) -> empty.

I had objects left with wrong tabart-classification which changed the tablespace in the reorg.

Reference (unfortuantely german only, but the first code box should be understandable)

http://www.oracle.com/webfolder/technetwork/de/community/dbadmin/tipps/segment_112/index.html

You need to check dba_tables/dba_indexes for defered objects in addition.

Best regards

Volker

[jorge_velasquez]

HI, The problem is solved.

SQL> select USERNAME, DEFAULT_TABLESPACE from dba_users where DEFAULT_TABLESPACE='PSAPSR3700';

USERNAME DEFAULT_TABLESPACE

-

---

-

---

OPS$ORADEV PSAPSR3700

OPS$DEVADM PSAPSR3700

OPS$DEVADM PSAPSR3700

OPS$SAPSERVICEDEV PSAPSR3700

TSMSYS PSAPSR3700

DIP PSAPSR3700

MDDATA PSAPSR3700

ORACLE_OCM PSAPSR3700

SCOTT PSAPSR3700

9 rows selected.

-

---

SQL> ALTER DATABASE DEFAULT TABLESPACE PSAPSR3701;

Database altered.

-

---

SQL> drop tablespace PSAPSR3700 including contents and datafiles cascade constraints;

Tablespace dropped.

Regards.

[Former Member]

Hi,

After the enhancement package installation, ( PSAPSR3700 )table spaces are empty and are no longer used. We can delete these table spaces.

Have you checked table space is empty before deleting? and what is the size of your PSAPSR3701 table space?

Please let me know.

Regards,

Kiran .V

[sunny_pahuja2]

Hi,

When you dropped the tablespace, was tablespace empty that time ?

Thanks

Sunny

[jorge_velasquez]

Hi,

I have not dropped the tablespace. I put it offline then startsap doesnt work.

I dont want to drop it until SAP works with the TS offline.

The file /oracle/<SID>/sapdata4/sr3700_2/sr3700.data2' exists so I don't know what the problem is?

The TS is not totally empty.

-

---

Main data (Last analysis: 22.11.2011 04:36:07)

-

---

Tablespace name

Size(MB)

Free(MB)

Used(%)

Autoextend

Total size(MB)

Total free space(MB)

#Segments

#Extents---- *PSAPSR3700

140.500,00

140.499,25

0

NO

140.500,00

140.499,25

0

1

1*

|PSAPSR3701 |174.000,00 |101.539,06 | 42 |NO | 174.000,00 | 101.539,06 | 42 | 802 | 10.495

Any Clue?

[Former Member]

Hi,

While performing the EHP4 upgrade in "check" phase it will ask us to create the PSAPSR3701 table space with sufficient space.

In "pre-processing" phase DB CLONE jobs will copy the content of PSAPSR3700 into PSAPSR3701 table space.

After end of the EHP4 upgrade you will see the empty table space ( PSAPSR3700) and upgraded data will be available in PSAPSR3701 table space.

Can you please confirm your EHP4 upgrade finished successfully?

Regards,

Kiran .V,

[jorge_velasquez]

Hi,

Yes the installation was successful.

Regards.

[sunny_pahuja2]

Hi,

You should not put this Tablespace offline. You should either delete it if tablespace is empty or if tablespace was not empty then you cannot delete it and you cannot make the tablespace offline also.

Thanks

Sunny

[former_member188883]

Hi,

Since your upgrade was successful you can drop the tablespace PSAPSR3700 using the command below.

DROP TABLESPACE PSAPSR3700

INCLUDING CONTENTS AND DATAFILES;

This will ensure that all its datafiles are also dropped from the database.

Do not make the tablespace offline. It will not help to achieve your desired result.

Regards,

Deepak Kori

[Former Member]

Hi,

Since you have put PSAPSR3700 on offline mode, This could be reason of this error.

Can you please follow mentioned steps below.

1.sqlplus / as sysdba

2. SQL>select status, enabled from v$datafile where file#=11;

If status is following

STATUS ENABLED

-

---

-

---

offline DISABLED

3. SQL> alter tablespace PSAPSR3700 online;

check datafile status

4. SQL>select status, enabled from v$datafile where file#=11;

Now dropping the tablespace.

5. SQL>DROP TABLESPACE PSAPSR3700 INCLUDING CONTENTS AND DATAFILES;

Finally check if PSAPSR3700 is dropped.

Thanks,

Kapil Pandey

[volker_borowski2]

Hi,

put the tablespace online again.

Then try to drop it with brtools.

Watch out carefully if it issues a warning that the tablespace is not empty.

If this is the case, DO NOT PROCEED, but abort the action!

Then o a table reorg first to move eventually remaining table out of this old tablespace.

Then repeat to try the drop with brspace.

Do not execute any actions as the ones described in sqlplus unless you know for sure what you are doing.

I think it is a dangerous advice to drop the TS with the including contents clause while there

have been known bugs from earlier upgrades leaving stuff in the tablespaces behind that was important.

It surely SHOULD BE the case, that the TS is empty, but it MAY BE not, so you better check.

This is why you should use brspace to do the drop.

It issues a warning if there is still contents.

Volker