Note: Technical Talk being moved to:
www.nazimcricket.com/wiki


Wednesday, 28 April 2010

Oracle / SQL Server Equivalent

This post shall define the equivalent views between Oracle and SQLServer.
OracleSQLServer
sys.dba_tablesinformation_schema.tables
sys.dba_tab_columnsinformation_schema.columns

Tuesday, 27 April 2010

OEM Repository Scripts

This Blog will be a running compilation of useful Enterprise Manager Repository scripts (run as the SYSMAN user). Please note some of the metric names used here may be exclusive to my work place and not generic. You should be able to find something similar in your databases though!
 
Database Sizes (1)
select a.DTTM 
     , trim(substr(a.target_name, instr(a.target_name,'.')+1,20)) HOST_NAME
     , trim(substr(a.target_name, 1, instr(a.target_name,'.')-1)) DATABASE
     , ALLOC_GB
     , USED_GB
   from
(SELECT TARGET_NAME 
     , TO_CHAR(ROLLUP_TIMESTAMP,'YYYY-MM-DD') DTTM
     ,TO_CHAR(MAX(AVERAGE),'999999.99') USED_GB
  FROM MGMT$METRIC_DAILY 
 WHERE METRIC_NAME = 'DATABASE_SIZE'
   AND TO_CHAR(ROLLUP_TIMESTAMP,'DD') = '01'
   AND METRIC_COLUMN = 'USED_GB'
GROUP BY TARGET_NAME, TO_CHAR(ROLLUP_TIMESTAMP,'YYYY-MM-DD')) a ,
(SELECT TARGET_NAME 
     , TO_CHAR(ROLLUP_TIMESTAMP,'YYYY-MM-DD') DTTM
     ,TO_CHAR(MAX(AVERAGE),'999999.99') ALLOC_GB
  FROM MGMT$METRIC_DAILY 
 WHERE METRIC_NAME = 'DATABASE_SIZE'
   AND TO_CHAR(ROLLUP_TIMESTAMP,'DD') = '01'
   AND METRIC_COLUMN = 'ALLOCATED_GB'
GROUP BY TARGET_NAME, TO_CHAR(ROLLUP_TIMESTAMP,'YYYY-MM-DD')) b
WHERE A.TARGET_NAME = B.TARGET_NAME
  AND A.DTTM = B.DTTM
