Rman backup through batchfile how to?

we can use rman along with batch file to take the database backup. 

First you have to create a rcv with following contents . I name it as bkp.rcv  and kept in d: drive.

The contents of my bkp.rcv is given below

backup datafile 1 format 'c:\rman\datafile_1_%D%M%Y%U';

backup datafile 2 format 'c:\rman\datafile_2_%D%M%Y%U';

backup datafile 3 format 'c:\rman\datafile_3_%D%M%Y%U';

backup datafile 4 format 'c:\rman\datafile_4_%D%M%Y%U';

backup datafile 5 format 'c:\rman\datafile_5_%D%M%Y%U';

backup datafile 6 format 'd:\rman\datafile_6_%D%M%Y%U';

backup datafile 7 format 'c:\rman\datafile_7_%D%M%Y%U';

exit;

Next create a file named rman_backup.txt , and insert the following contents ,set the PATH and ORACLE_SID accordingly .

set PATH=C:\oracle\product\10.2.0\Asm_1\bin;%PATH%

set ORACLE_SID=oracle1

C:\oracle\product\10.2.0\Asm_1\bin\rman target sys/sys cmdfile d:\bkp.rcv log d:\rmanbkp.log

pause

After creating the file rename it as rman_backup.bat and execute the batch file.

Hopes it will help for anybody :)

Oracle isql plus

iSQLPlus was first introduced with Oracle 8i to reflect the company's focus on Internet technology. iSQLPlus required that the Oracle SQL*Net drivers be installed on the client as well as the iSQLPlus program itself.

Starting with Oracle 9i, iSQLPlus was delivered as a web application. This removed the need to install client side software and enabled any client with an web browser to connect to iSQLPlus to run database queries.

Oracle decided to desupport iSQLPlus with the Oracle 11g release. Users should migrate to SQL Developer instead.

ISQL*PLUS:

• Recognizes SQL statements and sends them to the server.
• Oracle proprietary interface for executing SQL statements.
• Does not allows manipulation of values in the database.
• Uses commands for formatting data.

Oracle stopped supporting iSQL*Plus starting with Oracle11g. Oracle recommend switching to SQL Developer which can be downloaded for free from http://www.oracle.com/technetwork/developer-tools/sql-developer/overview/index.html. 

SQL Developer can do all of the same things iSQL*Plus can do, plus more. There are other third-party Oracle DBA/Developer tools available for purchase which I will not promote in this post, but SQL Developer should meet your needs and is easy to install/use.

Configuring I-SQL Plus

STEP 1

goto dos prompt.....  start...Run....Cmd

on command prompt type:  isqlplusctl start

after issuing the above command following prompt will displayed

Starting iSQL*Plus ...

iSQL*Plus started.

STEP 2

Open Any compatible Internet Browser.

Type the address for ISQL*PLUS

http://PC-NAME:5560/isqlplus/ 

Syntax

http:// host_name:portnumber/isqlplus

to check Port number:

oracle_home=where oracle software installed.

oracle_home\install\portlist.ini

step 3

after opening the iSQL*Plus page on Expolorer.. 

LOGIN SCREEN 

ENTER username , password and the connection string 

Now isqlplus started.
you can query the database here...

Some good question and answers that i got from different oracle forums 

1.I am not able to get much details about the use of STANDBY_ARCHIVE_DEST . Can anyone clarify the same ? Is it the place where archived logs of standby redo logs are stored ? In that case if we have both STANDBY_ARCHIVE_DEST and LOG_ARCHIVE_DEST set then whether both of them are used , if so in which cases each destination is used ?

Ans:- If both STANDBY_ARCHIVE_DEST and LOG_ARCHIVE_DEST_n are specified, then STANDBY_ARCHIVE_DEST will be used, however it is sufficient enough to simply use LOG_ARCHIVE_DEST_n.  STANDBY_ARCHIVE_DEST is actually deprecated in 11g.

2. what is the need of standby redo log file??
Ans:- Standby redo logs are necessary for the higher protection levels such as 
Guaranteed, Instant, and Rapid. In these protection modes LGWR from the 
Primary host writes transactions directly to the standby redo logs. 
This enables no data loss solutions and reduces the amount of data loss 
in the event of failure. Standby redo logs are not necessary if you are using 
the delayed protection mode. 

If you configure standby redo on the standby then you should also configure 
standby redo logs on the primary database. Even though the standby redo logs
are not used when the database is running in the primary role, configuring 
the standby redo logs on the primary database is recommended in preparation 
for an eventual switchover operation. 
Standby redo logs must be archived before the data can be applied to the 
standby database. The standby archival operation occurs automatically, even if 
the standby database is not in ARCHIVELOG mode. However, the archiver process 
must be started on the standby database. Note that the use of the archiver 
process (ARCn) is a requirement for selection of a standby redo log.

You must have the same number of standby redo logs on the standby as you have 
online redo logs on production. They must also be exactly the same size.

The following syntax is used to create standby redo logs:

SQL> alter database add standby logfile
2 '/export/home/oracle/temp/oracle/data/srl_1.dbf' size 20m;

Database altered.
SQL> alter database add standby logfile
2 '/export/home/oracle/temp/oracle/data/srl_2.dbf' size 20m;

Database altered.

SQL> alter database add standby logfile
2 '/export/home/oracle/temp/oracle/data/srl_3.dbf' size 20m;

Database altered.


 3. what is the importance of DBID in rman??

In the case that you need to restore control file or spfile using RMAN , you need to set the DBID first.

4. RMAN Not able to find Controlfile autobackup. what may be the reason?
If Your source database has the controlfile autobackup set to a non-default location , for example
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/backup/%d/controlfile_backup_%d_%F';
Set the controlfile autobackup format on the target and try to restore it.

Refer this http://waseemslearning.blogspot.com/2011/03/rman-06563-control-file-or-spfile-must.html

Suppose on the target server, you have copied the controlfile autobackup to location "/u01/autobackup/", then on the target server do as:
RMAN>run
{
set controlfile autobackup format for device type disk to '/u01/autobackup/%F';
restore controlfile from autobackup;
}

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>