OCP Blog
Super DBA
Flush Single SQL statement from shared pool
by vazha mantua Monday, September 16, 2013 4:04 PM

Sometime on database we observed SQL statements which change SQL_Plans, cause of this may be various situation, for example recalculation  table statistics, or bind mismatch, etc. Sometime we simple want to delete bad SQL_Plan from shared pool, of course we can do it with flushing whole shared pool, but the best way is flushing single SQL statement.

First of all we should find address and hash value of statement, with this command:

select address||','||hash_value
  from v$sqlarea
  where sql_id like '1u1hcw5c5t8r3';

after that, we copy results to argument to procedure sys.dbms_shared_pool.purge

Example

begin
   sys.dbms_shared_pool. purge('00000004FA8BA948,1482466019','C',1);
end;  

That’s all.

Tags:

ORA-39002: invalid operation ORA-39070: Unable to open the log file
by vazha mantua Tuesday, December 18, 2012 2:30 PM

Hello All,

 

Today we resolved problem with error

ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation

This error we got when use ASM file system as directory. expdp can’t create log file on ASM disk storage.

For avoiding creating expdp log file on ASM use second directory with standard  file system

Solution :

create directory EXPDP_LOG as '/u0/app/oracle/logs'

and use expdp parameter LOGFILE=EXPDP_LOG :exp_dbh.log

Deploy jboss application server on oracle enterprise manager
by vazha mantua Wednesday, October 10, 2012 10:57 AM

Good Day,

Today we will observe how can we add jboss applications on OEM targets. Beginning with Grid Control OMS 10.2.0.3 and Grid Control Agent 10.2.0.3, the Third Party Plug-in for JBoss monitoring is available.

Supported version of jboss - JBoss are 4.0.x and above:  Supported version of OEM agent: 10.2.0.3 or higher, it will be perfect if you use version 11g

So what we did:

1. Install OEM agent 11g on target server , where is installed jboss service

2. Open  JNDI Port of JBoss Application Server on  the Agent Host. JNDI port is 1099

3.The Management ejb (MEJB) must have been deployed to JBoss Application Server, for this task you should find ejb-management.jar file under <JBoss_HOME>/docs/examples/jmx and  copy the jar file to the deploy folder of the running JBoss Application Server.

After this procedure go to EOM on Target Page<Middleware. and chose Jboss Apliication Server and fill following form:

jboss

 

You should fill server host,Install home and JNDI port(1099). After that you should add credential of server and OEM automatic find appropriate jboss service.

 

Most popular issue after deploying is metric error like this : Java Virtual Machine context could not be obtained. Cause of this issues is  memory parameters are not enough for resource usage.

Solution:

1.Stop OEM agent

2.Backup and edit agent11g/sysman/config/emd.properties file

3.Change from :
                           agentJavaDefines=-Doracle.dms.refresh.wait.time=1000 -DUrlTiming.UseJSSE=true-Dnetworkaddress.cache.ttl=1800 -Djava.awt.headless=true
                           to:
                           agentJavaDefines=-Xms256m -Xmx512m -Doracle.dms.refresh.wait.time=1000-DUrlTiming.UseJSSE=true -Dnetworkaddress.cache.ttl=1800-Djava.awt.headless=true

4. Start OEM agent

 

And Enjoy with OEM!

 

 

Welcome to OCP Blog
by vazha mantua Friday, April 20, 2012 10:00 AM

If you see this post it means that BlogEngine.NET 2.6 is running and the hard part of creating your own blog is done. There is only a few things left to do.

Tags: ,

Installing Oracle Management Agent 11g with response file
by vazha mantua Monday, November 8, 2010 12:11 PM
Hello Again,

As you know, we installed Oracle EM Grid control 11g in Linux server 2 day ago, now we are trying install management agent in server where is our DB's

First of all download install packages from:
http://www.oracle.com/technology/software/products/oem/htdocs/agentsoft.html

Our example is for :
Agent Software for 64-bit Platforms,Linux x86-64

1. copy zip files in server

2. unzip Linux_x86_64_Grid_Control_agent_download_11_1_0_1_0.zip file(for example /0 folder)

3. make changes on file additional_agent.rsp located linux_x64/response sub folder.

We should set some init parameters

SECURITY_UPDATES_VIA_MYORACLESUPPORT=FALSE
DECLINE_SECURITY_UPDATES=TRUE
ORACLE_AGENT_HOME_LOCATION=/u1/app/oracle/agent11g(you can change as you wish)
OMS_HOST=OEMserver (host where is installed EM grid 11g)
OMS_PORT=4889
AGENT_REGISTRATION_PASSWORD=****** (Agent registration password, if you set it during Oracle EM installation )

4. execute runinstaller with specified parameters
./runInstaller -silent -responseFile /0/linux_x64/response/additional_agent.rsp


If you have some question please contact me by e-mail: vmantua@gmail.com

Tags: , ,

Install Grid Control 11g on linux CentOS(32 bit)
by vazha mantua Monday, November 8, 2010 11:56 AM

Hi All,
I'm writing manual of installation grid control 11g in Linux (32 bit ). Installation of this product not like early versions of grid control, cause of this is that grid control instead of oracle application server is use oracle web logic server now.


Let's Start!

First of all we should install oracle DBMS and create database for repository OEM.

A. Installation DBMS

1. Install rpm’s

Login on server with user root
 
1.1 run and install rpm’s with command rpm -Uvh

libaio-devel-0.3.106-3.2.i386.rpm

unixODBC-2.2.11-7.1.i386.rpm

unixODBC-devel-2.2.11-7.1.i386.rpm

unixODBC-devel-2.2.11-7.1.x86_64.rpm

1.2 delete old version of rmp ksh

rpm -e ksh-20060214-1.7.i386

1.3 Install new version of ksh: rpm –Uvh pdksh-5.2.14-36.el5.i386.rpm

2. Configuring Linux for Oracle

mkdir -p /u0/app/oracle

groupadd oinstall
groupadd dba
useradd –s /bin/bash –d /u0/app/oracle –g oinstall –G dba oracle

passwd oracle
chown -R oracle:oinstall /u0/app
chmod -R 777 /u0/app 
 

change /etc/sysctl.conf, add lines:
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 536870912
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048586



Reboot system

3. Install Oracle software

3.1 Download from http://www.oracle.com/technology/software/products/database/index.html

Installation packages

Oracle Database 11g Release 2 (11.2.0.1.0) for Linux x86

<!--[if !supportLists]--> <!--[endif]-->Linux_11gR2_database_1of2.zip (1,285,396,902 bytes) (cksum - 2237015228)

<!--[endif]-->Linux_11gR2_database_2of2.zip (995,359,177 bytes) (cksum - 2649514514)

3.2 login on server with user oracle , unzip zip files and run runinstaller

B. Install Oracle Web Logic Server And Grid Control


1. <!--[endif]-->Install Oracle RDMS 11g
Please see part A


2.
<!--[endif]-->Install Oracle Web Logic server


2.1
<!--[endif]-->download installation package http://www.oracle.com/technology/software/products/ias/htdocs/wls_main.html Oracle WebLogic Server 11gR1 (10.3.2) - Package Installer

2.2 Copy wls1032_linux32.bin file in server and run command chmod a+x *.bin

2.3 <!--[endif]-->
Run ./ wls1032_linux32.bin for installation web logic server

3. Create DB , configure listener.ora file and tnsnames.ora file for connecting DB(You can use dbca utility)

4.Change Oracle instance parameters and increase size of UNDO TBS: 
  

ALTER system SET session_cached_cursors = 200 scope=spfile;
ALTER system SET log_buffer = 10485760 scope=spfile;
ALTER system SET processes = 500 scope=spfile;
ALTER system SET open_cursors = 300 scope=spfile;
   Restart DB and run command :
   ALTER DATABASE DATAFILE ‘/u1/oradata/OEMREP/undotbs01.dbf’ RESIZE 250M;



5. Download Grig control soft from
       

http://www.oracle.com/technology/software/products/oem/htdocs/linuxsoft.html
      
<!--[endif]-->GridControl_11.1.0.1.0_Linux_1of3.zip (1,346,827,162 bytes) (cksum - 4036510904)   
<!--[if !supportLists]-->           <!--[endif]--><!--[endif]-->GridControl_11.1.0.1.0_Linux_2of3.zip (1,504,676,900 bytes) (cksum - 2185370223)   
<!--[if !supportLists]-->       <!--[endif]-->GridControl_11.1.0.1.0_Linux_3of3.zip (1,336,577,399 bytes) (cksum - 4010010869)
  
