Gavinsoorma.com.au



[pic]

Oracle GoldenGate 18c and GoldenGate Cloud Services Workshop

Hands-on Lab Exercise 7

Configure GoldenGate Security

Encrypt data in Trail Files

Note the current position in the local trail the extract is writing to

GGSCI (rac01.localdomain) 1> send ext1 status

Sending STATUS request to EXTRACT EXT1 ...

EXTRACT EXT1 (PID 27885)

Current status: Recovery complete: Processing data

Current read position:

Redo thread #: 2

Sequence #: 39

RBA: 135365776

Timestamp: 2019-01-20 09:24:39.000000

SCN: 0.5492606 (5492606)

Current write position:

Sequence #: 16

RBA: 332607384

Timestamp: 2019-01-20 09:24:35.625063

Extract Trail: ./dirdat/ogg1/lt

Use logdump utility to view the contents of the local Trail File sequence identified above. We will filter the display of data in the trail file to the keyword ‘DEMO’.

We will see that we can clearly see the data (in clear text) related to the DEMO.MYOBJECTS table.

GGSCI (rac01.localdomain) 4> shell ls -lrt ./dirdat/ogg1/lt*

-rw-r----- 1 oracle oinstall 2455 Jan 20 08:00 ./dirdat/ogg1/lt000000014

-rw-r----- 1 oracle oinstall 1477 Jan 20 09:00 ./dirdat/ogg1/lt000000015

-rw-r----- 1 oracle oinstall 332607384 Jan 20 09:09 ./dirdat/ogg1/lt000000016

Logdump 53 >open ./dirdat/ogg1/lt000000016

Current LogTrail is /acfs_oh/app/goldengate/dirdat/ogg1/lt000000016

Logdump 54 >ghdr on

Logdump 55 >detail on

Logdump 56 >detail data

Logdump 57 >filter string 'DEMO'

Logdump 58 >n

Scanned 10000 records, RBA 4060010, 2019/01/20 09:06:26.000.000

Scanned 20000 records, RBA 8183727, 2019/01/20 09:06:26.000.000

Scanned 30000 records, RBA 12515125, 2019/01/20 09:06:26.000.000

Scanned 40000 records, RBA 16824315, 2019/01/20 09:06:26.000.000

Scanned 50000 records, RBA 21068585, 2019/01/20 09:06:26.000.000

Scanned 60000 records, RBA 25311548, 2019/01/20 09:06:26.000.000

___________________________________________________________________

Hdr-Ind : E (x45) Partition : . (x0c)

UndoFlag : . (x00) BeforeAfter: A (x41)

RecLength : 309 (x0135) IO Time : 2019/01/20 09:06:26.000.000

IOType : 5 (x05) OrigNode : 255 (xff)

TransInd : . (x01) FormatType : R (x52)

SyskeyLen : 0 (x00) Incomplete : . (x00)

AuditRBA : 37 AuditPos : 158368272

Continued : N (x00) RecCount : 1 (x01)

2019/01/20 09:06:26.000.000 Insert Len 309 RBA 28770532

Name: PDB1.DEMO.MYOBJECTS (TDR Index: 1)

After Image: Partition 12 G m

0000 0800 0000 0400 4445 4d4f 0100 1000 0000 0c00 | ........DEMO........

504b 5f4d 594f 424a 4543 5453 0200 0400 ffff 0000 | PK_MYOBJECTS........

0300 0900 0000 0500 3734 3338 3904 0009 0000 0005 | ........74389.......

0037 3435 3735 0500 0900 0000 0500 494e 4445 5806 | .74575........INDEX.

0015 0000 0032 3031 392d 3031 2d32 303a 3030 3a32 | .....2019-01-20:00:2

333a 3037 0700 1500 0000 3230 3139 2d30 312d 3230 | 3:07......2019-01-20

3a30 383a 3530 3a35 3408 0017 0000 0013 0032 3031 | :08:50:54........201

Column 0 (x0000), Len 8 (x0008)

0000 0400 4445 4d4f | ....DEMO

Column 1 (x0001), Len 16 (x0010)

0000 0c00 504b 5f4d 594f 424a 4543 5453 | ....PK_MYOBJECTS

Column 2 (x0002), Len 4 (x0004)

ffff 0000 | ....

Column 3 (x0003), Len 9 (x0009)

0000 0500 3734 3338 39 | ....74389

Column 4 (x0004), Len 9 (x0009)

0000 0500 3734 3537 35 | ....74575

Column 5 (x0005), Len 9 (x0009)

0000 0500 494e 4445 58 | ....INDEX

Column 6 (x0006), Len 21 (x0015)

…

….

Logdump 59 >exit

[oracle@rac01 goldengate]$

Create the wallet and add the master key to the wallet.

GGSCI (rac01.localdomain) 1> dblogin useridalias oggadmin_cdb1

Successfully logged into database.

GGSCI (rac01.localdomain as oggadmin@racdb1) 2> create wallet

Created wallet at location 'dirwlt'.

Opened wallet at location 'dirwlt'.

GGSCI (rac01.localdomain as C##oggadmin@cdb1_2/CDB$ROOT) 5> shell ls -lrt ./dirwlt/*

-rw-r----- 1 oracle oinstall 381 Jan 20 09:32 ./dirwlt/cwallet.sso

GGSCI (rac01.localdomain as C##oggadmin@cdb1_2/CDB$ROOT) 10> add masterkey

2019-01-20T01:35:27Z INFO OGG-06142 Created version 1 of master key 'OGG_DEFAULT_MASTERKEY' in Oracle Wallet.

GGSCI (rac01.localdomain as C##oggadmin@cdb1_2/CDB$ROOT) 11> info masterkey

Masterkey Name: OGG_DEFAULT_MASTERKEY

Version Creation Date Status

1 2019-01-20T09:35:27.000+08:00 Current

Stop the extract processes which are currently running.

GGSCI (rac01.localdomain as oggadmin@racdb1) 7> stop extract *

Sending STOP request to EXTRACT EXT1 ...

Request processed.

Sending STOP request to EXTRACT PUMP1 ...

Request processed.

Add the ENCRYPTRAIL parameter to the extract and Issue the ETROLLOVER command to make the extract write to a new trail file which will be encrypted.

GGSCI (rac01.localdomain as oggadmin@racdb1) 16> edit params ext1

EXTRACT ext1

SETENV (ORACLE_HOME='/acfs_oh/product/12.2.0/dbhome_1')

USERIDALIAS oggadmin_cdb1

EXTTRAIL ./dirdat/ogg1/lt

ENCRYPTTRAIL AES256

SOURCECATALOG pdb1

TABLE HR.*;

TABLE DEMO.*;

~

GGSCI (rac01.localdomain as C##oggadmin@cdb1_2/CDB$ROOT) 13> alter extract ext1 etrollover

2019-01-20 09:46:57 INFO OGG-01520 Rollover performed. For each affected output trail of Version 10 or higher format, after starting the source extract, issue ALTER EXTSEQNO for that trail's reader (either pump EXTRACT or REPLICAT) to move the reader's scan to the new trail file; it will not happen automatically.

EXTRACT altered.

GGSCI (rac01.localdomain as C##oggadmin@cdb1_2/CDB$ROOT) 16> start ext1

Sending START request to MANAGER ...

EXTRACT EXT1 starting

GGSCI (rac01.localdomain as C##oggadmin@cdb1_2/CDB$ROOT) 17> info ext1

EXTRACT EXT1 Initialized 2019-01-20 09:00 Status RUNNING

Checkpoint Lag 00:00:00 (updated 00:05:12 ago)

Process ID 12024

Log Read Checkpoint Oracle Integrated Redo Logs

2019-01-20 09:46:37

SCN 0.5502556 (5502556)

Position the extract pump to read now from the(new) encrypted trail file .

GGSCI (rac01.localdomain as C##oggadmin@cdb1_2/CDB$ROOT) 18> send ext1 status

Sending STATUS request to EXTRACT EXT1 ...

EXTRACT EXT1 (PID 12024)

Current status: Recovery complete: At EOF

Current read position:

Redo thread #: 0

Sequence #: 0

RBA: 0

Timestamp: 2019-01-20 09:52:43.000000

SCN: 0.5504811 (5504811)

Current write position:

Sequence #: 17

RBA: 1418

Timestamp: 2019-01-20 09:52:46.741024

Extract Trail: ./dirdat/ogg1/lt

GGSCI (rac01.localdomain as C##oggadmin@cdb1_2/CDB$ROOT) 19> alter extract pump1 extseqno 17 extrba 1418

EXTRACT altered.

GGSCI (rac01.localdomain) 1> start pump1

Sending START request to MANAGER ...

EXTRACT PUMP1 starting

GGSCI (rac01.localdomain) 2> info pump1

EXTRACT PUMP1 Last Started 2019-01-20 09:57 Status RUNNING

Checkpoint Lag 00:00:00 (updated 00:03:15 ago)

Process ID 13862

Log Read Checkpoint File /acfs_oh/app/goldengate/dirdat/ogg1/lt000000017

First Record RBA 1418

Position the replicat to read now from the(new) encrypted trail file as well .

GGSCI (rac01.localdomain) 3> send pump1 status

Sending STATUS request to EXTRACT PUMP1 ...

EXTRACT PUMP1 (PID 13862)

Current status: Recovery complete: At EOF

Current read position:

Sequence #: 17

RBA: 1418

Timestamp: Not Available

Extract Trail: /acfs_oh/app/goldengate/dirdat/ogg1/lt

Current write position:

Sequence #: 24

RBA: 0

Timestamp: 2019-01-20 09:58:30.261497

Extract Trail: ./dirdat/ogg2/rt

GGSCI (rac01.localdomain) 11> stop rep3

Sending STOP request to REPLICAT REP3 ...

Request processed.

GGSCI (rac01.localdomain) 12> alter rep3 extseqno 24 extrba 0

2019-01-20 10:00:04 INFO OGG-06594 Replicat REP3 has been altered. Even the start up position might be updated, duplicate suppression remains active in next startup. To override duplicate suppression, start REP3 with NOFILTERDUPTRANSACTIONS option.

REPLICAT (Integrated) altered.

GGSCI (rac01.localdomain) 13> start rep3

Sending START request to MANAGER ...

REPLICAT REP3 starting

GGSCI (rac01.localdomain) 7> info rep3

REPLICAT REP3 Last Started 2019-01-20 10:00 Status RUNNING

INTEGRATED

Checkpoint Lag 00:00:00 (updated 00:00:46 ago)

Process ID 15136

Log Read Checkpoint File /acfs_oh/app/goldengate/dirdat/ogg2/rt000000024

First Record RBA 0

6

Insert data into the source MYOBJECTS table.

Source database (PDB1)

[oracle@rac01 goldengate]$ sqlplus demo/oracle@pdb1

SQL> insert into myobjects select * from all_objects;

68210 rows created.

SQL> commit;

Verify that the extract has captured the changes

GGSCI (rac01.localdomain) 36> stats ext1 latest

Sending STATS request to EXTRACT EXT1 ...

Start of Statistics at 2019-01-20 10:13:20.

Output to ./dirdat/ogg1/lt:

Extracting from PDB1.DEMO.MYOBJECTS to PDB1.DEMO.MYOBJECTS:

*** Latest statistics since 2019-01-20 10:12:08 ***

Total inserts 68210.00

Total updates 0.00

Total deletes 0.00

Total discards 0.00

Total operations 68210.00

End of Statistics.

Note the trail file the extract is currently writing to

GGSCI (rac01.localdomain) 37> send ext1 status

Sending STATUS request to EXTRACT EXT1 ...

EXTRACT EXT1 (PID 12024)

Current status: Recovery complete: Processing data

Current read position:

Redo thread #: 2

Sequence #: 40

RBA: 58705012

Timestamp: 2019-01-20 10:13:48.000000

SCN: 0.5517810 (5517810)

Current write position:

Sequence #: 17

RBA: 28772529

Timestamp: 2019-01-20 10:13:43.676415

Extract Trail: ./dirdat/ogg1/lt

GGSCI (rac01.localdomain) 38> shell ls -lrt ./dirdat/ogg1/lt*

-rw-r----- 1 oracle oinstall 2455 Jan 20 08:00 ./dirdat/ogg1/lt000000014

-rw-r----- 1 oracle oinstall 1477 Jan 20 09:00 ./dirdat/ogg1/lt000000015

-rw-r----- 1 oracle oinstall 332607384 Jan 20 09:09 ./dirdat/ogg1/lt000000016

-rw-r----- 1 oracle oinstall 28772529 Jan 20 10:12 ./dirdat/ogg1/lt000000017

Use logdump to open that trail file.

Note now that we have enabled encryption, we cannot see in clear text as we could before data of the MYOBJECTS table.

GGSCI (rac01.localdomain) 39> quit

[oracle@rac01 goldengate]$ ./logdump

Oracle GoldenGate Log File Dump Utility for Oracle

Version 18.1.0.0.0 OGGCORE_18.1.0.0.0_PLATFORMS_180928.0432

Copyright (C) 1995, 2018, Oracle and/or its affiliates. All rights reserved.

Logdump 59 >open ./dirdat/ogg1/lt000000017

Current LogTrail is /acfs_oh/app/goldengate/dirdat/ogg1/lt000000017

Logdump 60 >ghdr on

Logdump 61 >detail on

Logdump 62 >detail data

Logdump 62 >n

….

….

2019-01-20 10:40:04 INFO OGG-25234 Retrieved masterkey OGG_DEFAULT_MASTERKEY version 1 with state Active.

929c 7524 e30a 34a5 f255 c472 6f13 7e59 e3c7 d550 | ..u$..4..U.ro.~Y...P

3b46 3799 5bde 9b27 c40f 9543 497d 4fb4 d261 b65d | ;F7.[..'...CI}O..a.]

fafa 24cd 9c69 1fe6 2c50 bda7 f612 cf54 e3c2 44c2 | ..$..i..,P.....T..D.

d0ad b2e3 765e b358 8705 384d d8a3 51e7 bcc3 7d1a | ....v^.X..8M..Q...}.

f225 7020 242b ead7 a39a 89 | .%p $+.....

…

…

2019/01/20 10:38:35.000.000 Insert Len 302 RBA 3771

Name: PDB1.DEMO.MYOBJECTS (TDR Index: 1)

After Image: Partition 12 G b

a29c 2524 e20a 35a5 a00c 9573 5801 7f59 7d01 ade3 | ..%$..5....sX..Y}...

1430 9d7d 8cd9 236c 9543 d9ad 179d 9872 7334 fc01 | .0.}..#l.C.....rs4..

5787 3790 d965 bb54 eb2e aa73 51b2 b4bb 2caa 10f4 | W.7..e.T...sQ...,...

81e3 3279 b0d0 0ceb 684d 5d7e c834 e8c7 e83c 0c1c | ..2y....hM]~.4....E

1568 9c11 1c53 9963 49b6 fd74 b965 b700 3499 03af | .h...S.cI..t.e..4...

c488 9eb8 43f1 832f 44fc ed2e ad6e 6114 6ebd 74f1 | ....C../D....na.n.t.

Bad compressed block, found length of 9253 (x2425), RBA 3771

If the trail file is encrypted, set to use decryption on using decrypt command and rerun. If the decrypt command is used, wallet file or key name is incorrect, verify the encryption configuration.

Note that the remote trail file is also encrypted as well.

GGSCI (rac01.localdomain) 24> info rep3 detail

REPLICAT REP3 Last Started 2019-01-20 10:51 Status RUNNING

INTEGRATED

Checkpoint Lag 00:00:00 (updated 00:00:01 ago)

Process ID 30555

Log Read Checkpoint File /acfs_oh/app/goldengate/dirdat/ogg2/rt000000026

2019-01-20 10:59:12.000948 RBA 61425

GGSCI (rac01.localdomain) 25> quit

[oracle@rac01 goldengate]$ ./logdump

Oracle GoldenGate Log File Dump Utility for Oracle

Version 18.1.0.0.0 OGGCORE_18.1.0.0.0_PLATFORMS_180928.0432

Copyright (C) 1995, 2018, Oracle and/or its affiliates. All rights reserved.

Logdump 102 >open /acfs_oh/app/goldengate/dirdat/ogg2/rt000000026

Current LogTrail is /acfs_oh/app/goldengate/dirdat/ogg2/rt000000026

Logdump 103 >ghdr on

Logdump 104 >detail on

Logdump 105 >detail data

Logdump 106 >n

…

…

After Image: Partition 0 G s

Logdump 108 >n

___________________________________________________________________

Hdr-Ind : E (x45) Partition : . (x00)

UndoFlag : . (x00) BeforeAfter: A (x41)

RecLength : 91 (x005b) IO Time : 2019/01/20 10:38:37.721.585

IOType : 170 (xaa) OrigNode : 1 (x01)

TransInd : . (x03) FormatType : R (x52)

SyskeyLen : 0 (x00) Incomplete : . (x00)

DDR/TDR Idx: (001, 000) AuditPos : 110131780

Continued : N (x00) RecCount : 1 (x01)

2019/01/20 10:38:37.721.585 Metadata Len 91 RBA 1539

Name: PDB1

2019-01-20 11:01:27 INFO OGG-25234 Retrieved masterkey OGG_DEFAULT_MASTERKEY version 1 with state Active.

929c 7524 e30a 34a5 f255 c472 6f13 7e59 e3c7 d550 | ..u$..4..U.ro.~Y...P

3b46 3799 5bde 9b27 c40f 9543 497d 4fb4 d261 b65d | ;F7.[..'...CI}O..a.]

fafa 24cd 9c69 1fe6 2c50 bda7 f612 cf54 e3c2 44c2 | ..$..i..,P.....T..D.

d0ad b2e3 765e b358 8705 384d d8a3 51e7 bcc3 7d1a | ....v^.X..8M..Q...}.

f225 7020 242b ead7 a39a 89 | .%p $+.....

Disable the trail file encryption.

GGSCI (rac01.localdomain) 1> stop ext1

Sending STOP request to EXTRACT EXT1 ...

Request processed.

GGSCI (rac01.localdomain) 2> edit params ext1

EXTRACT ext1

SETENV (ORACLE_HOME='/acfs_oh/product/12.2.0/dbhome_1')

USERIDALIAS oggadmin_cdb1

--ENCRYPTTRAIL AES256

EXTTRAIL ./dirdat/ogg1/lt

SOURCECATALOG pdb1

TABLE HR.*;

TABLE DEMO.*;

GGSCI (rac01.localdomain) 3> start ext1

Sending START request to MANAGER ...

EXTRACT EXT1 starting

-----------------------

Oracle GoldenGate 18c and Oracle GoldenGate Cloud Workshop Lab Exercise 7

2

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download