1 2 Previous Next 17 Replies Latest reply: Aug 4, 2012 8:09 PM by EdStevens RSS

    Steps to clone database in other server with restore daily

    Me_101
      Hi,

      I think i know what is the steps but need a guru that give me the ok.

      I have been asked to perform a clone database (11.2.0.3 with ASM) in another server. The original database (call PROD) has full backup everyday that it automatically (with crontab and a script i guess) restore the second database. The problem i see its that the original database is a RAC (2 nodes) but the second database is standalone (call TEST)

      I think the steps are:

      1) create a standalone database in second server with same number of diskgroups and same directories
      2) copy full backup to TEST server.
      3) duplicate with rman:

      connect auxiliary sys/password@TEST
      connect target /
      startup auxiliary nomount;
      run {
      allocate auxiliary channel ch1 type disk;
      duplicate target database to 'TEST' nofilenamecheck;
      release channel ch1;
      }

      4) copy PFILE from PROD and modify (like standalone database) at TEST
      5) startup nomount; --test database
      6) restore controlfile from '< backup piece name of controlfile auto backup copied in step 2>'; --at test database
      7) alter database mount; --test database
      8) recover, restore from full backup and open database-->
      run {
      allocate channel d1 device type disk format ' <path of the copied backup in step 2 >';
      set until sequence < sequence> thread < thread# >;
      restore database;
      recover database;
      }

      SQL> alter database open resetlogs;

      9) like its a backup that has 2 undo tablespaces, should i drop 1?:
      SQL> drop tablespace UNDOTBS2 including contents and datafiles;

      All this steps at first time, are they right?, to do it automatically in the script i should use 2) 5) 6) 7) 8)?

      -- like the databases names are different, should i have any problem when restore de controlfile? if yes, how to avoid it?

      Thanks in advance and sorry for the inconveniences

      Edited by: Me_101 on 30-jul-2012 5:46
        • 1. Re: Steps to clone database in other server with restore daily
          Balazs Papp
          step 1) its not necessary to have the same diskgroups/structure

          for the rest:
          you should have a look at the new 11.2 feature, backup-based duplication
          you wont have to connect to original DB + it will take care of step 6-7-8 and the different database names

          also for first time setup: you duplicate in step 3, then why do a restore+recover in step 5-6-7-8?
          • 2. Re: Steps to clone database in other server with restore daily
            EdStevens
            Me_101 wrote:
            Hi,

            I think i know what is the steps but need a guru that give me the ok.

            I have been asked to perform a clone database (11.2.0.3 with ASM) in another server. The original database (call PROD) has full backup everyday that it automatically (with crontab and a script i guess)
            Why guess? Can't you look at the crontab schedule and see for yourself?
            restore the second database. The problem i see its that the original database is a RAC (2 nodes) but the second database is standalone (call TEST)
            That should not be an issue. Even with RAC there is still only a single database to duplicate.
            I think the steps are:

            1) create a standalone database in second server with same number of diskgroups and same directories
            2) copy full backup to TEST server.
            3) duplicate with rman:

            connect auxiliary sys/password@TEST
            connect target /
            startup auxiliary nomount;
            run {
            allocate auxiliary channel ch1 type disk;
            duplicate target database to 'TEST' nofilenamecheck;
            release channel ch1;
            }

            4) copy PFILE from PROD and modify (like standalone database) at TEST
            5) startup nomount; --test database
            6) restore controlfile from '< backup piece name of controlfile auto backup copied in step 2>'; --at test database
            7) alter database mount; --test database
            8) recover, restore from full backup and open database-->
            run {
            allocate channel d1 device type disk format ' <path of the copied backup in step 2 >';
            set until sequence < sequence> thread < thread# >;
            restore database;
            recover database;
            }

            SQL> alter database open resetlogs;

            9) like its a backup that has 2 undo tablespaces, should i drop 1?:
            SQL> drop tablespace UNDOTBS2 including contents and datafiles;

            All this steps at first time, are they right?, to do it automatically in the script i should use 2) 5) 6) 7) 8)?

            -- like the databases names are different, should i have any problem when restore de controlfile? if yes, how to avoid it?

            Thanks in advance and sorry for the inconveniences

            Edited by: Me_101 on 30-jul-2012 5:46
            Take a look at the DUPLICATE DATABASE command in rman. Key is to connect to the production db as target, duplicate databse as auxiliary
            connect catalog rman/cat@rmcat
            connect target sys/pswd@proddb 
            connect auxiliary /
            run {                         
            duplicate target database to NEWDBSID;
            }                                        
            where NEWDBSID is the name of the new database


            Put together what you think is the correct procedure and give it a shot. If you have specific problems, bring them to the forum.
            • 3. Re: Steps to clone database in other server with restore daily
              Me_101
              step 1) its not necessary to have the same diskgroups/structure
              
              for the rest:
              you should have a look at the new 11.2 feature, backup-based duplication
              you wont have to connect to original DB + it will take care of step 6-7-8 and the different database names
              
              also for first time setup: you duplicate in step 3, then why do a restore+recover in step 5-6-7-8?
              Thanks for reply,

              Yes, i have to duplicate first, but in the next days i have to restore/recover from the full backup of PROD database. I just question all like one question, but probably i have to make like 2 separated questions. Sorry for the confusion.

              You told that step 1 is not necessary. So i suppose that i only have to create a database + ASM (same number of diskgroups)?
              I can't test it at this moment, so if someone can tell me what happend if i create a database and duplicate from a database + ASM, will it work? I think no, but not sure.
              • 4. Re: Steps to clone database in other server with restore daily
                Me_101
                Hi and thanks for reply,

                I can't tested it in crontab now because i haven't the duplicated database and haven't enough space to create a new one for test :( I will do it, but can't at this moment.

                I have a new question about your answer:
                connect target sys/pswd@proddb 
                connect auxiliary / 
                run {                         
                duplicate target database to NEWDBSID;
                }
                i'm not using catalog, so i omitted it.
                Your script should be run from TEST server and my script should be run from PROD server, right?
                Anyway to run it from any server could it be?:
                connect target sys/pswd@PROD
                connect auxilary sys/pswd@TEST
                run{
                duplicate target database to TEST;
                }

                is there a good idea execute this? I think i will avoid any error if i execute it from wrong server.

                Again, thanks for all
                • 5. Re: Steps to clone database in other server with restore daily
                  EdStevens
                  Me_101 wrote:
                  Hi and thanks for reply,

                  I can't tested it in crontab
                  Who said anything about testing in crontab? You said "The original database (call PROD) has full backup everyday that it automatically (with crontab and a script i guess)" So I suggested there is no reason to "guesss" if it is run from a crontab script. Just look at crontab (cmd: crontab -l) and see for yourself if it is or not. No guessing required.



                  now because i haven't the duplicated database and haven't enough space to create a new one for test :( I will do it, but can't at this moment.
                  You seem to be confusing "duplicating" a database with "createing" a database. It's the same thing. "DUPLICATE DATABASE" creates another database using a backup of an existing database. Duplicate it again the next day, and you are re-creating it again.
                  >
                  I have a new question about your answer:
                  connect target sys/pswd@proddb 
                  connect auxiliary / 
                  run {                         
                  duplicate target database to NEWDBSID;
                  }
                  i'm not using catalog, so i omitted it.
                  Your script should be run from TEST server and my script should be run from PROD server, right?
                  well, when you connect "@somedb" you are using sqlnet to connect to a database .. so it can be over the network. When you simply connect "/" (with no sqlnet connect string) you are connecting to a local database.

                  Anyway to run it from any server could it be?:
                  connect target sys/pswd@PROD
                  connect auxilary sys/pswd@TEST
                  What does it cost to try it for yourself?
                  run{
                  duplicate target database to TEST;
                  }

                  is there a good idea execute this? I think i will avoid any error if i execute it from wrong server.
                  Just make sure you keep straight which is the primary and which is the auxiliary, so you don't end up trying to overwrite the live, production database.
                  Again, thanks for all
                  • 6. Re: Steps to clone database in other server with restore daily
                    Me_101
                    Thanks again, Edstevens. I am very grateful for your answers and the time you spend with me.
                    Who said anything about testing in crontab? You said "The original database (call PROD) has full backup everyday that it automatically (with crontab and a script i guess)" So I suggested there is no reason to "guesss" if it is run from a crontab script. Just look at crontab (cmd: crontab -l) and see for yourself if it is or not. No guessing required.
                    I was misunderstood, I did not know explain it correctly in English, Sorry
                    I will have to create a crontab to make daily the restore (from the PROD backup) in the TEST database. I mean, PROD database with Enterprise manager makes a full backup every day, then with a crontab in its server move the full backup to TEST server(this crontab is created at this moment) and in the TEST server i have to create a automatic proccess to restore the TEST database.

                    To restore TEST database with the backup i have to restore the controlfile first. This step makes me a bit scared. Could i received a error because the names are different in both databases?
                    Anyway to run it from any server could it be?:
                    connect target sys/pswd@PROD
                    connect auxilary sys/pswd@TEST
                    What does it cost to try it for yourself?
                    The cost is high, because it's a production database (24x7). I can't try all i think with it so I try to resolve some doubts before try anything.

                    I think i have clear idea about primary and auxiliary: Primary is the original database (PROD), and auxiliary is database that will be overwritten (TEST). I only have to take care, like you told to me :).
                    • 7. Re: Steps to clone database in other server with restore daily
                      mseberg
                      Hello;

                      If I was doing this daily I would be looking at "Active Database Duplicating". I would setup an INIT file for Active Database Duplicating and create an spfile from it. After that is a simple RMAN start and a single line duplicate command.
                      Start RMAN
                      
                      $ORACLE_HOME/bin/rman target=sys/<password>@recover2 auxiliary=sys/<password>@reclone
                      
                      
                      Recovery Manager: Release 11.2.0.2.0 - Production on Wed Feb 22 14:50:31 2012
                      
                      Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
                      
                      connected to target database: RECOVER2 (DBID=3806912436)
                      connected to auxiliary database: RECLONE (not mounted)
                      
                      Issue Duplicate Command
                      
                      
                      DUPLICATE TARGET DATABASE TO RECLONE FROM ACTIVE DATABASE SPFILE NOFILENAMECHECK; 
                      I do this all the time for testing and RMAN overwrites the prior database. If you keep a stable Pfile for the duplicate the whole thing with database shutdown takes about 2 minutes. The the memory script just runs.

                      Best Regards

                      msebeg
                      • 8. Re: Steps to clone database in other server with restore daily
                        Me_101
                        Dear mseberg,

                        In first place, i though the same like you but my boss want to recover the backup in the new database to check the backups and be sure that they can be restored without problems. So i have to restore everyday from the full copy of PROD database....
                        • 9. Re: Steps to clone database in other server with restore daily
                          mseberg
                          Understood.

                          My push back would be "Show you want to do something RMAN does and is better equipped to do?"

                          My boss says things like this all the time. I just setup the demo in test and try to give him credit.

                          I know the boss wins even if its not logical.

                          But the push back is very important. As it is Business people have taken I.T. off course. They try to measure it like we are moving boxes while at the same time planning incompatible systems. If I.T. never pushes back then non-technical people will run it into the ground. The whole idea is to more with less, not make more, more complex.

                          I wish you the best.

                          Best Regards

                          mseberg

                          Edited by: mseberg on Jul 31, 2012 7:37 AM
                          • 10. Re: Steps to clone database in other server with restore daily
                            Me_101
                            I know the boss wins even if its not logical.
                            So truth...
                            • 11. Re: Steps to clone database in other server with restore daily
                              Me_101
                              i will try with your advices, if i have any problem i will report again in this thread if not i will close it :)

                              thanks to all so much.
                              • 12. Re: Steps to clone database in other server with restore daily
                                Me_101
                                Hi again gurus,

                                i created two databases on my laptop to try it and i have some issues.

                                I duplicated database PROD into database COPY successfully. And copy the pfile from PROD and paste at COPY:
                                COPY.__db_cache_size=671088640
                                COPY.__java_pool_size=16777216
                                COPY.__large_pool_size=16777216
                                COPY.__oracle_base='C:\Users\my_user\miBBDD'#ORACLE_BASE set from environment
                                COPY.__pga_aggregate_target=671088640
                                COPY.__sga_target=973078528
                                COPY.__shared_io_pool_size=0
                                COPY.__shared_pool_size=251658240
                                COPY.__streams_pool_size=0
                                *.audit_file_dest='C:\Users\my_user\miBBDD\admin\COPY\adump'
                                *.audit_trail='db'
                                *.compatible='11.2.0.0.0'
                                *.control_files='C:\Users\my_user\miBBDD\oradata\COPY\control01.ctl','C:\Users\my_user\miBBDD\flash_recovery_area\COPY\control02.ctl'
                                *.db_block_size=8192
                                *.db_domain=''
                                *.db_name='COPY'
                                *.db_unique_name='PROD'
                                *.db_recovery_file_dest='C:\Users\my_user\miBBDD\flash_recovery_area'
                                *.db_recovery_file_dest_size=4102029312
                                *.diagnostic_dest='C:\Users\my_user\miBBDD'
                                *.dispatchers='(PROTOCOL=TCP) (SERVICE=PRODXDB)'
                                *.local_listener='LISTENER_PROD'
                                *.memory_target=1632632832
                                *.open_cursors=300
                                *.processes=150
                                *.remote_login_passwordfile='EXCLUSIVE'
                                *.undo_tablespace='UNDOTBS1'
                                At this step if i tried to open the database COPY i get the ORA-01102: cannot mount database in EXCLUSIVE mode
                                Also if i tried with FORCE option:
                                SQL> shutdown immediate
                                ORA-01507: base de datos sin montar
                                
                                Instancia ORACLE cerrada.
                                
                                SQL> STARTUP FORCE PFILE='C:\Users\my_user\miBBDD\product\11.2.0\dbhome_1\data
                                base\INITcopia.ORA';
                                Instancia ORACLE iniciada.
                                
                                Total System Global Area 1636814848 bytes
                                Fixed Size                  2176248 bytes
                                Variable Size             956304136 bytes
                                Database Buffers          671088640 bytes
                                Redo Buffers                7245824 bytes
                                ORA-01102: cannot mount database in EXCLUSIVE mode
                                Also if i try to open without PFILE option i get ORA-01103:
                                SQL> startup
                                Instancia ORACLE iniciada.
                                
                                Total System Global Area 1636814848 bytes
                                Fixed Size                  2176248 bytes
                                Variable Size             956304136 bytes
                                Database Buffers          671088640 bytes
                                Redo Buffers                7245824 bytes
                                ORA-01103: database name 'PROD' in control file is not 'COPY'
                                Like you can see i add *.db_unique_name='PROD' at pfile of COPY database (i read it in a thread of this forum, but it doesn't seem work.)

                                I don't know what try now, so i need your help again.

                                Thanks and sorry for the inconveniences.
                                • 13. Re: Steps to clone database in other server with restore daily
                                  Sebastian Solbach -Dba Community-Oracle
                                  Hi,

                                  what does the alert.log of copy tell you in the first case?

                                  Regards
                                  Sebastian
                                  • 14. Re: Steps to clone database in other server with restore daily
                                    Me_101
                                    i drop both databases and i'm trying to duplicate again so can't do it. Thanks any way i will close this thread to clean the forum.
                                    1 2 Previous Next