Saturday, May 14, 2022

Windows commands line cheat sheet for DBAs

This image has an empty alt attribute; its file name is image.png
Intro

We all know Windows is the preferred platform for Oracle DBAs !! I hear that every day :D. 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 uptime 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 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 specially 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 & 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 right side of the current locale date, MM

  • %date:~-7,2% : the seventh and sixth characters from 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 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 a your advanced system properties opened as 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 addit to the task scheduler for a nightly run (10PM)

    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.

    Thanks for reading


    Friday, April 29, 2022

    Hashicorp Terraform Associate certification takeaways

    Intro


    The thing we usually forget when completing a certification is to save a mental image of the topics that were exciting to learn or the concepts that we had no clue about right before starting the preparation. This is what practitioners should focus on after every major Cloud cert exam, as it provides an accurate snapshot of your feeling about the certification material. Therefore, when I was asked about my impressions on the Terraform Associate Certification exam, I decided to share my thoughts in my blog around the prep, and my feedback on the course I followed.


    After timidly starting my "terraform for dummies" blog series, one cloud at a time (OCI, AWS, GCP, Azure) and even On-Prem, It didn't make sense not to take this exam and I am glad It finally happened.
    Hashicorp is an exciting company & their disruptive vision is a game changer in a world where automation is King.

     

    The exam summary 

    Table of contents

    All you need to know about the exam can be found in the official Page. But bellow is what to expect in a nutshell:

    Length: 1 hours.

    Questions:  57

    Exam format: Multiple choice and multiple select

    Recommended experience:  Hands-on experience on deployments on any major Cloud platform.

    Pass/actual Score: 70%.

    Score per topic: Available and broke down into 9 content area.

    Terraform version: 0.12 and higher.

    Exam center:  PSI Online (requires GitHub account)

    Retake Policy: 7 days between exam failed attempts. Max 4 exam attempts in total in a one year period.

    Pass confirmation: Results and badge in less than 1 hour.

    Preparation time: Few weekends if you have already deployed with terraform (videos+ labs + practice exams) .

    Impressions 

    It may feel intimidating at first for a tech that’s still young but just getting familiar with the syntax and trying few deployment samples in AWS can help getting into it. Now of course, I didn’t really start from scratch considering all my deployments shenanigans in 4 major cloud providers, but I was still far from having what it takes to pass. Above all, what really surprised me when starting my prep journey is how little I knew about the extent of their services and features available in the OSS version of terraform. Those were exciting bits that I can’t wait to use, like remote backends, Terraform Cloud along with modules that I hate less now :). More about it in the Last tip section.

    By the way, If you’re too broke for ACloudGuru, jump right to 100% FREE section 

    Exam guide | Official material

    Again the content is available in the official hashcorp certification page including the study guide and exam review. It is broken down into 9 areas. I have put Hashicorp Doc/tutorial link for each sub section respectively.

      What I liked about it

      Believe it or not, I completely missed that Hashicorp had lab tutorials for each topic in their study guide. That’s so cool because you get to practice in a timed sandbox just by following their instructions and all for free. It’s too late for me, but I wanted to emphasize on Hashicorp’s efforts here, as labs are very important to prepare for this exam. 


      I know these are a lot of things to cover, but just keep in mind that at the end of the day the key workflow is:
      Write –> terraform Plan –> terraform Apply> Terraform Destroy


      Courses I followed

      As usual first thing people do is shop online for the best training material and possibly free. But in my case I took advantage of already available course in my CloudGuru Subscription.  


      I. A Cloud Guru:
      HashiCorp Certified Terraform Associate

      This required paying a yearly subscription for which I thank my Company for sponsoring this learning path.This image has an empty alt attribute; its file name is image-11.png

      • Pros
        • This is by far the best learning platform I ever tried, not just for preparing certs but also to follow tailored paths where you can learn by practicing through 100s of labs available
        • Moosa Khalid was very effective making the course interesting & easy to follow without unnecessary frills 
        • The themed labs in this course are available at the end of each section
        • It also has an exam practice which can be very handy
        • Modules , variable types (Collections,complex types), and concepts like vault were very well covered 
      • Cons
        • Although most topics, labs did the job, I found that some points still lacked detailed/necessary coverage 
        • Missing examples on attributes for init subcommand  like –backend or out
        • Missing different Ways to configure a remote Backend depending on the chosen source
        • Missing different ways to call modules
      • Pro Tip : skip the second half of the labs and do them all together when finishing with the theory(videos)     

      100% Free resources  


      I. Youtube

      1. HashiCorp Terraform Associate Certification Course - Pass the Exam! By @AndrewBrown


      Content:
      It’s 12 hours long and was dropped few months ago but still valid. Andrew is very popular in the Cloud community as he has a lot of free courses in YouTube beside his learning platform in examPro . I didn’t follow this one but he never ceases to deliver , as I followed a couple of his free courses in the past.

      1. Learn Terraform by Building a Dev Environment – Full Course for Beginners By @Derek Morgan

        Content:

        This will not meant to answer all the exam questions but will rather help you get the necessary skills to get familiar with terraform deployments in AWS. That is why it is only 1 hour and half long. I didn’t need it but it’s a good start before diving into the exam theory concepts.


        II. Exam practice/ Articles 

         There are many but the only one that stood out for my is the following medium blog post by Bhargav Bachina 

        III. My notes and labs

        This is basically a mix of  Bhargav’s Sample questions with tips I gathered during my learning from ACG/other . This was more than enough to confidently pass teh exam. I still use it today to refresh my memory on terraform options and tricky commands. 

        Link: BrokeDBA Bundles Notes on Hashicorp terraform associate exam

        Pro-Tip:
        Another way to learn about terraform in any cloud platform is to read my Terraform for dummies Blog Series.


        Last tip (goodies)

        The online courses won’t help clear all the exam questions, so you better train a bit more. The last contribution I wanted to share is few tricks/notes on things I learned that I had no clue about before preparing the exam

        • Terraform init options
          $ terraform init -backend=false     -- skip backend
          $ terraform init -get=false         -- skip modules
          $ terraform init -get-plugins=false -- skip plugins
        • Module location: terraform/modules/
        • Syntax for referencing a registry module: <NAMESPACE>/<NAME>/<PROVIDER>
          module "consul" {
             source = "hashicorp/consul/aws"
             version = "0.1.0"
          }
        • Syntax for referencing a private registry module : <HOSTNAME>/<Org-NAME>/<NAME>/<PROVIDER>
          module "vpc" {
             source = "app.terraform.io/My_corp/vpc/aws" –> 
             version = "0.9.3"
          }
        • Preview the behavior of the command terraform destroy
          $ terraform plan –destroy
        • Save the execution plan
          $ terraform plan -out=filename
        • Manually source module into your working directory
          $ terraform init -from-module=MODULE-SOURCE
        • target only specific resources when you run a terraform plan/destroy
          -target=resource
        •  Update the state prior to checking differences when you run a terraform plan
          $ terraform plan -refresh=true
        • Provide remaining arguments when using partial configuration
          - Interactively:
          - File: terraform init -backend-config=PATH
          - Command-line key/pair: terraform -backend-config="KEY=VALUE"
           

        Conclusion


        As I said It was long overdue but I am very glad I finally passed terraform associate exam. It is a very exciting realm and I can’t wait learn even more each day. I hope this blog post will help those who are interested in taking a step forward to start their preparation journey as I did few months ago. Best of luck !! 

        Automation is here today I tell ya :).


        Thank you for reading