Upload attachments to ORACLE SUPPORT TEAM
curl -v -T "awrrpt_2_29210_29212.html" -o output -u "zameerbasha.b@gmail.com" "https://transport.oracle.com/upload/issue/3-22054156781/"
Connecting using Database service:
sqlplus sys/'password'@kol-scan:61000/pdb_name.localdomain.com as sysdba
Deleting records from a table in chunks
begin
LOOP
DELETE FROM schema_name.table_name
WHERE column_name= 'value'
and ROWNUM <50000;
EXIT WHEN sql%ROWCOUNT = 0;
commit;
END LOOP;
COMMIT;
END;
/
LOOP
DELETE FROM schema_name.table_name
WHERE column_name= 'value'
and ROWNUM <50000;
EXIT WHEN sql%ROWCOUNT = 0;
commit;
END LOOP;
COMMIT;
END;
/
Execution of sql files in nohup mode:
nohup sqplus "/ as sysdba" @filename.sql &
nohup sqlplus username/'password'@tnsname as sysdba @filename.sql &
Long running session check:
SELECT
a.sql_fulltext,
a.sql_id,
b.last_call_et,
b.sid,
b.serial#,
b.username,
b.inst_id,
b.machine,
b.module,
b.client_identifier,
b.action,
b.osuser,
b.program,
b.event,
b.final_blocking_session,
b.status
FROM
gv$sql a,
gv$session b
WHERE
--b.client_identifier like '%test%' and
--b.machine='machinename.domain.com' and
a.sql_id = b.sql_id
AND b.osuser !='oracle'
AND b.username != 'SYS'
AND b.status= 'ACTIVE'
ORDER BY
b.last_call_et DESC;
Table Index check:
SELECT
aic.index_owner,
aic.table_name,
aic.index_name,
LISTAGG(aic.column_name, ',') WITHIN GROUP(
ORDER BY
aic.column_position
) cols
FROM
all_ind_columns aic
WHERE
aic.table_name = 'TABLE_NAME'
GROUP BY
aic.index_owner,
aic.table_name,
aic.index_name
ORDER BY
aic.index_owner,
aic.table_name;
Blocking Session Check:
SELECT
b.inst_id,
lpad('--->', DECODE(a.request, 0, 0, 5))
|| a.sid sid,
b.serial#,
b.sql_id,
b.prev_sql_id,
a.id1,
a.id2,
a.lmode,
a.block,
a.request,
DECODE(a.type, 'MR', 'Media Recovery', 'RT', 'Redo Thread', 'UN', 'User Name', 'TX', 'Transaction','TM', 'DML', 'UL', 'PL/SQL User Lock'
, 'DX', 'Distributed Xaction', 'CF', 'Control File', 'IS', 'Instance State', 'FS', 'File Set', 'IR', 'Instance Recovery', 'ST', 'Disk Space Transaction'
, 'TS', 'Temp Segment', 'IV', 'Library Cache Invalidation', 'LS', 'Log Start or Switch', 'RW', 'Row Wait', 'SQ', 'Sequence Number'
, 'TE', 'Extend Table', 'TT', 'Temp Table', a.type) lock_type,
b.program,
b.osuser,
b.username,
b.status,
b.module,
b.action,
b.logon_time,
b.last_call_et,
'alter system kill session '
|| ''''
|| a.sid
|| ', '
|| b.serial#
|| ''''
|| ' immediate;' kill_session,
DECODE(object_type, NULL, NULL, 'Dbms_Rowid.rowid_create(1, '
|| row_wait_obj#
|| ', '
|| row_wait_file#
|| ', '
|| row_wait_block#
|| ', '
|| row_wait_row#
|| ')') row_id
FROM
gv$lock a,
gv$session b,
dba_objects o
WHERE
( a.id1,
a.id2 ) IN (
SELECT
id1,
id2
FROM
gv$lock
WHERE
lmode = 0
)
AND a.inst_id = b.inst_id
AND a.sid = b.sid
AND o.object_id (+) = DECODE(b.row_wait_obj#, - 1, NULL, b.row_wait_obj#)
ORDER BY
a.id1,
a.id2,
a.request;
select a.inst_id, a.sid, a.serial#, a.process, a.logon_time, c.object_name
from gv$session a, gv$locked_object b, dba_objects c
where b.object_id = c.object_id
and a.sid = b.session_id
and OBJECT_NAME like '%LINK%' order by a.logon_time;
from gv$session a, gv$locked_object b, dba_objects c
where b.object_id = c.object_id
and a.sid = b.session_id
and OBJECT_NAME like '%LINK%' order by a.logon_time;
Gather Stats:
exec DBMS_STATS.GATHER_TABLE_STATS (ownname => 'schema_owner' ,tabname => 'table_name', cascade => true, estimate_percent => dbms_stats.auto_sample_size, degree => 15);
execute dbms_stats.gather_schema_stats(ownname => 'schema_owner',ESTIMATE_PERCENT =>dbms_stats.auto_sample_size,CASCADE => TRUE,degree => 15);
select 'alter system kill session '''|| s.sid|| ','|| s.serial#|| ''' immediate;' from gv$session S where status='INACTIVE';
Tablespace create from source to target :
select 'CREATE BIGFILE TABLESPACE ' || tablespace_name || ' DATAFILE ''+DATA1'' SIZE 200M AUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED;' from dba_tablespaces where tablespace_name not in ('SYSTEM','SYSAUX','TEMP');
FRA SPACE CHECK:
col name for a32
col size_m for 999,999,999
col used_m for 999,999,999
col pct_used for 999
SELECT name
, ceil( space_limit / 1024 / 1024) SIZE_M
, ceil( space_used / 1024 / 1024) USED_M
, decode( nvl( space_used, 0),
0, 0
, ceil ( ( space_used / space_limit) * 100) ) PCT_USED
FROM v$recovery_file_dest
ORDER BY name
/
Grep Command:
grep sga_max_size diag/rdbms/*/*/trace/alert* | sort | uniq
grep pga_aggregate_limit diag/rdbms/*/*/trace/alert* | sort | uniq
Undo Usage:
----------------
1. To check the current size of the Undo tablespace:
select sum(a.bytes) as undo_size from v$datafile a, v$tablespace b, dba_tablespaces c where c.contents = 'UNDO' and c.status = 'ONLINE' and b.name = c.tablespace_name and a.ts# = b.ts#;
2. To check the free space (unallocated) space within Undo tablespace:
select sum(bytes)/1024/1024 "mb" from dba_free_space where tablespace_name ='<undo tablespace name>';
3.To Check the space available within the allocated Undo tablespace:
select tablespace_name , sum(blocks)*8/(1024) reusable_space from dba_undo_extents where status='EXPIRED' group by tablespace_name;
4. To Check the space allocated in the Undo tablespace:
select tablespace_name , sum(blocks)*8/(1024) space_in_use from dba_undo_extents where status IN ('ACTIVE','UNEXPIRED') group by tablespace_name;
with free_sz as ( select tablespace_name, sum(f.bytes)/1048576/1024 free_gb from dba_free_space f group by tablespace_name ) , a as ( select tablespace_name , sum(case when status = 'EXPIRED' then blocks end)*8/1048576 reusable_space_gb , sum(case when status in ('ACTIVE', 'UNEXPIRED') then blocks end)*8/1048576 allocated_gb from dba_undo_extents where status in ('ACTIVE', 'EXPIRED', 'UNEXPIRED') group by tablespace_name ) , undo_sz as ( select tablespace_name, df.user_bytes/1048576/1024 user_sz_gb from dba_tablespaces ts join dba_data_files df using (tablespace_name) where ts.contents = 'UNDO' and ts.status = 'ONLINE' ) select tablespace_name, user_sz_gb, free_gb, reusable_space_gb, allocated_gb , free_gb + reusable_space_gb + allocated_gb total from undo_sz join free_sz using (tablespace_name) join a using (tablespace_name) ;
select name "FEATURE", first_usage_date "FROM", last_usage_date "TO"
from DBA_FEATURE_USAGE_STATISTICS
where name like '%OLAP%'
from DBA_FEATURE_USAGE_STATISTICS
where name like '%OLAP%'
select value from v$diag_info where name ='Diag Trace'; --> alert log location
column usr format a20 Heading 'Osuser/User'
column sid format 9999 heading 'S|I|D'
column program format A29 heading 'program'
column stat format A1 heading 'S|t|a|t'
column serial format 999999 heading 'Sr#'
column machine format A20 heading 'Machine'
column logical format A19 heading ' Logical| Gets / Chgs'
column module format A30 heading 'Module'
column sess_detail format A14 heading 'Sess_details'
set pagesize 300
col client_identifier for a30
select s.inst_id||':('||s.sid||','||s.serial#||')' sess_detail,s.sql_id,s.machine,s.osuser||' '||s.username usr,s.logon_time,s.program,s.event,s.status,s.client_identifier,s.MODULE
from gv$session s,gv$process p
where p.addr=s.paddr and s.sql_id='a9gvfh5hx9u98' ;
Trace File name finder:
column trace new_val T
select c.value || '/' || d.instance_name || '_ora_' ||
a.spid || '.trc' ||
case when e.value is not null then '_'||e.value end trace
from v$process a, v$session b, v$parameter c, v$instance d, v$parameter e
where a.addr = b.paddr
and b.audsid = userenv('sessionid')
and c.name = 'user_dump_dest'
and e.name = 'tracefile_identifier';
A query to discover the process ID (PID) associated with my dedicated server (the SPID from
V$PROCESS is the operating system PID of the process that was being used during the execution of that query).
select a.spid dedicated_server, b.process clientpid
from v$process a, v$session b
where a.addr = b.paddr
and b.sid = sys_context('userenv','sid');
Top N objects : largest object find query
with
seg as (
select
owner,segment_name
,segment_type
,tablespace_name
,sum(blocks) blocks
,sum(bytes) bytes
from dba_segments s
where segment_type not in (
'TYPE2 UNDO'
,'ROLLBACK'
,'SYSTEM STATISTICS'
)
and segment_name not like 'BIN$%' --not in recyclebin
and owner in ('NIMBUS')-- you can specify schema here
group by owner,segment_name,segment_type,tablespace_name
)
,segs as (
select
owner,segment_name
,case when segment_name like 'DR$%$%' then 'CTX INDEX' else segment_type end segment_type
,tablespace_name
,case
when segment_name like 'DR$%$%'
then (select table_owner||'.'||table_name from dba_indexes i where i.owner=s.owner and i.index_name = substr(segment_name,4,length(segment_name)-5))
when segment_type in ('TABLE','TABLE PARTITION','TABLE SUBPARTITION')
then owner||'.'||segment_name
when segment_type in ('INDEX','INDEX PARTITION','INDEX SUBPARTITION')
then (select i.table_owner||'.'||i.table_name from dba_indexes i where i.owner=s.owner and i.index_name=s.segment_name)
when segment_type in ('LOBSEGMENT','LOB PARTITION','LOB SUBPARTITION')
then (select l.owner||'.'||l.TABLE_NAME from dba_lobs l where l.segment_name = s.segment_name and l.owner = s.owner)
when segment_type = 'LOBINDEX'
then (select l.owner||'.'||l.TABLE_NAME from dba_lobs l where l.index_name = s.segment_name and l.owner = s.owner)
when segment_type = 'NESTED TABLE'
then (select nt.owner||'.'||nt.parent_table_name from dba_nested_tables nt where nt.owner=s.owner and nt.table_name=s.segment_name)
when segment_type = 'CLUSTER'
then (select min(owner||'.'||table_name) from dba_tables t where t.owner=s.owner and t.cluster_name=s.segment_name and rownum=1)
end table_name
,blocks
,bytes
from seg s
)
,so as (
select
segs.owner
,substr(segs.table_name,instr(segs.table_name,'.')+1) TABLE_NAME
,sum(segs.bytes)/1024/1024/1024 total_Size_GB
,sum(segs.blocks) total_blocks
,sum(case when segs.segment_type in ('TABLE','TABLE PARTITION','TABLE SUBPARTITION','NESTED TABLE','CLUSTER') then segs.bytes/1024/1024/1024 end) tab_size_GB
,sum(case when segs.segment_type in ('INDEX','INDEX PARTITION','INDEX SUBPARTITION','CTX INDEX') then segs.bytes/1024/1024/1024 end) ind_size_GB
,sum(case when segs.segment_type in ('CTX INDEX') then segs.bytes end) ctx_size
,sum(case when segs.segment_type in ('LOBSEGMENT','LOBINDEX','LOB PARTITION','LOB SUBPARTITION') then segs.bytes/1024/1024/1024 end) lob_size_GB
from segs
group by owner,table_name
)
,tops as (
select
dense_rank()over (order by total_Size_GB desc) rnk
,so.*
from so
)
select *
from tops
where rnk<=20
Fragmented Table finder query;
select
table_name,round(((blocks*8)/1024/1024),2) "size (gb)" ,
round(((num_rows*avg_row_len/1024))/1024/1024,2) "actual_data (gb)",
round((((blocks*8)) - ((num_rows*avg_row_len/1024)))/1024/1024,2) "wasted_space (gb)",
round(((((blocks*8)-(num_rows*avg_row_len/1024))/(blocks*8))*100 -10),2) "reclaimable space %",
partitioned
from
dba_tables
where
(round((blocks*8),2) > round((num_rows*avg_row_len/1024),2))
order by 4 desc;
Parent child table relationship finding query
with list_of_pks as (
select owner, table_name, constraint_name as pk_constraint_name
from all_constraints
where constraint_type in( 'P', 'U')
), pcc_tables( owner, table_name, parent_table, pk_constraint_name ) as (
select owner, table_name, null, pk_constraint_name
from list_of_pks
where owner = 'NIMBUS' and table_name in ('SUBSCRIPTION' , 'IADINFO','SUBSCRIPTION_SERVICEINFO','SERVICEORDER','SOVERSION') -- replace these values here
union all
select a.owner, a.table_name, c.table_name, a.pk_constraint_name
from list_of_pks a
join all_constraints b on a.table_name=b.table_name and a.owner=b.owner
join pcc_tables c on b.r_owner= c.owner and b.r_constraint_name=c.pk_constraint_name
)
select *
from pcc_tables;
++++++++++++++++++++++++++++
Schema Objects drop:
++++++++++++++++++++++++++++
select count(1),username,osuser,machine from gv$session where username='PCBHUSER' group by username,osuser,machine;
alter session set current_schema=PCBHUSER;
----------------------------------------------
create or replace procedure PCBHUSER.DB_DROP as
--code to drop all objects in a schema
--please confirm and recheck the schema name and TNS details
BEGIN
begin
execute immediate 'ALTER SESSION FORCE PARALLEL DDL';
execute immediate 'ALTER SESSION FORCE PARALLEL DML';
end;
begin
execute immediate 'purge recyclebin';
end;
begin
FOR I IN (select object_type,object_name from user_objects where object_type='TABLE')
LOOP
BEGIN
EXECUTE IMMEDIATE 'DROP '||I.OBJECT_TYPE||' '||I.OBJECT_NAME||' CASCADE CONSTRAINTS PURGE';
EXCEPTION WHEN OTHERS THEN NULL;
END;
END LOOP;
END;
BEGIN
FOR J IN (select object_type,object_name from user_objects where object_type!='TABLE' and object_name not in ('DB_DROP'))
LOOP
BEGIN
EXECUTE IMMEDIATE 'DROP '||J.OBJECT_TYPE||' '||J.OBJECT_NAME||'';
EXCEPTION WHEN OTHERS THEN NULL;
END;
END LOOP;
END;
begin
execute immediate 'purge recyclebin';
execute immediate 'DELETE FROM user_sdo_geom_metadata';
----execute immediate 'DELETE FROM mdsys.SDO_GEOM_METADATA_TABLE;';
execute immediate 'commit';
execute immediate 'purge recyclebin';
end;
END;
/
execute PCBHUSER.DB_DROP;
DELETE FROM mdsys.SDO_GEOM_METADATA_TABLE;
commit;
select count(1) from dba_objects where owner='PCBHUSER';
+++++++++++++++++++++++++++++++
Silent DB creation commands:
dbca -silent -createTemplateFromDB -sourceDB sim1 -templateName sim1_db_from_osdsimdb1_template.dbt -sysDBAUserName sys -sysDBAPassword cowboy
[oracle@localhost u01]$ find . -type f -name 'sim1_db_from_osdsimdb1_template*'
.
dbca -silent -createDatabase -templateName sim1_db_from_osdsimdb1_template.dbt -gdbname sim3 -sid sim3 -sysPassword cowboy -systemPassword cowboy
set lines 150 pages 150
col HOST_NAME for a15
col NAME for a10
col INSTANCE_NAME for a10
col LOG_MODE for a12
col DATABASE_ROLE for a18
col OPEN_MODE for a20
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
select name,db_unique_name,open_mode,log_mode,database_role from gv$database;
select instance_name,status,host_name,startup_time,logins from gv$instance;
select distinct db_unique_name DB_Name,instance_name,open_mode,log_mode,logins,host_name,startup_time,database_role from gv$database,gv$instance;
SELECT host_name,instance_name,TO_CHAR(startup_time, 'DD-MM-YYYY HH24:MI:SS') startup_time,FLOOR(sysdate-startup_time) days FROM sys.v_$instance;
Patch details Check:
=============================
SET LINESIZE 400
COLUMN action_time FORMAT A20
COLUMN action FORMAT A10
COLUMN status FORMAT A10
COLUMN description FORMAT A40
COLUMN version FORMAT A10
COLUMN bundle_series FORMAT A10
SELECT TO_CHAR(action_time, 'DD-MON-YYYY HH24:MI:SS') AS action_time,
action,
status,
description,
version,
patch_id,
bundle_series
FROM dba_registry_sqlpatch
ORDER by action_time;
set lines 400;
col action_time for a30;
col description for a80;
col action for a10;
select patch_id, action, description, action_time from dba_registry_sqlpatch where to_char(action_time,'DD/MM/YYYY')=to_char(sysdate,'DD/MM/YYYY') order by action_time;
col action_time for a30;
col description for a80;
col action for a10;
select patch_id, action, description, action_time from dba_registry_sqlpatch where to_char(action_time,'DD/MM/YYYY')=to_char(sysdate,'DD/MM/YYYY') order by action_time;
select name, ISSYS_MODIFIABLE from v$parameter where name='_external_scn_rejection_delta_threshold_minutes';
Also make a note to use the below steps to login to root on non prod env if have access to root to perform during patching.
Step-1: sudo to oragrid
Step-2: sudo /usr/localcw/bin/eksh -l
==To Check User equivalence oragrid@ipagt1d8(1334) +ASM4 $ cluvfy comp admprv -n all -o user_equiv -verbose
user equivalence below generates trace also.
mkdir /tmp/cvutrace
export CV_TRACELOC=/tmp/cvutrace
export SRVM_TRACE=true
export SRVM_TRACE_LEVEL=2
cluvfy comp admprv -n all -o user_equiv -verbose
Below output looks good. But in the logs we can see for below command it gave SSH error:
Output: '<CV_TRC>/usr/bin/ssh -o FallBackToRsh=no -o PasswordAuthentication=no -o StrictHostKeyChecking=yes -o NumberOfPasswordPrompts=0 ipagt1d5 -n /bin/true >> /tmp/CVU_19_t1cnp1d1_2024-12-31_04-11-37_127593/scratch/exout36842.out
Please run below command from all the three nodes & provide the output:
1.From node ipagt1d7:
/usr/bin/ssh -o FallBackToRsh=no -o PasswordAuthentication=no -o StrictHostKeyChecking=yes -o NumberOfPasswordPrompts=0 ipagt1d5 -n /bin/true
/usr/bin/ssh -o FallBackToRsh=no -o PasswordAuthentication=no -o StrictHostKeyChecking=yes -o NumberOfPasswordPrompts=0 ipagt1d8 -n /bin/true
2. From node ipagt1d5:
/usr/bin/ssh -o FallBackToRsh=no -o PasswordAuthentication=no -o StrictHostKeyChecking=yes -o NumberOfPasswordPrompts=0 ipagt1d7 -n /bin/true
/usr/bin/ssh -o FallBackToRsh=no -o PasswordAuthentication=no -o StrictHostKeyChecking=yes -o NumberOfPasswordPrompts=0 ipagt1d8 -n /bin/true
3. From node ipagt1d8:
/usr/bin/ssh -o FallBackToRsh=no -o PasswordAuthentication=no -o StrictHostKeyChecking=yes -o NumberOfPasswordPrompts=0 ipagt1d5 -n /bin/true
/usr/bin/ssh -o FallBackToRsh=no -o PasswordAuthentication=no -o StrictHostKeyChecking=yes -o NumberOfPasswordPrompts=0 ipagt1d7 -n /bin/true ===SRVCTL command for older versions =================
mkdir /tmp/cvutrace
export CV_TRACELOC=/tmp/cvutrace
export SRVM_TRACE=true
export SRVM_TRACE_LEVEL=2
cluvfy comp admprv -n all -o user_equiv -verbose
Below output looks good. But in the logs we can see for below command it gave SSH error:
Output: '<CV_TRC>/usr/bin/ssh -o FallBackToRsh=no -o PasswordAuthentication=no -o StrictHostKeyChecking=yes -o NumberOfPasswordPrompts=0 ipagt1d5 -n /bin/true >> /tmp/CVU_19_t1cnp1d1_2024-12-31_04-11-37_127593/scratch/exout36842.out
Please run below command from all the three nodes & provide the output:
1.From node ipagt1d7:
/usr/bin/ssh -o FallBackToRsh=no -o PasswordAuthentication=no -o StrictHostKeyChecking=yes -o NumberOfPasswordPrompts=0 ipagt1d5 -n /bin/true
/usr/bin/ssh -o FallBackToRsh=no -o PasswordAuthentication=no -o StrictHostKeyChecking=yes -o NumberOfPasswordPrompts=0 ipagt1d8 -n /bin/true
2. From node ipagt1d5:
/usr/bin/ssh -o FallBackToRsh=no -o PasswordAuthentication=no -o StrictHostKeyChecking=yes -o NumberOfPasswordPrompts=0 ipagt1d7 -n /bin/true
/usr/bin/ssh -o FallBackToRsh=no -o PasswordAuthentication=no -o StrictHostKeyChecking=yes -o NumberOfPasswordPrompts=0 ipagt1d8 -n /bin/true
3. From node ipagt1d8:
/usr/bin/ssh -o FallBackToRsh=no -o PasswordAuthentication=no -o StrictHostKeyChecking=yes -o NumberOfPasswordPrompts=0 ipagt1d5 -n /bin/true
/usr/bin/ssh -o FallBackToRsh=no -o PasswordAuthentication=no -o StrictHostKeyChecking=yes -o NumberOfPasswordPrompts=0 ipagt1d7 -n /bin/true ===SRVCTL command for older versions =================
find / -name jenkins.war 2>/dev/null
No comments:
Post a Comment