Wednesday, December 28, 2005

Never Use Raid 5 for Oracle disks

I have never ever before seen such an excellent collection of articles on the Topic Never Use Raid 5 for your Oracle disks!

Sphere: Related Content

Wednesday, December 21, 2005

AspectJ 1.5.0 released

News here and here.

Sphere: Related Content

Monday, December 19, 2005

ora-1000 errors, "Maximum open cursors exceeded."

When ora-1000 errors, "Maximum open cursors exceeded." happens most Oracle DBAs think "damned java developers, close your statements!!!". Which is true in 99% of the cases. However sometimes there might be more to it than some statements left open by developers.

Natalka Roshak has an article called "Monitoring Open and Cached Cursors" that helps with some of the debugging pain. Additionally you may use the trace to get a snapshot of the offending cursors when ORA-1000 is thrown.

Enable the errorstack by using alter system or alter session:

alter system set events = '1000 trace name ERRORSTACK level 3'

alter session set events = '1000 trace name ERRORSTACK level 3'

disable by settin it back to off as in ....ERRORSTACK off'


Sphere: Related Content

Friday, December 16, 2005

Content Services Backup

I had a question about how to do enable single file backup from Oracle Content Services for deleted files. The answer is that Oracle Content Services has a two level trash. When users delete a file it goes into their Trash folder - and they can recover files from the Trash themselves. If the user empty their Trash folder the contents are moved to the Archive. The subscriber adminstrator can recover files from the Archive. Each Site contains an Archive folder. Depending on how the Site has been configured, items in the Archive may be automatically deleted after a specified period of time.

Full size backups are handled by RMAN as usuall and the new features in R1 and R2 apply, I hope that you read up on incremental backups and the flash recovery area.

Sphere: Related Content

Wednesday, December 14, 2005

DBDebunk site

Pascal and Date's DBDebunk site has a lot of good information in it, and there is also a lot of fun!

Sphere: Related Content

Oracle donates ADF Faces to Apache project

Jonas Jacobi and Lucas Jellema both Blog about that Oracle will donate the ADF Faces technology to the Open Source community, as Cherokee - a subproject under the Apache MyFaces project, see

http://www.orablogs.com/jjacobi/archives/001533.html
http://technology.amis.nl/blog/?p=947

This is very good news!

Sphere: Related Content

Monday, December 12, 2005

How to register a XSD schema and use it in a object table

Just a quick example on how to use XMLDB with xml schemas. For more info check the documentation out or purchase some books.

First create a user for your table.

create user mytest identified by nice
default tablespace USERS
temporary tablespace TEMP;
grant connect,resource to mytest;
grant alter session to mytest;
grant create type to mytest;
grant create table to mytest;
grant create procedure to mytest;
grant create trigger to mytest;
grant create view to mytest;

Connect with the new user

connect mytest/nice

Register a schema

BEGIN
DBMS_XMLSCHEMA.registerSchema('region.xsd',
'
xmlns:xsd="http://www.w3.org/2001/XMLSchema">








', TRUE, TRUE, FALSE);
END;

anonymous block competed

Check that the schema was registred

select * from user_xml_schemas

SCHEMA_URL |LOCAL|SCHEMA |INT_OBJNAME |QUAL_SCHEMA_URL |HIER_TYPE |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
region.xsd |YES | |XDBgMQGI96d9zgQAB/AQBiwA== |http://xmlns.oracle.com/xdb/schemas/MYTEST/region.xsd |CONTENTS |

1 rows selected

Create a table

CREATE TABLE region_xmltab OF XMLType
XMLSchema "region.xsd" ELEMENT "region";


CREATE TABLE succeeded.

Insert some XML

INSERT INTO region_xmltab
VALUES (XMLType(
'
xsi:noNamespaceSchemaLocation="region.xsd">
5
Japan
'));


1 rows inserted

Create a OV

CREATE OR REPLACE VIEW region_xmltabv OF XMLType
XMLSchema "region.xsd" ELEMENT "region"
AS SELECT value(r) FROM region_xmltab r;

Get the some information and the XML out of the view

select v.ISSCHEMABASED(),v.GETSCHEMAURL(), v.GETSTRINGVAL() from region_xmltabv v

V.ISSCHEMABASED() |V.GETSCHEMAURL() |V.GETSTRINGVAL() |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 |region.xsd |
5
Japan

|

1 rows selected


Sphere: Related Content

Using 10gR2 Flashback Database Restore Point for testing

Oracle 10g R2 introduces a new feature to the already almighty flashback database - Restore points. The concept is the same as for SQL transaction savepoints.

So what is the benefits of this - many I would say, one is for testing. In todays world of SOA testing your integrated systems is harder then ever... first you spend days on setting your data up for your testcases, then you back them up. Run your 5 minute test case, and then you are back to recovering your backups for your 25 systems or so. So here comes Oracle 10g R2 Flashback database to the rescue.

In 10g R1 you needed to flashback to a certain SCN or timestamp, which is easy enough. With savepoints you create a restore point after you have setup your test case. For example:

SQL>create restore point testcase_a_rp guarantee flashback database;

And when you want to flashback to this particular database state, you issue:

SQL> flashback database to restore point testcase_a_rp;

A testcase setup would then be the following setup for each database:

  1. Setup the database schema to the state needed prior to test run
  2. Create a restore point
  3. Run tests
  4. Evaluate tests
  5. Flashback database to restore point
  6. Go to 3 to run test again
If you are confused like me, you might forget your savepoint names. That is allright as long as you remember the view name v$restore_point. This view will tell you all about your restore points.

To do the above flashback needs to be turned on. See the documentation for your version of the database for instructions on how to do this or look at this article.

Sphere: Related Content