Oracle Application Express: -
https://docs.oracle.com/database/apex-5.1/HTMIG/overview.htm#HTMIG363
-----------------------------------------------------------------------------------------------------------------------------
Query to set parameters: -
alter system set compatible='19.0.0' scope=spfile;
alter system set sec_case_sensitive_logon=TRUE scope=both;
alter system reset sec_case_sensitive_logon scope=spfile;
tdpoconf showenv -tdpo_optfile=tdpo.opt
tdpoconf password -tdpo_optfile=tdpo.opt
==========================================================================
select SID, SID_CATEGORY from oracle_adm.sid_v4 where appl_grp not in ('SAP','DECOM','EXTERNAL') and sid like '%\_S%' escape '\';
==========================================================================
If agent goes down multiple time after restarting it again & again, please try below OS cmd :-
<AGENT_INST_HOME>/bin/emctl setproperty agent -allow_new -name CollectionResults.MaximumRowsFloodControlMax -value 80000
<AGENT_INST_HOME>/bin/emctl setproperty agent -allow_new -name CollectionResults.MaximumRowsFloodControlMin -value 25000
==========================================================================
ALTER SYSTEM KILL SESSION 'SID, SERIAL#, @INSTANCE_ID' [IMMEDIATE];
---> V$SESSION used for non RAC databases to find sid, serial#
---> GV$SESSION used for RAC databases to find sid, serial#, inst_id
---> non-RAC databases
SELECT s.sid, s.serial# FROM v$session s WHERE username = 'SCOTT';
---> RAC databases
SELECT s.inst_id, s.sid, s.serial# FROM gv$session s WHERE username = 'SCOTT';
---> non rac kill example
ALTER SYSTEM KILL SESSION '123,34216';
ALTER SYSTEM KILL SESSION '123,34216' IMMEDIATE;
Use immediate to avoid hung: -
--> RAC kill example
ALTER SYSTEM KILL SESSION '123,34216,@2';
ALTER SYSTEM KILL SESSION '123,34216,@1' IMMEDIATE;
Kill session from os level: -
For windows -
c:\orakill ORACLE_SID spid
For unix -
kill -9 spid
ps -ef | grep spid
=========================================================================
RMAN> LIST RESTORE POINT ALL;
linux command :-
mv /dbfra/oracle/fra/U241CP/archivelog/2021_10_19/* /db/ORACLE/Archives_U241CP/2021_10_19
oracle@st-vdb12 1017%sqlplus system@T307HY
To delete files: -
ls -lt | grep 'Aug 4' | awk '{print $9}' | xargs rm -fr
q arch "/dbprog/oracle/admin/P900R3/exp/" -descr=P900R3-Exp.20220822*
ps -p <pid> -lF
pidof process_name
If archive save to 2 different location. ( check only log arc.dest_1 should set)
alter system set log_archive_dest_10='' scope=both;
delete archivelog until time 'sysdate-1';
RMAN> delete expired archivelog all;
osdbcomm all stop
osdbcomm all start
oslsnrctl start all
oslsnrctl stop all
oslsnrctl start all
chkPerform -sid T561F -server
chkconfig | grep dbora
drop tablespace tabplespace_name;
drop user user_name cascase;
alter tablespace TEMP add tempfile '/db/ORACLE/P830B/temp_02.dbf' size 100m autoextend on next 200m maxsize 10000m;
Check Resource Utilization :-
SELECT CURRENT_UTILIZATION,MAX_UTILIZATION,(CURRENT_UTILIZATION/400)*100 PERCENT_UTILIZATION from v$resource_limit WHERE RESOURCE_NAME='processes';
select name,dbid,open_mode,cdb,version,status from v$database,v$instance;
select sga_size,sga_size_factor,estd_db_time from v$sga_target_advice;
alter system set sga_target=8192M;
alter system set sga_max_size=8192M;
select sum(bytes)/1024/1024/1024 from dba_data_files WHERE TABLESPACE_NAME='SYSTEM';
TO check redo log switch per day: -
select trunc(completion_time) rundate,count(*) logswitch,round((sum(blocks*block_size)/1024/1024)) "REDO PER DAY (MB)" from v$archived_log group by trunc(completion_time) order by 1 desc;
------------------------------------------------------------------------------------------------------------------------
To see alerts using query: -
select ORIGINATING_TIMESTAMP, MESSAGE_TYPE,USER_ID, MESSAGE_TEXT from v$diag_alert_ext where message_type=2
==========================================================================
to check running jobs :-
--------------------------------------------------------------------
select * from v$session where status = 'ACTIVE';
to check export/backups renning details :-
--------------------------------------------------------------------
SELECT ROUND(sofar/totalwork*100,2) as PercentDone,
v$session_longops.*
FROM v$session_longops
WHERE sofar <> totalwork
ORDER BY target, sid;
select job_name, owner_name, state from dba_datapump_jobs;
------------------------------------------------------------------------
select sum(bytes)/1024/1024 from dba_data_files WHERE TABLESPACE_NAME='UNDO';
select sum(bytes)/1024/1024 from dba_data_files WHERE TABLESPACE_NAME='TEMP';
alter user SYSTEM account unlock;
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
select flashback_on from v$database;
select name,open_mode from v$database;
select sched_type, owner, owner_type, last_occurrence, event_status From QIPADMIN.sched_prof;
To check password status:-
col USERNAME for a40
col ACCOUNT_STATUS for a20
col EXPIRY_DATE for a50
col PROFILE for a30
set lines 180
set pages 1100
select USERNAME, ACCOUNT_STATUS, LOCK_DATE, EXPIRY_DATE, PROFILE from dba_users where USERNAME like 'DYNATRACE_%';
SQL> create user TEST identified by U3#PJYTVCENQZO5LR#56; temporary tablespace temp;
ALTER USER SYSTEM ACCOUNT UNLOCK;
alter user SYSTEM identified by W0_OBQGF3J#0ED7FN5VV;
select OWNER,OBJECT_NAME, OBJECT_TYPE from ALL_OBJECTS WHERE OBJECT_TYPE IN ('FUNCTION','PROCEDURE','PACKAGE');
col OWNER for a20
col OBJECT_NAME for a30
select OWNER,OBJECT_NAME, OBJECT_TYPE from ALL_OBJECTS WHERE OBJECT_TYPE IN ('FUNCTION');
to check failed logon attemp username :-
---------------------------------------------------------------------------
SET LINES 300
SET PAGES 999
COL INUM FOR 9999
COL OS_USERNAME FOR A10
COL USERNAME FOR A10
COL USERHOST FOR A25
COL ACTION_NAME FOR A10
COL OS_PROCESS FOR A10
COL TIMESTAMP FOR A20
SELECT INSTANCE_NUMBER INUM,
OS_USERNAME,
USERNAME,
USERHOST,
TO_CHAR(EXTENDED_TIMESTAMP,'DD-MON-YYYY HH24:MI:SS') TIMESTAMP,
ACTION_NAME,
OS_PROCESS,
RETURNCODE
FROM
DBA_AUDIT_SESSION
WHERE
EXTENDED_TIMESTAMP > (SYSDATE - 20) AND RETURNCODE > 0
ORDER BY EXTENDED_TIMESTAMP;
-----------------------------------------------------------------------------------------------------
SELECT
os_username,
username,
terminal,
to_char(timestamp,'MM-DD-YYYY HH24:MI:SS') as timestamp
FROM
DBA_AUDIT_TRAIL
where EXTENDED_TIMESTAMP > (SYSDATE - 1)
AND USERNAME NOT like '%DBSNMP'
order by EXTENDED_TIMESTAMP;
-------------------------------------------------------------------------------------------
col OS_USERNAME for a10
col USERNAME for a10
col USERHOST for a15
col TERMINAL for a18
col ACTION_NAME for a8
select
os_username, username, userhost,
terminal,
timestamp,
action_name,
logoff_time
from dba_audit_session;
-------------------------------------------------------------------------------------------------------
col USERNAME for a40
col ACCOUNT_STATUS for a20
col EXPIRY_DATE for a50
col PROFILE for a30
set lines 180
set pages 1100
select USERNAME, ACCOUNT_STATUS from dba_users where ACCOUNT_STATUS='OPEN'
------------------------------------------------------------------------------------------------------------
Archive log generation:
---------------------------------------------------------------------------
Set lines 900
column day format a10
column Switches_per_day format 9999
column 00 format 999
column 01 format 999
column 02 format 999
column 03 format 999
column 04 format 999
column 05 format 999
column 06 format 999
column 07 format 999
column 08 format 999
column 09 format 999
column 10 format 999
column 11 format 999
column 12 format 999
column 13 format 999
column 14 format 999
column 15 format 999
column 16 format 999
column 17 format 999
column 18 format 999
column 19 format 999
column 20 format 999
column 21 format 999
column 22 format 999
column 23 format 999
select to_char(first_time,'DD-MON') day,
sum(decode(to_char(first_time,'hh24'),'00',1,0)) "00",
sum(decode(to_char(first_time,'hh24'),'01',1,0)) "01",
sum(decode(to_char(first_time,'hh24'),'02',1,0)) "02",
sum(decode(to_char(first_time,'hh24'),'03',1,0)) "03",
sum(decode(to_char(first_time,'hh24'),'04',1,0)) "04",
sum(decode(to_char(first_time,'hh24'),'05',1,0)) "05",
sum(decode(to_char(first_time,'hh24'),'06',1,0)) "06",
sum(decode(to_char(first_time,'hh24'),'07',1,0)) "07",
sum(decode(to_char(first_time,'hh24'),'08',1,0)) "08",
sum(decode(to_char(first_time,'hh24'),'09',1,0)) "09",
sum(decode(to_char(first_time,'hh24'),'10',1,0)) "10",
sum(decode(to_char(first_time,'hh24'),'11',1,0)) "11",
sum(decode(to_char(first_time,'hh24'),'12',1,0)) "12",
sum(decode(to_char(first_time,'hh24'),'13',1,0)) "13",
sum(decode(to_char(first_time,'hh24'),'14',1,0)) "14",
sum(decode(to_char(first_time,'hh24'),'15',1,0)) "15",
sum(decode(to_char(first_time,'hh24'),'16',1,0)) "16",
sum(decode(to_char(first_time,'hh24'),'17',1,0)) "17",
sum(decode(to_char(first_time,'hh24'),'18',1,0)) "18",
sum(decode(to_char(first_time,'hh24'),'19',1,0)) "19",
sum(decode(to_char(first_time,'hh24'),'20',1,0)) "20",
sum(decode(to_char(first_time,'hh24'),'21',1,0)) "21",
sum(decode(to_char(first_time,'hh24'),'22',1,0)) "22",
sum(decode(to_char(first_time,'hh24'),'23',1,0)) "23",
count(to_char(first_time,'MM-DD')) Switches_per_day
from Gv$log_history
where trunc(first_time) between trunc(sysdate) - 30 and trunc(sysdate)
group by to_char(first_time,'DD-MON')
order by to_char(first_time,'DD-MON') ;
-----------------------------------------------------------------------------------------------------------------
to check what privs user have :-
select * from dba_tab_privs where grantee='USERNAME';
==========================================================================
col SID for a10
col NODE for a15
SELECT SID,NODE, BACKUP_START, BACKUP_COMPLETE, BACKUP_TYPE, BACKUP_STATUS, BACKUP_COMMENTS FROM oracle_adm.backup_info WHERE BACKUP_COMPLETE>=(SYSDATE-1) AND backup_status not in ('OK','ONDISK') AND sid IN (SELECT sid FROM oracle_adm.sid_v4 WHERE SID_CATEGORY = 'P' AND APPL_GRP not in ('DECOM','SAP','EXTERNAL'));
==========================================================================
If getting listener error after db copy in osmossad :-
----------------------------------------
to check services:-
--------------------------------------------------------------
set lines 200
set pages 200
col NAME for a15
col NETWORK_NAME for a20
col FAILOVER_TYPE for a20
col FAILOVER_METHOD for a20
col EDITION for a20
select NAME,CREATION_DATE,NETWORK_NAME,FAILOVER_TYPE,SERVICE_ID,GLOBAL_SERVICE,FAILOVER_METHOD,EDITION,MAX_LAG_TIME,GSM_FLAGS from dba_services;
-----------------------------------------------------------------------------------------------
exec dbms_service.DELETE_SERVICE('P025AZ')
exec dbms_service.STOP_SERVICE('P025AZ_MSS')
to create services name (tnsping for servicename)
----------------------------------------------------------------------
exec DBMS_SERVICE.CREATE_SERVICE(service_name => 'T025T_MSS',network_name => 'T025T_MSS', failover_method => 'BASIC',failover_type => 'SELECT',failover_retries => 180,failover_delay => 1);
to create trigger-
CREATE OR REPLACE TRIGGER manage_dgservice
after startup on database
DECLARE
role VARCHAR(30);
BEGIN
SELECT DATABASE_ROLE INTO role FROM V$DATABASE;
IF role = 'PRIMARY' THEN
DBMS_SERVICE.START_SERVICE('T025T_MSS');
END IF;
END;
to start service---
exec dbms_service.start_service('T025T_MSS');
ALTER DATABASE MOUNT STANDBY DATABASE;
alter database commit to switchover to physical standby with session shutdown;
==========================================================================
to check trigger on db :-
col TRIGGER_NAME for a20
col TABLE_OWNER for a20
select TRIGGER_NAME, TABLE_OWNER, STATUS from ALL_TRIGGERS;
SELECT OBJECT_NAME, OBJECT_TYPE, CREATED, LAST_DDL_TIME FROM USER_OBJECTS WHERE OBJECT_NAME = 'SAFRAN_LOGIN';
to check trigger ddl :-
set long 1000000 longc 32000 lin 32000 trims on hea off pages 0 newp none emb on tab off feed off echo off
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true)
select dbms_metadata.get_ddl('TRIGGER','TRIGGER_NAME','SYS') from dual;
==========================================================================
to check running MRP for standby database sync:-
select process, status, sequence#, block#, blocks from v$managed_standby;
select version from v$instance;
select * from v$backup;
FILE# STATUS CHANGE# TIME CON_ID
---------- ------------------ ---------- ------------------- ----------
1 NOT ACTIVE 865492854 11/02/2019 12:49:41 0
3 NOT ACTIVE 865492854 11/02/2019 12:49:41 0
alter database begin backup; /// to make v$backup status ACTIVE.
select name from v$datafile;
SQL> select name from v$database;
NAME
---------
P578E
SQL> select STARTUP_TIME from v$instance;
STARTUP_TIME
-------------------
06/10/2021 14:56:41
========================================================================
---> To check archive log gap in standby
SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#;
=================================================================
Select username,timestamp from sys.dba_audit_session where username='ISSUETRACKER';
Select username,timestamp from sys.dba_audit_session where ACTION_NAME='LOGON';
col DESCRIPTION for a70
col STATUS for a20
set lines 200
set pages 200
col ACTION_TIME for a30
select NAME, patch_id, patch_uid, status, ACTION_TIME, description from dba_registry_sqlpatch,v$database;
set lines 250
col USERNAME for a20
col OS_USERNAME for a12
col OWNER for a15
col USERHOST for a20
col ACTION_NAME for a20
select OWNER,OS_USERNAME,USERNAME,ACTION,ACTION_NAME,TIMESTAMP,RETURNCODE,USERHOST from dba_audit_trail where USERNAME='ISSUETRACKER' order by TIMESTAMP;
<HOME NAME="OraDB19Home9" LOC="/dbprog/oracle/product/19.3.0.0.17EL" TYPE="O" IDX="13"/>
set lines 250
col USERNAME for a20
col OS_USERNAME for a12
col OWNER for a15
col USERHOST for a20
col ACTION_NAME for a20
select OWNER,OS_USERNAME,USERNAME,ACTION,ACTION_NAME,TIMESTAMP,RETURNCODE,USERHOST from dba_audit_trail where USERNAME='ISSUETRACKER' order by TIMESTAMP;
==========================================================================
select count(1) from user_objects where status <> 'VALID';
=========================================================================
set head off
set pages 9999
set long 9999999
SELECT dbms_metadata.get_ddl('USER','P_SAPITGADM') FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','P_SAPITGADM') FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','P_SAPITGADM') FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','P_SAPITGADM') FROM DUAL;
set head on;
========================================================================
column object_name format a30
select object_name, object_type
from dba_objects
where object_name||object_type in
(select object_name||object_type
from dba_objects
where owner = 'SYS')
and owner = 'SYSTEM';
========================================================================
To Restart Database on RAC server,
oraexadb@st-dbadm144 1008%srvctl stop database -d P422
oraexadb@st-dbadm144 1009%srvctl start database -d P422
oraexadb@st-dbadm144 1010%srvctl status database -d P422
Instance P4221 is running on node st-dbadm144
Instance P4222 is running on node st-dbadm145
========================================================================
To check metadata of any invalid function objects:-
SQL> set long 9999
SQL> SELECT dbms_metadata.get_ddl('FUNCTION', 'STL_VERIFY_FUNC_SPEC_PRIV', 'SYS') FROM DUAL;
Example:
CREATE OR REPLACE NONEDITIONABLE FUNCTION "SYS"."STL_VERIFY_FUNC_SPEC_PRIV"
(username varchar2,
password varchar2,
old_password varchar2)
return boolean IS
differ integer;
begin
if not ora_complexity_check(password, chars => 20, upper => 1, lower => 0, di
git => 1, special => 1) then
return(false);
end if;
-- Check if the password differs from the previous password by at least
-- 4 characters
if old_password is not null then
differ := ora_string_distance(old_password, password);
if differ < 4 then
raise_application_error(-20032, 'Password should differ from previous '
|| 'password by at least 4 characters');
end if;
end if;
return(true);
end;
=========================================================================
oracle@st-vtdb29 1006%cat /etc/redhat-release
Red Hat Enterprise Linux Server release 7.9 (Maipo)
========================================================================
select profile, resource_name, limit from dba_profiles where RESOURCE_NAME = 'PASSWORD_VERIFY_FUNCTION';
=======================================================================
set pages 400
set lines 200
COLUMN tablespace_name format a21
COLUMN file_name format a35
COLUMN free% format a12
break ON tablespace_name SKIP 1
SELECT aus.tablespace_name, df.file_name, df.TOTAL_SPACE_MB, NVL(dfs.FREE_SPACE_MB,0) Free_space_MB,
NVL(TO_CHAR( (free_space_mb*100/total_space_mb),'09.00') ,0)"FREE%",
TRUNC(df.TOTAL_SPACE_MB-USED_SPACE_MB ) Reclaim_space_MB,
AUTOEXTENSIBLE, MAXBYTES_MB,INCREMENT_BY_MB
FROM (SELECT tablespace_name, file_id,SUM(bytes)/1024/1024 AS "FREE_SPACE_MB" FROM DBA_FREE_SPACE GROUP BY tablespace_name, file_id) dfs,
(SELECT file_id, file_name, SUM(bytes)/1024/1024 AS "TOTAL_SPACE_MB" FROM DBA_DATA_FILES GROUP BY file_id, file_name) df,
(SELECT DISTINCT f.TABLESPACE_NAME, file_name,((ROUND(f.bytes / 1024 / 1024) - NVL(ROUND(s.bytes / 1024 / 1024),0))) "USED_SPACE_MB",
f.AUTOEXTENSIBLE, TRUNC(f.MAXBYTES/1024/1024,2) MAXBYTES_MB, TRUNC(f.INCREMENT_BY/(1048576/(select value from v$parameter where name = 'db_block_size')),2) INCREMENT_BY_MB
FROM DBA_DATA_FILES f, DBA_FREE_SPACE s WHERE f.file_id = s.file_id (+)
AND NVL(s.block_id,0) IN (NVL((SELECT MAX(block_id) FROM DBA_FREE_SPACE WHERE file_id = s.file_id),0))) aus
WHERE dfs.FILE_ID (+)= df.FILE_ID
AND aus.file_name (+)= df.file_name
ORDER BY 1,2;
==========================================================================
col size_mb format 999,999,999
col used_mb format 999,999,999
col name format a32
col pct_used format 999
select name, ceil(space_limit / 1024 / 1024/1024)size_GB, ceil( space_used / 1024 / 1024/1024) used_GB, decode(nvl(space_used,0),0, 0, ceil (( space_used / space_limit) * 100) ) pct_used from v$recovery_file_dest order by name desc;
show parameter reco
to start all db in one time(linux level cmd)
-----------------------------------------------
osdbcomm all start
==========================================================================
set linesize 700
set pagesize 100
Select JOB_SEQ_NO,SID,FROM_SID,REQUESTED_START_TIME,STARTED, COMPLETED,STATUS from schedule_main.jobs order by REQUESTED_START_TIME;
--------------------------------------------------------------------------------------------------------------
col FROM_SID for a8
col TO_SID for a8
col JOB_TYPE for a10
set lines 200
set pages 200
select job_seq_no, job_type, from_sid, sid "TO_SID", requested_start_time, active, started, completed, status, comments from schedule_main.jobs where to_date(Requested_start_time) between '22-JAN-23' and '25-JAN-23' ORDER BY REQUESTED_START_TIME desc;
--------------------------------------------------------------------------------------------------------------------
update schedule_main.jobs set comments = 'Copy from DISP to T660K completed successfully by rakg at at 12/06-2022 08:34 ' where job_seq_no = 9276;
commit;
==========================================================================
To check failed logon attemps by a users :-
-----------------------------------------
SET LINES 300
SET PAGES 999
COL INUM FOR 9999
COL OS_USERNAME FOR A10
COL USERNAME FOR A10
COL USERHOST FOR A25
COL ACTION_NAME FOR A10
COL OS_PROCESS FOR A10
COL TIMESTAMP FOR A20
SELECT INSTANCE_NUMBER INUM,
OS_USERNAME,
USERNAME,
USERHOST,
TO_CHAR(EXTENDED_TIMESTAMP,'DD-MON-YYYY HH24:MI:SS') TIMESTAMP,
ACTION_NAME,
OS_PROCESS,
RETURNCODE
FROM
DBA_AUDIT_SESSION
WHERE
EXTENDED_TIMESTAMP > (SYSDATE - 1) AND RETURNCODE > 0
ORDER BY EXTENDED_TIMESTAMP;
SQL> select username,account_status,lock_date,expiry_date,profile from dba_users where username='GLA';
==========================================================================
Total count of database:-
--------------------------------------------
select count(*) from oracle_adm.sid_v4 where appl_grp not in ('SAP','DECOM','EXTERNAL') and sid not like '%EXA%';
Total standby database:-
------------------------------------------
select count(*) from oracle_adm.sid_v4 where appl_grp not in ('SAP','DECOM','EXTERNAL') and sid not like '%EXA%' and sid like '%\_S%' escape '\';
===================================
select * from "ORACLE_MONITOR"."DEFAULT_TBS_USAGE" where PCT_OF_MAX>75;
========================================
total count
select count(*) from oracle_adm.sid_v4 where appl_grp not in ('SAP','DECOM','EXTERNAL') and sid not like '%EXA%';
Standby count
select count(*) from oracle_adm.sid_v4 where appl_grp not in ('SAP','DECOM','EXTERNAL') and sid not like '%EXA%' and sid like '%\_S%' escape '\';
production count
select count(*) from oracle_adm.sid_v4 where appl_grp not in ('SAP','DECOM','EXTERNAL') and sid not like '%EXA%' and SID_CATEGORY='T';
Prod standby count
select count(*) from oracle_adm.sid_v4 where appl_grp not in ('SAP','DECOM','EXTERNAL') and sid not like '%EXA%' and SID_CATEGORY='P' and sid like '%\_S%' escape '\';
select count(*) from oracle_adm.sid_v4 where appl_grp not in ('SAP','DECOM','EXTERNAL') and sid not like '%EXA%' and SID_CATEGORY='T' and sid like '%\_S%' escape '\';
select count(*) from oracle_adm.sid_v4 where appl_grp not in ('SAP','DECOM','EXTERNAL') and sid not like '%EXA%' and SID_CATEGORY='X' and sid like '%\_S%' escape '\';
select count(*) from oracle_adm.sid_v4 where appl_grp not in ('SAP','DECOM','EXTERNAL') and sid not like '%EXA%' and SID_CATEGORY='U' and sid like '%\_S%' escape '\';
=====================================
SQL> sho parameter local
select nvl(pool,name) pool
,sum(bytes)/1024/1024 MB
from v$sgastat
group by nvl(pool,name)
;
=========================================================================
- dsmc archive -archmc=ARC-1Y "/dbbck/P335_RMANBKP_RITM2532299/*.bkp" -subdir=yes
- dsmc q ar "/dbbck/RMANBACKUP_NEW_P210B/*"
dsmc archive -archmc=ARC-5Y "/dbbck/P226_BCKUP_1204/*" -subdir=yes
dsmc q ar "/dbbck/P226_BCKUP_1204/*"
==========================================================================
update schedule_main.jobs set Active='N' where job_seq_no = 10085;
update schedule_main.jobs set Status='C' where job_seq_no = 10085;
commit;
=========================================================================
SID_LIST_LISTENER_1 =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /dbprog/oracle/product/19.3.0.0.15)
(ENVS="TNS_ADMIN=/dbprog/oracle/product/19.3.0.0.15/network/admin")
(SID_NAME = P742)
)
)
(SID_DESC =
(ORACLE_HOME = /dbprog/oracle/product/19.3.0.0.14)
(ENVS="TNS_ADMIN=/dbprog/oracle/product/19.3.0.0.13/network/admin")
(SID_NAME = SV4TSTA)
)
=========================================================================
ARC SDE configuration:-
SQL> select name from v$database;
NAME
---------
T195X
SQL> select sde.ST_AsText(SDE.ST_Geometry('POINT (10 10)', 0)) from dual;
SDE.ST_ASTEXT(SDE.ST_GEOMETRY('POINT(1010)',0))
--------------------------------------------------------------------------------
POINT ( 10.00000000 10.00000000)
=======================
Password Reset Activity:-
Pass verify function error
alter user system profile default;
col LIMIT for a10
col PROFILE for a10
select * from dba_profileS where profile='DEFAULT';
alter profile DEFAULT LIMIT PASSWORD_REUSE_MAX UNLIMITED;
alter profile DEFAULT LIMIT PASSWORD_REUSE_MAX 12;
STL_ADMIN_3MONTHS
alter user system profile STL_ADMIN_3MONTHS;
select username,profile,account_status from dba_users where username='SYSTEM';
alter user SYSTEM account unlock;
=========================================================================
col NODE for a25
col MOUNT for a10
col UPDATED for a30
set lines 400 pagesize 200
select node, round(total/1024/1024, 2) "total (in GB)", round(free /1024/1024, 2) "free (in GB)", percent, mount, updated from oracle_adm.db_filesystems where PERCENT > 75 AND ( mount like '%arch%' or mount like '%db%' or mount like '%prog%' or mount like '%log%' ) order by percent desc;
set lines 200
set pages 200
COLUMN OWNER for a30
COLUMN object_name FORMAT A30
SELECT owner,
object_type,
object_name,
status
FROM dba_objects
WHERE status = 'INVALID'
ORDER BY owner, object_type, object_name;
======================================================================================================
select count(s.status) INACTIVE_SESSIONS from gv$session s, v$process p where p.addr=s.paddr and s.status='INACTIVE';
SELECT CURRENT_UTILIZATION,MAX_UTILIZATION,(CURRENT_UTILIZATION/MAX_UTILIZATION)*100 as PERCENT_UTILIZATION from v$resource_limit WHERE RESOURCE_NAME='processes';
alter system kill session '575,11846';
System altered.
SYS at P343 >select SID,SERIAL#,USERNAME,USER#,STATUS from v$session;
SYS at P343 >select count(s.status) INACTIVE_SESSIONS from gv$session s, v$process p where p.addr=s.paddr and s.status='INACTIVE';
INACTIVE_SESSIONS
-----------------
339
SYS at P343 >
SYS at P343 >
SYS at P343 >select (CURRENT_UTILIZATION/MAX_UTILIZATION)*100 as process_percent from v$resource_limit where RESOURCE_NAME='processes';
PROCESS_PERCENT
---------------
98.75
-----------------------------------------------------------------------------------------------------------------------
How to check database home using sql command :-
SQL> var OH varchar2(200);
SQL> EXEC dbms_system.get_env('ORACLE_HOME', :OH) ;
PL/SQL procedure successfully completed.
SQL> PRINT OH
OH
--------------------------------------------------------------------------------
/dbprog/oracle/product/19.3.0.0.13E
SQL>
SQL>
SQL> select name from V$database;
NAME
---------
P482_AZ
=========================================================================
To check ora error from last 15 days :-
SET linesize 200 pagesize 200
col RECORD_ID FOR 9999999 head ID
col MESSAGE_TEXT FOR a120 head Message
SELECT record_id, to_char(originating_timestamp,'DD-MON-YYYY HH24:MI:SS') , message_text FROM X$DBGALERTEXT WHERE originating_timestamp > systimestamp-15 AND regexp_like(message_text, '(ORA-|error)') order by record_id;
=========================================================================
SQL> alter system set db_files=500 scope=spfile;
SQL> shutdown immediate;
SQL> startup;
Check current DB_FILES again.
SQL> show parameter db_files;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_files integer 500
=========================================================================
select user# from user$ where name='TRADEDB_STATOIL';
=========================================================================
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
select sysdate from dual;
SELECT TO_CHAR (SYSDATE, 'MM-DD-YYYY HH24:MI:SS') "CURRENT_DATE" FROM DUAL;
select username, profile, created,account_status,lock_date,expiry_date,to_char(last_login,'DD-MON-YYYY') as LAST_LOGIN,oracle_maintained
from dba_user where account_status='OPEN'
or to_date(to_char(lock_date,'DD-MON-YYYY'),'DD-MON-YYYY')>='01-JAN-2022'
or to_date(to_char(expiry_date,'DD-MON-YYYY'),'DD-MON-YYYY')>='01-JAN-2022'
or to_date(to_char(last_login,'DD-MON-YYYY'),'DD-MON-YYYY')>='01-JAN-2022' and account_status like '%LOCKED%')
order by oracle_maintained,username;
=========================================================================
telnet smtp_server 25
SELECT host, lower_port, upper_port, acl
FROM dba_network_acls
/
SELECT acl,
principal,
privilege,
is_grant,
TO_CHAR(start_date, 'DD-MON-YYYY HH24:MI') AS start_date,
TO_CHAR(end_date, 'DD-MON-YYYY') AS end_date
FROM dba_network_acl_privileges
/
select object_name,object_type,owner from dba_objects where object_name in('UTL_MAIL','UTL_SMTP');
[11/30/2021 11:13 PM] Ravindra Kumar Gupta
/sys/acls/SEND_MAIL.xml
SYS at U241CP >SELECT host, lower_port, upper_port, acl
FROM dba_network_acls
/ 2 3
HOST LOWER_PORT UPPER_PORT ACL
------------------------------ ---------- ---------- ----------------------------------------
mailhost.statoil.no 25 25 /sys/acls/SEND_MAIL.xml
stfo-lnsmtp.statoil.no 25 25 /sys/acls/SEND_MAIL.xml
localhost /sys/acls/oracle-sysman-ocm-Resolve-Acce
ss.xml
login.microsoftonline.com 443 443 /sys/acls/send_mail_access.xml
www-authproxy.statoil.net 80 80 /sys/acls/send_mail_access.xml
www-proxy.statoil.no 80 80 /sys/acls/send_mail_access.xml
* NETWORK_ACL_5C3A2F9462A4345EE0538212618F
2ABA
==========================================================================================================
ret /prog88/oracle/admin/P061/exp/* /db88/ORACLE/P061/ -desc="P061-Exp.20180223.1317"
========================================================================================================
[1/30 10:15 AM] Vivek Kumar Sharma
select OWNER_NAME,JOB_NAME,STATE,OPERATION from dba_datapump_jobs where STATE='EXECUTING';
[1/30 10:16 AM] Vivek Kumar Sharma
select * from dba_datapump_jobs;
========================================================================================================
select PROTECTION_MODE, PROTECTION_LEVEL from v$database;
PROTECTION_MODE PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM AVAILABILITY UNPROTECTED
========================================================================================================
select * from all_source where type = 'LIBRARY' and (lower(text) like ('%raster%') or lower(text) like ('%shape%'));
[12/29/2021 1:34 PM] Neha Sundriyal
mujhe P230 ke database ka size bta do
col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
, round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
from (select bytes
from v$datafile
union all
select bytes
from v$tempfile
union all
select bytes
from v$log) used
, (select sum(bytes) as p
from dba_free_space) free
group by free.p;
set pages 1000;
col comp_id for a12;
col comp_name for a35;
col version for a12;
col status for a12;
select comp_id,comp_name,version,status from dba_registry;
select * from dba_profiles;
===================================================================================
SQL> select DBTIMEZONE from v$database;
DBTIME
------
+02:00
SQL> select current_timestamp from v$database;
CURRENT_TIMESTAMP
---------------------------------------------------------------------------
07-SEP-22 07.04.30.019257 AM +00:00
ALTER DATABASE SET TIME_ZONE = '+00:00';
===========================================================================================
For Error :-
RMAN-08137: warning: archived log not deleted, needed for standby or upstream capture process
Please apply the below Fix :-
SQL> show parameter log_archive_dest_state_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2 string RESET
log_archive_dest_state_20 string enable
log_archive_dest_state_21 string enable
log_archive_dest_state_22 string enable
log_archive_dest_state_23 string enable
log_archive_dest_state_24 string enable
log_archive_dest_state_25 string enable
log_archive_dest_state_26 string enable
log_archive_dest_state_27 string enable
log_archive_dest_state_28 string enable
log_archive_dest_state_29 string enable
SQL> alter system set log_archive_dest_state_2=enable;
System altered.
==================================================================================================
redo entries:-
"select s.sid, n.name, s.value, sn.username, sn.program, sn.type, sn.module
from v$sesstat s
join v$statname n on n.statistic# = s.statistic#
join v$session sn on sn.sid = s.sid
where name like '%redo entries%'
order by value desc"
===============================================================================================
To check Backup database entry :-
tnsping P010RMAN
Login to server.
sqlplus rman/HplADKZfgjlqxyjz2A5H5QXHZ@P010RMAN
select db_key,dbid,name from rc_database where name='db';
===============================================================================================
To check database size and freee space.
col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
, round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
from (select bytes
from v$datafile
union all
select bytes
from v$tempfile
union all
select bytes
from v$log) used
, (select sum(bytes) as p
from dba_free_space) free
group by free.p
/
==========================================================================================================
telnet 10.73.16.25 10001 from st-vtdb19.st.statoil.no
--------------------------------------------------------------------------------------
oracle@st-vtdb19 1011%nc -zv 10.73.16.25 10001
Ncat: Version 7.50 ( https://nmap.org/ncat )
Ncat: Connected to 10.73.16.25:10001.
Ncat: 0 bytes sent, 0 bytes received in 0.02 seconds.
======================================================================================================
Huge trace generation in P010
Check sql_trace parameter should be FALSE.
any job is hanging like Rman Backup,RmanLog,Export Backup,sqlplus
================================================================================================
1. Stop the agent: emctl stop agent
2.<AgentInstanceHome>/bin/emctl setproperty agent -name "SSLCipherSuites" -value "SSL_RSA_WITH_AES_128_CBC_SHA:SSL_RSA_WITH_AES_256_CBC_SHA:TLS_RSA_WITH_AES_128_CBC_SHA256:TLS_RSA_WITH_AES_256_CBC_SHA256:TLS_RSA_WITH_AES_128_GCM_SHA256:TLS_RSA_WITH_AES_256_GCM_SHA384:TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA:TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA:TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384:TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256:TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384:TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA256:TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA:TLS_ECDHE_ECDSA_WITH_AES_256_CBC_SHA:TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA256:TLS_ECDHE_ECDSA_WITH_AES_256_CBC_SHA384:TLS_ECDHE_ECDSA_WITH_AES_128_GCM_SHA256:TLS_ECDHE_ECDSA_WITH_AES_256_GCM_SHA384"
3. Start the agent: <AgentInstanceHome>/bin/emctl start agent
4. Check the agent to OMS communication: <AgentInstanceHome>/bin/emctl pingOMS
===============================================================================================================
begin
dbms_network_acl_admin.assign_acl(acl=>'SPORT_HTTP_ACCESS.xml',host=>'api.gateway.equinor.com',lower_port=>80,upper_port=>80);
dbms_network_acl_admin.assign_acl(acl=>'SPORT_HTTP_ACCESS.xml',host=>'api.gateway.equinor.com',lower_port=>443,upper_port=>443);
dbms_network_acl_admin.assign_acl(acl=>'SPORT_HTTP_ACCESS.xml',host=>'login.microsoftonline.com',lower_port=>80,upper_port=>80);
dbms_network_acl_admin.assign_acl(acl=>'SPORT_HTTP_ACCESS.xml',host=>'login.microsoftonline.com',lower_port=>443,upper_port=>443);
end;
/
========================================================================================================================
set long 10000
spool get_ddl.txt
select dbms_metadata.get_ddl('PROCEDURE','KILL_SESSION','DB_TEAM') from dual;
select * from USER_ROLE_PRIVS where USERNAME=SDE_IT;
select * from USER_TAB_PRIVS where Grantee =SDE_IT;
select * from USER_SYS_PRIVS where USERNAME =SDE_IT;
set head off
set pages 9999
set long 9999999
SELECT dbms_metadata.get_ddl('USER','DB_TEAM') FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','DB_TEAM') FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','DB_TEAM') FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','DB_TEAM') FROM DUAL;
set head on;
=========================================================================
SGA advice :-
SQL> set linesize 750
SQL> select * from v$sga_target_advice order by sga_size;
SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS ESTD_BUFFER_CACHE_SIZE ESTD_SHARED_POOL_SIZE CON_ID
---------- --------------- ------------ ------------------- ------------------- ---------------------- --------------------- ----------
1536 .375 5605681 352.2484 83493277 256 348 0
2048 .5 16340 1.0268 42169950 512 612 0
2560 .625 16245 1.0208 36498087 1024 604 0
3072 .75 16216 1.019 34918517 1536 732 0
===================================================================================================================
check current client info. :-
---------------------------
SELECT DISTINCT s.client_version FROM v$session_connect_info s WHERE s.sid = SYS_CONTEXT('USERENV', 'SID');
=======================================================================================================================
To check ddl of db links. :-
---------------------------
Set long 1000
SELECT DBMS_METADATA.GET_DDL('DB_LINK',db.db_link,db.owner) from dba_db_links db;
==========================================================================================================================
Password history :-
select * from user_history$ where user# in (select user# from user$ where name='EPDS');
If pwd history corrupted, use below command to remove password history.
----------------------------------------------------------------------
delete from user_history$ where user# in (select user# from user$ where name='EPDS');
commit;
=======================================================================================================================
Add application id -
-------------------------------
INSERT into ORACLE_ADM.CUSTOMER VALUES ('appl_id','Appl_name');
Commit;
==========================================================================
SQL> show parameter sec_case
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon boolean TRUE
SQL>
SQL>
SQL>
SQL> select p.name,p.value from v$parameter p, v$spparameter s where s.name=p.name and p.isdeprecated='TRUE' and s.isspecified='TRUE';
NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
sec_case_sensitive_logon
TRUE
SQL>
SQL> alter system reset sec_case_sensitive_logon scope=spfile;
System altered.
SQL> select p.name,p.value from v$parameter p, v$spparameter s where s.name=p.name and p.isdeprecated='TRUE' and s.isspecified='TRUE';
no rows selected
========================================================================================================================================
ON P010
SQL> update oracle_adm.SID_APPLICATION_ID set APPLICATION_ID='8************' where sid='U230';
1 row updated.
SQL> commit;
