How to estimate space needed for archive logs

We are back in LinkedIn and this time in Oracle senior DBA group discussion. The question is:

How to calculate necessary space on File System for put in archive log mode an database oracle 11g, currently is not in archive log mode, thanks

Space needed for your archive log location(s) can be estimated by following script, which gets average redo log file size (which should be same for all files BTW) and basically multiplies that by number of log file switches per day:

SELECT log_hist.*,
ROUND(log_hist.num_of_log_swithes * log_file.avg_log_size / 1024 / 1024) avg_mb
FROM
(SELECT TO_CHAR(first_time,'DD.MM.YYYY') DAY,
COUNT(1) num_of_log_swithes
FROM v$log_history
GROUP BY TO_CHAR(first_time,'DD.MM.YYYY')
ORDER BY DAY DESC
) log_hist,
(SELECT AVG(bytes) avg_log_size FROM v$log) log_file;

Archive log files usually go into same location where your “hot” backups go, like FRA. Space needed there depends on your backup strategy and retention policy. If you are deleting archive log files after each successful full or incremental backup, space needed should be count of days between backups plus some good reserve. If you are backing archive log files from archive log destination to some other device, than you should estimate needed space based on that.

ORA-12008: error in materialized view refresh path

This was just one of those days, when strange things happen. Consider this:

You have a materialized view with left outer join:

CREATE MATERIALIZED VIEW table_mw
      REFRESH FAST ON COMMIT
      WITH ROWID
      AS
SELECT
      v.ROWID v_rid,
      t.ROWID t_rid,
      s.ROWID s_rid,
      v.*,
      s.dfr,
      s.dfr
FROM
      table_v v,
      table_t t,
      table_s s
WHERE v.ref_id = t.id (+)
      AND v.ref_id = s.id (+)
/

CREATE MATERIALIZED VIEW LOG ON table_v WITH ROWID
/

CREATE MATERIALIZED VIEW LOG ON table_t WITH ROWID
/

CREATE MATERIALIZED VIEW LOG ON table_s WITH ROWID
/

