I reviewed Oracle Database 12c for the InfoWorld magazine.
You can read the article here: Oracle Database 12c Review
Posted by Riyaj Shamsudeen on June 26, 2013
I reviewed Oracle Database 12c for the InfoWorld magazine.
You can read the article here: Oracle Database 12c Review
Posted in 12c | Tagged: 12c articles, 12c optimizer features, 12c RAC features, Oracle Database 12c, pluggable database | 2 Comments »
Posted by Riyaj Shamsudeen on June 12, 2013
This blog entry is to discuss a method to identify the objects inducing higher amount of redo. First,we will establish that redo size increased sharply and then identify the objects generating more redo. Unfortunately, redo size is not tracked at a segment level. However, you can make an educated guess using ‘db block changes’ statistics. But, you must use logminer utility to identify the objects generating more redo scientifically.
Detecting redo size increase
AWR tables (require Diagnostics license) can be accessed to identify the redo size increase. Following query spools the daily rate of redo size. You can easily open the output file redosize.lst in an Excel spreadsheet and graph the data to visualize the redo size change. Use pipe symbol as the delimiter while opening the file in excel spreadsheet.
spool redosize.lst REM You need Diagnostic Pack licence to execute this query! REM Author: Riyaj Shamsudeen col begin_interval_time format a30 set lines 160 pages 1000 col end_interval_time format a30 set colsep '|' alter session set nls_date_format='DD-MON-YYYY'; with redo_sz as ( SELECT sysst.snap_id, sysst.instance_number, begin_interval_time ,end_interval_time , startup_time, VALUE - lag (VALUE) OVER ( PARTITION BY startup_time, sysst.instance_number ORDER BY begin_interval_time, startup_time, sysst.instance_number) stat_value, EXTRACT (DAY FROM (end_interval_time-begin_interval_time))*24*60*60+ EXTRACT (HOUR FROM (end_interval_time-begin_interval_time))*60*60+ EXTRACT (MINUTE FROM (end_interval_time-begin_interval_time))*60+ EXTRACT (SECOND FROM (end_interval_time-begin_interval_time)) DELTA FROM sys.wrh$_sysstat sysst , DBA_HIST_SNAPSHOT snaps WHERE (sysst.dbid, sysst.stat_id) IN ( SELECT dbid, stat_id FROM sys.wrh$_stat_name WHERE stat_name='redo size' ) AND snaps.snap_id = sysst.snap_id AND snaps.dbid =sysst.dbid AND sysst.instance_number=snaps.instance_number and begin_interval_time > sysdate-90 ) select instance_number, to_date(to_char(begin_interval_time,'DD-MON-YYYY'),'DD-MON-YYYY') dt , sum(stat_value) redo1 from redo_sz group by instance_number, to_date(to_char(begin_interval_time,'DD-MON-YYYY'),'DD-MON-YYYY') order by instance_number, 2 / spool off
Visualizing the data will help you to quickly identify any pattern anomalies in redo generation. Here is an example graph created from the excel spreadsheet and see that redo size increased recently.
Posted in 11g, Oracle database internals, Performance tuning, RAC | Tagged: identify objects redo, redo internals, segment_stats.sql, v$logmnr_contents, v$segment_stats | 22 Comments »
Posted by Riyaj Shamsudeen on June 5, 2013
The restart of a UNIX server call initialization scripts to start processes and daemons. Every platform has a unique directory structure and follows a method to implement server startup sequence. In Linux platform (prior to Linux 6), initialization scripts are started by calling scripts in the /etc/rcX.d directories, where X denotes the run level of the UNIX server. Typically, Clusterware is started at run level 3. For example, ohasd daemon started by /etc/rc3.d/S96ohasd file by supplying start as an argument. File S96ohasd is linked to /etc/init.d/ohasd.
S96ohasd -> /etc/init.d/ohasd /etc/rc3.d/S96ohasd start # init daemon starting ohasd.
Similarly, a server shutdown will call scripts in rcX.d directories, for example, ohasd is shut down by calling K15ohasd script:
K15ohasd -> /etc/init.d/ohasd /etc/rc3.d/K15ohasd stop #UNIX daemons stopping ohasd
In Summary, server startup will call files matching the pattern of S* in the /etc/rcX.d directories. Calling sequence of the scripts is in the lexical order of script name. For example, S10cscape will be called prior to S96ohasd, as the script S10cscape occurs earlier in the lexical sequence.
Google if you want to learn further about RC startup sequence. Of course, Linux 6 introduces Upstart feature and the mechanism is a little different: http://en.wikipedia.org/wiki/Upstart
That’s not the whole story!
Have you ever thought why the ‘crsctl start crs’ returns immediately? You can guess that Clusterware is started in the background as the command returns to UNIX prompt almost immediately. Executing the crsctl command just modifies the ohasdrun file content to ‘restart’. It doesn’t actually perform the task of starting the clusterware. Daemon init.ohasd reads the ohasdrun file every few seconds and starts the Clusterware if the file content is changed to ‘restart’.
# cat /etc/oracle/scls_scr/oel6rac1/root/ohasdrun
restart
Posted in 11g, Oracle database internals, RAC | Tagged: clusterware startup, ohasd startup, ohasdrun, ohasdstr, RAC, RC scripts clusterware | 21 Comments »