使数rman备份oracle,ORACLE RMAN 备份

一、归档和非归档转换

1.查看数据库模式

SQL> show user;

USER is "SYS"

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     8

Next log sequence to archive   10

Current log sequence           10

为了测试，先转换为非归档模式，再转为归档模式

1.关闭数据库

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

2.启动到mount

SQL> startup mount;

ORACLE instance started.

Total System Global Area  451981312 bytes

Fixed Size                  1337100 bytes

Variable Size             322963700 bytes

Database Buffers          121634816 bytes

Redo Buffers                6045696 bytes

alter database Database mounted.

3.关闭闪回模式，否则后续关闭归档可能会有ORA-38774错误

SQL> alter database flashback off;

Database altered.

4.修改为非归档模式

QL> alter database noarchivelog;

alter database noarchivelog

*

ERROR at line 1:

ORA-38781: cannot disable media recovery - have guaranteed restore points

删除还原点

SQL> select name from v$restore_point;

NAME

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

TEST_GUARANTEE_201306302215

SQL> drop restore point TEST_GUARANTEE_201306302215;

Restore point dropped.

5.打开数据库

SQL> alter database noarchivelog;

Database altered.

QL> alter database open;

Database altered.

6修改为归档模式

SQL> archive log list;

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     8

Current log sequence           10

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area  451981312 bytes

Fixed Size                  1337100 bytes

Variable Size             322963700 bytes

Database Buffers          121634816 bytes

Redo Buffers                6045696 bytes

Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     9

Next log sequence to archive   11

Current log sequence           11

SQL> select  name,flashback_on from v$database;

NAME               FLASHBACK_ON

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

ORCL               NO

7.查看闪回参数(使用alter system 设置)

SQL> show parameter db_recovery;

NAME                                 TYPE                   VALUE

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

db_recovery_file_dest                string                 /u01/app/oracle/flash_recovery

_area

db_recovery_file_dest_size           big integer            10G

SQL> show parameter db_flashback;

NAME                                 TYPE                   VALUE

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

db_flashback_retention_target        integer                1440

8.启用闪回

SQL> shutdown immediate;

Database closed.

Database dismounted.

startup ORACLE instance shut down.

SQL> startup mount;

SP2-0714: invalid combination of STARTUP options

SQL> startup mount;

ORACLE instance started.

Total System Global Area  451981312 bytes

Fixed Size                  1337100 bytes

Variable Size             322963700 bytes

Database Buffers          121634816 bytes

Redo Buffers                6045696 bytes

Database mounted.

SQL> alter database flashback on;

Database altered.

SQL> alter database open;

Database altered.

SQL> select  name,flashback_on from v$database;

NAME               FLASHBACK_ON

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

ORCL               YES

9.查看闪回是否启用

SQL> select  name,flashback_on from v$database;

NAME               FLASHBACK_ON

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

ORCL               YES

二、连接测试

1.连接RMAN

[oracle@pc-centos ~]$ rman target sys/orcl

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Feb 17 22:13:03 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1310729769)

2.执行全库备份

RMAN> backup database plus archivelog delete input;--表示执行数据文件及控制文件，参数文件及所有归档重做日志文件，并删除旧的归档日志。

Starting backup at 2014-02-17 22:20:20

current log archived

using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=4 RECID=54 STAMP=819749439

channel ORA_DISK_1: starting piece 1 at 2014-02-17 22:20:20

channel ORA_DISK_1: finished piece 1 at 2014-02-17 22:20:21

piece handle=/opt/oracle/obak/bkup_5fp0sn54_1_1 tag=TAG20140217T222020 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

channel ORA_DISK_1: deleting archived log(s)

archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2013_07_02/o1_mf_1_4_8x5jsy8z_.arc RECID=54 STAMP=819749439

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=9 RECID=220 STAMP=839629815

input archived log thread=1 sequence=11 RECID=221 STAMP=839802020

channel ORA_DISK_1: starting piece 1 at 2014-02-17 22:20:22

channel ORA_DISK_1: finished piece 1 at 2014-02-17 22:20:25

piece handle=/opt/oracle/obak/bkup_5gp0sn56_1_1 tag=TAG20140217T222020 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03

channel ORA_DISK_1: deleting archived log(s)

archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_02_15/o1_mf_1_9_9hyyhpj6_.arc RECID=220 STAMP=839629815

archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_02_17/o1_mf_1_11_9j46o4fs_.arc RECID=221 STAMP=839802020

Finished backup at 2014-02-17 22:20:25

Starting backup at 2014-02-17 22:20:25

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00006 name=/u01/app/oracle/oradata/orcl/jerry.db

input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf

input datafile file number=00002 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf

input datafile file number=00013 name=/u01/app/oracle/oradata/orcl/backup01.dbf

input datafile file number=00015 name=/u01/app/oracle/oradata/orcl/backup02.dbf

input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf

input datafile file number=00005 name=/u01/app/oracle/oradata/orcl/example01.dbf

input datafile file number=00008 name=/u01/app/oracle/oradata/orcl/rman01.dbf

input datafile file number=00010 name=/u01/app/oracle/oradata/orcl/fbra01.dbf

input datafile file number=00012 name=/u01/app/oracle/oradata/orcl/testreuse03.dbf

input datafile file number=00014 name=/u01/app/oracle/oradata/orcl/undotbs02.dbf

input datafile file number=00016 name=/u01/app/oracle/oradata/orcl/fda1

input datafile file number=00017 name=/u01/app/oracle/oradata/orcl/fda2

input datafile file number=00018 name=/u01/app/oracle/oradata/orcl/fda3

input datafile file number=00019 name=/u01/app/oracle/oradata/orcl/fda4

input datafile file number=00009 name=/u01/app/oracle/oradata/orcl/eygle01.dbf

input datafile file number=00007 name=/u01/app/oracle/oradata/orcl/bftbs01.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf

input datafile file number=00011 name=/u01/app/oracle/oradata/orcl/demo01.dbf

channel ORA_DISK_1: starting piece 1 at 2014-02-17 22:20:25

channel ORA_DISK_1: finished piece 1 at 2014-02-17 22:22:40

piece handle=/opt/oracle/obak/bkup_5hp0sn59_1_1 tag=TAG20140217T222025 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:02:15

Finished backup at 2014-02-17 22:22:40

Starting backup at 2014-02-17 22:22:40

current log archived

using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=12 RECID=222 STAMP=839802160

channel ORA_DISK_1: starting piece 1 at 2014-02-17 22:22:41

channel ORA_DISK_1: finished piece 1 at 2014-02-17 22:22:42

piece handle=/opt/oracle/obak/bkup_5ip0sn9h_1_1 tag=TAG20140217T222241 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

channel ORA_DISK_1: deleting archived log(s)

archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_02_17/o1_mf_1_12_9j46sjw0_.arc RECID=222 STAMP=839802160

Finished backup at 2014-02-17 22:22:42

Starting Control File and SPFILE Autobackup at 2014-02-17 22:22:42

piece handle=/u01/app/oracle/flash_recovery_area/ORCL/autobackup/2014_02_17/o1_mf_s_839802162_9j46slmz_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 2014-02-17 22:22:43

3.备份某个指定的表空间

RMAN> backup tablespace jerry plus archivelog delete input;

Starting backup at 2014-02-17 22:26:54

current log archived

using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=13 RECID=223 STAMP=839802414

channel ORA_DISK_1: starting piece 1 at 2014-02-17 22:26:54

channel ORA_DISK_1: finished piece 1 at 2014-02-17 22:26:55

piece handle=/opt/oracle/obak/bkup_5kp0snhe_1_1 tag=TAG20140217T222654 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

channel ORA_DISK_1: deleting archived log(s)

archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_02_17/o1_mf_1_13_9j471gok_.arc RECID=223 STAMP=839802414

Finished backup at 2014-02-17 22:26:55

Starting backup at 2014-02-17 22:26:55

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00006 name=/u01/app/oracle/oradata/orcl/jerry.db

channel ORA_DISK_1: starting piece 1 at 2014-02-17 22:26:56

channel ORA_DISK_1: finished piece 1 at 2014-02-17 22:28:03

piece handle=/opt/oracle/obak/bkup_5lp0snhg_1_1 tag=TAG20140217T222655 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:01:07

Finished backup at 2014-02-17 22:28:03

Starting backup at 2014-02-17 22:28:03

current log archived

using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=14 RECID=224 STAMP=839802484

channel ORA_DISK_1: starting piece 1 at 2014-02-17 22:28:05

channel ORA_DISK_1: finished piece 1 at 2014-02-17 22:28:08

piece handle=/opt/oracle/obak/bkup_5mp0snjl_1_1 tag=TAG20140217T222804 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03

channel ORA_DISK_1: deleting archived log(s)

archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_02_17/o1_mf_1_14_9j473nsc_.arc RECID=224 STAMP=839802484

Finished backup at 2014-02-17 22:28:08

Starting Control File and SPFILE Autobackup at 2014-02-17 22:28:09

piece handle=/u01/app/oracle/flash_recovery_area/ORCL/autobackup/2014_02_17/o1_mf_s_839802489_9j473ts8_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 2014-02-17 22:28:12

4.备份归档日志

RMAN> backup archivelog all delete input;

Starting backup at 2014-02-17 22:29:52

current log archived

using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=15 RECID=225 STAMP=839802593

channel ORA_DISK_1: starting piece 1 at 2014-02-17 22:29:53

channel ORA_DISK_1: finished piece 1 at 2014-02-17 22:29:54

piece handle=/opt/oracle/obak/bkup_5op0snn1_1_1 tag=TAG20140217T222953 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

channel ORA_DISK_1: deleting archived log(s)

archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_02_17/o1_mf_1_15_9j4771kt_.arc RECID=225 STAMP=839802593

Finished backup at 2014-02-17 22:29:54

Starting Control File and SPFILE Autobackup at 2014-02-17 22:29:54

piece handle=/u01/app/oracle/flash_recovery_area/ORCL/autobackup/2014_02_17/o1_mf_s_839802594_9j47735p_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 2014-02-17 22:29:5

5.使用list backup查看备份信息

RMAN> list backupset;

...................

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

154     Full    1.29G      DISK        00:00:57     2014-02-17 22:27:53

BP Key: 154   Status: AVAILABLE  Compressed: NO  Tag: TAG20140217T222655

Piece Name: /opt/oracle/obak/bkup_5lp0snhg_1_1

List of Datafiles in backup set 154

File LV Type Ckp SCN    Ckp Time            Name

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

6       Full 10018739   2014-02-17 22:26:56 /u01/app/oracle/oradata/orcl/jerry.db

..............................................

全备和0级增量备份

二者区别在于0级备份可以做为增量备份的基础。

可以使用批处理命令

run{

allocate channel channel1 type disk;

backup

format '/u01/app/oracle/flash_recovery_area/fullbackup_%t'

tag jerryfullbackup

database;

release channel channel1;

}

format:

%c：备份片的拷贝数(从1开始编号)；

%d：数据库名称；

%D：位于该月中的天数(DD)；

%M：位于该年中的月份(MM)；

%F：一个基于DBID唯一的名称，这个格式的形式为c-xxx-YYYYMMDD-QQ,其中xxx位该数据库的DBID，YYYYMMDD为日期，QQ是一个1-256的序列；

%n：数据库名称，并且会在右侧用x字符进行填充，使其保持长度为8；

%u：是一个由备份集编号和建立时间压缩后组成的8字符名称。利用%u可以为每个备份集产生一个唯一的名称；

%p：表示备份集中的备份片的编号，从1开始编号；

%U：是%u_%p_%c的简写形式，利用它可以为每一个备份片段(既磁盘文件)生成一个唯一的名称.

%t：备份集时间戳;

%T:年月日格式(YYYYMMDD);

channel指RMAN和目标数据库之间的连接，在目标数据库启动分配一个服务器进程，用来执行操作。

RMAN备份方案参考：

1.周日晚上      -level 0 backup performed

2.周一晚上      -level 2 backup performed

3.周二晚上      -level 2 backup performed

4.周三晚上      -level 1 backup performed

5.周四晚上      -level 2 backup performed

6.周五晚上      -level 2 backup performed

7.周六晚上      -level 2 backup performed

如果周二需要恢复的话，只需要1+2,

如果周四需要恢复的话，只需要1+4,

如果周五需要恢复的话，只需要1+4+5,

如果周六需要恢复的话，只需要1+4+5+6

阅读(1224) | 评论(0) | 转发(1) |