Standalone Standby Database Creation for 2 node RAC in Oracle 11g

Environment.

Primary 2 Node Database RAC
1. Instance rac1 host rac1
2. Instance rac2 host rac2

Standby Database STBY
Instance stby1 host stby


At Standby, I have 3 disks avaiable, so I created the new Disk group FRA as identical to Primary database.

select a.name,a.state,b.name,b.label,b.path
from v$asm_disk b, v$asm_diskgroup a
where a.GROUP_NUMBER=b.GROUP_NUMBER;


NAME       STATE       NAME       LABEL      PATH
---------- ----------- ---------- ---------- ---------------
DATA       MOUNTED     DISK1      DISK1      ORCL:DISK1
DATA       MOUNTED     DISK2      DISK2      ORCL:DISK2
DATA       MOUNTED     DISK3      DISK3      ORCL:DISK3



SQL> select label from v$asm_disk;

LABEL
----------
DISK4
DISK5
DISK6
DISK1
DISK2
DISK3



SELECT group_number, name, compatibility, database_compatibility, ALLOCATION_UNIT_SIZE/1024/1024 "AU_Size in MB" FROM v$asm_diskgroup;

GROUP_NUMBER NAME       COMPATIBILITY        DATABASE_COMPATIBILI AU_Size in MB
------------ ---------- -------------------- -------------------- -------------
           1 DATA       11.2.0.0.0           10.1.0.0.0                       1


create diskgroup FRA EXTERNAL REDUNDANCY
DISK
'ORCL:DISK4',
'ORCL:DISK5',
'ORCL:DISK6'
 ATTRIBUTE
'au_size'='1M',
'compatible.asm'='11.2.0.0.0',
'compatible.rdbms'='10.1.0.0.0'
/

Diskgroup created.


select a.name,a.state,b.name,b.label,b.path
from v$asm_disk b, v$asm_diskgroup a
where a.GROUP_NUMBER=b.GROUP_NUMBER;

NAME       STATE       NAME       LABEL                           PATH
---------- ----------- ---------- ------------------------------- --------------------
DATA       MOUNTED     DISK1      DISK1                           ORCL:DISK1
DATA       MOUNTED     DISK2      DISK2                           ORCL:DISK2
DATA       MOUNTED     DISK3      DISK3                           ORCL:DISK3
FRA        MOUNTED     DISK4      DISK4                           ORCL:DISK4
FRA        MOUNTED     DISK5      DISK5                           ORCL:DISK5
FRA        MOUNTED     DISK6      DISK6                           ORCL:DISK6

6 rows selected.



At Standby Database server create a temporary listener, listening to standby instance stby1.

[root@stby bin]# ./srvctl add listener -l listener_standby -p "TCP:1522" -o /u01/app/oracle/product/11.2.0/dbhome_1/

Make Static entry for Standby database.

LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))))            # line added by Agent
LISTENER_STANDBY=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_STANDBY))))            # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_STANDBY=ON              # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent

LISTENER_STANDBY =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = stby.xxx.com)(PORT = 1521))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
      )
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = stby1)
      (GLOBAL_DBNAME = stby)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
    )
  )



[root@stby bin]# ./srvctl start listener -l listener_standby

[oracle@stby dbs]lsnrctl status listener

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 03-OCT-2014 16:13:09

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                03-OCT-2014 14:03:18
Uptime                    0 days 2 hr. 9 min. 50 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/stby/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.27.31.46)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.27.31.3)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.27.31.46)(PORT=1522)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.27.31.3)(PORT=1522)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "stby" has 1 instance(s).
  Instance "stby1", status UNKNOWN, has 1 handler(s) for this service...
Service "stby.xxx.com" has 1 instance(s).
  Instance "stby1", status READY, has 1 handler(s) for this service...
The command completed successfully



TNSNAMES.ORA file entry at both server i.e. Primary and standby.

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

########################################################################################################

                      #       Primary Server Entry      #

########################################################################################################

