环境如下
ORACLE 版本11.2.0.4
OS OEL 5.7 64BIT
OGG 版本 11.2.1.0.1
开启ddl 在运行 ddl_setup.sql 时候报错如下
SQL> @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
-------------------- -----------------------------------------------------------------
1453/9 PL/SQL: SQL Statement ignored
1455/23 PL/SQL: ORA-00942: table or view does not exist
1464/9 PL/SQL: SQL Statement ignored
1466/23 PL/SQL: ORA-00942: table or view does not exist
1478/9 PL/SQL: SQL Statement ignored
1480/23 PL/SQL: ORA-00942: table or view does not exist
1485/9 PL/SQL: SQL Statement ignored
1487/23 PL/SQL: ORA-00942: table or view does not exist
1492/9 PL/SQL: SQL Statement ignored
1494/23 PL/SQL: ORA-00942: table or view does not exist
1499/9 PL/SQL: SQL Statement ignored
Line/pos Error
-------------------- -----------------------------------------------------------------
1501/23 PL/SQL: ORA-00942: table or view does not exist
1581/4 PL/SQL: SQL Statement ignored
1582/18 PL/SQL: ORA-00942: table or view does not exist
1584/4 PL/SQL: SQL Statement ignored
1585/18 PL/SQL: ORA-00942: table or view does not exist
1600/25 PL/SQL: ORA-00942: table or view does not exist
1600/4 PL/SQL: SQL Statement ignored
1602/25 PL/SQL: ORA-00942: table or view does not exist
1602/4 PL/SQL: SQL Statement ignored
DDL IGNORE TABLE
-----------------------------------
FAILED: Table does not exist
DDL IGNORE LOG TABLE
-----------------------------------
FAILED: Table does not exist
DDLAUX PACKAGE STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
No errors No errors
DDLAUX PACKAGE BODY STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
0/0 PL/SQL: Compilation unit analysis terminated
1/21 PLS-00304: cannot compile body of 'DDLAUX' without its
specification
1/21 PLS-00905: object OGG.DDLAUX is invalid
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
-----------------------------------
FAILED: Table does not exist
DDL HISTORY TABLE(1)
-----------------------------------
FAILED: Table does not exist
DDL DUMP TABLES
-----------------------------------
FAILED: Table does not exist
DDL DUMP COLUMNS
-----------------------------------
FAILED: Table does not exist
DDL DUMP LOG GROUPS
-----------------------------------
FAILED: Table does not exist
DDL DUMP PARTITIONS
-----------------------------------
FAILED: Table does not exist
DDL DUMP PRIMARY KEYS
-----------------------------------
FAILED: Table does not exist
DDL SEQUENCE
-----------------------------------
FAILED: Sequence does not exist
GGS_TEMP_COLS
-----------------------------------
FAILED: Table does not exist
GGS_TEMP_UK
-----------------------------------
FAILED: Table does not exist
DDL TRIGGER CODE STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
126/9 PL/SQL: SQL Statement ignored
128/23 PL/SQL: ORA-00942: table or view does not exist
133/21 PL/SQL: ORA-02289: sequence does not exist
133/5 PL/SQL: SQL Statement ignored
657/14 PLS-00905: object OGG.DDLAUX is invalid
657/5 PL/SQL: Statement ignored
919/25 PL/SQL: ORA-00942: table or view does not exist
919/4 PL/SQL: SQL Statement ignored
DDL TRIGGER INSTALL STATUS
-----------------------------------
OK
DDL TRIGGER RUNNING STATUS
----------------------------------------------------------------------
ENABLED
FROM "OGG" ."GGS_SETUP"
*
ERROR at line 2:
ORA-00942: table or view does not exist
FROM "OGG" ."GGS_SETUP"
*
ERROR at line 2:
ORA-00942: table or view does not exist
FROM "OGG" ."GGS_SETUP"
*
ERROR at line 2:
ORA-00942: table or view does not exist
LOCATION OF DDL TRACE FILE
------------------------------------------------------------------------------------------------------------------------
/darin/diag/rdbms/ggt/ggt/trace/ggs_ddl_trace.log
Analyzing installation status...
STATUS OF DDL REPLICATION
------------------------------------------------------------------------------------------------------------------------
ERRORS detected in installation of DDL Replication software components (6)
Script complete.
SQL>
SQL>
SQL>
SQL> @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
where is the user assigned to the GoldenGate processes.
SQL> grant GGS_GGSUSER_ROLE to ogg;
grant GGS_GGSUSER_ROLE to ogg
*
ERROR at line 1:
ORA-04098: trigger 'SYS.GGS_DDL_TRIGGER_BEFORE' is invalid and failed re-validation
查看你日志文件
Wrote file ddl_setup_set.txt
Working, please wait ...
DROP TRIGGER sys .GGS_DDL_TRIGGER_BEFORE
*
ERROR at line 1:
ORA-04080: trigger 'GGS_DDL_TRIGGER_BEFORE' does not exist
No errors.
No errors.
No errors.
Warning: Procedure created with compilation errors.
No errors.
GG_
---
OGG
Warning: Function created with compilation errors.
No errors.
No errors.
No errors.
No errors.
No errors.
No errors.
No errors.
BEGIN "OGG" .initial_setup; END;
*
ERROR at line 1:
ORA-20782: Creating GGS_DDL_RULES table:ORA-01031: insufficient privileges:ORA-01031: insufficient privileges
ORA-06512: at "OGG.INITIAL_SETUP", line 477
ORA-06512: at line 1
No errors.
Warning: Package Body created with compilation errors.
No errors.
Warning: Package created with compilation errors.
No errors.
Warning: Package Body created with compilation errors.
Errors for PACKAGE BODY "OGG".DDLAux:
LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0 PL/SQL: Compilation unit analysis terminated
1/21 PLS-00304: cannot compile body of 'DDLAUX' without its
specification
1/21 PLS-00905: object OGG.DDLAUX is invalid
Warning: Trigger created with compilation errors.
No errors.
从日志里看 应该是我的ogg用户没有权限,但是我已经把dba权限给了,以及其他很多的权限
在网查了一下 是ogg的bug
解决方法
1 赋予 GRANT CREATE TABLE,CREATE SEQUENCE TO OGG;
2 下载最新的ogg 11.2.0.2版本
我采用了方法1 结果可以了。 以后多多注意版本的兼容性,不过11.1.0.1出现这样的感觉实在是。。
ORACLE 版本11.2.0.4
OS OEL 5.7 64BIT
OGG 版本 11.2.1.0.1
开启ddl 在运行 ddl_setup.sql 时候报错如下
SQL> @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
-------------------- -----------------------------------------------------------------
1453/9 PL/SQL: SQL Statement ignored
1455/23 PL/SQL: ORA-00942: table or view does not exist
1464/9 PL/SQL: SQL Statement ignored
1466/23 PL/SQL: ORA-00942: table or view does not exist
1478/9 PL/SQL: SQL Statement ignored
1480/23 PL/SQL: ORA-00942: table or view does not exist
1485/9 PL/SQL: SQL Statement ignored
1487/23 PL/SQL: ORA-00942: table or view does not exist
1492/9 PL/SQL: SQL Statement ignored
1494/23 PL/SQL: ORA-00942: table or view does not exist
1499/9 PL/SQL: SQL Statement ignored
Line/pos Error
-------------------- -----------------------------------------------------------------
1501/23 PL/SQL: ORA-00942: table or view does not exist
1581/4 PL/SQL: SQL Statement ignored
1582/18 PL/SQL: ORA-00942: table or view does not exist
1584/4 PL/SQL: SQL Statement ignored
1585/18 PL/SQL: ORA-00942: table or view does not exist
1600/25 PL/SQL: ORA-00942: table or view does not exist
1600/4 PL/SQL: SQL Statement ignored
1602/25 PL/SQL: ORA-00942: table or view does not exist
1602/4 PL/SQL: SQL Statement ignored
DDL IGNORE TABLE
-----------------------------------
FAILED: Table does not exist
DDL IGNORE LOG TABLE
-----------------------------------
FAILED: Table does not exist
DDLAUX PACKAGE STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
No errors No errors
DDLAUX PACKAGE BODY STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
0/0 PL/SQL: Compilation unit analysis terminated
1/21 PLS-00304: cannot compile body of 'DDLAUX' without its
specification
1/21 PLS-00905: object OGG.DDLAUX is invalid
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
-----------------------------------
FAILED: Table does not exist
DDL HISTORY TABLE(1)
-----------------------------------
FAILED: Table does not exist
DDL DUMP TABLES
-----------------------------------
FAILED: Table does not exist
DDL DUMP COLUMNS
-----------------------------------
FAILED: Table does not exist
DDL DUMP LOG GROUPS
-----------------------------------
FAILED: Table does not exist
DDL DUMP PARTITIONS
-----------------------------------
FAILED: Table does not exist
DDL DUMP PRIMARY KEYS
-----------------------------------
FAILED: Table does not exist
DDL SEQUENCE
-----------------------------------
FAILED: Sequence does not exist
GGS_TEMP_COLS
-----------------------------------
FAILED: Table does not exist
GGS_TEMP_UK
-----------------------------------
FAILED: Table does not exist
DDL TRIGGER CODE STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
126/9 PL/SQL: SQL Statement ignored
128/23 PL/SQL: ORA-00942: table or view does not exist
133/21 PL/SQL: ORA-02289: sequence does not exist
133/5 PL/SQL: SQL Statement ignored
657/14 PLS-00905: object OGG.DDLAUX is invalid
657/5 PL/SQL: Statement ignored
919/25 PL/SQL: ORA-00942: table or view does not exist
919/4 PL/SQL: SQL Statement ignored
DDL TRIGGER INSTALL STATUS
-----------------------------------
OK
DDL TRIGGER RUNNING STATUS
----------------------------------------------------------------------
ENABLED
FROM "OGG" ."GGS_SETUP"
*
ERROR at line 2:
ORA-00942: table or view does not exist
FROM "OGG" ."GGS_SETUP"
*
ERROR at line 2:
ORA-00942: table or view does not exist
FROM "OGG" ."GGS_SETUP"
*
ERROR at line 2:
ORA-00942: table or view does not exist
LOCATION OF DDL TRACE FILE
------------------------------------------------------------------------------------------------------------------------
/darin/diag/rdbms/ggt/ggt/trace/ggs_ddl_trace.log
Analyzing installation status...
STATUS OF DDL REPLICATION
------------------------------------------------------------------------------------------------------------------------
ERRORS detected in installation of DDL Replication software components (6)
Script complete.
SQL>
SQL>
SQL>
SQL> @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
where is the user assigned to the GoldenGate processes.
SQL> grant GGS_GGSUSER_ROLE to ogg;
grant GGS_GGSUSER_ROLE to ogg
*
ERROR at line 1:
ORA-04098: trigger 'SYS.GGS_DDL_TRIGGER_BEFORE' is invalid and failed re-validation
查看你日志文件
Wrote file ddl_setup_set.txt
Working, please wait ...
DROP TRIGGER sys .GGS_DDL_TRIGGER_BEFORE
*
ERROR at line 1:
ORA-04080: trigger 'GGS_DDL_TRIGGER_BEFORE' does not exist
No errors.
No errors.
No errors.
Warning: Procedure created with compilation errors.
No errors.
GG_
---
OGG
Warning: Function created with compilation errors.
No errors.
No errors.
No errors.
No errors.
No errors.
No errors.
No errors.
BEGIN "OGG" .initial_setup; END;
*
ERROR at line 1:
ORA-20782: Creating GGS_DDL_RULES table:ORA-01031: insufficient privileges:ORA-01031: insufficient privileges
ORA-06512: at "OGG.INITIAL_SETUP", line 477
ORA-06512: at line 1
No errors.
Warning: Package Body created with compilation errors.
No errors.
Warning: Package created with compilation errors.
No errors.
Warning: Package Body created with compilation errors.
Errors for PACKAGE BODY "OGG".DDLAux:
LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0 PL/SQL: Compilation unit analysis terminated
1/21 PLS-00304: cannot compile body of 'DDLAUX' without its
specification
1/21 PLS-00905: object OGG.DDLAUX is invalid
Warning: Trigger created with compilation errors.
No errors.
从日志里看 应该是我的ogg用户没有权限,但是我已经把dba权限给了,以及其他很多的权限
在网查了一下 是ogg的bug
解决方法
1 赋予 GRANT CREATE TABLE,CREATE SEQUENCE TO OGG;
2 下载最新的ogg 11.2.0.2版本
我采用了方法1 结果可以了。 以后多多注意版本的兼容性,不过11.1.0.1出现这样的感觉实在是。。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26175573/viewspace-1310269/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26175573/viewspace-1310269/