Yesterday I am importing some table data in non production database using Datapump utility.

ORA-22804: remote operations not permitted on object tables or User-defined type columns

Restriction on Using User-Defined Types with a Remote Database:

One of my application team lead called me... Raja, we have faced DB link issue while accessing the table from remote database.

“While accessing the table from remote database & they got the below error”

ERROR at line 1: ORA-22804: remote operations not permitted on object tables or
User-defined type columns

I have verified the database link & its working fine.

Colored SQL in AWR report:

AWR captured all the SQL which was running in database?

No.

MMON captured only top resource consumed queries based on
STATISTICS_LEVEL parameter and dbms_workload_repository.modify_snapshot_settings topnsql variable value.

I want to capture some sql in AWR report.

Today I planned to test “Recover the datafile without prior backup”

Currently there is no failure in database. I shutdown the database & removed the below datafile in OS level “D:\ORACLE\APP\ORADATA\CHE\TEST.DBF”.

Note:

1.Database must be in archive log mode.
2.We need all the archive log files from lost datafile created.

1.Login into che database

C:\>set oracle_sid=CHE

SQL*Plus: Release 11.1.0.7.0 - Production on Wed May 19 14:21:03 2010

Copyright (c) 1982, 2008, Oracle.

Oracle Data Recovery Advisor 11g new feature

Data recovery advisor is one of the oracle 11g new features.

The Data Recovery Advisor tool can assist with diagnosing media failures, making recommendations and providing the RMAN commands to resolve the problem.

Data Recovery advisor we have used three commands.

1.List failure
2.Advice failure
3.Repair failure

How it works?

1.List failure:

The Data Recovery Advisor stores its information outside the database in the Automatic Diagnostic Repository (ADR - V$diag_info)

If you find any problem in database and Data recovery a

Why indexes are going to unusable status?
When will the indexes goes for unusable state?
1.Any maintenance activity in partition table – Alter command
1.Truncate the table partition
2.Drop the table partition
3.Split Part/Sub Partition

2.Any maintenance activity in table – Table Reorganization.
1.Move the table.
2.Online redefinition

More details about online redefinition -
http://dbarajabaskar.blogspot.com/2010/04/online-redefintion-in-oracle-table.html

3.Oracle Import – While import the data with skip_unusable_indexes=y.

To improve the perfo

RMAN Cloning using active database feature - Oracle 11g feature

Normally we well knew about RMAN cloning.
Oracle9i/10g using duplicate cloning command, we do the RMAN cloning.
We need a RMAN full backup for cloning the database in oracle9i/10g.
In oracle 11g provides a new feature, without RMAN database backup we can clone the database.
I have tested the active database RMAN cloning in my test server.

Environment Details:

Operating system: Windows XP service pack 2
Database Version: 11.1.0.7 (32 bit)
Source database name: troy
Clone database name: clonedb

How its work?

READ ONLY tables in oracle 11g

Restore point:

A restore point can be used to flash back a table or the database to the time specified by the restore point without the need to determine the SCN or timestamp. Restore points are also useful in various RMAN operations, including backups and database duplication. You can use RMAN to create restore points in the process of implementing an archival backup.

Two types:

1. Normal restore point
2.

Oracle installation: Oracle Binary Cloning

Clone the oracle software between two server.

Please check the OS compatibility are same between source & destination server.

Source Server:
****************

steps 1:

ORACLE_HOME: /apps/oracle/product/10.2.0.1

Tar the oracle home 10.2.0.1

$cd /apps/oracle/product

$tar -cvf 10.2.0.1.tar 10.2.0.1

Destination server:
******************

Steps 2:

Copy the oracle binary tar file from source server to Destination server.

$mkdir -p /apps/oracle/product

$cd /apps/oracle/product
$sftp oracle@source