The main idea of the blog is to share solutions for problems I faced before

Tuesday, 12 March 2013

Query to find the SCHEMA Size in oracle database

Here the query,
SELECT s.owner,SUM (s.BYTES) / (1024 * 1024 * 1024) SIZE_IN_GB
FROM dba_segments s
GROUP BY s.owner;

Monday, 11 March 2013

Hadoop Installtion on OE Linux --Cluster nodes

My requirement is to install and configure Hadoop on Oracle Linux. I followed the below procedure.

Prerequisites :

1. Java 1.6 or later must be installed on all machines(nodes).

2. ssh must be installed on all machines. Also check if sshd service is running on all machines(nodes).

3. Clock on all machines must be in sync with each other.

Installtion & Configuration :

1. CREATE USER : Create common user on all Linux nodes. Here are the steps to do that

I. Execute command : useradd hadoop

II. Execute command : passwd hadoop . It will ask to enter password, please set the desired password and confirm it. OR use Administration--> User and Groups.

2.       CONFIGURE SSH FOR PASSWORD LESS ENTRY :
SSHb/wClusterNodes
3.      DOWNLOAD :
LinkToDownloadSoftware

4.       INSTALL : Now will see hadoop-1.0.4.tar.gz created in your current directory. We need to unpack this file. [We will do it on one node only, later on will copy the directory to all other nodes, those steps are mentioned later in the document.]

i. Use command –“ tar xzf hadoop-1.0.4.tar.gz–C /home/hadoop/” . Now a directory will be created of name “hadoop-1.0.4” at /home/hadoop.

ii. Now we need to set the owner and group of this directory as “hadoop”. Execute command – chown –R hadoop:hadoop /home/hadoop/hadoop-1.0.4/

5. CREATE DIRECTORIES : [Do it on all nodes].

i. Execute command sudo mkdir /var/log/hadoop/

ii. Execute command – sudo chown hadoop:hadoop /var/log/hadoop/

iii. Execute command – sudo mkdir /usr/local/hadoopstorage

iv. Execute command – sudo chown hadoop:hadoop /usr/local/hadoopstorage/

v. Execute command - chmod -R 755 /usr/loacal/hadoopstorage

vi. Execute command – cd /usr/local/hadoopstorage/

vii. Execute command – mkdir datanode

viii. Execute command – mkdir namenode
6. CONFIGURATIONS : Now we need to set various configuration files according to our environment.

i. cd /home/hadoop/hadoop-1.0.4/conf/

ii. There will be on file of name “masters” . Open that file and write <IP> of master server(Namenode). Execute command - vi masters , then write IP and save file. In my case it was 192.16.12.248

iii. Now we need to add IPs of slaves(Datanodes) into “slaves” file. So execute – vi slaves. And write all IPs of slave machines. In my case it was as –

192.16.12.249
iv. Now we need to make changes in /hadoop-env.sh script. So execute vi hadoop-env.sh and change few of the lines as mentioned below :

a. We need to set JAVA_HOME variable. In my case it was like –

export JAVA_HOME=/usr/java/jdk1.7.0_03/

b. Set heap size as –

export HADOOP_HEAPSIZE=2000

c. Set log file path as –

export HADOOP_LOG_DIR=/var/log/hadoop

v. Now we need to set core-site.xml file. Here we need to set how namenode will be accessed i.e. we need to use IP/hostname of namenode. So add following block into your core-site.xml file under “configuration” tag
.

<name>fs.default.name</name>

</property>

My core-site.xml file looks like –

<?xml version=”1.0″?>

<?xml-stylesheet type=”text/xsl” href=”configuration.xsl”?>

<!– Put site-specific property overrides in this file. –>

<name>fs.default.name</name>

<value>hdfs://paxmsql102/</value>

</property>

</configuration>

vi. Now we need to set hdfs-site.xml file as below :

<name>hadoop.tmp.dir</name>

<value>/usr/local/hadoopstorage/</value>

<description>A base for other temporary directories.</description>

</property>

<value>/usr/local/hadoopstorage/namenode</value>

</property>

<value>/usr/local/hadoopstorage/datanode</value>

</property>

<name>dfs.replication</name>

<description> Default block replication. The actual number of replications can be specified when the file is created. The default is used if replication is not specified in create time.

</description>

</property>

</configuration>

vii. Now we are done with all configuration changes. So we need to copy all these changes to other nodes too. So either we can follow all above mentioned steps on every node, or we can just copy this hadoop-1.0.4 directory to all the nodes. For that we need to do –

a. scp –r /home/hadoop/hadoop-1.0.4 hadoop@<IP>:/home/hadoop/

b. Now go to that node where you just copy this stuff and check if “/home/hadoop/hadoop-1.0.4” directory has same user and group as of first node.

c. Repeat a,b for all other nodes.

Format and Manage cluster :

Till now we are done with setup, next step is to format this cluster. For that we need to execute following commands from Master(Namenode) :

a. cd /home/hadoop/hadoop-1.0.4/

b. ./bin/hadoop namenode –format

ii. START CLUSTER : HDFS is one point start i.e. we can start the hdfs cluster by just one command on master node. It
will start the whole cluster(i.e. namenode and all the datanodes) On Master(Namenode) execute following command :

./bin/start-dfs.sh

iii. STOP CLUSTER : On Master(Namenode) execute following command :

./bin/stop-dfs.sh

Setup Verification :

a. Execute – cd /home/hadoop/hadoop-1.0.4/

b. Execute – ./bin/hadoop fs –ls /

c. Execute – ./bin/hadoop fs –mkdir /test

d. Execute – ./bin/hadoop fs –chown –R hadoop:hadoop /test

e. Execute – ./bin/hadoop fs –ls /

Common Problems : attached in later

References : http://hadoop.apache.org/docs/r1.1.2/cluster_setup.html

Configuring SSH on Cluster Member Nodes

While Database clustering came across many issues. One of them is related to SSH connectivity b/w the nodes.
We got through the following oracle link and fixed it.

SSHconnectivityB/WclusterNodes

Wednesday, 6 March 2013

Ora-01652 : Unable to extend temp segment by 128 in tablespace USERS

That error occurs when failed to allocate an extent of the required number of blocks for a temporary segment in the tablespace indicated.
So we can this in two ways.

1.
Check the datafiles sizes..

SQL> select FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024 from dba_data_files;

FILE_NAME    TABLESPACE_NAME                BYTES/1024/1024

/u01/app/oracle/oradata/dev/users01.dbf

USERS                                  32767.5

/u01/app/oracle/oradata/dev/undotbs01.dbf

UNDOTBS1                                  8025

/u01/app/oracle/oradata/dev/sysaux01.dbf

SYSAUX                                    1340

/u01/app/oracle/oradata/dev/system01.dbf

SYSTEM                                    1150

/u01/app/oracle/oradata/dev/APEX_1344701438996813.dbf

APEX_1344701438996813                   5.0625

/u01/app/oracle/oradata/dev/BIAS1_mds.dbf

BIAS1_MDS                                  100

/u01/app/oracle/oradata/dev/BIAS1_biplatform.dbf

BIAS1_BIPLATFORM                            64

/u01/app/oracle/oradata/dev/BIAS2_odi_user.dbf

BIAS2_ODI_USER                             200

/u01/app/oracle/oradata/dev/DEV_biplatform.dbf

DEV_BIPLATFORM                              64

/u01/app/oracle/oradata/dev/DEV_mds.dbf

DEV_MDS                                    100

/u01/app/oracle/oradata/dev/ggs_data01.dbf

GGS_DATA                                   200

11 rows selected.

Then resize your datafile or add a new datafile to current tablespace
Resizing Datafile :
ALTER DATABASE DATAFILE ‘/u01/app/oracle/oradata/users.dbf‘ RESIZE *M;
Addind Datafile to existing Tablespace:
ALTER TABLESPACE USERS ADD DATAFILE ‘/u01/app/oracle/oradata/user_2.dbf SIZE 100M;

2.
Change the user’s default tablespace to a bigger one

SELECT * FROM Dba_Users;

  USERNAME USER_ID ACCOUNT_STATUS  DEFAULT_TABLESPACE
1 ASD         36     OPEN              SYSTEM
2 SYS          0     OPEN              SYSTEM
3 SYSTEM       5     OPEN              SYSTEM
4 ANONYMOUS   28     OPEN              SYSAUX

ALTER USER asd DEFAULT TABLESPACE users;

It changes “asd” schema’s default tablespace to USERS tablespace.

SELECT * FROM Dba_Users;

USERNAME USER_ID  ACCOUNT_STATUS  DEFAULT_TABLESPACE
1 ASD       36        OPEN             USERS
2 SYS        0        OPEN             SYSTEM
3 SYSTEM     5        OPEN             SYSTEM
4 ANONYMOUS 28        OPEN             SYSAUX

You can also do this per object : “alter table table_name move tablespace users; ”
You can also select the tablespace in the create script of the object.

Tuesday, 5 March 2013

“Prerequisite check “CheckActiveFilesAndExecutables” failed error” While issueing opatch apply

Problem
While applying interim patch, I got Prerequisite check “CheckActiveFilesAndExecutables” failed error and resolved the issue with the help of fuser.

oracle@biasore>opatch apply
 Invoking OPatch 11.1.0.6.6

Oracle Interim Patch Installer version 11.1.0.6.6
 Copyright (c) 2009, Oracle Corporation.  All rights reserved.

Oracle Home       : /u01/app/oracle/product/11.2.0/dbhome_1
 Central Inventory : /u01/app/oraInventory
 from           : /etc/oraInst.loc
 OPatch version    : 11.1.0.6.6
 OUI version       : 11.2.0.1.0
 Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2013-03-05_23-47-17PM.log

Patch history file :
/u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch_history.txt

Applysession applying interim patch '11678127' to OH '/u01/app/oracle/product/11.2.0/dbhome_1'
 Verifying environment and performing prerequisite checks...
 Prerequisite check "CheckActiveFilesAndExecutables" failed.
 The details are:

Following executables are active :
 /u01/app/oracle/product/11.2.0/dbhome_1/lib/libclntsh.so.11.1
 ApplySession failed during prerequisite checks: Prerequisite check "CheckActiveFilesAndExecutables" failed.
 System intact, OPatch will not attempt to restore the system

Recommended actions: OPatch needs to modify files which are being used by some processes.

OPatch failed with error code74

Cause
Something is using oracle library.
Solution
I had solved the issue first looking at the log file and saw that the inuse control made by fuser, this is also a clue for me to find who is using the library file.

oracle@biasore>/sbin/fuser /u01/app/oracle/product/11.2.0/dbhome_1/lib/libclntsh.so.11.1
 /u01/app/oracle/product/11.2.0/dbhome_1/lib/libclntsh.so.11.1: 29279m
 oracle@biasore>

oracle@biasore>ps -ef |grep 29279
  oracle   22813  3717  0 21:18 pts/13   00:00:00 grep 29279
Here I found some thing is running 29279 pid.
 oracle@biasore>kill -9 29279

After than I had successfully applied the patch.

This document describes how you can install the one-off patch for bug# 11678127 on your Oracle Database 11g Release 11.2.0.1.0

(1) Prerequisites
--------------------
Before you install or deinstall the patch, ensure that you meet the following requirements:
Note: In case of an Oracle RAC environment, meet these prerequisites on each of the nodes.

1. Ensure that the Oracle Database on which you are installing the patch or from which you are rolling back the patch is Oracle Database 11g Release 11.2.0.1.0.

2. Oracle recommends you to use the latest version of OPatch.
If you do not have the latest version, then follow the instructions outlined in the My Oracle Support note 224346.1 available at:
https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=224346.1

3. Ensure that you set the ORACLE_HOME environment variable to the Oracle home of the Oracle Database.

4. Ensure that you set the PATH environment variable to include the location of the unzip executable, and the <ORACLE_HOME>/bin and the <ORACLE_HOME>/OPatch directories present in the Oracle home of the Oracle Database.

5. Ensure that you verify the Oracle Inventory because OPatch accesses it to install the patches. To verify the inventory, run the following command. If the command displays some errors, then contact Oracle Support and resolve the issue.
$ opatch lsinventory

6. Ensure that you shut down all the services running from the Oracle home.

Note:
- For a Non-RAC environment, shut down all the services running from the Oracle home.

(2) Installation
-----------------
To install the patch, follow these steps:
Note: In case of an Oracle RAC environment, perform these steps on each of the nodes.

1. Maintain a location for storing the contents of the patch ZIP file. In the rest of the document, this location (absolute path) is referred to as <PATCH_TOP_DIR>.

2. Extract the contents of the patch ZIP file to the location you created in Step (1). To do so, run the following command:
 $ unzip -d <PATCH_TOP_DIR> p11678127_112010_Linux-x86-64.zip

3. Navigate to the <PATCH_TOP_DIR>/11678127 directory:
 $ cd <PATCH_TOP_DIR>/11678127

4. Install the patch by running the following command:
 $ opatch apply

 Note:
 When OPatch starts, it validates the patch and ensures that there are no conflicts with the software already installed in the ORACLE_HOME of the Oracle Database. OPatch categorizes conflicts into the following types: 
 - Conflicts with a patch already applied to the ORACLE_HOME - In this case, stop the patch installation and contact Oracle Support Services.
 - Conflicts with a patch already applied to the ORACLE_HOME that is a subset of the patch you are trying to apply  - In this case, continue with the patch installation because the new patch contains all the fixes from the existing patch in the ORACLE_HOME. The subset patch will automatically be rolled back prior to the installation of the new patch.

5. Start the services from the Oracle home.

(3) Deinstallation
---------------------
To deinstall the patch, follow these steps:
Note: In case of an Oracle RAC environment, perform these steps on each of the nodes.

1. Navigate to the <PATCH_TOP_DIR>/11678127 directory:
 $ cd <PATCH_TOP_DIR>/11678127

2. Deinstall the patch by running the following command:
 $ opatch rollback -id 11678127

3. Start the services from the Oracle home.


(4) Bugs Fixed by This Patch
---------------------------------
The following are the bugs fixed by this patch:
  11678127: OCIDESCRIBEANY RETURNS ORA:24323 FOR AN OBJECT TYPE USE CASE

Tuesday, 25 December 2012

Difference between DROP,Truncate and Delete in Oracle?

Difference between Truncate and Delete in Oracle?

Answer:

1.TRUNCATE is a DDL command and cannot be rolled back. All of the memory space is released back to the server.

2. DELETE is a DML command and can be rolled back.

3. TRUNCATE : You can't use WHERE clause and DELETE : You can use WHERE clause

4. Both commands accomplish identical tasks (removing all data from a table), but TRUNCATE is much faster.

5.Truncate: Drop all object's statistics and marks like High Water Mark, free extents and leave the object really empty with the first extent.Delete: You can keep object's statistics and all allocated space.

6. In case of TRUNCATE ,Trigger doesn't get fired.But in DML commands like DELETE .Trigger get fired.

7. Drop command will delete the entire row also the structure.But truncate will delete the contenets only not the strucure, so no need to give specifications for another table creation.

8. Drop command remove the table from data dictionary. This is the DDL statement. We can not recover the table before Oracle 10g. But Oracle 10g provide the command to recover it by using the command (FLASHBACK)

9. DROP and TRUNCATE are DDL commands, whereas DELETE is a DML command. Therefore DELETE operations can be rolled back (undone), while DROP and TRUNCATE operations cannot be rolled back.

From Oracle 10g a table can be "undropped". Example:

