Oracle Database (All) : Archive Log Generation (Distribution - hourly/daily/size)

Oracle Database (All) : Archive Log Generation (Distribution - hourly/daily/size)

19c Vineet Sachdeva   May 17, 2020

In this blog I am going to cover on how to find out 

1. total number of archives generated per hour & per day 

2. The size of the archives MB/ hour & MB per day

 SQL Code to Run 

Output Sample for redo switches / hour

Redo Switches / hour /day

Redo Size / hour /day

The complete script can be download as a GIST from here 

Oracle Database: Process RSM0, PID = , will be killed

Oracle Database: Process RSM0, PID = , will be killed

DGBroker Vineet Sachdeva   September 06, 2019

When you check status of database using dgmgrl and you get the error message in alert log - 
'Process RSM0, PID = 1524144984, will be killed'

that means that dgmgrl is not able to get the response within stipulated amount of  time. 
This can be because of many reasons such has high CPU, Load Average , network etc. 


So, what you can do is increase the overall timeout period using 

'EDIT CONFIGURATION SET PROPERTY OperationTimeout=90'

another symptom of this is when you will check the database details, there will be no metrics which will be populated. 

Oracle Exadata: chrony Daemon (release 19.1)

Oracle Exadata: chrony Daemon (release 19.1)

Exadata Vineet Sachdeva   August 27, 2019

Release 19.1 of Exadata has taken a leap in using chrony daemon instead of age old written NTP daemon and protocol.

You will not find any NTP daemon installed once you upgrade to this release and note in this release you are at Linux 7. 

To check status of chrony daemon here are few commands which will help. 

1. Check Sources

# chronyc sources

210 Number of sources = 3
MS Name/IP address         Stratum Poll Reach LastRx Last sample
===============================================================================
^? aa-bbb-ccc-1100.dddddddd>     3   4   377     3  -2328ms[-2328ms] +/- 8332ms
^? bb-bbb-ccc-1100.dddddddd>     3   4   377     4  -2358ms[-2358ms] +/- 8681ms

^? cc-bbb-ccc-1100.dddddddd>     3   4   377     7  -2321ms[-2321ms] +/- 8696ms

2. Check Source stats

# chronyc sourcestats

210 Number of sources = 3

Name/IP Address            NP  NR  Span  Frequency  Freq Skew  Offset  Std Dev

==============================================================================

aa-bbb-ccc-1100.dddddddd>   6   3    81   +951.004   4830.397  -2334ms    31ms

bb-bbb-ccc-1100.dddddddd>   9   4   129   -438.641    216.568  -2372ms  4677us

cc-bbb-ccc-1100.dddddddd>  26  11   421   -381.175     20.365  -2332ms  3727us

3. Check sync

# chronyc tracking

Reference ID    : 0A901009 (cc-bbb-ccc-1100.dddddddd)

Stratum         : 4

Ref time (UTC)  : Mon Aug 26 03:19:47 2019

System time     : 0.000000304 seconds fast of NTP time

Last offset     : -0.000190595 seconds

RMS offset      : 0.012864766 seconds

Frequency       : 84.308 ppm slow

Residual freq   : -0.004 ppm

Skew            : 0.720 ppm

Root delay      : 0.140609756 seconds

Root dispersion : 1.491145134 seconds

Update interval : 16.1 seconds

Leap status     : Normal

You can find more details on chrony commands here 

https://www.thegeekdiary.com/centos-rhel-7-configuring-ntp-using-chrony/

Oracle Database: TFACTL Blackout ORA-600/7445/700

Oracle Database: TFACTL Blackout ORA-600/7445/700

Admin Vineet Sachdeva   August 21, 2019

tfactl or oracle's trace file analyzer utility can be really helpful sometimes, and also a lot of pain in many, when it sends a lot of alerts some of which you know and it's a known issue or you don't want to do anything about it. 

Fortunately, you can blackout such events from tfactl 

To supppress a specific string ORA-0600 this is what I would do. 

tfactl blackout add -targettype database -target mydb -c -event  "kewrspbr_2"  -timeout none -reason "skipping ORA-0600 Doc ID 2313603.1" 

where 

-target : dbname

-c : clusterwide

-event: String contained in the event (find it from the email you got)

-timeout : no timeout

Similarly, you can play around with the parameters and blackout a lot of unwanted emails.
You can find them documentation below. 


Reference - https://docs.oracle.com/en/engineered-systems/health-diagnostics/trace-file-analyzer/tfaug/running-administration-commands.html#GUID-56A03EC7-3324-48BD-9701-2411626D16CB

Oracle Database: Permissions to Execute Job Class

Oracle Database: Permissions to Execute Job Class

OracleScheduler Vineet Sachdeva   August 02, 2019

There is a great mis conception in DBA's who primarily work on OLTP about Job classes.

Job classes are one of the essentially used components in job definitions which people seldom use and seldom know about. 

This blog however is a small and nice one which talks about how to see permissions to execute a job class.

Yes you read that right. You need execute permission for a job class. 
Only if you have execute permission to job class, you will be able to add it to your create job. 

the way to grant is same 

Grant execute 'on job class' to <user>';

to verify you should use dba_tab_privs to know if your user has execute privilege on job class or not. 

select * from dba_tab_privs where grantee = '<your user>' and type = 'JOB CLASS';

Example output below (from TOAD)

By default permission to execute 'DEFAULT JOB CLASS' is granted to PUBLIC, so you if you do not specify any job class name, default job class will be taken and it will be okay. 

Oracle Database 19c: Silent Database Deletion

Oracle Database 19c: Silent Database Deletion

RAC Vineet Sachdeva   June 14, 2019

In this blog I am going to present on how to delete database silently (RAC / Non RAC)


$ export ORACLE_BASE=/u01/app/oracle
$export ORACLE_HOME=/u01/app/oracle/product/190/db/
$ export PATH=$ORACLE_HOME/bin:$PATH
$ dbca -silent -deleteDatabase -sourceDB  ORPL
Enter SYS user password:

[WARNING] [DBT-19202] The Database Configuration Assistant will delete the Oracle instances and datafiles for your database. All information in the database will be destroyed.
Prepare for db operation
32% complete
Connecting to database
39% complete
42% complete
45% complete
48% complete
52% complete
55% complete
58% complete
65% complete
Updating network configuration files
68% complete
Deleting instances and datafiles
77% complete
87% complete
97% complete
100% complete
Database deletion completed.
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/orpl/orpl.log" for further details.