13 Replies Latest reply: Oct 28, 2010 5:55 PM by CKPT RSS

    Duplicate database

    772262
      ***** BACKUP of Target Database*
      [oracle@rs1pre92dvdbsa01 MDM2]$ . oraenv
      ORACLE_SID = [oracle] ? MDM2
      The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 is /u01/ app/oracle
      [oracle@rs1pre92dvdbsa01 MDM2]$ rman target /

      Recovery Manager: Release 11.2.0.1.0 - Production on Wed Oct 27 16:31:19 2010

      Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

      connected to target database: MDM (DBID=1485385044)

      RMAN> run
      2> {
      3> BACKUP DATABASE format '/backups/rman/rman_bck/MDM2/MDM2_bk_%s_%p_%t.bkp';
      4> BACKUP ARCHIVELOG ALL Format '/backups/rman/rman_bck/MDM2_al_%s_%p_%t.bkp';
      5> }

      Starting backup at 27-OCT-10
      using target database control file instead of recovery catalog
      allocated channel: ORA_DISK_1
      channel ORA_DISK_1: SID=409 device type=DISK
      channel ORA_DISK_1: starting full datafile backup set
      channel ORA_DISK_1: specifying datafile(s) in backup set
      input datafile file number=00001 name=+MDMDG1/mdm2/datafile/system.311.733500249
      input datafile file number=00002 name=+MDMDG1/mdm2/datafile/sysaux.317.733500249
      input datafile file number=00006 name=+MDMDG1/mdm2/datafile/canvas_popularity_da ta.335.733500249
      input datafile file number=00007 name=+MDMDG1/mdm2/datafile/canvas_popularity_id x.336.733500249
      input datafile file number=00008 name=+MDMDG1/mdm2/datafile/mips_mdm_data.337.73 3500249
      input datafile file number=00009 name=+MDMDG1/mdm2/datafile/mips_mdm_idx.312.733 500249
      input datafile file number=00010 name=+MDMDG1/mdm2/datafile/mdm_src_data.316.733 500249
      input datafile file number=00011 name=+MDMDG1/mdm2/datafile/mdm_src_idx.341.7335 00251
      input datafile file number=00012 name=+MDMDG1/mdm2/datafile/test_tbsp1.305.73350 0679
      input datafile file number=00003 name=+MDMDG1/mdm2/datafile/undotbs1.343.7335002 51
      input datafile file number=00005 name=+MDMDG1/mdm2/datafile/undotbs2.344.7335002 51
      input datafile file number=00004 name=+MDMDG1/mdm2/datafile/users.345.733500253
      channel ORA_DISK_1: starting piece 1 at 27-OCT-10

      ***********************************
      RESTORE ON DUPLICATE DATABAE

      . oraenv
      MDM11
      [oracle@rs1pre91dvdbaa01 ~]$ srvctl stop database -d MDM1
      [oracle@rs1pre91dvdbaa01 ~]$ . oraenv
      ORACLE_SID = [MDM11] ? MDM11
      The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 is /u01/app/oracle
      [oracle@rs1pre91dvdbaa01 ~]$ sqlplus / as sysdba
      SQL*Plus: Release 11.2.0.1.0 Production on Thu Oct 28 15:03:07 2010

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


      Connected to:
      Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
      With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
      Data Mining and Real Application Testing options

      SQL> shutdown immediate;
      ORA-01109: database not open


      Database dismounted.
      ORACLE instance shut down.
      SQL> startup nomount;
      ORACLE instance started.

      Total System Global Area 835104768 bytes
      Fixed Size 2217952 bytes
      Variable Size 679479328 bytes
      Database Buffers 146800640 bytes
      Redo Buffers 6606848 bytes
      SQL> exit
      Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64 bit Production
      With the Partitioning, Real Application Clusters, OLAP, Data Mining
      and Real Application Testing options
      [oracle@rs1pre91dvdbaa01 ~]$ rman target sys/c9nv9sd9t9f11e@mdm2 auxiliary /

      Recovery Manager: Release 11.2.0.1.0 - Production on Thu Oct 28 15:03:49 2010

      Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

      connected to target database: MDM (DBID=1485385044)
      connected to auxiliary database: MDM (not mounted)

      RMAN> duplicate target database to mdm;
      Starting Duplicate Db at 28-10-2010 15:04:06
      using target database control file instead of recovery catalog
      allocated channel: ORA_AUX_DISK_1
      channel ORA_AUX_DISK_1: SID=402 instance=MDM11 device type=DISK

      contents of Memory Script:
      {
      sql clone "alter system set control_files =
      ''+MDMDG1/mdm1/controlfile/current.376.733409429'', ''+MDMDG2/mdm1/controlfile /current.358.733409429'' comment=
      ''Set by RMAN'' scope=spfile";
      sql clone "alter system set db_name =
      ''MDM'' comment=
      ''Modified by RMAN duplicate'' scope=spfile";
      shutdown clone immediate;
      startup clone force nomount
      restore clone primary controlfile;
      alter clone database mount;
      }
      executing Memory Script

      sql statement: alter system set control_files = ''+MDMDG1/mdm1/controlfile/cu rrent.376.733409429'', ''+MDMDG2/mdm1/controlfile/current.358.733409429'' commen t= ''Set by RMAN'' scope=spfile

      sql statement: alter system set db_name = ''MDM'' comment= ''Modified by RMAN duplicate'' scope=spfile

      Oracle instance shut down

      Oracle instance started

      Total System Global Area 835104768 bytes

      Fixed Size 2217952 bytes
      Variable Size 679479328 bytes
      Database Buffers 146800640 bytes
      Redo Buffers 6606848 bytes

      Starting restore at 28-10-2010 15:04:22
      allocated channel: ORA_AUX_DISK_1
      channel ORA_AUX_DISK_1: SID=404 instance=MDM11 device type=DISK

      channel ORA_AUX_DISK_1: starting datafile backup set restore
      channel ORA_AUX_DISK_1: restoring control file
      channel ORA_AUX_DISK_1: reading from backup piece +MDMDG2/mdm2/autobackup/2010_1                                                                              0_27/n_733513135.274.733513135
      channel ORA_AUX_DISK_1: ORA-19870: error while restoring backup piece +MDMDG2/md                                                                              m2/autobackup/2010_10_27/n_733513135.274.733513135
      ORA-19505: failed to identify file "+MDMDG2/mdm2/autobackup/2010_10_27/n_7335131 35.274.733513135"
      ORA-17503: ksfdopn:2 Failed to open file +MDMDG2/mdm2/autobackup/2010_10_27/n_73                                                                              3513135.274.733513135
      ORA-15012: ASM file '+MDMDG2/mdm2/autobackup/2010_10_27/n_733513135.274.73351313 5' does not exist

      failover to previous backup

      channel ORA_AUX_DISK_1: starting datafile backup set restore
      channel ORA_AUX_DISK_1: restoring control file
      channel ORA_AUX_DISK_1: reading from backup piece +MDMDG2/mdm2/autobackup/2010_1                                                                              0_27/n_733502024.275.733502025
      channel ORA_AUX_DISK_1: ORA-19870: error while restoring backup piece +MDMDG2/md                                                                              m2/autobackup/2010_10_27/n_733502024.275.733502025
      ORA-19505: failed to identify file "+MDMDG2/mdm2/autobackup/2010_10_27/n_7335020 24.275.733502025"
      ORA-17503: ksfdopn:2 Failed to open file +MDMDG2/mdm2/autobackup/2010_10_27/n_73                                                                              3502024.275.733502025
      ORA-15012: ASM file '+MDMDG2/mdm2/autobackup/2010_10_27/n_733502024.275.73350202 5' does not exist

      failover to previous backup

      RMAN-00571: ===========================================================
      RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
      RMAN-00571: ===========================================================
      RMAN-03002: failure of Duplicate Db command at 10/28/2010 15:04:24
      RMAN-03015: error occurred in stored script Memory Script
      RMAN-06026: some targets not found - aborting restore
      RMAN-06024: no backup or copy of the control file found to restore

      RMAN> duplicate target database to mdm from active database;
      Starting Duplicate Db at 28-10-2010 15:07:58
      RMAN-00571: ===========================================================
      RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
      RMAN-00571: ===========================================================
      RMAN-03002: failure of Duplicate Db command at 10/28/2010 15:07:58
      RMAN-06217: not connected to auxiliary database with a net service name

      RMAN> exit


      Recovery Manager complete.
      [oracle@rs1pre91dvdbaa01 ~]$ rman target sys/c9nv9sd9t9f11e@mdm2 auxiliary sys/c9nv9sd9t9f11e@mdm11

      Recovery Manager: Release 11.2.0.1.0 - Production on Thu Oct 28 15:08:31 2010

      Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

      connected to target database: MDM (DBID=1485385044)
      RMAN-00571: ===========================================================
      RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
      RMAN-00571: ===========================================================
      RMAN-00554: initialization of internal recovery manager package failed
      RMAN-04006: error from auxiliary database: ORA-12528: TNS:listener: all appropriate instances are blocking new connections
      [oracle@rs1pre91dvdbaa01 ~]$ rman target sys/c9nv9sd9t9f11e@mdm2 auxiliary sys/c9nv9sd9t9f11e@mdm1

      Recovery Manager: Release 11.2.0.1.0 - Production on Thu Oct 28 15:08:35 2010

      Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

      connected to target database: MDM (DBID=1485385044)
      RMAN-00571: ===========================================================
      RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
      RMAN-00571: ===========================================================
      RMAN-00554: initialization of internal recovery manager package failed
      RMAN-04006: error from auxiliary database: ORA-12528: TNS:listener: all appropriate instances are blocking new connections
      [oracle@rs1pre91dvdbaa01 ~]$ rman target sys/c9nv9sd9t9f11e@mdm2 auxiliary sys/c9nv9sd9t9f11e@mdm11

      Recovery Manager: Release 11.2.0.1.0 - Production on Thu Oct 28 15:09:20 2010

      Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

      connected to target database: MDM (DBID=1485385044)
      RMAN-00571: ===========================================================
      RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
      RMAN-00571: ===========================================================
      RMAN-00554: initialization of internal recovery manager package failed
      RMAN-04006: error from auxiliary database: ORRA-12528: TNS:listener: all appropriate instances are blocking new connections
      [oracle@rs1pre91dvdbaa01 ~]$



      1. How to duplicate on datafiles from the target databse without control files.
      my target databsae is single instance and duplicate datbase is 2 node RAC.

      2. I have been said to start nomount duplicate database but then I can't get auxiliary connection thru RMAN in duplicate database.


      Thanks
        • 1. Re: Duplicate database
          CKPT
          channel ORA_AUX_DISK_1: ORA-19870: error while restoring backup piece +MDMDG2/md m2/autobackup/2010_10_27/n_733513135.274.733513135
          check the permissions on the backup piece, give full permissions and start duplicate again.

          ls -ltr n_733513135.274.733513135
          • 2. Re: Duplicate database
            772262
            ASMCMD [+mdmdg2/mdm2/autobackup] > ls
            2010_10_27/
            ASMCMD [+mdmdg2/mdm2/autobackup] > cd 2010_10_27
            ASMCMD [+mdmdg2/mdm2/autobackup/2010_10_27] > ls
            n_733502024.275.733502025
            n_733513135.274.733513135
            ASMCMD [+mdmdg2/mdm2/autobackup/2010_10_27] > ls -lt
            Type Redund Striped Time Sys Name
            AUTOBACKUP UNPROT COARSE OCT 27 17:00:00 Y n_733513135.274.733513135
            AUTOBACKUP UNPROT COARSE OCT 27 14:00:00 Y n_733502024.275.733502025
            ASMCMD [+mdmdg2/mdm2/autobackup/2010_10_27] >



            please can I know, how to change the permission in asmcmd ?
            • 3. Re: Duplicate database
              CKPT
              check the diskgroups and give it as
              /bin/chmod 660 /dev/raw/raw1
              • 4. Re: Duplicate database
                772262
                [root@rs1pre91dvdbaa01 ~]# /bin/chmod 660 /dev/sdg2
                [root@rs1pre91dvdbaa01 ~]# /bin/chmod 660 /dev/sdg1

                Question -- how do I find out which disk used in the diskgroup.

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

                [oracle@rs1pre91dvdbaa01 ~]$ sqlplus / as sysdba

                SQL*Plus: Release 11.2.0.1.0 Production on Thu Oct 28 15:44:11 2010

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


                Connected to:
                Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
                With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
                Data Mining and Real Application Testing options

                SQL> shutdown immediate;
                ORA-01507: database not mounted


                ORACLE instance shut down.
                SQL> startup nomount;
                ORACLE instance started.

                Total System Global Area 835104768 bytes
                Fixed Size 2217952 bytes
                Variable Size 679479328 bytes
                Database Buffers 146800640 bytes
                Redo Buffers 6606848 bytes
                SQL> exit
                Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64 bit Production
                With the Partitioning, Real Application Clusters, OLAP, Data Mining
                and Real Application Testing options
                [oracle@rs1pre91dvdbaa01 ~]$ rman target sys/c9nv9sd9t9f11e@mdm2 auxiliary sys/c 9nv9sd9t9f11e@mdm11

                Recovery Manager: Release 11.2.0.1.0 - Production on Thu Oct 28 15:45:07 2010

                Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

                connected to target database: MDM (DBID=1485385044)
                RMAN-00571: ===========================================================
                RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
                RMAN-00571: ===========================================================
                RMAN-00554: initialization of internal recovery manager package failed
                RMAN-04006: error from auxiliary database: ORA-12528: TNS:listener: all appropri ate instances are blocking new connections
                [oracle@rs1pre91dvdbaa01 ~]$ sqlplus / as sysdba
                SQL*Plus: Release 11.2.0.1.0 Production on Thu Oct 28 15:45:12 2010

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


                Connected to:
                Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
                With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
                Data Mining and Real Application Testing options

                SQL> alter database mount;

                Database altered.

                SQL> exit
                Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64 bit Production
                With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
                Data Mining and Real Application Testing options
                [oracle@rs1pre91dvdbaa01 ~]$ rman target sys/c9nv9sd9t9f11e@mdm2 auxiliary sys/c 9nv9sd9t9f11e@mdm11

                Recovery Manager: Release 11.2.0.1.0 - Production on Thu Oct 28 15:45:50 2010

                Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

                connected to target database: MDM (DBID=1485385044)
                connected to auxiliary database: MDM (DBID=1485385044, not open)

                RMAN> duplicate target database to mdm from active database;

                Starting Duplicate Db at 28-10-2010 15:45:54
                using target database control file instead of recovery catalog
                allocated channel: ORA_AUX_DISK_1
                channel ORA_AUX_DISK_1: SID=404 instance=MDM11 device type=DISK
                RMAN-00571: ===========================================================
                RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
                RMAN-00571: ===========================================================
                RMAN-03002: failure of Duplicate Db command at 10/28/2010 15:45:55
                RMAN-05500: the auxiliary database must be not mounted when issuing a DUPLICATE command

                RMAN>


                I am still not sure why I get error to connect when the MDM11(instnace 1 ) is unmounted
                • 5. Re: Duplicate database
                  Oviwan
                  >
                  Question -- how do I find out which disk used in the diskgroup.
                  >

                  on asm instance use v$asm_disk and v$asm_diskgroup
                  • 6. Re: Duplicate database
                    CKPT
                    SELECT name, header_status, path FROM V$ASM_DISK;
                    NAME HEADER_STATUS PATH
                    --------- ------------- ---------------------
                    CANDIDATE /dev/rdsk/disk07
                    DISK06 MEMBER /dev/rdsk/disk06
                    DISK05 MEMBER /dev/rdsk/disk05
                    DISK04 MEMBER /dev/rdsk/disk04
                    • 7. Re: Duplicate database
                      772262
                      SQL> SELECT name, header_status, path FROM V$ASM_DISK;

                      NAME HEADER_STATU PATH
                      ------------------------------ ------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                      PROVISIONED ORCL:DWHDISK4
                      PROVISIONED ORCL:MDMDISK4
                      CRSDISK1 MEMBER ORCL:CRSDISK1
                      CRSDISK2 MEMBER ORCL:CRSDISK2
                      CRSDISK3 MEMBER ORCL:CRSDISK3
                      DWHDISK1 MEMBER ORCL:DWHDISK1
                      DWHDISK2 MEMBER ORCL:DWHDISK2
                      DWHDISK3 MEMBER ORCL:DWHDISK3
                      MDMDISK1 MEMBER ORCL:MDMDISK1
                      MDMDISK2 MEMBER ORCL:MDMDISK2
                      MDMDISK3 MEMBER ORCL:MDMDISK3


                      does not give me the physical disk name.

                      and any help on duplicate database with nomount?
                      • 8. Re: Duplicate database
                        Oviwan
                        you use asmlib execute as root
                        oracleasm listdisks
                        oracleasm querydisk <Diskname>
                        • 9. Re: Duplicate database
                          CKPT
                          and any help on duplicate database with nomount?
                          you cannot..
                          • 10. Re: Duplicate database
                            772262
                            [oracle@rs1pre92dvdbsa01 ~]$ oracleasm listdisks
                            MDMDISK1
                            MDMDISK2
                            MDMDISK3
                            [oracle@rs1pre92dvdbsa01 ~]$ oracleasm querydisk MDMDISK1
                            Disk "MDMDISK1" is a valid ASM disk
                            [oracle@rs1pre92dvdbsa01 ~]$




                            Please I get some help with duplicating a rac database from a single instance on different servers.
                            • 11. Re: Duplicate database
                              CKPT
                              SQL> select GROUP_NUMBER, DISK_NUMBER, MODE_STATUS, STATE, NAME, PATH from v$asm_disk;

                              GROUP_NUMBER DISK_NUMBER MODE_ST STATE NAME PATH
                              ------------ ----------- ------- -------- --------- ----------------
                              0 0 ONLINE NORMAL /dev/rdsk/c0t2d0

                              SQL> alter diskgroup data mount;

                              Diskgroup altered.

                              SQL> select GROUP_NUMBER, DISK_NUMBER, MODE_STATUS, STATE, NAME, PATH from v$asm_disk;

                              GROUP_NUMBER DISK_NUMBER MODE_ST STATE NAME PATH
                              ------------ ----------- ------- -------- --------- ----------------
                              1 0 ONLINE NORMAL DATA_0000 /dev/rdsk/c0t2d0

                              SQL> select GROUP_NUMBER, NAME, STATE, TYPE from v$asm_diskgroup;

                              GROUP_NUMBER NAME STATE TYPE
                              ------------ ------------------------------ ----------- ------
                              1 DATA MOUNTED EXTERN

                              SQL> show parameter diskstring;

                              NAME TYPE VALUE
                              ------------------------------------ ----------- ------------------------------
                              asm_diskstring string /dev/rdsk/*




                              can you please check in PFILE/SPFIL.. parameter asm_diskstring
                              • 12. Re: Duplicate database
                                772262
                                my duplicate database is RAC 2 node
                                step 1. stop database mdm -- srvctl database -d mdm1
                                step2. . connect to instnace 1 MDM11, startup mount
                                step 3 as follows
                                [oracle@rs1pre91dvdbaa01 ~]$ rman target sys/c9nv9sd9t9f11e@mdm2 auxiliary sys/c9nv9sd9t9f11e@mdm11

                                Recovery Manager: Release 11.2.0.1.0 - Production on Thu Oct 28 16:38:14 2010

                                Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

                                connected to target database: MDM (DBID=1485385044)
                                connected to auxiliary database: MDM (DBID=1485385044, not open)


                                RMAN> run
                                2> {
                                3> duplicate target database to mdm from active database
                                4> db_file_name_convert 'MDM2','MDM1';
                                5> }

                                Starting Duplicate Db at 28-10-2010 16:41:10
                                using target database control file instead of recovery catalog
                                allocated channel: ORA_AUX_DISK_1
                                channel ORA_AUX_DISK_1: SID=405 instance=MDM11 device type=DISK
                                RMAN-00571: ===========================================================
                                RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
                                RMAN-00571: ===========================================================
                                RMAN-03002: failure of Duplicate Db command at 10/28/2010 16:41:11
                                RMAN-05500: the auxiliary database must be not mounted when issuing a DUPLICATE command


                                If I do startup nomount then I get this error
                                [oracle@rs1pre91dvdbaa01 ~]$ rman target sys/c9nv9sd9t9f11e@mdm2 auxiliary sys/c9nv9sd9t9f11e@mdm11

                                Recovery Manager: Release 11.2.0.1.0 - Production on Thu Oct 28 15:09:20 2010

                                Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

                                connected to target database: MDM (DBID=1485385044)
                                RMAN-00571: ===========================================================
                                RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
                                RMAN-00571: ===========================================================
                                RMAN-00554: initialization of internal recovery manager package failed
                                RMAN-04006: error from auxiliary database: ORRA-12528: TNS:listener: all appropriate instances are blocking new connections
                                [oracle@rs1pre91dvdbaa01 ~]$



                                Where is the problem -- I am going in a loop :(
                                • 13. Re: Duplicate database
                                  CKPT
                                  Hi OP,
                                  connected to auxiliary database: MDM (DBID=1485385044, not open)
                                  have you restored controlfile eariler... then how it come in (not open i.e. mount ) status. it should be nomount, earlier if i mentioned as mount sorry, correct it..
                                  when ever you trying to duplicate it will recreate the controlfile, so make sure there is no controlfile in the directory..

                                  Thanks