Solved

Ora-12505 with a standby database?

Posted on 2004-03-23
3
2,426 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
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 47

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

773 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