Skip to Content

Introduction

The main goal of this document is serving as a base for SAP Basis Administrator who work with Oracle Databases and want to perform simple checks.


I will try to improve this document as much as I can.

Of course, do not hesitate to collaborate by adding more SQL queries.


Considerations

DBSID: SAP

SAPSID: SAP

System variables

SPOOL <filename>

SET LINESIZE 32767

SET WRAP OFF

SPOOL OFF

Check SQL*Plus User’s Guide and Reference.

Database

Show Oracle Database version

SELECT *

FROM v$version;

BANNER

——————————————————————————–

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

PL/SQL Release 11.2.0.3.0 – Production CORE    11.2.0.3.0      Production

TNS for IBM/AIX RISC System/6000: Version 11.2.0.3.0 – Production

NLSRTL Version 11.2.0.3.0 – Production

Show database details

SELECT NAME, LOG_MODE, OPEN_MODE, DATABASE_ROLE, PLATFORM_NAME

FROM  v$database;

NAME LOG_MODE     OPEN_MODE   DATABASE_ROLE PLATFORM_NAME

—- ———— ———– ————- ————————–

SAP  NOARCHIVELOG READ WRITE  PRIMARY       AIX-Based Systems (64-bit)


Show database size

SELECT SUM(BYTES)/1024/1024/1024 AS “DBSIZE(GB)”

FROM dba_data_files;

DBSIZE(GB)

———-

5312.59766


Show Oracle Instant Client Version

SELECT DISTINCT client_version

FROM v$session_connect_info

WHERE sid = sys_context(‘userenv’, ‘sid’);

CLIENT_VERSION

————–

11.2.0.3.0


For a list of all possibilities to check and identify Oracle Instant Client Version check this document.




Installed components in Database

SELECT comp_name, version, status

FROM dba_registry;



Instance

Show database instance details

SELECT INSTANCE_NAME, HOST_NAME, VERSION, STARTUP_TIME, STATUS, INSTANCE_ROLE

FROM v$instance;

INSTANCE_NAME HOST_NAME VERSION    STARTUP_TIME STATUS INSTANCE_ROLE  

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

SAP           SAPSERVER 11.2.0.3.0 26-MAR-15    OPEN   PRIMARY_INSTANCE

Monitor instance status of oracle

SELECT host_name, status

FROM v$instance;

DB Schema for SAP

SELECT OWNER

FROM DBA_TABLES WHERE TABLE_NAME = ‘T000’;

OWNER

—–

SAPSR3

Tablespaces

Show tablespaces details

SELECT TABLESPACE_NAME, STATUS, CONTENTS, SEGMENT_SPACE_MANAGEMENT

FROM dba_tablespaces;

TABLESPACE_NAME                STATUS    CONTENTS  SEGMEN

—————————— ——— ——— ——

SYSTEM                         ONLINE    PERMANENT MANUAL

PSAPUNDO                       ONLINE    UNDO      MANUAL

SYSAUX                         ONLINE    PERMANENT AUTO

PSAPTEMP                       ONLINE    TEMPORARY MANUAL

PSAPSR3                        ONLINE    PERMANENT AUTO

PSAPSR3USR                     ONLINE    PERMANENT AUTO

TOOLS                          ONLINE    PERMANENT AUTO

PSAPSR3731                     ONLINE    PERMANENT AUTO

List of datafiles for tablespace

SELECT file_name

FROM dba_data_files

WHERE tablespace_name='<TABLESPACE_NAME>’;

Find tables being used by tablespace

SELECT table_name

FROM dba_tables

WHERE tablespace_name=’PSAPSR37XX’;

Check autoextend

SELECT TABLESPACE_NAME,SEGMENT_SPACE_MANAGEMENT

FROM dba_tablespaces;

Check what is the retention period for PSAPUNDO tablespace

SELECT name “retention”,value/60 “minutes”

FROM v$parameter

WHERE name like ‘%undo_retention%’;

