ORACLE-DBA: Ocak 2015

15 Ocak 2015 Perşembe

Goldengate Multiple Schema Replication between

Oracle 11gR2 and Oracle 12cR1

include DDL replication

Source Host:

OS: Red Hat Enterprise Linux Server release 5.10 (Tikanga) x86_64

DB Name: source à 11gR2 (11.2.0.4.0) reside on ASM

Host Name: rac1.localdomain

Target Host:

OS: Red Hat Enterprise Linux Server release 6.6 (Santiago) x86_64

DB Name: target à 12cR1 (12.1.0.2.0) reside on ASM

Host Name: node1.localdomain

Goldengate Version: 12.1.2.1.0

Main Configuration Steps

1- Install Goldengate Software on both source and target systems

2- Export the schemas which will be replicated and import them to the target database

3- Prepare the source database

4- Prepare the target database

5- Configure extract, pump and replicat process

6- Start extract and replication proccess

7- Control DML and DDL operations

1. Install Goldengate Software on both source and target systems

Source:

[oracle@rac1 ~]$ cd /media/sf_oracle_setups/goldengate/fbo_ggs_Linux_x64_shiphome/Disk1/

[oracle@rac1 Disk1]$ sh runInstaller

[oracle@rac1 gg]$ pwd

/u01/app/oracle/product/gg

[oracle@rac1 gg]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140727.2135.1_FBO

Linux, x64, 64bit (optimized), Oracle 11g on Aug 7 2014 09:14:25

Operating system character set identified as UTF-8.

GGSCI (rac1.localdomain) 1> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

GGSCI (rac1.localdomain) 2>

Target:

[oracle@node1 goldengate]$ cd /media/sf_12c/goldengate/fbo_ggs_Linux_x64_shiphome/Disk1/

[oracle@node1 Disk1]$ sh runInstaller

[oracle@node1 gg]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140727.2135.1_FBO

Linux, x64, 64bit (optimized), Oracle 12c on Aug 7 2014 10:21:34

Operating system character set identified as UTF-8.

GGSCI (node1.localdomain) 1> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

GGSCI (node1.localdomain) 2>

2. Export the schemas which will be replicated and import them to the target database

Source:

create or replace directory pump_dir as '/u01/app/oracle/pump';

expdp userid='"/ as sysdba"' schemas=hr,scott dumpfile=scott_hr.dmp directory='pump_dir'

Target:

create or replace directory pump_dir as '/u01/app/oracle/pump';

create tablespace target_tbs datafile '+DATA' size 500m;

impdp userid='"/ as sysdba"' dumpfile=scott_hr.dmp directory='pump_dir' remap_tablespace=example:target_tbs;

grant connect, resource, unlimited tablespace to hr, scott;

***impdp can give some errors about granting roles if you dont create the roles first manually.

3. Prepare the source database

a. Switch database archivelog

[oracle@rac1 ~]$ srvctl stop database -d source

[oracle@rac1 ~]$ srvctl start database -d source -o mount

SQL> alter database archivelog;

SQL> alter database open;

b. Add supplemental log and disable recyclebin

SQL> alter database add supplemental log data;

SQL> alter system set recyclebin=off scope=spfile;

SQL> purge dba_recyclebin;

c. Create goldengate tablespace and user

SQL> create tablespace gg_tbs datafile '+DATA' size 500M reuse autoextend on;

SQL> create user gg_user identified by oracle default tablespace gg_tbs quota unlimited on gg_tbs;

SQL> grant create session, connect, resource to gg_user;

SQL> grant dba to gg_user;

SQL> grant execute on utl_file to gg_user;

d. Create the objects and roles for ddl replication

[oracle@rac1 gg]$ pwd

/u01/app/oracle/product/gg

SQL> @marker_setup.sql

--enter gg_user for goldengate schema

SQL> @ddl_setup.sql

--enter gg_user for goldengate schema

SQL> @role_setup.sql

--enter gg_user for goldengate schema

SQL> GRANT GGS_GGSUSER_ROLE TO gg_user;

SQL> @ddl_enable.sql

SQL> alter system set enable_goldengate_replication=true;

e. Add info about Goldengate DDL schema

[oracle@rac1 gg]$ ./ggsci

GGSCI (rac1.localdomain) 1> edit params ./GLOBALS

GGSCHEMA gg_user

GGSCI (rac1.localdomain) 2>

f. Create trail and discard(only on target) directories

Source:

[oracle@rac1 ~]$ mkdir -p /u01/app/oracle/product/gg/dirdat/tr

Target:

[oracle@node1 ~]$ mkdir -p /u01/app/oracle/product/gg/dirdat/tr

[oracle@node1 ~]$ mkdir -p /u01/app/oracle/product/gg/discard

g. Check manager process and port on host side

GGSCI (rac1.localdomain) 1> edit params mgr

PORT 7809

GGSCI (rac1.localdomain) 2> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER STOPPED

GGSCI (rac1.localdomain) 3> start manager

Manager started.

GGSCI (rac1.localdomain) 4> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

h. Add primary key information to the logfiles for replicated schemas

GGSCI (rac1.localdomain) 1> dblogin userid gg_user;

Password:

Successfully logged into database.

GGSCI (rac1.localdomain as gg_user@source1) 2> add schematrandata hr

2015-01-14 17:24:04 INFO OGG-01788 SCHEMATRANDATA has been added on schema hr.

2015-01-14 17:24:04 INFO OGG-01976 SCHEMATRANDATA for scheduling columns has been added on schema hr.

GGSCI (rac1.localdomain as gg_user@source1) 3> add schematrandata scott

2015-01-14 17:24:28 INFO OGG-01788 SCHEMATRANDATA has been added on schema scott.

2015-01-14 17:24:28 INFO OGG-01976 SCHEMATRANDATA for scheduling columns has been added on schema scott.

i. Add ASM entry to the tnsnames.ora for redo logs

ASM1 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.150.198)(PORT = 1521))

(CONNECT_DATA =

(INSTANCE_NAME = +ASM1)

(SERVER = dedicated)

(SERVICE_NAME = +ASM)

)

4. Prepare the target database

SQL> create tablespace gg_tbs datafile '+DATA' size 500M reuse autoextend on;

SQL> create user gg_user identified by oracle default tablespace gg_tbs quota unlimited on gg_tbs;

SQL> grant create session, connect, resource to gg_user;

SQL> grant dba to gg_user;

SQL> grant execute on utl_file to gg_user;

SQL> alter system set enable_goldengate_replication=TRUE;

5. Configure extract, pump and replicat process

Source:

GGSCI (rac1.localdomain) 1> add extract ext1, tranlog, begin now

EXTRACT added.

GGSCI (rac1.localdomain) 2> add exttrail /u01/app/oracle/product/gg/dirdat/tr, extract ext1

EXTTRAIL added.

GGSCI (rac1.localdomain) 3> edit params ext1

extract ext1

userid gg_user password oracle

tranlogoptions asmuser sys@asm1 asmpassword 123456

exttrail /u01/app/oracle/product/gg/dirdat/tr

ddl include mapped

map hr.*;

map scott.*;

table hr.*;

table scott.*;

GGSCI (rac1.localdomain) 5> add extract pump1, exttrailsource /u01/app/oracle/product/gg/dirdat/tr, begin now

EXTRACT added

GGSCI (rac1.localdomain) 7> add rmttrail /u01/app/oracle/product/gg/dirdat/tr, extract pump1

RMTTRAIL added.

GGSCI (rac1.localdomain) 6> edit params pump1

extract pump1

userid gg_user, password oracle

rmthost 192.168.150.241, mgrport 7809

rmttrail /u01/app/oracle/product/gg/dirdat/tr

passthru

table hr.*;

table scott.*;

GGSCI (rac1.localdomain) 8> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT STOPPED EXT1 00:00:00 00:31:46

EXTRACT STOPPED PUMP1 00:00:00 00:09:15

Target:

GGSCI (node1.localdomain) 1> edit params mgr

PORT 7809

GGSCI (node1.localdomain) 5> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

GGSCI (node1.localdomain) 6> edit params ./GLOBALS

checkpointtable gg_user.checkpoint

GGSCI (node1.localdomain) 1> dblogin userid gg_user

Password:

Successfully logged into database.

GGSCI (node1.localdomain as gg_user@target1) 2> add checkpointtable gg_user.checkpoint

Successfully created checkpoint table gg_user.checkpoint.

GSCI (node1.localdomain as gg_user@target1) 3> add replicat rep1, exttrail /u01/app/oracle/product/gg/dirdat/tr, begin now

REPLICAT added.

GGSCI (node1.localdomain as gg_user@target1) 4> edit params rep1

replicat rep1

assumetargetdefs

SETENV (ORACLE_SID=target1)

SETENV (ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_1)

userid gg_user, password oracle

discardfile /u01/app/oracle/product/gg/discard/rep1_discard.txt, append, megabytes 10

DDL

map hr.*, target hr.*;

map scott.*, target scott.*;

GGSCI (node1.localdomain as gg_user@target1) 6> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

REPLICAT STOPPED REP1 00:00:00 00:11:44

6. Start extract and replication proccess.

Source:

GGSCI (rac1.localdomain) 1> start extract ext1

Sending START request to MANAGER ...

EXTRACT EXT1 starting

GGSCI (rac1.localdomain) 2> start extract pump1

Sending START request to MANAGER ...

EXTRACT PUMP1 starting

GGSCI (rac1.localdomain) 3> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING EXT1 00:00:00 00:00:00

EXTRACT RUNNING PUMP1 00:00:00 00:00:08

Target:

GGSCI (node1.localdomain) 11> start replicat rep1

Sending START request to MANAGER ...

REPLICAT REP1 starting

GGSCI (node1.localdomain) 12> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

REPLICAT RUNNING REP1 00:00:00 00:00:08

7. Control DML and DDL operations

Source:

[oracle@rac1 ~]$ sqlplus hr/hr

SQL> create table emre_test (idx number);

SQL> insert into emre_test values (1);

SQL> insert into emre_test values (2);

SQL> commit;

exit;

[oracle@rac1 ~]$ sqlplus scott/scott

SQL> create table emre_test2 (names varchar2(30));

SQL> insert into emre_test2 values ('emre');

SQL> insert into emre_test2 values ('emir');

SQL> commit;

Target:

[oracle@node1 tr]$ sqlplus hr/hr

SQL> select * from emre_test;

IDX

----------

[oracle@node1 tr]$ sqlplus scott/scott

SQL> select * from emre_test2;

NAMES

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

emre

emir

9 Ocak 2015 Cuma

CONSISTENT GET MECHANISM

As you know there are 2 types of IO operations on Oracle Database

A.physical reads: these are done by reading from disk (expensive reads). However, physical reads can also occur on IO devices (such as RAW,ASM) or disk cache.

B.logical reads: these are done by reading the data blocks on buffer cache. (performance reads). There are 2 types of logical reads:

B.1. Consistent Get: this is a read on buffer cache to access the data in the block which is consistent with a given point time or SCN. Consistent gets will be increased if it needs to use undo segments to apply the uncommited changes.

B.2. DB Block Get(current get): this is a read on buffer cache to access the most up-to-date current copy of the data in the blcok. There can be only one current copy of a block.

**db block gets are generally used while DML operations

This is a very brief explanation of reads, now I will only try to explain consistent get mechanism.

What is consistent gets?

Actually, a consistent get is the number of accessing blocks in the buffer cache.

Example;

--create a tablespace with uniform extent size;

create tablespace test_tbs datafile '+DATA' size 100m extent management local uniform size 64k;

--create a table;

create table demo_objects tablespace test_tbs as select * from dba_objects where rownum between 1 and 300;

--gather the statistics

begin

dbms_stats.gather_table_stats('HR', 'DEMO_OBJECTS', granularity => 'ALL');

end;

--check the number of rows and total number of blocks

select num_rows, blocks from dba_tables where table_name='DEMO_OBJECTS';

NUM_ROWS BLOCKS

300 8

--check the data block and number of rows for each block

select dbms_rowid.ROWID_BLOCK_NUMBER(rowid) blkno, count(*) row_cnt

from demo_objects

group by dbms_rowid.ROWID_BLOCK_NUMBER(rowid)

order by 1

BLKNO ROW_CNT

131 76

132 73

133 72

134 70

135 9

--there are total 8 blocks but 5 of them are data blocks. So, what is the rest of 3 blocks?

select extent_id, block_id from dba_extents where segment_name='DEMO_OBJECTS'

EXTENT_ID BLOCK_ID

0 128

***our first block is at 128th block in the 0th extent

--header block

select header_block from dba_segments s where s.segment_name='DEMO_OBJECTS'

HEADER_BLOCK

130

Question:

if our segment header block is 130, what are the 128th and 129th blocks?

Answer:
They are segment space management blocks

AUTOMATIC SEGMENT SPACE MANAGEMENT BLOCKS (BitMapBlocks)

Before the data blocks, every segment has "some segment management blcoks" to hold some information. When we start to read a table, these blocks also cause some consistent gets.

L1 Leaf BMB---> always starts from first block at first extent, indicates the free space of blocks, can be more than one

L2 Branch BMB---> contains search hint for first L1 BMB

L3 Root BMB---> segment header and L3 Root reside in same block

For our “demo_objects” table:

Block# 128------------> L1 BMB

Block# 129------------> L2 BMB

Block# 130------------> L3 BMB and segment header

CHECK THE STATISTICS AND CONSISTENT GETS

session#1

---------

select * from demo_objects;

Statistics

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

1 recursive calls

0 db block gets

26 consistent gets

0 physical reads

0 redo size

35259 bytes sent via SQL*Net to client

760 bytes received via SQL*Net from client

21 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

300 rows processed

We have 8 blocks for “demo_objects” table but consistent gets value is 26. So, it means we accessed the blocks multiple times.

Let's make a DML operation from another session (without commit) and check the consistent gets again;

session#2

---------

SQL> update demo_objects set object_name='xxx' where rownum < 50;

49 rows updated.

session#1

---------

SQL> select * from demo_objects;

Statistics

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

0 recursive calls

0 db block gets

76 consistent gets

0 physical reads

116 redo size

35259 bytes sent via SQL*Net to client

760 bytes received via SQL*Net from client

21 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

300 rows processed

As you see, consistent gets are increased from 26 to 76. Also there is no physical reads and db_block gets.

So where are these extra consistent gets are coming from? Answer is "undo segments"

After we rollback the session, you will see that consistent gets will be 26 again.

Now, how the consistent gets are happening?

To explain the mechanism, we should understand the array size first;

array size: When you send a query to the database, the rows are fetched from the block according to the array size. The default value of array size for SQL*Plus is 15. You can change this value if you want.

SQL> show arraysize

arraysize 15

Now, check how many rows each data block has for ‘demo_objects’ table;

select dbms_rowid.ROWID_BLOCK_NUMBER(rowid) blkno, count(*) row_cnt

from demo_objects

group by dbms_rowid.ROWID_BLOCK_NUMBER(rowid)

order by 1

BLKNO ROW_CNT

131 76

132 73

133 72

134 70

135 9

we have 5 data blocks between 131 and 135

76+73+72+70+9 = 300 rows

When we send the query, database will start to read 15 rows for each fetch.

For e.g.:

*****

Block# 132 Rows=72

**For the last fetch, there is only 12 rows but as we know fetch wants to read 15 rows.

So, what will be to the excess 3 rows? These rows will be read from next block.

**Another point is Oracle starts the execution of query by finding the 1st row of the table. We can prove this from 10046 trace file:

r= number of rows

cr= consistent reads

PARSE #139848017540176:c=1000,e=955,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=1714943783,tim=793948881469

EXEC #139848017540176:c=0,e=21,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1714943783,tim=793948881532

FETCH #139848017540176:c=1000,e=242,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=1,plh=1714943783,tim=793948881828

FETCH #139848017540176:c=0,e=34,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=1714943783,tim=793948882270

FETCH #139848017540176:c=0,e=33,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=1714943783,tim=793948882561

FETCH #139848017540176:c=0,e=78,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=1714943783,tim=793948883180

FETCH #139848017540176:c=0,e=32,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=1714943783,tim=793948883455

FETCH #139848017540176:c=0,e=32,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=1714943783,tim=793948883730

FETCH #139848017540176:c=0,e=34,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=1714943783,tim=793948884373

FETCH #139848017540176:c=0,e=33,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=1714943783,tim=793948884658

FETCH #139848017540176:c=0,e=33,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=1714943783,tim=793948884979

