Tuesday, September 18, 2012

Add Database as a Target in Oracle Enterprise Manager 12c Cloud Control


Step 1: Just verify the added host.
image




Step 2: Enterprise Manager Home Page
image





Step 3: Database Home Page, Click “ADD”
image





Step 4: Click icon to select Host
image





Step 5: Select Host
image





Step 6: Click Continue
image





Step 7: Enter Password in “Monitor Password” textbox
image





Step 8: Click Test Connection on the top right side of the page, click “Next”
image





Step 9: Note: Set database notification methods using Metrics icon
image





Step 10: Click Save
image





Step 11: Target Configuration Results page will be displayed.
image





Step 12: Database is added as a Monitoring Target.
image





Step 13: Database Monitoring Page.
image


Bye,
Saurabh

Sunday, September 9, 2012

Location of Different Log files in Exadata Environment


I was looking at one of the Oracle DOC ID 1326382.1 two months back, I found it very useful, specially when some issue arises and we start looking at the logs and get puzzled where to look. This post is for all people working on Exadata, hope it will be helpful for you :)

On the cell nodes
================ 

1. Cell alert.log file
/opt/oracle/cell/log/diag/asm/cell/{node name}/trace/alert.logorif the CELLTRACE parameter is set just do cd $CELLTRACE

2. MS logfile
/opt/oracle/cell/log/diag/asm/cell/{node name}/trace/ms-odl.log.
orif the CELLTRACE parameter is set just do cd $CELLTRACE

3. OS watcher output data
/opt/oracle.oswatcher/osw/archive/
To get OS watcher data of specific date :
cd /opt/oracle.oswatcher/osw/archive
find . -name '*12.01.13*' -print -exec zip /tmp/osw_`hostname`.zip {} ;
where 12- year 01- Month 13-day

4. Os message logfile
/var/log/messages

5. VM Core files
/var/log/oracle/crashfiles
More details can be found in the following note:
Where / How to find OS crashcore file in Exadata Systems [Linux] (Doc ID 1389225.1)

6. SunDiag output files.
/tmp/sundiag_.tar.bz2

7. Cell patching issues related logfiles:
/var/log/cellos
The major logfile of patch application output you will find in the db node from where you are patching in the location /tmp/<cell version>/patchmgr.stdout and patchmgr.err

8. Disk controller firmware logs:
/opt/MegaRAID/MegaCli/Megacli64 -fwtermlog -dsply -a0


On the Database nodes
=====================

1. Database alert.log
$ORACLE_BASE/diag/rdbms/{DBNAME}/{sid}/trace/alert_{sid}.log
Ex: /u01/app/oracle/diag/rdbms/dbfs/DBFS2/trace/alert_DBFS2.log

2. ASM alert.log
$ORACLE_BASE/diag/asm/+asm/+ASM{instance number}/trace/ alert_+ASM {instance number}.log
Ex: /u01/app/oracle/diag/asm/+asm/+ASM2/trace/alert_+ASM2.log

3. Clusterware CRS alert.log
$GRID_HOME/log/{node name}/alert{node name}.log
Ex: /u01/app/11.2.0/grid/log/dmorldb02/alertdmorldb02.log

4. Diskmon logfiles
$GRID_HOME/log/{node name}/diskmon/diskmon.lo*
Ex: /u01/app/11.2.0/grid/log/dmorldb02/diskmon/diskmon.log

5. OS Watcher output files
/opt/oracle.oswatcher/osw/archive/
To get OS watcher data of specific date :
cd /opt/oracle.oswatcher/osw/archive
find . -name '*12.01.13*' -print -exec zip /tmp/osw_`hostname`.zip {} ;
where 12- year 01- Month 13-day

6. Os message logfile
/var/log/messages

7. VM Core files for Linux
/u01/crashfiles
More details can be found in the following note:Where / How to find OS crashcore file in Exadata Systems [Linux] (Doc ID 1389225.1)

8. Disk controller firmware logs:
/opt/MegaRAID/MegaCli/Megacli64 -fwtermlog -dsply -a0

