mattypenny

A quick post on a quick fix.

I installed Sql Server Reporting Services 2008 R2 on the report server, then installed Oracle client 11.2.0.4.

I copied across a valid tnsnames.ora file, and verified connectivity with both sqlplus and tnsping.

However, trying to connect to one of the Oracle databases from within SSRS I got:

System.Data.OracleClient requires Oracle client software version 8.1.7 or greater

…helpfully highlighted in an angry red.

Fix is fairly straightforward - SSRS hadn’t picked up the change to %PATH% done by the Oracle install yet, so the fix was to re-start the SSRS service.

This page is a bit of documentation for how I do stuff on the multifarious websites that I’ve perpetrated over the years. These are very much notes for myself but if anybody else should happen upon this page I hope you find it useful :)

On This Day - how it works

These are a couple of vim things I use regularly.

My local history website has a list of stuff that happened on this day' in Salisbury’s history

I maintain the list by adding a page for each event into a Wordpress page structure that looks like this:

• on-this-day
  • month
  • event page, title of which is prefixed by the date

I fill in the event pages' ‘Order’ field (under ‘Page Attributes’) with the day of the month. The months' ‘Order’ field has the ‘number of the month’ (i.e. 1 is January, 2 is February etc). This keeps everything in order.

Then the top level page has this:

    <ul class="sitemap-pages">
    
    

I think I’m going to change this to have a separate wp_list_pages for each month. This will allow me to have a table of contents, although it will slow it down, I guess.

On This Day - reformatting from dynamic list of Wordpress child pages to static list for the weekly post

Anyhoo, I use the top level list to create a weekly post, summarizing that weeks events in Salisbury history.

There are a couple of vim things that help me do that.

When I grab the page source from the top level list, each page looks like this:

<li class="page_item page-item-7354"><a href="/on-this-day/april/28th-april-1948-terry-pratchetts-birthday">28th April 1948 - Terry Pratchett's birthday</a>

which looks like this on the page:

map q /[0-9][0-9][0-9][0-9] dw$A (pi).

/ May
cw: 

Problem came up trying to connect to a remote Oracle source:

Started:  12:03:03
Error: 2013-10-09 12:03:03.84
   Code: 0xC0202009
   Source: 
   Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x800703E6.
An OLE DB record is available.  Source: "Microsoft OLE DB Service Components"  Hresult: 0x800703E6  Description: "Invalid access to memory location.".
End Error
Error: 2013-10-09 12:03:03.84
   Code: 0xC020801C
   Source: Copy Data
   Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "xxx" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.
End Error
Error: 2013-10-09 12:03:03.84
   Code: 0xC0047017
   Source: xxxx
   Description: component "xxx" (1) failed validation and returned error code 0xC020801C.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).

Fix was to go into job step properties, select the ‘Execution options’ tab and click on ‘Use 32-bit runtime’.

I’m wondering whether we had an Oracle 32-bit install, or a 32-bit install that wasn’t entirely removed

Clustering Conundrum - Allan Hirt

Running some HA classes in Europe.

Loose definition - networked servers.

Used in different contexts in sqlserver - clusterered indexes, SETI type stuff, windows clusters, NLB, WSFC

• Windows - network load balance, windows server failover cluster(wsfc)
• Sqlserver - failover cluster instance

Can’t have the latter without the former

Sqlserver built on WSFC

75% of problems tend not to be SS

Resource group - like a folder of resources

Resources are often interdependent

Quorum - what you need to have to be up or down

Has become much easier from 2008 onwards.

Availability Groups - are sql-level

www.sqlha.com/2012/01/0… www.sqlha.com/2013/04/2…

Need to get involved with the Windows very heavily - can’t just do the sql bit. <— big takeaway

DBAs tend to become expert in Windows clustering

Can’t convert a standalone instance to cluster. Has to be new install.

Can do a 2-node cluster at Standard Edition.

2008 needs to be Windows Enterprise. 2012 onwards can be standard or datacenter editions

You get a ‘tranport error’ during failover, but SSMS, at least, automatically failsover.

Got to have AD

Only one default instance per WSFC, but can have multiple instances on one WSFC

Keys to success Learn WSFC Validation can show up problems Need to have test clustered instances Need to be an WSFC expert

www.sql ha.com MS Cluster Team blog KBs: find the slide

Hands-on fundamentals for good SQL programming - Kevin Kline

Test harness needs: dbcc freeproccache dbcc dropcleanbuffers set statistics time set statistics io set showplan text|xml

sys.dm_os_performance counters sys.dm_os_wait_stats

sqlskills.com - Glenn Berry

Scan count - number of scans! Reads - in 8K pages Read-ahead - it dynamically goes multi-page read (i.e. multiblock reads)

set showplan_text on - does the command line style explain plan

clustered index scan is equivalent of an Oracle FTS, because, by default tables are held in clustered indexes

Can set statistics time and i/o from within the toolbar

Worth enabling:

• estimated execution plan and

• actual execution plan

• it shows up differences, which can show problems with statisitcs etc

Sys.dm_os_wait_stats

• shows bottlenecks

Sys.dm_exec_query_stats

• query performance stats

sys.dm_db_index_usage_stats

sys.dm_io_virtual_file_stats

Queries from SQLAgent man ?

Spools are red flags.

As are, scans, parallelism

Kimberly Tripps articles on ‘indexes from every angle’

Window Function Fundamentals - Christina Leo

Stuff like:

SELECT empid, ordermonth, qty, SUM(qty) OVER(PARTITION BY empid ORDER BY ordermonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS runqty FROM Sales.EmpOrders;

Indexing segment and sequence operators in Explain Plan

Index should be POC

• Partition
• Ordering
• Columns

Even if this doesn’t improve the elapsed time of the query it will eat up less memory.

The where clause happens before the select, so the filter happens before the Window function kicks in

christinaleo.net

CTE - the view thing at the top of the query with the WITH

Powerpivot - Susan O'Connell

Free add-in in 2010, native in 2013

Very worthwhile to leeave stuff out of the data model, both for performance and so as to simplify from a user perspective.

There are options to hide stuff from the users anyway.

Needs 8G of RAM on client

Data Internals - Bradley Balls

@sqlballs

Why learn internals - it conveys the idea that you know what you’re talking about!

Byte swapping - swaps hex pairs to protect proprietry data

dbcc ind dbcc page dbcc traceon(3604) -output goes to smss window

Record structure

• Tag bytes
• null bitmap
• fixed length
• null bitmap
• variable offset attay
• variable length columns

forwarding records - only in heaps like chains in Oracle

• only occurs in heaps

Text records

• LOBS > 8000 bytes (or sometimes 4000 bytes)
• 24 byte pointer to LOB

Ghost record

• deleted record left in place for undo optimization
• when in snapshot isolation mode

Versioned Records

• when Version Store os used. e.g. got online index rebuilds

MCM Video Series - especially

Record structure

• Tag bytes
• null bitmap
• fixed length
• null bitmap
• variable offset attay
• variable length columns

In the bitmap, 1 is null, 0 is not null

Record structure

• Tag bytes
• null bitmap
• fixed length
• null bitmap
• variable offset attay
• variable length columns

Pages

Data page header 96 bytes data 8096 bytes Row offest array (aka slot array) at the bottom

Index page leaf non-leaf

Text text tree lob text mox slob

sort

boot page 1:9 in any database dbcc page (‘db name’ , 1, 9, 3) - shows lots of boot info about database <— worth doing

Extents

• grouping of 8 pages

Allocation bitmaps

• free pages and allocated pages

Structure is: page is allocated is mixed allocation page is iam page page has ghosted rows space indicator - 3 bits show whether over 10% full, 30% full etc

IAM page

Storage and Virtualization - Denny Cherry

With Fusion IO, disk i/o is no longer the bottleneck

OLTP systems don’t much use the SAN cache because they don’t tend to use the same data twice. Data warehouses get more value because of pre-fetching.

Implies San cache is more about pre-cacheing than re-use

Force flushing effectively disables the cache until it’s finished. Don’t want to do it Low-water mark should be around 20% - you don’t want to be flushing immediately High-water mark 90% - because force flushes are bad

Power failure - most arrays have internal batteries.

Planned outage - MUST shut down sql server before the cache flushes.

Disk alignment matters

Big Data Visualization for Business Intelligence Professionals - @JenStirrup

Powerview - anti-spiderman. great power, but no responsibility for the data

Powermap -3d excel plugin. Formerly known as geo flow

Hortonworks sandbox- free download with hadoop stuff

Database Design - @DataAudrey

Availabilty Groups - Joe Dantoni

ex-Entergy chap!

Valid DR plan - we can do without system X, but not system Y.

Dilbert Our Disaster Recovery Plan Goes Something Like This <— google this

HA DR

Clustering works well from windows/sql 2008 on, but it’s not a DR solution.

Most cloud providers don’t charge for inbound data.

RPO - recovery point objective. How much data can we afford to lose? RTO - recovery time objective. How long can we do without a system?

Everything should be written down, and executive signed off.

Free Azure capacity with MSDN ? are we DR-ing Reporting Services?

Software as a service - gmail, salesforce.com

Infrastructure as a service - Azure, Amazon

platform as a service - Azure SQL, Amazon Relational Database Service

Sql azure now knwon as Windows Azure Sql Database (WASD).

Security in the Cloud

• are your security standards as good as Microsoft or Amazons

MS compliant with SOX and European regulations

Always On Availability Groups

• doesn’t need shared storage
• can go across IP subnets
• can therfore be a DR solution

Is based on log shipping(?)

Availability Groups is standard only, but there are ways of doing log-shipping. No failover capacity in standard.

Dont quite get why you nedd wsfc

Scalability - Glenn Berry

Common issues:

• overuse of query generators
• ad-hoc sql from the application
• lots of xml columns
• user-defined functions
• too much business logic in the database
• overuse of DML triggers
• overuse of CLR assemblies

Glenn has sql server diagnostic information queries <—-worth downloading

Intel tick-tock release strategy

Servers are like pets - scale up Servers are like cattle - commodity

Trying to change the sql server agent to run as a non-admin user, I couldn’t get Sql Server Agent to work without giving it administrator privileges. I got:

WMI Provider Error
Access is denied. [0x80070005]
OK

You have to grant two privileges - both the Agent User and the MSSQL user.

• SQLServerSQLAgentUser$bigserver$inst01
• SQLServerMSSQLUser$bigserver$inst01

Trying to change the sql server agent to run as a non-admin user, I couldn’t get Sql Server Agent to work without giving it administrator privileges. I got:

---------------------------
WMI Provider Error
---------------------------
Access is denied. [0x80070005]
---------------------------
OK
---------------------------

You have to grant two privileges - both the Agent User and the MSSQL user.

• SQLServerSQLAgentUser$bigserver$inst01
• SQLServerMSSQLUser$bigserver$inst01

I set up a Sqlserver Maintenance Cleanup Task, but couldn’t get it to delete the appropriate backup files.

The log looked like this:

Maintenance Cleanup Task ()
Maintenance Cleanup on Local server connection
Cleanup Database Backup files
Age: Older than 5 Weeks
Task start: 2011-11-09T11:45:01.
Task end: 2011-11-09T11:45:01.
Success
Command:EXECUTE master.dbo.xp_delete_file 0,N''F:mssql_backup'',N''.bak'',N''2011-10-05T11:45:01'',1
GO

…which looks reasonable.

It was showing ‘Success’

My mistake was that I had entered .bak rather than just bak as the ‘File extension’. The ‘.’ is not needed. Presumably it was looking for files called ‘whatever..bak’

I spent a couple of lunchtimes going through Sqlserver Management Studio’s ‘Object Explorer’ panel, typing up all the different elements. This might seem like an odd thing to do, but I thought it might be useful for the following reasons:

• as a reference. A searchable list of where everything is
• as the beginnings of a checklist. Making a system wide change it might be handy to scan through this list and consider whether there’s an impact on each element
• to get me blogging again :)

