cancel
Showing results for 
Search instead for 
Did you mean: 

Migration from DMTS to LMTS

Former Member
0 Kudos

Dear all,

I need to perform migration from dictionary managed to locally managed tablespaces.

We are using SAP R/3 4.6C, with 4.6D_EXT kernel 2271, Oracle 9.2.0.5 and HP-UX 11.23

PLS if anyone have the procedure for migration first to all other tablespaces, PSAPTEMP and on the end for the SYSTEM tablespace. I need this information before I start the Oracle upgrade to 10g.

And PLS tell me where can I find to download the brtools with brspace 6.40.

I've downloaded the most resent DBATL620O92_136-10002585.CAR DBATOOLS Package for oracle 9.2.x and there is only

> brtools

BR651I BRTOOLS 6.20 (136)

BR280I Time stamp 2007-01-22 13.10.22

BR656I Choice menu 1 - please make a selection

-


BR*Tools main menu

1 = Backup and database copy

2 - Restore and recovery

3 - Check and verification

4 - Database statistics

5 - Profiles and logs

6 - Additional functions

7 - Exit program

Thanks in advance,

Ruzica

Accepted Solutions (1)

Accepted Solutions (1)

vince_laurent
Active Participant
0 Kudos

I don't have the process written in stone but what I did was export the tablespaces and associated indexes (is PSAPBTABD and PSAPBTABI), drop the tablespace including datafiles, then recreate the tablespaces as LMTS then imp. Allowed me to kill 2 birds with 1 stone: migration to LMTS and defrag.

HTH,

Vince

Former Member
0 Kudos

Hello Vince,

Do you use the brspace command or you did with oracle commands?

Thanks,

Ruzica

vince_laurent
Active Participant
0 Kudos

Oracle and SAPDBA. I know, I know, I should have used BRTOOLS but they are so confusing and TOO many options. I did the exp with oracle tools, dropped the tablespace with BRTOOLS (drop tablespace PSAPXXXD including datafiles and contents), then recreated the tablespace with SAPDBA, then reimported with oracle imp. I have the scripts I can post if you are interested.

Kludgey, yes. Works, yes.

Vince

Former Member
0 Kudos

Yes Vince,

Please if you can post me your scripts, I would appreciate.

I'm just afraid about the indexes, and the tablespaces like PSAPBTABI.

You did the migration for these tablespaces too?

Do the export contain the information for the indexes?

Thanks,

Ruzica

vince_laurent
Active Participant
0 Kudos

Here they are in the order I did them. I had SAP down but oracle up when I did these. You will have to put in your own system password AND make sure there are enough target datafiles.

1. Export the data

#

  1. Name :odsd_exp.sh

#

#!/usr/bin/csh

export ORACLE_SID=BDE

$ORACLE_HOME/bin/exp system/PASSWORD filesize=1500M \

tablespaces=PSAPODSD,PSAPODSI \

file='/megadisk/odsd1.dmp,

/megadisk/odsd2.dmp,

/megadisk/odsd3.dmp,

/megadisk/odsd4.dmp

/megadisk/odsd5.dmp,

/megadisk/odsd6.dmp,

/megadisk/odsd7.dmp,

/megadisk/odsd8.dmp,

/megadisk/odsd9.dmp,

/megadisk/odsd10.dmp,

/megadisk/odsd11.dmp,

/megadisk/odsd12.dmp,

/megadisk/odsd13.dmp,

/megadisk/odsd14.dmp,

/megadisk/odsd15.dmp,

/megadisk/odsd16.dmp,

/megadisk/odsd17.dmp,

/megadisk/odsd18.dmp,

/megadisk/odsd19.dmp' \

feedback=2000 compress=n consistent=y direct=y indexes=y buffer=10485760 \

log=/megadisk/odsd.log

2. Create the indexes:

#

  1. Name: odsd_imp_step1.sh

#

#!/usr/bin/csh

export ORACLE_SID=BDE

$ORACLE_HOME/bin/imp system/PASSWORD \

file='/megadisk/odsd1.dmp,

/megadisk/odsd2.dmp,

/megadisk/odsd3.dmp,

/megadisk/odsd4.dmp

/megadisk/odsd5.dmp,

/megadisk/odsd6.dmp,

/megadisk/odsd7.dmp,

