Tablespace

select df.tablespace_name "Tablespace",

totalusedspace "Used MB",

(df.totalspace - tu.totalusedspace) "Free MB",

df.totalspace "Total MB",

round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))

"Pct. Free"

from

(select tablespace_name,

round(sum(bytes) / 1048576) TotalSpace

from dba_data_files

group by tablespace_name) df,

(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name

from dba_segments

group by tablespace_name) tu

where df.tablespace_name = tu.tablespace_name ;

alter tablespace SOAPRD_SOAINFRA add datafile size 2G autoextend on maxsize 20G;

alter tablespace SOADE3_STB add datafile size 2G autoextend on maxsize 20G;

G-Invoicing - ECC

 

Installing Oracle Enterprise Command Center Framework, Release 12.2 (Latest Version) (Doc ID 2495053.1) Installing Oracle Enterprise Command Center Framework, Release 12.2 V12 (Doc ID 3017956.1)

G-Invoice:  G-Invoicing ECC Patch (Patch - 36861505, 36236796, 36393109, 35921608, 35921575, 36032737, 35921481, 35302496) - ECC Upgrade to V12

Sequence below

---------------------

36236796

36393109

35921608

35921575

36032737

35921481

35302496

36861505

 July CU ECC Patch - 36861505
 July CU Servicing Patch - 36866950

• grep -i ecc_ $CONTEXT_FILE
• 
  
• To start and Stop ECC
• /dRAM4ei/applmgr/Oracle/quickInstall/bin/startAllEcc.sh
• /dRAM4ei/applmgr/Oracle/quickInstall/bin/stopAllEcc.sh
• 
  
• more $ECC_BASE/Oracle/quickInstall/env/ecc.env|grep -i ebs
• 
  
• more $ECC_BASE/Oracle/quickInstall/env/ecc.env|grep -i ebs
• 
  
• ps -fu $LOGNAME
• 
  
• IT:
• 
  
• [oracle@lufRAMfebsdl613 app]$ sqlplus ecc/INT_3CCCLE_SIT3M@\"$ECC_DB_CONNECTION\"
• 
  
• $ pwd
• /dRAM4ei/applmgr/Oracle/software/zookeeper/bin
• $ ls -ltr
• total 31
• -rwx------. 1 apdRAM4ei aadRAM4ei 1385 Mar  1  2022 zkTxnLogToolkit.sh
• -rwx------. 1 apdRAM4ei aadRAM4ei 6773 Mar  1  2022 zkServer.sh
• -rwx------. 1 apdRAM4ei aadRAM4ei 2696 Mar  1  2022 zkEnv.sh
• -rwx------. 1 apdRAM4ei aadRAM4ei 1534 Mar  1  2022 zkCli.sh
• -rwx------. 1 apdRAM4ei aadRAM4ei 1937 Mar  1  2022 zkCleanup.sh
• -rwx------. 1 apdRAM4ei aadRAM4ei  232 Mar  1  2022 README.txta

Audit Queries

 Audit:

select aud.audit_user_name UPDATED_BY, aud.audit_timestamp UPDATE_DATE, 
    (select distinct user_concurrent_program_name from APPS.FND_CONCURRENT_PROGRAMS_VL where aud.concurrent_program_id = CONCURRENT_PROGRAM_ID and last_update_date > to_date('10/01/2021','MM/DD/YYYY')) as program_name,
    decode(aud.audit_transaction_type,'U','(~) Update','I','(+) Insertion','D','(-) Deletion') as audit_type,
    (select application_name from apps.fnd_application_vl where application_id = aud.application_id) as program_app,
    aud.concurrent_program_name, aud.executable_application_id, aud.executable_id, aud.request_set_flag,
    aud.enabled_flag, aud.run_alone_flag, aud.queue_control_flag, aud.argument_method_code, aud.queue_method_code, aud.execution_method_code, aud.execution_options, aud.output_print_style, aud.print_flag,
    aud.srs_flag, aud.required_style, aud.save_output_flag, aud.printer_name, aud.minimum_width, aud.minimum_length, aud.request_priority, aud.iprog_id, aud.restart, aud.nls_compliant, aud.output_file_type, aud.enable_trace,
    aud.cd_parameter, aud.increment_proc, aud.mls_executable_app_id, aud.mls_executable_id, aud.enable_time_statistics, aud.security_group_id, aud.activity_summarizer, aud.program_type
from apps.FND_CONCURRENT_PROGRAMS_A aud
where audit_timestamp >= to_date('10/01/2023','MM/DD/YYYY')
    and audit_user_name not in ('APPS','SYSADMIN','ANONYMOUS','APPSMGR','HHSOPERATOR','UPA_SOLUTION','ORACLE12.2.0','CONCURRENT MANAGER')
order by aud.audit_timestamp;

Add responsibilities

 ./addresp.sh GV3T_ZU_Q9KLSSD1 SEETHA  "Requesting Approver"

 more ./addresp.sh

APPSPWD=$1

username=$2

respname="$3"

if [[ -z ${username} ]]

then

  echo "Enter Username "

  read username

fi

if [[ -z ${respname} ]]

then

  echo "Enter responsibility like System Administrtor "

  read respname

fi

PDBSID=`echo "$ORACLE_SID" | cut -c 1-6`

HOST="`hostname`"

