Skip to main content

Posts

Showing posts from 2018

Powershell to collect SQLServer compliant info Verson 2

We established some best practice rules for SQL Server, we need method to collect these data and analyze the compliance. Powershell script is our solution。 The Best Practice rules are: ##• Do not install sqlserver on C drive, ##• do not put database files on C drive ##• Set maximum memory: should not left as default, leave 1GB memory for OS ##• Agent start automatically ##• Database auto growth on, with 10% percentage ##• Database backup regularly, either by SQLServer maintenance, Netbackup agent or Doc Ave ##• Transaction log backup regularly to prevent db size growth out of control ##• Disable CLR, with exception on kdcwdbsqlprd03 where respontek requires CLR. ##• Disable xp_cmdshell ##• Database recovery mode: full recovery mode for production, simple recovery mode for PPE ##• Turn off DB autoshrink Step 1: create the tables in oracle where the data will be saved, we choose oracle, it can be MSSQL, create table mssqlhostcpuinfo (hostname varchar2(20),

RAC database restore from netbackup tape without RMAN catalog

The Oracle RAC databases were being backed up regularly without using catalog, even worse, the database controlfile autobackup was turned off.  That configuration compromise the RPO. I restored and recovered the database to the latest scn that the last controlfile backup knows, but not the latest scn that available in the archivelogs. Below are the steps: The assumption is that all database files, controlfile are gone. 1. list available backup handles in netbackup after a particular date: /usr/openv/netbackup/bin/bplist -l -S $netbackupserver -C $client -t 4 -R -s 11/29/2018 / output: ... -rw-rw---- oracle    asmadmin     19922944 Nov 29 11:55 /ctrl_dAPIMGRS_u16tje9lj_s1062_p1_t993470131 -rw-rw---- oracle    asmadmin    142082048 Nov 29 11:54 /arch_dAPIMGRS_u15tje9kd_s1061_p1_t993470093 ... From the above output, we guess the latest controlfile backup is in /ctrl_dAPIMGRS_u16tje9lj_s1062_p1_t993470131 2.  restore controlfile from this handle: shutdown immediate start

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: Create tables as CMDB repository in oracle db: mssqlhostcpuinfo ; mssqlhostmemoryinfo ; mssqlhostdiskinfo ; mssqlhostosinfo ; mssqlhostdbinfo ; Truncate the above tables Run powershell to collect MSSQL server information and output to files mssqlcpu.txt, ssqlos.txt,mssqldb.txt,mssqlmem.txt,mssqldisk.txt Run sqlldr and load above txt file into the repository tables 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)); c

Create automatic sql tuning task advisor report for special sql_id

--   Yannick Jaquier  blog helps me to create this solution. Thank you Sir! --  https://blog.yannickjaquier.com/oracle/automatic-sql-tuning-task-overview.html spool c:\dropit\sqlidautosqltune.txt exec dbms_output.put_line('#####find which execution_name and object_id has this sql_id run') select distinct execution_name,object_id,trunc(TIMESTAMP) from DBA_ADVISOR_SQLPLANS where sql_id='&sqlid'; -- DBA_ADVISOR_SQLSTATS also have execution_name and object_id as in DBA_ADVISOR_SQLPLANS, but not always, I randomly checked about ten sql_id, all of them exists in DBA_ADVISOR_SQLPLANS, but some of them are not in DBA_ADVISOR_SQLSTATS exec dbms_output.put_line('#####gather the execution_name and object_id which has the sqlid, run report on it') SET lines 200 pages 1000 SET LONG 999999999 SET longchunksize 200 SELECT DBMS_AUTO_SQLTUNE.REPORT_AUTO_TUNING_TASK('&&execution_name','&&execution_name','TEXT','ALL',&#

Golden Gate Error not shown in GGSCI but shown in command line

I have a GG replication special run on multitenant databases, run it in ggsci GGSCI (KDCWASDBAPRD01) 8> view params rinitst1 replicat rinitst1 specialrun end runtime SETENV (ORACLE_SID = "orcl") SETENV (ORACLE_HOME = "D:\oracle\product\12.2.0\dbhome_1") userid c##ggadmin@test2 password password assumetargetdefs extfile ./dirdat/tcustord.dat discardfile ./dirrpt/rinitst1.dsc,purge map TEST1.*.*, target TEST2.*.*; GGSCI> start rinitst1 GGSCI> view report rinitst1 。。。 2018-10-03 11:11:48  ERROR   OGG-10144  (RINITST1.prm) line 2: Parameter [specia lrun] is not valid for this configuration. 。。。 because it complains about "specialrun", so I removed the line from parameter file, and run again, it does not throw any error message in ggserr.log, trace file, and report file,but data is not replicated either. After tried many things, finally, I run the replicat from command line, it throw error that I can chew on. D:\oracle\produc

PowerShell script to manage Maximo

#run powershell script remotely Invoke-Command -ComputerName COMPUTER -ScriptBlock { COMMAND } Invoke-Command -ComputerName COMPUTER -ScriptBlock { COMMAND } -credential a.jsun #find computer last reboot time systeminfo -S servername   |findstr /I "boot time" PS > Invoke-Command -ComputerName servername  -ScriptBlock { Get-CimInstance -ClassName win32_operatingsystem | select csname, lastbootuptime } #find computer last 10 reboot time invoke-command -computername $servername -scriptblock {get-eventlog system | where-object {$_.eventid -eq 6006 -or $_.eventid -eq 6005 }  | select -first 10} #query status of windows service sc \\servername  query "OracleWeblogic ship76domain_Vessel_ManSrv" PS > Invoke-Command -ComputerName servername  -ScriptBlock { Get-Service -Name "OracleWeblogic ship76domain_Vessel_ManSrv" } #query history of a service start/stop in eventlog PS > (Get-EventLog -LogName "System" -Source "Service Contro

Case study of SQL AlwaysOn transaction log file shrink

Case study of SQL AlwaysOn transaction log file shrink. Usually backup transaction log will move the LVF head lower, make it possible to shrink transaction log, but with sqlalwaysOn configuration, I found that it not work as always, I need to specifically fill up the highest LVF so that transaction log head can move to lower side. This is the case study of that. The following blog explained lazy truncation of sql AlwaysOn: https://blogs.msdn.microsoft.com/sql_pfe_blog/2013/06/27/lazy-log-truncation-clearing-of-sql-transaction-log-vlf-status-deferred/ To find out the file size: SELECT DB_NAME ( database_id ) AS DatabaseName , Name AS Logical_Name , Physical_Name , ( size * 8 )/ 1024 SizeMB FROM sys . master_files --WHERE DB_NAME(database_id) = 'AdventureWorks' order by 4 desc GO We use Sp_CentralAdmin database as example to shrink logfile: Use Sp_CentralAdmin go Dbcc loginfo go Noticed that all the LVF has status=2.