Extend tablespace in SAP system
To resize a tablespace in a SAP-Oracle system there are two ways.
if you want to resize an existing datafile, you can do it with sqlplus as explained in http://bbdd-error.blogspot.com.es/2017/09/extend-tablespace.html
If you want to add another datafile to an existing tablespace you can´t do it with sqlplus because it will generate differences between database catalog and SAP catalog... so it is mandatory to do it with brtools
First of all you need to know tablespace name you are going to resize and path in witch you are going to add the new datafile, also it is important follow existing naming policy..
Tablespace Name MBytes Used Free Used Largest
--------------------------------- ---------------- ---------------- ---------------- ------ ----------------
SYSTEM 2,048 285 1,763 13.9 1,762
SYSAUX 2,048 437 1,611 21.3 1,588
PSAPUNDO 9,700 7,903 1,797 81.5 295
PSAPSR3DB 22,528 18,967 3,561 84.2 1,108
---------------- ---------------- ----------------
sum 36,324 27,592 8,732
We are going to resize PSAPSR3DB tablespace, and to do so we list datafiles belonging to PSAPSR3DB tablespace as you can see below:
SQL> set linesize 140
set pages 130
column FILE_NAME format a100
select FILE_ID, FILE_NAME, BYTES/1024/1024, AUTOEXTENSIBLE from dba_data_files where tablespace_name like 'PSAPSR3DB';SQL> SQL> SQL>
FILE_ID FILE_NAME BYTES/1024/1024 AUT
---------- ---------------------------------------------------------------------------------------------------- --------------- ---
4 /oracle/BD0/sapdata4/sr3db_1/sr3db.data1 6144 NO
5 /oracle/BD0/sapdata4/sr3db_2/sr3db.data2 6144 NO
6 /oracle/BD0/sapdata1/sr3db_3/sr3db.data3 6144 NO
7 /oracle/BD0/sapdata1/sr3db_4/sr3db.data4 4096 NO
after that i check where can i locate new datafile
hostsap0:orajp0 33> bdf | grep /oracle/BD0/sapdata
15728640 21347 14725595 0% /oracle/BD0/sapdata5
15400960 12604212 2621958 83% /oracle/BD0/sapdata4
15400960 21267 14418470 0% /oracle/BD0/sapdata3
18546688 12108806 6035521 67% /oracle/BD0/sapdata2
17498112 14722854 2601809 85% /oracle/BD0/sapdata1
so /oracle/BD0/sapdata3 and /oracle/BD0/sapdata5 are good candidates... (i used bdf commnad because i work in a HP-UX system, but if your system is other you can use "df -k" instead)so i decide to add a new datafile in /oracle/BD0/sapdata3/ called sr3db.data5 of 4Gb. to do that we have to options..
With brtools interactively
hostsap0:orajp0 29> brtools BR0651I BRTOOLS 7.20 (42) BR0280I BRTOOLS time stamp: 2017-09-25 16.06.36 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 ------------------------------------------------------------------------------- BR0662I Enter your choice:
Then press option 2 and intro.
BR0280I BRTOOLS time stamp: 2017-09-25 16.06.59 BR0663I Your choice: '2' BR0280I BRTOOLS time stamp: 2017-09-25 16.06.59 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:Then press option 1 and intro.
BR0280I BRTOOLS time stamp: 2017-09-25 16.39.23 BR0657I Input menu 81 - please enter/check input values ------------------------------------------------------------------------------- BRSPACE options for tablespace extension 1 - BRSPACE profile (profile) ...... [initJP0.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 initJP0.sap -s 20 -l E -f tsextend] Standard keys: c - cont, b - back, s - stop, r - refr, h - help ------------------------------------------------------------------------------- BR0662I Enter your choiceThen press option 3 and intro. and after that system as you tablespace name...
BR0280I BRTOOLS time stamp: 2017-09-25 16.39.32 BR0663I Your choice: '3' BR0681I Enter string value for "tablespace" []:i wrote PSAPSR3DB, but should write your tablespace name..
BR0280I BRTOOLS time stamp: 2017-09-25 16.39.42 BR0683I New value for "tablespace": 'PSAPSR3DB' BR0280I BRTOOLS time stamp: 2017-09-25 16.39.42 BR0657I Input menu 81 - please enter/check input values ------------------------------------------------------------------------------- BRSPACE options for tablespace extension 1 - BRSPACE profile (profile) ...... [initJP0.sap] 2 - Database user/password (user) .. [/] 3 ~ Tablespace name (tablespace) ... [PSAPSR3DB] 4 - Confirmation mode (confirm) .... [yes] 5 - Scrolling line count (scroll) .. [20] 6 - Message language (language) .... [E] 7 - BRSPACE command line (command) . [-p initJP0.sap -s 20 -l E -f tsextend -t PSAPSR3DB] Standard keys: c - cont, b - back, s - stop, r - refr, h - help ------------------------------------------------------------------------------- BR0662I Enter your choice:
after that it is needed to press 'c' and intro:
BR0663I Your choice: 'c' BR0259I Program execution will be continued... BR0291I BRSPACE will be started with options '-p initJP0.sap -s 20 -l E -f tsextend -t PSAPSR3DB' BR0280I BRTOOLS time stamp: 2017-09-25 16.43.11press 'c' and into again
BR0280I BRTOOLS time stamp: 2017-09-25 16.43.14 BR0257I Your reply: 'c' BR0259I Program execution will be continued... ############################################################################### BR1001I BRSPACE 7.20 (42) BR1002I Start of BRSPACE processing: sewuizhi.tse 2017-09-25 16.43.14 BR0484I BRSPACE log file: /oracle/BD0/sapreorg/sewuizhi.tse BR0280I BRSPACE time stamp: 2017-09-25 16.43.15 BR1009I Name of database instance: BD0 BR1010I BRSPACE action ID: sewuizhi BR1011I BRSPACE function ID: tse BR1012I BRSPACE function: tsextend BR0280I BRSPACE time stamp: 2017-09-25 16.43.15 BR0657I Input menu 303 - please enter/check input values ------------------------------------------------------------------------------- Options for extension of tablespace PSAPSR3DB (1. file) 1 * Last added file name (lastfile) ....... [/oracle/BD0/sapdata1/sr3db_4/sr3db.data4] 2 * Last added file size in MB (lastsize) . [4096] 3 - New file to be added (file) ........... [/oracle/BD0/sapdata1/sr3db_5/sr3db.data5] 4 ~ Raw disk / link target (rawlink) ...... [] 5 - Size of the new file in MB (size) ..... [4096] 6 - File autoextend mode (autoextend) ..... [no] 7 # Maximum file size in MB (maxsize) ..... [] 8 # File increment size in MB (incrsize) .. [] 9 - SQL command (command) ................. [alter tablespace PSAPSR3DB add datafile '/oracle/BD0/sapdata1/sr3db_5/sr3db.data5' size 4096M autoextend off] Standard keys: c - cont, b - back, s - stop, r - refr, h - help ------------------------------------------------------------------------------- BR0662I Enter your choice:at this point brtools proposes us file '/oracle/BD0/sapdata1/sr3db_5/sr3db.data5' to be added, but we want to add new file on filesystem /oracle/BD0/sapdata3 so we need to modify this parameter by selecting option 3
3 BR0280I BRSPACE time stamp: 2017-09-25 16.50.00 BR0663I Your choice: '3' BR0681I Enter string value for "file" (| | ) [/oracle/BD0/sapdata1/sr3db_5/sr3db.data5]:
and write the new filesystem path (/oracle/BD0/sapdata3) as you can see below (do not write a final slash in yuor path):
/oracle/BD0/sapdata3 BR0280I BRSPACE time stamp: 2017-09-25 16.52.12 BR0683I New value for "file": '/oracle/BD0/sapdata3' BR0280I BRSPACE time stamp: 2017-09-25 16.52.12 BR0657I Input menu 303 - please enter/check input values ------------------------------------------------------------------------------- Options for extension of tablespace PSAPSR3DB (1. file) 1 * Last added file name (lastfile) ....... [/oracle/BD0/sapdata1/sr3db_4/sr3db.data4] 2 * Last added file size in MB (lastsize) . [4096] 3 - New file to be added (file) ........... [/oracle/BD0/sapdata3/sr3db_5/sr3db.data5] 4 ~ Raw disk / link target (rawlink) ...... [] 5 - Size of the new file in MB (size) ..... [4096] 6 - File autoextend mode (autoextend) ..... [no] 7 # Maximum file size in MB (maxsize) ..... [] 8 # File increment size in MB (incrsize) .. [] 9 - SQL command (command) ................. [alter tablespace PSAPSR3DB add datafile '/oracle/BD0/sapdata3/sr3db_5/sr3db.data5' size 4096M autoextend off] Standard keys: c - cont, b - back, s - stop, r - refr, h - help ------------------------------------------------------------------------------- BR0662I Enter your choice:
If you want to modify file size you can do that pressing option 5 and size you whant in Mb..
BR0682I Enter integer value for "size" (1-32767) [4096]: 2048 BR0280I BRSPACE time stamp: 2017-09-25 16.59.11 BR0683I New value for "size": '2048' BR0280I BRSPACE time stamp: 2017-09-25 16.59.11 BR0657I Input menu 303 - please enter/check input values ------------------------------------------------------------------------------- Options for extension of tablespace PSAPSR3DB (1. file) 1 * Last added file name (lastfile) ....... [/oracle/BD0/sapdata1/sr3db_4/sr3db.data4] 2 * Last added file size in MB (lastsize) . [4096] 3 - New file to be added (file) ........... [/oracle/BD0/sapdata3/sr3db_5/sr3db.data5] 4 ~ Raw disk / link target (rawlink) ...... [] 5 - Size of the new file in MB (size) ..... [2048] 6 - File autoextend mode (autoextend) ..... [no] 7 # Maximum file size in MB (maxsize) ..... [] 8 # File increment size in MB (incrsize) .. [] 9 - SQL command (command) ................. [alter tablespace PSAPSR3DB add datafile '/oracle/BD0/sapdata3/sr3db_5/sr3db.data5' size 2048M autoextend off] Standard keys: c - cont, b - back, s - stop, r - refr, h - help ------------------------------------------------------------------------------- BR0662I Enter your choice:and finally if we agree with all parameter we should press 'c' option followed by intro
Standard keys: c - cont, b - back, s - stop, r - refr, h - help ------------------------------------------------------------------------------- BR0662I Enter your choice: c BR0280I BRSPACE time stamp: 2017-09-25 17.00.34 BR0663I Your choice: 'c' BR0259I Program execution will be continued... BR1091I Next data file can be specified now BR0280I BRSPACE time stamp: 2017-09-25 17.00.34 BR0675I This is a optional action - do you want to execute it now? BR0676I Enter 'y[es]' to execute the action, 'n[o]/c[ont]' to skip it, 's[top]' to abort:
and press c again (not yes) if you want brtools to execute this command now
BR0280I BRSPACE time stamp: 2017-09-25 17.01.01 BR0257I Your reply: 'c' BR0678I The action will be skipped... BR0259I Program execution will be continued... BR0370I Directory /oracle/BD0/sapreorg/sewujanh created BR0280I BRSPACE time stamp: 2017-09-25 17.01.02 BR0319I Control file copy created: /oracle/BD0/sapreorg/sewujanh/cntrlJP0.old 21446656 BR0370I Directory /oracle/BD0/sapdata3/sr3db_5 created BR0280I BRSPACE time stamp: 2017-09-25 17.01.02 BR1088I Extending tablespace PSAPSR3DB ... BR0280I BRSPACE time stamp: 2017-09-25 17.01.13 BR1016I SQL statement 'alter tablespace PSAPSR3DB add datafile '/oracle/BD0/sapdata3/sr3db_5/sr3db.data5' size 2048M autoextend off' executed successfully BR1051I Tablespace PSAPSR3DB extended successfully with file: /oracle/BD0/sapdata3/sr3db_5/sr3db.data5 2048M BR0280I BRSPACE time stamp: 2017-09-25 17.01.13 BR0340I Switching to the next online redolog file for database instance BD0 ... BR0321I Switch to the next online redolog file for database instance BD0 successful BR0280I BRSPACE time stamp: 2017-09-25 17.01.14 BR0319I Control file copy created: /oracle/BD0/sapreorg/sewujanh/cntrlJP0.new 21446656 BR0280I BRSPACE time stamp: 2017-09-25 17.01.14 BR0670I Enter 'c[ont]' to continue, 'b[ack]' to go back, 's[top]' to abort:
Finally, i´m going to tell you which way you can do this process simpler and directly.. you can do that with this command from unix prompt:
brspace -f tsextend -t PSAPSR3DB -s 2048 -a no -f /oracle/BD0/sapdata3/sr3db_5/sr3db.data5
Execution example of the command with "-c force" unattended mode :
hostsap0:oracle 5% brspace -c force -f tsextend -t PSAPSR3 -s 2048 -a no -f /oracle/BD0/sapdata3/sr3db_5/sr3db.data5 BR1001I BRSPACE 7.20 (42) BR1002I Start of BRSPACE processing: sewujsln.tse 2017-09-25 20.19.07 BR0484I BRSPACE log file: /oracle/BD0/sapreorg/sewujsln.tse BR0280I BRSPACE time stamp: 2017-09-25 20.19.07 BR1009I Name of database instance: BD0 BR1010I BRSPACE action ID: sewujsln BR1011I BRSPACE function ID: tse BR1012I BRSPACE function: tsextend BR0134I Unattended mode with 'force' active - no operator confirmation allowed BR0280I BRSPACE time stamp: 2017-09-25 20.19.07 BR0657I Input menu 303 # please enter/check input values ------------------------------------------------------------------------------- Options for extension of tablespace PSAPSR3 (1. file) 1 * Last added file name (lastfile) ....... [/oracle/BD0/sapdata2/sr3_2/sr3.data2] 2 * Last added file size in MB (lastsize) . [11640] 3 - New file to be added (file) ........... [/oracle/BD0/sapdata2/sr3_3/sr3.data3] 4 ~ Raw disk / link target (rawlink) ...... [] 5 - Size of the new file in MB (size) ..... [2048] 6 - File autoextend mode (autoextend) ..... [no] 7 # Maximum file size in MB (maxsize) ..... [] 8 # File increment size in MB (incrsize) .. [] 9 - SQL command (command) ................. [alter tablespace PSAPSR3 add datafile '/oracle/BD0/sapdata2/sr3_3/sr3.data3' size 2048M autoextend off] Standard keys: c - cont, b - back, s - stop, r - refr, h - help ------------------------------------------------------------------------------- BR0280I BRSPACE time stamp: 2017-09-25 20.19.07 BR0134I Unattended mode with 'force' active - continuing processing with default reply 'cont' BR1091I Next data file can be specified now BR0280I BRSPACE time stamp: 2017-09-25 20.19.07 BR0675I This is a optional action - do you want to execute it now? BR0126I Unattended mode active - continuing processing with default reply 'no' BR0370I Directory /oracle/BD0/sapreorg/sewujsln created BR0280I BRSPACE time stamp: 2017-09-25 20.19.08 BR0319I Control file copy created: /oracle/BD0/sapreorg/sewujsln/cntrlPLP.old 24690688 BR0370I Directory /oracle/BD0/sapdata2/sr3_3 created BR0280I BRSPACE time stamp: 2017-09-25 20.19.08 BR1088I Extending tablespace PSAPSR3 ... BR0280I BRSPACE time stamp: 2017-09-25 20.19.10 BR1016I SQL statement 'alter tablespace PSAPSR3 add datafile '/oracle/BD0/sapdata2/sr3_3/sr3.data3' size 2048M autoextend off' executed successfully BR1051I Tablespace PSAPSR3 extended successfully with file: /oracle/BD0/sapdata3/sr3db_5/sr3db.data5 2048M BR0280I BRSPACE time stamp: 2017-09-25 20.19.10 BR0340I Switching to the next online redolog file for database instance BD0 ... BR0321I Switch to the next online redolog file for database instance BD0 successful BR0280I BRSPACE time stamp: 2017-09-25 20.19.11 BR0319I Control file copy created: /oracle/BD0/sapreorg/sewujsln/cntrlPLP.new 24690688 BR0280I BRSPACE time stamp: 2017-09-25 20.19.11 BR1020I Number of tablespaces processed: 1 BR1003I BRSPACE function 'tsextend' completed BR1008I End of BRSPACE processing: sewujsln.tse 2017-09-25 20.19.11 BR0280I BRSPACE time stamp: 2017-09-25 20.19.11 BR1005I BRSPACE completed successfully
Comments
Post a Comment