Physical vs logical standby database in oracle 

The Data Guard Configuration consists of one Production database and upto nine Standby databases. The production database can be a single instance or RAC database similarly Standby database can be single instance or RAC database. It is also possible to have single instance standby database for a RAC production database.

The standby database is a transactionally consistent copy of the production database. It is created initially from the backup of production database. Once created, the data guard automatically synchronizes the standby.

There are two types of standby database, they are

1. Physical Standby
2. Logical Standby

What is physical standby database?

Physical standby database is physically identical to the primary database. It is block by block copy of the primary images. The archived redo log files are shipped to standby database and applied the archived redo log files on the standby database. So standby database should be always in recovery mode. This is like, DBA is sitting in remote location and recovering the primary database in different server by applying the archived redo log files.

What is logical standby database?

Logical standby database is logically identical to the primary database. Oracle use the logminer technology to transforms standard archived redo logs(by default) or redo logs(if real-time apply enabled) into SQL statements and applies them to the logical stand by database. A logical standby database can remain open and the same time its tables are updated from the primary database, and those tables are simultaneously available for read access. Oracle9i introduced logical standby database.

Physical standby is different from logical standby:

• Physical standby schema matches exactly the source database.

• Archived redo logs and FTP'ed directly to the standby database which is always running in "recover" mode.  Upon arrival, the archived redo logs are applied directly to the standby database.

Logical standby is different from physical standby:

• Logical standby database does not have to match the schema structure of the source database.

 • Logical standby uses LogMiner techniques to transform the archived redo logs into native DML statements (insert, update, delete).  This DML is transported and applied to the standby database.

 • Logical standby tables can be open for SQL queries (read only), and all other standby tables can be open for updates.

 • Logical standby database can have additional materialized views and indexes added for faster performance.

Configuring Physical standbys offers these benefits:

• An identical physical copy of the primary database

• Disaster recovery and high availability

• High Data protection

• Reduction in primary database workload

• Performance Faster

Configuring Logical standbys offer:

• Simultaneous use for reporting, summations and queries

• Efficient use of standby hardware resources

• Reduction in primary database workload

• Some limitations on the use of certain datatypes

A comparison

S.No	Physical Standby	Logical Standby
1	Identical to the Primary database including the physical organization in the disk	Same logical information but physical organization and the structure of data are different.
2	DG uses Redo Apply technology, which applies redo data using standard recovery techniques.	DG uses SQL Apply, which first transforms the redo data into SQL statements and then executes the statement.
3	Can be used for Backups	Can be opened for reporting.
4	All data types are supported	Not all data types are supported for eg. LONG, NCLOB, LONG RAW, BFILE, XML types are not supported. To see what other datatypes which may not be support in your logical standby database, you can query the view DBA_LOGSTDBY_UNSUPPORTED.
5	Can open in ‘Read only’ but cannot apply logs.	Can open in normal mode and simultaneously apply the logs.
6	No additional objects can be created.	Additional indexes, materialized views can be created

Usually organizations use Logical Standby databases mainly for reporting purposes and not for failover/switchover operations. For failover and switchover they use physical standby database. The reason is maintaining logical standby is almost a full time job, need extensive tuning of log apply services, and over hundreds of patches, the logical is usually 3 to 5 hours behind the live database, thus making it impossible for failover/switchover.

Note:-

Oracle recommends not to apply by DML operations on logical standby tables maintained by SQL Apply. This will introduce deviations between the primary and standby databases that will make it impossible for the logical standby database to be maintained.

As per Oracle10gR2, Some of the SQL statments will not be shipped from primary database to logical standby database. Some sample SQL statements are......

1. CREATE or ALTER or DROP MATERIALIZED VIEW

2. CREATE or ALTER or DROP MATERIALIZED VIEW LOG

3. ALTER SESSION

4. CREATE PFILE

5. CREATE or DROP DATABASE LINK

what is the use of bind variable in oracle 

