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’;