RAMN备份与恢复（完全恢复）

最新推荐文章于 2025-02-25 12:29:47 发布

cluz63733

最新推荐文章于 2025-02-25 12:29:47 发布

阅读量428

点赞数

文章标签：数据库操作系统数据结构与算法

RAMN备份与恢复（完全恢复）

这一部分主要是实验操作，主要从两方面来讲，一个是rman的完全恢复，另一个是不完全恢复。在这里再说明一点，我用的都是备份集而不是映像副本。映像副本会在用户自定义备份里面详细介绍。

1. 备份全库

非常简单 backup database或者backup database plus archivelog delete input; (备份全库及控制文件、服务器参数文件与所有归档的重做日志，并删除旧的归档日志)

这里有一点说明一下，当你打开

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP on;

new RMAN configuration parameters:

CONFIGURE CONTROLFILE AUTOBACKUP ON;

new RMAN configuration parameters are successfully stored

这时候，RMAN 会在BACKUP 命令后自动备份控制文件和当前服务器参数文件，或者在数据结构改变的时候。

Backup database plus archivelog

太长不截图了。这里由于我启动了闪回区功能，所以会把备份集自动放到该flash_recovery_area/目录下。当然你也可以手工指定要放置的地方以及备份文件的格式。

可以通过list backup ;查看已经做好的备份

RMAN> list backup ;

List of Backup Sets

===================

BS Key Size Device Type Elapsed Time Completion Time

------- ---------- ----------- ------------ ---------------

1 39.68M DISK 00:00:06 20-NOV-10

BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20101120T055028

Piece Name: /opt/ora10g/flash_recovery_area/INOMC/backupset/2010_11_20/o1_mf_annnn_TAG20101120T055028_6gfw95tg_.bkp

List of Archived Logs in backup set 1

Thrd Seq Low SCN Low Time Next SCN Next Time

---- ------- ---------- --------- ---------- ---------

1 28 335048 14-OCT-10 359333 19-NOV-10

1 29 359333 19-NOV-10 390539 20-NOV-10

1 30 390539 20-NOV-10 396089 20-NOV-10

BS Key Type LV Size Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

2 Full 685.83M DISK 00:01:36 20-NOV-10

BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20101120T055037

Piece Name: /opt/ora10g/flash_recovery_area/INOMC/backupset/2010_11_20/o1_mf_nnndf_TAG20101120T055037_6gfw9gm5_.bkp

List of Datafiles in backup set 2

File LV Type Ckp SCN Ckp Time Name

---- -- ---- ---------- --------- ----

1 Full 396097 20-NOV-10 /opt/ora10g/oradata/inomc/system01.dbf

2 Full 396097 20-NOV-10 /opt/ora10g/oradata/inomc/undotbs01.dbf

3 Full 396097 20-NOV-10 /opt/ora10g/oradata/inomc/sysaux01.dbf

4 Full 396097 20-NOV-10 /opt/ora10g/oradata/inomc/users01.dbf

5 Full 396097 20-NOV-10 /opt/ora10g/datafile/text01-1.dbf

BS Key Size Device Type Elapsed Time Completion Time

------- ---------- ----------- ------------ ---------------

3 10.50K DISK 00:00:01 20-NOV-10

BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20101120T055224

Piece Name: /opt/ora10g/flash_recovery_area/INOMC/backupset/2010_11_20/o1_mf_annnn_TAG20101120T055224_6gfwdsod_.bkp

List of Archived Logs in backup set 3

Thrd Seq Low SCN Low Time Next SCN Next Time

---- ------- ---------- --------- ---------- ---------

1 31 396089 20-NOV-10 396144 20-NOV-10

BS Key Type LV Size Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

4 Full 6.80M DISK 00:00:01 20-NOV-10

BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20101120T055227

Piece Name: /opt/ora10g/flash_recovery_area/INOMC/autobackup/2010_11_20/o1_mf_s_735544347_6gfwdwnx_.bkp

Control File Included: Ckp SCN: 396150 Ckp time: 20-NOV-10

SPFILE Included: Modification time: 20-NOV-10

这里你还可以指定备份表空间

Backup tablespace text01;

数据文件，控制文件，参数文件等等……

这里再废话一下介绍一下增量备份的概念

差异备份Differential

简单一点说，0级备份是全备份，在星期天晚上做，然后在星期一、二做二级增量备份，星期一会做自星期天以来的增来备份，星期二因为是Differential备份机制，只会备份自星期一以来的增量的备份。然后是星期三的一级备份，因为前面都是二级备份，那么会备份在星期日以来的增量。

累积增量备份Cumulative

简单一点说，0级备份是全备份，在星期天晚上做，然后在星期一、二做二级增量备份，星期一会做自星期天以来的增来备份，星期二因为是Cumulative备份机制，他会备份自星期日以来的增量的备份。然后是星期三的一级备份，因为前面都是二级备份，那么会备份在星期日以来的增量。

2. 数据库恢复

当数据文件出现介质失败时，需要使用restore命令转储备份，使用recovery命令进行恢复。除了system表空间之外，其它数据文件都可以在open状态下恢复。

由于一般情况下都是在数据库打开下，数据文件丢失，我们就会在数据打开的情况下模拟，参数文件、控制文件，system数据文件，普通数据文件丢失来恢复数据库。

i. 参数文件丢失

a、先做个备份

[oracle@field dbs]$ cp spfileinomc.ora spfileinomc.ora.bak

b、删除spfile

[oracle@field dbs]$ rm spfileinomc.ora

c、修改参数

alter system set rescource_limit=true ;

直接报错

alter system set resource_limit=true

ERROR at line 1:

ORA-01565: error in identifying file

'/opt/ora10g/product/10.2.0/db_1/dbs/spfileinomc.ora'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

d、查看rman关于spifle的备份

RMAN> list backup of spfile;

List of Backup Sets

===================

BS Key Type LV Size Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

4 Full 6.80M DISK 00:00:01 20-NOV-10

BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20101120T055227

Piece Name: /opt/ora10g/flash_recovery_area/INOMC/autobackup/2010_11_20/o1_mf_s_735544347_6gfwdwnx_.bkp

SPFILE Included: Modification time: 20-NOV-10

RMAN>

e、恢复控制文件

RMAN> restore spfile to '/opt/ora10g/product/10.2.0/db_1/dbs/spfile.ora';

Starting restore at 20-NOV-10

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore

channel ORA_DISK_1: restoring SPFILE

output filename=/opt/ora10g/product/10.2.0/db_1/dbs/spfile.ora

channel ORA_DISK_1: reading from backup piece /opt/ora10g/flash_recovery_area/INOMC/autobackup/2010_11_20/o1_mf_s_735544347_6gfwdwnx_.bkp

channel ORA_DISK_1: restored backup piece 1

piece handle=/opt/ora10g/flash_recovery_area/INOMC/autobackup/2010_11_20/o1_mf_s_735544347_6gfwdwnx_.bkp tag=TAG20101120T055227

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

Finished restore at 20-NOV-10

好了，已经成功了。这样比较麻烦，如果在打开状态下丢失了直接 create spfile from memory;就可以了，总之控制文件丢失不是什么大问题。

ii. 控制文件丢失

a、查看控制文件的位置

QL> show parameter control

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

control_file_record_keep_time integer 7

control_files string /opt/ora10g/oradata/inomc/control01.ctl, /opt/ora10g/oradata/inomc/control02.ctl, /opt/ora10g/orada

b、删除其中一个

[oracle@field inomc]$ rm control01.ctl

c、创建表空间text02

SQL> create tablespace text02

2 datafile '/opt/ora10g/oradata/inomc/text02.dbf'

3 size 5m;

create tablespace text02

datafile '/opt/ora10g/oradata/inomc/text02.dbf'

size 5m

ORA-00210: cannot open the specified control file

ORA-00202: control file: '/opt/ora10g/oradata/inomc/control01.ctl'

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

大家看到报错了

d、恢复控制文件

RMAN> restore controlfile to '/opt/ora10g/oradata/inomc/control01.ctl';

Starting restore at 20-NOV-10

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of restore command at 11/20/2010 07:57:38

ORA-19607: /opt/ora10g/oradata/inomc/control01.ctl is an active control file

报错，因为我们要恢复的控制文件是 control01.ctl正在活动的。

e、修改参数文件中的控制文件参数

SQL> set system set control_files='/opt/ora10g/oradata/inomc/control02.ctl' scope=spfile;

Cannot SET SYSTEM

又失败，看来是不能在打开的状态下恢复控制文件了，只能关闭数据库，然后用老一套的办法恢复了。

f、关闭数据库

SQL> shutdown immediately

SP2-0717: illegal SHUTDOWN option

SQL> shutdown immediate

ORA-00210: cannot open the specified control file

ORA-00202: control file: '/opt/ora10g/oradata/inomc/control01.ctl'

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

还是报错，数据库打开的状况下丢失数据文件真是麻烦啊

强行关闭

Shutdown abort

复制控制文件

[oracle@field inomc]$ cp control02.ctl control01.ctl

打开数据库

成功

这里我们可以假设一下，如果控制文件全部丢失，那么可以使用rman 恢复控制文件至一个new的location，然后强行关闭数据库，再打开至nomount状态，修改控制文件参数，使用新的位置的控制文件打开数据库。

iii. 丢失system表空间的数据文件

因为这个是系统表空间的数据文件，一丢失数据库就会报错

看来书上胡说，我删除了system01.dbf这个数据文件后数据库并没有立即关闭，而且关闭的时候又报错了只能abort了。

a、启动到mount状态

SQL> startup mount

ORACLE instance started.

Total System Global Area 167772160 bytes

Fixed Size 1218316 bytes

Variable Size 88082676 bytes

Database Buffers 75497472 bytes

Redo Buffers 2973696 bytes

Database mounted.

b、使用rman恢复

RMAN> restore database;

Starting restore at 20-NOV-10

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=155 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00001 to /opt/ora10g/oradata/inomc/system01.dbf

restoring datafile 00002 to /opt/ora10g/oradata/inomc/undotbs01.dbf

restoring datafile 00003 to /opt/ora10g/oradata/inomc/sysaux01.dbf

restoring datafile 00004 to /opt/ora10g/oradata/inomc/users01.dbf

restoring datafile 00005 to /opt/ora10g/datafile/text01-1.dbf

channel ORA_DISK_1: reading from backup piece /opt/ora10g/flash_recovery_area/INOMC/backupset/2010_11_20/o1_mf_nnndf_TAG20101120T055037_6gfw9gm5_.bkp

channel ORA_DISK_1: restored backup piece 1

piece handle=/opt/ora10g/flash_recovery_area/INOMC/backupset/2010_11_20/o1_mf_nnndf_TAG20101120T055037_6gfw9gm5_.bkp tag=TAG20101120T055037

channel ORA_DISK_1: restore complete, elapsed time: 00:01:27

Finished restore at 20-NOV-10

e、恢复数据库

RMAN> recover database;

Starting recover at 20-NOV-10

using channel ORA_DISK_1

starting media recovery

media recovery complete, elapsed time: 00:00:17

Finished recover at 20-NOV-10

RMAN>

f、打开数据库并检查

SQL> alter database open ;

Database altered.

SQL> desc lsy.students;

Name Null? Type

----------------------------------------- -------- ----------------------------

SNO NOT NULL NUMBER(6)

SNAME NOT NULL CHAR(8)

SEX CHAR(8)

AGE NUMBER(3)

BPLACE CHAR(20)

SQL>

好了，搞定

iv. 普通数据文件丢失

普通数据文件丢失就更不会是数据库关闭了，他只会让有关于那个数据文件的查询失败。

a、删除text01表空间的数据文件text01.dbf

SQL> select name from v$datafile where FILE#=5;

NAME

--------------------------------------------------------------------------------

/opt/ora10g/datafile/text01-1.dbf

[oracle@field datafile]$ rm text01-1.dbf

c、在该数据文件上插入表数据

SQL> insert into STUDENTS (Sno,Sname,sex,age,bplace) values ('95029','赵新 ','男',20,'新疆') ;

insert into STUDENTS (Sno,Sname,sex,age,bplace) values ('95029','赵新 ','男',20,'新疆')

ORA-01116: error in opening database file 5

ORA-01110: data file 5: '/opt/ora10g/datafile/text01-1.dbf'

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

当然，报错了

d、在打开的情况下恢复，并且假设磁盘损坏，需要转储到其它的磁盘

SQL> alter database datafile 5 offline;

Database altered.

e、检查数据文件副本状态

RMAN> crosscheck copy;

released channel: ORA_DISK_1

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=158 devtype=DISK

validation failed for datafile copy

datafile copy filename=/opt/ora10g/oradata/inomc/text01.dbf recid=9 stamp=735557203