script to find a tablespace is fragmented oracle

December 13, 2009 at 11:25 am | In 1 | Leave a Comment
Tags: ,

To find whether a tablespace is fragmented you can fire the following query
SELECT
TABLESPACE_NAME,COUNT(*) FREE_CHUNKS,decode( round((max(BYTES) / 1024000),2),
null,0, round((max(BYTES) / 1024000),2)) largest_chunk,
nvl(round(sqrt(max(BLOCKS)/sum(BLOCKS))*(100/sqrt(sqrt(count(BLOCKS)) )),2), 0) FRAG_INDEX
FROM
SYS.DBA_FREE_SPACE
GROUP BY
TABLESPACE_NAME
ORDER BY 2 DESC, 1;

The FRAG_INDEX column shows the detailed result and if the value is higher like –100 then tablespace is working fine and otherwise if its lower then that tablespace is fragmented .

Your Comments are welcome.
– Vivek

How to Schedule a Mapping in Oracle Warehouse Builder(ETL)

December 12, 2009 at 12:51 am | In 1 | 1 Comment
Tags: , ,

There are some tricky ways to achieve this in OWB. First You should do a mapping for ETL either from flatfiles or other databases . then you should create a process flow(which uses workflow).Process flows are for triggering the event and sometimes for notifying the administrator if the event fails.After creating the processflow ,go to the Schedule node under the project you created ,and add a New Schedule by right clicking that and give some meaningful name.Now the Important thing is –Again right click the schedule you have created ,now it will ask how schedule should be , now you can define the schedule as how it should execute, daily,hourly,weekly,monthly like that and create the schedule. Now after creating everything,go to the mapping you have created and right click that and click configure, it will open the configuration module,in that pane click Calender and choose the schedule You have created. After doing this , go to controlcenter and deploy the schedule and then click Start from file. now in the schedule tab of control center ,you can see the scheduled jobs(mapping and processflows). This is how you can schedule automatically a Mapping or process flow in Oracle Warehouse Builder.
Your comments and queries are welcome.

– Vivek

How to Increase Performance of OBIEE Reports(Using Cache)

December 11, 2009 at 11:51 pm | In 1 | Leave a Comment
Tags: , ,

Performance Tuning of BI Reports is key for a Large Business Intelligence Environments – Where large data ware houses are running with millions of data.The Cache Future of OBIEE solves this problem to a great extent. When The Same Report is run again and again OBIEE takes advantage of Cache and scales up the Performance of Reports.This is not a database cache and by Enabling YES to CACHE_MANAGEMENT Parameter in NQSConfig.ini file and by restarting the BIServer Services You can enable the Cache for Reports. You can manage Cache using OBIEE Administrator.Operations like purging Cache for a Particular report or the entire cache , or for a user can done using OBIEE Administration Console.After enabling the Cache You can find the time for report result is drastically reduced.In NQSConfig.ini you can also specify the Cache Size.

—Vivek

About PO Approval and workflow notification mailer integration with blackberry

December 6, 2009 at 9:17 pm | In 1 | Leave a Comment
Tags: , , , , ,

Dear Readers,
Purchase Order Approval is a flow in iProcurement Module of Oracle E-Business Suite.When an End User raises a Purchase Order,If the Order value is within permissible range then it can be set to auto approve. If the value is beyond the range , then using Oracle Workflow the Approval request notification is sent to the Approver either through self service or through e-mail in which a link will be given to the Approver either to approve it or not to approve it.Some times we may face issue like ,even though the approver approved the PO , the Purchase Order may not be compeleted, this can be solved by running the Concurrent Request “Synchorinze workflow” for the PO Approval. The Approval can be done even by sending a reply mail to the notification from a blackberry with the message “approved”.For this your apps dba need to configure the Workflow Notification Mailer with E-mail inbound and outbound Servers in the configuration page. This task has to be in the Oracle Application Manager.

Your Comments and Queries are welcome.

– Vivek

Designing Backup Strategy for VLDB

December 4, 2009 at 11:13 am | In 1 | Leave a Comment
Tags: , ,

Dear Readers,
In this post I am going to discuss about the Backup Strategy the DBA’s will form for Very Large Databases.
when saying VLDB it means databases above 1 TB capacity.If you do a Normal hot or cold backup or RMAN backup you will see that the backup runs for ages.The Best Solution for this will be using MML for RMAN module using fast tape backup devices. My Suggestion will be to use Oracle Secure Backup,Legato or VERITAS NetBackup. Also to further reduce your backup time you can configure RMAN Incremental Backups with These Interfaces .These measures will drastically reduce the time to backup VLDB’s.

