Category: Errors and Bugs

Database, Enterprise Manager, Errors and Bugs

Service Detected on OEM but not in SRVCTL or SERVICE_NAMES Parameter?

 , , , , ,

Okey, it happens.
To me, after a database moving from a cluster to another. The service was registered by SRVCTL in the old cluster but is not needed. So, was not registered in the new cluster.
But OEM insists to list, for example, the “service3” as offline. The problem is that you can not remove it by SRVCTL, because you had not registered, right? See the example below:

Listing services:
srvdatabase1:/home/oracle>srvctl status service -d systemdb
Service service1_systemdb is running on nodes: srvdatabase1
Service service2 is running on nodes: srvdatabase1
Service service2_systemdb is running on nodes: srvdatabase1

In the service_name parameter:
srvdatabase1:/home/oracle>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon Jun 8 15:21:00 2015
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> show parameters service;
NAME                                 TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
service_names                        string
service2,test,systemdb

And the offline alarm goes to “service3“?
The easiest fix:

SQL> exec dbms_service.DELETE_SERVICE('service3');
PL/SQL procedure successfully completed.

Matheus.

Database, Errors and Bugs, RAC | Data Guard

ORA-10456: cannot open standby database; media recovery session may be in progress

 , ,

Easy, easy… Take a look:

# Error

db2database2p:>srvctl status database -d database
Instance database1 is running on node db1database1p
Instance database2 is not running on node db2database2p
db2database2p:>srvctl start instance -d database -i database2
PRCR-1013 : Failed to start resource ora.database.db
PRCR-1064 : Failed to start resource ora.database.db on node db2database2p
CRS-5017: The resource action "ora.database.db start" encountered the following error:
ORA-10456: cannot open standby database; media recovery session may be in progress
. For details refer to "(:CLSN00107:)" in "/grid/product/11.2.0/log/db2database2p/agent/crsd/oraagent_oracle/oraagent_oracle.log".
CRS-2674: Start of 'ora.database.db' on 'db2database2p' failed.

# Solution

db2database2p:>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Jun 4 20:27:46 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.
Total System Global Area 1.1224E+11 bytes
Fixed Size 2234920 bytes
Variable Size 6.1472E+10 bytes
Database Buffers 5.0466E+10 bytes
Redo Buffers 299741184 bytes
Database mounted.
ORA-10456: cannot open standby database; media recovery session may be in progress

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
SQL> ALTER DATABASE OPEN READ ONLY;
Database altered.
SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

db2database2p:>srvctl status database -d database
Instance database1 is running on node db1database1p
Instance database2 is running on node db2database2p

Matheus.

Database, Errors and Bugs, Migration | Upgrades | Patching

Leap Second and Impact for Oracle Database

 , , , , ,

Don’t know what is this? Oh boy, I suggest you take a look…

It can sound a little crazy, but it’s about an universal time adjustment of atomic time. Something like that. To understand, take a look on:
http://www.meinberg.de/english/info/leap-second.htm
http://en.wikipedia.org/wiki/Coordinated_Universal_Time
http://en.wikipedia.org/wiki/International_Atomic_Time
http://www.britannica.com/EBchecked/topic/136395/Coordinated-Universal-Time
http://www.britannica.com/EBchecked/topic/290686/International-Atomic-Time

20499seconds
Okey doke!
But what about Oracle Database adjustment? Good news: Nothing to do! 😀

In Oracle words: “The Oracle RDBMS needs no patches and has no problem with the leap second changes on OS level.”

But, attention!
If your application uses timestamp or sysdate, verify the adjust of the OS Level. If it consists on a “60” second, it can result on “ORA-01852 seen 60 seconds is a illegal value for the date or timestamp dataype.”
(Insert leap seconds into a timestamp column fails with ORA-01852 (Doc ID 1553906.1))

Another possibilities is documented on these notes:
NTP leap second event causing Oracle Clusterware node reboot (Doc ID 759143.1)
(Oracle VM and RHEL 4.4 to 6.2): Leap Second Hang – CPU Can Be Seen at 100% (Doc ID 1472421.1)
(OEM on Linux): Enterprise Manager Management Agent or OMS CPU Use Is Excessive near Leap Second Additions on Linux (Doc ID 1472651.1)

So, pay attention! 🙂

Here other Oracle notes that I recommend to take a look:
Leap seconds (extra second in a year) and impact on the Oracle database. (Doc ID 730795.1)
Leap Second Time Adjustment (e.g. on June 30, 2015 at 23:59:59 UTC) and Its Impact on Exadata Database Machine (Doc ID 1986986.1)
How Leap Second Affects The OS Clock on Linux and Oracle VM (Doc ID 1453523.1)
NOTE:1461363.1 – What Leap Second Affects Occur In Tuxedo?
NOTE:1553906.1 – Insert leap seconds into a timestamp column fails with ORA-01852
NOTE:412160.1 – Updated DST Transitions and New Time Zones in Oracle RDBMS and OJVM Time Zone File Patches
NOTE:1453523.1 – How Leap Second Affects The OS Clock on Linux and Oracle VM
NOTE:1019692.1 – Leap Second Handling in Solaris – NTPv3 and NTPv4
NOTE:1444354.1 – Strftime(3c) Does Not Show The Leap Second As 23:59:60
NOTE:1461606.1 – Any Effect of Leap Seconds to MessageQ?

Matheus.

Database, Errors and Bugs

ORA-24777: use of non-migratable database link not allowed

 , , , , , ,

Hoje reencontrei este erro e, como de costume, já havia esquecido qual era a solução. Por isso fica aqui minha anotação. 🙂
Tive que puxar mais da memória do que qualquer outra coisa, pois todos os links da primeira página do Google tinham apenas o stack do erro mas sem solução. Que na realidade é bem simples:

O erro ocorre devido sessão utilizando shared connection (XA) utilizar DBLink com conexão dedicated. O uso de XA no JDBC é uma configuração do datasource, caso você esteja utilizando weblogic, jboss, tomcat ou outro com suporte a java.
A solução que adotei até hoje consiste setar o “host” do DBLink ou a entrada de TNS utilizada pelo DBLink no arquivo tnsnames do BD de origem para utilizar tipo de conexão “shared” para conexão remota.

Ex Antes:  (DESCRIPTION= (ADDRESS_LIST= (ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=))) (CONNECT_DATA= (SERVICE_NAME=<serviço>) (SERVER=DEDICATED)))
Ex Depois:  (DESCRIPTION= (ADDRESS_LIST= (ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=))) (CONNECT_DATA= (SERVICE_NAME=<serviço>) (SERVER=SHARED)))

Segundo o stackoverflow, pode ser resolvido criando o database link como shared.
Ex: CREATE SHARED DATABASE LINK CONNECT TO bob IDENTIFIED  BY “MyNewPasswd1” AUTHENTICATED BY jim IDENTIFIED BY “JimsPass23” USING ‘DB01’;

Na realidade a segunda solução me pareceu muito mais elegante. Mas só percebi que existia depois de já ter resolvido pela primeira abordagem.
Se acontecer novamente, testo da segunda forma e comento aqui. Prometo. 😉

Ah!
Acho que não é necessario mencionar que o BD destino do DBLink precisa estar escutando em algum serviço com suporte a XA, né?
Não é o assunto deste post, então não vou entrar em detalhes. Mas basicamente você precisa setar para um valor diferente de zero os parâmetros: max_shared_servers; shared_servers; max_dispatchers e dispatchers e subir o serviço do dispartcher no listener. Taca no Google aí que essa parte é barbada!

Abraço!
Matheus.