Adrci Show Alert Hung

Sometimes alert log of DB is too large.  We may mv , rm  or vi them to make it smaller
However it may affect format the alert logs, adrci  show alert may hung due to bad format
We can use purge commend of adrci to fix it.  It's side effect of the purge command.

ADR Homes:
diag/rdbms/cmru/test1
adrci> set home diag/rdbms/test/test1
adrci> PURGE -age 1440 -type ALERT

ORA-01031 insufficient privileges

2 possible reasons

• check password file, if possible, recreate them on all nodes to make them consistent
• double check password file name ,must be orapw<SID> , not orapwd<SID>
• When we setup dataguard service, the standby DB may need to copy password file from primary, and rename as  orapw<sid>
• Use below sql to check password file user

  select * from v$pwfile_users;
  select value from v$option where parameter='Oracle Database Vault';

How To Transfer SCN to Redo Log Sequence

Sometimes we need to know date and redo log sequence from SCN
When we debug, we know SCN, then we need to know what archive logs and log sequence number

ie we have SCN 0x0b09.1c7947c0 . its hex
We converts it  to decimal it is 12133760321472

use below sql to do it

SELECT thread#, name, sequence#, first_time, next_time
   FROM gv$archived_log
  WHERE 12133760321472
BETWEEN first_change# AND next_change#;

How To Check User Login Information In The Past

Use DBA_HIST_ACTIVE_SESS_HISTORY:

To get client machine name:
SELECT distinct machine  FROM DBA_HIST_ACTIVE_SESS_HISTORY

To get timeframe
SELECT *   FROM DBA_HIST_ACTIVE_SESS_HISTORY where sql_exec_start  > sysdate -7  order by sql_exec_start desc

To get User name

SELECT  user_id  FROM DBA_HIST_ACTIVE_SESS_HISTORY where sql_exec_start  > sysdate -7  order by sql_exec_start desc

user user_id to get name from dba_users

Output v$flash_recovery_area_usage is incorrect

sometimes we clean recovery area manually , delete archive logs, old files ourselves
the output v$flash_recovery_area_usage can be  incorrect.

Use rman to sync the data dictionary

RMAN>CROSSCHECK BACKUP;
RMAN>CROSSCHECK ARCHIVELOG ALL;   (crosscheck won't delete, just mark them)

RMAN>Delete expired backup;     (will delete items which crosscheck fail )
RMAN>Delete expired archivelog all;
RMAN>Delete force obsolete;

RMAN> list backup    (it won't list data copies )
RMAN> list datafilecopy all;
RMAN> crosscheck datafilecopy all;
RMAN> delete datafilecopy all;
RMAN>delete datafilecopy tag=test_copy;

RMAN>Delete archivelog all completed before  'SYSDATE-7';
 
Then
use exec dbms_backup_restore.refreshagedfiles ; 
to refresh