MSSQL: Maintaining SQL DB Statistics.

MSSQL: Maintaining SQL DB Statistics.
May 22, 2019.

**Notes: All queries have been run and validated in MSSQL 2008/2012/2017.

Statistics are a description of the distribution of data in a column or index.

The query optimizer relies heavily on statistics, so if the stats do not represent what you are looking for very well then the optimizer
can create an inefficient query plan. The optimizer’s query plan is determined before execution, and once the plan is made the execution must either complete or be cancelled. (In other words, there is no mechanism by which after running for a while SQL can see that the plan it made is based on bad statistics and start over.)

Knowing how to evaluate the freshness of your stats sure makes troubleshooting easier when problems occur.

The query below will look at all statistics in the current database and for each one show you the time at which statistics were last updated, as well as the approximate number of rows that have been changed in that statistic since the last update.

SELECT DISTINCT
tablename=object_name(i.object_id)
, o.type_desc
,index_name=i.[name]
, statistics_update_date = STATS_DATE(i.object_id, i.index_id)
, si.rowmodctr
FROM sys.indexes i (nolock)
JOIN sys.objects o (nolock) on
i.object_id=o.object_id
JOIN sys.sysindexes si (nolock) on
i.object_id=si.id
and i.index_id=si.indid
where
o.type = ‘S’
–ignore system objects
and STATS_DATE(i.object_id, i.index_id) is not null
order by si.rowmodctr desc

Updating Stats Using T-SQL:

Update statistics for an index
USE AdventureWorks;
GO
UPDATE STATISTICS Adventureworks. ;
GO

Update statistics for a table
USE AdventureWorks;
GO
UPDATE STATISTICS Adventureworks.;
GO

Update all statistics
USE AdventureWorks
GO
EXEC sp_updatestats;
GO
==================================================
Prakash

Oracle – Audit Trail (Logins, Connect Info).

Oracle – Audit Trail (Logins, Connect Info)
May 20, 2019.

**Notes: All scripts have been validated/used with Oracle 11g Ent Edition running on Oracle Linux.

The audit trail lets you see important session information, including who has connected to the database, how long for and where
failed logon attempts have come from. This information is essential for security.

The first step is to turn the audit trail on. This needs to be done in the database’s init.ora file. Add the following parameter to the init.ora file:

audit_trail=DB
or
*.audit_trail=’db_extended’
The extended will give you a more granular audit info even for SYSDBA sessions. The blog below link explains the differences.

https://jhdba.wordpress.com/2014/07/15/the-value-of-audit_traildbextended/

Please note that both types of audit info are going to the DB, not to an external flat file.
The database needs to be shutdown and started up again for the parameter to be read.

Validating the audit option is enabled:
 select value
 from v$parameter
 where name = ‘audit_trail’;

Enable Connection Auditing
SQL>audit connect;

The audit trail should now log every logon and logoff to the database

Querying the Audit Trail
The connection audit trial can be queried via the dba_audit_sessions view.
Some examples.

Count of Connects by User in the Last 7 Days.
SELECT das.username,
        COUNT(*) logonCount
   FROM sys.dba_audit_session das
  WHERE das.timeStamp > SYSDATE-7
    AND das.returnCode = 0
  GROUP BY das.username;

Number of Minutes Connected By User in the Last 7 Days.
SELECT das.username,
        ROUND(SUM((NVL(das.logoff_time,SYSDATE)-das.timestamp)*1440)) connectMins
   FROM sys.dba_audit_session das
  WHERE das.timeStamp > SYSDATE-7
    AND das.returnCode = 0
  GROUP BY das.username;

Unsuccessful Logon Attempts in the Last 7 Days.
SELECT das.username,
        das.os_username,
        das.terminal,
        TO_CHAR(das.timeStamp,’DD Mon YYYY HH24:MI’) timestamp,
        das.returnCode
   FROM sys.dba_audit_session das
  WHERE das.timeStamp > SYSDATE-7
    AND das.returnCode != 0;

The above is particularly useful in triaging locked service accounts if they are used in scheduled reports. Also useful is identifying errant/unauthorized user accounts.

