Recent Articles

Configuring Websphere Plugin with NAGIOS Monitoring System

Posted by Sagar Patil

1. Download WAS plugin for Nagios from here.

Read more…

Digging deep in Oracle Grid Repository for Information

Posted by Sagar Patil

How Grid Repository Stores Data?

The agents upload data at MGMT_METRIC_RAW table which is retained for 7 days.
Raw data are aggregated by Grid Control based on a predefined policy into 1-hour records, stored in MGMT_METRIC_1HOUR.
After 31 days or a month, 1-hour records in MGMT_METRIC_1HOUR are subject for further aggregation that rolls into 1-day records.
These 1-day records are stored in MGMT_METRIC_1Day for 365 days or 1 year before purged by Grid Control.

Read more…

SQL Server logs, changing level of logging

Posted by Sagar Patil

To view SQL Server error log from SQL Server Enterprise Manager
- Expand a server group, and then expand a server.
- Expand Management, and then expand SQL Server Logs.
- Click the SQL Server Log to view it. Error log information appears in the details pane

Read more…

How to truncate SQL Server Transaction Log

Posted by Sagar Patil

Step 1.  Find Size of Database Log files

SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) like ‘%AdventureWorks%’
and name like ‘%Log%’
order by SizeMB desc

Read more…

SQL Server Permissions of Fixed Database Roles

Posted by Sagar Patil

Fixed database roles can be mapped to the more detailed permissions that are included in SQL Server.

Installing, Upgrading PERL Packages

Posted by Sagar Patil

perl GetMail_.pl returned
Can’t locate Email/MIME.pm in @INC (@INC contains: C:/Perl64/site/lib C:/Perl64/
lib .) at GetMail_.pl line 6.
BEGIN failed–compilation aborted at GetMail_.pl line 6.

Read more…

SQL Server Error Messages

Posted by Sagar Patil

SQL Server comes with pre-defined error messages that can be raised when a particular condition occurs. Built-in error messages are stored in the sysmessages table of the master database. Depending on the severity level, error messages can be returned to the offending connection or could be recorded in the SQL Server error log. The severity level determines the type and seriousness of the problem that caused the error. The following table summarizes various error severity levels: Read more…

SQL server SQLDIAG Utility

Posted by Sagar Patil

SQLDIAG.exe executable can be found in the SQL Server installation directory under the BINN folder. SQLDIAG records the SQL Server configuration, contents of the error logs (current, as well as archived logs), and Windows configuration including registry keys. If SQL Server is running when SQLDIAG is executed, it will record the output of the following routines: Read more…

SQL Server | How to create a Read Only User/Role

Posted by Sagar Patil

I have number of SQL server databases and users . I am in a need to create read only access to users who wants to look at data. Read more…

TSQL | Delete All Objects from Database

Posted by Sagar Patil

Often I have to copy number of SQL server database structures from prod system for testing. It’s difficult to get rid of all objects within a database manually. Following script will do just about that.

Read more…

What patches are applied to my SQL Server

Posted by Sagar Patil

Use

  • select @@VERSION”  OR
  • SELECT SERVERPROPERTY(‘productversion’), SERVERPROPERTY (‘productlevel’), SERVERPROPERTY (‘edition’) ” Or
  • Simply look at the value in the Management Studio Explorer

Read more…

How to use SP_CONFIGURE in SQL Server

Posted by Sagar Patil

sp_configure is a tool to display and change SQL Server settings. Please be aware that changing these settings can dramatically affect your instance.

Read more…

SQL Server Maintenance Scripts : YourSqlDba & SQL Server Maintenance Solution

Posted by Sagar Patil

If you are looking for a good TSQL packaged scripts which will do usual database maintenance activities then you are on right page. I have detailed 2 such projects which will help you to setup maintenance job in less than an hour.

Read more…

How to create a new named instance under SQL 2005/2008

Posted by Sagar Patil

We can only configure one default instance under SQL server installation.  If we need additional instance  it should be a named instance.

Read more…

How to locate SQL server clustered database & it’s properties

Posted by Sagar Patil

 

1. How to locate if I am connected to a SQL server cluster?

SELECT SERVERPROPERTY(‘IsClustered’)

Read more…

Streams | ORA-24082 while disabling a propagation job

Posted by Sagar Patil

The reason for the ORA-24082 error is that a message has been enqueued  onto the AQ$_PROP_NOTIFY_<N> queue to notify the job_queue_process associated with  the propagation schedule to stop propagating messages and the
job_queue_process has failed to acknowledge that message within a  reasonable amount of time. Read more…

Streams – Capture Process States

Posted by Sagar Patil

The state of a capture process describes what the capture process is doing currently. You can view the state of a capture process by querying the STATE column in the V$STREAMS_CAPTURE dynamic performance view. The following capture process states are possible

Read more…

Useful Tables and Views, for Troubleshooting Streams Issues

Posted by Sagar Patil
CAPTURE PROCESS
streams$_capture_process Lists all defined capture processes
dba_capture Basic status, error info
v$streams_capture Detailed status info
dba_capture_parameters Configuration information
PROPAGATE PROCESS
streams$_propagation_process: Lists all defined propagate processes
dba_propagation Basic status, error info
v$propagation_sender Detailed status info
v$propagation_receiver Detailed status info
 APPLY PROCESS
 streams$_apply_process Lists all defined apply processes
 dba_apply Basic status, error info
 v$streams_apply_reader Status of the apply reader
 v$streams_apply_server Status of apply server(s)
 v$streams_apply_coordinator Overall status, latency info
 dba_apply_parameters Configuration information
Miscellaneous Tables 
 v$buffered_queues View that displays the current and cumulative number of messages enqueued and spilled, for each buffered queue.
 sys.streams$_apply_spill_msgs_part Table that the apply process uses, to “spill” messages from large transactions to disk.
 system.logmnr_restart_ckpt$ Table that holds capture process “checkpoint” information.

Read more…

Identify Issues and Fix Streams Capture Process

Posted by Sagar Patil

My Capture, Propagate and apply processes were working fine until the server fial over. After a restart even though capture processes are enabled, they are stalled at status “INITIALIZING”. The Propagate and Apply were working fine.

Read more…

Cloning Oracle Clusterware (Applicable only to 11.2.0.2.0 and not for any previous Releases)

Posted by Sagar Patil

Cloning is the process of copying an existing Oracle installation to a different location and then updating the copied installation to work in the new environment.

Read more…

Streams/RAC and Database Link Problem

Posted by Sagar Patil

While working with streams 10g RAC,  setup went well but source database  propagation process won’t work with target database “strmrepl1″ and returned TNS “12514″ error.

Read more…

Streams Performance Data Collection Tools for 10g & 11g

Posted by Sagar Patil

There are diferent ways to collect performance related data

  • Install Application Workload Repository (AWR) /Active Session History (ASH). Take AWR snapshots an hour or for desired interval
  • Install and Configure STRMMON utility if you have 10gR2(Not available in 11g). Use Metalink Article “290605.1″
  • Use UTL_SPADV as a alternative to STRMMON for > 11gR1
  • Run Streams Healthcheckat each database: before, during , and after performance period of interest, available from Metalink:Article273674.1

Read more…

11gR2 RAC-Dataguard Sync issue Between Primary & Standby

Posted by Sagar Patil

I have a 2 node RAC -DG setup between 2 remote data centres. After building DataGuard between them I am now coming across stange latency stats. Read more…

Setting up Bidirectional Streams SCHEMA Replication

Posted by Sagar Patil

Details below are for configuring a schema level replication using API DBMS_STREAMS_ADM.MAINTAIN_SCHEMAS.

Read more…

Building Tablespace Growth Report from Grid Data

Posted by Sagar Patil

Use SQL Query below at Grid sysman database to locate growth of tablespaces over time. The returned results data could be used under EXCEL to generate graphs. Read more…

Oracle Roles and Users audit report

Posted by Sagar Patil

I have often used following procedure to create list of roles & corresponding users mapped into it. This was then used to email business owners as well as for user accesss auditing. Read more…

Streams Replication| Monitoring Streams Capture Processes

Posted by Sagar Patil

Capture process is an optional Streams client that is an Oracle background process that scans a database redo log to capture DML and DDL changes made to database objects. Read more…

Top of Page
Page 1 of 1612345...10...Last »