DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Could someone review my RMAN Duplicate Command Syntax?

  1. #1
    Join Date
    Aug 2007
    Location
    Alabama
    Posts
    6

    Question Could someone review my RMAN Duplicate Command Syntax?

    I've been tasked with re-creating a test instance using the RMAN duplicate command, and I plan to try this out on Monday.

    I'm using Oracle 9iR2 and Solaris v9.2.0.7.0. I'm a neophyte DBA with little actual Oracle experience at this point. However, I've taken the Backup and Recovery course. I've also read the Recovery Manager Quick Reference - a96564.pdf, Backup and Recovery Concepts - a96519.pdf, and I'm in the process of reading the Recovery Manager Users Guide - a96566.pdf.

    Would you guys mind reviewing the script below and let me know: (1) if the syntax looks OK; (2) if there's anything that I'm forgetting.

    Thanks. I really appreciate your help.

    #
    # Author:
    # Script: rest_226_from_t.sh
    # Date: August 17, 2007
    #
    # Purpose: Use this script to restore the TEST226 database from what has been backed up
    # for MACTIVET using the last RMAN backup. Note: (1) Auxillary instance, TEST226,
    # must be started with the NOMOUNT option; (2) Either Move disk copies and backups
    # from the target host to the duplicate host and re-catalog them OR
    # make sure that all backups and copies on the target host are remotely accessible
    # from the duplicate host.
    #
    # Commands to specify group1 and group2 for the logfiles have been removed as the
    # syntax below should do the following - If neither logfile nor LOG_FILE_NAME_CONVERT is specified,
    # RMAN uses the original target redo log filenames for the duplicate files. You must
    # use the nofilenamecheck option in this case. This according to information found at
    # http://download-west.oracle.com/docs...sy24.htm#49769
    #
    # syntax for set newname command found in PDF version of Recovery Manager Reference on
    # page 139 or 292 in PDF (RMAN Commands 2-113)
    #
    # TEMP file location / name changed to use correct one in MACTIVET.
    su - oracle
    cd /u01/app/oracle/admin/MPRTEST/scripts/RMAN
    rman target / catalog radmin/change_on_install@rcat
    'connect catalog radmin/radmin@rcat;
    connect auxiliary sys/change_on_install@TEST226;
    run {
    allocate auxiliary channel test1 type 'disk';
    allocate auxiliary channel test2 type 'disk';
    allocate auxiliary channel test3 type 'disk';
    allocate auxiliary channel test4 type 'disk';
    set newname for system01 1 TO '/u02/oradata/TEST226/system01.dbf';
    set newname for undotbs01 2 TO '/u03/oradata/TEST226/undotbs01.dbf';
    set newname for ADBASE_01 3 TO '/u04/oradata/TEST226/ADBASE_01.dbf';
    set newname for ADBASE_BI_01 4 TO '/u07/oradata/TEST226/ADBASE_BI_01.dbf';
    set newname for ADBASE_BI_IDX_01 5 TO '/u05/oradata/TEST226/ADBASE_BI_IDX_01.dbf';
    set newname for CWMLITE_01 6 TO '/u04/oradata/TEST226/CWMLITE_01.dbf';
    set newname for indx01 7 TO '/u05/oradata/TEST226/indx01.dbf';
    set newname for ODM_01 8 TO '/u07/oradata/TEST226/ODM_01.dbf';
    set newname for PGL_01 9 TO '/u04/oradata/TEST226/PGL_01.dbf';
    set newname for tools01 10 TO '/u07/oradata/TEST226/tools01.dbf';
    set newname for users01 11 TO '/u04/oradata/TEST226/users_01.dbf';
    set newname for ADBASE_BLOB_01 12 TO '/u07/oradata/TEST226/ADBASE_BLOB_01.dbf';
    set newname for TEMP01.dbf 13 TO '/u06/oradata/TEST226/temp01.dbf';

    duplicate target database to TEST226 nofilenamecheck;
    }
    Last edited by mpoisson; 08-28-2007 at 01:23 PM.
    "There is no pleasure in having nothing to do; the fun is having lots to do and not doing it." Andrew Jackson

  2. #2
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    Hmm from your script it sounds like duplicating rman database is painful.

    I would rather suggest that you have the same filesystem (i assume you are cloning it for development) as the production that would make things easier
    rather then hard coding those path names

    Lookup in the manuals esp for these two parameters
    db_file_name_convert
    log_file_name_convert

    so you dont have to hard code so many paths in your script

    regards
    Hrishy
    Last edited by hrishy; 08-27-2007 at 01:23 AM.

  3. #3
    Join Date
    Aug 2007
    Location
    Alabama
    Posts
    6

    Unhappy

    Thanks hrishy for the review. Unfortunately, the two test instances have already been installed with the paths detailed above so I'm going to have to work with what I have.

    When I finally get the OK to do this, I'll post back and let you know how it goes.

    P.S. Thanks for the hint about using db_filename_convert and logfile_name_convert. I'll looking them up now in the 'Recovery Manager Users Guide' - a96566.pdf
    Last edited by mpoisson; 08-27-2007 at 10:29 AM. Reason: Clarification
    "There is no pleasure in having nothing to do; the fun is having lots to do and not doing it." Andrew Jackson

  4. #4
    Join Date
    Aug 2007
    Location
    Alabama
    Posts
    6

    Getting an ORA-12523 Listener Error

    Since my last post I've broken up the script into two parts:

    rest_226_from_t.sh

    cd /u01/app/oracle/admin/MPRTEST/scripts/RMAN
    pwd
    export ORACLE_SID=MPRTEST
    env
    rman target / catalog radmin/change_on_install@rcat CMDFILE rest_226_from_t.res LOG duptest.log
    echo "Done!"

    rest_226_from_t.res

    connect auxiliary sys/change_on_install@TEST226;
    run {
    set newname for datafile '/u02/oradata/MPRTEST/system01.dbf' TO '/u02/oradata/TEST226/system01.dbf';
    set newname for datafile '/u03/oradata/MPRTEST/undotbs01.dbf' TO '/u03/oradata/TEST226/undotbs01.dbf';
    set newname for datafile '/u04/oradata/MPRTEST/ADBASE_01.dbf' TO '/u04/oradata/TEST226/ADBASE_01.dbf';
    set newname for datafile '/u07/oradata/MPRTEST/ADBASE_BI_01.dbf' TO '/u07/oradata/TEST226/ADBASE_BI_01.dbf';
    set newname for datafile '/u05/oradata/MPRTEST/ADBASE_BI_IDX_01.dbf' TO '/u05/oradata/TEST226/ADBASE_BI_IDX_01.dbf';
    set newname for datafile '/u04/oradata/MPRTEST/CWMLITE_01.dbf' TO '/u04/oradata/TEST226/CWMLITE_01.dbf';
    set newname for datafile '/u05/oradata/MPRTEST/indx01.dbf' TO '/u05/oradata/TEST226/indx01.dbf';
    set newname for datafile '/u07/oradata/MPRTEST/ODM_01.dbf' TO '/u07/oradata/TEST226/ODM_01.dbf';
    set newname for datafile '/u04/oradata/MPRTEST/PGL_01.dbf' TO '/u04/oradata/TEST226/PGL_01.dbf';
    set newname for datafile '/u07/oradata/MPRTEST/tools01.dbf' TO '/u07/oradata/TEST226/tools01.dbf';
    set newname for datafile '/u04/oradata/MPRTEST/users_01.dbf' TO '/u04/oradata/TEST226/users_01.dbf';
    set newname for datafile '/u07/oradata/MPRTEST/ADBASE_BLOB_01.dbf' TO '/u07/oradata/TEST226/ADBASE_BLOB_01.dbf';


    duplicate target database to TEST226 nofilenamecheck;
    }


    TEST226 has been stated in NOMOUNT mode.

    When I run it, I get the following error:

    MACTIVET:/home/oracle>cat duptest.log

    .
    .

    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-04006: error from auxiliary database: ORA-12523: TNS:listener could not fin
    d instance appropriate for the client connection

    Recovery Manager complete.

    If I STARTUP the auxiliary database and it's mounted, then I get the error:

    Starting Duplicate Db at 27-AUG-07
    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of Duplicate Db command at 08/27/2007 15:18:30
    RMAN-05500: the auxiliary database must be not mounted when issuing a DUPLICATE
    command

    Recovery Manager complete.
    MACTIVET:/home/oracle>


    I don't get it because if the TEST226 instance is started up normally, then I can connect to it, query it, etc.

    I'm stumped as to why I'm getting the listener error. Here are the contents of my listener.ora and tnsnames.ora on the server where both instances reside:

    listener.ora
    # LISTENER.ORA Network Configuration File: /u01/app/oracle/product/9.2.0/network/admin/listener.ora
    # Generated by Oracle configuration tools.

    LISTENER =
    (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
    )
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = MACTIVET)(PORT = 1521))
    )
    )
    )

    SID_LIST_LISTENER =
    (SID_LIST =
    (SID_DESC =
    (SID_NAME = PLSExtProc)
    (ORACLE_HOME = /u01/app/oracle/product/9.2.0)
    (PROGRAM = extproc)
    )
    (SID_DESC =
    (GLOBAL_DBNAME = MPRTEST)
    (ORACLE_HOME = /u01/app/oracle/product/9.2.0)
    (SID_NAME = MPRTEST)
    )

    (SID_DESC =
    (GLOBAL_DBNAME = TEST226)
    (ORACLE_HOME = /u01/app/oracle/product/9.2.0)
    (SID_NAME = TEST226)
    )
    (SID_DESC =
    (GLOBAL_DBNAME = RCAT)
    (ORACLE_HOME = /u01/app/oracle/product/9.2.0)
    (SID_NAME = RCAT)
    )
    )
    tnsnames.ora
    # TNSNAMES.ORA Network Configuration File: /u01/app/oracle/product/9.2.0/network/admin/tnsnames.ora
    # Generated by Oracle configuration tools.

    INST1_HTTP =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = MACTIVEP)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = MODOSE)
    (PRESENTATION = http://HRService)
    )
    )

    MPR =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.9.204.89)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVICE_NAME = MPR)
    )
    )

    MPRTEST =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = MACTIVET)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = MPRTEST)
    )
    )
    RCAT =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = MACTIVET)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = RCAT)
    )
    )

    EXTPROC_CONNECTION_DATA =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
    )
    (CONNECT_DATA =
    (SID = PLSExtProc)
    (PRESENTATION = RO)
    )
    )

    TEST226 =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = MACTIVET)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = TEST226)
    )
    )

    MPRBI =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = MACTIVET)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = MPRBI)
    )
    )
    Last edited by mpoisson; 08-28-2007 at 01:24 PM. Reason: Clarification after correction of RMAN syntax errors
    "There is no pleasure in having nothing to do; the fun is having lots to do and not doing it." Andrew Jackson

  5. #5
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    I had a look at this and in my opinion your initialization file should contain the db_file_name_convert parameter and you should reduce your coding

    db_file_name_convert =
    (
    '/u02/oradata/MPRTEST' , '/u02/oradata/TEST226' ,
    '/u03/oradata/MRPTEST' , '/u03/oradata/TEST226' ,
    '/u04/oradata/MRPTEST' , '/u04/oradata/TEST226' ,
    '/u05/oradata/MRPTEST' , '/u05/oradata/TEST226' ,
    '/u07/oradata/MRPTEST' , '/u07/oradata/TEST226'
    )


    regarding the error
    ORA-12523: TNS:listener could not find instance appropriate for the client connection

    whats the output of

    lsnrctl services

    Are you able to connect to target and auxillary instances using sqlplus ?

    sqlplus "sys/change_on_install@TEST226 as sysdba"

    If you not then try changing the SID to service name in listener.ora and the reloading the listener.

    The key thing is you should get the listener file working correctly and should be able to connect to both auxillary and source(Target database as oracle calls it) before you proceed with cloning

    i.e both the following commands should work
    sqlplus "sys/change_on_install@TEST226 as sysdba"
    sqlplus "sys/change_on_install@MPRTEST as sysdba"

    regards
    Hrishy

  6. #6
    Join Date
    Aug 2007
    Location
    Alabama
    Posts
    6

    Question lsnrctl status

    Hi Hrishy,

    The status displays as blocked (see below). When the instance is up and running it looks like the others. It's in a startup / nomount mode.

    lsnMACTIVET:/home/oracle>lsnrctl status

    LSNRCTL for Solaris: Version 9.2.0.7.0 - Production on 28-AUG-2007 10:47:43

    Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
    STATUS of the LISTENER
    ------------------------
    Alias LISTENER
    Version TNSLSNR for Solaris: Version 9.2.0.7.0 - Production
    Start Date 18-JUN-2006 09:21:31
    Uptime 436 days 1 hr. 26 min. 11 sec
    Trace Level off
    Security OFF
    SNMP OFF
    Listener Parameter File /u01/app/oracle/product/9.2.0/network/admin/listener.o
    ra
    Listener Log File /u01/app/oracle/product/9.2.0/network/log/listener.log
    Listening Endpoints Summary...
    (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=MACTIVET)(PORT=1521)))
    Services Summary...
    Service "MPRBI" has 1 instance(s).
    Instance "MPRBI", status READY, has 1 handler(s) for this service...
    Service "MPRTEST" has 2 instance(s).
    Instance "MPRTEST", status UNKNOWN, has 1 handler(s) for this service...
    Instance "MPRTEST", status READY, has 2 handler(s) for this service...
    Service "PLSExtProc" has 1 instance(s).
    Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Service "RCAT" has 2 instance(s).
    Instance "RCAT", status UNKNOWN, has 1 handler(s) for this service...
    Instance "RCAT", status READY, has 2 handler(s) for this service...
    Service "TEST226" has 1 instance(s).
    Instance "TEST226", status BLOCKED, has 1 handler(s) for this service...
    The command completed successfully
    MACTIVET:/home/oracle>

    I can hit the target no problem with SQL Plus, but not the auxiliary:

    SQL> connect sys/change_on_install@TEST226 as sysdba;
    ERROR:
    ORA-12523: TNS:listener could not find instance appropriate for the client
    connection


    SQL>

    The entry in LISTENER.ORA is as follows:

    (SID_DESC =
    (GLOBAL_DBNAME = TEST226)
    (ORACLE_HOME = /u01/app/oracle/product/9.2.0)
    (SID_NAME = TEST226)
    )


    The SID_NAME matches the SERVICE_NAME in TNSNAMES.ORA


    TEST226 =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = MACTIVET)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = TEST226)

    I changed the entry in LISTENER.ORA to

    SERVICE_NAME = TEST226

    from

    SID_NAME = TEST226

    I'm still seeing a status of blocked. Thanks again for all of your help with this.
    Last edited by mpoisson; 08-28-2007 at 01:25 PM. Reason: clarification
    "There is no pleasure in having nothing to do; the fun is having lots to do and not doing it." Andrew Jackson

  7. #7
    Join Date
    Aug 2007
    Location
    Alabama
    Posts
    6

    Thumbs up Listener part resolved

    I logged a Metalink issue, followed the instructions reference in the article below, and this did the trick.

    Article-ID: Note 313026.1
    Title: Rman Offline Backup Fails with ORA-12519 or ORA-12523 Error


    Basically, I changed it back to SERVICE_NAME back to SID_NAME = TEST226.
    I then did a lsnctrl stop and a lsnctrl start

    I can now connect to the nomount instance and I'll proceed on with the other part. Thanks.
    Last edited by mpoisson; 08-29-2007 at 10:25 AM. Reason: Clarification
    "There is no pleasure in having nothing to do; the fun is having lots to do and not doing it." Andrew Jackson

  8. #8
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    If you look above in you listener.ora you already had static listener connection i am not sure what you changed now to make it work ?

    regards
    Hrishy

  9. #9
    Join Date
    Aug 2007
    Location
    Alabama
    Posts
    6
    When it was all said and done, I really didn't change anything as I just changed SERVICE_NAME = TEST226 back to SID_NAME = TEST226. I think something had probably hosed up with the listener, and that's why the stop and start of the listener corrected the issue.
    "There is no pleasure in having nothing to do; the fun is having lots to do and not doing it." Andrew Jackson

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width