12 Replies Latest reply: Mar 20, 2013 7:19 AM by EdStevens RSS

    Troubleshooting Oracle RMAN Catalog and Target Database

    984556
      Hello Folks,

      Thought of opening a new thread to troubleshoot Oracle RMAN Catalog and Target Database connection.

      I am having a problem in connecting and registering to RMAN Catalog from the target database ... Appreciate your feedback and participation in the thread..

      Operating System AIX 5.3
      Oracle Database 9i

      RMAN Catalog Database
      hostname : rmandb
      SID : rmandb
      Database port: 1521


      Target Database
      hostname : test8
      SID : PROD
      Database port: 1524

      each machine/server/host can ping the other

      rmandb> ping test8
      OK

      test8> ping rmandb
      OK


      From Target Database Machine/Host

      tnsping result, tnsnames.ora, listener.ora, lsnrctl status, lsnrctl services.

      oraprod@test8:/oracle/proddb/9.2.0/network/admin/PROD_test8> cat tnsnames.ora
      
      rmandb = (DESCRIPTION=
                      (ADDRESS=(PROTOCOL=tcp)(HOST=rmandb)(PORT=1521))
                      (CONNECT_DATA=(SID=rmandb))
                  )
      oraprod@test8:/oracle/proddb/9.2.0/network/admin/PROD_test8> cat listener.ora

      Did not add rmandb in listener.ora , only added in tnsnames.ora




      [oraprod@test8:]$ lsnrctl status rmandb
      LSNRCTL for IBM/AIX RISC System/6000: Version 9.2.0.3.0 - Production on 25-FEB-2013 23:56:39
      
      Copyright (c) 1991, 2002, Oracle Corporation.  All rights reserved.
      
      Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rmandb)(PORT=1521))(CONNECT_DATA=(SID=rmandb)))
      STATUS of the LISTENER
      ------------------------
      Alias                     rmandb
      Version                   TNSLSNR for IBM/AIX RISC System/6000: Version 9.2.0.3.0 - Production
      Start Date                25-FEB-2013 19:24:23
      Uptime                    0 days 1 hr. 31 min. 40 sec
      Trace Level               off
      Security                  OFF
      SNMP                      OFF
      Listener Parameter File   /oracle/proddb/9.2.0/network/admin/rmandb_rmandb/listener.ora
      Listener Log File         /oracle/proddb/9.2.0/network/admin/rmandb.log
      Listening Endpoints Summary...
        (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROCrmandb)))
        (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rmandb.com)(PORT=1521)))
      Services Summary...
      Service "PLSExtProc" has 1 instance(s).
        Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
      Service "rmandb" has 2 instance(s).
        Instance "rmandb", status UNKNOWN, has 1 handler(s) for this service...
        Instance "rmandb", status READY, has 1 handler(s) for this service...
      The command completed successfully
      
      ***************************************************
      
      oraprod@test8:/oracle/proddb/9.2.0/network/admin/PROD_test8>lsnrctl status
      
      LSNRCTL for IBM/AIX RISC System/6000: Version 9.2.0.3.0 - Production on 26-FEB-2013 00:58:36
      
      Copyright (c) 1991, 2002, Oracle Corporation.  All rights reserved.
      
      Connecting to (ADDRESS=(PROTOCOL=tcp)(PORT=1521))
      STATUS of the LISTENER
      ------------------------
      Alias                     LISTENER
      Version                   TNSLSNR for IBM/AIX RISC System/6000: Version 9.2.0.3.0 - Production
      Start Date                25-FEB-2013 01:17:22
      Uptime                    0 days 23 hr. 41 min. 14 sec
      Trace Level               off
      Security                  OFF
      SNMP                      OFF
      Listener Parameter File   /oracle/proddb/9.2.0/network/admin/PROD_test8/listener.ora
      Listener Log File         /oracle/proddb/9.2.0/network/log/listener.log
      Listening Endpoints Summary...
        (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test8.com)(PORT=1521)))
      Services Summary...
      Service "PROD" has 1 instance(s).
        Instance "PROD", status READY, has 1 handler(s) for this service...
      The command completed successfully
      oraprod@test8:/oracle/proddb/9.2.0/network/admin/PROD_test8>
      oraprod@test8:/oracle/proddb/9.2.0/network/admin/PROD_test8>lsnrctl services
      
      LSNRCTL for IBM/AIX RISC System/6000: Version 9.2.0.3.0 - Production on 26-FEB-2013 00:15:48
      
      Copyright (c) 1991, 2002, Oracle Corporation.  All rights reserved.
      
      Connecting to (ADDRESS=(PROTOCOL=tcp)(PORT=1521))
      Services Summary...
      Service "PROD" has 1 instance(s).
        Instance "PROD", status READY, has 1 handler(s) for this service...
          Handler(s):
            "DEDICATED" established:0 refused:0 state:ready
               LOCAL SERVER
      The command completed successfully
      oraprod@test8:/oracle/proddb/9.2.0/network/admin/PROD_test8>lsnrctl services rmandb
      
      LSNRCTL for IBM/AIX RISC System/6000: Version 9.2.0.3.0 - Production on 26-FEB-2013 00:16:56
      
      Copyright (c) 1991, 2002, Oracle Corporation.  All rights reserved.
      
      Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rmandb)(PORT=1521))(CONNECT_DATA=(SID=rmandb)))
      Services Summary...
      Service "PLSExtProc" has 1 instance(s).
        Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
          Handler(s):
            "DEDICATED" established:0 refused:0
               LOCAL SERVER
      Service "rmandb" has 2 instance(s).
        Instance "rmandb", status UNKNOWN, has 1 handler(s) for this service...
          Handler(s):
            "DEDICATED" established:0 refused:0
               LOCAL SERVER
        Instance "rmandb", status READY, has 1 handler(s) for this service...
          Handler(s):
            "DEDICATED" established:1 refused:0 state:ready
               LOCAL SERVER
      The command completed successfully
      $ tnsping rmandb
      
      TNS Ping Utility for IBM/AIX RISC System/6000: Version 9.2.0.3.0 - Production on 26-FEB-2013 00:18:09
      
      Copyright (c) 1997 Oracle Corporation.  All rights reserved.
      
      Used parameter files:
      
      
      Used TNSNAMES adapter to resolve the alias
      Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=rmandb)(PORT=1521)) (CONNECT_DATA=(SID=rmandb)))
      OK (0 msec)
      oraprod@test8> rman target sys/sys@PROD_test8 rcvcat rman/rman@rmandb_rmandb
      
      
      Recovery Manager: Release 9.2.0.3.0 - 64bit Production
      
      Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.
      
      RMAN-00571: ===========================================================
      RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
      RMAN-00571: ===========================================================
      RMAN-00554: initialization of internal recovery manager package failed
      RMAN-04005: error from target database:
      ORA-12154: TNS:could not resolve service name
        • 1. Re: Troubleshooting Oracle RMAN Catalog and Target Database
          Seberg
          What exactly are you trying to do here ( with statement shown )?

          rman target sys/sys@PROD_test8 rcvcat rman/rman@rmandb_rmandb
          • 2. Re: Troubleshooting Oracle RMAN Catalog and Target Database
            984556
            USIS1 wrote:
            What exactly are you trying to do here ( with statement shown )?

            rman target sys/sys@PROD_test8 rcvcat rman/rman@rmandb_rmandb
            Connect to both, the database and catalog and want to register

            rman target sys/PASSWORD@dbname rcvcat cat/CATPASSWD@rcat
            register database;
            • 3. Re: Troubleshooting Oracle RMAN Catalog and Target Database
              mseberg
              Hello;

              Should this ?
              rman target sys/sys@PROD_test8 rcvcat rman/rman@rmandb_rmandb
              Just be this ?

              rman target / catalog rman/rman@rmandb_rmandb
              Best Regards

              mseberg
              • 4. Re: Troubleshooting Oracle RMAN Catalog and Target Database
                984556
                mseberg wrote:
                Hello;

                Should this ?
                rman target sys/sys@PROD_test8 rcvcat rman/rman@rmandb_rmandb
                Just be this ?

                rman target / catalog rman/rman@rmandb_rmandb
                Best Regards

                mseberg
                thanks for the reply:

                would give me this error
                oraprod@test8:/oraclec/proddb/9.2.0/network/admin/PROD_test8> rman target / catalog rman/rman@rmandb_rmandb
                
                
                Recovery Manager: Release 9.2.0.3.0 - 64bit Production
                
                Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.
                
                connected to target database: PROD (DBID=219850293)
                RMAN-00571: ===========================================================
                RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
                RMAN-00571: ===========================================================
                RMAN-00554: initialization of internal recovery manager package failed
                RMAN-04004: error from recovery catalog database: ORA-12154: TNS:could not resolve service name
                • 5. Re: Troubleshooting Oracle RMAN Catalog and Target Database
                  EdStevens
                  ORA-12154: TNS:could not resolve service name
                  As I pointed up in another thread this morning, a ORA-12154 from rman is no different that from any other client.

                  see: [url http://edstevensdba.wordpress.com/2011/02/26/ora-12154tns-03505/] ora-12154 TNS:could not resolve the connect identifier specified

                  Also ...
                  see: [url http://edstevensdba.wordpress.com/2011/02/09/sqlnet_overview/] Help! I can’t connect to my database
                  see: [url http://edstevensdba.wordpress.com/2011/02/16/sqlnet_client_cfg/] Help! I can’t connect to my database (part duex) 
                  • 6. Re: Troubleshooting Oracle RMAN Catalog and Target Database
                    984556
                    EdStevens wrote:
                    ORA-12154: TNS:could not resolve service name
                    As I pointed up in another thread this morning, a ORA-12154 from rman is no different that from any other client.

                    see: [url http://edstevensdba.wordpress.com/2011/02/26/ora-12154tns-03505/] ora-12154 TNS:could not resolve the connect identifier specified
                    Also ...
                    see: [url http://edstevensdba.wordpress.com/2011/02/09/sqlnet_overview/] Help! I can’t connect to my database
                    see: [url http://edstevensdba.wordpress.com/2011/02/16/sqlnet_client_cfg/] Help! I can’t connect to my database (part duex)
                    Thanks ED Stevens for those useful links. I have read all three links:
                    and also searched on this forum like this thread RMAN-04004@ORA-01017


                    I am able to connect to rman database using the method which you mentioned , however i was looking for troubleshoot to connect from target database to rman catalog and register,

                    I was able to solve by doing this way
                    oraprod@test8:/oracle/proddb/9.2.0/network/admin/PROD_test8> sqlplus rman/rman@rmandb
                    SQL*Plus: Release 9.2.0.3.0 - Production on Tue Feb 26 02:42:16 2013
                    
                    Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
                    
                    
                    Connected to:
                    Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production
                    With the Partitioning, OLAP and Oracle Data Mining options
                    JServer Release 9.2.0.3.0 - Production
                    
                    sql> exit
                    
                    oraprod@test8:/oracle/proddb/9.2.0/network/admin/PROD_test8> rman
                    
                    Recovery Manager: Release 9.2.0.3.0 - 64bit Production
                    
                    Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.
                    
                    RMAN> connect catalog rman/rman@rmandb
                    
                    connected to recovery catalog database
                    
                    RMAN> register database;
                    
                    RMAN-00571: ===========================================================
                    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
                    RMAN-00571: ===========================================================
                    RMAN-03002: failure of register command at 02/26/2013 02:46:26
                    RMAN-06171: not connected to target database
                    
                    RMAN> connect target
                    
                    connected to target database: PROD (DBID=219850293)
                    
                    RMAN> register database;
                    
                    database registered in recovery catalog
                    starting full resync of recovery catalog
                    full resync complete
                    Thanks ! Solved, the issue.
                    • 7. Re: Troubleshooting Oracle RMAN Catalog and Target Database
                      984556
                      Okay, facing a new issue.

                      I am able to connect from my target database to my rman catalog database without any problems, and the target database is also registered in the catalog database.

                      however, unable to connect from my RMAN Database to Target Database

                      Could this be related to listener / database ports as RMANDB is running on different ports than PROD ( target Database ) ?


                      RMAN Catalog Database
                      hostname : rmandb
                      SID : rmandb
                      Database port: 1521
                      Target Database
                      hostname : test8
                      SID : PROD
                      Database port: 1524

                      each machine/server/host can ping the other

                      host@rmandb> ping test8
                      OK

                      host@test8> ping rmandb
                      OK
                      From Target Database Machine/Host


                      Troubleshooting
                      Start from Target Database 
                      test8>
                      
                      LSNRCTL> status
                      Connecting to (ADDRESS=(PROTOCOL=tcp)(PORT=1521))
                      STATUS of the LISTENER
                      ------------------------
                      Alias                     LISTENER
                      Version                   TNSLSNR for IBM/AIX RISC System/6000: Version 9.2.0.3.0 - Production
                      Start Date                28-FEB-2013 16:27:34
                      Uptime                    3 days 6 hr. 14 min. 2 sec
                      Trace Level               off
                      Security                  OFF
                      SNMP                      OFF
                      Listener Parameter File   /oracle/proddb/9.2.0/network/admin/PROD_test8/listener.ora
                      Listener Log File         /oracle/proddb/9.2.0/network/log/listener.log
                      Listening Endpoints Summary...
                        (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test8.com)(PORT=1521)))
                      Services Summary...
                      Service "PROD" has 1 instance(s).
                        Instance "PROD", status READY, has 1 handler(s) for this service...
                      The command completed successfully
                      LSNRCTL>
                      
                      oraprod@test8:/oracle/proddb/9.2.0>tnsping rmandb
                      
                      TNS Ping Utility for IBM/AIX RISC System/6000: Version 9.2.0.3.0 - Production on 03-MAR-2013 22:44:37
                      
                      Copyright (c) 1997 Oracle Corporation.  All rights reserved.
                      
                      Used parameter files:
                      
                      
                      Used TNSNAMES adapter to resolve the alias
                      Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=rmandb)(PORT=1521)) (CONNECT_DATA=(SID=rmandb)))
                      OK (10 msec)
                      
                      oraprod@test8:/oracle/proddb/9.2.0>sqlplus sys/sys@rmandb
                      
                      SQL*Plus: Release 9.2.0.3.0 - Production on Sun Mar 3 22:45:31 2013
                      
                      Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
                      
                      
                      Connected to:
                      Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production
                      With the Partitioning, OLAP and Oracle Data Mining options
                      JServer Release 9.2.0.3.0 - Production
                      
                      SQL> select name from v$database  ;
                      
                      NAME
                      ---------
                      RMANDB
                      
                      exit;
                      
                      oraprod@test8:/oracle/proddb/9.2.0/network/admin/PROD_test8>cat tnsnames.ora
                      
                      
                      rmandb = (DESCRIPTION=
                                      (ADDRESS=(PROTOCOL=tcp)(HOST=rmandb)(PORT=1521))
                                      (CONNECT_DATA=(SID=rmandb))
                                  )
                      
                      
                      PROD = (DESCRIPTION=
                                      (ADDRESS=(PROTOCOL=tcp)(HOST=test8)*(PORT=1524))*
                                      (CONNECT_DATA=(SID=PROD))
                                  )
                      The above shows that there is no issue from Target Database PROD connecting to RMANDB

                      Now going to the problem which from RMANDB to PROD

                      First thing would be to have the correct entry in tnsnames.ora of RMANDB ; so I did is cut and paste from the tnsnames.ora of PROD@test8
                      oraprod@test8:/oracle/proddb/9.2.0/network/admin/PROD_test8>cat tnsnames.ora
                      
                      
                      rmandb = (DESCRIPTION=
                                      (ADDRESS=(PROTOCOL=tcp)(HOST=rmandb)(PORT=1521))
                                      (CONNECT_DATA=(SID=rmandb))
                                  )
                      
                      
                      PROD = (DESCRIPTION=
                                      (ADDRESS=(PROTOCOL=tcp)(HOST=test8)*(PORT=1524))*
                                      (CONNECT_DATA=(SID=PROD))
                                  )
                      
                      
                      
                      
                      oraprod@rmandb:/oracle/proddb/9.2.0/network/admin/rmandb_rmandb>tnsping PROD
                      
                      TNS Ping Utility for IBM/AIX RISC System/6000: Version 9.2.0.3.0 - Production on 03-MAR-2013 20:00:38
                      
                      Copyright (c) 1997 Oracle Corporation.  All rights reserved.
                      
                      Used parameter files:
                      
                      TNS-03505: Failed to resolve name
                      oraprod@rmandb:/oracle/proddb/9.2.0/network/admin/rmandb_rmandb>tnsping test8
                      
                      TNS Ping Utility for IBM/AIX RISC System/6000: Version 9.2.0.3.0 - Production on 03-MAR-2013 20:00:54
                      
                      Copyright (c) 1997 Oracle Corporation.  All rights reserved.
                      
                      Used parameter files:
                      
                      Used HOSTNAME adapter to resolve the alias
                      Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SID=*)(SERVICE_NAME=test8.))(ADDRESS=(PROTOCOL=TCP)(HOST=test8)(PORT=1521)))
                      OK (0 msec)
                      
                      
                      with instance name
                      sqlplus sys/sys@PROD
                      
                      SQL*Plus: Release 9.2.0.3.0 - Production on Sun Mar 3 20:21:37 2013
                      
                      Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
                      
                      ERROR:
                      ORA-12154: TNS:could not resolve service name
                      
                      
                      With Hostname/servername/machine name
                      sqlplus sys/sys@test8      
                      
                      SQL*Plus: Release 9.2.0.3.0 - Production on Sun Mar 3 20:20:31 2013
                      
                      Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
                      
                      ERROR:
                      ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connect
                      descriptor
                      
                      
                      Changing the port in the tnsnames.ora from 1524 to 1521
                      PROD = (DESCRIPTION=
                                      (ADDRESS=(PROTOCOL=tcp)(HOST=test8)*(PORT=1521))*
                                      (CONNECT_DATA=(SID=PROD))
                                  )
                      
                      
                      repeating the same excercise, and getting the same results.
                      What could I check to resolve this problem
                      • 8. Re: Troubleshooting Oracle RMAN Catalog and Target Database
                        984556
                        So, from RMAN Catalog Database cannot connect to Target Database
                        • 9. Re: Troubleshooting Oracle RMAN Catalog and Target Database
                          mseberg
                          Hello;

                          I will triple check your detailed post but only a couple things come to mind.

                          1. Space or hidden character in the tnsnames.ora file on the catalog server. When I do this to myself I try adding a new entry to the file because generally everything below the bad character will not work.

                          2. Try an entry using SERVICE_NAME instead of sid.
                          SERVICE_NAME = PROD
                          3. Confirm the SERVICE_NAME ( it looks correct based on your post ) mine generally have most of the hostname as part of them. PROD.HOSTNAME.

                          select * from global_name;

                          Best Regards

                          mseberg
                          • 10. Re: Troubleshooting Oracle RMAN Catalog and Target Database
                            984556
                            I believe the issue is something to do with the password file or network connectivity to oracle.

                            from rmandb =catalog database server , I can sqlplus sys/sys@test8 which is the target server
                            from target server test8 , I can sqlplus sys/sys@rmandb which is the catalog rmandb server

                            so, i created password file on the target machine
                            $orapwd file=$ORACLE_HOME/dbs/orapwPROD8 password=sys entries=10
                            
                            root@test8:/oracle/proddb/9.2.0/dbs>ls
                            PROD8_test8_ifile.ora   init.ora                initPROD_noaq.ora
                                        initdw.ora
                                    lkPROD8
                                    orapwPROD8
                                initPROD8.ora
                                              initPROD8_noaq.ora
                            
                            root@test8:/oracle/proddb/9.2.0/dbs>cat orapwPROD8
                            Z[\]ORACLE Remote Password fileNTERNA6A75B1BBE50E66ABSYS4DE42795E66117AE
                            root@test8:/oracle/proddb/9.2.0/dbs>
                            
                            stop the database;
                            
                            add the line in  init.ora
                            
                            REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
                            
                            
                            startup the database;
                            
                            SQL> show parameter password;
                            
                            NAME                                 TYPE        VALUE
                            ------------------------------------ ----------- ------------------------------
                            remote_login_passwordfile            string      NONE
                            
                            
                            SQL> select * from v$pwfile_users;
                            
                            no rows selected
                            
                            SQL>
                            problem: can someone point out why the user sys has not been added ?


                            solution: add in the initPROD8.ora == which is the init_service_name.ora
                            REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
                            • 11. Re: Troubleshooting Oracle RMAN Catalog and Target Database
                              984556
                              So, the steps for configuring RMAN are as above.
                              • 12. Re: Troubleshooting Oracle RMAN Catalog and Target Database
                                EdStevens
                                981553 wrote:
                                I believe the issue is something to do with the password file or network connectivity to oracle.

                                from rmandb =catalog database server , I can sqlplus sys/sys@test8 which is the target server
                                from target server test8 , I can sqlplus sys/sys@rmandb which is the catalog rmandb server

                                so, i created password file on the target machine
                                $orapwd file=$ORACLE_HOME/dbs/orapwPROD8 password=sys entries=10
                                
                                root@test8:/oracle/proddb/9.2.0/dbs>ls
                                PROD8_test8_ifile.ora   init.ora                initPROD_noaq.ora
                                initdw.ora
                                lkPROD8
                                orapwPROD8
                                initPROD8.ora
                                initPROD8_noaq.ora
                                
                                root@test8:/oracle/proddb/9.2.0/dbs>cat orapwPROD8
                                Z[\]ORACLE Remote Password fileNTERNA6A75B1BBE50E66ABSYS4DE42795E66117AE
                                root@test8:/oracle/proddb/9.2.0/dbs>
                                As the password file is binary, you'll get a better picture of it with 'strings' instead of 'cat'.
                                stop the database;

                                add the line in init.ora

                                REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE


                                startup the database;

                                SQL> show parameter password;

                                NAME TYPE VALUE
                                ------------------------------------ ----------- ------------------------------
                                remote_login_passwordfile string NONE


                                Did you not notice that your setting of the parameter didn't "take"? Why do you suppose that is? ;-)

                                SQL> select * from v$pwfile_users;
                                
                                no rows selected
                                
                                SQL>
                                problem: can someone point out why the user sys has not been added ?


                                solution: add in the initPROD8.ora == which is the init_service_name.ora
                                REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
                                Edited by: EdStevens on Mar 20, 2013 7:18 AM