Bye,
Saurabh


Saturday, September 8, 2012

Encrypt a single column of a table using Oracle wallet

This post is basically for all DBA who are looking a way to implement column level encryption policy in their database. I know most of the time we start looking this kind of security implementation for data confidentiality when we get an alert from Audit team to be align with information & security compliance policy of the organization. This happened to me also J

Well, I am going to implement this using Oracle wallet.

Platform Detail:
Database version: 11.2.0.3
Operating System: Linux 5.3


Step 1: created a user “Saurabh” and creating a table with an encrypted column.

Connect saurabh:

SQL> CREATE TABLE TEST_USER
(
USER VARCHAR2(10),
PWD  VARCHAR2(30)ENCRYPT,
CREATE_DATE_TIME DATE,
MOD_DATE_TIME DATE
);

CREATE TABLE TEST_USER
*
ERROR at line 1:
ORA-28365: wallet is not open

Note: We get above error message “wallet is not open” so before encrypting any column we must have a wallet created and encryption key defined.

Step 2: Connected to oracle user at command line, created a directory under “$ORACLE_BASE/admin/<SID>” folder.

$ cd $ORACLE_BASE/admin/<SID>

$ mkdir wallet

Step 3: Connected to Sys and defined encryption key for wallet.

Connect as sys:

SQL> ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "SauPass";
System altered.

Note: Now we can see one file is created under wallet directory:

$ pwd
/u01/app/oracle/admin/orcl/wallet

$ ll
total 4
-rw-rw-r-- 1 oracle oracle 1573 Sep  6 11:02 ewallet.p12

Step 4: Connected to Saurabh user and created a table with one encrypted column.
Connect saurabh:

SQL> CREATE TABLE TEST_USER
(
USER VARCHAR2(10),
PWD  VARCHAR2(30)ENCRYPT,
CREATE_DATE_TIME DATE,
MOD_DATE_TIME DATE
);
Table created.

Step 5: Data inserted into the table.

