DBA

This script will loop all running db instances and run rman validate database #!/bin/sh #created on March, 2020 by Jiulu Sun #use the following two commands to manually generate the instance list and create oratab_host file to loop # ps -ef|grep ckpt |awk '{print $8}'|awk -F "_" '{if (NF==3) print $3;if (NF==4) print $3"_"$4}'|grep -v "ASM"|grep -v '^_' > dblist_vdclracdev01 # for inst in $(<dblist_vdclracdev01)^Jdo^Jgrep -i ${inst}":" /etc/oratab|awk -F ":" '{print $1":"$2}'^Jdone > oratab_vdclracdev01 #now the oratab_host file is created, validate all of them. LOG_LOCATION=/u01/dba/jsun timestamp=`date  +%Y_%m_%d_%H_%M_%S` hostname=`hostname` RMANLOG_FILE=$LOG_LOCATION/RmanValidate_$hostname_$timestamp.log SQLPLUS_LOGFILE=$LOG_LOCATION/sqlplus_logfile_$hostname_$timestamp.log ORATAB=$LOG_LOCATION/oratab_vdclracdev01 HOSTNAME=`hostname` export NLS_DATE_FORMAT="dd-mo...

this is helpful. https://perfstat.wordpress.com/2015/02/06/ezconnect-without-a-password/ EZCONNECT without a password Leave a reply After encountering this little annoyance for the nth time, I thought I should write it up once and for all, so it is committed to my electronic memory. The EZCONNECT naming method is a handy way of connecting to an Oracle database by its service name, which avoids the need for aliases in the TNSNAMES file, or lengthy SQL*Net connection strings : 1 2 SQL> connect user/password@hostname:port/service_name Connected. However, one would expect that if you don’t want to specify the password on the command line, then simply omit the password and await the prompt. But… 1 2 3 SQL> connect user@hostname:port/service_name ERROR: ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA Without the password present, the command interpreter parses the string differently, and (according to Or...

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

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

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