Migrating SYSTEM tablespace from DMTS to LMTS in Oracle 9.2.0.7

[Former Member]

Migrating SYSTEM tablespace from DMTS to LMTS in Oracle 9.2.0.7 using

brspace -f dbcreate

SAP version: 4.6C

Oracle: 9.2.0.7

OS: AIX 5.3

BRTools: 6.40(42) /** 6.40(10) or (12) will be sufficient according to SAP ***/

• IMPORTANT ***************************************

MUST DO:

1. Create a Full Backup of your system

2. Test your Restore and recovery of your backup.

3. Have a copy of all your tablespaces names on hand

4. Know your SYS and SYSTEM passwords

5. Run CheckDB in DB13 to ensure it is completed successfully with no warnings. This reduce the chance of hitting errors in the process

6. Ensure your UNDO tablespace is big enough

7. OSS 400241 Problems with ops$ or sapr3 connect to Oracle

************************************************************************

NOTE: OSS 706625(Read this note)

The migration from a dictionary-managed SYSTEM tablespace to a locally-managed tablespace using the PL/SQL procedure DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL is not supported in the SAP environment.

In UNIX, logon as ora<sid>

run command: brspace -f dbcreate

This command will triggers a Menu. The are seven(7) steps to complete the whole process. Do them in sequence, from step 1 to step 7 faithfully. In Step 1, ensure that your settings of PSAPTEMP, PSAPUNDO etc details such as filenames are correct. The rest I leave it as default and they are fine. Do not change redo log group from 8 to 4 even if you only have 4 redo groups. If not, you might need to restore the system! If the seven steps are complete without errors(warnings is acceptable), congrats. Perform a backup again.

Problems I encountered that caused me to restore system:

1./ Problem: I changed the redo group from 8 to 4 and in the later stage after the tablespaces and files are dropped, the system prompted me that 4 is not acceptable! I can't go back then so a restore is performed.

Solution: Leave the default value 8 as it is

2./ I was using wireless network and the network breaks thus process breaks.

Solution: This process in user-interactive and requires you to input confirmation along the way so do it using LAN.

3./ In the process of dropping tablespace PSAP<SID>, I encountered:

BR0301E SQL error -604 at location BrTspDrop-2

ORA-00601: error occurred at recursive SQL level 1

ORA-01555: snapshot too old: rollback segment number 22 with name '_SYSSMU22$" too small

Solution: I have not fixed this yet but I think it is because my PSAPUNDO is too small(800M) so I will increase it to a bigger value e.g. 5GB

4. Problem: Unable to start sap after successfully migrated. OPS$user problem

Solution: logon as <sid>adm, run R3trans -x in a directory that <sid>adm has read/write permission. R3trans -x will creates a file call trans.log. Read the details and refer to OSS 400241

Result: I have successfully performed this on one(1) system and doing this on the another one currently but encounter Problem 3. Will update this further if there are more findings.

REFERENCE:

OSS 748434 New BRSPACE function "dbcreate" - recreate database

OSS 646681 Reorganizing tables with BRSPACE

OSS 541538 FAX: Reorganizations

Message was edited by:

Annie Chan

Message was edited by:

Annie Chan

Message was edited by:

Annie Chan

[Former Member]

If I remember correctly, migrating SYSTEM tablespace to Locally Managed is only supported by SAP with complete export/import of the database.

[former_member204746]

I agree with Maurice,

do not migrate SYSTEM tablespace to LMTS using procedure dbms_space_admin.tablespace_migrate_from_local

[Former Member]

Eric,

LMTS had a lot of bugs in the beginning. So I strongly recomment to use a most current version.

I would expect that br*tools are converting the system tablespace to LMTS via transportable tablespaces.

That is exporting every tablespace, creating system tablespace new and import the tablespaces now to the newly created database.

regards

Peter

[former_member204746]

exporting whole database and recreating database from scratch and importing export is the only supported way to move SYSTEM tablespace from DMTS to LMTS.

Oracle support s tools to migrate SYSTEM but most DBA would not advise people to do the change.

so, reorg all tablespace from DMTS to LMTS ... except for SYSTEM tablespace. this can be done wirh reorg (sap note 646681), but not use migrate tool.

[Former Member]

Yes, it is a complete export/import of whole database which includes user & global objects, tablespaces.

[former_member204746]

> Yes, it is a complete export/import of whole database

> which includes user & global objects, tablespaces.

