Tuesday, August 2, 2022

Explore Alibaba Cloud part 2: AliCloud CLI installation and few examples

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

Cloud CLI tools are the Most direct and simple way to interact with a new Cloud platform, It is the perfect tool for executing simple and repeatable tasks which you don’t need to keep track off. Alibaba Cloud CLI tool which also manages Cloud resources (or stacks) is no exception. In this blog post, we will install AliCloud cli and try few API requests. I do that every time I explore a cloud platform (see previous posts aws, oci, azure, gcp).

As usual, this post will be followed by another article about provisioning a vm in Ali Cloud through terraform.


Whether on windows or on Linux the basic installation of Aliyun CLI will always require 2 elements:

I. Alibaba Cloud CLI Installation

Aliyun (translated “Ali Cloud”) was the original name of the company until 2017. Therefore you will install Aliyun CLI not Alibaba Cloud cli ;).

  • Windows

       1- Download & execute the following AzliyunCli installer(current version: v3.0.123) from

            Alibaba Cloud website (latest zipped version)

            GitHub(aliyun/aliyun-cli) (different version available)

       2- Unzip the downloaded file to get the executable file named aliyun.exe

       3- Add the exe file location directory path to your User %PATH% environment variable via GUI or cmd terminal

    -- Option 1: from the GUI
    C:\> SystemPropertiesAdvanced

    Note: Once the window is displayed –> click environment variables

    --- Option 2: using pathed

    C:\> PathEd.exe add "C:\DATA\cloud\alibaba

    C:\> aliyun version

     Note: I used the handy pathed tool to append the %PATH% environment variable permanently

  • Linux
    Download the installation package for Linux using curl , decompress it and copy the content to the user bin directory.
  • brokedba~$ curl -sL https://github.com/aliyun/aliyun-cli/releases/download/v3.0.123/aliyun-cli-linux-3.0.123-amd64.tgz | sudo tar xzC /usr/local/bin
    brokedba~$ aliyun version

II. Authenticate to Alibaba Cloud from Aliyun CLI

Once your
Alibaba Cloud Free Tier account is created and aliyun cli installed. You will need API credentials including region, and language. There is a list of credentials type available to use in order to authenticate with.
 This image has an empty alt attribute; its file name is image-6.png

The 2 categories are:  1. Key/StsToken based        2. Role/Instance principal based

