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:
disks, tablespaces, data files, etc.
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:
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
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.
To create and execute a script (with sqldba/svrmgr):
to a file named script_name
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.
This is easier, especially for executing one-time scripts (like deleting
a set of objects):
output is messed up by page or line breaks, adjust the buffer settings
linesize, pagesize, buffer width, buffer length.
and press Enter a few times to return to the SQL*Plus prompt.
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)
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,
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:
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
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
. . . . .
. . . . .
. . . . .
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:
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:
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:
already installed and working
SAP installation manual
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
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)
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:
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
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:
and the export would begin executing, while logging output (from stderr) to the
userid=system/manager
buffer=131072
file=d:\devexport.dmp
compress=y
full=y
consistent=n
file d:\devexp.log, with
the command
exp parfile=d:\devexp.par 2>devexp.log
Some things to keep in mind are:
database.
40-60% of the total size of the data files.
desirable.
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.
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.
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:
errors occur, it may slow the import down considerably, so make sure to check
the import every few hours.
to create a logfile.
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.
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.
After importing the database, a few other steps need to be done:
parameters, and datafile storage parameters
language environment)
profiles of the source system. First edit the files in the operating system, then
use CCMS to import them into the database.
as outlined in your acceptance documentation (see
Submitting a Proposal to Management)
Additional Notes:
back to top
This section describes some general troubleshooting and analysis methods:
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:
Post a Comment