How to remove OEM agent in the release 3 of the Oracle Enterprise Manager.


OEM 12c R3 has a very useful Perl script which allows removing an installed OEM agent in the host. The process is quite straightforward just by invoking the AgentDeinstall.pl script located under:
$AGENT_HOME/sysman/install/AgentDeinstall.pl
[oracle@ol6-112 12.1.0.3.0]$ /u01/app/oracle/agent12cr3/core/12.1.0.3.0/perl/bin/perl /u01/app/oracle/agent12cr3/core/12.1.0.3.0/sysman/install/AgentDeinstall.pl -agentHome $AGENT_HOME
 Agent Oracle Home: /u01/app/oracle/agent12cr3/core/12.1.0.3.0
NOTE: The agent base directory: /u01/app/oracle/agent12cr3 will be removed after successful deinstallation of agent home.
 DetachHome Command executed:/u01/app/oracle/agent12cr3/core/12.1.0.3.0/oui/bin/runInstaller -detachHome -force -depHomesOnly -silent ORACLE_HOME=/u01/app/oracle/agent12cr3/core/12.1.0.3.0 -waitForCompletion -invPtrLoc /u01/app/oracle/agent12cr3/core/12.1.0.3.0/oraInst.loc
Starting Oracle Universal Installer…
Checking swap space: must be greater than 500 MB.   Actual 8015 MB    Passed
The inventory pointer is located at /u01/app/oracle/agent12cr3/core/12.1.0.3.0/oraInst.loc
‘DetachHome’ was successful.
Starting Oracle Universal Installer…
Checking swap space: must be greater than 500 MB.   Actual 8015 MB    Passed
The inventory pointer is located at /u01/app/oracle/agent12cr3/core/12.1.0.3.0/oraInst.loc
The Oracle home ‘/u01/app/oracle/agent12cr3/sbin’ could not be updated as it does not exist.
Deinstall Command executed:/u01/app/oracle/agent12cr3/core/12.1.0.3.0/oui/bin/runInstaller -deinstall -silent “REMOVE_HOMES={/u01/app/oracle/agent12cr3/core/12.1.0.3.0}” -waitForCompletion -removeAllFiles -invPtrLoc /u01/app/oracle/agent12cr3/core/12.1.0.3.0/oraInst.loc
Starting Oracle Universal Installer…
Checking swap space: must be greater than 500 MB.   Actual 8015 MB    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2013-11-11_02-22-46PM. Please wait …Oracle Universal Installer, Version 11.1.0.11.0 Production
Copyright (C) 1999, 2013, Oracle. All rights reserved.
Starting deinstall
Deinstall in progress (Monday, November 11, 2013 2:23:00 PM CST)
Configuration assistant “Agent Deinstall Assistant” succeeded
……………………………………………………… 100% Done.
Deinstall successful
End of install phases.(Monday, November 11, 2013 2:23:37 PM CST)
End of deinstallations
Please check ‘/u01/app/oraInventory/logs/silentInstall2013-11-11_02-22-46PM.log’ for more details.
Do you want us to delete the old oracle home [yes/no] :y

Hope this help you when need to remove an OEM agent installation.
Thanks,
Alfred

Oracle Enterprise Manager 12c Release 3 New Features

Today I will talk about some new features in the release 3 of the Oracle Enterprise Manager.
  • All Metrics Chart Enhancements

This new feature show more data in the metrics page, like minimum and maximum values, it will highlight periods when the agent was unreachable or target was down, it will also show the period when the metric was in warning or critical severity.
This really helps DBAs to better understand the behavior of the target including the metric data.
  • Metrics Schedule Enhancements

Metrics now support additional collection schedule like weekly on specified days of the week and monthly on specified days of the month.



  • Service Target Dashboard

Provides a quickly out-of-box overview of the health of services, this is really useful when looking for a quick view of what service is having problem in the environment.


  • Performance Diagnostics Enhancements

Even when this is not a new feature of the OEM 12c R3, ASH Analytics is really cool. It allows DBAs to diagnose performance exactly when DBA desire; it has customized selection of the desired point in time when issues appeared.
Well, these are some new and cool features of the Oracle Enterprise Manager 12c Release 3. Hope you can enjoy this features as much as me.
Thanks,

Alfredo

Understanding Oracle Histograms

Histograms are a feature of the Cost Based Optimizer (CBO) that helps to know the data distribution in a column. Histograms should be created in columns with highly skew data and are included in the filter clause (WHERE) of a SQL statement.
Histograms play a very important role in the creation of the execution plan and we must be careful in the method used to gather statistics in the DB objects.
Let me show you how histograms work, for this test I have created a table T_HIST with some data in it:
 SQL> CREATE TABLE T_HIST AS SELECT LEVEL C1, mod(round(dbms_random.value(1,100000)),254) c2,mod(round(dbms_random.value(1,100000)),254) c3, ‘C’ C4 from dual connect by level < 529101;

SQL> INSERT INTO T_HIST SELECT LEVEL C1, mod(round(dbms_random.value(1,100000)),254) c2,
mod(round(dbms_random.value(1,100000)),254) c3, ‘P’ from dual connect by level < 301;

300 rows created.

SQL> INSERT INTO T_HIST SELECT LEVEL C1, mod(round(dbms_random.value(1,100000)),254) c2,
mod(round(dbms_random.value(1,100000)),254) c3, ‘R’ from dual connect by level < 301;

300 rows created.

SQL> INSERT INTO T_HIST SELECT LEVEL C1, mod(round(dbms_random.value(1,100000)),254) c2,
mod(round(dbms_random.value(1,100000)),254) c3, ‘T’ from dual connect by level < 301;

300 rows created.

SQL> INSERT INTO T_HIST SELECT LEVEL C1, mod(round(dbms_random.value(1,100000)),254) c2,
  2  mod(round(dbms_random.value(1,100000)),254) c3, ‘A’ from dual connect by level < 500001;

500000 rows created.

SQL> COMMIT;

Commit complete.

SQL>

We will now create an index in the column C4:
SQL> CREATE INDEX T_HIST_IDX1 ON T_HIST(C4);

Index created.

SQL>

Let us see how the data is distributed inside column C4:
SQL> SELECT C4,COUNT(*) FROM T_HIST GROUP BY C4 ORDER BY C4;
C   COUNT(*)
– ———-
A     500000
C     529100
P        300
R        300
T        300
You can see that we have 5 distinct values in column C4 (A, C, P, R & T) and the number of values that correspond to each. Let us take value ‘R’ as example, if we query the table the expected execution plan is to use the Index created, because we need only 300 rows of the 1030000 rows total. Let’s query:
SQL> explain plan for select /* test23 */ *
  2  from T_HIST
  3  where      C4 = ‘R’
  4  order by
  5      C1
  6  ;
Explained.
SQL>
SET LINESIZE 130
SET PAGESIZE 0
SELECT *
FROM   TABLE(DBMS_XPLAN.DISPLAY);  2    3    4    5    6
Explained.

SQL> SQL> SQL> SQL>   2
Plan hash value: 3146893614

——————————————————————————————–
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
——————————————————————————————–
|   0 | SELECT STATEMENT             |             |    30 |  1260 |   387   (1)| 00:00:05 |
|   1 |  SORT ORDER BY               |             |    30 |  1260 |   387   (1)| 00:00:05 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T_HIST      |    30 |  1260 |   386   (0)| 00:00:05 |
|*  3 |    INDEX RANGE SCAN          | T_HIST_IDX1 |  3607 |       |   375   (0)| 00:00:05 |
——————————————————————————————–

Predicate Information (identified by operation id):
—————————————————

   3 – access(“C4″=’R’)

Note
—–
   – dynamic sampling used for this statement (level=2)

What happened? Execution plan is saying that our query will return only 30 rows! obviously this is totally wrong; also look at the estimated elapsed time (5 sec). Ok, we are being unfair to the CBO as we are not giving all the information required, so let’s gather statistics but without histograms:
SQL> exec DBMS_STATS.GATHER_TABLE_STATS (ownname => ‘ALFRED’, tabname=>’T_HIST’, estimate_percent=>dbms_stats.auto_sample_size,method_opt=>’for all columns size 1′);
PL/SQL procedure successfully completed.
SQL> explain plan for select /* test23 */ *
  2  from T_HIST
  3  where      C4 = ‘R’
  4  order by
  5      C1
  6  ;
Explained.
SQL>
SQL> SET LINESIZE 130
SQL> SET PAGESIZE 0
SQL> SELECT *
  2  FROM   TABLE(DBMS_XPLAN.DISPLAY);
Plan hash value: 2911980760
————————————————————————————-
| Id  | Operation          | Name   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
————————————————————————————-
|   0 | SELECT STATEMENT   |        |   206K|  3017K|       |  1832   (2)| 00:00:22 |
|   1 |  SORT ORDER BY     |        |   206K|  3017K|  5672K|  1832   (2)| 00:00:22 |
|*  2 |   TABLE ACCESS FULL| T_HIST |   206K|  3017K|       |   774   (2)| 00:00:10 |
————————————————————————————-
Predicate Information (identified by operation id):
—————————————————
   2 – filter(“C4″=’R’)
What happened? Now the CBO decided to choose a Full Table Scan, it also states that will return 206k rows and the estimated elapsed time is 22 sec. That’s because we used AUTO_SAMPLE_SIZE so Oracle we decide how many rows to sample and for sure values of ‘A’ & ‘C’ represent more than 99% of the samples. But how can we tell the CBO that our data distribution is skewed? that’s where histograms enter into play. Let’s now gather statistics using histogram for column C4:
SQL> exec DBMS_STATS.GATHER_TABLE_STATS (ownname => ‘ALFRED’, tabname=>’T_HIST’, estimate_percent=>dbms_stats.auto_sample_size,method_opt=>’for columns C4 size 254′);
PL/SQL procedure successfully completed.
SQL> explain plan for select /* test23 */ *
  2  from T_HIST
  3  where      C4 = ‘R’
  4  order by
  5      C1
  6  ;
Explained.
SQL>
SQL> SET LINESIZE 130
SQL> SET PAGESIZE 0
SQL> SELECT *
  2  FROM   TABLE(DBMS_XPLAN.DISPLAY);
Plan hash value: 3146893614
——————————————————————————————–
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
——————————————————————————————–
|   0 | SELECT STATEMENT             |             |   941 | 14115 |     8  (13)| 00:00:01 |
|   1 |  SORT ORDER BY               |             |   941 | 14115 |     8  (13)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T_HIST      |   941 | 14115 |     7   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T_HIST_IDX1 |   941 |       |     4   (0)| 00:00:01 |
——————————————————————————————–
Predicate Information (identified by operation id):
—————————————————
   3 – access(“C4″=’R’)
Now is using the Index again, states that will return 941 rows but look how the cost decreased and the estimated elapsed time to 1 sec! Even if the CBO wasn’t able to calculate the real number of rows being returned the cost was fixed, in order to have the most accurate execution plan we must increase the sample size. Let us make a test with 100% of the samples:
SQL> exec DBMS_STATS.GATHER_TABLE_STATS (ownname => ‘ALFRED’, tabname=>’T_HIST’, estimate_percent=>100,method_opt=>’for columns C4 size 254′);
PL/SQL procedure successfully completed.
SQL> explain plan for select /* test23 */ *
  2  from T_HIST
  3  where      C4 = ‘R’
  4  order by
  5      C1
  6  ;
Explained.
SQL>
SQL> SET LINESIZE 130
SQL> SET PAGESIZE 0
SQL> SELECT *
  2  FROM   TABLE(DBMS_XPLAN.DISPLAY);
Plan hash value: 3146893614
——————————————————————————————–
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
——————————————————————————————–
|   0 | SELECT STATEMENT             |             |   300 |  4500 |     5  (20)| 00:00:01 |
|   1 |  SORT ORDER BY               |             |   300 |  4500 |     5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T_HIST      |   300 |  4500 |     4   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T_HIST_IDX1 |   300 |       |     3   (0)| 00:00:01 |
——————————————————————————————–
Predicate Information (identified by operation id):
—————————————————
   3 – access(“C4″=’R’)
Now is totally accurate! Right? The execution plan generated by the CBO is using the Index, the estimated rows are 300 which is correct and the cost is even better. This is an example of a very small table, but is really an option to gather statistics with estimate of 100% to a big table?, I don’t think so; that’s why AUTO_SAMPLE_SIZE can be a good balance between sample size and accuracy.
Column C4 now is having a frequency histogram because is having less than 254 distinct values (NDV), only 5; that means that frequency histogram has the restriction of 254 buckets (254 NDV’s), seriously?. Let test it, we will gather statistics to all columns of the table using AUTO_SAMPLE_SIZE and AUTO for histograms:
SQL> exec dbms_stats.gather_table_stats(ownname=>’ALFRED’,tabname=>’T_HIST’, estimate_percent=>null, method_opt=>’FOR ALL COLUMNS SIZE AUTO’);
PL/SQL procedure successfully completed.
SQL> select COLUMN_NAME, NUM_DISTINCT, DENSITY, NUM_BUCKETS, HISTOGRAM from user_tab_columns where table_name = ‘T_HIST’;
C1                                   529100  .00000189           1 NONE
C2                                      254 .003937008           1 NONE
C3                                      254 4.8544E-07         254 FREQUENCY
C4                                        5 4.8544E-07           5 FREQUENCY
What happened?, well column C3 was also candidate for a histogram because it’s density (skewed) but look at the number of buckets is 254; that means that we are on the edge of the limit in order to have a histogram in this column. Don’t believe me? let’s make a quick test, we will insert a new distinct value into this column, gather statistics again and see what happen:
SQL> insert into T_HIST values (100001,42, 99999999999,’B’);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> exec dbms_stats.gather_table_stats(ownname=>’ALFRED’,tabname=>’T_HIST’, estimate_percent=>null, method_opt=>’FOR ALL COLUMNS SIZE AUTO’);
PL/SQL procedure successfully completed.
SQL> select COLUMN_NAME, NUM_DISTINCT, DENSITY, NUM_BUCKETS, HISTOGRAM from user_tab_columns where table_name = ‘T_HIST’;
C1                                   529100  .00000189           1 NONE
C2                                      254 .003937008           1 NONE
C3                                      255 .003921569           1 NONE
C4                                        6 4.8544E-07           6 FREQUENCY
Can you see how the number of distinct values (NVD) changed? now we can’t have a histogram in that column, really?; well we can force it but look how low is our density. Let’s try to force it and specify to the gather stats that we want all columns with maximum of 254 buckets:
SQL> exec dbms_stats.gather_table_stats(ownname=>’ALFRED’,tabname=>’T_HIST’, estimate_percent=>null, method_opt=>’FOR ALL COLUMNS SIZE 254′);
PL/SQL procedure successfully completed.
SQL> select COLUMN_NAME, NUM_DISTINCT, DENSITY, NUM_BUCKETS, HISTOGRAM from user_tab_columns where table_name = ‘T_HIST’;
C1                                   529100 1.9186E-06         254 HEIGHT BALANCED
C2                                      254 4.8544E-07         254 FREQUENCY
C3                                      255 .003935563         254 HEIGHT BALANCED
C4                                        6 4.8544E-07           6 FREQUENCY
Look how it works? it can create frequency histogram on columns with less than 254 NDV’s and height balanced histogram in columns with more than 254 NDV’s. Also note the first example, we used size of 1 (1 bucket) that means no histogram because all values are inside only one bucket.
This is a basic example of how histograms work, honestly I was having a lot of doubts before making this test and some of them are clarified reading Jonathan Lewis new post in allthinngsoracle.com, however there’s a lot of path to walk through and even difficult as 12c have 2 more types of histograms to help CBO.

Thanks,
Alfredo  

MRP0: Background Media Recovery terminated with error 1237

Some days back checking an Oracle physical standby database found that the DB was some hours back than the primary database.

alter session set nls_date_format=’DD-MM-yyyy HH24:MI:SS’;
show parameter dest
select thread#,max(sequence#) from gv$log_history group by thread#;
select (a.amct-b.bmct)*24 “Hours Standby is Behind: ”
from (select max(completion_time) amct from v$archived_log) a,
(select max(completion_time) bmct from v$archived_log where applied=’YES’) b;
Hours Standby is Behind:
————————-
45.000054
The very next thing to check is what is going on with the MRP process.
select inst_id, process,status,sequence#, thread# from gv$managed_standby where process=’MRP0′;
no rows selected
So, the MRP process wasn’t running in the standby database. Let’s check the alert.log file.
MRP0: Background Media Recovery terminated with error 1237
ORA-01237: cannot extend datafile 13
The mount point where the datafile 13 resides is 100% full, that’s why the MRP couldn’t resize the datafile and was terminated by the instance.
In order to fix this you should increase the size of the mount point or if you have another mount point with enough free space you can do the following:

      ·        Shutdown standby database

SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
·                  ·         Copy the datafile to the new location
#> cp –p users03.dbf /u02/oradata/test/users03.dbf
·          Startup mount standby database
SQL> startup nomount
ORACLE instance started.
SQL> alter database mount standby database;
Database altered.
·                    ·        Modify “standby_file_management” parameter to manual
As per Oracle documentation:
STANDBY_FILE_MANAGEMENT enables or disables automatic standby file management. When automatic standby file management is enabled, operating system file additions and deletions on the primary database are replicated on the standby database.
SQL> alter system set standby_file_management=’MANUAL’ scope=both;
System altered.
·          Rename the datafile in order to reflect the changes in the standby control file.
SQL> alter database rename file ‘/u01/oradata/test/users03.dbf’ to ‘/u02/oradata/test/users03.dbf’;
Database altered.
·          Now let’s reset “standby_file_management” to AUTO.
SQL> alter system set standby_file_management=’AUTO’ scope=both;
System altered.
·          And start the MRP process again.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
After this MRP was able to successfully apply archive logs from primary database.
We have to be sure that every time we increase the size of a datafile in the primary database, have enough free space in the standby server to fit the new size of the datafile.
Thanks,

Alfredo

Testing user calls

In the performance tuning world specially talking about Response Time Analysis (RTA), user calls is often used as a workload metric; however this instance statistic was not yet completely clear.

When this metric is incremented?, How this metric is instrumented?, Why sometimes user calls is less with the same SQL statement?; these were some questions rounding my mind.

My friend Craig Shallahamer did a great post in his blog “A Wider View” related to user calls (http://shallahamer-orapub.blogspot.mx/2010/05/understanding-user-calls.html), however looking into Tanel Poder’s blog today everything got clear.

Let’s start tracing an Oracle session using trace events ‘10046’ & ‘10051’. 

Event ‘10051’ will trace OPI calls, more information (http://blog.tanelpoder.com/2011/03/20/lobread-sql-trace-entry-in-oracle-11-2/), what are OPI calls?, well as per Oracle documentation:

Oracle Program Interface (OPI)

A networking layer responsible for responding to each of the possible messages sent by OCI. For example, an OCI request to fetch 25 rows would have an OPI response to return the 25 rows once they have been fetched.


Let’s do it!

SQL> ALTER SESSION SET EVENTS ‘10046 TRACE NAME CONTEXT FOREVER, LEVEL 12’;
ALTER SESSION SET EVENTS ‘10051 TRACE NAME CONTEXT FOREVER, LEVEL 12’;
Session altered.
SQL>
Session altered.
SQL> SELECT TRACEFILE FROM V$PROCESS WHERE ADDR =(SELECT PADDR FROM V$SESSION WHERE SID=49);
TRACEFILE
——————————————————————————–
/u01/app/oracle/diag/rdbms/rac/RAC1/trace/RAC1_ora_5393.trc

In another window open the trace file using tail –f in order to monitor the trace execution. Now let’s issue a small query:

SQL> select * from dual;
D
X
SQL>
Here’s the output of the trace file:

*** 2013-08-05 21:42:15.721
WAIT #3: nam=’SQL*Net message from client’ ela= 342028978 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1375756935721909
OPI CALL: type=105 argc= 2 cursor=  0 name=Cursor close all
CLOSE #3:c=0,e=18,dep=0,type=0,tim=1375756935722017
OPI CALL: type=94 argc=28 cursor=  0 name=V8 Bundled Exec
WAIT #1: nam=’ges message buffer allocation’ ela= 2 pool=0 request=1 allocated=0 obj#=-1 tim=1375756935722368
WAIT #1: nam=’ges message buffer allocation’ ela= 0 pool=0 request=1 allocated=0 obj#=-1 tim=1375756935722448
WAIT #1: nam=’library cache lock’ ela= 568 handle address=1287268616 lock address=1263092292 100*mode+namespace=65538 obj#=-1 tim=1375756935723089
WAIT #1: nam=’ges message buffer allocation’ ela= 1 pool=0 request=1 allocated=0 obj#=-1 tim=1375756935723137
WAIT #1: nam=’ges message buffer allocation’ ela= 2 pool=0 request=1 allocated=0 obj#=-1 tim=1375756935723205
WAIT #1: nam=’library cache pin’ ela= 473 handle address=1287268616 pin address=1263092428 100*mode+namespace=65538 obj#=-1 tim=1375756935723815
=====================
PARSING IN CURSOR #4 len=210 dep=1 uid=0 oct=3 lid=0 tim=1375756935724353 hv=864012087 ad=’4cb562e8′ sqlid=’96g93hntrzjtr’
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where obj#=:1 and intcol#=:2
END OF STMT
PARSE #4:c=0,e=70,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=3,plh=2239883476,tim=1375756935724351
BINDS #4:
 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=0040a79c  bln=22  avl=03  flg=05
  value=116
 Bind#1
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=0040a778  bln=24  avl=02  flg=05
  value=1
EXEC #4:c=0,e=89,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=3,plh=2239883476,tim=1375756935724524
FETCH #4:c=0,e=51,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=3,plh=2239883476,tim=1375756935724594
STAT #4 id=1 cnt=1 pid=0 pos=1 obj=424 op=’TABLE ACCESS BY INDEX ROWID HIST_HEAD$ (cr=3 pr=0 pw=0 time=0 us)’
STAT #4 id=2 cnt=1 pid=1 pos=1 obj=426 op=’INDEX RANGE SCAN I_HH_OBJ#_INTCOL# (cr=2 pr=0 pw=0 time=0 us)’
CLOSE #4:c=0,e=40,dep=1,type=3,tim=1375756935724660
=====================
PARSING IN CURSOR #1 len=18 dep=0 uid=0 oct=3 lid=0 tim=1375756935725402 hv=942515969 ad=’4b5c8e54′ sqlid=’a5ks9fhw2v9s1′
select * from dual
END OF STMT
PARSE #1:c=3999,e=3334,p=0,cr=3,cu=0,mis=1,r=0,dep=0,og=1,plh=272002086,tim=1375756935725401
EXEC #1:c=0,e=13,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=272002086,tim=1375756935725457
WAIT #1: nam=’SQL*Net message to client’ ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1375756935725480
WAIT #1: nam=’ges message buffer allocation’ ela= 2 pool=0 request=1 allocated=0 obj#=-1 tim=1375756935725627
WAIT #1: nam=’gc cr grant 2-way’ ela= 1035 p1=1 p2=928 p3=4 obj#=116 tim=1375756935726935
WAIT #1: nam=’db file sequential read’ ela= 634 file#=1 block#=928 blocks=1 obj#=116 tim=1375756935737381
WAIT #1: nam=’ges message buffer allocation’ ela= 2 pool=0 request=1 allocated=0 obj#=116 tim=1375756935741146
WAIT #1: nam=’gc cr grant 2-way’ ela= 470 p1=1 p2=929 p3=1 obj#=116 tim=1375756935741693
WAIT #1: nam=’db file sequential read’ ela= 1312 file#=1 block#=929 blocks=1 obj#=116 tim=1375756935743074
FETCH #1:c=1999,e=17670,p=2,cr=3,cu=0,mis=0,r=1,dep=0,og=1,plh=272002086,tim=1375756935743169
STAT #1 id=1 cnt=1 pid=0 pos=1 obj=116 op=’TABLE ACCESS FULL DUAL (cr=3 pr=2 pw=0 time=0 us cost=2 size=2 card=1)’
WAIT #1: nam=’SQL*Net message from client’ ela= 221 driver id=1650815232 #bytes=1 p3=0 obj#=116 tim=1375756935743513
OPI CALL: type= 5 argc= 2 cursor=  1 name=FETCH
FETCH #1:c=0,e=1,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=272002086,tim=1375756935743579
WAIT #1: nam=’SQL*Net message to client’ ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=116 tim=1375756935743629
Snapper shows the below metrics for our session:

SQL> @snapper4 all 5 1 49
Sampling SID 49 with interval 5 seconds, taking 1 snapshots…
— Session Snapper v4.09 BETA – by Tanel Poder ( http://blog.tanelpoder.com ) – Enjoy the Most Advanced Oracle Troubleshooting Script on the Planet! 🙂
——————————————————————————————————————————————————————————————————————
 SID @INST, USERNAME  , TYPE, STATISTIC                                                 ,         DELTA, HDELTA/SEC,    %TIME, GRAPH       , NUM_WAITS,  WAITS/SEC,   AVERAGES
——————————————————————————————–
    49  @1, SYS       , STAT, opened cursors cumulative,             1,         .2,         ,             ,          ,           ,          1 per execution
    49  @1, SYS       , STAT, user calls,             3,        .59,         ,             ,          ,           ,          3 per execution
Now let’s create a dummy table and insert some rows:
SQL> insert into test_user (user_name) select username from dba_users;
36 rows created.

From trace file:
*** 2013-08-05 21:55:34.386
WAIT #1: nam=’SQL*Net message from client’ ela= 58636486 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1375757734386174
OPI CALL: type=105 argc= 2 cursor=  0 name=Cursor close all
CLOSE #1:c=0,e=15,dep=0,type=0,tim=1375757734386272
OPI CALL: type=94 argc=28 cursor=  0 name=V8 Bundled Exec
=====================
PARSING IN CURSOR #3 len=37 dep=1 uid=0 oct=3 lid=0 tim=1375757734387095 hv=1398610540 ad=’4cbf4cb0′ sqlid=’grwydz59pu6mc’
select text from view$ where rowid=:1
END OF STMT
PARSE #3:c=0,e=375,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1375757734387093
BINDS #3:
 Bind#0
  oacdty=11 mxl=16(16) mxlc=00 mal=00 scl=00 pre=00
  oacflg=18 fl2=0001 frm=00 csi=00 siz=16 off=0
  kxsbbbfp=1184df90  bln=16  avl=16  flg=05
  value=00002068.0000.0001
EXEC #3:c=2000,e=54991,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=3684871272,tim=1375757734442193
FETCH #3:c=0,e=34,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,plh=3684871272,tim=1375757734442283
STAT #3 id=1 cnt=1 pid=0 pos=1 obj=69 op=’TABLE ACCESS BY USER ROWID VIEW$ (cr=1 pr=0 pw=0 time=0 us cost=1 size=15 card=1)’
CLOSE #3:c=0,e=51,dep=1,type=0,tim=1375757734442357
WAIT #2: nam=’ges message buffer allocation’ ela= 4 pool=0 request=1 allocated=0 obj#=-1 tim=1375757734443204
WAIT #2: nam=’ges message buffer allocation’ ela= 2 pool=0 request=1 allocated=0 obj#=-1 tim=1375757734443382
WAIT #2: nam=’library cache lock’ ela= 549 handle address=1222331724 lock address=1220909652 100*mode+namespace=65538 obj#=-1 tim=1375757734444345
WAIT #2: nam=’KJC: Wait for msg sends to complete’ ela= 10 msg=1361877012 dest|rcvr=65536 mtype=12 obj#=-1 tim=1375757734444389
WAIT #2: nam=’ges message buffer allocation’ ela= 2 pool=0 request=1 allocated=0 obj#=-1 tim=1375757734444433
WAIT #2: nam=’ges message buffer allocation’ ela= 2 pool=0 request=1 allocated=0 obj#=-1 tim=1375757734444564
WAIT #2: nam=’library cache pin’ ela= 640 handle address=1222331724 pin address=1220909516 100*mode+namespace=65538 obj#=-1 tim=1375757734445329
WAIT #2: nam=’KJC: Wait for msg sends to complete’ ela= 9 msg=1361877012 dest|rcvr=65536 mtype=12 obj#=-1 tim=1375757734445367
=====================
PARSING IN CURSOR #6 len=210 dep=1 uid=0 oct=3 lid=0 tim=1375757734475342 hv=864012087 ad=’4cb562e8′ sqlid=’96g93hntrzjtr’
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where obj#=:1 and intcol#=:2
END OF STMT
PARSE #6:c=0,e=73,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=3,plh=2239883476,tim=1375757734475341
BINDS #6:
 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=0040210c  bln=22  avl=04  flg=05
  value=74760
 Bind#1
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=004020e8  bln=24  avl=02  flg=05
  value=1
EXEC #6:c=0,e=91,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=3,plh=2239883476,tim=1375757734475532
FETCH #6:c=0,e=15,p=0,cr=2,cu=0,mis=0,r=0,dep=1,og=3,plh=2239883476,tim=1375757734475567
STAT #6 id=1 cnt=0 pid=0 pos=1 obj=424 op=’TABLE ACCESS BY INDEX ROWID HIST_HEAD$ (cr=2 pr=0 pw=0 time=0 us)’
STAT #6 id=2 cnt=0 pid=1 pos=1 obj=426 op=’INDEX RANGE SCAN I_HH_OBJ#_INTCOL# (cr=2 pr=0 pw=0 time=0 us)’
CLOSE #6:c=0,e=1,dep=1,type=3,tim=1375757734475616
=====================
PARSING IN CURSOR #2 len=64 dep=0 uid=0 oct=2 lid=0 tim=1375757734477276 hv=1206268670 ad=’48d0a418′ sqlid=’4x5wmqj3yccry’
insert into test_user (user_name) select username from dba_users
END OF STMT
PARSE #2:c=10999,e=90955,p=0,cr=4,cu=0,mis=1,r=0,dep=0,og=1,plh=3422547789,tim=1375757734477275
WAIT #2: nam=’ges message buffer allocation’ ela= 5 pool=0 request=1 allocated=0 obj#=74760 tim=1375757734478040
WAIT #2: nam=’ges message buffer allocation’ ela= 2 pool=0 request=1 allocated=0 obj#=74760 tim=1375757734478200
WAIT #2: nam=’enq: TM – contention’ ela= 616 name|mode=1414332419 object #=74760 table/partition=0 obj#=74760 tim=1375757734478936
WAIT #2: nam=’asynch descriptor resize’ ela= 4 outstanding #aio=0 current aio limit=384 new aio limit=390 obj#=-1 tim=1375757734486405
WAIT #2: nam=’ges message buffer allocation’ ela= 2 pool=0 request=1 allocated=0 obj#=-1 tim=1375757734486564
WAIT #2: nam=’gc cr grant 2-way’ ela= 519 p1=1 p2=1976 p3=4 obj#=281 tim=1375757734487242
WAIT #2: nam=’db file sequential read’ ela= 788 file#=1 block#=1976 blocks=1 obj#=281 tim=1375757734488113
WAIT #2: nam=’ges message buffer allocation’ ela= 2 pool=0 request=1 allocated=0 obj#=281 tim=1375757734488224
WAIT #2: nam=’gc cr grant 2-way’ ela= 431 p1=1 p2=1977 p3=1 obj#=281 tim=1375757734488790
WAIT #2: nam=’db file sequential read’ ela= 889 file#=1 block#=1977 blocks=1 obj#=281 tim=1375757734489741
WAIT #2: nam=’ges message buffer allocation’ ela= 2 pool=0 request=1 allocated=0 obj#=281 tim=1375757734491203
WAIT #2: nam=’ges message buffer allocation’ ela= 2 pool=0 request=1 allocated=0 obj#=281 tim=1375757734491352
WAIT #2: nam=’enq: HW – contention’ ela= 1020 name|mode=1213661190 table space #=0 block=4282248 obj#=281 tim=1375757734492503
WAIT #2: nam=’ges message buffer allocation’ ela= 2 pool=0 request=1 allocated=0 obj#=281 tim=1375757734492596
WAIT #2: nam=’gc current grant 2-way’ ela= 422 p1=1 p2=87945 p3=33554433 obj#=74760 tim=1375757734493138
EXEC #2:c=5000,e=15461,p=2,cr=41,cu=7,mis=0,r=36,dep=0,og=1,plh=3422547789,tim=1375757734493365
STAT #2 id=1 cnt=0 pid=0 pos=1 obj=0 op=’LOAD TABLE CONVENTIONAL  (cr=41 pr=2 pw=0 time=0 us)’
STAT #2 id=2 cnt=36 pid=1 pos=1 obj=0 op=’HASH JOIN  (cr=40 pr=2 pw=0 time=35 us cost=24 size=1092 card=13)’
STAT #2 id=3 cnt=36 pid=2 pos=1 obj=0 op=’HASH JOIN  (cr=37 pr=2 pw=0 time=35 us cost=22 size=1053 card=13)’
STAT #2 id=4 cnt=36 pid=3 pos=1 obj=0 op=’HASH JOIN  (cr=28 pr=2 pw=0 time=35 us cost=17 size=1014 card=13)’
STAT #2 id=5 cnt=36 pid=4 pos=1 obj=0 op=’HASH JOIN OUTER (cr=19 pr=2 pw=0 time=140 us cost=13 size=975 card=13)’
STAT #2 id=6 cnt=36 pid=5 pos=1 obj=0 op=’HASH JOIN  (cr=16 pr=2 pw=0 time=35 us cost=10 size=598 card=13)’
STAT #2 id=7 cnt=36 pid=6 pos=1 obj=0 op=’HASH JOIN  (cr=13 pr=0 pw=0 time=0 us cost=8 size=572 card=13)’
STAT #2 id=8 cnt=2 pid=7 pos=1 obj=0 op=’MERGE JOIN CARTESIAN (cr=6 pr=0 pw=0 time=3 us cost=4 size=16 card=1)’
STAT #2 id=9 cnt=1 pid=8 pos=1 obj=280 op=’TABLE ACCESS FULL PROFILE$ (cr=3 pr=0 pw=0 time=0 us cost=2 size=8 card=1)’
STAT #2 id=10 cnt=2 pid=8 pos=2 obj=0 op=’BUFFER SORT (cr=3 pr=0 pw=0 time=1 us cost=2 size=8 card=1)’
STAT #2 id=11 cnt=2 pid=10 pos=1 obj=280 op=’TABLE ACCESS FULL PROFILE$ (cr=3 pr=0 pw=0 time=1 us cost=2 size=8 card=1)’
STAT #2 id=12 cnt=36 pid=7 pos=2 obj=22 op=’TABLE ACCESS FULL USER$ (cr=7 pr=0 pw=0 time=0 us cost=3 size=1008 card=36)’
STAT #2 id=13 cnt=2 pid=6 pos=2 obj=281 op=’TABLE ACCESS FULL PROFNAME$ (cr=3 pr=2 pw=0 time=1 us cost=2 size=2 card=1)’
STAT #2 id=14 cnt=2 pid=5 pos=2 obj=297 op=’TABLE ACCESS FULL RESOURCE_GROUP_MAPPING$ (cr=3 pr=0 pw=0 time=1 us cost=2 size=29 card=1)’
STAT #2 id=15 cnt=7 pid=4 pos=2 obj=16 op=’TABLE ACCESS FULL TS$ (cr=9 pr=0 pw=0 time=48 us cost=4 size=21 card=7)’
STAT #2 id=16 cnt=7 pid=3 pos=2 obj=16 op=’TABLE ACCESS FULL TS$ (cr=9 pr=0 pw=0 time=12 us cost=4 size=21 card=7)’
STAT #2 id=17 cnt=9 pid=2 pos=2 obj=292 op=’TABLE ACCESS FULL USER_ASTATUS_MAP (cr=3 pr=0 pw=0 time=8 us cost=2 size=27 card=9)’
WAIT #2: nam=’SQL*Net message to client’ ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=74760 tim=1375757734493823
And from Snapper:
SQL> @snapper4 all 10 1 49
Sampling SID 49 with interval 10 seconds, taking 1 snapshots…
— Session Snapper v4.09 BETA – by Tanel Poder ( http://blog.tanelpoder.com ) – Enjoy the Most Advanced Oracle Troubleshooting Script on the Planet! 🙂
——————————————————————————————————————————————————————————————————————
 SID @INST, USERNAME  , TYPE, STATISTIC                                                 ,         DELTA, HDELTA/SEC,    %TIME, GRAPH       , NUM_WAITS,  WAITS/SEC,   AVERAGES
——————————————————————————————————————————————————————————————————————
    49  @1, SYS       , STAT, opened cursors cumulative,             3,        .33,         ,             ,          ,           ,          1 per execution
    49  @1, SYS       , STAT, user calls,             2,        .22,         ,             ,          ,           ,        .67 per execution
Now let’s commit:
SQL> commit;
Commit complete.
SQL>
From trace file:
*** 2013-08-05 22:01:49.281
WAIT #2: nam=’SQL*Net message from client’ ela= 374787222 driver id=1650815232 #bytes=1 p3=0 obj#=74760 tim=1375758109281068
OPI CALL: type=105 argc= 2 cursor=  0 name=Cursor close all
CLOSE #2:c=0,e=20,dep=0,type=0,tim=1375758109281167
OPI CALL: type=94 argc=28 cursor=  0 name=V8 Bundled Exec
=====================
PARSING IN CURSOR #4 len=6 dep=0 uid=0 oct=44 lid=0 tim=1375758109281299 hv=3480936638 ad=’0′ sqlid=’23wm3kz7rps5y’
commit
END OF STMT
PARSE #4:c=0,e=91,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=1375758109281298
XCTEND rlbk=0, rd_only=0, tim=1375758109281345
EXEC #4:c=0,e=102,p=0,cr=0,cu=1,mis=0,r=0,dep=0,og=0,plh=0,tim=1375758109281434
WAIT #4: nam=’log file sync’ ela= 24619 buffer#=4018 sync scn=1381492 p3=0 obj#=74760 tim=1375758109306071
WAIT #4: nam=’SQL*Net message to client’ ela= 351 driver id=1650815232 #bytes=1 p3=0 obj#=74760 tim=1375758109307375
From Snapper:
SQL> @snapper4 all 6 1 49
Sampling SID 49 with interval 6 seconds, taking 1 snapshots…
— Session Snapper v4.09 BETA – by Tanel Poder ( http://blog.tanelpoder.com ) – Enjoy the Most Advanced Oracle Troubleshooting Script on the Planet! 🙂
——————————————————————————————————————————————————————————————————————
 SID @INST, USERNAME  , TYPE, STATISTIC                                                 ,         DELTA, HDELTA/SEC,    %TIME, GRAPH       , NUM_WAITS,  WAITS/SEC,   AVERAGES
——————————————————————————————————————————————————————————————————————
    49  @1, SYS       , STAT, opened cursors cumulative,             1,        .17,         ,             ,          ,           ,          ~ per execution
    49  @1, SYS       , STAT, user commits,             1,        .17,         ,             ,          ,           ,          ~ per execution
    49  @1, SYS       , STAT, user calls,             2,        .34,         ,             ,          ,           ,          ~ per execution
This is just a little example of how OPI CALL is related to user calls metric. You can play and test more scenarios of select statements to see how they are being affected by ARRAYSIZE setting in SqlPlus.
Craig posted a table containing the result of his testing which is having the same results as this test.

SQL
User Calls
Select 1 row
3
Insert
2
Commit
2
Thanks,

Alfredo

Oracle 12c ASM new features


Today I will talk about some new features for ASM on 12c version.
Oracle Flex ASM
Oracle Flex ASM is a set of new features that provide critical capabilities required for cloud computing. Oracle Flex ASM redefines the traditional ASM cluster architecture of having one ASM instance on every node in the cluster; this means you can have less ASM instances than nodes in your cluster. In this configuration the number of ASM instances running is called ASM cardinality, by default the ASM cardinality is 3 and can be changed with a Clusterware command.
What are the benefits?
On the traditional architecture when an ASM instance fails; all DB instances connected to that instance in the node will also fail. With Oracle Flex ASM if the ASM instance fail the Clusterware will relocate that ASM instance to a different node and the DB instances will remotely (through private network) connect to the relocated ASM instance, all without disruption to the DB client.
Dedicated ASM Network
This new 12c feature provides the option to dedicate a private network for ASM network traffic only. There’s also the option to use the Oracle Clusterware interconnect private network.
Remote Access
As per the new Oracle Flex ASM feature, the DB instance can remotely connect to ASM instance hence the need of a password file in order to authenticate remote DB instances to ASM. Oracle Flex ASM has the ability of storing password files in a Disk Group and is extended to DB clients; this is really useful to avoid synchronizing multiple password files within the cluster.
Oracle Flex ASM is one of the most important enhancements to ASM in 12c version, however in the below whitepaper describes the rest of new features present.
Bertrand Drouvot has already tested this Oracle Flex ASM feature and uploaded the contents into his own blog (http://bdrouvot.wordpress.com/2013/06/29/build-your-own-flex-asm-12c-lab-using-virtual-box/).
Another interesting feature is the Extent Reading Selection Enhancement which evenly distributes the selection of which copy of a block is read in ASM Disk Groups with normal or high redundancy. This feature is enabled by default in 12c and states that users on I/O bound systems should notice a performance improvement while reading blocks from disks.
Thanks,
Alfredo

Why my new Disk Group is not getting mounted automatically when ASM instance starts?

Today I faced the issue that a new ASM Disk Group created in instance +ASM1 is not getting automatically mounted in instance +ASM2.

The first step was to check DB dependencies:

[oracle@ol5-112-rac2 trace]$ srvctl config database -d RAC
Database unique name: RAC
Database name: RAC
Oracle home: /u01/app/oracle/product/11.2.0/db_1
Oracle user: oracle
Spfile: +DATA/RAC/spfileRAC.ora
Domain: localdomain
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: RAC
Database instances: RAC1,RAC2
Disk Groups: DATA
Services: reports
Database is administrator managed
You can see that only dependent Disk Groups are ‘DATA’.

Let us add ‘FRA’ also as dependent Disk Group:

[oracle@ol5-112-rac2 trace]$ srvctl modify database -d RAC -a ‘DATA,FRA’
[oracle@ol5-112-rac2 trace]$ srvctl config database -d RAC
Database unique name: RAC
Database name: RAC
Oracle home: /u01/app/oracle/product/11.2.0/db_1
Oracle user: oracle
Spfile: +DATA/RAC/spfileRAC.ora
Domain: localdomain
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: RAC
Database instances: RAC1,RAC2
Disk Groups: DATA,FRA
Services: reports
Database is administrator managed
The next step is to add the Disk Groups to ASM_DISKGROUPS parameter:

SQL> ALTER SYSTEM SET ASM_DISKGROUPS=’DATA’,’FRA’ scope=both sid=’+ASM1′;
System altered.
SQL> ALTER SYSTEM SET ASM_DISKGROUPS=’DATA’,’FRA’ scope=both sid=’+ASM2′;
System altered.
After restarting +ASM2 instance we can see that FRA Disk Group is mounted automatically:

[oracle@ol5-112-rac2 trace]$ sqlplus / as sysasm
SQL*Plus: Release 11.2.0.1.0 Production on Wed Jun 12 15:44:44 2013
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
With the Real Application Clusters and Automatic Storage Management options
SQL> set lines 1000
SQL> select * from V$ASM_DISKGROUP;
GROUP_NUMBER NAME                           SECTOR_SIZE BLOCK_SIZE ALLOCATION_UNIT_SIZE STATE       TYPE     TOTAL_MB    FREE_MB HOT_USED_MB COLD_USED_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB OFFLINE_DISKS COMPATIBILITY                                                DATABASE_COMPATIBILITY                                       V
———— —————————— ———– ———- ——————– ———– —— ———- ———- ———– ———— ———————– ————– ————- ———————————————————— ———————————————————— –
           1 DATA                                   512       4096              1048576 MOUNTED     EXTERN      15342      12616           0         2726                       0          12616     0 11.2.0.0.0                                                   10.1.0.0.0                                                   N
           2 FRA                                    512       4096              1048576 MOUNTED     EXTERN       6133       5962           0          171                       0           5962     0 11.2.0.0.0                                                   10.1.0.0.0                                                   N
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
With the Real Application Clusters and Automatic Storage Management options
Thanks,

Alfredo

ORA-15097: cannot SHUTDOWN ASM instance with connected client

In 11g R2 Clusterware configurations the OCR is located in ASM disk, that’s why ASM can’t be shutdown while the cluster is running.  You have to shutdown your cluster in order to shutdown ASM instance.

Oracle support note 984663.1

Here’s a good blog about this:
Example, how to shutdown ASM instance on node ol5-112-rac1:
[oracle@ol5-112-rac1 ~]$ crsctl status resource -t
——————————————————————————–
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
——————————————————————————–
Local Resources
——————————————————————————–
ora.DATA.dg
               ONLINE  ONLINE       ol5-112-rac1
               ONLINE  ONLINE       ol5-112-rac2
ora.FRA.dg
               OFFLINE OFFLINE      ol5-112-rac1
               ONLINE  ONLINE       ol5-112-rac2
ora.LISTENER.lsnr
               ONLINE  ONLINE       ol5-112-rac1
               ONLINE  ONLINE       ol5-112-rac2
ora.asm
               ONLINE  ONLINE       ol5-112-rac1
               ONLINE  ONLINE       ol5-112-rac2             Started
ora.eons
               ONLINE  ONLINE       ol5-112-rac1
               ONLINE  ONLINE       ol5-112-rac2
ora.gsd
               OFFLINE OFFLINE      ol5-112-rac1
               OFFLINE OFFLINE      ol5-112-rac2
ora.net1.network
               ONLINE  ONLINE       ol5-112-rac1
               ONLINE  ONLINE       ol5-112-rac2
ora.ons
               ONLINE  ONLINE       ol5-112-rac1
               ONLINE  ONLINE       ol5-112-rac2
——————————————————————————–
Cluster Resources
——————————————————————————–
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       ol5-112-rac1
ora.oc4j
      1        OFFLINE OFFLINE
ora.ol5-112-rac1.vip
      1        ONLINE  ONLINE       ol5-112-rac1
ora.ol5-112-rac2.vip
      1        ONLINE  ONLINE       ol5-112-rac2
ora.rac.db
      1        ONLINE  ONLINE       ol5-112-rac2             Open
      2        ONLINE  ONLINE       ol5-112-rac1
ora.rac.reports.svc
      1        ONLINE  ONLINE       ol5-112-rac1
ora.scan1.vip
      1        ONLINE  ONLINE       ol5-112-rac1
[oracle@ol5-112-rac1 ~]$
Shutdown RAC1 instance first:

[oracle@ol5-112-rac1 ~]$ srvctl stop instance –d RAC –I RAC1 –o immediate
Then proceed to shutdown cluster (root required):

[oracle@ol5-112-rac1 ~]$ su –
[root@ol5-112-rac1 bin]#
[root@ol5-112-rac1 bin]# ./crsctl stop cluster
CRS-2673: Attempting to stop ‘ora.crsd’ on ‘ol5-112-rac1’
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on ‘ol5-112-rac1’
CRS-2673: Attempting to stop ‘ora.LISTENER.lsnr’ on ‘ol5-112-rac1’
CRS-2673: Attempting to stop ‘ora.DATA.dg’ on ‘ol5-112-rac1’
CRS-2677: Stop of ‘ora.LISTENER.lsnr’ on ‘ol5-112-rac1’ succeeded
CRS-2673: Attempting to stop ‘ora.ol5-112-rac1.vip’ on ‘ol5-112-rac1’
CRS-2677: Stop of ‘ora.ol5-112-rac1.vip’ on ‘ol5-112-rac1’ succeeded
CRS-2672: Attempting to start ‘ora.ol5-112-rac1.vip’ on ‘ol5-112-rac2’
CRS-2676: Start of ‘ora.ol5-112-rac1.vip’ on ‘ol5-112-rac2’ succeeded
CRS-2677: Stop of ‘ora.DATA.dg’ on ‘ol5-112-rac1’ succeeded
CRS-2673: Attempting to stop ‘ora.asm’ on ‘ol5-112-rac1’
CRS-2677: Stop of ‘ora.asm’ on ‘ol5-112-rac1’ succeeded
CRS-2673: Attempting to stop ‘ora.ons’ on ‘ol5-112-rac1’
CRS-2673: Attempting to stop ‘ora.eons’ on ‘ol5-112-rac1’
CRS-2677: Stop of ‘ora.ons’ on ‘ol5-112-rac1’ succeeded
CRS-2673: Attempting to stop ‘ora.net1.network’ on ‘ol5-112-rac1’
CRS-2677: Stop of ‘ora.net1.network’ on ‘ol5-112-rac1’ succeeded
CRS-2677: Stop of ‘ora.eons’ on ‘ol5-112-rac1’ succeeded
CRS-2792: Shutdown of Cluster Ready Services-managed resources on ‘ol5-112-rac1’ has completed
CRS-2677: Stop of ‘ora.crsd’ on ‘ol5-112-rac1’ succeeded
CRS-2673: Attempting to stop ‘ora.cssdmonitor’ on ‘ol5-112-rac1’
CRS-2673: Attempting to stop ‘ora.ctssd’ on ‘ol5-112-rac1’
CRS-2673: Attempting to stop ‘ora.evmd’ on ‘ol5-112-rac1’
CRS-2673: Attempting to stop ‘ora.asm’ on ‘ol5-112-rac1’
CRS-2677: Stop of ‘ora.cssdmonitor’ on ‘ol5-112-rac1’ succeeded
CRS-2677: Stop of ‘ora.evmd’ on ‘ol5-112-rac1’ succeeded
CRS-2677: Stop of ‘ora.ctssd’ on ‘ol5-112-rac1’ succeeded
CRS-2677: Stop of ‘ora.asm’ on ‘ol5-112-rac1’ succeeded
CRS-2673: Attempting to stop ‘ora.cssd’ on ‘ol5-112-rac1’
CRS-2677: Stop of ‘ora.cssd’ on ‘ol5-112-rac1’ succeeded
CRS-2673: Attempting to stop ‘ora.diskmon’ on ‘ol5-112-rac1’
CRS-2677: Stop of ‘ora.diskmon’ on ‘ol5-112-rac1’ succeeded
[root@ol5-112-rac1 bin]# ps -ef|grep pmon
root      7072  5722  0 14:45 pts/2    00:00:00 grep pmon
[root@ol5-112-rac1 bin]# ps -ef|grep +ASM1
root      7074  5722  0 14:46 pts/2    00:00:00 grep +ASM1
[root@ol5-112-rac1 bin]#
At this point ASM & Cluster Ready Services managed resources are down.

In order to start them again just need to issue:

[root@ol5-112-rac1 bin]# ./crsctl start cluster
[root@ol5-112-rac1 bin]# exit
[oracle@ol5-112-rac1 ~]$ srvctl start instance –d RAC –I RAC1

Thanks,
Alfredo

Hands on practice ASM

Today I want to share with you an ASM exercise which includes how to add an additional disk to our VM RAC nodes, prepare the disk, add the disk to ASM configuration and create a new diskgroup using this disk.

The very first step is to have our VM machines turned off. Then go into your VM host server’s terminal and get into ASM shared disks location.

alfredo@alfredo-N56VM:~$ cd /u04

alfredo@alfredo-N56VM:/u04$ ls

VirtualBox

alfredo@alfredo-N56VM:/u04$ cd VirtualBox/

alfredo@alfredo-N56VM:/u04/VirtualBox$ ls

ol5-112-rac

alfredo@alfredo-N56VM:/u04/VirtualBox$ cd ol5-112-rac/

alfredo@alfredo-N56VM:/u04/VirtualBox/ol5-112-rac$ ls

asm1.vdi asm2.vdi asm3.vdi asm4.vdi


Then create the new VM shared disk and assign it to your VM machines.

alfredo@alfredo-N56VM:/u04/VirtualBox/ol5-112-rac$ VBoxManage createhd --filename asm5.vdi --size 1024 --format VDI --variant Fixed

0%...10%...20%...30%...40%...50%...60%...70%...80%...90%...100%

Disk image created. UUID: f317a4fb-d132-49fa-8572-76ca7a509e04

alfredo@alfredo-N56VM:/u04/VirtualBox/ol5-112-rac$ VBoxManage storageattach ol5-112-rac1 --storagectl "SATA" --port 5 --device 0 --type hdd --medium asm5.vdi --mtype shareable

alfredo@alfredo-N56VM:/u04/VirtualBox/ol5-112-rac$ VBoxManage modifyhd asm5.vdi --type shareable

alfredo@alfredo-N56VM:/u04/VirtualBox/ol5-112-rac$ VBoxManage storageattach ol5-112-rac2 --storagectl "SATA" --port 5 --device 0 --type hdd --medium asm5.vdi --mtype shareable

alfredo@alfredo-N56VM:/u04/VirtualBox/ol5-112-rac$ VBoxManage modifyhd asm5.vdi --type shareable


Now turn your on your node 1, open a terminal as root and format the disk.

[oracle@ol5-112-rac1 ~]$ su -
Password:
[root@ol5-112-rac1 ~]# cd /dev
[root@ol5-112-rac1 dev]# ls sd*
sda sda1 sda2 sdb sdb1 sdc sdc1 sdd sdd1 sde sde1 sdf sdf1 sdg sdg1 sdh
[root@ol5-112-rac1 dev]# fdisk /dev/sdh
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel. Changes will remain in memory only,
until you decide to write them. After that, of course, the previous
content won't be recoverable.

Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-130, default 1):
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-130, default 130):
Using default value 130

Command (m for help): p

Disk /dev/sdh: 1073 MB, 1073741824 bytes
255 heads, 63 sectors/track, 130 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System
/dev/sdh1 1 130 1044193+ 83 Linux

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.


Turn on the second VM node.
The next step is to add the newly partitioned disk to ASM.

[root@ol5-112-rac1 dev]# /usr/sbin/oracleasm listdisks
DISK1
DISK2
DISK3
DISK4
[root@ol5-112-rac1 dev]# /usr/sbin/oracleasm createdisk DISK5 /dev/sdh1
Writing disk header: done
Instantiating disk: done
[root@ol5-112-rac1 dev]# /usr/sbin/oracleasm listdisks
DISK1
DISK2
DISK3
DISK4
DISK5
[root@ol5-112-rac1 dev]# exit



At this point the new disk is already added to ASM configuration; let’s login to ASM instance and confirm that new disk is present.

[oracle@ol5-112-rac2 ~]$ . oraenv
ORACLE_SID = [RAC2] ? +ASM2
The Oracle base for ORACLE_HOME=/u01/app/11.2.0/grid is /u01/app/oracle
[oracle@ol5-112-rac2 ~]$ crsctl status resource -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
ONLINE ONLINE ol5-112-rac1
ONLINE ONLINE ol5-112-rac2
ora.LISTENER.lsnr
ONLINE ONLINE ol5-112-rac1
ONLINE ONLINE ol5-112-rac2
ora.asm
ONLINE ONLINE ol5-112-rac1
ONLINE ONLINE ol5-112-rac2 Started
ora.eons
ONLINE ONLINE ol5-112-rac1
ONLINE ONLINE ol5-112-rac2
ora.gsd
OFFLINE OFFLINE ol5-112-rac1
OFFLINE OFFLINE ol5-112-rac2
ora.net1.network
ONLINE ONLINE ol5-112-rac1
ONLINE ONLINE ol5-112-rac2
ora.ons
ONLINE ONLINE ol5-112-rac1
ONLINE ONLINE ol5-112-rac2
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE ol5-112-rac1
ora.oc4j
1 OFFLINE OFFLINE
ora.ol5-112-rac1.vip
1 ONLINE ONLINE ol5-112-rac1
ora.ol5-112-rac2.vip
1 ONLINE ONLINE ol5-112-rac2
ora.rac.db
1 ONLINE ONLINE ol5-112-rac2 Open
2 ONLINE ONLINE ol5-112-rac1
ora.rac.reports.svc
1 ONLINE ONLINE ol5-112-rac1
ora.scan1.vip
1 ONLINE ONLINE ol5-112-rac1
[oracle@ol5-112-rac2 ~]$ sqlplus / as sysasm


SQL*Plus: Release 11.2.0.1.0 Production on Sun Jun 9 16:06:27 2013

Copyright (c) 1982, 2009, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Real Application Clusters and Automatic Storage Management options

SQL> set lines 1000
SQL> column path format a50
SQL> SELECT PATH,NAME,GROUP_NUMBER,DISK_NUMBER,MOUNT_STATUS,HEADER_STATUS,MODE_STATUS,STATE,REDUNDANCY,OS_MB,TOTAL_MB,FREE_MB FROM V$ASM_DISK;

PATH NAME GROUP_NUMBER DISK_NUMBER MOUNT_S HEADER_STATU MODE_ST STATE REDUNDA OS_MB TOTAL_MB FREE_MB
-------------------------------------------------- ------------------------------ ------------ ----------- ------- ------------ ------- -------- ------- ---------- ---------- ----------
/dev/oracleasm/disks/DISK5 0 0 CLOSED PROVISIONED ONLINE NORMAL UNKNOWN 1019 0 0
/dev/oracleasm/disks/DISK4 DATA_0003 1 3 CACHED MEMBER ONLINE NORMAL UNKNOWN 5114 5114 4439
/dev/oracleasm/disks/DISK3 DATA_0002 1 2 CACHED MEMBER ONLINE NORMAL UNKNOWN 5114 5114 4439
/dev/oracleasm/disks/DISK2 DATA_0001 1 1 CACHED MEMBER ONLINE NORMAL UNKNOWN 5114 5114 4444
/dev/oracleasm/disks/DISK1 DATA_0000 1 0 CACHED MEMBER ONLINE NORMAL UNKNOWN 5114 5114 4406



You can see the DISK5 present in the ASM system view, the HEADER_STATUS is ‘PROVISIONED’. The PROVISIONED status implies that an additional platform-specific action has been taken by an administrator to make the disk available for ASM.

Now, let’s drop DISK4 from DATA diskgroup, this will free DISK4 for our new configuration.

SQL> select * from V$ASM_DISKGROUP;

GROUP_NUMBER NAME SECTOR_SIZE BLOCK_SIZE ALLOCATION_UNIT_SIZE STATE TYPE TOTAL_MB FREE_MB HOT_USED_MB COLD_USED_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB OFFLINE_DISKS COMPATIBILITY DATABASE_COMPATIBILITY V
------------ ------------------------------ ----------- ---------- -------------------- ----------- ------ ---------- ---------- ----------- ------------ ----------------------- -------------- ------------- ------------------------------------------------------------ ------------------------------------------------------------ -
1 DATA 512 4096 1048576 MOUNTED EXTERN 20456 17728 0 2728 0 17728 0 11.2.0.0.0 10.1.0.0.0 N



SQL> ALTER DISKGROUP DATA DROP DISK DATA_0003;

Diskgroup altered.

SQL> SELECT PATH,NAME,GROUP_NUMBER,DISK_NUMBER,MOUNT_STATUS,HEADER_STATUS,MODE_STATUS,STATE,REDUNDANCY,OS_MB,TOTAL_MB,FREE_MB FROM V$ASM_DISK;

PATH NAME GROUP_NUMBER DISK_NUMBER MOUNT_S HEADER_STATU MODE_ST STATE REDUNDA OS_MB TOTAL_MB FREE_MB
-------------------------------------------------- ------------------------------ ------------ ----------- ------- ------------ ------- -------- ------- ---------- ---------- ----------
/dev/oracleasm/disks/DISK5 0 0 CLOSED PROVISIONED ONLINE NORMAL UNKNOWN 1019 0 0
/dev/oracleasm/disks/DISK4 DATA_0003 1 3 CACHED MEMBER ONLINE DROPPING UNKNOWN 5114 5114 4546
/dev/oracleasm/disks/DISK3 DATA_0002 1 2 CACHED MEMBER ONLINE NORMAL UNKNOWN 5114 5114 4404
/dev/oracleasm/disks/DISK2 DATA_0001 1 1 CACHED MEMBER ONLINE NORMAL UNKNOWN 5114 5114 4409
/dev/oracleasm/disks/DISK1 DATA_0000 1 0 CACHED MEMBER ONLINE NORMAL UNKNOWN 5114 5114 4369


You can see the STATE of DISK4 is DROPPING, in this stage ASM will rebalance all the data from DISK4 into the disk of diskgroup DATA. In order to speed this operation we are going to modify the rebalance power of the diskgroup.

SQL> ALTER DISKGROUP DATA REBALANCE POWER 5;

Diskgroup altered.

SQL> SELECT PATH,NAME,GROUP_NUMBER,DISK_NUMBER,MOUNT_STATUS,HEADER_STATUS,MODE_STATUS,STATE,REDUNDANCY,OS_MB,TOTAL_MB,FREE_MB FROM V$ASM_DISK;


PATH NAME GROUP_NUMBER DISK_NUMBER MOUNT_S HEADER_STATU MODE_ST STATE REDUNDA OS_MB TOTAL_MB FREE_MB
-------------------------------------------------- ------------------------------ ------------ ----------- ------- ------------ ------- -------- ------- ---------- ---------- ----------
/dev/oracleasm/disks/DISK5 0 0 CLOSED PROVISIONED ONLINE NORMAL UNKNOWN 1019 0 0
/dev/oracleasm/disks/DISK4 0 1 CLOSED FORMER ONLINE NORMAL UNKNOWN 5114 0 0
/dev/oracleasm/disks/DISK3 DATA_0002 1 2 CACHED MEMBER ONLINE NORMAL UNKNOWN 5114 5114 4213
/dev/oracleasm/disks/DISK2 DATA_0001 1 1 CACHED MEMBER ONLINE NORMAL UNKNOWN 5114 5114 4218
/dev/oracleasm/disks/DISK1 DATA_0000 1 0 CACHED MEMBER ONLINE NORMAL UNKNOWN 5114 5114 4185

SQL> show parameter power

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
asm_power_limit integer 1
SQL> ALTER DISKGROUP DATA REBALANCE POWER 1;

Diskgroup altered.


After modifying the rebalance power to 5 ASM will provide more resources to the rebalance process, this results in faster rebalance times. You can see that DISK4’s header status is now ‘FORMER’ this means a disk was once part of a disk group but has been dropped cleanly from the group. It may be added to a new disk group with the ALTER DISKGROUP statement.

Be sure to modify back the power of the disk group DATA to 1.

Now let’s create a new disk group named FRA using DISK4 & DISK5.

SQL> CREATE DISKGROUP FRA EXTERNAL REDUNDANCY DISK '/dev/oracleasm/disks/DISK4' NAME FRA_0000, '/dev/oracleasm/disks/DISK5' NAME FRA_0001;

Diskgroup created.

SQL> SELECT PATH,NAME,GROUP_NUMBER,DISK_NUMBER,MOUNT_STATUS,HEADER_STATUS,MODE_STATUS,STATE,REDUNDANCY,OS_MB,TOTAL_MB,FREE_MB FROM V$ASM_DISK;

PATH NAME GROUP_NUMBER DISK_NUMBER MOUNT_S HEADER_STATU MODE_ST STATE REDUNDA OS_MB TOTAL_MB FREE_MB
-------------------------------------------------- ------------------------------ ------------ ----------- ------- ------------ ------- -------- ------- ---------- ---------- ----------
/dev/oracleasm/disks/DISK4 FRA_0000 2 0 CACHED MEMBER ONLINE NORMAL UNKNOWN 5114 5114 5072
/dev/oracleasm/disks/DISK3 DATA_0002 1 2 CACHED MEMBER ONLINE NORMAL UNKNOWN 5114 5114 4215
/dev/oracleasm/disks/DISK2 DATA_0001 1 1 CACHED MEMBER ONLINE NORMAL UNKNOWN 5114 5114 4218
/dev/oracleasm/disks/DISK1 DATA_0000 1 0 CACHED MEMBER ONLINE NORMAL UNKNOWN 5114 5114 4183
/dev/oracleasm/disks/DISK5 FRA_0001 2 1 CACHED MEMBER ONLINE NORMAL UNKNOWN 1019 1019 1009

SQL> select * from V$ASM_DISKGROUP;

GROUP_NUMBER NAME SECTOR_SIZE BLOCK_SIZE ALLOCATION_UNIT_SIZE STATE TYPE TOTAL_MB FREE_MB HOT_USED_MB COLD_USED_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB OFFLINE_DISKS COMPATIBILITY DATABASE_COMPATIBILITY V
------------ ------------------------------ ----------- ---------- -------------------- ----------- ------ ---------- ---------- ----------- ------------ ----------------------- -------------- ------------- ------------------------------------------------------------ ------------------------------------------------------------ -
1 DATA 512 4096 1048576 MOUNTED EXTERN 15342 12616 0 2726 0 12616 0 11.2.0.0.0 10.1.0.0.0 N
2 FRA 512 4096 1048576 MOUNTED EXTERN 6133 6081 0 52 0 6081 0 10.1.0.0.0 10.1.0.0.0 N

SQL>

ALTER DISKGROUP FRA SET ATTRIBUTE 'compatible.asm'='11.2.0.0.0';

SQL> ALTER DISKGROUP FRA SET ATTRIBUTE 'compatible.asm'='11.2.0.0.0';

Diskgroup altered.

SQL> select * from V$ASM_DISKGROUP;

GROUP_NUMBER NAME SECTOR_SIZE BLOCK_SIZE ALLOCATION_UNIT_SIZE STATE TYPE TOTAL_MB FREE_MB HOT_USED_MB COLD_USED_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB OFFLINE_DISKS COMPATIBILITY DATABASE_COMPATIBILITY V
------------ ------------------------------ ----------- ---------- -------------------- ----------- ------ ---------- ---------- ----------- ------------ ----------------------- -------------- ------------- ------------------------------------------------------------ ------------------------------------------------------------ -
1 DATA 512 4096 1048576 MOUNTED EXTERN 15342 12616 0 2726 0 12616 0 11.2.0.0.0 10.1.0.0.0 N
2 FRA 512 4096 1048576 MOUNTED EXTERN 6133 6079 0 54 0 6079 0 11.2.0.0.0 10.1.0.0.0 N

SQL>



FRA disk group is ready to host our flash recovery area for archivelogs and backups.

This is a small but useful example of the ASM functionality.

Thanks,
Alfredo





GoldenGate 11G Install on Linux x86

Today I will show you how to install GoldenGate under Linux 5.5 following the recipe of my teammate Gleb.
Prerequisites:
·          Oracle Linux 5.5 installed in 2 VM using VirtualBox, I have used one pre-built VM from Oracle (http://www.oracle.com/technetwork/database/enterprise-edition/databaseappdev-vm-161299.html)
·          Download Oracle GoldenGate software from (http://www.oracle.com/technetwork/middleware/goldengate/downloads/index.html)
·          Start both VM machines and change the host name to source_host & target_host respectively
·          Test ping connectivity from source_host to target_host
Installation
·          Create /01 directory and change the owner to oracle:dba on source_host:
[oracle@source_host Desktop]$ su –
Password:
[root@source_host ~]# cd /
[root@source_host /]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/hda1              11G  2.7G  6.9G  28% /
/dev/hdb1              12G   11G  683M  94% /home
tmpfs                1014M  264M  750M  27% /dev/shm
[root@source_host /]# mkdir /u01
[root@source_host /]# chown oracle:dba /u01
[root@source_host /]# exit
Logout
·          Copy GoldenGate binaries to /u01 and unzip the file, then create goldengate directory under /u01 and untar the file:
[oracle@source_host Desktop]$ mv ogg112101_fbo_ggs_Linux_x86_ora11g_32bit.zip /u01/
[oracle@source_host Desktop]$ cd /u01
[oracle@source_host u01]$ ls
ogg112101_fbo_ggs_Linux_x86_ora11g_32bit.zip
[oracle@source_host u01]$ unzip ogg112101_fbo_ggs_Linux_x86_ora11g_32bit.zip
Archive:  ogg112101_fbo_ggs_Linux_x86_ora11g_32bit.zip
  inflating: fbo_ggs_Linux_x86_ora11g_32bit.tar 
  inflating: OGG_WinUnix_Rel_Notes_11.2.1.0.1.pdf 
  inflating: Oracle GoldenGate 11.2.1.0.1 README.txt 
  inflating: Oracle GoldenGate 11.2.1.0.1 README.doc 
[oracle@source_host u01]$ ls
fbo_ggs_Linux_x86_ora11g_32bit.tar
ogg112101_fbo_ggs_Linux_x86_ora11g_32bit.zip
OGG_WinUnix_Rel_Notes_11.2.1.0.1.pdf
Oracle GoldenGate 11.2.1.0.1 README.doc
Oracle GoldenGate 11.2.1.0.1 README.txt
[oracle@source_host u01]$ mkdir goldengate
[oracle@source_host u01]$ mv fbo_ggs_Linux_x86_ora11g_32bit.tar goldengate/
[oracle@source_host u01]$ cd goldengate/
[oracle@source_host goldengate]$ ls
fbo_ggs_Linux_x86_ora11g_32bit.tar
[oracle@source_host goldengate]$ tar -xvf fbo_ggs_Linux_x86_ora11g_32bit.tar
UserExitExamples/
UserExitExamples/ExitDemo_passthru/
UserExitExamples/ExitDemo_passthru/Makefile_passthru.AIX
UserExitExamples/ExitDemo_passthru/exitdemo_passthru.c
UserExitExamples/ExitDemo_passthru/Makefile_passthru.LINUX
UserExitExamples/ExitDemo_passthru/Makefile_passthru.HP_OSS
UserExitExamples/ExitDemo_passthru/exitdemopassthru.vcproj
UserExitExamples/ExitDemo_passthru/readme.txt
UserExitExamples/ExitDemo_passthru/Makefile_passthru.SOLARIS
UserExitExamples/ExitDemo_passthru/Makefile_passthru.HPUX
UserExitExamples/ExitDemo_lobs/
UserExitExamples/ExitDemo_lobs/exitdemo_lob.vcproj
UserExitExamples/ExitDemo_lobs/Makefile_lob.HPUX
UserExitExamples/ExitDemo_lobs/exitdemo_lob.c
UserExitExamples/ExitDemo_lobs/Makefile_lob.AIX
UserExitExamples/ExitDemo_lobs/Makefile_lob.SOLARIS
UserExitExamples/ExitDemo_lobs/Makefile_lob.LINUX
UserExitExamples/ExitDemo_lobs/readme.txt
UserExitExamples/ExitDemo/
UserExitExamples/ExitDemo/exitdemo.vcproj
UserExitExamples/ExitDemo/Makefile_exit_demo.SOLARIS
UserExitExamples/ExitDemo/exitdemo_utf16.c
UserExitExamples/ExitDemo/readme.txt
UserExitExamples/ExitDemo/exitdemo.c
UserExitExamples/ExitDemo/Makefile_exit_demo.HPUX
UserExitExamples/ExitDemo/Makefile_exit_demo.AIX
UserExitExamples/ExitDemo/Makefile_exit_demo.LINUX
UserExitExamples/ExitDemo/Makefile_exit_demo.HP_OSS
UserExitExamples/ExitDemo_pk_befores/
UserExitExamples/ExitDemo_pk_befores/exitdemo_pk_befores.c
UserExitExamples/ExitDemo_pk_befores/Makefile_pk_befores.SOLARIS
UserExitExamples/ExitDemo_pk_befores/exitdemo_pk_befores.vcproj
UserExitExamples/ExitDemo_pk_befores/readme.txt
UserExitExamples/ExitDemo_pk_befores/Makefile_pk_befores.AIX
UserExitExamples/ExitDemo_pk_befores/Makefile_pk_befores.LINUX
UserExitExamples/ExitDemo_pk_befores/Makefile_pk_befores.HPUX
UserExitExamples/ExitDemo_more_recs/
UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.HPUX
UserExitExamples/ExitDemo_more_recs/exitdemo_more_recs.vcproj
UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.AIX
UserExitExamples/ExitDemo_more_recs/readme.txt
UserExitExamples/ExitDemo_more_recs/exitdemo_more_recs.c
UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.LINUX
UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.SOLARIS
bcpfmt.tpl
bcrypt.txt
cfg/
cfg/ProfileConfig.xml
cfg/jps-config-jse.xml
cfg/password.properties
cfg/MPMetadataSchema.xsd
cfg/mpmetadata.xml
cfg/Config.properties
chkpt_ora_create.sql
cobgen
convchk
db2cntl.tpl
ddl_cleartrace.sql
ddl_ddl2file.sql
ddl_disable.sql
ddl_enable.sql
ddl_filter.sql
ddl_nopurgeRecyclebin.sql
ddl_ora10.sql
ddl_ora10upCommon.sql
ddl_ora11.sql
ddl_ora9.sql
ddl_pin.sql
ddl_purgeRecyclebin.sql
ddl_remove.sql
ddl_session.sql
ddl_session1.sql
ddl_setup.sql
ddl_status.sql
ddl_staymetadata_off.sql
ddl_staymetadata_on.sql
ddl_trace_off.sql
ddl_trace_on.sql
ddl_tracelevel.sql
ddlcob
defgen
demo_more_ora_create.sql
demo_more_ora_insert.sql
demo_ora_create.sql
demo_ora_insert.sql
demo_ora_lob_create.sql
demo_ora_misc.sql
demo_ora_pk_befores_create.sql
demo_ora_pk_befores_insert.sql
demo_ora_pk_befores_updates.sql
dirjar/
dirjar/xmlparserv2.jar
dirjar/spring-security-cas-client-3.0.1.RELEASE.jar
dirjar/spring-security-taglibs-3.0.1.RELEASE.jar
dirjar/org.springframework.transaction-3.0.0.RELEASE.jar
dirjar/identityutils.jar
dirjar/xpp3_min-1.1.4c.jar
dirjar/jps-api.jar
dirjar/jacc-spi.jar
dirjar/org.springframework.jdbc-3.0.0.RELEASE.jar
dirjar/org.springframework.aspects-3.0.0.RELEASE.jar
dirjar/identitystore.jar
dirjar/jps-mbeans.jar
dirjar/fmw_audit.jar
dirjar/commons-codec-1.3.jar
dirjar/jmxremote_optional-1.0-b02.jar
dirjar/spring-security-core-3.0.1.RELEASE.jar
dirjar/log4j-1.2.15.jar
dirjar/commons-logging-1.0.4.jar
dirjar/org.springframework.context-3.0.0.RELEASE.jar
dirjar/org.springframework.asm-3.0.0.RELEASE.jar
dirjar/org.springframework.expression-3.0.0.RELEASE.jar
dirjar/org.springframework.instrument-3.0.0.RELEASE.jar
dirjar/jps-wls.jar
dirjar/jps-upgrade.jar
dirjar/jdmkrt-1.0-b02.jar
dirjar/jps-ee.jar
dirjar/jps-common.jar
dirjar/org.springframework.beans-3.0.0.RELEASE.jar
dirjar/jps-manifest.jar
dirjar/oraclepki.jar
dirjar/spring-security-acl-3.0.1.RELEASE.jar
dirjar/jagent.jar
dirjar/osdt_xmlsec.jar
dirjar/jps-patching.jar
dirjar/org.springframework.context.support-3.0.0.RELEASE.jar
dirjar/jps-unsupported-api.jar
dirjar/monitor-common.jar
dirjar/osdt_cert.jar
dirjar/spring-security-web-3.0.1.RELEASE.jar
dirjar/spring-security-config-3.0.1.RELEASE.jar
dirjar/org.springframework.aop-3.0.0.RELEASE.jar
dirjar/xstream-1.3.jar
dirjar/jps-internal.jar
dirjar/osdt_core.jar
dirjar/org.springframework.core-3.0.0.RELEASE.jar
dirjar/slf4j-api-1.4.3.jar
dirjar/ldapjclnt11.jar
dirjar/org.springframework.test-3.0.0.RELEASE.jar
dirjar/org.springframework.orm-3.0.0.RELEASE.jar
dirjar/org.springframework.web-3.0.0.RELEASE.jar
dirjar/slf4j-log4j12-1.4.3.jar
dirjar/jsr250-api-1.0.jar
dirprm/
dirprm/jagent.prm
emsclnt
extract
freeBSD.txt
ggMessage.dat
ggcmd
ggsci
help.txt
jagent.sh
keygen
libantlr3c.so
libdb-5.2.so
libgglog.so
libggrepo.so
libicudata.so.38
libicui18n.so.38
libicuuc.so.38
libxerces-c.so.28
libxml2.txt
logdump
marker_remove.sql
marker_setup.sql
marker_status.sql
mgr
notices.txt
oggerr
params.sql
prvtclkm.plb
pw_agent_util.sh
remove_seq.sql
replicat
retrace
reverse
role_setup.sql
sequence.sql
server
sqlldr.tpl
tcperrs
ucharset.h
ulg.sql
usrdecs.h
zlib.txt
[oracle@source_host goldengate]$
·          Start GG command line utility and create necessary working directories for GoldenGate:
[oracle@source_host goldengate]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x86, 32bit (optimized), Oracle 11g on Apr 23 2012 08:09:25
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (source_host.localdomain) 1> create subdirs
Creating subdirectories under current directory /u01/goldengate
Parameter files                /u01/goldengate/dirprm: already exists
Report files                   /u01/goldengate/dirrpt: created
Checkpoint files               /u01/goldengate/dirchk: created
Process status files           /u01/goldengate/dirpcs: created
SQL script files               /u01/goldengate/dirsql: created
Database definitions files     /u01/goldengate/dirdef: created
Extract data files             /u01/goldengate/dirdat: created
Temporary files                /u01/goldengate/dirtmp: created
Stdout files                   /u01/goldengate/dirout: created
GGSCI (source_host.localdomain) 2>
GGSCI (source_host.localdomain) 2> exit
[oracle@source_host goldengate]$ mkdir discard
[oracle@source_host goldengate]$
·          Connect to Source DB and run required configuration and scripts:
[oracle@source_host goldengate]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Sat Jun 8 11:55:56 2013
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> alter database add supplemental log data;
Database altered.
SQL> alter system set recyclebin=off scope=spfile;
System altered.
SQL> create user ggate identified by ggate default tablespace users temporary tablespace temp;
User created.
SQL> grant connect,resource to ggate;
Grant succeeded.
SQL> grant execute on utl_file to ggate;
Grant succeeded.
SQL>
SQL> @marker_setup.sql
Marker setup script
You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.
Enter Oracle GoldenGate schema name:ggate
Marker setup table script complete, running verification script…
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to GGATE
MARKER TABLE
——————————-
OK
MARKER SEQUENCE
——————————-
OK
Script complete.
SQL>
SQL> @ddl_setup.sql
Oracle GoldenGate DDL Replication setup script
Verifying that current user has privileges to install DDL Replication…
You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: For an Oracle 10g source, the system recycle bin must be disabled. For Oracle 11g and later, it can be enabled.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.
Enter Oracle GoldenGate schema name:ggate
Working, please wait …
Spooling to file ddl_setup_spool.txt
Checking for sessions that are holding locks on Oracle Golden Gate metadata tables …
Check complete.
Using GGATE as a Oracle GoldenGate schema name.
Working, please wait …
DDL replication setup script complete, running verification script…
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to GGATE
CLEAR_TRACE STATUS:
Line/pos                                 Error
—————————————- —————————————————————–
No errors                                No errors
CREATE_TRACE STATUS:
Line/pos                                 Error
—————————————- —————————————————————–
No errors                                No errors
TRACE_PUT_LINE STATUS:
Line/pos                                 Error
—————————————- —————————————————————–
No errors                                No errors
INITIAL_SETUP STATUS:
Line/pos                                 Error
—————————————- —————————————————————–
No errors                                No errors
DDLVERSIONSPECIFIC PACKAGE STATUS:
Line/pos                                 Error
—————————————- —————————————————————–
No errors                                No errors
DDLREPLICATION PACKAGE STATUS:
Line/pos                                 Error
—————————————- —————————————————————–
No errors                                No errors
DDLREPLICATION PACKAGE BODY STATUS:
Line/pos                                 Error
—————————————- —————————————————————–
No errors                                No errors
DDL IGNORE TABLE
———————————–
OK
DDL IGNORE LOG TABLE
———————————–
OK
DDLAUX  PACKAGE STATUS:
Line/pos                                 Error
—————————————- —————————————————————–
No errors                                No errors
DDLAUX PACKAGE BODY STATUS:
Line/pos                                 Error
—————————————- —————————————————————–
No errors                                No errors
SYS.DDLCTXINFO  PACKAGE STATUS:
Line/pos                                 Error
—————————————- —————————————————————–
No errors                                No errors
SYS.DDLCTXINFO  PACKAGE BODY STATUS:
Line/pos                                 Error
—————————————- —————————————————————–
No errors                                No errors
DDL HISTORY TABLE
———————————–
OK
DDL HISTORY TABLE(1)
———————————–
OK
DDL DUMP TABLES
———————————–
OK
DDL DUMP COLUMNS
———————————–
OK
DDL DUMP LOG GROUPS
———————————–
OK
DDL DUMP PARTITIONS
———————————–
OK
DDL DUMP PRIMARY KEYS
———————————–
OK
DDL SEQUENCE
———————————–
OK
GGS_TEMP_COLS
———————————–
OK
GGS_TEMP_UK
———————————–
OK
DDL TRIGGER CODE STATUS:
Line/pos                                 Error
—————————————- —————————————————————–
No errors                                No errors
DDL TRIGGER INSTALL STATUS
———————————–
OK
DDL TRIGGER RUNNING STATUS
————————————————————————————————————————
ENABLED
STAYMETADATA IN TRIGGER
————————————————————————————————————————
OFF
DDL TRIGGER SQL TRACING
————————————————————————————————————————
0
DDL TRIGGER TRACE LEVEL
————————————————————————————————————————
0
LOCATION OF DDL TRACE FILE
————————————————————————————————————————
/home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/ggs_ddl_trace.log
Analyzing installation status…
STATUS OF DDL REPLICATION
————————————————————————————————————————
SUCCESSFUL installation of DDL Replication software components
Script complete.
SQL>
SQL> @role_setup.sql
GGS Role setup script
This script will drop and recreate the role GGS_GGSUSER_ROLE
To use a different role name, quit this script and then edit the params.sql script to change the gg_role parameter to the preferred name. (Do not run the script.)
You will be prompted for the name of a schema for the GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.
Enter GoldenGate schema name:ggate
Wrote file role_setup_set.txt
PL/SQL procedure successfully completed.
Role setup script complete
Grant this role to each user assigned to the Extract, GGSCI, and Manager processes, by using the following SQL command:
GRANT GGS_GGSUSER_ROLE TO
where is the user assigned to the GoldenGate processes.
SQL>
SQL> grant GGS_GGSUSER_ROLE to ggate;
Grant succeeded.
SQL> @ddl_enable.sql
Trigger altered.
SQL>
SQL> create user sender identified by qwerty default tablespace users temporary tablespace temp;
User created.
SQL> grant connect,resource,unlimited tablespace to sender;
Grant succeeded.
SQL>
·          The installation is done in the Source host, now do the same on the Target host
·          Connect to the Target DB and create a receiver user which will be synced from Source DB
SQL> create user receiver identified by qwerty default tablespace users temporary tablespace temp;
User created.
SQL> grant connect,resource,unlimited tablespace to receiver;
Grant succeeded.
SQL>
·          In the Source host configure the MANAGER and the Extract processes
[oracle@source_host goldengate]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x86, 32bit (optimized), Oracle 11g on Apr 23 2012 08:09:25
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (source_host.localdomain) 1> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     STOPPED                                          
GGSCI (source_host.localdomain) 2> edit params mgr
–Add this line–
PORT 7809
GGSCI (source_host.localdomain) 3> start manager
Manager started.
GGSCI (source_host.localdomain) 7> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING                                          
GGSCI (source_host.localdomain) 8> add extract ext1, tranlog, begin now
EXTRACT added.
GGSCI (source_host.localdomain) 9> add exttrail /u01/goldengate/dirdat/lt, extract ext1
EXTTRAIL added.
GGSCI (source_host.localdomain) 10> edit params ext1
–Add these lines–
–extract group–
extract ext1
–connection to database–
userid ggate, password ggate
–hostname and port for trail–
rmthost target_host.localdomain, mgrport 7809
–path and name for trail–
rmttrail /u01/goldengate/dirdat/lt
–DDL support
ddl include mapped objname sender.*;
–DML
table sender.*;
GGSCI (source_host.localdomain) 12>
·          Now configure the MANAGER and Replicat processes in Target host
[oracle@target_host goldengate]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x86, 32bit (optimized), Oracle 11g on Apr 23 2012 08:09:25
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (target_host.localdomain) 1> edit params ./GLOBAL
GGSCI (target_host.localdomain) 4> dblogin userid ggate
Password:
Successfully logged into database.
GGSCI (target_host.localdomain) 5> add checkpointtable ggate.checkpoint
Successfully created checkpoint table ggate.checkpoint.
GGSCI (target_host.localdomain) 6> add replicat rep1, exttrail /u01/goldengate/dirdat/lt,checkpointtable ggate.checkpoint
REPLICAT added.
GGSCI (target_host.localdomain) 7> edit params rep1
–Add these lines–
–Replicat group —
replicat rep1
–source and target definitions
ASSUMiETARGETDEFS
–target database login —
userid ggate, password ggate
–file for dicarded transaction —
discardfile /u01/goldengate/discard/rep1_discard.txt, append, megabytes 10
–ddl support
DDL
–Specify table mapping —
map sender.*, target receiver.*;
GGSCI (target_host.localdomain) 11> edit params mgr
–Add this line–
PORT 7809
GGSCI (target_host.localdomain) 12> start manager
Manager started.
GGSCI (target_host.localdomain) 13> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING                                          
REPLICAT    STOPPED     REP1        00:00:00      00:03:31   
·          Now on the Source host start the Extract process
GGSCI (source_host.localdomain) 3> start extract ext1
Sending START request to MANAGER …
EXTRACT EXT1 starting
GGSCI (source_host.localdomain) 4> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING                                          
EXTRACT     RUNNING     EXT1        00:00:00      00:22:34   
GGSCI (source_host.localdomain) 5>
·          Now on the Target host start the Replicat process
GGSCI (target_host.localdomain) 14> start replicat rep1
Sending START request to MANAGER …
REPLICAT REP1 starting
GGSCI (target_host.localdomain) 16> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING                                          
REPLICAT    RUNNING     REP1        00:00:00      00:00:05  
·          At this point GoldenGate is installed and configured in both Source & Target hosts, Extract/Replicat processes are running, now the funny part begins. Let’s test our replication environment!
·          In the Source DB run the below command:

SQL> create table sender.test_tab_1 (id number,rnd_str varchar2(12));
SQL> insert into sender.test_tab_1 values (1,’test_1′);
SQL> commit;

·                     ·          Now check the Target DB:

SQL> select * from receiver.test_tab_1;
ID         RND_STR
———- ————
1          test_1

Our GoldenGate DDL and DML replication is now working. The table was created on the Target DB and data were replicated.

Thanks,
Alfredo