RMAN error

From: mek s <sidi.bouzid.meknessy_at_gmail.com>
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

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 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

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 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

RMAN>

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jul 13 2011 - 09:27:58 CDT

This message: [ Message body ]
Next message: Martin Bach: "Re: ASM or not to ASM"
Previous message: Dba DBA: "Re: Somewhat OT - interesting interview of Mike Stonebraker"
Next in thread: Allen, Brandon: "RE: RMAN error"
Reply: Allen, Brandon: "RE: RMAN error"
Reply: De DBA: "Re: RMAN error"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message