SQL Command to check I/O bottle neck for sql statement in oracle database







>Sessions that are currently waiting for I/O resources:

SELECT username,

       program,

       machine,

       sql_id

  FROM v$session

 WHERE event LIKE 'db file%read';

> Which SQL statements are using a lots of disks:

 col schema format a20

 SELECT *

  FROM (  SELECT parsing_schema_name Schema, SQL_ID,

                 SUBSTR (sql_text, 1, 75) SQL,

                 disk_reads

            FROM v$sql

        ORDER BY disk_reads DESC)

 WHERE ROWNUM < 20;

 And with the result set of (1) or (2):

 set long 1000

 select SQL_FULLTEXT from v$sql;

O/P:

Database cloning using RMAN

In this workout we have,
Source database="ORACLE"
Duplicate database="CLONEDB"

Step-1:Create Password file for Auxiliary Database:

C:\Documents and Settings\suvdas>orapwd file='D:\app\EST_Suvendu\product\11.2.0\

dbhome_1\database\PWDclonedb' password=manager

Step-2: Create Initializing parameter file for auxiliary database:

SQL> create pfile='D:\app\EST_Suvendu\product\11.2.0\dbhome_1\database\initCLONEDB.ora' from spfile;

File created.

After creating the initialization parameter for the duplicate database, and change at leastthe following parameters:

           
db_file_name_convert = ('
D:\APP\EST_SUVENDU\oradata
\ORACLE', '
D:\APP\EST_SUVENDU\oradata
\CLONEDB')

log_file_name_convert = ('
D:\APP\EST_SUVENDU\oradata
\ORACLE', '
D:\APP\EST_SUVENDU\oradata
\CLONEDB')

control_files = '
D:\APP\EST_SUVENDU\oradata
\CLONEDB\control01.ctl'

              , '
D:\APP\EST_SUVENDU\oradata
\CLONEDB\control02.ctl'

              , '
D:\APP\EST_SUVENDU\oradata
\CLONEDB\control03.ctl'

db_name = 'CLONEDB'
instance_name = 'CLONEDB'
background_dump_dest = '
D:\APP\EST_SUVENDU
\admin\
CLONEDB
\bdump'
core_dump_dest = '
D:\APP\EST_SUVENDU
\admin\
CLONEDB
\cdump'
user_dump_dest = '
D:\APP\EST_SUVENDU
\admin\
CLONEDB
\udump'
dispatchers = '(PROTOCOL=TCP) (SERVICE=CLONEDBXDB)'
log_archive_dest_1 = 'location=
D:\APP\EST_SUVENDU\oradata
\
CLONEDB
\archive MANDATORY'

Step-3:Create/start Auxiliary database
Create a new Windows service for the duplicate database CLONEDB using oradim:

C:\Documents and Settings\suvdas> mkdir D:\APP\EST_SUVENDU\oradata\CLONEDB
C:\Documents and Settings\suvdas> mkdir D:\APP\EST_SUVENDU\oradata\CLONEDB\archive
C:\Documents and Settings\suvdas> mkdir D:\APP\EST_SUVENDU\admin\CLONEDB\bdump
C:\Documents and Settings\suvdas> mkdir D:\APP\EST_SUVENDU\admin\CLONEDB\bdump
C:\Documents and Settings\suvdas> mkdir D:\APP\EST_SUVENDU\admin\CLONEDB\create
C:\Documents and Settings\suvdas> mkdir D:\APP\EST_SUVENDU\admin\CLONEDB\bdump\pfile
C:\Documents and Settings\suvdas> mkdir D:\APP\EST_SUVENDU\admin\CLONEDB\bdump\scripts
C:\Documents and Settings\suvdas> mkdir D:\APP\EST_SUVENDU\admin\CLONEDB\bdump\udump

C:\Documents and Settings\suvdas>oradim -new -sid CLONEDB -intpwd manager -start

mode auto -pfile 'D:\app\EST_Suvendu\product\11.2.0\dbhome_1\database\initCLONEDB.ora

 C:\Documents and Settings\suvdas>set ORACLE_SID=CLONEDB

 C:\Documents and Settings\suvdas> sqlplus "/ as sysdba"

 SQL> startup nomount

Step-4:

The listener file should look like below:

# listener.ora Network Configuration File: D:\app\EST_Suvendu\product\11.2.0\dbhome_1\network\admin\listener.ora

# Generated by Oracle configuration tools.

LOCALLSNR =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.52)(PORT = 1521))

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

  )

