Solved

Ora-12505 with a standby database?

Posted on 2004-03-23
3
2,450 Views
Last Modified: 2013-12-11
Hi there everybody,

I am running a primary and a standby database both on 8.1.7.4, win2k server and on separate servers.

I have configured the standby database so it in managed recover and is waiting for an archive log.

I seem to be getting an ORA-12505 error message when the primary database is trying to send the log to the standby database.

ORA-12505: TNS:listener could not resolve SID given in connect descriptor

The primary databases TNSlistener.ora:

# TNSNAMES.ORA Network Configuration File: D:\Oracle\ora817\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

EXTPROC_CONNECTION_DATA.*********** =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC3))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

INST1_HTTP.************* =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = DELL_SERVER)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = SHARED)
      (SERVICE_NAME = MODOSE)
      (PRESENTATION = http://admin)
    )
  )

TR2.*************** =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = DELL_SERVER)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = tr2)
    )
  )

STANDBY1 = (DESCRIPTION= (ADDRESS=(PROTOCOL=TCP)
      (PORT=5112) (HOST=TEST-SERVER))
      (CONNECT_DATA=(SID=standby1)))

The standby databases listener.ora

# LISTENER.ORA Network Configuration File: C:\oracle\ora81\network\admin\listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = test-server)(PORT = 1521))
      )
    )
      (ADDRESS_LIST=
         (ADDRESS=(PROTOCOL=TCP)(PORT=5112)(HOST=test-server)))

    (DESCRIPTION =
      (PROTOCOL_STACK =
        (PRESENTATION = GIOP)
        (SESSION = RAW)
      )
      (ADDRESS = (PROTOCOL = TCP)(HOST = test-server)(PORT = 2481))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:\oracle\ora81)
      (PROGRAM = extproc)
    )
   (SID_DESC=
    (SID_NAME=standby1)
     (ORACLE_HOME=\ORACLE\ORA81))

    (SID_DESC =
      (GLOBAL_DBNAME = standby1.global)
      (ORACLE_HOME = C:\oracle\ora81)
      (SID_NAME = standby1)
    )
  )

STANDBY_LISTENER = (ADDRESS_LIST=
 (ADDRESS=(PROTOCOL=tcp)(PORT=5112)(HOST=test-server)))

SID_LIST_STBY1_LISTENER = (SID_LIST=
      (SID_DESC=(SID_NAME=standby1)(ORACLE_HOME=\ORACLE\ORA81)))

The only other error that I could find on either of the server is a: TNS-12564: TNS:connection refused in the sqlnet.ora on the primary database.

I really need a fresh pair of eyes to look that this as I am sure that I am missing something very simple.

Thanks in advance

Eddy
0
Comment
Question by:teched1000
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 8

Accepted Solution

by:
baonguyen1 earned 300 total points
ID: 10664012
Can you try to modify:

1. tnsnames.ora:

Modify this line:

STANDBY1.xxx =
(DESCRIPTION= (ADDRESS=(PROTOCOL=TCP)
     (PORT=5112) (HOST=TEST-SERVER))
     (CONNECT_DATA=(SID=standby1))
)

With xxx is the default domain in sqlnet.ora file

2. Modiy the listener.ora:

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = test-server)(PORT = 1521))
      )
    )
    #  (ADDRESS_LIST=
    #     (ADDRESS=(PROTOCOL=TCP)(PORT=5112)(HOST=test-server)))

    (DESCRIPTION =
      (PROTOCOL_STACK =
        (PRESENTATION = GIOP)
        (SESSION = RAW)
      )
      (ADDRESS = (PROTOCOL = TCP)(HOST = test-server)(PORT = 2481))
    )
  )
#Add more:
LISTENER_STB=
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = test-server)(PORT = 5112))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
      )
    )
  )
################

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:\oracle\ora81)
      (PROGRAM = extproc)
    )
   (SID_DESC=
    (SID_NAME=standby1)
     (ORACLE_HOME=\ORACLE\ORA81))

 #   (SID_DESC =
 #     (GLOBAL_DBNAME = standby1.global)
 #     (ORACLE_HOME = C:\oracle\ora81)
 #     (SID_NAME = standby1)
 #   )
  )