/megadisk/odsd8.dmp,

/megadisk/odsd9.dmp,

/megadisk/odsd10.dmp,

/megadisk/odsd11.dmp,

/megadisk/odsd12.dmp,

/megadisk/odsd13.dmp,

/megadisk/odsd14.dmp,

/megadisk/odsd15.dmp,

/megadisk/odsd16.dmp,

/megadisk/odsd17.dmp,

/megadisk/odsd18.dmp,

/megadisk/odsd19.dmp,

/megadisk/odsd20.dmp' \

full=y indexfile=indexes.sql buffer=10485760 \

log=/megadisk/imp_odsd.log

3. I then dropped the tablespaces (contents and datafile) (PSAPODSD and PSAPODSI) via brtools.

4. I then recreated them using SAPDBA and added the right amount of datafiles AND made sure they were LMTS.

5. Then I imported the data:

#

  1. Name: odsd_imp_step2.sh

#

#!/usr/bin/csh

export ORACLE_SID=BDE

$ORACLE_HOME/bin/imp system/PASSWORD \

file='/megadisk/odsd1.dmp,

/megadisk/odsd2.dmp,

/megadisk/odsd3.dmp,

/megadisk/odsd4.dmp

/megadisk/odsd5.dmp,

/megadisk/odsd6.dmp,

/megadisk/odsd7.dmp,

/megadisk/odsd8.dmp,

/megadisk/odsd9.dmp,

/megadisk/odsd10.dmp,

/megadisk/odsd11.dmp,

/megadisk/odsd12.dmp,

/megadisk/odsd13.dmp,

/megadisk/odsd14.dmp,

/megadisk/odsd15.dmp,

/megadisk/odsd16.dmp,

/megadisk/odsd17.dmp,

/megadisk/odsd18.dmp,

/megadisk/odsd19.dmp,

/megadisk/odsd20.dmp' \

feedback=2000 analyze=n full=y indexes=n commit=y buffer=10485760 \

log=/megadisk/imp_odsd1.log

7. Then I recreate the indexes using the output file from step 2

SQL> @/megadisk/indexes.sql

8. Then I reran the update statstics via DB13.

The number of files I exported to was just as guess. I just made sure I had enough. I also recreated the datafiles as 2G each and put them where I wanted to to help with performance issues.

HTH,

Vince

Former Member
0 Kudos

Hi Vince,

after all that, when you run the DB check job in DB13, does it report that the objects which are now in your new tablespace are in the wrong tablespace....

implying that SAP does not understand you moved all the objects to a new tablespace?

