Building your Dashboard at work – Oracle HTML DB

I used to spend a lot of time on email/phonecalls - answering some of the frequently asked questions

when was preprod /test/dev1/dev2/test...refreshed?
what Application db patches do we have on the different environments?
when different Application db patches were applied to different environments?
DB release notes for the different patches?
Results of different load and datafix runs?
Schedule for load runs?Schedule for db refreshes?

I decided to build a central dashboad that will answer all the FAQ s realtime - reducing my time on emails and helping me focus on other important db tasks.

Oracle HTML DB did it for me – I had a quick Dashboard Application up in running in 3 hrs (The Application connects to atleast 10 databases and Reports on different information for different instances – based on the FAQs)………..Read More >>>>


DRINK COFFEE AND PLOT AWAY YOUR DATABASE METRICS

Are you required to plot database statistics every Monday Morning to present Reports to Management?
Are you part of a business that cannot pay for Reporting Tools and have to plot your metrics in Excel?

While Excel works great for plots – it is client driven and not server driven and you loose all your configurations everytime you change your client machine and run into the risk of your plots not updating if your DSN or odbc is incorrect/updated .

I found a quick way to plot reports from my database server using gnuplot and then scheduling it as a DBMS job to email me the plot weekly – just drink your coffee Monday morning and send management the attachment you get via email weekly – and the best part everything runs within your database – how cool is that!

It also works great if you want to report statistics on Oracle Applications concurrent programs where start and endate’s of a program’s runtime are recorded in the database. I currently use it to report statistics on my load runs. Automating this process has given me the time to focus on more important aspects of my database.

.......Read More >>>

Test your Oracle Partition Archive/Restore Strategies:

Are we testing our Oracle Partitioned Tables Restore strategies?

Your Partition Archives are no good if they cannot be restored when needed and if your SLA’s require you to restore the partition if needed by the customer.Below are some quick steps you can follow to test your Oracle Partitioning Archive and Restore Strategy.......Read More >>>

No Monitoring Tools to Monitor your Database

Have you been part of an organization that has huge Oracle Databases but no database monitoring tools? It seems to be quite normal – while funding for database Licenses have been approved without a problem it has been quite a challenge to get funding approved for monitoring tools like SpotLight, Foglight, Precise, Hotsos Profiler etc.

So how does a DBA then debug performance issues? While writing shell scripts to monitor space usage, memory usage to page the DBA will help – scripts may not be the best way to solve the performance problem in hand when users are complaining about slow performance.I have worked for organization where we had no third party Products to monitor our databases or drill down into Performance Issues when users are complaining about slow performance. This is where OEM Diagnostic Pack (Performance Manager) has come very handy.....Read More >>>

Below is an Overview of Oracle HTML db and how you can use it fairly fast to set up a Real Time Application.

Oracle HTML DB renders Real Time Applications from data stored in Tables.

1)Before Installing Oracle HTML DB install Oracle HTTP Server standalone from the Oracle 10g Companion CD

2) To install the Oracle HTML DB software, perform the following steps:

a. Download the HTML DB software from OTN .
b. Unzip htmldb_2.0.zip.
c. Open a terminal window and execute the following commands
cd
sqlplus /nolog
connect / as sysdba
@htmldbins oracle SYSAUX SYSAUX TEMP /i/ orcl

Oracle HTML DB is installed into the database.

3)You need to copy the images that Oracle HTML DB uses to the appropriate directory. Perform the following steps:

a) Open a DOS window and execute the following commands:

cd /Apache/Apache
mkdir images
cd images
xcopy /s /images/*.*
is the directory during the HTTP install where you specified the oracle_home (i.e. c:\oracle\product\10.2.0\http_1)
is the directory where you unzipped the htmldb zip file and made available for install.


4) Configuring HTTP Server to Run Oracle HTML DB
In order to run Oracle HTML DB, you need to configure an application in HTTP Server. Perform the following steps:

(i) Create the \Apache\modplsql\conf\marvel.conf file and add the following statements:

Alias /i/ "\Apache\Apache\images/"
AddType text/xml xbl
AddType text/x-component htc

SetHandler pls_handler
Order deny,allow
Allow from all
AllowOverride None
PlsqlDatabaseUsername HTMLDB_PUBLIC_USER
PlsqlDatabasePassword oracle
PlsqlDatabaseConnectString :1521:
PlsqlDefaultPage htmldb
PlsqlDocumentTablename wwv_flow_file_objects$
PlsqlDocumentPath docs
PlsqlDocumentProcedure wwv_flow_file_manager.process_download
PlsqlAuthenticationMode Basic
PlsqlNLSLanguage AMERICAN_AMERICA.UTF8


Note:
refers to the name of the host where your database is resides. refers to the connect string to your database

(ii)Modify the \Apache\Apache\conf\httpd.conf file and add the following lines to the end:

# Include the Oracle configuration file for HTML DB
include "\Apache\modplsql\conf\marvel.conf"

(iii)Stop and start the HTTP Server with the following commands:

cd \opmn\bin
opmnctl stopall
opmnctl startall

5) Creating a New Workspace
Before you create an application, you need to create a workspace. Perform the following steps:

1. Open your browser and enter the following URL:

http://:7777/pls/htmldb/htmldb_admin
Enter admin as the username and enter your password oracle (or what ever the password you specified during the installation). Then click Login.

2. Under Manage Workspaces, click Create Workspace.

3. Enter a workspace name and then click next

4. For "Re-use Existing Schema?" select No. Enter for the Schema Name and Password, and select Medium: 5 Megabytes for the Space Quota. Then click Next.

5. Enter as the username, and enter your password and your e-mail address. Then click Next. Note: A new administrator user is created in addition to the workspace.

6. Review your workspace request, and then click Create.

7. Your workspace and user is now created. Click Done.

8. You now want to login as the user for the workspace. Click Logout.

6) Loginto your workspace


The first step to creating your Application is logging into your workspace
Login with workspace name,username,password

Once you have logged in – you will be prompted for (Application Builder, SQL Workshop, Administration)


Click on Application Builder and create a new Application say “dbaconsole”
Create Application from scratch
you can add a page to your Application at this point
select a tab level if you want tabs on your page




click the next and go through the option you would like to have for your Application and finish Create Application

The above page will show up – indicating the Application that was just created and showing the page that was added to the Application
click on the page and add the regions you want added to your page – say we want a SQL Report add to our page 1
click add Region and check on Report

enter the sql you need to display your results in the form of a Report
finish by clicking create region

Now Run your application and you will see our data displayed in a Report format

Different Options are available to edit Page Attributes:

-Regions, Buttons,Items (Eg: Checkbox,datepicker,Display only,File Browse etc),Processes like unnamed pl/sql blocks can also be added to a Page. Numerous options are available to edit page display properties.


Oracle HTML DB – is completely UI driven – its great to set up small Applications – like the one I was trying to build – a Realtime Dashboard – that lists the status of all my databases, db patch level on the different databases, Application db patch updates on the different databases, it is great for storing all your documents as well – a central Repository for the work the dba’s do accessible from anywhere at anytime!!!