ORA 600s after applying PSU7 for 11.2.0.3

We were seeing the following two ORA 600s after applying PSU7. Technically the issue wasnt related to PSU 7 but more the switchover involved when patching…

SR 3-8646935731 : ORA 600 [ktbdchk1: bad dscn]
SR 3-8664869401 : ORA 600-[FILE:jsks.c LINE:2388 FUNCTION:jsksStartOCICall() ID:OCIKCallPus]

Caused by Bug 8895202 : ITL HAS HIGHER COMMIT SCN THAN BLOCK SCN

The solution was to set the following dynamic parameter on both the primary and standby database:

ALTER SYSTEM SET "_ktb_debug_flags"=8 SCOPE=BOTH;

This parameter is designed to heal blocks having invalid dependent scn’s on switchover operations.

From the traces provided to oracle, I was told that the affected object for this issue was ID 12152331.

Running the following SQL determines the object:

select owner,object_name,object_type,subobject_name,object_id,data_object_id
  from   dba_objects
  where  object_id in (12152331)
  or     data_object_id in (12152331);

The affected blocks were on index SYS.I_SCHEDULER_JOB_RUN_DETAILS on table SCHEDULER$_JOB_RUN_DETAILS.

As well as the dynamic parameter, I also did the following:

ANALYZE TABLE SCHEDULER$_JOB_RUN_DETAILS VALIDATE STRUCTURE online;

ALTER INDEX SYS.I_SCHEDULER_JOB_RUN_DETAILS REBUILD online;

Follow me on Twitter! @dbamohsin

Ive set up a new twitter handle for all things DBA – follow me @dbamohsin.

All future posts will be tweeted on this handle

Thanks

Principal "username" is not able to access the database "db" under the current security context.

The server principal "username" is not able to access the database "dbname" under the current security context.
.Net SqlClient Data Provider in SQL Server Management Studio.

This is because the MS SQL Server is attempting to execute a T-SQL query to retrieve a list of databases along with additional information about those databases. One of those pieces of information is "Collation", which you will not have permission to action for all databases as you’re in a shared hosting environment and you only have access to your database alone.

Solution:
Step 1: In Object Explorer, click Databases
Step 2: Display Object Explorer Details (F7) or View > Object Explorer Details
Step 3: Right click the column headers and de-select "Collation"
Step 4: Refresh Databases.

Counters I use for Baselining SQL Server performance

<Counter>\Memory\Available MBytes</Counter>
<Counter>\Memory\Pages/sec</Counter>
<Counter>\MSSQL$SQLINS01:Access Methods\Full Scans/sec</Counter>
<Counter>\MSSQL$SQLINS01:Buffer Manager\Buffer cache hit ratio</Counter>
<Counter>\MSSQL$SQLINS01:Buffer Manager\Page life expectancy</Counter>
<Counter>\MSSQL$SQLINS01:Buffer Manager\Page reads/sec</Counter>
<Counter>\MSSQL$SQLINS01:Buffer Manager\Page writes/sec</Counter>
<Counter>\MSSQL$SQLINS01:General Statistics\User Connections</Counter>
<Counter>\MSSQL$SQLINS01:Latches\Average Latch Wait Time (ms)</Counter>
<Counter>\MSSQL$SQLINS01:Locks(_Total)\Lock Waits/sec</Counter>
<Counter>\MSSQL$SQLINS01:Locks(_Total)\Number of Deadlocks/sec</Counter>
<Counter>\MSSQL$SQLINS01:Memory Manager\Target Server Memory (KB)</Counter>
<Counter>\MSSQL$SQLINS01:Memory Manager\Total Server Memory (KB)</Counter>
<Counter>\MSSQL$SQLINS01:Transactions\Free Space in tempdb (KB)</Counter>
<Counter>\MSSQL$SQLINS01:Transactions\Transactions</Counter>
<Counter>\PhysicalDisk(3 M:)\Avg. Disk Queue Length</Counter>
<Counter>\PhysicalDisk(3 M:)\Avg. Disk sec/Read</Counter>
<Counter>\PhysicalDisk(3 M:)\Avg. Disk sec/Write</Counter>
<Counter>\PhysicalDisk(3 M:)\Disk Reads/sec</Counter>
<Counter>\PhysicalDisk(3 M:)\Disk Writes/sec</Counter>
<Counter>\Processor(_Total)\% Processor Time</Counter>
<CounterDisplayName>\Memory\Available MBytes</CounterDisplayName>
<CounterDisplayName>\Memory\Pages/sec</CounterDisplayName>
<CounterDisplayName>\MSSQL$SQLINS01:Access Methods\Full Scans/sec</CounterDisplayName>
<CounterDisplayName>\MSSQL$SQLINS01:Buffer Manager\Buffer cache hit ratio</CounterDisplayName>
<CounterDisplayName>\MSSQL$SQLINS01:Buffer Manager\Page life expectancy</CounterDisplayName>
<CounterDisplayName>\MSSQL$SQLINS01:Buffer Manager\Page reads/sec</CounterDisplayName>
<CounterDisplayName>\MSSQL$SQLINS01:Buffer Manager\Page writes/sec</CounterDisplayName>
<CounterDisplayName>\MSSQL$SQLINS01:General Statistics\User Connections</CounterDisplayName>
<CounterDisplayName>\MSSQL$SQLINS01:Latches\Average Latch Wait Time (ms)</CounterDisplayName>
<CounterDisplayName>\MSSQL$SQLINS01:Locks(_Total)\Lock Waits/sec</CounterDisplayName>
<CounterDisplayName>\MSSQL$SQLINS01:Locks(_Total)\Number of Deadlocks/sec</CounterDisplayName>
<CounterDisplayName>\MSSQL$SQLINS01:Memory Manager\Target Server Memory (KB)</CounterDisplayName>
<CounterDisplayName>\MSSQL$SQLINS01:Memory Manager\Total Server Memory (KB)</CounterDisplayName>
<CounterDisplayName>\MSSQL$SQLINS01:Transactions\Free Space in tempdb (KB)</CounterDisplayName>
<CounterDisplayName>\MSSQL$SQLINS01:Transactions\Transactions</CounterDisplayName>
<CounterDisplayName>\PhysicalDisk(3 M:)\Avg. Disk Queue Length</CounterDisplayName>
<CounterDisplayName>\PhysicalDisk(3 M:)\Avg. Disk sec/Read</CounterDisplayName>
<CounterDisplayName>\PhysicalDisk(3 M:)\Avg. Disk sec/Write</CounterDisplayName>
<CounterDisplayName>\PhysicalDisk(3 M:)\Disk Reads/sec</CounterDisplayName>
<CounterDisplayName>\PhysicalDisk(3 M:)\Disk Writes/sec</CounterDisplayName>
<CounterDisplayName>\Processor(_Total)\% Processor Time</CounterDisplayName>

