Grid Control - Alerts By type

Well and now, getting the information for all the [same] alerts across all targeted databases, you can use the following query (knowing the view, it is easy to get the rest of the different types of information). For this specific query I wanted the invalid objects order by number. Example:
"237 object(s) are invalid in the PUBLIC schema."


If you check the MGMT$ views on the sysman schema, you get all the necessary information. After this post:
http://pinelasgarden.blogspot.co.uk/2012/06/grid-control-target-databases-size.html
I leave you with the following query (the message_params field has the varying  info).



select target_name, message_params,message
from MGMT$ALERT_CURRENT 
where target_type='oracle_database'
and metric_name='invalid_objects'
order by to_number(substr(message_params,1,instr(message_params,'&')-1)) desc;

Grid Control - Target Databases Size Report

After going through all the MGMT$ views in the sysman schema, I finally found out how to get the size of all monitored targets (oracle databases) in a single table (I was only given access to GC and not any DB).
And after that, how to create a report based on that query in Grid Control (10g).
So here are the steps to create a report and the query for the report. 


Basically you go to reports, click create, add a "table from sql" element then you edit the element and paste the sql statement and with that and one or two more slight touches (names and so forth), you are good to go.

And the query is:

select host_name,target_name, 

ROUND(sum(tablespace_size)/1024/1024/1024,1) totalGB,

round(sum(tablespace_used_size)/1024/1024/1024,1) usedGB,

round((sum(tablespace_used_size))/(sum(tablespace_size))*100,1) usedPerc

From MGMT$DB_TABLESPACES

group by host_name,target_name order by 5 desc

This gives you the host and target name, the total GB allocated, the used GB, and the used percentage.

wordle - Pinela's garden

Bem, utilizei a ferramenta do wordle
http://www.wordle.net
para analisar o meu blog e aparentemente eu escrevo "foi" muitas vezes (e agora mais uma).
Assim como "the", "mais", "não", "mas", "muito".

Pronto, viver e aprender. Muito! :)

-- ============================= --

Well, switching to english now, for context.
I came across wordle in this article by a NYT journalist:
http://www.niemanlab.org/2011/10/word-clouds-considered-harmful/

To quickly explain his feelings about word clouds, here is a quote from the article: "Every time I see a word cloud presented as insight, I die a little inside.". Enough said.
He suggests alternativa display methods for word frequency, like the article in the NYT about graduation speeches:
http://www.nytimes.com/interactive/2011/06/10/education/commencement-speeches-graphic.html

Yes, the visualization, is really good.
The NYT journalists are effectively very good in visualizations. They have won several prizes.

And yes, I should remove the "stop words" from my word cloud, to get some more insight on topics, and less on used words. :)

[EN] Facebook buys Instragam - wow!!

Well, this was a really impressive deal. In 2 years (2010-2012) they were bought by 1 billion dollares.
Impressive. If you have great ideas, and you follow through (important this last part) success will come.
But you have to be careful, as pointed out in the last link.
Sure you can have a great website, but a real company (with a physical product, employees and paying taxes) might be a safer and longer bet.
Unfortunately (on my opinion) that is not so easy (and that's why it is better): Building a factory, a product, hiring workers and so forth, takes time and money.
So it is simple why a website is an easier bet. Nonetheless, if it was easy, everyone would do it :)

the news
http://techcrunch.com/2012/04/09/facebook-to-acquire-instagram-for-1-billion/
"Facebook has just finished a deal to acquire mobile photo sharing app Instagram for approximately $1 billion in cash and stock. Instagram will remain an independently branded standalone app that’s separate from Facebook, but the services will increase their ties to each other. The transaction should go through this quarter pending some standard closing procedures"


the why
http://gigaom.com/2012/04/09/here-is-why-did-facebook-bought-instagram/
"Facebook was scared <obscured> and knew that for first time in its life it arguably had a competitor that could not only eat its lunch, but also destroy its future prospects. Why? Because Facebook is essentially about photos, and Instagram had found and attacked Facebook’s achilles heel — mobile photo sharing."


the "don't feel bad"
http://techcrunch.com/2012/04/15/an-open-letter-to-those-not-employed-at-instagram/
"This happens about every half a decade. The first mover in a space gets taken out in record time with a ridiculous valuation and the founders look like epic geniuses. Do you remember YouTube? Sure you do. But what about Revver, Metacafe, Guba and Veoh? I didn’t think so."

[EN] Joseph Kony - please remember his name and spread the word

http://aeiou.expresso.pt/e-obrigatorio-ver-este-video-ate-ao-fim-custe-o-que-custar=f710025
http://kony2012.s3-website-us-east-1.amazonaws.com/

72 hours ago I had no idea who was this person. A friend refered him as a target on social networks. But I didn't know why.
I then watched the WHOLE following video and it really made an impact on me.
Read the video. You will understand what I mean.

However if you think you don't have the time to watch it, let me summarize the problem for you. This way there is no excuse:
- Joseph Kony is the leader of a rebel group in Africa
- he kidnapps children. Turns boys into soldiers, makes them kill and mutilate other children. Turns girls into sex slaves.
- he has kidnapped more than 30000 children
- kids are scared to sleep because they are afraid of being kidnapped
- he has kidnapped kids for 20 years
- he fights to maintain his power. JUST THAT!

Like a friend of mine says "change the world, one person at a time". Well, we can make a difference by making this name known worldwide. I hope you spread the word. Make others know this name. Let's start here with Joseph Kony.

Thank you.

[EN] - Data analysis - Really Cool visualizations

http://flowingdata.com/2012/03/06/data-in-a-human-context/


TEDxVancouver - Jer Thorp - The Weight of Data

This is a really interesting video.

The tool that apparently he built (@8:30) is really, really, REALLY impressive. I would love to know how he did that: in what program / language.

-- update --

well, after reading through the first link (all of it) I found what he used:

http://processing.org/

http://mongodb.org/

This information was subtly hidden in the next sentence:

"Cascade was developed by R&D using open source tools including Processing and MongoDB."

[EN] - Big Data - NYTimes - numbers are cool now

http://www.nytimes.com/2012/02/12/sunday-review/big-datas-impact-in-the-world.html?pagewanted=1&_r=1


"GOOD with numbers? Fascinated by data? The sound you hear is opportunity knocking."
This sentence could only point more to me if it spelled out my name!!! :)


"Veteran data analysts tell of friends who were long bored by discussions of their work but now are suddenly curious. “Moneyball” helped, they say, but things have gone way beyond that. “The culture has changed,” says Andrew Gelman, a statistician and political scientist at Columbia University. “There is this idea that numbers and statistics are interesting and fun. It’s cool now.”"


And an important date:
"In economic forecasting, research has shown that trends in increasing or decreasing volumes of housing-related search queries in Google are a more accurate predictor of house sales in the next quarter than the forecasts of real estate economists. The Federal Reserve, among others, has taken notice. In July, the National Bureau of Economic Research is holding a workshop on “Opportunities in Big Data” and its implications for the economics profession."


Relevant links:
http://www.unglobalpulse.org/
http://www.weforum.org/reports/big-data-big-impact-new-possibilities-international-development

[EN] - Big Data explained by Oracle

Big Data has been around for several years apparently, but I myself have only come across it mid last year due to a research paper (THANK GOD).
It is a extremely interesting subject, but I still haven't been able to achieve a simple mapreduce job. But I think I'm getting closer.


Meanwhile, I stumbled into some official Oracle Videos that give some interesting views.

Damn. The video insert feature is really stupid sometimes. Can't put the final video here. So here is the link:

http://www.youtube.com/watch?v=J2H6StHNJ18

[EN] SSIS - Starting a new path...

It's funny how when you start to adventure on a new area, at every step, you learn something.
Other funny thing is that you find new "usual" sites, where everybody goes to when they have a problem.


This as happened to me in the last few months, as I took some projects involving SSIS (Sql Server Integration Services). Every detail was a challenge and every single one came with doubts regarding the correct direction to take. 



The main challenge was: 
- "Can this be done this way? Should I keep searching for the solution for THIS method, or should I find another method to search for? Maybe this can't be done this way."
How do you know when to give up on that road, and go back to the intersection to pick another one? You probably don't. You just have to decide something and go with it. :)


BUT, every single one, was bypassed with "flying colors". 
You will see what I mean in a few days. I am going to do some posts on the aspects of reading excels (for now) and loading them into a database table.
The excels will provide enough challenges for the posts.


I will approach subjects like:
- excel source shows null columns
- bids says the provider is not registered (JET or ACE)
- how do you filter by numeric column vales
- what about excel files with dynamic /varying column numbers
- limitations of the components
- and so on...


Stay tuned.
Cheers.

[DBA] - asktom , CWs

well, latelly I've been really busy on tunning some heavy queries on a great number of rows (using all of them).
So, I came across one of the pages from the Oracle SQL GURU, Tom Kyte
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:7413988573867

which came crashing down on some of my beliefs (or "CWs - Conventional Wisdom" as he calls them). He says:

"Other CW's (that are all 100% wrong)

o indexes mean fast=true, if the optimizer doesn't use them, the optimizer has
  a bug

o indexes need to be rebuilt frequently or on a scheduled basis

o rebuilt indexes are faster, smaller and better

o segments should have few extents -- double digit extents are really bad for
  performance

