Oracle RMAN Full and Archive backup.

For RMAN Archive Backup

SET PAGES 999 LINES 999
COL STATUS FORMAT A9
COL "HRS" FORMAT 999.99
COL "MIN" FORMAT 99999.99
COL HOST_NAME FOR A10;
SELECT NAME,INSTANCE_NAME,HOST_NAME,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",
ELAPSED_SECONDS/60 "MIN"
FROM V$RMAN_BACKUP_JOB_DETAILS,
(SELECT NAME FROM V$DATABASE),
(SELECT INSTANCE_NAME,SUBSTR(HOST_NAME,1,10) "HOST_NAME" FROM GV$INSTANCE)
WHERE INPUT_TYPE='ARCHIVELOG'
ORDER BY SESSION_KEY;

 

For RMAN Full backup 

SET PAGES 999 LINES 999
COL STATUS FORMAT A9
COL "HRS" FORMAT 999.99
COL "MIN" FORMAT 99999.99
COL HOST_NAME FOR A10;
SELECT NAME,INSTANCE_NAME,HOST_NAME,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",
ELAPSED_SECONDS/60 "MIN"
FROM V$RMAN_BACKUP_JOB_DETAILS,
(SELECT NAME FROM V$DATABASE),
(SELECT INSTANCE_NAME,SUBSTR(HOST_NAME,1,10) "HOST_NAME" FROM GV$INSTANCE)
WHERE INPUT_TYPE NOT IN ('ARCHIVELOG')
ORDER BY SESSION_KEY;

Oracle RMAN backup expected time to complete

COL DBSIZE_MBYTES      FOR 99,999,990.00 JUSTIFY RIGHT HEAD "DBSIZE_MB"
COL INPUT_MBYTES       FOR 99,999,990.00 JUSTIFY RIGHT HEAD "READ_MB"
COL OUTPUT_MBYTES      FOR 99,999,990.00 JUSTIFY RIGHT HEAD "WRITTEN_MB"
COL OUTPUT_DEVICE_TYPE FOR A10           JUSTIFY LEFT HEAD "DEVICE"
COL COMPLETE           FOR 990.00        JUSTIFY RIGHT HEAD "COMPLETE %""
COL COMPRESSION        FOR 990.00        JUSTIFY RIGHT HEAD "COMPRESS|% ORIG"
COL EST_COMPLETE       FOR A20           HEAD "ESTIMATED COMPLETION"
COL RECID              FOR 9999999       HEAD "ID"
COL INSTANCE_NAME FOR A10;
COL HOST_NAME FOR A10;

SELECT RECID
     , OUTPUT_DEVICE_TYPE
     , DBSIZE_MBYTES
     , INPUT_BYTES/1024/1024 INPUT_MBYTES
     , OUTPUT_BYTES/1024/1024 OUTPUT_MBYTES
     , (OUTPUT_BYTES/INPUT_BYTES*100) COMPRESSION
     , (MBYTES_PROCESSED/DBSIZE_MBYTES*100) COMPLETE
     , TO_CHAR(START_TIME + (SYSDATE-START_TIME)/(MBYTES_PROCESSED/DBSIZE_MBYTES),'DD-MON-YYYY HH24:MI:SS') EST_COMPLETE
  FROM V$RMAN_STATUS RS
     , (SELECT SUM(BYTES)/1024/1024 DBSIZE_MBYTES FROM V$DATAFILE)
     , (SELECT NAME FROM V$DATABASE)
     , (SELECT INSTANCE_NAME,SUBSTR(HOST_NAME,1,10) FROM V$INSTANCE)
 WHERE STATUS='RUNNING'
   AND OUTPUT_DEVICE_TYPE IS NOT NULL;

DDL of User in ORACLE

 #!/bin/ksh
# Count Instance Numbers:
INS_COUNT=$( ps -ef|grep ora_pmon_$1|grep -v grep | awk '{print $8}' | wc -l)

# Exit if No DBs are running:
if [ $INS_COUNT -eq 0 ]
then
echo "No Database Running !"
exit
else
echo "###############################################################"
echo " Connecting $($1)..............."
echo "###############################################################"
fi

export ORAENV_ASK=NO
export ORACLE_SID=$1
. /usr/local/bin/oraenv
echo $ORACLE_SID
echo $ORACLE_HOME

DBNAME=$ORACLE_SID
export BACKUP_LOG_PATH=`date +%y%m%d%H%M%S`
listofuser=/u01/ListOfUsers_${DBNAME}_${BACKUP_LOG_PATH}.log
userddl=/u01/allusersddl_${DBNAME}_${BACKUP_LOG_PATH}.sql

######## Start Taking list of users ##########################

${ORACLE_HOME}/bin/sqlplus -S '/ as sysdba' << EOF
PROMPT
spool ${listofuser}
set pages 0
set echo off heading off feedback off
select username from dba_users where username not in ('SYS','SYSTEM','SYSBACKUP','SYSKM','OUTLN','SYSDG','SYSRAC','SYS\$UMF','DBSNMP','APPQOSSYS','GSMADMIN_INTERNAL','XDB','DBSFWUSER','GGSYS','ANONYMOUS',
'GSMCATUSER','OJVMSYS','AUDSYS','DIP','ORACLE_OCM','GSMUSER','REMOTE_SCHEDULER_AGENT','XS\$NULL') order by 1 asc;
spool off
EOF
################ END  ###################################


########### Generating DDL of users #####################
for USERNAME in `cat ${listofuser}`
do
export USERNAME

${ORACLE_HOME}/bin/sqlplus -S '/ as sysdba' << EOF
spool ${userddl} APPEND
set termout off
set linesize 190
set pages 50000
set feedback off
set trim on
set echo off
col USERNAME for a30
col account_status for a23
col profile for a10;
set pages 0
set echo off heading off feedback off
SELECT 'CREATE USER ' || u.username ||' IDENTIFIED ' ||' BY VALUES '''|| c.spare4||''' DEFAULT TABLESPACE ' || u.default_tablespace
||' TEMPORARY TABLESPACE ' || u.temporary_tablespace ||' PROFILE ' || u.profile || case when account_status= 'OPEN' then ';' else ' Account LOCK;' end
FROM dba_users u,user$ c where u.username=c.name and u.username=upper('$USERNAME')
UNION
select 'GRANT '||GRANTED_ROLE||' TO '||GRANTEE|| case when ADMIN_OPTION='YES' then ' WITH ADMIN OPTION;' else ';' end
from dba_role_privs where grantee= upper('$USERNAME')
UNION
select 'GRANT '||PRIVILEGE||' TO '||GRANTEE|| case when ADMIN_OPTION='YES' then ' WITH ADMIN OPTION;' else ';' end
from dba_sys_privs where grantee= upper('$USERNAME')
UNION
select 'GRANT '||PRIVILEGE||' ON '||OWNER||'.'||TABLE_NAME||' TO '||GRANTEE||case when GRANTABLE='YES' then ' WITH GRANT OPTION;' else ';' end
from DBA_TAB_PRIVS where GRANTEE=upper('$USERNAME');
spool off
EOF
done

echo "sql script save as : ${userddl}]"

[oracle@oel7 u01]$ ksh -x ddl.sh abc