OCP Blog
Super DBA
Load XML file to oracle database in varchar2 type
by Vazha Mantua Wednesday, December 21, 2011 3:30 PM
    • Good Day all,
    • Today we show example of possibility of loading xml file to oracle10g database to text.
    • This method is very easy of getting some data from XML file to oracle database.
    • declare
    •   v_req   utl_http.req;
    •   v_resp  utl_http.resp;
    •   v_value VARCHAR2(1024);
    •   v_url varchar2(100);
  • begin
    • ---
    •   v_url:='http://192.0.0.16/test.xml';
    •   v_req := utl_http.begin_request(v_url);
    •   v_resp := utl_http.get_response(v_req);
    •   utl_http.read_text(v_resp, v_value);
    •   dbms_output.put_line(v_value);
    •   utl_http.end_response(v_resp);
    • ---
    • end;

Let see packages which we used on this example:

utl_http.begin_request:

Begins a new HTTP request. When the function returns, the UTL_HTTP  package has established the network connection to the target Web server, or the proxy server if a proxy server is to be used, and has sent the HTTP request line. The PL/SQL program should continue the request by calling some other API to complete the request.

utl_http.get_response:

Reads the HTTP response and processes the status line and HTTP response headers. The status code, reason phrase and the HTTP protocol version are stored in the response record.

utl_http.read_text:

Reads the HTTP response body in text form and returns the output in the caller-supplied buffer. The end_of_body exception will be raised if the end of the HTTP response body is reached. Text data is automatically converted from the response body character set to the database character set.

utl_http.end_response:

Ends the HTTP response completing the HTTP request and response. Unless a HTTP 1.1 persistent  connection is used in this request, the network connection is closed.

 

Tags: , ,

ORA-06544: PL/SQL: internal error, arguments: [55916]
by Vazha Mantua Saturday, December 17, 2011 12:10 PM

Good Day All,

Today we will discuss about error which appeared during import some data to oracle database release 11gR2 via impdp utility

when session try compile package we have got message like this

ORA-39083: Object type PACKAGE_BODY failed to create with error:
ORA-04052: error occurred when looking up remote object test_procedure
@TESTDB.WORLD
ORA-00604: error occurred at recursive SQL level 3
ORA-06544: PL/SQL: internal error, arguments: [55916], [], [], [], [], [], [], []
ORA-06553: PLS-801: internal error [55916]
ORA-02063: preceding 2 lines from TESTDB.WORLD

 

 

As you see error appear when oracle 11gR2 compile package which contain remote object dependence

Database version where is located test_procedure is 10.2.0.1, after upgrading it to 10.2.0.2 problem resolved. I think it’s a bug of version 11.2.0.1.

Tags: , ,

Oracle Database not started via srvctl with error CRS-2674, ORA-01034 if you installed Grid infrastructure with different user
by Vazha Mantua Monday, November 28, 2011 3:59 PM

if you try start database(version 11R2) with srvctl you should got error:

 

We got this case, when we created database with Database scripts in postDBCreation.sql

 

PRCR-1079 : Failed to start resource ora.test.db

CRS-5011: Check of resource "TEST" failed: details at "(:CLSN00007:)" in "/u0/app/grid/product/11.2.0/log/testmachine/agent/ohasd/oraagent_grid/oraagent_grid.log"

CRS-2674: Start of 'ora.test.db' on 'testmachine' failed

ORA-01034: ORACLE not available

ORA-27101: shared memory realm does not exist

Linux-x86_64 Error: 2: No such file or directory

Process ID: 0

Session ID: 0 Serial number: 0

 

Cause of this error is that grid agent is running with difference user. During the instance startup using srvctl, agent tries to modify the files under $ORACLE_HOME/dbs and $ORACLE_HOME/srvm/admin,

But Unfortunately Grid user doesn't have write permissions to these directories, startup could fail.

Workaround:

You can add permission to write on directories for Grid user with commands:

chmod 775 /u0/app/oracle/product/11.2.0/dbs

chmod 775 /u0/app/oracle/product/11.2.0/srvm/admin

Tags: , , ,

ORA-15027: active use of diskgroup precludes its dismount
by Vazha Mantua Thursday, November 24, 2011 10:33 PM

Good Day,

Today We discuss about Error:ORA-15027: active use of diskgroup precludes its dismount.This error occurred  we I tried to  drop disk group on ASM instance (version 11GR2).

Cause of this error is that ASM spfile is located on Diskgroup

Solution:

In that case you need to create a pfile from the spfile and then startup the ASM instance using the pfile.

