Thursday, October 31, 2013

ORA-12154: TNS: could not resolve the connect identifier specified



ORA-12154: TNS: could not resolve the connect identifier specified

ORA 12154: TNS: Listener does not currently know of service requested

Checklist:

ORA 12154 looks a generic & very much known but getting cause for this error is the trick to resolve it quickly. Below 8 point checklist will ensure all basic's are followed & 9th point will be the final one which will take you closure to the resolution

1. Verify if you have compatible client version with RDBMS db version

10g, 11g or higher oracle version client are compatible with 9i or higher database versions but 8i or lower oracle databases need specific versions of oracle clients only. 

When 32 bit oracle client software is installed on 64 bit windows server it appends its own characters/braces() in connection description and results in a exception.Such issue can be tackled by installing 32 bit client in any other drive like d: , e: but not inside c: drive program files (64)

2. Client Server has only single oracle client binary installed

Mostly seen in windows machines previous oracle clients are not cleanly removed & reflecting multiple client in the same server. Need to remove all old client along with their directory structures correctly if not done by a clean removal method & have a reboot of the system before proceeding with new client installation 

3. tnsnames.ora file contains proper TNS Entry formatting

Having correct TNS entry is the key to resolving ORA-12154 error, make it as simple as below

mydb =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP) (HOST = hostname) (PORT = 1527))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = mydb)
    )
  )

If you suspect there are any missing braces or unknown issue in formatting just use below single liner

mydb=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname)(PORT = 1527)))(CONNECT_DATA = (SERVICE_NAME = mydb)))


and you can easily test its resolution  through TNS alias or using description itself as below

Yes this single liner will work , I am not keen to show any example snaps here as I believe you will test yourself instead of assuming it from my snaps

tnsping "(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname)(PORT = 1527)))(CONNECT_DATA = (SERVICE_NAME = mydb)))


4. Host which you are trying to connect is pingable & added in /etc/hosts

Coming from point 3 one of the basic check should be the resolution of hostname specified in HOST clause of TNS entry which can be an IP address or domain/DNS , LDAP or /etc/hosts resolvable entry

Whichever it is make sure it is the correct one

5. telnet Host Port is working

Checking correctness of hostname specified in HOST, we just have ping & telnet.
If it is pinging then check telnet to DB port on which listener is listening

telnet <IP> <Port>

In most of the cases ping & telnet seems to be working but error does not disappear , don’t lose the battle yet still few more points to be checked

6. DB service/Listener you are trying to connect is up & running on hosted db server

Yes this check looks obvious but do not skip it, may be listener is up on your DB server or although it is up but running with wrong IP (may be cluster IP) or hostname. So verify & compare TNS entry of the server against the client address and confirm its resolution to yourself

7. tnsping <TNS_Alias> is working from client

tnsping is a great tool to troubleshoot oracle net relevant issues , verify if it resolves through correct address description & within stipulated time

8. sqlplus <user>@<TNS_Alias>/<password> is working

Using sqlplus will isolate the issue if ORA-12154 is faced only in third party tools or application . sqlplus is an inbuilt oracle application development tool. Traversing it via tns alias will allow to replicate the issue & can be tested/fixed by yourself only rather than asking user to replicate it for you.

9. Troubleshoot using tracing 

If above all basic check has not helped then use tracing for sqlplus session by following the link, which will surely help to get the cause & can be fixed accordingly 

Before enabling tracing for the session make sure you are able to replicate the error which will get capture in the trace file along with the error around it


Let me know if all above doesn't help !!

Tuesday, October 29, 2013

BR0123E Format error found in line at location arch_last_get-10

Error:

brarchive -u / -c -d util_file -r /oracle/PRD/102_64/dbs/initPRD-logs.utl -s
BR0002I BRARCHIVE 7.00 (40)
BR0006I Start of offline redo log processing: aemjygtf.sve 2013-10-26 14.42.59
BR0484I BRARCHIVE log file: /oracle/PRD/saparch/aemjygtf.sve
BR0477I Oracle pfile /oracle/PRD/102_64/dbs/initPRD.ora created from spfile /oracle/PRD/102_64/dbs/spfilePRD.ora
BR0123E Format error found in line 1830934 of /oracle/PRD/saparch/archPRD.log at location arch_last_get-10
BR0121E Processing of log file /oracle/PRD/saparch/archPRD.log failed
BR0016I 0 offline redo log files processed, total size 0.000 MB
BR0007I End of offline redo log processing: aemjygtf.sve 2013-10-26 14.43.04
BR0280I BRARCHIVE time stamp: 2013-10-26 14.43.04
BR0005I BRARCHIVE terminated with errors


Cause:

1. Archive destination has reached 100% & DBA has removed or zipped the archive files causing mismatch/unavailability of archive sequence for brarchive backup

2. /oracle/PRD/saparch/archPRD.log file is corrupted 

3.  /oracle/PRD/saparch/archPRD.log file has got unwanted characters due to improper editing 

Solution:

Step 1. create a backup copy of arch<SID>.log 

cp /oracle/PRD/saparch/archPRD.log /oracle/PRD/saparch/archPRD.log_bkp

Step 2. check the line number mentioned in the error , go to archPRD.log using vi :1830934 & take appropriate action. Possibly it may have blank line or unwanted characters.

BR0123E Format error found in line 1830934 of /oracle/PRD/saparch/archPRD.log at location arch_last_get-10

Try executing backup again , if still same error then go to step 3

Step 3. It seems archive sequence missing issue , append available archive sequence at below highlighted line at the end of the file to direct brarchive to start backup sequence post 33999. Make sure it is correctly formatted as per similar looking old lines 

#
#* PRD  util_file  aemjntmr svd  2013-10-24 11.24.21  2013-10-24 11.29.04  0  ...........    339316   339376        0        0  ------- 7.00 (40)  @0759153586
#
#* PRD  util_file  aemjozlm svd  2013-10-24 17.24.22  2013-10-24 17.24.32  3  ...........         0        0        0        0  ------- 7.00 (40)  @0759153586

#ARCHIVE.. 339999  /oracle/PRD/oraarch/PRDarch1_339999_759153586.arc  2013-10-19 04.57.39  30245888     7994205640697  1
#SAVED.... aelfptms svd  *VXF1368919944    2013-10-19 05.04.12 ........... ............
#COPIED... ........ ...  ................. .......... ........ ........... ............
#DELETED.. aelfptms svd  2013-10-19 05.04.12#
#* PRD  util_file  aemjyhks sve  2013-10-26 14.50.34  2013-10-26 14.50.36  3  ...........    339998   339999        0        0  ------- 7.00 (40)  @0759153586
#



Try executing backup again , it will show messages like
"BR0017W Offline redo log file ' ' not found" which is ignorable & same has been skipped due their unavailability as directed by us. 

Backup should execute successfully now & will avoid error code 6  "backup failed to back up the requested files" in netbackup logs


brarchive -u / -c -d util_file -r /oracle/PRD/102_64/dbs/initPRD-logs.utl -s
BR0002I BRARCHIVE 7.00 (40)
BR0006I Start of offline redo log processing: aemjyhui.sve 2013-10-26 14.54.44
BR0484I BRARCHIVE log file: /oracle/PRD/saparch/aemjyhui.sve
BR0477I Oracle pfile /oracle/PRD/102_64/dbs/initPRD.ora created from spfile /oracle/PRD/102_64/dbs/spfilePRD.ora


BR0017W Offline redo log file '/oracle/PRD/oraarch/PRDarch1_265934_759153586.arc' not found
BR0017W Offline redo log file '/oracle/PRD/oraarch/PRDarch1_265935_759153586.arc' not found
.

.
.
BR0017W Offline redo log file '/oracle/PRD/oraarch/PRDarch1_339998_759153586.arc' not found
BR0017W Offline redo log file '/oracle/PRD/oraarch/PRDarch1_339999_759153586.arc' not found
 

BR0280I BRARCHIVE time stamp: 2013-10-26 15.18.55
BR0008I Offline redo log processing for database instance: PRD
BR0009I BRARCHIVE action ID: aemjyikw
BR0010I BRARCHIVE function ID: svd
BR0048I Archive function: save_delete
BR0011I 1167 offline redo log files found for processing, total size 33098.799 MB
BR0130I Backup device type: util_file
BR0109I Files will be saved by backup utility
BR0126I Unattended mode active - no operator confirmation required

