Note: Technical Talk being moved to:

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(,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, b.database_id, b.status, b.local_time
           order by 1,3;