---SYS@ orcl >@/u01/app/ogg/ddl_disable.sql
SYS@ orcl >@/u01/app/ogg/ddl_disable.sql
Trigger altered.
SYS@ orcl >@/u01/app/ogg/ddl_remove.sql
DDL replication removal script.
WARNING: this script removes all DDL replication objects and data.
You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
Enter Oracle GoldenGate schema name:scott
Working, please wait ...
Spooling to file ddl_remove_spool.txt
Script complete.
SYS@ orcl >@/u01/app/ogg/marker_remove.sql
Marker removal script.
WARNING: this script removes all marker objects and data.
You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
Enter Oracle GoldenGate schema name:scott
PL/SQL procedure successfully completed.
Sequence dropped.
Table dropped.
Script complete.

SQL> @/u01/app/ogg/marker_setup.sql

Marker setup script

You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.

Enter Oracle GoldenGate schema name:ogg


Marker setup table script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to OGG

MARKER TABLE
-------------------------------
OK

MARKER SEQUENCE
-------------------------------
OK

Script complete.
SQL> @/u01/app/ogg/ddl_setup.sql

Oracle GoldenGate DDL Replication setup script

Verifying that current user has privileges to install DDL Replication...

You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: For an Oracle 10g source, the system recycle bin must be disabled. For Oracle 11g and later, it can be enabled.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.

Enter Oracle GoldenGate schema name:ogg

Working, please wait ...
Spooling to file ddl_setup_spool.txt

Checking for sessions that are holding locks on Oracle Golden Gate metadata tables ...

Check complete.


Using OGG as a Oracle GoldenGate schema name.

Working, please wait ...

DDL replication setup script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to OGG

CLEAR_TRACE STATUS:

Line/pos                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                 No errors

CREATE_TRACE STATUS:

Line/pos                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                 No errors

TRACE_PUT_LINE STATUS:

Line/pos                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                 No errors

INITIAL_SETUP STATUS:

Line/pos                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                 No errors

DDLVERSIONSPECIFIC PACKAGE STATUS:

Line/pos                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                 No errors

DDLREPLICATION PACKAGE STATUS:

Line/pos                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                 No errors

DDLREPLICATION PACKAGE BODY STATUS:

Line/pos                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                 No errors

DDL IGNORE TABLE
-----------------------------------
OK

DDL IGNORE LOG TABLE
-----------------------------------
OK

DDLAUX    PACKAGE STATUS:

Line/pos                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                 No errors

DDLAUX PACKAGE BODY STATUS:

Line/pos                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                 No errors

SYS.DDLCTXINFO    PACKAGE STATUS:

Line/pos                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                 No errors

SYS.DDLCTXINFO    PACKAGE BODY STATUS:

Line/pos                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                 No errors

DDL HISTORY TABLE
-----------------------------------
OK

DDL HISTORY TABLE(1)
-----------------------------------
OK

DDL DUMP TABLES
-----------------------------------
OK

DDL DUMP COLUMNS
-----------------------------------
OK

DDL DUMP LOG GROUPS
-----------------------------------
OK

DDL DUMP PARTITIONS
-----------------------------------
OK

DDL DUMP PRIMARY KEYS
-----------------------------------
OK

DDL SEQUENCE
-----------------------------------
OK

GGS_TEMP_COLS
-----------------------------------
OK

GGS_TEMP_UK
-----------------------------------
OK

DDL TRIGGER CODE STATUS:

Line/pos                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                 No errors

DDL TRIGGER INSTALL STATUS
-----------------------------------
OK

DDL TRIGGER RUNNING STATUS
------------------------------------------------------------------------------------------------------------------------
ENABLED

STAYMETADATA IN TRIGGER
------------------------------------------------------------------------------------------------------------------------
OFF

DDL TRIGGER SQL TRACING
------------------------------------------------------------------------------------------------------------------------
0

DDL TRIGGER TRACE LEVEL
------------------------------------------------------------------------------------------------------------------------
0

LOCATION OF DDL TRACE FILE
------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/ogg/ogg/trace/ggs_ddl_trace.log

Analyzing installation status...


STATUS OF DDL REPLICATION
------------------------------------------------------------------------------------------------------------------------
SUCCESSFUL installation of DDL Replication software components

Script complete.
SQL> @/u01/app/ogg/role_setup.sql

GGS Role setup script

This script will drop and recreate the role GGS_GGSUSER_ROLE
To use a different role name, quit this script and then edit the params.sql script to change the gg_role parameter to the preferred name. (Do not run the script.)

You will be prompted for the name of a schema for the GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.

Enter GoldenGate schema name:ogg
Wrote file role_setup_set.txt

PL/SQL procedure successfully completed.


Role setup script complete

Grant this role to each user assigned to the Extract, GGSCI, and Manager processes, by using the following SQL command:

GRANT GGS_GGSUSER_ROLE TO <loggedUser>

where <loggedUser> is the user assigned to the GoldenGate processes.
SQL> @/u01/app/ogg/ddl_enable.sql

Trigger altered.

GGSCI (DSI) 2> DBLOGIN USERID ogg, PASSWORD ogg
Successfully logged into database.

GGSCI (DSI) 3> add trandata scott.emp_ogg

Logging of supplemental redo log data is already enabled for table SCOTT.EMP_OGG.

GGSCI (DSI) 4> add trandata scott.dept_ogg

Logging of supplemental redo log data is already enabled for table SCOTT.DEPT_OGG.

GGSCI (DSI) 5> add trandata scott.dept    

Logging of supplemental redo log data is already enabled for table SCOTT.DEPT.

GGSCI (DSI) 6> add trandata scott.emp;
ERROR: No viable tables matched specification.

GGSCI (DSI) 7> INFO TRANDATA scott.*

Logging of supplemental redo log data is disabled for table SCOTT.BONUS.

Logging of supplemental redo log data is enabled for table SCOTT.DEPT.

Columns supplementally logged for table SCOTT.DEPT: DEPTNO.

Logging of supplemental redo log data is enabled for table SCOTT.DEPT_OGG.

Columns supplementally logged for table SCOTT.DEPT_OGG: DEPTNO.

Logging of supplemental redo log data is enabled for table SCOTT.EMP.

Columns supplementally logged for table SCOTT.EMP: EMPNO.

Logging of supplemental redo log data is enabled for table SCOTT.EMP_OGG.

Columns supplementally logged for table SCOTT.EMP_OGG: EMPNO.

Logging of supplemental redo log data is disabled for table SCOTT.SALGRADE.

SYS@ orcl >conn scott/tiger
Connected.
SCOTT@ orcl >update emp_ogg set ename=''hq_orcl_1'' where empno=7934;

1 row updated.

SCOTT@ orcl >commit;

Commit complete.
GGSCI (DSI) 6> stats pump1

Sending STATS request to EXTRACT PUMP1 ...

Start of Statistics at 2019-06-20 15:40:11.

Output to ./dirdat/t1:

Extracting from SCOTT.EMP_OGG to SCOTT.EMP_OGG:

*** Total statistics since 2019-06-20 15:39:37 ***
    Total inserts                                  0.00
    Total updates                                  1.00
    Total deletes                                  0.00
    Total discards                                 0.00
    Total operations                               1.00

*** Daily statistics since 2019-06-20 15:39:37 ***
    Total inserts                                  0.00
    Total updates                                  1.00
    Total deletes                                  0.00
    Total discards                                 0.00
    Total operations                               1.00

*** Hourly statistics since 2019-06-20 15:39:37 ***
    Total inserts                                  0.00
    Total updates                                  1.00
    Total deletes                                  0.00
    Total discards                                 0.00
    Total operations                               1.00

*** Latest statistics since 2019-06-20 15:39:37 ***
    Total inserts                                  0.00
    Total updates                                  1.00
    Total deletes                                  0.00
    Total discards                                 0.00
    Total operations                               1.00

End of Statistics.

GGSCI (ogg) 7> stats rep1

Sending STATS request to REPLICAT REP1 ...

Start of Statistics at 2019-06-20 15:40:21.

Replicating from SCOTT.EMP_OGG to SCOTT.EMP_OGG:

*** Total statistics since 2019-06-20 15:39:42 ***

    No database operations have been performed.

