DBA

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

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

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

1. To test connection time: 1.1 $PASSWD="whatever" $PASSED get-content bosslist.txt|foreach-object {measure-command {(echo "select 1 from dual")|sqlplus jsun/$PASSWD@"(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = $_)(Port = 1521))) (CONNECT_DATA = (SID =SIDNAME)))"}}|findstr TotalSeconds output looks like this: TotalSeconds      : 1.6875126 TotalSeconds      : 1.6090257 TotalSeconds      : 1.61322 ... 1.2: This command will combine the server name to the totalseconds output. get-content bosslist.txt|foreach-object { $server=$_ measure-command {(echo "select 1 from dual")|sqlplus jsun/$PASSWD@"(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = $_)(Port = 1521))) (CONNECT_DATA = (SID =PCP1)))"} }|select-object  {"Server "+$server+" : "+$_.TotalSeconds} output look like this: "Server "+$server+" : "+$_.TotalSeconds ------------------------...

found this very helpful: https://www.thegeekstuff.com/2011/03/sar-examples/?utm_source=feedburner 1. CPU Usage of ALL CPUs (sar -u) This gives the cumulative real-time CPU usage of all CPUs. “1 3” reports for every 1 seconds a total of 3 times. Most likely you’ll focus on the last field “%idle” to see the cpu load. $ sar -u 1 3 Linux 2.6.18-194.el5PAE (dev-db) 03/26/2011 _i686_ (8 CPU) 01:27:32 PM CPU %user %nice %system %iowait %steal %idle 01:27:33 PM all 0.00 0.00 0.00 0.00 0.00 100.00 01:27:34 PM all 0.25 0.00 0.25 0.00 0.00 99.50 01:27:35 PM all 0.75 0.00 0.25 0.00 0.00 99.00 Average: all 0.33 0.00 0.17 0.00 0.00 99.50 Following are few variations: sar -u  Displays CPU usage for the current day that was collected until that point. sar -u 1 3  Displays real time CPU usage every 1 se...

Our Oracle database oracle_home and instance_name have massive change and the targets need to be removed, I can remove the host but the EM admin said he has group rule setup and do not want to delete host, so I have to delete the target one-by-one, clicking in EM console is no fun, I logon to the EM repository database and run this query to generate the script to run on EM weblogic server. select 'emcli delete_target -name='||TARGET_NAME||' -type="'||TARGET_TYPE||'"' from sysman.mgmt_targets where upper(host_name) like 'HOSTNAME%' and target_type like 'rac_database'; It generate command like below: $emcli delete_target -name=blablabla -type="rac_database" I need to run the emcli login first before run delete target... emcli login -username=sysman -password=thepassword