SID_LIST_LOCALLSNR =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME =CLONEDB )

      (ORACLE_HOME = D:\app\EST_Suvendu\product\11.2.0\dbhome_1)

      (PROGRAM = extproc)

      (ENVS = "EXTPROC_DLLS=ONLY:D:\app\EST_Suvendu\product\11.2.0\dbhome_1\bin\oraclr11.dll")

    )

   (SID_DESC =

      (SID_NAME =oracle )

      (ORACLE_HOME = D:\app\EST_Suvendu\product\11.2.0\dbhome_1)

      (PROGRAM = extproc)

      (ENVS = "EXTPROC_DLLS=ONLY:D:\app\EST_Suvendu\product\11.2.0\dbhome_1\bin\oraclr11.dll")

    )

  )

ADR_BASE_LOCALLSNR = D:\app\EST_Suvendu

#CLRExtProc

Step-5: Mount/Open the source database "oracle" if not open,

C:\Documents and Settings\suvdas> set ORACLE_SID=ORACLE
C:\Documents and Settings\suvdas> sqlplus "/ as sysdba"
SQL> startup open

Step-6:

>rman sys/manager@oracle auxiliary sys/manager@clonedb

it will connect to RMAN session then give below commands:

RMAN>run {

    # Allocate the channel for the duplicate work

    allocate auxiliary channel ch1 type disk;

    # Duplicate the database to CLONEDB

    duplicate target database to CLONEDB;

}

/////////

below are results of cloning:

Redo Buffers                   5804032 bytes

allocated channel: ch1

channel ch1: SID=134 device type=DISK

contents of Memory Script:

{

   sql clone "alter system set  db_name =

 ''ORACLE'' comment=

 ''Modified by RMAN duplicate'' scope=spfile";

   sql clone "alter system set  db_unique_name =

 ''CLONEDB'' comment=

 ''Modified by RMAN duplicate'' scope=spfile";

   shutdown clone immediate;

   startup clone force nomount

   restore clone primary controlfile;

   alter clone database mount;

}

executing Memory Script

sql statement: alter system set  db_name =  ''ORACLE'' comment= ''Modified by RM

AN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''CLONEDB'' comment= ''Modifi

ed by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area     535662592 bytes

Fixed Size                     1375792 bytes

Variable Size                192938448 bytes

Database Buffers             335544320 bytes

Redo Buffers                   5804032 bytes

allocated channel: ch1

channel ch1: SID=134 device type=DISK

Starting restore at 13-MAY-13

channel ch1: starting datafile backup set restore

channel ch1: restoring control file

channel ch1: reading from backup piece D:\APP\EST_SUVENDU\RMANBKP\C-1687975765-2

0130510-01.CTL

channel ch1: piece handle=D:\APP\EST_SUVENDU\RMANBKP\C-1687975765-20130510-01.CT

L tag=TAG20130510T144129

channel ch1: restored backup piece 1

channel ch1: restore complete, elapsed time: 00:00:01

output file name=D:\APP\EST_SUVENDU\ORADATA\CLONEDB\CONTROL01.CTL

output file name=D:\APP\EST_SUVENDU\ORADATA\CLONEDB\CONTROL02.CTL

Finished restore at 13-MAY-13

database mounted

contents of Memory Script:

{

   set until scn  1288024;

   set newname for datafile  1 to

 "D:\APP\EST_SUVENDU\ORADATA\CLONEDB\SYSTEM01.DBF";

   set newname for datafile  2 to

 "D:\APP\EST_SUVENDU\ORADATA\CLONEDB\SYSAUX01.DBF";

   set newname for datafile  3 to

 "D:\APP\EST_SUVENDU\ORADATA\CLONEDB\UNDOTBS01.DBF";

   set newname for datafile  4 to

 "D:\APP\EST_SUVENDU\ORADATA\CLONEDB\USERS01.DBF";

   set newname for datafile  5 to

 "D:\APP\EST_SUVENDU\ORADATA\CLONEDB\EXAMPLE01.DBF";

   restore

   clone database

   ;

}

executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 13-MAY-13

channel ch1: starting datafile backup set restore

channel ch1: specifying datafile(s) to restore from backup set

channel ch1: restoring datafile 00002 to D:\APP\EST_SUVENDU\ORADATA\CLONEDB\SYSA

UX01.DBF

channel ch1: restoring datafile 00003 to D:\APP\EST_SUVENDU\ORADATA\CLONEDB\UNDO

TBS01.DBF

channel ch1: restoring datafile 00005 to D:\APP\EST_SUVENDU\ORADATA\CLONEDB\EXAM

PLE01.DBF

channel ch1: reading from backup piece D:\APP\EST_SUVENDU\RMANBKP\10O99OUC_1_1.D

BF

channel ch1: piece handle=D:\APP\EST_SUVENDU\RMANBKP\10O99OUC_1_1.DBF tag=TAG201

30510T144011

channel ch1: restored backup piece 1

channel ch1: restore complete, elapsed time: 00:00:25

channel ch1: starting datafile backup set restore

channel ch1: specifying datafile(s) to restore from backup set

channel ch1: restoring datafile 00001 to D:\APP\EST_SUVENDU\ORADATA\CLONEDB\SYST

EM01.DBF

channel ch1: restoring datafile 00004 to D:\APP\EST_SUVENDU\ORADATA\CLONEDB\USER

S01.DBF

channel ch1: reading from backup piece D:\APP\EST_SUVENDU\RMANBKP\11O99OUE_1_1.D

BF

channel ch1: piece handle=D:\APP\EST_SUVENDU\RMANBKP\11O99OUE_1_1.DBF tag=TAG201

30510T144011

channel ch1: restored backup piece 1

channel ch1: restore complete, elapsed time: 00:00:33

Finished restore at 13-MAY-13

contents of Memory Script:

{

   switch clone datafile all;

}

executing Memory Script

datafile 1 switched to datafile copy

input datafile copy RECID=7 STAMP=815332942 file name=D:\APP\EST_SUVENDU\ORADATA

\CLONEDB\SYSTEM01.DBF

datafile 2 switched to datafile copy

input datafile copy RECID=8 STAMP=815332942 file name=D:\APP\EST_SUVENDU\ORADATA

\CLONEDB\SYSAUX01.DBF

datafile 3 switched to datafile copy

input datafile copy RECID=9 STAMP=815332943 file name=D:\APP\EST_SUVENDU\ORADATA

\CLONEDB\UNDOTBS01.DBF

datafile 4 switched to datafile copy

input datafile copy RECID=10 STAMP=815332943 file name=D:\APP\EST_SUVENDU\ORADAT

A\CLONEDB\USERS01.DBF

datafile 5 switched to datafile copy

input datafile copy RECID=11 STAMP=815332943 file name=D:\APP\EST_SUVENDU\ORADAT

A\CLONEDB\EXAMPLE01.DBF

contents of Memory Script:

{

   set until scn  1288024;

   recover

   clone database

    delete archivelog

   ;

}

executing Memory Script

executing command: SET until clause

Starting recover at 13-MAY-13

starting media recovery

archived log for thread 1 with sequence 14 is already on disk as file D:\APP\EST

_SUVENDU\ORADATA\ARCHIVE\ORACLE\ARC0000000014_0814898327.0001

archived log for thread 1 with sequence 15 is already on disk as file D:\APP\EST

_SUVENDU\ORADATA\ARCHIVE\ORACLE\ARC0000000015_0814898327.0001

archived log for thread 1 with sequence 16 is already on disk as file D:\APP\EST

