PLS-00905: object GGATE.DDLAUX is invalid
SQL> @ddl_setup
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:ggate
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 GGATE 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 GGATE
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
---------------------------------------- -----------------------------------------------------------------
1464/9 PL/SQL: SQL Statement ignored
1466/25 PL/SQL: ORA-00942: table or view does not exist
1475/9 PL/SQL: SQL Statement ignored
1477/25 PL/SQL: ORA-00942: table or view does not exist
1489/9 PL/SQL: SQL Statement ignored
1491/25 PL/SQL: ORA-00942: table or view does not exist
1496/9 PL/SQL: SQL Statement ignored
1498/25 PL/SQL: ORA-00942: table or view does not exist
1503/9 PL/SQL: SQL Statement ignored
1505/25 PL/SQL: ORA-00942: table or view does not exist
1510/9 PL/SQL: SQL Statement ignored
Line/pos Error
---------------------------------------- -----------------------------------------------------------------
1512/25 PL/SQL: ORA-00942: table or view does not exist
1595/4 PL/SQL: SQL Statement ignored
1596/20 PL/SQL: ORA-00942: table or view does not exist
1598/4 PL/SQL: SQL Statement ignored
1599/20 PL/SQL: ORA-00942: table or view does not exist
1614/27 PL/SQL: ORA-00942: table or view does not exist
1614/4 PL/SQL: SQL Statement ignored
1616/27 PL/SQL: ORA-00942: table or view does not exist
1616/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/23 PLS-00304: cannot compile body of 'DDLAUX' without its
specification
1/23 PLS-00905: object GGATE.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/25 PL/SQL: ORA-00942: table or view does not exist
133/23 PL/SQL: ORA-02289: sequence does not exist
133/5 PL/SQL: SQL Statement ignored
707/16 PLS-00905: object GGATE.DDLAUX is invalid
707/5 PL/SQL: Statement ignored
992/27 PL/SQL: ORA-00942: table or view does not exist
992/4 PL/SQL: SQL Statement ignored
DDL TRIGGER INSTALL STATUS
-----------------------------------
OK
DDL TRIGGER RUNNING STATUS
------------------------------------------------------------------------------------------------------------------------
ENABLED
FROM "GGATE" ."GGS_SETUP"
*
ERROR at line 2:
ORA-00942: table or view does not exist
FROM "GGATE" ."GGS_SETUP"
*
ERROR at line 2:
ORA-00942: table or view does not exist
FROM "GGATE" ."GGS_SETUP"
*
ERROR at line 2:
ORA-00942: table or view does not exist
LOCATION OF DDL TRACE FILE
------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/pspqa03/pspqa03/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> grant create any table to ggs_user;
grant create any table to ggs_user
*
ERROR at line 1:
ORA-04098: trigger 'SYS.GGS_DDL_TRIGGER_BEFORE' is invalid and failed re-validation
SQL>
grant create any view to ggs_user;
SQL> grant create any view to ggs_user
*
ERROR at line 1:
ORA-04098: trigger 'SYS.GGS_DDL_TRIGGER_BEFORE' is invalid and failed re-validation
SQL>
SQL> grant create any procedure to ggs_user;
grant create any procedure to ggs_user
*
ERROR at line 1:
ORA-04098: trigger 'SYS.GGS_DDL_TRIGGER_BEFORE' is invalid and failed re-validation
SQL>
SQL> grant create any sequence to ggs_user;
grant create any sequence to ggs_user
*
ERROR at line 1:
ORA-04098: trigger 'SYS.GGS_DDL_TRIGGER_BEFORE' is invalid and failed re-validation
SQL>
SQL> grant create any index to ggs_user;
grant create any index to ggs_user
*
ERROR at line 1:
ORA-04098: trigger 'SYS.GGS_DDL_TRIGGER_BEFORE' is invalid and failed re-validation
SQL>
SQL> grant create any trigger to ggs_user;
grant create any trigger to ggs_user
*
ERROR at line 1:
ORA-04098: trigger 'SYS.GGS_DDL_TRIGGER_BEFORE' is invalid and failed re-validation
SQL> SQL> grant create any view to ggs_user;
grant create any view to ggs_user
*
ERROR at line 1:
ORA-04098: trigger 'SYS.GGS_DDL_TRIGGER_BEFORE' is invalid and failed re-validation
SQL>
SQL>
SQL> @ddl_remove
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:ggate
Working, please wait ...
Spooling to file ddl_remove_spool.txt
Script complete.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> grant create any table to ggate;
grant create any view to ggate;
grant create any procedure to ggate;
grant create any sequence to ggate;
grant create any index to ggate;
grant create any trigger to ggate;
grant create any view to ggate;
Grant succeeded.
SQL> SQL>
Grant succeeded.
SQL> SQL>
Grant succeeded.
SQL> SQL>
Grant succeeded.
SQL> SQL>
Grant succeeded.
SQL> SQL>
Grant succeeded.
SQL> SQL>
Grant succeeded.
SQL>
SQL>
SQL>
SQL>
SQL> @ddl_setup
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:ggate
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 GGATE 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 GGATE
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/pspqa03/pspqa03/trace/ggs_ddl_trace.log
Analyzing installation status...
STATUS OF DDL REPLICATION
------------------------------------------------------------------------------------------------------------------------
SUCCESSFUL installation of DDL Replication software components
Script complete.
then run the other script:
SQL> @role_setup
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:ggate
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> GRANT GGS_GGSUSER_ROLE TO GGATE;
Grant succeeded.
SQL> @ddl_enable
Trigger altered.
1 comment:
Hi, guys!
The following link provides the way to get all references to an Oracle object.
Also, it explains how to fix ORA-00942 error that is due to lack of permissions
http://dbpilot.net/2018/01/23/getting-all-child-objects-within-an-object/
...
Post a Comment