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 

OEM 13cR3 - Silent Installation with DB 18c Templates - Part 1

OEM 13cR3 - Silent Installation with DB 18c Templates - Part 1

OEM Vineet Sachdeva   November 21, 2019

In this blog I am going to cover installation of OEM 13cR3 silent with Templates on DB 18
I will be referring to my previous blogs here to keep the length of this Series minimal.
This is a 2 Part blog

1. The first part covers Database Installation and DB Creation
2. The second part which can be found here covers the part where we install the OEM.

Part 1
The step 1 is obviously to have your database installed, you can refer to my blog on Database Installation Silent of 18c 

Once the Installation is complete, download the template for Database 18c for OEM 13cR3 from below link
https://www.oracle.com/enterprise-manager/downloads/db-templates-13c-release3-downloads.html

Unzip the template in $ORACLE_HOME/assistants/dbca/templates

$ cd /u01/app/oracle/product/183/db/assistants/dbca/templates
$ unzip -qq /u01/Template/18.1.0.0.0_database_template_for_em13_3_0_0_0_linux_x64.zip

Next Install EMREPO using the template

$ /u01/app/oracle/product/183/db/bin/dbca -silent -createDatabase -templateName  18_1_0_0_0_Database_Template_for_EM13_3_0_0_0_Small_deployment.dbc -gdbName EMPRD -sid EMPRD -sysPassword oracle -systemPassword  oracle

Once the DB is Installed and running, set SPFILE parameters according to your need and set  all the spfile parameters as specified in below link for an 18c Installation. 

https://www.oracle.com/enterprise-manager/downloads/db-templates-13c-release3-downloads.html

After that do a Bounce of the Instance.


This completes my database creation.
Ensure you have a listener running which registers the DB Services
IN the next part of the blog we will setup the OEM. 

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 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: Duplicate Specific Tablespaces only

Oracle Database: Duplicate Specific Tablespaces only

19c Vineet Sachdeva   June 12, 2019

Oracle allows you to duplicate a database using specific tablespaces only, .

The command is same as the duplicate command 

Mention the tablespace comma separated names (highlighted in bold)
Ofcourse temp, system,sysaux, undo will be duplicated along

• rman run at the new database site
• target is source
• auxiliary is the new db
• list of tablespaces is comma seperated.
• if you want to skip a specific tablespace use 'skip <tablespace_name>' 

rman 
connect TARGET sys/passs@PROD;
connect AUXILIARY sys/pass@XDB1_STDBY;
run 
{
set newname for database to '+FRA';
DUPLICATE TARGET DATABASE 
  TO XDB
  FROM ACTIVE DATABASE
  TABLESPACE users
  NOFILENAMECHECK;

}

The highlight here is that oracle will internally check during duplicate that if the tablespaces are linked (dependent) or not.

You will have to ensure that static entries is present for the new database in the listener, as it will need to be shut down and started by the rman.