Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Ora-12505 with a standby database?

Posted on 2004-03-23
3
Medium Priority
?
2,503 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 1200 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 800 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

Technology Partners: 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

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

722 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