_SUVENDU\ORADATA\ARCHIVE\ORACLE\ARC0000000016_0814898327.0001

archived log for thread 1 with sequence 17 is already on disk as file D:\APP\EST

_SUVENDU\ORADATA\ARCHIVE\ORACLE\ARC0000000017_0814898327.0001

archived log for thread 1 with sequence 18 is already on disk as file D:\APP\EST

_SUVENDU\ORADATA\ARCHIVE\ORACLE\ARC0000000018_0814898327.0001

archived log for thread 1 with sequence 19 is already on disk as file D:\APP\EST

_SUVENDU\ORADATA\ARCHIVE\ORACLE\ARC0000000019_0814898327.0001

archived log file name=D:\APP\EST_SUVENDU\ORADATA\ARCHIVE\ORACLE\ARC0000000014_0

814898327.0001 thread=1 sequence=14

archived log file name=D:\APP\EST_SUVENDU\ORADATA\ARCHIVE\ORACLE\ARC0000000015_0

814898327.0001 thread=1 sequence=15

archived log file name=D:\APP\EST_SUVENDU\ORADATA\ARCHIVE\ORACLE\ARC0000000016_0

814898327.0001 thread=1 sequence=16

archived log file name=D:\APP\EST_SUVENDU\ORADATA\ARCHIVE\ORACLE\ARC0000000017_0

814898327.0001 thread=1 sequence=17

archived log file name=D:\APP\EST_SUVENDU\ORADATA\ARCHIVE\ORACLE\ARC0000000018_0

814898327.0001 thread=1 sequence=18

archived log file name=D:\APP\EST_SUVENDU\ORADATA\ARCHIVE\ORACLE\ARC0000000019_0

814898327.0001 thread=1 sequence=19

media recovery complete, elapsed time: 00:00:30

Finished recover at 13-MAY-13

contents of Memory Script:

{

   shutdown clone immediate;

   startup clone nomount;

   sql clone "alter system set  db_name =

 ''CLONEDB'' comment=

 ''Reset to original value by RMAN'' scope=spfile";

   sql clone "alter system reset  db_unique_name scope=spfile";

   shutdown clone immediate;

   startup clone nomount;

}

executing Memory Script

database dismounted

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area     535662592 bytes

Fixed Size                     1375792 bytes

Variable Size                192938448 bytes

Database Buffers             335544320 bytes

Redo Buffers                   5804032 bytes

allocated channel: ch1

channel ch1: SID=133 device type=DISK

sql statement: alter system set  db_name =  ''CLONEDB'' comment= ''Reset to orig

inal value by RMAN'' scope=spfile

sql statement: alter system reset  db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area     535662592 bytes

Fixed Size                     1375792 bytes

Variable Size                192938448 bytes

Database Buffers             335544320 bytes

Redo Buffers                   5804032 bytes

allocated channel: ch1

channel ch1: SID=133 device type=DISK

sql statement: CREATE CONTROLFILE REUSE SET DATABASE "CLONEDB" RESETLOGS ARCHIVE

LOG

  MAXLOGFILES     16

  MAXLOGMEMBERS      3

  MAXDATAFILES      100

  MAXINSTANCES     8

  MAXLOGHISTORY      292

 LOGFILE

  GROUP  1 ( 'D:\APP\EST_SUVENDU\ORADATA\CLONEDB\REDO01.LOG' ) SIZE 50 M  REUSE,

  GROUP  2 ( 'D:\APP\EST_SUVENDU\ORADATA\CLONEDB\REDO02.LOG' ) SIZE 50 M  REUSE,

  GROUP  3 ( 'D:\APP\EST_SUVENDU\ORADATA\CLONEDB\REDO03.LOG' ) SIZE 50 M  REUSE

 DATAFILE

  'D:\APP\EST_SUVENDU\ORADATA\CLONEDB\SYSTEM01.DBF'

 CHARACTER SET AL32UTF8

contents of Memory Script:

{

   set newname for tempfile  1 to

 "D:\APP\EST_SUVENDU\ORADATA\CLONEDB\TEMP01.DBF";

   switch clone tempfile all;

   catalog clone datafilecopy  "D:\APP\EST_SUVENDU\ORADATA\CLONEDB\SYSAUX01.DBF"

,

 "D:\APP\EST_SUVENDU\ORADATA\CLONEDB\UNDOTBS01.DBF",

 "D:\APP\EST_SUVENDU\ORADATA\CLONEDB\USERS01.DBF",

 "D:\APP\EST_SUVENDU\ORADATA\CLONEDB\EXAMPLE01.DBF";

   switch clone datafile all;

}

executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to D:\APP\EST_SUVENDU\ORADATA\CLONEDB\TEMP01.DBF in control f

ile

cataloged datafile copy

datafile copy file name=D:\APP\EST_SUVENDU\ORADATA\CLONEDB\SYSAUX01.DBF RECID=1

STAMP=815332990

cataloged datafile copy

datafile copy file name=D:\APP\EST_SUVENDU\ORADATA\CLONEDB\UNDOTBS01.DBF RECID=2

 STAMP=815332990

cataloged datafile copy

datafile copy file name=D:\APP\EST_SUVENDU\ORADATA\CLONEDB\USERS01.DBF RECID=3 S

TAMP=815332990

cataloged datafile copy

datafile copy file name=D:\APP\EST_SUVENDU\ORADATA\CLONEDB\EXAMPLE01.DBF RECID=4

 STAMP=815332990

datafile 2 switched to datafile copy

input datafile copy RECID=1 STAMP=815332990 file name=D:\APP\EST_SUVENDU\ORADATA

\CLONEDB\SYSAUX01.DBF

datafile 3 switched to datafile copy

input datafile copy RECID=2 STAMP=815332990 file name=D:\APP\EST_SUVENDU\ORADATA

\CLONEDB\UNDOTBS01.DBF

datafile 4 switched to datafile copy

input datafile copy RECID=3 STAMP=815332990 file name=D:\APP\EST_SUVENDU\ORADATA

\CLONEDB\USERS01.DBF

datafile 5 switched to datafile copy

input datafile copy RECID=4 STAMP=815332990 file name=D:\APP\EST_SUVENDU\ORADATA

\CLONEDB\EXAMPLE01.DBF

contents of Memory Script:

{

   Alter clone database open resetlogs;

}

executing Memory Script

database opened

Finished Duplicate Db at 13-MAY-13

released channel: ch1

RMAN>

RMAN> exit

Recovery Manager complete.

C:\Documents and Settings\suvdas>sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Mon May 13 17:27:55 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Enter user-name: sys as sysdba

Enter password:

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name from v$instance,v$database;

NAME

---------

CLONEDB

SQL> select name,status from v$instance,v$database;

NAME      STATUS

--------- ------------

CLONEDB   OPEN

SQL>

===============Hope it will help you================================

RMAN backup and recovery for oracle database

RMAN
Backup and recovery manager...

Benefit of RMAN:
Database on Any OS, can be taken backup with only RMAN...
RMAN should be used for ASM.

RMAN Client --- > CATALOG Database
RMAN Server --- > TARGET Database

One RMAN catalog can take the backup and keep the backup information of any number of target databases on any OS.



RMAN Demo:

>>> RMAN Catalog Creation.

1 Identify the database to be used for CATALOG operations.
2 Create a tablespace of size 50m, to be used for RMAN catalog tables.
3 Create a user and grant rman permissions, to perform rman operations.
4 Connect to RMAN prompt and create catalog tables.


1. Identified orcl as catalog database.
set ORACLE_SID=orcl
sqlplus "/as sysdba"

2. Create a tablespace of size 50m.
create tablespace ts20 datafile'C:\app\prabhu\oradata\orcl\ts20_01.dbf' size 50m;

3. Create user and grant permissions..
SQL> create user suvendu identified by suvendu default tablespace ts20;
User created.
SQL> grant connect, resource to suvendu;
Grant succeeded.
SQL> grant recovery_catalog_owner to suvendu;
Grant succeeded.

