Thanks to this blog post and metalink doc 741004.1, learned some fun stuff about how an Oracle agent determines the amount of free memory available on a Solaris host.

ORACLE_HOME=/u01/app/oracle/product/agent11g
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
export PATH

$ORACLE_HOME/bin/nmupm osLoad | awk -F"|" '{print $14}'

Now how to figure out why this would be so wildly different than vmstat...

Insert update and deleting records in a view

Recently a vendor gave me a vague request to grant "write access" to a view. Remembering an error a user had shown me in the past, I replied off the cuff that one can't simply write to a view.

I created the following test to prove that I was right, and you'll see that I proved myself wrong:

drop user u_owner;
create user u_owner identified by pass2pass;
alter user u_owner default tablespace users;
alter user u_owner quota unlimited on users;
grant create session to u_owner;
grant create table to u_owner;
grant create view to u_owner;

drop user u_viewer;
create user u_viewer identified by pass2pass;
grant create session to u_viewer;

conn u_owner/pass2pass;

create table tb_test (v_int1 int);
create view vw_test as select v_int1 from tb_test;
grant select, insert, update, delete on vw_test to u_viewer;

conn u_viewer/pass2pass;

select * from u_owner.vw_test;
insert into u_owner.vw_test values (1);
select * from u_owner.vw_test;
update u_owner.vw_test set v_int1 = 2;
select * from u_owner.vw_test;
delete u_owner.vw_test where v_int1 = 2;
select * from u_owner.vw_test;
conn / as sysdba;
drop user u_owner cascade;
drop user u_viewer;

exit;

So as it turns out you absolutely can insert / update / delete into views. Still trying to make sense of it, but it *is* possible.

Grid control claims App Server 10.1.2 is down wen console says it's up

I'm currently going through moving all of my targets from grid control repositories and in the process of doing that I'm cleaning up anything that doesn't need to be there, or anything that reports as being down. For some reason I like to see the fully green pie chart.

I had added one of my oracle mid tiers that had a version of 10.1.2.3 running to support our forms application and grid control reported that the app server was down, but I knew for a fact it was not. All the subcomponents were reporing as up, opmnctl reported everything up, and the iasconsole on the server itself reported everything was up. In summary, it was up.

