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.
Wednesday, October 5, 2011
Part 35 of series - Install Oracle Enterprise Manager Cloud control 12c on Linux 64 bit
Even with great cleverness, the fear of death clings to you.You try all sorts of things, but your thirst is still not satisfied. Wearing various religious robes, the fire is not extinguished. Even making millions of efforts, you shall not be accepted in the Court of the Lord. You cannot escape to the heavens, or to the nether regions, if you are entangled in emotional attachment and the net of Maya. All other efforts are punished by the Messenger of Death, which accepts nothing at all, except meditation on the Lord of the Universe. Chanting the Name of the Lord, sorrow is dispelled.
Index to all the posts of Gurpartap's Blog
We will do a new install of Oracle Enterprise Manager Cloud Control 12c on Linux on Vmware machine on Linux 5.4 on x86_64 (64 bit).
I have created a new database for repository and named it "Khemkaran". This database is a single node 11gr2 database (11.2.0.1) running on ASM on grid infrastructure on single node(Oracle restart). The minimun certified database versions for Oracle Enterprise Manager Cloud Control 12c for hosting repository database is 10.2.0.5. I create a new san and named it san2 and just one machine to connect to that san and named it "restart.rac.meditate.com". To configure this look at post 1,2,3and 4. Then I installed grid infrastructure on it and created the ASM instance. Then I installed Oracle database binaries and created database KHEMKARAN on it. This what we did in post from 1 to 9 but in this case I just used one machine instead of 3 earlier. Since its already documented and can be easily done by looking at old posts, I am not re-documenting it.
Here is now our repository database "KHEMKARAN" and Grid Infrastructure looks like:
oracle : restart.rac.meditate.com : @KHEMKARAN : /home/oracle
$ . oraenv
ORACLE_SID = [KHEMKARAN] ? crs
The Oracle base for ORACLE_HOME=/u01/app/oracle/grid is /u01/app/oracle
oracle : restart.rac.meditate.com : @crs : /home/oracle
$ uname -a
Linux restart.rac.meditate.com 2.6.18-164.el5 #1 SMP Thu Sep 3 03:28:30 EDT 2009 x86_64 x86_64 x86_64 GNU/Linux
oracle : restart.rac.meditate.com : @crs : /home/oracle
$ crsctl status resource -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER.lsnr
ONLINE ONLINE restart
ora.RESTART_DATA_1.dg
ONLINE ONLINE restart
ora.RESTART_GRID_1.dg
OFFLINE OFFLINE restart
ora.RESTART_RECV_1.dg
ONLINE ONLINE restart
ora.asm
ONLINE ONLINE restart Started
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
1 ONLINE ONLINE restart
ora.diskmon
1 ONLINE ONLINE restart
ora.khemkaran.db
1 ONLINE ONLINE restart Open
oracle : restart.rac.meditate.com : @crs : /home/oracle
$ ps -ef grep pmon
oracle 5898 1 0 22:11 ? 00:00:00 asm_pmon_+ASM
oracle 6138 1 0 22:13 ? 00:00:00 ora_pmon_KHEMKARAN
oracle 6319 5966 0 22:15 pts/0 00:00:00 grep pmon
oracle : restart.rac.meditate.com : @crs : /home/oracle
$
==================================
oracle : restart.rac.meditate.com : @crs : /home/oracle
$ . oraenv
ORACLE_SID = [crs] ? KHEMKARAN
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0.1 is /u01/app/oracle
oracle : restart.rac.meditate.com : @KHEMKARAN : /home/oracle
$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue Oct 4 22:17:18 2011
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> set line 300
SQL> select * from gv$instance;
INST_ID INSTANCE_NUMBER INSTANCE_NAME HOST_NAME VERSION STARTUP_TIME STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
---------- --------------- ---------------- ---------------------------------------------------------------- ----------------- ------------------ ------------ --- ---------- ------- --------------------------------------------- ---------- --- ----------------- ------------------ --------- ---
1 1 KHEMKARAN restart.rac.meditate.com 11.2.0.1.0 04-OCT-11 OPEN NO 1 STARTED ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO
SQL> select name from v$datafile;
NAME
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+RESTART_DATA_1/khemkaran/datafile/system
+RESTART_DATA_1/khemkaran/datafile/sysaux
+RESTART_DATA_1/khemkaran/datafile/undotbs1
+RESTART_DATA_1/khemkaran/datafile/users
+RESTART_DATA_1/khemkaran/datafile/example
+RESTART_DATA_1/khemkaran/datafile/goldengate.261.763143597
6 rows selected.
SQL>
==============================
oracle : restart.rac.meditate.com : @KHEMKARAN : /home/oracle
$ . oraenv
ORACLE_SID = [KHEMKARAN] ? +ASM
The Oracle base for ORACLE_HOME=/u01/app/oracle/grid is /u01/app/oracle
oracle : restart.rac.meditate.com : @+ASM : /home/oracle
$ asmcmd -p
ASMCMD [+] > lsdg
State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED EXTERN N 512 4096 1048576 8192 6381 0 6381 0 N RESTART_DATA_1/
MOUNTED EXTERN N 512 4096 1048576 2048 1989 0 1989 0 N RESTART_GRID_1/
MOUNTED EXTERN N 512 4096 1048576 4096 3910 0 3910 0 N RESTART_RECV_1/
ASMCMD [+] >
Now we will download the binaries and they can be downloaded from OTN for following link:
http://www.oracle.com/technetwork/oem/grid-control/downloads/linuxx8664soft-085949.html
and they are here:
Enterprise Manager Cloud Control 12c Release 1 (12.1.0.1) for Linux x86-64 New!
Download the complete files
em12_linux64_disk1of2.zip (2,561,176,217 bytes) (cksum - 3191988111)
em12_linux64_disk2of2.zip (3,057,842,914 bytes) (cksum - 4262173197)
Please note that the size of the files is bigger than the size for Oracle Enterprise Manager Grid Control 11g.
After download move the files with winscp to the srever named "restart" where we already have single node grid infrastructure and database KHEMKARAN running.
Now run cksum on these zipped files to make sure everything is ok as:
cksum em12_linux64_disk1of2.zip
cksum em12_linux64_disk2of2.zip
The no.s returned must match the no.s for cksum given on OTN site.
You will need to unzip these files and sfter unzip you will have the following:
runInstaller
WT.zip
oms
jdk
response
wls
install
libskgxn
stage
plugins
Preupgrade_Console_Patch Release_Notes.pdf
em12_linux64_disk2of2.zip
em12_linux64_disk1of2.zip
Please note, now weblogic and jdk comes with the binaries of Enterprise Manager 12c.
You don't need to download them seperately. Also they have made the instasll simple as installer installs jdk,weblogic as well as Enterprise Manager 12c all in one shot.
Now Installer has capability to not only find the errors but also has teh capability to fix many of them.
Now that we are done with unzip part lets start the installer and before that we have to unset all the Oralce Environment Varibles and set your display and start your x-server client or Xming and run the installer as:
oracle : restart.rac.meditate.com : @+ASM : /home/oracle/software/Grid_control_12c_linux_x86_64
$ env grep ORA
ORA_CRS_HOME=/u01/app/oracle/grid
ORACLE_SID=+ASM
ORACLE_BASE=/u01/app/oracle
ORAENV_ASK=YES
ORACLE_HOME=/u01/app/oracle/grid
oracle : restart.rac.meditate.com : @+ASM : /home/oracle/software/Grid_control_12c_linux_x86_64
$ unset ORA_CRS_HOME ORACLE_SID ORACLE_BASE ORAENV_ASK ORACLE_HOME CRS_HOME
oracle : restart.rac.meditate.com : @ : /home/oracle/software/Grid_control_12c_linux_x86_64
$ env grep ORA
oracle : restart.rac.meditate.com : @ : /home/oracle/software/Grid_control_12c_linux_x86_64
$
oracle : restart.rac.meditate.com : @ : /home/oracle/software/Grid_control_12c_linux_x86_64
$ lst
total 7274304
-rwxr-xr-x 1 oracle oinstall 5136 Feb 10 2010 runInstaller
-rwxr-xr-x 1 oracle oinstall 1822416217 Sep 23 01:12 WT.zip
drwxr-xr-x 4 oracle oinstall 4096 Sep 23 01:12 oms
drwxr-xr-x 4 oracle oinstall 4096 Sep 23 01:22 jdk
drwxrwxr-x 2 oracle oinstall 4096 Sep 23 01:24 response
drwxrwxr-x 2 oracle oinstall 4096 Sep 23 01:24 wls
drwxr-xr-x 7 oracle oinstall 4096 Sep 23 01:24 install
drwxrwxr-x 4 oracle oinstall 4096 Sep 23 01:24 libskgxn
drwxr-xr-x 9 oracle oinstall 4096 Sep 23 01:25 stage
drwxr-xr-x 2 oracle oinstall 4096 Sep 23 01:26 plugins
drwxrwxr-x 2 oracle oinstall 4096 Sep 23 09:52 Preupgrade_Console_Patch
-rwxrwxr-x 1 oracle oinstall 100989 Sep 23 09:59 Release_Notes.pdf
-rw-r--r-- 1 oracle oinstall 3057842914 Oct 4 11:44 em12_linux64_disk2of2.zip
-rw-r--r-- 1 oracle oinstall 2561176217 Oct 4 11:46 em12_linux64_disk1of2.zip
drwxr-xr-x 6 oracle oinstall 4096 Oct 4 20:33 ..
drwxr-xr-x 11 oracle oinstall 4096 Oct 4 20:50 .
oracle : restart.rac.meditate.com : @ : /home/oracle/software/Grid_control_12c_linux_x86_64
$ runInstaller &
On the following screen uncheck "I wish to receive security updates via My Oracle Support"
On the following screen click "Yes".
On the following screen click skip and then next.
At the following screen Installer will do prerequisite checks to verify that your environment meets
all minimum requirements for installing and configuring Oracle Enterprise Manager Cloud Control 12c. All tests passed, I am copy pasting the details from the window on teh tests and the results from Installer. If something fails fix it and rerun it. After everything passes click next.
Checking if Oracle software certified on the current O/S...
Description:
This is a prerequisite condition to test whether the Oracle software is certified on the current O/S or not.
Expected result: One of enterprise-5.6,enterprise-6.0,redhat-6.0,redhat-5.6,enterprise-5.5,enterprise-5.4,enterprise-5.3,enterprise-5.2,enterprise-5.1,enterprise-5,asianux-3,redhat-5.5,redhat-5.4,redhat-5.3,redhat-5.3,redhat-5.2,redhat-5.1,redhat-5,SuSE-10,SuSE-11
Actual Result: redhat-5.6
Check complete. The overall result of this check is: Passed
Checking for required packages installed on the system ....
Description:
This is a prerequisite condition to test whether the minimum required packages are available on the system.
Checking for make-3.81; found make-1:3.81-3.el5-x86_64. Passed
Checking for binutils-2.17.50.0.6; found binutils-2.17.50.0.6-12.el5-x86_64. Passed
Checking for gcc-4.1.1; found gcc-4.1.2-51.el5-x86_64. Passed
Checking for libaio-0.3.106; found libaio-0.3.106-5-i386. Passed
Checking for glibc-common-2.3.4; found glibc-common-2.5-65-x86_64. Passed
Checking for libstdc++-4.1.1; found libstdc++-4.1.2-51.el5-x86_64. Passed
Checking for setarch-1.6; found setarch-2.0-1.1-x86_64. Passed
Checking for sysstat-5.0.5; found sysstat-7.0.2-11.el5-x86_64. Passed
Checking for rng-utils-2.0; found rng-utils-1:2.0-1.14.1.fc6-x86_64. Passed
Checking for glibc-devel-2.5-49-i386; found glibc-devel-2.5-65-i386. Passed
Checking for glibc-devel-2.5-49-x86_64; found glibc-devel-2.5-65-x86_64. Passed
Check complete. The overall result of this check is: Passed
Checking whether required GLIBC installed on the system ....
Description:
This is a prerequisite condition to test whether the minimum required glibc is available on the system.
Expected result: ATLEAST=2.5-12
Actual Result: 2.5-65
Check complete. The overall result of this check is: Passed
Checking for sufficient diskspace in TEMP location...
Description:
This check ensures that sufficient diskspace is available in system TEMP location.
Expected result: 400.0MB
Actual Result: 43028MB
Check complete. The overall result of this check is: Passed
Checking for sufficient disk space in Inventory location...
Description:
Checks whether the Inventory Location has at least 100 MB of free disk space.
Expected result: 100.0MB
Actual Result: 43028MB
Check complete. The overall result of this check is: Passed
Checking whether the software is compatible for current O/S...
Description:
This is a prerequisite condition to test whether the software is compatible for this o/s
Check complete. The overall result of this check is: Passed
Checking TimeZone settings...
Description:
Checking whether the timezone installed in the machine is supported for the agent startup
Check complete. The overall result of this check is: Passed
Checking for sufficient physical memory...
Description:
This is a prerequisite condition to test whether the system has sufficient physical memory.
Expected result: 3072MB
Actual Result: 7980MB
Check complete. The overall result of this check is: Passed
Checking for sufficient swap space...
Description:
This is a prerequisite condition to test whether the sufficient swapspace is available to perform the install.
Expected result: 250MB
Actual Result: 5023MB
Check complete. The overall result of this check is: Passed
Checking for required ulimit value...
Description:
This is a prerequisite condition to test whether the minimum required ulimit value is available on the system.
Expected result: 4096
Actual Result: 131072
Check complete. The overall result of this check is: Passed
Checking for the Hostname...
Description:
This is a prerequisite condition to test whether the host name where the installation will be done, is correct or not.
Expected result: Should be a Valid Host Name.
Actual Result: restart.rac.meditate.com
Check complete. The overall result of this check is: Passed
Checking for LD_ASSUME_KERNEL environment variable...
Description:
Check for LD_ASSUME_KERNEL
Expected result: LD_ASSUME_KERNEL environment variable is not set to 2.4.19
Actual Result: Variable Not set
Check complete. The overall result of this check is: Passed
On the follwoing screen, click "Create a new Enterprise Manager System" and click advanced and add the middleware home location as "/u01/app/oracle/middleware" and click next.
On the next screen choose the plugins you want to install and I picked all of them and click next.
On the next screen enter the weblogic domain name i.e. GCDomain's username and password,
as:
Username : weblogic
Password: gurpartap1 (I used this)
Confirm the password
also Node Manager password
I used "gurpartap1" same as given above and then
and the base location of OMS. as:
/u01/app/oracle/middleware/gc_inst
and click next.
On the following screen supply:
Databasse hostname as: restart.rac.meditate.com
Port : 1521
Service/SID=KHEMKARAN (you can use any service name here as well)
SYS Password : xxxx
and click next.
I got the following error. Looks like while install of this database I had configured local EM. I used the same command given below to de-configure it as follows and clicked ok.
oracle : restart.rac.meditate.com : @KHEMKARAN : /u01/app/oracle/product/11.2.0.1
$ /u01/app/oracle/product/11.2.0.1/bin/emca -deconfig dbcontrol db -repos drop -SYS_PWD gurpartap -SYSMAN_PWD gurpartap
STARTED EMCA at Oct 4, 2011 10:35:53 PM
EM Configuration Assistant, Version 11.2.0.0.2 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Enter the following information:
Database SID: KHEMKARAN
Listener port number: 1521
Do you wish to continue? [yes(Y)/no(N)]: y
Oct 4, 2011 10:36:07 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/app/oracle/cfgtoollogs/emca/KHEMKARAN/emca_2011_10_04_22_35_52.log.
Oct 4, 2011 10:36:08 PM oracle.sysman.emcp.EMDBPreConfig performDeconfiguration
WARNING: EM is not configured for this database. No EM-specific actions can be performed.
Oct 4, 2011 10:36:08 PM oracle.sysman.emcp.ParamsManager checkListenerStatusForDBControl
WARNING: Error initializing SQL connection. SQL operations cannot be performed
Oct 4, 2011 10:36:08 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Dropping the EM repository (this may take a while) ...
Oct 4, 2011 10:41:11 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully dropped
Enterprise Manager configuration completed successfully
FINISHED EMCA at Oct 4, 2011 10:41:11 PM
oracle : restart.rac.meditate.com : @KHEMKARAN : /u01/app/oracle/product/11.2.0.1
$
Click ok and click next on the screen above again.
You may get the following screen depending on the settings of the database.
Installer will disable the stats gathering feature here.
Click yes to let installer fix it for you.
and not you can get the follwoing screen depend on the settings on the repository database.
I copied the errors and they are here, though the installer says its just a warning for preformance issues later, I will fix them now.
ERRORS are:
=====================
Ensure that the EM tablespace has at least one datafile set to autoextensible Add at least one datafile with the autoextend attribute to the EM tablespace
=====================
Ensure that the EM tablespace has at least one datafile set to autoextensible Add at least one datafile with the autoextend attribute to the EM tablespace
Check the processes instance parameter value The processes instance parameter needs to be set to at least 300.
Check the session_cached_cursors instance parameter value. The session_cached_cursors instance parameter should be set to 300. Any value between 200 and 500, inclusive, will be accepted.
Check the job_queue_processes instance parameter value. The job_queue_processes instance parameter should be set to 20.
Check the shared_pool_size instance parameter value. The shared_pool_size instance parameter should be set to at least 600 megabytes or roughly one third of the sga_target size.
Check the redo log size. The size of the EM database instance should be 300 megabytes or greater. Generally, there should be 3 or more redo logs available of this size.
Fixed as:
Ensure that the EM tablespace has at least one datafile set to autoextensible Add at least one datafile with the autoextend attribute to the EM tablespace
FIXED:
SQL> alter tablespace SYSAUX add datafile '+RESTART_DATA_1' size 200m autoextend on;
Tablespace altered.
SQL> alter tablespace users add datafile '+RESTART_DATA_1' size 200m autoextend on;
Tablespace altered.
SQL>
============
Check the processes instance parameter value The processes instance parameter needs to be set to at least 300.
FIXED :
SQL> alter system set processes=300 scope=spfile;
System altered.
SQL>
=============
Check the session_cached_cursors instance parameter value. The session_cached_cursors instance parameter should be set to 300. Any value between 200 and 500, inclusive, will be accepted.
FIXED as:
SQL> alter system set session_cached_cursors = 300 scope=spfile;
System altered.
SQL>
===============
Check the job_queue_processes instance parameter value. The job_queue_processes instance parameter should be set to 20.
FIXED:
SQL> show parameter job_queue_processes
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 1000
SQL> alter system set job_queue_processes=20;
System altered.
SQL>
=======
Check the shared_pool_size instance parameter value. The shared_pool_size instance parameter should be set to at least 600 megabytes or roughly one third of the sga_target size.
FIXED:
SQL> show parameter shared
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0
max_shared_servers integer
shared_memory_address integer 0
shared_pool_reserved_size big integer 8M
shared_pool_size big integer 0
shared_server_sessions integer
shared_servers integer 1
SQL> alter system set shared_pool_size=600m;
System altered.
SQL>
=============
Check the redo log size. The size of the EM database instance should be 300 megabytes or greater. Generally, there should be 3 or more redo logs available of this size.
FIXED:
SQL> set line 300
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------ ------------ ------------------
4 1 84 5242880 512 1 YES INACTIVE 1198243 04-OCT-11 1203937 04-OCT-11
5 1 85 5242880 512 1 YES ACTIVE 1203937 04-OCT-11 1205854 04-OCT-11
6 1 86 5242880 512 1 NO CURRENT 1205854 04-OCT-11 2.8147E+14
SQL>alter database add logfile group 1 '+RESTART_DATA_1' size 400M;
alter database add logfile group 2 '+RESTART_DATA_1' size 400M;
alter database add logfile group 3 '+RESTART_DATA_1' size 400M;
Database altered.
SQL>
Database altered.
SQL>
Database altered.
SQL> SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------ ------------ ------------------
1 1 0 419430400 512 1 YES UNUSED 0 0
2 1 0 419430400 512 1 YES UNUSED 0 0
3 1 0 419430400 512 1 YES UNUSED 0 0
4 1 84 5242880 512 1 YES INACTIVE 1198243 04-OCT-11 1203937 04-OCT-11
5 1 85 5242880 512 1 YES INACTIVE 1203937 04-OCT-11 1205854 04-OCT-11
6 1 86 5242880 512 1 NO CURRENT 1205854 04-OCT-11 2.8147E+14
6 rows selected.
SQL> alter database drop logfile group 4;
Database altered.
SQL> alter database drop logfile group 5;
Database altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------ ------------ ------------------
1 1 0 419430400 512 1 YES UNUSED 0 0
2 1 0 419430400 512 1 YES UNUSED 0 0
3 1 0 419430400 512 1 YES UNUSED 0 0
6 1 86 5242880 512 1 NO CURRENT 1205854 04-OCT-11 2.8147E+14
SQL>
SQL> alter system archive log current;
alter system archive log current;
alter system archive log current;
System altered.
System altered.
System altered.
SQL> alter system checkpoint;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------ ------------ ------------------
1 1 87 419430400 512 1 YES INACTIVE 1206193 04-OCT-11 1206200 04-OCT-11
2 1 88 419430400 512 1 YES INACTIVE 1206200 04-OCT-11 1206209 04-OCT-11
3 1 89 419430400 512 1 NO CURRENT 1206209 04-OCT-11 2.8147E+14
6 1 86 5242880 512 1 YES INACTIVE 1205854 04-OCT-11 1206193 04-OCT-11
SQL>
SQL> alter database drop logfile group 6;
Database altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------ ------------ ------------------
1 1 87 419430400 512 1 YES INACTIVE 1206193 04-OCT-11 1206200 04-OCT-11
2 1 88 419430400 512 1 YES INACTIVE 1206200 04-OCT-11 1206209 04-OCT-11
3 1 89 419430400 512 1 NO CURRENT 1206209 04-OCT-11 2.8147E+14
SQL>
=======================
Ensure that the EM tablespace has at least one datafile set to autoextensible Add at least one datafile with the autoextend attribute to the EM tablespace
FIXED:
SQL> alter tablespace SYSAUX add datafile '+RESTART_DATA_1' size 200m autoextend on;
Tablespace altered.
SQL> alter tablespace users add datafile '+RESTART_DATA_1' size 200m autoextend on;
Tablespace altered.
SQL>
============
Check the processes instance parameter value The processes instance parameter needs to be set to at least 300.
FIXED :
SQL> alter system set processes=300 scope=spfile;
System altered.
SQL>
=============
Check the session_cached_cursors instance parameter value. The session_cached_cursors instance parameter should be set to 300. Any value between 200 and 500, inclusive, will be accepted.
FIXED as:
SQL> alter system set session_cached_cursors = 300 scope=spfile;
System altered.
SQL>
===============
Check the job_queue_processes instance parameter value. The job_queue_processes instance parameter should be set to 20.
FIXED:
SQL> show parameter job_queue_processes
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 1000
SQL> alter system set job_queue_processes=20;
System altered.
SQL>
=======
Check the shared_pool_size instance parameter value. The shared_pool_size instance parameter should be set to at least 600 megabytes or roughly one third of the sga_target size.
FIXED:
SQL> show parameter shared
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0
max_shared_servers integer
shared_memory_address integer 0
shared_pool_reserved_size big integer 8M
shared_pool_size big integer 0
shared_server_sessions integer
shared_servers integer 1
SQL> alter system set shared_pool_size=600m;
System altered.
SQL>
=============
Check the redo log size. The size of the EM database instance should be 300 megabytes or greater. Generally, there should be 3 or more redo logs available of this size.
FIXED:
SQL> set line 300
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------ ------------ ------------------
4 1 84 5242880 512 1 YES INACTIVE 1198243 04-OCT-11 1203937 04-OCT-11
5 1 85 5242880 512 1 YES ACTIVE 1203937 04-OCT-11 1205854 04-OCT-11
6 1 86 5242880 512 1 NO CURRENT 1205854 04-OCT-11 2.8147E+14
SQL>alter database add logfile group 1 '+RESTART_DATA_1' size 400M;
alter database add logfile group 2 '+RESTART_DATA_1' size 400M;
alter database add logfile group 3 '+RESTART_DATA_1' size 400M;
Database altered.
SQL>
Database altered.
SQL>
Database altered.
SQL> SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------ ------------ ------------------
1 1 0 419430400 512 1 YES UNUSED 0 0
2 1 0 419430400 512 1 YES UNUSED 0 0
3 1 0 419430400 512 1 YES UNUSED 0 0
4 1 84 5242880 512 1 YES INACTIVE 1198243 04-OCT-11 1203937 04-OCT-11
5 1 85 5242880 512 1 YES INACTIVE 1203937 04-OCT-11 1205854 04-OCT-11
6 1 86 5242880 512 1 NO CURRENT 1205854 04-OCT-11 2.8147E+14
6 rows selected.
SQL> alter database drop logfile group 4;
Database altered.
SQL> alter database drop logfile group 5;
Database altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------ ------------ ------------------
1 1 0 419430400 512 1 YES UNUSED 0 0
2 1 0 419430400 512 1 YES UNUSED 0 0
3 1 0 419430400 512 1 YES UNUSED 0 0
6 1 86 5242880 512 1 NO CURRENT 1205854 04-OCT-11 2.8147E+14
SQL>
SQL> alter system archive log current;
alter system archive log current;
alter system archive log current;
System altered.
System altered.
System altered.
SQL> alter system checkpoint;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------ ------------ ------------------
1 1 87 419430400 512 1 YES INACTIVE 1206193 04-OCT-11 1206200 04-OCT-11
2 1 88 419430400 512 1 YES INACTIVE 1206200 04-OCT-11 1206209 04-OCT-11
3 1 89 419430400 512 1 NO CURRENT 1206209 04-OCT-11 2.8147E+14
6 1 86 5242880 512 1 YES INACTIVE 1205854 04-OCT-11 1206193 04-OCT-11
SQL>
SQL> alter database drop logfile group 6;
Database altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------ ------------ ------------------
1 1 87 419430400 512 1 YES INACTIVE 1206193 04-OCT-11 1206200 04-OCT-11
2 1 88 419430400 512 1 YES INACTIVE 1206200 04-OCT-11 1206209 04-OCT-11
3 1 89 419430400 512 1 NO CURRENT 1206209 04-OCT-11 2.8147E+14
SQL>
=======================
Click ok and on the previous screen to let installer do the tests again and now it will ot will not complain for anything.
This is already fixed so you will not see it.
On the following screen supply the password and for all I supplied "gurpartap1" and the path for
datafiles. The datafiles names are alerady there just add the ASM path in front of all as:
"+RESTART_DATA_1/khemkaran/datafile"
Click next.
On the following screen I kept all default.
Click next.
Following are the two screen shots half each.
Click "Install" on the following window:
Installer will start the installation. To view the log just click the vliew log url and it will open other window as shown below. Next are couple of install screen shots with logs.
Now execute the root.sh script given below as:
oracle : restart.rac.meditate.com : @+ASM : /u01/app/oracle/product/11.2.0.1
$ su -
Password:
[root@restart ~]# /u01/app/oracle/middleware/oms/allroot.sh
Starting to execute allroot.sh .........
Starting to execute /u01/app/oracle/middleware/oms/root.sh ......
Running Oracle 11g root.sh script...
The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /u01/app/oracle/middleware/oms
Enter the full pathname of the local bin directory: [/usr/local/bin]:
The file "dbhome" already exists in /usr/local/bin. Overwrite it? (y/n)
[n]:
The file "oraenv" already exists in /usr/local/bin. Overwrite it? (y/n)
[n]:
The file "coraenv" already exists in /usr/local/bin. Overwrite it? (y/n)
[n]:
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.
/etc exist
Creating /etc/oragchomelist file...
/u01/app/oracle/middleware/oms
Finished execution of /u01/app/oracle/middleware/oms/root.sh ......
Starting to execute /u01/app/oracle/middleware/agent/core/12.1.0.1.0/root.sh ......
Finished product-specific root actions.
/etc exist
Finished execution of /u01/app/oracle/middleware/agent/core/12.1.0.1.0/root.sh ......
[root@restart ~]#
$ su -
Password:
[root@restart ~]# /u01/app/oracle/middleware/oms/allroot.sh
Starting to execute allroot.sh .........
Starting to execute /u01/app/oracle/middleware/oms/root.sh ......
Running Oracle 11g root.sh script...
The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /u01/app/oracle/middleware/oms
Enter the full pathname of the local bin directory: [/usr/local/bin]:
The file "dbhome" already exists in /usr/local/bin. Overwrite it? (y/n)
[n]:
The file "oraenv" already exists in /usr/local/bin. Overwrite it? (y/n)
[n]:
The file "coraenv" already exists in /usr/local/bin. Overwrite it? (y/n)
[n]:
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.
/etc exist
Creating /etc/oragchomelist file...
/u01/app/oracle/middleware/oms
Finished execution of /u01/app/oracle/middleware/oms/root.sh ......
Starting to execute /u01/app/oracle/middleware/agent/core/12.1.0.1.0/root.sh ......
Finished product-specific root actions.
/etc exist
Finished execution of /u01/app/oracle/middleware/agent/core/12.1.0.1.0/root.sh ......
[root@restart ~]#
and click ok.
You will get the following screen. Just click close.
Finally, lets Login to Enterprise Manager Cloud Control 12c.
Open the following window in your browser as :
If this link doesn't work you may need to open your router port 7101.
Happiness, intuitive peace, poise and bliss --- in the Company ofthe Holy, meditate on the Lord of supreme bliss. You shall be spared from hell --- save your soul! Drink in the ambrosial essence of the Glorious Praises of the Lord of the Universe. Focus your consciousness on the One, the All-pervading Lord . He has One Form, but He has many manifestations. Sustainer of the Universe, Lord of the world, Kind to the poor, Destroyer of sorrow, perfectly Merciful. Meditate, meditate in remembrance on the Naam, again and again.
Subscribe to:
Posts (Atom)