The Oracle DBA's

#################################FAIL OVER#################################### --Primary Database Crushes Due to several reason like Hardware failure. On Standby Database: #Stop Redo Apply:-   ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; #Finish applying all received redo data:- ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH; #Verify that the target standby database is ready to become a primary database:- SELECT SWITCHOVER_STATUS FROM V$DATABASE; #A value of either TO PRIMARY or SESSIONS ACTIVE indicates that the standby database is ready to be switched to the primary role. #Perform failover: ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN; /* Session shutdown is optional if  switchover status returns TO_PRIMARY */ srvctl status database -d ORCL_PRODRAC2 -v Thanks & Regards Nikhil Kumar.

#################### AWR (automatic workload repository) of Oracle #################### Retention period of AWR report: select retention from dba_hist_wr_control; /*If we have more than 7 days,we can reduce it to 7 day which is 7*24*60 = 10080 minutes. the retention period is modified to 7 days (10080 minutes) and  the interval between each snapshot is 60 minutes through below query. */ execute dbms_workload_repository.modify_snapshot_settings (interval => 60,retention => 10080); In case while reducing the retention period if we face an  error then check the MOVING_WINDOW_SIZE value with below query SELECT moving_window_size FROM dba_hist_baseline WHERE baseline_type = 'MOVING_WINDOW'; exec DBMS_WORKLOAD_REPOSITORY.modify_baseline_window_size( window_size =>7); Then update it to correct value and then execute the above AWR retention query. execute dbms_workload_repository.modify_snapshot_settings (interval => 60,retention => 10080); Once the...

#################Check whats occupying SYSAUX tablespace######################## SQL> --Check whats occupying SYSAUX tablespace: SQL> --Occupent occupying space  in sysaux tablespac SQL> SQL> set pages 333 lines 222 SQL> select occupant_name,occupant_desc,space_usage_kbytes from v$sysaux_occupants; Thanks & Regards Nikhil Kumar.

How to find the assigned privileges to a user in oracle database : In order to find this, below queries will be helpful. 1) select dbms_metadata.get_granted_ddl('ROLE_GRANT','&SCHEMA') from dual union all select dbms_metadata.get_granted_ddl('SYSTEM_GRANT','&&SCHEMA') from dual; 2) select * from dba_role_privs connect by prior granted_role = grantee start with grantee = '&SCHEMA' order by 1,2,3;  select * from dba_sys_privs  where grantee = '&&SCHEMA' or grantee in (select granted_role from dba_role_privs connect by prior granted_role = grantee start with grantee = '&&SCHEMA') order by 1,2,3; select * from dba_tab_privs  where grantee = '&&SCHEMA' or grantee in (select granted_role from dba_role_privs connect by prior granted_role = grantee start with grantee = '&&SCHEMA') order by 1,2,3,4; Thanks & Regards Nikhil Kumar.