Renaming tablespace : error

[Former Member]

Hi,

i have tried reorganize tables in tablespace PSAPBTABD in oracle 10, but in step 4 got error which can not resolve by myself:

I have done:

1.brspace -f tscreate -t PSAPNEWD -d both -l PSAPBTABD

2.brspace -f tbreorg -s PSAPBTABD -t "*" -n PSAPNEWD -p 6

3. brspace -f tsdrop -t PSAPBTABD

4. Renaming the new tablespace (sap must be done)

-

---

brspace -f tsalter -a rename -t PSAPNEWD -n PSAPBTABD

also have tried sqlplus

(alter tablespace PSAPNOVD rename to PSAPBTABD;).

In my opinion when i have execute stopsap i have started automaticly script stopdb and my DB now in strange condition. I have tryed shutdown immediate with sqlplus (getting error no logging) and brtools (see output below), i have tryed also to put tablespace PSAPBNOV to offline status but did not success (no logging message.

Pls help with step 4 (rename tablespavce) and startup DB.

Thanks a lot.

1 - Database close mode (mode) ...... [immediate]

2 - Force instance shutdown (force) . [yes]

3 - SQLPLUS command (command) ....... [shutdown immediate]

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

-

---

BR0662I Enter your choice:

c

BR0280I BRSPACE time stamp: 2007-10-27 08.42.44

BR0663I Your choice: 'c'

BR0259I Program execution will be continued...

BR0280I BRSPACE time stamp: 2007-10-27 08.42.44

BR0301W SQL error -1089 at location BrDbaConnect-2, SQL statement:

'CONNECT / IN SYSDBA MODE'

ORA-01089: immediate shutdown in progress - no operations are permitted

BR0310W Connect to database instance failed

BR0668I Warnings or errors occurred - you can continue to ignore them or go back to repeat the last action

BR0280I BRSPACE time stamp: 2007-10-27 08.42.44

BR0670I Enter 'c[ont]' to continue, 'b[ack]' to go back, 's[top]' to abort:

c

BR0280I BRSPACE time stamp: 2007-10-27 08.42.47

BR0257I Your reply: 'c'

BR0259I Program execution will be continued...

BR0064I Database instance will be shut down now with mode 'immediate'

BR0280I BRSPACE time stamp: 2007-10-27 08.42.47

BR0670I Enter 'c[ont]' to continue, 'b[ack]' to go back, 's[top]' to abort:

c

BR0280I BRSPACE time stamp: 2007-10-27 08.42.52

BR0257I Your reply: 'c'

BR0259I Program execution will be continued...

BR0280I BRSPACE time stamp: 2007-10-27 08.42.52

BR0307I Shutting down database instance ...

BR0278E Command output of '/oracle/SID/1020_64/bin/sqlplus':

SQL*Plus: Release 10.2.0.3.0 - Production on Sat Oct 27 08:42:52 2007

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

SQL> Connected to an idle instance.

SQL>

SQL> ORA-24324: service handle not initialized

ORA-24323: value not allowed

ORA-01089: immediate shutdown in progress - no operations are permitted

SQL> Disconnected

BR0280I BRSPACE time stamp: 2007-10-27 08.42.54

BR0279E Return code from '/oracle/SID/1020_64/bin/sqlplus': 0

BR0302E SQLPLUS call for database instance failed

BR0309E Shutdown of database instance failed

[Former Member · Accepted solution]

HI,

The message ORA-24324 is normally a result of another ORA message,which is the actual cause of the problem.

for,ORA-01089:

oracle might have crashed / coredumped or may be smon/pmon/any GBProcess crash. In any case, instance might have died....

pls paste the alert log ....

[Former Member]

Hello All,

thank you very much for your replies, oracle processes were running even i have used shutdown immediate with force option. After kill ora_pmon, i was able to rename and start sap, unfortunatly db check not Ok, so will do restore with right oracle patch , thank you for tips.

I will start reorganization after restore again, few question according planned action:

1. creation of new tablespace.

"create tablespace PSAPNEWD extent management local autoallocate segment space management auto datafile '/oracle/R46/sapdata1/novd_1/novd.data1' size 700M autoextend on next M maxsize M]

- how big can be datafile, next extend, maxsize (propotions) for new tablespace where will copied tables for reorganization?

- under the note "brspace -f tscreate -t PSAPNEWD -d both -l PSAPBTABD " have to reorganize all tables, including tables with LOG RAW. In my situation, a lot of tables were skipped.

How to avoid it?

Thanks a lot.

Zulfiya

[former_member204746]

try this:

create tablespace PSAPNEWD datafile 'i:\oracle\PRD\sapdata1\NEWD_1\NEWD.DATA1'

size 64M reuse

AUTOEXTEND ON NEXT 64M MAXSIZE 32000M

EXTENT MANAGEMENT LOCAL

SEGMENT SPACE MANAGEMENT AUTO;

it will create a small 64Mb datafile that will automatically extend until 32Gb

-

---

about LONG RAW, you must reorg them OFFLINE. No way to avoid this. So, this means you export them and re-import them. SAP note <b>646681 [FIX]</b> explains the process. take note that export/import of LONG RAW is very long, it takes roughly 2GB per hour which is very slow.

another possible solution is to do a long2lob, this also takes time but will improve speed of future reorgs and will allow you to reorg everything online. refer to the Oracle recommendation in Note 835552. it may not suit your configuration and this usually slows down requests on this type of table.

note number was fixed... see bold...

Message was edited by:

Eric Brunelle

[Former Member]

Thank you Eric.

[Former Member]

see please the Oracle Note <b><u>270061.1</u></b> : How To Rename a Tablespace and Avoid Certain Errors

If you don't have an metalink account, see please the following SAP Note:

<b><u>758563</u></b> - Oracle Metalink access for SAP customers

[Former Member]

Oracle Database 10.2.0.3 is <b><u>NOT</u></b> certified for SAP. The next Oracle Release 10.2.0.4 will be certified by SAP.

see please following SAP Note:

<b><u>871096 -</u></b> Oracle Database 10g: Patch sets/patches for 10.2.0

[stefan_koehler]

Hello Tukhvatullina ,

at first as i can see you are using "Oracle 10g 10.2.0.3.0" - Patchset 3 is not released by SAP - so you are running an unsupported oracle database/instance.

I would guess that the shutdown process (maybe the SMON is still cleaning up) is still in progress. Have you checked the oracle processes?

Regards

Stefan