Connections Made Out Of Working Hours in the Last Week
SELECT das.username,
        TO_CHAR(das.timestamp,’DD Mon YYYY HH24:MI:SS’)   logontime,
        TO_CHAR(das.logoff_time,’DD Mon YYYY HH24:MI:SS’) logofftime
   FROM sys.dba_audit_session das
  WHERE das.timeStamp > SYSDATE-7
    AND das.returnCode = 0
    AND NOT (    TO_NUMBER(TO_CHAR(das.timestamp,’D’)) < 6
             AND TO_NUMBER(TO_CHAR(das.timestamp,’HH24MI’)) BETWEEN 800 AND 1800 )
  ORDER BY das.username;

Prakash.

MSSQL ERROR 15141: The server principal owns an events notification and cannot be dropped.(Microsoft Sql Server)

MSSQL ERROR 15141: The server principal owns an events notification and cannot be dropped.(Microsoft Sql Server,
Error : 15141)
May 18 2019.

Problem: As a SQl admin you are trying to drop a user from instance logins.
SQL Server Instance—Security–Login–Highlight a user to drop—delete.

The SQL Server errors out with 15141 code.

Solution:
The error just means that the particular user, you are trying to drop, has event notifications enabled/active.
Run this query:

SELECT
s.name AS ‘EventNotificationName’
, p.name AS ‘PrincipalName’
, ‘DROP EVENT NOTIFICATION ‘+s.name+’ ON SERVER;’ AS ‘DropEventNotificationQuery’
FROM sys.server_event_notifications s
LEFT JOIN sys.server_principals p
ON s.principal_id = p.principal_rror
WHERE p.name = ‘domain\user’;

replace domain\user with your domain and user.

Should give you the broker processes the user has notifications enabled and the last column will give you the SQL statement to drop the notifications.
Once that is done you should be able to Drop the login.

Prakash.

ORA-00258: Manual Archiving in NOARCHIVELOG mode must identify log.

ORA-00258: Manual Archiving in NOARCHIVELOG mode must identify log.
May 6, 2019

Problem:
Trying to do full backup of an Oracle database using RMAN.
Get the above error.

Here is the entire seq of errors before RMAN kicks out.
RMAN>
Starting backup at 14-APR-18
ORACLE error from target database:
ORA-00258: manual archiving in NOARCHIVELOG mode must identify log

using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
specification does not match any archived log in the repository
backup cancelled because there are no files to backup
Finished backup at 14-APR-18

Solution:
Basically the DB is in a NOARCHIEVLOG mode and we cannot backup the DB without specifying Log Seq Number.

Restarting the Database in a ARCHIEVLOG mode.

Using SQL*Plus to Change the Database Archiving Mode

1: Connect to DB as SYSDBA
SQLPLUS ‘/as sysdba’

2:SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

3: Start the instance and mount the database:
    SQL> STARTUP MOUNT
ORACLE instance started.
Database mounted.

4: Place the database in ARCHIVELOG mode
SQL> ALTER DATABASE ARCHIVELOG;
Database altered.

5: Open Database
SQL> ALTER DATABASE OPEN;
Database altered.

6: Verify your changes:
SQL>ARCHIVE LOG LIST

Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u05/oradata/flash_recovery_area/xxxx/archivelog/
Oldest online log sequence xxxxxx
Next log sequence to archive xxxxxx
Current log sequence xxxxxxx

As you can see the Archievlog is enabled.
No we try to do a backup again using RMAN.
The backup, using RMAN, works.

Prakash

Oracle: pfile and spfile notes.

Oracle: pfile and spfile notes (Tested on Oracle 11g on Oracle Linux)
May 5, 2019

By default Oracle will start using the spfilesid.ora if there is one present in:
$ORACLE_HOME\dbs

If there is no spfile then it starts up using pfilesid.ora in:
$ORACLE_HOME\dbs

If we wanted to to startup oracle using pfile other that the default names then the full path command would be:
startup pfile=’/u01/app/oracle/product/11.2.0/db_1/dbs/XXXXXX.ora’;
‘XXXXX’ being one time use pfile name that we want to use.

To make a pfile from a working spfile:
create pfile=’/$ORACLE_HOME/dbs/xxxxxxx.ora’ from spfile=’/$ORACLE_HOME/dbs/xxxxxxxxx.ora’;

To make a spfile from a working pfile:
CREATE SPFILE=’$ORACLE_HOME/dbs/xxxxxxx.ora’ FROM PFILE=’$ORACLE_HOME/dbs/xxxxxxxx.ora’;

