Simple Write-Up: 11gr2

Showing posts with label 11gr2. Show all posts

Friday, December 24, 2010

Oracle Database 11g: Interactive Quick Reference

Confiram no link abaixo :

http://goo.gl/rQejT

" Oracle Database 11g Release 2 Enterprise Edition provides comprehensive features to easily manage the most demanding transaction processing, business intelligence, and content management applications.

Now you have easy access to information that you need to administer your Oracle Database with our downloadable Interactive Quick Reference. We have taken our traditional printed poster for Database Administrators, and created an electronic format packed with even more information.

Use this helpful reference as a cheat sheet for writing custom data dictionary scripts, locating views pertinent to a specific database component, and more. "

Friday, July 02, 2010

Apex 4 Installation on 11gR2

A URL da doc : http://download.oracle.com/docs/cd/E17556_01/doc/install.40/e15513/otn_install.htm#insertedID6

Baseada na documentação coloquei os seguintes passos:

--1º install Oracle Text if you got :
/*Elapsed: 00:00:00.01
Application Express installation requires the Oracle Text database component.
begin
*
ERROR at line 1:
ORA-00900: invalid SQL statement
ORA-06512: at line 4
Disconnected from Oracle Database 11g Release 11.2.0.1.0 - Production
*/
@?/ctx/admin/catctx.sql ctxsys DRSYS TEMP(your temporary tablespace) NOLOCK

--all ok
-- 2ºinstall apex

@apexins SYSAUX SYSAUX TEMP /i/

--all ok
--3º change ADMIN password

@apxchpwd
--Brasil#2010

--all ok
-- 4º unlock APEX_PUBLIC_USER
ALTER USER APEX_PUBLIC_USER ACCOUNT UNLOCK ;

--all ok
-- 5º change the passwaord for ALTER USER APEX_PUBLIC_USER IDENTIFIED BY

ALTER USER APEX_PUBLIC_USER IDENTIFIED BY senha

--all ok
-- 6º grant connect privileges to any host for the APEX_040000 database user

DECLARE
  ACL_PATH VARCHAR2(4000);
  ACL_ID RAW(16);
BEGIN
  -- Look for the ACL currently assigned to '*' and give APEX_040000
  -- the "connect" privilege if APEX_040000 does not have the privilege yet.

  SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS
   WHERE HOST = '*' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;

  -- Before checking the privilege, ensure that the ACL is valid
  -- (for example, does not contain stale references to dropped users).
  -- If it does, the following exception will be raised:
  --
  -- ORA-44416: Invalid ACL: Unresolved principal 'APEX_040000'
  -- ORA-06512: at "XDB.DBMS_XDBZ", line ...
  --
  SELECT SYS_OP_R2O(extractValue(P.RES, '/Resource/XMLRef')) INTO ACL_ID
   FROM XDB.XDB$ACL A, PATH_VIEW P
   WHERE extractValue(P.RES, '/Resource/XMLRef') = REF(A) AND
   EQUALS_PATH(P.RES, ACL_PATH) = 1;

  DBMS_XDBZ.ValidateACL(ACL_ID);
   IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'APEX_040000',
   'connect') IS NULL THEN
   DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,
   'APEX_040000', TRUE, 'connect');
  END IF;

EXCEPTION
  -- When no ACL has been assigned to '*'.
  WHEN NO_DATA_FOUND THEN
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('power_users.xml',
   'ACL that lets power users to connect to everywhere',
   'APEX_040000', TRUE, 'connect');
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*');
END;
/

commit ;

--all ok
-- 7º Enable Indexing of Online Help in Oracle Database 11gR2 and Higher

--a)
SELECT par_value FROM ctxsys.ctx_parameters WHERE par_name = 'FILE_ACCESS_ROLE';

--if return null then

--b) create a role

SQL> CREATE ROLE APEX_URL_DATASTORE_ROLE;

Role created.

--c)grant the user APEX_040000

SQL> GRANT APEX_URL_DATASTORE_ROLE to APEX_040000;

Grant succeeded.

--d)grant permission

EXEC ctxsys.ctx_adm.set_parameter('file_access_role', 'APEX_URL_DATASTORE_ROLE');

-- e) if a) return a value grant the user APEX_040000 with the role wich showed at step a) and continue ons tep c)

--all Ok
--8º Embedded gateway + configuração do local das imagens(the base directory location where the APEX was unzipped)

@apex_epg_config /home/oracle

--all ok
--9º unlock anonymous

ALTER USER ANONYMOUS ACCOUNT UNLOCK;

--all ok
-- 10º change the image directory.the base directory location where the APEX was unzipped

@apxldimg.sql /home/oracle

--10º drop the old apex users

--a)
SELECT username
   FROM dba_users
WHERE (username LIKE 'FLOWS_%' OR USERNAME LIKE 'APEX_%')
   AND USERNAME NOT IN (
   SELECT 'FLOWS_FILES'
   FROM DUAL
   UNION
   SELECT 'APEX_PUBLIC_USER' FROM DUAL
   UNION
   SELECT SCHEMA s
   FROM dba_registry
   WHERE comp_id = 'APEX');