SQL> INSERT INTO TEST_USER VALUES(‘abcde’,'xyzer’,sysdate,sysdate);
1 row created.

SQL> commit;
Commit complete.

SQL> select * from test_user;
USER    PWD                         CREATE_DA MOD_DATE_
---------- ------------------------------ --------- ---------
abcde      xyzer                          06-SEP-12 06-SEP-12
We can fetch the data and encrypted column data is appearing with no issues.

Step 6: Connect to Sys and close the wallet.
Connect as sys:

SQL> ALTER SYSTEM SET ENCRYPTION WALLET CLOSE IDENTIFIED BY "SauPass";
System altered.

Step 7: Connect to Saurabh user and fetch the table data.
Connect saurabh:

SQL> select * from test_user;
select * from test_user
              *
ERROR at line 1:
ORA-28365: wallet is not open

Note: “wallet is not open” error message appears when we try to fetch all columns data of the table but we are able to fetch the columns data other than encrypted column:

SQL> SELECT USER FROM TEST_USER;
USER
----------
abcde

SQL> SELECT PWD FROM TEST_USER;
SELECT PWD FROM TEST_USER
                   *
ERROR at line 1:
ORA-28365: wallet is not open

Step 8: Hence, once a table’s column is encrypted and we need to see the encrypted column data, the wallet must be opened. Connected to sys and opened the wallet.
Connect as sys:

SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "SauPass";
System altered.

Step 9: Connected to Saurabh user and fetch all the columns.
Connect saurabh:

SQL> select * from test_user;
USER    PWD                         CREATE_DA MOD_DATE_
---------- ------------------------------ --------- ---------
abcde      xyzer                          06-SEP-12 06-SEP-12

SQL> SELECT PWD FROM TEST_USER;
PWD
------------------------------
xyzer


Bye,
Saurabh

Friday, September 7, 2012

“UTF-8 encoding of Unicode” PowerCenter Integration Service is unavailable – Informatica 9.0.1

I had a tough time while resolving Informatica repository & Integration services issues. I installed Informatica on Linux platform with no issues but after creating repository, integration service was not getting up. After scrutinizing the issue I found that basically its environmet and language settings at OS and repository services level that was causing the issue so these settings must be properly defined. During repository creation and integration services configuration I faced a problem “UTF-8 encoding of Unicode”, below is the steps how I coped with this issue and made my day happy J

Hope this will be helpful for the visitors of this page.

Environment:
Informatica 9.0.1
Platform: Linux 5.3 64 bit
Database: Oracle 11.2.0.3 64 bit client
Informatica Power User: Oracle
Oracle Client User: Oracle

Step 1: Repository Services is Up but Integration service is down.

 image


Step 2: Click on Repository Service “pow_rep” at left pane, “UTF-8 encoding of Unicode” error is appearing. It shows that its related to some language setting issue because in repository code page “MS Window Latin 1 (ANSI)” is set and somewhere in the system UTF-8 is set that is not compatible to repository code page language.

 image


Step 3: Click on Integration service “pow_int” at left pane, it shows Powercenter Integration Service is unavailable.

image


Step 4: Check environment settings, we found that LANG=en US.UTF-8 is set in our environment settings.

image

Step 5: Unset the LANG setting.
[oracle@sun2 bin]$ unset LANG

Step 6: Restart informatica services:

image

Step 7: Restart repository & integration services, both went up with no issue.

image


Please put your comment if this blog is useful for you.

Yours,

Saurabh

“Knowledge brings the power, attitude respect.”





Thursday, September 6, 2012

OBIEE Start & Stop Script in Linux

Start/Stop OBIEE 11g Services in Linux using script

Hi,

Till few days back I had been struggling on obiee 11g services start/stop on Linux as if there is any development work and obiee has to be restarted then I have to stop the weblogic, bi server and bi presentation services in proper sequence and same applies for starting. I was looking some automated way, hence running a single stop command can stop the all bi services and a single start would do for all bi services to start.

I have found an interesting script on an Oracle Forum and its really very nicely written, using the below script we can start/stop/check status of bi services. I put this script file in linux runlevel, hence every time server reboots, it stops and starts the bi services automatically. And if required, we can manually stop and start the bi services with a single command.

Below is the script:

#!/bin/bash
# File:    /etc/init.d/obiee
# Purpose: Start and stop Oracle Business Intelligence 11g components.
#
# chkconfig: 2345 99 10
# description: Manage OBIEE service.
#
# These values must be adapted to your environment.
ORACLE_OWNR=obi                  # Local Unix user running OBIEE
ORACLE_FMW=/MiddlewareHome        # Deployment root directory
                                   
BIEE_USER=weblogic                 # BIEE administrator name
BIEE_PASSWD=<put weblogic's password>               # BIEE administrator password               
BIEE_DOMAIN=bifoundation_domain           # Domain name
BIEE_INSTANCE=instance1            # Instance name
BIEE_SERVER=bi_server1               # Server name
BIEE_MANAGER_URL=x.x.x.x:port#     # Admin server URL (hostname:port)    
# These should require no change.
WL_PATH=$ORACLE_FMW/wlserver_10.3/server/bin
BIEE_PATH=$ORACLE_FMW/user_projects/domains/$BIEE_DOMAIN/bin
ORACLE_INSTANCE=$ORACLE_FMW/instances/$BIEE_INSTANCE
export ORACLE_INSTANCE
START_LOG=/var/log/obiee-start.log
STOP_LOG=/var/log/obiee-stop.log
SUBSYS=obiee
start() {
    echo "********************************************************************************"
    echo "Starting Admin Server on $(date)"
    echo "********************************************************************************"
    su $ORACLE_OWNR -c "$BIEE_PATH/startWebLogic.sh" &
    wait_for "Server started in RUNNING mode"
    
    echo "********************************************************************************"
    echo "Starting Node Manager on $(date)"
    echo "********************************************************************************"
    su $ORACLE_OWNR -c "$WL_PATH/startNodeManager.sh" &
    wait_for "socket listener started on port"
    echo "********************************************************************************"
    echo "Starting Managed Server $BIEE_SERVER on $(date)"
    echo "********************************************************************************"
    su $ORACLE_OWNR -c "$BIEE_PATH/startManagedWebLogic.sh $BIEE_SERVER http://$BIEE_MANAGER_URL" &
    wait_for "Server started in RUNNING mode"
    echo "********************************************************************************"
    echo "Starting BI components on $(date)"
    echo "********************************************************************************"
    su $ORACLE_OWNR -c "$ORACLE_INSTANCE/bin/opmnctl startall"
    echo "********************************************************************************"
    echo "OBIEE start sequence completed on $(date)"
    echo "********************************************************************************"
}
stop() {
    echo "********************************************************************************"
    echo "Stopping BI components on $(date)"
    echo "********************************************************************************"
    su $ORACLE_OWNR -c "$ORACLE_INSTANCE/bin/opmnctl stopall"
    echo "********************************************************************************"
    echo "Stopping Managed Server $BIEE_SERVER on $(date)"
    echo "********************************************************************************"
    su $ORACLE_OWNR -c "$BIEE_PATH/stopManagedWebLogic.sh $BIEE_SERVER t3://$BIEE_MANAGER_URL $BIEE_USER $BIEE_PASSWD"
    echo "********************************************************************************"
    echo "Stopping Node Manager on $(date)"
    echo "********************************************************************************"
    pkill -TERM -u $ORACLE_OWNR -f "weblogic\\.NodeManager"
    
    echo "********************************************************************************"
    echo "Stopping Admin Server on $(date)"
    echo "********************************************************************************"
    su $ORACLE_OWNR -c "$BIEE_PATH/stopWebLogic.sh"
    
    echo "********************************************************************************"
    echo "OBIEE stop sequence completed on $(date)"
    echo "********************************************************************************"
}
wait_for() {
    res=0
    while [[ ! $res -gt 0 ]]
    do
        res=$(tail -5 "$START_LOG" | fgrep -c "$1")
        sleep 5
    done
}
case "$1" in
    start)
        echo "********************************************************************************"
        echo "Starting Oracle Business Intelligence on $(date)"
        echo "Logs are sent to $START_LOG"
        echo "********************************************************************************"
        start &> $START_LOG &
        touch /var/lock/subsys/$SUBSYS
    ;;
    stop)
        echo "********************************************************************************"
        echo "Stopping Oracle Business Intelligence on $(date)"
        echo "Logs are sent to $STOP_LOG"
        echo "********************************************************************************"
        stop &> $STOP_LOG
        rm -f /var/lock/subsys/$SUBSYS
    ;;
    status)
        echo "********************************************************************************"
        echo "Oracle BIEE components status...."
        echo "********************************************************************************"
        su $ORACLE_OWNR -c "$ORACLE_INSTANCE/bin/opmnctl status"
    ;;
    restart)
        $0 stop
        $0 start
    ;;
    *)
        echo "Usage: $(basename $0) start|stop|restart|status"
        exit 1
