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