To recover to a particular point in time, getting details about time or scn will help in doing a point in time recovery. The goal here is limited to do recovery using incremental backups.
SQL> conn / as sysdba
Connected.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
8142998
SQL> select systimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
21-JUN-12 02.22.37.519217 PM -05:00
Kick off by taking a level 0 Backup, Taking a level 0 cumulative backup
[oracle@lnx133 work]$ ls -lrt r*
-rwxr-xr-x 1 oracle oinstall 294 Jun 21 13:58 rmanbackup.sh
-rw-r--r-- 1 oracle oinstall 586 Jun 21 14:31 rmanhotbackup.shdrwxrwxr-x 2 oracle oinstall 16K Dec 19 2011 lost+found
A look at the RMAN scripts to take hot backup
[oracle@lnx133 work]$ cat rmanbackup.sh#!/bin/ksh
export ORACLE_SID=maze
export ORACLE_HOME=/u01/app/oracle/product/11g/11.2.0.2/dbhome_1
export PATH=$PATH:$ORACLE_HOME/bin
msglog=rman_hot_backup_`date +\%Y\%m\%d`.log
rman target / cmdfile=rmanhotbackup.sh log=$msglog append
The command file for RMAN, you can configure it as per your requirement, here basic few parameters
have been configured.
[oracle@lnx133 work]$ cat rmanhotbackup.shrun
{
configure CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/app/oracle/backup/maze/ctl%d_%F';
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/backup/maze/sncf_%d';
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
backup tag HBlevel0 filesperset 10 format '/u01/app/oracle/backup/maze/Hot_Base_%d_s%s_%U' as compressed backupset incremental level 0 cumulative database plus archivelog;
release channel c1;
release channel c2;
release channel c3;
}
exit;
[oracle@lnx133 work]$ nohup ./rmanbackup.sh &[1] 3764
[oracle@lnx133 work]$ nohup: appending output to `nohup.out'
[oracle@lnx133 work]$ ls -lrt r*
-rwxr-xr-x 1 oracle oinstall 294 Jun 21 13:58 rmanbackup.sh
-rw-r--r-- 1 oracle oinstall 586 Jun 21 14:31 rmanhotbackup.sh
-rw-r--r-- 1 oracle oinstall 6195 Jun 21 14:34 rman_hot_backup_20120621.logmonitoring the rman log
[oracle@lnx133 work]$ tail -f rman_hot_backup_20120621.logchannel c1: starting piece 1 at 21-JUN-12
channel c2: starting compressed archived log backup set
channel c2: specifying archived log(s) in backup set
input archived log thread=1 sequence=341 RECID=2 STAMP=786550083
input archived log thread=1 sequence=342 RECID=3 STAMP=786550332
channel c2: starting piece 1 at 21-JUN-12
channel c3: starting compressed archived log backup set
channel c3: specifying archived log(s) in backup set
input archived log thread=1 sequence=343 RECID=4 STAMP=786551149
channel c3: starting piece 1 at 21-JUN-12
channel c1: finished piece 1 at 21-JUN-12
piece handle=/u01/app/oracle/backup/maze/Hot_Base_MAZE_s15_0fne3kli_1_1 tag=HBLEVEL0 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:04
channel c1: starting compressed archived log backup set
channel c1: specifying archived log(s) in backup set
input archived log thread=1 sequence=344 RECID=5 STAMP=786551357
input archived log thread=1 sequence=345 RECID=6 STAMP=786551473
channel c1: starting piece 1 at 21-JUN-12
channel c2: finished piece 1 at 21-JUN-12
piece handle=/u01/app/oracle/backup/maze/Hot_Base_MAZE_s16_0gne3kli_1_1 tag=HBLEVEL0 comment=NONE
channel c2: backup set complete, elapsed time: 00:00:04
channel c3: finished piece 1 at 21-JUN-12
piece handle=/u01/app/oracle/backup/maze/Hot_Base_MAZE_s17_0hne3klj_1_1 tag=HBLEVEL0 comment=NONE
channel c3: backup set complete, elapsed time: 00:00:03
channel c1: finished piece 1 at 21-JUN-12
piece handle=/u01/app/oracle/backup/maze/Hot_Base_MAZE_s18_0ine3klm_1_1 tag=HBLEVEL0 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 21-JUN-12
Starting backup at 21-JUN-12
channel c1: starting compressed incremental level 0 datafile backup setchannel c1: specifying datafile(s) in backup set
input datafile file number=00002 name=/u01/app/oracle/oradata1/maze/sysaux01.dbf
input datafile file number=00006 name=/u01/app/oracle/oradata1/maze/sj01.dbf
channel c1: starting piece 1 at 21-JUN-12
channel c2: starting compressed incremental level 0 datafile backup set
channel c2: specifying datafile(s) in backup set
input datafile file number=00003 name=/u01/app/oracle/oradata1/maze/undotbs01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata1/maze/example01.dbf
channel c2: starting piece 1 at 21-JUN-12
channel c3: starting compressed incremental level 0 datafile backup set
channel c3: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata1/maze/system01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata1/maze/users01.dbf
channel c3: starting piece 1 at 21-JUN-12
channel c2: finished piece 1 at 21-JUN-12
piece handle=/u01/app/oracle/backup/maze/Hot_Base_MAZE_s20_0kne3klo_1_1 tag=HBLEVEL0 comment=NONE
channel c2: backup set complete, elapsed time: 00:00:45
channel c3: finished piece 1 at 21-JUN-12
piece handle=/u01/app/oracle/backup/maze/Hot_Base_MAZE_s21_0lne3klo_1_1 tag=HBLEVEL0 comment=NONE
channel c3: backup set complete, elapsed time: 00:03:06
channel c1: finished piece 1 at 21-JUN-12
piece handle=/u01/app/oracle/backup/maze/Hot_Base_MAZE_s19_0jne3klo_1_1 tag=HBLEVEL0 comment=NONE
channel c1: backup set complete, elapsed time: 00:03:16
Finished backup at 21-JUN-12
Starting backup at 21-JUN-12
current log archivedchannel c1: starting compressed archived log backup set
channel c1: specifying archived log(s) in backup set
input archived log thread=1 sequence=346 RECID=7 STAMP=786551678
channel c1: starting piece 1 at 21-JUN-12
channel c1: finished piece 1 at 21-JUN-12
piece handle=/u01/app/oracle/backup/maze/Hot_Base_MAZE_s22_0mne3ks0_1_1 tag=HBLEVEL0 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 21-JUN-12
Starting Control File and SPFILE Autobackup at 21-JUN-12
piece handle=/u01/app/oracle/backup/maze/ctlMAZE_c-1370075061-20120621-02 comment=NONE
Finished Control File and SPFILE Autobackup at 21-JUN-12
released channel: c1
released channel: c2
released channel: c3
Recovery Manager complete.
[1]+ Done nohup ./rmanbackup.sh
level 0 backup is followed by generating activity--> creating & inserting records in scott.worker2 table, followed by incremental level 1 cumulative backup[oracle@lnx133 work]$ sql
SQL*Plus: Release 11.2.0.2.0 Production on Thu Jun 21 14:35:13 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> conn scott/abc123;
Connected.
SQL> create table worker2 as select * from worker;Table created.
SQL> insert into worker2 select * from worker2;6 rows created.
SQL> /
12 rows created.
SQL> /
24 rows created.
SQL> /
48 rows created.
SQL> /
96 rows created.
SQL> /
192 rows created.
SQL> /
384 rows created.
SQL> /
768 rows created.
SQL> /
1536 rows created.
SQL> /
3072 rows created.
SQL> /
6144 rows created.
SQL> /
12288 rows created.
SQL> /
24576 rows created.
SQL> /
49152 rows created.
SQL> /
98304 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from worker2;COUNT(*)
----------
196608
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Ready to take level 1 incremental cumulative backup so it back's up the changes.
[oracle@lnx133 work]$ ls -lrt r*
-rwxr-xr-x 1 oracle oinstall 294 Jun 21 13:58 rmanbackup.sh
-rw-r--r-- 1 oracle oinstall 586 Jun 21 14:31 rmanhotbackup.sh
-rw-r--r-- 1 oracle oinstall 6195 Jun 21 14:34 rman_hot_backup_20120621.log
-rwxr-xr-x 1 oracle oinstall 301 Jun 21 14:42 rmanincbackup.sh
-rw-r--r-- 1 oracle oinstall 585 Jun 21 14:44 rmaninchotbackup.sh[oracle@lnx133 work]$ cat rmanincbackup.sh#!/bin/ksh
export ORACLE_SID=maze
export ORACLE_HOME=/u01/app/oracle/product/11g/11.2.0.2/dbhome_1
export PATH=$PATH:$ORACLE_HOME/bin
msglog=rman_hot_backup_`date +\%Y\%m\%d_\%T`.log
rman target / cmdfile=rmaninchotbackup.sh log=$msglog append
{
configure CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/app/oracle/backup/maze/ctl%d_%F';
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/backup/maze/sncf_%d_`date`';
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
backup tag HBlevel1 filesperset 10 format '/u01/app/oracle/backup/maze/Hot_Inc_%d_s%s_%U' as compressed backupset incremental level 1 cumulative database plus archivelog;
release channel c1;
release channel c2;
release channel c3;
}
exit;
[oracle@lnx133 work]$ nohup ./rmanincbackup.sh &[1] 3973
[oracle@lnx133 work]$ nohup: appending output to `nohup.out'
[oracle@lnx133 work]$ ls -lrt r*
-rwxr-xr-x 1 oracle oinstall 294 Jun 21 13:58 rmanbackup.sh
-rw-r--r-- 1 oracle oinstall 586 Jun 21 14:31 rmanhotbackup.sh
-rw-r--r-- 1 oracle oinstall 6195 Jun 21 14:34 rman_hot_backup_20120621.log
-rwxr-xr-x 1 oracle oinstall 301 Jun 21 14:42 rmanincbackup.sh
-rw-r--r-- 1 oracle oinstall 585 Jun 21 14:44 rmaninchotbackup.sh
-rw-r--r-- 1 oracle oinstall 6316 Jun 21 14:49 rman_hot_backup_20120621_14:45:42.logmonitoring the rman backup log
[oracle@lnx133 work]$ tail -f rman_hot_backup_20120621_14\:45\:42.logchannel c2: starting compressed incremental level 1 datafile backup set
channel c2: specifying datafile(s) in backup set
input datafile file number=00003 name=/u01/app/oracle/oradata1/maze/undotbs01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata1/maze/example01.dbf
channel c2: starting piece 1 at 21-JUN-12
channel c3: starting compressed incremental level 1 datafile backup set
channel c3: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata1/maze/system01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata1/maze/users01.dbf
channel c3: starting piece 1 at 21-JUN-12
channel c3: finished piece 1 at 21-JUN-12
piece handle=/u01/app/oracle/backup/maze/Hot_Inc_MAZE_s30_0une3lh5_1_1 tag=HBLEVEL1 comment=NONE
channel c3: backup set complete, elapsed time: 00:02:06
channel c2: finished piece 1 at 21-JUN-12
piece handle=/u01/app/oracle/backup/maze/Hot_Inc_MAZE_s29_0tne3lh5_1_1 tag=HBLEVEL1 comment=NONE
channel c2: backup set complete, elapsed time: 00:02:26
channel c1: finished piece 1 at 21-JUN-12
piece handle=/u01/app/oracle/backup/maze/Hot_Inc_MAZE_s28_0sne3lh5_1_1 tag=HBLEVEL1 comment=NONE
channel c1: backup set complete, elapsed time: 00:02:56
Finished backup at 21-JUN-12
Starting backup at 21-JUN-12
current log archived
channel c1: starting compressed archived log backup set
channel c1: specifying archived log(s) in backup set
input archived log thread=1 sequence=348 RECID=9 STAMP=786552534
channel c1: starting piece 1 at 21-JUN-12
channel c1: finished piece 1 at 21-JUN-12
piece handle=/u01/app/oracle/backup/maze/Hot_Inc_MAZE_s31_0vne3lmn_1_1 tag=HBLEVEL1 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 21-JUN-12
Starting Control File and SPFILE Autobackup at 21-JUN-12
piece handle=/u01/app/oracle/backup/maze/ctlMAZE_c-1370075061-20120621-03 comment=NONE
Finished Control File and SPFILE Autobackup at 21-JUN-12
released channel: c1
released channel: c2
released channel: c3
Recovery Manager complete.
[1]+ Done nohup ./rmanincbackup.sh
[oracle@lnx133 work]$ sql
SQL*Plus: Release 11.2.0.2.0 Production on Thu Jun 21 14:50:17 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> conn scott/abc123;
Connected.
SQL> select count(*) from worker2;
COUNT(*)
----------
196608
SQL> insert into worker2 select * from worker;
6 rows created.
SQL> commit;
Commit complete.
SQL> insert into worker2 select * from worker2;
insert into worker2 select * from worker2
*
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/u01/app/oracle/oradata1/maze/users01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
SQL> conn / as sysdba
Connected.
SQL> shut immediate;
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/u01/app/oracle/oradata1/maze/users01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
SQL> shutdown abort;
ORACLE instance shut down.
SQL> exitDisconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@lnx133 work]$ sql
SQL*Plus: Release 11.2.0.2.0 Production on Thu Jun 21 14:52:30 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount;ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed Size 1344616 bytes
Variable Size 318770072 bytes
Database Buffers 96468992 bytes
Redo Buffers 6086656 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
We can/have to run the recovery from a script, but here I chose to run by connecting to rman & then issuing commands to restore/recover.
[oracle@lnx133 work]$ rman target /
Recovery Manager: Release 11.2.0.2.0 - Production on Thu Jun 21 14:52:57 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: MAZE (DBID=1370075061, not open)
RMAN> list backup summary;
using target database control file instead of recovery catalog
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
15 B A A DISK 21-JUN-12 1 1 YES HBLEVEL0
16 B A A DISK 21-JUN-12 1 1 YES HBLEVEL0
17 B A A DISK 21-JUN-12 1 1 YES HBLEVEL0
18 B A A DISK 21-JUN-12 1 1 YES HBLEVEL0
19 B 0 A DISK 21-JUN-12 1 1 YES HBLEVEL0
20 B 0 A DISK 21-JUN-12 1 1 YES HBLEVEL0
21 B 0 A DISK 21-JUN-12 1 1 YES HBLEVEL0
22 B A A DISK 21-JUN-12 1 1 YES HBLEVEL0
23 B F A DISK 21-JUN-12 1 1 NO TAG20120621T143442
24 B A A DISK 21-JUN-12 1 1 YES HBLEVEL1
25 B A A DISK 21-JUN-12 1 1 YES HBLEVEL1
26 B A A DISK 21-JUN-12 1 1 YES HBLEVEL1
27 B A A DISK 21-JUN-12 1 1 YES HBLEVEL1
28 B 1 A DISK 21-JUN-12 1 1 YES HBLEVEL1
29 B 1 A DISK 21-JUN-12 1 1 YES HBLEVEL1
30 B 1 A DISK 21-JUN-12 1 1 YES HBLEVEL1
31 B A A DISK 21-JUN-12 1 1 YES HBLEVEL1
32 B F A DISK 21-JUN-12 1 1 NO TAG20120621T144856
RMAN> run {Starting restore at 21-JUN-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=133 device type=DISK
channel ORA_DISK_1: starting datafile backup set restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata1/maze/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata1/maze/example01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup/maze/Hot_Base_MAZE_s20_0kne3klo_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/backup/maze/Hot_Base_MAZE_s20_0kne3klo_1_1 tag=HBLEVEL0
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:25
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata1/maze/system01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata1/maze/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup/maze/Hot_Base_MAZE_s21_0lne3klo_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/backup/maze/Hot_Base_MAZE_s21_0lne3klo_1_1 tag=HBLEVEL0
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:56
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata1/maze/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata1/maze/sj01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup/maze/Hot_Base_MAZE_s19_0jne3klo_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/backup/maze/Hot_Base_MAZE_s19_0jne3klo_1_1 tag=HBLEVEL0
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:35
Finished restore at 21-JUN-12Starting recover at 21-JUN-12using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/app/oracle/oradata1/maze/system01.dbf
destination for restore of datafile 00004: /u01/app/oracle/oradata1/maze/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup/maze/Hot_Inc_MAZE_s30_0une3lh5_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/backup/maze/Hot_Inc_MAZE_s30_0une3lh5_1_1 tag=HBLEVEL1
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
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 00003: /u01/app/oracle/oradata1/maze/undotbs01.dbf
destination for restore of datafile 00005: /u01/app/oracle/oradata1/maze/example01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup/maze/Hot_Inc_MAZE_s29_0tne3lh5_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/backup/maze/Hot_Inc_MAZE_s29_0tne3lh5_1_1 tag=HBLEVEL1
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
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 00002: /u01/app/oracle/oradata1/maze/sysaux01.dbf
destination for restore of datafile 00006: /u01/app/oracle/oradata1/maze/sj01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup/maze/Hot_Inc_MAZE_s28_0sne3lh5_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/backup/maze/Hot_Inc_MAZE_s28_0sne3lh5_1_1 tag=HBLEVEL1
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
starting media recovery
media recovery complete, elapsed time: 00:00:14
Finished recover at 21-JUN-12
sql statement: alter database open
RMAN> exitRecovery Manager complete.
2> restore database;
3> recover database;
4> sql 'alter database open';
5> }
Completed: ALTER DATABASE MOUNTThu Jun 21 14:55:25 2012
Full restore complete of datafile
checkpoint is 8143741
last deallocation scn is 8133875
Undo Optimization current scn is 8139292
Thu Jun 21 14:55:40 2012
Full restore complete of datafile 5 /u01/app/oracle/oradata1/maze/example01.dbf. Elapsed time: 0:01:16
checkpoint is 8143741
last deallocation scn is 7542368
Full restore complete of datafile 4 /u01/app/oracle/oradata1/maze/users01.dbf. Elapsed time: 0:00:02
checkpoint is 8143742
last deallocation scn is 8142856
Thu Jun 21 14:57:35 2012
Full restore complete of datafile 1 /u01/app/oracle/oradata1/maze/system01.dbf. Elapsed time: 0:01:49
checkpoint is 8143742
last deallocation scn is 7355940
Undo Optimization current scn is 8139292
Thu Jun 21 14:58:08 2012
Full restore complete of datafile 6 /u01/app/oracle/oradata1/maze/sj01.dbf. Elapsed time: 0:00:20
checkpoint is 8143740
last deallocation scn is 770465
Thu Jun 21 15:00:10 2012
Full restore complete of datafile 2 /u01/app/oracle/oradata1/maze/sysaux01.dbf. Elapsed time: 0:02:24
checkpoint is 8143740
last deallocation scn is 8093014
Thu Jun 21 15:00:22 2012
Incremental restore complete of datafile
checkpoint is 8144777
last deallocation scn is 8142856
Incremental restore complete of datafile 1 /u01/app/oracle/oradata1/maze/system01.dbf
checkpoint is 8144777
last deallocation scn is 7355940
Incremental restore complete of datafile 5 /u01/app/oracle/oradata1/maze/example01.dbf
checkpoint is 8144776
last deallocation scn is 7542368
Incremental restore complete of datafile 3 /u01/app/oracle/oradata1/maze/undotbs01.dbf
checkpoint is 8144776
last deallocation scn is 8133875
Incremental restore complete of datafile 6 /u01/app/oracle/oradata1/maze/sj01.dbf
checkpoint is 8144775
last deallocation scn is 770465
Incremental restore complete of datafile 2 /u01/app/oracle/oradata1/maze/sysaux01.dbf
checkpoint is 8144775
last deallocation scn is 8093014
Thu Jun 21 15:00:31 2012
alter database recover datafile list clear
Completed: alter database recover datafile list clearalter database recover datafile list
1 , 2 , 3 , 4 , 5 , 6
Completed: alter database recover datafile list
1 , 2 , 3 , 4 , 5 , 6
alter database recover if needed
start
Media Recovery Startstarted logmerger process
Parallel Media Recovery started with 2 slaves
Thu Jun 21 15:00:39 2012
Recovery of Online Redo Log: Thread 1 Group 3 Seq 348 Reading mem 0
Mem# 0: /u01/app/oracle/oradata1/maze/redo03.log
Recovery of Online Redo Log: Thread 1 Group 1 Seq 349 Reading mem 0
Mem# 0: /u01/app/oracle/oradata1/maze/redo01.log
Thu Jun 21 15:00:45 2012
Media Recovery Complete (maze)
Completed: alter database recover if needed
start
alter database openBeginning crash recovery of 1 threads
parallel recovery started with 2 processes
Started redo scan
Completed redo scan
read 0 KB redo, 0 data blocks need recovery
Started redo application at
Thread 1: logseq 349, block 154, scn 8145023
Recovery of Online Redo Log: Thread 1 Group 1 Seq 349 Reading mem 0
Mem# 0: /u01/app/oracle/oradata1/maze/redo01.log
Completed redo application of 0.00MB
Completed crash recovery at
Thread 1: logseq 349, block 154, scn 8165024
0 data blocks read, 0 data blocks written, 0 redo k-bytes read
Thu Jun 21 15:00:48 2012
LGWR: STARTING ARCH PROCESSES
Thu Jun 21 15:00:48 2012
ARC0 started with pid=24, OS id=4258
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Thu Jun 21 15:00:49 2012
ARC1 started with pid=25, OS id=4260
Thu Jun 21 15:00:49 2012
ARC2 started with pid=26, OS id=4262
ARC1: Archival started
ARC2: Archival started
Thu Jun 21 15:00:49 2012
ARC3 started with pid=27, OS id=4264
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
ARC2: Becoming the heartbeat ARCH
Thread 1 advanced to log sequence 350 (thread open)
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Thread 1 opened at log sequence 350
Current log# 2 seq# 350 mem# 0: /u01/app/oracle/oradata1/maze/redo02.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Thu Jun 21 15:00:50 2012
SMON: enabling cache recovery
Archived Log entry 10 added for thread 1 sequence 349 ID 0x51a9aeb5 dest 1:
Thu Jun 21 15:00:56 2012
[4206] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:447383434 end:447385294 diff:1860 (18 seconds)
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is AL32UTF8
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Thu Jun 21 15:01:05 2012
QMNC started with pid=28, OS id=4268
Thu Jun 21 15:01:13 2012
Completed: alter database open
Thu Jun 21 15:01:20 20124 /u01/app/oracle/oradata1/maze/users01.dbf3 /u01/app/oracle/oradata1/maze/undotbs01.dbf. Elapsed time: 0:00:42
Once the database is open, connecting to verify records.
SQL> select count(*) from scott.worker2;COUNT(*)
----------
196614
SQL> conn scott/abc123;
Connected.
SQL> insert into worker2 select * from worker2;
196614 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from scott.worker2;
COUNT(*)
----------
393228
Confirms we have recovered the failure.
Please refer to oracle documentation for a complete/accurate information.
SQL> conn / as sysdba
Connected.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
8142998
SQL> select systimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
21-JUN-12 02.22.37.519217 PM -05:00
Kick off by taking a level 0 Backup, Taking a level 0 cumulative backup
[oracle@lnx133 work]$ ls -lrt r*
-rwxr-xr-x 1 oracle oinstall 294 Jun 21 13:58 rmanbackup.sh
-rw-r--r-- 1 oracle oinstall 586 Jun 21 14:31 rmanhotbackup.shdrwxrwxr-x 2 oracle oinstall 16K Dec 19 2011 lost+found
A look at the RMAN scripts to take hot backup
[oracle@lnx133 work]$ cat rmanbackup.sh#!/bin/ksh
export ORACLE_SID=maze
export ORACLE_HOME=/u01/app/oracle/product/11g/11.2.0.2/dbhome_1
export PATH=$PATH:$ORACLE_HOME/bin
msglog=rman_hot_backup_`date +\%Y\%m\%d`.log
rman target / cmdfile=rmanhotbackup.sh log=$msglog append
The command file for RMAN, you can configure it as per your requirement, here basic few parameters
have been configured.
[oracle@lnx133 work]$ cat rmanhotbackup.shrun
{
configure CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/app/oracle/backup/maze/ctl%d_%F';
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/backup/maze/sncf_%d';
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
backup tag HBlevel0 filesperset 10 format '/u01/app/oracle/backup/maze/Hot_Base_%d_s%s_%U' as compressed backupset incremental level 0 cumulative database plus archivelog;
release channel c1;
release channel c2;
release channel c3;
}
exit;
[oracle@lnx133 work]$ nohup ./rmanbackup.sh &[1] 3764
[oracle@lnx133 work]$ nohup: appending output to `nohup.out'
[oracle@lnx133 work]$ ls -lrt r*
-rwxr-xr-x 1 oracle oinstall 294 Jun 21 13:58 rmanbackup.sh
-rw-r--r-- 1 oracle oinstall 586 Jun 21 14:31 rmanhotbackup.sh
-rw-r--r-- 1 oracle oinstall 6195 Jun 21 14:34 rman_hot_backup_20120621.logmonitoring the rman log
[oracle@lnx133 work]$ tail -f rman_hot_backup_20120621.logchannel c1: starting piece 1 at 21-JUN-12
channel c2: starting compressed archived log backup set
channel c2: specifying archived log(s) in backup set
input archived log thread=1 sequence=341 RECID=2 STAMP=786550083
input archived log thread=1 sequence=342 RECID=3 STAMP=786550332
channel c2: starting piece 1 at 21-JUN-12
channel c3: starting compressed archived log backup set
channel c3: specifying archived log(s) in backup set
input archived log thread=1 sequence=343 RECID=4 STAMP=786551149
channel c3: starting piece 1 at 21-JUN-12
channel c1: finished piece 1 at 21-JUN-12
piece handle=/u01/app/oracle/backup/maze/Hot_Base_MAZE_s15_0fne3kli_1_1 tag=HBLEVEL0 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:04
channel c1: starting compressed archived log backup set
channel c1: specifying archived log(s) in backup set
input archived log thread=1 sequence=344 RECID=5 STAMP=786551357
input archived log thread=1 sequence=345 RECID=6 STAMP=786551473
channel c1: starting piece 1 at 21-JUN-12
channel c2: finished piece 1 at 21-JUN-12
piece handle=/u01/app/oracle/backup/maze/Hot_Base_MAZE_s16_0gne3kli_1_1 tag=HBLEVEL0 comment=NONE
channel c2: backup set complete, elapsed time: 00:00:04
channel c3: finished piece 1 at 21-JUN-12
piece handle=/u01/app/oracle/backup/maze/Hot_Base_MAZE_s17_0hne3klj_1_1 tag=HBLEVEL0 comment=NONE
channel c3: backup set complete, elapsed time: 00:00:03
channel c1: finished piece 1 at 21-JUN-12
piece handle=/u01/app/oracle/backup/maze/Hot_Base_MAZE_s18_0ine3klm_1_1 tag=HBLEVEL0 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 21-JUN-12
Starting backup at 21-JUN-12
channel c1: starting compressed incremental level 0 datafile backup setchannel c1: specifying datafile(s) in backup set
input datafile file number=00002 name=/u01/app/oracle/oradata1/maze/sysaux01.dbf
input datafile file number=00006 name=/u01/app/oracle/oradata1/maze/sj01.dbf
channel c1: starting piece 1 at 21-JUN-12
channel c2: starting compressed incremental level 0 datafile backup set
channel c2: specifying datafile(s) in backup set
input datafile file number=00003 name=/u01/app/oracle/oradata1/maze/undotbs01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata1/maze/example01.dbf
channel c2: starting piece 1 at 21-JUN-12
channel c3: starting compressed incremental level 0 datafile backup set
channel c3: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata1/maze/system01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata1/maze/users01.dbf
channel c3: starting piece 1 at 21-JUN-12
channel c2: finished piece 1 at 21-JUN-12
piece handle=/u01/app/oracle/backup/maze/Hot_Base_MAZE_s20_0kne3klo_1_1 tag=HBLEVEL0 comment=NONE
channel c2: backup set complete, elapsed time: 00:00:45
channel c3: finished piece 1 at 21-JUN-12
piece handle=/u01/app/oracle/backup/maze/Hot_Base_MAZE_s21_0lne3klo_1_1 tag=HBLEVEL0 comment=NONE
channel c3: backup set complete, elapsed time: 00:03:06
channel c1: finished piece 1 at 21-JUN-12
piece handle=/u01/app/oracle/backup/maze/Hot_Base_MAZE_s19_0jne3klo_1_1 tag=HBLEVEL0 comment=NONE
channel c1: backup set complete, elapsed time: 00:03:16
Finished backup at 21-JUN-12
Starting backup at 21-JUN-12
current log archivedchannel c1: starting compressed archived log backup set
channel c1: specifying archived log(s) in backup set
input archived log thread=1 sequence=346 RECID=7 STAMP=786551678
channel c1: starting piece 1 at 21-JUN-12
channel c1: finished piece 1 at 21-JUN-12
piece handle=/u01/app/oracle/backup/maze/Hot_Base_MAZE_s22_0mne3ks0_1_1 tag=HBLEVEL0 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 21-JUN-12
Starting Control File and SPFILE Autobackup at 21-JUN-12
piece handle=/u01/app/oracle/backup/maze/ctlMAZE_c-1370075061-20120621-02 comment=NONE
Finished Control File and SPFILE Autobackup at 21-JUN-12
released channel: c1
released channel: c2
released channel: c3
Recovery Manager complete.
[1]+ Done nohup ./rmanbackup.sh
level 0 backup is followed by generating activity--> creating & inserting records in scott.worker2 table, followed by incremental level 1 cumulative backup[oracle@lnx133 work]$ sql
SQL*Plus: Release 11.2.0.2.0 Production on Thu Jun 21 14:35:13 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> conn scott/abc123;
Connected.
SQL> create table worker2 as select * from worker;Table created.
SQL> insert into worker2 select * from worker2;6 rows created.
SQL> /
12 rows created.
SQL> /
24 rows created.
SQL> /
48 rows created.
SQL> /
96 rows created.
SQL> /
192 rows created.
SQL> /
384 rows created.
SQL> /
768 rows created.
SQL> /
1536 rows created.
SQL> /
3072 rows created.
SQL> /
6144 rows created.
SQL> /
12288 rows created.
SQL> /
24576 rows created.
SQL> /
49152 rows created.
SQL> /
98304 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from worker2;COUNT(*)
----------
196608
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Ready to take level 1 incremental cumulative backup so it back's up the changes.
[oracle@lnx133 work]$ ls -lrt r*
-rwxr-xr-x 1 oracle oinstall 294 Jun 21 13:58 rmanbackup.sh
-rw-r--r-- 1 oracle oinstall 586 Jun 21 14:31 rmanhotbackup.sh
-rw-r--r-- 1 oracle oinstall 6195 Jun 21 14:34 rman_hot_backup_20120621.log
-rwxr-xr-x 1 oracle oinstall 301 Jun 21 14:42 rmanincbackup.sh
-rw-r--r-- 1 oracle oinstall 585 Jun 21 14:44 rmaninchotbackup.sh[oracle@lnx133 work]$ cat rmanincbackup.sh#!/bin/ksh
export ORACLE_SID=maze
export ORACLE_HOME=/u01/app/oracle/product/11g/11.2.0.2/dbhome_1
export PATH=$PATH:$ORACLE_HOME/bin
msglog=rman_hot_backup_`date +\%Y\%m\%d_\%T`.log
rman target / cmdfile=rmaninchotbackup.sh log=$msglog append
{
configure CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/app/oracle/backup/maze/ctl%d_%F';
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/backup/maze/sncf_%d_`date`';
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
backup tag HBlevel1 filesperset 10 format '/u01/app/oracle/backup/maze/Hot_Inc_%d_s%s_%U' as compressed backupset incremental level 1 cumulative database plus archivelog;
release channel c1;
release channel c2;
release channel c3;
}
exit;
[oracle@lnx133 work]$ nohup ./rmanincbackup.sh &[1] 3973
[oracle@lnx133 work]$ nohup: appending output to `nohup.out'
[oracle@lnx133 work]$ ls -lrt r*
-rwxr-xr-x 1 oracle oinstall 294 Jun 21 13:58 rmanbackup.sh
-rw-r--r-- 1 oracle oinstall 586 Jun 21 14:31 rmanhotbackup.sh
-rw-r--r-- 1 oracle oinstall 6195 Jun 21 14:34 rman_hot_backup_20120621.log
-rwxr-xr-x 1 oracle oinstall 301 Jun 21 14:42 rmanincbackup.sh
-rw-r--r-- 1 oracle oinstall 585 Jun 21 14:44 rmaninchotbackup.sh
-rw-r--r-- 1 oracle oinstall 6316 Jun 21 14:49 rman_hot_backup_20120621_14:45:42.logmonitoring the rman backup log
[oracle@lnx133 work]$ tail -f rman_hot_backup_20120621_14\:45\:42.logchannel c2: starting compressed incremental level 1 datafile backup set
channel c2: specifying datafile(s) in backup set
input datafile file number=00003 name=/u01/app/oracle/oradata1/maze/undotbs01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata1/maze/example01.dbf
channel c2: starting piece 1 at 21-JUN-12
channel c3: starting compressed incremental level 1 datafile backup set
channel c3: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata1/maze/system01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata1/maze/users01.dbf
channel c3: starting piece 1 at 21-JUN-12
channel c3: finished piece 1 at 21-JUN-12
piece handle=/u01/app/oracle/backup/maze/Hot_Inc_MAZE_s30_0une3lh5_1_1 tag=HBLEVEL1 comment=NONE
channel c3: backup set complete, elapsed time: 00:02:06
channel c2: finished piece 1 at 21-JUN-12
piece handle=/u01/app/oracle/backup/maze/Hot_Inc_MAZE_s29_0tne3lh5_1_1 tag=HBLEVEL1 comment=NONE
channel c2: backup set complete, elapsed time: 00:02:26
channel c1: finished piece 1 at 21-JUN-12
piece handle=/u01/app/oracle/backup/maze/Hot_Inc_MAZE_s28_0sne3lh5_1_1 tag=HBLEVEL1 comment=NONE
channel c1: backup set complete, elapsed time: 00:02:56
Finished backup at 21-JUN-12
Starting backup at 21-JUN-12
current log archived
channel c1: starting compressed archived log backup set
channel c1: specifying archived log(s) in backup set
input archived log thread=1 sequence=348 RECID=9 STAMP=786552534
channel c1: starting piece 1 at 21-JUN-12
channel c1: finished piece 1 at 21-JUN-12
piece handle=/u01/app/oracle/backup/maze/Hot_Inc_MAZE_s31_0vne3lmn_1_1 tag=HBLEVEL1 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 21-JUN-12
Starting Control File and SPFILE Autobackup at 21-JUN-12
piece handle=/u01/app/oracle/backup/maze/ctlMAZE_c-1370075061-20120621-03 comment=NONE
Finished Control File and SPFILE Autobackup at 21-JUN-12
released channel: c1
released channel: c2
released channel: c3
Recovery Manager complete.
[1]+ Done nohup ./rmanincbackup.sh
[oracle@lnx133 work]$ sql
SQL*Plus: Release 11.2.0.2.0 Production on Thu Jun 21 14:50:17 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> conn scott/abc123;
Connected.
SQL> select count(*) from worker2;
COUNT(*)
----------
196608
SQL> insert into worker2 select * from worker;
6 rows created.
SQL> commit;
Commit complete.
insert into worker2 select * from worker2
*
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/u01/app/oracle/oradata1/maze/users01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
SQL> conn / as sysdba
Connected.
SQL> shut immediate;
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/u01/app/oracle/oradata1/maze/users01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
SQL> shutdown abort;
ORACLE instance shut down.
SQL> exitDisconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@lnx133 work]$ sql
SQL*Plus: Release 11.2.0.2.0 Production on Thu Jun 21 14:52:30 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount;ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed Size 1344616 bytes
Variable Size 318770072 bytes
Database Buffers 96468992 bytes
Redo Buffers 6086656 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
We can/have to run the recovery from a script, but here I chose to run by connecting to rman & then issuing commands to restore/recover.
[oracle@lnx133 work]$ rman target /
Recovery Manager: Release 11.2.0.2.0 - Production on Thu Jun 21 14:52:57 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: MAZE (DBID=1370075061, not open)
RMAN> list backup summary;
using target database control file instead of recovery catalog
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
15 B A A DISK 21-JUN-12 1 1 YES HBLEVEL0
16 B A A DISK 21-JUN-12 1 1 YES HBLEVEL0
17 B A A DISK 21-JUN-12 1 1 YES HBLEVEL0
18 B A A DISK 21-JUN-12 1 1 YES HBLEVEL0
19 B 0 A DISK 21-JUN-12 1 1 YES HBLEVEL0
20 B 0 A DISK 21-JUN-12 1 1 YES HBLEVEL0
21 B 0 A DISK 21-JUN-12 1 1 YES HBLEVEL0
22 B A A DISK 21-JUN-12 1 1 YES HBLEVEL0
23 B F A DISK 21-JUN-12 1 1 NO TAG20120621T143442
24 B A A DISK 21-JUN-12 1 1 YES HBLEVEL1
25 B A A DISK 21-JUN-12 1 1 YES HBLEVEL1
26 B A A DISK 21-JUN-12 1 1 YES HBLEVEL1
27 B A A DISK 21-JUN-12 1 1 YES HBLEVEL1
28 B 1 A DISK 21-JUN-12 1 1 YES HBLEVEL1
29 B 1 A DISK 21-JUN-12 1 1 YES HBLEVEL1
30 B 1 A DISK 21-JUN-12 1 1 YES HBLEVEL1
31 B A A DISK 21-JUN-12 1 1 YES HBLEVEL1
32 B F A DISK 21-JUN-12 1 1 NO TAG20120621T144856
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=133 device type=DISK
channel ORA_DISK_1: starting datafile backup set restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata1/maze/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata1/maze/example01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup/maze/Hot_Base_MAZE_s20_0kne3klo_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/backup/maze/Hot_Base_MAZE_s20_0kne3klo_1_1 tag=HBLEVEL0
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:25
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata1/maze/system01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata1/maze/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup/maze/Hot_Base_MAZE_s21_0lne3klo_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/backup/maze/Hot_Base_MAZE_s21_0lne3klo_1_1 tag=HBLEVEL0
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:56
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata1/maze/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata1/maze/sj01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup/maze/Hot_Base_MAZE_s19_0jne3klo_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/backup/maze/Hot_Base_MAZE_s19_0jne3klo_1_1 tag=HBLEVEL0
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:35
Finished restore at 21-JUN-12Starting recover at 21-JUN-12using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/app/oracle/oradata1/maze/system01.dbf
destination for restore of datafile 00004: /u01/app/oracle/oradata1/maze/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup/maze/Hot_Inc_MAZE_s30_0une3lh5_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/backup/maze/Hot_Inc_MAZE_s30_0une3lh5_1_1 tag=HBLEVEL1
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
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 00003: /u01/app/oracle/oradata1/maze/undotbs01.dbf
destination for restore of datafile 00005: /u01/app/oracle/oradata1/maze/example01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup/maze/Hot_Inc_MAZE_s29_0tne3lh5_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/backup/maze/Hot_Inc_MAZE_s29_0tne3lh5_1_1 tag=HBLEVEL1
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
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 00002: /u01/app/oracle/oradata1/maze/sysaux01.dbf
destination for restore of datafile 00006: /u01/app/oracle/oradata1/maze/sj01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup/maze/Hot_Inc_MAZE_s28_0sne3lh5_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/backup/maze/Hot_Inc_MAZE_s28_0sne3lh5_1_1 tag=HBLEVEL1
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
starting media recovery
media recovery complete, elapsed time: 00:00:14
Finished recover at 21-JUN-12
sql statement: alter database open
RMAN> exitRecovery Manager complete.
2> restore database;
3> recover database;
4> sql 'alter database open';
5> }
Full restore complete of datafile
checkpoint is 8143741
last deallocation scn is 8133875
Undo Optimization current scn is 8139292
Thu Jun 21 14:55:40 2012
Full restore complete of datafile 5 /u01/app/oracle/oradata1/maze/example01.dbf. Elapsed time: 0:01:16
checkpoint is 8143741
last deallocation scn is 7542368
Full restore complete of datafile 4 /u01/app/oracle/oradata1/maze/users01.dbf. Elapsed time: 0:00:02
checkpoint is 8143742
last deallocation scn is 8142856
Thu Jun 21 14:57:35 2012
Full restore complete of datafile 1 /u01/app/oracle/oradata1/maze/system01.dbf. Elapsed time: 0:01:49
checkpoint is 8143742
last deallocation scn is 7355940
Undo Optimization current scn is 8139292
Thu Jun 21 14:58:08 2012
Full restore complete of datafile 6 /u01/app/oracle/oradata1/maze/sj01.dbf. Elapsed time: 0:00:20
checkpoint is 8143740
last deallocation scn is 770465
Thu Jun 21 15:00:10 2012
Full restore complete of datafile 2 /u01/app/oracle/oradata1/maze/sysaux01.dbf. Elapsed time: 0:02:24
checkpoint is 8143740
last deallocation scn is 8093014
Thu Jun 21 15:00:22 2012
Incremental restore complete of datafile
checkpoint is 8144777
last deallocation scn is 8142856
Incremental restore complete of datafile 1 /u01/app/oracle/oradata1/maze/system01.dbf
checkpoint is 8144777
last deallocation scn is 7355940
Incremental restore complete of datafile 5 /u01/app/oracle/oradata1/maze/example01.dbf
checkpoint is 8144776
last deallocation scn is 7542368
Incremental restore complete of datafile 3 /u01/app/oracle/oradata1/maze/undotbs01.dbf
checkpoint is 8144776
last deallocation scn is 8133875
Incremental restore complete of datafile 6 /u01/app/oracle/oradata1/maze/sj01.dbf
checkpoint is 8144775
last deallocation scn is 770465
Incremental restore complete of datafile 2 /u01/app/oracle/oradata1/maze/sysaux01.dbf
checkpoint is 8144775
last deallocation scn is 8093014
Thu Jun 21 15:00:31 2012
alter database recover datafile list clear
Completed: alter database recover datafile list clearalter database recover datafile list
1 , 2 , 3 , 4 , 5 , 6
Completed: alter database recover datafile list
1 , 2 , 3 , 4 , 5 , 6
alter database recover if needed
start
Media Recovery Startstarted logmerger process
Parallel Media Recovery started with 2 slaves
Thu Jun 21 15:00:39 2012
Recovery of Online Redo Log: Thread 1 Group 3 Seq 348 Reading mem 0
Mem# 0: /u01/app/oracle/oradata1/maze/redo03.log
Recovery of Online Redo Log: Thread 1 Group 1 Seq 349 Reading mem 0
Mem# 0: /u01/app/oracle/oradata1/maze/redo01.log
Thu Jun 21 15:00:45 2012
Media Recovery Complete (maze)
Completed: alter database recover if needed
start
alter database openBeginning crash recovery of 1 threads
parallel recovery started with 2 processes
Started redo scan
Completed redo scan
read 0 KB redo, 0 data blocks need recovery
Started redo application at
Thread 1: logseq 349, block 154, scn 8145023
Recovery of Online Redo Log: Thread 1 Group 1 Seq 349 Reading mem 0
Mem# 0: /u01/app/oracle/oradata1/maze/redo01.log
Completed redo application of 0.00MB
Completed crash recovery at
Thread 1: logseq 349, block 154, scn 8165024
0 data blocks read, 0 data blocks written, 0 redo k-bytes read
Thu Jun 21 15:00:48 2012
LGWR: STARTING ARCH PROCESSES
Thu Jun 21 15:00:48 2012
ARC0 started with pid=24, OS id=4258
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Thu Jun 21 15:00:49 2012
ARC1 started with pid=25, OS id=4260
Thu Jun 21 15:00:49 2012
ARC2 started with pid=26, OS id=4262
ARC1: Archival started
ARC2: Archival started
Thu Jun 21 15:00:49 2012
ARC3 started with pid=27, OS id=4264
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
ARC2: Becoming the heartbeat ARCH
Thread 1 advanced to log sequence 350 (thread open)
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Thread 1 opened at log sequence 350
Current log# 2 seq# 350 mem# 0: /u01/app/oracle/oradata1/maze/redo02.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Thu Jun 21 15:00:50 2012
SMON: enabling cache recovery
Archived Log entry 10 added for thread 1 sequence 349 ID 0x51a9aeb5 dest 1:
Thu Jun 21 15:00:56 2012
[4206] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:447383434 end:447385294 diff:1860 (18 seconds)
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is AL32UTF8
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Thu Jun 21 15:01:05 2012
QMNC started with pid=28, OS id=4268
Thu Jun 21 15:01:13 2012
Completed: alter database open
Thu Jun 21 15:01:20 20124 /u01/app/oracle/oradata1/maze/users01.dbf3 /u01/app/oracle/oradata1/maze/undotbs01.dbf. Elapsed time: 0:00:42
Once the database is open, connecting to verify records.
SQL> select count(*) from scott.worker2;COUNT(*)
----------
196614
SQL> conn scott/abc123;
Connected.
SQL> insert into worker2 select * from worker2;
196614 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from scott.worker2;
COUNT(*)
----------
393228
Confirms we have recovered the failure.
Please refer to oracle documentation for a complete/accurate information.
No comments:
Post a Comment