Saturday, May 30, 2015

Sample Static IP configuration in OL7 RHEL7

[root@mysql01 ~]# cat /etc/sysconfig/network-scripts/ifcfg-eno16777728
TYPE=Ethernet
BOOTPROTO=static
NAME=eno16777728
ONBOOT=yes
IPADDR=192.168.6.51
NETMASK=255.255.255.0
GATEWAY=192.168.6.2
DNS1=192.168.6.2
DNS2=8.8.8.8
DOMAIN=localdomain

Disable firewall on Redhat EL7 (OL7)

[root@mysql01 ~]# systemctl stop firewalld.service
[root@mysql01 ~]# systemctl disable firewalld.servicerm '/etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service'
rm '/etc/systemd/system/basic.target.wants/firewalld.service'



[root@mysql01 ~]# iptables -LChain INPUT (policy ACCEPT)
target     prot opt source               destination


Chain FORWARD (policy ACCEPT)
target     prot opt source               destination


Chain OUTPUT (policy ACCEPT)
target     prot opt source               destination




[root@mysql01 ~]# systemctl status firewalld.servicefirewalld.service - firewalld - dynamic firewall daemon
   Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled)
   Active: inactive (dead)


May 30 19:22:38 mysql01.localdomain systemd[1]: Starting firewalld - dynamic firewall daemon...
May 30 19:22:42 mysql01.localdomain systemd[1]: Started firewalld - dynamic firewall daemon.
May 30 19:24:55 mysql01.localdomain systemd[1]: Stopping firewalld - dynamic firewall daemon...
May 30 19:24:56 mysql01.localdomain systemd[1]: Stopped firewalld - dynamic firewall daemon.
May 30 19:25:14 mysql01.localdomain systemd[1]: Stopped firewalld - dynamic firewall daemon.

Monday, May 25, 2015

How to fix Maintenance Plan after renaming SQL Server Hostname

After renaming sever from “WIN-QITD52CONSC” to “VMWDB02”, the “Connections” inside the maintenance plan still pointing to previous server name.

image

 

image

image

 

image

image

 

image

image

 

image

image

 

image

 

image

image

Rename Windows Server name from “WIN-QITD52CONSC” to “VMWDB02”


PS C:\> sqlcmd -S VMWDB02\PROD1 –W


1> select @@servername
2> GO

-
WIN-QITD52CONSC\PROD1

(1 rows affected)


1> select serverproperty('servername')
2> GO

-
VMWDB02\PROD1

(1 rows affected)


1> sp_dropserver 'WIN-QITD52CONSC\PROD1'
2> GO


1> sp_addserver 'VMWDB02\PROD1',local
2> go


1> exit

 

Followed by a restart of SQL Service

https://msdn.microsoft.com/en-us/library/ms345235.aspx

Configure ODBC Data Source for Oracle Client

 

Most easiest: Using EZConnect:

image

Standard: Using TNSNAMES.ORA:

image

Wednesday, May 13, 2015

Data export/import is the only way in Oracle 12c to change the characterset from AL32UTF8 to WE8ISO8859P1

SQL>  select property_value from database_properties where property_name='NLS_CHARACTERSET';

PROPERTY_VALUE
--------------------------------------------------------------------------------
AL32UTF8


SQL> select value from nls_database_parameters where parameter='NLS_CHARACTERSET';

VALUE
--------------------------------------------------------------------------------
AL32UTF8


SQL> alter database character set INTERNAL_CONVERT WE8ISO8859P1;
alter database character set INTERNAL_CONVERT WE8ISO8859P1
*
ERROR at line 1:
ORA-12712: new character set must be a superset of old character set

Wednesday, May 6, 2015

Useful WMIC Queries

