Tablespace Administration

• Creation of a new tablespace, for example as a preparation to move certain tables out of the standard tablespace PSAP into a special tablespace
• Deletion of a tablespace, for example after an upgrade
• Extending tablespaces by adding a data file or tempfile or by resizing existing data files
• Moving data files, for example because after adding a new disk a data file which was created on another disk needs to be moved to the new disk This lesson describes the actions of BRTOOLS or BRGUI under the main menu item Space management.

Creating and Dropping tablespaces

On normal operation creating a new tablespace or dropping an existing tablespace is not necessary.

The creation of a new tablespace is necessary for example

• in preparation for the upgrade: During the upgrade, the old ABAP programs in PSAP are deleted and the new ABAP programs are imported into the new tablespace PSAP.
• in preparation for an online reorganization of a tablespace, for example to switch from a dictionary managed to a locally managed tablespace.

To create a new tablespace use BRTOOLS or BRGUI and select Space management ? Create tablespace. To enter the main menu mode of BRSPACE, select continue in the next screen and select Create tablespace from the BRSPACE menu. As an alternative, you can start brspace -f tscreate and press continue.. Now fill in the necessary information by selecting the appropriate menu items.

Create a tablespace using BR*Tools

BR0657I Input menu 305 - please check/enter input values
-------------------------------------------------------------------------------
Main options for creation of tablespace in database T00
1 - Tablespace name (tablespace) ......... [PSAPT00NEW]
2 - Tablespace contents (contents) ....... [data]
3 - Segment space management (space) ..... [auto]
4 # Database owner of tablespace (owner) . []
5 ~ Table data class / tabart (class) .... []
6 - Data type in tablespace (data) ....... [both]
7 # Joined index/table tablespace (join) . []
Standard keys: c - cont, b - back, s - stop, r - refr, h - help
-------------------------------------------------------------------------------
BR0662I Enter your choice:

The main options and their possible entries are:

tablespace

Enter the name of the tablespace to be created. According to the SAP conventions, the tablespace name should start with the letters "PSAP", followed by the SCHEMA-ID, plus a unique name.

contents

• data for normal tablespaces containing tables and/or indexes
• temp for a temporary tablespace
• undo for an undo tablespace

space

You can select either auto for automatic segment space management (default) or manual for manual segment space management.

owner

Changing this is only possible if you have multiple components in one database. The owner is SAP of the database schema you create the tablespace for.

data

In the "old" tablespace layout a tablespace contained either tables (table) or indexes (index). In the new MCOD tablespace layout, tablespaces contain tables and indexes (both). SAP recommends generally to create new tablespaces containing data and indexes to simplify administration.

join

If in the data field you entered either table or index, you can enter here the corresponding index or table tablespace. In the first case, BRSPACE will create both tablespaces. In the second case, BRSPACE creates only a index tablespace on joins it with already existing table tablespace. When you entered both in the data field, this option is not selectable.

After continuing, the menu for the data file properties is shown. The screenshot shows the output after changing autoextent to yes.

Options for creation of a tablespace

