Execution Time of an SQL using SQL_ID with respect to SNAP_ID

Here we can calculate Elapsed of time of an sql statement using sql id. Below query will figure out the Elapsed time in seconds.



col begin_time for a25
col end_time for a11
col inst for 99999
col snapid for 999999
set lines 200
set pages 20000
select snap_id snapid,
(select substr(BEGIN_INTERVAL_TIME,1,18)||' '||substr(BEGIN_INTERVAL_TIME,24,2) from dba_hist_snapshot b where b.snap_id=a.snap_id and

a.INSTANCE_NUMBER=b.INSTANCE_NUMBER) begin_time
,(select substr(end_INTERVAL_TIME,11,8)||' '||substr(end_INTERVAL_TIME,24,2) from dba_hist_snapshot b where b.snap_id=a.snap_id and

a.INSTANCE_NUMBER=b.INSTANCE_NUMBER) end_time
,INSTANCE_NUMBER inst , PLAN_HASH_VALUE,
EXECUTIONS_DELTA Executions,
ROWS_PROCESSED_DELTA rows1,
round( CPU_TIME_DELTA /1000000,0) cpu_time,round(IOWAIT_DELTA /1000000,0) io_wait,
round( ELAPSED_TIME_DELTA /1000000,0) elapsed
from wrh$_sqlstat a where sql_id in('&SQL_ID')
order by snap_id, INSTANCE_NUMBER;

Thanks,

Jyothish Balakrishnan

Archive Generation Details

Archive Generation per Hour

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY MM DD HH24:MI:SS';
select trunc(COMPLETION_TIME,'HH') Hour,round(sum(BLOCKS*BLOCK_SIZE)/1024/1024/1024) GB,count(*) Archives from v$archived_log group by trunc(COMPLETION_TIME,'HH') order by 1 ;

Archive Generation per Day

select trunc(COMPLETION_TIME,'DD') Day, round(sum(BLOCKS*BLOCK_SIZE)/1024/1024/1024) GB,count(*) Archives_Generated from v$archived_log
group by trunc(COMPLETION_TIME,'DD') order by 1;

How to find out ASM file locations for a Database

column full_alias_path format a70
column file_type format a15


select concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path,
       system_created, alias_directory, file_type
from ( select b.name gname, a.parent_index pindex, a.name aname,
              a.reference_index rindex , a.system_created, a.alias_directory,
              c.type file_type
       from v$asm_alias a, v$asm_diskgroup b, v$asm_file c
       where a.group_number = b.group_number
             and a.group_number = c.group_number(+)
             and a.file_number = c.file_number(+)
             and a.file_incarnation = c.incarnation(+)
     )
start with (mod(pindex, power(2, 24))) = 0
            and rindex in
                ( select a.reference_index
                  from v$asm_alias a, v$asm_diskgroup b
                  where a.group_number = b.group_number
                        and (mod(a.parent_index, power(2, 24))) = 0
                        and a.name = '&DATABASENAME'
                )
connect by prior rindex = pindex;


You can use this query with any Database running on the same server for a Database.

Drop Database Link in another schema .

We can drop Database link in another schema by a simple procedure without knowing password of the user

SQL> conn / as sysdba
SQL> create or replace procedure schema.procedurename as
begin
execute immediate  ‘drop database link  db_link_name’;
end procedurename;


SQL>exec Procedurename;

Oracle Multimedia (ORDIM) status is "LOADING" in DBA_REGISTRY

Oracle Version - 11g

Cause : Oracle Multimedia Registry Components are not properly configured .

sqlplus / as sysdab

SQL> COL COMP_NAME FOR A20
SQL> COL COMP_ID FOR A20
SQL> select comp_id,comp_name,status from dba_registry where comp_name='Oracle Multimedia';

SQL> select comp_id,comp_name,status from dba_registry where comp_name='Oracle Multimedia';

COMP_ID              COMP_NAME            STATUS
-------------------- -------------------- --------
ORDIM                Oracle Multimedia    LOADING



Action

1) Reload Registry component


SQL>conn / as sysdba

SQL> execute sys.dbms_registry.loaded('ORDIM');

2) Validate Registry Status .


SQL> execute sys.dbms_registry.valid('ORDIM');


Now we can see the status is "VALID"


SQL> select comp_id,comp_name,status from dba_registry where comp_name='Oracle Multimedia';

COMP_ID              COMP_NAME            STATUS
-------------------- -------------------- --------
ORDIM                Oracle Multimedia    VALID

Drop user failed with ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-00942: table or view does not exist

Cause

Table system.aq$_internet_agent_privs is missing in "SYSTEM" Schema.



Action:

1. Check if system.aq$_internet_agent_privs exist.

       SQL> conn / as sysdba

       SQL> desc system.aq$_internet_agent_privs

2. Make sure that the default tablespace of SYSTEM user is properly set to SYSTEM tablespace.

        SQL> select default_tablespace from dba_users where   username=’SYSTEM’;

      DEFAULT_TABLESPACE
      ——————————
      SYSTEM

Run the below Script as sysdba user.

      SQL> conn / as sysdba
      SQL> @$ORACLE_HOME/rdbms/admin/catqueue.sql 
      SQL> exit


3. Then execute the DROP USER command again.

4.   SQL> Drop user Username;

      User Dropped

ORA-16053: DB_UNIQUE_NAME is not in the Data Guard Configuration

Cause : DB_UNIQUE_NAME is not in the Data Guard Configuration. We can check current DB_UNIQUE_NAME in the configuration.

SQL> show parameter log_archive_config;

The list of valid DB_ UNIQUE_NAMEs can be seen with the V$DATAGUARD_CONFIG view. This problem can also occur when specifying a non-standby destination with an DB_ UNIQUE_NAME attribute that does not match the DB_UNIQUE_NAME initialization parameter for the current instance.

Action:

Amend the   DB_UNIQUE_NAME in LOG_ARCHIVE_CONFIG parameter . Below example shows  physical standby database as ORCL_STBY

1) ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORCL, ORCL_STBY)';

2)  Re – enable Standby Archive Location Parameter;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE= ORCL_STBY  REOPEN=5 NOAFFIRM  ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME= ORCL_STBY;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3=ENABLE;

3) Check that status of the  standby destination.

SQL>  select dest_name,status,error from v$archive_dest_status where dest_id=2;

Cheers !!!