Users

Show database users

SELECT USERNAME, ACCOUNT_STATUS, AUTHENTICATION_TYPE

FROM dba_users;

USERNAME                       ACCOUNT_STATUS                   AUTHENTI

—————————— ——————————– ——–

SAPSR3                         OPEN                             PASSWORD

SYSTEM                         OPEN                             PASSWORD

SYS                            OPEN                             PASSWORD

OPS$SAPSERVICESAP              OPEN                             EXTERNAL

OPS$ORASAP                     OPEN                             EXTERNAL

To see all user who were locked by the system admin

SELECT bname

FROM <schema-name>.USR02

WHERE uflag=’64’ and mandt='<client-id>’;


Unlock user account

ALTER <USERNAME> ACCOUNT UNLOCK;


Create user

CREATE USER <USERNAME> IDENTIFIED BY <PASSWORD> [PROFILE <PROFILE_NAME>]


Delete SAP* user (or another user)

First we check if user exists. Then delete it.

SELECT MANDT, BNAME

FROM <DB_SCHEMA>.USR02

WHERE MANDT = ‘XXX’ AND BNAME = ‘SAP*’;



DELETE

FROM <DB_SCHEMA>.USR02

WHERE MANDT = ‘XXX’ AND BNAME = ‘SAP*’;

(change <DB_SCHEMA> and MANDT)

Password complexity

Execute script sap_utlpwdmg.sql from SAP Note 1522952 in SQL Plus:

@sap_utlpwdmg.sql

Activate complexity in profiles:

ALTER PROFILE DEFAULT LIMIT PASSWORD_VERIFY_FUNCTION verify_function_sap;

ALTER PROFILE SAPUPROF LIMIT PASSWORD_VERIFY_FUNCTION verify_function_sap;


Deativate complexity in profiles:

ALTER PROFILE DEFAULT LIMIT PASSWORD_VERIFY_FUNCTION NULL;

ALTER PROFILE SAPUPROF LIMIT PASSWORD_VERIFY_FUNCTION NULL;

Datafiles

Check datafiles

SELECT FILE#, STATUS, ENABLED

FROM  v$datafile;


     FILE# STATUS  ENABLED

———- ——- ———-

       375 ONLINE  READ WRITE

       376 ONLINE  READ WRITE

       377 ONLINE  READ WRITE

       378 ONLINE  READ WRITE

       379 ONLINE  READ WRITE

       380 ONLINE  READ WRITE

List of datafiles

SELECT FILE_NAME AS Datafile, BYTES/1024/1024 AS “Size(MB)”

FROM DBA_DATA_FILES

WHERE TABLESPACE_NAME LIKE ‘%<TABLESPACE_NAME>%’;

Datafile                                          Size(MB)

—————————————— —————

[…]

/oracle/SAP/sapdata9/sr3731_3/sr3731.data3           20000

/oracle/SAP/sapdata9/sr3731_4/sr3731.data4           20000

/oracle/SAP/sapdata9/sr3731_5/sr3731.data5           20000

/oracle/SAP/sapdata9/sr3731_6/sr3731.data6           20000

/oracle/SAP/sapdata9/sr3731_7/sr3731.data7            3500

/oracle/SAP/sapdata5/sr3_348/sr3.data348             31744

Resize datafile until size

ALTER DATABASE DATAFILE ‘<PATH_TO_DATAFILE>‘ RESIZE <SIZE>M;

Database altered.

Redologs

List of redologs groups and files belonging to each group

SELECT a.group#, a.member, b.bytes

FROM v$logfile a, v$log b

WHERE a.group# = b.group#;

    GROUP# MEMBER

