Archive for February, 2011

If you want top 200 sql in your report
dbms_workload_repository.modify_snapshot_settings (topnsql=> 200)

If you want a particular sql even if it’s not in your top list.
dbms_workload_repository.add_colored_sql(:sql_id)

trace one or more sql id
alter session set events ‘sql_trace [sql: sql_id1 |sql_id2]‘

I know about the last one.

convert this post to pdf.

Posted by Borndba on February 22, 2011 at 2:24 pm under Oracle Administration.
Comments Off.

Pls see this interesting note,

http://blogs.oracle.com/optimizer/2011/02/how_do_i_deal_with_a_third_party_application_that_has_embedded_hints_that_result_in_a_sub-optimal_ex.html

convert this post to pdf.

Posted by Borndba on February 16, 2011 at 1:15 pm under Oracle Performance.
Comments Off.

If one of your data file is corrupted in Production or in Standby, you can pick that data file from other location and restore.
We don’t have to recreate the standby db or restore the DB. Please find the steps we followed..

SQL> select * from v$database_block_corruption;

FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO
———- ———- ———- —————— ———
36     289469          1                  0 CORRUPT
75      96029          1                  0 CORRUPT

Primary:

RMAN> backup as copy datafile 36 format ‘/ora_dr_backup/rman/drdb/data36.dbf’;

RMAN>backup as copy datafile 75 format ‘/ora_dr_backup/rman/drdb/data75.dbf’;

ftp Standby

SQL> alter database datafile 36 offline ;   If your standby datafile is corrupted use ‘drop’ option
SQL> alter database datafile 75 offline ;  If your standby datafile is corrupted use ‘drop’ option

RMAN> catalog datafilecopy  ‘/ora_dr_backup/rman/drdb/data36.dbf’;

RMAN> catalog datafilecopy ‘/ora_dr_backup/rman/drdb/data75.dbf’;

RMAN> list copy of datafile 36; ==> Check above mountpoint is listed

RMAN> list copy of datafile 75; ==> Check above mountpoint is listed

RMAN> restore datafile 36;

SQL> alter database datafile 36 online;

RMAN> recover datafile 36;

RMAN> restore datafile 75;

SQL> alter database datafile 75 online;

RMAN> recover datafile 75;

convert this post to pdf.

Posted by Borndba on February 8, 2011 at 5:13 pm under Oracle Backup Recovery.
Comments Off.

Got lot of insight on various process in DG environment.

http://forums.oracle.com/forums/thread.jspa?messageID=4090175

convert this post to pdf.

Posted by Borndba on February 2, 2011 at 9:31 pm under Oracle Dataguard/RAC.
Comments Off.

New 11gR2 feature. Seems interesting. Can it be a alternate to active-passive cluster or Dataguard? May not be for DG, as DG protects you in cases like corruption.

http://download.oracle.com/docs/cd/E11882_01/rac.112/e16795/onenode.htm

convert this post to pdf.

Posted by Borndba on February 1, 2011 at 7:30 pm under Oracle Administration.
Comments Off.