1.Find location of AMS spfile with command SQL> show parameter spfile

2.Create pfile from spfile

3.Start ASM instance with pfile, for Example SQL> startup pfile=$ORACLE_HOME/dbs/init+ASM.ora

4.Drop Diskgroup

Good Luck!

Tags:

ORA-27029: skgfrtrv: sbtrestore returned error
by Vazha Mantua Wednesday, November 16, 2011 1:43 PM

Good Day All,

Today We observe a main error, which appear when you are trying restore database to new location via Veritas Netbackup soft

Problem:

When you tried restore for example your controlfile from autobackup which is stored on types, you got a error

ORA-27029: skgfrtrv: sbtrestore returned error

 

 

Cause:

With NetBackup on UNIX one can restore a database to a client other than the one that originally performed the backup. Symantec refers to this process as an 'alternate client restore' while Informix refers to this same process as an 'imported restore.By default configuration there is some restrictions for restoring to new server.

Workaround:

1.Remove restrictions for alternate client restores for all clients

Create the following f file /usr/openv/netbackup/db/altnames/No.Restrictions on the master server

2.Restrict the restore to / from certain clients

Create the following file on the NetBackup master server: /usr/openv/netbackup/db/altnames/<target_client_name>
where "<target_client_name>" is the name of the client that will receive the restore. This will allow this client to restore images from any other client.

 

 

Tags: ,

ORA-600 [qertbfetchbyrowid]
by Vazha Mantua Saturday, October 15, 2011 10:43 PM

Good Evening All,

 

We have updated on of our database from 10.2.0.2 to 10.2.0.4, after upgrading in 2 days in alert log we got internal error ORA-00600: internal error code, arguments: [qertbFetchByRowID], [], [], [], [], [], [], [] .

 

Cause of this error is index corruption

How can we resolve this problem?

Solution

1. Find appropriate trace file in udump for discover which SQL Statement is cause of problem.

2.In SQL statement find table which is cause of problem

3. with SQL plan of statement you can find appropriate index which is corrupted

4.Recreate index(not rebuild)!

 

After these 4 steps you problem will be resolved!

 

Good luck

Tags: ,

Problem During Installation Patch Set 10.2.0.4, PL/SQL: ORA-00904: "OTHER_XML": invalid identifier
by Vazha Mantua Sunday, September 18, 2011 2:49 PM

Good Day All,

 

 

 

After applying Patch Set 10.2.0.4 on Oracle RDBMS we discover that one of component was not updated correctly.Please see below:

 

Component Status Version HH:MM:SS
Oracle Database Server INVALID 10.2.0.4.0 00:19:27
Oracle XDK VALID 10.2.0.4.0 00:00:00
Oracle Workspace Manager VALID 10.2.0.4.3 00:01:22
Oracle Expression Filter VALID 10.2.0.4.0 00:00:16

 

In Installation Log file we found error PL/SQL: ORA-00904: "OTHER_XML": invalid identifier, while compiling package dbms_sqlpa.

In this post we will provide us how can you fix this problem.It’s very simple:

SQL>sqlplus / as sysdba
SQL>drop table plan_table;
SQL>@?/rdbms/admin/utlxplan
SQL>@?/rdbms/admin/prvtspao.plb
SQL>@?/rdbms/admin/utlrp.sql

Tags: ,

Installation Guide For Oracle TimesTen 11.2.1 In-Memory Database
by Vazha Mantua Thursday, August 25, 2011 5:38 PM

Good day all,

Today we will announced about a very nice type of database provided by Oracle. it’s Oracle TimesTen in memory database.

While installing a new technology, you can watch small presentation about this product, which I found on YouTube:

 

As you watched :

TimesTen is an in-memory relational database software product from Oracle. TimesTen is a high performance event-processing software component that enables applications to capture, store, use, and distribute information in real-time,
while preserving transactional integrity and continuous availability.

Full Articles about this product you can find on link: http://www.oracle.com/technetwork/database/timesten/overview/index.html

 

On This article we will provide you step by step procedure how install Oracle TimesTen 11.2.1 in Memory Database on Windows Platform 32 bit. Future We are going to publish some articles which deeply describe this product.

 

Let’s Start

 

Step 1: Install Software

First of all you should download installation package from Oracle:

You can download soft form link: http://www.oracle.com/technetwork/database/timesten/downloads/index.html

Run Setup.exe file , You can see in pictures steps , which you should done for installing software.

12

34

56

78

910

1112

13

After Installation software we should connect to DB

