RMAN duplicate in oracle 11g

Ref: http://oracleinaction.com/duplicate-db-no-db-conn/


duplicate a database from its backups without any
connection to the source database. This method can be used if source database is not
available .
********************************
  source database  orcl
  Duplicate database  orclt
********************************
Overview:
on the source  host
- BACKUP DATABASE PLUS ARCHIVELOG AND CONTROLFILE
-  Copy these backup files to the server where you want to create the duplicate copy.
- CREATE PFILE FROM SOURCE DATABASE
on the target host
- Add a line in the file /etc/oratab to reflect the database instance you are going to copy
- create folders
- Copy the backup files from the source database
- Copy the initialization parameter file from the source database add edit it.
- Copy the password file
- Startup the target database in nomount mode using modified parameter file
- Using RMAN  connect to the  duplicate database (orclt) as auxiliary instance
- Specify the location of the backups and duplicate the database orcl to orclt
Implementation
————–
*******************
on the source  host
*******************
- BACKUP DATABASE PLUS ARCHIVELOG AND CONTROLFILE
—-
—–
oracle@source$mkdir/home/oracle/stage
oracle@source$. oraenv orcl
              rman target /
RMAN>backup database format ‘/home/oracle/stage/%U.bak’;
     backup archivelog all format ‘/home/oracle/stage/arch_%r%_s_%t.bak’
 ;
     backup current controlfile format ‘/home/oracle/stage/control.bak’;
– CREATE PFILE FROM SOURCE DATABASE
SQL>CREATE PFILE=’/home/oracle/stage/initsource.ora’
    FROM SPFILE;
*****************
 on the target host.
*****************
– Make a staging folder for backups and pfile
oracle@dest$mkdir -p /home/oracle/stage
– create other required  folders
 $mkdir -p /u01/app/oracle/oradata/orclt
  mkdir -p /u01/app/oracle/flash_recovery_area/orclt
  mkdir -p /u01/app/oracle/admin/orclt/adump
  mkdir -p /u01/app/oracle/admin/orclt/dpdump
– Copy backup files from the source host
 # scp source:/home/oracle/stage/*.bak /home/oracle/stage/
– Copy pfile of source database (orcl)
 # scp source:/home/oracle/stage/initsource.ora /home/oracle/stage/inittarget.ora
– Copy the password file as well 
 $ cp /u01/app/oracle/product/11.2.0/db_1/dbs/orapworcl  /u01/app/oracle/product/11.2.0/db_1/dbs/orapworclt

– Add a line in the file /etc/oratab to reflect the database instance you are going to  copy:
orclt:/u01/app/oracle/product/11.2.0/db1:N
– Edit the initialization parameter file from the main database.
$vi /home/oracle/stage/inittarget.ora
   – Change db_name = orclt
   – Edit it to reflect the new locations that might be appropriate
     such as control file locations,audit dump destinations, datafile
     locations, etc.
   – add these lines –
     db_file_name_convert = (“/u01/app/oracle/oradata/orcl”,
                             “/u01/app/oracle/oradata/orclt”)
     log_file_name_convert = (“/u01/app/oracle/oradata/orcl”,
                             “/u01/app/oracle/oradata/orclt”)
In case sorce and destination databases ae ASM, following lines can be added :
db_file_name_convert = (“+DATA/orcl”,”+DATA/orclt”)
log_file_name_convert = (“+DATA/orcl”,”+DATA/orclt”, “+FRA/orcl”,”+FRA/orclt”)
– Now set the Oracle SID as the duplicated database SID:
$ . oraenv
ORACLE_SID = [orclt] ?
– Startup the target database in nomount mode using modified parameter file
$sqlplus sys/oracle as sysdba
SQL> startup nomount pfile=’/u01/app/oracle/oradata/orclt/inittarget.ora’;
     create spfile from pfile=’/u01/app/oracle/oradata/orclt/inittarget.ora’;
- Using RMAN  connect to the  duplicate database (orclt) as auxiliary instance
$. oraenv
   orclt
$rman auxiliary /
– duplicate the database orcl to orclt
– the command performs the following steps:
    * Creates an SPFILE
    * Shuts down the instance and restarts it with the new spfile
    * Restores the controlfile from the backup
    * Mounts the database
    * Performs restore of the datafiles. In this stage it creates the files in the
      converted names.
    * Recovers the datafiles up to the time specified and opens the database
