ORA-12514: TNS listener cannot resolve service name error

This came up after I created a test RMAN catalog database on one of my test serves that had three other databases running on it. These databases are actually ones that I restored while testing my full database backups, therefore I never really bothered about the networking aspect of them as I never connect to them from outside the server.

I had to setup the tnsnames.ora and listener configuartion to enable connections to and from the RMAN catalog database. This was a pretty straight forward thing but I unfortunately skipped a key configuration which caused the error ORA-12514 every time I tried to connect to the Catalog DB from any of the others.

Note that #tnsping CATDB was successful, which got me more confused and at the same time convinced that all was well with the SERVICE_NAME definition.

I also ran #lsnrctl service and I could see the CATDB service running.

However, the problem was the SERVICE_NAME I defined for the Catalog DB. 

Original configuration in tnsnames.ora

CATDB=
        (DESCRIPTION=
                (ADDRESS=(PROTOCOL = TCP)(HOST = 1.1.1.1)(PORT = 1521))
                (CONNECT_DATA = (SERVICE_NAME = CATDB))

Correct configuration

CATDB=
        (DESCRIPTION=
                (ADDRESS=(PROTOCOL = TCP)(HOST = 1.1.1.1)(PORT = 1521))
                (CONNECT_DATA = (SERVICE_NAME = CATDB.xxxxxx.local))

I did not fully specify the service name!

This resolved my problem and I was able to connect to the Catalog DB.

ORA-01180: can not create datafile 1 ORA-01110: data file 1: '/dir/system01.dbf' during RMAN restore.

I got this error while trying do a full database restore to another host. I have always done same without issues.

Changes:
The source database is now the primary in a ADG set up.
Source database now has FRA confirgured.
Source database opened with reset logs during a migration to a new host as the original host had to be maintained.

Road to resolution:

At first I checked the directory on the new host to confirm that oracle user has appropriate permission to write to the directory.

I also checked to ensure that the backup sets are fully cataloged and that the restored control file is the most recent.

I tried the process one more time and still got the same error.

I went on MOS and found two notes ID 1265151.1 & ID 392237.1.
 
RMAN restore fails with ORA-01180: can not create datafile 1 (Doc ID 1265151.1)


RMAN restore of database fails with ORA-01180: Cannot create datafile 1 (Doc ID 392237.1)

"The current incarnation only contains incremental level 1 backups of the database.
There are NO level 0 backups in this incarnation.
As the physical datafiles do not exist RMAN attempts to create the physical files but this is NOT allowed if the file belongs to the SYSTEM tablespace."

solution: Reset the database to the previous incarnation and run the restore and recovery again:
 select count(*) from v$database_incarnation;

  COUNT(*)
----------
         4
SQL> select INCARNATION#, RESETLOGS_TIME from  v$database_incarnation order by RESETLOGS_TIME desc;

INCARNATION# RESETLOGS_TIME
------------ --------------------
           4 02:28:32 19:aug:2014
           2 04:23:11 08:aug:2014
           3 10:03:41 25:jul:2014
           1 07:01:36 09:dec:2011

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

>rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Tue Sep 9 13:04:29 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: XXXXXX (DBID=111111111, not open)

RMAN> reset database to incarnation 2;

RMAN>restore database;


Walla!!! it was successful.

ORA-12516: TNS:listener could not find available handler with matching protocol

I got this error while trying to connect to a database from one of our new application servers.

oerr returned the following:

$ oerr ora 12516
12516, 00000, "TNS:listener could not find available handler with matching protocol stack"
// *Cause: None of the known and available service handlers for the given
// SERVICE_NAME support the client's protocol stack: transport, session,
// and presentation protocols.
// *Action: Check to make sure that the service handlers (e.g. dispatchers)
// for the given SERVICE_NAME are registered with the listener, are accepting
// connections, and that they are properly configured to support the desired
// protocols.


This message did not help as I had ensured that the tnames.ora is properly configured and I was sure of the SERVICE name.

SOLUTION:

 I increased processes parameter to 3000.

alter system set processes=3000 scope=spfile;

The database was restarted. Then the error below showed up!

ORA-00371: not enough shared pool memory, should be atleast 5207647846 bytes

This was fixed by modifying the shared_pool_memory parameter to a little over the suggested value above.

Issued solved!

renaming an Oracle 11.2.0.2 database

To change an Oracle 11.2.0.2 DB name from DB1 to DB2

    STEP 1: Backup the database.
   
    STEP 2: Do a cleab shutdown of the database and mount the database after:

        SQL> SHUTDOWN IMMEDIATE
        SQL> STARTUP MOUNT

    STEP 3: Invoke the DBNEWID utility (nid) specifying the new DBNAME from the command line using a user with SYSDBA privilege:

        nid TARGET=sys/password@DB1 DBNAME=DB2

        # nid TARGET=sys/password@TSH1 DBNAME=DB2
        DBNEWID: Release 11.2.0.2.0 - Production on Wed Aug 27 12:12:52 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

########################
########################
########################
########################
########################
########################
########################

        Database name changed to DB2.
        Modify parameter file and generate a new password file before restarting.
        Database ID for database DB2 changed to 3437107090.
        All previous backups and archived redo logs for this database are unusable.
        Shut down database and open with RESETLOGS option.
        Succesfully changed database name and ID.
        DBNEWID - Completed succesfully.

    STEP 4: Create new pfile from spfile.

    Shutdown the database:

        SHUTDOWN IMMEDIATE

    STEP 5: Modify the DB_NAME parameter in the initialization parameter file to reflect the new DB name .

      
    STEP 6: Create a new password file:

      
    orapwd file=$ORACLE_HOME/dbs/orapwDB2 password=choicepassword


    STEP 7: Rename the SPFILE to match the new DBNAME.
   

    STEP 8: On UNIX/Linux, reset the ORACLE_SID environment variable:
   

        ORACLE_SID=DB2; export ORACLE_SID

    STEP 9: Alter the listener.ora and tnsnames.ora setting to match the new database name and restart the listener:
   

        lsnrctl reload

    STEP 10: Open the database with RESETLOGS:
   

        STARTUP MOUNT
        ALTER DATABASE OPEN RESETLOGS;

    STEP 11: Backup the database.



 ##########################################################################################################################

Issues encountered.



execution of DBNEWID failed initially with the error shown below:

NID-00111: Oracle error reported from target database while executing
    begin       dbms_backup_restore.nidprocessdf(:fno, :istemp, :skipped, :idchged,                                                                          :nmchged);    end;
ORA-01000: maximum open cursors exceeded



Change of database name and database ID failed.
Must finish change or REVERT changes before attempting any database operation.
DBNEWID - Completed with errors.



I tried to reverse my action using

 nid target=/ REVERT=Y

This also failed with the error below:

NID-00111: Oracle error reported from target database while executing
    begin       dbms_backup_restore.nidprocessdf(:fno, :istemp, :skipped, :idchged,                                                                          :nmchged);    end;
ORA-01000: maximum open cursors exceeded



Reversion of database name and database ID failed.
Must finish REVERT changes before attempting any database operation.
DBNEWID - Completed with errors.



Resolution:

I had to correct the error ORA-01000: maximum open cursors exceeded

SYS> show parameter open_cursors

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     100

SYS> alter system set open_cursors=1024 SCOPE=BOTH;

System altered.

SYS> show parameter open_cursors

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     1024


The rename ran successfully after correcting this error.

###########################################################################################################

BEST PRACTICES:

Take controlfile backups – binary and trace backups before the bid operation.

If there are tempfiles in the dictionary, make sure they are accessible. Check both v$tempfile and dba_temp_files, not just dba_temp_files. The nid operation tries to update the dbname in tempfiles too and if it is not found, the operation fails. If there are tempfiles that doesn’t exist, it is better to drop them from the dictionary before the nid operation.

nid requires the database to be mounted in exclusive mode, so set the cluster_database=false if it is RAC(mandatory, else nid will say that the DB needs to be mounted in exclusive mode.

Check the open_cursors parameter and it is safe to be set to a very high value during the operation, this needs to be reverted back to the original value after nid operation.

SOURCES:

http://www.dbapundits.com/blog/oracle-utilities/nid-failure-and-open_cursors/
http://www.dba-oracle.com/t_rename_database_oracle_sid.htm

ORA-12549: TNS: Operating System Resource Quota Exceeded

I faced this error on a new standby database server running on AIX 6.1. The error is however pretty self-explanatory.

I ensured that ulimit values for the oracle user are appropriate.
# ulimit -a
time(seconds)        unlimited
file(blocks)         unlimited
data(kbytes)         unlimited
stack(kbytes)        unlimited
memory(kbytes)       unlimited
coredump(blocks)     unlimited
nofiles(descriptors) unlimited
threads(per process) unlimited
processes(per user)  unlimited

Second I checked the current value of maxuproc for all users. It was at default.

# lsattr -El sys0 |grep maxuproc
maxuproc        128              Maximum number of PROCESSES allowed per user      True

128 is the default value, this will be increased.

# chdev -l sys0  -a maxuproc=10240

# lsattr -El sys0 |grep maxuproc
maxuproc        10240             Maximum number of PROCESSES allowed per user      True

I rebooted the LPAR easily as this was not a production server.

Problem solved!

ORA-1652: unable to extend temp segment by 128 in tablespace TEMP

Hi all,

Been noticing this error in my alert log for a while. Then I got a call from one user complaining of a failing query..this is a scheduled query that runs early in the mornings daily.

Solution to my case:

1. I looked through my alert log to see if I will find any special incidents around the period which these errors were encountered. I didn't find any!

2. I looked at AWR and ADDM reports for the periods around this occurrence. No serious observation except that I found "Undersized PGA". This I did not take seriously.

3. I immediately proceeded to increase the size of my TEMP tablespace by resizing the one of the datafiles. This had no effect whatsoever because the error continued to show up in the alert logs and the query kept failing.

4. I had them schedule the query to a period when there's less transaction on the database (weekend). It still failed. This morning, I came in a looked at my alert logs only to find several incidents of ORA-1652 all through the weekend! This was a period when activity on the DB is not anywhere near optimal.

6. All the while, "Undersized PGA" kept showing up in my ADDM reports.So I decided to increase my pga_aggregate_target. We ran the query again and it worked!

Lesson:

Pay attention to seemingly unimportant alerts especially from your ADDM reports. I must add that all the while that I had "Undersized PGA", it stated that this had less than 2% impact on my peformance.

OUI - 10022: Fixing a corrupt OraInventory

I ran into this error while deploying OEM 12c agent to a remote AIX 6.1 server running Oracle DB 11.2.0.2.0.

The following error came up:

OUI-10022:The target area $ORACLE_BASE/oraInventory cannot be used because it is in an invalid state.. This implies my global oracle inventory is corrupt. First I checked to ensure that I have the appropriate permission (755) on the oraInventory direction. This was in place so permissions could not be the issue.

For AIX/UNIX, the OraInventory can be located by checking /etc/oraInst.loc. Having located the file, the contents looked like this:

inventory_loc=<location_for_oraInventory>
inst_group=<name_of_group_for_installtion> 

I navigated to the inventory location and renamed the existing oraInventory directory.

mv oraInventory oraInventory_orig

Next, I navigated to $ORACLE_HOME/oui/bin where I ran OUI as follows:

 ./runInstaller -silent -ignoreSysPrereqs -attachHome ORACLE_HOME="oracle_home_directory" ORACLE_HOME_NAME="OraDB11g_home1"


Verify the newly created oraInventory using:

$ORACLE_HOME/OPatch/opatch lsinventory -detail