How to Check DB2 database on SAP Level

We can check the database on SAP level with tcode DB02 or DBACOCKPIT and navigate to space -> Space Overview to view

• Database Name
• Database System
• Size
• Free Size
• Used Percentage
• Number of Tablespaces
• Number of Container Tablespaces

In DB02 or DBACOCKPIT, navigate to space -> space overview to find all on below details

Database Name and Database System

 
Total Size of Database, Free size and Used Percentage of Database

 In DB02 or DBACOCKPIT, navigate to tablespaces -> tablespaces overview to find all on below details

Total Size of Tablespaces, Free size and Used Percentage of Tablespaces

Tablespaces content name

 In DB02 or DBACOCKPIT, navigate to Containers -> containers overview to find all on below details

List Containers of tablespaces

How to reallocate and rename a datafile on SAP oracle

The Condition if we want to reallocate and rename a datafile oracle :

a. We have created a datafile on the wrong filesystem or drive.
b. We have created a datafile where a hidden character or a linefeed was introduced by by accident.
c. We add a new filesystem(s), drive(s) to the system and want to redistribute the datafiles.
d. We restore some datafiles to a new location because the original is not available any more ( media error )

Before setting offline any tablespace, be sure that all datafiles of the tablespace are ONLINE.

For example, use the following command:

Login to SQL from the user <oraSID>

Erppi1# su orapi1

erppi1:orapi1 54>

erppi1:orapi1 53> sqlplus / as sysdba

SQL>
SQL> SELECT vtbs.name, vdf.status, count(*) cnt FROM  v$datafile vdf, v$tablespace vtbs WHERE  vdf.TS#=vtbs.TS# AND vtbs.name = '<tablespace_name>' GROUP BY vtbs.name, vdf.status;

There are several options to perform this condition:

Option 1) 

Connect to the open database:
Login to SQL from the user <oraSID>

Erppi1# su orapi1

erppi1:orapi1 54>

erppi1:orapi1 53> sqlplus / as sysdba

Take the tablespace offline:
SQL> ALTER TABLESPACE <NAME> OFFLINE;

Copy the datafiles to the new location by using OS commands manually: 
Compare the size of the two files, they have to be the same

Update the new datafile location with one of this commands:
SQL> ALTER DATABASE RENAME FILE '<old>' TO '<new>';
SQL> ALTER TABLESPACE <NAME> RENAME DATAFILE '<old>' TO '<new>';

Bring the tablespace online with the command:
SQL> ALTER TABLESPACE <NAME> ONLINE;

Remove at OS level the original file.

You cannot use this method for the SYSTEM tablespace.


Option 2)

Stop the database:
Login to SQL from the user <oraSID>

Erppi1# su orapi1

erppi1:orapi1 54>

erppi1:orapi1 53> sqlplus / as sysdba
SQL>
SQL> shutdown immediate;

Copy the datafiles to the new location by using OS commands manually: 

Compare the size of the two files, they have to be the same

Mount the database:

Login to SQL from the user <oraSID>

Erppi1# su orapi1

erppi1:orapi1 54>

erppi1:orapi1 53> sqlplus / as sysdba

SQL>

SQL> startup mount;

Update the new datafile location:

Login to SQL from the user <oraSID>

Erppi1# su orapi1

erppi1:orapi1 54>

erppi1:orapi1 53> sqlplus / as sysdba

SQL>

SQL> ALTER DATABASE RENAME FILE '<old>' TO '<new>';

Open the database:

Login to SQL from the user <oraSID>

Erppi1# su orapi1

erppi1:orapi1 54>

erppi1:orapi1 53> sqlplus / as sysdba

SQL>

SQL> ALTER DATABASE OPEN;

Remove at OS level the original file.


Option 3)

This option is useful when you can not type the name due to the linefeed or hidden character.

Make a trace file:

Login to SQL from the user <oraSID>

Erppi1# su orapi1

erppi1:orapi1 54>

erppi1:orapi1 53> sqlplus / as sysdba

SQL>

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

Modify datafile pointers in the tracefile generated above to reflect the changes and rename the file so the extension is "sql".

Stop the database:
Login to SQL from the user <oraSID>

Erppi1# su orapi1

erppi1:orapi1 54>

erppi1:orapi1 53> sqlplus / as sysdba
SQL>
SQL> shutdown immediate;

Copy the datafiles to the new location by using OS commands manually: 

Compare the size of the two files, they have to be the same

Run the script created before:

Login to SQL from the user <oraSID>

Erppi1# su orapi1

erppi1:orapi1 54>

erppi1:orapi1 53> sqlplus / as sysdba
SQL>
SQL> @filename.sql

Remove at OS level the original file.


Option 4)

Oracle 12c includes an enhancement to the ALTER DATABASE command to allow datafiles to be moved online: 
Login to SQL from the user <oraSID>

Erppi1# su orapi1

erppi1:orapi1 54>

erppi1:orapi1 53> sqlplus / as sysdba
SQL>
SQL> ALTER DATABASE MOVE DATAFILE ('<old>') TO ('<new>') [REUSE] [KEEP];

The REUSE keyword indicates the new file should be created even if it already exists.
The KEEP keyword indicates the original copy of the datafile should be retained.

This option is also available in the BRSPACE tool (function "dfmove" option "-o yes").

NOTE: BEFORE making any structural changes to a database, such as renaming and relocating the datafiles of one or more tablespaces, always completely backup the database. AFTER making any structural changes to a database, always perform an immediate and complete backup.

Difference between PFILE and SPFILE in Oracle

A PFILE is a static, client side text file. This file normally resides on the server. However in case you would like to start oracle from a remote machine, you will need a local copy of the PFILE in that machine.

This file can be edited with a standard text editor like notepad or vi editor. This file will be commonly referred as INIT.ORA file.

SPFILE (Server parameter file) is a persistent server side binary file. SPFILE should only be modified through “ALTER SYSTEM SET” command . Editing an SPFILE directly will corrupt the file and the start up of the database or instance will not be possible.

As SPFILe is a server side binary file,  local copy of the PFILE is not required to start oracle from a remote machine. 

Advantages of SPFILE compared to PFILE

• A SPFILE doesnot need a local copy of  the pfile to start  oracle from a remote machine.  Thus eliminates configuration problems.

• SPFILE  is a binary file and modications to that can only be done through ALTER SYSTEM SET command.

• As SPFILE is maintained by the server, human errors can be eliminated as the parameters are checked before modification in SPFILE

• It is easy  to locate SPFILE as it is stored in a central location

• Changes to the parameters in SPFILE will take immediate effect without restart of the instance i.e Dynamic change of parameters is possible

• SPFILE can be backed up by RMAN

Difference between PFILE and SPFILE in Oracle :

Query to findout, if database is running with PFILE or SPFILE

Goto SQL prompt and execute the following command to figure out if database is started with a PFILE or SPFILE.

SQL > SELECT DECODE(value, NULL, ‘PFILE’, ‘SPFILE’) “Init File” FROM sys.v_$parameter WHERE name = ‘spfile’; 

How to rename or move control files location in SAP oracle

Query to find current location of control files :

SQL > select name from v$controlfile;

After executing the above query, System outputs  the location of all control files in the system

Usually there will be 3 control files in oracle database( for SAP applications).

Control files location can changed with the control_files instance parameter.

Please execute the below query to display the current value of control_files parameter

SQL > show parameter control_files

The above command outputs the current value. This value can be changed with the ALTER SYSTEM command. In other words the current location of the control files can be changed  using ALTER SYSTEM command.

Steps to be followed to rename or move control files location

1)  Alter the control_files parameter using the ALTER SYSTEM command

