RMAN error
Date: Wed, 13 Jul 2011 16:27:58 +0200
Message-ID: <CAJX5RkRqNUNusAVdwOt9F9GW2pHC8f5Mp=+K_1+CjoWGMfA_aA_at_mail.gmail.com>
I am trying to test backup restore in this case, *why I am not able to restore my database to any point of time within the past 3 days of my recovery window?*
I am changing my laptop time settings to simulate a daily backup of 13, 14 , 15 and 16 of July 2011.
My backup Script is:
## Supports : 10g and 11g only
CONNECT TARGET /
CROSSCHECK BACKUP;
CROSSCHECK ARCHIVELOG ALL;
# Incrementally updated backup.
RECOVER COPY OF DATABASE WITH TAG 'LVL0_MERGE_INCR';
BACKUP CHECK LOGICAL INCREMENTAL LEVEL 1 CUMULATIVE COPIES=1 FOR
RECOVER OF COPY WITH TAG 'LVL0_MERGE_INCR' DATABASE;
# Switch current logfile to archive
sql 'ALTER SYSTEM ARCHIVE LOG CURRENT';
# Backup archivelogs
BACKUP CHECK LOGICAL AS COMPRESSED BACKUPSET FILESPERSET 10 ARCHIVELOG
ALL DELETE INPUT;
# delete obsolete backups
CROSSCHECK BACKUP;
DELETE NOPROMPT OBSOLETE;
DELETE NOPROMPT EXPIRED BACKUP;
exit;
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 3 DAYS;
old RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
*new* RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 3 DAYS;
*new* RMAN configuration parameters are successfully stored
RMAN> As of 13 of July 2011, I do:
SQL> conn DBA_USER
Enter password:
Connected.
SQL> create table test_backup (P_DATE DATE);
Table created.
SQL> insert into test_backup values (sysdate);
1 row created.
SQL> commit;
Commit complete.
SQL> SQL> alter session set nls_date_format='dd.mm.yyyy hh24:mi:ss';
Session altered.
SQL> select * from test_backup;
P_DATE
13.07.2011 12:00:38
SQL> Now, I run the BACKUP OF 13 of JULY. Backup successfully done.
now as 14 JULY 2011, I *do*:
Enter user-name: DBA_USER
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> insert into test_backup values (sysdate);
1 row created.
SQL> commit;
Commit complete.
SQL> alter session set nls_date_format='dd.mm.yyyy hh24:mi:ss';
Session altered.
SQL> select * from test_backup;
P_DATE
14.07.2011 12:01:02
13.07.2011 12:00:38
SQL> Now, I run the BACKUP OF 14 JULY. Backup successfully done.
As of 15 JULY 2011, I *do*:
SQL> insert into test_backup values (sysdate);
1 row created.
SQL> commit;
Commit complete.
SQL> alter session set nls_date_format='dd.mm.yyyy hh24:mi:ss';
Session altered.
SQL> select * from test_backup;
P_DATE
14.07.2011 12:01:02 13.07.2011 12:00:38 15.07.2011 12:00:30
SQL> Now, I run the BACKUP OF 15 JULY. Backup successfully done.
At 16 JULY 2011, I *do* :
SQL> insert into test_backup values (sysdate);
1 row created.
SQL> commit;
Commit complete.
SQL> alter session set nls_date_format='dd.mm.yyyy hh24:mi:ss';
Session altered.
SQL> select * from test_backup;
P_DATE
14.07.2011 12:01:02 13.07.2011 12:00:38 15.07.2011 12:00:30 16.07.2011 12:01:03
SQL> Now, I run the BACKUP OF 16 th of JULY. Backup successfully done.
NOW I simulate a FAILURE:
SQL> !mv /home/oracle/app/oracle/oradata_2/DB/datafile/o1_mf_undotbs2_711lopmy_.dbf
/home/oracle/app/oracle/oradata_2/DB/datafile/o1_mf_undotbs2_711lopmy_.dbf.OLD
SQL>
SQL> startup force;
ORACLE instance started.
Total System Global Area 3307048960 bytes
Fixed Size 2217872 bytes Variable Size 2113931376 bytes Database Buffers 1174405120 bytes Redo Buffers 16494592 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 11 - see DBWR trace file ORA-01110: data file 11:
'/home/oracle/app/oracle/oradata_2/DB/datafile/o1_mf_undotbs2_711lopmy_.dbf '
SQL> First Try : Restore the database from 2 days before backup.
[oracle_at_server logs]$ rlrman
Recovery Manager: Release 11.2.0.1.0 - Production on Sat Jul 16 12:11:39 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: DB (DBID=3175451436, not open)
run{
allocate channel dev1 type disk;
set until time "to_date('2011-14-07:12:01:02', 'yyyy-dd-mm:hh24:mi:ss')";
restore database;
recover database;
6> }
using target database control file instead of recovery catalog
allocated channel: dev1
channel dev1: SID=395 device type=DISK
executing command: SET until clause
Starting restore at 16-JUL-11
released channel: dev1
RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of restore command at 07/16/2011 12:11:45 RMAN-06026: some targets not found - aborting restore RMAN-06023: no backup or copy of datafile 5 found to restore RMAN-06023: no backup or copy of datafile 4 found to restoreRMAN-06023: no backup or copy of datafile 2 found to restore RMAN-06023: no backup or copy of datafile 1 found to restore
RMAN> Second Try 2: Restore control file from last backup and the datafiles from 2 days before backup.
RMAN> shutdown immediate;
using target database control file instead of recovery catalog
database dismounted
Oracle instance shut down
RMAN> SET DBID 3175451436 executing command: SET DBID
RMAN> startup nomount;
Oracle instance started
Total System Global Area 3307048960 bytes
Fixed Size 2217872 bytes Variable Size 2113931376 bytes Database Buffers 1174405120 bytes Redo Buffers 16494592 bytes
run{
allocate channel dev1 type disk;
restore controlfile from
'/home/oracle/backups/fra/DB/autobackup/2011_07_16/o1_mf_s_756648333_722rmfts_.bkp';
alter database mount;
set until time "to_date('2011-14-07:12:01:02', 'yyyy-dd-mm:hh24:mi:ss')";
restore database;
recover database;
}
allocated channel: dev1
channel dev1: SID=396 device type=DISK
Starting restore at 16-JUL-11
channel dev1: restoring control file
channel dev1: restore complete, elapsed time: 00:00:01
output file name=/home/oracle/app/oracle/oradata/DB/control01.ctl
output file name=/home/oracle/app/oracle/flash_recovery_area/DB/control02.ctl
Finished restore at 16-JUL-11
database mounted
executing command: SET until clause
Starting restore at 16-JUL-11
Starting implicit crosscheck backup at 16-JUL-11
Crosschecked 27 objects
Finished implicit crosscheck backup at 16-JUL-11
Starting implicit crosscheck copy at 16-JUL-11
Crosschecked 12 objects
Finished implicit crosscheck copy at 16-JUL-11
searching *for* all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
File Name: /home/oracle/backups/fra/DB/backupset/2011_06_29/o1_mf_annnn_TAG20110629T205228_70px4vpp_.bkp File Name: /home/oracle/backups/fra/DB/backupset/2011_06_29/o1_mf_nnnd1_LVL0_MERGE_INCR_70px2qs7_.bkp File Name: /home/oracle/backups/fra/DB/backupset/2011_06_29/o1_mf_annnn_TAG20110629T205228_70px3f5l_.bkp File Name: /home/oracle/backups/fra/DB/backupset/2011_06_29/o1_mf_nnnd1_LVL0_MERGE_INCR_70px2qjy_.bkp File Name: /home/oracle/backups/fra/DB/backupset/2011_06_29/o1_mf_annnn_TAG20110629T205228_70px3doz_.bkp File Name: /home/oracle/backups/fra/DB/backupset/2011_06_28/o1_mf_annnn_TAG20110628T203749_70n7wsjr_.bkp File Name: /home/oracle/backups/fra/DB/backupset/2011_06_28/o1_mf_nnnd1_LVL0_MERGE_INCR_70n7tpw3_.bkp File Name: /home/oracle/backups/fra/DB/backupset/2011_06_28/o1_mf_nnnd1_LVL0_MERGE_INCR_70n7tq2s_.bkp File Name: /home/oracle/backups/fra/DB/backupset/2011_06_28/o1_mf_annnn_TAG20110628T203749_70n7vyxq_.bkp File Name: /home/oracle/backups/fra/DB/backupset/2011_06_28/o1_mf_annnn_TAG20110628T203749_70n7wjkq_.bkp File Name: /home/oracle/backups/fra/DB/backupset/2011_06_28/o1_mf_annnn_TAG20110628T203749_70n7xmoo_.bkp File Name: /home/oracle/backups/fra/DB/backupset/2011_06_30/o1_mf_nnnd1_LVL0_MERGE_INCR_70skxsv7_.bkp File Name: /home/oracle/backups/fra/DB/backupset/2011_06_30/o1_mf_annnn_TAG20110630T210027_70skyd9t_.bkp File Name: /home/oracle/backups/fra/DB/backupset/2011_06_30/o1_mf_annnn_TAG20110630T210027_70skycyh_.bkp File Name: /home/oracle/backups/fra/DB/backupset/2011_06_30/o1_mf_nnnd1_LVL0_MERGE_INCR_70skxt9o_.bkp File Name: /home/oracle/backups/fra/DB/backupset/2011_07_16/o1_mf_nnnd1_LVL0_MERGE_INCR_722rmz15_.bkp File Name: /home/oracle/backups/fra/DB/backupset/2011_07_16/o1_mf_annnn_TAG20110716T120559_722rn84b_.bkp File Name: /home/oracle/backups/fra/DB/backupset/2011_07_16/o1_mf_nnnd1_LVL0_MERGE_INCR_722rmto8_.bkp File Name: /home/oracle/backups/fra/DB/backupset/2011_07_16/o1_mf_annnn_TAG20110716T120559_722rncx5_.bkp File Name: /home/oracle/backups/fra/DB/backupset/2011_07_16/o1_mf_annnn_TAG20110716T120559_722rn8fv_.bkp File Name: /home/oracle/backups/fra/DB/autobackup/2011_07_16/o1_mf_s_756648365_722rng59_.bkp File Name: /home/oracle/backups/fra/DB/autobackup/2011_07_16/o1_mf_s_756648333_722rmfts_.bkp File Name: /home/oracle/backups/fra/DB/autobackup/2011_07_16/o1_mf_s_756648353_722rn25d_.bkp
released channel: dev1
RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of restore command at 07/16/2011 12:18:04 RMAN-06026: some targets not found - aborting restore RMAN-06023: no backup or copy of datafile 5 found to restore RMAN-06023: no backup or copy of datafile 4 found to restoreRMAN-06023: no backup or copy of datafile 2 found to restore RMAN-06023: no backup or copy of datafile 1 found to restore
RMAN>
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Jul 13 2011 - 09:27:58 CDT