12 Replies Latest reply: Jul 16, 2014 11:12 PM by KoshNardana RSS

    Automation Clone Script

    KoshNardana

      Hello All,

       

      I am trying to automate the cloning process for our RAC one node databases, but I am having a bit of trouble.

      Here is the relevant part of my script (I think):

      ================================================================

      rman log='singleracclone.log' target sys/$PASSWORD@"(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=$TARGET_HOST)(PORT=$TARGET_PORT))

      (CONNECT_DATA=(SERVICE_NAME=$TARGET_SID)))" AUXILIARY sys/$PASSWORD@"(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)

      (HOST=$AUX_HOST)(PORT=AUX_PORT))(CONNECT_DATA=(SERVICE_NAME=$AUX_SID)(UR=A)))" <<EOF

      run {

          allocate channel ch1 type disk;

          allocate channel ch2 type disk;

          allocate channel ch3 type disk;

          allocate channel ch4 type disk;

          allocate channel ch5 type disk;

          allocate auxiliary channel ch6 type disk;

          allocate auxiliary channel ch7 type disk;

          allocate auxiliary channel ch8 type disk;

          allocate auxiliary channel ch9 type disk;

          allocate auxiliary channel ch10 type disk;

          DUPLICATE TARGET DATABASE TO $AUX_DB_NAME

          FROM ACTIVE DATABASE

          DB_FILE_NAME_CONVERT=(

          '$TARGET_ASM_PATH','$AUX_ASM_PATH'

          );

      }

      =============================================================

      Which yields the following error:

       

      contents of Memory Script:

      {

         shutdown clone immediate;

         startup clone nomount;

      }

      executing Memory Script

       

      Oracle instance shut down

       

      released channel: ch1

      released channel: ch2

      released channel: ch3

      released channel: ch4

      released channel: ch5

      RMAN-00571: ===========================================================

      RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

      RMAN-00571: ===========================================================

      RMAN-03002: failure of Duplicate Db command at 07/16/2014 00:11:04

      RMAN-06174: not connected to auxiliary database

      RMAN-03015: error occurred in stored script Memory Script

      RMAN-04006: error from auxiliary database: ORA-12514: TNS:listener does not currently know of serv

      ice requested in connect descriptor

       

      =============================================================

      As you can see I am trying to just use the connection strings in my RMAN command in order to avoid fussing around with the tnsnames.ora.

      As part of the script I spin up a custom listener running on a pre-arranged port with a SID_LIST_LISTENER entry that points to the auxiliary database.

      I can see the service when I do an lsnrctl status LISTENER_CUSTOM.

      I can connect to both the target and auxiliary on both servers using the connection strings.  In fact my RMAN command above connects just fine w/the connection strings initially.  It is after the memory script has been executed and the Oracle instance shut down.

       

      Any thoughts as to what I am missing?

       

      Thanks in advance.

       

      Kosh

        • 1. Re: Automation Clone Script
          soufir

          You might be missing exporting the Oracle_home and Oracle_base PATH in the script.

           

          As below:

           

          #!/bin/bash

          # Set up Oracle

          export ORACLE_BASE=/opt/oracle

          export ORACLE_HOME="/opt/oracle/product/11.2.0/dbhome_1"

          export ORACLE_BIN="/opt/oracle/product/11.2.0/dbhome_1/bin"

          export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH

          export ORACLE_CRS_HOME=/opt/11.2.0/grid

          #export TNS_ADMIN=$ORACLE_HOME/network/admin

          export ORACLE_SID=cdb2

          export ORACLE_UNQNAME=cdb

           

           

          PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin:$ORACLE_CRS_HOME/bin

           

          Just try

          • 2. Re: Automation Clone Script
            yoonas

            Kosh,

             

             

            RMAN-04006: error from auxiliary database: ORA-12514: TNS:listener does not currently know of serv

             

            Official description

            12514, 00000, "TNS:listener does not currently know of service requested in connect descriptor"

            // *Cause:  The listener received a request to establish a connection to a

            // database or other service. The connect descriptor received by the listener

            // specified a service name for a service (usually a database service)

            // that either has not yet dynamically registered with the listener or has

            // not been statically configured for the listener.  This may be a temporary

            // condition such as after the listener has started, but before the database

            // instance has registered with the listener.

            // *Action:

            //  - Wait a moment and try to connect a second time.

            //  - Check which services are currently known by the listener by executing:

            //    lsnrctl services <listener name>

            //  - Check that the SERVICE_NAME parameter in the connect descriptor of the

            //    net service name used specifies a service known by the listener.

            //  - If an easy connect naming connect identifier was used, check that

            //    the service name specified is a service known by the listener.

            //  - Check for an event in the listener.log file.

             

             

            Are you running commands from source or target(auxiliary)

             

            On auxilary can you post status of lsnrctl status

             

            Check this as well

            RMAN-04006 And ORA-12514 During RMAN Duplicate (Doc ID 883490.1)


            Regards

            Yoonas



            • 3. Re: Automation Clone Script
              KoshNardana

              Soufir,

               

              Thanks for the reply.

              I tried your suggestion and it did not change anything. 

               

              Any other thoughts?

               

              Thanks,

               

              Kosh

              • 4. Re: Automation Clone Script
                KoshNardana

                Hi Yoonas,

                 

                Thanks for the reply.

                 

                I am running this from the auxiliary.

                 

                Here is the output of lsnrctl status:

                 

                > lsnrctl status LISTENER_CUSTOM

                 

                LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 16-JUL-2014 09:07:50

                 

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

                 

                Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=MYHOST)(PORT=2521)))

                STATUS of the LISTENER

                ------------------------

                Alias                     LISTENER_CUSTOM

                Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production

                Start Date                15-JUL-2014 17:19:36

                Uptime                    0 days 15 hr. 48 min. 13 sec

                Trace Level               off

                Security                  ON: Local OS Authentication

                SNMP                      OFF

                Listener Parameter File   /path/to/custom/listener/listener.ora

                Listener Log File         /u01/db/diag/tnslsnr/myhost/listener_custom/alert/log.xml

                Listening Endpoints Summary...

                  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=myhost)(PORT=2521)))

                Services Summary...

                Service "orcl2" has 1 instance(s).

                  Instance "orcl2", status BLOCKED, has 1 handler(s) for this service...

                The command completed successfully

                 

                I checked the listener log file and I am not seeing any errors or anything that *looks* like an error.

                 

                I am reviewing the document now.

                 

                Please let me know if you have any other thoughts.

                 

                Thanks again for your help thus far!

                 

                Kosh

                • 5. Re: Automation Clone Script
                  KoshNardana

                  Hi Yoonas,

                   

                  I was reading the document and followed the link from it to the document 419440.1 Connection to Auxiliary using connect string failed with ORA-12528.

                   

                  In that document it makes the following statement:

                   

                  If "Duplicate from Active Database" available and used

                  Then you must connect using 'TNS alias' to both, Target and AUXiliary Instance

                  AUXiliary Instance should have a static Listener configured like:

                    

                     $ rman target sys/password@Target auxiliary sys/password@AUXiliary

                   

                  # To test, sqlplus (sysdba user), and rman must be able to connect to AUXiliary Instance, even if AUX instance is down/nomount 

                  # For this recommendation is to create a 'Static Listener' entry for the AUX instance

                   

                  Does that mean that I MUST use a tns entry in the tnsnames.ora and that I can use the connection string like the one in my original post?  It sure seems to be saying that....

                   

                  Thanks,

                   

                  Kosh

                  • 6. Re: Automation Clone Script
                    Nip-Oracle

                    Yes, that's right. You need to have static listener service registration and as well as connect to target and auxiliary using tns entry

                    • 7. Re: Automation Clone Script
                      yoonas

                      Then you must connect using 'TNS alias' to both, Target and AUXiliary Instance

                      AUXiliary Instance should have a static Listener configured like:

                      Go ahead and do configure as per the note

                       

                      before you duplicate make sure you can connect

                      sqlplus sys@<service_name> as sysdba

                      • 8. Re: Automation Clone Script
                        KoshNardana

                        Hi Yoonas (& everyone else),

                         

                        I did that and I am still getting the same error.

                         

                        On the target host I can sqlplus to both target & auxiliary as sys.

                        On the auxiliary host I can sqlplus to both target & auxiliary as sys.

                         

                        Any ideas what I am missing?  This is positively maddening!!!

                         

                        Thanks,

                         

                        Kosh

                        • 9. Re: Automation Clone Script
                          yoonas

                          show us your connection output with sys user and also post auxiliary listener.ora

                          and output of lsnrctl on auxiliary

                          have you reloaded listener on auxiliary

                           

                          previous output

                            Instance "orcl2", status BLOCKED, has 1 handler(s) for this service...

                          on auxiliary you have already started database in nomount mode it seems

                          shutdown database and connect using sys@<service_name>

                          Hope you have already created password file

                          • 10. Re: Automation Clone Script
                            KoshNardana

                            Hi Yoonas,

                             

                            Sorry this took so long...crazy day....

                             

                            show us your connection output with sys user

                            From Aux Host:

                            <aux_host|AUX_DB>:[/u101/scripts/clone/AUX_DB]

                            > sqlplus sys@aux_db_clone as sysdba                

                             

                            SQL*Plus: Release 11.2.0.3.0 Production on Wed Jul 16 15:56:25 2014

                             

                            Copyright (c) 1982, 2011, Oracle.  All rights reserved.

                             

                            Enter password:

                             

                            Connected to:

                            Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

                            With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

                            Data Mining and Real Application Testing options

                             

                            SQL> exit

                            Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

                            With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

                            Data Mining and Real Application Testing options

                             

                            <aux_host|AUX_DB>:[/u101/scripts/clone/AUX_DB]

                            > sqlplus sys@target_db as sysdba

                             

                            SQL*Plus: Release 11.2.0.3.0 Production on Wed Jul 16 15:56:53 2014

                             

                            Copyright (c) 1982, 2011, Oracle.  All rights reserved.

                             

                            Enter password:

                             

                            Connected to:

                            Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

                            With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

                            Data Mining and Real Application Testing options

                             

                            SQL> exit

                            Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

                            With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

                            Data Mining and Real Application Testing options

                             

                            From Target Host:

                            > sqlplus sys@aux_db_clone as sysdba

                             

                            SQL*Plus: Release 11.2.0.3.0 Production on Wed Jul 16 15:57:42 2014

                             

                            Copyright (c) 1982, 2011, Oracle.  All rights reserved.

                             

                            Enter password:

                             

                            Connected to:

                            Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

                            With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

                            Data Mining and Real Application Testing options

                             

                            SQL> exit

                            Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

                            With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

                            Data Mining and Real Application Testing options

                             

                            <target_host|TARGET_DB>:[/u101/scripts/clone/AUX_DB]

                            > sqlplus sys@target_db as sysdba

                             

                            SQL*Plus: Release 11.2.0.3.0 Production on Wed Jul 16 15:58:00 2014

                             

                            Copyright (c) 1982, 2011, Oracle.  All rights reserved.

                             

                            Enter password:

                             

                            Connected to:

                            Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

                            With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

                            Data Mining and Real Application Testing options

                             

                            SQL> exit

                            Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

                            With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

                            Data Mining and Real Application Testing options

                             

                            ...and also post auxiliary listener.ora

                            <aux_host|AUX_DB>:[/u101/scripts/clone/AUX_DB/tnsadmin]

                            > cat listener.ora

                            LISTENER_CUSTOM=

                              (DESCRIPTION=

                                (ADDRESS_LIST=

                                  (ADDRESS=(PROTOCOL=tcp)(HOST=aux_host)(PORT=2521))

                                  ))

                             

                            SID_LIST_LISTENER =

                              (SID_LIST =

                                (SID_DESC =

                                  (GLOBAL_DBNAME = AUX_DB)

                                  (ORACLE_HOME = /u01/db/11.2.0.3)

                                  (SID_NAME = AUX_DB)

                                )

                              )

                             

                            ...and output of lsnrctl on auxiliary

                             

                            <aux_host|AUX_DB>:[/u101/scripts/clone/AUX_DB/tnsadmin]

                            > lsnrctl status LISTENER_CUSTOM

                             

                            LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 16-JUL-2014 15:59:06

                             

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

                             

                            Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=aux_host)(PORT=2521)))

                            STATUS of the LISTENER

                            ------------------------

                            Alias                     LISTENER_CUSTOM

                            Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production

                            Start Date                15-JUL-2014 17:19:36

                            Uptime                    0 days 22 hr. 39 min. 29 sec

                            Trace Level               off

                            Security                  ON: Local OS Authentication

                            SNMP                      OFF

                            Listener Parameter File   /u101/scripts/clone/AUX_DB/tnsadmin/listener.ora

                            Listener Log File         /u01/db/diag/tnslsnr/aux_host/listener_clone/alert/log.xml

                            Listening Endpoints Summary...

                              (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=aux_host)(PORT=2521)))

                            Services Summary...

                            Service "AUX_DB" has 1 instance(s).

                              Instance "AUX_DB", status BLOCKED, has 1 handler(s) for this service...

                            The command completed successfully

                             

                            shutdown database and connect using sys@<service_name>

                            > sqlplus sys@aux_db_clone as sysdba

                             

                            SQL*Plus: Release 11.2.0.3.0 Production on Wed Jul 16 16:00:02 2014

                             

                            Copyright (c) 1982, 2011, Oracle.  All rights reserved.

                             

                            Enter password:

                            Connected to an idle instance.

                             

                            SQL> exit

                            Disconnected

                             

                            Yes, the password file has already been created.

                             

                            What else do you need?

                            • 11. Re: Automation Clone Script
                              Hemant K Chitale

                              >LISTENER_CUSTOM

                              doesn't match

                              >SID_LIST_LISTENER

                              The first entry says that the listener name is "LISTENER_CUSTOM".  The second entry is attempting to register the SID with a listener called "LISTENER" and not "LISTENER_CUSTOM"  Change the second entry to "SID_LIST_LISTENER_CUSTOM"

                               

                               

                              Hemant K Chitale


                              • 12. Re: Automation Clone Script
                                KoshNardana

                                Holy. Cow.  That fixed it!

                                Even this works:

                                rman log='singleracclone.log' target sys/$PASSWORD@"(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=$TARGET_HOST)(PORT=$TARGET_PORT))

                                (CONNECT_DATA=(SERVICE_NAME=$TARGET_SID)))" AUXILIARY sys/$PASSWORD@"(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)

                                (HOST=$AUX_HOST)(PORT=AUX_PORT))(CONNECT_DATA=(SERVICE_NAME=$AUX_SID)(UR=A)))" <<EOF

                                 

                                Thank you VERY much Hemant!