OLTP vs MPP vs Hadoop

Some of my friends asked me about OLTP, MPP and Hadoop. I tried to expain them as below.
This is related to the time writing this post. Things are changing so fast :).

OLTP Databases (Oracle,DB2) vs MPP (Netezza, Teradata, Vertica et.):

                1. - DB Oracle or DB2 needs to read data from disk to memory before start processing, so very fast in memory calculation.
                   - MPP takes the processing as close possible to the data, so less data movement
             
                2. - DB Oracle or DB2 is good for smaller OLTP (transaction) operations. It also maintains very high level of data intigrity.
                   - MPP is good for batch processing. Some of the MPP(Netezza, Vertica) overlooks intigrity like enforcing unique key for the sake of batch performance.

Hadoop(without impala or EMC HAWQ) vs MPP:

                1. - Conventional MPP database stores data in a matured internal structure. So data loading and data processing with SQL is efficient.
                   - There are no such structured architecture for data stored on hadoop. So, accessing and loading data is not as efficient as conventional MPP systems.
                2. - With conventional MPP, it support only relational models(row-column)
                   - hadoop support virtually any kind of data.
             
                * However the main objective of MPP and hadoop is same, process data parallely near storage.
             

Cloudera impala(or pivotal HAWQ) vs MPP:

                1. - MPP supports advanced indatabase analytics
                   - Till now (impala 2.0) started supporting "SQL 2003" which may lead them to intruduce indatabase analytics.
                2. - MPP databases have industry standard security features and well defined user schema structure.
                   - Impala has very immatured security system and virtually no user schema.
                3. - MPP only supports only vendor specific filesystem and need to load data using specific loading tool.
                   - impala supports most open file formats (text, parquate)
             
                * However impala seems to become a MPP & Columnar like Vertica but cheap & open database system in near future. Just need to implement security and advance indatabase analytics.

How to choice what (in general and my personal opinion):

1. OLTP Databases (Oracle,DB2, MySQL, MS SQL, Exadata):
                - Transaction based application
                - Smaller DWH
                * However Exadata is a hybrid system and I have experience to handle DWH with ~20TB data.

2. MPP (Netezza, Teradata, Vertica)
                - Bigger Data warehouse (may be having tables with size more than 4-5 TB)  
                - Needs no or little pre-processing
                - Needs faster batch processing speed
                - In database analytics

3. Only Hadoop:
                - All data as heavily unstructured (documents, audio, video etc)
                - need to process in batch

4. Hadoop and using mainly Impala (or EMC HAWQ)
                - Need to have a DWH with low cost
                - No need to have advance analytics features
                - Can utilize open source tools
                - Not concern about security or limited number of users
             
5. Hadoop(with impala or HAWQ) + MPP:
                - Some Data need heavy pre-processing before ready to advance analytics.
                - Need cheaper query able archive or backup for older data


Referencs:
http://www.quora.com/Is-Impala-aiming-to-be-an-open-source-alternative-to-existing-MPP-solutions
http://www.quora.com/What-features-of-a-relational-database-are-most-useful-when-building-a-data-warehouse
http://blog.pivotal.io/big-data-pivotal/products/exploratory-data-science-when-to-use-an-mpp-database-sql-on-hadoop-or-map-reduc

DBFS on Exadata, Normal Oracle Database and on Client

I prepared below action plan while installing DBFS on both exadata and on an external ETL machine to mount DBFS from exadata.


-- ---------------------------
-- serverside [if not exadata]
-- ---------------------------
1. Check to see if the "kernel-devel" package is installed.
#rpm -q kernel-devel
kernel-devel-2.6.18-128.el5
#If not, then install it

2. Determine the kernel directory.
# echo /usr/src/kernels/`uname -r`-`uname -p`
/usr/src/kernels/2.6.18-128.el5-x86_64

3. Install the FUSE package as the "root" user using the following commands, substituting your kernel 

directory.
--There may be WARNING regarding "--with-kernel",  just ignore it
--download fuse-2.7.3.tar.gz from "http://fuse.sourceforge.net/"
# tar -xzvf fuse-2.7.3.tar.gz
# cd fuse-2.7.3
# ./configure --prefix=/usr --with-kernel=/usr/src/kernels/2.6.18-128.el5-x86_64
# make
# make install
# /sbin/depmod
# /sbin/modprobe fuse
# chmod 666 /dev/fuse
# echo "/sbin/modprobe fuse" >> /etc/rc.modules
# chmod 744 /etc/rc.modules

4. install below rpms
fuse-libs-2.7.4-8.el5
fuse-devel-2.7.4-8.el5
fuse-2.7.4-8.el5

5. create DBFS tablespace
SQL> create bigfile tablespace tbs_dbfs datafile '+DATA2' size 200G autoextend off;

6. create DBFS user
SQL> create user dbfs_user identified by dbfs_passwd default tablespace tbs_dbfs quota unlimited on tbs_dbfs;
SQL> grant create session, create table, create procedure, dbfs_role to dbfs_user;

7. create the file system in tablespace 
cd $ORACLE_HOME/rdbms/admin
sqlplus dbfs_user/dbfs_passwd@DB

SQL> @dbfs_create_filesystem.sql tbs_dbfs my_dbfs  
--tbs_dbfs= DBFS TABLESPACE, my_dbfs=USER GIVEN NAME OF FILESYSTEM
[
-- for advanced setting use below command 
SQL> start dbfs_create_filesystem_advanced tbs_dbfs DBFS nocompress nodeduplicate noencrypt non-partition
]

[
--to drop a file system, this can be done using the "dbfs_drop_filesystem.sql" script with the file system 

name.
cd $ORACLE_HOME/rdbms/admin
sqlplus test/test

SQL> @dbfs_drop_filesystem.sql my_dbfs
--my_dbfs=USER GIVEN NAME OF FILESYSTEM which created in previous step
]

8. add oracle to fuse group
# groupadd fuse
# usermod -a -G fuse oracle

9. create dbfs mount point 
# mkdir /data01/DBFS_DIR
# chown oracle:oinstall /data01/DBFS_DIR

10. to let "dbfs os user(oracle)" provide access to other and root
# echo user_allow_other > /etc/fuse.conf -- to let "dbfs os user(oracle)" provide access to other and root 