4. Connect to RMAN prompt and create catalog tables.
SQL> host
Microsoft Windows [Version 6.1.7600]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:\Users\Prabhu>rman catalog suvendu/suvendu

Recovery Manager: Release 11.1.0.7.0 - Production on Thu Mar 1 15:34:05 2012
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
connected to recovery catalog database

RMAN> create catalog;
recovery catalog created

RMAN> exit
Recovery Manager complete.

C:\Users\Prabhu>exit
SQL> conn suvendu/suvendu
Connected.
SQL> select count(*) from tab;
  COUNT(*)
----------
       142
SQL> conn /as sysdba
Connected.
SQL>

========== Completes Catalog Creation =============

>>> Target Configuration to take backup using catalog.

1. Identify target database to be backed up.
2. It has to be in Archive Mode.
3. Check target database password file availability.
4. Configure and check connectivity for SYSDBA from catalog to target.
rman target / catalog user/pwd@tns -- from target to catalog
rman target user/pwd@tns catalog user/pwd -- from catalog to target
rman target / nocatalog -- No Catalog, Target cfile will be used for info
5. Connect to RMAN and Register target database with rman catalog.
6. Configure rman parameters.
7. Take backup to backup location.

1. Identify target database to be backed up.
C:\Users\Prabhu>set ORACLE_SID=oracle
C:\Users\Prabhu>sqlplus "/as sysdba"

2. It has to be in Archive Mode.
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled

3. Check target database password file availability.
SQL> select * from v$pwfile_users;
USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE  TRUE  FALSE
SQL> alter user sys identified by sys;
User altered.

Alternate Method to create password file..

C:\app\Prabhu\product\11.1.0\db_1\database>orapwd file='PWDoracle.ora' password=
sys force=y

4. Configure and check connectivity for SYSDBA from catalog to target.
tnsping oracle
sqlplus sys/pwd@oracle as sysdba

5. Connect to RMAN and Register target database with rman catalog.

C:\Users\Prabhu>rman catalog suvendu/suvendu target sys/sys@oracle

Recovery Manager: Release 11.1.0.7.0 - Production on Thu Mar 1 15:56:28 2012
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
connected to target database: ORACLE (DBID=1644963733)
connected to recovery catalog database

RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

6. Configure rman parameters.
Rman> show all;

RMAN configuration parameters for database with db_unique_name ORACLE are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION on; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default (sbt_tape)
CONFIGURE CONTROLFILE AUTOBACKUP on; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'C:\app\Prabhu\rmanbkp\%F.ctl'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET; # default
configure channel 1 device type disk format 'C:\app\Prabhu\rmanbkp\%U.dbf';
configure channel 2 device type disk format 'C:\app\Prabhu\rmanbkp\%U.dbf';
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE BACKUPSET SIZE TO 20000m; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BZIP2'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'C:\APP\PRABHU\PRODUCT\11.1.0\DB_1\DATABASE\SNCFORACLE.ORA'; # default


7. Take backup to backup location.
open new terminal.

set ORACLE_SID=orcl
rman catalog suvendu/suvendu target sys/sys@oracle
rman> backup database;


============= Rman Configuration and backup complete ============

Q. What all the types of rman backup?
Ans Rman backups are of two types:
BACKUPSET -  Content bkp
COPY backup. -  As Is bkp.

Test For RMAN backup :Output



C:\Users\Prabhu>set ORACLE_SID=orclMicrosoft Windows [Version 6.1.7600]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:\Users\Prabhu>set ORACLE_SID=orcl

C:\Users\Prabhu>rman catalog ravi/ravi target sys/sys@oracle

Recovery Manager: Release 11.1.0.7.0 - Production on Thu Mar 1 16:14:46 2012

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: ORACLE (DBID=1644963733)
connected to recovery catalog database

RMAN>

RMAN> backup database;

Starting backup at 01-MAR-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=134 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=140 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=C:\APP\PRABHU\ORADATA\ORACLE\SYSAUX01.DBF
input datafile file number=00005 name=C:\APP\PRABHU\ORADATA\ORACLE\EXAMPLE01.DBF