o all cursors in code should be explicit cursors, implicit cursors are slow and
  bad

o select count(1) is better then select count(*)

o procedural code is faster then doing it in SQL

o adding more CPU will make our systems faster

o index space is not reused

o nologging stops all redo log from being generated on that segment

o the most selective fiedls must be first in an index

o you should commit frequently to save resources and time

o a cold backup is better then a hot backup"

[DBA] - mysql update with self values

interesting how a simple:

update tab1 set field1=(select field1 from tab1 where field1=field2)

cannot be simply done in mysql. you need some workarounds like another duplicate table or as demonstrated at
http://www.xaprb.com/blog/2006/06/23/how-to-select-from-an-update-target-in-mysql/
since mysql "materializes subqueries in the FROM clause (“derived tables”) as temporary tables, wrapping the subquery into another inner subquery in the FROM clause causes it to be executed and stored into a temporary table, then referenced implicitly in the outer subquery.  "

The error message is:
" ERROR 1093 (HY000): You can't specify target table '<whatever>' for update in FROM clause."

Strange.

[DBA] - OBIEE 11g - BI admin Tool - Connection Failed

In order to connect the BI Administration Tool to a DB by OCI 10g/11g a few steps are required to avoid the connection Failed error..

1 - you need to put a tnsnames.ora on the <BI MDW Home>\network\admin folder..

2 - you need to place the <BI MDW Home>\bin directory on the PATH environmental variable.

3 - if you connect with sys (just sys) he will return the "the connection has failed" error without the useful information regarding the "as sysdba" argument.

In the shown example, my <BI MDW Home> is E:\mdw_home\Oracle_BI.

After these changes, AND restarting the BI tool, everything should be fine.

Hope that helps.

[DBA] - OBIEE 11g (1.1.1.3) (post) installation errors

OBIEE is a very interesting tool. If all goes well during the install process, you don't have to worry about anything. Just input the data, and next, next, next.
If, however problems due happen, it can be pretty boring to find out why.
So here are some helpfull pointers:

1 - RTFM - http://www.oracle.com/technetwork/middleware/bi-enterprise-edition/documentation/index.html

2 - pay atention to your hosts file. Don't have any entries with the hostname and IPS other than 127.0.0.1 (unless you definitely know you need it).

3 - the diagnostics directory under <MDW_HOME>\instances\instance can be very useful when diagnosing the separate components.

4 - most (stupid = not your fault :P) problems during install process may happen simply due to memory shortage. The page at http://download.oracle.com/docs/html/E18558_01/fusion_requirements.htm indicates no less than 2GB of physical memory and 4GB of total memory (physical + swap). Well, my VMs never had more than 2GB, and the swap was around 2.5GB and the virtual memory was always in the dog house. So bear this in mind.
I had problems that just went away after a reboot and a restart of the install process like (creating ASinstance, Creating BI components, etc)

5 - problems during "start process coreapplication_obips1" can be tested by the "sawping" comand. this because the Presentation Services webpage (by default <hostname>:9704/analytics) contact with the sawserver and if there are problems with the port, or the services aren't up. That and the DSN created by OBIEE can have the IP instead of the hostname in it's configuration. Check it out.
Also, this is a very demanding memory process (atleast so it seemed by taskmanager's behaviour).
Memory problems could occur.

6 - problems during "start process coreapplication_obijh1" can be that you're machine is having problems with the 1024M parameter in <MDW_HOME>\instances\instance\config\opmn\opmn\opmn.xml. you can switch it to 768M (http://forums.oracle.com/forums/thread.jspa?threadID=1124066)

7 - when you reboot, you have to give it some time to start all the services. Even after the browser window opens with the Analytics page and the shell is closed. use <MDW_HOME>\instances\instance1\bin\opmnctl status to check the components. Components with alive are ok, with init are still starting (give them a chance) and with down will not start on their own. You can use the Admin Pages (EM or Console) or the command:
opmnctl startproc ias-component=<component name on status list>.

hope it was helpful.
BR.

[EN] - OEL 5.6 + 11gR2 + OEM = Streams? simple and easy?

well this saturday I updated my test environment and created a VM with OEL 5.6 and Oracle Database 11gR2 (I tried OEL 6 but that was a little frustrating - more on that later). I had previous experience with 11gR2 but never had a permanent VM.
I then migrate my data from the 10gR2 DB to this one via Data Pump. Worked great.

After that I went browsing Enterprise Manager (OEM) and found this interesting option:

Very interesting! One of these days I'll duplicate my VM, change the DB name (for simplicity) and try this option. Let's see how it goes.

More information in the near future :D

EN] - HAA in Oracle vs MySQL - part 2

filling in the gaps..
http://pinelasgarden.blogspot.com/2011/06/en-haa-in-oracle-vs-mysql.html

I said that "(...) mysql replication which had asynchronous data transmission and this does not garantee me data persistence (yet). " and I've found that for the Mysql Cluster, you don't need the normal server or replication. It does thant synchronously by itself.
Very nice.

[EN] - RMAN set newname slowwwwwwwwwww

Well this was an interesting solution....

Problem:
A restore on a vm with windows for a 10.2.0.3 db, was taking something lik 50/60 seconds between the setnewname commands. I had 120 so, do the math. This was seriously cramping my style (and annoying me).

Solution:
I googled, then I metalinked (:D) and then I found the note
SET NEWNAME takes 2 minutes per file [ID 554923.1]
which told me to
"1) Move SET UNTIL TIME command just before RESTORE DATABASE command.
2) Move all SQL commands that are used to rename log file names, before SET UNTIL TIME command. "

Actually the note was dead on. I had the set until command before the newname commands.
The google part of the research, had already discovered other dbas with a similar problemas (although with duplicates) and their research found that the slow querys were on the v$log_history view.
http://ronr.blogspot.com/2009/07/rman-duplicate-database-slow.html
This indicated something related to archive logs.
Honestly I didn't think that the command's position mattered.
Live and learn. :D

[EN] - HAA in Oracle vs MySQL

well, the other day an interesting question came up, which led me to think "what if I wanted an EBS-like architecture with high availabity, but with MySQL?".

I have all the knowledge I need regarding what I need and how to configure and install all the components for such an architecture with oracle technology: RAC, shared storage, network, Web servers all this and more.
But how do I do this with MySQL? This is an interesting question for knowledge sake as well as for simple discussion on the benefits of the alternative.
After googling, I came across two new concepts (rather obviously named): MySQL Cluster and MySQL Replication.
mysql cluster (loosing the case text) looked like Oracle Real Application Cluster.
http://www.mysql.com/products/cluster/architecture.html

What I'm still questioning are the "data nodes". I need some more research to find a relation to the database nodes on RAC. This because I found mysql replication which had asynchronous data transmission and this does not garantee me data persistence (yet). But, I love to learn.

If these Data nodes use mysql replication, I need to read some more :D

more on this in the future.

[EN] - excel dynamic pivot table

There is a frequent problem with frequent data analysis. And anyone that as tried this has run into this problem.
Regularly, the structure of the data is persistent (you have X number of columns, with the same type of data) but the amount is not (you can have Y rows one day/week/month and Y+1, Y-1, Y+Z..).
So when you use one of the greatest excel tools (now also available on google docs), the pivot table, every time the amount of data changes, you have to redefine the data source.

this isn't very time consuming, but it surely is ANNOYING!!! So how to automate this? Well with the named range feature (example here http://pinelasgarden.blogspot.com/2011/05/en-excel-dynamic-chart-range-with-named.html) you can have a dynamic pivot table. After adding more rows, all you need is to refresh the data source. That's all.

In the following image the needed steps are shown.

1 -define your data

- create a string with the data range

2- define a named range (pvt_range in the example) to hold the range's reference

3- create a pivot table

- insert the named range's name in the table/range box.

4 - after adding new rows the named range updates, and you just have to refresh the pivot table's data source.

The use of a cell for the named range isn't necessary. But for testing and changing purposes it is the best way.

For more serious implementations, the formula should be in the named range definition.

[EN] - 100th post - Data Analysts Needed - Big Data

A great subject for the 100th post.
This is right up my alley.
quoting http://flowingdata.com/2011/05/20/growing-need-for-data-heads/:
"I've said it before, but if digging into data is your idea of fun, there's a whole mess of excitement and adventure headed your way."

I love probing data for meaning! love it!!!

Articles:
http://www.mckinsey.com/mgi/publications/big_data/index.asp
and the full pdf:
http://www.mckinsey.com/mgi/publications/big_data/pdfs/MGI_big_data_full_report.pdf

also a NYTimes
http://www.nytimes.com/2011/05/13/technology/13data.html?_r=1

Interesting, interesting, interesting :D

[EN] - Linkedin (LNKD) 2011-May-19 22:28 GMT+0

Well this was a very interesting day...... the IPO was valued at $45, the stock price reached 174% increase (over $120), but lost some strength (with many investors cashing in on some very considerable profit) during the rest of the day, finishing around $93, with a +100% gain.

It will be very interesting watching what happens in the next few days. Was this only a sudden initial fever? Will this mean sustainable growth?

We both now, that all of us would like to have entered google, or even apple at an early stage, and probably at that point NOBODY believed that these stocks would reach the actual values.

So what would happen?

Stay tuned :D