--b) drop all user that appear on the select above

drop user "usuario" cascade ;

Depois :

http://hostname ou IP:8080/apex/apex_admin
http://hostname ou IP:8080/apex

--Any problem wth ADMIN password,reset with @apxchpwd

Troque a senha e enjoy it!

Wednesday, June 02, 2010

Client do Oracle 11gR2 no Ubuntu 10.04

Instalei o client do Oracle 11gR2 no meu Ubuntu 10.04.

Rodando certinho...

jcorrea@ubuntu:/$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jun 2 17:38:59 2010

SQL>; exit

jcorrea@ubuntu:/$ tnsping stewie

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 02-JUN-2010 17:39:13

Used parameter files:

/u01/app/jcorrea/product/11.2.0/client_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.36.2)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = stewie)))

OK (10 msec)

jcorrea@ubuntu:/$ oerr ora 12514
12514, 00000, "TNS:listener does not currently know of service requested in connect descriptor"
// *Cause: The listener received a request to establish a connection to a
// database or other service. The connect descriptor received by the listener
// specified a service name for a service (usually a database service)
// that either has not yet dynamically registered with the listener or has
// not been statically configured for the listener. This may be a temporary
// condition such as after the listener has started, but before the database
// instance has registered with the listener.
// *Action:
// - Wait a moment and try to connect a second time.
// - Check which services are currently known by the listener by executing:
// lsnrctl services
// - Check that the SERVICE_NAME parameter in the connect descriptor of the
// net service name used specifies a service known by the listener.
// - If an easy connect naming connect identifier was used, check that
// the service name specified is a service known by the listener.
// - Check for an event in the listener.log file.

jcorrea@ubuntu:/$ oerr ora 1555
01555, 00000, "snapshot too old: rollback segment number %s with name \"%s\" too small"
// *Cause: rollback records needed by a reader for consistent read are
// overwritten by other writers
// *Action: If in Automatic Undo Management mode, increase undo_retention
// setting. Otherwise, use larger rollback segments
jcorrea@ubuntu:/$

Thursday, May 13, 2010

OWB 11.2 integrado com o Oracle Database 11gR2

Esta semana fiz a configuração do OWB 11.2 no Oracle Database 11gR2.De inicio tive problemas com um "abort"! inesperado no meio da configuração(que demorava mas de 30 minutos) .Como o banco de dados que eu estava tentando configurar fica em uma localização remota 7.900 KM mais ou menos.Conclui que o problema era rede.Testando localmente configurei em mais ou menos 10 minutos.

Para o uso das feartures integradas não é preciso licença,pois já vem licenciada juntamente com o banco de dados 11g.Feature Adicionais são licenciadas a parte.

Link para a home do OWB na OTN : http://migre.me/EIKT

Saturday, May 08, 2010

Oracle - Usando Virtual Indexes

Quer testar a alteração de um plano de execução sem criar um índice físico no Oracle?

O uso da opção NOSEGMENT no comando CREATE INDEX é um recurso muito útil quando aparece a dúvida de criar um INDEX e saber se realmente ele fará efeito m um determinado plano de execução.

Claro que com Know-How da coisa você já sabe onde ajustar para que tenha um efeito desejado até chegar em um ponto aceitável.

Por padrão o uso da opção NOSEGMENT não faz efeito imediato na sua sessão , por isso preciso fazer uma alteração no parâmetro "_use_nosegment_indexes" para TRUE.

ex. 1

ALTER SESSION _use_nosegment_indexes=TRUE ;

Depois faça seu CREATE INDEX e no final coloque a opção NOSEGMENT.

ex.2

CREATE INDEX my_index ON my_table(my_column) NOSEGMENT;

Teste o seu plano de execução novamente e observe se realmente faz sentido criar o INDEX.

Tudo Ok faça DROP e crie o INDEX sem a opção NOSEGMENT.

Até,

Wednesday, February 24, 2010

Upgrade to Oracle 11gr2 - Para quem não sabe por onde começar

Abaixo uma figura que ilustra os simples passos para o processo de upgrade!
Esta é uma das tarefas mais delicadas,pois envolverá o seu ambiente de produção.

Esqueça ir direto para o passo 5 please!
So , take easy e take care with your database !

Abaixo o link da documentação(em .pdf) do Oracle Database 11gr2 que fala sobre o processo de migração de uma versão anterior para o 11gr2,mais conhecido como upgrade kk.

http://download.oracle.com/docs/cd/E11882_01/server.112/e10819.pdf

Tem um post ótimo sobre isso do meu amigo Eduardo Legatti aqui .

Att,

Júlio César Corrêa

Oracle SQL - Usando MERGE e error log

Já encontrei muitos códigos SQL onde se poderia usar o comando MERGE para resolver o problema.

Sintaxe do comando MERGE:

MERGE <hint> INTO <table_name>
USING <table_view_or_query>
ON (<condition>)
WHEN MATCHED THEN <update_clause>
DELETE <where_clause>
WHEN NOT MATCHED THEN <insert_clause>
[LOG ERRORS <log_errors_clause> <reject limit <integer | unlimited>];

O MERGE normalmente é usado nas situações em que já se tem uma tabela populada(alvo) e necessita inserir os registros da tabela origem que ainda não se encontram na tabela alvo.

Digamos que sua tabela alvo foi atualizada semana passada.Então hoje você resolve fazer um MERGE entre as linhas para caso tenha ocorrido alteração na tabela origem que esta seja propagada/efetuada na tabela alvo via UPDATE .

O interessante então no MERGE são as opções WHEN MATCHED THEN usando INSERT ou UPDATE.E também a cláusula DELETE .

Vamos fazer um exemplo de merge usando a tabela PRODUCTS do schema OE.

dbajcc@DBPOST on 24-FEB-10 >desc oe.products;

Name Null? Type

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

PRODUCT_ID NOT NULL NUMBER(6)

LANGUAGE_ID VARCHAR2(3)

PRODUCT_NAME NVARCHAR2(125)

CATEGORY_ID NUMBER(2)

PRODUCT_DESCRIPTION NVARCHAR2(2000)

WEIGHT_CLASS NUMBER(1)

WARRANTY_PERIOD INTERVAL YEAR(2) TO MONTH

SUPPLIER_ID NUMBER(6)

PRODUCT_STATUS VARCHAR2(20)

LIST_PRICE NUMBER(8,2)

MIN_PRICE NUMBER(8,2)

CATALOG_URL VARCHAR2(50)

dbajcc@DBPOST on 24-FEB-10 >select count(*) from oe.products;

COUNT(*)

----------

288

Elapsed: 00:00:00.01

dbajcc@DBPOST on 24-FEB-10 >

Vamos criar uma tabela cópia da tabela oe.products mas no schema dbajcc

dbajcc@DBPOST on 24-FEB-10 >create table jcc_products

2 as select * from oe.products where rownum=0 ;

Table created.

Elapsed: 00:00:01.57

dbajcc@DBPOST on 24-FEB-10 >

Vamos inserir exatamente 100 linhas nesta nova tabela,lembrando que na tabela oe.products há 288 linhas :

dbajcc@DBPOST on 24-FEB-10 >

dbajcc@DBPOST on 24-FEB-10 >insert into jcc_products

2 select * from oe.products

3 where rownum < 101 ;

100 rows created.

Elapsed: 00:00:00.20

dbajcc@DBPOST on 24-FEB-10 >commit ;

Commit complete.

Elapsed: 00:00:00.01

dbajcc@DBPOST on 24-FEB-10 >

Agora vamos usar o MERGE para inserir os outros 188 registros .

Vamos usar a package DBMS_ERRLOG para criar uma tabela de captura de erros:

dbajcc@DBPOST on 24-FEB-10 >EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG('jcc_products', 'jcc_products_log');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.30

dbajcc@DBPOST on 24-FEB-10 >

Usando o Merge:

dbajcc@DBPOST on 24-FEB-10 >

dbajcc@DBPOST on 24-FEB-10 >merge into jcc_products jp

2 using oe.products p

on (jp.product_id = p.product_id)

when not matched then insert

3 4 5 ( jp.language_id,

jp.product_name,

jp.category_id,

jp.product_description,

jp.weight_class,

jp.warranty_period,

jp.supplier_id,

jp.product_status,

jp.list_price,

jp.min_price ,

jp.catalog_url

)

values

( p.language_id,

p.product_name,

p.category_id,

p.product_description,

p.weight_class,

p.warranty_period,

p.supplier_id,

p.product_status,

p.list_price,

p.min_price ,

6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 p.catalog_url

); 29

188 rows merged.

Elapsed: 00:00:00.13

dbajcc@DBPOST on 24-FEB-10 >commit ;

Commit complete.

Elapsed: 00:00:00.01

dbajcc@DBPOST on 24-FEB-10 >

dbajcc@DBPOST on 24-FEB-10 >SELECT ORA_ERR_MESG$, ORA_ERR_TAG$ from jcc_products_log ;

no rows selected

Elapsed: 00:00:00.00

dbajcc@DBPOST on 24-FEB-10 >

Principalmente para quem trabalha com ETL o MERGE é essencial !

Oracle SQL - Usando LEFT and RIGHT JOIN

Olá,

Hoje o assunto é OUTER JOIN.

Essa dica de post eu peguei com o meu amigo Eduardo do blog bieduardo.blogspot.com/ .

Normalmente muitos profissionais caem nesta dúvida cruel.

Como fazer corretamente um OUTER JOIN?

Pirmeiro vamos relembrar o que é um OUTER JOIN.

"Um OUTER JOIN é um SIMPLE JOIN ou INNER JOIN com uma "opção extendida " de trazer também os dados que não satisfazem a condição de JOIN.

