Tag: ora-xxxx

Database, Errors and Bugs, Windows

Windows: “ORA-12514” After Database Migration/Moving (Using DNS Alias)

 , , ,

It’s usual to use DNS Aliases pointing to scanlistener. This way, we create an abstraction/layer bewteen clients/application and the cluster where database is. Some activities like tierization/consolidation and database moving between clusters (converting to Pluggable, etc), would be much more transparent.

Buuuut, if after a database migration, all the services online and listening, your client is stucking with:

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Remmember you are using DNS to make this layer. Have you tried to flush DNS Cache?
I faced this problem with a Windows Application. The solution:

C:\Users\matheus_boesing>ipconfig /flushdns
Windows IP Configuration
Successfully flushed the DNS Resolver Cache.

All working fine after that. 🙂

Matheus.

Database, Errors and Bugs, RAC | Data Guard

ORA-29760: instance_number parameter not specified

 , , , ,

I felt myself stupid when I lost a few minutes to undestand this error:

SQL> startup pfile=init_corpdb.ora
ORA-29760: instance_number parameter not specified

Do you belive the solution was simply to set a number in ORACLE_SID?
Take a look:

dbsrvr>echo $ORACLE_SID
corpdb
dbsrvr>export ORACLE_SID=corpdb_1
dbsrvr>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sun Jun 28 00:18:05 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup pfile=init_corpdb.ora
ORACLE instance started.
Total System Global Area 4275781632 bytes
Fixed Size 2220200 bytes
Variable Size 889196376 bytes
Database Buffers 3372220416 bytes
Redo Buffers 12144640 bytes
Database mounted.
Database opened.

I hope neve miss time with this again… 😛

Matheus.

Database, Errors and Bugs, Linux

ORA-00845: MEMORY_TARGET not supported on this system (RHEL)

 , , , ,

# Solution:
Make sure that /dev/shm is mounted. You can check this by typing df -k at the command prompt. It will look something like this:

Filesystem Size Used Avail Use% Mounted on
…
shmfs 1G 512M 512M 50% /dev/shm

If you don’t find it then you will have to manually mount it as root user. The size should be more than MEMORY_TARGET or MEMORY_MAX_TARGET.

For example, if the MEMORY_TARGET is less than 2 GB, you should make like that:

#root: mount -t tmpfs shmfs -o size=2048m /dev/shm

I recommend you add an entry in /etc/fstab so that the mount remains persistent even after a reboot.
To make it, add the following entry in /etc/fstab:

shmfs /dev/shm tmpfs size=2048m 0 0

Helped?
Share this post!

Matheus.

Database, Errors and Bugs

ORA-01548: active rollback segment found, terminate

 , , , ,

# Problem

SQL> drop tablespace UNDOTBS1;
drop tablespace UNDOTBS1
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU10_1251904955$' found, terminate dropping tablespace
SQL> drop rollback segment "_SYSSMU3_1251904955$";
Rollback segment dropped.
SQL> drop tablespace UNDOTBS1;
drop tablespace UNDOTBS1
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU10_1251904955$' found, terminate dropping tablespace


# Solution

CREATE ROLLBACK SEGMENT rb1 STORAGE(INITIAL 1M next 1M minextents 20) tablespace UNDOTBS5;
CREATE ROLLBACK SEGMENT rb2 STORAGE(INITIAL 1M next 1M minextents 20) tablespace UNDOTBS5;
CREATE ROLLBACK SEGMENT rb3 STORAGE(INITIAL 1M next 1M minextents 20) tablespace UNDOTBS5;

# Why?
The UNDO_MANAGEMENT is set as ‘MANUAL’, right? To drop any undo the default UNDO must have at least one segment.

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.