. $ORACLE_HOME/"$PDBSID"_"$HOST".env

sqlplus -s apps/$APPSPWD@$ORACLE_SID << EOF

set serveroutput on

declare

v_userid fnd_user.user_id%type;

v_respid fnd_responsibility_tl.responsibility_id%type;

v_applid fnd_responsibility_tl.application_id%type;

begin

select user_id

into v_userid

from fnd_user

where user_name='${username}';

dbms_output.put_line('userid for $username is ' || v_userid);

select application_id, responsibility_id

into v_applid, v_respid

from fnd_responsibility_tl

where responsibility_name like '${respname}';

dbms_output.put_line('Resp id, Application id  for ${respname}  are ' || v_respid ||','|| v_applid);

fnd_user_resp_groups_api.Upload_Assignment(v_userid,v_respid,v_applid,0,sysdate,sysdate+90,'Comments');

exception

when others then

  dbms_output.put_line(sqlerrm);

end;

/

EOF

Parallel Query Validation

 

select distinct  'alter system kill session ' || '''' || sid || ',' || serial# || ',@' || s.inst_id || ''';' alter_statement, s.SQL_ID, s.CLIENT_IDENTIFIER,cast(s.SQL_EXEC_START as TIMESTAMP), SQL_EXEC_START,SQL_TEXT FROM gv$session s, gv$sql q  where s.sql_id = q.sql_id  AND s.status = 'ACTIVE' AND s.username = 'BIS_RT' and lower(CLIENT_IDENTIFIER) like '%@%gov%';

SELECT s.inst_id, s.sid,
       s.serial#,
       s.username,
       s.client_identifier,
       s.status,
       s.machine,
       s.logon_time,
       ROUND((SYSDATE - s.logon_time) * 24 * 60, 2) AS minutes_running,
       'IHS - Document View Report' program,
       q.sql_id
FROM gv$session s
LEFT JOIN gv$sql q
ON s.sql_id = q.sql_id AND s.inst_id = q.inst_id
WHERE s.username is not null
AND s.status = 'ACTIVE'
AND s.username = 'BIS_RT'
AND s.program like '%nqsserver%'
AND q.sql_fulltext LIKE '%Document View Report%'
AND sql_fulltext LIKE '%+%Parallel%(%16%)%'
--AND s.logon_time < SYSDATE - 10/(24*60) -- More than 10 minutes
ORDER BY minutes_running DESC;

DOC IDs:

 DOC IDs:

Query To Get Enabled Trace/Log/Debug Profile Options (Doc ID 559618.1)

HIST

 SELECT   SNAP_ID ,

DBID ,

INSTANCE_NUMBER ,

SAMPLE_ID ,

to_char(SAMPLE_TIME,'DD-MON-YYYY HH24:MI:SS') ,

SESSION_ID ,

SESSION_SERIAL# ,

USER_ID ,

SQL_ID ,

SQL_CHILD_NUMBER ,

SQL_PLAN_HASH_VALUE ,

FORCE_MATCHING_SIGNATURE ,

SQL_OPCODE ,

PLSQL_ENTRY_OBJECT_ID ,

PLSQL_ENTRY_SUBPROGRAM_ID ,

PLSQL_OBJECT_ID ,

PLSQL_SUBPROGRAM_ID ,

SERVICE_HASH ,

SESSION_TYPE ,

SESSION_STATE ,

QC_SESSION_ID ,

QC_INSTANCE_ID ,

BLOCKING_SESSION ,

BLOCKING_SESSION_STATUS ,

BLOCKING_SESSION_SERIAL# ,

EVENT ,

EVENT_ID ,

SEQ# ,

P1TEXT ,

P1 ,

P2TEXT ,

P2 ,

P3TEXT ,

P3 ,

WAIT_CLASS ,

WAIT_CLASS_ID ,

WAIT_TIME ,

TIME_WAITED ,

CURRENT_OBJ# ,

CURRENT_FILE# ,

CURRENT_BLOCK# ,

PROGRAM ,

MODULE ,

ACTION ,

CLIENT_ID ,

FLAGS

 FROM  dba_hist_active_sess_history WHERE   sample_time BETWEEN TO_DATE('14-AUG-2024 16:00','DD-MON-YYYY HH24:MI')

AND  TO_DATE('14-AUG-2024 20:00','DD-MON-YYYY HH24:MI')

 and client_id='RAM';

SELECT A.REQUEST_ID, D.SID, D.SERIAL# , C.SPID

FROM APPS.FND_CONCURRENT_REQUESTS A,

APPS.FND_CONCURRENT_PROCESSES B,

V$PROCESS C,

V$SESSION D

WHERE A.CONTROLLING_MANAGER = B.CONCURRENT_PROCESS_ID

AND C.PID = B.ORACLE_PROCESS_ID

AND B.SESSION_ID=D.AUDSID

AND A.REQUEST_ID = 129634975

AND A.PHASE_CODE = 'R';

select session_id, session_serial#, module, sql_id, sql_plan_hash_value, count(*)*10 runtime_sec, min(sample_time), max(sample_time)

from dba_hist_active_sess_history

where 1=1

and (session_id, session_serial#) in ((129615845,10613))

group by session_id, session_serial#, sql_id, sql_plan_hash_value, module

order by count(*)*10 desc