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.
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.
https://blogs.sap.com/2015/03/26/useful-oracle-queries-for-sap-basis-adminsitrators/