How to recreate oraInventory (Central Inventory) in RDBMS Homes if the central inventory is corrupted or lost?

SOLUTION STEP 1:  Locating the central inventory   The location of the central inventory can be found from the central inventory point...

Read more

How to Recreate The AWR( AUTOMATIC WORK LOAD ) Repository

The best way to deinstall/install AWR is as follows: 1. Disable AWR statistics gathering by setting the statistics level to basic as follows: Check settings for parameters as follows: sqlplus /nolog connect / as sysdba show parameter cluster_database show parameter statistics_level show parameter sga_tar...

Read more

Standby MRP needs old log sequence even after restore of incremental backup at standby

At standby applied incremental backup but  MRP  process at standby is looking for very old sequence. SQL>SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"    FROM (SELECT THREAD# ,SEQUENCE#    FROM V$ARCHIVED_LOG&nb...

Read more

DBUA error database not in mounted state

 While  upgrading one of my 10.2.0.4 64-bit database to 11.2.0.2 using DBUA it failed  with retrieve db information  and pop up the message "The Upgrade Assistant failes in bringing up the database XXX. Oracle Home /oracle/OraHome11203 obtained from file /etc/oratab was used to connect to the databa...

Read more

How to copy schema stats from one schema to another schema

Here we will see  how to copy schema statistics from one schema to another. Basically there are two methods for doing this: using DBMS_STATS or using Data Pump This is especially useful when doing large data transfers between either schemas or databases and statistics are needed fast. Remember that data density, row chaining or index cluster factor changed due to the data import and index rebuilds. Method 1: Using DBMS_STATS Extract the statistics First we need to login as schema owner which from which we want to extract...

Read more

Restoring Optimizer Statistics

  In some cases we may find that gathering fresh statistics has led to the optimizer executing sub-optimal execution plans and we would like to restore the statistics as of a date when we had good performance. Here we can  restore table statistics to a previous point in the past by using the DBMS_STATS.RESTORE_TABLE_STATS package. We can check how far back in time we can go to restore statistics. SQL> select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY  from dual; GET_STATS_HISTORY_AVAILABILITY --------------------------------------------------------------------------- 13-MAR-14 10.51.37.266819000...

Read more

Refresh of Mview is Throwing ORA-00942

Refresh of Mview is throwing ORA-942 after successful creation. Create materialized view schema_a.client_mview  tablespace users build immediate refresh force start with trunc(sysdate)+1455/1440 next trunc(sysdate)+1455/1440 as select client_id, username, createdate, swver from client_req@TEST_LINK where swver is not null; Materialized View Created. select db_link, username from dba_db_links where db_link like 'TEST_LINK'; DB_LINK                      USERNAME --------------------...

Read more

LOG MINING IN ORACLE DATABASE

Suppose data in a table has been deleted. Audit trail was not enabled, therefore we do not have a clue of the user who has performed deletion and there are around 30 users that have DELETE rights on that table. Management want to know the name of the user, here we can use Oracle LOG MINING feature to filter out the transactions performed during that period. Note: Make sure the Archive log must be enabled previously, if you want to use LOG Mining technique. CONSIDERATIONS Database : ORCL Schema : SCOTT Table : STUDENTS Date of Deletion...

Read more