Skip to main content

Posts

Showing posts from October, 2018

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