Databases
  System Databases
  Database Snapshots
  User Databases
    Database Diagrams
    Tables
    Views
    Synonyms
    Programmability
      Stored Procedures
      Functions
      Triggers
      Assemblies
      Types
      Rules
      Defaults
      Plan Guides
    Service Broker
      Message Types
      Contracts
      Queues
      Services
      Routes
      Remote ServiceBindings
      Broker Priorities
    Storage
      Full Text Catalogs
      Partition Schenes
      Patition Functions
      Full Text Stoplists
    Security
      Users
      Roles
      Schemas
      Asymmetric Keys
      Certificates
      Symmetric Keys
      Database Audit Specifications
Security
  Logins
  Server Roles
  Credentials
  Cryptographic Providers
  Audits
  Server Audit Specifications
Server Objects
  Backup Devices
  Endpoints
    System Endpoints
      Database Mirroring
      Service Broker
      SOAP
      TSQL
        Dedicated Admin Connection
        TSQL Default TCP
        TSQL Default VIA
        TSQL Local Machine
        TSQL Named Pipes
    Database Mirroring
    Service Broker
    SOAP
    TSQL
  Linked Servers
    Providers
  Triggers
Replication
  Local publications
  Local subscriptions
Management
  Data-tier Applications
  Policy Management
    Policies
      System Policies
    Conditions
      System Conditions
    Facets
  Data Collection
  Resource Governor
    Resource Pools
      System Resource Pools
  Maintenance Plans
SQL Server Logs
  Database Mail
  Distributed Trnasaction Co-ordinator
Legacy
  Database Maintenance Plans
  Data Transformation Services
  SQL Mail
SQL Server Agent
  Jobs
  Alerts
  Operators
  Proxies
  Error Logs

I had to run nid (also known as dbnewid) on a database as part of a test refresh at the weekend. This changes the dbid of the database.

Out of sheer laziness I ran it from with sqlplus by using the ‘!’ host command feature.

Nid, quite understandablly doesn’t really like this - it hangs.

I ctrl-c’d it, exited to the OS and re-ran and it worked fine.

The 10g doc for nid is here: http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/dbnewid.htm

I’ve never really used the analytic functions in anger, so consequently I don’t really have the skills at my fingertips. By way of re-learning them (using Expert One-on-One Oracle | Thomas Kyte | Apress) I’ve been playing about with a list of English Premiership scorers.

The table is:

SQL> desc goals
Name                                      Null?    Type
----------------------------------------- -------- ------------

DIVISION                                  NOT NULL VARCHAR2(20)
END_DATE                                  NOT NULL DATE
PLAYER                                    NOT NULL VARCHAR2(40)
TEAM                                               VARCHAR2(20)
GOALS                                              NUMBER 

The third highest scorer for each team, should you want to know that bit of information, is given by:

select * 
from (select team, player, goals, 
row_number() over (partition by team order by goals desc) ranking
from goals)
where ranking = 3

Output is:

TEAM             PLAYER                GOALS RANKING
---------------- --------------------- ----- -------
Arsenal          Robin van Persie          7       3
Aston Villa      Ashley Young              5       3
Birmingham       Sebastian Larsson         4       3
Blackburn        Morten Gamst Pedersen     3       3
Bolton           Kevin Davies              5       3
Burnley          David Nugent              6       3
Chelsea          Florent Malouda          11       3
Everton          Mikel Arteta              5       3
Fulham           Damien Duff               6       3
Hull City        Deiberson Geovanni        3       3
Liverpool        Steven Gerrard            7       3
Man Utd          Antonio Valencia          5       3
Manchester City  Craig Bellamy             9       3
Portsmouth       Nadir Belhadj             3       3
Stoke City       Ricardo Fuller            3       3
Sunderland       Fraizer Campbell          4       3
Tottenham        Niko Kranjcar             6       3
West Ham Utd     Guillermo Franco          4       3
Wigan Athletic   Paul Scharner             4       3
Wolves           Matthew Jarvis            3       3

To further develop this query to return a pivot table, you use a decode on the artificial ‘ranking’ field, as follows:

select team,
max(decode(ranking, 1, player, null)) golden_boot,
max(decode(ranking, 2, player, null)) silver_boot,
max(decode(ranking, 3, player, null)) bronze_boot
from
(select team, player, goals, row_number()
over (partition by team order by goals desc) ranking
from goals)
where ranking <= 3 group by team 

