Author Archives: Andrew Fraser

Shrink msdb database SQL Server

Posted on 18 May 2013 by Andrew Fraser

Steps to shrink msdb database in SQL Server 2005: T-SQL: truncate table msdb.dbo.log_shipping_monitor_history_detail T-SQL: exec msdb.dbo.sp_purge_jobhistory @oldest_date='2013-02-07T13:55:10'(change date above as required) T-SQL: alter index all on msdb.dbo.sysjobhistory reorganize Management Studio: system databases > msdb > tasks > shrink > databaseOR, … Continue reading →

Posted in SQL server | Leave a comment

Study notes crib sheet for exams 70-457 70-458 70-459 MCSE SQL Server 2012

Posted on 1 May 2013 by Andrew Fraser

Here is the crib sheet/study notes I used for the three exams: 70-457 Transition your MCTS on SQL Server 2008 to MCSA: SQL Server 2012 – Part 1 70-458 Transition your MCTS on SQL Server 2008 to MCSA: SQL Server … Continue reading →

Posted in SQL server | Leave a comment

Angry DBA

Posted on 8 March 2013 by Andrew Fraser

Some fun from the Angry DBA site – http://web.archive.org/web/20050206023801/http://www.angrydba.com/ Contents: Why are DBA’s angry? Angry DBA stories Is your DBA angry? Angry DBA Gallery How to make your DBA angry DBA Jokes Are you an angry DBA? The website disappeared … Continue reading →

Posted in Uncategorized | Leave a comment

Upgrade rman catalog – no need to upgrade oracle database version

Posted on 2 March 2013 by Andrew Fraser

Rman catalog can be upgraded to a higher version than its hosting database oracle version. This is useful if you want to back up new versions of oracle without changing the oracle version on the database used to host the … Continue reading →

Posted in Rman | Leave a comment

Patch upgrade Oracle RAC 11.2.0.2 to 11.2.0.3 on linux

Posted on 1 March 2013 by Andrew Fraser

I used the steps below to apply patchset 11.2.0.3 to an existing 11.2.0.2 2-node RAC cluster on linux RHEL 5.8 64 bit. Note that GRID_HOME and DB_HOME locations will both be changed as part of this patchset. 0) Download and … Continue reading →

Posted in Installs, Linux, RAC | Leave a comment

Fix for ORA-24247 ACL calling Oracle Reports

Posted on 18 February 2013 by Andrew Fraser

Found this error on upgrade to 11gR2 trying to call Oracle Reports from within database: ERROR at line 1: ORA-20001: Error for submit_report. Report: ab_batch. Error -29273 ORA-29273: HTTP request failed ORA-06512: at “SYS.UTL_HTTP”, line 1722 ORA-24247: network access denied … Continue reading →

Posted in Uncategorized | Leave a comment

List databases using materialized view log

Posted on 31 January 2013 by Andrew Fraser

See which remote databases are using a ‘materialized view log’ on source database with: SELECT mview_site , COUNT(*) FROM dba_registered_mviews GROUP BY mview_site ORDER BY 1 ; The snaptime$$ column shows how old the data is in any materialized view … Continue reading →

Posted in Materialized views | Leave a comment

RAC 11.2.0.2 Grid install fails on second node until disable virbr0 virtual network

Posted on 14 November 2012 by Andrew Fraser

I found I had to disable the virbr0 network interface to get Oracle RAC 11gR2 11.2.0.2 to install correctly (on RHEL 5.8 64 bit 2.6.18-308.el5 with ocfs2). Problem was that root.sh failed on the second node. The error recorded in … Continue reading →

Posted in Uncategorized | Leave a comment

Timeout SQL materialized view refresh

Posted on 31 October 2012 by Andrew Fraser

Problem today where one hourly cron MV refresh hung (waiting on “SQL*Net message from dblink”), causing the other refreshes to queue up waiting on locks, eventually using up all the session so users got “ORA-00018: maximum number of sessions exceeded” … Continue reading →

Posted in Uncategorized | Leave a comment

Convert java dates to oracle sql dates (esp. odm_publicobject createdate)

Posted on 26 October 2012 by Andrew Fraser

Java dates are recorded in milliseconds after 01-Jan-1970 00:00:00 GMT. To convert these to oracle dates, use sql like: SELECT TO_DATE(’01-JAN-1970 00:00:00′,’DD-MON-YYYY HH24:MI:SS’) + createdate/(1000*60*60*24) createdate , creator FROM odm_publicobject WHERE TO_DATE(’01-JAN-1970 00:00:00′,’DD-MON-YYYY HH24:MI:SS’) + createdate/(1000*60*60*24) > sysdate – 30 … Continue reading →

Posted in Scripts | Leave a comment