Step 2 Configure DSN

A Timesten Database is simply created by connecting to it, A first connection to an already-existing database results in loading the database in memory.Only A instance administrator can create TimesTen Database.

First of all We should configure system DSN for connecting to DB.

1. Go to control panel , then administrative Tools and Data Sources(ODBC)

2. Switch menu on System DSN part

3. Add System Data Source, named is my_ttdb

4. In Data Store Path browse directory in which you will store data

5. In Transaction Log Directory choose directory, where you will store log data.

6. Choose character set of DB, if you planning pin Oracle TimesTen Database on Oracle Database in future , choose character set, which you have an Oracle Database

7. In section First Connection you can set First connection attributes.

 

Step3 Connecting And Creating TimesTen Database

 

1. Enter ttisql at the OS Command prompt to launch ttIsql

2. For connecting to DB run connect “dns=my_ttdb”;

3. When the connect command returns , the database my_ttdb has been created and loaded into memory.

 

Now you can create users and object within database my_ttdb

 

Very useful instrument for TimesTen Database in SQL Developer. it’s very simple to use SQL Developer for TimesTen Database.

 

Status of Service you can get by command ttStatus from OS command prompt .

 

 

 

Tags: , , , , , ,

Oracle Announces Openworld 2011
by Vazha Mantua Wednesday, August 24, 2011 10:39 AM

Oracle OpenWorld is an annual Oracle event for business decision-makers, IT management. It is held in San Francisco, California; São Paulo, Brazil,Shanghai and China. The world's largest conference for Oracle customers and technologists.

 

Openworld in 2010:

About 41,000 people will attend this year’s expo, up from 37,000 in 2009, said Tania Weidick, vice president of event marketing for Redwood City, California-based Oracle. Those guests will spend more than $100 million on hotel rooms, restaurants and shopping, the company said.

 

Oracle welcomes proposals for presentations on a wide variety of topics, with content targeted at all levels of attendees from beginner to expert. Attendees come from around the world representing thousands of Oracle customers and partners. At this time Oracle is also accepting proposals for Oracle Develop content.

Oracle Openworld 2011 starts on 2 of October and ends 6 of October in Moscone Center 747 Howard Street, San Francisco , CA, USA

Detail Information about this event you can find on link : http://www.oracle.com/openworld/index.html

Tags: , ,

DBMS_PROFILER - Identify performance bottleneck
by Vazha Mantua Monday, August 22, 2011 3:13 PM

The dbms_profiler  package provides an interface to profile existing PL/SQL applications and identify performance bottlenecks. You can then collect and persistently store the PL/SQL profiler data.

The basic idea behind profiling with dbms_profiler is for the developer to understand where their code is spending the most time, so they can detect and optimize it.

Let see a example:

In Scott schema you have a procedure named create_client_information. Your goal is find which lines of source code of procedure is potential bottleneck .

Once you have run the profiler, Oracle will place the results inside the dbms_profiler tables.

First of all you should build appropriate tables and procedures for using dbms_profiler.

Step By Step:

 

1. Create necessary metadata for using dbms_profiler

   connect  as sysdba
run scripts @$ORACLE_HOME/rdbms/admin/profload.sql

    connect  as Scott 
run scripts @$ORACLE_HOME/rdbms/admin/proftab.sql

 

2. Delete unused data and start profiler for collecting information

delete from plsql_profiler_data;
delete from plsql_profiler_units;
delete from plsql_profiler_runs;
commit;

declare
  success binary_integer;
begin
  success := dbms_profiler.start_profiler(run_comment => 'test_run');

  if success <> 0 then
    dbms_output.put_line('could not start profiler');
  else
    dbms_output.put_line('Profiler started!');
  end if;
end;

3. Run procedure which you are testing now, for our example

begin  create_client_information(); end;

4. Stopping profiler

 select decode(
  dbms_profiler.stop_profiler,
  '0', 'Profiler Stopped',
  'Could not stop profiler')
from
  dual;

5. Get A results

 

First of all you should find which runid  is your case, you can get this information from statement:

select runid from plsql_profiler_runs
 
then you can get information with select

SELECT *
FROM PLSQL_PROFILER_DATA A,
     PLSQL_PROFILER_UNITS B
WHERE A.UNIT_NUMBER = B.UNIT_NUMBER
   AND A.RUNID = &ID

 

 With dbms_profiler you can find in which line of procedure you have a performance problem, but only find, it will not help you optimize code itself.

Tags: ,

Filter by APML

Calendar

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

View posts in large calendar

TextBox