Copy these files in server unzip and run runinstaller

6. After install

Navigate to the Oracle home of the OMS and run the following command to see a message that confirms that OMS is up and running.

$<OMS_HOME>/bin/emctl status oms

Go to the Oracle home of the Management Agent and run the following command to see a message that confirms that the Management Agent is up and running.

$<AGENT_HOME>/bin/emctl status agent

Run the following command to see a message that confirms that EMD upload completed successfully.

[$<AGENT_HOME>/bin/emctl upload


6.1. Adding ports to firewall rules.

  • Don’t forget to open ports on your firewall:

Default Ports to be opened:

Enterprise Manager Upload Http Port

4889

Enterprise Manager Upload Http SSL Port

4900

Enterprise Manager Central Console Http SSL Port

7799

Enterprise Manager Central Console Http Port

7202

Enterprise Manager Central Console Http Port

7788

Node Manager Http SSL Port

7403

Managed Server Http SSL Port

7301

Oracle Management Agent Port

3872

Admin Server Http SSL Port

7101


Login to EM Grid Control console (sysman/password)

SSL – standard approach:
https://hostname.local:7799/em

It’s also possible to avoid SSL connection. To do that, first you must unlock http access:

[oracle@hostname bin]$ ./emctl secure unlock -console
Oracle Enterprise Manager 11g Release 1 Grid Control
Copyright (c) 1996, 2010 Oracle Corporation. All rights reserved.
Enter Enterprise Manager Root (SYSMAN) Password :******
OMS Console is unlocked. HTTP ports too can be used to access console.
http://hostname.local:7202/em/console/home


Login without SSL (default port 7202):
http:// hostname.local:7202/em

7. Start/stop the environment: On OMS Server

  • to start Oracle Enterprise Manager Grid Control services:

$<OMS_HOME>/bin/ emctl start oms –all

  • to stop Oracle Enterprise Manager Grid Control services:

$<OMS_HOME>/bin/ emctl stop oms –all



Tags:

Veritas Netbackup problem for Oracle 11gR2
by vazha mantua Monday, November 8, 2010 11:52 AM

 

Problem:

After migration DB in Oracle 11g, we found out problem with backuping DB over Veritas Netbackup.
In Alert log we saw message likes this :
ORA-07445: exception encountered: core dump [strcpy()+16] [SIGSEGV] [ADDR:0x12][PC:0x3E16079190] [Address not mapped to object] []

On oracle metalink we found that:

The latest version of Netbackup is not compatible with 11gR2
we have client version 6.5.2 which was not supported for 11gR2
also you can read it in this article :
Document ID: 337527
RMAN-10038 error when attempting to backup Oracle version 11.2 using NetBackup 6.x.
URL: http://support.veritas.com/docs/337527


Solution:

We download Veritas Netbackup client patch 6.5.5 , for Linux Red hat. you need to download this patches: (You should extract these patches in a same location for avoiding install problem)

NB_CLT_6.5.5_337786.tar
NB_6.5.5.linuxR_x86_337780.tar
NB_JAV_6.5.5_337809.tar

Also you should download this patch:

NB_6.5.5_ET1940073_1_346033.zip(Bug fixing patch for oracle , netbackup version 6.5.5)
install instruction for this bug : http://www.symantec.com/business/support/index?page=content&id=TECH64620
After applying this patch problem resolved

Tags:

library cache pin
by vazha mantua Monday, November 8, 2010 11:50 AM
When you try compile package on Oracle DB 10g , compile process may be hang, reason of this is any session which using this package, on this scenario you should see wait event like that library cache pin.
The library cache pin Oracle metric takes place if the process wants to pin an object in memory in the library cache for examination, ensuring no other processes can update the object at the same time. The library cache pin wait usually happens when you are compiling or parsing a PL/SQL object or a view.

use this select to see which session lock your compile process. run it with sys user!

select decode(lob.kglobtyp, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
11, 'PACKAGE BODY', 12, 'TRIGGER',
13, 'TYPE', 14, 'TYPE BODY',
19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB',
22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE',
28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE',
32, 'INDEXTYPE', 33, 'OPERATOR',
34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION',
40, 'LOB PARTITION', 41, 'LOB SUBPARTITION',
42, 'MATERIALIZED VIEW',
43, 'DIMENSION',
44, 'CONTEXT', 46, 'RULE SET', 47, 'RESOURCE PLAN',
48, 'CONSUMER GROUP',
51, 'SUBSCRIPTION', 52, 'LOCATION',
55, 'XML SCHEMA', 56, 'JAVA DATA',
57, 'SECURITY PROFILE', 59, 'RULE',
62, 'EVALUATION CONTEXT',
'UNDEFINED') object_type,
lob.KGLNAOBJ object_name,
pn.KGLPNMOD lock_mode_held,
pn.KGLPNREQ lock_mode_requested,
ses.sid,
ses.serial#,
ses.username
FROM
x$kglpn pn,
v$session ses,
x$kglob lob,
v$session_wait vsw
WHERE
pn.KGLPNUSE = ses.saddr and
pn.KGLPNHDL = lob.KGLHDADR
and lob.kglhdadr = vsw.p1raw
and vsw.event = 'library cache pin'

Tags: , ,

How Find Large Object in DB
by vazha mantua Monday, November 8, 2010 11:48 AM
This is small script which provide how oracle developer can find large object in oracle DB for a schema.

script :

select round(sum(s),2),t
from (
select sum(b.bytes)/1024/1024/1024 s, a.table_name t from dba_indexes a, dba_segments b
where b.segment_type in ('INDEX','INDEX PARTITION')
and a.index_name=b.segment_name
and a.owner=&SCHEMA
group by a.table_name

union all

select sum(a.bytes)/1024/1024/1024 s,a.segment_name t from dba_segments a
where a.owner=&SCHEMA
and a.segment_type in ('TABLE','TABLE PARTITION')
group by a.segment_name

union all

select sum(b.bytes)/1024/1024/1024 s,a.table_name t from dba_lobs a, dba_segments b
where a.owner=&SCHEMA
and a.segment_name=b.segment_name
group by a.table_name

)
group by t
order by 1 desc

Tags: , ,

Run ADDM Statistic
by vazha mantua Monday, November 8, 2010 11:27 AM

The Automatic Database Diagnostic Monitor (ADDM) analyzes data in the Automatic Workload Repository (AWR) to identify potential performance bottlenecks. For each of the identified issues it locates the root cause and provides recommendations for correcting the problem. An ADDM analysis task is performed and its findings and recommendations stored in the database every time an AWR snapshot is taken provided the STATISTICS_LEVEL parameter is set to TYPICAL or ALL. The ADDM analysis includes

Run ADDM report user can with Oracle Enterprise manager, but there i want tell you about script which provided same information. After running this script in PL/SQL Developer you can get ADDM report result.

1. Report Interval

you should decide on what period do you want see ADDM report, thats why you run this select on DB :

select * from dba_hist_snapshot
order by snap_id

and now you know begin and end snap_id(snap_id_1, snap_id_2)

2. Create ADDM Task

BEGIN
-- Create an ADDM task.
DBMS_ADVISOR.create_task (
advisor_name => 'ADDM', 
task_name => 'Test Report',
task_desc => 'Test Report');

-- Set the start and end snapshots.
DBMS_ADVISOR.set_task_parameter (
  task_name =>'Test Report',
parameter => 'START_SNAPSHOT',
  value => snap_id_1);

DBMS_ADVISOR.set_task_parameter (
  task_name => 'Test Report',
parameter => 'END_SNAPSHOT',
  value => snap_id_2);

-- Execute the task.
DBMS_ADVISOR.execute_task(task_name =>'Test Report');

END;

3. Get Report

SELECT DBMS_ADVISOR.get_task_report('Test Report ','TEXT', 'TYPICAL', 'ALL') AS report
FROM dual;

In BLOB you can see result of report

 

4. Delete Report

for deleting report you can run this statement:

BEGIN
  DBMS_ADVISOR.delete_task ( task_name => 'Test Report');
END;

Tags:

Filter by APML

Calendar

<<  October 2017  >>
MoTuWeThFrSaSu
2526272829301
2345678
9101112131415
16171819202122
23242526272829
303112345

View posts in large calendar

TextBox