Materialised Views – Mlog Build up Issues

I had an issue whereby the Materialised View Log (MLog) was building up for a table even thought the snapshot sites where receiving an up to date dataset. I managed to find the issue by writing a couple of scripts to diagnose the problem and find out what was holding up the log from purging.

Materialised view logs build up when snapshot sites are requesting FAST refreshes from the source. Until all the registered destinations for a particular Mview have received the log, the purge of the log will not happen. One of the ways around this is to change the refresh mode from FAST to COMPLETE if for example one site is requesting a mview refresh less than the other sites. Important to consider the size of the table being refreshed.

–Find oldest snaptime for a mlog – i.e. time the mlog has been building up since
select * from sys.slog$
where mowner = ‘SCHEMA’
and Master = ‘MVIEW_NAME’
ORDER BY SNAPTIME DESC;

–row growth every time the mlog updates
select count(*)
from ATD_UK_FP.MLOG$_TABLE_NAME
group by SNAPTIME$$;

— SNapshot sites for a particular mview
select owner, name ,snapshot_site, snapshot_id, refresh_method from dba_registered_snapshots rs
WHERE rs.OWNER = ‘SCHEMA’
and rs.Name = ‘MVIEW_NAME’
order by rs.owner, rs.name;

–Find which snapshot_site is holding up the mlog
select sl.*, rs.snapshot_site, rs.owner, rs.name, rs.refresh_method from sys.slog$ sl
left join dba_registered_snapshots rs on rs.SNAPSHOT_ID = sl.SNAPID
where mowner = ‘SCHEMA’
and Master = ‘MVIEW_NAME’
ORDER BY SNAPTIME DESC;

–Find unregistered snaps and snaps older than certain number of days not being refreshed
select mowner, master, snapid,  nvl(r.snapsite, ‘not registered’) snapsite,snaptime
from   sys.slog$ s, sys.reg_snap$ r where  s.snapid=r.snapshot_id(+) and s.snaptime < (sysdate0)
and mowner = ‘SCHEMA’;

–Change the refresh mode for a Mview
ALTER MATERIALIZED VIEW SCHEMA.MVIEW_NAME
 REFRESH COMPLETE
 WITH PRIMARY KEY;

Useful Oracle Metalink Docs

236233.1           Materialized View Refresh : Log Population and Purge
727632.1           What to do When Materialized View Log is not cleared automatically after a Fast Refresh
258634.1           Materialized View registration at Master Site

Advertisements

SQL Server 2005 Emergency Diagnostics and Performance Queries

All Credit to Glenn Berry

Below is a set of troubleshooting and diagnostic queries that will help you quickly identify some common performance and other issues you may run into with a SQL Server 2005 OLTP workload. Many of the DMV queries require VIEW SERVER STATE permission on the server.

-- SQL Server 2005 Emergency Diagnostic and Performance Queries
-- Glenn Berry 3-17-2008
      

-- Step 1 - Check Task Manager. Are all CPUs above 90-95% for an extended period of time?
-- If yes, run HIGH CPU queries below:

-- Step 2 - Check Performance Monitor
-- SQL Server Buffer Manager: Buffer Cache Hit Ratio and Page Life Expectancy
-- SQL Server Memory Manager: Memory Grants Pending and Memory Grants Pending
-- Physical Disk: Avg disk sec/Read and Avg disk sec/Write

-- Step 3 - Check for locking, blocking and missing indexes
-- Run the BLOCKING queries below: 

-- Step 4 - Is the transaction log full?
-- Run the TRANSACTION LOG FULL queries below:

-- Step 5 - Check for IO Problems
-- Run the IO ISSUES queries below



    -- HIGH CPU *******
    -- Get the most CPU intensive queries
    SET NOCOUNT ON;

    DECLARE @SpID smallint
    DECLARE spID_Cursor CURSOR
    FAST_FORWARD FOR
    
    SELECT TOP 25 spid
    FROM master..sysprocesses
    WHERE status = 'runnable'
    AND spid > 50   -- Eliminate system SPIDs
    AND spid <> @@SPID
    ORDER BY CPU DESC

    OPEN spID_Cursor

    FETCH NEXT FROM spID_Cursor
    INTO @spID
        WHILE @@FETCH_STATUS = 0
        BEGIN
            PRINT 'Spid #:' + STR(@spID)
            EXEC ('DBCC INPUTBUFFER (' + @spID + ')')

               FETCH NEXT FROM spID_Cursor
            INTO @spID
        END

    -- Close and deallocate the cursor
    CLOSE spID_Cursor
    DEALLOCATE spID_Cursor


      -- HIGH CPU *******
      -- Isolate top waits for server instance
      WITH Waits AS
      (
        SELECT
            wait_type,
            wait_time_ms / 1000. AS wait_time_s,
            100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
            ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
        FROM sys.dm_os_wait_stats
        WHERE wait_type NOT LIKE '%SLEEP%'
      )
      SELECT
        W1.wait_type, 
        CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
        CAST(W1.pct AS DECIMAL(12, 2)) AS pct,
        CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
      FROM Waits AS W1
      INNER JOIN Waits AS W2
      ON W2.rn <= W1.rn
      GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
      HAVING SUM(W2.pct) - W1.pct < 90 -- percentage threshold
      ORDER BY W1.rn;

      -- HIGH CPU *******
      -- Total waits are wait_time_ms (high signal waits indicates CPU pressure)
      SELECT '%signal (cpu) waits' = CAST(100.0 * SUM(signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2)),
           '%resource waits'= CAST(100.0 * SUM(wait_time_ms - signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2))
      FROM sys.dm_os_wait_stats;

      -- HIGH CPU *******
      -- Check SQL Server Schedulers to see if they are waiting on CPU (runnable tasks above 10 is very bad)
      SELECT scheduler_id, current_tasks_count, runnable_tasks_count
      FROM sys.dm_os_schedulers
      WHERE scheduler_id < 255

      -- HIGH CPU *******
      --  Who is running what at this instant 
      SELECT st.text AS [Command text], login_time, [host_name], 
      [program_name], sys.dm_exec_requests.session_id, client_net_address,
      sys.dm_exec_requests.status, command, db_name(database_id) AS DatabaseName
      FROM sys.dm_exec_requests 
      INNER JOIN sys.dm_exec_connections 
      ON sys.dm_exec_requests.session_id = sys.dm_exec_connections.session_id
      INNER JOIN sys.dm_exec_sessions 
      ON sys.dm_exec_sessions.session_id = sys.dm_exec_requests.session_id
      CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
      WHERE sys.dm_exec_requests.session_id >= 51
      AND sys.dm_exec_requests.session_id <> @@spid
      ORDER BY sys.dm_exec_requests.status

      -- HIGH CPU *******
      -- Get a snapshot of current activity
      SELECT LTRIM (st.text) AS 'Command Text',[host_name], der.session_id AS 'SPID',
      der.status, db_name(database_id) AS DatabaseName, ISNULL(der.wait_type, 'None')AS 'Wait Type', der.logical_reads 
      FROM sys.dm_exec_requests AS der
      INNER JOIN sys.dm_exec_connections AS dexc
      ON der.session_id = dexc.session_id
      INNER JOIN sys.dm_exec_sessions AS dexs
      ON dexs.session_id = der.session_id
      CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
      WHERE der.session_id >= 51
      AND der.session_id <> @@spid
      ORDER BY der.status

      -- BLOCKING ************
      -- Detect blocking
      SELECT blocked_query.session_id AS blocked_session_id,
      blocking_query.session_id AS blocking_session_id,
      sql_text.text AS blocked_text, sql_btext.text AS blocking_text, waits.wait_type AS blocking_resource
      FROM sys.dm_exec_requests AS blocked_query
      INNER JOIN sys.dm_exec_requests AS blocking_query 
      ON blocked_query.blocking_session_id = blocking_query.session_id
      CROSS APPLY
      (SELECT * FROM sys.dm_exec_sql_text(blocking_query.sql_handle)
      ) sql_btext
      CROSS APPLY
      (SELECT * FROM sys.dm_exec_sql_text(blocked_query.sql_handle)
      ) sql_text
      INNER JOIN sys.dm_os_waiting_tasks AS waits 
      ON waits.session_id = blocking_query.session_id

      -- BLOCKING ************
      -- Index Contention
      SELECT dbid=database_id, objectname=object_name(s.object_id),
      indexname=i.name, i.index_id, row_lock_count, row_lock_wait_count,
      [block %]= CAST (100.0 * row_lock_wait_count / (1 + row_lock_count) AS NUMERIC(15,2)),
      row_lock_wait_in_ms,
      [avg row lock waits in ms]= CAST (1.0 * row_lock_wait_in_ms / (1 + row_lock_wait_count) AS NUMERIC(15,2))
      FROM sys.dm_db_index_operational_stats (db_id(), NULL, NULL, NULL) AS s
      INNER JOIN sys.indexes AS i
      ON i.object_id = s.object_id
      WHERE objectproperty(s.object_id,'IsUserTable') = 1
      AND i.index_id = s.index_id
      ORDER BY row_lock_wait_count DESC

      -- TRANSACTION LOG FULL *****
      -- Find the log reuse description for the transaction log
      SELECT name, database_id, log_reuse_wait_desc 
      FROM sys.databases

      -- TRANSACTION LOG FULL *****
      -- Individual File Size query
      SELECT name AS 'File Name' , physical_name AS 'Physical Name', size/128 AS 'Total Size in MB',
      size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Available Space In MB'--, *
      FROM sys.database_files;

      -- IO ISSUES *****************
      -- Analyze DB IO, ranked by IO Stall %
      WITH DBIO AS
      (
        SELECT
            DB_NAME(IVFS.database_id) AS db,
            CASE WHEN MF.type = 1 THEN 'log' ELSE 'data' END AS file_type,
            SUM(IVFS.num_of_bytes_read + IVFS.num_of_bytes_written) AS io,
            SUM(IVFS.io_stall) AS io_stall
        FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS IVFS
            JOIN sys.master_files AS MF
              ON IVFS.database_id = MF.database_id
              AND IVFS.file_id = MF.file_id
        GROUP BY DB_NAME(IVFS.database_id), MF.type
      )
      SELECT db, file_type, 
        CAST(1. * io / (1024 * 1024) AS DECIMAL(12, 2)) AS io_mb,
        CAST(io_stall / 1000. AS DECIMAL(12, 2)) AS io_stall_s,
        CAST(100. * io_stall / SUM(io_stall) OVER()
               AS DECIMAL(10, 2)) AS io_stall_pct,
        ROW_NUMBER() OVER(ORDER BY io_stall DESC) AS rn
      FROM DBIO
      ORDER BY io_stall DESC;

      -- HIGH CPU ************
      -- Get Top 100 executed SP's ordered by execution count
      SELECT TOP 100 qt.text AS 'SP Name', qs.execution_count AS 'Execution Count',  
      qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS 'Calls/Second',
      qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',
      qs.total_worker_time AS 'TotalWorkerTime',
      qs.total_elapsed_time/qs.execution_count AS 'AvgElapsedTime',
      qs.max_logical_reads, qs.max_logical_writes, qs.total_physical_reads, 
      DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache'
      FROM sys.dm_exec_query_stats AS qs
      CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
      WHERE qt.dbid = db_id() -- Filter by current database
      ORDER BY qs.execution_count DESC

      -- HIGH CPU *************
      -- Get Top 20 executed SP's ordered by total worker time (CPU pressure)
      SELECT TOP 20 qt.text AS 'SP Name', qs.total_worker_time AS 'TotalWorkerTime', 
      qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',
      qs.execution_count AS 'Execution Count', 
      ISNULL(qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()), 0) AS 'Calls/Second',
      ISNULL(qs.total_elapsed_time/qs.execution_count, 0) AS 'AvgElapsedTime', 
      qs.max_logical_reads, qs.max_logical_writes, 
      DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache'
      FROM sys.dm_exec_query_stats AS qs
      CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
      WHERE qt.dbid = db_id() -- Filter by current database
      ORDER BY qs.total_worker_time DESC

SQL Server 2005 Diagnostics Queries

I was reading Glenn Berry’s Blog and found a really useful set of queries he has developed to give a high level overview of how their SQL Server system is configured and how it is performing.

The initial queries are instance or server specific, while the later ones are database specific (which means you have to switch to the database you are concerned with).  Most of the DMV queries require VIEW SERVER STATE permission on the database server.

These queries will work on SQL Server 2005 or 2008, but not on SQL Server 2000.

-- SQL Server 2005 Diagnostic Information Queries
-- Glenn Berry April 2009
-- http://glennberrysqlperformance.spaces.live.com/


-- SQL Version information for current instance
SELECT @@VERSION AS 'Version Info';
 
-- Hardware Information
SELECT cpu_count AS 'Logical CPU Count', hyperthread_ratio AS 'Hyperthread Ratio',
cpu_count/hyperthread_ratio As 'Physical CPU Count', 
physical_memory_in_bytes/1048576 AS 'Physical Memory (MB)'
FROM sys.dm_os_sys_info;

-- get sp_configure values for instance
EXEC sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
EXEC sp_configure


-- File Names and Paths for all databases in instance 
SELECT dbid, fileid, filename 
FROM sys.sysaltfiles;

-- Recovery model for all databases on instance
SELECT [name], recovery_model_desc, log_reuse_wait_desc, [compatibility_level] 
FROM sys.databases;

-- Clear Wait Stats
-- DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);

-- Isolate top waits for server instance since last restart or statistics clear
WITH Waits AS
(SELECT wait_type, wait_time_ms / 1000. AS wait_time_s,
    100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
    ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
 FROM sys.dm_os_wait_stats
 WHERE wait_type NOT IN( 'SLEEP_TASK', 'BROKER_TASK_STOP', 
  'SQLTRACE_BUFFER_FLUSH', 'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT',
  'LAZYWRITER_SLEEP')) -- filter out additional irrelevant waits
SELECT W1.wait_type, 
  CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
  CAST(W1.pct AS DECIMAL(12, 2)) AS pct,
  CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
FROM Waits AS W1
INNER JOIN Waits AS W2
ON W2.rn <= W1.rn
GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
HAVING SUM(W2.pct) - W1.pct < 95; -- percentage threshold


-- Signal Waits for instance
SELECT '%signal (cpu) waits' = CAST(100.0 * SUM(signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2)),
       '%resource waits'= CAST(100.0 * SUM(wait_time_ms - signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2))
FROM sys.dm_os_wait_stats;


-- Page Life Expectancy value
SELECT cntr_value AS 'Page Life Expectancy'
FROM sys.dm_os_performance_counters
WHERE object_name = 'SQLServer:Buffer Manager'
AND counter_name = 'Page life expectancy';


-- Buffer Pool Usage 
SELECT TOP (10) [type], sum(single_pages_kb) AS [SPA Mem, Kb] 
FROM sys.dm_os_memory_clerks 
GROUP BY type  
ORDER BY SUM(single_pages_kb) DESC;

-- Switch to user database
--USE YourDatabaseName;
--GO

-- Individual File Sizes and space available for current database
SELECT name AS 'File Name' , physical_name AS 'Physical Name', size/128 AS 'Total Size in MB',
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Available Space In MB' FROM sys.database_files;


-- Cached SP's By Execution Count
SELECT TOP (25) qt.text AS 'SP Name', qs.execution_count AS 'Execution Count',  
qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS 'Calls/Second',
qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',
qs.total_worker_time AS 'TotalWorkerTime',
qs.total_elapsed_time/qs.execution_count AS 'AvgElapsedTime',
qs.max_logical_reads, qs.max_logical_writes, qs.total_physical_reads, 
DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache'
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.dbid = db_id() -- Filter by current database
ORDER BY qs.execution_count DESC;


-- Cached SP's By Worker Time
SELECT TOP (25) qt.text AS 'SP Name', qs.total_worker_time AS 'TotalWorkerTime', 
qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',
qs.execution_count AS 'Execution Count', 
ISNULL(qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()), 0) AS 'Calls/Second',
ISNULL(qs.total_elapsed_time/qs.execution_count, 0) AS 'AvgElapsedTime', 
qs.max_logical_reads, qs.max_logical_writes, 
DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache'
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.dbid = db_id() -- Filter by current database
ORDER BY qs.total_worker_time DESC;


-- Cached SP's By Logical Reads
SELECT TOP (25) qt.text AS 'SP Name', total_logical_reads, 
qs.execution_count AS 'Execution Count', total_logical_reads/qs.execution_count AS 'AvgLogicalReads',
qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS 'Calls/Second', 
qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',
qs.total_worker_time AS 'TotalWorkerTime',
qs.total_elapsed_time/qs.execution_count AS 'AvgElapsedTime',
qs.total_logical_writes,
qs.max_logical_reads, qs.max_logical_writes, qs.total_physical_reads, 
DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache'
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.dbid = db_id() -- Filter by current database
ORDER BY total_logical_reads DESC;


-- Possible bad Indexes (writes > reads)
SELECT object_name(s.object_id) AS 'Table Name', i.name AS 'Index Name', i.index_id,
        user_updates AS 'Total Writes', user_seeks + user_scans + user_lookups AS 'Total Reads',
        user_updates - (user_seeks + user_scans + user_lookups) AS 'Difference'
FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON s.object_id = i.object_id
AND i.index_id = s.index_id
WHERE objectproperty(s.object_id,'IsUserTable') = 1
AND s.database_id = db_id()
AND user_updates > (user_seeks + user_scans + user_lookups)
AND i.index_id > 1
ORDER BY 'Difference' DESC, 'Total Writes' DESC, 'Total Reads' ASC;