BR0280I BRARCHIVE time stamp: 2013-10-26 15.18.55
BR0229I Calling backup utility with function 'backup'...
BR0278I Command output of 'backint -u PRD -f backup -i /oracle/PRD/saparch/.aemjyikw.lst -t file -p /oracle/PRD/102_64/dbs/initPRD-logs.utl -c':
WARNING: sort_backup_type not specified in .utl file
WARNING: default sort_backup_type to <size> for backup
********************************************************************************
Program:                backint 7.1
Input File:             /oracle/PRD/saparch/.aemjyikw.lst
Profile:                /oracle/PRD/102_64/dbs/initPRD-logs.utl
Function:               BACKUP
Backup Type:            BACKUP_FILE
********************************************************************************
[16753.01] 15:19:07 INF - Processing /oracle/PRD/oraarch/PRDarch1_340000_759153586.arc
15:19:07.870 [16753] <1> :  Backup of </oracle/PRD/oraarch/PRDarch1_340000_759153586.arc> is in progress.
[16753.01] 15:19:10 INF - Data buffer size = 262144
[16753.01] 15:19:11 INF - Using Media ID @AAAAD for Backup ID SAPDB_1382780941
[16753.01] 15:19:15 INF - Processing /oracle/PRD/oraarch/PRDarch1_340001_759153586.arc
15:19:16.874 [16753] <1> :  Backup of </oracle/PRD/oraarch/PRDarch1_340001_759153586.arc> is in progress.


Thursday, October 24, 2013

BR0439E No offline redolog files found in ..processing for device type TAPE/BACKINT/RMAN in recovery interval

Error:

brrestore -a 265931-265933=/oracle/PRD/oraarch/ -r /oracle/PRD/102 _64/dbs/initPRD.utl

BR0401I BRRESTORE 7.20 (33)
BR0405I Start of file restore: remjonan.rsa 2013-10-24 15.04.25
BR0484I BRRESTORE log file: /oracle/PRD/sapbackup/remjonan.rsa

BR0461W Redolog sequence number in line 1830920 of /oracle/PRD/saparch/archPRD.log not ascending
BR0462W All lines in /oracle/PRD/saparch/archPRD.log till line 1830920 will be ignored
BR0461W Redolog sequence number in line 1831168 of /oracle/PRD/saparch/archPRD.log not ascending
BR0462W All lines in /oracle/PRD/saparch/archPRD.log till line 1831168 will be ignored

BR0439E No offline redolog files found in /oracle/PRD/saparch/archPRD.log for processing for device type TAPE/BACKINT/RMAN in recovery interval of 30 days


BR0406I End of file restore: remjonan.rsa 2013-10-24 15.04.27
BR0280I BRRESTORE time stamp: 2013-10-24 15.04.27
BR0404I BRRESTORE terminated with errors


OR

brrestore -a 265931-265982=/oracle/PRD/oraarch/ -r /oracle/PRD/102 _64/dbs/initPRD.utl
 
BR0401I BRRESTORE 7.20 (33)
BR0405I Start of file restore: remjomos.rsa 2013-10-24 14.59.18
BR0484I BRRESTORE log file: /oracle/PRD/sapbackup/remjomos.rsa

BR0461W Redolog sequence number in line 1830920 of /oracle/PRD/saparch/archPRD.log not ascending
BR0462W All lines in /oracle/PRD/saparch/archPRD.log till line 1830920 will be ignored

BR0461W Redolog sequence number in line 1831168 of /oracle/PRD/saparch/archPRD.log not ascending
BR0462W All lines in /oracle/PRD/saparch/archPRD.log till line 1831168 will be ignored
BR0438W Redolog sequence number 265931 not found in /oracle/PRD/saparch/archPRD.log
BR0438W Redolog sequence number 265932 not found in /oracle/PRD/saparch/archPRD.log
BR0438W Redolog sequence number 265933 not found in /oracle/PRD/saparch/archPRD.log
 

Solution:

Step 1. Make a backup copy of archPRD.log file

cp  /oracle/PRD/saparch/archPRD.log /oracle/PRD/saparch/archPRD.log_bkp

Step 2. Identify sequence line entry in archPRD.log file for the archive files to be restored


#ARCHIVE.. 265931  /oracle/PRD/oraarch/PRDarch1_265931_759153586.arc  2013-05-19 04.50.33  26548224     7994146315208  1
#SAVED.... aelfptms svd  *VXF1368919944    2013-05-19 05.04.11 ........... ............
#COPIED... ........ ...  ................. .......... ........ ........... ............
#DELETED.. aelfptms svd  2013-05-19 05.04.11
#
#ARCHIVE.. 265932  /oracle/PRD/oraarch/PRDarch1_265932_759153586.arc  2013-05-19 04.57.24  28307968     7994149385123  1
#SAVED.... aelfptms svd  *VXF1368919944    2013-05-19 05.04.11 ........... ............
#COPIED... ........ ...  ................. .......... ........ ........... ............
#DELETED.. aelfptms svd  2013-05-19 05.04.11
#
#ARCHIVE.. 265933  /oracle/PRD/oraarch/PRDarch1_265933_759153586.arc  2013-05-19 04.57.39  30245888     7994205640697  1
#SAVED.... aelfptms svd  *VXF1368919944    2013-05-19 05.04.12 ........... ............
#COPIED... ........ ...  ................. .......... ........ ........... ............
#DELETED.. aelfptms svd  2013-05-19 05.04.12
#


Step 3. Modify date as recent (within 30 days) and paste it at the end of the archPRD.log file , save the file . Changed 2013-05-19 to 2013-10-19

#ARCHIVE.. 265931  /oracle/PRD/oraarch/PRDarch1_265931_759153586.arc  2013-10-19 04.50.33  26548224     7994146315208  1
#SAVED.... aelfptms svd  *VXF1368919944    2013-10-19 05.04.11 ........... ............
#COPIED... ........ ...  ................. .......... ........ ........... ............
#DELETED.. aelfptms svd  2013-10-19 05.04.11
#
#ARCHIVE.. 265932  /oracle/PRD/oraarch/PRDarch1_265932_759153586.arc  2013-10-19 04.57.24  28307968     7994149385123  1
#SAVED.... aelfptms svd  *VXF1368919944    2013-10-19 05.04.11 ........... ............
#COPIED... ........ ...  ................. .......... ........ ........... ............
#DELETED.. aelfptms svd  2013-10-19 05.04.11
#
#ARCHIVE.. 265933  /oracle/PRD/oraarch/PRDarch1_265933_759153586.arc  2013-10-19 04.57.39  30245888     7994205640697  1
#SAVED.... aelfptms svd  *VXF1368919944    2013-10-19 05.04.12 ........... ............
#COPIED... ........ ...  ................. .......... ........ ........... ............
#DELETED.. aelfptms svd  2013-10-19 05.04.12
#


Step 4.  execute the brrestore command


brrestore -a 265931-265933=/oracle/PRD/oraarch/ -r /oracle/PRD/102 _64/dbs/initPRD.utl
BR0401I BRRESTORE 7.20 (33)
BR0405I Start of file restore: remjoneo.rsa 2013-10-24 15.06.10
BR0484I BRRESTORE log file: /oracle/PRD/sapbackup/remjoneo.rsa

BR0461W Redolog sequence number in line 1830920 of /oracle/PRD/saparch/archPRD.log not ascending
BR0462W All lines in /oracle/PRD/saparch/archPRD.log till line 1830920 will be ignored
BR0414I Offline redolog files for restore of database instance PRD:
/oracle/PRD/oraarch/:
265931,265932,265933

BR0280I BRRESTORE time stamp: 2013-10-24 15.06.13
BR0256I Enter 'c[ont]' to continue, 's[top]' to cancel BRRESTORE:

BR0280I BRRESTORE time stamp: 2013-10-24 15.06.18
BR0257I Your reply: 'c'
BR0259I Program execution will be continued...

BR0280I BRRESTORE time stamp: 2013-10-24 15.06.18
BR0407I Restore of database: PRD
BR0408I BRRESTORE action ID: remjoneo
BR0409I BRRESTORE function ID: rsa
BR0419I Files will be restored from backup: aelfptms.svd 2013-05-19 05.02.14
BR0416I 3 files found to restore, total size 196.816 MB
BR0421I Restore device type: util_file

BR0280I BRRESTORE time stamp: 2013-10-24 15.06.18
BR0256I Enter 'c[ont]' to continue, 's[top]' to cancel BRRESTORE:

BR0280I BRRESTORE time stamp: 2013-10-24 15.06.19
BR0257I Your reply: 'c'
BR0259I Program execution will be continued...

BR0280I BRRESTORE time stamp: 2013-10-24 15.06.19
BR0229I Calling backup utility with function 'restore'...
BR0278I Command output of 'backint -u PRD -f restore -i /oracle/PRD/sapbackup/.remjoneo.lst -t file -p /oracle/PRD/102_64/dbs/initPRD.utl':
********************************************************************************
Program:        backint 7.5
Input File:        /oracle/PRD/sapbackup/.remjoneo.lst
Profile:        /oracle/PRD/102_64/dbs/initPRD.utl
Function:        RESTORE
********************************************************************************
[14722.00] Restore started Thu Oct 24 15:06:20 2013
[14722.00]
[14722.00]
[14722.00] 15:06:21 (819365.xxx) Restore job id 819365 will require 1 image.
[14722.00] 15:06:21 (819365.xxx) Media id 000677 is needed for the restore.
[14722.00]
[14722.00] 15:06:23 (819365.001) Restoring from copy 2 of image created Sun May 19 05:02:24 2013
[14722.00] 15:06:26 (819365.001) TAR STARTED 14788
[14722.00] 15:06:26 (819365.001) INF - If Media id 000677 is not in a robotic library administrative interaction may be required to satisfy this mount request.
[14722.00] 15:06:29 (819365.001) INF - No drives available or requesting resources
[14722.00] 15:06:50 (819365.001) INF - Waiting for mount of media id 000677 on server lvsb-media2 for reading.
[14722.00] 15:07:56 (819365.001) INF - Waiting for positioning of media id 000677 on server lvsb-media2 for reading.
[14722.00] 15:08:50 (819365.001) INF - Beginning restore from server lvsb-media2 to client inmulvsapcdbdev.
[14722.00] 15:08:52 (819365.001) /oracle/PRD/oraarch/PRDarch1_265931_759153586.arc
[14722.00] 15:08:53 (819365.001) /oracle/PRD/oraarch/PRDarch1_265932_759153586.arc
BR0280I BRRESTORE time stamp: 2013-10-24 15.08.54
#ARCHIVE.. /oracle/PRD/oraarch/PRDarch1_265931_759153586.arc  /oracle/PRD/oraarch//PRDarch1_265931_759153586.arc
#RESTORED. VXF1368919944
[14722.00] 15:08:57 (819365.001) /oracle/PRD/oraarch/PRDarch1_265933_759153586.arc
[14722.00] 15:10:02 (819365.001) INF - TAR EXITING WITH STATUS = 0
[14722.00] 15:10:02 (819365.001) INF - TAR RESTORED 3 OF 3 FILES SUCCESSFULLY
[14722.00] 15:10:02 (819365.001) INF - TAR KEPT 0 EXISTING FILES
[14722.00] 15:10:02 (819365.001) INF - TAR PARTIALLY RESTORED 0 FILES
[14722.00] 15:10:02 (819365.001) Status of restore from copy 2 of image created Sun May 19 05:02:24 2013 = the requested operation was successfully completed
BR0280I BRRESTORE time stamp: 2013-10-24 15.10.03
[14722.00] 15:10:03 INF - Server status = 0
[14722.00] 15:10:03 (819365.xxx) INF - Status = the requested operation was successfully completed.
********************************************************************************
BR0280I BRRESTORE time stamp: 2013-10-24 15.10.08
BR0374I 3 of 3 files restored by backup utility
BR0230I Backup utility called successfully
BR0406I End of file restore: remjoneo.rsa 2013-10-24 15.10.08
BR0280I BRRESTORE time stamp: 2013-10-24 15.10.08
BR0403I BRRESTORE completed successfully with warnings



Make sure sequence line formatting is similar as below , any additional hash will lead to BR0438W Redolog sequence number .. not found in /oracle/PRD/saparch/archPRD.log

#ARCHIVE.. 265933  /oracle/PRD/oraarch/PRDarch1_265933_759153586.arc  2013-10-19 04.57.39  30245888     7994205640697  1
#SAVED.... aelfptms svd  *VXF1368919944    2013-10-19 05.04.12 ........... ............
#COPIED... ........ ...  ................. .......... ........ ........... ............
#DELETED.. aelfptms svd  2013-10-19 05.04.12
#