Tuesday, June 21, 2011

Tip to Save Discoverer Administrators Time and Frustration

Our Discoverer Administrators had a couple of issues with backing up and refreshing EULS. On a one by one basis, the Java tool on the PC worked fine. But, when they tried to do either a full export for backup purposes or tried to refresh all of production from the QA instance of Discoverer, the java refreshes took all day and typically hung up their PC so they couldn't do any other work.

We decided to see if we could do the work on the Discoverer server, rather than the PC, so the user's PC would not be hung up, and we could schedule the backups through a cron job.

Sounds simple enough, right?

Well, I couldn't find any examples of how to refresh the whole schema through the eulapi on the Discoverer Server. I could only find some examples of how to export/import a workbook or a business area or import a group of workbooks or business areas. The trouble is, if you have a lot of workbooks or business areas, this could take quite a bit of effort. So we really needed a way to export and import the whole schema easily.

After playing around with a combination of examples that Oracle had for eulapi and for the Java api used by the Discoverer Administrator tool, I found the combination that worked for us. Whereas the Java export took about 4 hours on a PC, the export took less than 30 minutes on the server. The Java import took all day on the PC and only 30 minutes or so on the Discoverer Server. Also, the user didn't have to use a different PC to do work during the day, or try to remember to run imports and exports at night.

This is run from the $ORACLE_HOME/bin for Discoverer and assumes you have your user and permissions set up.

For a basic eulapi export of the whole schema:

./eulapi -connect yourusername/yourpassword @source_db -export yourfilename.eex -all -business_area_and_contents % -wildcard -log origeul_db.log

Backup script for Linux:

I schedule this as a cron job; it could be scheduled through Grid Control as well.

I set up a file that has a number of preset variables including Oracle Home, Date, where I want my logs to go, etc., and source that first in the script.

In the script below I set up the variables $DISCO_ADMIN for my Discoverer Home directory, $MAINT_BACKUPS for the directory where I want my backups to go, $TODAY for today's date in a format I prefer, and $MAINT_LOG for the directory where my log output is to go.

#!/bin/bash

. $HOME/.environmentsetupfile

. $DISCO_ADMIN/bin/eulapi -connect yourusername/yourpassword@source_db
-export $MAINT_BACKUPS/yourfilename_$TODAY.eex -all
-business_area_and_contents % -wildcard -log
$MAINT_LOGS/yourfilename_$TODAY.log

If you set a backup to run periodically, be sure to set up a job to remove the older backups and log files.

For the Import

You can either import the data into a new schema, or refresh the whole schema. You could set this up as a cron job and schedule the import. You could set it up to run periodically or during your maintenance window.

./eulapi -connect yourusername/yourpassword@targetdb -import
yourfilename.eex -identifier -preserve_workbook_owner
-import_rename_mode refresh -log yourfilename_in.log

For the import_rename_mode we use refresh so we overlay any existing objects with the imported objects with the same name.

Other options are: rename_new, rename_old, do_not_import:

• use rename_new to rename imported objects that match existing objects.
• use rename_old to rename existing objects that match imported objects.
• use do_not_import to not import objects that match existing objects.

You can also do the export/import of the EUL schema at the database level, but I prefer this method, as I have the flexibility where I can overlay items, rename items or not import items as needed. With the import method at the database level, if the schema already has objects, I have to manually rename each item or remove each item

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect