Oracle: How to reactive-ly monitoring db_link timeout.

I thought this is an interesting question that brought up to me on how to reactive-ly responding to when there are packet loss and impacting the main server where application unable to retrieve data through synonym that pointing to db link. Since db link isn't a process and packet loss is outside of the database control and the server hosting the database instance, there isn't really anything can be done about it within the database. There isn't anything to be restarted or retried or anything built in inside Oracle OEM to monitor and reactive-ly responding to packet loss. 

Perhaps install simple script to check remote db_link is alive in the crontab.

#!/bin/ksh

chk_table=`sqlplus -s vc50/password1@orcl<<oem

set heading off;

select count(1) from dual@db_link_name;

oem

`

if [ $chk_table != "1" ]

then

echo $chk_table

echo -e 'Remote server might be down '`uname -n`'' | mail -v -s 'Package loss is happening' admin@company.com

fi

Another option would probably increase the sqlnet.inbound_connect_timeout parameter within sqlnet.ora but that is not exactly a way to monitor. Any other ways of reactive-ly monitoring db link dis-connectivity ?  If you do, please leave me a note. Thanks in advances.

MSSQL: CHECKDB found 0 allocation errors and X consistency error

Found corruption error while running DBCC CHECKDB on the SQL Server DB.

Error messages

Msg 8990, Sev 16, State 1, Line 2 : CHECKDB found 0 allocation errors and 10 consistency errors in table 'VPX_EVENT' (object ID 138483572). [SQLSTATE 01000]

Msg 8989, Sev 16, State 1, Line 2 : CHECKDB found 0 allocation errors and 10 consistency errors in database 'VCDB'. [SQLSTATE 01000]

Msg 8958, Sev 16, State 1, Line 2 : repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (VCDB). [SQLSTATE 01000]

This is data loss corruption similar to Oracle’s datafile header corruption. Damage already been done. User can use repair_allow_data_loss feature as the SQL Server error suggested or restore from good backup. If backup available, I would prefer restoring the db or the specific table. The good news is, the VPX_EVENT isn’t a crucial table. It stores events related information.

Here is what can be done on the repair route. Another option is to clone the existing VCDB to a different MSSQL database and test out the procedures. If everything works smoothly (verified with no data loss), perform the same on the production side.

--If user decided to do it in existing db then take backup of the VCDB first

use VCDB

--verify the corruption still exist

DBCC CHECKDB

--this can only be done in single user mode. So, switch it to single user.

ALTER DATABASE VCDB SET SINGLE_USER;

--attempt with REPAIR REBUILD first where it will attempt to repair without data loss.

DBCC CHECKDB ('VCDB', REPAIR_REBUILD);

--To verify if corruption still exist.

DBCC CHECKDB;

--if it does, move on to repair allow data loss feature, this will result of masking out the corruption where there will unrepairable data loss.

DBCC CHECKDB ('VCDB', REPAIR_ALLOW_DATA_LOSS);

Once completed, set the db back to multi users, otherwise no one else can access the database.

ALTER DATABASE VCDB SET MULTI_USER;

DBA should find out what causes this corruption. This typically results of disk issues.

VC6.5: DB lock has been detected on the database by the user associated with the provided DSN.

This is caused by either the vCenter schema is not completely created or the schema session is locked by something else. If you know that the possible locked session is all you need to get rid of then do the following query.
Or
Bouncing the database instance took care of it.

SYS> select 'alter system kill session ''' || sid || ',' || serial# || ''';' from gv$session where username ='VPX';

'ALTERSYSTEMKILLSESSION'''||SID||','||SERIAL#||''';'

--------------------------------------------------------------------------------------------------------------

alter system kill session '240,4292';

SYS> alter system kill session '240,4292';

System altered.

MSSQL on Linux datafiles with C prompt

Installing MSSQL on Linux is fairly straightforward.

Installation 

https://www.microsoft.com/en-us/sql-server/sql-server-vnext-including-Linux

For MSSQL on Ubuntu simply follow this link.

https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup-ubuntu

SQL Server installation do enforce the 3.25Gig memory size. So, prior to stat the installation make sure you environment or VM do have around 4gig of memory to work with. I allocated 60Gig on my VMs. After the Ubunto Server 64 bits deployment and MSSQL installation, I still have 52gig left.

The main point of this blog is not to guide user to deploy MSSQL on Linux but to show one of the interesting behavior I noticed.

administrator@ubuntu:/var/opt$ sqlcmd -U SA

Password:

1> select @@version;

2> go

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Microsoft SQL Server vNext (CTP1.1) - 14.0.100.187 (X64)

Dec 10 2016 02:51:11

Copyright (C) 2016 Microsoft Corporation. All rights reserved.

on Linux (Ubuntu 16.10)

(1 rows affected)

1> select name from sys.databases;

2> go

name

--------------------------------------------------------------------------------------------------------------------------------

master

tempdb

model

msdb

(4 rows affected)

1>

As you can see here, the datafile path starts with C:\ prompt.

