Blogs

Windows Commands Line Cheatsheet for DBAs

Intro

We all know Windows is the preferred platform for Oracle Database Administrators! I hear that every day. All right, maybe not so much… But jokes aside, when your client has the majority of his DB workload running on Windows, it’s hard to dodge the job. It becomes even more challenging to switch to GUI clicks when coming from Linux world. That’s why I had to google a lot along my years working on non Unix shops. After a while, I wound up curating a bunch of useful tips through the years and kept them in a text file, however I think a web page is a way better place to navigate than a raw text in a notepad anytime a quick help is needed.

This list won’t have a specific structure but I’ll try to keep it coherent.

 

Basic Windows Checks

 

Check OS Versions

C:\>winver 
  -- A Window will appear

C:\>systeminfo | findstr OS
 OS Name:                   Microsoft Windows Server 2012 Standard
 OS Version:                6.2.9200 N/A Build 9200
 OS Manufacturer:           Microsoft Corporation
 OS Configuration:          Member Server
 OS Build Type:             Multiprocessor Free
BIOS Version:              Phoenix Technologies LTD 6.00, 9/21/2015

 

Check Pptime and last boot

Choose from following options 

C:\> systeminfo | find "System Boot Time"
System Boot Time:          2022-05-04, 8:59:32 PM

C:\> NET STATS srv
Statistics since 2022-05-04 8:59:55 PM

C:\> wmic OS GET CSName,LastBootUpTime
CSName   LastBootUpTime
BROKDBA  20220504205932.600144-240

 

Check Service Uptime

Extract the service PID from the Service Name

C:\> sc queryex MSSQLSERVER 

SERVICE_NAME: MSSQLSERVER 
        TYPE              : 10 WIN32_OWN_PROCESS 
        STATE             : 4 RUNNING 
                              (STOPPABLE, PAUSABLE, ACCEPTS_SHUTDOWN) 
        WIN32_EXIT_CODE   : 0 (0x0) 
        SERVICE_EXIT_CODE : 0 (0x0) 
        CHECKPOINT        : 0x0 
        WAIT_HINT         : 0x0 
        PID               : 5068 ----> Copy PID 
        FLAGS             :

 

Check the Uptime in PowerShell

PS C:\> Get-Process | select name, id, starttime | select-string 5068
  @{Name=sqlservr; Id=5068; StartTime=11/24/2020 09:46:20}

 

Check CPUs

-- Dos Command line
C:\> wmic CPU Get NumberOfCores,NumberOfLogicalProcessors /Format:List

NumberOfCores=6 NumberOfLogicalProcessors=12
-- Powershell

PS C:\> WmiObject -class win32_processor -Property Name, NumberOfCores, NumberOfLogicalProcessors | Format-List -Property Name, NumberOfCores, NumberOfLogicalProcessors

Name                      : Intel(R) Core(TM) i7-9750H CPU @ 2.60GHz
NumberOfCores             : 6
NumberOfLogicalProcessors : 12

 

Check running Services

PS C:\>  net start 
...
OracleOraDB19Home1TNSListener
...
Windows Search
Windows Update
..

 

Manage Services

--- Start stop
C:\>  net start/stop "SERVICE-NAME" 
--- Delete a Service 
C:\> SC DELETE "SERVICE-NAME"

 

User Management

Show current user

PS C:\> whoami
Domain\myUser

List user group members

PS C:\> NET LOCALGROUP ORA_DBA
Alias name     ORA_DBA
Comment        Oracle DBA Group
Members
------------------------------------- 
DOMAIN\dbadmin
DOMAIN\User2
NT AUTHORITY\SYSTEM 

Add new user

Syntax: net user username password /ADD /DOMAIN

C:\> net user Oracle * /add /MyDomain

Type a password for the user: ***** Retype the password to confirm: ****
The command completed successfully.
-- Change password

C:\> net user Oracle *
Type a password for the user:
Retype the password to confirm: 

list all processes in Windows

Choose from the following options 

C:\> tasklist
--- Via WMI ( more details)
C:\> wmic process
--- PowerShell
PS C:\>  get-process 

 

Tail -f in PowerShell

PS C:\>  Get-Content -Path "C:\APP\ORACLE\diag\rdbms\PATH\trace\alert_MYDB.log" -Wait

 

Storage 

 

Check System Block Size

C:\> fsutil fsinfo ntfsinfo D:

NTFS Volume Serial Number :                           
Version :                                                                             
Bytes Per Sector  :               512 
Bytes Per Physical Sector :       <Not Supported> 
Bytes Per Cluster :               4096      -------- 4K /64K etc.. 
Bytes Per FileRecord Segment    : 1024 
Clusters Per FileRecord Segment:  

 

Check (Shared) Drive Space Usage

This is especially useful to check shared drive overall usage as you can’t see the info through the explorer

C:\> fsutil volume diskfree D:
Total # of free bytes        : 37252968448
Total # of bytes             : 858856091648
Total # of avail free bytes  : 37252968448 --> 34.69GB free

There is simple script check_freespace.bat you can use to get the result in GB units

C:\> check_freespace.bat \\RemoteServer\S01$
Total # of free bytes        : 2846.80 GB
Total # of bytes             : 3311.81 GB
Total # of avail free bytes  : 2846.80 GB

 

