Why Oracle Database 12c?

Oracle has recently launched the 12c version of the database. Though the huge list of new features and options, definitely the most important one in this version is the pluggable database.

There is an entirely new architectural concept to make room for the cointainer - pluggable relationship, but why the trouble?

Do you know that feeling when you have several databases created on a single machine and the command ps -ef | grep oracle throws you a ridiculous list of processes?
 In the case that one database is not loaded and the other one has a huge spike, in that moment did you ever think that the memory could be managed better on that machine and shared between the databases?

With the Oracle Database 12c there is the concept of a container database. As the name says it is really a container where you can create/plug/clone/unplug/delete... databases(called pluggable databases).  The interesting fact is that the container manages all the things (processes, memory, some tablespaces,) that can be shared between the pluggable databases.
This makes it easier to manage several databases on the same machine.

In the case of prod, dev, test environment, how is it to clone the prod/dev database to create a new test database?
With the Oracle Database 12c is a matter of making the process easier. In staid of a large RMAN script is just a short command:
CREATE PLUGGABLE DATABASE PDB_TEST FROM PDB_DEV;

Well the benefits are much more then these but let me just keep the mystery until you test it.
http://www.oracle.com/technetwork/database/enterprise-edition/overview/index.html

Three ways to tail the alert log in Oracle 11g

The Oracle 11g database offers us several choices in reading the alert log of the database.

1. You can read the last 200 lines from sqlplus using the folowing sql.

select * from (
select record_ID,message_text from X$DBGALERTEXT
order by record_ID desc
)
where rownum <=200
order by record_ID asc

It's something like tail -n 200 alert_SID.log


2. Oracle 11g introduces the adrci, a diagnostic utility so that you can see the alerts from different logs. It also can be customized very easely. The simpelest way to read the last 200 lines from the alert log with the follow option is:


[oracle@oracle11gdb ~]$ adrci


ADRCI: Release 11.1.0.6.0 - Beta on Wed Mar 30 14:59:02 2011


Copyright (c) 1982, 2007, Oracle.  All rights reserved.


ADR base = "/db/app/oracle"
adrci> show log -tail 200 -f



3. The clasic way, using the tail command, but now the file is in a differen location:

$ORACLE_BASE/diag/rdbms/DBNAME/ORACLE_SID/trace/alert_SID.log

so, the command might be:

tail -fn 200  $ORACLE_BASE/diag/rdbms/db11g/db11g/trace/alert_db11g.log

how to deal with block corruption

The block corruption is a tricky issue to deal with. You can usually see the corrupted blocks with the following query:


select * from v$database_block_corruption


If there are logical corruptions they can be fixed with restore-recover from RMAN or other many ways.

If there are physical corruptions, first fix the storage, and after this you can clear the corrupted blocks.




I found a SQL that determines which objects are affected by the corrupted blocks on this post:

http://toddlerdba.blogspot.com/2010/09/block-corrouption.html

It creates “segment_corrupted” table and inserts into it the objects affected by the corrupted blocks returned by the query above.


create table segment_corrupted (owner varchar2(10), segment_name varchar2(50), segment_type varchar(20));

-----------------------------------------------------------------------------------------------------------------------------
truncate table segment_corrupted;

----------------------------------------------------------------------------------------------------------

declare
begin
for i in (select file#,block# from v$database_block_corruption) loop
begin
insert into segment_corrupted select owner, segment_name, segment_type from dba_extents where file_id=i.file# and i.block# between block_id and block_id + blocks -1;
commit;
end;
end loop;
commit;
end;
/

The segment_name is the Object Name and the segment_type is the Object Type.  After this script ends the Segment_corrupted table will have the objects affected by the corrupted blocks.

Sometimes a corrupted block may not affect any object.



There are many ways of dealing with corrupted blocks.


If there are indexes affected the simplest way is to drop and recreate the index.

If there are other types of objects, and you have a valid backup, you can drop them and recover from that backup.

 For tables, sometimes works rebuilding them using Toad (in fact toad renames the table, creates another one with the same data and recreates indexes, constraints, triggers, and other objects, but this doesn’t always work).

If the corrupted block does not affect any object, another solution besides recovering the datafile from backup, is to fill the tablespace and empty it (create a huge table with random data and drop it after) but this is a time consuming solution.


Do not forget to take a full database backup after you have cleared the corrupted blocks.

ORA-07445 with UTL_MATCH.JARO_WINKLER_SIMILARITY

I noticed an exception in the alert log of one of the databases with this error.

ORA-07445: exception encountered: core dump [pi_jaro_winkler_int()+409] [SIGSEGV] [Invalid permissions for mapped object] [0x2AD7A3481000] [] []

Looking in the trace file, an sql using this function was crushing the database:

UTL_MATCH.JARO_WINKLER_SIMILARITY

Because I found no suitable solution, I chose the following workaround:


A colleague found a suitable java class that had the same results like Oracle's UTL_MATCH's function and I created it in the database using the following syntax:


 CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED JaroWinklerDistance AS
....


The next step was to create a function that can call the java class and return the output.


CREATE OR REPLACE f_jaro(p_string1 varchar2 , p_string2 varchar2 ) RETURN number AS
LANGUAGE JAVA NAME 'JaroWinklerDistance.proximityPercent(java.lang.String , java.lang.String) return int';
/

 And the last step was to rewrite the sql so it would use this function.

how to optimize sqls with Like expression

Let's say we have an sql like this:

select * from table_andrei
where name like '%Andrei%' ;


We could rewrite it as it follows:


select * from table_andrei
where instr(name ,'Andrei')>0 ;



  An index on instr(name,’Andrei’) can be created only if it is absolutely necessary.

how to create a database directory on current SO directory

I needed to create a one-click import with datapump script on Windows operating system, and for this I needed to create a database directory mapped on the current directory where the dumps are.
So I wrote an sql file:
 create_directory.sql

CREATE OR REPLACE DIRECTORY DATAPUMP AS '&&1'
/
exit
/

And import_one_click.bat file looks like this:

sqlplus / as sysdba @create_directory.sql %CD%

impdp '/ as sysdba' directory=DATAPUMP  dumpfile=export_andrei.dmp logfile=import_andrei.log schemas='ANDREI'

how to fix a shared server issue?

This is more of a question for you guys.

In the alert log i have this:

Errors in file /oradb/app/oracle/admin/test/bdump/test_s003_123472.trc:
ORA-00600: internal error code, arguments: [ksudel1], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [unable to load XDB library], [], [], [], [], [], [], []
Wed Nov  3 15:35:36 2010
found dead shared server 'S000', pid = (10, 2)
found dead shared server 'S001', pid = (24, 3)
found dead shared server 'S002', pid = (28, 1)
found dead shared server 'S003', pid = (29, 1)

And of course lots of trace files with test_s****.trc

Actually the solution I adopted is to make all connection on this database DEDICATED. So I changed everywhere SERVER=DEDICATED. And since then I had no error coming.

Do you have any ideas?