*** Daily statistics since 2019-06-20 15:39:42 ***

    No database operations have been performed.

*** Hourly statistics since 2019-06-20 15:39:42 ***

    No database operations have been performed.

*** Latest statistics since 2019-06-20 15:39:42 ***

    No database operations have been performed.

End of Statistics.

GGSCI (DSI) 7> info pump1,detail

EXTRACT    PUMP1     Last Started 2019-06-20 15:37   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:03 ago)
Log Read Checkpoint  File ./dirdat/r1000000
                     2019-06-20 15:39:35.000000  RBA 1153

  Target Extract Trails:

  Remote Trail Name                                Seqno        RBA     Max MB

  ./dirdat/t1                                          0       1183          5

  Extract Source                          Begin             End             

  ./dirdat/r1000000                       2019-06-20 15:27  2019-06-20 15:39
  ./dirdat/r1000000                       * Initialized *   2019-06-20 15:27


Current directory    /u01/app/ogg

Report file          /u01/app/ogg/dirrpt/PUMP1.rpt
Parameter file       /u01/app/ogg/dirprm/pump1.prm
Checkpoint file      /u01/app/ogg/dirchk/PUMP1.cpe
Process file         /u01/app/ogg/dirpcs/PUMP1.pce
Stdout file          /u01/app/ogg/dirout/PUMP1.out
Error log            /u01/app/ogg/ggserr.log

GGSCI (ogg) 8> info rep1,detail

REPLICAT   REP1      Last Started 2019-06-20 15:38   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:04 ago)
Log Read Checkpoint  File ./dirdat/t1000000
                     2019-06-20 15:39:38.217835  RBA 1183

  Extract Source                          Begin             End             

  ./dirdat/t1000000                       * Initialized *   2019-06-20 15:39
  ./dirdat/t1000000                       * Initialized *   First Record    


Current directory    /u01/app/ogg

Report file          /u01/app/ogg/dirrpt/REP1.rpt
Parameter file       /u01/app/ogg/dirprm/rep1.prm
Checkpoint file      /u01/app/ogg/dirchk/REP1.cpr
Checkpoint table     ogg.ggschkpt
Process file         /u01/app/ogg/dirpcs/REP1.pcr
Stdout file          /u01/app/ogg/dirout/REP1.out
Error log            /u01/app/ogg/ggserr.log

【MySQL】如何对 SQL 语句进行跟踪（trace）？

三 其他一个相似问题

单表扫描，使用ref和range从索引获取数据一例  

http://blog.163.com/li_hx/blog/static/183991413201461853637715/

分析： 这个问题，执行计划指示使用ref效果更好，但实际执行时，指定使用range方式sql执行效率更高一些。
而且，通常情况下，ref的效率比range的效率要高，所以MySQL优先使用ref方式（这是一条启发式规则）。
但究竟是否使用ref或range，MySQL还需要通过代价估算进行比较再做决定。
代价估算是一个求近似值的过程，因为计算基于的一些值是估算得来的，并不十分精准，这就造成了计算误差。
但是，如果索引的选择率较低（如低于10%），则使用ref的效果好于range的效果的概率大。反过来说，如果索引的选择率较高，则ref未必range的效果好，但是因计算误差，使得执行计划得到了ref好于range的错误结论。
进一步讲，如果索引的选择率很高（如远高于10%，这是大概值，不精确），甚至数据存放是顺序连续的，有可能的是，尽管索引存在，但索引扫描的效果还差与全表扫描。
其他说明：尽管这个事例中的SQL使用了LIMIT子句，但其对ref和range方式的计算和比较，不构成影响。

进一步了解情况：

--这个查询,能得到多少行元组?  占全表的所有元组的百分比是多少?
去掉limit后，符合那个时间段的记录数占那个uid的88%，占全表记录数的的40%

进一步分析： 从更详细的查询执行计划看，查询执行计划结果一，显示了ref的cost是''2.77e6'', 而range的cost是’5.46e6‘，这说明优化器理所当然地认为ref比range好。
可是，鉴于实际上索引选择率太高，使得使用索引已经没有意义（但优化器不知道这一信息），所以实际上使用’force index (uid) ‘会得到更好的执行效果。
这就是这个想象的答案。

深入代码分析： 在best_access_path()函数中，比较了各种路径的代价。所以是使用ref还是range甚至full table scan，在这个函数中有计算和比较。
摘录代码中部分注释如下，能表明一些含义。
 /*
    Don''t test table scan if it can''t be better.
    Prefer key lookup if we would use the same key for scanning.

    Don''t do a table scan on InnoDB tables, if we can read the used
    parts of the row from any of the used index.
    This is because table scans uses index and we would not win
    anything by using a table scan. The only exception is INDEX_MERGE
    quick select. We can not say for sure that INDEX_MERGE quick select
    is always faster than ref access. So it''s necessary to check if
    ref access is more expensive.

    We do not consider index/table scan or range access if:

    1a) The best ''ref'' access produces fewer records than a table scan
        (or index scan, or range acces), and
    1b) The best ''ref'' executed for all partial row combinations, is
        cheaper than a single scan. The rationale for comparing

        COST(ref_per_partial_row) * E(#partial_rows)
           vs
        COST(single_scan)

        is that if join buffering is used for the scan, then scan will
        not be performed E(#partial_rows) times, but
        E(#partial_rows)/E(#partial_rows_fit_in_buffer). At this point
        in best_access_path() we don''t know this ratio, but it is
        somewhere between 1 and E(#partial_rows). To avoid
        overestimating the total cost of scanning, the heuristic used
        here has to assume that the ratio is 1. A more fine-grained
        cost comparison will be done later in this function.
    (2) This doesn''t hold: the best way to perform table scan is to to perform
        ''range'' access using index IDX, and the best way to perform ''ref''
        access is to use the same index IDX, with the same or more key parts.
        (note: it is not clear how this rule is/should be extended to
        index_merge quick selects)
    (3) See above note about InnoDB.
    (4) NOT ("FORCE INDEX(...)" is used for table and there is ''ref'' access
             path, but there is no quick select)
        If the condition in the above brackets holds, then the only possible
        "table scan" access method is ALL/index (there is no quick select).
        Since we have a ''ref'' access path, and FORCE INDEX instructs us to
        choose it over ALL/index, there is no need to consider a full table
        scan.
  */

About Me

............................................................................................................................................. ● 本文作者：小麦苗，部分内容整理自网络，若有侵权请联系小麦苗删除 ● 本文在 itpub（http://blog.itpub.net/26736162/abstract/1/）、博客园（http://www.cnblogs.com/lhrbest）和个人微信公众号（xiaomaimiaolhr）上有同步更新 ● 本文 itpub 地址：http://blog.itpub.net/26736162/abstract/1/ ● 本文博客园地址：http://www.cnblogs.com/lhrbest ● 本文 pdf 版、个人简介及小麦苗云盘地址：http://blog.itpub.net/26736162/viewspace-1624453/ ● 数据库笔试面试题库及解答：http://blog.itpub.net/26736162/viewspace-2134706/ ● DBA 宝典今日头条号地址：http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826 ............................................................................................................................................. ● QQ 群号：230161599（满）、618766405 ● 微信群：可加我微信，我拉大家进群，非诚勿扰 ● 联系我请加 QQ 好友（646634621），注明添加缘由 ● 于 2017-12-01 09:00 ~ 2017-12-31 22:00 在魔都完成 ● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解 ● 版权所有，欢迎分享本文，转载请保留出处 ............................................................................................................................................. ● 小麦苗的微店：https://weidian.com/s/793741433?wfr=c&ifr=shopdetail ● 小麦苗出版的数据库类丛书：http://blog.itpub.net/26736162/viewspace-2142121/ ............................................................................................................................................. 使用微信客户端扫描下面的二维码来关注小麦苗的微信公众号（xiaomaimiaolhr）及 QQ 群（DBA 宝典），学习最实用的数据库技术。    小麦苗的微信公众号      小麦苗的 DBA 宝典 QQ 群 2     《DBA 笔试面宝典》读者群       小麦苗的微店 .............................................................................................................................................

来自 “ITPUB 博客” ，链接：http://blog.itpub.net/26736162/viewspace-2149385/，如需转载，请注明出处，否则将追究法律责任。