Syntax:
SQL> ALTER SYSTEM SET control_files=’<New controlfile1 path or newcontrol file name>’, <New controlfile2 path or newcontrol file name>’, <New controlfile13 path or newcontrol name>’ SCOPE=SPFILE;

Eg: In the below command set the paths or names  for various control files  as per your requirement

SQL> ALTER SYSTEM SET control_files=’D:\ORACLE\..\..\..\Newname1.CTL’, ’D:\ORACLE\..\..\..\Newname2.CTL’,  ’D:\ORACLE\..\..\..\Newname3.CTL’ SCOPE=SPFILE;


2) Shutdown the database

     SQL> shutdown immediate;


3) Rename the physical file in the OS

     SQL>  HOST  MOVE <old controlfile with path> <new controlfile with path > 


Eg:
     SQL> HOST  MOVE  D:\ORACLE\ORADATA\CONTROL01.CTL  D:\ORACLE\ORADATA\NEW_CONTROL01.CTL;


4) Start the database

     SQL> startup;

How to Extend Tablespaces on SAP Oracle with BRtools

The purpose is this document is to show how to extend a database table space using BRTOOLS in LINUX environment and then verify whether the in the new data file has been added correctly or not.

Steps for Extend Tablespace :

1.  Check tablespaces before extend on SAP using tcode DB02 and check database size

select tablespace which is intend to extend (eg: PSAPSR3)

It shows the last datafile as the sr3.data18

2. Check using SQL command on OS and select datafile of tablespace

Login to SQL from the user <oraSID>

Erppi1# su orapi1

erppi1:orapi1 54>

erppi1:orapi1 53> sqlplus / as sysdba

SQL>

SQL> select file_name from dba_data_files;

Then It will display datafiles till the last.

Eg : /oracle/PI1/sapdata4/sr3_18/sr3.data18

3. Extend Tablespace on Oracle 

Log in to the operating system with a user who has authorize for Oracle DBA and then run the command BRTOOLS.

Erppi1# su orapi1

erppi1:orapi1 54> brtools

BR0651I BRTOOLS 7.00 (32)

BR0280I BRTOOLS time stamp: 2012-06-26 09.51.52

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:

2

BR0280I BRTOOLS time stamp: 2012-06-26 09.51.56

BR0663I Your choice: ‘2’

BR0280I BRTOOLS time stamp: 2012-06-26 09.51.56

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: 2012-06-26 09.51.56

BR0663I Your choice: ‘1’

BR0280I BRTOOLS time stamp: 2012-06-26 09.51.56

BR0657I Input menu 81 – please check/enter input values

——————————————————————————-

BRSPACE options for tablespace extension

1 – BRSPACE profile (profile) …… [initPI1.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 initPI1.sap -s 20 -l E -f tsextend]

Standard keys: c – cont, b – back, s – stop, r – refr, h – help

——————————————————————————-

BR0662I Enter your choice:

c

BR0280I BRTOOLS time stamp: 2012-06-26 09.52.04

BR0663I Your choice: ‘c’

BR0259I Program execution will be continued…

BR0291I BRSPACE will be started with options ‘-p initPI1.sap -s 20 -l E -f tsextend’

BR0280I BRTOOLS time stamp: 2012-06-26 09.52.04

BR0670I Enter ‘c[ont]’ to continue, ‘b[ack]’ to go back, ‘s[top]’ to abort:

c

BR0280I BRTOOLS time stamp: 2012-06-26 09.52.11

BR0257I Your reply: ‘c’

BR0259I Program execution will be continued…

###############################################################################

BR1001I BRSPACE 7.00 (32)

BR1002I Start of BRSPACE processing: seivvhdr.tse 2012-06-26 09.52.11

BR0484I BRSPACE log file: /oracle/PI1/sapreorg/seivvhdr.tse

BR0280I BRSPACE time stamp: 2012-06-26 09.52.11

BR1009I Name of database instance: PI1

BR1010I BRSPACE action ID: seivvhdr

