How to reduce the Tivoli Data Warehouse Oracle database

Body

Background: This is a follow up to the blog on how to reduce the IBM Tivoli Monitoring warehouse table size. The database engine type here is Oracle.
If the size of the Tivoli Database Warehouse (TDW) is too big or if you must reduce the size of TDW database you can use one of the three methods below.
But before you do anything, I will recommend that your backup your warehouse database. The command you use to back up the database will depend on the type of database that you are running.

A typical Oracle backup requires you to do the following or you use Oracle RMAN to perform the backup.
1. Login with database administrator privileges
2. The database be running in ARCHIVELOG Mode
3. Use a Flash Recovery Area. (The flash recovery area is a set up location on disk where the database can create and manage a variety of backup and recovery-related files).
 
There are three ways to reduce the size of the Tivoli Data Warehouse (TDW) database.

1.In this case you use the DROP command to delete the entire TDW database and if RMAN is connected to a recovery catalog then unregistered it.This should remove all data files, online redo logs and the files belonging to the database. Here are the steps using RMAN..
    RMAN> STARTUP FORCE MOUNT
    RMAN> SQL 'ALTER SYSTEM ENABLE RESTRICTED SESSION';
    RMAN> DROP DATABASE INCLUDING BACKUPS NOPROMPT;
    DROP DATABASE <database name>
Note that dropping a database can have far-reaching effects, because this action deletes all its objects, containers, and associated files. The dropped database is removed and uncataloged from the database directories.

2.The second option is to purge the database. Purge in Oracle removes table or index.
Note, you cannot roll back a PURGE statement, or recover an object after it is purged in an Oracle database. The database object must reside in your own schema or you must have the “DROP ANY...”  system privilege for the type of object to be purged, or you must have the SYSDBA system privilege.

3.The third which does not really reduce the overall size of the database will involve putting in place an archiving strategy or using compression or both. The cost of using this third method is reduction in performance when reading the tables.

Now the purge itself is a series of reducing each table size in the database. You can also use the delete command and specifying a date to start and then reduced by say 30 days.
First run the Describe command against the database to get the schema of the table.
DESCRIBE {table-Name | view-Name}

These three commands are also useful.
Show Tables command - For a list of tables in the current schema
Show Views command - For a list of views in the current schema.
Show schema command - For a list of available schemas.

Use a date field from the output describe command say (ordertime)
DELETE from <tablename > where ordertime < (current timestamp - 30 days)

How to purge or reduce the Tivoli Warehouse Size

Subscribe and follow us for all the latest information directly on your Social feeds: