Recently, moved most of my scripts to github, most for private repo for now, starting to extract general scripts and posted in General DBA tips (zhoutransform.github.io) https://zhoutransform.github.io/GeneralDBA/
Posts
GENERATE RSA KEYS WITH SSH BY USING PUTTYGEN
- Get link
- Other Apps
GENERATE RSA KEYS WITH SSH BY USING PUTTYGEN 5.12.3.9.1 Autologin (TFDBA runbook Unix/Linux Tips) 1. Use puttygen to create keys, move mouse to create random 2. Save keys public and private, if doesn’t want passphrase to login using keys, ignore the passphrase 3. Copy and paste the output on the screen of puttygen to clipboard (for HPUX use "...\putty\PuttyKeysPublicPrivate\authorized_keys" or linux one for linux, open and copy and paste) 4. In Linux Server, under your name, create $HOME/.ssh/authorized_keys file and paste step 3 to it and save Same as HPUX, for root it is /.ssh/authorized_keys, append the keys (paste below existing if there is) 5. In putty ->connection->SSH->Auth load the private key :\putty\PuttyKeysPublicPrivate\testCernerPrivateKey.ppk 6. Save session. (connection->data_autologin name, add your loginname)
blocking in SQL server
- Get link
- Other Apps
we have a vendor's application, has blocking happens very often, at lease once a week. Without proper tool to trace the application's activity to prove what we believe or what we should suggest to change, cannot get the transaction start as we noticed the blocking ,the statement already passed, We noticed that the application kept 2 transactions open without commit or rollback, in the transactions, there are a few locks held on page, row levels ,and tens of other sessions were blocked. submitted the request to Microsoft, suggested using the RCSI level, with the expense of tempdb size and some IO overload. to find the statements in extended event, I searched online https://www.sqlserver-dba.com/2019/09/how-to-read-and-parse-the-microsoft-sql-server-extended-event-log-file-xel-file.html and used something below: --Read the Extended Event Log File output file and PARSE the returned xml format rows DECLARE @path NVARCHAR(260); ----retrieve the local path of system_health fi
non owner likes to truncate table in oracle
- Get link
- Other Apps
Without grant Drop any table, I used the following script 1. create a stored procedure 2. grant execute on that procedure to that user create or replace procedure userx.truncateTable( p_tname in varchar2 ) as begin execute immediate 'truncate table ' || p_tname; --DDL, implicit commit end; CREATE TABLE userx.TEMP11 (x NUMBER(2,0), x2 VARCHAR2(20 CHAR), x3 VARCHAR2(20 CHAR), x4 VARCHAR2(20 CHAR), x5 VARCHAR2(20 CHAR) ); grant execute on userx.truncateTable to usery; have usery run execute userx.truncateTable ('Temp11');
Oracle blocking
- Get link
- Other Apps
today one of our interface queued up, I got the call and noticed the table the interface was inserting was locked . I used following script to identify the blocker: select a.sid, a.serial#, a.process from v$session a, v$locked_object b, dba_objects c where b.object_id = c.object_id and a.sid = b.session_id and OBJECT_NAME=upper('xxxxxtablexxxxname'); then alter system kill session 'sid,servial#' immediate; the blocker was one developer run a stored procedure locking that table, but not commit and left sql developer open.
SQL server agent password change, not require reboot, but requires retype AD user when reboot in the future
- Get link
- Other Apps
SQL server agent password change, not require reboot, but requires retype AD user when reboot in the future when I change sql agent service AD user password to meeting security requirement, I used the below powershell script and change all my 200+ sql instances sql agent service, no service restart required. Today just found after I done a patching on one of my clusters, the sql agent service didn't start, I thought the password was old, and retype pwd, still failed to start, then I tried to reload the AD user by search user name in AD and type the password, it works. script : [System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SqlWmiManagement”) | out-null $text=Get-Content C:\sql\serverlist0.txt $TotalComputers=$text.Length write-host "the total number of computers is $TotalComputers" For ($i=0; $i -lt $text.Length; $i++) { # write-host " the server name is $text[$i] being working on " $SMOWmiserver = New-Object (‘Microsoft.SqlServ