Each time an SQL statement is sent to the database, an exact text match is performed to see if the statement is already present in the shared pool. If no matching statement is found a hard parse is performed, which is a resource intensive process. If the statement is found in the shared pool this step is not necessary and a soft parse is performed. 
Note:- oracle perform hard parse only when it does't find the sql hash value on the memory for the statement  it want to execute.
Concatenating variable values into an SQL statement makes the statement unique, forcing a hard parse. By contrast, using bind variables allow reuse of statements as the text of the statement remains the same. Only the value of the bind variable changes.
Consider the following example,

A table is created.
SQL> create table bind_ex (
  2     col_1 number,
  3     col_2 varchar2(10)
  4  );

Table created.

and filled with some values..
SQL> insert into bind_ex values (1, 'one'  );
1 row created.
SQL> insert into bind_ex values (2, 'two'  );
1 row created.
SQL> insert into bind_ex values (3, 'three');
1 row created.

Although the three insert statements do conceptually the same thing, they are not the same statement. This can be verified by examing v$sql ,
SQL> set linesize 100
SQL> select substr(sql_text, 1, 100) from v$sql where lower(sql_text) like '% bind_ex %';

SUBSTR(SQL_TEXT,1,100)
----------------------------------------------------------------------------------------------------
select substr(sql_text, 1, 100) from v$sql where lower(sql_text) like '% bind_ex %'
insert into bind_ex values (2, 'two'  )
insert into bind_ex values (3, 'three')
insert into bind_ex values (1, 'one'  )


The line returned is the statement to retrieve the other three. Since these three other statements are different, Oracle had to analyse them first in order to execute them. This is a costly operation and referred to as hard parse.
Since it is (sometimes) desirable to prevent such hard parses, bind variables can be used. In an SQL statements, bind variables are indicated by a colon (:) followed by a name or a number. So, these insert statements rewritten with bind variables will then look like: insert into bind_ex values(:bind_var_1, :bind_var_2).
In PL/SQL, execute immediate can be used for bind variables:


SQL> declare
  2    stmt constant varchar2(52) := 'insert into bind_ex values(:bind_var_1, :bind_var_2)';
  3  begin
  4    execute immediate stmt using 4, 'four';
  5    execute immediate stmt using 5, 'five';
  6    execute immediate stmt using 6, 'six' ;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Again checking v$sql:
SQL> select substr(sql_text, 1, 100) from v$sql where lower(sql_text) like '% bind_ex %';

SUBSTR(SQL_TEXT,1,100)
----------------------------------------------------------------------------------------------------
select substr(sql_text, 1, 100) from v$sql where lower(sql_text) like '% bind_ex %'
insert into bind_ex values (2, 'two'  )
insert into bind_ex values (3, 'three')
insert into bind_ex values (1, 'one'  )
insert into bind_ex values(:bind_var_1, :bind_var_2)
declare   stmt constant varchar2(52) := 'insert into bind_ex values(:bind_var_1, :bind_var_2)'; begi

6 rows selected.

SQL>

The statment returns two new Statements: the PL/SQL block and the one that was (three times) executed within the block. Note:- Thus the use of bind variable will reduce the hard parse and will improve the database performance to a great extend .

More good Links..

http://www.dbforums.com/oracle/1677686-procedure-using-bind-variable.html

http://www.oracle-base.com/articles/misc/literals-substitution-variables-and-bind-variables.php

Batch file for taking cold backup and expdp

In many case we need to take the cold backup , am happy to share this batch file that will  take the full database backup and finally it will automatically start the database after cold backup.

1. First create a file name backup.txt and save the following contents,
REM
echo off
echo coldbackup started
for /F "tokens=1-4 delims=/ " %%i in ('date /t') do (
set dayofweek=%%i
set month=%%j
set day=%%k
set year=%%l
set curtime=%time%
set fname=BOOST-%%i-%%j-%%k-%%l
)
md e:\dbbackup\%fname%
set oracle_sid=boost  --> here change your database name
sqlplus -s "sys/maku as sysdba" @e:\coldbackup.sql
echo cold backup completed succesffully.
pause

After saving , rename it as backup.bat .

