Skip to main content

Powershell, SQLLDR, CMDB for MSSQL

The following are the end-to-end coding solution to collect MSSQL system information (CPU/cores, memroy, disk space and free space, OS version, database version and edition), and load the information to Oracle database as CMDB repository, the steps are:


  1. Create tables as CMDB repository in oracle db: mssqlhostcpuinfo ; mssqlhostmemoryinfo ; mssqlhostdiskinfo ; mssqlhostosinfo ; mssqlhostdbinfo ;
  2. Truncate the above tables
  3. Run powershell to collect MSSQL server information and output to files mssqlcpu.txt, ssqlos.txt,mssqldb.txt,mssqlmem.txt,mssqldisk.txt
  4. Run sqlldr and load above txt file into the repository tables
  5. Create view mssqlcmdball, mssqlcmdbserver, mssqlcmdbdb

Step 1:


create table mssqlhostcpuinfo (hostname varchar2(20), cores int,logicalcors int);

create table mssqlhostmemoryinfo (hostname varchar2(20), membytes number(13));
create table mssqlhostdiskinfo (hostname varchar2(20), diskletter varchar2(2),capacity number(13), freedisk number(13));
create table mssqlhostosinfo (hostname varchar2(20), os varchar2(60),version varchar2(15));
create table mssqlhostdbinfo (hostname varchar2(20), instancename varchar2(30), dbname varchar2(100),recoverymode varchar2(10),status varchar2(10),version varchar2(15),sp varchar2(10),edition varchar2(60));
create table mssqlaux (host varchar2(20),dbname varchar2(100),lifecyclestatus varchar2(10))

Step 5:

create or replace view mssqlcmdball
as
select cpu.hostname, db.instancename,os.version osversion,cpu.cores,mem.membytes/1024/1024 MEM_MB, disk.diskletter,disk.capacity,disk.freedisk,substr(db.dbname,1,50) dbname,db.version dbversion
from mssqlhostcpuinfo cpu,
mssqlhostmemoryinfo mem,
mssqlhostdiskinfo disk,
mssqlhostosinfo os,
mssqlhostdbinfo db
where cpu.hostname=mem.hostname(+)
and cpu.hostname=disk.hostname(+)
and cpu.hostname=os.hostname(+)
and cpu.hostname=db.hostname(+)
;

create or replace view mssqlcmdbserver
as
select cpu.hostname, os.version osversion,cpu.cores,mem.membytes/1024/1024 MEM_MB, disk.diskletter,disk.capacity,disk.freedisk
from mssqlhostcpuinfo cpu,
mssqlhostmemoryinfo mem,
mssqlhostdiskinfo disk,
mssqlhostosinfo os
where cpu.hostname=mem.hostname(+)
and cpu.hostname=disk.hostname(+)
and cpu.hostname=os.hostname(+)
;

create or replace view mssqlcmdbdb
as
select cpu.hostname, db.instancename,os.version osversion,cpu.cores,mem.membytes/1024/1024 MEM_MB,substr(db.dbname,1,60) dbname,db.version dbversion
from mssqlhostcpuinfo cpu,
mssqlhostmemoryinfo mem,
mssqlhostosinfo os,
mssqlhostdbinfo db
where cpu.hostname=mem.hostname(+)
and cpu.hostname=os.hostname(+)
and cpu.hostname=db.hostname(+)
;

 Powershell script to run step 2,3,4:

#* FileName: ServerInventory.ps1
#*=============================================================================
#* Script Name: [CollectMSSQLServerinfo.ps1]
#* Created: [Nov 6th, 2018]
#* Author: jiulu Sun
#* Company: BCFerries
# usage : BCFerries data service team managed sqlservers
# How to use : run powershell, type $PathOf\filename.ps1, it will loop all servers defined in $arrComputers
# you need to change executionpolicy first if its at restricted mode. or you will get an error
# "File C:\vb\sqlserver_info1.ps1 cannot be loaded because the execution of scripts is disabled on this system. Please see "get-help about_signing" for more details."
# this is an example
# PS C:\vb> Get-ExecutionPolicy
# Restricted
# PS C:\vb> set-executionpolicy remotesigned
# PS C:\vb> Get-ExecutionPolicy
# RemoteSigned
# define the file_name which has sqlserver instance names, this file will be read in loop
$arrComputers = get-Content -Path "C:\projects\mssql\CMDB\SQLServerList.txt"
#define output files
$OutputfileCPU = "C:\projects\mssql\CMDB\MSSQLCPU.txt"
$OutputfileMem = "C:\projects\mssql\CMDB\MSSQLMem.txt"
$OutputfileDisk = "C:\projects\mssql\CMDB\MSSQLDisk.txt"
$OutputfileDB = "C:\projects\mssql\CMDB\MSSQLDB.txt"
$OutputfileOS = "C:\projects\mssql\CMDB\MSSQLOS.txt"
#clear the outputfiles
remove-item -Path $OutputfileCPU -ErrorAction SilentlyContinue
remove-item -Path $OutputfileMem -ErrorAction SilentlyContinue
remove-item -Path $OutputfileDisk -ErrorAction SilentlyContinue
remove-item -Path $OutputfileDB -ErrorAction SilentlyContinue
remove-item -Path $OutputfileOS -ErrorAction SilentlyContinue

$ONETAB+=" "
$TWOTABS=" "
$BLANKLINE=""

Function MemInfo {
Get-WmiObject Win32_ComputerSystem -Namespace "root\CIMV2" -ComputerName $strComputer|Select-Object -Property "Name","TotalPhysicalMemory" |Export-Csv -Path $OutputfileMEM -append -NoTypeInformation -force
}

Function OSInfo {
 Get-WmiObject Win32_OperatingSystem -ComputerName $strComputer|select-object csname,caption,version|Export-Csv -Path $OutputfileOS -append -NoTypeInformation -force
}

Function CPUInfo {
$property = "systemname","numberOfCores", "NumberOfLogicalProcessors"
Get-WmiObject win32_processor -computername $strComputer |Select-Object -Property $property |sort -unique|Export-Csv -Path $OutputfileCPU -append -NoTypeInformation -force
}

Function DiskInfo {
$property = "__SERVER","DriveLetter","Capacity","FreeSpace"
Get-WmiObject Win32_Volume -filter "DriveLetter IS NOT NULL AND FileSystem IS NOT NULL" -computername $strComputer|Select-Object -Property $property|Export-Csv -Path $OutputfileDISK -append -NoTypeInformation -force
}


Function sysdbInfo {
$sql= @"
SET NOCOUNT ON
SELECT CONVERT(varchar(80),serverproperty('MachineName'))
+ ',' + CONVERT(varchar(64),serverproperty ('servername'))
+ ',' + name
+ ',' + CONVERT(varchar(20),DATABASEPROPERTYEX(sd.name, 'Recovery'))
+ ',' + CONVERT(varchar(20),DATABASEPROPERTYEX(sd.name, 'Status'))
+ ',' + RTRIM (CONVERT(char(20),serverproperty('ProductVersion')))
+ ',' + CONVERT(varchar(20), serverproperty('ProductLevel')) 
+ ',' + CONVERT(varchar(80),SERVERPROPERTY('Edition'))
  FROM master.dbo.sysdatabases sd
"@
#invoke-sqlcmd -ServerInstance $strComputer  -Query $sql|Export-Csv -append -path $OutputfileDB -NoTypeInformation -force
invoke-sqlcmd -ServerInstance $strComputer  -Query $sql|ConvertTo-Csv -NoTypeInformation -Delimiter "," | Select-Object -Skip 1 | % {$_ -replace '"', ""} | Out-File -Force  -append -FilePath $OutputfileDB
}


#*=============================================================================
#* SCRIPT BODY
#*=============================================================================

#truncate the cmdb tables before
$server="emservername"
(echo "truncate table mssqlhostcpuinfo;")|sqlplus jsun/$PASSWORD@"(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = $server)(Port = 1528))) (CONNECT_DATA = (service_name =emservicename.bcferries.com)))"
(echo "truncate table mssqlhostmemoryinfo;")|sqlplus jsun/$PASSWORD@"(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = $server)(Port = 1528))) (CONNECT_DATA = (service_name =emservicename.bcferries.com)))"
(echo "truncate table mssqlhostdiskinfo;")|sqlplus jsun/$PASSWORD@"(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = $server)(Port = 1528))) (CONNECT_DATA = (service_name =emservicename.bcferries.com)))"
(echo "truncate table mssqlhostosinfo;")|sqlplus jsun/$PASSWORD@"(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = $server)(Port = 1528))) (CONNECT_DATA = (service_name =emservicename.bcferries.com)))"
(echo "truncate table mssqlhostdbinfo;")|sqlplus jsun/$PASSWORD@"(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = $server)(Port = 1528))) (CONNECT_DATA = (service_name =emservicename.bcferries.com)))"