-- Missing Indexes for entire instance by Index Advantage
SELECT user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS index_advantage, migs.last_user_seek, 
mid.statement AS [Database.Schema.Table],
mid.equality_columns, mid.inequality_columns, mid.included_columns,
migs.unique_compiles, migs.user_seeks, migs.avg_total_user_cost, migs.avg_user_impact
FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)
INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK)
ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)
ON mig.index_handle = mid.index_handle
ORDER BY index_advantage DESC;


-- Breaks down buffers used by current database by object (table, index) in the buffer cache
SELECT OBJECT_NAME(p.object_id) AS 'ObjectName', p.object_id, 
p.index_id, COUNT(*)/128 AS 'buffer size(MB)',  COUNT(*) AS 'buffer_count' 
FROM sys.allocation_units AS a
INNER JOIN sys.dm_os_buffer_descriptors AS b
ON a.allocation_unit_id = b.allocation_unit_id
INNER JOIN sys.partitions AS p
ON a.container_id = p.hobt_id
WHERE b.database_id = db_id()
GROUP BY p.object_id, p.index_id
ORDER BY buffer_count DESC;


-- Detect blocking 
SELECT t1.resource_type AS 'lock type',db_name(resource_database_id) AS 'database',
t1.resource_associated_entity_id AS 'blk object',t1.request_mode AS 'lock req',                                                                          --- lock requested
t1.request_session_id AS 'waiter sid', t2.wait_duration_ms AS 'wait time',             -- spid of waiter  
(SELECT [text] FROM sys.dm_exec_requests AS r                                           -- get sql for waiter
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) 
WHERE r.session_id = t1.request_session_id) AS 'waiter_batch',
(SELECT substring(qt.text,r.statement_start_offset/2, 
    (CASE WHEN r.statement_end_offset = -1 
    THEN LEN(CONVERT(nvarchar(max), qt.text)) * 2 
    ELSE r.statement_end_offset END - r.statement_start_offset)/2) 
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS qt
WHERE r.session_id = t1.request_session_id) AS 'waiter_stmt',    -- statement blocked
t2.blocking_session_id AS 'blocker sid',                         -- spid of blocker
(SELECT [text] FROM sys.sysprocesses AS p                        -- get sql for blocker
CROSS APPLY sys.dm_exec_sql_text(p.sql_handle) 
WHERE p.spid = t2.blocking_session_id) AS 'blocker_stmt'
FROM sys.dm_tran_locks AS t1 
INNER JOIN sys.dm_os_waiting_tasks AS t2
ON t1.lock_owner_address = t2.resource_address;

Cannot create an instance of OLE DB provider “OraOLEDB.Oracle” for linked server “LinkedServerName”.

Today an incident was raised with me whereby a user wasnt able to remotely query an oracle database (10g) via a SQL Server instance.

SELECT * FROM OPENQUERY(LinkedServerName, ‘select * from oracleschema.tablename’)

Interestingly, the query worked perfectly when running it directly on the Server which hosted the SQL Server Instance. The problem was when the query was run remotely on the client machine – which gave the error below:

Msg 7302, Level 16, State 1, Line 1
Cannot create an instance of OLE DB provider "OraOLEDB.Oracle" for linked server "LinkedServerName

I did quite a lot of research on this and the issue can be resolved by editing the Provider Properties (right-click the provider in the providers folder) and ticking the "Allow inprocess" option corrects my problem.
Existing linked servers will not pick up the provider setting, so new linked server objects will have to be created. But the new ones work.

http://www.sqlservercentral.com/Forums/Topic491682-149-1.aspx

image

image

Similar errors about linked servers on SQL Server linking to Oracle

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "OraOLEDB.Oracle" for linked server "LinkedServerName" reported an error. Access denied.
Msg 7332, Level 16, State 2, Line 1
Cannot rescan the result set from OLE DB provider "OraOLEDB.Oracle" for linked server "LinkedServerName". Unknown provider error.

http://www.sqlservercentral.com/Forums/Topic482481-146-1.aspx

Where to look for Errors

General Database activity:
cd $ORACLE_BASE/admin/DBNAME/bdump – contains trace files that relate any any oracle background process (pmon, smon etc) and also the database alert log
cd $ORACLE_BASE/admin/DBNAME/udump – containst trace files for user specific processes
cd $ORACLE_BASE/admin/DBNAME/cdump – contains core dumps, associated with one or more trace files in the bdump directory

Voting Disk Errors:
cd $ORA_CRS_HOME/css/log

Cluster Issues:
cd $ORA_CRS_HOME/crs/log