Hello, Talking about RMAN incremental updated backup.I am comparing between the use > of “until time” clause and not including this clause "until time" in my > backups. > Basically I am going to compare between 2 backup scripts: I just need to > test and understand the 2 scripts and decide which is the best! > Basically, we have to be able to recover the database to any time within > the past 31 days and we weekly move the backups to tape. > Please give your feedback if I am doing it wrong. My conclusion: we must > use UNTIL TIME clause if we want to recover the database to any point in > time within the recovery window. But, we have backup tapes! so what do you > think? > > 1- First Backup Script: > > CONNECT TARGET / > > CROSSCHECK BACKUP; > CROSSCHECK ARCHIVELOG ALL; > > # Incrementally updated backup. > RECOVER COPY OF DATABASE WITH TAG 'LVL0_MERGE_INCR' UNTIL TIME 'SYSDATE-7'; > 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; > > 2- Second backup script: > > 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; > > In my demonstration, I will play with the system date in my laptop; I will > modify the date, to simulate what will happen during backup work days. > > My database is 11gR2 on fedora Linux. > > RMAN settings are the same for both scenarios and are the following: > > RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON; > > using target database control file instead of recovery catalog > > old RMAN configuration parameters: > > CONFIGURE CONTROLFILE AUTOBACKUP ON; > > new RMAN configuration parameters: > > CONFIGURE CONTROLFILE AUTOBACKUP ON; > > new RMAN configuration parameters are successfully stored > > RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS; > > old RMAN configuration parameters: > > CONFIGURE RETENTION POLICY TO REDUNDANCY 3; > > new RMAN configuration parameters: > > CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS; > > new RMAN configuration parameters are successfully stored > > RMAN> > > Database parameters are the following for both scenarios: > > SQL> select log_mode from v$database; > > LOG_MODE > > ------------ > > ARCHIVELOG > > SQL> show parameter db_recovery > > NAME TYPE VALUE > > ------------------------------------ ----------- > ------------------------------ > > db_recovery_file_dest string /home/oracle/backups/fra > > db_recovery_file_dest_size big integer 4G > > SQL> > > 1- Scenario 1: > WITH UNTIL CLAUSE > RECOVER COPY OF DATABASE WITH TAG ‘LVL0_MERGE_INCR’ UNTIL TIME ‘SYSDATE-7′; > > [oracle@wissem ~]$ date > > Tue Jun 28 20:22:15 CEST 2011 > > [oracle@wissem ~]$ > > [oracle@wissem dbbackup_scripts]$ rlsqlplus > > SQL*Plus: Release 11.2.0.1.0 Production on Tue Jun 28 20:27:21 2011 > > Copyright (c) 1982, 2009, Oracle. All rights reserved. > > Enter user-name: sys as sysdba > > 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> create table wissem.test_backup(p_date date); > > Table created. > > SQL> insert into wissem.test_backup values (sysdate); > > 1 row created. > > SQL> commit; > > Commit complete. > > SQL> > > In the crontab, I have added this entry to automate the backup: > > 37 20 * * * /home/oracle/dbbackup_scripts/rmanincrupdbackup.sh ORAWISS > > /home/oracle/dbbackup_scripts/logs/cron_1 > > Below, the list of backups done with scenario 1: > > [oracle@wissem logs]$ ll /home/oracle/backups/fra/ORAWISS/backupset/ > > total 8 > > drwxr-xr-x 2 oracle oinstall 4096 Jun 28 20:39 2011_06_01 > > drwxr-x--- 2 oracle oinstall 4096 Jun 28 20:39 2011_06_28 > > [oracle@wissem logs]$ > [oracle@wissem logs]$ rlsqlplus > > SQL*Plus: Release 11.2.0.1.0 Production on Tue Jun 28 20:46:09 2011 > > Copyright (c) 1982, 2009, Oracle. All rights reserved. > > Enter user-name: sys as sysdba > > 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 wissem.test_backup values (sysdate); > > 1 row created. > > SQL> commit; > > Commit complete. > > SQL> > > The Backup of 28 June is done. > > Now, we change the system date. > > [oracle@wissem logs]$ date > > Wed Jun 29 20:36:14 CEST 2011 > > [oracle@wissem logs]$ > [oracle@wissem logs]$ rlsqlplus > > SQL*Plus: Release 11.2.0.1.0 Production on Wed Jun 29 20:37:54 2011 > > Copyright (c) 1982, 2009, Oracle. All rights reserved. > > Enter user-name: sys as sysdba > > 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> alter session set nls_date_format='dd.mm.yyyy hh24:mi:ss'; > > Session altered. > > SQL> select sysdate from dual; > > SYSDATE > > ------------------- > > 29.06.2011 20:38:26 > > SQL> exit > > The backup of 29 of June is done. > Now, we change the system date. > [oracle@wissem logs]$ date > > Thu Jun 30 20:55:02 CEST 2011 > > [oracle@wissem logs]$ rlsqlplus > > SQL*Plus: Release 11.2.0.1.0 Production on Thu Jun 30 20:55:20 2011 > > Copyright (c) 1982, 2009, Oracle. All rights reserved. > > Enter user-name: sys as sysdba > > 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> alter session set nls_date_format='dd.mm.yyyy hh24:mi:ss'; > > Session altered. > > SQL> select sysdate from dual; > > SYSDATE > > ------------------- > > 30.06.2011 20:55:29 > > SQL> insert into wissem.test_backup values (sysdate); > > 1 row created. > > SQL> commit; > > Commit complete. > > SQL> alter system switch logfile; > > System altered. > > SQL> / > > System altered. > > SQL> / > > System altered. > > SQL> > > => The backup of 30 of June is done. > > Now, we change the system date. > [oracle@wissem logs]$ date > > Fri Jul 1 21:01:37 CEST 2011 > > [oracle@wissem logs]$ > [oracle@wissem logs]$ ll /home/oracle/backups/fra/ORAWISS/backupset/ > > total 12 > > drwxr-x--- 2 oracle oinstall 4096 Jun 28 20:39 2011_06_28 > > drwxr-x--- 2 oracle oinstall 4096 Jun 29 20:53 2011_06_29 > > drwxr-x--- 2 oracle oinstall 4096 Jun 30 21:00 2011_06_30 > > [oracle@wissem logs]$ > > I move the backup directory 2011_06_28 to 2011_06_00. This is like deleting > the backup files from the Flash Recovery Area. > [oracle@wissem backupset]$ mv 2011_06_28/ 2011_06_00 > > [oracle@wissem backupset]$ ls -rtl > > total 12 > > drwxr-x--- 2 oracle oinstall 4096 Jun 28 20:39 2011_06_00 > > drwxr-x--- 2 oracle oinstall 4096 Jun 29 20:53 2011_06_29 > > drwxr-x--- 2 oracle oinstall 4096 Jun 30 21:00 2011_06_30 > > [oracle@wissem backupset]$ > > Now I will simulate a loss of the undo tablespace. Which is considered a > critical tablespace. > > SQL> select file_name from dba_data_files; > > FILE_NAME > > > -------------------------------------------------------------------------------- > > /home/oracle/app/oracle/oradata/ORAWISS/users01.dbf > > /home/oracle/app/oracle/oradata/ORAWISS/sysaux01.dbf > > /home/oracle/app/oracle/oradata/ORAWISS/system01.dbf > > /home/oracle/app/oracle/oradata/ORAWISS/example01.dbf > > /home/oracle/app/oracle/oradata/ORAWISS/mgmt_ecm_depot1.dbf > > /home/oracle/app/oracle/oradata/ORAWISS/mgmt.dbf > > /home/oracle/app/oracle/oradata/ORAWISS/mgmt_ad4j.dbf > > /home/oracle/app/oracle/oradata/ORAWISS/fb_data_archive.dbf > > > /home/oracle/app/oracle/oradata_2/ORAWISS/datafile/o1_mf_tsh_data_70c9obp9_.dbf > > > /home/oracle/app/oracle/oradata_2/ORAWISS/datafile/o1_mf_undotbs2_70cdsd5g_.dbf > > 10 rows selected. > > SQL> shutdown immediate; > > Database closed. > > Database dismounted. > > ORACLE instance shut down. > > SQL> !mv > /home/oracle/app/oracle/oradata_2/ORAWISS/datafile/o1_mf_undotbs2_70cdsd5g_.dbf > /home/oracle/app/oracle/oradata_2/ORAWISS/datafile/o1_mf_undotbs2_70cdsd5g_.dbf.OLD > > SQL> > > SQL> startup > > 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/ORAWISS/datafile/o1_mf_undotbs2_70cdsd5g_.dbf > > ' > > SQL> > > Now, I restore and recover the database using RMAN. > [oracle@wissem backupset]$ rlrman target / > > Recovery Manager: Release 11.2.0.1.0 - Production on Fri Jul 1 21:08:18 > 2011 > > Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights > reserved. > > connected to target database: ORAWISS (DBID=3175451436, not open) > > RMAN> restore database; > > Starting restore at 01-JUL-11 > > using target database control file instead of recovery catalog > > allocated channel: ORA_DISK_1 > > channel ORA_DISK_1: SID=395 device type=DISK > > allocated channel: ORA_DISK_2 > > channel ORA_DISK_2: SID=10 device type=DISK > > channel ORA_DISK_1: restoring datafile 00001 > > input datafile copy RECID=6 STAMP=755037181 file > name=/home/oracle/backups/fra/ORAWISS/datafile/o1_mf_system_70n7kjrd_.dbf > > destination for restore of datafile 00001: > /home/oracle/app/oracle/oradata/ORAWISS/system01.dbf > > channel ORA_DISK_2: restoring datafile 00002 > > input datafile copy RECID=5 STAMP=755037169 file > name=/home/oracle/backups/fra/ORAWISS/datafile/o1_mf_sysaux_70n7kk7l_.dbf > > destination for restore of datafile 00002: > /home/oracle/app/oracle/oradata/ORAWISS/sysaux01.dbf > > channel ORA_DISK_2: copied datafile copy of datafile 00002 > > output file name=/home/oracle/app/oracle/oradata/ORAWISS/sysaux01.dbf > RECID=0 STAMP=0 > > channel ORA_DISK_2: restoring datafile 00004 > > input datafile copy RECID=14 STAMP=755037220 file > name=/home/oracle/backups/fra/ORAWISS/datafile/o1_mf_users_70n7n3sd_.dbf > > destination for restore of datafile 00004: > /home/oracle/app/oracle/oradata/ORAWISS/users01.dbf > > channel ORA_DISK_1: copied datafile copy of datafile 00001 > > output file name=/home/oracle/app/oracle/oradata/ORAWISS/system01.dbf > RECID=0 STAMP=0 > > channel ORA_DISK_1: restoring datafile 00005 > > input datafile copy RECID=9 STAMP=755037207 file > name=/home/oracle/backups/fra/ORAWISS/datafile/o1_mf_example_70n7m8d9_.dbf > > destination for restore of datafile 00005: > /home/oracle/app/oracle/oradata/ORAWISS/example01.dbf > > channel ORA_DISK_2: copied datafile copy of datafile 00004 > > output file name=/home/oracle/app/oracle/oradata/ORAWISS/users01.dbf > RECID=0 STAMP=0 > > channel ORA_DISK_2: restoring datafile 00006 > > input datafile copy RECID=13 STAMP=755037217 file > name=/home/oracle/backups/fra/ORAWISS/datafile/o1_mf_mgmt_ecm_70n7n07h_.dbf > > destination for restore of datafile 00006: > /home/oracle/app/oracle/oradata/ORAWISS/mgmt_ecm_depot1.dbf > > channel ORA_DISK_2: copied datafile copy of datafile 00006 > > output file > name=/home/oracle/app/oracle/oradata/ORAWISS/mgmt_ecm_depot1.dbf RECID=0 > STAMP=0 > > channel ORA_DISK_2: restoring datafile 00007 > > input datafile copy RECID=7 STAMP=755037190 file > name=/home/oracle/backups/fra/ORAWISS/datafile/o1_mf_mgmt_tab_70n7lxrg_.dbf > > destination for restore of datafile 00007: > /home/oracle/app/oracle/oradata/ORAWISS/mgmt.dbf > > channel ORA_DISK_1: copied datafile copy of datafile 00005 > > output file name=/home/oracle/app/oracle/oradata/ORAWISS/example01.dbf > RECID=0 STAMP=0 > > channel ORA_DISK_1: restoring datafile 00008 > > input datafile copy RECID=8 STAMP=755037202 file > name=/home/oracle/backups/fra/ORAWISS/datafile/o1_mf_mgmt_ad4_70n7m8q4_.dbf > > destination for restore of datafile 00008: > /home/oracle/app/oracle/oradata/ORAWISS/mgmt_ad4j.dbf > > channel ORA_DISK_2: copied datafile copy of datafile 00007 > > output file name=/home/oracle/app/oracle/oradata/ORAWISS/mgmt.dbf RECID=0 > STAMP=0 > > channel ORA_DISK_2: restoring datafile 00009 > > input datafile copy RECID=12 STAMP=755037217 file > name=/home/oracle/backups/fra/ORAWISS/datafile/o1_mf_flash_da_70n7n0rr_.dbf > > destination for restore of datafile 00009: > /home/oracle/app/oracle/oradata/ORAWISS/fb_data_archive.dbf > > channel ORA_DISK_2: copied datafile copy of datafile 00009 > > output file > name=/home/oracle/app/oracle/oradata/ORAWISS/fb_data_archive.dbf RECID=0 > STAMP=0 > > channel ORA_DISK_2: restoring datafile 00010 > > input datafile copy RECID=10 STAMP=755037214 file > name=/home/oracle/backups/fra/ORAWISS/datafile/o1_mf_tsh_data_70n7mqz4_.dbf > > destination for restore of datafile 00010: > /home/oracle/app/oracle/oradata_2/ORAWISS/datafile/o1_mf_tsh_data_70c9obp9_.dbf > > channel ORA_DISK_1: copied datafile copy of datafile 00008 > > output file name=/home/oracle/app/oracle/oradata/ORAWISS/mgmt_ad4j.dbf > RECID=0 STAMP=0 > > channel ORA_DISK_1: restoring datafile 00011 > > input datafile copy RECID=11 STAMP=755037214 file > name=/home/oracle/backups/fra/ORAWISS/datafile/o1_mf_undotbs2_70n7mrs5_.dbf > > destination for restore of datafile 00011: > /home/oracle/app/oracle/oradata_2/ORAWISS/datafile/o1_mf_undotbs2_70cdsd5g_.dbf > > channel ORA_DISK_2: copied datafile copy of datafile 00010 > > output file > name=/home/oracle/app/oracle/oradata_2/ORAWISS/datafile/o1_mf_tsh_data_70c9obp9_.dbf > RECID=0 STAMP=0 > > channel ORA_DISK_1: copied datafile copy of datafile 00011 > > output file > name=/home/oracle/app/oracle/oradata_2/ORAWISS/datafile/o1_mf_undotbs2_70w6vms1_.dbf > RECID=0 STAMP=0 > > Finished restore at 01-JUL-11 > > RMAN> > > RMAN> recover database; > > Starting recover at 01-JUL-11 > > using channel ORA_DISK_1 > > using channel ORA_DISK_2 > > channel ORA_DISK_1: starting incremental datafile backup set restore > > channel ORA_DISK_1: specifying datafile(s) to restore from backup set > > destination for restore of datafile 00001: > /home/oracle/app/oracle/oradata/ORAWISS/system01.dbf > > destination for restore of datafile 00005: > /home/oracle/app/oracle/oradata/ORAWISS/example01.dbf > > destination for restore of datafile 00006: > /home/oracle/app/oracle/oradata/ORAWISS/mgmt_ecm_depot1.dbf > > destination for restore of datafile 00009: > /home/oracle/app/oracle/oradata/ORAWISS/fb_data_archive.dbf > > destination for restore of datafile 00010: > /home/oracle/app/oracle/oradata_2/ORAWISS/datafile/o1_mf_tsh_data_70c9obp9_.dbf > > channel ORA_DISK_1: reading from backup piece > /home/oracle/backups/fra/ORAWISS/backupset/2011_06_30/o1_mf_nnnd1_LVL0_MERGE_INCR_70skxt9o_.bkp > > channel ORA_DISK_2: starting incremental datafile backup set restore > > channel ORA_DISK_2: specifying datafile(s) to restore from backup set > > destination for restore of datafile 00002: > /home/oracle/app/oracle/oradata/ORAWISS/sysaux01.dbf > > destination for restore of datafile 00004: > /home/oracle/app/oracle/oradata/ORAWISS/users01.dbf > > destination for restore of datafile 00007: > /home/oracle/app/oracle/oradata/ORAWISS/mgmt.dbf > > destination for restore of datafile 00008: > /home/oracle/app/oracle/oradata/ORAWISS/mgmt_ad4j.dbf > > destination for restore of datafile 00011: > /home/oracle/app/oracle/oradata_2/ORAWISS/datafile/o1_mf_undotbs2_70w6vms1_.dbf > > channel ORA_DISK_2: reading from backup piece > /home/oracle/backups/fra/ORAWISS/backupset/2011_06_30/o1_mf_nnnd1_LVL0_MERGE_INCR_70skxsv7_.bkp > > channel ORA_DISK_1: piece > handle=/home/oracle/backups/fra/ORAWISS/backupset/2011_06_30/o1_mf_nnnd1_LVL0_MERGE_INCR_70skxt9o_.bkp > tag=LVL0_MERGE_INCR > > channel ORA_DISK_1: restored backup piece 1 > > channel ORA_DISK_1: restore complete, elapsed time: 00:00:03 > > channel ORA_DISK_2: piece > handle=/home/oracle/backups/fra/ORAWISS/backupset/2011_06_30/o1_mf_nnnd1_LVL0_MERGE_INCR_70skxsv7_.bkp > tag=LVL0_MERGE_INCR > > channel ORA_DISK_2: restored backup piece 1 > > channel ORA_DISK_2: restore complete, elapsed time: 00:00:07 > > starting media recovery > > media recovery complete, elapsed time: 00:00:05 > > Finished recover at 01-JUL-11 > > RMAN> > > From the outputs above, you can see that the recover was done based on the > 2011_06_30 last backup copy. > > Let’s confirm it. I will delete the 2011_06_28 and 2011_06_29 backup copies > to see if they are needed for recovery. > > [oracle@wissem backupset]$ ls -rtl > > total 12 > > drwxr-x--- 2 oracle oinstall 4096 Jun 28 20:39 2011_06_00 > > drwxr-x--- 2 oracle oinstall 4096 Jun 29 20:53 2011_06_29 > > drwxr-x--- 2 oracle oinstall 4096 Jun 30 21:00 2011_06_30 > > [oracle@wissem backupset]$ mv 2011_06_29 2011_06_01 > > [oracle@wissem backupset]$ ls -rtl > > total 12 > > drwxr-x--- 2 oracle oinstall 4096 Jun 28 20:39 2011_06_00 > > drwxr-x--- 2 oracle oinstall 4096 Jun 29 20:53 2011_06_01 > > drwxr-x--- 2 oracle oinstall 4096 Jun 30 21:00 2011_06_30 > > [oracle@wissem backupset]$ > > Again, We simulate a data loss of the undo tablespace. > [oracle@wissem backupset]$ rlsqlplus > > SQL*Plus: Release 11.2.0.1.0 Production on Fri Jul 1 21:13:47 2011 > > Copyright (c) 1982, 2009, Oracle. All rights reserved. > > Enter user-name: sys as sysdba > > 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> select file_name from dba_data_files; > > select file_name from dba_data_files > > * > > ERROR at line 1: > > ORA-01219: database not open: queries allowed on fixed tables/views only > > SQL> alter database open; > > Database altered. > > SQL> select file_name from dba_data_files; > > FILE_NAME > > > -------------------------------------------------------------------------------- > > /home/oracle/app/oracle/oradata/ORAWISS/users01.dbf > > /home/oracle/app/oracle/oradata/ORAWISS/sysaux01.dbf > > /home/oracle/app/oracle/oradata/ORAWISS/system01.dbf > > /home/oracle/app/oracle/oradata/ORAWISS/example01.dbf > > /home/oracle/app/oracle/oradata/ORAWISS/mgmt_ecm_depot1.dbf > > /home/oracle/app/oracle/oradata/ORAWISS/mgmt.dbf > > /home/oracle/app/oracle/oradata/ORAWISS/mgmt_ad4j.dbf > > /home/oracle/app/oracle/oradata/ORAWISS/fb_data_archive.dbf > > > /home/oracle/app/oracle/oradata_2/ORAWISS/datafile/o1_mf_tsh_data_70c9obp9_.dbf > > > /home/oracle/app/oracle/oradata_2/ORAWISS/datafile/o1_mf_undotbs2_70w6vms1_.dbf > > 10 rows selected. > > SQL> !mv > /home/oracle/app/oracle/oradata_2/ORAWISS/datafile/o1_mf_undotbs2_70w6vms1_.dbf > /home/oracle/app/oracle/oradata_2/ORAWISS/datafile/o1_mf_undotbs2_70w6vms1_.dbf.OLD > > SQL> select 1 from dual; > > 1 > > ---------- > > 1 > > 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/ORAWISS/datafile/o1_mf_undotbs2_70w6vms1_.dbf > > ' > > SQL> > > Now, we restore, and recover the database using RMAN. > > [oracle@wissem backupset]$ rlrman target / > > Recovery Manager: Release 11.2.0.1.0 - Production on Fri Jul 1 21:15:57 > 2011 > > Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights > reserved. > > connected to target database: ORAWISS (DBID=3175451436, not open) > > RMAN> restore database; > > Starting restore at 01-JUL-11 > > using target database control file instead of recovery catalog > > allocated channel: ORA_DISK_1 > > channel ORA_DISK_1: SID=395 device type=DISK > > allocated channel: ORA_DISK_2 > > channel ORA_DISK_2: SID=10 device type=DISK > > channel ORA_DISK_1: restoring datafile 00001 > > input datafile copy RECID=6 STAMP=755037181 file > name=/home/oracle/backups/fra/ORAWISS/datafile/o1_mf_system_70n7kjrd_.dbf > > destination for restore of datafile 00001: > /home/oracle/app/oracle/oradata/ORAWISS/system01.dbf > > channel ORA_DISK_2: restoring datafile 00002 > > input datafile copy RECID=5 STAMP=755037169 file > name=/home/oracle/backups/fra/ORAWISS/datafile/o1_mf_sysaux_70n7kk7l_.dbf > > destination for restore of datafile 00002: > /home/oracle/app/oracle/oradata/ORAWISS/sysaux01.dbf > > channel ORA_DISK_1: copied datafile copy of datafile 00001 > > output file name=/home/oracle/app/oracle/oradata/ORAWISS/system01.dbf > RECID=0 STAMP=0 > > channel ORA_DISK_1: restoring datafile 00004 > > input datafile copy RECID=14 STAMP=755037220 file > name=/home/oracle/backups/fra/ORAWISS/datafile/o1_mf_users_70n7n3sd_.dbf > > destination for restore of datafile 00004: > /home/oracle/app/oracle/oradata/ORAWISS/users01.dbf > > channel ORA_DISK_2: copied datafile copy of datafile 00002 > > output file name=/home/oracle/app/oracle/oradata/ORAWISS/sysaux01.dbf > RECID=0 STAMP=0 > > channel ORA_DISK_2: restoring datafile 00005 > > input datafile copy RECID=9 STAMP=755037207 file > name=/home/oracle/backups/fra/ORAWISS/datafile/o1_mf_example_70n7m8d9_.dbf > > destination for restore of datafile 00005: > /home/oracle/app/oracle/oradata/ORAWISS/example01.dbf > > channel ORA_DISK_1: copied datafile copy of datafile 00004 > > output file name=/home/oracle/app/oracle/oradata/ORAWISS/users01.dbf > RECID=0 STAMP=0 > > channel ORA_DISK_1: restoring datafile 00006 > > input datafile copy RECID=13 STAMP=755037217 file > name=/home/oracle/backups/fra/ORAWISS/datafile/o1_mf_mgmt_ecm_70n7n07h_.dbf > > destination for restore of datafile 00006: > /home/oracle/app/oracle/oradata/ORAWISS/mgmt_ecm_depot1.dbf > > channel ORA_DISK_1: copied datafile copy of datafile 00006 > > output file > name=/home/oracle/app/oracle/oradata/ORAWISS/mgmt_ecm_depot1.dbf RECID=0 > STAMP=0 > > channel ORA_DISK_1: restoring datafile 00007 > > input datafile copy RECID=7 STAMP=755037190 file > name=/home/oracle/backups/fra/ORAWISS/datafile/o1_mf_mgmt_tab_70n7lxrg_.dbf > > destination for restore of datafile 00007: > /home/oracle/app/oracle/oradata/ORAWISS/mgmt.dbf > > channel ORA_DISK_2: copied datafile copy of datafile 00005 > > output file name=/home/oracle/app/oracle/oradata/ORAWISS/example01.dbf > RECID=0 STAMP=0 > > channel ORA_DISK_2: restoring datafile 00008 > > input datafile copy RECID=8 STAMP=755037202 file > name=/home/oracle/backups/fra/ORAWISS/datafile/o1_mf_mgmt_ad4_70n7m8q4_.dbf > > destination for restore of datafile 00008: > /home/oracle/app/oracle/oradata/ORAWISS/mgmt_ad4j.dbf > > channel ORA_DISK_1: copied datafile copy of datafile 00007 > > output file name=/home/oracle/app/oracle/oradata/ORAWISS/mgmt.dbf RECID=0 > STAMP=0 > > channel ORA_DISK_1: restoring datafile 00009 > > input datafile copy RECID=12 STAMP=755037217 file > name=/home/oracle/backups/fra/ORAWISS/datafile/o1_mf_flash_da_70n7n0rr_.dbf > > destination for restore of datafile 00009: > /home/oracle/app/oracle/oradata/ORAWISS/fb_data_archive.dbf > > channel ORA_DISK_2: copied datafile copy of datafile 00008 > > output file name=/home/oracle/app/oracle/oradata/ORAWISS/mgmt_ad4j.dbf > RECID=0 STAMP=0 > > channel ORA_DISK_2: restoring datafile 00010 > > input datafile copy RECID=10 STAMP=755037214 file > name=/home/oracle/backups/fra/ORAWISS/datafile/o1_mf_tsh_data_70n7mqz4_.dbf > > destination for restore of datafile 00010: > /home/oracle/app/oracle/oradata_2/ORAWISS/datafile/o1_mf_tsh_data_70c9obp9_.dbf > > channel ORA_DISK_1: copied datafile copy of datafile 00009 > > output file > name=/home/oracle/app/oracle/oradata/ORAWISS/fb_data_archive.dbf RECID=0 > STAMP=0 > > channel ORA_DISK_1: restoring datafile 00011 > > input datafile copy RECID=11 STAMP=755037214 file > name=/home/oracle/backups/fra/ORAWISS/datafile/o1_mf_undotbs2_70n7mrs5_.dbf > > destination for restore of datafile 00011: > /home/oracle/app/oracle/oradata_2/ORAWISS/datafile/o1_mf_undotbs2_70w6vms1_.dbf > > channel ORA_DISK_2: copied datafile copy of datafile 00010 > > output file > name=/home/oracle/app/oracle/oradata_2/ORAWISS/datafile/o1_mf_tsh_data_70c9obp9_.dbf > RECID=0 STAMP=0 > > channel ORA_DISK_1: copied datafile copy of datafile 00011 > > output file > name=/home/oracle/app/oracle/oradata_2/ORAWISS/datafile/o1_mf_undotbs2_70w79rlf_.dbf > RECID=0 STAMP=0 > > Finished restore at 01-JUL-11 > > RMAN> recover database; > > Starting recover at 01-JUL-11 > > using channel ORA_DISK_1 > > using channel ORA_DISK_2 > > channel ORA_DISK_1: starting incremental datafile backup set restore > > channel ORA_DISK_1: specifying datafile(s) to restore from backup set > > destination for restore of datafile 00001: > /home/oracle/app/oracle/oradata/ORAWISS/system01.dbf > > destination for restore of datafile 00005: > /home/oracle/app/oracle/oradata/ORAWISS/example01.dbf > > destination for restore of datafile 00006: > /home/oracle/app/oracle/oradata/ORAWISS/mgmt_ecm_depot1.dbf > > destination for restore of datafile 00009: > /home/oracle/app/oracle/oradata/ORAWISS/fb_data_archive.dbf > > destination for restore of datafile 00010: > /home/oracle/app/oracle/oradata_2/ORAWISS/datafile/o1_mf_tsh_data_70c9obp9_.dbf > > channel ORA_DISK_1: reading from backup piece > /home/oracle/backups/fra/ORAWISS/backupset/2011_06_30/o1_mf_nnnd1_LVL0_MERGE_INCR_70skxt9o_.bkp > > channel ORA_DISK_2: starting incremental datafile backup set restore > > channel ORA_DISK_2: specifying datafile(s) to restore from backup set > > destination for restore of datafile 00002: > /home/oracle/app/oracle/oradata/ORAWISS/sysaux01.dbf > > destination for restore of datafile 00004: > /home/oracle/app/oracle/oradata/ORAWISS/users01.dbf > > destination for restore of datafile 00007: > /home/oracle/app/oracle/oradata/ORAWISS/mgmt.dbf > > destination for restore of datafile 00008: > /home/oracle/app/oracle/oradata/ORAWISS/mgmt_ad4j.dbf > > destination for restore of datafile 00011: > /home/oracle/app/oracle/oradata_2/ORAWISS/datafile/o1_mf_undotbs2_70w79rlf_.dbf > > channel ORA_DISK_2: reading from backup piece > /home/oracle/backups/fra/ORAWISS/backupset/2011_06_30/o1_mf_nnnd1_LVL0_MERGE_INCR_70skxsv7_.bkp > > channel ORA_DISK_1: piece > handle=/home/oracle/backups/fra/ORAWISS/backupset/2011_06_30/o1_mf_nnnd1_LVL0_MERGE_INCR_70skxt9o_.bkp > tag=LVL0_MERGE_INCR > > channel ORA_DISK_1: restored backup piece 1 > > channel ORA_DISK_1: restore complete, elapsed time: 00:00:03 > > channel ORA_DISK_2: piece > handle=/home/oracle/backups/fra/ORAWISS/backupset/2011_06_30/o1_mf_nnnd1_LVL0_MERGE_INCR_70skxsv7_.bkp > tag=LVL0_MERGE_INCR > > channel ORA_DISK_2: restored backup piece 1 > > channel ORA_DISK_2: restore complete, elapsed time: 00:00:07 > > starting media recovery > > media recovery complete, elapsed time: 00:00:06 > > Finished recover at 01-JUL-11 > > RMAN> > > So from the output above, only the latest backup copy is needed for restore > and recovery. > [oracle@wissem backupset]$ rlsqlplus > > SQL*Plus: Release 11.2.0.1.0 Production on Fri Jul 1 21:18:14 2011 > > Copyright (c) 1982, 2009, Oracle. All rights reserved. > > Enter user-name: sys as sysdba > > 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> alter database open; > > Database altered. > > SQL> alter session set nls_date_format='dd.mm.yyyy hh24:mi:ss'; > > Session altered. > > SQL> select *from wissem.test_backup; > > P_DATE > > ------------------- > > 28.06.2011 20:28:06 > > 30.06.2011 20:55:36 > > 28.06.2011 20:46:17 > > SQL> > > Now. let’s try to recover the database to a time in the past within the 7 > days of recovery window. > Let’s restore the backup directories to their original directory names. > [oracle@wissem backupset]$ mv 2011_06_01 2011_06_29 > > [oracle@wissem backupset]$ mv 2011_06_00/ 2011_06_28 > > [oracle@wissem backupset]$ ls -rtl > > total 12 > > drwxr-x--- 2 oracle oinstall 4096 Jun 28 20:39 2011_06_28 > > drwxr-x--- 2 oracle oinstall 4096 Jun 29 20:53 2011_06_29 > > drwxr-x--- 2 oracle oinstall 4096 Jun 30 21:00 2011_06_30 > > [oracle@wissem backupset]$ > > Now, using RMAN we will try to recover the database to a time in the past > within the 7 days of recovery window. > > RMAN> run{ > > 2> allocate channel dev1 type disk; > > 3> set until time "to_date('2011-28-06:20:55:36', > 'yyyy-dd-mm:hh24:mi:ss')"; > > 4> restore database; > > 5> recover database; > > 6> } > > allocated channel: dev1 > > channel dev1: SID=395 device type=DISK > > executing command: SET until clause > > Starting restore at 01-JUL-11 > > channel dev1: restoring datafile 00001 > > input datafile copy RECID=6 STAMP=755037181 file > name=/home/oracle/backups/fra/ORAWISS/datafile/o1_mf_system_70n7kjrd_.dbf > > destination for restore of datafile 00001: > /home/oracle/app/oracle/oradata/ORAWISS/system01.dbf > > channel dev1: copied datafile copy of datafile 00001 > > output file name=/home/oracle/app/oracle/oradata/ORAWISS/system01.dbf > RECID=0 STAMP=0 > > channel dev1: restoring datafile 00002 > > input datafile copy RECID=5 STAMP=755037169 file > name=/home/oracle/backups/fra/ORAWISS/datafile/o1_mf_sysaux_70n7kk7l_.dbf > > destination for restore of datafile 00002: > /home/oracle/app/oracle/oradata/ORAWISS/sysaux01.dbf > > channel dev1: copied datafile copy of datafile 00002 > > output file name=/home/oracle/app/oracle/oradata/ORAWISS/sysaux01.dbf > RECID=0 STAMP=0 > > channel dev1: restoring datafile 00004 > > input datafile copy RECID=14 STAMP=755037220 file > name=/home/oracle/backups/fra/ORAWISS/datafile/o1_mf_users_70n7n3sd_.dbf > > destination for restore of datafile 00004: > /home/oracle/app/oracle/oradata/ORAWISS/users01.dbf > > channel dev1: copied datafile copy of datafile 00004 > > output file name=/home/oracle/app/oracle/oradata/ORAWISS/users01.dbf > RECID=0 STAMP=0 > > channel dev1: restoring datafile 00005 > > input datafile copy RECID=9 STAMP=755037207 file > name=/home/oracle/backups/fra/ORAWISS/datafile/o1_mf_example_70n7m8d9_.dbf > > destination for restore of datafile 00005: > /home/oracle/app/oracle/oradata/ORAWISS/example01.dbf > > channel dev1: copied datafile copy of datafile 00005 > > output file name=/home/oracle/app/oracle/oradata/ORAWISS/example01.dbf > RECID=0 STAMP=0 > > channel dev1: restoring datafile 00006 > > input datafile copy RECID=13 STAMP=755037217 file > name=/home/oracle/backups/fra/ORAWISS/datafile/o1_mf_mgmt_ecm_70n7n07h_.dbf > > destination for restore of datafile 00006: > /home/oracle/app/oracle/oradata/ORAWISS/mgmt_ecm_depot1.dbf > > channel dev1: copied datafile copy of datafile 00006 > > output file > name=/home/oracle/app/oracle/oradata/ORAWISS/mgmt_ecm_depot1.dbf RECID=0 > STAMP=0 > > channel dev1: restoring datafile 00007 > > input datafile copy RECID=7 STAMP=755037190 file > name=/home/oracle/backups/fra/ORAWISS/datafile/o1_mf_mgmt_tab_70n7lxrg_.dbf > > destination for restore of datafile 00007: > /home/oracle/app/oracle/oradata/ORAWISS/mgmt.dbf > > channel dev1: copied datafile copy of datafile 00007 > > output file name=/home/oracle/app/oracle/oradata/ORAWISS/mgmt.dbf RECID=0 > STAMP=0 > > channel dev1: restoring datafile 00008 > > input datafile copy RECID=8 STAMP=755037202 file > name=/home/oracle/backups/fra/ORAWISS/datafile/o1_mf_mgmt_ad4_70n7m8q4_.dbf > > destination for restore of datafile 00008: > /home/oracle/app/oracle/oradata/ORAWISS/mgmt_ad4j.dbf > > channel dev1: copied datafile copy of datafile 00008 > > output file name=/home/oracle/app/oracle/oradata/ORAWISS/mgmt_ad4j.dbf > RECID=0 STAMP=0 > > channel dev1: restoring datafile 00009 > > input datafile copy RECID=12 STAMP=755037217 file > name=/home/oracle/backups/fra/ORAWISS/datafile/o1_mf_flash_da_70n7n0rr_.dbf > > destination for restore of datafile 00009: > /home/oracle/app/oracle/oradata/ORAWISS/fb_data_archive.dbf > > channel dev1: copied datafile copy of datafile 00009 > > output file > name=/home/oracle/app/oracle/oradata/ORAWISS/fb_data_archive.dbf RECID=0 > STAMP=0 > > channel dev1: restoring datafile 00010 > > input datafile copy RECID=10 STAMP=755037214 file > name=/home/oracle/backups/fra/ORAWISS/datafile/o1_mf_tsh_data_70n7mqz4_.dbf > > destination for restore of datafile 00010: > /home/oracle/app/oracle/oradata_2/ORAWISS/datafile/o1_mf_tsh_data_70c9obp9_.dbf > > channel dev1: copied datafile copy of datafile 00010 > > output file > name=/home/oracle/app/oracle/oradata_2/ORAWISS/datafile/o1_mf_tsh_data_70c9obp9_.dbf > RECID=0 STAMP=0 > > channel dev1: restoring datafile 00011 > > input datafile copy RECID=11 STAMP=755037214 file > name=/home/oracle/backups/fra/ORAWISS/datafile/o1_mf_undotbs2_70n7mrs5_.dbf > > destination for restore of datafile 00011: > /home/oracle/app/oracle/oradata_2/ORAWISS/datafile/o1_mf_undotbs2_70w79rlf_.dbf > > channel dev1: copied datafile copy of datafile 00011 > > output file > name=/home/oracle/app/oracle/oradata_2/ORAWISS/datafile/o1_mf_undotbs2_70w79rlf_.dbf > RECID=0 STAMP=0 > > Finished restore at 01-JUL-11 > > Starting recover at 01-JUL-11 > > channel dev1: starting incremental datafile backup set restore > > channel dev1: specifying datafile(s) to restore from backup set > > destination for restore of datafile 00002: > /home/oracle/app/oracle/oradata/ORAWISS/sysaux01.dbf > > destination for restore of datafile 00004: > /home/oracle/app/oracle/oradata/ORAWISS/users01.dbf > > destination for restore of datafile 00007: > /home/oracle/app/oracle/oradata/ORAWISS/mgmt.dbf > > destination for restore of datafile 00008: > /home/oracle/app/oracle/oradata/ORAWISS/mgmt_ad4j.dbf > > destination for restore of datafile 00011: > /home/oracle/app/oracle/oradata_2/ORAWISS/datafile/o1_mf_undotbs2_70w79rlf_.dbf > > channel dev1: reading from backup piece > /home/oracle/backups/fra/ORAWISS/backupset/2011_06_28/o1_mf_nnnd1_LVL0_MERGE_INCR_70n7tq2s_.bkp > > channel dev1: piece > handle=/home/oracle/backups/fra/ORAWISS/backupset/2011_06_28/o1_mf_nnnd1_LVL0_MERGE_INCR_70n7tq2s_.bkp > tag=LVL0_MERGE_INCR > > channel dev1: restored backup piece 1 > > channel dev1: restore complete, elapsed time: 00:00:03 > > channel dev1: starting incremental datafile backup set restore > > channel dev1: specifying datafile(s) to restore from backup set > > destination for restore of datafile 00001: > /home/oracle/app/oracle/oradata/ORAWISS/system01.dbf > > destination for restore of datafile 00005: > /home/oracle/app/oracle/oradata/ORAWISS/example01.dbf > > destination for restore of datafile 00006: > /home/oracle/app/oracle/oradata/ORAWISS/mgmt_ecm_depot1.dbf > > destination for restore of datafile 00009: > /home/oracle/app/oracle/oradata/ORAWISS/fb_data_archive.dbf > > destination for restore of datafile 00010: > /home/oracle/app/oracle/oradata_2/ORAWISS/datafile/o1_mf_tsh_data_70c9obp9_.dbf > > channel dev1: reading from backup piece > /home/oracle/backups/fra/ORAWISS/backupset/2011_06_28/o1_mf_nnnd1_LVL0_MERGE_INCR_70n7tpw3_.bkp > > channel dev1: piece > handle=/home/oracle/backups/fra/ORAWISS/backupset/2011_06_28/o1_mf_nnnd1_LVL0_MERGE_INCR_70n7tpw3_.bkp > tag=LVL0_MERGE_INCR > > channel dev1: restored backup piece 1 > > channel dev1: restore complete, elapsed time: 00:00:01 > > starting media recovery > > channel dev1: starting archived log restore to default destination > > channel dev1: restoring archived log > > archived log thread=1 sequence=205 > > channel dev1: restoring archived log > > archived log thread=1 sequence=206 > > channel dev1: reading from backup piece > /home/oracle/backups/fra/ORAWISS/backupset/2011_06_29/o1_mf_annnn_TAG20110629T205228_70px4vpp_.bkp > > channel dev1: piece > handle=/home/oracle/backups/fra/ORAWISS/backupset/2011_06_29/o1_mf_annnn_TAG20110629T205228_70px4vpp_.bkp > tag=TAG20110629T205228 > > channel dev1: restored backup piece 1 > > channel dev1: restore complete, elapsed time: 00:00:15 > > archived log file > name=/home/oracle/archivelog/orawiss_1/orawiss_205_1_741269760_bd458b2c.arc > thread=1 sequence=205 > > archived log file > name=/home/oracle/archivelog/orawiss_1/orawiss_206_1_741269760_bd458b2c.arc > thread=1 sequence=206 > > channel dev1: starting archived log restore to default destination > > channel dev1: restoring archived log > > archived log thread=1 sequence=207 > > channel dev1: reading from backup piece > /home/oracle/backups/fra/ORAWISS/backupset/2011_06_30/o1_mf_annnn_TAG20110630T210027_70skycyh_.bkp > > channel dev1: piece > handle=/home/oracle/backups/fra/ORAWISS/backupset/2011_06_30/o1_mf_annnn_TAG20110630T210027_70skycyh_.bkp > tag=TAG20110630T210027 > > channel dev1: restored backup piece 1 > > channel dev1: restore complete, elapsed time: 00:00:02 > > archived log file > name=/home/oracle/archivelog/orawiss_1/orawiss_207_1_741269760_bd458b2c.arc > thread=1 sequence=207 > > media recovery complete, elapsed time: 00:00:01 > > Finished recover at 01-JUL-11 > > released channel: dev1 > > RMAN> > > Now, open the database with open resetlogs; > > SQL> alter database open resetlogs; > > Database altered. > > SQL> alter session set nls_date_format='dd.mm.yyyy hh24:mi:ss'; > > Session altered. > > SQL> select *from wissem.test_backup; > > P_DATE > > ------------------- > > 28.06.2011 20:28:06 > > 28.06.2011 20:46:17 > > SQL> > > From the output above, you can see we can recover the database to any point > in time within the 7 days of recovery window. > > 2- Scenario 2: > > Without the until time clause > > RECOVER COPY OF DATABASE WITH TAG ‘LVL0_MERGE_INCR’; > > SQL> create table wissem.testbackup_2 (p_date date); > > Table created. > > SQL> insert into wissem.testbackup_2 values (sysdate); > > 1 row created. > > SQL> commit; > > Commit complete. > > SQL> select sysdate from dual; > > SYSDATE > > ------------------- > > 01.07.2011 21:34:48 > > SQL> > > Now, I remove all the backups done from the scenario 1! > > [oracle@wissem backupset]$ ls -rtl > > total 12 > > drwxr-x--- 2 oracle oinstall 4096 Jun 28 20:39 2011_06_28 > > drwxr-x--- 2 oracle oinstall 4096 Jun 29 20:53 2011_06_29 > > drwxr-x--- 2 oracle oinstall 4096 Jun 30 21:00 2011_06_30 > > [oracle@wissem backupset]$ mv 2011_06_28/ 2011_06_00 > > [oracle@wissem backupset]$ mv 2011_06_29/ 2011_06_01 > > [oracle@wissem backupset]$ mv 2011_06_30/ 2011_06_02 > > [oracle@wissem backupset]$ > > I modify the backup script and modify the crontab job. > > 40 21 * * * /home/oracle/dbbackup_scripts/rmanincrupdbackup.sh ORAWISS > > /home/oracle/dbbackup_scripts/logs/cron_1 > > [oracle@wissem logs]$ date > > Fri Jul 1 21:40:05 CEST 2011 > > [oracle@wissem logs]$ > > => The backup of 1st of July is done and thus without including the Until > time clause. > > Change the system time setting. > > [oracle@wissem logs]$ date > > Sat Jul 2 21:42:18 CEST 2011 > > [oracle@wissem logs]$ > > [oracle@wissem logs]$ rlsqlplus > > SQL*Plus: Release 11.2.0.1.0 Production on Sat Jul 2 21:42:36 2011 > > Copyright (c) 1982, 2009, Oracle. All rights reserved. > > Enter user-name: sys as sysdba > > 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> alter session set nls_date_format='dd.mm.yyyy hh24:mi:ss'; > > Session altered. > > SQL> insert into wissem.testbackup_2 values (sysdate); > > 1 row created. > > SQL> commit; > > Commit complete. > > SQL> select sysdate from dual; > > SYSDATE > > ------------------- > > 02.07.2011 21:42:58 > > SQL> > [oracle@wissem logs]$ ls -trl /home/oracle/backups/fra/ORAWISS/backupset/ > > total 16 > > drwxr-x--- 2 oracle oinstall 4096 Jun 28 20:39 2011_06_00 > > drwxr-x--- 2 oracle oinstall 4096 Jun 29 20:53 2011_06_01 > > drwxr-x--- 2 oracle oinstall 4096 Jun 30 21:00 2011_06_02 > > drwxr-x--- 2 oracle oinstall 4096 Jul 1 21:40 2011_07_01 > > [oracle@wissem logs]$ > > => The backup of 2nd of July is done and this without including the until > time clause. > > SQL> alter session set nls_date_format='dd.mm.yyyy hh24:mi:ss'; > > Session altered. > > SQL> insert into wissem.testbackup_2 values (sysdate); > > 1 row created. > > SQL> commit; > > Commit complete. > > SQL> select sysdate from dual; > > SYSDATE > > ------------------- > > 03.07.2011 21:52:18 > > Now, we change the time setting, by adding one day in the clock. > > [oracle@wissem logs]$ date > > Sun Jul 3 21:52:40 CEST 2011 > > [oracle@wissem logs]$ > > [oracle@wissem logs]$ ls -trl /home/oracle/backups/fra/ORAWISS/backupset/ > > total 20 > > drwxr-x--- 2 oracle oinstall 4096 Jun 28 20:39 2011_06_00 > > drwxr-x--- 2 oracle oinstall 4096 Jun 29 20:53 2011_06_01 > > drwxr-x--- 2 oracle oinstall 4096 Jun 30 21:00 2011_06_02 > > drwxr-x--- 2 oracle oinstall 4096 Jul 1 21:40 2011_07_01 > > drwxr-x--- 2 oracle oinstall 4096 Jul 2 21:50 2011_07_02 > > [oracle@wissem logs]$ > > => The backup of 3rd of July is done, and thus without the until time > clause. > > SQL> alter session set nls_date_format='dd.mm.yyyy hh24:mi:ss'; > > Session altered. > > SQL> insert into wissem.testbackup_2 values (sysdate); > > 1 row created. > > SQL> commit; > > Commit complete. > > SQL> select sysdate from dual; > > SYSDATE > > ------------------- > > 03.07.2011 21:57:23 > > SQL> > > Now let’s simulate a data loss. I delete the undo tablespace, like I did in > the scenario 1. > > SQL> !mv > /home/oracle/app/oracle/oradata_2/ORAWISS/datafile/o1_mf_undotbs2_70w79rlf_.dbf > /home/oracle/app/oracle/oradata_2/ORAWISS/datafile/o1_mf_undotbs2_70w79rlf_.dbf.OLD > > SQL> shutdown immediate; > > ORA-01116: error in opening database file 11 > > ORA-01110: data file 11: > '/home/oracle/app/oracle/oradata_2/ORAWISS/datafile/o1_mf_undotbs2_70w79rlf_.dbf' > > ORA-27041: unable to open file > > Linux-x86_64 Error: 2: No such file or directory > > Additional information: 3 > > SQL> startup > > ORA-01081: cannot start already-running ORACLE - shut it down first > > 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/ORAWISS/datafile/o1_mf_undotbs2_70w79rlf_.dbf > > ' > > SQL> > > Now, let’s move the backup copies done in 01st and 2nd of July and keep > only the latest backup copy done 3rd of July. > [oracle@wissem backupset]$ ls -rtl > > total 24 > > drwxr-x--- 2 oracle oinstall 4096 Jun 28 20:39 2011_06_00 > > drwxr-x--- 2 oracle oinstall 4096 Jun 29 20:53 2011_06_01 > > drwxr-x--- 2 oracle oinstall 4096 Jun 30 21:00 2011_06_02 > > drwxr-x--- 2 oracle oinstall 4096 Jul 1 21:40 2011_07_01 > > drwxr-x--- 2 oracle oinstall 4096 Jul 2 21:50 2011_07_02 > > drwxr-x--- 2 oracle oinstall 4096 Jul 3 21:56 2011_07_03 > > [oracle@wissem backupset]$ mv 2011_07_01 2010_07_01 > > [oracle@wissem backupset]$ mv 2011_07_02 2010_07_02 > > [oracle@wissem backupset]$ > > Like the scenario 1, I “moved” the backups done in 2011_07_01 and > 2011_07_02. Let’s verify of the database will be recovered from the latest > backup copy. > > $ rlrman target / > > Recovery Manager: Release 11.2.0.1.0 - Production on Sun Jul 3 22:00:34 > 2011 > > Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights > reserved. > > connected to target database: ORAWISS (DBID=3175451436, not open) > > RMAN> restore database; > > Starting restore at 03-JUL-11 > > using target database control file instead of recovery catalog > > allocated channel: ORA_DISK_1 > > channel ORA_DISK_1: SID=395 device type=DISK > > allocated channel: ORA_DISK_2 > > channel ORA_DISK_2: SID=10 device type=DISK > > channel ORA_DISK_1: restoring datafile 00001 > > input datafile copy RECID=63 STAMP=755560519 file > name=/home/oracle/backups/fra/ORAWISS/datafile/o1_mf_system_70n7kjrd_.dbf > > destination for restore of datafile 00001: > /home/oracle/app/oracle/oradata/ORAWISS/system01.dbf > > channel ORA_DISK_2: restoring datafile 00002 > > input datafile copy RECID=64 STAMP=755560525 file > name=/home/oracle/backups/fra/ORAWISS/datafile/o1_mf_sysaux_70n7kk7l_.dbf > > destination for restore of datafile 00002: > /home/oracle/app/oracle/oradata/ORAWISS/sysaux01.dbf > > channel ORA_DISK_2: copied datafile copy of datafile 00002 > > output file name=/home/oracle/app/oracle/oradata/ORAWISS/sysaux01.dbf > RECID=0 STAMP=0 > > channel ORA_DISK_2: restoring datafile 00004 > > input datafile copy RECID=56 STAMP=755560508 file > name=/home/oracle/backups/fra/ORAWISS/datafile/o1_mf_users_70n7n3sd_.dbf > > destination for restore of datafile 00004: > /home/oracle/app/oracle/oradata/ORAWISS/users01.dbf > > channel ORA_DISK_1: copied datafile copy of datafile 00001 > > output file name=/home/oracle/app/oracle/oradata/ORAWISS/system01.dbf > RECID=0 STAMP=0 > > channel ORA_DISK_1: restoring datafile 00005 > > input datafile copy RECID=59 STAMP=755560512 file > name=/home/oracle/backups/fra/ORAWISS/datafile/o1_mf_example_70n7m8d9_.dbf > > destination for restore of datafile 00005: > /home/oracle/app/oracle/oradata/ORAWISS/example01.dbf > > channel ORA_DISK_2: copied datafile copy of datafile 00004 > > output file name=/home/oracle/app/oracle/oradata/ORAWISS/users01.dbf > RECID=0 STAMP=0 > > channel ORA_DISK_2: restoring datafile 00006 > > input datafile copy RECID=57 STAMP=755560509 file > name=/home/oracle/backups/fra/ORAWISS/datafile/o1_mf_mgmt_ecm_70n7n07h_.dbf > > destination for restore of datafile 00006: > /home/oracle/app/oracle/oradata/ORAWISS/mgmt_ecm_depot1.dbf > > channel ORA_DISK_2: copied datafile copy of datafile 00006 > > output file > name=/home/oracle/app/oracle/oradata/ORAWISS/mgmt_ecm_depot1.dbf RECID=0 > STAMP=0 > > channel ORA_DISK_2: restoring datafile 00007 > > input datafile copy RECID=62 STAMP=755560518 file > name=/home/oracle/backups/fra/ORAWISS/datafile/o1_mf_mgmt_tab_70n7lxrg_.dbf > > destination for restore of datafile 00007: > /home/oracle/app/oracle/oradata/ORAWISS/mgmt.dbf > > channel ORA_DISK_1: copied datafile copy of datafile 00005 > > output file name=/home/oracle/app/oracle/oradata/ORAWISS/example01.dbf > RECID=0 STAMP=0 > > channel ORA_DISK_1: restoring datafile 00008 > > input datafile copy RECID=61 STAMP=755560515 file > name=/home/oracle/backups/fra/ORAWISS/datafile/o1_mf_mgmt_ad4_70n7m8q4_.dbf > > destination for restore of datafile 00008: > /home/oracle/app/oracle/oradata/ORAWISS/mgmt_ad4j.dbf > > channel ORA_DISK_2: copied datafile copy of datafile 00007 > > output file name=/home/oracle/app/oracle/oradata/ORAWISS/mgmt.dbf RECID=0 > STAMP=0 > > channel ORA_DISK_2: restoring datafile 00009 > > input datafile copy RECID=55 STAMP=755560507 file > name=/home/oracle/backups/fra/ORAWISS/datafile/o1_mf_flash_da_70n7n0rr_.dbf > > destination for restore of datafile 00009: > /home/oracle/app/oracle/oradata/ORAWISS/fb_data_archive.dbf > > channel ORA_DISK_1: copied datafile copy of datafile 00008 > > output file name=/home/oracle/app/oracle/oradata/ORAWISS/mgmt_ad4j.dbf > RECID=0 STAMP=0 > > channel ORA_DISK_1: restoring datafile 00010 > > input datafile copy RECID=58 STAMP=755560511 file > name=/home/oracle/backups/fra/ORAWISS/datafile/o1_mf_tsh_data_70n7mqz4_.dbf > > destination for restore of datafile 00010: > /home/oracle/app/oracle/oradata_2/ORAWISS/datafile/o1_mf_tsh_data_70c9obp9_.dbf > > channel ORA_DISK_2: copied datafile copy of datafile 00009 > > output file > name=/home/oracle/app/oracle/oradata/ORAWISS/fb_data_archive.dbf RECID=0 > STAMP=0 > > channel ORA_DISK_2: restoring datafile 00011 > > input datafile copy RECID=60 STAMP=755560515 file > name=/home/oracle/backups/fra/ORAWISS/datafile/o1_mf_undotbs2_70n7mrs5_.dbf > > destination for restore of datafile 00011: > /home/oracle/app/oracle/oradata_2/ORAWISS/datafile/o1_mf_undotbs2_70w79rlf_.dbf > > channel ORA_DISK_1: copied datafile copy of datafile 00010 > > output file > name=/home/oracle/app/oracle/oradata_2/ORAWISS/datafile/o1_mf_tsh_data_70c9obp9_.dbf > RECID=0 STAMP=0 > > channel ORA_DISK_2: copied datafile copy of datafile 00011 > > output file > name=/home/oracle/app/oracle/oradata_2/ORAWISS/datafile/o1_mf_undotbs2_711lopmy_.dbf > RECID=0 STAMP=0 > > Finished restore at 03-JUL-11 > > RMAN> > > RMAN> recover database; > > Starting recover at 03-JUL-11 > > using channel ORA_DISK_1 > > using channel ORA_DISK_2 > > channel ORA_DISK_1: starting incremental datafile backup set restore > > channel ORA_DISK_1: specifying datafile(s) to restore from backup set > > destination for restore of datafile 00001: > /home/oracle/app/oracle/oradata/ORAWISS/system01.dbf > > destination for restore of datafile 00005: > /home/oracle/app/oracle/oradata/ORAWISS/example01.dbf > > destination for restore of datafile 00006: > /home/oracle/app/oracle/oradata/ORAWISS/mgmt_ecm_depot1.dbf > > destination for restore of datafile 00009: > /home/oracle/app/oracle/oradata/ORAWISS/fb_data_archive.dbf > > destination for restore of datafile 00010: > /home/oracle/app/oracle/oradata_2/ORAWISS/datafile/o1_mf_tsh_data_70c9obp9_.dbf > > channel ORA_DISK_1: reading from backup piece > /home/oracle/backups/fra/ORAWISS/backupset/2011_07_03/o1_mf_nnnd1_LVL0_MERGE_INCR_711l9wys_.bkp > > channel ORA_DISK_2: starting incremental datafile backup set restore > > channel ORA_DISK_2: specifying datafile(s) to restore from backup set > > destination for restore of datafile 00002: > /home/oracle/app/oracle/oradata/ORAWISS/sysaux01.dbf > > destination for restore of datafile 00004: > /home/oracle/app/oracle/oradata/ORAWISS/users01.dbf > > destination for restore of datafile 00007: > /home/oracle/app/oracle/oradata/ORAWISS/mgmt.dbf > > destination for restore of datafile 00008: > /home/oracle/app/oracle/oradata/ORAWISS/mgmt_ad4j.dbf > > destination for restore of datafile 00011: > /home/oracle/app/oracle/oradata_2/ORAWISS/datafile/o1_mf_undotbs2_711lopmy_.dbf > > channel ORA_DISK_2: reading from backup piece > /home/oracle/backups/fra/ORAWISS/backupset/2011_07_03/o1_mf_nnnd1_LVL0_MERGE_INCR_711lb8w8_.bkp > > channel ORA_DISK_1: piece > handle=/home/oracle/backups/fra/ORAWISS/backupset/2011_07_03/o1_mf_nnnd1_LVL0_MERGE_INCR_711l9wys_.bkp > tag=LVL0_MERGE_INCR > > channel ORA_DISK_1: restored backup piece 1 > > channel ORA_DISK_1: restore complete, elapsed time: 00:00:08 > > channel ORA_DISK_2: piece > handle=/home/oracle/backups/fra/ORAWISS/backupset/2011_07_03/o1_mf_nnnd1_LVL0_MERGE_INCR_711lb8w8_.bkp > tag=LVL0_MERGE_INCR > > channel ORA_DISK_2: restored backup piece 1 > > channel ORA_DISK_2: restore complete, elapsed time: 00:00:25 > > starting media recovery > > media recovery complete, elapsed time: 00:00:03 > > Finished recover at 03-JUL-11 > > RMAN> alter database open; > > database opened > > RMAN> > > SQL> alter session set nls_date_format='dd.mm.yyyy hh24:mi:ss'; > > Session altered. > > SQL> select * from wissem.testbackup_2 ; > > P_DATE > > ------------------- > > 02.07.2011 21:42:52 > > 03.07.2011 21:52:09 > > 03.07.2011 21:57:17 > > 01.07.2011 21:34:36 > > SQL> > > Now remove the backup copies made in the 03rd of July and 02nd of July and > consider recover until time prior to 1 july 2011 > > [oracle@wissem backupset]$ ls -rtl > > total 24 > > drwxr-x--- 2 oracle oinstall 4096 Jun 28 20:39 2011_06_00 > > drwxr-x--- 2 oracle oinstall 4096 Jun 29 20:53 2011_06_01 > > drwxr-x--- 2 oracle oinstall 4096 Jun 30 21:00 2011_06_02 > > drwxr-x--- 2 oracle oinstall 4096 Jul 1 21:40 2010_07_01 > > drwxr-x--- 2 oracle oinstall 4096 Jul 2 21:50 2010_07_02 > > drwxr-x--- 2 oracle oinstall 4096 Jul 3 21:56 2011_07_03 > > [oracle@wissem backupset]$ mv 2011_07_03/ 2010_07_03 > > [oracle@wissem backupset]$ mv 2010_07_01 2011_07_01 > > [oracle@wissem backupset]$ ls -rtl > > total 24 > > drwxr-x--- 2 oracle oinstall 4096 Jun 28 20:39 2011_06_00 > > drwxr-x--- 2 oracle oinstall 4096 Jun 29 20:53 2011_06_01 > > drwxr-x--- 2 oracle oinstall 4096 Jun 30 21:00 2011_06_02 > > drwxr-x--- 2 oracle oinstall 4096 Jul 1 21:40 2011_07_01 > > drwxr-x--- 2 oracle oinstall 4096 Jul 2 21:50 2010_07_02 > > drwxr-x--- 2 oracle oinstall 4096 Jul 3 21:56 2010_07_03 > > [oracle@wissem backupset]$ > > [oracle@wissem backupset]$ rlrman target / > > Recovery Manager: Release 11.2.0.1.0 - Production on Sun Jul 3 22:06:47 > 2011 > > Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights > reserved. > > connected to target database: ORAWISS (DBID=3175451436) > > RMAN> run{ > > 2> allocate channel dev1 type disk; > > 3> set until time "to_date('2011-01-07:21:35:36', > 'yyyy-dd-mm:hh24:mi:ss')"; > > 4> restore database; > > 5> recover database; > > 6> } > > using target database control file instead of recovery catalog > > allocated channel: dev1 > > channel dev1: SID=403 device type=DISK > > executing command: SET until clause > > Starting restore at 03-JUL-11 > > released channel: dev1 > > RMAN-00571: =========================================================== > > RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== > > RMAN-00571: =========================================================== > > RMAN-03002: failure of restore command at 07/03/2011 22:07:44 > > 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> > > We got an error, let’s try to keep all the backups available. > > [oracle@wissem backupset]$ mv 2010_07_02 2011_07_02 > > [oracle@wissem backupset]$ mv 2010_07_03/ 2011_07_03 > > [oracle@wissem backupset]$ ls -rl > > total 24 > > drwxr-x--- 2 oracle oinstall 4096 Jul 3 21:56 2011_07_03 > > drwxr-x--- 2 oracle oinstall 4096 Jul 2 21:50 2011_07_02 > > drwxr-x--- 2 oracle oinstall 4096 Jul 1 21:40 2011_07_01 > > drwxr-x--- 2 oracle oinstall 4096 Jun 30 21:00 2011_06_02 > > drwxr-x--- 2 oracle oinstall 4096 Jun 29 20:53 2011_06_01 > > drwxr-x--- 2 oracle oinstall 4096 Jun 28 20:39 2011_06_00 > > [oracle@wissem backupset]$ > > [oracle@wissem backupset]$ rlrman target / > > Recovery Manager: Release 11.2.0.1.0 - Production on Sun Jul 3 22:13:10 > 2011 > > Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights > reserved. > > connected to target database: ORAWISS (DBID=3175451436) > > RMAN> run{ > > 2> allocate channel dev1 type disk; > > 3> set until time "to_date('2011-01-07:21:35:36', > 'yyyy-dd-mm:hh24:mi:ss')"; > > 4> restore database; > > 5> recover database; > > 6> } > > using target database control file instead of recovery catalog > > allocated channel: dev1 > > channel dev1: SID=19 device type=DISK > > executing command: SET until clause > > Starting restore at 03-JUL-11 > > released channel: dev1 > > RMAN-00571: =========================================================== > > RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== > > RMAN-00571: =========================================================== > > RMAN-03002: failure of restore command at 07/03/2011 22:13:32 > > 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> > > [oracle@wissem backupset]$ mv 2011_06_00 2011_06_28 > [oracle@wissem backupset]$ mv 2011_06_01 2011_06_29 > [oracle@wissem backupset]$ mv 2011_06_02 2011_06_30 > [oracle@wissem backupset]$ ls -rtl > total 24 > drwxr-x--- 2 oracle oinstall 4096 Jun 28 20:39 2011_06_28 > drwxr-x--- 2 oracle oinstall 4096 Jun 29 2011 2011_06_29 > drwxr-x--- 2 oracle oinstall 4096 Jun 30 2011 2011_06_30 > drwxr-x--- 2 oracle oinstall 4096 Jul 1 2011 2011_07_01 > drwxr-x--- 2 oracle oinstall 4096 Jul 2 2011 2011_07_02 > drwxr-x--- 2 oracle oinstall 4096 Jul 3 2011 2011_07_03 > [oracle@wissem backupset]$ rlrman target / > > Recovery Manager: Release 11.2.0.1.0 - Production on Tue Jun 28 23:25:35 > 2011 > > Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights > reserved. > > connected to target database: ORAWISS (DBID=3175451436) > > RMAN> run{ > 2> set until time "to_date('2011-01-07:21:35:36', > 'yyyy-dd-mm:hh24:mi:ss')"; > 3> restore database; > 4> recover database; > 5> } > > executing command: SET until clause > > Starting restore at 28-JUN-11 > using target database control file instead of recovery catalog > allocated channel: ORA_DISK_1 > channel ORA_DISK_1: SID=395 device type=DISK > allocated channel: ORA_DISK_2 > channel ORA_DISK_2: SID=10 device type=DISK > > RMAN-00571: =========================================================== > RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== > RMAN-00571: =========================================================== > RMAN-03002: failure of restore command at 06/28/2011 23:25:53 > 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> > > We have all the backups, we cannot recover within the recovery window of 7 > days. > > > thank you > > > <http://es.linkedin.com/in/orawiss> > >