ORACLE DBA BLOG

 Normally in RMAN the date is show in format DD-MON-YY . we may require output to be more precise in format DD-MON-YY HH24:MI.    We cannot user sql "alter session set NLS_DATE_FORMAT"  . Instead we can use below command to extract output in desired output .  $ rman target /  RMAN>  host 'export NLS_DATE_FORMAT="DD-MON-YYYY HH24:MI:SS"; $ORACLE_HOME/bin/rman target / catalog ****';  RMAN > list backup summary ;  

 1) Basic information of database (primary or standby) SQL> SELECT DATABASE_ROLE, DB_UNIQUE_NAME INSTANCE, OPEN_MODE, PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS FROM V$DATABASE; 2) Check for messages/errors SQL> SELECT MESSAGE FROM V$DATAGUARD_STATUS; 3) To display current status information for specific physical standby database background processes. SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY ; 4) Show received archived logs on physical standby -Run this query on physical standby SQL> select registrar, creator, thread#, sequence#, first_change#, next_change# from v$archived_log; 5) To check the log status  select 'Last Log 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 Log received : ' Logs, to_char(next_time,'DD-MON-YY:HH24:MI:SS') Time from v

 extract Date in DDMONYYYY format for future date   DT=`date +'%d%^b%Y' -d "365 days"` echo $DT  Grep Multiple Words in single time  cat ****|grep -i 'Terrm1\|Term2\|Term3' To comments every line in a script or in Crontab :%s/^/#/g  =====> Put entries at start of all lines  10,20s/^/#/ =====> put entries from line 10 to 20 How to remove certain terms like Special characters in shell Script  tr (trim command) : tr -d 'values to trim'           

    select t.sample_time, t.sql_id, t.temp_mb, t.temp_diff            ,s.sql_text       from (             select --session_id,session_serial#,                    --'alter system kill session ''' || session_id || ',' || session_serial# || ''' immediate;' kill_session_cmd,                    trunc(sample_time) sample_time,sql_id, sum(temp_mb) temp_mb, sum(temp_diff) temp_diff                    , row_number() over (partition by trunc(sample_time) order by sum(temp_mb) desc nulls last) as rn               from (                     select sample_time,session_id,session_serial#,sql_id,temp_space_allocated/1024/1024 temp_mb,                            temp_space_allocated/1024/1024-lag(temp_space_allocated/1024/1024,1,0) over (order by sample_time) as temp_diff                      --from dba_hist_active_sess_history                      from v$active_session_history                     where 1 = 1                     -- session_id=&1