RAC =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = rac.xxx.com)
    )
  )

RAC1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = rac.xxx.com)
      (INSTANCE_NAME = rac1)
    )
  )

RAC2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = rac.xxx.com)
      (INSTANCE_NAME = rac2)
    )
  )



########################################################################################################


                        #       Standby Server Entry      #

########################################################################################################

STBY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = stby-vip)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = stby.xxx.com)
      (UR = A )
      (INSTANCE_NAME= stby1)
    )
  )





-bash-3.2$ ./crsctl stat res -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
               ONLINE  ONLINE       stby
ora.FRA.dg
               ONLINE  ONLINE       stby
ora.LISTENER.lsnr
               ONLINE  ONLINE       stby
ora.LISTENER_STANDBY.lsnr
               ONLINE  ONLINE       stby
ora.asm
               ONLINE  ONLINE       stby                     Started
ora.gsd
               OFFLINE OFFLINE      stby
ora.net1.network
               ONLINE  ONLINE       stby
ora.ons
               ONLINE  ONLINE       stby
ora.registry.acfs
               ONLINE  ONLINE       stby
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       stby
ora.cvu
      1        ONLINE  ONLINE       stby
ora.oc4j
      1        ONLINE  ONLINE       stby
ora.scan1.vip
      1        ONLINE  ONLINE       stby
ora.stby.vip
      1        ONLINE  ONLINE       stby



-- > Copy the password file at standby location and rename with the Standby database name.

--> Modify the parameter file as below.

[oracle@stby dbs]vim initstby1.ora
stby1.__db_cache_size=117440512
stby1.__java_pool_size=4194304
stby1.__large_pool_size=4194304
stby1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
stby1.__pga_aggregate_target=209715200
stby1.__sga_target=314572800
stby1.__shared_io_pool_size=0
stby1.__shared_pool_size=180355072
stby1.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/stby/adump'
*.audit_trail='db'
*.cluster_database=false
*.compatible='11.2.0.0.0'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain='xxx.com'
*.db_name='rac'
*.db_unique_name='stby'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=stbyXDB)'
*.fal_client='STBY'
*.fal_server='RAC'
stby1.instance_number=1
*.log_archive_config='DG_CONFIG=(rac,stby)'
*.log_archive_dest_1='LOCATION=+DATA VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=stby'
*.log_archive_dest_2='SERVICE=rac LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) COMPRESSION=ENABLE DB_UNIQUE_NAME=rac'
*.log_archive_dest_state_1='ENABLE'
#*.log_archive_dest_state_2='DEFER'
*.memory_target=524288000
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='exclusive'
*.resumable_timeout=0
stby1.thread=1
stby1.undo_tablespace='UNDOTBS1'
~


--> Startup standby database stby1 in nomount mode using parameter file.

SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initstby1.ora';
ORACLE instance started.

Total System Global Area  523108352 bytes
Fixed Size                  1346052 bytes
Variable Size             398460412 bytes
Database Buffers          117440512 bytes

Redo Buffers                5861376 bytes

--> Connecting Standby Database from Primary Database. 

[oracle@rac1 ~]rman target / auxiliary sys/Oracle12@stby

Recovery Manager: Release 11.2.0.3.0 - Production on Fri Oct 3 15:29:31 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: RAC (DBID=2444882112)
connected to auxiliary database: RAC (not mounted)

RMAN> run
2>
3> {

4> 5> duplicate target database for standby from active database

spfile
6> 7> 8>
parameter_value_convert 'rac','stby'
9> 10>
11> set cluster_database='FALSE'

12> 13> set db_file_name_convert='+DATA/rac','+DATA/stby','/u01/','+DATA/stby'

14> 15> set log_file_name_convert='+DATA/rac','+DATA/stby'
16>
set control_files='+DATA/'
17> 18>
set db_unique_name='stby'

set fal_client='stby'

19> 20> 21> 22> 23> set fal_server='rac'

24> 25> set standby_file_management='AUTO'
26>
27> set audit_file_dest='/u01/app/oracle'

28> 29> set diagnostic_dest='/u01/app/oracle'
30>
;
31> 32>
}33>

