rman恢复示例

来源：互联网发布：网络的坏处英文作文编辑：程序博客网时间：2024/06/07 19:19

《三思笔记》--读书笔记

linux下

首先执行备份脚本

如下：

[oracle@cindy rman_backup]$ /u01/oracle/rman_backup/back_jiaoben.shRMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14>

back_jiaoben.sh的内容如下：

rman target / msglog /u01/oracle/rman_backup/` date +"%Y%m%d" `.log cmdfile=/u01/oracle/rman_backup/back_jiaoben.rman

back_jiaoben.rman的内容如下：

run{configure retention policy to recovery window of 14 days;configure controlfile autobackup on;configure controlfile autobackup format for device type disk to '/u01/oracle/rman_backup/%F';allocate channel c1 device type disk format '/u01/oracle/rman_backup/bak_%U';backup database skip inaccessible    plus archivelog filesperset 20    delete all input;release channel c1;}allocate channel for maintenance device type disk;crosscheck backupset;delete noprompt obsolete;

然后查看备份的内容，如下：

[oracle@cindy rman_backup]$ ls20140902.log  back_jiaoben.rman  back_jiaoben.sh  bak_2pphf9id_1_1  bak_2qphf9if_1_1  bak_2rphf9ok_1_1  c-4252637343-20140902-03

------------------------------------------------------------华丽的分割线--------------------上面是备份------------下面的模拟的各种恢复---------------------------------------
接下来模拟各种情况下恢复数据库的情况

归档模式有备份，丢失数据文件的恢复

1，构造数据

[oracle@cindy ~]$ sqlplus scott/tiger;SQL*Plus: Release 11.2.0.3.0 Production on Tue Sep 2 03:53:14 2014Copyright (c) 1982, 2011, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> create table tmp2(name varchar2(20));Table created.SQL> insert into tmp2 values ('test1');1 row created.SQL> insert into tmp2 values ('test2');1 row created.SQL> insert into tmp2 values ('test3');1 row created.SQL> commit;Commit complete.

2，模拟文件丢失

以sysdba身份登录并关闭数据库

SQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.

然后手动删除数据文件

[oracle@cindy ora11g]$ cd /u02/oradata/ora11g[oracle@cindy ora11g]$ lscindy01.dbf  control01.ctl  example01.dbf  redo01.log  redo02.log  redo03.log  sysaux01.dbf  system01.dbf  temp01.dbf  undotbs01.dbf  users01.dbf[oracle@cindy ora11g]$ rm users01.dbf

重启数据库

[oracle@cindy ora11g]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.3.0 Production on Tue Sep 2 06:17:51 2014Copyright (c) 1982, 2011, Oracle.  All rights reserved.Connected to an idle instance.SQL> startupORACLE instance started.Total System Global Area  417546240 bytesFixed Size                  2228944 bytesVariable Size             322964784 bytesDatabase Buffers           88080384 bytesRedo Buffers                4272128 bytesDatabase mounted.ORA-01157: cannot identify/lock data file 4 - see DBWR trace fileORA-01110: data file 4: '/u02/oradata/ora11g/users01.dbf'

此时报错，找不到数据文件，此时数据库处于mount状态

3，执行恢复

重新打开一个窗口，执行rman

[oracle@cindy ~]$ rman target /Recovery Manager: Release 11.2.0.3.0 - Production on Tue Sep 2 06:24:19 2014Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.connected to target database: ORA11G (DBID=4252637343, not open)RMAN> restore datafile 4;Starting restore at 02-SEP-14using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=20 device type=DISKchannel ORA_DISK_1: starting datafile backup set restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setchannel ORA_DISK_1: restoring datafile 00004 to /u02/oradata/ora11g/users01.dbfchannel ORA_DISK_1: reading from backup piece /u01/oracle/rman_backup/bak_2qphf9if_1_1channel ORA_DISK_1: piece handle=/u01/oracle/rman_backup/bak_2qphf9if_1_1 tag=TAG20140902T034518channel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete, elapsed time: 00:00:07Finished restore at 02-SEP-14RMAN> recover datafile 4;Starting recover at 02-SEP-14using channel ORA_DISK_1starting media recoverymedia recovery complete, elapsed time: 00:00:00Finished recover at 02-SEP-14RMAN> alter database open;database opened

切换到sql，查询数据

[oracle@cindy ~]$ sqlplus scott/tigerSQL*Plus: Release 11.2.0.3.0 Production on Tue Sep 2 06:26:32 2014Copyright (c) 1982, 2011, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> select * from tmp2;NAME--------------------test1test2test3

数据都回来了

ps:用来查询表空间及其关联的数据文件

SELECT A.ABLESPACE_NAME, B.FILE_ID, B.FILE_NAME, B.STATUS
FROM DBA_TABLESPACES A,
(SELECT TABLESPACE_NAME, FILE_ID, FILE_NAME, STATUS
FROM DBA_DATA_FILES
UNION
SELECT TABLESPACE_NAME, FILE_ID, FILE_NAME, STATUS
FROM DBA_TMP_FILES) B
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME;

