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 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. 

Oracle Database: Planning for New Storage

Oracle Database: Planning for New Storage

19c Vineet Sachdeva   June 12, 2019

In this blog I cover topics which need to be considered when doing a new storage design for your Oracle Database. 

1. Review the current storage speed using the storage consoles which you have and find out the iops happening on the SSD Cluster + HDD Cluster, you can get iops and data transferred from the storages itself. From the current design, you must add the iops of SSD and HDD cluster and the data transfer as well

• design should include at least these much iops and probably a 20-30% more.
• design should be done considering the peak hours storage iops and the data transfer rates.

2. Design should/not enforce redundancy. 

• data is better handled by ASM redundancy, in case using ASM use ASM redundancy - Normal is preferred for DATA and FRA, High for REDO, CF and OCR.
• in case you are not using ASM then ask storage to include external parity and redundancy
• the effective storage capacity should be given as per the approach followed. 

3. A design having multiple disks performs better in oracle, as when you give the luns to oracle, it will stripe the data across the disks, thus maximizing performance. 

• design with many small disks can mean additional controllers and more management and more sockets to insert to.
• design with big disks again can reduce performance, so you should have a tradeoff with respect to cost and design.

4.  Latency which you plan should be as seen by Oracle and not from storage perspective

• from the AWR find out the current latency, min, max and average.
• In the AWR, you will have details of what you want or you can use my blog to find out latency of the events. 
• latency introduced by networking components should be considered
• networking should be such that it does not introduces a significant amount of latency
• latency planned should be the same at the maximum iops and maximum data transfer
• prefer to have redo, controlfile, ocr on fastest disks as these require less storage area.
• if you have a read intensive and dw application, you can have temp to on faster storage area. 

5. Any storage design should exclude any type of inline compression or storage optimization, unless the storage compression (at storage level) is certified by Oracle.

6. Migration to new storage depends on what type of design you use.

• when migrating to new storage always follow standby first approach
• when using ASM, use drop and add disks in the same operation
• these operations are resumable after restart of the cluster also
• when not using ASM, use methods such as duplicate and rolling switch of data files to minimize downtime.

Oracle Database: Flashback RAC Primary and Standby

Oracle Database: Flashback RAC Primary and Standby

DGBroker Vineet Sachdeva   June 11, 2019

In this blog I am going to discuss on how to flashback a RAC Primary and RAC Standby both and ensure Standby is in sync. 
These are for some maintenance activities you might want to perform on Primary and to have a rollback plan. 
You can re-order few steps if you ant


My primary Database name is PRIM and Standby is STDBY.

Step 1 and 2 are setup for the flashback. 

Step 1  - Stop the apply on STDBY
dgmgrl /
edit datbase 'STDBY' set state 'APPLY-OFF'

Step 2 - Create GRP (Guranteed Restore Point)
i. Switch logfile on Primary (alter system archive log current) 
ii. Create GRP on Standby (Create Restore  Point BEFORE_RELOAD GUARANTEE FLASHBACK DATABASE)
iii. Create GRP on Primary  (Create Restore  Point BEFORE_RELOAD GUARANTEE FLASHBACK DATABASE)
iv. take output of select * from v$restore_point and note the scn


Now do your changes (patching etc), assuming your changes were not successful and you want to rollback.  

Step 3 - Do the Flashback
i. Stop Database Primary (srvctl stop database -d PRIM)
ii. Start one instance in mount (startup mount exclusive)
iii. Flashback Database (flashback database to restore point BEFORE_RELOAD);
iv. alter database open reset logs

Step 4 - Start Primary (all nodes)
i. srvctl start database -d PRIM


Step 5 - Flashback Standby 
i. Check the current_scn (select current_scn from V$database)
ii. if current_scn of Standby is less than 2.iv, i.e scn of GRP on primary then you need not to do any flashback and can skip
iii. If current_scn is greater then you have 2 options
First - srvctl stop database -d STDBY and then startup mount exclusive for 1 instance
Then - 
    a. Flashback to scn of the GRP of primary (flashback database to scn <> )
    b. Flashback to scn of the GRP of standby (flashback database to restore point BEFORE_RELOAD). This assumes the scn of standby GRP is less than scn of primary GRP
iv. start DB (srvctl stop database -d CHMSTDBY, and srvctl start database -d CHMSTDBY)
v. Ensure apply is working fine. (edit database 'STDBY' set state='APPLY-ON')

Step 6 - Finally Drop Restore Points
i. Primary - Drop restore point BEFORE_RELOAD
ii. Standby - for this you will have to have database is mount mode (in case your standby was read only) and then 'drop restore point BEFORE_RELOAD'

