AWR compare script

I don't know if anyone else has seen this, but with 12c, you can't compare before and after AWR reports.  I found this quite annoying so I used the oracle scripts, and tweaked them.. Here is the script I use.

http://dl.dropbox.com/u/23998484/awr_bsg.sql

Here are the really intesting, that you can use for any AWR period compars.

   define  num_days     = 0;
      define  dbid         =1595564914; 
      define  instance_numbers_or_ALL    = 'ALL';
      define  begin_snap   = 47097;
      define  end_snap     = 47111;
      define  num_days2    = 0;
      define  dbid2        = 1342862996;
      define  instance_numbers_or_ALL2    = 'ALL';
      define  begin_snap2  = 45907;
      define  end_snap2    = 45921; 
      define  report_type  = 'html';
      define  report_name  = /tmp/awr_gg.html
      define top_n_files        = 50;
      define top_n_segments     = 50;
      define top_n_services     = 50;
      define top_n_sql          = 100;
      @@?/rdbms/admin/awrgdrpi

Note all the Juicy goodies..  I am choosing 'ALL' for the instances in my RAC cluster.  Also notice I am overriding the number of top sql, and other things in this file.  The default number of top sql is never enough

This can be very useful to get a more meaningful AWR report in a rac environment.

Monitoring Goldengate through sql

I have been working on implementing GoldenGate.  Golden Gate works with Oracle, but I wanted to be able to monitor it's status within an Oracle session, and then use a database link to remotely query the status.. Once I have all this in place, I want to create a web page through Apex, that does a union of all my GG environments onto a single screen.. real time ! nifty Eh.

This is how I went about doing it..

1)  The basis for it all is the "info all" command executed within GGSCI.  The output looks like this.

GGSCI (orclbox) 10> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING
EXTRACT     STOPPED     S_EXTR      00:00:00      00:10:38

in this example you can see that the manager is running, but the extract is stopped.  I took the output of this command and created my own command script..

ggsci_status.sh

#!/bin/bash
export ORACLE_SID=${ORA_SID}
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 
export ADR_HOME=/u01/app/oracle/diag/rdbms/${DB_UNIQUE_NAME}/${ORACLE_SID}
export PATH=${ORACLE_HOME}/bin:${PATH_ORIG} 
export LD_LIBRARY_PATH=$ORACLE_HOME/lib 
export PATH=$PATH:/dbfs/gg/ggs11
 cd /dbfs/gg/ggs11
 
cd /dbfs/gg/ggs11 
ggsci </tmp/ggsci.log 2>/tmp/ggsci.log
info all
EOF

cat /tmp/ggsci.log  | grep EXTRACT
cat /tmp/ggsci.log  | grep MANAGER
cat /tmp/ggsci.log  | grep REPLICAT
 

The output of this script is the 2 lines from above that show the statuses of those processes.

MANAGER     RUNNING
EXTRACT     STOPPED     S_EXTR      00:00:00      00:10:38

So now for step 2... We have the statuses we are looking for as the output of a script.. What to do ?  External tables with a preprocessor (sorry I believe this is an 11.2 feature)..

First create a directory and put your script in that directory.  (this is within oracle). I called mine SP_COPY

Here is how I defined my table creation to read the output of the above script.

create table bgrenn.ggsci_status
(gg_type varchar(20),
 status  varchar(20),
 lag_time       varchar(20),
  checkpoint    varchar(20)
)
 organization external
(type oracle_loader
  default directory SP_COPY
  access parameters
(  
   records delimited by newline
   preprocessor SP_COPY:'ggsci_status.sh'
   badfile SP_COPY: 'ggsci_status.bad'
   logfile SP_COPY: 'ggsci_status.log'
    fields terminated by whitespace
     missing field values are null
   (
   gg_type,
   status,
   lag_time,
   checkpoint
  )
)
   location ('ggsci_status.sh')
)
reject limit unlimited;

 

Now select against this table and you will see the columns from the output of your script appear as columns in the table (2 rows for this example).


Finally .... Step 3.. create a database link to this database and do a select * from ggsci_status@mylink.

There you go.  How to dynamically show the status of Golden Gate processes through a database link on a remote database.


NOTE : If the database is a RAC cluster with GG running on only one node, you need to specify the SID to ensure you are looking at the correct node running GG.

Enjoy...

Hadoop (again)

I know I've blogged in the past that I am working on implementing Hadoop. Here are 3 articles that should explain why.

 First
http://www.nytimes.com/2011/12/18/sunday-review/the-internet-gets-physical.html?_r=1&pagewanted=all

This is a great article from the NY times, explaining that sensors are turning up everywhere. Of course more sensors mean more data! Lots more data. So how do we collect all this data and process it ?? http://www.calxeda.com/ Calxeda !! These are 5 watt processors that can be scaled up to thousands of nodes (yes I said THOUSANDS). And I know what you are saying. So what do we do this data ? So what ?

Here is a great article on how to tie it all together.

http://blogs.oracle.com/datawarehousing/entry/understanding_a_big_data_implementation

So there you are.. I think this is the vision of the future, and if you are not looking at these technologies, and how they tie together, you are missing the next big leap that is happening in the IT field. Our jobs as IT specialists will become even more important as we become an integral part of all our companies (whatever it is) business process.