Changing an SPFILE Parameter While the DB is UP:

ALTER SYSTEM SET smtp_out_server=’mail.hallmarkbusiness.com’
                 COMMENT=’mail server change.’
                 SCOPE=BOTH;

examples:
create pfile=’/$ORACLE_HOME/dbs/initbsid.ora’ from spfile=’/$ORACLE_HOME/dbs/spfilebsid.ora’;
create SPFILE=’/$ORACLE_HOME/dbs/spfilebsid.ora’ from pfile=’/$ORACLE_HOME/dbs/initbsid.ora’;

STARTING DB WITH PFILE:
STARTUP PFILE = /$ORACLE_HOME/dbs/initbsid.ora

Prakash

MSSQL: Finding out TCP/IP ports a MSSQL Server Instance is running under.

Finding out what ports a MSSQL Server Instance is Configured.
May 5, 2019

1: SELECT DISTINCT local_net_address, local_tcp_port
FROM sys.dm_exec_connections
WHERE local_net_address IS NOT NULL

2: Execute below script if SQL Server is configured with dynamic ports.
SELECT local_tcp_port
FROM   sys.dm_exec_connections
WHERE  session_id = @@SPID


3: Execute below script if SQL Server is configured with static port number

DECLARE       @portNo   NVARCHAR(10)

EXEC   xp_instance_regread
  @rootkey    = ‘HKEY_LOCAL_MACHINE’,
  @key        =
‘Software\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib\Tcp\IpAll’,
  @value_name = ‘TcpPort’,
  @value      = @portNo OUTPUT

SELECT [PortNumber] = @portNo

Any one of the above will get you the port information.

Prakash

Oracle Linux: Setting the AUDIT_SYSLOG_LEVEL Parameter.Oracle 11g. RHEL6 (onwards).

Setting the AUDIT_SYSLOG_LEVEL Parameter. Oracle 11g. REHL6 (Onwards).
May 5, 2019

APPLIES TO:
Oracle Database – Enterprise Edition – Version 10.2.0.1 to 11.2.0.4 [Release 10.2 to 11.2]
ALL platforms.

ISSUE:
Because of Infosec dictate we are required to port/export our DB logs to OS rsyslog.
Example: If the ‘Connect’ audit trail is enabled in the DB, the requirement would be to write these connect logs to OS rsyslogs.

SOLUTION:
AUDIT_SYSLOG_LEVEL parameter. When the AUDIT_TRAIL parameter is set to OS, writes DB audit records to the system audit log using the rsyslog utility.

To enable syslog auditing for all the users (privileged or not privileged), you assign a value of OS to the AUDIT_TRAIL initialization parameter, as described in “Setting the AUDIT_TRAIL Initialization Parameter”. 
You assign to the AUDIT_SYSLOG_LEVEL parameter a facility and priority in the format AUDIT_SYSLOG_LEVEL=facility.priority. The facility argument describes the part of the operating system that is logging the message while the priority argument defines the severity of the message.
The syslog daemon compares the value assigned to the facility argument of the AUDIT_SYSLOG_LEVEL parameter with the rsyslog.conf file in order to determine where to log information.
For example, the following statement identifies the facility as local1 with a priority level of warning:

AUDIT_SYSLOG_LEVEL=local1.warning
Setting the AUDIT_SYSLOG_LEVEL initialization parameter to the default value (NONE) will result in DBAs gaining access to the OS audit records.

To enable syslog auditing, follow these steps:
Assign a value of OS to the AUDIT_TRAIL initialization parameter:

For example:
SQL> ALTER SYSTEM SET AUDIT_TRAIL=OS SCOPE=SPFILE;
Options available for us:
Values:< BR>NONE

Disables standard auditing.
This value is the default if the AUDIT_TRAIL parameter was not set in the initialization parameter file or if you created the database using a method other than Database Configuration Assistant.
If you created the database using Database Configuration Assistant, then the default is db.

OS
Directs all audit records to an operating system file. Oracle recommends that you use the OS setting, particularly if you are using an ultra-secure database configuration.

DB
Directs audit records to the database audit trail (the SYS.AUD$ table), except for records that are always written to the operating system audit trail. Use this setting for a general database for manageability.
If the database was started in read-only mode with AUDIT_TRAIL set to db, then Oracle Database internally sets AUDIT_TRAIL to os. Check the alert log for details.