SQL> FLASHBACK TABLE emp TO BEFORE DROP;
Flashback complete.

PS: DELETE will not free up used space within a table. This means that repeated DELETE commands will severely fragment the table and queries will have to navigate this "free space" in order to retrieve rows.

10. Truncate will not use the undo TBS, whereas a delete will.

Friday, 14 December 2012

OHS and It's Configuration

mod_wl_ohs: is a module in Oracle HTTP Server 11g R1 which allows requests to be proxied from Oracle HTTP Server (OHS) to Oracle WebLogic Server.

mod_weblogic: This module is part of Apache HTTP Server and allows requests to be proxied from Apache HTTP Server to Oracle WebLogic Server.

For difference between mod_wl_ohs and mod_weblogic click here

Things good to know about configuring OHS infront of weblogic

1. You can use Fusion Middleware control /em (register OHS with weblogic Server to access it from control) , steps here or directly update httpd.conf (steps given below) to configure mod_wl_ohs
2. If weblogic server is clustered then mod_wl_ohs uses simple round-robin to forwards requests from HTTP Server to all available weblogic servers.
  mod_wl_ohs directs HTTP requests containing a cookie, URL-encoded session, or a session stored in the POST data to the server in the cluster that originally created the cookie.

3. mod_wl_ohs (as of 11gR1) only support container level failover and NOT application level failover. mod_wl_ohs continues to route requests to a down application as long as the managed server is up and running.

4. Configuration file of mod_wl_ohs is $INSTANCE_HOME/ config/ OHS/ <component_name>/ mod_wl_ohs.conf and included in $INSTANCE_HOME/ config/ OHS/ <component_name>/ httpd.conf (entry like
include “${ORACLE_INSTANCE}/ config/ ${COMPONENT_TYPE}/${COMPONENT_NAME}/ mod_wl_ohs.conf”)

5. mod_wl_ohs module file is available at $ORACLE_HOME/ ohs/ modules/ mod_wl_ohs.so
6. You can either use URL like /console using location directive (<Location /console>) or MatchExpression directive in mod_wl_ohs.conf to forward requests from HTTP Server to WebLogic Server.

7. While starting OHS on Windows, if you see error like
—
C:/atul/ fmw/ instances1/ config/ OHS/ ohs1/ mod_wl_ohs.conf:
Cannot load C:/ atul/ fmw/ ohs/ modules/ mod_wl_ohs.so into server: The specified module could not be found
—
Check mod_wl_ohs.so exists in specified location, if yes then
Copy “$ORACLE_HOME\ oui\ lib\ win32\ msvcp71.dll” to “c:\ windows\ system32″ and try again
.
Configure HTTP Serer infront of WebLogic Server
1. Install WebLogic Server and define server listening on port XXXX (7001 in this example)
2. Install Oracle HTTP Sever 11g steps here
3. Modify mod_wl_ohs.conf
$ORACLE_INSTANCE/ config/ <COMPONENT_TYPE>/ <COMPONENT_NAME>/ mod_wl_ohs.conf
a) For weblogic single instance
<Location /console>
    SetHandler weblogic-handler
    WebLogicHost server1
    WeblogicPort 7001
</Location>
* This will forward /console from HTTP server to /console on WebLogic Server server1:7001

b) For Weblogic instances in cluster
<Location /myServerURL>
    SetHandler weblogic-handler
    WebLogicCluster server1:7010,server2:7010
</Location>
* This will forward /myServerURL from HTTP server to /myServerURL on WebLogic Cluster server1:7010 and server2:7010

4. Restart HTTP Server
$INSTANCE_HOME/ bin/ opmnctl restartproc ias-component=ohs1
5. Test that you can access application deployed on Weblogic using Oracle HTTP Server like
http://servername:ohs_http_port/console

Source: http://onlineappsdba.com/index.php/2009/09/23/configure-oracle-http-server-infront-of-oracle-weblogic-server-mod_wl_ohs/
Thanks to Atul Kumar