Your Comments are welcome
–Vivek

How snapshot standby and database replay helps performance tuning in oracle

October 23, 2009 at 7:57 pm | In 1 | Leave a Comment
Tags: , , ,

Dear Readers,

Database Replay is a new feature in Oracle 11g using which you can replay all the actions in the database .This is usefull especially when you will be doing performance tuning.You can capture the database and replay it in a test database,so that you can identify the problem.But the question is the primary db and the test db will not be identical and you will not want to corrupt the test db with database replay.Oracle Snapshot standby helps here.In 11g you can put the standby database in snapshot standby mode and do the replay and performance tuning,once the job is done , when you put back the snapshot standby in managed recovery mode(normal dataguard) the flash back will be done and it will be back in normal standby mode.Your data will not corrupt and you have dataguard instance healthy.

Your comments are welcome.

– Vivek

Siebel CRM startup and shutdown scripts in linux and in Unix Flavours

October 23, 2009 at 7:55 pm | In 1 | Leave a Comment
Tags: , , , , , ,

Dear Readers,
In this post i will explain how to start and stop siebel CRM in linux

For Starting Siebel CRM do the following

1.start the oracle database and listener.

2.start the gateway server using the following command after sourcing the environment
cfgenv.sh in gtwysrvr directory and siebenv.sh in siebsrvr directory

$start_ns
the above is actually in gtwysrvr/bin directory..

after starting gateway server
3.start the siebel enterprise server using the following command after sourcing the siebenv.sh in siebsrvr
directory (this utility is in siebsrvr/bin dir)

$start_server all

4.Start the Oracle HTTP Server using the following command

cd $OHS_SERVER_PATH/opmn/bin
./opmnctl startall

Now your Siebel Server is Started…

To Stop the Siebel Server in Linux do the following

1.stop the Apache Oracle HTTP Server

./opmnctl stopall

2.stop the Siebel Enterprise Server using the following in siebsrvr/bin directory

$stop_server all

3.stop the Gateway Server using the following in gtwysrvr/bin directory

$stop_ns

4.Shutdown the listner and Oracle Database.

I hope this post will be usefull for all Siebel Administrators.

Your comments are welcome

– Vivek

How to Install Siebel CRM Webclient 8.0.0.0 in client machines

October 12, 2009 at 9:29 am | In 1 | Leave a Comment
Tags: , , ,

It is very hard to install siebel crm webclient unless you follow
certain steps.Mainly verify your operating system pre-requisite.Windows xp with sp2 is recommended.After that install the database client.If you are having Oracle Database 10g in the server , install oracle database 10g client(not server) in the client machine.If you want you can change the sibel.ini in file as required,Then start the siebel webclient installation, it will go through.You need Siebel Web client because you need to apply siebel crm licenses before accessing the applications.Hope this helps.

Your comments are welcome.
– Vivek

How to Install Oracle Hyperion EPM 11.1.1.3.0 In RedHat Linux Version 4

October 6, 2009 at 2:29 pm | In 1 | Leave a Comment
Tags: , ,

1.download the required zip files from http://edelivery.oracle.com
It depends upon which module you want to install

for Planning Plus Hyperion Module download the following zip files

V17766-01.zip, V17382-01.zip, V17366-01.zip , V17372-01.zip , V17389-01.zip , V17378-01.zip , V17380-01.zip, V17367-01.zip, V14827-01.zip, V17402-01.zip, V13482-01.zip, V14803-01.zip, V17390-01.zip , V17388-01.zip, V17370-01.zip, V17369-01.zip, V17397-01.zip

you can find more information on the documentation zipfile as for which module what to download.?
also download 10201_database_linux32.zip from oracle technology network.

step I.
install oracle database 10g r2 and

create a user and assign dba privelege ,this user will be used while installing hyperion

Step II.
unzip all the zip files into a centralized area

and then from vnc run the following

sh installTool.sh

It will automatically select the components and click install.

once installed click configure in the Final Page which will take you configuration

give oracle database details and leave rest of the things as default

once the configuration is successfull ,you will be able to access the hyperion EPM from the following URL’s

Hyperion workspace

http://hostname.domainname:45000/workspace

hyperion shared services

http://hostname.domainname:28080/interop/

Hyperion Planning

http://hostname.domainname:8300/HyperionPlanning/

Hyperion WebAnalysis

http://hostname.domainname:16000/WebAnalysis

username :- admin
password :- password

Your questions,comments are welcome

– Vivek R

Blog at WordPress.com. | Theme: Pool by Borja Fernandez.
Entries and comments feeds.