foreach ($strComputer in $arrComputers)

{ #Function Calls go here

"#########################################################################"
"Computer Name:" +$strComputer
"#########################################################################"


#* Call database Function
$BLANKLINE
"@@@SQLServer database information"
sysdbInfo

####now remove the name instance.
if ($strComputer.contains("\"))
{#'this is a named instance
$position=$strComputer.indexof("\")
$strComputer = $strComputer.substring(0,$position)
#"computer name is :" + $strComputer
}

#* Call SysInfo Function
"Sytem Information"
MemInfo

#* Call OSInfo Function
"Operating System Information"
OSInfo

#* Call CPUInfo Function
"Processor Information"
CPUInfo

#* Call DiskInfo Function
"Disk Information"
DiskInfo

}

#now load these tables
& "C:\ORACLE\product\12.1.0\client_1\BIN\sqlldr.exe" userid=jsun/$password@emrept control=C:\projects\MSSQL\CMDB\mssqlcpu.ctl log=C:\projects\MSSQL\CMDB\mssqlcpu.log
& "C:\ORACLE\product\12.1.0\client_1\BIN\sqlldr.exe" userid=jsun/$password@emrept control=C:\projects\MSSQL\CMDB\mssqlOS.ctl log=C:\projects\MSSQL\CMDB\mssqlOS.log
& "C:\ORACLE\product\12.1.0\client_1\BIN\sqlldr.exe" userid=jsun/$password@emrept control=C:\projects\MSSQL\CMDB\mssqlDB.ctl log=C:\projects\MSSQL\CMDB\mssqlDB.log
& "C:\ORACLE\product\12.1.0\client_1\BIN\sqlldr.exe" userid=jsun/$password@emrept control=C:\projects\MSSQL\CMDB\mssqlMEM.ctl log=C:\projects\MSSQL\CMDB\mssqlMEM.log
& "C:\ORACLE\product\12.1.0\client_1\BIN\sqlldr.exe" userid=jsun/$password@emrept control=C:\projects\MSSQL\CMDB\mssqlDisk.ctl log=C:\projects\MSSQL\CMDB\mssqlDISK.log


SQLLDR ctrl files are listed below:

MSSQLCPU.ctl:
LOAD DATA
CHARACTERSET UTF8
    INFILE 'C:\projects\MSSQL\CMDB\MSSQLCPU.txt'
    BADFILE 'C:\projects\MSSQL\CMDB\mssqlcpu.bad'
    DISCARDFILE 'C:\projects\MSSQL\CMDB\mssqlcpu.dis'
    APPEND
    INTO TABLE jsun.mssqlhostcpuinfo  
    when hostname != BLANKS
     FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
     (
     HOSTNAME char,
     CORES ,
     LOGICALCORS
    )
    
MSSQLDB.ctl:
LOAD DATA
CHARACTERSET AL16UTF16
    INFILE 'C:\projects\MSSQL\CMDB\MSSQLDB.txt'
    BADFILE 'C:\projects\MSSQL\CMDB\mssqlDB.bad'
    DISCARDFILE 'C:\projects\MSSQL\CMDB\mssqlDB.dis'
    APPEND
    INTO TABLE jsun.mssqlhostdbinfo 
    FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
     (
     HOSTNAME char,
     INSTANCENAME char,
     DBNAME char,
     RECOVERYMODE char,
     STATUS char,
     VERSION char,
     SP char,
     edition char "rtrim(:edition)" 
     )
    
MSSQLDISK.ctl:
LOAD DATA
CHARACTERSET UTF8
    INFILE 'C:\projects\MSSQL\CMDB\MSSQLDISK.txt'
    BADFILE 'C:\projects\MSSQL\CMDB\mssqlDISK.bad'
    DISCARDFILE 'C:\projects\MSSQL\CMDB\mssqlDISK.dis'
    APPEND
    INTO TABLE jsun.mssqlhostdiskinfo  
    when hostname != BLANKS
     FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
     (
     HOSTNAME char,
     diskletter char,
     capacity,
     freedisk
     )
    
    
 MSSQLMEM.ctl:
LOAD DATA
CHARACTERSET UTF8
    INFILE 'C:\projects\MSSQL\CMDB\MSSQLMEM.txt'
    BADFILE 'C:\projects\MSSQL\CMDB\mssqlMEM.bad'
    DISCARDFILE 'C:\projects\MSSQL\CMDB\mssqlMEM.dis'
    APPEND
    INTO TABLE jsun.mssqlhostmemoryinfo  
    when hostname != BLANKS
     FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
     (
     HOSTNAME char,
     membytes
     )
MSSQLOS.ctl:
LOAD DATA
CHARACTERSET UTF8
    INFILE 'C:\projects\MSSQL\CMDB\MSSQLOS.txt'
    BADFILE 'C:\projects\MSSQL\CMDB\mssqlOS.bad'
    DISCARDFILE 'C:\projects\MSSQL\CMDB\mssqlOS.dis'
    APPEND
    INTO TABLE jsun.mssqlhostosinfo  
    when hostname != BLANKS
     FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
     (
     HOSTNAME char,
     OS char ,
     version char
    )
    
 Sample query result of view:
















Comments

Post a Comment

Popular posts from this blog

Opatch apply/lsinventory error: oneoff is corrupted or does not exist

I am applying the quarterly patch for 19c RDBMS, I tried using napply but failed, but somehow it corrupted the inventory though nothing applied. further apply and lsinventory command ran into error like this: $ ./OPatch/opatch lsinventory Oracle Interim Patch Installer version 12.2.0.1.21 Copyright (c) 2020, Oracle Corporation.  All rights reserved. Oracle Home       : /u02/app/oracle/19.0.0 Central Inventory : /u01/app/oraInventory    from           : /u02/app/oracle/19.0.0/oraInst.loc OPatch version    : 12.2.0.1.21 OUI version       : 12.2.0.7.0 Log file location : /u02/app/oracle/19.0.0/cfgtoollogs/opatch/opatch2020-09-08_13-35-59PM_1.log Lsinventory Output file location : /u02/app/oracle/19.0.0/cfgtoollogs/opatch/lsinv/lsinventory2020-09-08_13-35-59PM.txt -------------------------------------------------------------------------------- Inventory load failed... OPatch cannot load inventory ...
Post a Comment
Read more

oracle dba_hist_sysmetric_summary

found this blog is helpful to get CPU and IO statistics on oracle database. http://shob-dbadmin.blogspot.ca/2012/12/how-to-find-total-io-of-database.html courtesy to  Shomil Bansal , below are hist writing, not mine. How to find total IO of the database instance Total IO of database instance is sum of the physical reads, physical writes and redo writes. There are several views to find these values. v$sysmetric  - Reports metric values for only the most current time sample 60 secs. v$sysmetric_summary  - Reports metric values for time sample of 1 hour. v$sysmetric_history  - Reports metric values every 60 sec from the time instance is up. Better way to analyse IO using this view to take deltas between two time periods. dba_hist_sysmetric_history  - All the above views are refreshed when the instance is restarted. This view, part of AWR, stores the historical stats. I have used this view for my report. Query: ====== set lines 350...
Post a Comment
Read more

ORA_RMAN_SGA_TARGET

assume that we lost all the files of oracle database but we do have rman backup, when trying to bring up a dummy database before restore start, I get this error. RMAN> startup nomount force; WARNING: cannot translate ORA_RMAN_SGA_TARGET value startup failed: ORA-01078: failure in processing system parameters ORA-01565: error in identifying file '+DATA/PROD/spfilePROD.ora' ORA-17503: ksfdopn:2 Failed to open file +DATA/PROD/spfilePROD.ora ORA-15056: additional error message ORA-17503: ksfdopn:DGOpenFile05 Failed to open file +DATA/prod/spfileprod.ora ORA-17503: ksfdopn:2 Failed to open file +DATA/prod/spfileprod.ora ORA-15173: entry 'spfileprod.ora' does not exist in directory 'prod' ORA-06512: at line 4 starting Oracle instance without parameter file for retrival of spfile RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =================================...
2 comments
Read more