Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2004
    Location
    Manchester, UK
    Posts
    143

    Unanswered: Oracle 10g Cloning onto Same Server

    Hi,

    I have 2 Oracle 10g databases on one Intel server. One is live, and one is test. I want to copy live to test without live coming down. I have used the following many times before when copying between servers but I'm pretty sure never on the same server. The error I get is:

    HTML Code:
    GISTEST oracle > rman target sys/xxxx nocatalog
    
    Recovery Manager: Release 10.2.0.1.0 - Production on Thu Apr 25 14:44:47 2013
    
    Copyright (c) 1982, 2005, Oracle.  All rights reserved.
    
    connected to target database: GISLIVE (not mounted)
    using target database control file instead of recovery catalog
    
    RMAN> set dbid = 4072485475
    
    executing command: SET DBID
    
    RMAN> restore controlfile from '/u02/RMAN/GISLIVE/autobackup_control_filec-4072485475-20130424-01';
    
    Starting restore at 25-APR-13
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: sid=156 devtype=DISK
    
    channel ORA_DISK_1: restoring control file
    channel ORA_DISK_1: restore complete, elapsed time: 00:00:09
    output filename=/u01/app/oradata/GISTEST/control01.ctl
    output filename=/u01/app/oradata/GISTEST/control02.ctl
    output filename=/u01/app/oradata/GISTEST/control03.ctl
    Finished restore at 25-APR-13
    
    RMAN> sql 'alter database mount';
    
    sql statement: alter database mount
    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03009: failure of sql command on default channel at 04/25/2013 14:47:00
    RMAN-11003: failure during parse/execution of SQL statement: alter database mount
    ORA-01102: cannot mount database in EXCLUSIVE mode

    The process I am following is this:

    1. Find next SCN of the latest backup of the live database
    2. Set the test environment (SID, ORACLE_BASE etc)
    3. Shutdown test database, startup mount restrict and drop database.
    4. Take a copy of the pfile.
    5. Edit the pfile and change db_name to the name of the live database
    6. Shutdown the test instance and startup nomount
    7. Run the following script, using the live db's DBID, the live control file backup, and the next SCN of the last live backup in RMAN.

    HTML Code:
    #!/usr/bin/ksh
    #
    echo $ORACLE_SID
    #set up the environment
    PATH=/opt/csw/bin:/usr/sbin:/usr/bin:/usr/dt/bin:/usr/openwin/bin:/usr/ccs/bin:/usr/local/bin:/usr/bin:/usr/ucb:/etc:/u01/app/oracle/product/10.2.0/db_1/bin
    export PATH
    echo $ORACLE_SID
    ORACLE_BASE=/u01/app
    export ORACLE_BASE
    ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
    export ORACLE_HOME
    ORAENV_ASK=NO
    export ORAENV_ASK
    . oraGISTEST
    echo $ORACLE_SID
    #
    rman target sys/xxxx nocatalog << EOF
    set dbid=4072485475
    run {
    ALLOCATE CHANNEL RMAN_BACK_CH01 TYPE DISK;
    ALLOCATE CHANNEL RMAN_BACK_CH02 TYPE DISK;
    ALLOCATE CHANNEL RMAN_BACK_CH03 TYPE DISK;
    restore controlfile from '/u02/RMAN/GISLIVE/autobackup_control_filec-4072485475-20130424-01'; 
    sql 'alter database mount';
    Set newname for datafile '/u01/app/oradata/GISLIVE/system01.dbf' to '/u01/app/oradata/GISTEST/system01.dbf';
    Set newname for datafile '/u01/app/oradata/GISLIVE/undotbs01.dbf' to '/u01/app/oradata/GISTEST/undotbs01.dbf';
    Set newname for datafile '/u01/app/oradata/GISLIVE/sysaux01.dbf' to '/u01/app/oradata/GISTEST/sysaux01.dbf';
    Set newname for datafile '/u01/app/oradata/GISLIVE/OSADMIN01.DBF'  to '/u01/app/oradata/GISTEST/OSADMIN01.DBF';
    Set newname for datafile '/u01/app/oradata/GISLIVE/OSADMIN02.DBF'  to '/u01/app/oradata/GISTEST/OSADMIN02.DBF';
    Set newname for datafile '/u01/app/oradata/GISLIVE/OSADMIN03.DBF'  to '/u01/app/oradata/GISTEST/OSADMIN03.DBF';
    Set newname for datafile '/u01/app/oradata/GISLIVE/OSADMIN04.DBF'  to '/u01/app/oradata/GISTEST/OSADMIN04.DBF';
    Set newname for datafile '/u01/app/oradata/GISLIVE/GISADMIN01.DBF'  to '/u01/app/oradata/GISTEST/GISADMIN01.DBF';
    Set newname for datafile '/u01/app/oradata/GISLIVE/example01.dbf' to '/u01/app/oradata/GISTEST/example01.dbf';
    Set newname for datafile '/u01/app/oracle/product/10.2.0/db_1/dbs/SPATIAL_ARCHIVE01.DBF' to '/u01/app/oradata/GISTEST/SPATIAL_ARCHIVE01.DBF';
    Set newname for datafile '/u01/app/oradata/GISLIVE/users01.dbf' to '/u01/app/oradata/GISTEST/users01.dbf';
    Set newname for tempfile '/u01/app/oradata/GISLIVE/temp01.dbf' to '/u01/app/oradata/GISTEST/temp01.dbf';
    sql "alter database rename file ''/u01/app/oradata/GISLIVE/redo01.log'' to ''/u01/app/oradata/GISTEST/redo01.log''";
    sql "alter database rename file ''/u01/app/oradata/GISLIVE/redo02.log'' to ''/u01/app/oradata/GISTEST/redo02.log''";
    sql "alter database rename file ''/u01/app/oradata/GISLIVE/redo03.log'' to ''/u01/app/oradata/GISTEST/redo03.log''";
    restore database;
    switch datafile all;
    switch tempfile all;
    recover database until scn 170235338;
    sql 'alter database open resetlogs';
    }
    exit;
    EOF

    At first I wondered if I should not put the live database name in the pfile, so I amended it and got the following error:


    HTML Code:
    RMAN> sql 'alter database mount';
    
    sql statement: alter database mount
    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03009: failure of sql command on default channel at 04/25/2013 13:11:15
    RMAN-11003: failure during parse/execution of SQL statement: alter database mount
    ORA-01103: database name 'GISLIVE' in control file is not 'GISTEST'
    So, I think that Oracle does not like having to have 2 sets of processes connected to GISLIVE (which happens temporarily as the live control file is restored into the test environment), thereby creating the 'cannot mount in EXCLUSIVE mode' error.

    Am I right, and if so what can I do about it? If I shutdown the live database and follw the above procedure will it work? I'd rather not shut down live but it might be an option.

    Or, can anyone suggest a better way? I know there is a duplicate database command but I have never tried it and am not sure of any pre-requisite steps to carry out.

    Any help much appreciated.

    Thanks,
    Paula.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    when all else fails, Read The Fine Manual

    Online Database Maintenance with Oracle Streams
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Aug 2004
    Location
    Manchester, UK
    Posts
    143
    Thank you, but to be brutally honest I wanted a quick fix! I just don't think I've got time to read all that and go through all the setup! Very tight timescales going on!

    I think I may have to shut down the live db and try my script again.....but I wouldn't mind some reassurance that it will probably work if I do that. Then after the dust has settled, I promise I'll look into the DUPLICATE command.

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Quote Originally Posted by Polly013 View Post
    Thank you, but to be brutally honest I wanted a quick fix! I just don't think I've got time to read all that and go through all the setup! Very tight timescales going on!

    I think I may have to shut down the live db and try my script again.....but I wouldn't mind some reassurance that it will probably work if I do that. Then after the dust has settled, I promise I'll look into the DUPLICATE command.
    1) There is no "quick fix", you will have to study (read the fine manual) independent of the alternative you choose.
    2) You do not need to shutdown if: a) You restore from a backup or b) you use DUPLICATE command.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You only need to understand what you're doing if you want things to work. As long as you don't mind the occasional system crash, or having the test instance change the production data, then you can rush/experiment as you see fit.

    If you don't have time to read the fine manual, I'd recommend that you update your resume... You might need it soon!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Aug 2004
    Location
    Manchester, UK
    Posts
    143
    Just an update ..... I did as I originally planned and shut the live database down briefly, and it worked very well, as I suspected but didn't know for sure. This was the question I originally asked - in other people's opinion would it work if I did that. Unfortunately instead of people giving their opinion on that question you both chose to berate me. And so I went ahead and took a chance which paid off.

    In the real working world people work under pressure and time constraints. I know this as I have worked for 8 years in my current role, supporting 4 different database platforms and around 400 databases on around 40 servers, single handedly (I am the only DBA for my organisation). This means that I don't always have all the answers, and also that I don't always have the luxury of 'reading the manual'. If I did this instead of getting the job done I would without doubt have to 'update my resume' as my employer would not be happy with my performance. As it is, my employer trusts me to perform and do my job well, which I have been doing for the past 8 years. In a time of economic difficulty the real working world requires that we all do our best with the resources we have and I try to do that. Occasionally it's a lonely position to be in, and I feel the need to ask for reassurance from others. I know now never to do that again on this forum, as it would seem that unbending, unsympathetic and smug responses are all I would receive.

    Now that I have resolved my issue, I will add researching the duplicate command to my list of things to do when I get a quiet moment, as contrary to your beliefs I am good at my job.

    I hope you feel contented on your oh so very high horses.

  7. #7
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Quote Originally Posted by Polly013 View Post
    . . . E t c . . .
    I hope you feel contented on your oh so very high horses.
    We truly apologize if you believe we were trying to "berate" you by providing responses that "seem unbending, unsympathetic and smug".

    Many of us "experts" have at one time or another experienced the high pressure situation that you describe and therefore are very cautious on suggesting / reassuring or recommending you follow an unorthodox procedure which could result in catastrophic failure.

    You took a chance that could have ended you career.

    Good luck in the future!
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  8. #8
    Join Date
    Aug 2004
    Location
    Manchester, UK
    Posts
    143
    No chance taken. Proper change procedures were followed, customers were notified of planned downtime, and as a good DBA I of course had a live backup to revert back to (which would have taken around 30 mins to restore) had anything gone wrong. End of.

  9. #9
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    I think I may have to shut down the live db and try my script again.....but I wouldn't mind some reassurance that it will probably work if I do that.
    I could have told you that it would probably work, but then what would have happened if it hadn't? ANY deviation from Oracle recommended procedures carries a risk of things going catastrophically wrong. It would be very naughty of us to recommend or encourage you to do something that may end your career. You took a chance & got away with it. Well done! But don't have a go at forum members for not wishing to join in with your risk-taking ventures.
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

Posting Permissions

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