Map/Unmap Network Drives

 Workstations/servers might have network drives attached to them. Here are ways to add and delete the mapping

1. Map shared drive to Z:
C:\> net use Z: \\StorageServer\Drive$ /user:MyUser Mypassword /persistent:Yes
2. Delete mapping 
C:\> net use Z: /delete

-- Delete all mappings 
C:\> net use * /delete

 

Miscellaneous  

 

Grep in Windows

File content or command output filtering. with below Metacharacters : 
‘.’ any character  
‘*’ zero or more occurrences of previous character or class

C:\> FINDSTR /i /r /c:"^ORA-600*" alert_MyDB.log 
-- Using PowerShell PS C:\> Select-String "^ORA-1146" --- ls | grep equivalent C:\>  DIR /B | findstr /i /r /c:"alert.*log"
alert_MyDB.log

 

File Naming and Variable Substrings

Get a substring of a string of a variable from position X in string with Y characters length.%Variable:~X,Y%:

C:\> SET VAR=FOOBARFOOBA
C:\> ECHO %VAR:~3,8%
     BARFOOBA

 

Timestamp a logfile (tested in Windows server)

Example: db_bkp_YYYYMMDD_HHMMSS.log

ECHO db_bkp_%date:~-4,4%%date:~-10,2%%date:~-7,2%_%time:~0,2%%time:~3,2%%time:~6,2%.log
C:\>  db_bkp_20220514_134656.log    

 

Date Section Filter Description

• %date:~-4,4%: the last 4 characters of the current locale date which is obviously the year, YYYY

• %date:~-10,2%: the tenth and ninth characters from the right side of the current locale date, MM

• %date:~-7,2%: the seventh and sixth characters from the right side of the current locale date, the day, DD

• %time:~0,2%: the first 2 characters of the current locale time which is the hour, HH

• %time:~3,2%: the fourth and fifth character of the current locale time which is the minute, Min

• %time:~6,2% : the seventh and eighth caractere of the current local time which is the seconds,SS
  

 

Kill Process

You can either use the PID or the full name of the process. Both can be extracted through Tasklist Command

taskkill /F /PID 11764
taskkill /IM "SocketHeciServer.exe" /F

 

Time Zone

Check and set the time zone in your machine

--- Get
tzutil /g
Eastern Standard Time
--- list 
tzutil /l
--- Set
tzutil /s "Mountain Standard Time"

 

File System Permissions

 List and set privileges on a file or directory

C:\> cacls D:\oracle
D:\oracle BUILTIN\Administrators:(OI)(CI)F
          NT AUTHORITY\SYSTEM:(OI)(CI)F
          Server\ORA_OraDB19Home1_SVCACCTS:(OI)(CI)F
          Domain\User1:(OxI)(CI)F

 

Inherited folder permissions are given as:

• OI – Object inherit – This folder and files. (no inheritance to subfolders)

• CI – Container inherit – This folder and subfolders.

• IO – Inherit only – The ACE does not apply to the current file/directory

F: Full Control

 

Change permission to a File to the current user

C:\> icacls .\private.key /inheritance:r /grant:r "%username%":"(R)"

 

First attribute will remove all inheritance, the second will grant a new RO permission for the current user to private.key

 

Check/Kill connected RDP Sessions

 List the sessions from a Windows server command line not from your Workstation

C:\> query session /server:RemoteServer
SESSIONNAME       USERNAME                 ID  STATE   TYPE        DEVICE
services                                    0  Disc
console                                     1  Conn
rdp-tcp#1 RemoteUser 2 Active rdpwd
rdp-tcp                                 65536  Listen

 

Kill remote RDP Session (ID: 2)

C:\> reset session 2 /server:RemoteServer

 

Note: You can also check remote sessions connected to the local server using qwinsta command

 

Open Environment Variable Window (as Admin)

You can either use the RunasAdmin or a direct command to get your advanced system properties opened as a system

C:\> RunAs.exe /user:Administrator "rundll32 sysdm.cpl,EditEnvironmentVariables"
Enter the password for Administrator: ***

--- Option 2 direct access as admin:
C:\> SystemPropertiesAdvanced
Note: Once the window is displayed –> click environment variables

 

Scheduler via Command Line

In this example, we will create a task that runs a cleanup script and add it to the task scheduler for a nightly run (10 PM)

Script : archive_cleanup.ps1
1. Define the trigger, user, and action
$Trigger= New-ScheduledTaskTrigger -At 10:00pm –Daily 
$User= "NT AUTHORITY\SYSTEM" 
$Action= New-ScheduledTaskAction -Execute "PowerShell.exe" -Argument "-ExecutionPolicy Bypass -File J:\archive_cleanup.ps1" 

2. Create and specify the name of the task
Register-ScheduledTask -TaskName "CleanupDBarchives" -Trigger $Trigger -User $User -Action $Action -RunLevel Highest –Force 

 

Conclusion

• These entries have accompanied my day to day work on Windows servers for quite sometime

• It might become redundant when you regularly look for the same thing in Google without remembering the Syntax

• This will finally replace my messy text file, so I won’t waste time looking for quick Command-Line tricks on Windows

• The goal was to get this accessible to me in the first place but I hope it will be a good reference for those non GUI fans, who are usually allergic to NT OS.

More from this Author