———- ———————————–

        26 /oracle/SAP/origlogB/log_g26_m1.dbf

        26 /oracle/SAP/mirrlogB/log_g26_m2.dbf

        25 /oracle/SAP/origlogA/log_g25_m1.dbf

        25 /oracle/SAP/mirrlogA/log_g25_m2.dbf

        24 /oracle/SAP/origlogB/log_g24_m1.dbf

        24 /oracle/SAP/mirrlogB/log_g24_m2.dbf

        23 /oracle/SAP/origlogA/log_g23_m1.dbf

        23 /oracle/SAP/mirrlogA/log_g23_m2.dbf

        22 /oracle/SAP/origlogB/log_g22_m1.dbf

        22 /oracle/SAP/mirrlogB/log_g22_m2.dbf

        21 /oracle/SAP/origlogA/log_g21_m1.dbf

        21 /oracle/SAP/mirrlogA/log_g21_m2.dbf

Active Redolog groups

SELECT group#, status

FROM v$log;

    GROUP# STATUS

———- —————-

        21 INACTIVE

        22 INACTIVE

        23 CURRENT

        24 INACTIVE

        25 INACTIVE

        26 INACTIVE

Check the online redolog files details

set linesize 150

set pagesize 200

column member format a40

 

SELECT l.group#,f.member,l.archived,l.bytes/1078576 bytes,l.status,f.type

FROM v$log l, v$logfile f

WHERE l.group# = f.group#;

SAP Queries

system in upgrade, no import possible

UPDATE SAPSR3.uvers set PUTSTATUS=’+’;

COMMIT;

BRBACKUP error

When you are getting follwing error:

BR0051I BRBACKUP 6.40 (43)

BR0055I Start of database backup: bebchpaa.anf 2014-01-16 01.00.34

BR0484I BRBACKUP log file: /oracle/SAP/sapbackup/bebchpaa.anf

BR0071E BRBACKUP currently running or was killed

BR0072I Please delete file /oracle/SAP/sapbackup/.lock.brb if BRBACKUP was killed

BR0073E Setting of BRBACKUP lock failed

BR0056I End of database backup: bebchpaa.anf 2014-01-16 07.00.04

BR0280I BRBACKUP time stamp: 2009-07-26 07.00.05

BR0054I BRBACKUP terminated with errors

BR0280I BRBACKUP time stamp: 2009-07-26 07.00.05

BR0291I BRARCHIVE will be started with options ‘-U -jid ALLOG20090607070000 -d util_file -c force -p initSAP.sap -cds’

Connect to database:

sqlplus /nolog

SQL> connect /as sysdba

End backup:

ALTER DATABASE END BACKUP;

Then check if lock exists the lock file and delete it. If not found then run the backup again.

To report this post you need to login first.

10 Comments

You must be Logged on to comment or reply to a post.

  1. Mories S

    All major commands included including instant client version, db version etc.

    it will much better if this article includes sql command for oracle installed patch for hpux or windows.

    still, helpful article 🙂

    thanks

    (0) 
    1. Tomas Virseda Garcia Post author

      Hi Mories.

      Do not hesitate to contact me if you want to help me to improve this document. I am open to any kind of suggestions.

      You just have to send me a short text with the necessary SQL sentences (the output is optional) and, a short description. Also, you can suggest a category for this procedure.

      Then, your procedure will be included as soon as possible and your name will appear at the end of this document as collaborator 🙂

      Thanks and Regards

      (0) 
        1. Tomas Virseda Garcia Post author

          Job done.

          I spent more time working with the format of the SQL sentences than doing it. It’s horrible the online editor.

          Thanks for your contribution.

          (0) 
  2. Yves KERVADEC

    Hello Tomas,

    Your query “Is Database of type OLAP?” does only list an Oracle option that is required by SAP, it does not tell if the DB is an Olap or an Oltp one.

    740897 – Info about the scope of the Oracle license; Required Oracle options

    By the way the output for that query is wrong…

    To determine installed patch you could query dba_registry_history

    1696147 – How to determine the list of Oracle patches or SAP Bundle Patches (SBP) that are installed in SAP system

    1899453 – dba_registry_history does not record SBP installed

    Best regards

    (0) 

Leave a Reply