Validating Delta 15 Errors:

select * from ad_zd_logs where message_text like '%ORA-38805%';

select * from ad_zd_logs where type like '%ERROR%';

ORA-38805: edition is in use, SQL: drop edition "V_20240327_1705" cascade

Please run the script $AD_TOP/patch/115/sql/ADZDKILLOLDSESSIONS.sql to resolve ORA-38805: edition is in use issue

19C implementation - Could not drop edition V_20240327_1705ORA-38805: edition is in use

SELECT codelevel FROM AD_TRACKABLE_ENTITIES WHERE abbreviation in ('txk','ad');

SELECT ABBREVIATION,NAME,codelevel FROM AD_TRACKABLE_ENTITIES WHERE abbreviation in ('txk','ad');

 Quick Hits:

Concurrent Program Status:

SELECT distinct t.user_concurrent_program_name,

r.REQUEST_ID,u.USER_NAME,

to_char(r.ACTUAL_START_DATE,'dd-mm-yy hh24:mi:ss') "Started at",

to_char(r.ACTUAL_COMPLETION_DATE,'dd-mm-yy hh24:mi:ss') "Completed at",round(((nvl(v.actual_completion_date,sysdate)-v.actual_start_date)*24*60)) Etime,

decode(r.PHASE_CODE,'C','Completed','I','Inactive','P ','Pending','R','Running','NA') phasecode,

decode(r.STATUS_CODE, 'A','Waiting', 'B','Resuming', 'C','Normal', 'D','Cancelled', 'E','Error', 'F','Scheduled', 'G','Warning', 'H','On Hold', 'I','Normal', 'M',

'No Manager', 'Q','Standby', 'R','Normal', 'S','Suspended', 'T','Terminating', 'U','Disabled', 'W','Paused', 'X','Terminated', 'Z','Waiting') "Status",r.argument_text "Parameters"

FROM

apps.fnd_concurrent_requests r ,

apps.fnd_concurrent_programs p ,

apps.fnd_concurrent_programs_tl t,

apps.fnd_user u, apps.fnd_conc_req_summary_v v

WHERE 

r.CONCURRENT_PROGRAM_ID = p.CONCURRENT_PROGRAM_ID

AND r.actual_start_date >= (sysdate-1)

--AND r.requested_by=22378

AND   r.PROGRAM_APPLICATION_ID = p.APPLICATION_ID

AND t.concurrent_program_id=r.concurrent_program_id

AND r.REQUESTED_BY=u.user_id

AND v.request_id=r.request_id

--AND r.request_id ='2260046' in ('13829387','13850423')

and t.user_concurrent_program_name like '%Active%Users%'

order by to_char(r.ACTUAL_COMPLETION_DATE,'dd-mm-yy hh24:mi:ss')

 Pre Patch Analysis Output 

SELECT   a.bug_no,

          app.application_name,

           a.DIRECTORY,

           a.filename,

           a.old_version,

           a.new_version,

           DECODE (a.typeid,

                   'not applied',

                   'Unchanged File',

                   'upgrade',

                   'Changed File',

                   'new',

                   'New')

              change_type

    FROM   apps.fnd_imp_psmaster_vl a, apps.fnd_application_vl app

   WHERE   a.bug_no in ('35538202')

           AND app.application_short_name = a.app_short_name

ORDER BY   DECODE (a.typeid,

                   'not applied',

                   'Unchanged File',

                   'upgrade',

                   'Changed File',

                   'new',

                   'New') ASC;

output:

 How To Identify Sessions Which Cause PL/SQL LOCK TIMER Wait Event:

SELECT vs.sid,vs.sql_id, vs.inst_id,vs.osuser,vw.event,vw.p1,vw.p2,vw.p3 ,vt.sql_text , vs.program FROM gv$session_wait vw, gv$sqltext vt , gv$session vs

WHERE vw.event = 'PL/SQL lock timer'

AND vt.address=vs.sql_address

AND vs.inst_id = vw.inst_id

AND vs.sid = vw.sid;

 How to Determine the SQL_ID for a SQL Statement:

a. How to identify the SQL_ID of a statement:

SELECT /* TARGET SQL - Ram */ * FROM dual;

SELECT sql_id, plan_hash_value, substr(sql_text,1,40) sql_text  

FROM  gv$sql 

WHERE sql_text like 'SELECT /* TARGET SQL - Ram */%'

 

b. Find the SQL_ID in the V$SQL view using a substitution variable:

SELECT sql_id, plan_hash_value, SUBSTR(sql_text,1,40) Text

FROM gv$sql

WHERE sql_text LIKE '%TARGET SQL - Ram%';  -- An_Identifiable_String

 

c. If SQL is no longer available in gv$sql, you can find it in the AWR history views: DBA_HIST_SQLTEXT and DBA_HIST_SQLSTAT :

SELECT

    s.sql_id,

    s.plan_hash_value,

    t.sql_text,

    s.snap_id

FROM

    dba_hist_sqlstat   s,

    dba_hist_sqltext   t

WHERE s.dbid = t.dbid

AND   s.sql_id = t.sql_id

AND   sql_text LIKE '%Ram%'

ORDER BY

    s.sql_id;

d. How to Determine the SQL_ID of a SQL Statement in a PL/SQL Block:

select sql_id, sql_text from gv$sql where sql_text like '%declare%fnd_concurrent.wait_for_request%';

EBS System Date Change - 19c (CDB/PDB)

EBS System Date Change  - 19c (CDB/PDB):

1. MT level -  CM logs backed up with CM down, then using 'mv' for the large directories.

2. DB level - turned on flashback, created restore point for the PDB.

3. Changed fixed_date at PDB level

ALTER SYSTEM SET fixed_date='2022-10-31:10:10:10' SCOPE=BOTH SID='*' PDB='SRN5I';

----

ALTER SYSTEM SET fixed_date='2022-10-31:10:10:10' SCOPE=BOTH SID='*';

ALTER SYSTEM SET fixed_date='2022-10-31:10:10:10' SCOPE=BOTH SID='*' PDB='SRN5I';

ALTER SYSTEM SET fixed_date='NONE' SCOPE=BOTH SID='*';

SQL> show con_name;

CON_NAME

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

CDB$ROOT

SQL> alter system set fixed_date =none scope=both sid='*';

System altered.

NAME                                 TYPE        VALUE

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

fixed_date                           string      NONE

SQL> select sysdate from dual;

SYSDATE

---------

16-JUN-22

 alter session set container=SRN5I;

select sysdate from dual;

SYSDATE

---------

31-OCT-22

Vagrant

C:\Users\njohi>vagrant box list

C:\Users\njohi\vagrant>mkdir project1

C:\Users\njohi\vagrant>cd project1

C:\Users\njohi\vagrant\project1>ls -ltr

'ls' is not recognized as an internal or external command,

operable program or batch file.

C:\Users\njohi\vagrant\project1>dir

 Volume in drive C is OS

 Volume Serial Number is F21F-70F2

 Directory of C:\Users\njohi\vagrant\project1

08/14/2018  10:09 AM    <DIR>          .

08/14/2018  10:09 AM    <DIR>          ..

               0 File(s)              0 bytes

               2 Dir(s)  270,020,452,352 bytes free

C:\Users\njohi\vagrant\project1>vagrant init centos/7

A `Vagrantfile` has been placed in this directory. You are now

ready to `vagrant up` your first virtual environment! Please read

the comments in the Vagrantfile as well as documentation on

`vagrantup.com` for more information on using Vagrant.

C:\Users\njohi\vagrant\project1>dir

 Volume in drive C is OS

 Volume Serial Number is F21F-70F2

 Directory of C:\Users\njohi\vagrant\project1

08/14/2018  10:11 AM    <DIR>          .

08/14/2018  10:11 AM    <DIR>          ..

08/14/2018  10:11 AM             3,085 Vagrantfile

               1 File(s)          3,085 bytes

               2 Dir(s)  269,974,601,728 bytes free

C:\Users\njohi\vagrant\project1>vagrant up

Bringing machine 'default' up with 'virtualbox' provider...

==> default: Box 'centos/7' could not be found. Attempting to find and install...

    default: Box Provider: virtualbox

    default: Box Version: >= 0

==> default: Loading metadata for box 'centos/7'

    default: URL: https://vagrantcloud.com/centos/7

==> default: Adding box 'centos/7' (v1804.02) for provider: virtualbox

    default: Downloading: https://vagrantcloud.com/centos/boxes/7/versions/1804.02/providers/virtualbox.box

    default: Download redirected to host: cloud.centos.org

    default:

==> default: Successfully added box 'centos/7' (v1804.02) for 'virtualbox'!

==> default: Importing base box 'centos/7'...

==> default: Matching MAC address for NAT networking...

==> default: Checking if box 'centos/7' is up to date...

==> default: Setting the name of the VM: project1_default_1534267088297_30073

==> default: Clearing any previously set network interfaces...

==> default: Preparing network interfaces based on configuration...

    default: Adapter 1: nat

==> default: Forwarding ports...

    default: 22 (guest) => 2222 (host) (adapter 1)

==> default: Booting VM...

==> default: Waiting for machine to boot. This may take a few minutes...

    default: SSH address: 127.0.0.1:2222

    default: SSH username: vagrant

    default: SSH auth method: private key

    default:

    default: Vagrant insecure key detected. Vagrant will automatically replace

    default: this with a newly generated keypair for better security.

    default:

    default: Inserting generated public key within guest...

    default: Removing insecure key from the guest if it's present...

    default: Key inserted! Disconnecting and reconnecting using new SSH key...

==> default: Machine booted and ready!

==> default: Checking for guest additions in VM...

    default: No guest additions were detected on the base box for this VM! Guest

    default: additions are required for forwarded ports, shared folders, host only

    default: networking, and more. If SSH fails on this machine, please install

    default: the guest additions and repackage the box to continue.

    default:

    default: This is not an error message; everything may continue to work properly,

    default: in which case you may ignore this message.

==> default: Rsyncing folder: /cygdrive/c/Users/njohi/vagrant/project1/ => /vagrant

C:\Users\njohi\vagrant\project1>vagrant ssh

Last login: Tue Aug 14 17:24:42 2018

[vagrant@localhost ~]$ sudo su -

Last failed login: Tue Aug 14 17:21:52 UTC 2018 on tty1

There was 1 failed login attempt since the last successful login.

[root@localhost ~]# exit

logout

[vagrant@localhost ~]$ exit

logout

Connection to 127.0.0.1 closed.