SELECT cast(DB_NAME(database_id) as varchar(20)) AS "Database Name",

       cast(Name as varchar(25)) AS "Logical Name",

       cast(Physical_Name as varchar (100)) as "Datafiles Path",

       (size * 8) / 1024 as "Size in MB",

       (size * 8) / 1024/ 1024 as "Size in Gig"

FROM sys.master_files;

Database Name        Logical Name              Datafiles Path                                                                                       Size in MB  Size in Gig

-------------------- ------------------------- ---------------------------------------------------------------------------------------------------- ----------- -----------

master               master                    C:\var\opt\mssql\data\master.mdf                                                                               4           0

master               mastlog                   C:\var\opt\mssql\data\mastlog.ldf                                                                              2           0

tempdb               tempdev                   C:\var\opt\mssql\data\tempdb.mdf                                                                               8           0

tempdb               templog                   C:\var\opt\mssql\data\templog.ldf                                                                              8           0

model                modeldev                  C:\var\opt\mssql\data\model.mdf                                                                                8           0

model                modellog                  C:\var\opt\mssql\data\modellog.ldf                                                                             8           0

msdb                 MSDBData                  C:\var\opt\mssql\data\MSDBData.mdf                                                                            13           0

msdb                 MSDBLog                   C:\var\opt\mssql\data\MSDBLog.ldf                                                                              0           0

User can access the physical datafile with sudo.

administrator@ubuntu:/var/opt$ sudo ls -las /var/opt/mssql/data/

total 53320

    4 drwxr-xr-x 2 mssql mssql     4096 Dec 19 10:54 .

    4 drwxrwx--- 7 mssql mssql     4096 Dec 19 10:55 ..

 4096 -rw-r----- 1 mssql mssql  4194304 Dec 19 11:06 master.mdf

 2048 -rw-r----- 1 mssql mssql  2097152 Dec 19 12:06 mastlog.ldf

 8192 -rw-r----- 1 mssql mssql  8388608 Dec 19 11:00 modellog.ldf

 8192 -rw-r----- 1 mssql mssql  8388608 Dec 19 11:00 model.mdf

13632 -rw-r----- 1 mssql mssql 13959168 Dec 19 10:55 msdbdata.mdf

  768 -rw-r----- 1 mssql mssql   786432 Dec 19 10:55 msdblog.ldf

 8192 -rw-r----- 1 mssql mssql  8388608 Dec 19 10:55 tempdb.mdf

 8192 -rw-r----- 1 mssql mssql  8388608 Dec 19 11:00 templog.ldf

Interesting that SQL Server still keeping the Windows behavior.

Unable to access VCSA appliance after initial setup

After everything's deployed, ssh, ping, curl works from within the appliance but access through web browser or anything remote will not worked..

At this point, only either firewall or gateway would be the issue. Sure enough, gateway was not even listed (default). Adding temporary gateway works immediately. I am able to see the Web Client.

Checking the IP Route, I was not finding my default Gateway at all.

Running the following command fixed my webclient issue. This is a temporary solution. After reboot of the appliance, the gateway will disappeared. One can implement a permanent solution to the network entries.

route add default gw 10.131.15.254

The Gateway can be found at the host - Configuration but for some reason, it did not get picked up during deployment. 

VCSA: Where does vCenter Appliance stores its Performance Chart connect string.

Be reminded that this is going through JDBC instead of ODBC. This piece is responsible for Hourly, Daily, Weekly and Yearly Performance Chart.

/etc/vmware-vpx/vcdb.properties

driver = org.postgresql.Driver

dbtype = PostgreSQL

url = jdbc:postgresql://localhost:5432/VCDB

username = vc

password = w4%wH3@!BybXd

password.encrypted = false

VCSA: Where does vCenter Appliance store the ODBC settings?

It resides in /etc/odbc.ini

[VMware VirtualCenter]

;DB_TYPE = PostgreSQL

;SERVER_NAME = localhost

;SERVER_PORT = 5432

;TNS_SERVICE = VCDB

;USER_ID = vc

Application Attributes = T

Attributes = W

BatchAutocommitMode = IfAllSuccessful

BindAsFLOAT = F

CloseCursor = F

DisableDPM = F

DisableMTS = T

Driver = PostgreSQL

DSN = VMware VirtualCenter

EXECSchemaOpt =

EXECSyntax = T

Failover = T

FailoverDelay = 10

FailoverRetryCount = 10

ForceWCHAR = F

Lobs = T

Longs = T

MetadataIdDefault = F

QueryTimeout = T

ResultSets = T

ServerName = localhost

PortNumber = 5432

Server = localhost

Port = 5432

SQLGetData extensions = F

Translation DLL =

Translation Option = 0

DisableRULEHint = T

UserID = vc

User = vc

Database = VCDB

Logging = 0

QuotedId = Yes

AnsiNPW = Yes

Mars_Connection = No

ByteaAsLongVarBinary = 1

BoolsAsChar = 0

UseDeclareFetch = 1

Fetch = 1024