To execute these queries, run “WMIC” at a command prompt, followed by one of the following alias/es:
baseboard
get Manufacturer, Model, Name, PartNumber, slotlayout, serialnumber, poweredon
bios
get name, version, serialnumber
bootconfig
get BootDirectory, Caption, TempDirectory, Lastdrive
cdrom
get Name, Drive, Volumename
computersystem
get Name, domain, Manufacturer, Model, NumberofProcessors, PrimaryOwnerName,Username, Roles, totalphysicalmemory /format:list
cpu
get Name, Caption, MaxClockSpeed, DeviceID, status
datafile
where name='c:\\boot.ini' get Archive, FileSize, FileType, InstallDate, Readable, Writeable, System, Version
dcomapp
get Name, AppID /format:list
desktop
get Name, ScreenSaverExecutable, ScreenSaverActive, Wallpaper /format:list
desktopmonitor
get screenheight, screenwidth
diskdrive
get Name, Manufacturer, Model, InterfaceType, MediaLoaded, MediaType
diskquota
get User, Warninglimit, DiskSpaceUsed, QuotaVolume
environment
get Description, VariableValue
fsdir
where name='c:\\windows' get Archive, CreationDate, LastModified, Readable, Writeable, System, Hidden, Status
group
get Caption, InstallDate, LocalAccount, Domain, SID, Status
idecontroller
get Name, Manufacturer, DeviceID, Status
irq
get Name, Status
job
get Name, Owner, DaysOfMonth, DaysOfWeek, ElapsedTime, JobStatus, StartTime, Status
loadorder
get Name, DriverEnabled, GroupOrder, Status
logicaldisk
get Name, Compressed, Description, DriveType, FileSystem, FreeSpace, SupportsDiskQuotas, VolumeDirty, VolumeName
memcache
get Name, BlockSize, Purpose, MaxCacheSize, Status
memlogical
get AvailableVirtualMemory, TotalPageFileSpace, TotalPhysicalMemory, TotalVirtualMemory
memphysical
get Manufacturer, Model, SerialNumber, MaxCapacity, MemoryDevices
netclient
get Caption, Name, Manufacturer, Status
netlogin
get Name, Fullname, ScriptPath, Profile, UserID, NumberOfLogons, PasswordAge, LogonServer, HomeDirectory, PrimaryGroupID
netprotocol
get Caption, Description, GuaranteesSequencing, SupportsBroadcasting, SupportsEncryption, Status
netuse
get Caption, DisplayType, LocalName, Name, ProviderName, Status
nic
get AdapterType, AutoSense, Name, Installed, MACAddress, PNPDeviceID,PowerManagementSupported, Speed, StatusInfo
nicconfig
get MACAddress, DefaultIPGateway, IPAddress, IPSubnet, DNSHostName, DNSDomain
nicconfig
get MACAddress, IPAddress, DHCPEnabled, DHCPLeaseExpires, DHCPLeaseObtained, DHCPServer
nicconfig
get MACAddress, IPAddress, DNSHostName, DNSDomain, DNSDomainSuffixSearchOrder, DNSEnabledForWINSResolution, DNSServerSearchOrder
nicconfig
get MACAddress, IPAddress, WINSPrimaryServer, WINSSecondaryServer, WINSEnableLMHostsLookup, WINSHostLookupFile
ntdomain
get Caption, ClientSiteName, DomainControllerAddress, DomainControllerName, Roles, Status
ntevent
where (LogFile='system' and SourceName='W32Time') get Message, TimeGenerated
ntevent
where (LogFile='system' and SourceName='W32Time' and Message like '%timesource%') get Message, TimeGenerated
ntevent
where (LogFile='system' and SourceName='W32Time' and EventCode!='29') get TimeGenerated, EventCode, Message
onboarddevice
get Description, DeviceType, Enabled, Status
os
get Version, Caption, CountryCode, CSName, Description, InstallDate, SerialNumber, ServicePackMajorVersion, WindowsDirectory /format:list
os
get CurrentTimeZone, FreePhysicalMemory, FreeVirtualMemory, LastBootUpTime, NumberofProcesses, NumberofUsers, Organization, RegisteredUser, Status
pagefile
get Caption, CurrentUsage, Status, TempPageFile
pagefileset
get Name, InitialSize, MaximumSize
partition
get Caption, Size, PrimaryPartition, Status, Type
printer
get DeviceID, DriverName, Hidden, Name, PortName, PowerManagementSupported, PrintJobDataType, VerticalResolution, Horizontalresolution
printjob
get Description, Document, ElapsedTime, HostPrintQueue, JobID, JobStatus, Name, Notify, Owner, TimeSubmitted, TotalPages
process
get Caption, CommandLine, Handle, HandleCount, PageFaults, PageFileUsage, PArentProcessId, ProcessId, ThreadCount
product
get Description, InstallDate, Name, Vendor, Version
qfe
get description, FixComments, HotFixID, InstalledBy, InstalledOn, ServicePackInEffect
quotasetting
get Caption, DefaultLimit, Description, DefaultWarningLimit, SettingID, State
recoveros
get AutoReboot, DebugFilePath, WriteDebugInfo, WriteToSystemLog
Registry
get CurrentSize, MaximumSize, ProposedSize, Status
scsicontroller
get Caption, DeviceID, Manufacturer, PNPDeviceID
server
get ErrorsAccessPermissions, ErrorsGrantedAccess, ErrorsLogon, ErrorsSystem, FilesOpen, FileDirectorySearches
service
get Name, Caption, State, ServiceType, StartMode, pathname
share
get name, path, status
sounddev
get Caption, DeviceID, PNPDeviceID, Manufacturer, status
startup
get Caption, Location, Command
sysaccount
get Caption, Domain, Name, SID, SIDType, Status
sysdriver
get Caption, Name, PathName, ServiceType, State, Status
systemenclosure
get Caption, Height, Depth, Manufacturer, Model, SMBIOSAssetTag, AudibleAlarm, SecurityStatus, SecurityBreach, PoweredOn, NumberOfPowerCords
systemslot
get Number, SlotDesignation, Status, SupportsHotPlug, Version, CurrentUsage, ConnectorPinout
tapedrive
get Name, Capabilities, Compression, Description, MediaType, NeedsCleaning, Status, StatusInfo
timezone
get Caption, Bias, DaylightBias, DaylightName, StandardName
useraccount
get AccountType, Description, Domain, Disabled, LocalAccount, Lockout, PasswordChangeable, PasswordExpires, PasswordRequired, SID
memorychip
get BankLabel, Capacity, Caption, CreationClassName, DataWidth, Description, Devicelocator, FormFactor, HotSwappable, InstallDate, InterleaveDataDepth, InterleavePosition, Manufacturer, MemoryType, Model, Name, OtherIdentifyingInfo, PartNumber, PositionInRow, PoweredOn, Removable, Replaceable, SerialNumber, SKU, Speed, Status, Tag, TotalWidth, TypeDetail, Version



Example: 
C:\Users\donghua>wmic /output:c:\test3.txt useraccount list brief /format:table

Troubleshooting SQL Server mirroring message

Symptom:

Error: 1443, Severity: 16, State: 2.
Database mirroring connection error 4 'An error occurred while receiving data: '10054(An existing connection was forcibly closed by the remote host.)'.' for 'TCP://vmmdb01:5023'.

Database Mirroring login attempt by user 'NT Service\MSSQL$PROD.' failed with error: 'Connection handshake failed. The login 'NT Service\MSSQL$PROD' does not have CONNECT permission on the endpoint. State 84.'.  [CLIENT: 2001:0:5ef5:79fb:2839:2416:f5ff:fdf0]

How to fix: 

Grant permission to all remote SQL instance connecting users to current instance DB Mirror End point

USE [master]
GO
CREATE LOGIN [NT SERVICE\MSSQL$PROD] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO

GRANT CONNECT ON ENDPOINT::[Mirroring] TO [NT SERVICE\MSSQL$PROD2]; 

How to verify:

SELECT e.name as mirror_endpoint_name, s.name AS login_name
, p.permission_name, p.state_desc as permission_state, e.state_desc endpoint_state
FROM sys.server_permissions p
INNER JOIN sys.endpoints e ON p.major_id = e.endpoint_id
INNER JOIN sys.server_principals s ON p.grantee_principal_id = s.principal_id
WHERE p.class_desc = 'ENDPOINT' AND e.type_desc = 'DATABASE_MIRRORING'