Now, let’s keep aside that not only you have to comply with documented rules for view to be fast refreshable (http://docs.oracle.com/cd/B28359_01/server.111/b28313/basicmv.htm#i1006674), there is also one undocumented restriction: ANSI-joins are not supported.

With that solved, we did a simple UPDATE of one row in table TABLE_V. Everything looked fine, until we wanted to commit. Commit returned following error stack:

ORA-12008: error in materialized view refresh path
ORA-00942: table or view does not exist

Cause:  Table SNAP$_<mview_name> reads rows from the view MVIEW$_<mview_name>, which is a view on the master table (the master may be at a remote site). Any error in this path will cause this error at refresh time. For fast refreshes, the table <master_owner>.MLOG$_<master> is also referenced.

Action: Examine the other messages on the stack to find the problem. See if the objects SNAP$_<mview_name>, MVIEW$_<mview_name>, <mowner>.<master>@<dblink>, <mowner>.MLOG$_<master>@<dblink> still exist.

I checked alert log and trace files to find nothing of use there. After some experimentation I found out that for some reason, Oracle cannot cope with timestamp-based materialized view logs. Implementation of commit SCN-based materialized view logs solved the problem.

So in the end materialized view logs looked like this:

CREATE MATERIALIZED VIEW LOG ON table_v WITH ROWID, COMMIT SCN
/

CREATE MATERIALIZED VIEW LOG ON table_t WITH ROWID, COMMIT SCN
/

CREATE MATERIALIZED VIEW LOG ON table_s WITH ROWID, COMMIT SCN
/

Seems like one of those problems you solve, but don’t know why they came in first place.

PS: There is also one important think you should not forget or you'll get same generic error on COMMIT of one of the source tables. The thing is that if you materialized view is in different schema, don't forget to also GRANT SELECT on materialized view logs (MLOG$, RUPD$).

How to get IOPS from AWR and Real-time

Recently an interesting question came up in Oracle Database Performance Tuning forum on LinkedIn.

The question was:

How to calculate the IOPS from AWR Report. ?

With follow up question after my first SQL:

Is there any way to calculate IOPS in real time?
What views we should use to calculate IOPS real time.
And can we use gv$ASM_IOSTAT or gv$sysmetric.

I did little Google check and linked nice blog post by G.Makino about how to get needed information from AWR Report in EM: https://gmakino.wordpress.com/2012/11/08/how-to-identify-iops-in-awr-reports .

So I figured it would be nice to share some SQL in LinkedIn forum and here.

Get IOPS from AWR:

SELECT metric_name,
  (CASE WHEN metric_name LIKE '%Bytes%' THEN TO_CHAR(ROUND(MIN(minval / 1024),1)) || ' KB' ELSE TO_CHAR(ROUND(MIN(minval),1)) END) min,
  (CASE WHEN metric_name LIKE '%Bytes%' THEN TO_CHAR(ROUND(MAX(maxval / 1024),1)) || ' KB' ELSE TO_CHAR(ROUND(MAX(maxval),1)) END) max,
  (CASE WHEN metric_name LIKE '%Bytes%' THEN TO_CHAR(ROUND(AVG(average / 1024),1)) || ' KB' ELSE TO_CHAR(ROUND(AVG(average),1)) END) avg
FROM dba_hist_sysmetric_summary
WHERE metric_name
  IN ('Physical Read Total IO Requests Per Sec',
  'Physical Write Total IO Requests Per Sec',
  'Physical Read Total Bytes Per Sec',
  'Physical Write Total Bytes Per Sec')
GROUP BY metric_name
ORDER BY metric_name;

METRIC_NAME                                                      MIN                                         MAX                                         AVG ---------------------------------------------------------------- ------------------------------------------- ------------------------------------------- ------------------------------------------- Physical Read Total Bytes Per Sec                                0 KB                                        83453.8 KB                                  415.9 KB Physical Read Total IO Requests Per Sec                          0                                           881.9                                       13.3 Physical Write Total Bytes Per Sec                               0 KB                                        8014.9 KB                                   55 KB Physical Write Total IO Requests Per Sec                         0                                           901.1                                       3.8

Get real time IOPS (It’s good to point out, that there is no such view as gv$ASM_IOSTAT). There is view gv$asm_disk_stat, but its stats are cumulative. For real time sample we have to use gv$sysmetric (v$sysmetric):

SELECT inst_id,
  intsize_csec / 100 "Interval (Secs)",
  metric_name "Metric",
  (
  CASE
    WHEN metric_name LIKE '%Bytes%'
    THEN TO_CHAR(ROUND(AVG(value / 1024 ),1))
      || 'KB'
    ELSE TO_CHAR(ROUND(AVG(value),1))
  END) "Value"
FROM gv$sysmetric
WHERE metric_name IN ('Physical Read Total IO Requests Per Sec', 'Physical Write Total IO Requests Per Sec', 'Physical Read Total Bytes Per Sec', 'Physical Write Total Bytes Per Sec')
GROUP BY inst_id,
  intsize_csec,
  metric_name
ORDER BY inst_id,
  intsize_csec,
  metric_name;

   INST_ID Interval (Secs) Metric                                                           Value ---------- --------------- ---------------------------------------------------------------- ------------------------------------------          1           60.77 Physical Read Total Bytes Per Sec                                354.6KB          1           60.77 Physical Read Total IO Requests Per Sec                          16.4          1           60.77 Physical Write Total Bytes Per Sec                               27.8KB          1           60.77 Physical Write Total IO Requests Per Sec                         2.7          2            59.6 Physical Read Total Bytes Per Sec                                2460KB          2            59.6 Physical Read Total IO Requests Per Sec                          32.8          2            59.6 Physical Write Total Bytes Per Sec                               67.6KB          2            59.6 Physical Write Total IO Requests Per Sec                         3.4

You should be aware of fact that samples 60 seconds +/- on each node of RAC. So they are not exactly the same, but difference is not of a big deal.