esac
exit 0



Deployment Steps:

In order for the procedure to go through smoothly, you need to provide the admin credentials (username/password, defaulting to weblogic/weblogic) in three different places:

1. In configuration file <FMW_HOME>/user_projects/domains/<domain name>/servers/AdminServer/security/boot.properties for the administration server;

If boot.properties file is not available then create a new one with the below entries:

username=[your user, usually weblogic]
pasword=[password for account above]


The next time you start weblogic, it will encrypt the file.

2. In script <FMW_HOME>/user_projects/domains/<domain name>/bin/startManagedWebLogic.sh (variables WLS_USER and WLS_PW) for the managed server;

3. In the startup script itself (variables BIEE_USER and BIEE_PASSWD) for shutting down the managed server.

Complete logs are available in /var/log/obiee-start (-stop).log files.

To Start OBIEE:
 /etc/init.d/./obiee start 

To Stop OBIEE:
 /etc/init.d/./obiee stop

To Check the Status of OBIEE:
 /etc/init.d/./obiee status

I think, it will really help the readers of this blog and at the same time I would like to thank to Chris (On Oracle Forum) for sharing such a nice script.

Bye,
Saurabh

Shell Script for finding Temperature of Exadata Cell Servers and DB nodes

Exadata Servers contain both Cell Storage Servers and DB nodes. At times it is difficult to go and find the temperature by logging in each and every server.