– If duplicate database has the same directory structure as source (on a different host)
RMAN>duplicate target database to orclt backup location ‘/home/oracle/stage/’ nofilenamecheck;
OR
– If duplicate database has different directory structure from source
RMAN>duplicate target database to orclt backup location ‘/home/oracle/stage/’ ;
– check that duplicate database is up
$sqlplus / as sysdba
sql>conn hr/hr
    select * from tab;
– Note that  DBID is different from the main database so it can be backed up
   independently and using the same catalog as well.
SQL> select dbid from v$database;
     conn sys/oracle@orcl as sysdba
     select dbid from v$database;

Dataguard status sync -

Ref: http://select-star-from.blogspot.com.au/search/label/DATAGUARD


Method :1
=========
PRIMARY
========

sqlplus "/ as sysdba"
set pages 50000 lines 32767col OPEN_MODE for a10
col HOST_NAME for a10
col DATABASE_ROLE for a7

select NAME,DB_UNIQUE_NAME,INSTANCE_NAME,HOST_NAME,OPEN_MODE,LOG_MODE,DATABASE_STATUS,DATABASE_ROLE,SWITCHOVER_STATUS,PROTECTION_MODE,PROTECTION_LEVEL,CONTROLFILE_TYPE,STATUS,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,gv$instance;

SELECT NAME,OPEN_MODE,DATABASE_ROLE,PROTECTION_LEVEL,SWITCHOVER_STATUS,STATUS,LOGINS FROM V$DATABASE,GV$INSTANCE;

select THREAD#,SEQUENCE#,STATUS,ARCHIVED from v$log;

select dest_id,database_mode,recovery_mode,protection_mode,archived_seq#,applied_seq# from v$archive_dest_Status where dest_id in (1,2);