Starting Duplicate Db at 03-OCT-14
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=140 device type=DISK

contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/u01/app/oracle/product/11.2.0/db_1/dbs/orapwrac1' auxiliary format
 '/u01/app/oracle/product/11.2.0/dbhome_1//dbs/orapwstby1'   targetfile
 '/u01/app/oracle/product/11.2.0/db_1/dbs/spfilerac1.ora' auxiliary format
 '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfilestby1.ora'   ;
   sql clone "alter system set spfile= ''/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfilestby1.ora''";
}
executing Memory Script

Starting backup at 03-OCT-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=161 instance=rac1 device type=DISK
Finished backup at 03-OCT-14

sql statement: alter system set spfile= ''/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfilestby1.ora''

contents of Memory Script:
{
   sql clone "alter system set  dispatchers =
 ''(PROTOCOL=TCP) (SERVICE=stbyXDB)'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_archive_dest_1 =
 ''LOCATION=+DATA VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=stby'' comment=
 '''' scope=spfile";
   sql clone "alter system set  remote_listener =
 ''stby-scan:1521'' comment=
 '''' scope=spfile";
   sql clone "alter system set  cluster_database =
 FALSE comment=
 '''' scope=spfile";
   sql clone "alter system set  db_file_name_convert =
 ''+DATA/rac'', ''+DATA/stby'', ''/u01/'', ''+DATA/stby'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_file_name_convert =
 ''+DATA/rac'', ''+DATA/stby'' comment=
 '''' scope=spfile";
   sql clone "alter system set  control_files =
 ''+DATA/'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''stby'' comment=
 '''' scope=spfile";
   sql clone "alter system set  fal_client =
 ''stby'' comment=
 '''' scope=spfile";
   sql clone "alter system set  fal_server =
 ''rac'' comment=
 '''' scope=spfile";
   sql clone "alter system set  standby_file_management =
 ''AUTO'' comment=
 '''' scope=spfile";
   sql clone "alter system set  audit_file_dest =
 ''/u01/app/oracle'' comment=
 '''' scope=spfile";
   sql clone "alter system set  diagnostic_dest =
 ''/u01/app/oracle'' comment=
 '''' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  dispatchers =  ''(PROTOCOL=TCP) (SERVICE=stbyXDB)'' comment= '''' scope=spfile

sql statement: alter system set  log_archive_dest_1 =  ''LOCATION=+DATA VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=stby'' comment= '''' scope=spfile

sql statement: alter system set  remote_listener =  ''stby-scan:1521'' comment= '''' scope=spfile

sql statement: alter system set  cluster_database =  FALSE comment= '''' scope=spfile

sql statement: alter system set  db_file_name_convert =  ''+DATA/rac'', ''+DATA/stby'', ''/u01/'', ''+DATA/stby'' comment= '''' scope=spfile

sql statement: alter system set  log_file_name_convert =  ''+DATA/rac'', ''+DATA/stby'' comment= '''' scope=spfile

sql statement: alter system set  control_files =  ''+DATA/'' comment= '''' scope=spfile

sql statement: alter system set  db_unique_name =  ''stby'' comment= '''' scope=spfile

sql statement: alter system set  fal_client =  ''stby'' comment= '''' scope=spfile

sql statement: alter system set  fal_server =  ''rac'' comment= '''' scope=spfile

sql statement: alter system set  standby_file_management =  ''AUTO'' comment= '''' scope=spfile

sql statement: alter system set  audit_file_dest =  ''/u01/app/oracle'' comment= '''' scope=spfile

sql statement: alter system set  diagnostic_dest =  ''/u01/app/oracle'' comment= '''' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     523108352 bytes

Fixed Size                     1346052 bytes
Variable Size                314574332 bytes
Database Buffers             201326592 bytes
Redo Buffers                   5861376 bytes

contents of Memory Script:
{
   sql clone "alter system set  control_files =
  ''+DATA/stby/controlfile/current.262.859994701'' comment=
 ''Set by RMAN'' scope=spfile";
   backup as copy current controlfile for standby auxiliary format  '+DATA/stby/controlfile/current.263.859994701';
   sql clone "alter system set  control_files =
  ''+DATA/stby/controlfile/current.263.859994701'' comment=
 ''Set by RMAN'' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  control_files =   ''+DATA/stby/controlfile/current.262.859994701'' comment= ''Set by RMAN'' scope=spfile

Starting backup at 03-OCT-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_rac1.f tag=TAG20141003T153009 RECID=7 STAMP=859995012
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 03-OCT-14

sql statement: alter system set  control_files =   ''+DATA/stby/controlfile/current.263.859994701'' comment= ''Set by RMAN'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     523108352 bytes

Fixed Size                     1346052 bytes
Variable Size                314574332 bytes
Database Buffers             201326592 bytes
Redo Buffers                   5861376 bytes

contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database
RMAN-05529: WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.

contents of Memory Script:
{
   set newname for tempfile  1 to
 "+data";
   switch clone tempfile all;
   set newname for datafile  1 to
 "+data";
   set newname for datafile  2 to
 "+data";
   set newname for datafile  3 to
 "+data";
   set newname for datafile  4 to
 "+data";
   set newname for datafile  5 to
 "+data";
   set newname for datafile  6 to
 "+data";
   set newname for datafile  7 to
 "+DATA/stbydemo01.dbf";
   backup as copy reuse
   datafile  1 auxiliary format
 "+data"   datafile
 2 auxiliary format
 "+data"   datafile
 3 auxiliary format
 "+data"   datafile
 4 auxiliary format
 "+data"   datafile
 5 auxiliary format
 "+data"   datafile
 6 auxiliary format
 "+data"   datafile
 7 auxiliary format
 "+DATA/stbydemo01.dbf"   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to +data in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 03-OCT-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=+DATA/rac/datafile/undotbs1.258.856899977
output file name=+DATA/stby/datafile/undotbs1.264.859994751 tag=TAG20141003T153057
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:05:55
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+DATA/rac/datafile/sysaux.257.856899977
output file name=+DATA/stby/datafile/sysaux.265.859995107 tag=TAG20141003T153057
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:55
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DATA/rac/datafile/system.256.856899975
output file name=+DATA/stby/datafile/system.266.859995223 tag=TAG20141003T153057
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=+DATA/rac/datafile/example.264.856900139
output file name=+DATA/stby/datafile/example.267.859995297 tag=TAG20141003T153057
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=+DATA/rac/datafile/undotbs2.265.856900533
output file name=+DATA/stby/datafile/undotbs2.268.859995333 tag=TAG20141003T153057
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DATA/rac/datafile/users.259.856899977
output file name=+DATA/stby/datafile/users.269.859995369 tag=TAG20141003T153057
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=/u01/demo01.dbf
output file name=+DATA/stbydemo01.dbf tag=TAG20141003T153057
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 03-OCT-14

sql statement: alter system archive log current

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=7 STAMP=859995378 file name=+DATA/stby/datafile/system.266.859995223
datafile 2 switched to datafile copy
input datafile copy RECID=8 STAMP=859995378 file name=+DATA/stby/datafile/sysaux.265.859995107
datafile 3 switched to datafile copy
input datafile copy RECID=9 STAMP=859995379 file name=+DATA/stby/datafile/undotbs1.264.859994751
datafile 4 switched to datafile copy
input datafile copy RECID=10 STAMP=859995379 file name=+DATA/stby/datafile/users.269.859995369
datafile 5 switched to datafile copy
input datafile copy RECID=11 STAMP=859995379 file name=+DATA/stby/datafile/example.267.859995297
datafile 6 switched to datafile copy
input datafile copy RECID=12 STAMP=859995380 file name=+DATA/stby/datafile/undotbs2.268.859995333
datafile 7 switched to datafile copy
input datafile copy RECID=13 STAMP=859995380 file name=+DATA/stbydemo01.dbf
Finished Duplicate Db at 03-OCT-14

RMAN>





SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
RAC       READ ONLY            PHYSICAL STANDBY

SQL> select process,status from v$managed_standby;

PROCESS   STATUS
--------- ------------
ARCH      CONNECTED
ARCH      CONNECTED
ARCH      CONNECTED
ARCH      CLOSING
RFS       IDLE
RFS       IDLE
RFS       IDLE
RFS       IDLE


SQL> recover managed standby database using current logfile disconnect;
Media recovery complete.

SQL> select process,status from v$managed_standby;

PROCESS   STATUS
--------- ------------
ARCH      CONNECTED
ARCH      CONNECTED
ARCH      CONNECTED
ARCH      CLOSING
RFS       IDLE
RFS       IDLE
RFS       IDLE
RFS       IDLE
MRP0      WAIT_FOR_LOG

9 rows selected.

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
RAC       READ ONLY WITH APPLY PHYSICAL STANDBY

Script to Monitor the RMAN JOBs in Detail

Script to monitor the RMAN running jobs in detail.


$ cat > rman.sql


*********************************************************************
alter session set nls_date_format='dd-mon-rr hh24:mi:ss';

set lines 1500
set pages 100
col CLI_INFO format a10
col spid format a5
col ch format a20
col seconds format 999999.99
col filename format a65
col bfc  format 9
col "% Complete" format 999.99
col event format a40
set numwidth 10
select sysdate from dual;
REM gv$session_longops (channel level)
prompt
prompt Channel progress - gv$session_longops:
prompt
select s.inst_id, o.sid, CLIENT_INFO ch, context, sofar, totalwork,
                    round(sofar/totalwork*100,2) "% Complete"
     FROM gv$session_longops o, gv$session s
     WHERE opname LIKE 'RMAN%'
     AND opname NOT LIKE '%aggregate%'
     AND o.sid=s.sid
     AND totalwork != 0
     AND sofar <> totalwork;
REM Check wait events (RMAN sessions) - this is for CURRENT waits only
REM use the following for 11G+
prompt
prompt Session progess - CURRENT wait events and time in wait so far:
prompt
select inst_id, sid, CLIENT_INFO ch, seq#, event, state, wait_time_micro/1000000 seconds
from gv$session where program like '%rman%' and
wait_time = 0 and
not action is null;
REM gv$backup_async_io
prompt
prompt Disk (file and backuppiece) progress - includes tape backuppiece
prompt if backup_tape_io_slaves=TRUE:
prompt
select s.inst_id, a.sid, CLIENT_INFO Ch, a.STATUS,
open_time, round(BYTES/1024/1024,2) "SOFAR Mb" , round(total_bytes/1024/1024,2)
TotMb, io_count,
round(BYTES/TOTAL_BYTES*100,2) "% Complete" , a.type, filename
from gv$backup_async_io a,  gv$session s
where not a.STATUS in ('UNKNOWN')
and a.sid=s.sid and open_time > to_date('&1', 'dd-mon-rr hh24:mi:ss') order by 2,7;
REM gv$backup_sync_io
prompt
prompt Tape backuppiece progress (only if backup_tape_io_slaves=FALSE):
prompt
col FILENAME for a50
set line 400 pagesize 100
select s.inst_id, a.sid, CLIENT_INFO Ch, filename, a.type, a.status, buffer_size bsz, buffer_count bfc,
open_time open, io_count
from gv$backup_sync_io a, gv$session s
where
a.sid=s.sid and
open_time > to_date('&1', 'dd-mon-rr hh24:mi:ss') ;

*******************************************************************************


O/p of the script.


SQL> select to_char(sysdate,'dd-mon-yy hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'D

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

14-jul-14 15:19:57

SQL> @rman.sql '14-jul-14 15:19:57'

Session altered.

　

SYSDATE

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

14-jul-14 15:21:58

　

Channel progress - gv$session_longops:

　

INST_ID SID CH CONTEXT SOFAR TOTALWORK % Complete

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

1 54 rman channel=ORA_DIS 1 142524 236800 60.19

K_1

　

　

Session progess - CURRENT wait events and time in wait so far:

　

INST_ID SID CH SEQ# EVENT STATE SECONDS

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

1 42 1407 SQL*Net message from client WAITING 34.21

1 54 rman channel=ORA_DIS 2654 RMAN backup & recovery I/O WAITING .02

K_1

　

　

Disk (file and backuppiece) progress - includes tape backuppiece

if backup_tape_io_slaves=TRUE:

old 7: and a.sid=s.sid and open_time > to_date('&1', 'dd-mon-rr hh24:mi:ss') order by 2,7

new 7: and a.sid=s.sid and open_time > to_date('14-jul-14 15:19:57', 'dd-mon-rr hh24:mi:ss') order by 2,7

INST_ID SID CH STATUS OPEN_TIME SOFAR Mb TOTMB IO_COUNT % Complete TYPE FILENAME

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

1 54 rman channel=ORA_DIS FINISHED 14-jul-14 15:21:25 1.5 5 5 30.00 INPUT /u01/app/oracle/oradata/db1/users01.dbf

K_1

1 54 rman channel=ORA_DIS FINISHED 14-jul-14 15:21:25 55 55 112 100.00 INPUT /u01/app/oracle/oradata/db1/undotbs01.dbf

K_1

1 54 rman channel=ORA_DIS FINISHED 14-jul-14 15:21:25 10 500 22 2.00 INPUT /u01/app/oracle/oradata/db1/test01.dbf

K_1

1 54 rman channel=ORA_DIS IN PROGRESS 14-jul-14 15:21:25 276.99 580 554 47.76 INPUT /u01/app/oracle/oradata/db1/sysaux01.dbf

K_1

1 54 rman channel=ORA_DIS IN PROGRESS 14-jul-14 15:21:25 276.99 710 554 39.01 INPUT /u01/app/oracle/oradata/db1/system01.dbf

K_1

1 54 rman channel=ORA_DIS IN PROGRESS 14-jul-14 15:21:24 462.99 464 OUTPUT/u01/app/oracle/fast_recovery_area/DB1/backupset/2014_07_14/o1_mf

K_1 _nnndf_TAG20140714T152124_9w7b0wwj_.bkp

　

6 rows selected.

　

Tape backuppiece progress (only if backup_tape_io_slaves=FALSE):

old 6: open_time > to_date('&1', 'dd-mon-rr hh24:mi:ss')

new 6: open_time > to_date('14-jul-14 15:19:57', 'dd-mon-rr hh24:mi:ss')

no rows selected

SQL> /

old 6: open_time > to_date('&1', 'dd-mon-rr hh24:mi:ss')

new 6: open_time > to_date('14-jul-14 15:19:57', 'dd-mon-rr hh24:mi:ss')

no rows selected

SQL> @rman.sql '14-jul-14 15:19:57'

Session altered.

　

SYSDATE

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

14-jul-14 15:22:42

　

Channel progress - gv$session_longops:

　

INST_ID SID CH CONTEXT SOFAR TOTALWORK % Complete

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

1 54 rman channel=ORA_DIS 1 236158 236800 99.73

K_1

　

　

Session progess - CURRENT wait events and time in wait so far:

　

INST_ID SID CH SEQ# EVENT STATE SECONDS

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

1 42 1407 SQL*Net message from client WAITING 77.77

　

Disk (file and backuppiece) progress - includes tape backuppiece

if backup_tape_io_slaves=TRUE:

old 7: and a.sid=s.sid and open_time > to_date('&1', 'dd-mon-rr hh24:mi:ss') order by 2,7

new 7: and a.sid=s.sid and open_time > to_date('14-jul-14 15:19:57', 'dd-mon-rr hh24:mi:ss') order by 2,7

INST_ID SID CH STATUS OPEN_TIME SOFAR Mb TOTMB IO_COUNT % Complete TYPE FILENAME

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

1 54 rman channel=ORA_DIS FINISHED 14-jul-14 15:21:25 1.5 5 5 30.00 INPUT /u01/app/oracle/oradata/db1/users01.dbf

K_1

1 54 rman channel=ORA_DIS FINISHED 14-jul-14 15:21:25 55 55 112 100.00 INPUT /u01/app/oracle/oradata/db1/undotbs01.dbf

K_1

1 54 rman channel=ORA_DIS FINISHED 14-jul-14 15:21:25 10 500 22 2.00 INPUT /u01/app/oracle/oradata/db1/test01.dbf

K_1

1 54 rman channel=ORA_DIS FINISHED 14-jul-14 15:21:25 536 580 1074 92.41 INPUT /u01/app/oracle/oradata/db1/sysaux01.dbf

K_1

1 54 rman channel=ORA_DIS FINISHED 14-jul-14 15:21:25 704.5 710 1411 99.23 INPUT /u01/app/oracle/oradata/db1/system01.dbf

K_1

1 54 rman channel=ORA_DIS FINISHED 14-jul-14 15:21:24 1077.8 1078 OUTPUT/u01/app/oracle/fast_recovery_area/DB1/backupset/2014_07_14/o1_mf

K_1 _nnndf_TAG20140714T152124_9w7b0wwj_.bkp

　

6 rows selected.

　

Tape backuppiece progress (only if backup_tape_io_slaves=FALSE):

old 6: open_time > to_date('&1', 'dd-mon-rr hh24:mi:ss')

new 6: open_time > to_date('14-jul-14 15:19:57', 'dd-mon-rr hh24:mi:ss')

no rows selected




References. OTN DOC


SQL> select to_char(sysdate,'dd-mon-yy hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'D

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

14-jul-14 15:19:57

SQL> @rman.sql '14-jul-14 15:19:57'

Session altered.

　

SYSDATE

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

14-jul-14 15:21:58

　

Channel progress - gv$session_longops:

　

INST_ID SID CH CONTEXT SOFAR TOTALWORK % Complete

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

1 54 rman channel=ORA_DIS 1 142524 236800 60.19

K_1

　

　

Session progess - CURRENT wait events and time in wait so far:

　

INST_ID SID CH SEQ# EVENT STATE SECONDS

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

1 42 1407 SQL*Net message from client WAITING 34.21

1 54 rman channel=ORA_DIS 2654 RMAN backup & recovery I/O WAITING .02

K_1

　

　

Disk (file and backuppiece) progress - includes tape backuppiece

if backup_tape_io_slaves=TRUE:

old 7: and a.sid=s.sid and open_time > to_date('&1', 'dd-mon-rr hh24:mi:ss') order by 2,7

new 7: and a.sid=s.sid and open_time > to_date('14-jul-14 15:19:57', 'dd-mon-rr hh24:mi:ss') order by 2,7

INST_ID SID CH STATUS OPEN_TIME SOFAR Mb TOTMB IO_COUNT % Complete TYPE FILENAME

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

1 54 rman channel=ORA_DIS FINISHED 14-jul-14 15:21:25 1.5 5 5 30.00 INPUT /u01/app/oracle/oradata/db1/users01.dbf

K_1

1 54 rman channel=ORA_DIS FINISHED 14-jul-14 15:21:25 55 55 112 100.00 INPUT /u01/app/oracle/oradata/db1/undotbs01.dbf

K_1

1 54 rman channel=ORA_DIS FINISHED 14-jul-14 15:21:25 10 500 22 2.00 INPUT /u01/app/oracle/oradata/db1/test01.dbf

K_1

1 54 rman channel=ORA_DIS IN PROGRESS 14-jul-14 15:21:25 276.99 580 554 47.76 INPUT /u01/app/oracle/oradata/db1/sysaux01.dbf

K_1

1 54 rman channel=ORA_DIS IN PROGRESS 14-jul-14 15:21:25 276.99 710 554 39.01 INPUT /u01/app/oracle/oradata/db1/system01.dbf

K_1

1 54 rman channel=ORA_DIS IN PROGRESS 14-jul-14 15:21:24 462.99 464 OUTPUT/u01/app/oracle/fast_recovery_area/DB1/backupset/2014_07_14/o1_mf

K_1 _nnndf_TAG20140714T152124_9w7b0wwj_.bkp

　

6 rows selected.

　

Tape backuppiece progress (only if backup_tape_io_slaves=FALSE):

old 6: open_time > to_date('&1', 'dd-mon-rr hh24:mi:ss')

new 6: open_time > to_date('14-jul-14 15:19:57', 'dd-mon-rr hh24:mi:ss')

no rows selected

SQL> /

old 6: open_time > to_date('&1', 'dd-mon-rr hh24:mi:ss')

new 6: open_time > to_date('14-jul-14 15:19:57', 'dd-mon-rr hh24:mi:ss')

no rows selected

SQL> @rman.sql '14-jul-14 15:19:57'

Session altered.

　

SYSDATE

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

14-jul-14 15:22:42

　

Channel progress - gv$session_longops:

　

INST_ID SID CH CONTEXT SOFAR TOTALWORK % Complete

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

1 54 rman channel=ORA_DIS 1 236158 236800 99.73

K_1

　

　

Session progess - CURRENT wait events and time in wait so far:

　

INST_ID SID CH SEQ# EVENT STATE SECONDS

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

1 42 1407 SQL*Net message from client WAITING 77.77

　

Disk (file and backuppiece) progress - includes tape backuppiece

if backup_tape_io_slaves=TRUE:

old 7: and a.sid=s.sid and open_time > to_date('&1', 'dd-mon-rr hh24:mi:ss') order by 2,7

new 7: and a.sid=s.sid and open_time > to_date('14-jul-14 15:19:57', 'dd-mon-rr hh24:mi:ss') order by 2,7

INST_ID SID CH STATUS OPEN_TIME SOFAR Mb TOTMB IO_COUNT % Complete TYPE FILENAME

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

1 54 rman channel=ORA_DIS FINISHED 14-jul-14 15:21:25 1.5 5 5 30.00 INPUT /u01/app/oracle/oradata/db1/users01.dbf

K_1

1 54 rman channel=ORA_DIS FINISHED 14-jul-14 15:21:25 55 55 112 100.00 INPUT /u01/app/oracle/oradata/db1/undotbs01.dbf

K_1

1 54 rman channel=ORA_DIS FINISHED 14-jul-14 15:21:25 10 500 22 2.00 INPUT /u01/app/oracle/oradata/db1/test01.dbf

K_1

1 54 rman channel=ORA_DIS FINISHED 14-jul-14 15:21:25 536 580 1074 92.41 INPUT /u01/app/oracle/oradata/db1/sysaux01.dbf

K_1

1 54 rman channel=ORA_DIS FINISHED 14-jul-14 15:21:25 704.5 710 1411 99.23 INPUT /u01/app/oracle/oradata/db1/system01.dbf

K_1

1 54 rman channel=ORA_DIS FINISHED 14-jul-14 15:21:24 1077.8 1078 OUTPUT/u01/app/oracle/fast_recovery_area/DB1/backupset/2014_07_14/o1_mf

K_1 _nnndf_TAG20140714T152124_9w7b0wwj_.bkp

　

6 rows selected.

　

Tape backuppiece progress (only if backup_tape_io_slaves=FALSE):

old 6: open_time > to_date('&1', 'dd-mon-rr hh24:mi:ss')

new 6: open_time > to_date('14-jul-14 15:19:57', 'dd-mon-rr hh24:mi:ss')

no rows selected

References. OTN DOC ID.1487262.1