DBA

I am setting up a new server that has no internet connection, I need to install sqlserver module for powershell. this document is helpful https://docs.microsoft.com/en-us/powershell/module/packagemanagement/install-packageprovider?view=powershell-6 Step 1.        Manually Download the package provider NuGet on a computer that has internet Run Install-PackageProvider -Name NuGet -RequiredVersion 2.8.5.201 -Force to install the provider from a computer with an internet connection. After the install, you can find the provider installed in $env:ProgramFiles\PackageManagement\ReferenceAssemblies\\\<ProviderName\>\\\<ProviderVersion\> or $env:LOCALAPPDATA\PackageManagement\ProviderAssemblies\\\<ProviderName\> \\\<ProviderVersion\ >. (In my case, The file name is Microsoft.PackageManagement.NuGetProvider.dll ) Place the <ProviderName> folder, which in this case is the Nuget folder, in the corresponding location on your target computer.  （In my case,

Sharepoint, index defragmentation, dm_db_index_physical_stats, physical IO, sql server AlwaysOn failover How do these things come together? I have a sqlserver alwaysOn hosting sharepoint databases, the sqlserver failover every night, it's caused by the sharepoint nightly job "health analysis job" which run proc_DefragmentIndices on each sharepoint databases and killed the sqlserver. the problem of this proc is that it use dm_db_index_physical_stats to find index fragmentation and rebuild the fragmented indexes, which caused huge physical disk IO and hung the sqlserver. How did I find out. here are the the path. First of all, SQL Server  log shows a lot of errors and failover messages. such as "The availability group database "sharepoint_content3" is changing roles from "PRIMARY" to "RESOLVING" because the mirroring session or availability group failed over due to role synchronization." “SQL Server hosting availability gr

My server is configured with HugePage, it's swapping, this is the shell script to find out how much shared memory used by each database, how much memory is using normal page or HugePage, this script only list memory bigger than 100,000kB. for line in `ps -ef|grep ckpt|grep -v "+ASM"|grep -v "grep"|awk '{print $2"_"$8}'`; do #echo $line PID=$(echo $line|cut  -d "_" -f 1) DBNAME=$(echo $line|cut  -d "_" -f 4) SMAPS=$"/proc/"$PID"/smaps" echo "#########"$DBNAME"########"$SMAPS"########" awk '/^Size:/ {if ($2>100000) i=1};i && i++ <= 12' $SMAPS done Sample output: This example shows that 268MB on 4kB (normal page), 10GB on 2048kB page (HugePage) #########FFDE2########/proc/64294/smaps######## Size:             268332 kB Rss:               12300 kB Pss:                 135 kB Shared_Clean:      12300 kB Shared_Dirty:          0 kB Private_

Our RAC crash when we have storage issue, after storage is fixed, starting RAC using "crsctl start crs" failed on ASM start. Did not think it could be caused by GNS but looks like it was. GNS was offline because the VIP somehow ran away on one of the RAC node, I can ping and ssh to the VIP but cannot run "srvctl stop/start GNS". This is the evidence that ASM rely on GNS to be online, if GNS is not online, then the remote_listener in +ASM's parameter file is invalid and asm is killed. In alert_ASM1.log: Using parameter settings in server-side spfile +OCR_VOTE/wwwracprdcrs00/asmparameterfile/registry.253.830014321 ... Sat Aug 03 00:49:10 2019 USER (ospid: 16060): terminating the instance due to error 119 The ASM log does not say why error 119 occur, but checking ohasd_oraagent_grid.trc, it's the remote_listener that causing problem: In ohasd_oraagent_grid.trc: 2019-08-03 00:49:10.295345 :CLSDYNAM:2912564992: [ ora.asm]{0:5:3} [start] ORA-00119: i

This is the Powershell script: #This script monitor DS critical URLs, send email alert if URL is not "0% fail" $ErrorActionPreference = 'SilentlyContinue' c: cd C:\DBA\bin\getty $URLSource="c:\dba\bin\getty\URLsource.txt" $LOG="c:\dba\bin\getty\DSMonitoringURLsPS.log" $GETTYDIR="C:\DBA\bin\getty" $GETTY="C:\DBA\bin\getty\getty_NO_Save_Log.bat" remove-item -Path $LOG -ErrorAction Ignore #foreach($ThisURL in Get-Content $URLSource| Where {$_ -notmatch '^#.*'}) foreach($ThisURL in Get-Content $URLSource)  { if ($ThisURL -notmatch '^#.*') { $ThisURL=$ThisURL.trim() cd $GETTYDIR & $GETTY $ThisURL 3 >> $LOG } else { "skipped checking this URL :"+$ThisURL >> $LOG }  } if (!(Test-Path $LOG)) {   Write-Warning "outputfile absent, exiting"   exit } $SEL = get-content $LOG|select-string " fail"|select-string -notMatch -simplematch "

example 1: calculate shared memory usage  # ipcs -m|awk '$5 ~ /^[0-9]/ {print $5}'|xargs | sed -e 's/ /+/g' | bc example 2: find process which locks ASM disk # lsof /dev/oracleasm/vote0|awk '$2 ~ /^[0-9]/ {print $2}'|sed 's/ /,/g'|xargs ps -fp UID        PID  PPID  C STIME TTY      STAT   TIME CMD oracle    1426     1  0 Apr30 ?        Ss     0:29 ora_rbal_db1_1 oracle    1463     1  0 Apr30 ?        Ss     0:28 ora_rbal_db2_1

Using "backup location", I do not need to connect to target database, but how do I know what "until time" is available in the backup pieces? one way is to find the backup piece information from target database by using "list backup...", but what if I do not have the target database at all? while, I can arbitrarily give a time, then RMAN will tell me what is the time in those back pieces, nice! Steps captured! 1.create the RAC database using dbca 2.create spfile='+oradata/bcfw/spfileBCFW.ora' from pfile='/tmp/pfile.ora'.     alter system set cluster_database=false scope=memory 3.srvctl start instance -d dbname -i dbname1 -o nomount 4.export NLS_DATE_FORMAT='yyyymmdd hh24:mi:ss' 5.rman> duplicate database /* the backup was done using this: RUN {   ALLOCATE CHANNEL ch11 TYPE DISK MAXPIECESIZE 1G;   BACKUP as COMPRESSED backupset   FORMAT '/u01/dba/jsun/%d_D_%T_%u_s%s_p%p'   DATABASE   PLUS ARCHIVELOG   FORM

After GI is patched. the " kfod op=PATCHLVL " and " crsctl query crs softwarepatch " ,output mismatched from node 3 comparing to node 1&2, though "opatch lsinventory" output match... Patch ID  28729169 and  4104219146 were not in the output of " kfod op=PATCHES   " on node 3. Created oracle SR, the support suggested the following steps to fix the issue. root>patchgen commit -pi 28729169  root>patchgen commit -pi  4104219146    check the below on all nodes and notify if there is any difference  kfod op=PATCHLVL  kfod op=PATCHES  If they are same run the below  # rootcrs.sh -patch  That fixed the problem.

According to this Doc, I can remove the patch folder in .patch_storage if the patch is not listed in opatch inventory. How To Avoid Disk Full Issues Because OPatch Backups Take Big Amount Of Disk Space. (Doc ID 550522.1) The following two scripts work the same way, they list the patch number that has zero match in opatch inventory. #1: $ORACLE_HOME/OPatch/opatch lsinventory>dropit; for patch in `ls|cut -d "_" -f 1|grep [0-9]`;   do echo $patch:`grep -c $patch dropit`; done|awk -F":" '{if ($2==0) print $0}' #2 ls $ORACLE_HOME/.patch_storage|cut -d "_" -f 1|grep [0-9] > patchstorage.txt; $ORACLE_HOME/OPatch/opatch lsinventory>opatchinventory.txt; for patch in `cat patchstorage.txt`; do echo $patch:`grep -c $patch opatchinventory.txt`; done|awk -F":" '{if ($2==0) print $0}' sample output: 19872484:0 20299018:0 20831113:0 21436941:0 23854735:0 #3: this will display the ls command of those pat