11. using oracle user collect group id of oinstall group in the DB server 
$cat /etc/group|grep oinstall|cut -d: -f3 

12. using oracle user create a password file
$ echo dbfs_passwd > $HOME/passwd.txt
chmod 600 $HOME/passwd.txt #only oracle 

13. mount DBFS for only mounter
export ORACLE_HOME=;export TNS_ADMIN=;export 

LD_LIBRARY_PATH=$ORACLE_HOME/lib;$ORACLE_HOME/bin/dbfs_client dbfs_user@DEVDW  -o direct_io /data01/DBFS_DIR < 

$HOME/passwd.txt &
--or
-- for root also
export ORACLE_HOME=;export TNS_ADMIN=;export 

LD_LIBRARY_PATH=$ORACLE_HOME/lib;$ORACLE_HOME/bin/dbfs_client dbfs_user@DEVDW -o allow_root  -o direct_io 

/data01/DBFS_DIR < $HOME/passwd.txt &

14. unmount
fusermount -u /data01/DBFS_DIR

-- -------------------
-- IF DB is on Exadata 
-- -------------------

1. OS level COnfig
# usermod -a -G fuse oracle
# mkdir /u01/app/DBFS_DIR
# chown oracle:oinstall /u01/app/DBFS_DIR
# echo user_allow_other > /etc/fuse.conf

2. Create tablespace and user
create bigfile tablespace tbs_dbfs datafile '+DATA2' size 200G autoextend off;
create user dbfs_user identified by dbfs_passwd default tablespace tbs_dbfs quota unlimited on tbs_dbfs;
grant create session, create table, create procedure, dbfs_role to dbfs_user;
start dbfs_create_filesystem_advanced tbs_dbfs DBFS nocompress nodeduplicate noencrypt non-partition

3. create a file with password
$echo dbfs_passwd > $ORACLE_HOME/passwd.txt
#chown 700 /u01/app/oracle/product/11.2.0/dbhome_1/passwd.txt

4. Create mount script
export 

ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1;TNS_ADMIN=/u01/app/oracle/product/11.2.0/dbhome_1/network/

admin;export LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0/dbhome_1/lib;nohup 

/u01/app/oracle/product/11.2.0/dbhome_1/bin/dbfs_client dbfs_user@DEVDW -o allow_other,direct_io 

/u01/app/DBFS_DIR < /u01/app/oracle/product/11.2.0/dbhome_1/passwd.txt &
-- for root also
export 

ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1;TNS_ADMIN=/u01/app/oracle/product/11.2.0/dbhome_1/network/

admin;export LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0/dbhome_1/lib;
nohup /u01/app/oracle/product/11.2.0/dbhome_1/bin/dbfs_client dbfs_user@DEVDW -o allow_root,direct_io 

/u01/app/DBFS_DIR < /u01/app/oracle/product/11.2.0/dbhome_1/passwd.txt &

5. command for unlount
fusermount -u /u01/app/DBFS_DIR


-- -----------
-- client side
-- -----------


1. Check to see if the "kernel-devel" package is installed.
#rpm -q kernel-devel
kernel-devel-2.6.18-128.el5
#If not, then install it

2. Determine the kernel directory.
# echo /usr/src/kernels/`uname -r`-`uname -p`
/usr/src/kernels/2.6.18-128.el5-x86_64

3. Install the FUSE package as the "root" user using the following commands, substituting your kernel 

directory.
--There may be WARNING regarding "--with-kernel",  just ignore it
# tar -xzvf fuse-2.7.3.tar.gz
# cd fuse-2.7.3
# ./configure --prefix=/usr --with-kernel=/usr/src/kernels/2.6.18-128.el5-x86_64
# make
# make install
# /sbin/depmod
# /sbin/modprobe fuse
# chmod 666 /dev/fuse
# echo "/sbin/modprobe fuse" >> /etc/rc.modules
# chmod 744 /etc/rc.modules

4. install below rpms
fuse-libs-2.7.4-8.el5
fuse-devel-2.7.4-8.el5
fuse-2.7.4-8.el5

5. permit other user to execute dbfs_client
#chmod 755 <$ORACLE_HOME>/bin/dbfs_client

6. permit other user to read tnsnames.ora
#chmod 755 <$ORACLE_HOME>/network/admin/tnsnames.ora

7. create OS dbfsuser and group
#groupadd -g 101 fuse [with same ID as server]
#create user dbfs_user -g fuse

8. create DBFS mount point
#mkdir /data01/DBFS_DIR
#chown oracle:fuse /data01/DBFS_DIR
#chmod 775 /data01/DBFS_DIR

9. create password file for dbfs  
#echo dbfs_passwd > $HOME/passwd.txt
#chown oracle:fuse $HOME/passwd.txt
#chmod 640 $HOME/passwd.txt

10. mount DBFS for only mounter
export ORACLE_HOME=;export TNS_ADMIN=;export 

LD_LIBRARY_PATH=$ORACLE_HOME/lib;$ORACLE_HOME/bin/dbfs_client dbfs_user@DEVDW  -o direct_io /data01/DBFS_DIR < 

$HOME/passwd.txt &
--or
-- for root also
export ORACLE_HOME=;export TNS_ADMIN=;export 

LD_LIBRARY_PATH=$ORACLE_HOME/lib;$ORACLE_HOME/bin/dbfs_client dbfs_user@DEVDW -o allow_root  -o direct_io 

/data01/DBFS_DIR < $HOME/passwd.txt &

11. with DBFS mounted create directory fully accessible by both client user & server group
#mkdir /data01/DBFS_DIR//EXT_DIR/
#c€hown dbfs_user: /data01/DBFS_DIR/EXT_DIR  # here 

owner=local user, group = DBFS DB oinstall group

12. unmount
fusermount -u /data01/DBFS_DIR

Reference:
http://download.oracle.com/docs/cd/E11882_01/appdev.112/e10645/adlob_client.htm
http://www.oracle-base.com/articles/11g/DBFS_11gR2.php
http://fuse.sourceforge.net/

Exadata Storage Cell Disk Change

I follow below steps while changing and faulty disk on Exadata. We are using Exadata storage SW version 11.2.2.4.0.

1. You have identified following disk to be replaced:
       
$ cellcli -e list physicaldisk detail
         name:                   18:4
         deviceId:               10
         diskType:               HardDisk
         enclosureDeviceId:      18
         errMediaCount:          471
         errOtherCount:          17
         foreignState:           false
         luns:                   0_4
         makeModel:              "SEAGATE ST360057SSUN600G"
         physicalFirmware:       0805
         physicalInsertTime:     2010-03-26T05:05:46+06:00
         physicalInterface:      sas
         physicalSerial:         E08WD8
         physicalSize:           558.9109999993816G
         slotNumber:             4
         status:                 critical

2. You need to make sure that there is no other disks are offline/missing in other cells(failgroup) :

select count(*),failgroup from gv$asm_disk group by failgroup;


3. Check ASM_POWER_LIMIT and if it is 0 then set it to non zero value so that after the disk replacement rebalance can happen and the newly added disks will have data written on them.
This parameter can be set to any value between 0 to 11.
--on asm instance
alter system set asm_power_limit=10;

4. Make sure there are no rebalance operation running on any instance on any node:

select * from gv$asm_operation;

5. Identify the ASM disks which are existing on the physical disk to be replaced:

a. On the cell node execute following:

- Find the lun number "0_11" in the list physicaldisk detail output
# cellcli -e list physicaldisk detail
.....
luns: 0_4
.....

- Find the celldisk created for this lun (physicaldisk)
# cellcli -e list celldisk where lun=0_4
         CD_04_axdwcel06         not present

- Find the griddisks created for this celldisk
# cellcli -e list griddisk where celldisk=CD_04_axdwcel06
         DATA1_CD_04_axdwcel06           not present
         DATA2_CD_04_axdwcel06           not present
         RECO_CD_04_axdwcel06            not present
         SYSTEMDG_CD_04_axdwcel06        not present



b. Execute following on any db node connecting to the ASM instance:

SQL> select name, substr(path,1,50) , mount_status, header_status, state from gv$asm_disk where name in ('DATA1_CD_04_AXDWCEL06','DATA2_CD_04_AXDWCEL06','SYSTEMDG_CD_04_AXDWCEL06','RECO_CD_04_AXDWCEL06');

NAME SUBSTR           (PATH,1,50)                        MOUNT_S HEADER_STA STATE
------------------------------ ----------------------------------------   ------
DATA_CD_11_DMORLCEL02 o/192.168.10.6/DATA_CD_11_dmorlcel02 CACHED  MEMBER  NORMAL
RECO_CD_11_DMORLCEL02 o/192.168.10.6/RECO_CD_11_dmorlcel02 CACHED  MEMBER  NORMAL



6. Now drop the disk from the diskgroup(s):

alter diskgroup DATA1 drop disk 'DATA1_CD_04_AXDWCEL06';
alter diskgroup DATA2 drop disk 'DATA2_CD_04_AXDWCEL06';
alter diskgroup SYSTEMDG drop disk 'SYSTEMDG_CD_04_AXDWCEL06';
alter diskgroup RECO drop disk 'RECO_CD_04_AXDWCEL06';

7. Wait until the Oracle ASM disks associated with the grid disks on the bad disk have been successfully dropped by querying the V$ASM_DISK_STAT view.

V$ASM_DISK_STAT displays disk statistics in the same way that V$ASM_DISK does, but without performing discovery of new disks. This results in a less expensive operation. However, since discovery is not performed, the output of this view does not include any data about disks that are new to the system.


8. Mark this disk at the hardware level to be serviced:

cellcli -e 'alter physicaldisk 18:4 serviceled on'

Here disk_name should be replaced by the name of the physical disk '18:4'.

This step will help in identifying the disk to be replaced at the h/w level. Please note that this command may  not work in version less than 11.2.2.2. but it is not mandatory. it just helps in identifying the disk to be replaced.

9. Now replace the disk at the h/w level. The service LED will glow for this disk as done in the previous step.

10. After replacing the disk Exadata s/w will automatically create celldisk, griddisks. You can tail the cell alert.log and ASM alert.log to see this activity. But the disks will not be added back to the ASM as we did not use FORCE option while dropping them. So we need to add them manually:

alter diskgroup DATA1 add  disk 'o/10.10.0.10/DATA1_CD_04_AXDWCEL06';
alter diskgroup DATA2 add  disk 'o/10.10.0.10/DATA2_CD_04_AXDWCEL06';
alter diskgroup RECO add  disk 'o/10.10.0.10/RECO_CD_04_AXDWCEL06';
alter diskgroup SYSTEMDG add  disk 'o/10.10.0.10/SYSTEMDG_CD_04_AXDWCEL06';

-- Location of the cell alert.log file:
/opt/oracle/cell11.2.2.4.0_LINUX.X64_110929/log/diag/asm/cell/axdwcel06/trace/alert.log

-- Location of the ASM alert.log
/u01/app/oracle/diag/asm/+asm/+ASM1/trace/alert_+ASM1.log


11. Check until the rebalance completes using following sql:
select * from gv$asm_operation;

12. Once the rebalance is over check the status of the ASM disks again and verify that everything is fine:

SQL> select name, substr(path,1,50) , mount_status, header_status, state from gv$asm_disk where name in ('DATA1_CD_04_AXDWCEL06','DATA2_CD_04_AXDWCEL06','SYSTEMDG_CD_04_AXDWCEL06','RECO_CD_04_AXDWCEL06');

select * from gv$asm_disk

13. Verify all grid disks have been successfully put online using the following command from the cell where disk change is done:
    (Wait until asmmodestatus is ONLINE for all grid disks
      and Oracle ASM synchronization is only complete when all grid disks show asmmodestatus=ONLINE.)
     cellcli -e list griddisk attributes name,asmmodestatus,asmdeactivationoutcome

14. also check the alert history from the cell where disk change is done
cellcli -e list alerthistory

Shutting Down Exadata Storage Cell

Normally I follow below steps while shutting down an Exadata Storage Cell for maintenance purpose. (We were using Exadata storage SW version 11.2.2.4.0.) 


1. check disk repair time from ASM instance and ajust it if required.
select dg.name, a.value
  from v$asm_diskgroup dg, v$asm_attribute a
 where dg.group_number = a.group_number
   and a.name = 'disk_repair_time';

2. from both ASM & DB instance, make sure that all the DISKs are online

select header_status,mode_status,state,failgroup,count(*) from gv$asm_disk group by header_status,mode_status,state,failgroup;

3. from ASM instanvce wait until below query returns zero rows
select * from gv$asm_operation;

4. Next you will need to check if ASM will be OK if the grid disks go OFFLINE. The following command should return 'Yes' for the grid disks being listed:
   cellcli -e list griddisk attributes name,asmmodestatus,asmdeactivationoutcome 


5. inactivated all griddisks
cellcli -e ALTER GRIDDISK ALL INACTIVE

6. Confirm that the griddisks are now offline by performing the following actions:
   a. Execute the command below and the output should show asmmodestatus=UNUSED and asmdeactivationoutcome=Yes for all griddisks once the disks are offline in ASM. Only then is it safe to proceed with shutting down or restarting the cell:
      cellcli -e list griddisk attributes name,asmmodestatus,asmdeactivationoutcome
   b. List the griddisk to confirm that all show inactive:
      cellcli -e LIST GRIDDISK    

7. from both ASM & DB instance, make sure that all the DISKs are online

select header_status,mode_status,state,failgroup,count(*) from gv$asm_disk group by header_status,mode_status,state,failgroup;     

8. The following command will reboot Oracle Exadata Storage Server immediately:
   (When powering off Oracle Exadata Storage Servers, all storage services are automatically stopped.)
        # shutdown -y now   
        sync 
        sync
        init 0   

9. check validation log's last 20 lines all should bew PASSED or NOHUP RUN or BACKGROUND RUN
tail -20 /var/log/cellos/validations.log

10. Once the cell comes back online - you will need to reactive the griddisks:

        cellcli -e alter griddisk all active

11. Issue the command below and all disks should show 'active':

        cellcli -e list griddisk

12. Verify all grid disks have been successfully put online using the following command:
    (Wait until asmmodestatus is ONLINE for all grid disks 
      and Oracle ASM synchronization is only complete when all grid disks show asmmodestatus=ONLINE.)
     cellcli -e list griddisk attributes name,asmmodestatus,asmdeactivationoutcome

13. from both ASM & DB instance, make sure that all the DISKs are online

select header_status,mode_status,state,failgroup,count(*) from gv$asm_disk group by header_status,mode_status,state,failgroup;

14. Next you will need to check if ASM will be OK if the grid disks go OFFLINE. The following command should return 'Yes' for the grid disks being listed:
   cellcli -e list griddisk attributes name,asmmodestatus,asmdeactivationoutcome 
-- >>> check that all the grid disks are online

15. from ASM instanvce wait until below query returns zero rows
select * from gv$asm_operation;

16. check cell alert for any uncleared critical alerts
cellcli -e list alerthistory

17. check below from both DB & ASM 
select * from gv$instance;

Shutting Down a Exadata DB Node [same on normal RAC]

While we need to shutdown any of exadata(same for any RAC DB) DB Nodes I normally follow below checklist. 

1. from DB & ASM node run below query and cahek all the nodes are in open staus
select * from gv$instance;
2. login to the target DB node with oracle user and set .oracrs.sh env

3. now shutdown the instance on the target node using below command
/u01/app/11.2.0/grid/bin/srvctl stop instance -d ORADB -i

4. FROM DB node run belwo quert to be sure that target instance is now down
select * from gv$instance;

5. now login as root and run below command to shudown CRS
/u01/app/11.2.0/grid/bin/crsctl stop crs

-- from DB node run below command and make sure that crs is down [CRS-0184]
/u01/app/11.2.0/grid/bin/crs_stat -t
CRS-0184: Cannot communicate with the CRS daemon.

6. FROM ASM run below query to make sure that ASM on tgaret hots is down
select * from gv$instance;

7. shutdown the DB machine

8. startup the DB machine

9. check with below command the all the cluster services are online except .gsd and .oc4j
/u01/app/11.2.0/grid/bin/crs_stat -t|grep OFFLINE
--from DB node with oracle user and .oracrs.sh env run below command to make sure that all the ASM disks are online
asmcmd
ASMCMD> ls

. oracrs.sh
sqlplus / as sysasm

10. if any disk is missing the from ASM instance of the target machine run below command to make the perticular disk online
alter diskgroup mount;

11. chek that DB instance is online or not if not automatically online use below command to make it online
srvctl start -d axdw -i

12. from ASM & DB instance user below query to check all the ASM and DB instances are online
select * from gv$instance;

13. from ASM & DB instance user below query to check all the ASM DISKS are online from all the instance
select header_status,mode_status,state,failgroup,count(*) from gv$asm_disk group by header_status,mode_status,state,failgroup;

14. check alertlog of both db & asm for the target node

Oracle Datamining 11gR2 Repository Installation on Oracle Database 11.2.0.1

We followed below steps to install Oracle Datamining 11gR2 Repository our Oracle Database 11.2.0.1 [Exadata V2]:

Download scrits for ODM 11.2 form
http://www.oracle.com/technetwork/database/options/odm/dataminerinstallationscripts-112-323231.zip
and follow steps below:


1. Check Data Mining Option is installed in the database, if not then install Oracle Option on the Oracle Database:

SELECT * FROM V$OPTION where value='TRUE' and parameter='Data Mining';

If above returns a row then Data Mining Option is installed on the Database.
If it is not installed then install it using MOS Note:ID 818314.1

2. Check Oracle Text is installed:

select * from dba_registry where comp_id = 'CONTEXT';

If above returns no rows then, install Oracle Text using MOS Note:ID 970473.1

3. Check XML/XDB is installed:

select * from dba_registry where comp_id ='XDB';

If above returns no rows then, install XML using MOS Note:ID 1292089.1

** But while inatalling XDB we found below error, because JVM was not installed on this DB, so we need to install JVM:

GRANT javauserpriv TO xdb
*
ERROR at line 1:
ORA-01919: role 'JAVAUSERPRIV' does not exist


4. Check whether JAVA/JVM is installed:

select owner, count(*) from all_objects
where object_type like '%JAVA%' group by owner;

select * from dba_registry where comp_id in('CATJAVA','JAVAVM');

If JVM is not installed then install it using MOS Note:1112983.1

After JVM installation, run below command:

SQL> GRANT javauserpriv TO xdb;

5.Create tablespace for ODM repository:

CREATE TABLESPACE "TBS_ODM" DATAFILE '+DATA1(DATAFILE)' SIZE 1024M auto extend off; --need atleast 1GB space

6. Create ODM user:

create user ODMUSER identified by password;
alter user ODMUSER default tablespace TBS_ODM temporary tablespace TEMP;
grant create session to ODMUSER;
alter user ODMUSER quota unlimited on TBS_ODM;-- [for default tablespace]

Nevigate to unzipped folder of downloaded dataminerinstallationscripts-112-323231.zip and run commands in the below steps from sqlplus:

7. Create ODM Repository:[@installodmr.sql ][as sysdba]
set serveroutput on
set echo on
@installodmr.sql TBS_ODM TEMP

8. Granting a User the Rights to Access the Repository: [@usergrants.sql ]
set serveroutput on
set echo on
@usergrants.sql ODMUSER

Now Check as: [tested with sqldeveloper-3.0.04.34]
- Open Oracle sql developer
- goto View > Data Miner > Data Miner > Dataminer Connection
- Give: Connection Name, Username, password and connection details
- "Data Miner Panel" will be shown
- Now form Data Miner Panel, try to browse your newly created connection
and you will not be warrened some thing like "no data miner repository found"

9. To drop ODM repository:

@dropRepositoryAndUserObjects.sql

Exadata: Implementing Database Machine Bundle Patch (8)

According to the readme and companion documents we have prepared below action plan:
(We need to monitor Oracle MOS Note 888828.1 for Exadata supported patch)

** This patch is RAC Rolling Installable for ourcase, as BP5 9870547 is currently installed

-- -------------
-- Prerequisites
-- -------------

1. Inatall latest version of OPatch and dismount all DBFS:

** Logon to each(4) DB Machine and follow below steps:

For GI HOME:
------------
$ echo $ORACLE_HOME
/u01/app/11.2.0/grid

$ mv /u01/app/11.2.0/grid/OPatch /u01/app/11.2.0/grid/OPatch_old
$ unzip p6880880_112000_Linux-x86-64.zip -d /u01/app/11.2.0/grid/
$ cd $ORACLE_HOME/OPatch
$ ./opatch version [to check that the version is 11.2.0.1.4]

For DB HOME:
------------
$ echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0/dbhome_1

$ mv /u01/app/oracle/product/11.2.0/dbhome_1/OPatch /u01/app/oracle/product/11.2.0/dbhome_1/OPatch_old
$ unzip p6880880_112000_Linux-x86-64.zip -d /u01/app/oracle/product/11.2.0/dbhome_1/
$ cd $ORACLE_HOME/OPatch
$ ./opatch version [to check that the version is 11.2.0.1.4]


2. Verify the OUI Inventory:

** Logon to each(4) DB Machine and follow below steps:

For GI HOME:
------------
$ echo $ORACLE_HOME
/u01/app/11.2.0/grid

$cd $ORACLE_HOME/OPatch
$./opatch lsinventory

For DB HOME:
------------
$ echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0/dbhome_1

$ cd $ORACLE_HOME/OPatch
$ ./opatch lsinventory

[If the command errors out, contact Oracle Support]

3. Create a location PATCH_TOP

** Logon All 4 DB Machine and follow below steps:

$ mkdir /u01/app/patch/p10389035_112010_Linux-x86-64
$ export PATCH_TOP=/u01/app/patch/p10389035_112010_Linux-x86-64
$ unzip -d $PATCH_TOP p10389035_112010_Linux-x86-64.zip

4. Determine whether any currently installed one-off patches conflict with the DBM bundle patch8 10389035:
** For Both RDBMS & GI_HOME on all 4 nodes, check below

$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir $PATCH_TOP/10389035
- Conflicts with a patch already applied to the ORACLE_HOME - In this case, stop the patch installation and contact Oracle Support Services
- Conflicts with subset patch already applied to the ORACLE_HOME - In this case, continue with the patch installation because as the new patch

-- ------------
-- Installation
-- ------------
**for all 4 nodes repeat below steps [steps 5 to 7]:

5. Install Patch on GI_HOME
- As oracle run below:
$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
$ $ORACLE_HOME/bin/srvctl stop instance -d db_unique_name -n node_name

- As root run below:
# /u01/app/11.2.0/grid/bin/crsctl stop crs
# /u01/app/11.2.0/grid/crs/install/rootcrs.pl -unlock

- As oracle run below:
$ export ORACLE_HOME=/u01/app/11.2.0/grid
$ export PATH=$PATH:$ORACLE_HOME/OPatch
$ which opatch
/u01/app/11.2.0/grid/OPatch/opatch
$ export PATCH_TOP=/u01/app/patch/p10389035_112010_Linux-x86-64
$ cd $PATCH_TOP/10389035
$ opatch apply -local

- As root run below:
# /u01/app/11.2.0/grid/crs/install/rootcrs.pl -patch
# /u01/app/11.2.0/grid/bin/crsctl start crs

- As oracle run below:
$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
$ $ORACLE_HOME/bin/srvctl start instance -d db_unique_name -n node_name

6. Install Patch on DB_HOME [Start a new session to unset ENV variable in step:5]
- As oracle run below:
$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
$ $ORACLE_HOME/bin/srvctl stop home -o /u01/app/oracle/product/11.2.0/dbhome_1 -s STAT_FILE_LOCATION -n NODE_NAME
$ export PATH=$PATH:$ORACLE_HOME/OPatch
$ which opatch
/u01/app/oracle/product/11.2.0/dbhome_1/OPatch/opatch
$ export PATCH_TOP=/u01/app/patch/p10389035_112010_Linux-x86-64
$ cd $PATCH_TOP/10389035
$ opatch apply -local
$ $ORACLE_HOME/bin/srvctl start home -o /u01/app/oracle/product/11.2.0/dbhome_1 -s STAT_FILE_LOCATION -n NODE_NAME

-- ---------------------------------------------
-- Postinstallation only on a ***single instance
-- --------------------------------------------

7. Reload the packages into the database:

$ sqlplus /nolog
SQL> connect / as sysdba
SQL> @?/rdbms/admin/catbundle.sql exa apply

8. Navigate to the $ORACLE_HOME/cfgtoollogs/catbundle directory
(if $ORACLE_BASE is defined then the logs will be created under $ORACLE_BASE/cfgtoollogs/catbundle),
and check the following log files for any errors like "grep ^ORA | sort -u".
If there are errors, refer to "Known Issues". Here, the format of the is YYYYMMMDD_HH_MM_SS.

catbundle_EXA__APPLY_.log
catbundle_EXA__GENERATE_.log

-- --------------
-- Deinstallation
-- --------------

9. **for all 4 nodes repeat steps 5 to 7 except use "opatch rollback -id 10389035 -local" inetead of "opatch apply -local"

Exadata Storage Software Version upgrade from 11.2.1.3.1 to 11.2.2.2.0

We have just upgraded our Exadata Storage Software Version upgrade from 11.2.1.3.1 to 11.2.2.2.0 (patch: 10356485):
Before that we have done below:
1. implement Exadata Database Machine Bundle Patch 8
2. implement 10110978 above BP 8
3. implement 11661824 above BP 8

Normally Exadata SW patches are needs to e applied on both Cell Servers and DB Machines.
For supported patches for exadata machines, please monitor oracle MOS Doc ID: 888828.1

According to the readme and other related companion documents we prepared below action plans which is some like below:

---------------
PATCHING CELLS:
---------------

- Find the model of the cell or database host using command

dmidecode -s system-product-name

DB Machines : SUN FIRE X4170 SERVER
Cell Servers: SUN FIRE X4275 SERVER


PREPARING FOR PATCHING:
----------------------

- Check for clean file systems on cells at version less than 11.2.2.1.0

Check only one cell at a time(for all 7 cells) when using rolling updates

- Monitor the cells on their LO web console during this process.

- If you are doing this check in rolling fashion with no deployment downtime, then
ensure the grid disks on the cell are all offlined.

- From ASM execute,

ALTER DISKGROUP DATA1 OFFLINE DISKS IN FAILGROUP [FAIL_GROUP/CELL NAME];
ALTER DISKGROUP DATA2 OFFLINE DISKS IN FAILGROUP [FAIL_GROUP/CELL NAME];
ALTER DISKGROUP SYSTEMDG OFFLINE DISKS IN FAILGROUP [FAIL_GROUP/CELL NAME];
ALTER DISKGROUP RECO OFFLINE DISKS IN FAILGROUP [FAIL_GROUP/CELL NAME];

- Shut down cell services on the cell to avoid automatic online of grid disks:

cellcli -e 'alter cell shutdown services all'

- Login as root user and reboot cell using following command.
You can do this on all cells together if you are using non
rolling update. This forces a file system check at boot.

sync
sync
shutdown -F -r now

- If any cell encounters corrupt file system journal, then it will
not complete the reboot. If you encounter such cell contact Oracle
Support for guidance or refer to bug 9752748 to recover the cell.

- If you are doing this check in rolling fashion with no deployment downtime, then
ensure the grid disks are all online before starting this procedure on another cell.

cellcli -e 'list griddisk attributes name,asmmodestatus'

should show all grid disks that were online before step 02 are all back online.

- Ensure the various cell network configurations are consistent.


- One way to help verify the values are consistent is to run ipconf with -dry option.

- CAUTION: ipconf -dry, does not flag all inconsistencies. Inspection to compare with what the system is really using is needed.

- Any values marked as (custom) during such run indicate that the cell configurations were manually modified instead of using ipconf.

- All other values should be carefully inspected and compared to the actual values in use to ensure they do not differ from each other.

- Setting up for using patchmgr

- patchmgr must be launched as root user from a system that has root user

- ssh equivalence set up to the root user on all the cells to be patched
*** we are ging to use a DB machine for this

- To set up root user ssh equivalence from a Exadata DB Machine follw below:

- Prepare the list of cells file say called cell_group that has one cell host name or ip address per line for each cell to be patched.

- Check for existing root ssh equivalence. Following command should
require no password prompts and no interaction. It should return
the list of hostnames in the cell_group file.

dcli -g cell_group -l root 'hostname -i'

- Set up ssh root equivalence if not already done from the launch host
NOTE: Do not do this step if you already have root ssh equivalence.

- Generate root ssh keys.
a. ssh-keygen -t dsa
b. ssh-keygen -t rsa

Accept defaults so the ssh keys are created for root user

- Push ssh keys to set up ssh equivalence. Enter root password
when prompted for.

dcli -g cell_group -l root -k

- Check prerequisites


* If you are using rolling patch, then

./patchmgr -cells cell_group -patch_check_prereq -rolling

* If you are using rolling rollback, then

./patchmgr -cells cell_group -rollback_check_prereq -rolling


- For rolling patching

- Ensure the database is up and running

- Adjust the disk_repair_time for ASM(value should be 3.6h)
sqlplus> select dg.name,a.value from gv$asm_diskgroup
dg, gv$asm_attribute a where dg.group_number=a.group_number and
a.name='disk_repair_time';


PATCHING CELLS:
---------------

- Use the LO web based console to monitor the cell during patch

- Use a fresh login session for each patch or rollback execution

- Do not reboot or power cycle cells in the middle of applying the patch.
You may be left with an unbootable cell.

- Do not edit any log file or open them in writable mode.

- Monitoring patch activity

- Use 'less -rf patchmgr.stdout' from another terminal session or window
to see raw log details from patchmgr.

- In addition on each cell being patched, you can tail the file,

/root/_patch_hctap_/_p_/wait_out

- Use tail -200f to monitor the ASM alert logs on
all ASM instances to keep a watch on ASM activity as the patch or rollback
progresses.

- Patching steps using patchmgr

- Do not run more than one instance of patchmgr in the deployment. It
can cause a serious irrecoverable breakdown.

- Login to a system that has ssh equivalence set up for root to all cells that are to be patched.

- Unzip the patch, it will extract into the patch_11.2.2.2.0.101206.2 directory.

- Change to the patch_11.2.2.2.0.101206.2 directory.

- [according to DOC ID 1270634.1]Download any work around helpers attached to the MOS note 1270634.1, and replace dostep.sh in patch_11.2.2.2.0.101206.2 directory.

- Prepare a file listing the cells with one cell host name or ip address per
line. Say the file name is cell_group.

- Verify that the cells meet prerequisite checks. Use -rolling option if you plan to use rolling updates.

./patchmgr -cells cell_group -patch_check_prereq -rolling

- If the prerequisite checks pass, then start the patch application. Use -rolling option if you plan to use rolling updates.

./patchmgr -cells cell_group -patch -rolling

- Monitor the log files and cells being patched

See the "Monitoring patch activity" section earlier in this file.

- Verify the patch status

A. Check image status and history

Assuming the patching is successful, check the imageinfo output and
imagehistory output on each cell. A successful patch application will
show output similar to the following.

Kernel version: 2.6.18-194.3.1.0.3.el5 #1 SMP Tue Aug 31 22:41:13 EDT 2010 x86_64
Cell version: OSS_MAIN_LINUX.X64_101206.2
Cell rpm version: cell-11.2.2.2.0_LINUX.X64_101206.2-1

Active image version: 11.2.2.2.0.101206.2
Active image activated:
Active image status: success
Active system partition on device: < / file system device after successful patch e.g. /dev/md5>
Active software partition on device:

In partition rollback: Impossible

Cell boot usb partition:
Cell boot usb version: 11.2.2.2.0.101206.2

Inactive image version:
Inactive image activated:
Inactive image status: success
Inactive system partition on device:
Inactive software partition on device:

Boot area has rollback archive for the version:
Rollback to the inactive partitions: Possible

B. Only if you used -rolling option to patchmgr:

Ensure all grid disks that were active before patching started are
active and their asmmodestatus is ONLINE. You will find the list of
grid disks inactivated by the patch or rollback in the following file
on the respective storage cell

/root/attempted_deactivated_by_patch_griddisks.txt

cellcli -e 'list griddisk attributes name,status,asmmodestatus'

- if imagehistory shows failure and misceachboot shows FAILED in file "/var/log/cellos/validations.log" [the most recent run of validations]
of target CELL then do as below:

01. Log in to the cell as root user
02. Stop cell services: cellcli -e 'alter cell shutdown services all'
03. Set the LSI card to factory defaults: /opt/MegaRAID/MegaCli/MegaCli64 -AdpFacDefSet -a0
04. Reboot the cell.[shutdown -F -r now]
05. Ensure that there are no failed validations in /var/log/cellos/validations.log for
the most recent run of validations. Specifically there is no misceachboot validation failure.
06. /opt/oracle.cellos/imagestatus -set success
07. Verify imageinfo now shows success


- Clean up

Use -cleanup to clean up all the temporary patch or rollback files on the
cells. It will free up close to 900 MB disk space.

Also use this before retrying an aborted or failed run of patchmgr.

./patchmgr -cells cell_group -cleanup

- Optionally remove the root ssh equivalence:

This is an optional step.

dcli -g cell_group -l root --unkey


- Rolling back successfully patched cells using patchmgr

- Do not run more than one instance of patchmgr at a time in the deployment. It
can cause a serious irrecoverable breakdown.

- Check the prerequisites:

./patchmgr -cells cell_group -rollback_check_prereq -rolling

- Execute the rollback:

./patchmgr -cells cell_group -rollback -rolling


---------------------
PATCHING DB MACHINES:
---------------------

- The database host convenience pack is distributed as db_patch_11.2.2.2.0.101206.2.zip inside the cell patch.

- [according to DOC ID 1270634.1] Before copying the db_patch_11.2.2.2.0.101206.2.zip file from cell patch, do below:

- Completely "shut down Oracle" components on the database host and disable clusterware (CRS). For example,
/u01/app/11.2.0/grid/bin/crsctl stop crs -f
/u01/app/11.2.0/grid/bin/crsctl disable crs
- Do a clean reboot of the database host: As root user execute
# reboot

- Shut down oswatcher:
As root user:
a. cd /opt/oracle.oswatcher/osw
b. ./stopOSW.sh
- Turn on the controller cache
/opt/MegaRAID/MegaCli/MegaCli64 -LDSetProp WB -Lall -a0
/opt/MegaRAID/MegaCli/MegaCli64 -LDSetProp NoCachedBadBBU -Lall -a0
/opt/MegaRAID/MegaCli/MegaCli64 -LDSetProp NORA -Lall -a0
/opt/MegaRAID/MegaCli/MegaCli64 -LDSetProp Direct -Lall -a0

- Add the following lines before the last exit 0 line in the file /opt/oracle.cellos/validations/init.d/misceachboot:
/opt/MegaRAID/MegaCli/MegaCli64 -LDSetProp WB -Lall -a0
/opt/MegaRAID/MegaCli/MegaCli64 -LDSetProp NoCachedBadBBU -Lall -a0
/opt/MegaRAID/MegaCli/MegaCli64 -LDSetProp NORA -Lall -a0
/opt/MegaRAID/MegaCli/MegaCli64 -LDSetProp Direct -Lall -a0

- Apply the LSI disk controller firmware manually if it is not already at 12.9.0 [Currently 12.0.1-0081]
As root user:
a. Unzip the database convenience pack and extract the disk controller firmare
unzip db_patch_11.2.2.2.0.101206.2.zip
mkdir /tmp/tmpfw
tar -pjxvf db_patch_11.2.2.2.0.101206.2/11.2.2.2.0.101206.2.tbz -C /tmp/tmpfw \
opt/oracle.cellos/iso/cellbits/dbfw.tbz
tar -pjxvf /tmp/tmpfw/opt/oracle.cellos/iso/cellbits/dbfw.tbz -C /tmp/tmpfw \
ActualFirmwareFiles/12.9.0.0049_26Oct2010_2108_FW_Image.rom
b. Flush file system data to disks:
sync
/opt/MegaRAID/MegaCli/MegaCli64 -AdpCacheFlush -a0
c. Execute firmware update:
/opt/MegaRAID/MegaCli/MegaCli64 -AdpFwFlash -f \
/tmp/tmpfw/ActualFirmwareFiles/12.9.0.0049_26Oct2010_2108_FW_Image.rom -a0
e. Flush file system data to disks:
sync
/opt/MegaRAID/MegaCli/MegaCli64 -AdpCacheFlush -a0
f. Reboot the system: reboot

