Useful Oracle queries
for SAP Basis Adminsitrators
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
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
@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.
No comments:
Post a Comment