trigger 'SYS.GGS_DDL_TRIGGER_BEFORE' is invalid and failed re-validation
環境如下
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
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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- ORA-04098: trigger 'SYS.TR_LOGIN' is invalid and failed re-validationAI
- opatch error code 73 PrereqSession failed Invalid patch location 解決ErrorSessionAI
- 新增檔案到Sdcard出現Failed to push selection: Invalid argument問題AI
- vue報錯：[Vue warn]: Invalid prop: type check failed for prop "value". Expected Number, got String....VueAIGo
- Android執行出現android Installation failed due to invalid URI! 錯誤處理AndroidAI
- Mysql TriggerMySql
- jQuery trigger()jQuery
- ±±oracle trigger±±Oracle
- sql triggerSQL
- android模擬器 一個錯誤：X Error of failed request: BadRequest (invalid request code or no such operation)...AndroidErrorAI
- Test Oracle triggerOracle
- Oracle trigger問題Oracle
- JavaScript invalid 事件JavaScript事件
- iOS 上架報錯 This bundle is invalid 或 Invalid Image PathiOS
- SqlServer 2005 TriggerSQLServer
- python trigger 組合Python
- COMPOUND TRIGGER學習
- logon_triggerGo
- oracle trigger 同步資料Oracle
- Create a trigger TO monitoring DDL
- Remove Invalid Parentheses[難]REM
- Mysql之觸發器triggerMySql觸發器
- /proc/sysrq-trigger檔案
- PLSQL學習-【8trigger】SQL
- Database Logoff Trigger SQLDatabaseGoSQL
- TRIGGER裡的動態SQLSQL
- 禁止DDL執行的trigger
- StreamCorruptedException: invalid stream headerExceptionHeader
- Invalid double崩潰分析
- 重編譯 invalid 物件編譯物件
- How to compile Invalid Object?CompileObject
- Android Studio執行報錯INSTALL_FAILED_INVALID_APK: Split lib_slice _2_apk was defined multiple times....AndroidAIAPK
- MySQL入門-- TRIGGER（觸發器）MySql觸發器
- triggerHandler()和trigger()區別
- postman trigger xdebug session in phpstormPostmanSessionPHPORM
- PL/SQL 07 觸發器 triggerSQL觸發器
- 融會貫通學習trigger
- 限制資料庫登陸trigger資料庫