Oracle Database (JCDM) Blog: November 2013

Friday, November 29, 2013

Combined D3 Stacked Bar/Pie Charts will Oracle/UNix input

This final report follows on from the early ones (see here), and combines a stacked bar chart with a pie chart on the same page.

This time I’ve only shown the base html. It can be easily put into a Unix script as the others work with a little effort and then emailed to you on a daily basis. This would involve cat’ing the text to an output file with the ‘var data’ statements being prepared within SQLPLUS or UNIX commands. See the early posts.

<!DOCTYPE html>

<html>

<head>

<title>Oracle 11g - Daily Charts</title>

<style>

div.padded {

padding-top: 0px;

padding-right: 0px;

padding-bottom: 0px;

padding-left: 0px;

}

.chart rect {

fill: steelblue;

}

.bar2 bar { fill: lightred; }

.chart text {

fill: red;

font: 10px sans-serif;

text-anchor: middle;

}

.axis text {

font: 10px sans-serif;

}

.axis path,

.axis line {

fill: none;

stroke: #000;

shape-rendering: crispEdges;

}

//Next bit for grid lines

.grid .tick {

stroke: lightgrey;

opacity: 0.7;

}

.grid path {

stroke-width: 0;

}

.grid .tick {

stroke: lightgrey;

opacity: 0.7;

}

.grid path {

stroke-width: 0;

}

type="text/css">