#STANDBY_LISTENER = (ADDRESS_LIST=
# (ADDRESS=(PROTOCOL=tcp)(PORT=5112)(HOST=test-server)))

#SID_LIST_STBY1_LISTENER = (SID_LIST=
#     (SID_DESC=(SID_NAME=standby1)(ORACLE_HOME=\ORACLE\ORA81)))

SID_LIST_LISTENER_STB =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = stanby1)
      (ORACLE_HOME = C:\oracle\ora81)
      (SID_NAME = stanby1)
    )
  )

Then start the listener for the stanby:

C:\lsnrctl start listener_stb

Hope this helps
0
 
LVL 48

Assisted Solution

by:schwertner
schwertner earned 200 total points
ID: 10665545
Use NetConfig or Easyconfig GUI tools on the PRIMARY site and create an alias (STANDBY) to the Standby database. Of course the primary should work at this time to do the test.


1)  Set the initialization parameters for the primary database:
 
       Primary INIT.ORA:
   
       log_archive_dest_1='LOCATION=<Path local Archivelogs are stored'
       log_archive_dest_2='SERVICE=standby reopen=60'
       log_archive_dest_state_1=enable
       log_archive_dest_state_2=enable
       log_archive_format=arch_%t_%s.arc
       log_archive_start=true


    Create the standby INIT.ORA and set the initialization parameters for the  
    standby database. Depending on your configuration, you may need to set filename  
    conversion parameters.
   
       Standby INIT.ORA:
       
       log_archive_dest_1='LOCATION=<Path Standby Archive Logs are stored>'
       log_archive_dest_state_1=enable
       log_archive_format= arch_%t_%s.arc (MUST be the same as on Primiary !!)
       log_archive_start=true
       standby_archive_dest=<Path Standby Archive Logs are stored>
       db_name = <name of the primary DB>  --see the primary INIT.ORA file

    Example: STANDBY_ARCHIVE_DEST= /arc_dest/

    NOTE:
    STANDBY_ARCHIVE_DEST
    Used solely by a standby database in managed recovery mode to determine the
    location for the archived logs received from the primary database. Managed recovery
    mode uses this value along with LOG_ARCHIVE_FORMAT to generate the fully
    qualified standby database log filenames and stores the filenames in the standby
    control file. Managed recovery uses this data to drive recovery.
    For managed recovery, set STANDBY_ARCHIVE_DEST and LOG_ARCHIVE_DEST
    to the same value. If manual recovery is required because of a gap sequence, copy
    the missing log to the same location as the other logs and recover manually. You
    can then place the standby database back into managed recovery mode.


WINDOWS NT/2000: Due to a bug the names of the locations specified in  
    these Parameters MUST be in UPPERCASE. Otherwise, you will get an
    ORA-01157: cannot identify/lock data file when you try to recover.


    Example TNSNAMES.ORA (on Primary Host):
   
     Standby=
     (DESCRIPTION =
      (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 1521))
      )
      (CONNECT_DATA =
       (SID = STANDBY)
       (SERVER = DEDICATED)
      )
     )
 
    Example LISTENER.ORA (on Standby Host):
   
     SID_LIST_LISTENER =
      (SID_LIST =
       (SID_DESC =
        (GLOBAL_DBNAME = STANDBY.world)
        (ORACLE_HOME = $ORACLE_HOME)
        (SID_NAME = Standby)
       )
      )


2) Verify that automatic archiving is occurring:
 
    On the Primary database switch a log and verfy that it has been shipped  
    properly using the v$archive_dest view.
 
       SQL> alter system switch logfile;
 
       System altered.
 
       SQL> select status, error from v$archive_dest where dest_id=2;
 
       STATUS    ERROR
       --------- -------------------------------------------------------
       VALID
 
       SQL>

0
 

Author Comment

by:teched1000
ID: 10668286
Hi there baonguyen1 and schwertner,

Thanks for the quick responce.

Managed to find what was wrong. After of course trying to trying both your solutions.

The service entry in the primary databases was:

log_archive_dest_4 = 'SERVICE=standby1'
instead of
log_archive_dest_4 = 'SERVICE=STANDBY1'

Very anoying

But thanks for your input

Eddy
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question