channel ORA_DISK_1: starting piece 1 at 01-MAR-12
channel ORA_DISK_2: starting full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00001 name=C:\APP\PRABHU\ORADATA\ORACLE\SYSTEM01.DBF
input datafile file number=00004 name=C:\APP\PRABHU\ORADATA\ORACLE\USERS01.DBF
channel ORA_DISK_2: starting piece 1 at 01-MAR-12
channel ORA_DISK_1: finished piece 1 at 01-MAR-12
piece handle=C:\APP\PRABHU\RMANBKP\01N4PSCQ_1_1.CTL tag=TAG20120301T161521 comme
nt=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:26
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00003 name=C:\APP\PRABHU\ORADATA\ORACLE\UNDOTBS01.DBF

channel ORA_DISK_1: starting piece 1 at 01-MAR-12
channel ORA_DISK_2: finished piece 1 at 01-MAR-12
piece handle=C:\APP\PRABHU\RMANBKP\02N4PSCQ_1_1.CTL tag=TAG20120301T161521 comme
nt=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:01:26
channel ORA_DISK_1: finished piece 1 at 01-MAR-12
piece handle=C:\APP\PRABHU\RMANBKP\03N4PSFH_1_1.CTL tag=TAG20120301T161521 comme
nt=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 01-MAR-12

Starting Control File and SPFILE Autobackup at 01-MAR-12
piece handle=C:\APP\PRABHU\RMANBKP\C-1644963733-20120301-00.CTL comment=NONE
Finished Control File and SPFILE Autobackup at 01-MAR-12

RMAN>

RMAN> configure channel 1 device type disk format 'C:\app\Prabhu\rmanbkp\%U.dbf'
;

old RMAN configuration parameters:
CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT   'C:\app\Prabhu\rmanbkp\%U.ctl';
new RMAN configuration parameters:
CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT   'C:\app\Prabhu\rmanbkp\%U.dbf';
new RMAN configuration parameters are successfully stored
released channel: ORA_DISK_1
released channel: ORA_DISK_2
starting full resync of recovery catalog
full resync complete

RMAN> configure channel 2 device type disk format 'C:\app\Prabhu\rmanbkp\%U.dbf'
;

old RMAN configuration parameters:
CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT   'C:\app\Prabhu\rmanbkp\%U.ctl';
new RMAN configuration parameters:
CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT   'C:\app\Prabhu\rmanbkp\%U.dbf';
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

RMAN>

RMAN>

RMAN>

RMAN> list backup summary;

starting full resync of recovery catalog
full resync complete

List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
58      B  F  A DISK        01-MAR-12       1       1       NO         TAG201203
01T161521
59      B  F  A DISK        01-MAR-12       1       1       NO         TAG201203
01T161521
60      B  F  A DISK        01-MAR-12       1       1       NO         TAG201203
01T161521
73      B  F  A DISK        01-MAR-12       1       1       NO         TAG201203
01T161702

RMAN> backup database tag Thu_01Mar12
2> ;

Starting backup at 01-MAR-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=134 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=140 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=C:\APP\PRABHU\ORADATA\ORACLE\SYSAUX01.DBF
input datafile file number=00005 name=C:\APP\PRABHU\ORADATA\ORACLE\EXAMPLE01.DBF

channel ORA_DISK_1: starting piece 1 at 01-MAR-12
channel ORA_DISK_2: starting full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00001 name=C:\APP\PRABHU\ORADATA\ORACLE\SYSTEM01.DBF
input datafile file number=00004 name=C:\APP\PRABHU\ORADATA\ORACLE\USERS01.DBF
channel ORA_DISK_2: starting piece 1 at 01-MAR-12
channel ORA_DISK_1: finished piece 1 at 01-MAR-12
piece handle=C:\APP\PRABHU\RMANBKP\05N4PSIP_1_1.DBF tag=THU_01MAR12 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:01:28
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00003 name=C:\APP\PRABHU\ORADATA\ORACLE\UNDOTBS01.DBF

