Search on this Website

Thursday, May 24, 2007

SAP System Migration: Export/Import (using Oracle)




This section explains how to migrate an SAP system (version 3.x) running Oracle 7.x from
one machine to another. This procedure was used to migrate a database from
the Alpha (NT) platform to the Intel (NT) platform.
For instance, if you are upgrading to a new server, you can use this procedure
to move your system onto the new server. It will work even if the old server
and the new server


are different operating system platforms, as long as Oracle is able to
export from the source platform and import the target platform
(although SAP makes a very big deal about such cross-platform migrations).
Several steps in this procedure require familiarity with Oracle database administration,
which is not described in detail here.
Topics covered here are:


  1. Plan layout for target system (the most important step!):
    disks, tablespaces, data files, etc.

  2. Submitting a Proposal to Management
  3. Install software in target system
  4. Prepare the Target Database (tablespaces, data files, rollback segments)
  5. Perform cleanup, checks, and documentation for source system
  6. Export source system
  7. Import source system into target system
  8. Post-import steps
  9. Additional notes: scripting,
    Troubleshooting and log analysis with grep, awk, and diff


This document refers to several programs, some of
which you may not have used before. Some notes on these:

  • SAP: Whenever I mention a transaction, it means an SAP transaction
  • sapdba: the SAP administration program invoked from the server's command line
  • sqlplus: the SQL*Plus program
    that comes with your Oracle software. Like sqldba,
    sqlplus can be used to execute SQL commands. It also has a screen buffer, so you can
    scroll up to see output that is longer than the screen size (which you can't do with
    sqldba). On NT platforms, sqlplus has the additional benefit of being accessible from
    a remote workstation on the net. SQL*Plus is used here for generating scripts, and
    either SQL*Plus or sqldba can be used to execute the scripts
  • sqldba/svrmgr: the sqldba program invoked from the server's command line.
    I prefer using SQL*Plus when possible, because you can copy and paste in SQL*Plus.
    However, some scripts need to be run from sqldba (or svrmgr).
    Depending on your version of Oracle,
    the program for sqldba may be sqldba72, sqldba73, svrmgr23, or svrmgr30. For
    information on setting up SQL*Plus, see
    Troubleshooting SAP Server startup.
  • Creating and Executing a Script with sqldba/svrmgr:
    To create and execute a script (with sqldba/svrmgr):

    1. Log in to the database with SQL*Plus
    2. Type in (but don't execute) the script-generating SQL command
    3. Type in the statement "spool script_name" to start spooling
      to a file named script_name
    4. Execute the SQL statement
    5. Type "spool off" to stop spooling
    6. Edit the file script_name:

      • Delete any non-SQL statements at the beginning of the script
      • insert a "connect internal" statement on the first line
      • Delete any non-SQL statements at the end of the script

    7. Open a command prompt
    8. Enter "sqldba @script_name > log_file" where script_name is
      the full pathname of the script file, and log_file is the full pathname
      of the file to which you want to log the script's results.

  • Creating and Executing a Script with SQL*Plus:
    This is easier, especially for executing one-time scripts (like deleting
    a set of objects):

    1. Log in to the database with SQL*Plus
    2. Enter and execute the script-generating SQL command
    3. Select the output, and copy it to the clipboard (if the
      output is messed up by page or line breaks, adjust the buffer settings
      linesize, pagesize, buffer width, buffer length.
    4. Press Ctrl-V to paste the commands into the SQL*Plus command buffer,
      and press Enter a few times to return to the SQL*Plus prompt.
    5. Enter a slash to execute the command.

  • Export, EXP, Import, IMP: the
    export/import programs that come with your Oracle
    software (the actual names will be something like imp72.exe or exp72.exe, depending
    on your Oracle version)
  • grep, awk, diff: small useful utilities that are
    native to UNIX, but can be used on NT
    (by downloading them from any of several sites that have the GNU executables. One
    such site is ftp://ftp.tas.gov.au/gnu). This document describes some basic
    applications of them for searching (grep), manipulating (awk),
    and comparing (diff) text files.

Before starting any export/import, you should, of course, backup the database and the
database environment (such as control files, directory paths, and environment variables).
Also, you will need to obtain a new license key from SAP in order to run the system on
a different server.



Planning the Layout: the Most Important Step!

back to top



Before starting the export/import process (and ideally, before even procuring the
target system server), you must plan the layout for the target system,
primarily for security, performance, and reliability/redundancy reasons.
To plan out the layout,


  1. Decide on the maximum size for your Oracle database data files
  2. Find which, if any, tablespaces need to add datafiles
  3. Decide which datafiles need to be separated (such as data/index files)
  4. Calculate number of volumes you will use, and the size of each volume
  5. Decide on distribution of SAP directories (data and other)
  6. Decide on configuration of hard disks, mirroring, and RAID
  7. Decide on configuration of tablespaces and data files

The maximum data file size may be a limitation of your operating system; some
operating systems have a maximum file size of 2G. It may also be a matter of policy, as
performance may be better if large tablespaces are spread across several data files.


To assist in these tasks, use transaction DB02 and/or use
sapdba/sqldba/svrmgr/sqlplus to query the tables
dba_data_files and dba_free_space:


  • Tablespace/data file relationships and free space:

    select fs.tablespace_name, sum(fs.bytes/1024) FreeKb,
    df.status, df.file_name
    from dba_free_space fs, dba_data_files df
    where fs.file_id = df.file_id and
    fs.tablespace_name = df.tablespace_name
    group by fs.tablespace_name, df.status, df.file_name



  • Data files: select * from dba_data_files



  • Tablespace size statistics: total, used, free, %used:

    select TotalKbSQL.tablespace_name,
    round(TotalKb, 2) TotalKb,
    round(FreeKb, 2) FreeKb,
    round(TotalKb - FreeKb, 2) UsedKb,
    round(100*(TotalKb - FreeKb)/TotalKb, 1) PctUsed
    from (
    select tablespace_name, sum(bytes)/1024 FreeKb
    from dba_free_space
    group by tablespace_name
    ) FreeKbSQL,
    (
    select tablespace_name, sum(bytes)/1024 TotalKb
    from dba_data_files
    group by tablespace_name
    ) TotalKbSQL
    where TotalKbSQL.tablespace_name = FreeKbSQL.tablespace_name(+)


    The output of this query looks something like:

    TABLESPACE_NAME TOTALKB FREEKB USEDKB PCTUSED
    ------------------------------ --------- --------- --------- ---------
    PSAPBTABD 3416064 886584 2529480 74
    PSAPBTABI 1775616 537576 1238040 69.7
    PSAPCLUD 109568 48512 61056 55.7
    PSAPCLUI 20480 13104 7376 36
    . . . . .
    . . . . .
    . . . . .



  • Dataset size statistics: total, used, free, %used:

    select FreeMBSQL.dataset, round(TotalMB, 2) TotalMB,
    round(FreeMB, 2) FreeMB, round(TotalMB - FreeMB, 2) UsedMB,
    round(100*(TotalMB - FreeMB)/TotalMB, 1) PctUsed
    from (
    select
    decode(1,
    least(instr(df.file_name, 'SAPDATA1'), 1),'sapdata1',
    least(instr(df.file_name, 'SAPDATA2'), 1),'sapdata2',
    least(instr(df.file_name, 'SAPDATA3'), 1),'sapdata3',
    least(instr(df.file_name, 'SAPDATA4'), 1),'sapdata4',
    least(instr(df.file_name, 'SAPDATA5'), 1),'sapdata5',
    least(instr(df.file_name, 'SAPDATA6'), 1),'sapdata6',
    'unknown') dataset,
    sum(fs.bytes)/1048576 FreeMB
    from dba_free_space fs, dba_data_files df
    where df.file_id = fs.file_id and
    fs.tablespace_name = df.tablespace_name
    group by decode(1,
    least(instr(df.file_name, 'SAPDATA1'), 1),'sapdata1',
    least(instr(df.file_name, 'SAPDATA2'), 1),'sapdata2',
    least(instr(df.file_name, 'SAPDATA3'), 1),'sapdata3',
    least(instr(df.file_name, 'SAPDATA4'), 1),'sapdata4',
    least(instr(df.file_name, 'SAPDATA5'), 1),'sapdata5',
    least(instr(df.file_name, 'SAPDATA6'), 1),'sapdata6',
    'unknown')
    ) FreeMBSQL,
    (
    select decode(1,
    least(instr(df.file_name, 'SAPDATA1'), 1),'sapdata1',
    least(instr(df.file_name, 'SAPDATA2'), 1),'sapdata2',
    least(instr(df.file_name, 'SAPDATA3'), 1),'sapdata3',
    least(instr(df.file_name, 'SAPDATA4'), 1),'sapdata4',
    least(instr(df.file_name, 'SAPDATA5'), 1),'sapdata5',
    least(instr(df.file_name, 'SAPDATA6'), 1),'sapdata6',
    'unknown') dataset,
    sum(df.bytes)/1048576 TotalMb
    from dba_data_files df
    group by decode(1,
    least(instr(df.file_name, 'SAPDATA1'), 1),'sapdata1',
    least(instr(df.file_name, 'SAPDATA2'), 1),'sapdata2',
    least(instr(df.file_name, 'SAPDATA3'), 1),'sapdata3',
    least(instr(df.file_name, 'SAPDATA4'), 1),'sapdata4',
    least(instr(df.file_name, 'SAPDATA5'), 1),'sapdata5',
    least(instr(df.file_name, 'SAPDATA6'), 1),'sapdata6',
    'unknown')
    ) TotalMBSQL
    where FreeMBSQL.dataset = TotalMBSQL.dataset

    The output of this query looks something like:

    DATASET TOTALMB FREEMB USEDMB PCTUSED
    -------- --------- --------- --------- ---------
    sapdata1 4237 1467.95 2769.05 65.4
    sapdata2 2000 205.94 1794.06 89.7
    sapdata3 1200 160.1 1039.9 86.7
    sapdata4 2052.8 437.82 1614.98 78.7
    sapdata5 2923 1344.11 1578.89 54
    sapdata6 1975 703.84 1271.16 64.4



Using this information, plus your maximum data file size,
decide if you need to add more data files (if your tablespaces exceed
(maximum data file size)*(number of data files installed), you will have to
add data files).


At the end of this step, you should document the planned configuration of mirroring,
RAID configuration, data file location and size, and SAP directories.




Submitting the Proposal

back to top



Before going ahead and making any changes to any system, make sure that
everyone understands what is involved and agrees to it. For this end, a
formal proposal should be submitted to management. Downtime may affect
the company's productive schedule, so the proposal must include specific
dates of planned downtime for management approval. In order to get a better idea
of the necessary downtime, it may be necessary to run an export on the system
overnight, after shutting down SAP. If the system cannot be down for export testing,
then an online backup can be performed; using the backup tapes, the system can then
be restored to a test system, and the export procedure tested from the test system.


The proposal should include:


  • Specific dates of planned downtime
  • Minimum and maximum downtime estimates
  • Alternate plans in case of export/import errors or delays
  • Export/import strategy and procedure steps
  • Formal acceptance conditions, procedures, and documents. This may
    include, for example, specific reports or transactions to be run for
    verification after the import. Generally, as long as the SAP installation
    and the import execute without errors, there will be no
    discrepencies between the target system and the source system.

Only when the proposal is agreed upon and signed by management should
further action be taken.




Installing the Software on the Target System

back to top



When you install the software in the target system, note:


  • the target and source system must run the same SAP version during export/import
  • the target and source system need not run the same Oracle version, as long
    as the export (dump) file can be read by the target system's Oracle. For instance,
    Oracle 7.3 can import databases that were exported from Oracle 7.2, so
    importing a 7.2 database into a 7.3 target system is fine

Note that this the procedure described here is part of an overall
migration procedure to first install the SAP system and blank database using
the unchanged configuration of a new SAP system, then alter the
database configuration (to conform to the plan prepared
from the step Planning the Layout) in the step
Preparing the Target Database. An alternative is to alter the SAPfs.inf file,
which controls how SAP sets up the tablespaces and data files during installation, before
installing the SAP database server.


The procedure for software installation on the target server is:


  1. Make sure the operating system and any required patches or service packs are
    already installed and working
  2. Install the Oracle database software (using OraInst.exe), as described in the
    SAP installation manual
  3. Install R3inst.exe onto your computer from the SAP Kernel CD.
  4. (Installing the application server software)
    Run R3inst.exe (from the program menu, not the CD), select "New
    Server->Application Server" and continue to install the application server
    as described in the SAP manual
  5. If you want to change the default size of tablespaces, you can
    edit the SAPfs.inf. However, this step is not required, because we will
    later set tablespace data file sizes to be unlimited (only during import)
  6. (Installing the database server software) Run R3inst.exe again (from
    the program menu, not the CD)if it is not already running, and select
    "New Server->Database Server". When R3inst.exe prompts you to choose the
    items to import, check only "Create Database" and "Setup sapdba
    and brbackup". Do NOT check "Import Data".
    This will create the database, data files, and tablespaces. It will
    not create tables or import table data.




Preparing the Target Database

back to top



The actions taken in this section are:


  1. Activate rollback segments (this can also be done after the import)
  2. Turn off archive logging
  3. Add datafiles, if necessary
  4. Alter data file storage parameters, if necessary

Step 1 can optionally be done later, since the import will use the commit=y flag and
therefore will not write to the rollback segments.
Steps 3 and 4 are performed using the list (from the step Planning the Layout)
of tablespace and data file sizes and locations.


You can change the size of a data file by using the alter database command.
Since the import will probably require larger tablespaces and data files
for most tablespaces, it may be easier to first set all of your data files
to autoextend, and then manually change the storage parameters of particularly large
tablespaces. You can create an sqldba script to do this from SQL*Plus:


set linesize 40
select rpad('host echo autoextending ' ||tablespace_name, 40, '.'),
'alter database datafile "' || file_name ||
'" autoextend on next NEXT_SIZE maxsize MAX_DF_SIZE;'
from dba_data_files
where tablespace_name <> 'PSAPROLL' and tablespace_name <> 'SYSTEM'

This SQL statement outputs a list of commands, which can be run as a script
from sqldba. MAX_DF_SIZE is the maximum size of your data files (from the
Planning the Layout step), and NEXT_SIZE is the size of the next extent.
Note that, after generating the script, you will have edit it
and change all the double quotes (") to single quotes (') before sqldba can use it.




Cleaning Up, Checking, and Documenting the
Source Database


back to top



Cleaning up the database reduces the amount of data that needs to be exported/imported.
Checking the database is necessary to make sure that it is consistent before export.
Documenting of database and SAP logs is necessary in order to verify the results
of the export/import, and, in the case of differences between the source and target, to
distinguish whether the differences arose from the export/import, or from some other
factor.


Cleanup can be divided into two basic parts: database cleanup (the archiving and/or
deletion of records in db tables) and file cleanup (the archiving and/or deletion of files
on the server). Database cleanup should include regular maintenance from reports
RSBTCDEL, RSPO0041, RSBDCREO, RSSNAPDL, and RSBPSTDE, as well as archiving and deletion
of transport files, temporary files left over from reorgs or upgrades, etc. See
Cleaning the System for more info.


Database checks can be done from transaction DB02.


Document any warning or error entries in the system log (SM21) and
the database logs (located somewhere around the DVEBMGS/work directory).



Exporting the Database

back to top



Use Oracle's EXP program to export the database to a dump file. A sample use of export
would be to export the entire database to a file named d:\devexport.dmp. The
parameter file d:\devexp.par (text) would be:


userid=system/manager
buffer=131072
file=d:\devexport.dmp
compress=y
full=y
consistent=n
and the export would begin executing, while logging output (from stderr) to the
file d:\devexp.log, with
the command

exp parfile=d:\devexp.par 2>devexp.log

Some things to keep in mind are:

  • use EXP help=y for command-line help on EXP.
  • During export, SAP should not be running, in order to ensure a consistent
    database.
  • The size of the binary dump file will be about
    40-60% of the total size of the data files.
  • The compress parameter compresses table extents, and is therefore usually
    desirable.
  • Log files: a log file is essential
    for verifying a successful export and/or fixing problems during export or import.
    The export program let's you specify a logfile, or you can redirect
    output with 2> to capture the export program's screen output. Redirecting screen
    output generates much more useful information than using the logfile parameter.
  • Switch off the consistent parameter:
    If you have a consistent database that will not be modified throughout the
    export (usually the case), you can switch off consistency checking,
    which will save a lot of time.
  • Check the Oracle product documentation for more details on export/import.




Importing the Database

back to top



Use Oracle's IMP program to import the dump file. The IMP program can be run interactively, or using a parameter file. A first-time full import parameter file d:\devimp.par, for
importing a file devexport.dmp, might look like:


userid=system/manager
buffer=131072
ignore=y
commit=y
full=y
file=d:\devexport.dmp

The import would begin executing, while logging output (from stderr) to the
file d:\devimp.log, with the command

imp parfile=d:\devimp.par 2>devimp.log

If the import encounters errors, and some tables are not imported successfully,
a re-import is necessary. A re-import parameter file might look like:


userid=system/manager
buffer=131072
ignore=y
commit=y
full=n
file=F:\export.dmp
fromuser=SAPR3
tables=(TSKT4, TST01, TST03, VBAP)

This tells IMP to only import the specified tables (which belong to the user SAPR3,
as do all SAP tables). The 'ignore' parameter tells IMP whether to
skip a table if it already exists (ignore=N) or to import the table data regardless
of whether it exists already or not (ignore=Y). The 'commit' parameter tells IMP
not to roll back if an error occurs (commit=Y) while importing data. The data
that was already inserted into the table remains so. This may improve performance.


Some things to keep in mind are:


  • use IMP help=y to get command-line help for IMP.
  • Make sure that the target database's archive logging is turned off.
  • The import, if no errors are encountered, will take several hours. If
    errors occur, it may slow the import down considerably, so make sure to check
    the import every few hours.
  • Log files: Some versions of the IMP program produce logs that are practically useless if you use the logfile parameter. Use the redirect 2>
    to create a logfile.
  • Ignore parameter: if an import was stopped, you can use the ignore parameter
    to either re-import data (ignore=y), or to skip over tables that were already imported
    (ignore=n). If you use ignore=n, make sure to note any tables that were only partially
    imported, as these will have to be re-imported.
  • If your parameter file is incomplete for some reason, the
    IMP program might start and then just hang. And if it is working, the program
    may still hang for several seconds without doing anything. Check the CPU usage
    if you think it's hanging.
  • Check the Oracle product documentation for more details on export/import.



Post-Import Steps

back to top



After importing the database, a few other steps need to be done:


  1. Check the import log for errors, and handle any failed imports.
  2. Restore database settings, such as archive logging, tablespace storage
    parameters, and datafile storage parameters
  3. Run various database checks (DB02)
  4. Restore transport system control files and, if necessary, data files
  5. Check language environment(see Configuring the
    language environment
    )
  6. Edit the SAP start, instance, and default profiles, based on the start and instance
    profiles of the source system. First edit the files in the operating system, then
    use CCMS to import them into the database.
  7. Verify that additional application servers work properly
  8. Formal acceptance of target system: Perform all user acceptance testing
    as outlined in your acceptance documentation (see
    Submitting a Proposal to Management)
  9. Cleanup temporary files



Additional Notes:

back to top



This section describes some general troubleshooting and analysis methods:


  • Getting Grep, Awk, Diff
  • Deleting data from the database
  • Using grep to search the log files for regular expressions (patterns)
  • Using awk to manipulate text files and create scripts
  • Using diff to compare log files

Getting Grep, Awk, Diff



If you are using UNIX, you already can use grep, awk, and diff. Otherwise, you
can get the latest versions at www.simtel.net under the GNU Project (or

ftp://ftp.simtel.net/pub/simtelnet/gnu/djgpp/
or
ftp://ftp.tas.gov.au/gnu


Sample Script Creation Commands



These are some sample commands that can be used to create SQL scripts.
The command is run from SQLPlus and spooled into the script file.
Set the SQL*Plus buffer width to 40 first, to get line breaks at the right
place. Run the scripts with svrmgr or sqldba, such as

svrmgr23 @script.sql 2>logfile.log.


disable autoextend script:


select rpad('host echo autoextend off ' ||tablespace_name, 40, '.'),
'alter database datafile "' || file_name ||
'" autoextend OFF;'
from dba_data_files
where tablespace_name <> 'PSAPROLL' and tablespace_name <> 'SYSTEM'

The script that is generated needs to be further edited by adding the connect internal
command at the start, deleting superfluous output, and replacing double quotes
with single quotes.


drop tables script:


select rpad('host echo drop table ' ||table_name, 40, '.'),
'drop table ' || table_name || ';'
from user_tables



Deleting data from the database



To completely restart an import, the easiest way is to recreate the database by
starting R3INST and selecting only "Create Database" and "Link DB to sapdba/brbackup".



To delete tables, the fastest way is to use the TRUNCATE command, because it deletes
the tables immediately without using rollspace.



To re-import a table that already exists, set the IGNORE parameter to Y
when importing.


Using grep to search the log files for regular expressions (patterns)





No comments: