No question at this time
DBA Top 10
1 M. Cadot 9500
2 B. Vroman 7200
3 A. Kavsek 4600
4 P. Wisse 2000
5 J. PĂ©ran 1700
6 J. Schnackenberg 1200
7 T. Boles 800
8 D. Johnson 600
9 D. Dave 400
10 S. Spb 300
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48624
Total active users1393
Act. users last 24h2
Act. users last hour0
Registered user hits last week48
Registered user hits last month117
Go up

Query Help!
Next thread: UTL_SMTP
Prev thread: db growth on daily basis

Message Score Author Date
Hi Team, Need some help to figure out this requir...... S SPB Jun 27, 2021, 15:52
not sure which bundle you're looking for, but some...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts David Johnson Jun 27, 2021, 19:48
Hi David, Thanks for your time. I get below ...... S SPB Jun 28, 2021, 07:29
Hello SSP, -a- I advise you to format the dates...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts Bruno Vroman Jun 28, 2021, 11:10
then just change the inner query, remove the group...... David Johnson Jun 28, 2021, 15:11
Thanks Bruno Vroman, I m trying to figure out j...... S SPB Jun 28, 2021, 15:16
if you want only the latest data, and formatted ac...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts David Johnson Jun 28, 2021, 15:23

Follow up by mail Click here


Subject: Query Help!
Author: S SPB, India
Date: Jun 27, 2021, 15:52, 38 days ago
Os info: Any
Oracle info: 19c
Message: Hi Team,
Need some help to figure out this requirement.

How can I list the latest patch date with respect to PSU or RU from registry$history?

I need to get max action_time, comments which has comments like RU or PSU applied.

Or just wanted to know when was the recent security patch applied with the time stamp for any given database.

Thanks in advance,
SSP



Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Query Help!
Author: David Johnson, United States
Date: Jun 27, 2021, 19:48, 38 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: not sure which bundle you're looking for, but something like this may work:


select nvl( namespace , 'null' ) namespace
, nvl( bundle_series , 'null' ) bundle_series
, action_time
, comments
from sys.REGISTRY$HISTORY
where ( nvl(namespace,'null')
, nvl(bundle_series,'null')
, action_time
)
in
( select nvl(namespace,'null')
, nvl(bundle_series,'null')
, max(action_time )
from sys.registry$history
group by nvl(namespace,'null')
, nvl(bundle_series,'null')
)
order by 3 desc,1,2
;

Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Query Help!
Author: S SPB, India
Date: Jun 28, 2021, 07:29, 38 days ago
Message: Hi David,

Thanks for your time.

I get below result with multiple rows, But my goal is to find out only one row with the latest security patch application date and the patch information.


NAMESPACE BUNDLE_SER ACTION_TIME COMMENTS
---------- ---------- ------------------------------ ----------------------------------------
SERVER null 12-JUN-21 07.44.58.503253 AM Upgraded from 12.2.0.1.0 to 19.5.0.0.0
null null 20-JUN-20 11.01.20.763026 AM view invalidation
SERVER PSU 12-DEC-15 10.26.56.790736 AM PSU 11.2.0.4.4
SERVER CPU 06-OCT-13 12.38.51.346360 AM CPUJul2013

Thanks again,
SSP
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Query Help!
Author: Bruno Vroman, Belgium
Date: Jun 28, 2021, 11:10, 37 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: Hello SSP,

-a- I advise you to format the dates with "hour min sec"
(either SELECT TO_CHAR( ..., 'format' ), or use ALTER SESSION SET nls_date_format = '...')

-b- if you have a list of rows that you can order by time, it shouldn't be too difficule to keep only the most recent one... Search a bit the doc and or the web for examples.

-c- another query I use: (rem: for 19c we query "source_version, target_version", for 12.1 we query only "version")
SELECT TO_CHAR( r.action_time, 'DD-MON-YYYY HH24:MI:SS' ) m

, r.action a, r.status s, r.description d
, r.source_version v1
, r.target_version v2
, r.patch_id p
FROM sys.dba_registry_sqlpatch r
ORDER by r.action_time
HTH,

Bruno Vroman.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Query Help!
Author: David Johnson, United States
Date: Jun 28, 2021, 15:11, 37 days ago
Message: then just change the inner query, remove the group by clause and remove the grouping columns from the inner select, leaving only the max(action_time)
and from the where ... ( ,, action_time ) in clause


select nvl( namespace , 'null' ) namespace
, nvl( bundle_series , 'null' ) bundle_series
, action_time
, comments
from sys.REGISTRY$HISTORY
where action_time in
( select max(action_time )
from sys.registry$history
)
order by 3 desc,1,2
;

Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Query Help!
Author: S SPB, India
Date: Jun 28, 2021, 15:16, 37 days ago
Message: Thanks Bruno Vroman,

I m trying to figure out just by connecting to any random database in the network, if the latest security patch is applied based on just sysdate, if not when was it done last time.

registry$history has no meaningful information since it captures all kinds of APPLY/BOOTSTRAP/ROLLBACK etc type of actions and also the patch information is not unique across various versions and platforms.

Thanks,
SSP
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Query Help!
Author: David Johnson, United States
Date: Jun 28, 2021, 15:23, 37 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: if you want only the latest data, and formatted action_time try:


select to_char( action_time
,'YYYY.MM.DD..HH24.MI.SS') action_time
, nvl( namespace , 'null' ) namespace
, nvl( bundle_series , 'null' ) bundle_series
, comments
from sys.REGISTRY$HISTORY
where action_time in
( select max(action_time )
from sys.registry$history )
;

Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here