No Oracle para usar LEFT ou RIGHT OUTER JOIN em seu SQL você pode usar o caracter "+" entre parênteses para informar ao Oracle qual operador você está utilizando.Você também pode usar o padrão SQL92 para JOIN usando as palavras RIGHT OUTER JOIN e LEFT OUTER JOIN.

Mostrarei isso mais a frente no segundo post.

A explicação sobre de qual lado utilizar é simples:

Se a coluna que contém os dados nulos é a da direita você então coloca o sinal (+) na coluna da esquerda e vice-versa.

Ex. Nas tabelas A e B existe a coluna ID do tipo NUMBER.

Na tabela A existe uma linha em que a coluna ID é nula,mas mesmo assim você precisa trazer esta linha na sua query ,neste caso você precisar fazer um JOIN para obtê - las.

ex.

select

from tabela1 A,tabela2 B

where A.id = B.id(+) ;

No exemplo acima foi feito um LEFT JOIN pois quero retornar todos os dados da tabela A que não satisfazem a condição da JOIN .

Criando as tabelas de exemplo :

dbajcc@DBPOST on 23-FEB-10 >create table jcc_join_a

(seq number(2) ,

text varchar2(10)) ; 2 3

Table created.

Elapsed: 00:00:00.03

dbajcc@DBPOST on 23-FEB-10 > insert into jcc_join_a

values(1,'disk1') ; 2

1 row created.

Elapsed: 00:00:00.02

dbajcc@DBPOST on 23-FEB-10 > insert into jcc_join_a

values(2,'disk2') ; 2

1 row created.

Elapsed: 00:00:00.00

dbajcc@DBPOST on 23-FEB-10 > insert into jcc_join_a(seq)

values(3) ; 2

1 row created.

Elapsed: 00:00:00.00

dbajcc@DBPOST on 23-FEB-10 >commit ;

Commit complete.

Elapsed: 00:00:00.00

dbajcc@DBPOST on 23-FEB-10 > create table jcc_join_b

(seq number(2) ,

text varchar2(10)) ; 2 3

Table created.

Elapsed: 00:00:00.02

dbajcc@DBPOST on 23-FEB-10 > insert into jcc_join_b

values(1,'disk1') ;

1 row created.

Elapsed: 00:00:00.01

dbajcc@DBPOST on 23-FEB-10 > insert into jcc_join_b

values(2,'disk2') ; 2

1 row created.

Elapsed: 00:00:00.00

dbajcc@DBPOST on 23-FEB-10 > insert into jcc_join_b(text)

values('disk3') ; 2

1 row created.

Elapsed: 00:00:00.00

dbajcc@DBPOST on 23-FEB-10 >

Fazendo um RIGHT JOIN :

dbajcc@DBPOST on 23-FEB-10 >select a.* ,b.*

from jcc_join_a a,

jcc_join_b b

where a.seq(+) = b.seq ; 2 3 4

SEQ TEXT SEQ TEXT

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

1 disk1 1 disk1

2 disk2 2 disk2

disk3

Elapsed: 00:00:00.02

dbajcc@DBPOST on 23-FEB-10 >

Ou usando a sintaxe do padrão SQL92 :

dbajcc@DBPOST on 24-FEB-10 >select a.* ,b.*

2 from jcc_join_a a right outer join jcc_join_b b

3 on a.seq = b.seq ;

SEQ TEXT SEQ TEXT

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

1 disk1 1 disk1

2 disk2 2 disk2

disk3

Elapsed: 00:00:00.00

dbajcc@DBPOST on 24-FEB-10 >

Acima destaquei em azul a coluna na qual é o alvo e que por sinal contém um campo SEQ nulo para o campo TEXT "disk3" .

Agora vamos fazer um LEFT JOIN :

[oracle@marte ~]$ sqlplus dbajcc

SQL*Plus: Release 11.2.0.1.0 Production on Wed Feb 24 07:49:02 2010

Enter password:

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

dbajcc@DBPOST on 24-FEB-10 > select a.* ,b.*

from jcc_join_a a,

jcc_join_b b

where a.seq = b.seq(+) ; 2 3 4

SEQ TEXT SEQ TEXT

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

1 disk1 1 disk1

2 disk2 2 disk2

Elapsed: 00:00:00.05

dbajcc@DBPOST on 24-FEB-10 >

Ou usando o outro tipo de sintaxe :

dbajcc@DBPOST on 24-FEB-10 >select a.* ,b.*

2 from jcc_join_a a left outer join jcc_join_b b

3 on a.seq = b.seq ;

SEQ TEXT SEQ TEXT

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

1 disk1 1 disk1

2 disk2 2 disk2

Elapsed: 00:00:00.19

dbajcc@DBPOST on 24-FEB-10 >

Temos uma outra opção que é usar a um FULL OUTER JOIN para trazer os dados que não satisfazem a condição de JOIN das duas tabelas envolvidas.