归档模式无备份，丢失数据文件的恢复

如system表空间的数据文件丢失或损坏，除非借助备份，否则无法直接恢复，如果是临时表空间的数据文件丢失，不需要恢复，重建一个即可

1，创建新的表空间cindy02

确认处于归档模式

SQL> archive log listDatabase log mode              Archive ModeAutomatic archival             EnabledArchive destination            /u02/arch_logOldest online log sequence     9Next log sequence to archive   11Current log sequence           11

SQL> create tablespace cindy04 datafile '/u02/oradata/ora11g/cindy04.dbf' size 100m;Tablespace created.SQL> create user cindy04 identified by cindy default tablespace cindy04;User created.SQL> grant connect,resource to cindy04;Grant succeeded.SQL> conn cindy04/cindy;Connected.SQL> create table test(name varchar2(20));Table created.SQL> insert into test values ('acd');1 row created.SQL> commit;Commit complete.

2，关闭数据库，手动删除数据文件

SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.

[oracle@cindy ~]$ cd /u02[oracle@cindy u02]$ lsarch_log  oradata  rmanbak[oracle@cindy u02]$ cd oracle-bash: cd: oracle: No such file or directory[oracle@cindy u02]$ cd oradata[oracle@cindy oradata]$ lsora11g[oracle@cindy oradata]$ cd ora11g[oracle@cindy ora11g]$ lscindy01.dbf  cindy03.dbf  control01.ctl  redo01.log  redo03.log    system01.dbf  undotbs01.dbfcindy02.dbf  cindy04.dbf  example01.dbf  redo02.log  sysaux01.dbf  temp01.dbf    users01.dbf[oracle@cindy ora11g]$ rm  cindy04.dbf [oracle@cindy ora11g]$ lscindy01.dbf  cindy03.dbf    example01.dbf  redo02.log  sysaux01.dbf  temp01.dbf     users01.dbfcindy02.dbf  control01.ctl  redo01.log     redo03.log  system01.dbf  undotbs01.dbf

,3，打开数据库，报错

[oracle@cindy ora11g]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.3.0 Production on Tue Sep 2 07:10:41 2014Copyright (c) 1982, 2011, Oracle.  All rights reserved.Connected to an idle instance.SQL> startupORACLE instance started.Total System Global Area  417546240 bytesFixed Size                  2228944 bytesVariable Size             322964784 bytesDatabase Buffers           88080384 bytesRedo Buffers                4272128 bytesDatabase mounted.ORA-01157: cannot identify/lock data file 9 - see DBWR trace fileORA-01110: data file 9: '/u02/oradata/ora11g/cindy04.dbf'

由于此时没有备份，我们便从归档中恢复数据文件，如下

通过alter database create datafile命令重建一个该文件

SQL>  alter database create datafile '/u02/oradata/ora11g/cindy04.dbf' as '/u02/oradata/ora11g/cindy04.dbf'  2  ;Database altered.SQL> recover datafile 9;Media recovery complete.SQL> alter database open;Database altered.SQL> conn cindy04/cindyConnected.SQL> select * from test;NAME--------------------acd

可见，建完文件之后，恢复该文件，此处的关键点是，丢失的数据文件，从其创建时刻起所有的重做日志文件都还在，因此才可以在重建该数据文件后，通过recover命令应用所有的重做日志的方式，重建该数据文件中的内容

此次恢复没有使用rman，因为，在这种情况下（有归档无备份），上述恢复方式为最佳。

丢失控制文件的恢复

新增一条数据

SQL> insert into test values('ert');1 row created.SQL> commit;Commit complete.SQL> shutdown immediate   ORA-01031: insufficient privilegesSQL> conn / as sysdbaConnected.SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.

手动删除控制文件

[oracle@cindy ora11g]$ cd /u02/oradata/ora11g[oracle@cindy ora11g]$ lscindy01.dbf  cindy03.dbf  control01.ctl  redo01.log  redo03.log    system01.dbf  undotbs01.dbfcindy02.dbf  cindy04.dbf  example01.dbf  redo02.log  sysaux01.dbf  temp01.dbf    users01.dbf[oracle@cindy ora11g]$ rm control01.ctl[oracle@cindy ora11g]$ lscindy01.dbf  cindy03.dbf  example01.dbf  redo02.log  sysaux01.dbf  temp01.dbf     users01.dbfcindy02.dbf  cindy04.dbf  redo01.log     redo03.log  system01.dbf  undotbs01.dbf

由于控制文件丢失，数据库无法启动到mount状态，只能到nomount

SQL> startup nomountORACLE instance started.Total System Global Area  417546240 bytesFixed Size                  2228944 bytesVariable Size             322964784 bytesDatabase Buffers           88080384 bytesRedo Buffers                4272128 bytes

打开一个新窗口，连接到rman

首先设置dbid

[oracle@cindy ~]$ rman target /Recovery Manager: Release 11.2.0.3.0 - Production on Tue Sep 2 07:34:20 2014Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.connected to target database: ORA11G (not mounted)RMAN> set dbid = 4252637343executing command: SET DBID

因为备份是在nocatalog模式下进行的，因为备份信息、备份设置等都是存储在目标数据库的控制文件中，现在控制文件丢失，相当于前面的一些配置也丢失了，用show all命令查看，可见所有配置均恢复成默认值

RMAN> show all;using target database control file instead of recovery catalogRMAN configuration parameters for database with db_unique_name ORA11G are:CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # defaultCONFIGURE BACKUP OPTIMIZATION OFF; # defaultCONFIGURE DEFAULT DEVICE TYPE TO DISK; # defaultCONFIGURE CONTROLFILE AUTOBACKUP OFF; # defaultCONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # defaultCONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # defaultCONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # defaultCONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # defaultCONFIGURE MAXSETSIZE TO UNLIMITED; # defaultCONFIGURE ENCRYPTION FOR DATABASE OFF; # defaultCONFIGURE ENCRYPTION ALGORITHM 'AES128'; # defaultCONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # defaultCONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

此时恢复控制文件，不能直接使用restore controlfile from autobackup命令，因为自动备份的设置丢失了，并且此时在nocatalog模式下，无法配置controlfile autobackup的相关属性，所以此时要显示指定控制文件备份集的方式恢复控制文件，如下

RMAN> host;   [oracle@cindy ~]$ cd /u01/oracle/rman_backup[oracle@cindy rman_backup]$ ls20140902.log  back_jiaoben.rman  back_jiaoben.sh  bak_2pphf9id_1_1  bak_2qphf9if_1_1  bak_2rphf9ok_1_1  c-4252637343-20140902-03  c-4252637343-20140902-04[oracle@cindy rman_backup]$ lltotal 1328012-rw-r--r-- 1 oracle oinstall       4671 Sep  2 03:48 20140902.log-rw-r--r-- 1 oracle oinstall        484 Aug 31 16:44 back_jiaoben.rman-rwxr-xr-x 1 oracle oinstall        118 Sep  2 03:44 back_jiaoben.sh-rw-r----- 1 oracle oinstall     136192 Sep  2 03:45 bak_2pphf9id_1_1-rw-r----- 1 oracle oinstall 1338613760 Sep  2 03:48 bak_2qphf9if_1_1-rw-r----- 1 oracle oinstall      35840 Sep  2 03:48 bak_2rphf9ok_1_1-rw-r----- 1 oracle oinstall    9863168 Sep  2 03:48 c-4252637343-20140902-03-rw-r----- 1 oracle oinstall    9863168 Sep  2 07:16 c-4252637343-20140902-04[oracle@cindy rman_backup]$ exitexithost command completeRMAN> restore controlfile from '/u01/oracle/rman_backup/c-4252637343-20140902-04';Starting restore at 02-SEP-14allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=20 device type=DISKchannel ORA_DISK_1: restoring control filechannel ORA_DISK_1: restore complete, elapsed time: 00:00:07output file name=/u02/oradata/ora11g/control01.ctloutput file name=/u01/oracle/app/oracle/fast_recovery_area/ora11g/control02.ctlFinished restore at 02-SEP-14

有了控制文件，数据库就可置为mount状态

RMAN> alter database mount;database mountedreleased channel: ORA_DISK_1

由于只是控制文件丢失，数据文件仍在，因此并不需要对整个数据库进行修复操作，只需要执行recover 操作，重新应用备份的控制文件后生成的那些重做日志即可

RMAN> recover database;Starting recover at 02-SEP-14Starting implicit crosscheck backup at 02-SEP-14allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=20 device type=DISKCrosschecked 4 objectsFinished implicit crosscheck backup at 02-SEP-14Starting implicit crosscheck copy at 02-SEP-14using channel ORA_DISK_1Finished implicit crosscheck copy at 02-SEP-14searching for all files in the recovery areacataloging files...no files catalogedusing channel ORA_DISK_1starting media recoveryarchived log for thread 1 with sequence 12 is already on disk as file /u02/oradata/ora11g/redo03.logarchived log file name=/u02/oradata/ora11g/redo03.log thread=1 sequence=12media recovery complete, elapsed time: 00:00:00Finished recover at 02-SEP-14

由于是通过备份的控制文件恢复的，所以在打开数据库的时候，必须执行resetlogs

RMAN> alter database open resetlogs;database opened

切到sql下，查看数据是否还在

SQL> conn cindy04/cindyConnected.SQL> select * from temp;select * from temp              *ERROR at line 1:ORA-00942: table or view does not existSQL> select * from test;NAME--------------------acdert

丢失非当前联机重做日志文件的恢复，看之前的blog

0 0