References: How To Flashback Primary Database In Standby Configuration (Doc ID 728374.1)

Oracle Database: Tools for Monitoring (I/O, Network)

Oracle Database: Tools for Monitoring (I/O, Network)

Monitoring Vineet Sachdeva   May 31, 2019

In this blog I discuss few tools which can be used for database or systems monitoring to be precisely. 

If you want to know what rpms you should be installing just beyond the required ones to install oracle Database, then these are the ones  

Now assuming you have these installed

1. IOTOP
One of the really good tools which can help in debugging i/o performance 
this can tell 
1. Total disk read and write happening
2. Disk read and write by process
3. IO wait % for a process
4. Swap information for a process

Just run iotop as root

Total DISK READ :     574.79 K/s | Total DISK WRITE :       2.92 M/s
Actual DISK READ:     574.79 K/s | Actual DISK WRITE:       2.95 M/s
   TID  PRIO  USER     DISK READ  DISK WRITE  SWAPIN     IO>    COMMAND
  5174 rt/4 grid      969.66 B/s    0.00 B/s  0.00 %  1.64 % ocssd.bin
175547 be/4 oracle    106.06 K/s    0.00 B/s  0.00 %  0.87 % ora_j001_PR11
 10213 be/4 oracle    151.51 K/s  227.26 K/s  0.00 %  0.86 % ora_ckpt_PR11
 10335 be/4 oracle      0.00 B/s  674.22 K/s  0.00 %  0.74 % ora_rvwr_PR11
175549 be/4 oracle    151.51 K/s    0.00 B/s  0.00 %  0.68 % ora_j002_PR11
 17843 be/4 grid      121.21 K/s   15.15 K/s  0.00 %  0.66 % mdb_ckpt_-MGMTDB
  5170 rt/4 grid      969.66 B/s    0.00 B/s  0.00 %  0.17 % ocssd.bin

  5167 rt/4 grid      969.66 B/s    0.00 B/s  0.00 %  0.16 % ocssd.bin

2. IOSTAT
Iostat is complimentary to iotop, it provides details at the disk level 
run iostat -dmx 2 sdb sdc sds sdq sdm as root

You will get information like this below 

1. You can tell individual disk utilization 

2. -dmx 2 are arguments (you can look up in the man page)
3. to find out the sd devices you want to see use lsblk

Linux 3.10.0-693.17.1.el7.x86_64 (CHUMR1DB001)  05/31/2019      _x86_64_        (16 CPU)
Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sdb               0.00     0.00    4.73    0.73     0.15     0.01    59.82     0.05    9.07   10.02    2.91   4.71   2.57
sdc               0.00     0.00    5.38    5.32     2.82     1.98   918.41     1.44  134.90  140.15  129.58  12.13  12.98
sdm               0.00     0.00    2.21    1.33     0.00     0.00     2.20     0.01    2.28    1.90    2.92   2.09   0.74
sdq               0.00     0.00    2.33    1.21     0.10     0.04    79.00     0.02    6.47    8.15    3.22   5.63   1.99
sds               0.00     0.00    2.23    1.34     0.00     0.00     2.30     0.01    2.32    1.88    3.05   2.21   0.79
Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sdb               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00    0.00    0.00   0.00   0.00
sdc               0.00     0.00    0.00    2.50     0.00     0.76   624.00     0.01    3.60    0.00    3.60   3.60   0.90
sdm               0.00     0.00    2.00    1.00     0.00     0.00     1.00     0.00    0.67    0.50    1.00   0.67   0.20
sdq               0.00     0.00    1.50    1.00     0.02     0.05    63.60     0.00    1.40    1.00    2.00   1.40   0.35
sds               0.00     0.00    2.00    1.00     0.00     0.00     1.00     0.00    0.83    0.75    1.00   0.83   0.25

2. iptraf-ng
The next big tool is which is for networking is iptraf-ng
this can be a very useful network tool with a lot of features such as interfaces stats, breakdowns, traffic monitoring. 
You really want this as it is there in official redhat repo 

Just run iptraf-ng as root, and you will get all the options which you can choose from 


IP traffic monitor             
General interface statistics   
Detailed interface statistics  
Statistical breakdowns...      
LAN station monitor           

Choose the option you want - say traffic monitor, then select the interfaces and then you can see the live monitoring. 
You can look into Detailed interface statistics and choose the interface to find out the bandwidth (in rate and out rate currently)
It supports logical interfaces as well