Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Useful Oracle queries for SAP Basis Adminsitrators

Private_Member_27907
Participant
‎03-26-2015 3:34 PM

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.

11 Comments
Labels in this area
Popular Blog Posts