Pythian Blog: Technical Track

Replicating users and tablespaces with Oracle GoldenGate

by Gleb Otochkin on Jan 6, 2017 12:00:00 AM

I decided to write this blog post after reading some comments on my older posts and several online discussions where people had problems with replicating activity related to tablespaces, and users management for Oracle databases. In particular, people wanted to replicate users and tablespaces creation and some other actions related to their management such as granting privileges and quotas. Let's have a look at how it can be done using GoldenGate. I spoke with some colleagues about problems with the replication of user or tablespaces management and checked their parameters for replicat and extract processes. I found filters and mappings to replicate certain schemas and objects. As an example mapping for one extract looked like this: 
DDL include objname orcl.scott.*
 SOURCECATALOG ORCL
 TABLE scott.*;
and a replicat mapping parameters looked similar to: 
DDL include all
 map orcl.scott.*, target scott*;
What can we expect if we apply such rules to our extract and replicat? Apparently, it will capture and apply all DDL and DML changes to objects belonging to user SCOTT. But when somebody executes a command to create the user SCOTT? It is not going to be captured because you don't run the "create user ..." as user SCOTT. You do it as a user SYS, SYSTEM or maybe another user with appropriate privileges. And user SCOTT is not an object belonging to user SCOTT. We can have a look at the dump of a GoldenGate trail file and see how "create tablespace" and "create user" look inside: 
[oracle@bigdatalite oggora]$ rlwrap ./logdump 
 ..........................
 Logdump 20 >ghdr on
 Logdump 21 >detail on 
 Logdump 22 >reclen 1500
 Reclen set to 500 
 Logdump 23 >open dirdat/od000000000
 Current LogTrail is /u01/oggora/dirdat/od000000000 
 Logdump 24 >n
 ..................
 
 Logdump 33 >n
 ___________________________________________________________________ 
 Hdr-Ind : E (x45) Partition : . (x00) 
 UndoFlag : . (x00) BeforeAfter: A (x41) 
 RecLength : 1178 (x049a) IO Time : 2017/01/03 11:58:59.000.354 
 IOType : 160 (xa0) OrigNode : 0 (x00) 
 TransInd : . (x03) FormatType : R (x52) 
 SyskeyLen : 0 (x00) Incomplete : . (x00) 
 AuditRBA : 0 AuditPos : 0 
 Continued : N (x00) RecCount : 1 (x01) 
 
 2017/01/03 11:58:59.000.354 DDLOP Len 1178 RBA 1417 
 Name: 
 After Image: Partition 0 G s 
  2c43 353d 2730 272c 2c42 373d 2730 272c 2c42 323d | ,C5='0',,B7='0',,B2= 
  2730 272c 2c43 3231 3d27 4f52 434c 272c 2c42 333d | '0',,C21='ORCL',,B3= 
  2727 2c2c 4234 3d27 272c 2c43 3132 3d27 272c 2c43 | '',,B4='',,C12='',,C 
  3133 3d27 272c 2c42 353d 2754 4142 4c45 5350 4143 | 13='',,B5='TABLESPAC 
  4527 2c2c 4236 3d27 4352 4541 5445 272c 2c42 383d | E',,B6='CREATE',,B8= 
  2727 2c2c 4239 3d27 5359 5327 2c2c 4332 363d 2753 | '',,B9='SYS',,C26='S 
  5953 272c 2c43 3235 3d27 272c 2c43 373d 2727 2c2c | YS',,C25='',,C7='',, 
  4338 3d27 272c 2c43 393d 2756 414c 4944 272c 2c43 | C8='',,C9='VALID',,C 
  3130 3d27 3127 2c2c 4331 313d 2727 2c2c 4733 3d27 | 10='1',,C11='',,G3=' 
  272c 2c43 3134 3d27 272c 2c43 3230 3d27 272c 2c43 | ',,C14='',,C20='',,C 
  3135 3d27 4e4f 272c 2c43 3233 3d27 4e4f 272c 2c43 | 15='NO',,C23='NO',,C 
  3139 3d27 3137 272c 2c43 3137 2827 3127 293d 274e | 19='17',,C17('1')='N 
  4c53 5f4e 554d 4552 4943 5f43 4841 5241 4354 4552 | LS_NUMERIC_CHARACTER
 
 .......................
  274e 4c53 5f4e 4348 4152 5f43 4f4e 565f 4558 4350 | 'NLS_NCHAR_CONV_EXCP 
  272c 2c43 3138 2827 3137 2729 3d27 4641 4c53 4527 | ',,C18('17')='FALSE' 
  2c2c 4731 343d 2753 5953 272c 2c43 3232 3d27 3027 | ,,G14='SYS',,C22='0' 
  2c2c 4332 373d 2727 2c2c 4331 3d63 7265 6174 6520 | ,,C27='',,C1=create 
  7461 626c 6573 7061 6365 206f 6767 6464 6c00 | tablespace oggddl.
  
 Logdump 34 >n
 ___________________________________________________________________ 
 Hdr-Ind : E (x45) Partition : . (x00) 
 UndoFlag : . (x00) BeforeAfter: A (x41) 
 RecLength : 1503 (x05df) IO Time : 2017/01/03 12:01:08.000.378 
 IOType : 160 (xa0) OrigNode : 0 (x00) 
 TransInd : . (x03) FormatType : R (x52) 
 SyskeyLen : 0 (x00) Incomplete : . (x00) 
 AuditRBA : 0 AuditPos : 0 
 Continued : N (x00) RecCount : 1 (x01) 
 
 2017/01/03 12:01:08.000.378 DDLOP Len 1503 RBA 2689 
 Name: 
 After Image: Partition 0 G s 
  2c43 353d 2730 272c 2c42 373d 2730 272c 2c42 323d | ,C5='0',,B7='0',,B2= 
  2730 272c 2c43 3231 3d27 4f52 434c 272c 2c42 333d | '0',,C21='ORCL',,B3= 
  2727 2c2c 4234 3d27 4f47 4744 4c27 2c2c 4331 323d | '',,B4='OGGDL',,C12= 
  2727 2c2c 4331 333d 274f 4747 444c 272c 2c42 353d | '',,C13='OGGDL',,B5= 
  2755 5345 5227 2c2c 4236 3d27 4352 4541 5445 272c | 'USER',,B6='CREATE', 
  2c42 383d 2727 2c2c 4239 3d27 5359 5327 2c2c 4332 | ,B8='',,B9='SYS',,C2 
  363d 2753 5953 272c 2c43 3235 3d27 272c 2c43 373d | 6='SYS',,C25='',,C7= 
  2727 2c2c 4338 3d27 272c 2c43 393d 2756 414c 4944 | '',,C8='',,C9='VALID 
  272c 2c43 3130 3d27 3127 2c2c 4331 313d 2727 2c2c | ',,C10='1',,C11='',, 
  4733 3d27 272c 2c43 3134 3d27 272c 2c43 3230 3d27 | G3='',,C14='',,C20=' 
  272c 2c43 3135 3d27 4e4f 272c 2c43 3233 3d27 4e4f | ',,C15='NO',,C23='NO 
  272c 2c43 3139 3d27 3137 272c 2c43 3137 2827 3127 | ',,C19='17',,C17('1'
 
 .................................
 
  3d27 3027 2c2c 4332 373d 2727 2c2c 4331 3d63 7265 | ='0',,C27='',,C1=cre 
  6174 6520 7573 6572 206f 6767 646c 2069 6465 6e74 | ate user oggdl ident 
  6966 6965 6420 6279 2020 5641 4c55 4553 2027 533a | ified by VALUES 'S: 
  3031 3532 3839 4232 4245 4534 4531 3536 3535 3046 | 015289B2BEE4E156550F 
  3132 3938 4542 4633 4439 4245 3535 3234 4631 4133 | 1298EBF3D9BE5524F1A3 
  4132 3241 4136 3133 3837 3246 3544 3236 3844 4641 | A22AA613872F5D268DFA 
  3b48 3a45 3639 3146 4131 3137 3842 3238 3530 3039 | ;H:E691FA1178B285009 
  4146 3831 3230 3233 4334 4534 4338 423b 543a 4338 | AF812023C4E4C8B;T:C8 
  4538 4245 3935 4139 3539 4331 4445 3042 3835 3941 | E8BE95A959C1DE0B859A 
  4435 4433 3833 3944 3436 3246 3232 3633 4239 3435 | D5D3839D462F2263B945 
  3246 3939 3038 4546 3537 3842 4135 3344 4243 3237 | 2F9908EF578BA53DBC27 
  3130 3135 3339 4236 4246 3038 4344 3243 3931 3346 | 101539B6BF08CD2C913F 
  4231 3645 3337 3342 4144 3543 3230 4346 3734 4332 | B16E373BAD5C20CF74C2 
  3938 3639 4544 3131 4342 3933 3345 3935 3342 3232 | 9869ED11CB933E953B22 
  3642 3743 4345 3231 3841 3143 3936 4339 3332 3044 | 6B7CCE218A1C96C9320D 
  3936 4538 3734 3630 4438 4337 4646 3738 4436 3b34 | 96E87460D8C7FF78D6;4 
  3141 3731 4242 3144 4343 3841 3142 3227 2064 6566 | 1A71BB1DCC8A1B2' def 
  6175 6c74 2074 6162 6c65 7370 6163 6520 6f67 6764 | ault tablespace oggd 
  646c 00 | dl.
You can clearly see the command was executed by user "SYS", so we should include commands executed by "SYS" to the DDL mapping. Also any other user who could create or modify a tablespace or user. In reality if you don't really know who is going to do that you need to include all DDL to your replication and then exclude objects and schemas you don't want to replicate. Let's go and setup a simple Oracle GoldenGate (OGG) replication from Oracle to Oracle database with support for users and tablespaces management. I used a 12.1.0.2 container database as a source and replicated from an ORCL pluggable database to an 11.2.0.4 database using 12.2.0.1.1 OGG. In first I setup an extract on my source side. Here is my parameter file for the extract: 
 
 [oracle@bigdatalite]$ cat dirprm/ggextddl.prm 
 extract ggextddl
 setenv (ORACLE_SID = 'cdb')
 setenv (ORACLE_HOME = '/u01/app/oracle/product/12.1.0.2/dbhome_1')
 userid c##ogg,password AACAAAAAAAAAAAIARIXFKCQBMFIGFARA, BLOWFISH, ENCRYPTKEY DEFAULT
 RMTHOSTOPTIONS 
 RMTHOST bigdatalite, MGRPORT 7829
 RMTFILE dirdat/od, MEGABYTES 2, PURGE
 DDL include all
 SOURCECATALOG ORCL
 TABLE *.*;
Adding and starting the extract: 
GGSCI (bigdatalite.localdomain) 1> add extract ggextddl, integrated tranlog, begin now 
 EXTRACT (Integrated) added.
 
 
 GGSCI (bigdatalite.localdomain) 2> dblogin userid c##ogg,password AACAAAAAAAAAAAIARIXFKCQBMFIGFARA, BLOWFISH, ENCRYPTKEY DEFAULT
 Successfully logged into database CDB$ROOT.
 
 GGSCI (bigdatalite.localdomain as c##ogg@cdb/CDB$ROOT) 3> register extract ggextddl database container (orcl)
 
 2016-12-29 10:29:11 INFO OGG-02003 Extract GGEXTDDL successfully registered with database at SCN 26429336.
 
 GGSCI (bigdatalite.localdomain as c##ogg@cdb/CDB$ROOT) 4> start extract ggextddl
 
 Sending START request to MANAGER ...
 EXTRACT GGEXTDDL starting
As you can see from the parameter file I am capturing all DDL changes for ORCL pluggable database including all tables. For simplicity I didn't use an OGG pump and replicated directly to replicat side. The replicat parameter file is here : 
[oracle@bigdatalite]$ cat dirprm/ggrep.prm 
 replicat ggrep
 setenv (ORACLE_SID = 'test')
 setenv (ORACLE_HOME = '/u01/app/oracle/product/11.2.0.4/dbhome_1')
 userid c##ogg@test,password AACAAAAAAAAAAAIARIXFKCQBMFIGFARA, BLOWFISH, ENCRYPTKEY DEFAULT
 HANDLECOLLISIONS
 DDL include all
 map orcl.*.*, target *.*;
Adding the replicat: 
GGSCI (bigdatalite.localdomain) 1> dblogin userid c##ogg@test,password AACAAAAAAAAAAAIARIXFKCQBMFIGFARA, BLOWFISH, ENCRYPTKEY DEFAULT
 Successfully logged into database.
 
 GGSCI (bigdatalite.localdomain as c##ogg@test) 2> add checkpointtable c##ogg.checkpoints
 
 Successfully created checkpoint table c##ogg.checkpoints.
 
 GGSCI (bigdatalite.localdomain as c##ogg@test) 3> add replicat ggrep, exttrail dirdat/od,checkpointtable c##ogg.checkpoints
 REPLICAT added.
 
 
 GGSCI (bigdatalite.localdomain as c##ogg@test) 4> start replicat ggrep
 
 Sending START request to MANAGER ...
 REPLICAT GGREP starting
Having the replication setup and running let's try to create a tablespace on the source 12c database: 
orcl> show con_name
 
 CON_NAME
 ------------------------------
 ORCL
 orcl> 
 
 orcl> alter session set db_create_file_dest='/u01/app/oracle/oradata/cdb/orcl';
 
 Session altered.
 
 orcl> create tablespace oggddl;
 
 Tablespace created.
 
 orcl> select file_name from dba_data_files where tablespace_name='OGGDDL';
 
 FILE_NAME
 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 /u01/app/oracle/oradata/cdb/orcl/CDB/E5EA2B69B54E6CCDE0430100007F0F59/datafile/o1_mf_oggddl_d6bdx3qy_.dbf
 
 orcl>
What will we see on the destination? 
test> select file_name from dba_data_files where tablespace_name='OGGDDL';
 
 FILE_NAME
 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 /u02/app/oracle/oradata/TEST/datafile/o1_mf_oggddl_d6bf15rs_.dbf
 
 test>
Great, so far it works fine. Now we are adding a user to the source: 
orcl> create user oggddl identified by welcome1 default tablespace oggddl;
 
 User created.
 
 orcl>
But on the target our replicat crashed by an error. From the log we can see the cause: 
2016-12-29 11:13:29 ERROR OGG-00519 Oracle GoldenGate Delivery for Oracle, ggrep.prm: Fatal error executing DDL replication: error [Error code [972], ORA-00972: identifier is too long SQL /* GOLDENGATE_DDL_REPLICATION */ create user oggddl identified by VALUES 'S:3DABA25913D83AFC90A32463679D8D0AA4F2F5474C3A9D5A214EBCDD3F37;H:E691FA1178B285009AF812023C4E4C8B;T:EE5343D14469BD6913A24FB4A0928126F791BDAA630460D4B7319B04EBA8DD9E508B69FA888E267F32E88DAF447B2273B245103B9085FE8AD9EED5A5FBFBB419C49F93959BE380D2857B90CF744094D3;41A71BB1DCC8A1B2' default tablespace oggddl /* GOLDENGATE_DDL_REPLICATION */], no error handler present.
 2016-12-29 11:13:29 ERROR OGG-01668 Oracle GoldenGate Delivery for Oracle, ggrep.prm: PROCESS ABENDING.
It looked like we hit a problem with the password value length in the command. It was too big to run in Oracle 11.2.0.4 and it couldn't digest it. If we repeat the command in 12.1.0.2 it works fine: 
orcl> create user oggddl identified by VALUES 'S:3DABA25913D83AFC90A32463679D8D0AA4F2F5474C3A9D5A214EBCDD3F37;H:E691FA1178B285009AF812023C4E4C8B;T:EE5343D14469BD6913A24FB4A0928126F791BDAA630460D4B7319B04EBA8DD9E508B69FA888E267F32E88DAF447B2273B245103B9085FE8AD9EED5A5FBFBB419C49F93959BE380D2857B90CF744094D3;41A71BB1DCC8A1B2' default tablespace oggddl;
 
 User created.
 
 orcl>
But it is failing in 11.2.0.4 : 
test> create user oggddl identified by VALUES 'S:3DABA25913D83AFC90A32463679D8D0AA4F2F5474C3A9D5A214EBCDD3F37;H:E691FA1178B285009AF812023C4E4C8B;T:EE5343D14469BD6913A24FB4A0928126F791BDAA630460D4B7319B04EBA8DD9E508B69FA888E267F32E88DAF447B2273B245103B9085FE8AD9EED5A5FBFBB419C49F93959BE380D2857B90CF744094D3;41A71BB1DCC8A1B2' default tablespace oggddl;
 create user oggddl identified by VALUES 'S:3DABA25913D83AFC90A32463679D8D0AA4F2F5474C3A9D5A214EBCDD3F37;H:E691FA1178B285009AF812023C4E4C8B;T:EE5343D14469BD6913A24FB4A0928126F791BDAA630460D4B7319B04EBA8DD9E508B69FA888E267F32E88DAF447B2273B245103B9085FE8AD9EED5A5FBFBB419C49F93959BE380D2857B90CF744094D3;41A71BB1DCC8A1B2' default tablespace oggddl
  *
 ERROR at line 1:
 ORA-00972: identifier is too long
 
 
 test>
I didn't find any obvious solution or simple workaround for this. Maybe one of our readers can tell us in the comments how it can be solved. In my case, I switched to another 12.1.0.2 database as a target instead of 11g. The replication with 12.1.0.2 as a target database worked perfectly without any issues. I was able to replicate tablespaces, users, roles, and grants. Here is an example replicat parameter file used for my replication to the 12c database: 
[oracle@bigdatalite oggora]$ cat dirprm/ggrep.prm 
 replicat ggrep
 setenv (ORACLE_SID = 'tst')
 setenv (ORACLE_HOME = '/u01/app/oracle/product/12.1.0.2/dbhome_1')
 userid c##ogg@tst,password AACAAAAAAAAAAAIARIXFKCQBMFIGFARA, BLOWFISH, ENCRYPTKEY DEFAULT
 DDL include all
 mapexclude orcl.oggdltst.*
 map orcl.*.*, target *.*;
It replicates a creation or altering of a tablespace, user, granting any roles and, of course, DDL related to the schema objects like creating a table or other actions. Also, you can see that I've excluded one schema from the DML replication. All the DDL changes for that oggdltst schema will be replicated but not any DML for tables in the schema. I added it just as an example. You can come up with a more elaborate and complex configuration to filter only what you really need. Here is an example of how it looks for the oggdltst schema. On the source: 
orcl> create tablespace oggddltst;
 
 Tablespace created.
 
 orcl> create user oggdltst identified by welcome1 default tablespace oggddltst;
 
 User created.
 
 orcl> alter user oggdltst quota unlimited on oggddltst;
 
 User altered.
 
 orcl> grant resource to oggdltst;
 
 Grant succeeded.
 
 orcl> create table oggdltst.test_tab (id number, my_str varchar2(10), constraint id_pk primary key (id), supplemental log data (primary key) columns);
 
 Table created.
 
 orcl> insert into oggdltst.test_tab values (1,'test1');
 
 1 row created.
 
 orcl> commit;
On the target: 
tst> select default_tablespace from dba_users where username='OGGDLTST';
 
 DEFAULT_TABLESPACE
 ------------------------------
 OGGDDLTST
 
 tst> col username for a20
 tst> select * from dba_ts_quotas where username='OGGDLTST';
 
 TABLESPACE_NAME  USERNAME  BYTES  MAX_BYTES BLOCKS  MAX_BLOCKS DRO
 ------------------------------ -------------------- ---------------- ---------------- ---------------- ---------------- ---
 OGGDDLTST  OGGDLTST  0  -1  0  -1 NO
 
 tst> col grantee for a20
 tst> col granted_role for a20
 tst> select * from dba_role_privs where grantee='OGGDLTST';
 
 GRANTEE  GRANTED_ROLE  ADM DEL DEF COM
 -------------------- -------------------- --- --- --- ---
 OGGDLTST  RESOURCE  NO NO YES NO
 
 tst> desc oggdltst.test_tab
  Name  Null? Type
  ----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
  ID  NOT NULL NUMBER
  MY_STR  VARCHAR2(10)
 
 tst> select * from oggdltst.test_tab;
 
 no rows selected
 
 tst>
In summary, the replication of users and tablespaces is not difficult. It requires minimal configuration and works correctly unless you use some non-default options. Also, you need to keep in mind the source and target versions and make sure the requested object or action can be replicated from higher to lower versions.

No Comments Yet

Let us know what you think

See all

See all

Subscribe by email
Follow us
Love Your Data
Pythian Logo

© 2024 All rights reserved.