BR1011I BRSPACE function ID: tse

BR1012I BRSPACE function: tsextend

BR0280I BRSPACE time stamp: 2012-06-26 09.52.11

BR0656I Choice menu 301 – please make a selection

——————————————————————————-

Tablespace extension main menu

1 = Extend tablespace

2 – Show tablespaces

3 – Show data files

4 – Show disk volumes

5 * Exit program

6 – Reset program status

Standard keys: c – cont, b – back, s – stop, r – refr, h – help

——————————————————————————-

BR0662I Enter your choice:

c

BR0280I BRSPACE time stamp: 2012-06-26 09.52.23

BR0663I Your choice: ‘c’

BR0259I Program execution will be continued…

BR0280I BRSPACE time stamp: 2012-06-26 09.52.24

BR0658I List menu 302 – please select one entry

——————————————————————————-

List of tablespaces for extension

Pos. Tablespace    Files/AuExt.    Total[KB]      Used[%]    Free[KB]       MaxSize[KB]

  1 – PSAPSR3          18/18         175484928     96.07       6901376       176291840

  2 – PSAPSR3700      9/9           88948736       87.78       10866112     88948736

  3 – PSAPSR3USR    1/1           389120           95.82       16256           10240000

  4 – PSAPTEMP         1/0           2048000         0.00         2048000       2048000

  5 – PSAPUNDO        1/1           7987200         0.00         7987136       10240000

  6 – SYSAUX             1/1            634880          89.99        63552           10240000

  7 – SYSTEM             1/1            921600          98.41        14656           10240000

Standard keys: c – cont, b – back, s – stop, r – refr, h – help

——————————————————————————-

BR0662I Enter your selection:

1

BR0280I BRSPACE time stamp: 2012-06-26 09.52.27

BR0663I Your selection: ‘1’

BR0280I BRSPACE time stamp: 2012-06-26 09.52.27

BR0657I Input menu 303 – please check/enter input values

——————————————————————————-

Options for extension of tablespace PSAPSR3 (1. file)

1 * Last added file name (lastfile) ……. [/oracle/PI1/sapdata4/sr3_18/sr3.data18]

2 * Last added file size in MB (lastsize) . [9216]

3 – New file to be added (file) ……….. [/oracle/PI1/sapdata4/sr3_19/sr3.data19]

4 ~ Raw disk / link target (rawlink) …… []

5 – Size of the new file in MB (size) ….. [9216]

6 – File autoextend mode (autoextend) ….. [yes]

7 ? Maximum file size in MB (maxsize) ….. []

8 – File increment size in MB (incrsize) .. [20]

9 – SQL command (command) …………….. [alter tablespace PSAPSR3 add datafile ‘/oracle/PI1/sapdata4/sr3_19/sr3.data19’ size 9216M autoextend on next 20M maxsize M]

Standard keys: c – cont, b – back, s – stop, r – refr, h – help

——————————————————————————-

BR0662I Enter your choice:

5

BR0280I BRSPACE time stamp: 2012-06-26 09.57.52

BR0663I Your choice: ‘5’

BR0280I BRSPACE time stamp: 2012-06-26 09.57.52

BR0682I Enter integer value for “size” (1-32767) [9216]:

9216

BR0280I BRSPACE time stamp: 2012-06-26 09.57.59

BR0683I New value for “size”: ‘9216’

BR0280I BRSPACE time stamp: 2012-06-26 09.57.59

BR0657I Input menu 303 – please check/enter input values

——————————————————————————-

Options for extension of tablespace PSAPSR3 (1. file)

1 * Last added file name (lastfile) ……. [/oracle/PI1/sapdata4/sr3_18/sr3.data18]

2 * Last added file size in MB (lastsize) . [9216]

3 – New file to be added (file) ……….. [/oracle/PI1/sapdata4/sr3_19/sr3.data19]

4 ~ Raw disk / link target (rawlink) …… []