Obtain your Access key
We will choose AccessKey authentication for our example. 

  1. Hover onto the profile picture in the upper-right corner, and click AccessKey management.  

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

  2. On Access Key pair section click create AccessKey to generate AccessKey ID /AccessKey and copy them.

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

  3. Now that we obtained our key pair we can finally configure our aliyun CLI profile (find all region ids here)

    1. Interactive Configuration
    brokedba~$ aliyun configure --mode AK -–profile
    Configuring profile 'default' in '' authenticate mode...
    Access Key Id []: AccessKey ID
    Access Key Secret []: AccessKey Secret
    Default Region Id []: us-east-1
    Default Output Format [json]: json (Only support json)
    Default Language [zh|en] en: Saving profile[default] ...Done.

    Configure Done!!!
    ..............888888888888888888888 ........=8888888888888888888D=..............
    ...........88888888888888888888888 ..........D8888888888888888888888I...........
    .........,8888888888888ZI: ...........................=Z88D8888888888D..........
    .........+88888888 ..........................................88888888D..........
    .........+88888888 .......Welcome to use Alibaba Cloud.......O8888888D..........
    .........+88888888 ............. ************* ..............O8888888D..........
    .........+88888888 .... Command Line Interface(Reloaded) ....O8888888D..........
    ..........D888888888888DO+. ..........................?ND888888888888D..........
    ............ .:D8888888888888888888.........78888888888888888888O ..............

    2. static configuration

    brokedba~$ aliyun configure set \ --profile akProfile \ --mode AK \ --region cn-hangzhou \ --access-key-id AccessKeyID \ --access-key-secret AccessKeySecret

        • We can run aliyun configure command to verify our new configuration   
        • $ aliyun configure list

          Profile   | Credential      | Valid   | Region  | Language --------- | --------------- | ------- | --------- | -------- default * | AK:****6        | Valid   | us-east-1 | en

        •  aliyun cli configuration is typically stored in the below directory
          $ more $HOME/.aliyun/config.json
          "current": "default",
          "profiles": [
          "name": "default",
          "mode": "AK",
          "access_key_id": xxxxxxxx

            III.Test your first API request

            Few notions worth reminding before hitting the terminal with aliyun requests :    

            A. Command structure: is based on the below components

              $ aliyun <product> <operation> [--parameter1 value1 --parameter2 value2 ...]

            Each followed by  values.It will always depend on the product & operation value in the command.

            • Result related parameters :
              1-  unfortunately aliyun doesn’t support table output format unlike any other CSP (JSON only.Bummer!)
                 2- “--output” : Allows to pick the list of fields to return in the response. It can be used to do some filtering.

            B.  output :

            The --output option allows to specify either a field, rows (using JMESpath), or num(row number).
            best way to learn is to fetch all the output then locate the rows(path) and columns to display.

            This image has an empty alt attribute; its file name is image-7.png 
            To demonstrate the principle, here’s an example based on the ecs regions list command

            --- Original command 
            $ aliyun ecs DescribeRegions | head

            { "Regions": {
            "Region": [ ----> path/rows is Regions.Region[]
            { "LocalName": "华北1(青岛)",
              "RegionEndpoint": "ecs.cn-qingdao.aliyuncs.com",
            "RegionId": "cn-qingdao"

            --- Fileterd output based on RegionId and RegionEndPoint

            $ aliyun ecs DescribeRegions --output cols=RegionId,RegionEndpoint rows=Regions.Region[]

            RegionId       | RegionEndpoint
            --------       | --------------
            us-east-1      | ecs.us-east-1.aliyuncs.com
            us-west-1      | ecs.us-west-1.aliyuncs.com
            eu-west-1      | ecs.eu-west-1.aliyuncs.com
            me-east-1      | ecs.me-east-1.aliyuncs.com
            eu-central-1   | ecs.eu-central-1.aliyuncs.com



            You will have to rely on the help command first because there is no Alyun cli command reference available. This is the first time I see a CSP that doesn’t have a dedicated reference for its CLI tool.
            Needless to say you’re on your own as all I found was this pdf.

            You can get help by using the following commands:

                ○ aliyun help: get product list
                ○ aliyun help <product>: get the API information of a specific product
            $ aliyun Ecs StopInstance help
            Alibaba Cloud Command Line Interface Version 3.0.123
            Product: Ecs (Elastic Compute Service) Parameters: 
            --InstanceId  String  Required 
            --ConfirmStop Boolean Optional 
            --DryRun      Boolean Optional 
            --ForceStop   Boolean Optional 
            --Hibernate   Boolean Optional 
            --StoppedMode String  Optional


          • List of publicly available images in the current default region (us-east-1)

            $ aliyun ecs DescribeImages --output cols=OSNameEn,Architecture,OSType,IsPublic rows=Images.Image[]

            OSNameEn                                            | Architecture | OSType | IsPublic --------                                            | ------------ | ------ | --------
            CentOS  7.9 64 bit for SCC                          | x86_64       | linux  | true
            CentOS  8.4 64 bit for SCC                          | x86_64       | linux  | true
            AlmaLinux  9.0 64 bit                               | x86_64       | linux  | true
            Ubuntu  22.04 64 bit                                | x86_64       | linux  | true
            CentOS Stream  9 64 bit                             | x86_64       | linux  | true
            Fedora  35 64 bit                                   | x86_64       | linux  | true
            Alibaba Cloud Linux  3.2104 LTS 64 bit UEFI Edition | x86_64       | linux  | true Alibaba Cloud Linux  3.2104 LTS 64 bit ARM Edition  | arm64        | linux  | true Alibaba Cloud Linux  3.2104 LTS 64 bit              | x86_64       | linux  | true

          • Object storage tool:

          • Download and install ossutil

          • $ wget http://gosspublic.alicdn.com/ossutil/1.7.7/ossutil64 
            $ chmod 755 ossutil64
            ./ossutil64 config
          • Create a private bucket with Zonal redundancy in us-east-1 region

          • $ ./ossutil64 mb oss://brokedbabucket -e oss-us-east-1.aliyuncs.com --acl private --storage-class IA --redundancy-type ZRS

          Auto completion

            This commands is supposed to command to enable auto completion in zsh/bash . But it doesn’t autocomplete operation commands as available in aws cli for example. 
            $ aliyun auto-completion 


          In this tutorial we learned how to install and configure Aliyun cli. We also described the command syntax and tried few requests using output options.

          ○The filtering seem to be very limited as there are no JMESPATH examples anywhere associated with rows option

          ○ I wish there was a command line reference that could provide more help to end users

          ○ FREE tier: You will still have to confirm you can use your Free tier credits before deploying any resource. 


          Monday, July 18, 2022

          ZDM installation error in Oracle Linux 8 (Failed to initialize MySQL) & how to fix it

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


          Zero downtime migration (ZDM) is one of the best option available to migrate your Oracle database to Oracle Cloud. Automation and simplicity are among its strong benefits beside zero downtime feature. Although migrating in OCI is the classic use case(see Oracle migration & integration specialist certification), I had the chance to try a different scenario lately, where the path was On-Prem Linux to Oracle Exadata at Customer.

          In this blog post, I’ll describe the blocking issue I had while installing ZDM io an On-prem VM, and provide the solution.

          1. My ZDM environment


          Although, an SR was already opened for this issue and Oracle dev team seemed to make it work in a similar environment. I could still not get ZDM to be installed in my vm, hence this blog post to explain both the error causing MySQL initialization failure, and the workaround.


          OS Oracle Linux 8.4 kernel 5.4.17-2102.201.3.el8uek.x86_64

          File system: /dev/mapper/vg01-lvol1 on /u01 type ext4 (rw,relatime,seclabel)

          MySQL server 8.0.22

          ZDM 21.3 build

          [zdmuser@zdmserver ~]$ uname -a
          Linux zdmserver 5.4.17-2102.201.3.el8uek.x86_64 [zdmuser@zdmserver ~]$ cat /etc/redhat-release

          Red Hat Enterprise Linux release 8.4 (Ootpa)


          After downloading the ZDM installable Zip and extracting it, I have created necessary directories for the installation

          export INVENTORY_LOCATION=/u01/app/oraInventory
          export ORACLE_BASE=/u01/app/oracle
          export ZDM_BASE=/u01/app/oracle/zdmbase ----> ZDM config files, logs
          export ZDM_HOME=/u01/app/oracle/zdmbase/zdm21 ----> ZDM software binaries
          export ZDM_INSTALL_LOC=/u01/zdm21-inst ----> ZDM installable

          - Create directories

          [zdmuser@zdmserver]$ mkdir -p $ORACLE_BASE $ZDM_BASE $ZDM_HOME $ZDM_INSTALL_LOC

          Steps to Reproduce the error

          All I had to do is to run the install script with the required arguments (directories) to reproduce the behaviour.

          $ ./zdminstall.sh setup oraclehome=$ZDM_HOME oraclebase=$ZDM_BASE \
          ziploc=./zdm_home.zip -zdm

          Failure to initialize MySQL

          • The error as you can see happened at MySQL configuration stage and lacks any relevant description

          Setting up MySQL...
          Failed to initialize MySQL
          Failed to initialize MySQL
          One or more errors occurred while setting up No GI RHP.
          Trying to stop MySQL in case it was started and left up.
          spawn /u01/app/oracle/zdm21/mysql/server/bin/mysqladmin --defaults-file=/u01/app/oracle/crsdata/velzdm2prm/rhp/conf/my.cnf -u root -p shutdown WARNING: Failed to stop MySQL

          Now, at this point we can either, retry the installation forever, or start digging further.

          Which log to check 

          I had no clue really, but I decided to just hit find  under /u01 and try my luck with “mysql” as filter ;) .

          JACKPOT! here is the winner


          You got it, always look under $ORACLE_BASE/crsdata/myserver/rhp/mysql/metadata for such log 

          The actual error

          Clearly, MySQL creation step failed because temporary files couldn't be created in an ext FS as show below.

          $ more $ORACLE_BASE/zdmbase/crsdata/zdmserver/rhp/mysql/metadata/mysql-error.log
          … 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started. 1 [ERROR] [MY-012128] [InnoDB] posix_fallocate(): Failed to preallocate data
          for file ./#innodb_temp/temp_1.ibt, desired size 81920 Operating system error
          number 22. 1 [ERROR] [MY-012929] [InnoDB] InnoDB Database creation was aborted with error Out of disk space

          What does it really mean ?

          In MySQL8, innoDB files are created using a specific function (like
          dd) called posix_fallocate, which just writes 000s in a disk file to reserve space. See description below

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

          I obviously shared this in the SR, but I wanted to see what MySQL community had to say about it, so I looked it up.

          MySQL Bug or ext dislike

          It didn’t take long before I found the answer in stackoverflow . As explained below it seems that posix_fallocate function isn’t supported by ext file systems.


          In a nutshell, we have two bugs filed related to the same issue

          Solution: replace ext by XFS

          From the above bugs, two solutions were available for our ZDM installation problem.
          1. MySQL8 bug received a patch as a fix from a contributor. Contribution: fix_init_fail_on_ext3.patch

          2. The older bug for MySQL 5.7 had no permanent fix but using XFS got rid of the posix_fallocate bug.

          I decided to go for an XFS disk on /u01, mostly because it was the least intrusive option and particularly after reading Dimitri Kravtchuk piece about MySQL perf regression on ext4 (MySQL Performance : XFS -vs- EXT4 Story) where he recommended to move to XFS for kernels higher than 4.1.

            [root@zdmserver~]# mount|grep u01
            /dev/mapper/vg01-lvol1 on /u01old type ext4 (rw,relatime,seclabel)
            /dev/mapper/vg01-lvol2 on /u01 type xfs (rw,relatime,seclabel,attr2,inode64,logbufs=8,logbsize=32k,noquota)

            • ZDM was successfully installed after a cleanup and reinstall.

            -- Uninstall
            [zdmuser@zdmserver]$ $ZDM_HOME/bin/zdmservice stop deinstall

            -- Re-Install

            $ ./zdminstall.sh setup oraclehome=$ZDM_HOME oraclebase=$ZDM_BASE \
            ziploc=./zdm_home.zip -zdm

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


            This was an issue that had nothing to do with Oracle but allowed me to discover ZDM logs directories and choose the right file system for its MySQL DB. Hope this will help anyone who runs into the same error.    

                    Thank you for reading

            Saturday, June 25, 2022

            Explore Alibaba Cloud part 1: Intro to Alibaba Cloud

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


            I always believed in the horizontal approach to learn the cloud rather than the vertical method. I’m just convinced there is more to learn when you open your perspectives, even if you lose the ability to narrow your expertise on one platform. That’s a little price to pay if you want to broaden your lenses. This is why after exploring AWS, Azure, OCI, & GCP, I decided to sneak into Alibaba Cloud.

            In this blog post, I will introduce you to Alibaba Cloud through a little tour of the provider’s interface, available offering along with billing options. I’m hoping to find helpful insights on the user experience while navigating this platform based on my multi-cloud background. All while staying fair and unbiased so we can solely focus on the technical aspect (geopolitical considerations aside) .

            Table of contents

            Is Alibaba Cloud a thing :)?This image has an empty alt attribute; its file name is image.png

            If you ever doubted on the role of Alibaba in the Cloud World, here’s a memo to catch up on. Alibaba Cloud market share overtook GCP to become No.3 Worldwide in 2021 (9.5% or $8.7 Billion). It has also moved from "Niche Players" quadrant to the "Visionaries" quadrant in the
            Gartner 2021 report for Cloud Infrastructure & Platform services, reaching the 4th place. 
            Did you know that Aliyun (translated “Ali Cloud”) was the original name of the company until 2017? You can still see it hardcoded in their Console sub-pages URL.


            Alibaba Cloud operates in 27 regions totaling 84 availability zones around the world with more regions set to follow. Huge presence in the Asia-Pacific area with 13 regions for China only, and new ones in S-Korea/Thailand.

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

            Alibaba Cloud CDN service leverages a network of 2800 nodes in 70 countries across the globe (2300 in China)

            Alibaba Cloud free tier

            The Alibaba Cloud Free Tier provides FREE Cloud services Including 50+ Free Offers Worth $1700-$8500 USD.

            The offers are time based (1-12 Months) and sometimes based in cash credit for both Individuals and enterprises.

            Here are some Cloud services/products you’ll get for free and their period of availability.


            • Simple Application Server-Linux (1CPU, 1G, 20GB Disk, 4Mbps ) 3 months   (+ preinstalled app)

            • ECS- Burstable t5 (1CPU, 1GB , 40GB Disk, 1Mbps) 12 months

            • ECS- Shared Compact Type xn4 (1CPU, 1GB , 40GB System Disk, 3Mbps) 3 months
              Note: Activating the free trial on ECS will disqualify you from using free trial on Simple Application Server.


            • Server Load Balancer: $15 Credit

            • Alibaba Cloud CDN   6 months

            • Object Storage Service 500 GB 1 month

            • File storage NAS 100 GB 1month

            • Tablestore 500 GB storage 750 hrs instance $19 Credit

            • Hybrid Backup Recovery 100 GB storage 3 months

            • ApsaraDB RDS for MySQL/PostgresSQL Dual hot-standby nodes or single node (20GB storage) 1 month

            • ApsaraDB for Redis 4 GB instance 1 month

            • ApsaraDB for MongoDB  3 nodes +20 Gb storage 1 month

            • Elasticsearch 3 nodes (2CPU, 4GB) 1 month

            • Developer After-sales support plan 1 month

            • API Gateway 1 MM calls/month 1 year

            • AI (image Search, speech interaction) 1-6 month

            If you want to check the full package you can dig through here: Alibaba-Fee-trial

            Alibaba Cloud free usage tier expiration

            These free tier offers are only available in the first for 12 months following your the sign-up date. After a year

            or when your service use exceeds the tiers, you simply switch to pay-as-you-go service rates.

            Note: Fee usage does not accumulate. 

            Alibaba Always Free Services

            These services are,obviously, always free because they are merely services to manage cloud native resources like Container Registry, VPC, Basic monitoring tools, etc.. It is implicitly free for all other Cloud providers, so I’ll pass.


            Here we see a `feel and look` that’s pretty close to AWS. Even the ECS(Elastic Compute Service) is likely derived from AWS EC2.

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

            The annoying things are:

            • Some links will open a new page which can led to a chrome tab chaos after few clicks.

            • Beware, if you have Cloud shell open, any click on your main page will refresh the whole thing and you’ll loose all what you were working on in the terminal.

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

            I don’t know about you, having the category of resources listed on the right side of the screen is not super intuitive.


            Did you know Alibaba is the only Cloud that also takes PayPal as payment method?That’s actually quite cool, no more credit card needed as PayPal can be linked to your Debit cards.

            Unfortunately, Free tier credit can’t be used if PayPal is the unique payment configured in your account (PAYG)This image has an empty alt attribute; its file name is image-6.png.  
            Billing options:
            What struck me is everything you deploy ends up with a Buy or Add to Cart button, as if you were shopping on amazon. It doesn’t matter what Billing Method you choose.

            • Subscription: Allows you to use a resources only after you pay for them (at discounted rates).

              • You can choose 1 month to 1 year  ( i.e USD 155/month  = USD 0.215/hour)

              • Renewal: This is a very weird feature and even dangerous sometimes.

                • By default, AliCloud resources will just run for a duration chosen at creation but after say 1month you’d have to renew manually. Meaning you can end up with a total mess if you have a dozen of instances with different expiration dates. Hence Auto-Renewal safety.   

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

            • Pay-as-You-Go: Bills you for the exact amount of resources you use on an hourly basis.

              • example : polar.mysql.mmx4.large4  => USD 0.323/hour

              • Can be paired with PayPal but not enough to use Free tier resources 

            • Reserved instance and saving plans:  these are similar to what AWS offers


            Same here, the naming convention doesn’t go far from AWS. VPC is also present in Alibaba but funny enough this is the only vendor I know who has a unique name for subnets. In Alibaba Cloud it is rather called vSwitch.
            There can be a maximum of two VPCs per account for starters.

            • The VPC CIDR bock size can be from /8 to /24

            • The VSwitch CIDR block size can be from /16 to /29.

            • You can expand a VPC by adding a secondary CIDR block to the VPC. (kinda like GCP)

            VPC is their 2nd gen and more isolated network which is slowly replacing their 1st gen Classic Network.
            See comparison below

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

            Traffic vs bandwidth pricing

            When assigning a Public IP Address to your compute instance, you have 2 billing choices for the egress traffic
             This image has an empty alt attribute; its file name is image-12.png

            • Pay-By-Bandwidth
              Based on a specified outbound bandwidth (i.e 3Mb/s). Fees are added to Total ECS instance fees.

            • Pay-By-Traffic
              Based on the actual amount of the outbound traffic usage in GB per hour.It is a postpaid billing method.


            The Compute is the cornerstone of any Cloud provider , naming convention are –as expected- pretty similar with AWS. ECS (Elastic compute service) for EC2. With different instance families and architectures.
             See full list >> here or here

            • Architecture: X86, ARM, Bare Metal , heterogenous (With GPU/NPU)  

              • Category: General Purpose, Compute/Memory optimized, big data, high clock speed,local SSD

              • Type: Regular, Burstable (i.e baseline of 20% CPU), Preemptible Instance (aws spot instance)

              • OS: The usual windows and Linux distributions including Alibaba Cloud Linux 

              • Storage: EBS disks are SSD minimum, no HDD available. 

            • Pricing: PAYG or subscription based and usual discounts through Reserved Instances and Saving Plans.
                   Please have a look at the full detailed pricing >>here 

            • Deploying in China regions

              • Beijing region, allows you span deployments through a whooping 12 Availability zones.
                That’s what I call a “Mega” High availability plan ;)

              • Beware, local regulation still requires your identity registration including your passport number   

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

            Simple Application Server(SAS)

            SAS is a lightweight compute product that provides ready-to-use applications to help build websites or other developments stacks with Custom Application Images. Worth a try.

            • Features

              • 14x Application Images (WordPress, LAMP, Docker, Plesk,Drupal, cPanel..) Autoconfigured

              • 6x OS images

              • Integrated with many AliCloud Services in few clicks (DNS,WAF,CDN, Firewall, ApsaraDB RDS)

              • As low as US $3.5/month with enhanced SSDs (ESSDs)

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


            Automation (IaC)

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

            API based Management tools are also to a good cloud automation and governance. Here are the tools I found.

            • Alibaba Cloud CLI: same as any cloud CLI tools.

            • OpenAPI Explorer: allows you to retrieve/call API operations, & dynamically generate SDK sample code.

            • Alibaba Cloud SDK: For several languages(Java, Python,PHP) to build and manage AliCloud services.

            • Operation Orchestration Service (OOS): Automatically manages and executes O&M(operations & maintenance) tasks. You can define items such as execution tasks, sequence, & inputs and outputs in execution templates and use the templates to automate O&M tasks (i.e check CPU% and scale).

            Resource Orchestration Service (ROS):
            This is a user-defined templates similar to AWS Cloudformation. I said similar but it’s an understatement :).
            Just look at this Cloudformation–to-ROS converter

            Command : $ rostran transform ./cloudformation/vpc_sg.json --target-format json

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


            • That’s it I said I’ll give some time to explore my 5th Cloud provider and it has finally happened.

            • It was supposed to be a little one pager on the Alibaba Cloud Free-tier but I dug deeper than I should :)

            • I hope that the above (deep dive) introduction gave you a glimpse of what to expect on Alibaba platform

            • Part 2 and 3 will cover Alibaba Cloud CLI installation and a terraform deployment of a website

            Stay tuned    

            Saturday, May 14, 2022

            Windows commands line cheat sheet for DBAs

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

            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

            -- 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


            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
            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


            List user group members

            Alias name     ORA_DBA
            Comment        Oracle DBA Group

            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


            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



            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"

            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%

            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

            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 cmd to get a your advanced system properties opened

              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

              Edit a PATH Environment Variable

              Use pathed tool and directly change your system %PATH% environment variable permanently in a terminal 

              C:\> PathEd.exe add "C:\Program Files\MyPortableApp"

              --- Remove a directory from your Machine path

              C:\> PathEd.exe remove "C:\Program Files\RepoZ"

              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


              • 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