Solved

Ora-12505 with a standby database?

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

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Need help with Oracle syntax 4 42
Pl/SQL Query 31 62
What is the version of ojdbc6.jar 2 24
Schema creation in Oracle12c 6 25
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
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 explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

747 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now