Shrink msdb database SQL Server

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, same thing in T-SQL: dbcc shrinkdatabase('msdb') To see disk usage for a database: Management Studio: […]

Read More Shrink msdb database SQL Server
May 18, 2013
Posted in SQL server
One Comment

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

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 2012 – Part 2 70-459 Transition your MCITP on SQL Server 2008 to MCSE: Data Platform […]

Read More Study notes crib sheet for exams 70-457 70-458 70-459 MCSE SQL Server 2012
May 1, 2013
Posted in SQL server
2 Comments

Angry DBA

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 a long time ago, but is still available thanks to the Wayback Machine. H/t APC.

Read More Angry DBA
March 8, 2013
Posted in Uncategorized

Upgrade rman catalog – no need to upgrade oracle database version

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 rman catalog. To do this, run the following commands from the higher version oracle database: […]

Read More Upgrade rman catalog – no need to upgrade oracle database version
March 2, 2013
Posted in Rman
One Comment

Patch upgrade Oracle RAC 11.2.0.2 to 11.2.0.3 on linux

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 unzip software from My Oracle Support into e.g. /unzipped Main patch is 7 large zipfiles, […]

Read More Patch upgrade Oracle RAC 11.2.0.2 to 11.2.0.3 on linux
March 1, 2013
Posted in Installs, Linux, RAC

Fix for ORA-24247 ACL calling Oracle Reports

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 by access control list (ACL) ORA-06512: at “MYUSER.MYPROC”, line 60 ORA-06512: at line 1 Fix […]

Read More Fix for ORA-24247 ACL calling Oracle Reports
February 18, 2013
Posted in Uncategorized

List databases using materialized view log

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 log: SELECT TRUNC(snaptime$$) , COUNT(*) FROM owner.mlog$_mytable GROUP BY TRUNC(snaptime$$) ORDER BY 1 ; Oracle’s […]

Read More List databases using materialized view log
January 31, 2013
Posted in Materialized views