channel ORA_DISK_1: starting piece 1 at 01-MAR-12
channel ORA_DISK_2: finished piece 1 at 01-MAR-12
piece handle=C:\APP\PRABHU\RMANBKP\06N4PSIP_1_1.DBF tag=THU_01MAR12 comment=NONE

channel ORA_DISK_2: backup set complete, elapsed time: 00:01:26
channel ORA_DISK_1: finished piece 1 at 01-MAR-12
piece handle=C:\APP\PRABHU\RMANBKP\07N4PSLH_1_1.DBF tag=THU_01MAR12 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 01-MAR-12

Starting Control File and SPFILE Autobackup at 01-MAR-12
piece handle=C:\APP\PRABHU\RMANBKP\C-1644963733-20120301-01.CTL comment=NONE
Finished Control File and SPFILE Autobackup at 01-MAR-12

RMAN> list backup summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
58      B  F  A DISK        01-MAR-12       1       1       NO         TAG201203
01T161521
59      B  F  A DISK        01-MAR-12       1       1       NO         TAG201203
01T161521
60      B  F  A DISK        01-MAR-12       1       1       NO         TAG201203
01T161521
73      B  F  A DISK        01-MAR-12       1       1       NO         TAG201203
01T161702
116     B  F  A DISK        01-MAR-12       1       1       NO         THU_01MAR
12
117     B  F  A DISK        01-MAR-12       1       1       NO         THU_01MAR
12
118     B  F  A DISK        01-MAR-12       1       1       NO         THU_01MAR
12
136     B  F  A DISK        01-MAR-12       1       1       NO         TAG201203
01T162011

RMAN> backup as copy database;

Starting backup at 01-MAR-12
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=C:\APP\PRABHU\ORADATA\ORACLE\SYSTEM01.DBF
channel ORA_DISK_2: starting datafile copy
input datafile file number=00002 name=C:\APP\PRABHU\ORADATA\ORACLE\SYSAUX01.DBF
output file name=C:\APP\PRABHU\RMANBKP\DATA_D-ORACLE_I-1644963733_TS-SYSTEM_FNO-
1_09N4PSQ7.DBF tag=TAG20120301T162231 RECID=3 STAMP=776795016
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:08
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=C:\APP\PRABHU\ORADATA\ORACLE\EXAMPLE01.DBF

output file name=C:\APP\PRABHU\RMANBKP\DATA_D-ORACLE_I-1644963733_TS-SYSAUX_FNO-
2_0AN4PSQ7.DBF tag=TAG20120301T162231 RECID=2 STAMP=776795016
channel ORA_DISK_2: datafile copy complete, elapsed time: 00:01:01
channel ORA_DISK_2: starting datafile copy
input datafile file number=00003 name=C:\APP\PRABHU\ORADATA\ORACLE\UNDOTBS01.DBF

output file name=C:\APP\PRABHU\RMANBKP\DATA_D-ORACLE_I-1644963733_TS-EXAMPLE_FNO
-5_0BN4PSSB.DBF tag=TAG20120301T162231 RECID=4 STAMP=776795025
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:06
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=C:\APP\PRABHU\ORADATA\ORACLE\USERS01.DBF
output file name=C:\APP\PRABHU\RMANBKP\DATA_D-ORACLE_I-1644963733_TS-UNDOTBS1_FN
O-3_0CN4PSSC.DBF tag=TAG20120301T162231 RECID=5 STAMP=776795028
channel ORA_DISK_2: datafile copy complete, elapsed time: 00:00:03
output file name=C:\APP\PRABHU\RMANBKP\DATA_D-ORACLE_I-1644963733_TS-USERS_FNO-4
_0DN4PSSJ.DBF tag=TAG20120301T162231 RECID=6 STAMP=776795029
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 01-MAR-12

Starting Control File and SPFILE Autobackup at 01-MAR-12
piece handle=C:\APP\PRABHU\RMANBKP\C-1644963733-20120301-02.CTL comment=NONE
Finished Control File and SPFILE Autobackup at 01-MAR-12


RMAN> exit

Recovery Manager complete.

============Hope It will help you to learn about RMAN======================