This returns:

SQL> /

TEAM             GOLDEN_BOOT         SILVER_BOOT         BRONZE_BOOT
---------------- ------------------- ------------------- -------------------
Arsenal          Francesc Fabregas   Andrey Arshavin     Robin van Persie
Aston Villa      Gabriel Agbonlahor  John Carew          Ashley Young
Birmingham       Cameron Jerome      Lee Bowyer          Sebastian Larsson
Blackburn        David Dunn          Jason Roberts       Morten Gamst Peders
Bolton           Matthew Taylor      Ivan Klasnic        Kevin Davies
Burnley          Steven Fletcher     Graham Alexander    David Nugent
Chelsea          Didier Drogba       Frank Lampard       Florent Malouda
Everton          Louis Saha          Tim Cahill          Mikel Arteta
Fulham           Bobby Zamora        Clinton Dempsey     Damien Duff
Hull City        Stephen Hunt        Jimmy Bullard       Deiberson Geovanni
Liverpool        Fernando Torres     Dirk Kuyt           Steven Gerrard
Man Utd          Wayne Rooney        Dimitar Berbatov    Antonio Valencia
Manchester City  Carlos Tevez        Emmanuel Adebayor   Craig Bellamy
Portsmouth       Aruna Dindane       Frederic Piquionne  Nadir Belhadj
Stoke City       Matthew Etherington Tuncay Sanli        Ricardo Fuller
Sunderland       Darren Bent         Kenwyne Jones       Fraizer Campbell
Tottenham        Jermain Defoe       Peter Crouch        Niko Kranjcar
West Ham Utd     Carlton Cole        Alessandro Diamanti Guillermo Franco
Wigan Athletic   Hugo Rodallega      Charles N'Zogbia    Paul Scharner
Wolves           Kevin Doyle         Jody Craddock       Matthew Jarvis

20 rows selected.  

Another post that’s unlikely to be all that useful….but you never know.

As per a couple of my previous posts, I’m re-doing my Oracle certification from the ground up instead of doing the 10G new features only.

I’m using Oracle Database 10g OCP Certification by Damir Bersinic and John Watson, which from the bits I’ve read so far, I would recommend.

Anyhow, the book says:

If you are using UNDO_MANAGEMENT=AUTO, you must also specify UNDO_TABLESPACE.

I was curious to see how this was enforced, in particular whether you would get an error if you tried to ALTER SYSTEM to nullify the undo tablespace parameter. You don’t - you get an error when you try to use the undo, as follows:

SQL> @sparam
Enter value for parameter_name: UNDO
old   5: where name like lower('%&parameter_name%')
new   5: where name like lower('%UNDO%')
undo_management                AUTO                                          F
undo_retention                 900                                           T
undo_tablespace                UNDOTBS1                                      F

SQL> alter system set undo_tablespace='' scope =both;

System altered.

SQL> delete from scott.emp;
delete from scott.emp
                  *
ERROR at line 1:
ORA-01552: cannot use system rollback segment for non-system tablespace 'USERS'

A quick and dirty post for a quick and dirty technique that I used to get the Oracle CPU Database Risk Matrix into a spreadsheet. This won’t always work (the html table I was interested in happened to have the data in the same line of html as the tag), but it’s a start.

• Do ‘View Source’ to get the html for the table.
• Find the table
• Cut and paste the table into a text file. I called mine “risk_matrix_raw.txt”
• Create an awk file (mine was called risk_matrix.awk) with the following lines:

/<tr>/{printf("\n")}
/<td/{printf("%s~", $2)}

• awk the text file, then pipe into sed to remove some of the tags

awk95 -F">" -f risk_matrix.awk risk_matrix_raw.txt |^ sed -e "s/<\/tr//g" -e "s/<\/td//g" \> risk_matrix.txt

• Open the .txt file in Excel. When I did this, Excel asks whether its a fixed width or a delimited file. I specified that the file was delimited with a ‘~’ and Excel opened it.

Note (April 2020): for some reason in the original version of this post I had the code in .jpg files. Mea culpa. I’ve typed the code in, but not been able to test as yet, so, just for reference here are the original .jpgs