BR0657I Input menu 306 - please check/enter input values
--------------------------------------------------------------------
Space options for creation of tablespace PSAPT00TST (1. file)
1 - Tablespace file name (file) .......... [G:\oracle\T00\sapdata3\0tst.data1]
2 # Raw disk / link target (rawlink) ..... []
3 - File size in MB (size) ............... [2]
4 - File autoextend mode (autoextend) .... [yes]
5 - Maximum file size in MB (maxsize) .... [10]
6 - File increment size in MB (incrsize) . [2]
7 - SQL command (command) ................ [create tablespace PSAPT t management local autoallocate segment space management auto datafi le\T00\sapdata3\t00tst_1\t00tst.data1" size 2M autoextend on next 2M M]
Standard keys: c - cont, b - back, s - stop, r - refr, h - help
--------------------------------------------------------------------
BR0662I Enter your choice:

The main options and their possible entries are:

file

The name of the new data file for the tablespace. The default path and file name follows the SAP naming conventions for data files. You can create a tablespace with up to five data files using BRSPACE.

size

Size of the data file in MB.

autoextend

If set to yes, the new data file is created autoextensible. In this case, maxsize and incrsizemust be specified. If set to no, maxsize and incrsizecannot be specified and their entries are locked.

maxsize

For autoextensible data files, this parameter specifies the maximum size in MB up to which the data file can be increased.

incrsize

For autoextensible data files, this parameter specifies the size in MB, by which the data file is automatically increased when necessary.

Now the tablespace will be created. Because creating a tablespace is a structural change in the database, a control file backup is created in the directory $SAPDATA_HOME/sapreorg/ before and after the creation. In addition the action will be reported in $SAPDATA_HOME/sapreorg/struc .log.

To drop a tablespace use BRTOOLS or BRGUI and select Space management ? Drop tablespace. To enter the main menu mode of BRSPACE, select continue in the next screen and select Drop tablespace from the BRSPACE menu. As an alternative, you can start brspace -f tsdrop and enter continue.

Now a list of tablespaces is shown, from where you can select the tablespace to be dropped. After selecting the tablespace you can set the force option (default: no). Per default, BRSPACE will not drop a tablespace which is not empty. Using the force option, BRSPACE will drop the tablespace even if it is not empty.

When a tablespace is dropped, BRSPACE will

• create a control file backup in the directory $SAPDATA_HOME/sapreorg/ before and after the deletion
• check if the tablespace is empty. If the tablespace is not empty, it will not be dropped unless you force it by setting the force mode to yes.
• take the tablespace offline
• drop the tablespace including data files
• remove all subdirectories for the data files
• create an entry of the action in struc.log

Enlarging Tablespaces

To make a tablespace larger, there are three possible ways:

• a new data file can be added to the existing tablespace.

Use this option, if the existing data files cannot be resized because there is no disk space available on the disks holding the existing data files, or because the existing data files already have their maximum size and you don't want to make them larger.

• the properties of an existing data file can be changed to be autoextensible. Use this option to simplify further tablespace administration in the future. Any autoextensible data file will grow automatically when needed up to a maximum file size defined.

Caution: When using autoextensible data files, you have to monitor the disk space usage. In this case, a tablespace overflow can still occur if the disks holding these data files are full!

• an existing data file can be resized.

Use this option if you want to keep the control over the data file growth.

To enlarge a tablespace by adding a new data file start BRTOOLS or BRGUI and select Space management? Extend tablespace. To enter the main menu mode of BRSPACE, select continue in the next screen and select Extend tablespace from the BRSPACE menu. As an alternative, you can use brspace -f tsextend and enter continue.

After selecting the tablespace to be extended, a menu similar to the menu when creating a tablespace is shown. Enter the appropriate values as described above when creating a tablespace.

To switch an existing file to be autoextensible start BRTOOLS or BRGUI and select Space management ? Alter data file ? Maintain autoextend. To enter the main menu mode of BRSPACE, select continue in the next screen. As an alternative, you can use brspace -f dfalter and select Maintain autoextend.

Now select the data file from the list. The list only contains all data files because you can change maxsize and incrsize with this function.

In the next screen, the parameters for automatic extension of the data file have to be entered (maxsize,incrsize - see above).

To resize an existing data file start BRTOOLS or BRGUI and select Space management ? Alter data file ? Resize data file. To enter the main menu mode of BRSPACE, select continue in the next screen. As an alternative, you can use brspace -f dfalter and select Resize data file.

Now select the data file from the list. Specifiy the new data file size and continue.

Moving or Renaming Data Files

To move or rename a data file, it is not sufficient to just move or rename the file itself, as Oracle would not know about the new file name or location. After moving or renaming a data file, the new file name has to be entered in the control file with Oracle commands. Using BRSPACE, all necessary actions are performed.

You might want to move an existing data file to another location if

• you want to move data files from a file system to raw devices or from raw devices to a file system (on UNIX)

• you had to extend a tablespace by adding a new data file, but because of lack of disk space in the disks containing sapdata directories, the data file was created on another disk which was not intended to hold data files. After adding another disk you want to move this data file to the new disk.

• you need to replace a disk on Windows (due to different drive letters used).

To move or rename a data file start BRTOOLS or BRGUI and select Space management ? Move data file. To enter the menu mode of BRSPACE, select continue in the next screen. As an alternative, you can use brspace -f dfmove and enter continue. Now select the data file to move from the list of data files.

In the next screen, enter the required information:

destination

enter the full path of the sapdata directory where data file shall be moved to. This directory must already exist. The subdirectory for the data file must not be entered and will be created by BRSPACE.

parallel

when moving more than one data file, you can parallelize the copy process. Enter the number of copy processes here.

force

To move or rename a data file, the database needs to be shut down. BRSPACE will normally only shut down the database, if SAP is not running. You can force BRSPACE to shut down the database even when SAP is connected by setting the force option.

Additional Database Space Management Options

To show database information start BRTOOLS or BRGUI and select Space management ? Additional space functions.

You can view information about tablespace, data files, redo log and control files. Also disk usage information of all directories containing database data can be displayed.

To alter a tablespace start BRTOOLS or BRGUI and select Space management ? Alter tablespace. Now you have the following possibilities:

set tablespaces offline and online

When a tablespace is set offline, it cannot be accessed anymore. For SAP to work correctly, all tablespaces belonging to the corresponding database schema user SAP must be online!

There are situations, when Oracle sets a tablespace offline. This happens for example when Oracle receives an I/O error from the operating system when writing into a data file. To avoid corrupt blocks to be written, Oracle sets this tablespace offline.

Caution: Whenever a tablespace is offline, and it was not actively set offline by an database administrator, you must find the reason for this and solve the problem causing Oracle to set this tablespace offline! After finding and resolving the problem, the tablespace can be set online using this menu.

In databases having multiple components in one database (MCOD), the tablespaces belongs to one schema user SAP could be set offline to perform certain administrative actions without affecting other SAP systems using a different SAP user. But even in this situation, the tablespaces SYSTEM, PSAPROLL/PSAPUNDO and PSAPTEMP must not be set offline, because these tablespaces are used by all schema users.

set or reset the backup status

The backup status is regularly set by BRBACKUP when performing an online backup and reset after the backup. When BRBACKUP crashed during an online backup, one or more tablespaces remain in backup mode. This situation is checked by the database system check, which reports the following warning if a tablespace is in backup mode:BR0970W Database administration alert - level: WARNING, type: TABLESPACE_IN_BACKUP, object: PSAPC11 In this case,

• a normal shutdown would not work
• if the database crashes or a shutdown abort is performed while a tablespace is in backup mode, the database would need a manual recovery in order to be opened.

To avoid a manual recovery, reset the backup status when a tablespace is in backup mode when the following conditions are true:

• Analyzing the situation it turns out that an online backup crashed
• There is currently no online backup running.
Coalesce free extents

Coalescing free extents means that several free extents which directly follow each other are combined to a single, larger free extent. The tool database system check will automatically coalesce free extents for all tablespaces. Manually coalescing free extents using this menu makes sense when a high amount of data was deleted in the database, for example after archiving data or the deletion of a client.

OS DB Migration

Pre Export Process In Application level:-

o Check the Java JRE version (Min 1.4.1).
o Check the QCM table entries of SAP.
o Check for cancelled and update task
o Delete table entries from TATGPC, TATGPCA
o Check the incremental table conversion
o Cancel all released jobs
o Check for the operation modes
o Delete unnecessary spool data
o De-schedule all SAP jobs
o Delete all the batch jobs
o Release all repairs and correction and transports
o delete background input logs
o Delete job logs
o Delete the tRFC
o Check for the code pages installed
o Check the DDIC password for 000 client

Pre Export Process In OS level:-

o Deschedule all OS DB backups
o Shutdown all external interface
o The source should have minimum SP3

Pre Export Process In DB level:-

o Update DB statistics
o Change the DB to no archive log mode

Steps to Start the DB export

o Enter the system as adm
o Create to directory in the source system eg, Export, Install
o Check for the PSAPTEMP, the size should be 2 times the largest index
o Start the R3setup.bat program from the kernel CD (use the 45B_EXT kernel cd for the migration as the target system is AIX 5.3, always check the PAM before using the kernel cd.)
o Edit the DBEXPORT.R3S file and skip the update of statistics as it is already done and update statistics of R3SETUP is high time consuming. It can even run for days for a DB size more than 1 TB
o Edit the DBEXPORT.R3S to stop for splitter
o Start the export
o The export stops at the given breakpoint for the splitter
o Split the STR files generated
o Repeat the export process


Import process

o Check the Java version ( Minimum JRE version 1.4.1 )
o Check all necessary drives and filesystems. ( #mkdir /oracle/client/92x_64, # cd /oracle/stage, #mkdir 920_64 )
o Create to directory eg, Install, Import
o From install directory run instool.sh of Ext. kernel cd for AIX.
o set environment.
#csh
#setenv LIBPATH /sapmnt//exe:/oracle//920_64/lib.
o Install the central instances. R3SETUP –f CENTRAL.R3S
o In the next installation process choose “Exit”
o Install Oracle software as per WAS640 guide with ora user.
o After the oracle installation is done repeat the installation of the R3SETUP
o The R3SETUP stops for Migration key check phase
o Create the tablespaces of the required sizes
o Restart the database in no archive log mode
o Start the import process
o Complete the post installation steps from the migration guide of 45 B


Some of the problems faced with the above mentioned source and target system

1. During the export phase after the STR files are created the export stops with an error saying
“Cannot connect to DB”

Solution: Delete the following R3load, R3szchk, R3ldctl from the Instal directory.

2. Use update of DB statistics by sapdba or brtools. Avoid using the update of statistics by the export
Process

Solution: Edit the DBEXPORT.R3S file and delete the lines DBCOMPUTESTAT4MIG_XT*,
DBCOMPUTESTAT4MIG*, BRCONNECTEXPSTAT*

3. Export takes a huge time

Solution: Use the java based package splitter to split the STR files for the export.

4. Export fails complaining the PSAPTEMP size is small.

Solution: Check the size of the PSAPTEMP before export. It should be atleast 2 times the size of the largest index for R/3.

5. During the import process check the aio settings in the OS level.

Solution: We have used the mix server of 512 and max server of 1024 during the import process

6. The import process fails due to lack of space to extend the extent at PSAPROLL

Solution: Try using the PSAPUNDO in place of PSAPROLL

7. The total system downtime could be reduced if MIGMON was used. The firewall rule prevented
from the use of migmon between the source and the target.


Post Installation jobs for BASIS area

1. Replace the disp+work of the kernel.
2. Delete the table entries as mentioned in the Heterogeneous system copy guide of 45B
3. Edit the RFC destination address of other connected system.
4. Change the printer definition of the target. Test all the printers are working or we need to make changes in the settings.
5. Set up the TMS
6. Check for the ALE and IDOCS flow after the new system is up
7. Schedule all the basic BASIS background jobs.
8. Take a full offline backup


Reason for moving to Oracle 9.2.0.7

9.2.0.4 being the first stable release of R/3 had many bugs which are listed in the SAP and Oracle sites were later removed in later subsequent releases.

This oracle will be upgraded to Oracle 10.2.0.2 and for the upgrade the source should be at oracle 9.2.0.7 before it can be upgraded to 10g.

As we go higher up in the in the Optimizer gets improved hence the execution plans are improved and this results is transactions executing faster

Using of the Package splitter

The java based package splitter can be used to split the STR files
• Open new terminal preparation for splitter tool.
• Edit packet_splitter_end.properties file
• Use splitter guide for clarification.
• setenv JAVA_HOME /usr/opt/java141.
• using adm user, start split process.