.slice text {

font-size: 16pt;

font-family: Arial;

</style>

</head>

<body>

<td border=0 width=808 style="font-family:arial;color:red;font-size:20px;" align=center>Daily Monitoring Report</td></tr>

<tr>

// Stacked Bar Chart

var margin = {top: 50, right: 30, bottom: 150, left: 60},

width = 400 - margin.left - margin.right,

height = 400 - margin.top - margin.bottom;

var data = [

{name: "/", gbused: 0.23, gbfree: 0.02},

{name: "/usr", gbused: 2.37, gbfree: 0.07},

{name: "/var", gbused: 0.41, gbfree: 0.59},

{name: "/tmp", gbused: 0.01, gbfree: 1.99},

{name: "/home", gbused: 0.01, gbfree: 0.11},

{name: "/admin", gbused: 0, gbfree: 0.12},

{name: "/opt", gbused: 0.2, gbfree: 0.18},

{name: "/oracle", gbused: 30.72, gbfree: 19.03},

{name: "/usr/local/bin", gbused: 0.01, gbfree: 0.99},

{name: "/oracle/oradata_a", gbused: 93.03, gbfree: 11.97},

{name: "/oracle/oradata_b", gbused: 59.32, gbfree: 36.18},

{name: "/oracle/fast_recovery_area", gbused: 19.12, gbfree: 14.88},

{name: "/oracle/archive", gbused: 81.07, gbfree: 9.93},

];

// Note: This is produced by

// df -g | awk '{print "{name: \"" $1 "\", gbused: " $2 - $3 ", gbfree: " $3 "}," }'|grep -v proc | tail +2

// and then pasting the results in here. Alternative way is to ut and paste the rest of this into

// a shell script with this df statement running in situ. (df -g seems to be AIX only, -h for linux/solaris.

var x = d3.scale.ordinal()

.rangeRoundBands([0, width], .1);

var y = d3.scale.linear()

.range([height, 0]);

var xAxis = d3.svg.axis()

.scale(x)

.orient("bottom");

var yAxis = d3.svg.axis()

.scale(y)

.orient("left");

//var chart = d3.select(".chart")

var chart = d3.select("#area1")

.append("svg:svg")

.attr("width", width + margin.left + margin.right)

.attr("height", height + margin.top + margin.bottom)

.append("g")

.attr("transform", "translate(" + margin.left + "," + margin.top + ")");

x.domain(data.map(function(d) { return d.name; }));

y.domain([0, d3.max(data, function(d) { return d.gbused+d.gbfree; })]);

// Add the grid lines before everything else so they go underneath

chart.append("g")

.attr("class", "grid")

.attr("transform", "translate(0," + height + ")")

.call(make_x_axis()

.tickSize(-height, 0, 0)

.tickFormat("")

);

chart.append("g")

.attr("class", "grid")

.call(make_y_axis()

.tickSize(-width, 0, 0)

.tickFormat("")

);

// Add everything else

chart.append("g")

.attr("class", "x axis")

.attr("transform", "translate(0," + height + ")")

.call(xAxis)

.selectAll("text")

.style("text-anchor", "end")

.attr("dx", "-.8em")

.attr("dy", ".15em")

.attr("transform", function(d) {

return "rotate(-65)"

});

chart.append("g")

.attr("class", "y axis")

.call(yAxis);

chart.selectAll(".bar")

.data(data)

.enter().append("rect")

.attr("class", "bar")

.style("fill", function(d, i){return d>100?"red":"steelblue";})

.attr("x", function(d) { return x(d.name); })

.attr("y", function(d) { return y(d.gbused); })

.attr("height", function(d) { return height - y(d.gbused); })

.attr("width", x.rangeBand());

chart.selectAll(".bar2")

.data(data)

.enter().append("rect")

.attr("class", "bar2")

.style("fill", "pink")

.attr("x", function(d) { return x(d.name); })

.attr("y", function(d) { return y(d.gbfree + d.gbused); })

.attr("height", function(d) { return height - y(d.gbfree) ; })

.attr("width", x.rangeBand());

chart.append("g")

.attr("class", "y axis")

.call(yAxis)

.append("text")

.attr("transform", "rotate(-90)")

.attr("y", -40)

.attr("x", -(height / 2))

.attr("dy", ".71em")

.style("fill", "black")

.style("text-anchor", "end")

.text("Disk Space (Gb)");

chart.append("g")

.attr("class", "x axis")

.append("text")

.attr("y", height + 90)

.attr("x", width / 2)

.attr("dx", ".71em")

.style("font-size", "12px")

.style("fill", "black")

.style("text-anchor", "middle")

.text("Mount Point");

chart.append("text")

.attr("x", (width / 2))

.attr("y", 0 - (margin.top / 2))

.attr("text-anchor", "middle")

.style("fill", "black")

.style("font-size", "16px")

.style("text-decoration", "underline")

.text("Server Disk - Used/Free Space");

function make_x_axis() {

return d3.svg.axis()

.scale(x)

.orient("bottom")

.ticks(5)

}

function make_y_axis() {

return d3.svg.axis()

.scale(y)

.orient("left")

.ticks(5)

}

</script>

</td><td style="border-width: 1px; border-style: solid; border-color: black; " >

// Pie Chart

var canvasWidth = 400,

canvasHeight = 400,

outerRadius = 125,

color = d3.scale.category20();

var dataSet = [

{name:"ABD_TS", value:3},

{name:"SYSTEM", value:75},

{name:"SYSAUX", value:62},

{name:"UNDOTBS01", value:100},

{name:"USERS", value:53},

{name:"PERFSTAT", value:40},

];

var vis = d3.select("#area2")

.append("svg:svg")

.data([dataSet])

.attr("width", canvasWidth)

.attr("height", canvasHeight)

.append("svg:g") //make a group to hold our pie chart

.attr("transform", "translate(" + 1.5*outerRadius + "," + 1.5*outerRadius + ")")

var arc = d3.svg.arc()

.outerRadius(outerRadius);

var pie = d3.layout.pie()

.value(function(d) { return d.value; })

.sort( function(d) { return null; } );

var arcs = vis.selectAll("g.slice")

.data(pie)

.enter()

.append("svg:g")

.attr("class", "slice");

arcs.append("svg:path")

.attr("fill", function(d, i) { return color(i); } )

.attr("d", arc);

arcs.append("svg:text")

.attr("transform", function(d) {

d.outerRadius = outerRadius + 50;

d.innerRadius = outerRadius + 45;

return "translate(" + arc.centroid(d) + ")";

})

.attr("text-anchor", "middle")

.style("fill", "Purple")

.style("font", "bold 10px Courier")

.text(function(d, i) { return dataSet[i].name; });

arcs.filter(function(d) { return d.endAngle - d.startAngle > .2; }).append("svg:text")

.attr("dy", ".35em")

.attr("text-anchor", "middle")

.attr("transform", function(d) {

d.outerRadius = outerRadius;

d.innerRadius = outerRadius/2;

return "translate(" + arc.centroid(d) + ")rotate(" + angle(d) + ")";

})

.style("fill", "White")

.style("font", "bold 12px Courier")

.text(function(d) { return d.data.value; });

arcs.append("text")

.attr("x", 0 )

.attr("y", 0 - (canvasHeight / 2))

.attr("text-anchor", "middle")

.style("fill", "black")

.style("font", "16px sans-serif")

.style("text-decoration", "underline")

.text("Oracle Tablespaces");

function angle(d) {

var a = (d.startAngle + d.endAngle) * 90 / Math.PI - 90;

return a > 90 ? a - 180 : a;

}

</script>

</div>

</td></tr></table></tr></table>

</body>

</html>

Happyjohn

Thursday, November 28, 2013

Oracle 11g - Pie Chart Email

Here’s another example of how to use the D3 javascript library to produce a Pie Chart this time.

Essentially it’s the same as the bar chart example. I have added labels this time but you’ll get the general idea.

The script is more or less the same as the previous one just with the D3 bits before and after the SQLPLUS command heavily altered.

The results look something like this.

Now I appreciate that this example isn’t something you’d chose to display like this. But all you have to do is change the sql select to something more appropriate and you’re away. You might see that the wrapper is more HTML like this time. Once I’ve worked out what I think would be useful to show, I’m going to do a big catch all email with 4-5 graphs of differing types in one script with parameterised sql so you can use it out of the box, so to speak.

Here is the script…

I keep the email addresses as variables in the .profile, so it’s easier to amend them when someone new joins the team.

#!/bin/sh

. $HOME/.profile 2>/dev/null

tmpfile= `date +%d%b%Y`.html

rm ${tmpfile} 2>/dev/null

cat <<EOF1 >> ${tmpfile}

<!DOCTYPE html>

<html>

<head>

<title>Oracle 11g - Pie Chart</title>

.slice text {

font-size: 16pt;

font-family: Arial;

}

</style>

</head>

<body>

var canvasWidth = 500,

canvasHeight = 500,

outerRadius = 100,

color = d3.scale.category20();

var dataSet = [

EOF1

sqlplus -s / as sysdba <<EOF >> ${tmpfile} set lin 400 set pages 1000 set feedback off set head off

SELECT '{name: "'||ts.tablespace_name||'", value: '||round((fr."FREE(MB)" / df."SIZE(MB)") * 100,2) || '},'

FROM (SELECT tablespace_name, SUM(bytes) / (1024 * 1024) "FREE(MB)"

FROM dba_free_space GROUP BY tablespace_name) fr,

(SELECT tablespace_name, SUM(bytes) / (1024 * 1024) "SIZE(MB)", COUNT(*) "File Count", SUM(maxbytes) / (1024 * 1024) "MAX_EXT"

FROM dba_data_files GROUP BY tablespace_name) df,

(SELECT tablespace_name

FROM dba_tablespaces) ts

WHERE fr.tablespace_name = df.tablespace_name (+)

AND fr.tablespace_name = ts.tablespace_name (+) ORDER BY (fr."FREE(MB)" / df."SIZE(MB)") * 100;

exit

EOF

cat <<EOF2 >> ${tmpfile}

];

var vis = d3.select("body")

.append("svg:svg")

.data([dataSet])

.attr("width", canvasWidth)

.attr("height", canvasHeight)

.append("svg:g") //make a group to hold our pie chart

.attr("transform", "translate(" + 1.5*outerRadius + "," + 1.5*outerRadius + ")")

var arc = d3.svg.arc()

.outerRadius(outerRadius);

var pie = d3.layout.pie()

.value(function(d) { return d.value; })

.sort( function(d) { return null; } );

var arcs = vis.selectAll("g.slice")

.data(pie)

.enter()

.append("svg:g")

.attr("class", "slice");

arcs.append("svg:path")

.attr("fill", function(d, i) { return color(i); } )

.attr("d", arc);

arcs.append("svg:text")

.attr("transform", function(d) {

d.outerRadius = outerRadius + 50;

d.innerRadius = outerRadius + 45;

return "translate(" + arc.centroid(d) + ")";

})

.attr("text-anchor", "middle")

.style("fill", "Purple")

.style("font", "bold 10px Arial")

.text(function(d, i) { return dataSet[i].name; });

arcs.filter(function(d) { return d.endAngle - d.startAngle > .2; }).append("svg:text")

.attr("dy", ".35em")

.attr("text-anchor", "middle")

.attr("transform", function(d) {

d.outerRadius = outerRadius;

d.innerRadius = outerRadius/2;

return "translate(" + arc.centroid(d) + ")rotate(" + angle(d) + ")";

})

.style("fill", "White")

.style("font", "bold 12px Arial")

.text(function(d) { return d.data.value; });

function angle(d) {

var a = (d.startAngle + d.endAngle) * 90 / Math.PI - 90;

return a > 90 ? a - 180 : a;

}

</script>

</body>

</html>

EOF2

echo emailing $MY_EMAIL

( cat <<HERE; uuencode "${tmpfile}" "${tmpfile}" ) | sendmail -oi -t

From: production

To: ${MY_EMAIL}

Subject: Database info $ORACLE_SID from cron job

HERE

echo Done

Happyjohn.

Oracle 11g - How to create a bar chart graph and email it.

Just to add to the idea of producing more useful and dare I say it, beautified, results that can be emailed from the unix server to the support team, this script produces a nice little bar chart of some oracle data.

This one displays tablespaces and their percentage free.

It basically involves using HTML or SVG and the D3 javascript library (http://d3js.org/) which is open source. The script below consists of 3 sections. The first and last bits prepare the wrapper that formats the graph. The middle bit accesses sqlplus to retrieve the data in a predefined format.

There is so much you can do using this or other javascript libraries that the mind boggles. This one produces a result something like this.

It seemed while testing this, I was unable to have the output viewable straight from Outlook. So I’ve include the file as an attachment to the email.

#!/bin/sh

. $HOME/.profile 2>/dev/null

tmpfile=jhspace_`date +%d%b%Y`.html

cd /oracle/bmsusers/logs

rm ${tmpfile} 2>/dev/null

cat <<EOF1 >> ${tmpfile}

<!DOCTYPE html>

<style>

.chart rect {

fill: steelblue;

}

.chart text {

fill: red;

font: 10px sans-serif;

text-anchor: middle;

}

.axis text {

font: 10px sans-serif;

}

.axis path,

.axis line {

fill: none;

stroke: #000;

shape-rendering: crispEdges;

}

</style>

var data = [

EOF1

#--------------------------------------------------------------------------------------------------------

sqlplus -s / as sysdba <<EOF >> ${tmpfile} set lin 400 set pages 1000 set feedback off set head off

SELECT '{name: "'||ts.tablespace_name||'", value: '||round((fr."FREE(MB)" / df."SIZE(MB)") * 100,2) || '},'

FROM (SELECT tablespace_name, SUM(bytes) / (1024 * 1024) "FREE(MB)"

FROM dba_free_space GROUP BY tablespace_name) fr,

(SELECT tablespace_name, SUM(bytes) / (1024 * 1024) "SIZE(MB)", COUNT(*) "File Count", SUM(maxbytes) / (1024 * 1024) "MAX_EXT"

FROM dba_data_files GROUP BY tablespace_name) df,

(SELECT tablespace_name

FROM dba_tablespaces) ts

WHERE fr.tablespace_name = df.tablespace_name (+)

AND fr.tablespace_name = ts.tablespace_name (+) ORDER BY (fr."FREE(MB)" / df."SIZE(MB)") * 100;

exit

EOF

#--------------------------------------------------------------------------------------------------------

cat <<EOF2 >> ${tmpfile}

];

var margin = {top: 40, right: 30, bottom: 100, left: 40},

width = 960 - margin.left - margin.right,

height = 500 - margin.top - margin.bottom;

var x = d3.scale.ordinal()

.rangeRoundBands([0, width], .1);

var y = d3.scale.linear()

.range([height, 0]);

var xAxis = d3.svg.axis()

.scale(x)

.orient("bottom");

var yAxis = d3.svg.axis()

.scale(y)

.orient("left");

var chart = d3.select(".chart")

.attr("width", width + margin.left + margin.right)

.attr("height", height + margin.top + margin.bottom)

.append("g")

.attr("transform", "translate(" + margin.left + "," + margin.top + ")");

x.domain(data.map(function(d) { return d.name; }));

y.domain([0, d3.max(data, function(d) { return d.value; })]);

chart.append("g")

.attr("class", "x axis")

.attr("transform", "translate(0," + height + ")")

.call(xAxis)

.selectAll("text")

.style("text-anchor", "end")

.attr("dx", "-.8em")

.attr("dy", ".15em")

.attr("transform", function(d) {

return "rotate(-65)"

});

chart.append("g")

.attr("class", "y axis")

.call(yAxis);

chart.selectAll(".bar")

.data(data)

.enter().append("rect")

.attr("class", "bar")

.attr("x", function(d) { return x(d.name); })

.attr("y", function(d) { return y(d.value); })

.attr("height", function(d) { return height - y(d.value); })

.attr("width", x.rangeBand());

chart.append("g")

.attr("class", "y axis")

.call(yAxis)

.append("text")

.attr("transform", "rotate(-90)")

.attr("y", -40)

.attr("x", -(height / 2))

.attr("dy", ".71em")

.style("font-size", "12px")

.style("fill", "black")

.style("text-anchor", "end")

.text("Percentage Free");

chart.append("g")

.attr("class", "x axis")

.append("text")

.attr("y", height + 75)

.attr("x", width / 2)

.attr("dx", ".71em")

.style("font-size", "12px")

.style("fill", "black")

.style("text-anchor", "middle")

.text("Tablespace");

chart.append("text")

.attr("x", (width / 2))

.attr("y", 0 - (margin.top / 2))

.attr("text-anchor", "middle")

.style("fill", "black")

.style("font-size", "16px")

.style("text-decoration", "underline")

.text("${ORACLE_SID} Tablespaces - Free Space");

</script>

EOF2

( cat <<HERE; uuencode "${tmpfile}" "${tmpfile}" ) | sendmail -oi -t

From: Production_DB

To: ${MY_EMAIL}

Subject: Database info $ORACLE_SID from cron job

HERE

echo Done

Happyjohn

Wednesday, November 27, 2013

Notes on formatting emails from Oracle 11g/Unix

I like to have daily monitoring scripts running from crontab and sending me emails. When done in its most simplist….

sqlplus –s / as sysdba <<EOF > alogfile.txt

select * from jobs_report_table;

quit

EOF

mailx –s ‘Daily Monitoring’ myemail@abc.com < alogfile.txt

it has the disadvantage of being readable only in the default font of the email client. This means that columns rarely, if ever, line up. Added to this, the output looks plain and boring.

Answer, set the font and add some colour.

I have gone to great lengths in the past on this, producing complex HTML output, consisting of boxes, tables, etc. etc. But the basic idea is the same.

1. Create variables containing the formatting commands. One of starting and one for ending formatting.

eg. hilight_on='<span style="color: red; font-size: 8pt; font_weight: bold; ">'

hilight_off='</span>'

2. Perform all sql (as above) from the Unix script. Include the variables ${hiligh_on} ${highlight_off} before and after data or columns you wish to format.

3. Email the resulting file as follows.

( echo "Subject: Daily Checks"
echo "MIME-Version: 1.0"
echo "Content-Type: text/html"
echo "Content-Disposition: inline"
echo '<HTML><BODY><PRE>'
cat alogfile.txt
echo '</PRE></BODY></HTML>'
) | /usr/sbin/sendmail myemail@abc.com

The email (3) sets the main text into <PRE> which has a default of courier. This allows the columns to be more or less straight. In the example script below the PRE has been replaced and the text falls into the main BODY. In this case the font should be set manually.

There’s nothing to stop you putting the HTML span commands (or whatever) directly into you SQL statements but I prefer the variable approach. It keeps the thing neat and tidy.

Just can also put <TABLE> commands in to control the font, size, colours (background/foreground) etc. etc.

Here is the final example that emails a list of tablespaces in table format, with a horrible green background.

Points to note are

1. The prompt statements before and after the select to create the table, and end it.

2. The colsep which provides the horizontal tabbing

3. The addition column that ends and restarted the next table row. It also has the same as a column name to do the same to the headers.

#!/bin/sh

. $HOME/.profile 2>/dev/null

tmpfile=tbspace_`date +%d%b%Y`.out

sqlplus -s / as sysdba <<EOF

set pages 100

set colsep '</TD><TD>'

set und off

set feedback off

spool ${tmpfile}

column "File Count" format 999999

column "Size(MB)" format 999999999.99

column "Free(MB)" format 999999999.99

column "Used(MB)" format 999999999.99

column "Max Ext(MB)" format 999999999.99

column "% Free" format 999.99

column "% Free Ext" format 999.99

column "Graph" format a11

column tablespace_name format a20

prompt <TABLE border="1" bgcolor="#00FF00"><TR><TD>

set lin 400

SELECT ts.tablespace_name,

"File Count",

TRUNC("SIZE(MB)", 2) "Size(MB)",

TRUNC(fr."FREE(MB)", 2) "Free(MB)",

TRUNC("SIZE(MB)" - "FREE(MB)", 2) "Used(MB)",

df."MAX_EXT" "Max Ext(MB)",

(fr."FREE(MB)" / df."SIZE(MB)") * 100 "% Free",

RPAD('*', TRUNC(CEIL((fr."FREE(MB)" / df."SIZE(MB)") * 100)/10), '*') "Graph" ,

'</TD></TR><TR><TD>' "</TD></TR><TR><TD>"

FROM (SELECT tablespace_name, SUM(bytes) / (1024 * 1024) "FREE(MB)"

FROM dba_free_space GROUP BY tablespace_name) fr,

(SELECT tablespace_name, SUM(bytes) / (1024 * 1024) "SIZE(MB)", COUNT(*) "File Count", SUM(maxbytes) / (1024 * 1024) "MAX_EXT"

FROM dba_data_files GROUP BY tablespace_name) df,

(SELECT tablespace_name

FROM dba_tablespaces) ts

WHERE fr.tablespace_name = df.tablespace_name (+)

AND fr.tablespace_name = ts.tablespace_name (+)

ORDER BY "% Free";

prompt </TR></TABLE>

spool off

exit

EOF

(

echo "Subject: Database info $ORACLE_SID from cron job"

echo "MIME-Version: 1.0"

echo "Content-Type: text/html"

echo "Content-Disposition: inline"

echo '<HTML><BODY>'

cat ${tmpfile}

echo '</BODY></HTML>'

) | /usr/sbin/sendmail myemail@abc.com

echo Done

Happyjohn

Oracle 11g - Accessing Alert log details in the database

The difficulty with most monitoring scripts is that they look for errors in the alert log. And you can’t easily work out (programmatically) when the problem occurred. This is improved slightly with Oracle 11g in that instead of using the text based alert log, you have the XML alert log. But this still requires decoding as it were.

I worked at one company that grep’d the ORA- lines from the textual alert log and then emailed these to the support team if they didn’t match those previously recorded. It did this every 15 minutes. But you still needed to review the alert log to find out when the errors occurred.

This solution to the problem involves accessing the X$DBGALERTEXT view which contains all of the information you need, right inside the database. It can be a little slow with a where clause. In this example I’m retrieving any errors that occurred within a day and 5 minutes (don’t ask).

sqlplus -s / as sysdba <<EOF

set lines 132

set pages 1000

col message_Text for a80

select to_char(ORIGINATING_TIMESTAMP, 'dd-mon-yy hh24:mi:SS'), message_text

from X$DBGALERTEXT

where message_text like '%ORA-%'

and ORIGINATING_TIMESTAMP > (sysdate-1)-(5/1440)

quit

EOF

Happyjohn.

Thursday, November 21, 2013

Oracle Data Guard : Switchover/Failover

The following notes describe the switchover and failover commands for Oracle 11g. They are probably the same for previous versions.

I have named the servers A and B in order to maintain consistency where the words Primary and Standby are changeable during the process.do have Data Guard Broker, these instructions are unnecessary. Just connect to the broker on either server and issue the failover to… or switchover to…. commands and you’re away.

Of course always bear in mind that switchover is reversible, failover isn’t. Once you’ve failed over, you must rebuild the original primary as a standby andthen switch over to it.

Switchover to Standby

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

--On the test instances, I was able to switchover in less than 30 seconds.

On the Primary (A)

alter database commit to switchover to standby;

Once complete, on Standby (B)

alter database commit to switchover to primary;

alter database open;

On Primary (A) - which has been aborted by the process

shutdown immediate

conn / as sysdba

startup mount

alter database recover managed standby database disconnect;

On both

select controlfile_type from v$database;

-- (Current is the Primary)

DataGuard Failover

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

Make sure which is the Standby database.

select controlfile_type from v$database;

On the Standby, assuming you've lost the primary.

alter database recover managed standby database cancel;

alter database recover managed standby database finish;

Now issue the switchover command and then open the database

alter database commit to switchover to primary with session shutdown;

alter database open;

This leaves us with a single database instance running.

You might also need to set log_archive_dest_2_state to DEFER if the original primary server isn’t there otherwise you might get timeout delays when the instance tries to write the redo logs/archived redo log to it.

HappyJohn.

Locating the alert log (Oracle 10g - 11g)

I’ve been working on a 10g to 11g migration project, and as part of that I was tasked with migrating the scripts and crontab settings.

There were scripts for…..

Archiving the alert log and compressing it.
Searching the alert log for errors etc.
Also tidying the trace files etc.

I found that several scripts did things with the alert log and that the file paths to the alert log were hard coded, as were those for the trace files. As you should know by now, the background_dump_dest and user_dump_dest system parameters have been superseded by the diagnostic_dest parameter. Where it was simple 10g and before, its now complex at 11g+.

10g- - {background_dump_dest}/.....

11g+ - {diagnostic_diag}/diag/rdbms/{oracle Sid}/{oracle Sid}/trace/.....

And it doesn't end there because the first oracle Sid seems to be the db_unique_name in lowercase.

What I needed was a generic script that could locate the files with either setting, and this is what I came up with. This version returns the alert log file name and file path, but variations to just return the full path can be achieved simply.

$ cat locate_alert_log.sh

#-----------------------------------------------------

sqlplus -s "/ as sysdba" <<EOF > /tmp/locate_alert_log.log

set lines 200

set head off

select

decode((select value from v\$parameter where name = 'diagnostic_dest'),null,

(select value from v\$parameter where name = 'background_dump_dest')

|| '/alert_' ||

(select value from v\$parameter where name = 'db_name')

|| '.log'

(select value from v\$parameter where name = 'diagnostic_dest')

|| '/diag/rdbms/' ||

(select lower(value) from v\$parameter where name = 'db_unique_name')

|| '/' ||

(select value from v\$parameter where name = 'db_name')

|| '/trace/alert_' ||

(select value from v\$parameter where name = 'db_name')

|| '.log')

from dual

EOF

tail -2 /tmp/locate_alert_log.log | head -1

#--------------------------------------------

$ more `./locate_alert_log.sh`

Happyjohn

ORA-4030: out of process memory when trying to allocate 64544 bytes (sort subheap/sort key)

An oracle ORA-4030 occurred when we were testing a failover to a Data Guard physical standby.

We checked the trace files but they didn't give us much other than the statement concerned. What was really odd about this was that the query had worked fine on the primary database and the oracle system parameters where all the same (apart from those specifically relating to the Data Guard setup).

We found that by reducing the sga_target and pga_aggregate_target that the query seems to work! But as soon as more users came on, the problem reoccurred.

The following parameters all seemed to be concerned with this problem.
SGA_MAX_SIZE, SGA_TARGET, PGA_AGGREGATE_TARGET, SORT_AREA_SIZE, HASH_AREA_SIZE. We hadn't set MEMORY_MAX_TARGET or MEMORY_TARGET having upgraded from 10g and not got round to it yet. We also considered the hidden parameter _PGA_MAX_SIZE.

Details: the SGA was set to 7Gb and the PGA to 1Gb. We assumed this meant that approximately 8Gb of the total 25Gb was being used by oracle. On inspection, pretty much all of the memory was being used by oracle.

Reading about pga_aggregate_target, you'll find that it represents the maximum amount of memory used by ALL of the user processes. But this isn't true. It's only a target. We had assumed that if you add up the size of the sga and the pga you get the total memory consumption. However this isn't the case. Oracle seems happy to take as much memory as it can.

Solution: the oracle user (AIX) had insufficient ulimit settings. At 11.2 the installation guide recommends unlimited setting for FILE, CPU, DATA, STACK and Real Memory. This means the following (as root)...

tail /etc/security/limits
oracle:
fsize = -1
core = -1
cpu = -1
data = -1
rss = -1
stack = -1
nofiles = -1

These parameters are dynamic so there is no need to restart the database or the server, however, this should have been dealt with in the installation.

*** UPDATE: the parameters for ulimit might not be dynamic. We found that old logins to the server didn't assume the new values when we connected to the database. Also the web servers weren't showing any improvement so they may need to restart their connections.

Some other bits......

1) ulimit -Ha shows the maximum default setting but ulimit -a shows the actual settings.

2) ipcs -bm shows the memory in use by the SGA. Look for the SEGSZ (bytes) owner by the oracle user.

3) the memory used by the PGA can be assessed thus. It's return the memory in Mb.

ps -elf | egrep " oracle${ORACLE_SID} | ora_.*_${ORACLE_SID} " | grep -v grep |
awk '{sum += $10} END {print sum/1024}'

(You might need to change the quotes if you cut and paste this).

This is an estimate and might by overly large. Remember the memory sizes are constantly changing.

4) svmon -G shows the server memory usage. Unfortunately the memory sizes given are in pages on AIX, so they must be multiplied by 4096 before being divided by 1024*1024 to get Mb.

5) topas on AIX is very useful. It's like 'top' on Linux. Use 'c' to switch between CPU views and 'M' to display the memory usage.

HappyJohn.