How to check Clock synchronization between cluster nodes in RAC
We can use below to check the clock/time synchronization between nodes in RAC cluster. The crs alert log will also point out thee synchronization issue between the nodes.
Script:
cd $GRID_HOME/bin
./cluvfy comp clocksync -n all
Output:
Verifying Clock Synchronization across the cluster nodes
Oracle Clusterware is installed on all nodes.
CTSS resource check passed
Query of CTSS for time offset passed
CTSS is in Observer state. Switching over to clock synchronization checks using NTP
Starting Clock synchronization checks using Network Time Protocol(NTP)...
NTP configuration file "/etc/ntp.conf" existence check passed
Liveness check passed for "ntpd"
Check for NTP daemon or service alive passed on all nodes
Check of common NTP Time Server passed
Clock time offset check passed
Clock synchronization check using Network Time Protocol(NTP) passed
Oracle Cluster Time Synchronization Services check passed
Verification of Clock Synchronization across the cluster nodes was successful.
Check whether ctss or ntp is running
crsctl check ctss
CRS-4700: The Cluster Time Synchronization Service is in Observer mode.
Observer means – Time sync between nodes are taken care by NTP
Active means – Time sync between nodes are taken care by CTSS
Script:
cd $GRID_HOME/bin
./cluvfy comp clocksync -n all
Output:
Verifying Clock Synchronization across the cluster nodes
Oracle Clusterware is installed on all nodes.
CTSS resource check passed
Query of CTSS for time offset passed
CTSS is in Observer state. Switching over to clock synchronization checks using NTP
Starting Clock synchronization checks using Network Time Protocol(NTP)...
NTP configuration file "/etc/ntp.conf" existence check passed
Liveness check passed for "ntpd"
Check for NTP daemon or service alive passed on all nodes
Check of common NTP Time Server passed
Clock time offset check passed
Clock synchronization check using Network Time Protocol(NTP) passed
Oracle Cluster Time Synchronization Services check passed
Verification of Clock Synchronization across the cluster nodes was successful.
Check whether ctss or ntp is running
crsctl check ctss
CRS-4700: The Cluster Time Synchronization Service is in Observer mode.
Observer means – Time sync between nodes are taken care by NTP
Active means – Time sync between nodes are taken care by CTSS
Query to find out the status of Interface Managers
We can use below query to check the status for interface managers.
SELECT x.process_type "Name",
DECODE(
(SELECT '1' FROM fnd_concurrent_requests cr,
fnd_concurrent_programs_vl cp,
fnd_application a WHERE cp.concurrent_program_id = cr.concurrent_program_id
AND cp.concurrent_program_name = x.process_name
AND cp.application_id = a.application_id
AND a.application_short_name = x.process_app_short_name
AND phase_code != 'C'
),'1','Active','Inactive') "Status",
x.worker_rows "Worker Rows",
x.timeout_hours "Timeout Hours",
x.timeout_minutes "Timeout Minutes",
x.process_hours "Process Interval Hours",
x.process_minutes "Process Interval Minutes",
x.process_seconds "Process Interval Seconds"
FROM
(SELECT mipc.process_code ,
mipc.process_status ,
mipc.process_interval ,
mipc.manager_priority ,
mipc.worker_priority ,
mipc.worker_rows ,
mipc.processing_timeout ,
mipc.process_name ,
mipc.process_app_short_name ,
a.meaning process_type ,
FLOOR(mipc.process_interval /3600) process_hours ,
FLOOR((mipc.process_interval - (FLOOR(mipc.process_interval/3600) * 3600))/60) process_minutes ,
(mipc.process_interval - (FLOOR(mipc.process_interval/3600) * 3600) - (FLOOR((mipc.process_interval - (FLOOR(mipc.process_interval/3600) * 3600))/60) * 60)) process_seconds ,
FLOOR(mipc.processing_timeout /3600) timeout_hours ,
FLOOR((mipc.processing_timeout - FLOOR(mipc.processing_timeout/3600) * 3600)/60) timeout_minutes
FROM mtl_interface_proc_controls mipc,
mfg_lookups a
WHERE a.lookup_type = 'PROCESS_TYPE'
AND a.lookup_code = mipc.process_code
) x
-- WHERE x.PROCESS_TYPE = 'Cost Manager' -- uncomment this to display only the cost manager
ORDER BY 1;
SELECT x.process_type "Name",
DECODE(
(SELECT '1' FROM fnd_concurrent_requests cr,
fnd_concurrent_programs_vl cp,
fnd_application a WHERE cp.concurrent_program_id = cr.concurrent_program_id
AND cp.concurrent_program_name = x.process_name
AND cp.application_id = a.application_id
AND a.application_short_name = x.process_app_short_name
AND phase_code != 'C'
),'1','Active','Inactive') "Status",
x.worker_rows "Worker Rows",
x.timeout_hours "Timeout Hours",
x.timeout_minutes "Timeout Minutes",
x.process_hours "Process Interval Hours",
x.process_minutes "Process Interval Minutes",
x.process_seconds "Process Interval Seconds"
FROM
(SELECT mipc.process_code ,
mipc.process_status ,
mipc.process_interval ,
mipc.manager_priority ,
mipc.worker_priority ,
mipc.worker_rows ,
mipc.processing_timeout ,
mipc.process_name ,
mipc.process_app_short_name ,
a.meaning process_type ,
FLOOR(mipc.process_interval /3600) process_hours ,
FLOOR((mipc.process_interval - (FLOOR(mipc.process_interval/3600) * 3600))/60) process_minutes ,
(mipc.process_interval - (FLOOR(mipc.process_interval/3600) * 3600) - (FLOOR((mipc.process_interval - (FLOOR(mipc.process_interval/3600) * 3600))/60) * 60)) process_seconds ,
FLOOR(mipc.processing_timeout /3600) timeout_hours ,
FLOOR((mipc.processing_timeout - FLOOR(mipc.processing_timeout/3600) * 3600)/60) timeout_minutes
FROM mtl_interface_proc_controls mipc,
mfg_lookups a
WHERE a.lookup_type = 'PROCESS_TYPE'
AND a.lookup_code = mipc.process_code
) x
-- WHERE x.PROCESS_TYPE = 'Cost Manager' -- uncomment this to display only the cost manager
ORDER BY 1;
ORA-04023: Object could not be validated or authorized
While running any queries, or after creating any view,procedures we may start getting ORA-04023 errors.
Error:
ORA-04023: Object could not be validated or authorized
We may also see ORA-00001 errors in alert log file parallely.
If we have RAC database and then we may see ORA-04023 error only on one of the nodes which would be specific to instance or node
Cause:
This would be due to shared pool corruption.
Solution:
SQL> alter system flush shared_pool;
or bounce the database or specific instance.
If this does not solve the issue, we need to contact Oracle Support for further details.
Error:
ORA-04023: Object could not be validated or authorized
We may also see ORA-00001 errors in alert log file parallely.
If we have RAC database and then we may see ORA-04023 error only on one of the nodes which would be specific to instance or node
Cause:
This would be due to shared pool corruption.
Solution:
SQL> alter system flush shared_pool;
or bounce the database or specific instance.
If this does not solve the issue, we need to contact Oracle Support for further details.
Subscribe to:
Posts
(
Atom
)
3 comments :
Post a Comment