Standby roll forwad by using rman incremental backup

When the physical standby database lags far behind the primary database, using rman incremental backup is better than applying tons of archived logs. Here are steps.
Step1.Standby database: check the current scn
SQL> select current_scn from v$database;
CURRENT_SCN
—————————————-
788209520

Step2. Primary database: incremental backup from the scn.
run {
allocate channel dev_0 device type disk;
allocate channel dev_1 device type disk;
allocate channel dev_2 [...]

How to recover a corrupted block in oracle10g

Step1. check corrupted block file# and block#
SQL> SELECT * FROM V$DATABASE_BLOCK_CORRUPTION;

FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO
———- ———- ———- —————— ———
1        507          1                  0 CORRUPT

Step2. recover corrupted block and then validate again
rman target /
blockrecover datafile 1 block 507;
–To recover many blocks, using
BLOCKRECOVER DATAFILE 2 [...]

Type and statu of oracle constraint

1. Type of constraint
C (check constraint on a table)
P (primary key)
U (unique key)
R (referential integrity)
V (with check option, on a view)
O (with read only, on a view)
2.Statu of constraint
1) Enable Validate:
a) check current data. If invalidate, the constraint cannot be created or enabled. b) the constraint is enabled c) check new inserted or updated data. [...]

How to connect Oracle and Sql server

Method 1) Oracle connect to Sql server

Using Oracle Gateway in 11g, if the database version is 10g, patch 5965763 should be installed on it.
Step1: Install Oracle gateway at an existing Oracle 11g home or a new home, and configure listener, oracle gateway for Sql server initial file:
In our example, using new ORACLE_HOME /u01/app/oracle/product/11.1.0/db_1
1) [...]

How to use merge statement

Here is an example:
Table:
Create table t1(id number, hours date, total number);
merge into t1 a
using (select 200 id, to_date(‘2004-12-28 10′,’yyyy-mm-dd hh24′) hours from dual ) b
on(a.id=b.id and a.hours=b.hours)
when matched then
update set total=a.total+1
when not matched then
insert (a.id,a.hours,a.total) values(b.id,b.hours,1);
Note:
a. Statement after merge into and using should be table.
b. Statement after matched should be update statement; Statement after not [...]

Using trigger to audit delete on a table

create or replace trigger a_trigger
before delete on a
declare
username varchar2(20);
ipaddress varchar2(20);
sessionuser varchar2(20);
begin
SELECT sys_context(‘USERENV’,’OS_USER’) into username FROM dual;
SELECT sys_context(‘USERENV’,’IP_ADDRESS’) into ipaddress FROM dual;
SELECT sys_context(‘USERENV’,’SESSION_USER’) into sessionuser from dual;
insert a_delete_history (trigger_time,os_username,sess_username,ip_address) values (sysdate,username,sessionuser,ipaddress);
end a_trigger;

How-to find sqls NOT using bind variables?

Sometimes we need to get to know which sqls do not use bind variables. Here we can see how to find it out by using procedures.
 
create table t1 as select sql_text from v$sqlarea;
 
alter table t1 add sql_text_wo_constants varchar2(1000);
 
create or replace function
remove_constants( p_query in varchar2 ) return varchar2
as
    l_query long;
    l_char  varchar2(1 char);
    l_in_quotes boolean [...]

How-To Efficiently Truncate/Drop A Table With Many Extents

Scenario:
When we drop or truncate a large table, it is very slow sometimes.
 
Why slow?
1. When a truncate is issued, the checkpoint process does a complete scan (till 9204) of the buffer cache.  All of the dirty buffers of the object in the buffer cache are written down to disk.  All of the clean buffers [...]

How-to trace oracle sessions?

There are lots of methods to trace oracle sessions.
 
1. alter session set events ‘10046 trace name context forever,level 12′;
  alter session set tracefile_identifier=’10046′;
  alter session set timed_statistics = true;
  alter session set statistics_level=all;
  alter session set max_dump_file_size = unlimited;
Start trace:
  alter session set events ‘10046 trace name context forever,level 12′;
Stop trace:
  alter session set events [...]