Oracle GoldenGate DDL Replication setup: *** Please move GGUSER to its own tablespace ORA-06512: at line 34
I encountered an issue while running the script below. It seems that the problem arose because there were two users in the Golden Gate tablespace. I resolved this by removing one of the users, and after doing that, I ran the script again. This time, everything worked smoothly.
SQL> @ddl_setup.sqlOracle GoldenGate DDL Replication setup scriptI'm making sure that the current user has the necessary privileges to set up DDL Replication...You'll be asked for the name of a schema to associate with Oracle GoldenGate database objects. Just a quick note, if you're working with an Oracle 10g source, you need to disable the system recycle bin. For Oracle 11g and later versions, you can enable it. Oh, and remember to create the schema before you proceed with running this script. And yeah, make sure all DDL replication is stopped before you kick off this installation.Could you kindly provide the name of the schema you want to use for Oracle GoldenGate? Let's go with "gguser."Hang on a sec, I'm doing my thing...I'm recording everything in the file ddl_setup_spool.txtI'm checking if there are any sessions holding locks on Oracle Golden Gate metadata tables...All done with that check.Oh, by the way, just a quick heads-up about the error I encountered:There's an ORA-20783 error.It looks like the Oracle GoldenGate DDL Replication setup needs the GGUSER to have its very own tablespace.There's also an ORA-06512 error on line 34.Here's what I did to fix it:I found out that there were two users, GGUSER and GGUSER1, both associated with the GOLDENGATE tablespace.So, I went ahead and dropped the GGUSER1 user.Now it's just GGUSER in the GOLDENGATE tablespace.Okay, now that I've sorted that out, I'm giving the script another shot.
SQL> select USERNAME from dba_users where default_tablespace='GOLDENGATE';
USERNAME
--------------------------------------------------------------------------------
GGUSER
GGUSER1
SQL> drop user gguser1;
User gguser1 has been dropped.
SQL> select USERNAME from dba_users where default_tablespace='GOLDENGATE';
USERNAME
--------------------------------------------------------------------------------
GGUSER
All right, here we go again with the script.
SQL> @ddl_setup.sqlOracle GoldenGate DDL Replication setup scriptChecking to make sure that the current user has the necessary privileges to set up DDL Replication...You'll be asked for the name of a schema to associate with Oracle GoldenGate database objects. Just a quick note, if you're working with an Oracle 10g source, you need to disable the system recycle bin. For Oracle 11g and later versions, you can enable it. Oh, and remember to create the schema before you proceed with running this script. And yeah, make sure all DDL replication is stopped before you kick off this installation.Could you kindly provide the name of the schema you want to use for Oracle GoldenGate? Let's stick with "gguser."Hang on a sec, I'm doing my thing...I'm recording everything in the file ddl_setup_spool.txtI'm checking if there are any sessions holding locks on Oracle Golden Gate metadata tables...All done with that check.I'm using GGUSER as the schema name for Oracle GoldenGate.Okay, let's keep going...The DDL replication setup script is all done, and now I'm running a verification script...Could you please let me know the name of a schema for the GoldenGate database objects? Setting it as "GGUSER."Here's how the verification went:CLEAR_TRACE STATUS:Line/pos Error---------- -----------------------------------------------------------------No errors No errorsCREATE_TRACE STATUS:Line/pos Error---------- -----------------------------------------------------------------No errors No errorsTRACE_PUT_LINE STATUS:Line/pos Error---------- -----------------------------------------------------------------No errors No errorsINITIAL_SETUP STATUS:Line/pos Error---------- -----------------------------------------------------------------No errors No errorsDDLVERSIONSPECIFIC PACKAGE STATUS:Line/pos Error---------- -----------------------------------------------------------------No errors No errors...(Verification process continues for various components)...All the checks are good! No errors found.Just a quick note on the DDL trigger setup:The DDL trigger is up and running with no issues.It's set to be enabled.If you need to check the trace, here's the location of the DDL trace file:/ora/oracle/app/oracle/diag/rdbms/orasap/orasap/trace/ggs_ddl_trace.logI went through the installation status, and everything looks good:VERSION OF DDL REPLICATION------------------------------------------------------------------------------------------------------------------------OGGCORE_19.1.0.0.0_PLATFORMS_191017.1054STATUS OF DDL REPLICATION------------------------------------------------------------------------------------------------------------------------The installation of DDL Replication software components was successful.Script's all done.Oh, and by the way, here's what's next:
SQL> @role_setup.sql
GGS Role setup script
This script is going to drop and then recreate the role GGS_GGSUSER_ROLE
If you'd like to use a different name for the role, just quit this script and edit the params.sql script. Change the gg_role parameter to whatever you prefer. (Don't run the script though!)
You'll be asked for the name of a schema for the GoldenGate database objects. Oh, and remember, create the schema first before running this script. And yeah, stop all DDL replication before starting this installation.
Could you please provide the name of the GoldenGate schema? Let's go with "gguser."
I've saved the settings in the file role_setup_set.txt
Role setup script is complete.
To make things work, you need to grant this role to the users assigned to the Extract, GGSCI, and Manager processes. Use this SQL command:
GRANT GGS_GGSUSER_ROLE TO <loggedUser>
Replace <loggedUser> with the actual user assigned to the GoldenGate processes.
SQL> @ddl_enable.sqlSQL> GRANT GGS_GGSUSER_ROLE TO gguser;Role grant successful.And finally...
Trigger has been altered.
SQL>