Monday, May 25, 2015

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'


Thursday, April 30, 2015

Configure Windows 2012 Server network to join domain using netsh scripting

C:\Users\Administrator>netsh interface ip show config

Configuration for interface "Ethernet0"
    DHCP enabled:                         Yes
    IP Address:                           192.168.229.134
    Subnet Prefix:                        192.168.229.0/24 (mask 255.255.255.0)
    Default Gateway:                      192.168.229.2
    Gateway Metric:                       0
    InterfaceMetric:                      10
    DNS servers configured through DHCP:  192.168.229.2
    Register with which suffix:           Primary only
    WINS servers configured through DHCP: 192.168.229.2

Configuration for interface "Loopback Pseudo-Interface 1"
    DHCP enabled:                         No
    IP Address:                           127.0.0.1
    Subnet Prefix:                        127.0.0.0/8 (mask 255.0.0.0)
    InterfaceMetric:                      50
    Statically Configured DNS Servers:    None
    Register with which suffix:           Primary only
    Statically Configured WINS Servers:   None

C:\Users\Administrator>netsh interface ipv4 set address "Ethernet0" static 192.168.229.210 255.255.255.0 192.168.229.200 1


C:\Users\Administrator>netsh interface ipv4 set dnsserver "Ethernet0" static 192.168.229.200 primary


C:\Users\Administrator>netsh interface ip show config

Configuration for interface "Ethernet0"
    DHCP enabled:                         No
    IP Address:                           192.168.229.210
    Subnet Prefix:                        192.168.229.0/24 (mask 255.255.255.0)
    Default Gateway:                      192.168.229.200
    Gateway Metric:                       1
    InterfaceMetric:                      10
    Statically Configured DNS Servers:    192.168.229.200
    Register with which suffix:           Primary only
    Statically Configured WINS Servers:   None

Configuration for interface "Loopback Pseudo-Interface 1"
    DHCP enabled:                         No
    IP Address:                           127.0.0.1
    Subnet Prefix:                        127.0.0.0/8 (mask 255.0.0.0)
    InterfaceMetric:                      50
    Statically Configured DNS Servers:    None
    Register with which suffix:           Primary only
    Statically Configured WINS Servers:   None

C:\Users\Administrator>netdom renamecomputer %computername% /newname:VMMSQL03
This operation will rename the computer WIN-7FVEEOP8PPF
to VMMSQL03.

Certain services, such as the Certificate Authority, rely on a fixed machine
name. If any services of this type are running on WIN-7FVEEOP8PPF,
then a computer name change would have an adverse impact.

Do you want to proceed (Y or N)?
y
The computer needs to be restarted in order to complete the operation.

The command completed successfully.


C:\Users\Administrator>shutdown /r /t 0

C:\Users\Administrator>netdom join VMMSQL03 /domain:dbaglobe.com /ud:dbaglobe\administrator /pd:*
Type the password associated with the domain user:

The computer needs to be restarted in order to complete the operation.

The command completed successfully.

C:\Users\Administrator>shutdown /r /t 0 /f

Saturday, April 18, 2015

Windows Server 2012 R2 prevent automatic logoff due to inactivity

Unlocked the missing Power Settings feature in Server 2012.

  1. Open the following registry key -HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Power\PowerSettings\7516b95f-f776-4464-8c53-06167f40cc99\8EC4B3A5-6868-48c2-BE75-4F3044BE88A7

  2. Set the following value - Attributes => 2

  3. Now open Control Panel>Power Options>Change Plan Settings>Change Advanced Power Settings
    a. The new Display section Console lock display off timeout is now available.
    b. Configure your “Plugged in” value accordingly (0 to disable)

  4. Set timeout to 0 to turn it off.

enter image description here