在部署GoldenGate DDL选项时,执行ddl_setup.sql脚本报错。虽然OGG用户已经拥有了DBA权限,仍然需要显示的赋予create table和create sequence才能通过。
1. 执行ddl_setup.sql脚本
SYS@PROD1 > @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:oggadmin
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 OGGADMIN 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 OGGADMIN
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/28 PL/SQL: ORA-00942: table or view does not exist
1464/9 PL/SQL: SQL Statement ignored
1466/28 PL/SQL: ORA-00942: table or view does not exist
1478/9 PL/SQL: SQL Statement ignored
1480/28 PL/SQL: ORA-00942: table or view does not exist
1485/9 PL/SQL: SQL Statement ignored
1487/28 PL/SQL: ORA-00942: table or view does not exist
1492/9 PL/SQL: SQL Statement ignored
1494/28 PL/SQL: ORA-00942: table or view does not exist
1499/9 PL/SQL: SQL Statement ignored
Line/pos Error
---------------------------------------- -----------------------------------------------------------------
1501/28 PL/SQL: ORA-00942: table or view does not exist
1581/4 PL/SQL: SQL Statement ignored
1582/23 PL/SQL: ORA-00942: table or view does not exist
1584/4 PL/SQL: SQL Statement ignored
1585/23 PL/SQL: ORA-00942: table or view does not exist
1600/30 PL/SQL: ORA-00942: table or view does not exist
1600/4 PL/SQL: SQL Statement ignored
1602/30 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/26 PLS-00304: cannot compile body of 'DDLAUX' without its
specification
1/26 PLS-00905: object OGGADMIN.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/28 PL/SQL: ORA-00942: table or view does not exist
133/26 PL/SQL: ORA-02289: sequence does not exist
133/5 PL/SQL: SQL Statement ignored
657/19 PLS-00905: object OGGADMIN.DDLAUX is invalid
657/5 PL/SQL: Statement ignored
919/30 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 "OGGADMIN" ."GGS_SETUP"
*
ERROR at line 2:
ORA-00942: table or view does not exist
FROM "OGGADMIN" ."GGS_SETUP"
*
ERROR at line 2:
ORA-00942: table or view does not exist
FROM "OGGADMIN" ."GGS_SETUP"
*
ERROR at line 2:
ORA-00942: table or view does not exist
LOCATION OF DDL TRACE FILE
------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/prod/PROD1/trace/ggs_ddl_trace.log
Analyzing installation status...
STATUS OF DDL REPLICATION
------------------------------------------------------------------------------------------------------------------------
ERRORS detected in installation of DDL Replication software components (6)
Script complete.
2.执行一次禁用DDL脚本
@ddl_disable.sql
3. 查看OGG用户的角色,已经赋予了DBA角色
SYS@PROD1 > conn oggadmin/oggadmin
Connected.
OGGADMIN@PROD1 > select * from user_role_privs;
USERNAME GRANTED_ROLE ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
OGGADMIN CONNECT NO YES NO
OGGADMIN DBA NO YES NO
OGGADMIN RESOURCE NO YES NO
4.显示添加权限,虽然该用户已经是DBA权限了
SYS@PROD1 > grant create table,create sequence to oggadmin;
5.再次执行@ddl_setup.sql,顺利通过
SYS@PROD1 > @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:oggadmin
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 OGGADMIN 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 OGGADMIN
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/prod/PROD1/trace/ggs_ddl_trace.log
Analyzing installation status...
STATUS OF DDL REPLICATION
------------------------------------------------------------------------------------------------------------------------
SUCCESSFUL installation of DDL Replication software components
Script complete.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29047826/viewspace-1342395/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29047826/viewspace-1342395/