2.Now create coldbackup.sql as follows ,
set lines 1000
set head off
set term off
set feedback off
spool e:\coldbackup.bat
select 'copy '||name||' e:\dbbackup\%fname%' from v$datafile;
select 'copy '||name||' e:\dbbackup\%fname%' from v$controlfile;
select 'copy '||name||' e:\dbbackup\%fname%' from v$tempfile;
select 'copy '||member||' e:\dbbackup\%fname%' from v$logfile;
spool off
shutdown IMMEDIATE
host e:\coldbackup.bat
startup
host del e:\coldbackup.bat
set term on
set head on
set feedback on
set verify on
EXIT

And keep this file in e:\ folder .

3. Our script is ready , now run the batchfile "backup.bat" . The script will first create a spool file named "coldbackup.bat" and it contain the necessary command to copy the files from  the command line , and  will execute this spooled batch file by host e:\coldbackup.bat after  successful execution we will delete this spooled batch file. We can see the cold backup got generated in e:\dbbackup folder.

A simple batch file for taking date-wise expdp of a schema 

@ECHO OFF
: Sets the proper date and time stamp with 24Hr Time for log file naming
: convention

SET HOUR=%time:~0,2%
SET dtStamp9=%date:~-4%%date:~4,2%%date:~7,2%_0%time:~1,1%%time:~3,2%%time:~6,2% 
SET dtStamp24=%date:~-4%%date:~4,2%%date:~7,2%_%time:~0,2%%time:~3,2%%time:~6,2%
if "%HOUR:~0,1%" == " " (SET dtStamp=%dtStamp9%) else (SET dtStamp=%dtStamp24%)

set PATH=D:\oracle\product\10.2.0\db_1\BIN;%PATH% --> set your own path 
D:\oracle\product\10.2.0\db_1\BIN\expdp aiwa/aiwa@proddb dumpfile=prod_aiwa_%dtStamp%.dmp logfile=prod_aiwa_%dtStamp%.log directory=DATA_PUMP_DIR schemas=aiwa
pause

Package in oracle with example

A package is a group of procedures, functions, variables and sql statements created as a single unit. It is used to store together related objects. A package has two parts, Package Specification or spec or package header and Package Body.

Package Specification acts as an interface to the package. Declaration of types, variables, constants, exceptions, cursors and subprograms is done in Package specifications. Package specification does not contain any code. Thus a package specification lists the functions and procedures in the package, with their call specifications: the arguments and their datatypes. It can also define variables and constants accessible to all the procedures and functions in the package.

Package body is used to provide implementation for the subprograms, queries for the cursors declared in the package specification or spec.

Example.1

1. package specification

SQL> create or replace package circle_area_peri is
  2  function area(r number) return number; ---> function area is declared with datatype.
  3  function perimeter(r number) return number; ---> function perimeter is declared  with datatype,
  4  end;
  5  /
Package created.

SQL>

2. package body

SQL> create or replace package body circle_area_peri is
  2  function area(r number) return number is --> function area is implemented here.
  3  ar number(7,2);
  4  begin
  5  ar := 3.14159*r*r;
  6  return ar;
  7  end;
  8  function perimeter(r number) return number is --> function perimeter is implemented here.
  9  pr number(7,2);
 10  begin
 11  pr := 2*3.14159*r;
 12  return pr;
 13  end;
 14  end;
 15  /

Package body created.

SQL>

For using the package , create sql file as follows ,
ed packagedemo
declare
r number(5,2);
area number(7,2);
perimeter number(7,2);
ar number(7);
pr number(7);
begin
dbms_output.put_line('CIRCLE');
dbms_output.put_line('Enter the radius:');
r := &r;
area := circle_area_peri.area(r);
perimeter := circle_area_peri.perimeter(r);
dbms_output.put_line('Area of the circle is :'||area);
dbms_output.put_line('Perimeter of the circle is :'||perimeter);
end;

Execute the above sql to see how package works,
SQL> @packagedemo
Enter value for r: 10
old  12: r := &r;
new  12: r := 10;
CIRCLE
Enter the radius:
Area of the circle is :314.16
Perimeter of the circle is :62.83

PL/SQL procedure successfully completed.

SQL>