ITPub博客

首页 > 数据库 > Oracle > RMAN本地备份和异机恢复

RMAN本地备份和异机恢复

原创 Oracle 作者:woshishui11211 时间:2015-03-31 01:05:36 0 删除 编辑

RMAN本地备份和异机恢复

 

一、      本地环境

1)         env|grep ORA

ORACLE_SID=sdzy

ORACLE_BASE=/opt/oracle

ORACLE_HOME=/opt/oracle/product/10.2.0/db_1

 

查看参数文件

SQL> show parameter pfile;

 

NAME                                 TYPE        VALUE

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

spfile                               string      /opt/oracle/product/10.2.0/db_

                                                 1/dbs/spfilesdzy.ora

将参数文件转换成文本

SQL> create pfile='/opt/oracle/sdzy_pfile.ora' from spfile;

 

File created.

 

>cat /opt/oracle/sdzy_pfile.ora

sdzy.__db_cache_size=155189248

sdzy.__java_pool_size=4194304

sdzy.__large_pool_size=4194304

sdzy.__shared_pool_size=113246208

sdzy.__streams_pool_size=0

*.audit_file_dest='/opt/oracle/admin/sdzy/adump'

*.background_dump_dest='/opt/oracle/admin/sdzy/bdump'

*.compatible='10.2.0.3.0'

*.control_files='/opt/oracle/oradata/sdzy/control01.ctl','/opt/oracle/oradata/sdzy/control02.ctl','/opt/oracle/oradata/sdzy/control03.ctl'

*.core_dump_dest='/opt/oracle/admin/sdzy/cdump'

*.db_block_size=8192

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_name='sdzy'

*.db_recovery_file_dest='/opt/oracle/flash_recovery_area'

*.db_recovery_file_dest_size=2147483648

*.dispatchers='(PROTOCOL=TCP) (SERVICE=sdzyXDB)'

*.job_queue_processes=10

*.log_archive_format='%t_%s_%r.dbf'

*.open_cursors=300

*.pga_aggregate_target=93323264

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=279969792

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='/opt/oracle/admin/sdzy/udump'

 

 

SQL>  selectfile_name,tablespace_name from dba_data_files;

 

FILE_NAME                                          TABLESPACE_NAME

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

/opt/oracle/oradata/sdzy/system01.dbf              SYSTEM

/opt/oracle/oradata/sdzy/undotbs01.dbf             UNDOTBS1

/opt/oracle/oradata/sdzy/sysaux01.dbf              SYSAUX

/opt/oracle/oradata/sdzy/users01.dbf               USERS

/opt/oracle/oradata/sdzy/testtbs.dbf               USERS

/opt/oracle/oradata/sdzy/hellotbs01.dbf            USERS

 

6 rows selected.

 

查看控制文件

 

SQL> select name from v$controlfile;

 

NAME

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

/opt/oracle/oradata/sdzy/control01.ctl

/opt/oracle/oradata/sdzy/control02.ctl

/opt/oracle/oradata/sdzy/control03.ctl

 

查看日志文件

SQL> select * from v$logfile;

 

    GROUP# STATUS  TYPE    MEMBER                                             IS_

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

         1         ONLINE  /opt/oracle/oradata/sdzy/redo01.log                NO

         2         ONLINE  /opt/oracle/oradata/sdzy/redo02.log                NO

         3         ONLINE  /opt/oracle/oradata/sdzy/redo03.log                NO

SQL> select * from v$log;

 

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM

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

         1          1         31   52428800          1 NO  CURRENT                 358072 15-MAR-11

         2          1         29   52428800          1 YES ACTIVE                  357061 15-MAR-11

         3          1         30   52428800          1 YES ACTIVE                  358070 15-MAR-11

 

 

2)         执行测试数据生成SQL语句

CREATE TABLE departments(

department_id NUMBER(4),

department_nameVARCHAR2(30),

manager_id NUMBER(6),

location_id NUMBER(4))

STORAGE(INITIAL 200K NEXT 200K

PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS 5)

TABLESPACE users;

 

>vi /opt/oracle/insert.sql

declare

i integer;

begin

i := 1;

loop

insert into  departments

values

(100 + i,

'a',

i,

1000);

i := i + 1;

exit when i > 2000;

end loop;

commit;

end;

 

SQL> @/opt/oracle/insert.sql;

17  /

 

SQL> select count(*) from departments;

 

COUNT(*)

----------

      2000

 

 

3)         创建RMAN全备脚本

>vi /opt/oracle/rman_sdzy_full.sh

 

#!/bin/sh

#数据库全备,同时备份控制文件及归档日志文件,备份文件保存至/opt/oracle/sdzy/rmanbak目录下,并在完成归档日志文件备份后,自动删除已备份的归档日志

#RMAN配置了自动备份控制文件,另外全备也做了控制文件备份,不冲突

#备份保留7天,过期则自动删除

#保留操作日志备查到/opt/oracle/sdzy/rmanbak/rman_sdzy_`date +%Y%m%d-%H%M`.log

#事先创建好/opt/oracle/sdzy/rmanbak目录

ORACLE_SID=sdzy

ORACLE_BASE=/opt/oracle

ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1

export ORACLE_SID ORACLE_BASE ORACLE_HOME

export PATH=$ORACLE_HOME/bin:${PATH}:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin

RMAN=$ORACLE_HOME/bin/rman

$RMAN target / msglog=/opt/oracle/sdzy/rmanbak/rman_sdzy_`date +%Y%m%d-%H%M`.log<

run

{

CONFIGURE CONTROLFILE AUTOBACKUP ON;

ALLOCATE CHANNEL t1 TYPE DISK;

ALLOCATE CHANNEL t2 TYPE DISK;

ALLOCATE CHANNEL t3 TYPE DISK;

backup full database format '/opt/oracle/sdzy/rmanbak/%d_full_%u_%s_%p' tag='full_db' include current controlfile;

sql 'alter system archive log current';

backup format '/opt/oracle/sdzy/rmanbak/%d_arch_%T_%t_%U' archivelog all delete all input;

release channel t1;

release channel t2;

release channel t3;

 }

 

4)         执行备份脚本

>sh/opt/oracle/rman_sdzy_full.sh

 

 

5)         查看备份情况

 

 

oracle@linux-o154:~/sdzy/rmanbak>ll

total 875220

-rw-r----- 1 oracle oinstall   7143424 2011-03-15 21:04 c-1338037274-20110315-00

-rw-r----- 1 oracle oinstall   7143424 2011-03-15 21:04 c-1338037274-20110315-01

-rw-r--r-- 1 oracle oinstall      5555 2011-03-15 21:04 rman_sdzy_20110315-2102.log

-rw-r----- 1 oracle oinstall  30449152 2011-03-15 21:04 SDZY_arch_20110315_745880668_06m7af2s_1_1

-rw-r----- 1 oracle oinstall    150016 2011-03-15 21:04 SDZY_arch_20110315_745880668_07m7af2s_1_1

-rw-r----- 1 oracle oinstall  10651648 2011-03-15 21:04 SDZY_arch_20110315_745880668_08m7af2s_1_1

-rw-r----- 1 oracle oinstall   2457600 2011-03-15 21:03 SDZY_full_01m7af06_1_1

-rw-r----- 1 oracle oinstall 368779264 2011-03-15 21:03 SDZY_full_02m7af06_2_1

-rw-r----- 1 oracle oinstall 461455360 2011-03-15 21:04 SDZY_full_03m7af06_3_1

-rw-r----- 1 oracle oinstall   7110656 2011-03-15 21:04 SDZY_full_04m7af0f_4_1

查看库备份情况:

RMAN> list backup of database;

//之前备份的数据文件是6个,这里查询的文件也是6

List of Backup Sets

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

 

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

1       Full    2.34M      DISK        00:00:04     15-MAR-11     

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

        Piece Name: /opt/oracle/sdzy/rmanbak/SDZY_full_01m7af06_1_1

  List of Datafiles in backup set 1

  File LV Type Ckp SCN    CkpTime  Name

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

4       Full 359516     15-MAR-11 /opt/oracle/oradata/sdzy/users01.dbf

5       Full 331018     28-FEB-11 /opt/oracle/oradata/sdzy/testtbs.dbf

 

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

2       Full    351.69M    DISK        00:01:01     15-MAR-11     

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

        Piece Name: /opt/oracle/sdzy/rmanbak/SDZY_full_02m7af06_2_1

  List of Datafiles in backup set 2

  File LV Type Ckp SCN    CkpTime  Name

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

1       Full 359517     15-MAR-11 /opt/oracle/oradata/sdzy/system01.dbf

6       Full 359517     15-MAR-11 /opt/oracle/oradata/sdzy/hellotbs01.dbf

 

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

4       Full    440.07M    DISK        00:01:07     15-MAR-11     

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

        Piece Name: /opt/oracle/sdzy/rmanbak/SDZY_full_03m7af06_3_1

  List of Datafiles in backup set 4

  File LV Type Ckp SCN    CkpTime  Name

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

2       Full 359518     15-MAR-11 /opt/oracle/oradata/sdzy/undotbs01.dbf

3       Full 359518     15-MAR-11 /opt/oracle/oradata/sdzy/sysaux01.dbf

 

查看控制文件备份情况:

RMAN> list backup of controlfile;

//之前查询的控制文件是3个,这里自动备份的控制文件是2个,全备自动备一个

 

List of Backup Sets

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

 

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

3       Full    6.77M      DISK        00:00:58     15-MAR-11     

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

        Piece Name: /opt/oracle/sdzy/rmanbak/SDZY_full_04m7af0f_4_1//全备including

  Control File Included: Ckp SCN: 359526       Ckp time: 15-MAR-11

 

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

5       Full    6.80M      DISK        00:00:01     15-MAR-11     

        BP Key: 5   Status: AVAILABLE  Compressed: NO  Tag: TAG20110315T210419

        Piece Name: /opt/oracle/sdzy/rmanbak/c-1338037274-20110315-00//自动备份

  Control File Included: Ckp SCN: 359556       Ckp time: 15-MAR-11

 

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

9       Full    6.80M      DISK        00:00:00     15-MAR-11     

        BP Key: 9   Status: AVAILABLE  Compressed: NO  Tag: TAG20110315T210433

        Piece Name: /opt/oracle/sdzy/rmanbak/c-1338037274-20110315-01//自动备份

  Control File Included: Ckp SCN: 359580       Ckp time: 15-MAR-11

查看归档文件备份

RMAN> list backup of archivelog all;

//之前查询的日志文件是3个,这里备份后的备份结果也是3

 

List of Backup Sets

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

 

List of Backup Sets

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

 

BS Key  Size       Device Type Elapsed Time Completion Time

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

6       146.00K    DISK        00:00:02     15-MAR-11     

        BP Key: 6   Status: AVAILABLE  Compressed: NO  Tag: TAG20110315T210427

        Piece Name: /opt/oracle/sdzy/rmanbak/SDZY_arch_20110315_745880668_07m7af2s_1_1

 

  List of Archived Logs in backup set 6

ThrdSeq     Low SCN    Low Time  Next SCN   Next Time

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

  1    29      357061     15-MAR-11 358070     15-MAR-11

  1    30      358070     15-MAR-11 358072     15-MAR-11

 

BS Key  Size       Device Type Elapsed Time Completion Time

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

7       10.16M     DISK        00:00:03     15-MAR-11     

        BP Key: 7   Status: AVAILABLE  Compressed: NO  Tag: TAG20110315T210427

        Piece Name: /opt/oracle/sdzy/rmanbak/SDZY_arch_20110315_745880668_08m7af2s_1_1

 

  List of Archived Logs in backup set 7

ThrdSeq     Low SCN    Low Time  Next SCN   Next Time

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

  1    31      358072     15-MAR-11 359568     15-MAR-11

  1    32      359568     15-MAR-11 359573     15-MAR-11

 

BS Key  Size       Device Type Elapsed Time Completion Time

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

8       29.04M     DISK        00:00:03     15-MAR-11     

        BP Key: 8   Status: AVAILABLE  Compressed: NO  Tag: TAG20110315T210427

        Piece Name: /opt/oracle/sdzy/rmanbak/SDZY_arch_20110315_745880668_06m7af2s_1_1

 

  List of Archived Logs in backup set 8

ThrdSeq     Low SCN    Low Time  Next SCN   Next Time

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

  1    28      315840     30-OCT-10 357061     15-MAR-11

 

 

二、      异机环境

1.         准备一台同等机器环境,安装相同的OS和数据库软件

oracle@linux-o154:~>env|grep ORA

ORACLE_SID=sdzy

ORACLE_BASE=/opt/oracle

ORACLE_HOME=/opt/oracle/product/10.2.0/db_1

 

>mkdir -p /opt/oracle/sdzy/rmanbak

 

 

2.         将备份文件拷贝到异机环境中

>cd /opt/oracle/sdzy/rmanbak/

>scp * 192.168.219.55:/opt/oracle/sdzy/rmanbak

Password:

c-1338037274-20110315-00                                             100% 6976KB   6.8MB/s   00:00   

c-1338037274-20110315-01                                             100% 6976KB   6.8MB/s   00:01   

rman_sdzy_20110315-2102.log                                          100% 5555     5.4KB/s   00:00   

SDZY_arch_20110315_745880668_06m7af2s_1_1                            100%   29MB   9.7MB/s   00:03   

SDZY_arch_20110315_745880668_07m7af2s_1_1                            100%  147KB 146.5KB/s   00:00   

SDZY_arch_20110315_745880668_08m7af2s_1_1                            100%   10MB  10.2MB/s   00:01   

SDZY_full_01m7af06_1_1                                               100% 2400KB   2.3MB/s   00:00   

SDZY_full_02m7af06_2_1                                                                            100%  352MB   9.5MB/s   00:37   

SDZY_full_03m7af06_3_1                                                                            100%  440MB   9.8MB/s   00:45   

SDZY_full_04m7af0f_4_1                                                                            100% 6944KB   6.8MB/s   00:01

 

 

3.         创建口令文件

口令文件一般是在$ORACLE_HOME/dbs/目录下

作用:主要进行DBA权限的身份认证

DBA用户:具有sysdbasysoper权限的用户被称为dba用户。默认情况下sysdba角色中存在sys用户,sysoper角色中存在system用户

>orapwd file=$ORACLE_HOME/dbs/orapwsdzy password=oracle

 

4.         将参数文件拷贝过来

>scpsdzy_pfile.ora 192.168.219.55:/opt/oracle

Password:

sdzy_pfile.ora                              100% 2560     2.5KB/s   00:00

5.         依照参数文件在目标机上创建目录

mkdir -p /opt/oracle/admin/sdzy/adump

mkdir -p /opt/oracle/admin/sdzy/bdump

mkdir -p /opt/oracle/oradata/sdzy

mkdir -p /opt/oracle/admin/sdzy/cdump

mkdir -p /opt/oracle/flash_recovery_area

mkdir -p /opt/oracle/admin/sdzy/udump

6.         进入RMAN

oracle@linux-o154:~>rman target /

 

Recovery Manager: Release 10.2.0.3.0 - Production on Mon Mar 14 22:45:20 2011

 

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

 

connected to target database (not started)

 

7.         恢复参数文件

 

RMAN> set DBID=1338037274;

RMAN> startup nomountpfile=/opt/oracle/sdzy_pfile.ora;

 

Oracle instance started

 

Total System Global Area     281018368 bytes

 

Fixed Size                     1261348 bytes

Variable Size                113246428 bytes

Database Buffers             163577856 bytes

Redo Buffers                   2932736 bytes

 

8.         恢复控制文件

RMAN>restore controlfile from '/opt/oracle/sdzy/rmanbak/c-1338037274-20110315-01';//以之前查询到的Ckp SCN: 359580最大值恢复,否则可能出现部分数据文件和日志文件查询不到的情况

Starting restore at 15-MAR-11

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=156 devtype=DISK

 

channel ORA_DISK_1: restoring control file

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

output filename=/opt/oracle/oradata/sdzy/control01.ctl

output filename=/opt/oracle/oradata/sdzy/control02.ctl

output filename=/opt/oracle/oradata/sdzy/control03.ctl

Finished restore at 15-MAR-11

 

 

 

9.         将数据库启动到mount状态

RMAN> alter database mount;

 

database mounted

released channel: ORA_DISK_1

 

10.     查看备份日志

RMAN> list backup of database;

 

 

List of Backup Sets

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

 

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

1       Full    2.34M      DISK        00:00:04     15-MAR-11     

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

        Piece Name: /opt/oracle/sdzy/rmanbak/SDZY_full_01m7af06_1_1

  List of Datafiles in backup set 1

  File LV Type Ckp SCN    CkpTime  Name

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

4       Full 359516     15-MAR-11 /opt/oracle/oradata/sdzy/users01.dbf

5       Full 331018     28-FEB-11 /opt/oracle/oradata/sdzy/testtbs.dbf

 

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

2       Full    351.69M    DISK        00:01:01     15-MAR-11     

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

        Piece Name: /opt/oracle/sdzy/rmanbak/SDZY_full_02m7af06_2_1

  List of Datafiles in backup set 2

  File LV Type Ckp SCN    CkpTime  Name

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

1       Full 359517     15-MAR-11 /opt/oracle/oradata/sdzy/system01.dbf

6       Full 359517     15-MAR-11 /opt/oracle/oradata/sdzy/hellotbs01.dbf

 

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

4       Full    440.07M    DISK        00:01:07     15-MAR-11     

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

        Piece Name: /opt/oracle/sdzy/rmanbak/SDZY_full_03m7af06_3_1

  List of Datafiles in backup set 4

  File LV Type Ckp SCN    CkpTime  Name

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

2       Full 359518     15-MAR-11 /opt/oracle/oradata/sdzy/undotbs01.dbf

3       Full 359518     15-MAR-11 /opt/oracle/oradata/sdzy/sysaux01.dbf

 

查看归档日志和控制文件:

 

RMAN> list backup of archivelog all;

 

 

List of Backup Sets

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

 

BS Key  Size       Device Type Elapsed Time Completion Time

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

6       146.00K    DISK        00:00:02     15-MAR-11     

        BP Key: 6   Status: AVAILABLE  Compressed: NO  Tag: TAG20110315T210427

        Piece Name: /opt/oracle/sdzy/rmanbak/SDZY_arch_20110315_745880668_07m7af2s_1_1

 

  List of Archived Logs in backup set 6

ThrdSeq     Low SCN    Low Time  Next SCN   Next Time

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

  1    29      357061     15-MAR-11 358070     15-MAR-11

  1    30      358070     15-MAR-11 358072     15-MAR-11

 

BS Key  Size       Device Type Elapsed Time Completion Time

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

7       10.16M     DISK        00:00:03     15-MAR-11     

        BP Key: 7   Status: AVAILABLE  Compressed: NO  Tag: TAG20110315T210427

        Piece Name: /opt/oracle/sdzy/rmanbak/SDZY_arch_20110315_745880668_08m7af2s_1_1

 

  List of Archived Logs in backup set 7

ThrdSeq     Low SCN    Low Time  Next SCN   Next Time

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

  1    31      358072     15-MAR-11 359568     15-MAR-11

  1    32      359568     15-MAR-11 359573     15-MAR-11

 

BS Key  Size       Device Type Elapsed Time Completion Time

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

8       29.04M     DISK        00:00:03     15-MAR-11     

        BP Key: 8   Status: AVAILABLE  Compressed: NO  Tag: TAG20110315T210427

        Piece Name: /opt/oracle/sdzy/rmanbak/SDZY_arch_20110315_745880668_06m7af2s_1_1

 

  List of Archived Logs in backup set 8

ThrdSeq     Low SCN    Low Time  Next SCN   Next Time

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

  1    28      315840     30-OCT-10 357061     15-MAR-11

 

 

RMAN> list backup of controlfile;

 

 

List of Backup Sets

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

 

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

3       Full    6.77M      DISK        00:00:58     15-MAR-11     

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

        Piece Name: /opt/oracle/sdzy/rmanbak/SDZY_full_04m7af0f_4_1

  Control File Included: Ckp SCN: 359526       Ckp time: 15-MAR-11

 

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

5       Full    6.80M      DISK        00:00:01     15-MAR-11     

        BP Key: 5   Status: AVAILABLE  Compressed: NO  Tag: TAG20110315T210419

        Piece Name: /opt/oracle/sdzy/rmanbak/c-1338037274-20110315-00

  Control File Included: Ckp SCN: 359556       Ckp time: 15-MAR-11

 

 

11.     恢复数据文件

RMAN> restore database;

 

Starting restore at 15-MAR-11

Starting implicit crosscheck backup at 15-MAR-11

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=156 devtype=DISK

Crosschecked 8 objects

Finished implicit crosscheck backup at 15-MAR-11

 

Starting implicit crosscheck copy at 15-MAR-11

using channel ORA_DISK_1

Finished implicit crosscheck copy at 15-MAR-11

 

searching for all files in the recovery area

cataloging files...

no files cataloged

 

using channel ORA_DISK_1

 

channel ORA_DISK_1: starting datafilebackupset restore

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

restoringdatafile 00004 to /opt/oracle/oradata/sdzy/users01.dbf

restoringdatafile 00005 to /opt/oracle/oradata/sdzy/testtbs.dbf

channel ORA_DISK_1: reading from backup piece /opt/oracle/sdzy/rmanbak/SDZY_full_01m7af06_1_1

channel ORA_DISK_1: restored backup piece 1

piece handle=/opt/oracle/sdzy/rmanbak/SDZY_full_01m7af06_1_1 tag=FULL_DB

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

channel ORA_DISK_1: starting datafilebackupset restore

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

restoringdatafile 00001 to /opt/oracle/oradata/sdzy/system01.dbf

restoringdatafile 00006 to /opt/oracle/oradata/sdzy/hellotbs01.dbf

channel ORA_DISK_1: reading from backup piece /opt/oracle/sdzy/rmanbak/SDZY_full_02m7af06_2_1

channel ORA_DISK_1: restored backup piece 1

piece handle=/opt/oracle/sdzy/rmanbak/SDZY_full_02m7af06_2_1 tag=FULL_DB

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

channel ORA_DISK_1: starting datafilebackupset restore

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

restoringdatafile 00002 to /opt/oracle/oradata/sdzy/undotbs01.dbf

restoringdatafile 00003 to /opt/oracle/oradata/sdzy/sysaux01.dbf

channel ORA_DISK_1: reading from backup piece /opt/oracle/sdzy/rmanbak/SDZY_full_03m7af06_3_1

channel ORA_DISK_1: restored backup piece 1

piece handle=/opt/oracle/sdzy/rmanbak/SDZY_full_03m7af06_3_1 tag=FULL_DB

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

Finished restore at 15-MAR-11

 

查看恢复数据文件情况:

# ls -l

total 3245662

-rw-r----- 1 oracle oinstall    7061504 Mar 15 05:39 control01.ctl

-rw-r----- 1 oracle oinstall    7061504 Mar 15 05:39 control02.ctl

-rw-r----- 1 oracle oinstall    7061504 Mar 15 05:39 control03.ctl

-rw-r----- 1 oracle oinstall  104865792 Mar 15 05:37 hellotbs01.dbf

-rw-r----- 1 oracle oinstall  251666432 Mar 15 05:38 sysaux01.dbf

-rw-r----- 1 oracle oinstall  503324672 Mar 15 05:37 system01.dbf

-rw-r----- 1 oracle oinstall 2097160192 Mar 15 05:37 testtbs.dbf

-rw-r----- 1 oracle oinstall  309338112 Mar 15 05:38 undotbs01.dbf

-rw-r----- 1 oracle oinstall   32776192 Mar 15 05:37 users01.dbf

 

12.     恢复数据库

 

RMAN> recover database;

//会报错,但不要紧,我们只需要得到threadsequence号就行

Starting recover at 15-MAR-11

using channel ORA_DISK_1

 

starting media recovery

 

channel ORA_DISK_1: starting archive log restore to default destination

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=29

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=30

channel ORA_DISK_1: reading from backup piece /opt/oracle/sdzy/rmanbak/SDZY_arch_20110315_745880668_07m7af2s_1_1

channel ORA_DISK_1: restored backup piece 1

piece handle=/opt/oracle/sdzy/rmanbak/SDZY_arch_20110315_745880668_07m7af2s_1_1 tag=TAG20110315T210427

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

channel ORA_DISK_1: starting archive log restore to default destination

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=31

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=32

channel ORA_DISK_1: reading from backup piece /opt/oracle/sdzy/rmanbak/SDZY_arch_20110315_745880668_08m7af2s_1_1

channel ORA_DISK_1: restored backup piece 1

piece handle=/opt/oracle/sdzy/rmanbak/SDZY_arch_20110315_745880668_08m7af2s_1_1 tag=TAG20110315T210427

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

channel ORA_DISK_1: starting archive log restore to default destination

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=28

channel ORA_DISK_1: reading from backup piece /opt/oracle/sdzy/rmanbak/SDZY_arch_20110315_745880668_06m7af2s_1_1

channel ORA_DISK_1: restored backup piece 1

piece handle=/opt/oracle/sdzy/rmanbak/SDZY_arch_20110315_745880668_06m7af2s_1_1 tag=TAG20110315T210427

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

archive log filename=/opt/oracle/flash_recovery_area/SDZY/archivelog/2011_03_15/o1_mf_1_28_6qyf0jh7_.arc thread=1 sequence=28

channel default: deleting archive log(s)

archive log filename=/opt/oracle/flash_recovery_area/SDZY/archivelog/2011_03_15/o1_mf_1_28_6qyf0jh7_.arc recid=10 stamp=745825233

archive log filename=/opt/oracle/flash_recovery_area/SDZY/archivelog/2011_03_15/o1_mf_1_29_6qyf0gcr_.arc thread=1 sequence=29

channel default: deleting archive log(s)

archive log filename=/opt/oracle/flash_recovery_area/SDZY/archivelog/2011_03_15/o1_mf_1_29_6qyf0gcr_.arc recid=6 stamp=745825230

archive log filename=/opt/oracle/flash_recovery_area/SDZY/archivelog/2011_03_15/o1_mf_1_30_6qyf0gks_.arc thread=1 sequence=30

channel default: deleting archive log(s)

archive log filename=/opt/oracle/flash_recovery_area/SDZY/archivelog/2011_03_15/o1_mf_1_30_6qyf0gks_.arc recid=7 stamp=745825230

archive log filename=/opt/oracle/flash_recovery_area/SDZY/archivelog/2011_03_15/o1_mf_1_31_6qyf0hf5_.arc thread=1 sequence=31

channel default: deleting archive log(s)

archive log filename=/opt/oracle/flash_recovery_area/SDZY/archivelog/2011_03_15/o1_mf_1_31_6qyf0hf5_.arc recid=9 stamp=745825231

archive log filename=/opt/oracle/flash_recovery_area/SDZY/archivelog/2011_03_15/o1_mf_1_32_6qyf0hhd_.arc thread=1 sequence=32

channel default: deleting archive log(s)

archive log filename=/opt/oracle/flash_recovery_area/SDZY/archivelog/2011_03_15/o1_mf_1_32_6qyf0hhd_.arc recid=8 stamp=745825231

unable to find archive log

archive log thread=1 sequence=33

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

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

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

RMAN-03002: failure of recover command at 03/15/2011 05:40:35

RMAN-06054: media recovery requesting unknown log: thread 1 seq 33 lowscn 359573

 

//找到最接近seq 33 lowscn 359573的归档日志,是  32      359568

查询归档文件

RMAN> list backup of archivelog all;

 

 

List of Backup Sets

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

 

BS Key  Size       Device Type Elapsed Time Completion Time

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

6       146.00K    DISK        00:00:02     15-MAR-11     

        BP Key: 6   Status: AVAILABLE  Compressed: NO  Tag: TAG20110315T210427

        Piece Name: /opt/oracle/sdzy/rmanbak/SDZY_arch_20110315_745880668_07m7af2s_1_1

 

  List of Archived Logs in backup set 6

ThrdSeq     Low SCN    Low Time  Next SCN   Next Time

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

  1    29      357061     15-MAR-11 358070     15-MAR-11

  1    30      358070     15-MAR-11 358072     15-MAR-11

 

BS Key  Size       Device Type Elapsed Time Completion Time

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

7       10.16M     DISK        00:00:03     15-MAR-11     

        BP Key: 7   Status: AVAILABLE  Compressed: NO  Tag: TAG20110315T210427

        Piece Name: /opt/oracle/sdzy/rmanbak/SDZY_arch_20110315_745880668_08m7af2s_1_1

 

  List of Archived Logs in backup set 7

ThrdSeq     Low SCN    Low Time  Next SCN   Next Time

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

  1    31      358072     15-MAR-11 359568     15-MAR-11

  1    32      359568     15-MAR-11 359573     15-MAR-11

 

BS Key  Size       Device Type Elapsed Time Completion Time

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

8       29.04M     DISK        00:00:03     15-MAR-11     

        BP Key: 8   Status: AVAILABLE  Compressed: NO  Tag: TAG20110315T210427

        Piece Name: /opt/oracle/sdzy/rmanbak/SDZY_arch_20110315_745880668_06m7af2s_1_1

 

  List of Archived Logs in backup set 8

ThrdSeq     Low SCN    Low Time  Next SCN   Next Time

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

  1    28      315840     30-OCT-10 357061     15-MAR-11

 

 

再执行恢复

RMAN> recover database until scn 359568;

 

Starting recover at 15-MAR-11

using channel ORA_DISK_1

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

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

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

RMAN-03002: failure of recover command at 03/15/2011 05:49:24

RMAN-06556: datafile 1 must be restored from backup older than scn 359568

 

RMAN-06556: datafile 1 must be restored from backup older than scn 359568

 

13.     可以直接 alter database open resetlogs命令打开数据库

RMAN> alter database open resetlogs;

 

database opened

 

14.     检查恢复情况

SQL> select file_name,tablespace_name from dba_data_files;

 

FILE_NAME                                TABLESPACE_NAME

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

/opt/oracle/oradata/sdzy/system01.dbf    SYSTEM

/opt/oracle/oradata/sdzy/undotbs01.dbf   UNDOTBS1

/opt/oracle/oradata/sdzy/sysaux01.dbf    SYSAUX

/opt/oracle/oradata/sdzy/users01.dbf     USERS

/opt/oracle/oradata/sdzy/testtbs.dbf     USERS

/opt/oracle/oradata/sdzy/hellotbs01.dbf  USERS

 

6 rows selected.

 

SQL>  select name from v$controlfile;

 

NAME

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

/opt/oracle/oradata/sdzy/control01.ctl

/opt/oracle/oradata/sdzy/control02.ctl

/opt/oracle/oradata/sdzy/control03.ctl

 

SQL> /

 

    GROUP# STATUS  TYPE    MEMBER                                   IS_

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

         1         ONLINE  /opt/oracle/oradata/sdzy/redo01.log      NO

         2         ONLINE  /opt/oracle/oradata/sdzy/redo02.log      NO

         3         ONLINE  /opt/oracle/oradata/sdzy/redo03.log      NO

 

SQL> select * from v$log;

 

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM

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

         1          1          0   52428800          1 YES UNUSED                       0

         2          1          0   52428800          1 YES UNUSED                       0

         3          1          1   52428800          1 NO  CURRENT                 359574 15-MAR-11

 

SQL> select count(*) from departments;

 

COUNT(*)

----------

      2000

//测试表数据得到恢复

 

 

SQL> create spfile from pfile='/opt/oracle/sdzy_pfile.ora';

 

File created.

//否则以后启动的时候会找PFILE文件

 

SQL> show parameter pfile;

 

NAME                                 TYPE        VALUE

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

spfile                               string

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup;

ORACLE instance started.

 

Total System Global Area  281018368 bytes

Fixed Size                  1261348 bytes

Variable Size             125829340 bytes

Database Buffers          150994944 bytes

Redo Buffers                2932736 bytes

Database mounted.

Database opened.

SQL> show parameter pfile;

 

NAME                                 TYPE        VALUE

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

spfile                               string      /opt/oracle/product/10.2.0/db_

                                                 1/dbs/spfilesdzy.ora

 

 

 

15.     恢复完成后,做一次全备

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26217983/viewspace-1479576/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2014-03-15

  • 博文量
    39
  • 访问量
    271780