Today we will discuss the SAP recommended way that is to use BRtools to add datafiles to oracle database.
While performing many Basis activities like reorganization /upgrade, we come across the space issue with the Oracle tablespace.
Apart from BRtools,there are different methods to add additional datafiles like SQL queries /scripts or simply via hitting commands in SQLPLUS command prompt.
Here let’s assume our DB host as SAPDEV and <SID>=NED.
Start BRtools:
Login with SIDADM /ORASID user and execute command brtools:
brtoolsTool will display multiple option:
Select option 2: Space Management in BRTOOLS
BR0280I BRTOOLS time stamp: 2019-03-11 09.21.24
BR0656I Choice menu 1 - please make a selection
-------------------------------------------------------------------------------
BR*Tools main menu
1 = Instance management
2 - Space management
3 - Segment management
4 - Backup and database copy
5 - Restore and recovery
6 - Check and verification
7 - Database statistics
8 - Additional functions
9 - Exit program
Standard keys: c - cont, b - back, s - stop, r - refr, h - help
-------------------------------------------------------------------------------
BR0885I Enter your choice:
2
BR0280I BRTOOLS time stamp: 2019-03-11 09.21.43
BR0663I Your choice: '2'
Select option 1: Extend tablespace
BR0280I BRTOOLS time stamp: 2019-03-11 09.22.12
BR0656I Choice menu 5 - please make a selection
-------------------------------------------------------------------------------
Database space management
1 = Extend tablespace
2 - Create tablespace
3 - Drop tablespace
4 - Alter tablespace
5 - Alter data file
6 - Move data file
7 - Additional space functions
8 - Reset program status
Standard keys: c - cont, b - back, s - stop, r - refr, h - help
-------------------------------------------------------------------------------
BR0662I Enter your choice:
1
BR0280I BRTOOLS time stamp: 2019-03-11 09.22.37
BR0663I Your choice: '1'
Choose option-3 and enter the tablespace name:
BR0280I BRTOOLS time stamp: 2019-03-11 09.23.10
BR0657I Input menu 81 - please check/enter input values
-------------------------------------------------------------------------------
BRSPACE options for tablespace extension
1 - BRSPACE profile (profile) ...... [init<SID>.sap]
2 - Database user/password (user) .. [/]
3 ~ Tablespace name (tablespace) ... []
4 - Confirmation mode (confirm) .... [yes]
5 - Scrolling line count (scroll) .. [20]
6 - Message language (language) .... [E]
7 - BRSPACE command line (command) . [-p init<SID>.sap -s 20 -l E -f tsextend]
Standard keys: c - cont, b - back, s - stop, r - refr, h - help
-------------------------------------------------------------------------------
BR0662I Enter your choice:
3
BR0280I BRTOOLS time stamp: 2019-03-11 09.24.37
BR0663I Your choice: '3'
BR0280I BRTOOLS time stamp: 2019-03-11 09.24.37
BR0681I Enter string value for "tablespace" []:PSAPNED
Verify the options set for tablespace:
BR0280I BRTOOLS time stamp: 2019-03-11 09.24.45
BR0663I Your choice: '3'
BR0280I BRTOOLS time stamp: 2019-03-11 09.24.45
BR0681I Enter string value for "tablespace" []:
PSAPSR3
BR0280I BRTOOLS time stamp: 2019-03-11 09.25.15
BR0683I New value for "tablespace": 'PSAPNED'
BR0280I BRTOOLS time stamp: 2019-03-11 09.25.15
BR0657I Input menu 81 - please check/enter input values
-------------------------------------------------------------------------------
BRSPACE options for tablespace extension
1 - BRSPACE profile (profile) ...... [init<SID>.sap]
2 - Database user/password (user) .. [/]
3 ~ Tablespace name (tablespace) ... [PSAPNED]
4 - Confirmation mode (confirm) .... [yes]
5 - Scrolling line count (scroll) .. [20]
6 - Message language (language) .... [E]
7 - BRSPACE command line (command) .
[-p init<SID>.sap -s 20 -l E -f tsextend -t PSAPNED]
Standard keys: c - cont, b - back, s - stop, r - refr, h - help
-------------------------------------------------------------------------------
BR0662I Enter your choice:
c
Continue: Press C
BR0280I BRTOOLS time stamp: 2019-03-11 09.25.48
BR0663I Your choice: 'c'
BR0259I Program execution will be continued...
BR0291I BRSPACE will be started with options
‘-p init<SID>.sap -s 20 -l E -f tsextend -t PSAPNED’
Here, let us discuss in details each Menu term.
| Equivalent BRSPACE Command Option | Menu Entry |
| -p|-profile | BRSPACE profile (profile) |
| -s|-scroll | Scrolling line count (scroll) |
| -l|-language | Message language (language) |
| -f tsextend | Tablespace Extend |
| -t|-tablespace | Tablespace name (tablespace) |
BR0280I BRTOOLS time stamp: 2019-03-11 09.25.48
BR0670I Enter 'c[ont]' to continue, 'b[ack]' to go back, 's[top]' to abort:
cIn this next step, we can see the details of last datafile added and other details like name of new datafile, it’s size, if autoextend mode to be activated etc.
BR0280I BRTOOLS time stamp: 2019-03-11 09.28.10
BR0257I Your reply: 'c'
BR0259I Program execution will be continued...
###############################################################################
BR1001I BRSPACE 7.00 (32)
BR1002I Start of BRSPACE processing: sefmufom.tse 2019-03-11 09.28.35
BR0484I BRSPACE log file: /oracle/NED/sapreorg/zewshum.tse
BR0280I BRSPACE time stamp: 2019-03-11 09.28.35
BR1009I Name of database instance: <SID>
BR1010I BRSPACE action ID: sefmufom
BR1011I BRSPACE function ID: tse
BR1012I BRSPACE function: tsextend
BR0280I BRSPACE time stamp: 2019-03-11 09.28.35
BR0657I Input menu 303 - please check/enter input values
-------------------------------------------------------------------------------
Options for extension of tablespace PSAPNED (1. file)
1 * Last added file name (lastfile) .......
[/oracle/NED/sapdata4/sr3_20/sr3.data20]
2 * Last added file size in MB (lastsize) . [2160]
3 - New file to be added (file) ...........
[/oracle/NED/sapdata4/sr3_21/sr3.data21]
4 ~ Raw disk / link target (rawlink) ...... []
5 - Size of the new file in MB (size) ..... [2160]
6 - File autoextend mode (autoextend) ..... [yes]
7 - Maximum file size in MB (maxsize) ..... [10000]
8 - File increment size in MB (incrsize) .. [20]
9 - SQL command (command) ................. [alter tablespace PSAPNED add datafile
'/oracle/NED/sapdata4/sr3_21/sr3.data21' size 2160M autoextend on next 20M maxsize 10000M]
Standard keys: c - cont, b - back, s - stop, r - refr, h - help
-------------------------------------------------------------------------------
BR0662I Enter your choice:
c
BR0280I BRSPACE time stamp: 2019-03-11 09.29.01
BR0670I Enter 'c[ont]' to continue, 'b[ack]' to go back, 's[top]' to abort:
c
BR0280I BRSPACE time stamp: 2019-03-11 09.29.01
BR0257I Your reply: 'c'
BR0259I Program execution will be continued...If we need to add additional datafile, type ‘y’ or else type ‘c’ to continue .
BR0280I BRSPACE time stamp: 2019-03-11 09.29.11
BR1091I Next data file can be specified now
BR0675I Do you want to perform this action?
BR0676I Enter 'y[es]' to perform the action, 'n[o]/c[ont]' to skip it, 's[top]' to abort:
c
BR0280I BRSPACE time stamp: 2019-03-11 09.29.28
BR0257I Your reply: 'c'
BR0678I The action will be skipped...
BR0259I Program execution will be continued...Tablespace PSAPNED will be extended successfully.
BR0280I BRSPACE time stamp: 2019-03-11 09.32.21
BR1016I SQL statement 'alter tablespace PSAPSR3 add datafile
'/oracle/NED/sapdata4/sr3_21/sr3.data21' size 2160M autoextend on next 20M maxsize 10000M,
'/oracle/NED/sapdata4/sr3_22/sr3.data22' size 2160M autoextend on next 20M maxsize 10000M' executed successfully
BR1051I Tablespace PSAPNED extended successfully with files:
/oracle/NED/sapdata4/sr3_21/sr3.data21 2160M
Verification:
We can verify the extended space via transaction DB02 or DBACOCKPIT in SAP.