This discussion is archived
12 Replies Latest reply: Mar 20, 2013 5:19 AM by EdStevens RSS

Troubleshooting Oracle RMAN Catalog and Target Database

984556 Newbie
Currently Being Moderated
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
    Acooper Explorer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    So, from RMAN Catalog Database cannot connect to Target Database
  • 9. Re: Troubleshooting Oracle RMAN Catalog and Target Database
    mseberg Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    So, the steps for configuring RMAN are as above.
  • 12. Re: Troubleshooting Oracle RMAN Catalog and Target Database
    EdStevens Guru
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points