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


Saturday, 30 October 2010

Oracle 9 - Manually Create Standby DB from Hotbackup

This is basically a "Note-To-Self".

High Level Steps for Reference, are:

  • Copy Datafiles across.
  • On Target, set up env variables, password file, oratab entry etc.
  • In the init.ora, pay particular attention to parameters:
    - db_name -> Same as the Primary Database to match the Controlfile
    - log_archive_format
    - log_archive_dest
    - log_file_name_convert
    - db_file_name_convert
    - fal_client
    - fal_server
  • Use a standby controlfile from the hotbackup, or create one manually
  • Recovery Time

    It's quite useful sometimes to be able to see the timestamp associated with a Database Recovery.
    select to_char((max(TIME_DP)),'DD MON YYYY HH24:MI') FROM SMON_SCN_TIME;

    Friday, 29 October 2010

    SQLServer - Check Mirror Status

    A handy SQL contributed by Andrew R.
    SELECT left(a.name,50) 'Database'
          , CASE b.status WHEN 0 THEN 'Suspended'
            WHEN 1 THEN 'Disconnected'
            WHEN 2 THEN 'Synchronizing'
                   WHEN 3 THEN 'Pending Failover'
                   WHEN 4 THEN 'Synchronized'
                   ELSE 'Investigate status'
             END 'Mirror_Status'
          , local_time 'Time'
       from master.sys.databases a
          , msdb.dbo.dbm_monitor_data b
      where a.database_id = b.database_id
        and b.local_time = (select max(A_ED.local_time) 
                       from msdb.dbo.dbm_monitor_data A_ED
                      where b.database_id = A_ED.database_id
                                 and A_ED.local_time <= GETDATE())
           group by a.name, b.database_id, b.status, b.local_time
           order by 1,3;
    

    Wednesday, 8 September 2010

    Undo Tablespace Usage

    select sum(blocks)
         , sum(bytes)/1024/1024
      from dba_undo_extents;
    

    Thursday, 2 September 2010

    Monday, 28 June 2010

    Truncate and Shrink Database

    On SQLServer I've learnt that we can't take for granted that a table truncate will reuse storage. Therefore to reclaim empty space following the truncate of a large table, I ran:
    DBCC SHRINKDATABASE (DBNAME, TRUNCATEONLY)
    GO

    Friday, 18 June 2010

    Unix Yesterday's Date

    date -d '1 day ago'

    date -d 'yesterday'

    Tuesday, 8 June 2010

    TSM - Command Line Restore

    dsmc restore "/path/file*" -pick -inactive -subdir=y

    to pick files - enter line number followed by +, separated by colons for example 1:2+

    Once selected O for ok will restore them.

    Tuesday, 1 June 2010

    cmdline stop/start/check-status of a service remotely

    Using the sc utility ...
    sc \\servername stop ServiceName
    sc \\servername start ServiceName

    sc \\servername query ServiceName

    Inspired by this, I decided to create a little DOS program which an end-user could use to stop/start a service.
    @ECHO OFF
    SETLOCAL enableextensions

    for /f "tokens=4" %%s in ('sc query OracleServiceXE ^| find /i "state"') do (SET _state=%%s)

    for /f "tokens=2" %%t in ('sc query OracleServiceXE ^| find /i "service_name"') do (SET _service_name=%%t)

    ECHO ================================
    ECHO %_service_name% is %_state%
    ECHO ================================


    if %_state%==RUNNING goto SRVC-R
    if %_state%==STOPPED goto SRVC-S

    :SRVC-R
    echo %_service_name% Is RUNNING, would you like to STOP it? Y/N
    set /p Input1=
    if /i "%Input1%"=="y" sc stop OracleServiceXE
    echo.
    sleep 10

    for /f "tokens=4" %%s in ('sc query OracleServiceXE ^| find /i "state"') do (echo %_service_name% is %%s)

    pause
    exit /b


    :SRVC-S
    echo %_service_name% Is STOPPED, would you like to START it? Y/N
    set /p Input1=
    if /i "%Input1%"=="y" sc start OracleServiceXE
    echo.
    sleep 10

    for /f "tokens=4" %%s in ('sc query OracleServiceXE ^| find /i "state"') do (echo %_service_name% is %%s)

    pause
    exit /b

    Friday, 7 May 2010

    Check Oracle Blocking Locks

    SELECT to_char(sysdate,'yyyy-mm-dd hh24:mi') dttm
    , ' --- BLOCKER --- '
    , lo.session_id
    , lo.process
    , lo.os_user_name
    , dob.object_name
    , dob.object_type
    , ' --- BLOCKING --- '
    , s.sid
    , s.username
    , s.status
    , s.schemaname
    , s.osuser
    , s.process
    , s.machine
    , s.terminal
    , s.program
    , s.sql_hash_value
    , s.logon_time
    , p.spid
    , l.id2
    , l.ctime
    FROM v$session s
    , v$process p
    , v$lock l
    , v$locked_object lo
    , dba_objects dob
    WHERE s.lockwait is not null
    AND p.addr(+) = s.paddr
    AND l.sid = s.sid
    AND l.type='TX'
    AND l.id2 = lo.xidsqn
    AND lo.object_id = dob.object_id

    Wednesday, 5 May 2010

    Count network connections

    Task:

    Get a count of number of network connections on a Windows server.

    My Solution:

    Create a batch file containing the following lines:

    REM list network connections and write them to a file
    netstat -n > netstat_count.txt
    
    REM search for non-existant string in file and return line count instead
    find /v /c "zzz" netstat_count.txt
    
    

    This returns something like ...

    ---------- NETSTAT_COUNT.TXT: nnn

    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

    Wednesday, 31 March 2010

    Oracle Shell Script

    An example of a shell script which connects to multiple unix hosted databases and runs a simple SQL.
    #!/bin/ksh
    #
    # Shell script that connects to multiple database servers and runs a SQL
    #
    
    # 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

    Thursday, 25 February 2010

    Check Oracle Database Size

    A good way to check the size of your database is to add up the size of your tablespaces (including Temp) and the redo logs as follows:
    select sum(megabytes) 
     from (
    select sum(bytes)/1024/1024 megabytes from dba_temp_files
    union
    select sum(bytes)/1024/1024 megabytes from dba_data_files
    union
    select sum(bytes)/1024/1024 megabytes from v$log
    );

    Saturday, 6 February 2010

    RMAN Archivelog Deletions

    Note to self
    rman nocatalog<< DATA
    connect target
    delete noprompt archivelog until time 'SYSDATE-n';
    DATA
    rman nocatalog<< DATA
    connect target
    delete noprompt archivelog until sequence= nnnnnnn;
    DATA

    A scenario where so many archivelogs are being generated, that you have to resort to using RMAN to clear out archivelogs on the fly, can also be supported by increasing the physical space for archivelog files alongwith boosting the db_recovery_file_dest_size parameter i.e. increasing the flash recovery area.

    ORA-19815: WARNING: db_recovery_file_dest_size of n bytes is n% used, and has n remaining bytes available.

    alter system set db_recovery_file_dest_size=nM/G scope=both;

    Monday, 25 January 2010

    Close Encounter with Pakistan Cricket team

    On Saturday night, happened to be out and about in Sydney. Decided to get some takeaway from a Pakistani restaurant, but was denied service as they were playing host to the Pakistan Cricket team.

    I wish I had done better, but for my troubles, I was able to get autographs from Shoaib Malik, one of the Akmal brothers (Kamran I think?) and Waqar Younis.

    Also had some luck at a second hand book shop where I picked up for a pittance a West Indian cricket book with autographs of Michael Holding and Colin Croft.

    Links to photos and scans of the autographs ....

    http://www.nazimcricket.com/me_younis.jpg
    http://www.nazimcricket.com/images/younis11.jpg
    http://www.nazimcricket.com/me_malik_akmal.jpg
    http://www.nazimcricket.com/images/malmal.jpg
    http://www.nazimcricket.com/images/holcroft.jpg