Wednesday, November 13, 2013

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

Tuesday, November 12, 2013

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