ORDER BY HOST_NAME, DATABASE, DTTM
Database Sizes (2)
SELECT target_name, ROUND (SUM (FILE_SIZE) / 1024 / 1024 / 1024) GB
FROM MGMT$db_datafiles_all
GROUP BY target_name
Datafile Sizes - A good way to find datafiles incorrectly set to autoextend to Unlimited, i.e. growing to 32768M
SELECT target_name
, file_name
, sum (file_size) / 1024 / 1024
FROM mgmt$db_datafiles_all
HAVING sum(file_size)/1024/1024 > 15000
GROUP BY target_name, file_name
ORDER BY 3 desc;
Tablespace Allocated and Used Space Query
select A.RT
, A.ALLOCATED
, B.USED
from (
SELECT TO_CHAR(ROLLUP_TIMESTAMP,'YYYY-MM') RT
, AVG(AVERAGE) ALLOCATED
FROM MGMT$METRIC_DAILY
WHERE TARGET_NAME LIKE {database_name}
AND METRIC_NAME='tbspAllocation'
and key_value={tablespace_name}
and METRIC_COLUMN='spaceAllocated'
group by TO_CHAR(ROLLUP_TIMESTAMP,'YYYY-MM')
) a,
(
SELECT TO_CHAR(ROLLUP_TIMESTAMP,'YYYY-MM') RT
, AVG(AVERAGE) USED
FROM MGMT$METRIC_DAILY
WHERE TARGET_NAME LIKE {database_name}
AND METRIC_NAME='tbspAllocation'
and key_value={tablespace_name}
and METRIC_COLUMN='spaceUsed'
group by TO_CHAR(ROLLUP_TIMESTAMP,'YYYY-MM')
) b
where a.rt = b.rt
order by a.rt DESC;
Clear Alerts
select 'execute em_severity.delete_current_severity(p_target_guid =>'''||sev.target_guid||''', p_metric_guid =>'''||sev.metric_guid||''', p_key_value =>'''||key_value||''');' as command
from MGMT_TARGETS tgt, MGMT_CURRENT_SEVERITY sev
where TGT.TARGET_NAME like '%{TGT_NAME}%';
For older severities - just a straight delete from MGMT_SEVERITY will do the trick.
User Defined Metrics
select *
from mgmt$metric_current
where metric_name like '%UDM%';

Tuesday, 20 April 2010

Monday, 19 April 2010

Oracle 10g Log Mining

Please note the database parameter log_parallelism needs to be set at 1 for this to work

In this day and age of flashback configuration, log mining almost seems like an out-dated process, never to see the light of day. However, today it came in handy.

I was asked to check for any transactional rollbacks which may have been issued on the database during a certain time period. Unfortunately flashback was only configured for a short duration on the database and the time I had to check was out of bounds.

I therefore ....


  • Copied across the archive log files from backup tape to the archive log directory.


  • I then added to log miner the archive log files I wanted mined:
    exec SYS.DBMS_LOGMNR.ADD_LOGFILE('pathname/filename.arc');


  • The next step was to start log-mining based on the timestamps
    BEGIN
    SYS.DBMS_LOGMNR.START_LOGMNR(
    STARTTIME => TO_DATE('13-Apr-2010 08:00', 'DD-MON-YYYY HH24:MI')
    ,ENDTIME => TO_DATE('13-Apr-2010 16:00', 'DD-MON-YYYY HH24:MI')
    ,OPTIONS => SYS.DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + SYS.DBMS_LOGMNR.CONTINUOUS_MINE
    );
    END;
    /


  • This then gave me the information I needed:
    SELECT timestamp, sql_redo FROM v$logmnr_contents


  • I concluded the task by clearing out logminer:
    exec SYS.DBMS_LOGMNR.END_LOGMNR;
  • Thursday, 15 April 2010

    Trigger to Create Public Synonyms

    We have an application which creates dynamic temporary tables, which require public synonymns generated to enable users to access these tables. The solution which came to mind was to create a DDL trigger. However, once I'd finished writing the trigger, I discovered the following limitation:
    ORA-30511 invalid DDL operation in system triggers. 
    Cause
    An attempt was made to perform an invalid DDL operation in a system trigger. Most DDL operations currently are not supported in system triggers. The only currently supported DDL operations are table operations and ALTER?COMPILE operations. 
    

    I tried a few different options such as playing around with my favourite pragma autonomous_transaction method, but couldn't get the trigger to execute the "create public synonym" command.

    Therefore, I decided to create a stored procedure which would accept the parameters (objowner and objname) and wrote the trigger to execute a background job which would run the procedure in a minute - nice and easy solution.

    The Stored Procedure
    CREATE OR REPLACE PROCEDURE SYN_PROC (objname in varchar2, objowner in varchar2) IS
    
    begin
    
    execute immediate 'create public synonym  ' || objname || '  for ' || objowner || '.' || objname;
    
    end;
    /
    
    The Trigger
    CREATE OR REPLACE TRIGGER SYN_TRIG
    AFTER create ON SCHEMA
    declare
    
    jobnum   number;
    
    BEGIN 
    
    IF ORA_DICT_OBJ_OWNER='SCHEMA_OWNER' AND ORA_DICT_OBJ_NAME LIKE 'TABLENAME%' THEN
    
       SYS.DBMS_JOB.SUBMIT (jobnum, 'ORACLE_DBA.SYN_PROC(''' || ORA_DICT_OBJ_NAME || ''',''' || ORA_DICT_OBJ_OWNER || ''');' , sysdate+(1/24/60));
    
    END IF;
    
    END;
    /
    

    Wednesday, 14 April 2010

    Recreate Users and Roles

    At my workplace, there is often a need to capture user and role information prior to performing a database refresh, to allow existing user accounts and permissions to be re-created post-refresh.

    I have come up with the following scripts to assist with this task:

    List and generate scripts to recreate accounts with Domain Authenticaton:
    select 'CREATE LOGIN [DOMAIN\' + name + '] FROM WINDOWS WITH DEFAULT_DATABASE=[ceinfraprd]' from dbo.sysusers where isntname=1  
    
    
    List and generate scripts to reinstate the roles:
    select distinct 'exec sp_addrolemember ' + '''' + y.name + ''', ' + '''' + x.name + ''''
      from
    (select uid, name, groupuid
      from dbo.sysusers a
         , dbo.sysmembers b
     where a.uid = b.memberuid) as x,
    (select uid, name, groupuid
      from dbo.sysusers a
         , dbo.sysmembers b
     where a.uid = b.groupuid) as y
    where x.groupuid = y.uid

    Tuesday, 13 April 2010

    Oracle Shell Script - Emailing the Logfile

    An example of a shell script which connects to multiple unix hosted databases and runs a simple SQL. This version emails the logfile to people on the mailing list.
    #!/bin/ksh
    #
    # Shell script that connects to multiple database servers and runs a SQL
    #
    
    MAIL_LIST="nazim@nazimcricket.com"
    
    # Server List
    SERVERS='server1 server2 server3'
    
    LOG="/tmp/logfile.log"
    
    > $LOG
    
    for SERVER in $SERVERS
    do
    
    sqlplus -s username/password@$SERVER << EOF | tee -a $LOG
    select * from global_name;
    exit;
    EOF
    
    done
    
    uuencode ${LOG} ${LOG} | mail -s "Results Log" ${MAIL_LIST}
    

    Monday, 12 April 2010

    MS SQL Server user script

    I don't have much experience with SQLServer, so I'm sure there are better ways of doing this. This is the way I've worked out how to list users and their granted roles ....
    select distinct x.uid, x.name, y.name
      from
    (select uid, name, groupuid
      from dbo.sysusers a
         , dbo.sysmembers b
     where a.uid = b.memberuid) as x,
    (select uid, name, groupuid
      from dbo.sysusers a
         , dbo.sysmembers b
     where a.uid = b.groupuid) as y
    where x.groupuid = y.uid
    order by x.name

    Oracle Shell Script with Password Prompts

    An example of a shell script which connects to multiple unix hosted databases and runs a simple SQL. This version prompts for the username/password.
    #!/bin/ksh
    #
    # Shell script that connects to multiple database servers and runs a SQL
    #
    
    # Script expects the username and password to be parsed as arguments.
    # If not found, it will error with message ...
    
    if [[ $# -lt 2 ]]
    then
            print "\nUSAGE: script_name.ksh username password\n"
            print "\nExample: script_name.ksh scott tiger\n"
            exit 0
    fi
    
    username=$1
    password=$2
    
    # Server List
    SERVERS='server1 server2 server3'
    
    LOG="/tmp/logfile.log"
    
    > $LOG
    
    for SERVER in $SERVERS
    do
    
    sqlplus -s $username/$password@$SERVER << EOF | tee -a $LOG
    select * from global_name;
    exit;
    EOF
    
    done