dbajcc@DBPOST on 24-FEB-10 >select a.* ,b.*

2 from jcc_join_a a full outer join jcc_join_b b

3 on a.seq = b.seq ;

SEQ TEXT SEQ TEXT

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

1 disk1 1 disk1

2 disk2 2 disk2

disk3

Elapsed: 00:00:00.01

dbajcc@DBPOST on 24-FEB-10 >

Nem vou trazer exemplos sobre Cartesian Join pois acredito ser de pouca utilidade.

Até,

Júlio César Corrêa

Tuesday, February 23, 2010

Oracle Database 11g Essentials for Implementors (Beta)

Para quem trabalha com parceiros Oracle é uma boa este exame :

http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=41&p_exam_id=1Z0_514

Lembrando que vale para as duas versões!

"This exam is valid for Oracle Database 11gR1 and 11gR2."

Oracle SQL - Find whitespace- Regular Expressions - Oracle

Procurando espaços em branco em uma coluna :

dbajcc@DBPOST on 23-FEB-10 >create table jcc_regex(x number(1),y varchar2(15)) ;

Table created.

Elapsed: 00:00:00.02

dbajcc@DBPOST on 23-FEB-10 >insert into jcc_regex

2 values(1,'Os piratas');

1 row created.

Elapsed: 00:00:00.13

dbajcc@DBPOST on 23-FEB-10 >insert into jcc_regex

2 values(2,'Ospiratas');

1 row created.

Elapsed: 00:00:00.00

dbajcc@DBPOST on 23-FEB-10 >commit ;

Commit complete.

Elapsed: 00:00:00.01

dbajcc@DBPOST on 23-FEB-10 >

dbajcc@DBPOST on 23-FEB-10 >select x,regexp_count(y,'(\s)') whitespace from jcc_regex ;

X WHITESPACE

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

1 1

2 0

Elapsed: 00:00:00.01

dbajcc@DBPOST on 23-FEB-10 >

Se por acaso vc está usando um campo CHAR e por um motivo qualquer você precise fazer um replace ,use o REGEXP_REPLACE.

Você tem uma lista que fica na doc Database Advanced Application Developer's Guide.Tem vários exemplos que podem ajudar você .

Para obter das docs :

tahiti.oracle.com

Oracle Database 11gr2 - MEMORY_TARGET e /dev/shm

Olá,

Continuando os testes com o Oracle Database 11gr2 conforme o tempo livre que tenho,fiz alguns testes diminuindo a quantidade de memória RAM da VM .Como estava com o Oracle Restart habilitado,após subir o servidor de banco de dados logo teria os serviços disponíveis para uso.

[oracle@marte ~]$

[oracle@marte ~]$ echo $ORACLE_HOME

/u01/app/oracle/product/11.2.0/dbhome_1

[oracle@marte ~]$ echo $ORACLE_SID

dbpost

[oracle@marte ~]$ ps -ef|grep pmon

oracle 2997 1 0 08:42 ? 00:00:00 asm_pmon_+ASM

oracle 3118 1 0 08:43 ? 00:00:00 ora_pmon_dbpost

oracle 4083 3444 0 09:58 pts/2 00:00:00 grep pmon

[oracle@marte ~]$ ps -ef|grep tns

oracle 2908 1 0 08:41 ? 00:00:00 /u01/app/oracle/product/11.2.0/grid/bin/tnslsnr LISTENER -inherit

oracle 4086 3444 0 09:59 pts/2 00:00:00 grep tns

[oracle@marte ~]$

Bom parece que o banco de dados está disponível

[oracle@marte ~]$

