这是RMAN备份第一篇,主要演示归档模式,nocatalog有RMAN全备的情况下模拟全部数据文件(包括控制文件和参数文件)丢书的情况下如何进行恢复数据库(由于所有redo丢失,所以只能进行不完全恢复),关于RMAN的原理,配置等基础知识后续博文继续探讨。
数据库版本:
11.2.0.3
先对数据库进行全备,执行以下脚本(注意,一定要全备--0级备份):
RMAN nocatalog target /
run
{
configure device type disk parallelism 4;
backup incremental level= 0 skip inaccessible filesperset 5 Database format='/u01/backup/EDISON_lev0_%U_%T' tag='EDISON_lev0';
sql 'alter system archive log current';
backup archivelog all tag='arc_bak' format='/u01/backup/arch_%U_%T' skip inaccessible filesperset 5 not backed up 1 times delete input;
backup current controlfile tag='bak_ctlfile' format='/u01/backup/ctl_file_%U_%T';
backup spfile tag='spfile' format='/u01/backup/EDISON_spfile_%U_%T';
}
查看备份信息:
RMAN> list backup summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
61 B 0 A DISK 02-MAR-14 1 1 NO EDISON_LEV0
62 B 0 A DISK 02-MAR-14 1 1 NO EDISON_LEV0
63 B 0 A DISK 02-MAR-14 1 1 NO EDISON_LEV0
64 B A A DISK 02-MAR-14 1 1 NO ARC_BAK
65 B A A DISK 02-MAR-14 1 1 NO ARC_BAK
66 B F A DISK 02-MAR-14 1 1 NO BAK_CTLFILE
67 B F A DISK 02-MAR-14 1 1 NO SPFILE
首先确保备份是有效的,这一步意义,不用说了吧,每次备份完之后都是要检查的,并且定期要检查的。
RMAN> restore database validate;
Starting restore at 03-MAR-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=36 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=37 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=38 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=39 device type=DISK
channel ORA_DISK_1: starting validation of datafile backup set
channel ORA_DISK_1: reading from backup piece /s01/oracle/oradata/athena/EDISON/EDISON_lev0_2bp25ocv_1_1_20140302
channel ORA_DISK_2: starting validation of datafile backup set
channel ORA_DISK_2: reading from backup piece /s01/oracle/oradata/athena/EDISON/EDISON_lev0_2ap25ocv_1_1_20140302
channel ORA_DISK_3: starting validation of datafile backup set
channel ORA_DISK_3: reading from backup piece /s01/oracle/oradata/athena/EDISON/EDISON_lev0_29p25ocv_1_1_20140302
channel ORA_DISK_1: piece handle=/s01/oracle/oradata/athena/EDISON/EDISON_lev0_2bp25ocv_1_1_20140302 tag=EDISON_LEV0
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:15
channel ORA_DISK_2: piece handle=/s01/oracle/oradata/athena/EDISON/EDISON_lev0_2ap25ocv_1_1_20140302 tag=EDISON_LEV0
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: validation complete, elapsed time: 00:00:45
channel ORA_DISK_3: piece handle=/s01/oracle/oradata/athena/EDISON/EDISON_lev0_29p25ocv_1_1_20140302 tag=EDISON_LEV0
channel ORA_DISK_3: restored backup piece 1
channel ORA_DISK_3: validation complete, elapsed time: 00:00:55
Finished restore at 03-MAR-14
RMAN>
查看备份位置:
[oracle@Ora11gTest EDISON]$ pwd
/s01/oracle/oradata/athena/EDISON
[oracle@Ora11gTest EDISON]$ ll
total 1311636
-rw-r----- 1 oracle oinstall 629760 Mar 2 11:55 arch_2ep25oh0_1_1_20140302
-rw-r----- 1 oracle oinstall 2560 Mar 2 11:55 arch_2fp25oh0_1_1_20140302
-rw-r----- 1 oracle oinstall 10420224 Mar 2 11:55 ctl_file_2gp25oh1_1_1_20140302
-rw-r----- 1 oracle oinstall 669696000 Mar 2 11:55 EDISON_lev0_29p25ocv_1_1_20140302
-rw-r----- 1 oracle oinstall 522838016 Mar 2 11:54 EDISON_lev0_2ap25ocv_1_1_20140302
-rw-r----- 1 oracle oinstall 96133120 Mar 2 11:54 EDISON_lev0_2bp25ocv_1_1_20140302
-rw-r----- 1 oracle oinstall 98304 Mar 2 11:55 EDISON_spfile_2hp25oh3_1_1_20140302
[oracle@Ora11gTest EDISON]$
有了上面的准备,就可以放心折腾了,我们模拟故障,删除所有数据库文件:
[oracle@Ora11gTest athena]$ pwd
/s01/oracle/oradata/athena
[oracle@Ora11gTest athena]$ ll
total 2250264
-rw-r----- 1 oracle oinstall 10371072 Mar 3 15:13 control01.ctl
-rw-r----- 1 oracle oinstall 362422272 Mar 3 14:53 example01.dbf
-rw-r----- 1 oracle oinstall 52429312 Mar 3 14:53 redo01.log
-rw-r----- 1 oracle oinstall 52429312 Mar 3 15:11 redo02.log
-rw-r----- 1 oracle oinstall 52429312 Mar 3 14:53 redo03.log
-rw-r----- 1 oracle oinstall 608182272 Mar 3 15:09 sysaux01.dbf
-rw-r----- 1 oracle oinstall 775954432 Mar 3 15:09 system01.dbf
-rw-r----- 1 oracle oinstall 20979712 Mar 2 11:44 temp01.dbf
-rw-r----- 1 oracle oinstall 298852352 Mar 3 15:09 undotbs01.dbf
-rw-r----- 1 oracle oinstall 87826432 Mar 3 14:53 users01.dbf
[oracle@Ora11gTest athena]$ rm -rf *
[oracle@Ora11gTest athena]$ll
total 0
[oracle@Ora11gTest athena]$
现在,全部数据库文件已经已经删了,但数据库仍然是启动状态,这时查看数据库:
[oracle@Ora11gTest athena]$ exit
exit
SQL>select status from v$instance; --内存数据
STATUS
------------
OPEN
SQL> shutdown immediate; --找不到控制文件,数据库已挂,无法正常关闭!
Database closed.
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/s01/oracle/oradata/athena/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL>
--内存中的数据。
接下来开始恢复数据库:
由于控制文件也丢失了,rman的备份信息也全部记录在控制文件中,要想恢复库,先必须得恢复控制文件。
先连接rman:
[oracle@Ora11gTest ~]$ rman target / --坑吧,连接不上rman!
Recovery Manager: Release 11.2.0.3.0 - Production on Mon Mar 3 15:27:13 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04005: error from target database:
ORA-01089: immediate shutdown in progress - no operations are permitted
[oracle@Ora11gTest ~]$
强制关闭数据库:
SQL> shutdown abort;
ORACLE instance shut down.
SQL>
再连接rman:
[oracle@Ora11gTest ~]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Mon Mar 3 15:31:58 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN> startup nomount; --启动数据库到nomount状态
Oracle instance started
Total System Global Area 1068937216 bytes
Fixed Size 2235208 bytes
Variable Size 662701240 bytes
Database Buffers 398458880 bytes
Redo Buffers 5541888 bytes
RMAN> restore controlfile from autobackup; --从自动备份中恢复控制文件失败
Starting restore at 03-MAR-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
recovery area destination: /s01/oracle/fast_recovery_area
database name (or database unique name) used for search: ATHENA
channel ORA_DISK_1: no AUTOBACKUPS found in the recovery area
AUTOBACKUP search with format "%F" not attempted because DBID was not set
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 03/03/2014 15:35:31
RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece
--指定备份的控制文件恢复控制文件,成功!
RMAN> restore controlfile from '/s01/oracle/oradata/athena/EDISON/ctl_file_2gp25oh1_1_1_20140302';
Starting restore at 03-MAR-14
using channel ORA_DISK_1
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/s01/oracle/oradata/athena/control01.ctl
output file name=/s01/oracle/fast_recovery_area/athena/control02.ctl
Finished restore at 03-MAR-14
RMAN>
--现在启动数据库到mount状态
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN>
--复原数据库
RMAN> restore database;
Starting restore at 03-MAR-14
Starting implicit crosscheck backup at 03-MAR-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=20 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=21 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=22 device type=DISK
Crosschecked 5 objects
Crosschecked 2 objects
Finished implicit crosscheck backup at 03-MAR-14
Starting implicit crosscheck copy at 03-MAR-14
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
Finished implicit crosscheck copy at 03-MAR-14
searching for all files in the recovery area
cataloging files...
no files cataloged
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
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 00003 to /s01/oracle/oradata/athena/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /s01/oracle/oradata/athena/example01.dbf
channel ORA_DISK_1: reading from backup piece /s01/oracle/oradata/athena/EDISON/EDISON_lev0_2bp25ocv_1_1_20140302
channel ORA_DISK_2: starting datafile backup set restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_DISK_2: restoring datafile 00002 to /s01/oracle/oradata/athena/sysaux01.dbf
channel ORA_DISK_2: restoring datafile 00004 to /s01/oracle/oradata/athena/users01.dbf
channel ORA_DISK_2: reading from backup piece /s01/oracle/oradata/athena/EDISON/EDISON_lev0_2ap25ocv_1_1_20140302
channel ORA_DISK_3: starting datafile backup set restore
channel ORA_DISK_3: specifying datafile(s) to restore from backup set
channel ORA_DISK_3: restoring datafile 00001 to /s01/oracle/oradata/athena/system01.dbf
channel ORA_DISK_3: reading from backup piece /s01/oracle/oradata/athena/EDISON/EDISON_lev0_29p25ocv_1_1_20140302
channel ORA_DISK_1: piece handle=/s01/oracle/oradata/athena/EDISON/EDISON_lev0_2bp25ocv_1_1_20140302 tag=EDISON_LEV0
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
channel ORA_DISK_2: piece handle=/s01/oracle/oradata/athena/EDISON/EDISON_lev0_2ap25ocv_1_1_20140302 tag=EDISON_LEV0
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:01:25
channel ORA_DISK_3: piece handle=/s01/oracle/oradata/athena/EDISON/EDISON_lev0_29p25ocv_1_1_20140302 tag=EDISON_LEV0
channel ORA_DISK_3: restored backup piece 1
channel ORA_DISK_3: restore complete, elapsed time: 00:01:35
Finished restore at 03-MAR-14
RMAN>
--recover 数据库,由于redo日志全部丢失,因此只能做不完全恢复。
RMAN> recover database;
archived log thread=1 sequence=3
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/02/2014 14:58:57
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 3 and starting SCN of 1277831
RMAN>
--open数据库,由于控制文件和redo的丢失因此不完全恢复后不能正常open。
RMAN> alter database open;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 03/03/2014 15:47:39
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
RMAN>
--以resetlogs方式open库,还是提示sys数据文件没有完全复原!
RMAN> alter database open RESETLOGS;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 03/03/2014 15:49:56
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/s01/oracle/oradata/athena/system01.dbf'
RMAN>
--查看数据库状态:
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
SQL>
--以下查看scn recover数据库:
RMAN> list backup of archivelog all;
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
64 614.50K DISK 00:00:00 02-MAR-14
BP Key: 64 Status: AVAILABLE Compressed: NO Tag: ARC_BAK
Piece Name: /s01/oracle/oradata/athena/EDISON/arch_2ep25oh0_1_1_20140302
List of Archived Logs in backup set 64
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 33 4530216 02-MAR-14 4531615 02-MAR-14 --备份中归档1的scn 4530216-4531615
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
65 2.00K DISK 00:00:00 02-MAR-14
BP Key: 65 Status: AVAILABLE Compressed: NO Tag: ARC_BAK
Piece Name: /s01/oracle/oradata/athena/EDISON/arch_2fp25oh0_1_1_20140302
List of Archived Logs in backup set 65
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 34 4531615 02-MAR-14 4531623 02-MAR-14 --备份中归档2的scn 4531615-4531623
RMAN>
SQL> select max(checkpoint_change#) from v$datafile_header; --数据文件中最大scn号4531554
MAX(CHECKPOINT_CHANGE#)
-----------------------
4531554
SQL>
SQL> select checkpoint_change#,current_scn from v$database; --控制文件中最大scn号为4530216
CHECKPOINT_CHANGE# CURRENT_SCN
------------------ -----------
4530216 0
RMAN> recover database until scn 4531623; --按照最新的归档scn号recover数据库!
Starting recover at 03-MAR-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=21 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=22 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=23 device type=DISK
starting media recovery
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=33
channel ORA_DISK_1: reading from backup piece /s01/oracle/oradata/athena/EDISON/arch_2ep25oh0_1_1_20140302
channel ORA_DISK_2: starting archived log restore to default destination
channel ORA_DISK_2: restoring archived log
archived log thread=1 sequence=34
channel ORA_DISK_2: reading from backup piece /s01/oracle/oradata/athena/EDISON/arch_2fp25oh0_1_1_20140302
channel ORA_DISK_1: piece handle=/s01/oracle/oradata/athena/EDISON/arch_2ep25oh0_1_1_20140302 tag=ARC_BAK
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/s01/oracle/fast_recovery_area/ATHENA/archivelog/2014_03_03/o1_mf_1_33_9k8h96xh_.arc thread=1 sequence=33
channel default: deleting archived log(s)
archived log file name=/s01/oracle/fast_recovery_area/ATHENA/archivelog/2014_03_03/o1_mf_1_33_9k8h96xh_.arc RECID=74 STAMP=841249702
channel ORA_DISK_2: piece handle=/s01/oracle/oradata/athena/EDISON/arch_2fp25oh0_1_1_20140302 tag=ARC_BAK
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:00:02
archived log file name=/s01/oracle/fast_recovery_area/ATHENA/archivelog/2014_03_03/o1_mf_1_34_9k8h96xt_.arc thread=1 sequence=34
channel default: deleting archived log(s)
archived log file name=/s01/oracle/fast_recovery_area/ATHENA/archivelog/2014_03_03/o1_mf_1_34_9k8h96xt_.arc RECID=73 STAMP=841249702
media recovery complete, elapsed time: 00:00:00
Finished recover at 03-MAR-14
RMAN>
然后resetlogs方式open数据库!
RMAN> alter database open RESETLOGS;
database opened
RMAN>
--查看数据库文件,数据库状态
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
SQL>
SQL> !
[oracle@Ora11gTest ~]$ cd /s01/oracle/oradata/athena/
[oracle@Ora11gTest athena]$ ls
control01.ctl example01.dbf redo01.log redo02.log redo03.log sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf
[oracle@Ora11gTest athena]$
ok,至此数据库已经恢复。然后再对数据库进行全备~