Annie, you need to increase PSAPUNDO and play around with oracle parameter undo_retention. 800Mb is small for a PSAPUNDO.

how big is your whole database?

[fidel_vales]

Hello,

I'd like to mention that the following two comments are not correct:

<b>Maurice Sens</b>:

<i>If I remember correctly, migrating SYSTEM tablespace to Locally Managed is only supported by SAP with complete export/import of the database.</i>

<b>Eric Brunelle </b>

<i>exporting whole database and recreating database from scratch and importing export is the only supported way to move SYSTEM tablespace from DMTS to LMTS.</i>

The command mentioned at the beginning:

brspace -f dbcreate

is a supported way of migrating SYSTEM tablespace to LMTS.

As Peter Simon mentioned, in this case BRSPACE uses transportable tablespaces to avoid a complete export of the database data ( it only export the metadata ). In few steps it do the following:

1) "unplug" the tablespaces

2) drop the database

3) create an "empty" DB with SYSTEM as LMTS

4) "Plug" the tablespaces from point 1

The notes 706625 and 748434 contain details about it.

[Former Member]

The current one I am implementing is a development system. The database is less than 100GB. 800MB of PSAPUNDO is sufficient for our development usage.

Follow up on Problem 3:

I created another undo tablespace PSAPUNDO2(undodata.dbf) with size of 5GB. I switched undo tablespace to PSAPUNDO2 and placed PSAPUNDO(undo.data1) offline. With PSAPUNDO2 online and PSAPUNDO offline, I started brspace -f dbcreate and encountered the error below at Step 2 Export User tablespace:

BR0301E SQL error -376 at location BrStattabCreate-3

ORA-00376: file 17 cannot be read at this time

ORA-01110: data file 17: '/oracle/DVT/sapdata1/undo_1/undo.data1'

ORA-06512: at 'SYS.DBMS_STATS", line 5317

ORA-06512: at line 1

I aborted the process and verified that SAP is able to run with this settings. I started CheckDB in DB13 and it shows me these messages:

BR0301W SQL error -376 at location brc_dblog_open-5

ORA-00376: file 17 cannot be read at this time

ORA-01110: data file 17: '/oracle/DEV/sapdata1/undo_1/undo.data1'

BR0324W Insertion of database log header failed

I don't understand then. I have already switched the undo tablespace from PSAPUNDO to PSAPUNDO2. Why the message above still appears? Once I put PSAPUNDO online, CheckDB completes successfully without warning.

I did show parameter undo_tablespace and the result is PSAPUNDO2(5GB).

So exactly, what's going on? Can anyone advise?

===============================================

I have managed to clear the message in DB13 after dropping PSAPUNDO tablespace including contents and datafiles. This is mentioned is OSS note 600141 pg 8 as below:

Note: You cannot just set the old rollback-tablespace PSAPROLL to offline instead of deleting it properly. This results in ORA-00376 in connection with ORA-01110 error messages. PSAPROLL must remain ONLINE until it is deleted. (Oracle bug 3635653)

Message was edited by:

Annie Chan

[Former Member]

Hi Peter,

>>LMTS had a lot of bugs in the beginning. So I strongly recomment to use a most current version.

Do you mean LMTS is less problematic in Oracle 10g compare to 9i?

Are you aware of any technical performance problems in 9i?

[former_member204746]

Fidel,

We were not saying that SAP does not support LMTS on tablespace SYSTEM. We were saying that a migration from DMTS to LMTS using Oracle migration tool is not supported.

your solution seems very interesting.

[former_member204746]

you should not put PSAPUNDO offline, this was not a good idea. try to put it online again.

[Former Member]

Eric,

You are right. Although there should always be only one(1) undo tablespace active but both PSAPUNDO and PSAPUNDO2 should be online.

The CheckDB warning message is gone after I dropped one of the undo tablespace.

For brspace -f dbcreate, it is better to stick with the naming convention PSAPUNDO. I was using PSAPUNDO2 and I realised the program actually create a dummy PSAPUNDO, followed by dropping PSAPUNDO2. Another risk.

OPS$user problem exists in all three(3) systems that I implemented but it can be solved thru OSS 400241.

"brspace -f dbcreate" needs some improvement!

[former_member204746]

if problem is fixed, provide points to people who helped you.

[Former Member]

I solved the problem myself. But in any case, I cannot seems to change this thread into a Question to give points.