[oracle@marte ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Feb 23 10:02:19 2010

Connected to an idle instance.

idle on 23-FEB-10 >

idle on 23-FEB-10 >select * from v$instance;

select * from v$instance

ERROR at line 1:

ORA-01034: ORACLE not available

Process ID: 0

Session ID: 0 Serial number: 0

Elapsed: 00:00:00.17

idle on 23-FEB-10 >

Há algo errado.

Vamos ao alert log

[oracle@marte /]$ cd /u01/app/oracle/diag/rdbms/dbpost/dbpost/alert/

[oracle@marte alert]$ ls -ltrh

total 6.6M

-rw-r----- 1 oracle dba 6.6M Feb 23 09:36 log.xml

[oracle@marte alert]$

[oracle@marte alert]$ tail -20f log.xml

</msg>

<msg time='2010-02-23T09:36:06.048-02:00' org_id='oracle' comp_id='rdbms'

msg_id='opistr_real:935:3971575317' type='NOTIFICATION' group='startup'

level='16' host_id='marte' host_addr='127.0.0.1'

pid='3479'>

<txt>Starting ORACLE instance (normal)

</txt>

</msg>

<msg time='2010-02-23T09:36:06.075-02:00' org_id='oracle' comp_id='rdbms'

type='UNKNOWN' level='16' host_id='marte'

host_addr='127.0.0.1' pid='3479'>

<txt>WARNING: You are trying to use the MEMORY_TARGET feature. This feature requires the /dev/shm file system to be mounted for at least 536870912 bytes. /dev/shm is either not mounted or is mounted with available space less than this size. Please fix this so that MEMORY_TARGET can work as expected. Current available is 322220032 and used is 474198016 bytes. Ensure that the mount point is /dev/shm for this directory.

</txt>

</msg>

<msg time='2010-02-23T09:36:06.092-02:00' org_id='oracle' comp_id='rdbms'

type='UNKNOWN' level='16' host_id='marte'

host_addr='127.0.0.1' pid='3479'>

<txt>memory_target needs larger /dev/shm

</txt>

</msg>

Analisando o erro acima vamos ver o filesystem /dev/shm

[root@marte /]# df -Ph

Filesystem Size Used Avail Use% Mounted on

/dev/sda1 23G 15G 6.7G 70% /

tmpfs 760M 453M 308M 60% /dev/shm

[root@marte /]# cat /proc/meminfo

MemTotal: 1555508 kB

MemFree: 315880 kB

Buffers: 64700 kB

Cached: 968316 kB

SwapCached: 0 kB

Active: 423800 kB

Inactive: 769132 kB

HighTotal: 655296 kB

HighFree: 1168 kB

LowTotal: 900212 kB

LowFree: 314712 kB

SwapTotal: 1574360 kB

SwapFree: 1574360 kB

Dirty: 468 kB

Writeback: 0 kB

AnonPages: 159928 kB

Mapped: 91568 kB

Slab: 29760 kB

PageTables: 9068 kB

NFS_Unstable: 0 kB

Bounce: 0 kB

CommitLimit: 2352112 kB

Committed_AS: 1866740 kB

VmallocTotal: 114680 kB

VmallocUsed: 3280 kB

VmallocChunk: 111252 kB

HugePages_Total: 0

HugePages_Free: 0

HugePages_Rsvd: 0

Hugepagesize: 4096 kB

[root@marte /]#

Vamos ver o tamanho alocado no paramtreo MEMORY_TARGET :

[oracle@marte /]$ cd $ORACLE_HOME/dbs

[oracle@marte dbs]$ vi initdbpost.ora

...

*.memory_target=512m

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

Ok.

O erro está ocorrendo porque não temos 512MB disponíveis no /dev/shm .Isto ocorre porque como não configurei o /dev/shm e tenho somente 1536MB de RAM alocado para esta VM.

Vou retirar totalmente o banco de dados dbpost do Oracle Restart :

[oracle@marte dbs]$ cd /

[oracle@marte /]$ srvctl disable database -d dbpost

[oracle@marte /]$

[root@marte /]# ps -ef|grep pmon

oracle 2997 1 0 08:42 ? 00:00:00 asm_pmon_+ASM

root 4774 4362 0 11:39 pts/2 00:00:00 grep pmon

[root@marte /]#

Para efeito de testes não quero alterar o tamanho da memória RAM.Vamos alterar o /dev/shm que ainda está com valores default no meu SO.

Pode-se fazer via comando direto no shell/terminal,mas no caso para ficar definitivo após o boot vou colocá-lo no /etc/fstab .

root@marte /]# cat /etc/fstab

LABEL=/ / ext3 defaults 1 1

tmpfs /dev/shm tmpfs defaults 0 0

devpts /dev/pts devpts gid=5,mode=620 0 0

sysfs /sys sysfs defaults 0 0

proc /proc proc defaults 0 0

LABEL=SWAP-sda2 swap swap defaults 0 0

[root@marte /]#

Fazendo o remount do /dev/shm

[root@marte /]# mount -o remount,size=1G /dev/shm

[root@marte /]#

Alocando o espaço para ficar disponível na inicialização do sistema :

[root@marte /]# vi /etc/fstab

[root@marte /]# cat /etc/fstab

LABEL=/ / ext3 defaults 1 1

tmpfs /dev/shm tmpfs defaults,size=1024m,auto 0 0

devpts /dev/pts devpts gid=5,mode=620 0 0

sysfs /sys sysfs defaults 0 0

proc /proc proc defaults 0 0

LABEL=SWAP-sda2 swap swap defaults 0 0

[root@marte /]#

reboot :

[root@marte /]# shutdown -r now

Broadcast message from root (pts/2) (Tue Feb 23 11:50:40 2010):

The system is going down for reboot NOW!

[root@marte /]#

Curiosidade - Oracle Restart em ação

[root@marte ~]# tail -f /var/log/messages

Feb 23 11:52:34 localhost smartd[2568]: Device: /dev/sde, IE (SMART) not enabled, skip device Try 'smartctl -s on /dev/sde' to turn on SMART features

Feb 23 11:52:34 localhost smartd[2568]: Unable to register SCSI device /dev/sde at line 36 of file /etc/smartd.conf

...

Feb 23 11:52:37 localhost pcscd: winscard.c:219:SCardConnect() Reader E-Gate 0 0 Not Found