Percent_Complete – detailed output

Percent_Complete is readily available in the sys.dm_exec_requests table. I have enhanced it used if you want to track events based on time remaining and duration

SELECT 
percent_complete
,command
,session_id
,start_time 
,getdate() as 'Time Now' 
,DATEDIFF(mi, start_time, getdate()) as 'Duration (Mins)'
,    (((100/percent_complete)*DATEDIFF(mi, start_time, getdate()))
    -DATEDIFF(mi, start_time, getdate()))/60 as 'Hours Remaining' 
,t.text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE percent_complete > 0

Find Linked Server Usage in SQL

Very useful script if wanting to know where Linked Servers are being called:

SET NOCOUNT ON;
DECLARE @VName VARCHAR(256)
DECLARE Findlinked CURSOR
LOCAL STATIC FORWARD_ONLY READ_ONLY
     FOR
SELECT name AS name
    FROM sys.servers
    WHERE is_linked = 1
 
OPEN Findlinked;
FETCH NEXT FROM Findlinked INTO @VName;
 
WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @VName, OBJECT_NAME(OBJECT_ID) 
        FROM sys.sql_modules 
        WHERE Definition LIKE '%'+@VName +'%' 
        AND (OBJECTPROPERTY(OBJECT_ID, 'IsProcedure') = 1 
        OR OBJECTPROPERTY(OBJECT_ID, 'IsView') = 1);
 
    FETCH NEXT FROM Findlinked INTO @VName;
END
CLOSE Findlinked
 
OPEN Findlinked;
FETCH NEXT FROM Findlinked INTO @VName;
 
WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT j.name AS JobName,js.command 
        FROM msdb.dbo.sysjobsteps js
            INNER JOIN msdb.dbo.sysjobs j
                ON j.job_id = js.job_id
        WHERE js.command LIKE '%'+@VName +'%'
    FETCH NEXT FROM Findlinked INTO @VName;
END
 
CLOSE Findlinked
DEALLOCATE Findlinked

SSMS: Failed to connecr to an IPC Port (mscorlib) Opening DTA

Failed to connect to an IPC Port: The system cannot find the file specified.  (mscorlib)

1. started DTA.

2. stopped DTA during process.

3. Got error message when trying to restart the DTA on a query.

Solution was to open the Windows Task Manger and Killing DTAShell.exe and the error messsage went away

Follow

Get every new post delivered to your Inbox.

Join 129 other followers