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.

Create SPFILE on ASM from PFILE on Filesystem

Some basics, right?
Another thing that is not usual and everytime I do, someone be surprised: “shu” alias for “shutdown”:

SQL> create spfile='+DGDATA/MYDB/spfilemydb.ora' from pfile='/oracle/product/11.2/dbs/init_mydb.ora';
File created.
SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

The Bourleson Master also wrote about it. Take a look on a better detailed post about this subject: http://www.dba-oracle.com/concepts/pfile_spfile.htm.

Matheus.

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.

ASM: Disk Size Imbalance Query

It can be useful if you work frequently with OEM metrics…

# OEM’s Query

SELECT file_num, MAX(extent_count) max_disk_extents, MIN(extent_count)
min_disk_extents
, MAX(extent_count) - MIN(extent_count) disk_extents_imbalance
FROM (SELECT number_kffxp file_num, disk_kffxp disk_num, COUNT(xnum_kffxp)
extent_count
FROM x$kffxp
WHERE group_kffxp = 1
AND disk_kffxp != 65534
GROUP BY number_kffxp, disk_kffxp
ORDER BY number_kffxp, disk_kffxp)
GROUP BY file_num
HAVING MAX(extent_count) - MIN(extent_count) > 5
ORDER BY disk_extents_imbalance DESC;

# Matheus’ Query

select max(free_mb) biggest, min(free_mb) lowest, avg(free_mb) AVG,
trunc(GREATEST ((avg(free_mb)*100/max(free_mb)),(min(free_mb)*100/avg(free_mb))),2)||'%' as balanced,
trunc(100-(GREATEST ((avg(free_mb)*100/max(free_mb)),(min(free_mb)*100/avg(free_mb)))),2)||'%' as inbalanced
from v$asm_disk
where group_number in
(select group_number from v$asm_diskgroup where name = upper('&DG'));

I made my own query for two reasons:
1) I didn’t have the OEM query in the time i made it.
2) My query measures the imbalance with the avg of the disks (if every disk would balanced, how would be the difference), rather than the real/present difference between the disk with the maximum and the minimum usage…

So, you can chose the one you need… 🙂

Matheus.

OUI: RHEL Permission Denied error

Another quick tip about running DBCA:

# Error:

[oracle@dbsrv database]$ ./runInstaller
Starting Oracle Universal Installer...
Checking installer requirements...
All installer requirements met.
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2015-06-25_06-37-23PM. Please wait ...Error in CreateOUIProcess(): 13
: Permission denied

# Solution:

mount -t ext3 -o remount default /tmp

Ok doke?

Matheus.

RHEL5: Database 10g Installation – Checking operating system version error

Everything is old. RHEL and Database versions. But can be useful if you are preparing a nonprod lab of your legacy env, right?

Let’s see the problem:

[oracle@dbsrv database]$ ./runInstaller
Starting Oracle Universal Installer...
Checking installer requirements...
Checking operating system version: must be redhat-3, SuSE-9, redhat-4, UnitedLinux-1.0, asianux-1 or asianux-2
Failed <<<<

The “easiest” workaround:

[oracle@dbsrv database]$ ./runInstaller -ignoreSysPrereqs

The “hardway” workaround:

1. Copy parameter file:
$ cp database/install/oraparam.ini /tmp
2. Edit parameter file:
$ vi /tmp/oraparam.ini
2.1. Change
[Certified Versions]
Linux=redhat-3,SuSE-9,redhat-4,UnitedLinux-1.0,asianux-1,asianux-2
to
[Certified Versions]
Linux=redhat-3,SuSE-9,redhat-4,UnitedLinux-1.0,asianux-1,asianux-2,redhat-5
3. Run the installer again:
$ ./runInstaller -paramFile /tmp/oraparam.ini

KB: Requirements For Installing Oracle10gR2 On RHEL 5/OEL 5 (x86_64) [ID 421308.1]

Matheus.

RMAN-06059: expected archived log not found

# Error

RMAN-03002: failure of backup command at 06/28/2015 14:56:30
RMAN-06059: expected archived log not found, loss of archived log compromises recoverability
ORA-19625: error identifying file +DGFRA/corpdb/archivelog/2015_06_27/thread_1_seq_20198.1192.883524615
ORA-17503: ksfdopn:2 Failed to open file +DGFRA/corpdb/archivelog/2015_06_27/thread_1_seq_20198.1192.883524615
ORA-15012: ASM file '+DGFRA/corpdb/archivelog/2015_06_27/thread_1_seq_20198.1192.883524615' does not exist

# Solution
First of all, you need to know which files exists or not:

RMAN> CROSSCHECK ARCHIVELOG ALL;

Then, clear the missing and run another backup.

RMAN> DELETE EXPIRED ARCHIVELOG ALL;

It’s hardly recommended that you make a full backup after that, to ensure you have a recoverable state.

Matheus.

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.

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.