set pages 50000 lines 32767Set scan off
Set feed off
BREAK ON ROW SKIP 1
COL "THREAD" for a10
COL "PR-ARCHIVED" for a15
COL "STBY-ARCHIVED" for a15
COL "STBY-APPLIED" for a 15
COL "SHIPPING GAP(PR -> STBY)" for a20
COL "APPLIED GAP(STBY -> STBY)" for a20
set head off
select 'sysdate: '|| to_char(sysdate,'DD-Mon-YYYY Hh24:Mi:ss') from dual;
select '****************Standby Log ship and Log Apply Status*****************' from dual;
set head on
select DEST_ID,DESTINATION,TARGET,STATUS from V$ARCHIVE_DEST where DESTINATION is not null;
select * from (
select
lpad(t1,4,' ') "Thread",
lpad(pricre,9,' ') "PR - Archived",
lpad(stdcre,10,' ') "STBY - Archived",
lpad(stdnapp,9,' ') "STBY - Applied",
lpad(pricre-stdcre,13,' ') "Shipping GAP (PR -> STBY)",
lpad(stdcre-stdnapp,15,' ') "Applied GAP (STBY -> STBY)"
from
(select max(sequence#) stdcre, thread# t1 from v$archived_log where standby_dest='YES'  and resetlogs_id in (select max(RESETLOGS_ID) from v$archived_log) and thread# in (1,2,3,4) group by thread#) a,
(select max(sequence#) stdnapp, thread# t2 from v$archived_log where standby_dest='YES'  and resetlogs_id in (select max(RESETLOGS_ID) from v$archived_log) and thread# in (1,2,3,4) and applied='YES' group by thread#) b,
(select max(sequence#) pricre, thread# t3 from v$archived_log where standby_dest='NO' and resetlogs_id in (select max(RESETLOGS_ID) from v$archived_log) and  thread# in (1,2,3,4) group by thread#) c
where a.t1=b.t2 and b.t2=c.t3 and c.t3=a.t1) order by 1
/

THREAD PR-ARCHIVED STBY-ARCHIVED STBY-APPLIED SHIPPING GAP(PR -> STBY) APPLIED GAP(STBY -> STBY)
------ ----------- ------------- ------------ ------------------------ -------------------------

Switch log minimun 3 times
--------------------------
alter system switch logfile;       or
alter system switch all logfile;   or
alter system archive log current;

set pages 50000 lines 32767Set scan off
Set feed off
BREAK ON ROW SKIP 1
COL "THREAD" for a10
COL "PR-ARCHIVED" for a15
COL "STBY-ARCHIVED" for a15
COL "STBY-APPLIED" for a 15
COL "SHIPPING GAP(PR -> STBY)" for a20
COL "APPLIED GAP(STBY -> STBY)" for a20
set head off
select 'sysdate: '|| to_char(sysdate,'DD-Mon-YYYY Hh24:Mi:ss') from dual;
select '****************Standby Log ship and Log Apply Status*****************' from dual;
set head on
select DEST_ID,DESTINATION,TARGET,STATUS from V$ARCHIVE_DEST where DESTINATION is not null;
select * from (
select
lpad(t1,4,' ') "Thread",
lpad(pricre,9,' ') "PR - Archived",
lpad(stdcre,10,' ') "STBY - Archived",
lpad(stdnapp,9,' ') "STBY - Applied",
lpad(pricre-stdcre,13,' ') "Shipping GAP (PR -> STBY)",
lpad(stdcre-stdnapp,15,' ') "Applied GAP (STBY -> STBY)"
from
(select max(sequence#) stdcre, thread# t1 from v$archived_log where standby_dest='YES'  and resetlogs_id in (select max(RESETLOGS_ID) from v$archived_log) and thread# in (1,2,3,4) group by thread#) a,
(select max(sequence#) stdnapp, thread# t2 from v$archived_log where standby_dest='YES'  and resetlogs_id in (select max(RESETLOGS_ID) from v$archived_log) and thread# in (1,2,3,4) and applied='YES' group by thread#) b,
(select max(sequence#) pricre, thread# t3 from v$archived_log where standby_dest='NO' and resetlogs_id in (select max(RESETLOGS_ID) from v$archived_log) and  thread# in (1,2,3,4) group by thread#) c
where a.t1=b.t2 and b.t2=c.t3 and c.t3=a.t1) order by 1
/

THREAD PR-ARCHIVED STBY-ARCHIVED STBY-APPLIED SHIPPING GAP(PR -> STBY) APPLIED GAP(STBY -> STBY)
------ ----------- ------------- ------------ ------------------------ -------------------------

==================================================================================================================

Method :2
=========

STANDBY
========
sqlplus "/ as sysdba"

set pages 50000 lines 32767
col OPEN_MODE for a10
col HOST_NAME for a10
col DATABASE_ROLE for a7

select NAME,DB_UNIQUE_NAME,INSTANCE_NAME,HOST_NAME,OPEN_MODE,LOG_MODE,DATABASE_STATUS,DATABASE_ROLE,SWITCHOVER_STATUS,PROTECTION_MODE,PROTECTION_LEVEL,CONTROLFILE_TYPE,STATUS,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,gv$instance;

SELECT NAME,OPEN_MODE,DATABASE_ROLE,PROTECTION_LEVEL,SWITCHOVER_STATUS,STATUS,LOGINS FROM V$DATABASE,GV$INSTANCE;

select THREAD#,SEQUENCE#,STATUS,ARCHIVED from v$log;

SELECT PROCESS, CLIENT_PROCESS, SEQUENCE#, STATUS,THREAD#, DELAY_MINS FROM GV$MANAGED_STANDBY;

select dest_id,database_mode,recovery_mode,protection_mode,archived_seq#,applied_seq# from v$archive_dest_Status where dest_id in (1,2);

PROMPT
PROMPT
PROMPT Run on Standby Database. This script checks last log applied and last log received time
PROMPT
PROMPT

col time format a40
select 'Last applied  : ' Logs, to_char(next_time,'DD-MON-YY:HH24:MI:SS') Time
    from v$archived_log
    where sequence# = (select max(sequence#) from v$archived_log where applied='YES')
    union
    select 'Last received : ' Logs, to_char(next_time,'DD-MON-YY:HH24:MI:SS') Time
    from v$archived_log
   where sequence# = (select max(sequence#) from v$archived_log);
PROMPT last sequence# received and the last sequence# applied to standby database.
PROMPT
select al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied"
from (select thread# thrd, max(sequence#) almax
from v$archived_log
where resetlogs_change#=(select resetlogs_change# from v$database)
group by thread#) al,
(select thread# thrd, max(sequence#) lhmax
from v$log_history
where first_time=(select max(first_time) from v$log_history)
group by thread#) lh
where al.thrd = lh.thrd;

==================================================================================================================

Method :3
=========
PRIMARY
========
SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied",

(ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG

WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,

(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME)

FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

    Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------

Switch log minimun 3 times
--------------------------
alter system switch logfile;       or
alter system switch all logfile;   or
alter system archive log current;

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied",

(ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG

WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,

(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME)

FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
                                                                   0 OR min
==================================================================================================================

Method :4
=========
PRIMARY
========
Switch log minimun 3 times
--------------------------
alter system switch logfile;       or
alter system switch all logfile;   or
alter system archive log current;
select thread#,max(sequence#) from v$archived_log where archived='YES' group by thread# order by 1;

STANDBY
========
select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread# order by 1;

==================================================================================================================

Method :5
=========

STANDBY
========
SELECT PROCESS, CLIENT_PROCESS, SEQUENCE#, STATUS,THREAD#, DELAY_MINS FROM GV$MANAGED_STANDBY;

PROCESS   CLIENT_P  SEQUENCE# STATUS
--------- -------- ---------- ------
MRP0      N/A            1000 APPLIED_LOG

select THREAD#,SEQUENCE#,STATUS,ARCHIVED from v$log;  ------ the status of redo log files
LGWR RFS 1000 CURRENT

Both the above current values must be same.

Display the Data Guard related messages in the alert log file.

ALTER SESSION SET NLS_DATE_FORMAT='DD-Mon-RR HH12:MI AM';

ALTER SESSION SET NLS_DATE_LANGUAGE=AMERICAN;

SELECT FACILITY, ERROR_CODE, TIMESTAMP, MESSAGE

FROM V$DATAGUARD_STATUS

WHERE TRUNC(TIMESTAMP)= TRUNC(SYSTIMESTAMP)

ORDER BY TIMESTAMP;

Check if there is any redo log gap. This should be run from the standby database.

SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE#

FROM V$ARCHIVE_GAP;

The following script is another way to check if there is any redo log gap, from the primary database:

SELECT MAX(SEQUENCE#), THREAD#

FROM V$ARCHIVED_LOG

WHERE RESETLOGS_CHANGE# = (SELECT MAX(RESETLOGS_CHANGE#) FROM V$ARCHIVED_LOG)

GROUP BY THREAD#;

SELECT DESTINATION, STATUS, ARCHIVED_THREAD#, ARCHIVED_SEQ#

FROM V$ARCHIVE_DEST_STATUS WHERE STATUS <> 'DEFERRED' AND STATUS <> 'INACTIVE'

In the standby database, examine the transport and apply lag statistics. Run the following script:

ALTER SESSION SET NLS_DATE_FORMAT='DD-Mon-RR HH12:MI AM';

ALTER SESSION SET NLS_DATE_LANGUAGE=AMERICAN;

SELECT NAME, VALUE, UNIT, TIME_COMPUTED

FROM V$DATAGUARD_STATS

WHERE NAME IN ('transport lag','apply lag','apply finish time');

In the standby database, list the processes that are running as part of the Data Guard configuration

SELECT PID, PROCESS, STATUS, CLIENT_PROCESS, CLIENT_PID, THREAD#, SEQUENCE# SEQ#, BLOCK#, BLOCKS

FROM V$MANAGED_STANDBY

ORDER BY PROCESS;

RMAN

On the RMAN tool, delete archivelog older than a week.
# rman target /
;
RMAN> delete archivelog until time 'sysdate-7';

or
RMAN> delete force archivelog all;

or
RMAN> delete backup completed before 'sysdate-3';

Restore status

restore status

set pages 9999 lines 500

set numformat 99999.99

set trim on 

set trims on

alter session set nls_date_format = 'DD-MM-YYYY HH24:MI:SS';

select SID, START_TIME,TOTALWORK, sofar, (sofar/totalwork) * 100 done,
   sysdate + TIME_REMAINING/3600/24 end_at
    from v$session_longops
    where totalwork > sofar
    AND opname NOT LIKE '%aggregate%'

   AND opname like 'RMAN%'

RMAN backup job details for 'n' number of days:-

=========================================

Monitoring RMAN backup status using v$rman_backup_job_details and v$rman_status.

Note : - Enter the number of days required for status report, for 1 day backup status report provide input as '1'.

RMAN backup status using v$rman_backup_job_details :-
set pages 9999 lines 500
col INSTANCE for a9
col ELAPSED for a30
SELECT (  SELECT   instance_name FROM v$instance)
              || ' '
              || (  SELECT   instance_number FROM v$instance)
                 instance,
            --  TO_CHAR (start_time, 'YYYY-MM-DD HH24:MI') start_time,
       to_date (start_time, 'DD-MM-YYYY HH24:MI:SS') start_time,
              TO_CHAR (output_bytes / 1048576, '999,999,999.9') output_mb,
              TO_CHAR (output_bytes_per_sec / 1048576, '999,999.9') output_mb_per_sec,
              time_taken_display elapsed,input_type,status
         FROM v$rman_backup_job_details
         where start_time >= sysdate - &NUMBER_OF_DAYS
         ORDER BY start_time
         /


RMAN backup status using v$rman_backup_job_details , v$rman_status:-

set pages 9999 lines 500

set numformat 99999.99

set trim on 

set trims on

alter session set nls_date_format = 'DD-MM-YYYY HH24:MI:SS';

col INSTANCE for a9

col status for a22

col COMMAND_ID for a20

col INPUT_TYPE for a10

col OUTPUT_DEVICE_TYPE for a10

col OUTPUT_BYTES_PER_SEC_DISPLAY for a9

col status heading "BACKUP|STATUS" 

col COMMAND_ID heading "BACKUP NAME" 

col STARTED_TIME heading "START TIME" 

COL END_TIME heading "END TIME" 

col ELAPSED_TIME heading "MINUTES | TAKEN" 

col INPUT_TYPE heading "INPUT|TYPE" 

col OUTPUT_DEVICE_TYPE heading "OUTPUT|DEVICES" 

col INPUT_SIZE heading  "INPUT SIZE|GB"

col OUTPUT_SIZE heading  "OUTPUT SIZE|GB" 

col OUTPUT_BYTES_PER_SEC_DISPLAY heading "OUTPUT | RATE|(PER SEC)"

SELECT (SELECT instance_name FROM v$instance) || ' ' || (SELECT instance_number FROM v$instance) instance,rs.sid,

rj.COMMAND_ID,

rj.STATUS,

max(rj.START_TIME) STARTED_TIME, 

rj.END_TIME,

rj.ELAPSED_SECONDS/60 ELAPSED_TIME,

rj.INPUT_TYPE,

rj.OUTPUT_DEVICE_TYPE,

rj.INPUT_BYTES/1024/1024/1024 INPUT_SIZE, 

rj.OUTPUT_BYTES/1024/1024/1024 OUTPUT_SIZE,

rj.OUTPUT_BYTES_PER_SEC_DISPLAY

from v$rman_backup_job_details rj, v$rman_status rs

where rj.COMMAND_ID=rs.COMMAND_ID

group by rs.sid,rj.COMMAND_ID,rj.STATUS,rj.START_TIME,rj.END_TIME,rj.ELAPSED_SECONDS,rj.INPUT_TYPE,rj.OUTPUT_DEVICE_TYPE,rj.INPUT_BYTES,rj.OUTPUT_BYTES,rj.OUTPUT_BYTES_PER_SEC_DISPLAY 

having max(rj.START_TIME) > sysdate-&NUMBER_OF_DAYS order by rj.START_TIME desc

/

                                  BACKUP                                                          MINUTES  INPUT      OUTPUT     INPUT SIZE OUTPUT SIZE OUTPUT RATE

INSTANCE  SID BACKUP NAME          STATUS                 START TIME          END TIME                TAKEN TYPE       DEVICES            GB          GB (PER SEC)

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

Check RMAN Backup Status

SQL to report on all backups i.e., Full, Incremental and Archive log backups:

col STATUS format a9

col hrs format 999.99

select SESSION_KEY, INPUT_TYPE, STATUS,

to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,

to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,

elapsed_seconds/3600 hrs

from V$RMAN_BACKUP_JOB_DETAILS

order by session_key;

SQL to report on all Full and Incremental backup but not Archive log backups:

col STATUS format a9

col hrs format 999.99

select SESSION_KEY, INPUT_TYPE, STATUS,

to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,

to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,

elapsed_seconds/3600 hrs

from V$RMAN_BACKUP_JOB_DETAILS

where input_type='DB INCR'

order by session_key;

To check progress of RMAN Backup:

select SID, START_TIME,TOTALWORK, sofar, (sofar/totalwork) * 100 done,

sysdate + TIME_REMAINING/3600/24 end_at

from v$session_longops

where totalwork > sofar

AND opname NOT LIKE '%aggregate%'

AND opname like 'RMAN%';

Backup report no catalog database

set lines 130 pages 1000
set feedback off
col INPUT_TYPE for a11
col START_TIME for a20
col END_TIME for a20
col STATUS format a24
col DB_NAME for a10
col hrs format 999.99
select  (select name "DB_NAME" from v$database) DB_NAME,
        SESSION_KEY,
        INPUT_TYPE,
        STATUS,
        to_char(START_TIME,'DD/MON/YYYY hh24:mi') START_TIME,
        to_char(END_TIME,'DD/MON/YYYY hh24:mi')   END_TIME,
        elapsed_seconds/3600                      HRS
from (  select
        START_TIME,
        END_TIME,
        ELAPSED_SECONDS,
        STATUS,
        INPUT_TYPE,
        SESSION_KEY,
        rank()
                over (PARTITION BY INPUT_TYPE ORDER BY SESSION_KEY desc)
                      rankno
        from  V$RMAN_BACKUP_JOB_DETAILS
where input_type in ('DB INCR','DB FULL')
--            where input_type in ('DB INCR','DB FULL','ARCHIVELOG')
     order by SESSION_KEY desc)
WHERE   rankno < 2;



[oracle@raj ~]$ cat /u01/backup_SCRIPT.sh
export ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/dbhome_2
export $ORACLE_SID=DEMO
$ORACLE_HOME/bin/rman target / nocatalog  << EOF
run
{
allocate channel mwares_backup_disk1 type disk format '/u01/backup_06092017/';
backup AS COMPRESSED BACKUPSET tag '%TAG' incremental level 0 cumulative database;
backup as COMPRESSED BACKUPSET tag '%TAG' archivelog all not backed up;
backup current controlfile;
release channel backup_disk1;
}
[oracle@raj ~]$ cat  /mnt/nfs/tmp/RESTORE_SCRIPT.sh
ORACLE_SID=demo
ORAENV_ASK=NO
. oraenv
RESTORE_LOG=/u01/logs/$ORACLE_SID.log

rman AUXILIARY / <<! | tee -a $RESTORE_LOG

run {
ALLOCATE AUXILIARY CHANNEL AUX1 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL AUX2 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL AUX3 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL AUX4 DEVICE TYPE DISK;
DUPLICATE DATABASE TO demo
  BACKUP LOCATION '/mnt/BKP_05092017'
  NOFILENAMECHECK;
}
exit;

!

col cf for 9,999
col df for 9,999
col elapsed_seconds heading "ELAPSED|SECONDS"
col i0 for 9,999
col i1 for 9,999
col l for 9,999
col output_mbytes for 9,999,999 heading "OUTPUT|MBYTES"
col session_recid for 999999 heading "SESSION|RECID"
col session_stamp for 99999999999 heading "SESSION|STAMP"
col status for a10 trunc
col time_taken_display for a10 heading "TIME|TAKEN"
col db_name for 9999 heading "DB"
select
  j.db_name DB,
  to_char(j.start_time, 'yyyy-mm-dd hh24:mi:ss') start_time,
  decode(to_char(j.start_time, 'd'), 1, 'Sunday', 2, 'Monday',
                                     3, 'Tuesday', 4, 'Wednesday',
                                     5, 'Thursday', 6, 'Friday',
                                     7, 'Saturday') dow,
  to_char(j.end_time, 'yyyy-mm-dd hh24:mi:ss') end_time,
  (j.output_bytes/1024/1024) output_mbytes, j.status, j.input_type,
  j.elapsed_seconds, j.time_taken_display
from RC_RMAN_BACKUP_JOB_DETAILS j
  left outer join (select
                     d.session_recid, d.session_stamp,
                 --decode(d.backup_type,'D','Level 0','I','Level 1','L','Archive Log','Other')Backyp_Type,
                    sum(case when d.controlfile_included = 'YES' then d.pieces else 0 end) CF,
                     sum(case when d.controlfile_included = 'NO' and
                     d.backup_type = 'D' then d.pieces else 0 end) FB,
                     sum(case when d.backup_type = 'D' then d.incremental_level else 0 end)I0,
                     sum(case when d.backup_type = 'I' then d.incremental_level else 0 end)I1,
                     sum(case when d.backup_type = 'L' then d.pieces else 0 end) L
                   from
                     RC_BACKUP_SET_DETAILS d
                     join RC_BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count
                   where s.input_file_scan_only = 'NO' 
                    group by d.session_recid, d.session_stamp ) x
    on x.session_recid = j.session_recid and x.session_stamp = j.session_stamp
  left outer join (select o.recid, o.stamp
                   from RC_RMAN_OUTPUT o
                  group by o.recid, o.stamp )
    ro on ro.recid = j.SESSION_RECID  and ro.stamp = j.session_stamp
where j.start_time > trunc(sysdate)-1
order by j.db_name, j.start_time;