After much digging through perl code and metalink docs I have resolved the issue and figured out it had to do with Solaris 10 security of /usr/ucb/ps command not being able to report extended process info without being the owner of the process. There was a pretty good metalink document that put me on this trail, but didn't solve the problem itself. Here's a metalink ID roundup for what helped (or didn't but was related).


395013.1 - Application Server shown as Down in Grid Control even though all of its Components are shown as up
276350.1 - How to Enable the Metric Browser/Agent Browser for the Oracle Management Agent [Video]
752944.1 - CMSDK ORACLE_IAS TARGET DOWN WHEN IT IS UP

Enabling the metric browser was essential for this, in addition to running the perl scripts manually from the host.

So the problem ended up being this, the agent checks for forms processes on the host and reports back the sid.
Run this as your agent owner, and then also as root to see the difference.

/usr/ucb/ps -axww | grep oc4j.jar | grep OC4J_BI_Forms | grep <$ORACLE_HOME>

Since the process is owned by a different user than the agent, it can't get the extended process info like root can. So to solve that two things had to be done.

First set up sudo privileges for your agent user to run /usr/ucb/ps as root

gridagent          ALL=(root)NOPASSWD: /usr/ucb/ps

Second, modify the perl code in $AGENT_HOME/sysman/admin/scripts/formsinit.pl to use sudo.
Change

elsif ( $os eq "SunOS" )
{
    $PS = "/usr/ucb/ps -axww";

To

elsif ( $os eq "SunOS" )
{
    $PS = "sudo /usr/ucb/ps -axww";

Restart the agent force an upload and check all your metrics again. You can do that via the metric browser, the commandline or wait for grid control to register the target again.

If anyone finds a metalink document that covers this exactly please add something in the comments.

OCM patch required even though you're not using OCM

While installing OAS 10gR3 patch 8626084 to upgrade OAS to 10.1.3.5, I received an error that step "Run One-off OPatches" had failed.

Looking at the log file, I found the following:


This is a OCM patch.
Home has OCM installed but not configured.


To run in silent mode, OPatch requires a response file for Oracle Configuration Manager (OCM).
Run /u01/app/oracle/product/oas10gr3/OPatch/ocm/bin/emocmrsp to generate an OCM response file. The generated response file can be reused on different platforms and in multiple OPatch silent installs.

To regenerate an OCM response file, rerun /u01/app/oracle/product/oas10gr3/OPatch/ocm/bin/emocmrsp.


ERROR: OPatch failed because of cmd. args. problem.


Doing a bit of digging, I found this recommendation to go ahead and run setupCCR in disconnected mode.

What's frustrating is that I requested that OCM not be installed or configured.

The following did the trick and I was able to complete the patch installation.

/u01/app/oracle/product/oas10gr3/ccr/bin/setupCCR -s -d


Clean up old xauth entries

In a followup to a previous post on forwarding x sessions and su, here's a quick way to clean up old xauth entries.

> xauth list | cut -f1 -d\  | xargs -i xauth remove {}

You'll recall that cleanup will not happen automatically for the sessions that got su'ed to, so if you have several entries hanging around the one-liner will make quick work of them.

On the subject of support requests

Last night I was working on providing some information to a tech on MOS, and part of their request was screenshots. While I don't mind taking screenshots, it's actually a little quicker to take a screencast.

I used to use CamStudio or Wink, but now that I'm primarily using a Mac, I find that it's much simpler to use the java app at http://screencast-o-matic.com/.

Screencast-o-matic is drop dead simple to use, creates a reasonably small file, and allows you to provide more information to Oracle support with less effort. Edit: It's free for up to 15 minute long files.

Highly recommended.

Oracle database file migration script

I recently had a project to migrate datafiles to our new san array, and part of that project I wanted to split apart each of the databases to their own mount points to allow mirror splits to be easier for quick backups. To perform this I pretty much had two options to move all the datafiles (60-70 total/database). I could dump the controlfile to trace, make the modifications then recreate the controlfile or I would have to move the datafiles on the os and perform alter database statements.

I wanted to just go the route of recreating the controlfile because it would have been easier to do mass edits of one file but these particular databases were not registered to an rman catalog, and I did not want to lose backup history even though these were just test environments cloned on a regular basis.

So, the choice I had left was to move files and do the alter databases. After doing a bit of cursory searching I didn't find any scripts to generate the statements for me, so I put together a driver script that made all the necessary steps. Hopefully this will save someone time in the future with a script to start from. This was done against 10gR2.

Some assumptions:

• The datafile locations assume ofa compliant structure "/uxx/oradata/SID/"
  
• For the driver script being created the destination will be all the same, you can change this after the script has been generated.
  
• You need to change the source and destination.
  

set serveroutput on size unlimited for wra;
set linesize 200 
set feedback off
set echo off
set timi off
set trimspool on

spool &&SID._generate

CREATE TABLE weg_temp (
step INTEGER,
seq integer,
STATEMENT varchar2(200));

-- Generate move the controlfile script
DECLARE
   -- REGEX MATCHING PARMS
   source          VARCHAR2 (10) := '/u0[1-3]';
   destination     VARCHAR2 (5) := '/u06';

   CURSOR ctrl_file_cur
   IS
      SELECT name FROM v$controlfile;

   CURSOR redo_cur
   IS
      SELECT MEMBER FROM v$logfile;

   CURSOR datafile_cur
   IS
      SELECT name FROM v$datafile;

   CURSOR tempfile_cur
   IS
      SELECT name FROM v$tempfile;

   ctrl_file_rec   ctrl_file_cur%ROWTYPE;
   redo_rec        redo_cur%ROWTYPE;
   curholder       VARCHAR2 (500);
   sqlstatement    VARCHAR2 (2000);
   unixstatement   VARCHAR2 (2000);
   loopcount       INTEGER;
   regexbuffer     varchar2(100);
BEGIN
   sqlstatement := 'ALTER SYSTEM SET control_files=';
   loopcount := 0;
   
   -- Populate the table with the section headings and static steps
   insert into weg_temp values  (1,0,'-- 1. Change the controlfile locations in the spfile');
   insert into weg_temp values  (2,0,'-- 2. Shutdown the database');
   insert into weg_temp values  (2,1, 'shutdown immediate;');
   insert into weg_temp values  (3,0,'#  3. Move the control files on the os');
   insert into weg_temp values  (4,0,'#  4. Move the redo log files on the os');
   insert into weg_temp values  (5,0,'#  5. Move the datafiles on the os');
   insert into weg_temp values  (6,0,'#  6. Move the tempfiles on the os');
   insert into weg_temp values  (7,0, '-- 7. Start the database in mount');
   insert into weg_temp values  (7,1, 'startup mount;');
   insert into weg_temp values  (8,0, '-- 8. Change redo log files in the database');
   insert into weg_temp values  (9,0, '-- 9. Change datafiles in the database');
   insert into weg_temp values  (10,0, '-- 10. Change tempfiles in the database');
   insert into weg_temp values  (11,0, '-- 11. Open the database and hope it works.');
   insert into weg_temp values  (11,1, 'alter database open;');
   
   -- Spacing for file
   insert into weg_temp values  (2,-1,NULL);
   insert into weg_temp values  (3,-1,NULL);
   insert into weg_temp values  (4,-1,NULL);
   insert into weg_temp values  (5,-1,NULL);
   insert into weg_temp values  (6,-1,NULL);
   insert into weg_temp values  (7,-1,NULL);
   insert into weg_temp values  (8,-1,NULL);
   insert into weg_temp values  (9,-1,NULL);
   insert into weg_temp values  (10,-1,NULL);
   insert into weg_temp values  (11,-1,NULL);
   
      

   OPEN ctrl_file_cur;

   LOOP
      FETCH ctrl_file_cur INTO ctrl_file_rec;

      EXIT WHEN ctrl_file_cur%NOTFOUND;

      IF loopcount > 0
      THEN
         sqlstatement := sqlstatement || ', ';
      END IF;
     
      -- Calculate the destination filename
      regexbuffer := REGEXP_REPLACE (ctrl_file_rec.name, source, destination);
      sqlstatement := 
            sqlstatement
         || ''''
         || regexbuffer
         || '''';

      insert into weg_temp values (3 ,loopcount+1,'mv ' || ctrl_file_rec.name || ' ' || regexbuffer);
      loopcount := loopcount + 1;
      
   END LOOP;

   CLOSE ctrl_file_cur;
   -- Finish out the sqlstatement
   sqlstatement := sqlstatement || ' SCOPE=SPFILE;';
   -- Put generated sql statement into the table.
   insert into weg_temp values (1,1,sqlstatement);

   OPEN redo_cur;

   -- Reset loopcount back at 0
   loopcount := 0;
   LOOP
      FETCH redo_cur INTO curholder;

      EXIT WHEN redo_cur%NOTFOUND;
      regexbuffer := REGEXP_REPLACE (curholder, source, destination);
      
      insert into weg_temp values (4 ,loopcount+1,'mv '|| curholder|| ' '|| regexbuffer);
      
      insert into weg_temp values (8 ,loopcount+1, 'ALTER DATABASE RENAME FILE ''' || curholder || ''' TO ''' || regexbuffer || ''';');

      loopcount := loopcount + 1;
            
   END LOOP;

   CLOSE redo_cur;

   OPEN datafile_cur;

   -- Reset loopcount back at 0
   loopcount := 0;
   
   LOOP
      FETCH datafile_cur INTO curholder;

      EXIT WHEN datafile_cur%NOTFOUND;

      regexbuffer := REGEXP_REPLACE (curholder, source, destination);     
      insert into weg_temp values (5 ,loopcount+1,'mv '|| curholder|| ' '|| regexbuffer);
      
      insert into weg_temp values (9 ,loopcount+1, 'ALTER DATABASE RENAME FILE ''' || curholder || ''' TO ''' || regexbuffer || ''';');

      loopcount := loopcount + 1;
            
   END LOOP;

   CLOSE datafile_cur;


   OPEN tempfile_cur;
   -- Reset loopcount back at 0
   loopcount := 0;
   
   LOOP
      FETCH tempfile_cur INTO curholder;

      EXIT WHEN tempfile_cur%NOTFOUND;

      regexbuffer := REGEXP_REPLACE (curholder, source, destination);     
      insert into weg_temp values (6 ,loopcount+1,'mv '|| curholder|| ' '|| regexbuffer);
      
      insert into weg_temp values (10 ,loopcount+1, 'ALTER DATABASE RENAME FILE ''' || curholder || ''' TO ''' || regexbuffer || ''';');
      loopcount := loopcount + 1;
      
   END LOOP;

   CLOSE tempfile_cur;

   commit;
END;
/

spool off;

spool &&SID._script.txt

DECLARE
   CURSOR weg_temp_cur
   IS
        SELECT statement
          FROM weg_temp
      ORDER BY step, seq;

   wtr   weg_temp_cur%ROWTYPE;
BEGIN
   OPEN weg_temp_cur;

   LOOP
      FETCH weg_temp_cur INTO wtr;

      EXIT WHEN weg_temp_cur%NOTFOUND;
      DBMS_OUTPUT.put_line (wtr.statement);
   END LOOP;

   CLOSE weg_temp_cur;
END;
/

spool off;

drop table weg_temp;
exit;