FETCH #139848017540176:c=0,e=40,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=1714943783,tim=793948885401

FETCH #139848017540176:c=0,e=40,p=0,cr=2,cu=0,mis=0,r=15,dep=0,og=1,plh=1714943783,tim=793948885722

FETCH #139848017540176:c=0,e=85,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=1714943783,tim=793948886235

FETCH #139848017540176:c=0,e=36,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=1714943783,tim=793948886681

FETCH #139848017540176:c=0,e=31,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=1714943783,tim=793948886842

FETCH #139848017540176:c=0,e=32,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=1714943783,tim=793948887558

FETCH #139848017540176:c=0,e=35,p=0,cr=2,cu=0,mis=0,r=15,dep=0,og=1,plh=1714943783,tim=793948887873

FETCH #139848017540176:c=0,e=33,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=1714943783,tim=793948888158

FETCH #139848017540176:c=0,e=30,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=1714943783,tim=793948889540

FETCH #139848017540176:c=0,e=28,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=1714943783,tim=793948889818

FETCH #139848017540176:c=0,e=23,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=1714943783,tim=793948890134

FETCH #139848017540176:c=0,e=37,p=0,cr=2,cu=0,mis=0,r=14,dep=0,og=1,plh=1714943783,tim=793948890409

STAT #139848017540176 id=1 cnt=300 pid=0 pos=1 obj=94899 op='TABLE ACCESS FULL DEMO_OBJECTS (cr=26 pr=0 pw=0 time=1284 us cost=4 size=27900 card=300)'

As you see, the first fetch requests only 1 row and it makes 3 consistent reads.

We can say that, the first fetch used the "segment space management blocks" to find the 1st row and it makes 3 consistent gets for this operation.

Demonstration:

select * from demo_objects;

3 (first row) + 5 + 5 + 6 +6 + 1 = 26 consistent gets

We read 9+5=14 rows for the last fetch and we can check this from 10046 trace file

FETCH #139848017540176:c=0,e=28,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=1714943783,tim=793948889818

FETCH #139848017540176:c=0,e=23,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=1714943783,tim=793948890134
FETCH #139848017540176:c=0,e=37,p=0,cr=2,cu=0,mis=0,r=14,dep=0,og=1,plh=1714943783,tim=793948890409

I wrote a little complicated query to calculate the consistent gets. I assumed that it will cost 3 cr to find the 1^st row. This can change according to extent size and number of extents.

CALCULATION QUERY

select 'Finding 1st row' block_no,
1 rows_1st_count,
1 rows_2nd_count,
0 last_fetch_rows,
3 "block_touch(gets)"
from dual
union all
select to_char(d.blok_no),
d.row_count ROWs_1st_COUNT,
case
when rownum = 1 then
d.final_cnt - 1
else
d.final_cnt
end ROWS_2nd_COUNT,
d.rows_left last_fetch_rows,
case
when rownum = 1 and d.rows_left = 0 then
d.block_acc
when rownum != 1 and d.rows_left = 0 then
d.block_acc + 1
when lead(d.rows_left)
over(order by d.blok_no) is null and d.rows_left = 0 then
d.block_acc + 1
when lag(d.rows_left) over(order by d.blok_no) = 0 then
d.block_acc + 1
else
d.block_acc + 2
end "block_touch(gets)"
from (select c.*,
case
when rownum = 1 then
(c.final_cnt - c.rows_left - 1) / 15
else
(c.final_cnt - c.rows_left) / 15
end block_acc
from (select b.blok_no,
b.row_count,
case
when rownum = 1 or lag(b.rows_left)
over(order by b.blok_no) = 0 then
b.row_count
else
(b.row_count -
(15 - lag(b.rows_left) over(order by b.blok_no)))
end final_cnt,
b.rows_left
from (select a.*, mod(rr - 1, 15) rows_left
from (select dbms_rowid.rowid_block_number(rowid) blok_no,
count(*) row_count,
sum(count(*)) over(order by dbms_rowid.rowid_block_number(rowid)) rr
from demo_objects t
group by dbms_rowid.rowid_block_number(rowid)
order by 1) a) b) c) d

Query Result:

Menu

15 Ocak 2015 Perşembe

9 Ocak 2015 Cuma