DB, EXTENDED
Performs all actions of AUDIT_TRAIL=db, and also populates the SQL bind and SQL text CLOB-type columns of the SYS.AUD$ table, when available. These two columns are populated only when this parameter is specified.
If the database was started in read-only mode with AUDIT_TRAIL set to db, extended, then Oracle Database internally sets AUDIT_TRAIL to os. Check the alert log for details.

XML
Writes to the operating system audit record file in XML format. Records all elements of the AuditRecord node except Sql_Text and Sql_Bind to the operating system XML audit file.

XML, EXTENDED
Performs all actions of AUDIT_TRAIL=xml, and includes SQL text and SQL bind information in the audit trail.
Also set the AUDIT_SYSLOG_LEVELparameter.

SQL> ALTER SYSTEM SET AUDIT_SYSLOG_LEVEL=”local1.warning” SCOPE=SPFILE;
Set the AUDIT_SYSLOG_LEVEL parameter to specify a facility and priority in the format AUDIT_SYSLOG_LEVEL=facility.priority.
facility: Describes the part of the operating system that is logging the message. Accepted values are user, local0–local7, syslog, daemon, kern, mail, auth, lpr, news,uucp, andcron.

The local0–local7 values are predefined tags that enable you to sort the syslog message into categories. These categories can be log files or other destinations that the syslog utility can access.

priority: Defines the severity of the message. Accepted values are notice, info, debug, warning, err, crit, alert, and emerg.

The syslog daemon compares the value assigned to the facility argument of the AUDIT_SYSLOG_LEVEL parameter with the syslog.conf file to determine where to log the information.  The decision where to write the syslog entries does not belong to the Oracle services, but to the syslog daemon.

For example, the following statement identifies the facility as local1 with a priority level of warning:

AUDIT_SYSLOG_LEVEL=local1.warning

Add the audit file destination to the rsyslog configuration file /etc/rsyslog.conf.

For example, assuming you had set the AUDIT_SYSLOG_LEVEL to local1.warning, enter the following:

local1.warning    /var/log/audit.log

This setting logs all warning messages to the /var/log/audit.log file.

Comment: separate the entries in syslogd.conf by using TAB rather than spaces, otherwise it may not work for all syslogd versions, so the above would really be:

local1.warning<tab><tab>/var/log/audit.log

Also pre-create the file as follows (as root):

# touch /var/log/audit.log

The facility line for your messages in the file rsyslog.conf should appear before the “catch all” setting and you should include appropriate .none entry to “catch all” also.

Once the changes are made to the rsyslog.conf, restart the rsyslog service.

#systemctl restart rsyslog.servic

If you get a message like so:
Redirecting to /bin/systemctl restart syslog.service
Failed to restart syslog.service: Unit not found.

Then you are actually running rsyslog.
Make sure the changes are made to /etc/rsyslog.conf
#service rsyslog restart

Prakash

Oracle: Resolving Blocking.

Finding Blocked Sessions In Oracle (All scripts verified in 11g running under Oracle Linux)
May 5, 2019

Blocking sessions occur when one sessions holds an exclusive lock on an object and doesn’t release it before another sessions wants to update the same data. This will block the second until the first one has done its work. It mainly happens when a session issues an insert, update or delete command that changes a row. When the change occurs, the row is locked until the session either commits the change or rolls the change back.

Oracle provides several views to find the sessions that are the cause of blocking, like DBA_BLOCKERS, V$LOCK.

1: Here is query that will try to find locking sessions using V$LOCK view.

SELECT vs.username,
vs.osuser,
vh.sid locking_sid,
vs.status status,
vs.module module,
vs.program program_holding,
jrh.job_name,
vsw.username,
vsw.osuser,
vw.sid waiter_sid,
vsw.program program_waiting,
jrw.job_name,
‘alter system kill session ‘ || ””|| vh.sid || ‘,’ || vs.serial# || ”’;’  “Kill_Command”
FROM v$lock vh,
v$lock vw,
v$session vs,
v$session vsw,
dba_scheduler_running_jobs jrh,
dba_scheduler_running_jobs jrw
WHERE     (vh.id1, vh.id2) IN (SELECT id1, id2
FROM v$lock
WHERE request = 0
INTERSECT
SELECT id1, id2
FROM v$lock
WHERE lmode = 0)

AND vh.id1 = vw.id1
AND vh.id2 = vw.id2
AND vh.request = 0
AND vw.lmode = 0
AND vh.sid = vs.sid
AND vw.sid = vsw.sid
AND vh.sid = jrh.session_id(+)
AND vw.sid = jrw.session_id(+);

The output, if there is any blocking activity, will give us session information, blocking and blocked SID. The last column
 gives the actual kill command to kill a hung session.  

2:  Find blocking sessions with v$session

SELECT
   s.blocking_session,
   s.sid,
   s.serial#,
   s.seconds_in_wait
FROM
  
v$session s
WHERE
   blocking_session IS NOT NULL

3 Blocking sessions, a comprehensive query to get all available information. Run this query and you can immediately place a call to the user who is causing the blocking.

SELECT s1.username || ‘@’ || s1.machine
    || ‘ ( SID=’ || s1.sid || ‘ )  is blocking ‘
    || s2.username ||
‘@’ || s2.machine || ‘ ( SID=’ || s2.sid || ‘ ) ‘ AS blocking_status
    FROM v$lock l1, v$session s1, v$lock l2, v$session s2
    WHERE s1.sid=l1.sid AND s2.sid=l2.sid
    AND l1.BLOCK=1 AND l2.request > 0
    AND l1.id1 = l2.id1
    AND l1.id2 = l2.id2

These queries should help you to identify the cause of your blocking sessions!

To Kill a session:

Alter system kill session ‘23,7955’;   (example only)
Alter system kill session ‘sid, serial’;

4: Finding a session and killing a session
SELECT SID, SERIAL#, STATUS, SERVER, USERNAME
FROM V$SESSION

—–Where clause
–WHERE USERNAME = ‘tomf’

ALTER SYSTEM KILL SESSION ‘sid, serial’;

Prakash

Data Visualization: Pathway to being a Data Analyst/Engineer/Scientist.

Data Visualization. Developing Your Skill sets
1 of many
May 4, 2019

For those first entering the field of Data Analytics, knowing where to start, what skill set to acquire, and resources available can be a daunting task.

The field of Data Analytics is huge and ever expanding. Within the next few articles I will try to lay the basics of skills we can learn, on our own. These are the topics we will cover.

Advanced Excel:
     Pivot Tables.
     VBA Scripting.

Fundamental Statistics:
     Modeling.
     Forecasting.

Python Programming:
     Python 3.
     NumPy.
     Pandas.
     Matplotlib.
     API Interactions.
     Social Media Mining.

Databases:
     MySQL.
     MongoDB.
     ETL.

Front-End Web Visualization:
     HTML.
     CSS.
     Bootstrap.
     Dashboarding.
     JavaScript Charting.
     D3js.
     Geomapping with Leaflet js

Business intelligence Software:
     Tableau.

Advanced Topics:
     Big data Analytics with Hadoop.
     Machine Learning.

Prakash

Linux: Changing Hostname on a Linux Server.

Changing Hostname On a Linux Server.
April 30, 2019.

The manual method:
This method will work on nearly any Linux distribution.

Before we get into this method, do note it will require you to reboot the server. Otherwise, the new hostname will not go into effect and you could wind up with some random issues—depending upon what your server is used for.

Open up a terminal window. We can find out what our current hostname is by issuing the command hostname in Linux.

[test@testdb]#hostname
#testdb.dept118.local
[test@testdb]#


To modify the hostname, we need to modify two files.
vi /etc/hostname.
In this file you will see a single line that contains your system hostname. Edit that line to reflect the new hostname. Once you’ve done that, save and close the file.

Now we modify /etc/hosts file.
vi /etc/hosts
In this file, you’ll want to change any instance of the old hostname to reflect the new hostname. There should only be one entry/line to change.

Save the file.
Exit.
Reboot.

Once it is up, the new hostname should take effect. Open a terminal window. Issue hostname command. You will see the new name within the prompt.

Notes on VM Linux node existing within a windows infrastructure.
When we change the hosts file and the hostname above, the DNS entries local to the Linux VM node will change to the new name, BUT, if we are connecting to this from a Windows system, say, a remote putty session or any other remote session connect, then the DNS entries within the Windows domain need to be changed to point at at the new hostname.

Prakash