Replicating Users and Tablespaces with Oracle GoldenGate

Posted in: Oracle, Technical Track

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.

email

Interested in working with Gleb? Schedule a tech call.

About the Author

Regarded by his peers as an Oracle guru, Gleb is known for being able to resolve any problem related to Oracle. He loves the satisfaction of troubleshooting, and his colleagues even say that seeking Gleb’s advice regarding an issue is more efficient than looking it up. Gleb enjoys the variety of challenges he faces while working at Pythian, rather than working on the same thing every day. His areas of speciality include Oracle RAC, Exadata, RMAN, SQL tuning, high availability, storage, performance tuning, and many more. When he’s not working, running, or cycling, Gleb can be found reading.

2 Comments. Leave new

Hi Gleb,

I am one of those who asked this question and I was continuously checking how to get this done. Thank you for this, however I would like to know the source of this information; from where you got to know. I want to read about this in more detail.

I was configuring it for 11.2.0.4 setup, no idea this setup will work with 11gR2 or not, but I am going to test soon and update here.

I will really appreciate if you can share the source if any. Thanks in advance.

Thanks!
Gunny

Reply
Gleb Otochkin
January 16, 2017 9:36 am

Hi Gunny,
I have the same information as you. So, I am using documentation but I prefer to test everything what it say before take it. You have the same tools and can use it. You have the replication, some utilities provide by Oracle GoldenGate itself like logdump and diag commands in ggsci (like “send stats” and other). And of course you and I have a lot of online resources to help out. Stay tuned and good luck in your replication.

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *