To recover from a dropped
or truncated table, a dummy database (copy of primary) will be restored and
recovered to point in time so the table can be exported. Once the table
export is complete, the table can be imported into the primary database.
This dummy database can be a subset of the primary database. However, the
'dummy' database must include the SYSTEM, UNDO (or ROLLBACK), and the
tablespace(s) where the dropped/truncated table resid...
Monday, 15 September 2014
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...
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...
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...
Saturday, 7 June 2014
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...
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...
Sunday, 25 May 2014
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...
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
--------------------...
Saturday, 10 May 2014
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...
Sunday, 30 March 2014
Rapid Growth of SYSAUX Tablespace
Oracle 10g onwards, The SYSAUX an auxiliary tablespace to the SYSTEM
tablespace created in your database.
Some database components that formerly created and used separate tablespaces
now occupy the SYSAUX tablespace.
Below table shows the components that uses the SYSAUX tablespace as their default tablespace and
the tablespace in which they were stored in earlier releases.
Component
Using SYSAUX
Tablespace in
Earlier Releases
Analytical Workspace
Object Table
SYSTEM
Enterprise Manager
Repository
...