This discussion is archived
1 2 Previous Next 27 Replies Latest reply: Oct 25, 2010 2:43 PM by 808135 RSS

Problems with cloning database using image copies

660550 Newbie
Currently Being Moderated
I am using Oracle 10.2.0.4 on a RHEL 4 Linux 64bit setup.

The problem I am trying to solve is to copy the production database to a local machine for development and QA. I have tried to include as much detail as I can.

The environment:

I have a production system that is replicating to a physical standby.
We are taking incremental backups on the standby database, which are applied to image copies every night. We are using a recovery catalog.
The archive logs and a backup of the control files are stored in the same directory as the image copies.

The process:

We copied all the files from that backup directory to the local machine.
At this point I tried several methods to recover the database:

1. Clone:
- I copied the initialization file from the primary machine to the local machine. I modified the pathing and memory parameters.
- I made a trace of the control file and copied it to the local machine.
- After making the needed modifications to the creation script, I created the new control file
- I then issued:
recover database using backup controlfile until cancel;
- Upon receiving the following:
ORA-00279: change 313748229 generated at 09/14/2008 00:30:55 needed for thread 1
ORA-00289: suggestion :
/u02/app/oracle/flash_recovery_area/SWD1ORACLE1/archivelog/2008_09_18/o1_mf_1_22304_%u_.arc
ORA-00280: change 313748229 for thread 1 is in sequence #22304
Specify log: {=suggested | filename | AUTO | CANCEL} 
-- I then entered CANCEL
- My next message was: {codeORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 4 needs more recovery to be consistent
ORA-01110: data file 4:
'/u02/app/oracle/oradata/datatrak/ora_df664390338_s1145_s1'
ORA-01112: media recovery not started {code}
- Out of sheer hope and prayer, I issued:
alter database open resetlogs;
- That got me the following error:
ERROR at line 1:
ORA-01194: file 4 needs more recovery to be consistent
ORA-01110: data file 4:
'/u02/app/oracle/oradata/datatrak/ora_df664390338_s1145_s1'
I then moved to possibility number 2: RMAN

2. RMAN restore/recovery:
- I first tried
run { allocate channel ch1 device type disk; restore database; }
- It gave me the following:
allocated channel: ch1
channel ch1: sid=1643 devtype=DISK
Starting restore at 18-SEP-08
released channel: ch1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 09/18/2008 13:24:27
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 7 found to restore
RMAN-06023: no backup or copy of datafile 6 found to restore
RMAN-06023: no backup or copy of datafile 5 found to restore
RMAN-06023: no backup or copy of datafile 4 found to restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 2 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore
- Then I started crosschecking files:
-- datafile 1:
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=1643 devtype=DISK
-- datafile 2 on:
using channel ORA_DISK_1
- I then issued:
list incarnation of database;
- Which gave me:
List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       DATATRAK 3742967129       CURRENT 18136361   26-NOV-07
- I tried both of these
restore datafile 1;
restore datafile '/u02/app/oracle/oradata/datatrak/ora_df664390339_s1151_s 1'
, which gave me the same results:
Starting restore at 18-SEP-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=1643 devtype=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 09/18/2008 13:49:08
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 1 found to restore
- I then went back to the beginning and tried to restore the controlfile rather than using the newly created controlfile
- I issued:
restore controlfile from '/u02/app/oracle/oradata/datatrak/cf_D-DATATRAK_i d-3742967129_i6jqcmaq';
- Which gave me:
Starting restore at 18-SEP-08
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 09/18/2008 14:02:26
RMAN-06172: no autobackup found or specified handle is not a valid copy or piece
- After learning we didn't use autobackup to backup our controlfile, I now am faced with the proverbial wall.

Data Pump extract and RMAN duplicate are possibilities, but we really don't want to do those.

Any help on this problem would be greatly appreciated.

For completeness, the initialization
aq_tm_processes = 0
audit_file_dest = /u02/app/oracle/product/10.2.0/db_1/admin/datatrak/adump
background_dump_dest = /u02/app/oracle/product/10.2.0/db_1/admin/datatrak/bdump
core_dump_dest = /u02/app/oracle/product/10.2.0/db_1/admin/datatrak/cdump
db_file_multiblock_read_count = 16
db_unique_name = SWD1ORACLE1
disk_asynch_io = TRUE
dispatchers = '(PROTOCOL=TCP) (SERVICE=datatrakXDB)'
event = '10235 trace name context forever, level 2'
filesystemio_options = ASYNCH
job_queue_processes = 10
nls_length_semantics = BYTE
plsql_code_type = INTERPRETED
plsql_native_library_dir = /u01/app/oracle/ncomplibs/
plsql_native_library_subdir_count = 150
recyclebin = OFF
resource_manager_plan = ''
service_names = SWD1ORACLE1
session_cached_cursors = 400
session_max_open_files = 20
sga_max_size = 4000M
smtp_out_server = swd1oracle1
standby_file_management = AUTO
streams_pool_size = 50M
undo_retention = 900
user_dump_dest = /u02/app/oracle/product/10.2.0/db_1/admin/datatrak/udump
compatible = 10.2.0.1.0
control_files = ('/u02/app/oracle/product/10.2.0/db_1/dbs/cf_D-DATATRAK_id-3742967129_i6jqcmaq')
db_block_size = 8192
db_create_file_dest = '/u02/app/oracle/oradata/datatrak'
db_domain = ''
db_name = datatrak
db_recovery_file_dest = '/u02/app/oracle/flash_recovery_area'
db_recovery_file_dest_size = 150G
log_archive_dest_1 = 'LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DATATRAK'
log_archive_dest_state_1 = ENABLE
open_cursors = 6000
pga_aggregate_target = 250M
processes = 1500
remote_login_passwordfile = EXCLUSIVE
sessions = 1655
sga_target = 4000M
undo_management = AUTO
undo_tablespace = UNDOTBS1
And the control file creation:
CREATE CONTROLFILE REUSE DATABASE "DATATRAK" RESETLOGS NOARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 2336 LOGFILE GROUP 1 ( '/u02/app/oracle/oradata/datatrak/redo01.log' ) SIZE 50M, GROUP 2 ( '/u02/app/oracle/oradata/datatrak/redo02.log' ) SIZE 50M DATAFILE '/u02/app/oracle/oradata/datatrak/ora_df664390339_s1151_s1', '/u02/app/oracle/oradata/datatrak/ora_df664390338_s1149_s1', '/u02/app/oracle/oradata/datatrak/ora_df664390339_s1150_s1', '/u02/app/oracle/oradata/datatrak/ora_df664390338_s1145_s1', '/u02/app/oracle/oradata/datatrak/ora_df664390338_s1146_s1', '/u02/app/oracle/oradata/datatrak/ora_df664390338_s1147_s1', '/u02/app/oracle/oradata/datatrak/ora_df664390338_s1148_s1' CHARACTER SET WE8ISO8859P1;
Sincerely,
Derek
  • 1. Re: Problems with cloning database using image copies
    26741 Oracle ACE
    Currently Being Moderated
    Why did you CANCEL the Recovery on the Clone ? Image Backups still need ArchiveLogs to be applied to them.


    Hemant K Chitale
    http://hemantoracledba.blogspot.com
  • 2. Re: Problems with cloning database using image copies
    NavneetU Expert
    Currently Being Moderated
    Hi,

    Can you please tell what command you use to backup your database using RMAN?

    Beacuse If you have not included the "ALter system archive log current;" ,Backup will not contain the information from the current red log files. And that information is required when cloning the database. In your case you might be successfull restoring the database sometime back but not to the latest time.

    Please post the backup script you use for RMAN.
    hope it helps!

    Regards,
    Navneet
  • 3. Re: Problems with cloning database using image copies
    660550 Newbie
    Currently Being Moderated
    Hemant,

    When I ran into the error messages, I researched the problem and found that people had success with using the "until cancel" clause.
  • 4. Re: Problems with cloning database using image copies
    639907 Oracle ACE Director
    Currently Being Moderated
    Hi,

    Hermant is asking why you did (or entered) cancel, canceling the archive logs to be applied in the recovery process?

    Cheers,

    Francisco Munoz Alvarez
    http://www.oraclenz.com
  • 5. Re: Problems with cloning database using image copies
    660550 Newbie
    Currently Being Moderated
    If I run
    recover database using backup controlfile
    then I still run into the
    ORA-01194: file 4 needs more recovery to be consistent
    error. When I researched the error and possible solution, I found that people used the "until cancel" clause.
  • 6. Re: Problems with cloning database using image copies
    639907 Oracle ACE Director
    Currently Being Moderated
    Hi,

    Yes, you will use until cancel, but do not write cancel, you need to allow the process to apply the archives.
    ORA-00279: change 313748229 generated at 09/14/2008 00:30:55 needed for thread 1
    ORA-00289: suggestion :
    /u02/app/oracle/flash_recovery_area/SWD1ORACLE1/archivelog/2008_09_18/o1_mf_1_22304_%u_.arc
    ORA-00280: change 313748229 for thread 1 is in sequence #22304
    Specify log: {=suggested | filename | AUTO | CANCEL}
    In your case, the process is requesting the archive # 22304 and sugestion the location and name where is suppose to be:
    /u02/app/oracle/flash_recovery_area/SWD1ORACLE1/archivelog/2008_09_18/o1_mf_1_22304_%u_.arc, in case the suggestion is right just press enter, if is wrong, just enter the correct location like: $ORACLE_HOME/logs/xxxxxxx_22304.arc and press enter. If you don't have the archives requested, look for them in the source server and copy to the target server.

    Cheers,

    Francisco Munoz Alvarez
    http://www.oraclenz.com
  • 7. Re: Problems with cloning database using image copies
    660550 Newbie
    Currently Being Moderated
    I am a Jr. DBA trying to figure this out. The Sr. DBA is busy with other tasks, so I am basically on my own.

    We have the archive logs moving from the production box to the standby database and then backuped from there. In that process the file is renamed to an Oracle managed format (ex. ora_df665478030_s5416_s1). So I don't know what the original file name was nor the sequence number. Can someone please let me know how to find that information? That way I can provide the proper file.

    Thank you for the help.

    Sincerely,
    Derek Knutsen
  • 8. Re: Problems with cloning database using image copies
    660550 Newbie
    Currently Being Moderated
    Navneet:

    This is how we do our backups - For the full backup:
    su - "$ORACLE_USER" <<EOO
    rman target / catalog rman/rman@localhost:1541/qcdb <<EOA 
    recover copy of database with tag 'PHY';
    exit
    EOA
    rman target / catalog rman/rman@localhost:1541/qcdb <<EOB 
    backup as compressed backupset incremental level 1 for recover of copy with tag 'PHY' database;
    exit
    EOB
    rman target / catalog rman/rman@localhost:1541/qcdb <<EOD 
    delete noprompt archivelog until time 'sysdate -2';
    exit
    EOD
    For the archive logs:
    su - "$ORACLE_USER" <<EOO
    rman target / catalog rman/rman@localhost:1541/qcdb <<EOC 
    backup archivelog like '+BACKUP%' delete input;
    exit
    EOC
  • 9. Re: Problems with cloning database using image copies
    639907 Oracle ACE Director
    Currently Being Moderated
    Hi,

    No problem, try to look for the archive log requested in this part of your file: ora_df665478030_<s5416>_s1, where s5416 should have the number requested.

    Let me know if this works ;)

    Cheers,

    Francisco Munoz Alvarez
    http://www.oraclenz.com
  • 10. Re: Problems with cloning database using image copies
    660550 Newbie
    Currently Being Moderated
    That's what I was afraid of :(. All the change numbers that we have are after the one that is requested. We made sure to take an incremental, apply it to the full, and then backup the archive logs. We brought all those down to the destination machine. Am I hosed or is there another way?

    Sincerely,
    Derek Knutsen
  • 11. Re: Problems with cloning database using image copies
    639907 Oracle ACE Director
    Currently Being Moderated
    Hi,

    The archives are not available in the source server? How about tape? How about a new backup?
    If not, try to clone it again, put your source DB in backup mode, copy the database to the destination server, start DB then change the name of the DB.

    Cheers,

    Francisco Munoz Alvarez
    http://www.oraclenz.com

    Edited by: F. Munoz Alvarez on Sep 20, 2008 10:36 AM
  • 12. Re: Problems with cloning database using image copies
    26741 Oracle ACE
    Currently Being Moderated
    Those archivelogs would be on tape. Can you restore them from tape ?
  • 13. Re: Problems with cloning database using image copies
    660550 Newbie
    Currently Being Moderated
    I talked with the Sr. DBA. I can get the missing archive logs from the ASM, which I plan on doing Monday. I will see where things go from there. I am also going down the duplication route.

    Thank you guys.

    Sincerely,
    Derek Knutsen
  • 14. Re: Problems with cloning database using image copies
    639907 Oracle ACE Director
    Currently Being Moderated
    You are Welcome Derek.

    Cheers,

    Francisco Munoz Alvarez
    http://www.oraclenz.com
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points