5 – Size of the new file in MB (size) ….. [9216]

6 – File autoextend mode (autoextend) ….. [yes]

7 ? Maximum file size in MB (maxsize) ….. []

8 – File increment size in MB (incrsize) .. [20]

9 – SQL command (command) …………….. [alter tablespace PSAPSR3 add datafile ‘/oracle/PI1/sapdata4/sr3_19/sr3.data19’ size 9216M autoextend on next 20M maxsize M]

Standard keys: c – cont, b – back, s – stop, r – refr, h – help

——————————————————————————-

BR0662I Enter your choice:

7

BR0280I BRSPACE time stamp: 2012-06-26 09.58.19

BR0663I Your choice: ‘7’

BR0280I BRSPACE time stamp: 2012-06-26 09.58.19

BR0682I Enter integer value for “maxsize” (0-32767) []:

9216

BR0280I BRSPACE time stamp: 2012-06-26 09.58.24

BR0683I New value for “maxsize”: ‘9216’

BR0280I BRSPACE time stamp: 2012-06-26 09.58.24

BR0657I Input menu 303 – please check/enter input values

——————————————————————————-

Options for extension of tablespace PSAPSR3 (1. file)

1 * Last added file name (lastfile) ……. [/oracle/PI1/sapdata4/sr3_18/sr3.data18]

2 * Last added file size in MB (lastsize) . [9216]

3 – New file to be added (file) ……….. [/oracle/PI1/sapdata4/sr3_19/sr3.data19]

4 ~ Raw disk / link target (rawlink) …… []

5 – Size of the new file in MB (size) ….. [9216]

6 – File autoextend mode (autoextend) ….. [yes]

7 – Maximum file size in MB (maxsize) ….. [9216]

8 – File increment size in MB (incrsize) .. [20]

9 – SQL command (command) …………….. [alter tablespace PSAPSR3 add datafile ‘/oracle/PI1/sapdata4/sr3_19/sr3.data19’ size 9216M autoextend on next 20M maxsize 9216M]

Standard keys: c – cont, b – back, s – stop, r – refr, h – help

——————————————————————————-

BR0662I Enter your choice:

c

BR0280I BRSPACE time stamp: 2012-06-26 09.58.30

BR0663I Your choice: ‘c’

BR0259I Program execution will be continued…

BR0280I BRSPACE time stamp: 2012-06-26 09.58.30

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: 2012-06-26 09.59.30

BR0257I Your reply: ‘c’

BR0678I The action will be skipped…

BR0259I Program execution will be continued…

BR0280I BRSPACE time stamp: 2012-06-26 09.59.30

BR0370I Directory /oracle/PI1/sapreorg/seivvhdr created

BR0280I BRSPACE time stamp: 2012-06-26 09.59.33

BR0319I Control file copy created: /oracle/PI1/sapreorg/seivvhdr/cntrlPI1.old 31211520

BR0280I BRSPACE time stamp: 2012-06-26 09.59.33

BR0370I Directory /oracle/PI1/sapdata4/sr3_19 created

BR0280I BRSPACE time stamp: 2012-06-26 09.59.33

BR1088I Extending tablespace PSAPSR3…

4. Verify the extended tablespace on SAP

Use DB02 and then refresh to get oracle space statistics.

 

Then select OK to confirm the action.

Then Check the status thorugh the DB02 and database size (new size is 302.97 GB)

Then check the extended tablespace and added datafile (PSAPSR3). New datafile has been added as sr3.data19

5. Check using SQL command on OS

Check Using SQL on OS and select the datafiles of tablespaces.

Login to SQL from the user <oraSID>

Erppi1# su orapi1

erppi1:orapi1 54>

erppi1:orapi1 53> sqlplus / as sysdba

SQL>

SQL> select file_name from dba_data_files;

Then It will display datafiles till the last.

Eg : /oracle/PI1/sapdata4/sr3_18/sr3.data19