Friday, December 21, 2012

Exadata Backup on ZFS.

Links to this post
Below is Test of Exadata x2 half rack Database backup on ZFS.


Size : 2 TB
Used Size : 1.6 TB
Time Taken: 1hr 34 Mins.
Storage: 4 share of 300TB ZFS.
Channels Allocated: 5 channels per node per share. Totally 20 channels across 4 nodes and 4 shares.


#!/bin/ksh
export ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/db_1
export ORACLE_SID=DUMMYDB1
export PATH=$ORACLE_HOME/bin:$PATH
export TNS_ADMIN=/u01/app/oracle/product/11.2.0.3/db_1/network/admin
export ALTER SESSION NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI'
export LOGFILE=/home/oracle/DUMMYDB_rman_zfs_level0_ver3.log

#setup oracle environment based on SID
ORAENV_ASK=NO
. oraenv

rman target / catalog rman/xxxx@catalogdb <> ${LOGFILE}
run {
sql 'alter system set "_backup_disk_bufcnt"=64 scope=memory';
sql 'alter system set "_backup_disk_bufsz"=1048576 scope=memory';
allocate channel c01 DEVICE TYPE DISK FORMAT '/export/share8/DUMMYDB/DUMMYDBtest301_%U' CONNECT 'sys/XXXX@DUMMYDB1.WORLD';
allocate channel c02 DEVICE TYPE DISK FORMAT '/export/share8/DUMMYDB/DUMMYDBtest302_%U' CONNECT 'sys/XXXX@DUMMYDB1.WORLD';
allocate channel c03 DEVICE TYPE DISK FORMAT '/export/share8/DUMMYDB/DUMMYDBtest303_%U' CONNECT 'sys/XXXX@DUMMYDB1.WORLD';
allocate channel c04 DEVICE TYPE DISK FORMAT '/export/share8/DUMMYDB/DUMMYDBtest304_%U' CONNECT 'sys/XXXX@DUMMYDB1.WORLD';
allocate channel c05 DEVICE TYPE DISK FORMAT '/export/share8/DUMMYDB/DUMMYDBtest305_%U' CONNECT 'sys/XXXX@DUMMYDB1.WORLD';
allocate channel c06 DEVICE TYPE DISK FORMAT '/export/share7/DUMMYDB/DUMMYDBtest306_%U' CONNECT 'sys/XXXX@DUMMYDB3.WORLD';
allocate channel c07 DEVICE TYPE DISK FORMAT '/export/share7/DUMMYDB/DUMMYDBtest307_%U' CONNECT 'sys/XXXX@DUMMYDB3.WORLD';
allocate channel c08 DEVICE TYPE DISK FORMAT '/export/share7/DUMMYDB/DUMMYDBtest308_%U' CONNECT 'sys/XXXX@DUMMYDB3.WORLD';
allocate channel c09 DEVICE TYPE DISK FORMAT '/export/share7/DUMMYDB/DUMMYDBtest309_%U' CONNECT 'sys/XXXX@DUMMYDB3.WORLD';
allocate channel c10 DEVICE TYPE DISK FORMAT '/export/share7/DUMMYDB/DUMMYDBtest310_%U' CONNECT 'sys/XXXX@DUMMYDB3.WORLD';
allocate channel c11 DEVICE TYPE DISK FORMAT '/export/share6/DUMMYDB/DUMMYDBtest311_%U' CONNECT 'sys/XXXX@DUMMYDB2.WORLD';
allocate channel c12 DEVICE TYPE DISK FORMAT '/export/share6/DUMMYDB/DUMMYDBtest312_%U' CONNECT 'sys/XXXX@DUMMYDB2.WORLD';
allocate channel c13 DEVICE TYPE DISK FORMAT '/export/share6/DUMMYDB/DUMMYDBtest313_%U' CONNECT 'sys/XXXX@DUMMYDB2.WORLD';
allocate channel c14 DEVICE TYPE DISK FORMAT '/export/share6/DUMMYDB/DUMMYDBtest314_%U' CONNECT 'sys/XXXX@DUMMYDB2.WORLD';
allocate channel c15 DEVICE TYPE DISK FORMAT '/export/share6/DUMMYDB/DUMMYDBtest315_%U' CONNECT 'sys/XXXX@DUMMYDB2.WORLD';
allocate channel c16 DEVICE TYPE DISK FORMAT '/export/share5/DUMMYDB/DUMMYDBtest316_%U' CONNECT 'sys/XXXX@DUMMYDB4.WORLD';
allocate channel c17 DEVICE TYPE DISK FORMAT '/export/share5/DUMMYDB/DUMMYDBtest317_%U' CONNECT 'sys/XXXX@DUMMYDB4.WORLD';
allocate channel c18 DEVICE TYPE DISK FORMAT '/export/share5/DUMMYDB/DUMMYDBtest318_%U' CONNECT 'sys/XXXX@DUMMYDB4.WORLD';
allocate channel c19 DEVICE TYPE DISK FORMAT '/export/share5/DUMMYDB/DUMMYDBtest319_%U' CONNECT 'sys/XXXX@DUMMYDB4.WORLD';
allocate channel c20 DEVICE TYPE DISK FORMAT '/export/share5/DUMMYDB/DUMMYDBtest320_%U' CONNECT 'sys/XXXX@DUMMYDB4.WORLD';
sql "alter system archive log current";
backup incremental level 0 database plus archivelog;
sql "alter database backup controlfile to trace";
release channel c01;
release channel c02;
release channel c03;
release channel c04;
release channel c05;
release channel c06;
release channel c07;
release channel c08;
release channel c09;
release channel c10;
release channel c11;
release channel c12;
release channel c13;
release channel c14;
release channel c15;
release channel c16;
release channel c17;
release channel c18;
release channel c19;
release channel c20;
}
exit
EOF

References:

http://www.oracle.com/technetwork/database/features/availability/maa-wp-dbm-zfs-backup-1593252.pdf

http://www.oracle.com/technetwork/database/features/availability/maa-tech-wp-sundbm-backup-11202-183503.pdf

Thursday, December 13, 2012

GoldenGate performance Tuning - 1

Links to this post
Problem : Replicat was doing Full table scan on Updating 804000 rows.

Source. = 11.2.0.3 /TARGG

Below is update statement which was issued to update 804000 rows.
This completed in 15 seconds on SOURCE side DB.

sql_id :ghsutzgq0m8j9 

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
SQL_ID ghsutzgq0m8j9
--------------------
UPDATE dummy11.OS_HISTORYSTEP_BLD SET ENTRY_ID=5555, STEP_ID=100000,
ACTION_ID=20000, OWNER='JIGNESHKANKRECHA',
START_DATE=TO_TIMESTAMP('5/28/2012 9:19:11.910000
PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'),
FINISH_DATE=TO_TIMESTAMP('5/28/2012 9:19:11.910000
PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'),
DUE_DATE=TO_TIMESTAMP('5/28/2012 9:19:11.910000 PM','fmMMfm/fmDDfm/YYYY
fmHH12fm:MI:SS.FF AM'), STATUS='QC Validation failed', CALLER='KALIYA'
where ID  BETWEEN 1220000 AND 5999999

Plan hash value: 3578452229

-----------------------------------------------------------------------------------------
| Id  | Operation          | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |                    |       |       |   376 (100)|          |
|   1 |  UPDATE            | OS_HISTORYSTEP_BLD |       |       |            |          |
|   2 |   TABLE ACCESS FULL| OS_HISTORYSTEP_BLD |    47 |  4277 |   376   (1)| 00:00:05 |
-----------------------------------------------------------------------------------------


------------------------------------------------------------------
Target. = 10.2.0.4 / SOUGG

Below statment Replicat executed ,
Because i had BATCHSQL replicat made BATCH of few rows and send for update.
that is the reason we see COUNT STOPKEY in execution plan.

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
SQL_ID b4t7hv6p53165
--------------------
UPDATE "dummy11"."OS_HISTORYSTEP_BLD" SET "ENTRY_ID" = :a1,"STEP_ID" =
:a2,"ACTION_ID" = :a3,"OWNER" = :a4,"START_DATE" = :a5,"FINISH_DATE" =
:a6,"DUE_DATE" = :a7,"STATUS" = :a8,"CALLER" = :a9 WHERE "ID" = :b0 AND ROWNUM = 1

Plan hash value: 185372276

------------------------------------------------------------------------------------------
| Id  | Operation           | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT    |                    |       |       |   304 (100)|          |
|   1 |  UPDATE             | OS_HISTORYSTEP_BLD |       |       |            |          |
|   2 |   COUNT STOPKEY     |                    |       |       |            |          |
|   3 |    TABLE ACCESS FULL| OS_HISTORYSTEP_BLD |     1 |    91 |   304   (1)| 00:00:04 |
------------------------------------------------------------------------------------------

--Excerpt from Report file.

BATCHSQL BATCHESPERQUEUE 100, BATCHTRANSOPS 10000, OPSPERBATCH 10000, OPSPERQUEUE 100000

MAP resolved (entry dummy11.OS_HISTORYSTEP_BLD):
  MAP "dummy11"."OS_HISTORYSTEP_BLD", TARGET dummy11.OS_HISTORYSTEP_BLD, KEYCOLS(ID);

2012-12-12 12:00:30  WARNING OGG-00869  No unique key is defined for table 'OS_HISTORYSTEP_BLD'. All viable columns will be used to represent the key, but may not guarantee uniqueness.  KEYCOLS may be used to define the key.
Using following columns in default map by name:
  ID, ENTRY_ID, STEP_ID, ACTION_ID, OWNER, START_DATE, FINISH_DATE,
  DUE_DATE, STATUS, CALLER
Using the following key columns for target table dummy11.OS_HISTORYSTEP_BLD: ID.

--------some statistic of replicat. 
2012-12-12 12:18:22  INFO    OGG-01408  Restoring current schema for DDL operation to [GGADMIN].
              104578 records processed as of 2012-12-12 12:21:05 (rate 84,delta 236)
              457309 records processed as of 2012-12-12 13:35:07 (rate 80,delta 79)
              913119 records processed as of 2012-12-12 13:39:47 (rate 153,delta 1628)
              943118 records processed as of 2012-12-12 13:41:25 (rate 155,delta 305)
              953118 records processed as of 2012-12-12 13:42:43 (rate 155,delta 127)
              963118 records processed as of 2012-12-12 13:44:25 (rate 154,delta 98)
              973118 records processed as of 2012-12-12 13:46:29 (rate 153,delta 80)
              983118 records processed as of 2012-12-12 13:48:57 (rate 151,delta 67)
              993118 records processed as of 2012-12-12 13:51:47 (rate 148,delta 58)
             1003118 records processed as of 2012-12-12 13:54:59 (rate 146,delta 51)
             1013118 records processed as of 2012-12-12 13:58:35 (rate 143,delta 46)


SOLUTION:

1) kill session of replicat which was running update statement ,,This will abend replicat.
2) Add Index on ID column which was in KEYCOLS Of MAP statement.
3) start replicat .

And we are on Road again....

GGSCI (catlmsxt205) 4> INFO ALL

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     TAPSDR      00:00:00      03:32:40

GGSCI (catlmsxt205) 4> INFO ALL

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     TAPSDR      00:00:00      03:32:42

GGSCI (catlmsxt205) 6> INFO ALL
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
REPLICAT    RUNNING     TAPSDR      00:00:00      03:33:06

GGSCI (catlmsxt205) 7> INFO ALL

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     TAPSDR      00:00:00      00:00:03

Execution plan of Update after Index.

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  b4t7hv6p53165, child number 0
-------------------------------------
UPDATE "dummy11"."OS_HISTORYSTEP_BLD" SET "ENTRY_ID" = :a1,"STEP_ID" =
:a2,"ACTION_ID" = :a3,"OWNER" = :a4,"START_DATE" = :a5,"FINISH_DATE" =
:a6,"DUE_DATE" = :a7,"STATUS" = :a8,"CALLER" = :a9 WHERE "ID" = :b0 AND ROWNUM =
1

Plan hash value: 2252287618

-----------------------------------------------------------------------------------------
| Id  | Operation          | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |                    |       |       |     5 (100)|          |
|   1 |  UPDATE            | OS_HISTORYSTEP_BLD |       |       |            |          |
|*  2 |   COUNT STOPKEY    |                    |       |       |            |          |
|*  3 |    INDEX RANGE SCAN| OS_HIST_BLDIDX     |     1 |    91 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

   2 - filter(ROWNUM=1)
   3 - access("ID"=TO_NUMBER(:B0))