A quick and dirty post for a quick and dirty technique that I used to get the Oracle CPU Database Risk Matrix into a spreadsheet. This won’t always work (the html table I was interested in happened to have the data in the same line of html as the tag), but it’s a start.

• Do ‘View Source’ to get the html for the table.
• Find the table
• Cut and paste the table into a text file. I called mine “risk_matrix_raw.txt”
• Create an awk file (mine was called risk_matrix.awk) with the following lines:

/<tr>/{printf("\n")}
/<td/{printf("%s~", $2)}

• awk the text file, then pipe into sed to remove some of the tags

awk95 -F">" -f risk_matrix.awk risk_matrix_raw.txt |^ sed -e "s/<\/tr//g" -e "s/<\/td//g" \> risk_matrix.txt

• Open the .txt file in Excel. When I did this, Excel asks whether its a fixed width or a delimited file. I specified that the file was delimited with a ‘~’ and Excel opened it.

Note (April 2020): for some reason in the original version of this post I had the code in .jpg files. Mea culpa. I’ve typed the code in, but not been able to test as yet, so, just for reference here are the original .jpgs

Got the following error:

C:\oracle\ora92\OPatch>perl opatch.pl version
Perl lib version (v5.8.3) doesn't match executable version (v5.6.1) at x:/oracle/10.2.0/perl/5.8.3/lib/MSWin32-x86-multi-thread/Config.pm line 32.
Compilation failed in require at x:/oracle/10.2.0/perl/5.8.3/lib/MSWin32-x86-multi-thread/lib.pm line 6.
BEGIN failed--compilation aborted at x:/oracle/10.2.0/perl/5.8.3/lib/MSWin32-x86-multi-thread/lib.pm line 6.
Compilation failed in require at opatch.pl line 88.
BEGIN failed--compilation aborted at opatch.pl line 88.

Kind of obvious, but the fix here is to set your PERL5LIB variable correctly…

C:\oracle\ora92\OPatch>set PERL5LIB=C:\perl\lib
C:\oracle\ora92\OPatch>perl opatch.pl version

Oracle Interim Patch Installer version 1.0.0.0.57
Copyright (c) 2007 Oracle Corporation. All Rights Reserved..

We recommend you refer to the OPatch documentation under
OPatch/docs for usage reference. We also recommend using
the latest OPatch version. For the latest OPatch version
and other support related issues, please refer to document
293369.1 which is viewable from metalink.oracle.com

OPatch Version: 1.0.0.0.57 

Started getting this error from a batch job which was using oradim to do a shutdown.

instType unknown: db not started; use RDBMS as default

I think this has just been occurring since we put a 10G install on the same server as the existing 9i and 8i databases.

Anyhow it was fixed by explicitly setting ORACLE_HOME and prefixing oradim with %ORACLE_HOME%\bin

Started getting this error from a batch job which was using oradim to do a shutdown.

instType unknown: db not started; use RDBMS as default

I think this has just been occurring since we put a 10G install on the same server as the existing 9i and 8i databases.

Anyhow it was fixed by explicitly setting ORACLE_HOME and prefixing oradim with %ORACLE_HOME%\bin

The question of how to determine which CPUs have been applied to a database came up at a couple of presentations at the UKOUG, including mine.

A quick and dirty way (which I didn’t think of at the time) would be to look in the CPU directory:

c:> dir t:\oracle\ora92\cpu

11/06/2007  18:12     .
11/06/2007  18:12     ..
02/05/2006  17:30     CPUApr2006
30/04/2007  19:21     CPUApr2007
28/03/2006  18:51     CPUJan2006
......

Then to check which databases the catcpu script was run against, you can look in the directories themselves - the spoolfiles have the name of the database in the title:

c:> dir t:\oracle\ora92\cpu\CPUApr2007

30/04/2007  19:21            .
30/04/2007  19:21            ..
30/04/2007  18:19    4,918   APPLY_SID1_30Apr2007_19_19_49.log
30/04/2007  18:26    4,918   APPLY_SID2_30Apr2007_19_20_11.log