i used brtools to test online reorgs (which i have since been told not to trust, but i can't get a big enough downtime window, so what to do?).....

anyway, after using BRTOOLS to do an SAP supported reorg, DB13 says i have objects in the wrong tablespace......

why should that be especially since i used SAP supplied BRTOOLS?

thanks for your thoughts,

NormaJean Sebastian

Former Member
0 Kudos

Thank you Vince,

I've changed your scripts for my needs and started with export, and created the import_index.sql.

I am wondering about the sapdba for recreating the tablespaces with LMTS.

With sapdba the procedure for creating the tablespace:

sapdba->c-tablespace administration->g - Create/drop tablespace-->???

Can you pls advice?

Thanks again,

Many regards,

Ruzica

Former Member
0 Kudos

Hello,

Thanks Vince,

Using your example I tested with PSAPCLUD and PSAPCLUI and successfully migrate from DMTS into LMTS.

I've run the update statistics via DB13, all went:

CheckOpt -- successful

AnalyzeTab -- successful

NextExtent -- successful

<b>CheckDB -- Error , except the DB check</b>

BR0280I BRCONNECT time stamp: 2007-01-24 15.29.01

BR0818I Number of tables found in DBDIFF for owner SAPR3: 2

BR0280I BRCONNECT time stamp: 2007-01-24 15.29.01

BR0301E SQL error -1031 at location BrDdartRead-1

<b>ORA-01031: insufficient privileges </b>

BR0806I End of BRCONNECT processing: cdulobwn.chk 2007-01-24 15.29.01

BR0280I BRCONNECT time stamp: 2007-01-24 15.29.01

BR0804I BRCONNECT terminated with errors

Do you now what is the problem?

Many regards,

Ruzica

vince_laurent
Active Participant
0 Kudos

> Hi Vince,

>

> after all that, when you run the DB check job in

> DB13, does it report that the objects which are now

> in your new tablespace are in the wrong

> tablespace....

I never move them out of the tablespace. I export the data, drop the original tablespace, recreate the tablespace with the same name, then import it back in.

HTH,

Vince

vince_laurent
Active Participant
0 Kudos

> With sapdba the procedure for creating the

> tablespace:

> sapdba->c-tablespace administration->g - Create/drop

> tablespace-->???

> Can you pls advice?

Here are the steps I do:

sapdba

c- tablespace administration

a - tablespace name

g - create tablespace PSAP....D

e - tablespace parameters

h - EXTENT MANAGEMENT LOCAL AUTOALLOCATE

j - SEGMENT SPACE MANAGEMENT AUTO

q - return

c - new size

f - Select new path from a list of file systems

s - Start

HTH,

Vince

vince_laurent
Active Participant
0 Kudos

> Hello,

>

> BrDdartRead-1

> ORA-01031: insufficient privileges

Try rerunning the sapdba_role.sql script. It should be where

you have your brtools. There is a SAP note on that script too.

That 99.9% of the time fixes things.

HTH,

Vince

vince_laurent
Active Participant
0 Kudos

> i used brtools to test online reorgs (which i have

> since been told not to trust, but i can't get a big

> enough downtime window, so what to do?).....

> anyway, after using BRTOOLS to do an SAP supported

> reorg, DB13 says i have objects in the wrong

> tablespace......

> why should that be especially since i used SAP

> supplied BRTOOLS?

I haven't messed with BRTOOLS and online reorgs. You are ahead of the curve on me there. I use another tool for online reorgs BUT I always do a reorg from a table to another back to the original. UNLESS I break a table out of a tablespace. See SAP Note 154193.

HTH,

Vince

Former Member
0 Kudos

Hello,

Yes, the error was solved with <b>Note 900525 - BRCONNECT fails with ORA-01031 at location BrDdartRead-1</b>.

I forgot to copy the sapdba_role.sql 6.40 when I changed new DBAtools for brtools.

I've changed and implemented:

sqlplus /nolog @sapdba_role.sql <SID> UNIX

Now DB13 has successful CheckDB but with one unusual warning:

<b>BR0989W Unknown tablespace PSAPATABD defined in table SAPR3.TSORA</b>

In the table TSORA, Table of valid ORACLE tablespaces, the row with PSAPATABD and PSAPATABI exist.

I don't know could it be problem or no.

Thanks,

Ruzica

Former Member
0 Kudos

Hi Ruzica,

I am in the same spot as you. I have completed several DB reorgs on a test system and all seemed great.

However when running the DB13 checks i get a lot of that same message.

please post your answer if you find one.

thank you,

NJ

Former Member
0 Kudos

Hello Norma,

I solve the problem by deleting the unknown tablespace from TSORA, but first I looked in TAORA and IAORA, and compared data from the production. TAORA and IAORA have correct data except the TSORA.

<b>DB13 warnings</b>: Note 442395 - Descriptions of specific BR messages

BR0989W Unknown tablespace %s defined in table %s.%s

Explanation: A tablespace that does not exist in the database was specified in the TAORA, IAORA or TSORA table. The ABAP Dictionary uses these tables when creating new database objects. Based on these tables, BRCONNECT checks in multi component databases (MCOD, see Note 379572) whether objects of different schemes are contained in the same tablespace - this can cause problems with the tablespace Point-In-Time Recovery.

Program reaction: None.

User reaction: Correct the incorrect entries in tables TAORA, IAORA or TSORA with transaction SE16. Delete the entries if the relevant tablespace does not exist or change the TABSPACE field if the tablespace was defined with an incorrect name.

<b>I did this:</b>

SQL> update sapr3.tsora set TABSPACE='PSAPDDICD' where TABSPACE='PSAPATABD';

SQL> update sapr3.tsora set INDSPACE='PSAPDDICI' where INDSPACE='PSAPATABI';

SQL> select * from sapr3.tsora;

TABSPACE INDSPACE

-


-


PSAPDDICD PSAPDDICI

PSAPBTABD PSAPBTABI

PSAPDOCUD PSAPDOCUI

PSAPEL46CD PSAPEL46CI

PSAPES46CD PSAPES46CI

PSAPLOADD PSAPLOADI

PSAPPOOLD PSAPPOOLI

PSAPPROTD PSAPPROTI

PSAPSOURCED PSAPSOURCEI

PSAPSTABD PSAPSTABI

PSAPUSER1D PSAPUSER1I

TABSPACE INDSPACE

-


-


PSAPCLUD PSAPCLUI

Regards,

Ruzica

Former Member
0 Kudos

Hello Vince,

I did the migration from dictionary into locally managed tbs, mostly like in your script.

1. export data and index tablespace with Oracle command

2. import into file for creation of indexes

3. Drop the data and corresponding index tablesspace with brtools

4. recreate the tablespaces with sapdba or with sqlplus (using the ddl scripts created before dropping the tablespaces) - - <b>I think here is the problem</b>

5. import the data with Oracle commands

6. import the indexes from the previously created sql

But there is one problem.

Because I have also used the sapdba and ddl sql for recreating the tablespaces, I didn't specify the SAP data class for the newly created tablespace.

And in the tables TAORA, IAORA and DDART (what is very unclear for me) 3 rows missing for the USER1, TEMP and ??? data class.

If you try to import some transport in the corresponding tablespace which data class is missing an error ocur.

I have read the Note 646681 - Reorganizing tables with BRSPACE, and I saw the part: <i>If the <reorg_tsp> tablespace contains any of the following tables: SDBAH,SDBAD,DBAML,DBATL,MLICHECK,TGORA,IGORA,TSORA,TAORA, IAORA,SVERS,DD02L,DD09L,DDNTT,DDART,DARTT or SAPLIKEY (SAPLIKEY is only available in NetWeaver 2004s or higher), then you should move them to the tablespace <aux_tsp> by online reorganization using BRSPACE:

brspace -f tbreorg -t <table_list> -n <aux_tsp></i>

Do you have the same proble?

Do you know how I can solve the problem now after the hole migration was done?

How can I dedicate tablespace data class?

Is it enought to just enter the same rows into the TAORA, IAORA and DDART?

Thanks,

Many regards,

Ruzica

Answers (1)

Answers (1)

Former Member
0 Kudos

Hello,

For the reorganization you can follow the note 646681.

To make system LMTS, you first have to migrate the other TBS to LMTS following the previous note and activate AUM if not done previously.

Afterwards follow the note 748434, intersting note about it is also 706625.

The 6.40 BR* can be downloaded from SAP Service Marketplace.

http://service.sap.com/patches -> Entry by Application Group -> Additional Components

and follow the "path" towards the 6.40 kernel for your OS.

Former Member
0 Kudos

Hello,

Thanks for the information.

I've downloaded and install new version of brtools 640 and looked in the note 646681. It's a kind of confusing.

exp: For the tablespaces PSAPCLUD and PSAPCLUI. (initial and next size of 1MB, max number 505). In the section II. Migration to locally managed tablespaces in Oracle 92, of the Note 646681,

1. brspace -f tscreate -t PSAPCLUD2 -s 2.563 -a yes -m ? -i ? -l all

Can you PLS advice what is -m|-maxsize:(defines the maximum file size to which the file can be extended if autoextend is set) in brspace command? Is it the maximum number of extends? I think no, but I'm not sure what is.

In the documentation of brspace -- specifies the sequence number of the file for the new tablespace <size> specifies the maximum file size in MB.

And what about the -i size, -i|-incrsize: Defines the increment by which the file is extended if autoextend is set?

How can I distinguish if I have tables with LONG fields in my tablespace?

2. Online reorganization of tables without LONG fields

brspace -f tbreorg -s PSAPCLUD -t "*" -n PSAPCLUD2 -p

Should I do this?

3. Do I have to crate the ddl.sql for step <i>Creating DDL statements for tables with LONG fields</i>?

4.Exporting data from tables with LONG fields

5. Deleting old tablespaces

6. Creating tables with LONG fields in the new tablespace

7. Importing data from tables with LONG fields

8. Creating indexes and dependent objects

9. Creating new statistics for the reorganized tables

Do I have to perform all of this steps? Or only some of them.

Thanks in advance.

Many regards,

Ruzica