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 ( ...

 In Oracle database, the Flash Recovery Area or FRA is a location on disk where the database can create and manage several kinds of backup and recovery-related files. Main file types are archivelog, flashback log, backups, as well as mirrors for your control files and redo log files. All files in the FRA are Oracle-managed files. Using a Flash Recovery Area simplifies the administration of your database by automatically retaining them for as long as they are needed for restore and recovery activities, and deleting them when they are no longer needed, because the space is needed for another backup and recovery-related purpose. Checking the current usage  You can check the configuration by looking at two parameters.The parameters are db_receovery_file_dest and second one db_recovery_file_dest_size .  db_receovery_file_dest point to location where the files will be located in Server . FOr normal single Databases this will be any directory for RAC this will b...

 The Process describes the DB cloning using RMAN backup of Source Database . Step 1 : Crete a Pfile for Target database (mqtest) If the Database is being dropped , retain the spfile of existing database . Step 2 : Copy Password file from Source to target Server . Password file will be in location $ORACLE_HOME/dbs Password file name will be in format orapwORACLE_SID copy the password file from Sorce server to target Server and rename the file to target DB name Step 3 : Startup the database is Nomount State using Pfile (This step can be skipped if SPfile is already Availble ) SQL > start nomount pfile='$ORACLE_HOME/dbs/initmqtest.ora' ; SQL > create spfile from pfile='$ORACLE_HOME/dbs/initmqtest.ora' ; SQL > Shut immediaate ; Step4: Startup DB again in Nomount stage and verify if DB is in Archivelog mode set lines 550 pages 550 alter session set nls_date_format='DD_MM_YY HH24:MI' ; col db_unique_name for a20 col instance_name for a20 Col startup_time for ...

Below Sql generates the Size of backup and Time taken to Completed the backup .  SELECT TO_CHAR(completion_time, 'YYYY-MON-DD') completion_time, type, round(sum(bytes)/1048576) MB, round(sum(elapsed_seconds)/60) min   FROM   ( SELECT  CASE     WHEN s.backup_type='L' THEN 'ARCHIVELOG'     WHEN s.controlfile_included='YES' THEN 'CONTROLFILE'     WHEN s.backup_type='D' AND s.incremental_level=0 THEN 'LEVEL0'     WHEN s.backup_type='I' AND s.incremental_level=1 THEN 'LEVEL1'     END type, TRUNC(s.completion_time) completion_time, p.bytes, s.elapsed_seconds     FROM v$backup_piece p, v$backup_set s     WHERE p.status='A' AND p.recid=s.recid     UNION ALL     SELECT 'DATAFILECOPY' type, TRUNC(completion_time), output_bytes, 0 elapsed_seconds FROM v$backup_copy_details) GROUP BY TO_CHAR(completion_time, 'YYYY-MON-DD'), type ORDER BY 1 ASC,2,...