The drop is in the ocean, and the ocean is in the drop. Who understands, and knows this? He Himself creates the wondrousplay of the world. He Himself contemplates it, and understands its true essence. 1 How rare are those who contemplate this spiritual wisdom. Through this, the supreme state of liberation is attained. 1 Pause The night is in the day, and the day is in the night. The same is true of hot and cold. No one else knows Hisstate and extent; without the Guru, this is not understood. 2 The female is in the male, and the male is in the female.Understand this, O God-realized being! The meditation is in the music, and knowledge is in meditation. Become Gurmukh, andspeak the Unspoken Speech. 3 The Light is in the mind, and the mind is in the Light. The Guru brings the five sensestogether, like brothers. Nanak is forever a sacrifice to those who enshrine love for the One Word of the Shabad.
Index of all the posts of Gurpartap Singh's Blog
The version of the database is 11.2.0.1, which is a 2 node RAC but all tests are done from node 1. The OS is RHEL 64 bit.
I was working on one of the vendor application where the session will go inactive and will put a table lock on the most popular table and the application will get stuck. We were not able to find the sql_id or sql_text of teh sql, though we know the session id. Once I kill that sid we are back in bussiness and all locks were gone. Then I did some research and found the sql_text of that session from logmier. Logminer is very friendly in 11gr2 and has a very good GUI interface in Grid control for 11gr2 databases.
the sql_text I found was the insert statement and that puzzled me. How can an insert statement block the table. I knew something to do with index on foreign key but I knew it happens with update statement but didn't know that it can happen with insert statement as well.
So, I simulated the situation in the test database of same version and same platform and found if we insert in the child table with a non indexed foreign key, it places "enq: TM-contention" on the parent table. So any DML session on the parent table will hang and if most of the transactions are going on that parent table then all those processes will hang you will run out of processes and in turn the database will hang. I have seen this behaviour most of the time on Vendor applications where thorugh testing of application is not done with full production load.
Here are all sql scripts to tackle such problem including the cron script that will email you the details of such a problem if it happens.
I am using the default famous schema scott here. If its not there just install it in your database to do this simple test.
The structure of the two tables involved is given below. Table dept is a parent table and the table EMP is
tha child to it. There is no index on column (scott.emp.dept)
CREATE TABLE SCOTT.DEPT
(
DEPTNO NUMBER(2),
DNAME VARCHAR2(14 BYTE),
LOC VARCHAR2(13 BYTE)
)
TABLESPACE USERS
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
CREATE UNIQUE INDEX SCOTT.PK_DEPT ON SCOTT.DEPT
(DEPTNO)
LOGGING
TABLESPACE USERS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
ALTER TABLE SCOTT.DEPT ADD (
CONSTRAINT PK_DEPT
PRIMARY KEY
(DEPTNO)
USING INDEX SCOTT.PK_DEPT);
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
CREATE TABLE SCOTT.EMP
(
EMPNO NUMBER(4),
ENAME VARCHAR2(10 BYTE),
JOB VARCHAR2(9 BYTE),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2)
)
TABLESPACE USERS
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
CREATE UNIQUE INDEX SCOTT.PK_EMP ON SCOTT.EMP
(EMPNO)
LOGGING
TABLESPACE USERS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
ALTER TABLE SCOTT.EMP ADD (
CONSTRAINT PK_EMP
PRIMARY KEY
(EMPNO)
USING INDEX SCOTT.PK_EMP);
ALTER TABLE SCOTT.EMP ADD (
CONSTRAINT FK_DEPTNO
FOREIGN KEY (DEPTNO)
REFERENCES SCOTT.DEPT (DEPTNO));
===================================================================================================
I will do insert in the emp table and that will place a exlcusive lock on both the tables child table (emp) and
parent table (dept) .
Lets see that:
login as sysdba and execute following to check any locked objects:
SQL> select * from v$locked_object;
no rows selected
SQL>
Now will execute the following script to check blocking locks:
col blocking_status format a130
select l1.inst_id,s1.username '@' s1.machine
' Session status is ' s1.status ' ( SID=' s1.sid ' ) is blocking '
s2.username '@' s2.machine ' ( SID=' s2.sid ' ) ' AS blocking_status
from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2
SQL> col blocking_status format a130
SQL> select l1.inst_id,s1.username '@' s1.machine
2 ' Session status is ' s1.status ' ( SID=' s1.sid ' ) is blocking '
3 s2.username '@' s2.machine ' ( SID=' s2.sid ' ) ' AS blocking_status
from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
4 5 where s1.sid=l1.sid and s2.sid=l2.sid
6 and l1.BLOCK=1 and l2.request > 0
7 and l1.id1 = l2.id1
8 and l2.id2 = l2.id2
9 ;
no rows selected
SQL>
Now execute the following to see the status of the active sessions i.e. what are active sessions doing in the database:
set line 300
set pages 1000
column inst_id format 9
column sid format 99999
column sql_id format a15
column LAST_CALL_ET format 9999999999
column event format a50
column a format a70
select b.INST_ID,b.sid,b.sql_id,
b.LAST_CALL_ET,b.event,b.username' 'b.machineb.program' '
b.BLOCKING_INSTANCE' 'b.BLOCKING_SESSIONservice_name'' a
from gv$session b
where b.event<>'SQL*Net message from client'
and b.event<> 'rdbms ipc message'
and b.event not like '%gcs remote message%'
and service_name not like '%SYS$BACKGROUND%'
order by b.inst_id,b.username,b.sql_id
;
SQL> set line 300
SQL> set pages 1000
SQL> column inst_id format 9
SQL> column sid format 99999
SQL> column sql_id format a15
SQL> column LAST_CALL_ET format 9999999999
column event format a50
SQL> SQL> column a format a70
SQL> select b.INST_ID,b.sid,b.sql_id,
2 b.LAST_CALL_ET,b.event,b.username' 'b.machineb.program' '
3 b.BLOCKING_INSTANCE' 'b.BLOCKING_SESSIONservice_name'' a
4 from gv$session b
5 where b.event<>'SQL*Net message from client'
6 and b.event<> 'rdbms ipc message'
7 and b.event not like '%gcs remote message%'
8 and service_name not like '%SYS$BACKGROUND%'
9 order by b.inst_id,b.username,b.sql_id
10 ;
INST_ID SID SQL_ID LAST_CALL_ET EVENT A
------- ------ --------------- ------------ -------------------------------------------------- ----------------------------------------------------------------------
1 6 91jar5kj4q216 0 PX Deq: Execute Reply SYS restart.rac.meditate.comsqlplus@restart.rac.meditate.com (TNS V1-V3) SY
S$USERS
1 167 91jar5kj4q216 0 PX Deq: Execution Msg SYS restart.rac.meditate.comoracle@restart.rac.meditate.coma (PZ99) SYS$USER
S
1 363 0 Streams AQ: waiting for messages in the queue SYS restart.rac.meditate.comaoraagent.bin@restart.rac.meditate.com (TNS V1-V3
) SYS$USERS
2 446 91jar5kj4q216 0 PX Deq: reap credit SYS restart.rac.meditate.comoracle@restart (PZ99) SYS$USERS
4 rows selected.
SQL>
Now lets start the transaction:
Open two new putty windows and login as scott in both of them:
In window 2 do following:
select * from emp;
select * from dept;
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------ ---------- ---------- ----------
3 SCOTT CIO1 7839 17-NOV-11 2000 20 2
1 SCOTT CIO 7839 16-NOV-11 2000 20 1
2 SCOTT CIO1 7839 16-NOV-11 2000 20 2
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
17 rows selected.
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
1 COSTCENTER VANCOUVER
3 COSTCENTER VANCOUVER
2 EXPENSES SURREY
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
7 rows selected.
SQL>
In same session do:
insert into EMP values (4,'SCOTT','CIO1',7839,sysdate,2000,20,2);
Here is the output:
SQL> insert into EMP values (4,'SCOTT','CIO1',7839,sysdate,2000,20,2);
1 row created.
SQL>
Please note ---don't commit or rollback here.
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Now in window 1:
Run the status check query:
select b.INST_ID,b.sid,b.sql_id,
b.LAST_CALL_ET,b.event,b.username' 'b.machineb.program' '
b.BLOCKING_INSTANCE' 'b.BLOCKING_SESSIONservice_name'' a
from gv$session b
where b.event<>'SQL*Net message from client'
and b.event<> 'rdbms ipc message'
and b.event not like '%gcs remote message%'
and service_name not like '%SYS$BACKGROUND%'
order by b.inst_id,b.username,b.sql_id
;
SQL> l
1 select b.INST_ID,b.sid,b.sql_id,
2 b.LAST_CALL_ET,b.event,b.username' 'b.machineb.program' '
3 b.BLOCKING_INSTANCE' 'b.BLOCKING_SESSIONservice_name'' a
4 from gv$session b
5 where b.event<>'SQL*Net message from client'
6 and b.event<> 'rdbms ipc message'
7 and b.event not like '%gcs remote message%'
8 and service_name not like '%SYS$BACKGROUND%'
9 order by b.inst_id,b.username,b.sql_id
10*
SQL> /
INST_ID SID SQL_ID LAST_CALL_ET EVENT A
------- ------ --------------- ------------ -------------------------------------------------- ----------------------------------------------------------------------
1 6 91jar5kj4q216 0 PX Deq: Execute Reply SYS restart.rac.meditate.comsqlplus@restart.rac.meditate.com (TNS V1-V3) SY
S$USERS
1 167 91jar5kj4q216 0 PX Deq: Execution Msg SYS restart.rac.meditate.comoracle@restart.rac.meditate.com (PZ99) SYS$USER
S
1 363 0 Streams AQ: waiting for messages in the queue SYS restart.rac.meditate.comoraagent.bin@restart.rac.meditate.com (TNS V1-V3
) SYS$USERS
2 48 g12bmbazvj86x 0 direct path read DBSNMP gridemagent@restart (TNS V1-V3) SYS$USERS
2 446 91jar5kj4q216 0 PX Deq: reap credit SYS restart.rac.meditate.comoracle@lrestart9 (PZ99) SYS$USERS
5 rows selected.
SQL>
Now run the query to see the locked objects, and see object_id 73201 and 73199 are locked with lock mode 3 i.e exclusive row lock
on table.
SQL> select * from v$locked_object;
XIDUSN XIDSLOT XIDSQN OBJECT_ID SESSION_ID ORACLE_USERNAME OS_USER_NAME PROCESS LOCKED_MODE
---------- ---------- ---------- ---------- ---------- ------------------------------ ------------------------------ ------------------------ -----------
10 2 224330 73201 128 SCOTT oracle 14937 3
10 2 224330 73199 128 SCOTT oracle 14937 3
2 rows selected.
SQL>
Now see where these object ids map to and they are mapping to SCOTT.EMP and SCOTT.DEPT
SQL> col OBJECT_NAME format a10
SQL> select object_id,object_name,owner from dba_objects where object_id in (73201,73199);
OBJECT_ID OBJECT_NAM OWNER
---------- ---------- ------------------------------
73201 EMP SCOTT
73199 DEPT SCOTT
2 rows selected.
SQL>
SQL>
Right now there are no blocking locks.
col blocking_status format a130
select l1.inst_id,s1.username '@' s1.machine
' Session status is ' s1.status ' ( SID=' s1.sid ' ) is blocking '
s2.username '@' s2.machine ' ( SID=' s2.sid ' ) ' AS blocking_status
from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2
;
SQL> col blocking_status format a130
SQL> select l1.inst_id,s1.username '@' s1.machine
2 ' Session status is ' s1.status ' ( SID=' s1.sid ' ) is blocking '
3 s2.username '@' s2.machine ' ( SID=' s2.sid ' ) ' AS blocking_status
4 from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
5 where s1.sid=l1.sid and s2.sid=l2.sid
6 and l1.BLOCK=1 and l2.request > 0
7 and l1.id1 = l2.id1
8 and l2.id2 = l2.id2
9 ;
no rows selected
SQL>
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Now in window 3 excute the following sql:
update dept set DEPTNO=4 where DEPTNO=3;
SQL> show user
USER is "SCOTT"
SQL> update dept set DEPTNO=4 where DEPTNO=3;
and it will hang. now lets see what is going on in the database.
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Go to session 1 again and run the status query:
set line 300
set pages 1000
column inst_id format 9
column sid format 99999
column sql_id format a15
column LAST_CALL_ET format 9999999999
column event format a50
column a format a70
select b.INST_ID,b.sid,b.sql_id,
b.LAST_CALL_ET,b.event,b.username' 'b.machineb.program' '
b.BLOCKING_INSTANCE' 'b.BLOCKING_SESSIONservice_name'' a
from gv$session b
where b.event<>'SQL*Net message from client'
and b.event<> 'rdbms ipc message'
and b.event not like '%gcs remote message%'
and service_name not like '%SYS$BACKGROUND%'
order by b.inst_id,b.username,b.sql_id
;
SQL> set line 300
SQL> set pages 1000
SQL> column inst_id format 9
SQL> column sid format 99999
SQL> column sql_id format a15
SQL> column LAST_CALL_ET format 9999999999
SQL> column event format a50
column a format a70
SQL> SQL> select b.INST_ID,b.sid,b.sql_id,
b.LAST_CALL_ET,b.event,b.username' 'b.machineb.program' '
2 3 b.BLOCKING_INSTANCE' 'b.BLOCKING_SESSIONservice_name'' a
4 from gv$session b
5 where b.event<>'SQL*Net message from client'
and b.event<> 'rdbms ipc message'
and b.event not like '%gcs remote message%'
6 7 8 and service_name not like '%SYS$BACKGROUND%'
9 order by b.inst_id,b.username,b.sql_id
10 ;
INST_ID SID SQL_ID LAST_CALL_ET EVENT A
------- ------ --------------- ------------ -------------------------------------------------- ----------------------------------------------------------------------
1 286 d4qj9ft3rf761 63 enq: TM - contention SCOTT restart.rac.meditate.comsqlplus@restart.rac.meditate.com (TNS V1-V3) 1
128SYS$USERS
1 6 91jar5kj4q216 0 PX Deq: Execute Reply SYS restart.rac.meditate.comsqlplus@restart.rac.meditate.com (TNS V1-V3) SY
S$USERS
1 167 91jar5kj4q216 0 PX Deq: Execution Msg SYS restart.rac.meditate.comoracle@restart.rac.meditate.com (PZ99) SYS$USER
S
1 363 1 Streams AQ: waiting for messages in the queue SYS restart.rac.meditate.comoraagent.bin@restart.rac.meditate.com (TNS V1-V3
) SYS$USERS
2 446 91jar5kj4q216 0 PX Deq: reap credit SYS restart.rac.meditate.comoracle@restart (PZ99) SYS$USERS
5 rows selected.
SQL>
SQL>
See, session 286 is haing a wait event of "enq: TM - contention"
Now check the blocking locks as:
col blocking_status format a130
select l1.inst_id,s1.username '@' s1.machine
' Session status is ' s1.status ' ( SID=' s1.sid ' ) is blocking '
s2.username '@' s2.machine ' ( SID=' s2.sid ' ) ' AS blocking_status
from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2
;
SQL> col blocking_status format a130
SQL> select l1.inst_id,s1.username '@' s1.machine
' Session status is ' s1.status ' ( SID=' s1.sid ' ) is blocking '
2 3 s2.username '@' s2.machine ' ( SID=' s2.sid ' ) ' AS blocking_status
4 from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
5 where s1.sid=l1.sid and s2.sid=l2.sid
6 and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
7 8 and l2.id2 = l2.id2 ;
INST_ID BLOCKING_STATUS
------- ----------------------------------------------------------------------------------------------------------------------------------
1 SCOTT@restart.rac.meditate.com Session status is INACTIVE ( SID=128 ) is blocking SCOTT@restart.rac.meditate.com ( SID=286 )
1 row selected.
SQL>
So, sid 128 with the session status being inactive is blocking sid 286, believe me here it sid 286 is running update on emp.
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Now I opened a new session in new window 4 that will execute insert in dept (parent table):
SQL> show user
USER is "SCOTT"
SQL> insert into dept values (5,'ENJOYMENT','HOLLYWOOD');
Its, also hung.
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Lets run status check query again in window 1 again:
set line 300
set pages 1000
column inst_id format 9
column sid format 99999
column sql_id format a15
column LAST_CALL_ET format 9999999999
column event format a50
column a format a70
select b.INST_ID,b.sid,b.sql_id,
b.LAST_CALL_ET,b.event,b.username' 'b.machineb.program' '
b.BLOCKING_INSTANCE' 'b.BLOCKING_SESSIONservice_name'' a
from gv$session b
where b.event<>'SQL*Net message from client'
and b.event<> 'rdbms ipc message'
and b.event not like '%gcs remote message%'
and service_name not like '%SYS$BACKGROUND%'
order by b.inst_id,b.username,b.sql_id
;
SQL> set line 300
set pages 1000
SQL> SQL> column inst_id format 9
SQL> column sid format 99999
SQL> column sql_id format a15
column LAST_CALL_ET format 9999999999
SQL> SQL> column event format a50
SQL> column a format a70
SQL> select b.INST_ID,b.sid,b.sql_id,
2 b.LAST_CALL_ET,b.event,b.username' 'b.machineb.program' '
3 b.BLOCKING_INSTANCE' 'b.BLOCKING_SESSIONservice_name'' a
from gv$session b
4 5 where b.event<>'SQL*Net message from client'
6 and b.event<> 'rdbms ipc message'
7 and b.event not like '%gcs remote message%'
and service_name not like '%SYS$BACKGROUND%'
8 9 order by b.inst_id,b.username,b.sql_id
10 ;
INST_ID SID SQL_ID LAST_CALL_ET EVENT A
------- ------ --------------- ------------ -------------------------------------------------- ----------------------------------------------------------------------
1 286 d4qj9ft3rf761 522 enq: TM - contention SCOTT restart.rac.meditate.comsqlplus@restart.rac.meditate.com (TNS V1-V3) 1
128SYS$USERS
1 445 g41gxvszt15g2 135 enq: TM - contention SCOTT restart.rac.meditate.comsqlplus@restart.rac.meditate.com (TNS V1-V3)
SYS$USERS
1 167 91jar5kj4q216 0 PX Deq: Execution Msg SYS restart.rac.meditate.comoracle@restart.rac.meditate.com (PZ99) SYS$USER
S
1 6 91jar5kj4q216 0 SQL*Net message to client SYS restart.rac.meditate.comsqlplus@restart.rac.meditate.com (TNS V1-V3) SY
S$USERS
1 363 0 Streams AQ: waiting for messages in the queue SYS restart.rac.meditate.comoraagent.bin@restart.rac.meditate.com (TNS V1-V3
) SYS$USERS
2 446 91jar5kj4q216 0 PX Deq: reap credit SYS restart.rac.meditate.comoracle@restart (PZ99) SYS$USERS
6 rows selected.
SQL>
now session 286 and session 445 are hung and wait event is "enq: TM - contention" i.e. a table lock.
445 is trying to insert in parent table.
Lets see the blocking sessions now:
col blocking_status format a130
select l1.inst_id,s1.username '@' s1.machine
' Session status is ' s1.status ' ( SID=' s1.sid ' ) is blocking '
s2.username '@' s2.machine ' ( SID=' s2.sid ' ) ' AS blocking_status
from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2
;
SQL> col blocking_status format a130
SQL> select l1.inst_id,s1.username '@' s1.machine
2 ' Session status is ' s1.status ' ( SID=' s1.sid ' ) is blocking '
3 s2.username '@' s2.machine ' ( SID=' s2.sid ' ) ' AS blocking_status
4 from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
5 where s1.sid=l1.sid and s2.sid=l2.sid
6 and l1.BLOCK=1 and l2.request > 0
7 and l1.id1 = l2.id1
8 and l2.id2 = l2.id2 ;
INST_ID BLOCKING_STATUS
------- ----------------------------------------------------------------------------------------------------------------------------------
1 SCOTT@restart.rac.meditate.com Session status is INACTIVE ( SID=128 ) is blocking SCOTT@restart.rac.meditate.com ( SID=445 )
1 SCOTT@restart.rac.meditate.com Session status is INACTIVE ( SID=128 ) is blocking SCOTT@restart.rac.meditate.com ( SID=286 )
2 rows selected.
SQL>
Now lets see locks in more detail. Execute the following query against blocking session :
col OSUSER format a10
col TERMINAL format a15
col username format a10
col OBJECT_NAME format a20
col "Session Status" format a17
SELECT B.SID, C.USERNAME, C.OSUSER, C.TERMINAL, C.status "Session Status",
DECODE(B.ID2, 0, A.OBJECT_NAME,
'Trans-'to_char(B.ID1)) OBJECT_NAME, B.TYPE,
DECODE(B.LMODE,0,'--Waiting--',
1,'Null',
2,'Row Share',
3,'Row Excl i.e. TM - contention',
4,'Share',
5,'Sha Row Exc',
6,'Exclusive',
'Other') "Lock Mode",
DECODE(B.REQUEST,0,' ',
1,'Null',
2,'Row Share',
3,'Row Excl',
4,'Share',
5,'Sha Row Exc',
6,'Exclusive',
'Other') "Req Mode"
from DBA_OBJECTS A, V$LOCK B, V$SESSION C
where A.OBJECT_ID(+) = B.ID1
and B.SID = C.SID
AND c.sid = 128
and C.USERNAME is not null
order by B.SID, B.ID2;
SQL> col OSUSER format a10
SQL> col TERMINAL format a15
SQL> col username format a10
SQL> col OBJECT_NAME format a20
col "Session Status" format a17
SQL> SQL> SELECT B.SID, C.USERNAME, C.OSUSER, C.TERMINAL, C.status "Session Status",
DECODE(B.ID2, 0, A.OBJECT_NAME,
2 3 'Trans-'to_char(B.ID1)) OBJECT_NAME, B.TYPE,
DECODE(B.LMODE,0,'--Waiting--',
4 5 1,'Null',
6 2,'Row Share',
7 3,'Row Excl i.e. TM - contention',
4,'Share',
8 9 5,'Sha Row Exc',
6,'Exclusive',
10 11 'Other') "Lock Mode",
DECODE(B.REQUEST,0,' ',
1,'Null',
12 13 14 2,'Row Share',
3,'Row Excl',
15 16 4,'Share',
17 5,'Sha Row Exc',
18 6,'Exclusive',
19 'Other') "Req Mode"
from DBA_OBJECTS A, V$LOCK B, V$SESSION C
20 21 where A.OBJECT_ID(+) = B.ID1
22 and B.SID = C.SID
23 AND c.sid = 128
24 and C.USERNAME is not null
order by B.SID, B.ID2; 25
SID USERNAME OSUSER TERMINAL Session Status OBJECT_NAME TY Lock Mode Req Mode
------ ---------- ---------- --------------- ----------------- -------------------- -- ----------------------------- -----------
128 SCOTT oracle pts/1 INACTIVE ORA$BASE AE Share
128 SCOTT oracle pts/1 INACTIVE DEPT TM Row Excl i.e. TM - contention
128 SCOTT oracle pts/1 INACTIVE EMP TM Row Excl i.e. TM - contention
128 SCOTT oracle pts/1 INACTIVE Trans-655362 TX Exclusive
4 rows selected.
SQL>
Observe its lock mode is "TM Row Excl" i.e. enqueue is "TM - contention" i.e. table level lock on tables DEPT and EMP.
Now lets see what v$transaction is showing by running the following query:
col Seesion_status format a17
col Transaction_Status format a25
SELECT vs.sid,
vs.username,
vs.status Seesion_status,
vs.sql_id,
vs.event,
vt.start_time ,
vt.status Transaction_Status,
vt.START_SCN,
vt.DEPENDENT_SCN,
vt.XID
FROM v$transaction vt,v$session vs
WHERE vt.ses_addr=vs.saddr AND vs.sid=128;
SQL> col Seesion_status format a17
SQL> col Transaction_Status format a25
SQL> SELECT vs.sid,
2 vs.username,
3 vs.status Seesion_status,
4 vs.sql_id,
5 vt.start_time ,
6 vt.status Transaction_Status,
7 vt.START_SCN,
vt.DEPENDENT_SCN,
8 9 vt.XID
10 FROM v$transaction vt,v$session vs
11 WHERE vt.ses_addr=vs.saddr AND vs.sid=128;
SID USERNAME SEESION_STATUS SQL_ID EVENT START_TIME TRANSACTION_STATUS START_SCN DEPENDENT_SCN XID
------ ---------- ----------------- -------------- ------------------------------------------------- -------------------- ------------------------- ---------- ------------- ----------------
128 SCOTT INACTIVE SQL*Net message from client 11/18/11 00:16:56 ACTIVE 14702206 0 0A0002004A6C0300
1 row selected.
SQL>
Note in all above we have the session 128 locking two tables i.e. we cannot do DML against them and also the session 128 is inactive.
Now lets map v$transaction with table flashback_transaction_query and see the undo statement using the following query:
col LOGON_USER format a10
col TABLE_OWNER format a10
col TABLE_NAME format a15
col undo_chg format 9999
col OPERATION format a10
col UNDO_SQL format a30
SELECT
vs.sid,
vs.status Session_Status,
to_char(start_timestamp,'DD-MM-YY HH24:MI:SS'),
logon_user,
table_owner,
table_name,
undo_change# undo_chg,
operation,
undo_sql,
vt.status Transaction_Status,
ftq.XID,
ftq.START_SCN,
ftq.COMMIT_SCN
FROM flashback_transaction_query ftq,v$transaction vt,v$session vs
WHERE ftq.xid = vt.xid and vt.ses_addr=vs.saddr AND vs.sid=128;
SQL> col LOGON_USER format a10
SQL> col TABLE_OWNER format a10
SQL> col TABLE_NAME format a15
SQL> col undo_chg format 9999
SQL> col OPERATION format a10
SQL> col UNDO_SQL format a30
SQL> SELECT
2 vs.sid,
vs.status Session_Status,
to_char(start_timestamp,'DD-MM-YY HH24:MI:SS'),
3 4 5 logon_user,
table_owner,
table_name,
6 7 8 undo_change# undo_chg,
operation,
9 10 undo_sql,
vt.status Transaction_Status,
11 12 ftq.XID,
13 ftq.START_SCN,
14 ftq.COMMIT_SCN
FROM flashback_transaction_query ftq,v$transaction vt,v$session vs
15 WHERE ftq.xid = vt.xid and vt.ses_addr=vs.saddr AND vs.sid=128;
16
SID SESSION_ TO_CHAR(START_TIM LOGON_USER TABLE_OWNE TABLE_NAME UNDO_CHG OPERATION UNDO_SQL TRANSACTION_STATUS XID START_SCN COMMIT_SCN
------ -------- ----------------- ---------- ---------- --------------- -------- ---------- ------------------------------ ------------------------- ---------------- ---------- ----------
128 INACTIVE 18-11-11 00:16:55 SCOTT SCOTT EMP 1 INSERT delete from "SCOTT"."EMP" wher ACTIVE 0A0002004A6C0300 14702206
e ROWID = 'AAAR3xAAEAAAACUAAB'
;
128 INACTIVE 18-11-11 00:16:55 SCOTT 2 BEGIN ACTIVE 0A0002004A6C0300 14702206
2 rows selected.
SQL> SQL>
i.e. insert is the culprit and is the blocker.
To find the sql text in session 128 you need to use logminer. It has a very good interface in grid control for 11g databases.
=============================================================================================================================
I was having lots of problems when this happened on my vendor system so created a small shell script to get alerts
to my email when this type of thing happens.
Here is my script and run it every 5 minutes in cron:
$ cat locking.sh
#!/usr/bin/ksh
# Check details of blocking session
# usage:
# locking.sh $SID
# example (if sid of the database is KHEMHARAN1 use:
# locking.sh KHEMHARAN1
export PATH=$PATH:/usr/local/bin
export ORACLE_SID=$1
export ORAENV_ASK=NO
. oraenv
ps -efgrep pmongrep ${ORACLE_SID} > /dev/null
if [ $? = 1 ]
then
exit
fi
DT=`date +%Y%m%d%H%M%S`
ALERT_LOG=$HOME/gurpartap/log/alert_long_session_${ORACLE_SID}.log
echo " " > ${ALERT_LOG}
print " set head off verify off feed off pages 0 lin 120
conn system/xxxxx
set serveroutput on
set line 300
select 'long running sql 'sql_id' from 'username'@'machine' over 'LAST_CALL_ET' seconds sid='sid','serial#status
from v\$session
where
username like '%SCOTT' and
-- and service_name like '%sv1' and
(LAST_CALL_ET >180 and
status='ACTIVE')
or (LAST_CALL_ET >3600 and
status='KILLED');
SET SERVEROUTPUT ON;
declare
cursor cur is
select distinct s1.sid
from gv\$lock l1, gv\$session s1, gv\$lock l2, gv\$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;
cursor cur2 (p_sid number) is
SELECT
B.SID,
C.USERNAME,
C.OSUSER,
C.TERMINAL,
C.status "Session_Status",
DECODE(B.ID2, 0, A.OBJECT_NAME,'Trans-'to_char(B.ID1)) "OBJECT_NAME",
B.TYPE,
DECODE(B.LMODE,0,'--Waiting--',
1,'Null',
2,'Row Share',
3,'Row Excl i.e. TM - contention',
4,'Share',
5,'Sha Row Exc',
6,'Exclusive',
'Other') "Lock_Mode",
DECODE(B.REQUEST,0,' ',
1,'Null',
2,'Row Share',
3,'Row Excl',
4,'Share',
5,'Sha Row Exc',
6,'Exclusive',
'Other') "Req_Mode"
from DBA_OBJECTS A, V\$LOCK B, V\$SESSION C
where A.OBJECT_ID(+) = B.ID1
and B.SID = C.SID
AND c.sid = p_sid
and C.USERNAME is not null
order by B.SID, B.ID2;
cursor cur3 (p_sid number) is
SELECT vs.sid,
vs.username,
vs.status Seesion_status,
vs.sql_id,
vt.start_time ,
vt.status Transaction_Status,
vt.START_SCN,
vt.DEPENDENT_SCN,
vt.XID
FROM v\$transaction vt,v\$session vs
WHERE vt.ses_addr=vs.saddr AND vs.sid=p_sid;
cursor cur4 (p_sid number) is
SELECT
vs.sid,
vs.status Session_Status,
to_char(start_timestamp,'DD-MM-YY HH24:MI:SS') Start_Timestamp,
logon_user,
table_owner,
table_name,
undo_change# undo_chg,
operation,
undo_sql,
vt.status Transaction_Status,
ftq.XID,
ftq.START_SCN,
ftq.COMMIT_SCN
FROM flashback_transaction_query ftq,v\$transaction vt,v\$session vs
WHERE ftq.xid = vt.xid and vt.ses_addr=vs.saddr AND vs.sid=p_sid;
begin
for rec in cur loop
dbms_output.put_line('SID Username OSuser Terminal Session_Status TYPE OBJECT_NAME Lock_Mode Req_Mod');
for rec2 in cur2(rec.sid) loop
dbms_output.put_line(rec2.SID ' ' rec2.username ' ' rec2.osuser ' ' rec2.terminal ' ' rec2.Session_Status ' ' rec2.TYPE ' ' rec2.OBJECT_NAME ' ' rec2.Lock_Mode ' ' rec2.Req_Mode);
end loop;
dbms_output.put_line(chr(10));
dbms_output.put_line('SID Username Session_Status XID start_time TXN_Status START_SCN DEPENDENT_SCN sql_id');
for rec3 in cur3(rec.sid) loop
dbms_output.put_line(rec3.SID ' ' rec3.username ' ' rec3.Seesion_status ' ' rec3.XID ' ' rec3.start_time ' ' rec3.Transaction_Status ' ' rec3.START_SCN ' ' rec3.DEPENDENT_SCN ' ' rec3.sql_id);
end loop;
dbms_output.put_line(chr(10));
dbms_output.put_line('SID Session_Status Start_Timestamp Logon_User Operation XID Table_Owner Table_Name');
for rec4 in cur4(rec.sid) loop
dbms_output.put_line(rec4.SID ' ' rec4.Session_Status ' ' rec4.start_timestamp ' ' rec4.logon_user ' ' rec4.operation ' ' rec4.xid ' 'rec4.table_owner ' ' rec4.table_name);
end loop;
dbms_output.put_line(chr(10));
dbms_output.put_line('Operation XID START_SCN undo_sql COMMIT_SCN');
for rec5 in cur4(rec.sid) loop
dbms_output.put_line(rec5.operation ' ' rec5.xid ' ' rec5.START_SCN ' 'rec5.undo_sql ' ' rec5.COMMIT_SCN);
end loop;
end loop;
end;
/
" sqlplus -s /nolog > ${ALERT_LOG}
grep "long running sql" ${ALERT_LOG} > /dev/null
if [ $? = 0 ]
then
mailx -s "long running online sql found in ${ORACLE_SID} at ${DT}, call dba " "xxxxx.xxxx@yahoo.com" < ${ALERT_LOG} fi ========================================================================================================================== The email output from this script looks like: 'LONGRUNNINGSQL'SQL_ID'FROM'USERNAME'@'MACHINE'OVER'LAST_CALL_ET'SECONDSSID='SID','SERIAL#STATUS --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- long running sql 1gaj97tkvy8jd from SCOTT@restart.rac.meditate.com over 255 seconds sid=286,455ACTIVE 1 row selected. SID Username OSuser Terminal Session_Status TYPE OBJECT_NAME Lock_Mode Req_Mod 128 SCOTT oracle pts/1 INACTIVE AE ORA$BASE Share 128 SCOTT oracle pts/1 INACTIVE TM EMP Row Excl i.e. TM - contention 128 SCOTT oracle pts/1 INACTIVE TM DEPT Row Excl i.e. TM - contention 128 SCOTT oracle pts/1 INACTIVE TX Trans-65538 Exclusive SID Username Session_Status XID start_time TXN_Status START_SCN DEPENDENT_SCN sql_id 128 SCOTT INACTIVE 010002007A720000 11/17/11 23:36:57 ACTIVE 14694222 0 SID Session_Status Start_Timestamp Logon_User Operation XID Table_Owner Table_Name 128 INACTIVE 17-11-11 23:36:56 SCOTT INSERT 010002007A720000 SCOTT EMP 128 INACTIVE 17-11-11 23:36:56 SCOTT BEGIN 010002007A720000 Operation XID START_SCN undo_sql COMMIT_SCN INSERT 010002007A720000 14694222 delete from "SCOTT"."EMP" where ROWID = 'AAAR3xAAEAAAACUAAB'; BEGIN 010002007A720000 14694222 PL/SQL procedure successfully completed. ==========================================================================================================================
Now the fix.
I will rollback all the transactions and will create index on colum dept of table scott.emp and see what happens. To see the missing indexes on foreign key of a table use following script:
SELECT owner'.'table_name'.'column_name FROM (
SELECT c.owner,c.table_name, cc.column_name
FROM sys.dba_constraints c, sys.dba_cons_columns cc, sys.dba_constraints pk
WHERE c.constraint_name = cc.constraint_name
AND c.constraint_type = 'R'
AND pk.CONSTRAINT_NAME=c.R_CONSTRAINT_NAME
and pk.table_name like '%&parent_table_name%'
and c.owner like '%&owner%'
MINUS
SELECT i.owner,i.table_name, ic.column_name
FROM sys.dba_indexes i, sys.dba_ind_columns ic
WHERE i.index_name = ic.index_name and ic.COLUMN_POSITION=1
)
ORDER BY table_name;
Output is as:
SQL> l
1 SELECT owner'.'table_name'.'column_name FROM (
2 SELECT c.owner,c.table_name, cc.column_name
3 FROM sys.dba_constraints c, sys.dba_cons_columns cc, sys.dba_constraints pk
4 WHERE c.constraint_name = cc.constraint_name
5 AND c.constraint_type = 'R'
6 AND pk.CONSTRAINT_NAME=c.R_CONSTRAINT_NAME
7 and pk.table_name like '%&parent_table_name%'
8 and c.owner like '%&owner%'
9 MINUS
10 SELECT i.owner,i.table_name, ic.column_name
11 FROM sys.dba_indexes i, sys.dba_ind_columns ic
12 WHERE i.index_name = ic.index_name and ic.COLUMN_POSITION=1
13 )
14* ORDER BY table_name
SQL> /
Enter value for parent_table_name: DEPT
old 7: and pk.table_name like '%&parent_table_name%'
new 7: and pk.table_name like '%DEPT%'
Enter value for owner: SCOTT
old 8: and c.owner like '%&owner%'
new 8: and c.owner like '%SCOTT%'
OWNER'.'TABLE_NAME'.'COLUMN_NAME
------------------------------------------------------------------------------------------------------------------------
SCOTT.EMP.DEPTNO
1 row selected.
SQL>
CREATE INDEX SCOTT.DEPT_IX ON SCOTT.EMP
(DEPTNO)
LOGGING;
Output is as:
SQL> CREATE INDEX SCOTT.DEPT_IX ON SCOTT.EMP
(DEPTNO)
LOGGING;
2 3
Index created.
SQL>
================================================================================================
Lets repeat the scenario after the fix now:
In session 2 do following:
insert into EMP values (4,'SCOTT','CIO1',7839,sysdate,2000,20,2);
SQL> insert into EMP values (4,'SCOTT','CIO1',7839,sysdate,2000,20,2);
1 row created.
SQL>
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
In window 3 do same update and this time it doesn't hang and just updates and even commit works:
SQL> update dept set DEPTNO=4 where DEPTNO=3;
1 row updated.
SQL> commit;
Commit complete.
SQL>
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
In window 4 do same inser and this time it doesn't hang and just inserts and even commit works:
SQL> insert into dept values (5,'ENJOYMENT','HOLLYWOOD');
1 row created.
SQL> commit;
Commit complete.
SQL>
============================================================================================
============================================================================================
This body is frail; old age is overtaking it. Those whoare protected by the Guru are saved, while others die, to be reincarnated; they continue coming and going. Others die, to bereincarnated; they continue coming and going, and in the end, they depart with regret. Without the Name, there is no peace.According to one.s actions here, so are the rewards received; the self-willed manmukhs lose their honor. In the City of Death,there is pitch darkness and huge clouds of dust; neither sister nor brother is there. This body is frail; old age is overtaking it.
Thursday, November 17, 2011
Monday, November 14, 2011
Post 37 of series - Zero-Downtime Database Upgrades/Migrations Using Oracle GoldenGate
Some remain constantly imbued with the Love of the One Lord; I am forever a sacrifice to them. I dedicate my body, mind and wealth to them; bowing low, I fall at their feet. Meeting them, the mind is satisfied, and one.s hunger and thirst all depart. O Nanak, those who are attuned to the Naam are happy forever; they lovingly focus their awareness on the True Lord.
Index to all the posts of Gurpartap Singh's Blog
References:
http://www.oracle.com/technetwork/middleware/goldengate/overview/ggzerodowntimedatabaseupgrades-174928.pdf
Prerequisites:
You need to have the following prerequisites in place before you can start setting up replication for zero-downtime
migration:
1. The Oracle GoldenGate software installed on the source and target servers as described in post 16.
i.e.
http://gurpartapblog.blogspot.com/2010/11/installing-oracle-goldengate-part-16-of.html
2. The Oracle GoldenGate database User ID created on the source and target databases as in post 16.
3. The server name or IP address of the target database server
Source:
10gR1 database on Linux 32 bit running as a single instance on top of ASM.
ASM connects to the luns on SAN running openfiler.
the name of the SAN server is "filer01" and the ip is 192.168.1.116
The name of the database server is dg01 with ip 192.168.1.117
The name of the source database is "test"
Target:
11gR2 database on Red Hat Linux 64 bit running as a single instance RAC on top of Grid Infrastructure under Oracle
Restart Configuration.
ASM connects to the luns on SAN running Red Hat Linux 64 bit.
The name of the SAN server is "san2" and the ip is 192.168.1.195
The name of the database server is restart.rac.meditate.com with ip 192.168.1.196
The name of the target database is "KHEMKARAN"
You can use first 4 posts of my blog to create this machine
4. The Oracle GoldenGate Manager process up and running on the source and the target
5. TCP/IP network connectivity open from the source server to the target server's Oracle GoldenGate manager port
and visa versa
6. An understanding of the business and technical replication requirements of the planned migration
7. A few additional prerequisites are important for zero-downtime migration replication:
Backups of your Oracle GoldenGate software and working directories on the source and target servers
SQL scripts to grant any necessary object privileges required by your application during the cutover or fallback
procedure
8. Processes and scripts to move your application and users from the old to the new database and back again
Here I am creating 2 users on the source database that we will like to migrate to the new database
The name of the users are:
migrate and
source
SQL> conn / as sysdba
connected
SQL> create user migrate identified by migrate default tablespace users;
User created.
SQL> grant dba to migrate;
Grant succeeded.
SQL> conn migrate/migrate
Connected.
SQL> create table objects as select * from dba_objects;
Table created.
SQL> create table tables as select * from dba_tables;
Table created.
SQL> create table indexes as select * from dba_indexes;
Table created.
SQL>
create user source identified by source default tablespace users;
grant dba to source;
conn source/source
create table users as select * from dba_users;
create table sessions as select * from v$session;
create table sqls as select * from v$sql;
Output is as:
SQL> create user source identified by source default tablespace users;
User created.
SQL> grant dba to source;
Grant succeeded.
SQL> conn source/source
Connected.
SQL> create table users as select * from dba_users;
Table created.
SQL> create table sessions as select * from v$session;
Table created.
SQL> create table sqls as select * from v$sql;
Table created.
SQL>
-----------------------------------------------------------------------------------------
Now, I will create the same user on the target side as a part of initial load and will migrate the data on the target side.
There are different methods of doing initial load like :
You can use goldengate (Post 18),
Transfer over DB links as I will do here and you can do in in parallel if the data set is large .
Transfer with export import
Transfer with option using transportable tablespaces (Post 18)
etc...
SQL> create user migrate identified by migrate default tablespace users;
User created.
SQL> grant dba to migrate;
Grant succeeded.
SQL> conn migrate/migrate
Connected.
SQL>
SQL> create database link TEST connect to migrate identified by migrate using 'TEST';
Database link created.
SQL> select table_name from user_tables@test;
TABLE_NAME
--------------------------------------------------------------------------------
TABLES
INDEXES
OBJECTS
SQL>
SQL> create table TABLES as select * from TABLES@test;
Table created.
SQL>
SQL> create table INDEXES as select * from INDEXES@test;
Table created.
SQL> create table OBJECTS as select * from OBJECTS@test;
Table created.
SQL>
SQL> show user
USER is "MIGRATE"
SQL> select table_name from user_tables;
TABLE_NAME
------------------------------
OBJECTS
INDEXES
TABLES
SQL>
So, we have data on both on source and target sides i.e. initial load is done
create user source identified by source default tablespace users;
grant dba to source;
conn source/source
create database link SOURCE_TEST connect to source identified by source using 'TEST';
create table users as select * from users@SOURCE_TEST;
create table sessions as select * from sessions@SOURCE_TEST;
create table sqls as select * from sqls@SOURCE_TEST;
-------------------------------------------------------------------------------------------------
Output is as:
SQL> create user source identified by source default tablespace users;
User created.
SQL> grant dba to source;
Grant succeeded.
SQL> conn source/source
Connected.
SQL>
SQL> create database link SOURCE_TEST connect to source identified by source using 'TEST';
Database link created.
SQL> create table users as select * from users@SOURCE_TEST;
Table created.
SQL> create table sessions as select * from sessions@SOURCE_TEST;
Table created.
SQL> create table sqls as select * from sqls@SOURCE_TEST;
Table created.
SQL>
---------------------------------------------------------------------------------------------------------
Lets create the extract and replicats with ddl replication.
for that lets do the pre-steps for DDL replication in following post:
http://gurpartapblog.blogspot.com/2010/11/configure-ddl-synchronizationreplicatio.html
This time I hit this error so fixed it as well.
===============================================================================================
SQL> @ddl_pin GOLDENGATE
BEGIN dbms_shared_pool.keep('GOLDENGATE .DDLReplication', 'P'); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_SHARED_POOL.KEEP' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
BEGIN dbms_shared_pool.keep('GOLDENGATE .trace_put_line', 'P'); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_SHARED_POOL.KEEP' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
BEGIN dbms_shared_pool.keep('SYS. GGS_DDL_TRIGGER_BEFORE', 'R'); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_SHARED_POOL.KEEP' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
SQL> @$ORACLE_HOME/rdbms/admin/dbmspool.sql
Package created.
Grant succeeded.
View created.
Package body created.
SQL> @ddl_pin GOLDENGATE
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
SQL>
===============================================================================================
Source side:
GGSCI (dg01) 25> dblogin userid migrate, password migrate
Successfully logged into database.
GGSCI (dg01) 26> add trandata migrate.*;
ERROR: No viable tables matched specification.
GGSCI (dg01) 27>
GGSCI (dg01) 36> DBLOGIN USERID source, password source
Successfully logged into database.
GGSCI (dg01) 37> ADD TRANDATA source.*;
ERROR: No viable tables matched specification.
GGSCI (dg01) 38>
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SQL> alter system set recyclebin=OFF scope=spfile;
System altered.
SQL>
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
DBLOGIN USERID goldengate, PASSWORD goldengate
ADD EXTRACT EMIGRATE,TRANLOGS,BEGIN NOW
ADD EXTTRAIL /u01/app/oracle/product/goldengate/dirdat/mg, extract emigrate
ADD EXTRACT pmigrate, EXTTRAILSOURCE /u01/app/oracle/product/goldengate/dirdat/mg
ADD RMTTRAIL /u01/app/oracle/product/goldengate/dirdat/mg, EXTRACT PMIGRATE
edit params emigrate
EXTRACT emigrate
USERID goldengate, PASSWORD AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC, ENCRYPTKEY default
TRANLOGOPTIONS ASMUSER sys@DG01ASM, ASMPASSWORD AACAAAAAAAAAAAJASAOBIHRGNFGAYCKBHJPAGIXFGGBJUAXI, ENCRYPTKEY default
EXTTRAIL /u01/app/oracle/product/goldengate/dirdat/mg
REPORTCOUNT EVERY 5 MINUTES, RATE
DDL INCLUDE OBJNAME "migrate.*", INCLUDE OBJNAME "source.*"
TABLE migrate.*;
TABLE source.*;
edit params pmigrate
EXTRACT pmigrate
RMTHOST restart, MGRPORT 7809
RMTTRAIL /u01/app/oracle/product/goldengate/dirdat/mg
PASSTHRU
TABLE migrate.*;
TABLE source.*;
start extract EMIGRATE
start extract pmigrate
In logs we have:
2011-10-27 23:18:02 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): start extract EMIGRATE.
2011-10-27 23:18:02 INFO OGG-00963 Oracle GoldenGate Manager for Oracle, mgr.prm: Command received from GGSCI on host 192.168.1.117 (START EXTRACT EMIGRATE ).
2011-10-27 23:18:02 INFO OGG-00975 Oracle GoldenGate Manager for Oracle, mgr.prm: EXTRACT EMIGRATE starting.
2011-10-27 23:18:02 INFO OGG-00992 Oracle GoldenGate Capture for Oracle, emigrate.prm: EXTRACT EMIGRATE starting.
2011-10-27 23:18:02 INFO OGG-01635 Oracle GoldenGate Capture for Oracle, emigrate.prm: BOUNDED RECOVERY: reset to initial or altered checkpoint.
2011-10-27 23:18:03 INFO OGG-01515 Oracle GoldenGate Capture for Oracle, emigrate.prm: Positioning to begin time Oct 27, 2011 11:08:50 PM.
2011-10-27 23:18:05 INFO OGG-01516 Oracle GoldenGate Capture for Oracle, emigrate.prm: Positioned to Sequence 35, RBA 17320976, Oct 27, 2011 11:08:50 PM.
2011-10-27 23:18:05 INFO OGG-00993 Oracle GoldenGate Capture for Oracle, emigrate.prm: EXTRACT EMIGRATE started.
2011-10-27 23:18:05 INFO OGG-01052 Oracle GoldenGate Capture for Oracle, emigrate.prm: No recovery is required for target file /u01/app/oracle/product/goldengate/dirdat/mg000000, at RBA 0 (file not opened).
2011-10-27 23:18:05 INFO OGG-01478 Oracle GoldenGate Capture for Oracle, emigrate.prm: Output file /u01/app/oracle/product/goldengate/dirdat/mg is using format RELEASE 10.4/11.1.
2011-10-27 23:18:05 INFO OGG-01517 Oracle GoldenGate Capture for Oracle, emigrate.prm: Position of first record processed Sequence 35, RBA 17320976, SCN 0.1229585, Oct 27, 2011 11:08:54 PM.
2011-10-27 23:18:25 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): start extract pmigrate.
2011-10-27 23:18:25 INFO OGG-00963 Oracle GoldenGate Manager for Oracle, mgr.prm: Command received from GGSCI on host 192.168.1.117 (START EXTRACT PMIGRATE ).
2011-10-27 23:18:25 INFO OGG-00975 Oracle GoldenGate Manager for Oracle, mgr.prm: EXTRACT PMIGRATE starting.
2011-10-27 23:18:25 INFO OGG-00992 Oracle GoldenGate Capture for Oracle, pmigrate.prm: EXTRACT PMIGRATE starting.
2011-10-27 23:18:25 INFO OGG-00993 Oracle GoldenGate Capture for Oracle, pmigrate.prm: EXTRACT PMIGRATE started.
2011-10-27 23:18:30 INFO OGG-01226 Oracle GoldenGate Capture for Oracle, pmigrate.prm: Socket buffer size set to 27985 (flush size 27985).
2011-10-27 23:18:41 INFO OGG-01052 Oracle GoldenGate Capture for Oracle, pmigrate.prm: No recovery is required for target file /u01/app/oracle/product/goldengate/dirdat/mg000000, at RBA 0 (file not opened).
2011-10-27 23:18:41 INFO OGG-01478 Oracle GoldenGate Capture for Oracle, pmigrate.prm: Output file /u01/app/oracle/product/goldengate/dirdat/mg is using format RELEASE 10.4/11.1.
and
GGSCI (dg01) 63> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING ELORD1 00:00:00 00:00:04
EXTRACT RUNNING EMIGRATE 00:00:00 00:00:06
EXTRACT RUNNING ETRUTH 00:00:00 00:00:02
EXTRACT RUNNING PMIGRATE 00:00:00 00:00:04
EXTRACT RUNNING PTRUTH 00:00:00 00:00:09
GGSCI (dg01) 64>
On taget side we do:
ADD REPLICAT RMIGRATE, EXTTRAIL /u01/app/oracle/product/goldengate/dirdat/mg
REPLICAT rmigrate
ASSUMETARGETDEFS
USERID goldengate@khemkaran, PASSWORD AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC, ENCRYPTKEY default
ALLOWDUPTARGETMAP
ASSUMETARGETDEFS
DISCARDFILE /u01/app/oracle/product/goldengate/dirdat/KHEMKARAN/MIGRATE/discard_migrate, purge, MEGABYTES 100
DISCARDROLLOVER AT 20:00
REPERROR (DEFAULT, DISCARD)
GETINSERTS
GETUPDATES
GETDELETES
MAP migrate.*, TARGET migrate.*;
MAP source.*, TARGET source.*;
==================================================================================================================
Now, we will test the replication as:
On target:
GGSCI (dg01) 16> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING ELORD1 00:00:00 00:00:00
EXTRACT RUNNING EMIGRATE 00:00:00 00:00:00
EXTRACT RUNNING ETRUTH 00:00:00 00:00:09
EXTRACT RUNNING PMIGRATE 00:00:00 00:00:03
EXTRACT RUNNING PTRUTH 00:00:00 00:00:07
GGSCI (dg01) 17>
-------------------------------------------------------------------------------------------
On source:
GGSCI (restart.rac.meditate.com) 11> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING RLORD1 00:00:00 00:00:02
REPLICAT RUNNING RMIGRATE 00:00:00 00:00:07
REPLICAT RUNNING RTRUTH 00:00:00 00:00:07
GGSCI (restart.rac.meditate.com) 12>
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
On source:
SQL> desc objects
Name Null? Type
----------------------- -------- ----------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
SQL> select count(*) from objects where SECONDARY is not null;
COUNT(*)
----------
50418
-------------------------------------------------------------------------------
On target:
SQL> conn migrate/migrate
Connected.
SQL> select count(*) from objects where SECONDARY is not null;
COUNT(*)
----------
50418
SQL> desc objects
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(90)
OBJECT_NAME VARCHAR2(384)
SUBOBJECT_NAME VARCHAR2(90)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(57)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(57)
STATUS VARCHAR2(21)
TEMPORARY VARCHAR2(3)
GENERATED VARCHAR2(3)
SECONDARY VARCHAR2(3)
SQL>
--------------------------------------------------------------------------------------------
On Source:
SQL> alter table objects drop column SECONDARY;
Table altered.
SQL>
On target:
In goldengate log:
2011-11-03 10:41:38 INFO OGG-01407 Oracle GoldenGate Delivery for Oracle, rmigrate.prm: Setting current schema for DDL operation to [MIGRATE].
2011-11-03 10:42:03 INFO OGG-01408 Oracle GoldenGate Delivery for Oracle, rmigrate.prm: Restoring current schema for DDL operation to [GOLDENGATE].
In scehma on target we have:
SQL> desc objects
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(90)
OBJECT_NAME VARCHAR2(384)
SUBOBJECT_NAME VARCHAR2(90)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(57)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(57)
STATUS VARCHAR2(21)
TEMPORARY VARCHAR2(3)
GENERATED VARCHAR2(3)
SQL>
------------------------------------------------------------------------------------------------------
Also, the stats from target and sourcea are:
GGSCI (dg01) 18> STATS EXT EMIGRATE
Sending STATS request to EXTRACT EMIGRATE ...
Start of Statistics at 2011-11-02 17:52:31.
DDL replication statistics (for all trails):
*** Total statistics since extract started ***
Operations 1.00
Mapped operations 1.00
Unmapped operations 0.00
Other operations 0.00
Excluded operations 0.00
Output to /u01/app/oracle/product/goldengate/dirdat/mg:
Extracting from GOLDENGATE.GGS_MARKER to GOLDENGATE.GGS_MARKER:
*** Total statistics since 2011-11-02 17:33:49 ***
No database operations have been performed.
*** Daily statistics since 2011-11-02 17:33:49 ***
No database operations have been performed.
*** Hourly statistics since 2011-11-02 17:33:49 ***
No database operations have been performed.
*** Latest statistics since 2011-11-02 17:33:49 ***
No database operations have been performed.
End of Statistics.
GGSCI (dg01) 19>
On target:
GGSCI (restart.rac.meditate.com) 16> STATS REP RMIGRATE
Sending STATS request to REPLICAT RMIGRATE ...
No active replication maps
DDL replication statistics:
*** Total statistics since replicat started ***
Operations 1.00
Mapped operations 1.00
Unmapped operations 0.00
Other operations 0.00
Excluded operations 0.00
Errors 0.00
Retried errors 0.00
Discarded errors 0.00
Ignored errors 0.00
.
GGSCI (restart.rac.meditate.com) 17>
-------------------------------------------------------------------------------------------------------
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
So, DDL replication is working.
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Now, lets setup fall back extracts that we will start then application will be switched to target database.
So, on target do the following on ggsci command prompt:
DBLOGIN USERID goldengate, PASSWORD goldengate
ADD EXTRACT efallm,TRANLOGS,BEGIN NOW
ADD EXTTRAIL /u01/app/oracle/product/goldengate/dirdat/fm, extract efallm
ADD EXTRACT pfallm, EXTTRAILSOURCE /u01/app/oracle/product/goldengate/dirdat/fm
ADD RMTTRAIL /u01/app/oracle/product/goldengate/dirdat/fm, EXTRACT pfallm
edit params efallm
EXTRACT efallm
USERID goldengate, PASSWORD AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC, ENCRYPTKEY default
TRANLOGOPTIONS ASMUSER sys@DG01ASM, ASMPASSWORD AACAAAAAAAAAAAJASAOBIHRGNFGAYCKBHJPAGIXFGGBJUAXI, ENCRYPTKEY default
EXTTRAIL /u01/app/oracle/product/goldengate/dirdat/fm
REPORTCOUNT EVERY 5 MINUTES, RATE
DDL INCLUDE OBJNAME "migrate.*", INCLUDE OBJNAME "source.*"
TABLE migrate.*;
TABLE source.*;
On target the following:
mkdir -p /u01/app/oracle/product/goldengate/dirdat/KHEMKARAN/MIGRATE
ADD REPLICAT RFALLM, EXTTRAIL /u01/app/oracle/product/goldengate/dirdat/fm
edit params RFALLM
REPLICAT rfallm
ASSUMETARGETDEFS
USERID goldengate@khemkaran, PASSWORD AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC, ENCRYPTKEY default
ALLOWDUPTARGETMAP
ASSUMETARGETDEFS
DISCARDFILE /u01/app/oracle/product/goldengate/dirdat/KHEMKARAN/MIGRATE/discard_migrate, purge, MEGABYTES 100
DISCARDROLLOVER AT 20:00
REPERROR (DEFAULT, DISCARD)
GETINSERTS
GETUPDATES
GETDELETES
MAP migrate.*, TARGET migrate.*;
MAP source.*, TARGET source.*;
---------------------------------------------------------------------------------------------------------------
edit params pfallm
EXTRACT pfallm
RMTHOST restart, MGRPORT 7909
RMTTRAIL /u01/app/oracle/product/goldengate/dirdat/fm
PASSTHRU
TABLE migrate.*;
TABLE source.*;
---------------------------------------------------------------------------------------------------------------
On source we will allocate the fall back replicat as we did on target side.
==================================================================================================================
Now we are ready for change stop application and make sure all the changes have been applied to the target side and
stop orignal extracts and replicats and start the fallback extracts and replicats and point the application
to the target side. This gives you the option if you want to move back as well.
=====================================================================================================================
The Lord has blessed His servant with His Name. What can any poor mortal do to someone who has the Lord as his Savior and Protector? 1 Pause He Himself is the Great Being; He Himself is the Leader. He Himself accomplishes the tasks of His servant. Our Lord and Master destroys all demons; He is the Inner-knower, the Searcher of hearts. 1 He
Himself saves the honor of His servants; He Himself blesses them with stability. From the very beginning of time, and throughout the ages, He saves His servants. O Nanak, how rare is the person who knows God.
Index to all the posts of Gurpartap Singh's Blog
References:
http://www.oracle.com/technetwork/middleware/goldengate/overview/ggzerodowntimedatabaseupgrades-174928.pdf
Prerequisites:
You need to have the following prerequisites in place before you can start setting up replication for zero-downtime
migration:
1. The Oracle GoldenGate software installed on the source and target servers as described in post 16.
i.e.
http://gurpartapblog.blogspot.com/2010/11/installing-oracle-goldengate-part-16-of.html
2. The Oracle GoldenGate database User ID created on the source and target databases as in post 16.
3. The server name or IP address of the target database server
Source:
10gR1 database on Linux 32 bit running as a single instance on top of ASM.
ASM connects to the luns on SAN running openfiler.
the name of the SAN server is "filer01" and the ip is 192.168.1.116
The name of the database server is dg01 with ip 192.168.1.117
The name of the source database is "test"
Target:
11gR2 database on Red Hat Linux 64 bit running as a single instance RAC on top of Grid Infrastructure under Oracle
Restart Configuration.
ASM connects to the luns on SAN running Red Hat Linux 64 bit.
The name of the SAN server is "san2" and the ip is 192.168.1.195
The name of the database server is restart.rac.meditate.com with ip 192.168.1.196
The name of the target database is "KHEMKARAN"
You can use first 4 posts of my blog to create this machine
4. The Oracle GoldenGate Manager process up and running on the source and the target
5. TCP/IP network connectivity open from the source server to the target server's Oracle GoldenGate manager port
and visa versa
6. An understanding of the business and technical replication requirements of the planned migration
7. A few additional prerequisites are important for zero-downtime migration replication:
Backups of your Oracle GoldenGate software and working directories on the source and target servers
SQL scripts to grant any necessary object privileges required by your application during the cutover or fallback
procedure
8. Processes and scripts to move your application and users from the old to the new database and back again
Here I am creating 2 users on the source database that we will like to migrate to the new database
The name of the users are:
migrate and
source
SQL> conn / as sysdba
connected
SQL> create user migrate identified by migrate default tablespace users;
User created.
SQL> grant dba to migrate;
Grant succeeded.
SQL> conn migrate/migrate
Connected.
SQL> create table objects as select * from dba_objects;
Table created.
SQL> create table tables as select * from dba_tables;
Table created.
SQL> create table indexes as select * from dba_indexes;
Table created.
SQL>
create user source identified by source default tablespace users;
grant dba to source;
conn source/source
create table users as select * from dba_users;
create table sessions as select * from v$session;
create table sqls as select * from v$sql;
Output is as:
SQL> create user source identified by source default tablespace users;
User created.
SQL> grant dba to source;
Grant succeeded.
SQL> conn source/source
Connected.
SQL> create table users as select * from dba_users;
Table created.
SQL> create table sessions as select * from v$session;
Table created.
SQL> create table sqls as select * from v$sql;
Table created.
SQL>
-----------------------------------------------------------------------------------------
Now, I will create the same user on the target side as a part of initial load and will migrate the data on the target side.
There are different methods of doing initial load like :
You can use goldengate (Post 18),
Transfer over DB links as I will do here and you can do in in parallel if the data set is large .
Transfer with export import
Transfer with option using transportable tablespaces (Post 18)
etc...
SQL> create user migrate identified by migrate default tablespace users;
User created.
SQL> grant dba to migrate;
Grant succeeded.
SQL> conn migrate/migrate
Connected.
SQL>
SQL> create database link TEST connect to migrate identified by migrate using 'TEST';
Database link created.
SQL> select table_name from user_tables@test;
TABLE_NAME
--------------------------------------------------------------------------------
TABLES
INDEXES
OBJECTS
SQL>
SQL> create table TABLES as select * from TABLES@test;
Table created.
SQL>
SQL> create table INDEXES as select * from INDEXES@test;
Table created.
SQL> create table OBJECTS as select * from OBJECTS@test;
Table created.
SQL>
SQL> show user
USER is "MIGRATE"
SQL> select table_name from user_tables;
TABLE_NAME
------------------------------
OBJECTS
INDEXES
TABLES
SQL>
So, we have data on both on source and target sides i.e. initial load is done
create user source identified by source default tablespace users;
grant dba to source;
conn source/source
create database link SOURCE_TEST connect to source identified by source using 'TEST';
create table users as select * from users@SOURCE_TEST;
create table sessions as select * from sessions@SOURCE_TEST;
create table sqls as select * from sqls@SOURCE_TEST;
-------------------------------------------------------------------------------------------------
Output is as:
SQL> create user source identified by source default tablespace users;
User created.
SQL> grant dba to source;
Grant succeeded.
SQL> conn source/source
Connected.
SQL>
SQL> create database link SOURCE_TEST connect to source identified by source using 'TEST';
Database link created.
SQL> create table users as select * from users@SOURCE_TEST;
Table created.
SQL> create table sessions as select * from sessions@SOURCE_TEST;
Table created.
SQL> create table sqls as select * from sqls@SOURCE_TEST;
Table created.
SQL>
---------------------------------------------------------------------------------------------------------
Lets create the extract and replicats with ddl replication.
for that lets do the pre-steps for DDL replication in following post:
http://gurpartapblog.blogspot.com/2010/11/configure-ddl-synchronizationreplicatio.html
This time I hit this error so fixed it as well.
===============================================================================================
SQL> @ddl_pin GOLDENGATE
BEGIN dbms_shared_pool.keep('GOLDENGATE .DDLReplication', 'P'); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_SHARED_POOL.KEEP' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
BEGIN dbms_shared_pool.keep('GOLDENGATE .trace_put_line', 'P'); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_SHARED_POOL.KEEP' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
BEGIN dbms_shared_pool.keep('SYS. GGS_DDL_TRIGGER_BEFORE', 'R'); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_SHARED_POOL.KEEP' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
SQL> @$ORACLE_HOME/rdbms/admin/dbmspool.sql
Package created.
Grant succeeded.
View created.
Package body created.
SQL> @ddl_pin GOLDENGATE
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
SQL>
===============================================================================================
Source side:
GGSCI (dg01) 25> dblogin userid migrate, password migrate
Successfully logged into database.
GGSCI (dg01) 26> add trandata migrate.*;
ERROR: No viable tables matched specification.
GGSCI (dg01) 27>
GGSCI (dg01) 36> DBLOGIN USERID source, password source
Successfully logged into database.
GGSCI (dg01) 37> ADD TRANDATA source.*;
ERROR: No viable tables matched specification.
GGSCI (dg01) 38>
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SQL> alter system set recyclebin=OFF scope=spfile;
System altered.
SQL>
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
DBLOGIN USERID goldengate, PASSWORD goldengate
ADD EXTRACT EMIGRATE,TRANLOGS,BEGIN NOW
ADD EXTTRAIL /u01/app/oracle/product/goldengate/dirdat/mg, extract emigrate
ADD EXTRACT pmigrate, EXTTRAILSOURCE /u01/app/oracle/product/goldengate/dirdat/mg
ADD RMTTRAIL /u01/app/oracle/product/goldengate/dirdat/mg, EXTRACT PMIGRATE
edit params emigrate
EXTRACT emigrate
USERID goldengate, PASSWORD AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC, ENCRYPTKEY default
TRANLOGOPTIONS ASMUSER sys@DG01ASM, ASMPASSWORD AACAAAAAAAAAAAJASAOBIHRGNFGAYCKBHJPAGIXFGGBJUAXI, ENCRYPTKEY default
EXTTRAIL /u01/app/oracle/product/goldengate/dirdat/mg
REPORTCOUNT EVERY 5 MINUTES, RATE
DDL INCLUDE OBJNAME "migrate.*", INCLUDE OBJNAME "source.*"
TABLE migrate.*;
TABLE source.*;
edit params pmigrate
EXTRACT pmigrate
RMTHOST restart, MGRPORT 7809
RMTTRAIL /u01/app/oracle/product/goldengate/dirdat/mg
PASSTHRU
TABLE migrate.*;
TABLE source.*;
start extract EMIGRATE
start extract pmigrate
In logs we have:
2011-10-27 23:18:02 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): start extract EMIGRATE.
2011-10-27 23:18:02 INFO OGG-00963 Oracle GoldenGate Manager for Oracle, mgr.prm: Command received from GGSCI on host 192.168.1.117 (START EXTRACT EMIGRATE ).
2011-10-27 23:18:02 INFO OGG-00975 Oracle GoldenGate Manager for Oracle, mgr.prm: EXTRACT EMIGRATE starting.
2011-10-27 23:18:02 INFO OGG-00992 Oracle GoldenGate Capture for Oracle, emigrate.prm: EXTRACT EMIGRATE starting.
2011-10-27 23:18:02 INFO OGG-01635 Oracle GoldenGate Capture for Oracle, emigrate.prm: BOUNDED RECOVERY: reset to initial or altered checkpoint.
2011-10-27 23:18:03 INFO OGG-01515 Oracle GoldenGate Capture for Oracle, emigrate.prm: Positioning to begin time Oct 27, 2011 11:08:50 PM.
2011-10-27 23:18:05 INFO OGG-01516 Oracle GoldenGate Capture for Oracle, emigrate.prm: Positioned to Sequence 35, RBA 17320976, Oct 27, 2011 11:08:50 PM.
2011-10-27 23:18:05 INFO OGG-00993 Oracle GoldenGate Capture for Oracle, emigrate.prm: EXTRACT EMIGRATE started.
2011-10-27 23:18:05 INFO OGG-01052 Oracle GoldenGate Capture for Oracle, emigrate.prm: No recovery is required for target file /u01/app/oracle/product/goldengate/dirdat/mg000000, at RBA 0 (file not opened).
2011-10-27 23:18:05 INFO OGG-01478 Oracle GoldenGate Capture for Oracle, emigrate.prm: Output file /u01/app/oracle/product/goldengate/dirdat/mg is using format RELEASE 10.4/11.1.
2011-10-27 23:18:05 INFO OGG-01517 Oracle GoldenGate Capture for Oracle, emigrate.prm: Position of first record processed Sequence 35, RBA 17320976, SCN 0.1229585, Oct 27, 2011 11:08:54 PM.
2011-10-27 23:18:25 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): start extract pmigrate.
2011-10-27 23:18:25 INFO OGG-00963 Oracle GoldenGate Manager for Oracle, mgr.prm: Command received from GGSCI on host 192.168.1.117 (START EXTRACT PMIGRATE ).
2011-10-27 23:18:25 INFO OGG-00975 Oracle GoldenGate Manager for Oracle, mgr.prm: EXTRACT PMIGRATE starting.
2011-10-27 23:18:25 INFO OGG-00992 Oracle GoldenGate Capture for Oracle, pmigrate.prm: EXTRACT PMIGRATE starting.
2011-10-27 23:18:25 INFO OGG-00993 Oracle GoldenGate Capture for Oracle, pmigrate.prm: EXTRACT PMIGRATE started.
2011-10-27 23:18:30 INFO OGG-01226 Oracle GoldenGate Capture for Oracle, pmigrate.prm: Socket buffer size set to 27985 (flush size 27985).
2011-10-27 23:18:41 INFO OGG-01052 Oracle GoldenGate Capture for Oracle, pmigrate.prm: No recovery is required for target file /u01/app/oracle/product/goldengate/dirdat/mg000000, at RBA 0 (file not opened).
2011-10-27 23:18:41 INFO OGG-01478 Oracle GoldenGate Capture for Oracle, pmigrate.prm: Output file /u01/app/oracle/product/goldengate/dirdat/mg is using format RELEASE 10.4/11.1.
and
GGSCI (dg01) 63> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING ELORD1 00:00:00 00:00:04
EXTRACT RUNNING EMIGRATE 00:00:00 00:00:06
EXTRACT RUNNING ETRUTH 00:00:00 00:00:02
EXTRACT RUNNING PMIGRATE 00:00:00 00:00:04
EXTRACT RUNNING PTRUTH 00:00:00 00:00:09
GGSCI (dg01) 64>
On taget side we do:
ADD REPLICAT RMIGRATE, EXTTRAIL /u01/app/oracle/product/goldengate/dirdat/mg
REPLICAT rmigrate
ASSUMETARGETDEFS
USERID goldengate@khemkaran, PASSWORD AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC, ENCRYPTKEY default
ALLOWDUPTARGETMAP
ASSUMETARGETDEFS
DISCARDFILE /u01/app/oracle/product/goldengate/dirdat/KHEMKARAN/MIGRATE/discard_migrate, purge, MEGABYTES 100
DISCARDROLLOVER AT 20:00
REPERROR (DEFAULT, DISCARD)
GETINSERTS
GETUPDATES
GETDELETES
MAP migrate.*, TARGET migrate.*;
MAP source.*, TARGET source.*;
==================================================================================================================
Now, we will test the replication as:
On target:
GGSCI (dg01) 16> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING ELORD1 00:00:00 00:00:00
EXTRACT RUNNING EMIGRATE 00:00:00 00:00:00
EXTRACT RUNNING ETRUTH 00:00:00 00:00:09
EXTRACT RUNNING PMIGRATE 00:00:00 00:00:03
EXTRACT RUNNING PTRUTH 00:00:00 00:00:07
GGSCI (dg01) 17>
-------------------------------------------------------------------------------------------
On source:
GGSCI (restart.rac.meditate.com) 11> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING RLORD1 00:00:00 00:00:02
REPLICAT RUNNING RMIGRATE 00:00:00 00:00:07
REPLICAT RUNNING RTRUTH 00:00:00 00:00:07
GGSCI (restart.rac.meditate.com) 12>
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
On source:
SQL> desc objects
Name Null? Type
----------------------- -------- ----------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
SQL> select count(*) from objects where SECONDARY is not null;
COUNT(*)
----------
50418
-------------------------------------------------------------------------------
On target:
SQL> conn migrate/migrate
Connected.
SQL> select count(*) from objects where SECONDARY is not null;
COUNT(*)
----------
50418
SQL> desc objects
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(90)
OBJECT_NAME VARCHAR2(384)
SUBOBJECT_NAME VARCHAR2(90)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(57)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(57)
STATUS VARCHAR2(21)
TEMPORARY VARCHAR2(3)
GENERATED VARCHAR2(3)
SECONDARY VARCHAR2(3)
SQL>
--------------------------------------------------------------------------------------------
On Source:
SQL> alter table objects drop column SECONDARY;
Table altered.
SQL>
On target:
In goldengate log:
2011-11-03 10:41:38 INFO OGG-01407 Oracle GoldenGate Delivery for Oracle, rmigrate.prm: Setting current schema for DDL operation to [MIGRATE].
2011-11-03 10:42:03 INFO OGG-01408 Oracle GoldenGate Delivery for Oracle, rmigrate.prm: Restoring current schema for DDL operation to [GOLDENGATE].
In scehma on target we have:
SQL> desc objects
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(90)
OBJECT_NAME VARCHAR2(384)
SUBOBJECT_NAME VARCHAR2(90)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(57)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(57)
STATUS VARCHAR2(21)
TEMPORARY VARCHAR2(3)
GENERATED VARCHAR2(3)
SQL>
------------------------------------------------------------------------------------------------------
Also, the stats from target and sourcea are:
GGSCI (dg01) 18> STATS EXT EMIGRATE
Sending STATS request to EXTRACT EMIGRATE ...
Start of Statistics at 2011-11-02 17:52:31.
DDL replication statistics (for all trails):
*** Total statistics since extract started ***
Operations 1.00
Mapped operations 1.00
Unmapped operations 0.00
Other operations 0.00
Excluded operations 0.00
Output to /u01/app/oracle/product/goldengate/dirdat/mg:
Extracting from GOLDENGATE.GGS_MARKER to GOLDENGATE.GGS_MARKER:
*** Total statistics since 2011-11-02 17:33:49 ***
No database operations have been performed.
*** Daily statistics since 2011-11-02 17:33:49 ***
No database operations have been performed.
*** Hourly statistics since 2011-11-02 17:33:49 ***
No database operations have been performed.
*** Latest statistics since 2011-11-02 17:33:49 ***
No database operations have been performed.
End of Statistics.
GGSCI (dg01) 19>
On target:
GGSCI (restart.rac.meditate.com) 16> STATS REP RMIGRATE
Sending STATS request to REPLICAT RMIGRATE ...
No active replication maps
DDL replication statistics:
*** Total statistics since replicat started ***
Operations 1.00
Mapped operations 1.00
Unmapped operations 0.00
Other operations 0.00
Excluded operations 0.00
Errors 0.00
Retried errors 0.00
Discarded errors 0.00
Ignored errors 0.00
.
GGSCI (restart.rac.meditate.com) 17>
-------------------------------------------------------------------------------------------------------
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
So, DDL replication is working.
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Now, lets setup fall back extracts that we will start then application will be switched to target database.
So, on target do the following on ggsci command prompt:
DBLOGIN USERID goldengate, PASSWORD goldengate
ADD EXTRACT efallm,TRANLOGS,BEGIN NOW
ADD EXTTRAIL /u01/app/oracle/product/goldengate/dirdat/fm, extract efallm
ADD EXTRACT pfallm, EXTTRAILSOURCE /u01/app/oracle/product/goldengate/dirdat/fm
ADD RMTTRAIL /u01/app/oracle/product/goldengate/dirdat/fm, EXTRACT pfallm
edit params efallm
EXTRACT efallm
USERID goldengate, PASSWORD AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC, ENCRYPTKEY default
TRANLOGOPTIONS ASMUSER sys@DG01ASM, ASMPASSWORD AACAAAAAAAAAAAJASAOBIHRGNFGAYCKBHJPAGIXFGGBJUAXI, ENCRYPTKEY default
EXTTRAIL /u01/app/oracle/product/goldengate/dirdat/fm
REPORTCOUNT EVERY 5 MINUTES, RATE
DDL INCLUDE OBJNAME "migrate.*", INCLUDE OBJNAME "source.*"
TABLE migrate.*;
TABLE source.*;
On target the following:
mkdir -p /u01/app/oracle/product/goldengate/dirdat/KHEMKARAN/MIGRATE
ADD REPLICAT RFALLM, EXTTRAIL /u01/app/oracle/product/goldengate/dirdat/fm
edit params RFALLM
REPLICAT rfallm
ASSUMETARGETDEFS
USERID goldengate@khemkaran, PASSWORD AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC, ENCRYPTKEY default
ALLOWDUPTARGETMAP
ASSUMETARGETDEFS
DISCARDFILE /u01/app/oracle/product/goldengate/dirdat/KHEMKARAN/MIGRATE/discard_migrate, purge, MEGABYTES 100
DISCARDROLLOVER AT 20:00
REPERROR (DEFAULT, DISCARD)
GETINSERTS
GETUPDATES
GETDELETES
MAP migrate.*, TARGET migrate.*;
MAP source.*, TARGET source.*;
---------------------------------------------------------------------------------------------------------------
edit params pfallm
EXTRACT pfallm
RMTHOST restart, MGRPORT 7909
RMTTRAIL /u01/app/oracle/product/goldengate/dirdat/fm
PASSTHRU
TABLE migrate.*;
TABLE source.*;
---------------------------------------------------------------------------------------------------------------
On source we will allocate the fall back replicat as we did on target side.
==================================================================================================================
Now we are ready for change stop application and make sure all the changes have been applied to the target side and
stop orignal extracts and replicats and start the fallback extracts and replicats and point the application
to the target side. This gives you the option if you want to move back as well.
=====================================================================================================================
The Lord has blessed His servant with His Name. What can any poor mortal do to someone who has the Lord as his Savior and Protector? 1 Pause He Himself is the Great Being; He Himself is the Leader. He Himself accomplishes the tasks of His servant. Our Lord and Master destroys all demons; He is the Inner-knower, the Searcher of hearts. 1 He
Himself saves the honor of His servants; He Himself blesses them with stability. From the very beginning of time, and throughout the ages, He saves His servants. O Nanak, how rare is the person who knows God.
Part 36 of series - Presentation on Oracle Enterprise Manager Cloud Control 12c at Vancouver Oracle Users Group
The fool does not understand his own self; he annoys others with his speech. His underlying nature does not leave him; separated from the Lord, he is beaten. He has not changed and reformed himself in Fear of the True Guru, so that he might merge in the lap of God. Night and day, his doubts never stop; without the Word of the Shabad, he suffers in pain. Sexual desire, anger and greed are very powerful within; he passes his life constantly entangled in worldly affairs. His feet, hands, eyes and ears are exhausted; his days are numbered, and death is approaching. The True Name does not seem sweet to him . the Name by which the nine treasures are obtained. But if he remains dead while yet alive, then by so dying, he truly lives; thus, he attains liberation. But if he is not blessed with such pre-destined karma, then without this karma, what can he obtain? Meditate on the Word of the Guru.s Shabad, you fool; through the Shabad, you shall obtain salvation and wisdom. O Nanak, he alone finds the True Guru, who eradicates self-conceit from within.
Index to all the posts of Gurpartap Singh's Blog
Presentation on Oracle Enterprise Manager Cloud Control 12c
I had the opportunity to do the presentation on Cloud Control 12c on 26th Oct. 2011 at Vancouver Oracle Users Group.
The topic was:
- Enterprise Manager 12.1 Binaries Structure.
- New install of Enterprise Manager 12.1
- Types of installation method.
- Pre-requisites of Enterprise Manager 12.1 Install
- Step by Step process of Install on Linux x86_64
- Post install verification
- Log Files
- New Features
- Upgrade Paths
- Documentation
Presentation is now available on the website of Vancouver Oracle Users Group (http://www.vanoug.org/) under presentations tab.
The minds of the Lord?s devotee are filled with bliss.They become stable and permanent, and all their anxiety is gone. Their fears and doubts are dispelled in an instant. The Supreme Lord God comes to dwell in their minds. 1 The Lord is forever the Help and Support of the Saints. Inside the home of the heart, and outside as well, the ranscendent Lord is always with us, permeating and pervading all places. 1 Pause The Lord of the World is my wealth, property, youth and ways and means. He continually cherishes and brings peace to my soul and breath of life. He reaches out with His Hand and saves His slave. He does not abandon us, even for an instant; He is always with us. 2 There is no other Beloved like the Lord. The True Lord takes care of all. The Lord is our
Mother, Father, Son and Relation. Since the beginning of time, and throughout the ages, His devotees sing His Glorious Praises. 3 My mind is filled with the Support and the Power of the Lord. Without the Lord, there is no other at all. Nanak.s mind is encouraged by this hope, that God will accomplish my objectives in life.
Index to all the posts of Gurpartap Singh's Blog
Presentation on Oracle Enterprise Manager Cloud Control 12c
I had the opportunity to do the presentation on Cloud Control 12c on 26th Oct. 2011 at Vancouver Oracle Users Group.
The topic was:
- Enterprise Manager 12.1 Binaries Structure.
- New install of Enterprise Manager 12.1
- Types of installation method.
- Pre-requisites of Enterprise Manager 12.1 Install
- Step by Step process of Install on Linux x86_64
- Post install verification
- Log Files
- New Features
- Upgrade Paths
- Documentation
Presentation is now available on the website of Vancouver Oracle Users Group (http://www.vanoug.org/) under presentations tab.
The minds of the Lord?s devotee are filled with bliss.They become stable and permanent, and all their anxiety is gone. Their fears and doubts are dispelled in an instant. The Supreme Lord God comes to dwell in their minds. 1 The Lord is forever the Help and Support of the Saints. Inside the home of the heart, and outside as well, the ranscendent Lord is always with us, permeating and pervading all places. 1 Pause The Lord of the World is my wealth, property, youth and ways and means. He continually cherishes and brings peace to my soul and breath of life. He reaches out with His Hand and saves His slave. He does not abandon us, even for an instant; He is always with us. 2 There is no other Beloved like the Lord. The True Lord takes care of all. The Lord is our
Mother, Father, Son and Relation. Since the beginning of time, and throughout the ages, His devotees sing His Glorious Praises. 3 My mind is filled with the Support and the Power of the Lord. Without the Lord, there is no other at all. Nanak.s mind is encouraged by this hope, that God will accomplish my objectives in life.
Subscribe to:
Posts (Atom)