7 Replies Latest reply: Dec 27, 2011 10:34 AM by 756829 RSS

    RMAN-04004: error from recovery catalog database: ORA-12170: TNS:Connect ti

    756829
      Hi:

      Last night' RMAN backup error out: "RMAN-04004: error from recovery catalog database: ORA-12170: TNS:Connect timeout".

      I can sqlplus to the target database and the catalog database. The listener is up. What is the problem? What else I should check?

      Thank you for your help in advance.
        • 1. Re: RMAN-04004: error from recovery catalog database: ORA-12170: TNS:Connect ti
          Rajesh.Rathod
          Hi,

          Error RMAN-04004: error from recovery catalog database: ORA-12170: TNS:Connect timeou.


          can you ps check whether listener of catalog database is up ? it seems that catalog database listener is down.

          also check whether catalog database is in open mode ( read and write mode ).

          Edited by: Rajesh.Rathod on Dec 23, 2011 7:01 AM
          • 3. Re: RMAN-04004: error from recovery catalog database: ORA-12170: TNS:Connect ti
            Rajesh.Rathod
            suppose catalog database name is orcl

            then check (1)execute "tnsping orcl"
            this execution should give messege like
            --------------------------------------------
            [oracle@rac3 ~]$ tnsping orcl

            TNS Ping Utility for Linux: Version 10.2.0.5.0 - Production on 23-DEC-2011 20:40:50

            Copyright (c) 1997, 2010, Oracle. All rights reserved.

            Used parameter files:
            /opt/app/oracle/product/db/10.2.1/network/admin/sqlnet.ora


            Used TNSNAMES adapter to resolve the alias
            Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac3.rajesh.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
            OK (10 msec)

            -----------------------------
            this means service name working


            (2) go to server on which catalog db running , execute as oracle user (sw owner ) " lsnrctl status " output should be like

            -------------------------------------
            oracle@rac3 ~]$ lsnrctl status

            LSNRCTL for Linux: Version 10.2.0.5.0 - Production on 23-DEC-2011 20:42:00

            Copyright (c) 1991, 2010, Oracle. All rights reserved.

            Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
            STATUS of the LISTENER
            ------------------------
            Alias LISTENER
            Version TNSLSNR for Linux: Version 10.2.0.5.0 - Production
            Start Date 23-DEC-2011 20:40:19
            Uptime 0 days 0 hr. 1 min. 41 sec
            Trace Level off
            Security ON: Local OS Authentication
            SNMP OFF
            Listener Parameter File /opt/app/oracle/product/db/10.2.1/network/admin/listener.ora
            Listener Log File /opt/app/oracle/product/db/10.2.1/network/log/listener.log
            Listening Endpoints Summary...
            (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
            (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac3.rajesh.com)(PORT=1521)))
            Services Summary...
            Service "PLSExtProc" has 1 instance(s).
            Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
            Service "orcl" has 1 instance(s).
            Instance "orcl", status READY, has 1 handler(s) for this service...
            Service "orclXDB" has 1 instance(s).
            Instance "orcl", status READY, has 1 handler(s) for this service...
            Service "orcl_XPT" has 1 instance(s).
            Instance "orcl", status READY, has 1 handler(s) for this service...
            The command completed successfully
            [oracle@rac3 ~]$


            above status means listener is running
            ------------------------------------------------
            (check db is in open mode )

            when we connect to catalog database as 'sys' user use command
            (login to sqlplu using sys as sysdba)
            select name , open_mode from v$database ;

            ---------------------------------
            SQL> select name , open_mode from v$database ;

            NAME OPEN_MODE
            --------- ----------
            ORCL READ WRITE

            SQL>
            • 4. Re: RMAN-04004: error from recovery catalog database: ORA-12170: TNS:Connect ti
              756829
              Thanks.

              We have: "RMAN TARGET / CATALOG oratest/orapass@rmandb1". How to find where is it?

              Edited by: user9231603 on Dec 23, 2011 7:40 AM
              • 5. Re: RMAN-04004: error from recovery catalog database: ORA-12170: TNS:Connect ti
                Rajesh.Rathod
                in CATALOG oratest/orapass@rmandb1 ,

                rmandb1 is the service name. normally service name and db name are kept same.

                check tnsping rmandb1 where you are trying to execute "RMAN TARGET / CATALOG oratest/orapass@rmandb1" command.

                as optput will get ip address of server on which db is reside , port no , protocl

                like


                ---------

                oracle@rac3 ~]$ tnsping orcl

                TNS Ping Utility for Linux: Version 10.2.0.5.0 - Production on 23-DEC-2011 21:12:20

                Copyright (c) 1997, 2010, Oracle. All rights reserved.

                Used parameter files:
                /opt/app/oracle/product/db/10.2.1/network/admin/sqlnet.ora


                Used TNSNAMES adapter to resolve the alias
                Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac3.rajesh.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
                OK (10 msec)
                -------------------------------

                here HOST = rac3.rajesh.com is server on db present

                Edited by: Rajesh.Rathod on Dec 23, 2011 7:43 AM
                • 6. Re: RMAN-04004: error from recovery catalog database: ORA-12170: TNS:Connec
                  tychos
                  Hi,
                  I can sqlplus to the target database and the catalog database. The listener is up. What is the problem?
                  You ran out of resources on the catalog database.
                  What else I should check?
                  Post the output of this sql against the catalog:
                  select * from v$resource_limit;
                  This will show if you reached the maximum amount of processes.
                  Regards,
                  Tycho
                  • 7. Re: RMAN-04004: error from recovery catalog database: ORA-12170: TNS:Connec
                    756829
                    Thank you all. I have just thought, The listener (catalog's) should not be down because the catalog database is shared with other databases and other database's backups are okay. Right?. If the listener is down, all other backups won't start also, right?. I have checked the log file, the error is only for 12/22, starts from 23 to today, all fine. I will check the sql you provided once i get an access.
                    Can you please tell me how to setup an alert when a listener is down.