Feb 23 11:52:37 localhost last message repeated 3 times

Feb 23 11:52:38 localhost /u01/app/oracle/product/11.2.0/grid/bin/crswrapexece.pl[2552]: executing "/u01/app/oracle/product/11.2.0/grid/bin/ohasd.bin reboot"

Após o boot total,vamos tentar subir a instance dbpost.

Abaixo podemos ver que o /dev/shm está com a tamanho que foi colocado no /etc/fstab

[root@marte ~]# df -Ph

Filesystem Size Used Avail Use% Mounted on

/dev/sda1 23G 15G 6.7G 70% /

tmpfs 1.0G 154M 871M 16% /dev/shm

[root@marte ~]#

Startup no dbpost :

[oracle@marte dbs]$ ps -ef|grep pmon

oracle 2994 1 0 11:53 ? 00:00:00 asm_pmon_+ASM

oracle 3124 2948 0 12:00 pts/2 00:00:00 grep pmon

[oracle@marte dbs]$ strings spfiledbpost.ora |grep memory

*.memory_target=512m

[oracle@marte dbs]$ srvctl start database -d dbpost -o open

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

CRS-2501: Resource 'ora.dbpost.db' is disabled

[oracle@marte dbs]$

[oracle@marte dbs]$ echo $ORACLE_HOME

/u01/app/oracle/product/11.2.0/dbhome_1

[oracle@marte dbs]$ echo $ORACLE_SID

dbpost

[oracle@marte dbs]$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Tue Feb 23 12:03:58 2010

Connected to an idle instance.

idle on 23-FEB-10 >startup nomount ;

ORACLE instance started.

Total System Global Area 535662592 bytes

Fixed Size 1337720 bytes

Variable Size 364906120 bytes

Database Buffers 163577856 bytes

Redo Buffers 5840896 bytes

idle on 23-FEB-10 >alter database mount ;

Database altered.

Elapsed: 00:00:05.45

idle on 23-FEB-10 >alter database open;

Database altered.

Elapsed: 00:00:22.07

idle on 23-FEB-10 >exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@marte dbs]$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Tue Feb 23 12:05:57 2010

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

sys@DBPOST on 23-FEB-10 >select open_mode from v$database ;

OPEN_MODE

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

READ WRITE

Elapsed: 00:00:00.03

sys@DBPOST on 23-FEB-10 >exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

[oracle@marte dbs]$

Links para maiores informações :

http://www.cyberciti.biz/tips/what-is-devshm-and-its-practical-usage.html

http://www.oracle-base.com/articles/linux/LargeSGAOnLinux.php

http://kevinclosson.wordpress.com/2007/08/24/oracle11g-automatic-memory-management-part-ii-automatically-stupid/

metalink.oracle.com/ - Note ID 260152.1

Abs,

Júlio César Corrêa

Monday, February 22, 2010

Oracle Database - Datatype Number - Precisão máxima

Estes dias li um post muito legal do http://laurentschneider.com/ ,sempre leio bem os posts dele pois ele sempre procura coisas "escondidas" dentro do Oracle .Acho interessante a visão dele principalmente nos testes com SQL.

O datatype NUMBER tem algumas coisas interessantes para ser observadas.

Vamos lá!

[oracle@marte ~]$ sqlplus dbajcc

SQL*Plus: Release 11.2.0.1.0 Production on Mon Feb 22 13:10:53 2010

Enter password:

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

dbajcc@DBPOST on 22-FEB-10 >

dbajcc@DBPOST on 22-FEB-10 >create table jcc_numx (xnum number(39)) ;

create table jcc_numx (xnum number(39))

ERROR at line 1:

ORA-01727: numeric precision specifier is out of range (1 to 38)

Elapsed: 00:00:00.01

dbajcc@DBPOST on 22-FEB-10 >

dbajcc@DBPOST on 22-FEB-10 >create table jcc_numx (xnum number(38)) ;

Table created.

Elapsed: 00:00:00.03

dbajcc@DBPOST on 22-FEB-10 >

dbajcc@DBPOST on 22-FEB-10 >desc jcc_numx ;

Name Null? Type

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

XNUM NUMBER(38)

dbajcc@DBPOST on 22-FEB-10 >

dbajcc@DBPOST on 22-FEB-10 >--insere 38 digitos

insert into jcc_numx

values(10000000000000000000000000000000000000) ;

dbajcc@DBPOST on 22-FEB-10 > 2

1 row created.

Elapsed: 00:00:00.01

dbajcc@DBPOST on 22-FEB-10 >

dbajcc@DBPOST on 22-FEB-10 >--insere 39 digitos

insert into jcc_numx

values(100000000000000000000000000000000000000);

dbajcc@DBPOST on 22-FEB-10 > 2

values(100000000000000000000000000000000000000)

ERROR at line 2:

ORA-01438: value larger than specified precision allowed for this column

Elapsed: 00:00:00.02

dbajcc@DBPOST on 22-FEB-10 >

commit ;dbajcc@DBPOST on 22-FEB-10 >

Commit complete.

Elapsed: 00:00:00.00

dbajcc@DBPOST on 22-FEB-10 >select xnum,length(xnum) from jcc_numx ;

XNUM LENGTH(XNUM)

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

1.0000E+37 38

Elapsed: 00:00:00.01

dbajcc@DBPOST on 22-FEB-10 >

dbajcc@DBPOST on 22-FEB-10 >create table jcc_numx2 (xnum number) ;

Table created.

Elapsed: 00:00:00.02

dbajcc@DBPOST on 22-FEB-10 >desc jcc_numx2;

Name Null? Type

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

XNUM NUMBER

dbajcc@DBPOST on 22-FEB-10 >

dbajcc@DBPOST on 22-FEB-10 >--insere 38 digitos

insert into jcc_numx2

values(10000000000000000000000000000000000000) ;

commit ;

dbajcc@DBPOST on 22-FEB-10 > 2

1 row created.

Elapsed: 00:00:00.00

dbajcc@DBPOST on 22-FEB-10 >dbajcc@DBPOST on 22-FEB-10 >

Commit complete.

Elapsed: 00:00:00.01

dbajcc@DBPOST on 22-FEB-10 >--insere 39 digitos

insert into jcc_numx2

values(100000000000000000000000000000000000000);

commit ;dbajcc@DBPOST on 22-FEB-10 > 2

1 row created.

Elapsed: 00:00:00.00

dbajcc@DBPOST on 22-FEB-10 >dbajcc@DBPOST on 22-FEB-10 >

Commit complete.

Elapsed: 00:00:00.01

dbajcc@DBPOST on 22-FEB-10 >--insere um googol

insert into jcc_numx2

values(10000000000000000000000000000000000000000000000000000000000000

000000000000000000000000000000000000000) ;

commit ;dbajcc@DBPOST on 22-FEB-10 > 2

1 row created.

Elapsed: 00:00:00.00

dbajcc@DBPOST on 22-FEB-10 >dbajcc@DBPOST on 22-FEB-10 >

Commit complete.

Elapsed: 00:00:00.00

dbajcc@DBPOST on 22-FEB-10 >select xnum,length(xnum) from jcc_numx2 ;

XNUM LENGTH(XNUM)

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

1.0000E+37 38

1.0000E+38 39

1.000E+100 40

Elapsed: 00:00:00.00

dbajcc@DBPOST on 22-FEB-10 >

dbajcc@DBPOST on 22-FEB-10 >insert into jcc_numx2

values(11111111111111111111111111111111111111111111111111111111111111

11111111111111111111111111111111111111) ; 2

1 row created.

Elapsed: 00:00:00.00

dbajcc@DBPOST on 22-FEB-10 >select xnum,length(xnum) from jcc_numx2 ;

XNUM LENGTH(XNUM)

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

1.0000E+37 38

1.0000E+38 39

1.000E+100 40

1.1111E+99 40

Elapsed: 00:00:00.00

dbajcc@DBPOST on 22-FEB-10 >commit ;

Commit complete.

Elapsed: 00:00:00.01

dbajcc@DBPOST on 22-FEB-10 >

dbajcc@DBPOST on 22-FEB-10 >create table jcc_numx3 (xnum number(*)) ;

Table created.

Elapsed: 00:00:00.04

dbajcc@DBPOST on 22-FEB-10 >desc jcc_numx3 ;

Name Null? Type

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

XNUM NUMBER

dbajcc@DBPOST on 22-FEB-10 >

dbajcc@DBPOST on 22-FEB-10 >--insere 126 casas

dbajcc@DBPOST on 22-FEB-10 >insert into jcc_numx3

values(111111111111111111111111111111111111111111111111111111111111111111111

111111111111111111111111111111111111111111111111111111111) ;

commit ;

2 --insere 127 casas

insert into jcc_numx3

values(1111111111111111111111111111111111111111111111111111111111111111111111

111111111111111111111111111111111111111111111111111111111) ;

select xnum,length(xnum) from jcc_numx3 ;

1 row created.

Elapsed: 00:00:00.01

dbajcc@DBPOST on 22-FEB-10 >dbajcc@DBPOST on 22-FEB-10 >

Commit complete.

Elapsed: 00:00:00.00

dbajcc@DBPOST on 22-FEB-10 >dbajcc@DBPOST on 22-FEB-10 >dbajcc@DBPOST on 22-FEB-10 > 2 values(11111111111111111111111111111111111111111111111111111111111111111111111

11111111111111111111111111111111111111111111111111111111)

ERROR at line 2:

ORA-01426: numeric overflow

Elapsed: 00:00:00.01

dbajcc@DBPOST on 22-FEB-10 >dbajcc@DBPOST on 22-FEB-10 >

XNUM LENGTH(XNUM)

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

1.111E+125 40

Elapsed: 00:00:00.00

dbajcc@DBPOST on 22-FEB-10 >