Below is the script for finding out Server Temperature.

/tmp/consol_list --- contains the old temperature details , so we delete and first itself.
/var/tmp/dont_del_hosts ---- contains the servers for which we need to find temperatures.
The newserver is the DB node for which we are going to find the temp.
and
script_file is the file which contains the exact code for finding temperature of cell steorge servers. Pl find the code below for that.
################################################################################################
rm  /tmp/consol_list
for i in `cat /var/tmp/dont_del_hosts`
do
ssh root@$i < script_file  2>1
scp root@$i:/tmp/average_file /tmp/ 2>1
cat /tmp/average_file >> /tmp/consol_list
done
newserver=`ssh -t root@x.x.x.x "dcli -g all_group -l root "ipmitool sensor list | grep "degrees" | grep " T_AMB" | grep "db0" | cut -d '|' -f 2 | head -1`
echo "Average Temperature of  DBnode 1 is $newserver" >> /tmp/consol_list
mailx -s " TEMPERATURE DETAILS " jegan.ramachandran@gmail.com < /tmp/consol_list
#####################################################################################

Script_file

===================================================================
`cellcli << EOF
spool /tmp/txt_file
list metrichistory where objectType = 'CELL' and name = 'CL_TEMP'
EOF` 2>1
cd /tmp
cat txt_file | cut -d ' ' -f 4 > /tmp/txt1
sum=`awk '{ sum += $1 } END { print sum }' txt1`
line_count=`cat /tmp/txt1 | wc -l`
average=`expr $sum / $line_count`
echo "Average Temp for `hostname` is   $average" > /tmp/average_file
===============================================================

Thanks and Regards
JeganR

Monday, September 3, 2012

EM CLI Client Setup for OEM 12c Cloud Control


How to setup EMCLI:

We have installed EM CLI client on our Management Host Server during Oracle Enterprise Manager 12c Cloud Control Configuration.  Below is the step by step process we followed for EM CLI Client installation:

Platform: Linux x86

Ø  Go to ecmli download page:
    
·    Below page will appear:


  
Ø  Download EM CLI Kit

    ·     Transfer the downloaded jar file (emclikit.jar) to Management Host server
·         We must have Java version 1.6.0_25 or greater to install emcli.

Ø  Set your JAVA_HOME and PATH environment:

     ·         # echo $JAVA_HOME
/usr/java/jre1.6.0_22

·         # echo $PATH
/usr/kerberos/sbin:/usr/kerberos/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin:/usr/java/jre1.6.0_22/bin

#Make sure the JDK path is set as below

lrwxrwxrwx 1 root root         30 Sep  3 14:28 java -> /usr/java/jdk1.6.0_25/bin/java

 In the above case it is JDK 1.6...you may have a higher version also.




Ø  Install EM CLI client:
·         # mkdir /path/to/emcli

     # cd  /path/to/emcli/

 . # java -jar emclikit.jar client -install_dir=/path/to/emcli
·         # mkdir /path/to/oem12

    # cd /path/to/emcli

   #./emcli setup -dir=/path/to/oem12 -url=https://development2:7802/em/   -username=SYSMAN
Once you enter the above command, it will prompt you to put SYSMAN user password.



After certification chain settings we see message “Emcli setup successful”.

Ø  Set environment variable EMCLI_STATE_DIR to point to the setup directory for the OMS.
·          
      export  EMCLI_STATE_DIR=/path/to/oem12

Happy Reading... :)

Regards,
Saurabh