RMAN Backup Monitoring

Here is a simple query to monitor the RMAN progress.

select

 SID||'-'||OPNAME SID_OPNAME,

 100-round((sofar/totalwork) * 100,2) pct_left

from 

   v$session_longops

where 

   totalwork > sofar

AND 

   opname NOT LIKE '%aggregate%'

AND 

   opname like 'RMAN%';

SID_OPNAME                             PCT_LEFT               

-------------------------------------- --------

3119-RMAN: incremental datafile backup 79.77                  

3135-RMAN: incremental datafile backup 79.87                  

3123-RMAN: incremental datafile backup 79.79                  

3143-RMAN: incremental datafile backup 79.92                  

3127-RMAN: incremental datafile backup 79.82                  

3159-RMAN: incremental datafile backup 78.28                  

3120-RMAN: incremental datafile backup 79.91                  

3136-RMAN: incremental datafile backup 78.16                  

3146-RMAN: incremental datafile backup 79.86                  

3183-RMAN: incremental datafile backup 85.29                  

3152-RMAN: incremental datafile backup 78.10

3151-RMAN: incremental datafile backup 79.74                  

3111-RMAN: incremental datafile backup 80.00                     

3124-RMAN: incremental datafile backup 79.74                  

3139-RMAN: incremental datafile backup 79.68                  

15 rows selected

UNSUPPORTED way to delete scheduled workbook in Discoverer

Purely UNSUPPORTED way to delete scheduled workbook in Discoverer

Needs testing from your side and please do it with proper backup.

I have been recently working on a datacenter migration project that involved Discoverer.

After the migration some users who had large number of Discoverer Scheduled workbook had issues when click on Manage Schedule Workbook.

By documentation when a Discoverer Administrator in the admin edition set the scheduled results to be deleted and then the user logs in and logs out the workbook and its associated database objects gets deleted automatically.

Unfortunately this did not work for two of my users because of their mass schedules.

Here is a backend work that I did.

SQL> DELETE FROM EUL5_BATCH_PARAMS WHERE BP_CREATED_BY='TESTUSER';

SQL> DELETE FROM EUL5_BQ_DEPS WHERE BQD_CREATED_BY='TESTUSER';

SQL> DELETE FROM EUL5_BQ_TABLES WHERE BQT_CREATED_BY='TESTUSER';

SQL> DELETE FROM EUL5_BATCH_QUERIES WHERE BQ_CREATED_BY='TESTUSER';

SQL> DELETE FROM EUL5_BATCH_PARAMS WHERE BP_CREATED_BY='TESTUSER';

SQL> DELETE FROM EUL5_BATCH_SHEETS WHERE BS_CREATED_BY='TESTUSER';

SQL> DELETE FROM EUL5_BATCH_QUERIES WHERE BQ_CREATED_BY='TESTUSER';

SQL> DELETE FROM EUL5_BR_RUNS WHERE BRR_CREATED_BY='TESTUSER';

SQL> DELETE FROM EUL5_BATCH_REPORTS WHERE BR_CREATED_BY='TESTUSER';

SQL> COMMIT;

Scripts to delete the objects

SQL> SELECT LOWER('DROP PACKAGE TESTUSER.'||OBJECT_NAME||';')CMD FROM DBA_OBJECTS WHERE OWNER='TESTUSER' AND OBJECT_TYPE='PACKAGE' ;

SQL> SELECT LOWER('DROP TABLE TESTUSER.'||OBJECT_NAME||';')CMD FROM DBA_OBJECTS WHERE OWNER='TESTUSER' AND OBJECT_NAME LIKE 'EUL5_B%' AND OBJECT_TYPE='TABLE' ;

I hope this will be used with proper testing.

Invisible Index in 11g

11g has many new feature and one such is Invisible Index. It saved my day today.

There was a warehouse job having performance problem since the 11g upgrade.

My Test Database was on outage , so I had to tune it directly on the Production Database.

I know if a Index is created the issue will be resolved but the since the introduction of index would cause other problem , I had to create it in Invisible state.

create index index_for_testing on table table_name(column_name1,column_name2) invisible;

In the above statement the keyword invisible will not make the index ready to use. 

The optimizer has to be hinted on the usage and then the session will start to use the index.

SQL> alter session set optimized_use_invisible_indexes=true;

SQL> exec start_job;

The index is now valid only for that session and that run.

After proper testing I will make the index permanent by the below syntax

SQL> alter index index_for_testing visible;

I hope this tip will be helpful when it comes to creating indexes directly on the production environment.

Start to blog after a long break

Dear friends

I have started to blog after a long break.
Was busy with a datacenter migration project.

I hope you all will continue to support me.

Senthil