-Now proceed to applying the rest of the database host convenience pack


PREPARING FOR DATABASE HOST PATCHING:
-------------------------------------

* Run all steps as root user.

* Obtain Lights Out (LO) and serial console access for the database hosts to
be patched. It is useful if an issue needs fixing during the patch or if
some firmware update does not go through properly.

* For Exadata V1 (HP hardware) the serial console can be accessed by telnet
or ssh to the LO host name or ip address of by the administrative user
such as admin. To start serial console,

VSP

To stop it press escape key (ESC) followed by (

ESC (
stop

* For Exadata Oracle-Sun hardware ssh to the ILOM host name or IP
address as root user. To start the serial console,

start /SP/console

To stop it press escape key (ESC) followed by (

ESC (
stop /SP/console

* Check output of /usr/local/bin/imagehistory.

DB host must be at release 11.2.1.2.x or 11.2.1.3.1 or 11.2.2.1.x for Exadata V2.
DB host must be at release 11.2.1.2.x for Exadata machine V1.

* Unmount all NFS and DBFS file systems

* All Oracle software must be shut down before patching.

/u01/app/11.2.0/grid/bin/crsctl stop crs -f

Verify no cluster, ASM or Database processes are running:

ps -ef | grep -i 'grid\|ASM\|ora'

Shut down oswatcher:

cd /opt/oracle.oswatcher/osw
./stopOSW.sh

Oracle recommends that you do not interrupt the procedure with control-C.
-------------------------------------------------------------------

PATCHING DATABASE HOSTS:
-----------------------

00. Repeat the steps 01 onwards for EACH database host. If you are taking
deployment wide downtime for the patching, then these steps
may be performed in parallel on all database hosts.

01. Update the resource limits for the database and the grid users:

NOTE: This step does NOT apply if you have customized the values
for your specific deployment and database requirements.

WARNING: Do NOT execute this step, if you do have specific
customized values in use for your deployment.

01.a Calculate 75% of the physical memory on the machine.

let -i x=($((`cat /proc/meminfo | grep 'MemTotal:' | awk '{print $2}'` * 3 / 4)))
echo $x

01.b Edit the /etc/security/limits.conf file to update or add following
limits for the database owner (orauser) and the grid infrastructure
user (griduser). Your deployment may use the same operating system
user for both and it may be named as oracle user. Adjust the following
appropriately.

########### BEGIN DO NOT REMOVE Added by Oracle ###########

orauser soft core unlimited
orauser hard core unlimited
orauser soft nproc 131072
orauser hard nproc 131072
orauser soft nofile 131072
orauser hard nofile 131072
orauser soft memlock
orauser hard memlock

griduser soft core unlimited
griduser hard core unlimited
griduser soft nproc 131072
griduser hard nproc 131072
griduser soft nofile 131072
griduser hard nofile 131072
griduser soft memlock
griduser hard memlock

########### END DO NOT REMOVE Added by Oracle ###########

02. Login as root user to a the database host and copy over the
db_11.2.2.2.0.101206.2_patch.zip file from unzipped cell patch.

03. Unzip db_11.2.2.2.0.101206.2_patch.zip file. It will create directory:

db_patch_11.2.2.2.0.101206.2

04. Change to the db_patch_11.2.2.2.0.101206.2 directory.

05. Run ./install.sh [this should be run locally from the host which is being patched]

It will return to the prompt immediately after submitting the patch
in background. Return to the prompt does NOT mean the patch is
complete.

NOTE: The install.sh will submit the patch process in the background
to prevent interruption of the patch in case the login session
gets terminated due to network connection break. The database host
will reboot as part of the patch process after a while.

NOTE: The database host will reboot as part of this update.
You will lose your connections including the ssh connection
and the database host may appear to hang which the ssh connection
eventually times out. If the LO gets updated, then the same
connection loss or freeze will be experienced.
After 10 minutes retry connection.

06. Verify the patch status:

After the system is rebooted and up,

06.1. /usr/local/bin/imageinfo should show Image version as 11.2.2.2.0.
For example,

Image version: 11.2.2.2.0.101206.2
Image activated: 2010-12-06 11:35:59 -0700
Image status: success

06.2 Verify the ofa rpm version

Find the kernel value: uname -r
Find the ofa rpm version: rpm -qa | grep ofa

06.2.a ofa versions should match following table,

kernel ofa

2.6.18-194.3.1.0.3 1.5.1-4.0.28
2.6.18-194.3.1.0.2 None, the ofa is part of the kernel
2.6.18-128.1.16.0.1 1.4.2-14
2.6.18-53.1.21.2.1 1.4.2-14

06.3 Only for database host models X4170, X4170M2:

Verify the LSI disk controller firmware is now 12.9.0-0049

/opt/MegaRAID/MegaCli/MegaCli64 -AdpAllInfo -aAll | grep 'FW Package Build'

06.4. Only for the model X4170 database hosts:

06.4.a Verify the version of ILOM firmware: ipmitool sunoem version

Version: 3.0.9.19.a r55943

06.4.b Verify the thermal profile to ensure appropriate fan speeds.

ipmitool sunoem cli "show /SP/policy"

Should display: FLASH_ACCELERATOR_CARD_INSTALLED=enabled

Post Installation
-----------------
- Now proceed to applying the rest of the database host convenience pack.

- After the database host reboots at the end of the convenience pack, enable the
clusterware and bring up rest of the Oracle stack.
/u01/app/11.2.0/grid/bin/crsctl enable crs
- Verify that the controller cache is on by running,

/opt/MegaRAID/MegaCli/MegaCli64 -LDInfo -Lall -a0

The output for each Logical Drive should show following lines:

Default Cache Policy: WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU
Current Cache Policy: WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU
- Reboot and mount unmounted ASM Diskgroups [alter diskgroup ... mount;]