ORACLE RMAN备份的学习

--RMAN

 

PATH：$ORACLE_HOME/bin

1.数据库的运行状态

nomount  init.ora spfile.ora

mount  controlfile

open  打开数据库检查所有文件处于同一时间点，对错误进行恢复对未完成的事务做回滚，并最终可以准时用户访问

 

2.归档模式

数据库使用归档日志来完成rman的备份

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /opt/oracle/archivelog

Oldest online log sequence     1

Next log sequence to archive   2

Current log sequence           2

 

for 10g

shutdown immediate

startup mount

alter database archivelog;

alter database open;

 

for 9i

shutdown immediate

startup mount

alter database archivelog;

alter system archive log start;(必须执行，否则会宕机)

(alter system set log_archive_start=true scope=spfile;------******------)

alter database open;

 

 3.alter system set log_archive_dest_1="location=/opt/oracle/archivelog/";

log_archive_format  string %t_%s_%r.dbf 文件保存的格式

 

4.rman的环境和配置

a.建产rman表空间

SQL> create tablespace rman datafile '/sda8/rman/rman.dbf'

  2  size 500m extent management local segment space management auto;

 

Tablespace created.

 

b.创建用户

SQL> create user rman identified by rman default tablespace rman;

 

User created.

 

c.授权

SQL> grant connect,resource,recovery_catalog_owner to rman;

 

Grant succeeded.

 

d.登录

[oracle@pi database]$ rman  target /

 

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Nov 3 14:45:14 2009

 

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

 

connected to target database: HAO (DBID=1164604165)

 

RMAN>

 

5.rman基本配置

RMAN> show all;

 

using target database control file instead of recovery catalog

RMAN configuration parameters are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default  1、配置备份策略

CONFIGURE BACKUP OPTIMIZATION OFF; # default           2、配置备份优化

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default       3、配置设备类型

CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default        4、配置控制文件自动备份

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default

                                                       5、配置控制文件备份命名方式

                                                       6、配置控制文件备份路径

CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default

                                                       7、配置备份并行度

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

                8、配置数据/归档文件的冗余数量

CONFIGURE MAXSETSIZE TO UNLIMITED; # default           9、配置备份片段最大尺寸

CONFIGURE ENCRYPTION FOR DATABASE OFF; # default       10、配置备份是否启用加密模式

CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default     11、配置备份加密算法

CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default                                                 12、配置备份压缩方式

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default  13配置归档文件的删除策略

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/data/oracle/product/11.2/db_1/dbs/snapcf_pmh.f'; # default                           14、配置控制文件快照，用于控制文件的读一致性

以下为命令修改参数设置:

RMAN>configure retention policy CLEAR;   RMAN配置参数重新重置为默认值

RMAN>configure retention policy to recovery WINDOW OF n days;   基于时间的保留策略.

RMAN>configure retention policy to recovery REDUNDANCY n; 基于冗余数量的保留策略(默认为1)

 

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 3 DAYS; --保留三天

CONFIGURE DEFAULT DEVICE TYPE TO ‘SBT_TAPE’;

CONFIGURE DEFAULT DEVICE TYPE TO DISK;

CONFIGURE DEVICE TYPE DISK PARALLELISM 2;

CONFIGURE CONTROLFILE AUTOBACKUP on;

CONFIGURE RETENTION POLICY TO REDUNDANCY 2;

 

configure channel 1 device type disk format '/opt/oracle/database/temp1/rmanback_%u';

 

backup controlfile

run {

allocate channel a1 type disk;

allocate channel a2 type disk;

backup format '/opt/oracle/database/temp1/ctl_%u_%c_%p.ctl' current controlfile;

release channel a1;

release channel a2;

}

sql 'alter system archive log current'; ----在RMAN里面执行SQL语句，切换归档日志

delete noprompt obsolete;  ----------删除久的，不提示直接删除

run {

allocate channel a1 device type disk;

allocate channel a2 device type disk;

crosscheck backup;

crosscheck archivelog all;

sql 'alter system archive log current';

backup database format '/opt/oracle/database/temp1/dataall_%d_%T_%U';

sql 'alter system archive log current';

backup archivelog all format '/sda8/log/archive_%t_%s' delete all input;

backup format '/sda8/log/con_%s_%p' current controlfile;

crosscheck backup;

crosscheck archivelog all;

delete expired backup;  删除过期 

delete noprompt obsolete;

delete noprompt backup of database completed before 'sysdate -15';

delete noprompt archivelog all;

delete noprompt backup of archivelog all completed before 'sysdate -15';

release channel a1;

release channel a2;

}

 

 

备份格式

备份文件自定义格式：

%d 数据库名称

%c 备份片的拷贝数

%D 位于该月的第几天    -----常用

%M 位于该年的第几月

%F 一个基于dbid唯一的名称，这个格式的形式比较全面c-iiiiiiiiii-yyyymmdd-qq -----常用

 iiiiiiiiii:dbid yyyymmdd为日期 qq:1-256的序列号

 

 SQL> select dbid,name from v$database;

 

 DBID   NAME

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

 1164604165  HAO

%n 数据库名称，向右补足到最大8个字符

%u 一个八个字符的名称代表备份集于创建时间

%U 一个唯一的文件名，代表%u_%p_%c  -----常用

%P 该备份集的备份片号，从1开始到创建的文件数

%t 备份集的时间戳

%T yyyymmdd

 

查看备份文件的大小

SQL> select sum(bytes)/1024/1024 from dba_data_files;

 

SUM(BYTES)/1024/1024

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

                1590

 

SQL> select sum(bytes)/1024/1024 from dba_free_space;

 

SUM(BYTES)/1024/1024

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

             707.375

临控事务运行时间

select sid,serial#,context,sofar,totalwork,round(sofar/totalwork*100,2) "%_precent"

  from v$session_longops

  where opname like 'RMAN%%'

  and opname not like '%aggregate%'

  and totalwork!=0

  and sofar<>totalwork;

       SID    SERIAL#    CONTEXT      SOFAR  TOTALWORK  %_precent

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

       141         53          1      92350     128000      72.15

       144         58          1      69375      75520      91.86

 

断点续备

backup not backup since time 'sysdate -1'database plus archivelog;

RMAN数据恢复

A.整个数据库恢复

1.shutdown immediate

2.startup mount

3.rman target /

4.restore database;

5.recover database;

6.alter database open;

 

B.控制文件恢复

1.shutdown immediate

2.startup nomount

3.rman target /

4.第一种方法

RMAN＞ run{

2> allocate channel d1 type disk;

3> restore controlfile from '/opt/oracle/flash_recovery_area/HAO/autobackup/2009_11_05/o1_mf_s_702121736_5h4cr8kf_.bkp';

4> alter database mount;

5> recover database;

6> alter database open resetlogs;

7> }

5.第二种方法

RMAN> run {

2> allocate channel t1 type disk;

3> restore controlfile from autobackup;

4> alter database mount;

5> recover database;

6> alter database open resetlogs;

7> }

 

C.DBF文件被RM了

1.查看alert.log文件，查出那个文件丢失

2.在数据库查询对应DBF文件的编号

SQL> select file#,name,status from v$datafile;

 

     FILE# NAME                                     STATUS

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

         1 /opt/oracle/oradata/hao/system01.dbf     SYSTEM

         2 /opt/oracle/oradata/hao/undotbs01.dbf    ONLINE

         3 /opt/oracle/oradata/hao/sysaux01.dbf     ONLINE

         4 /opt/oracle/oradata/hao/users01.dbf      ONLINE

         5 /opt/oracle/oradata/hao/example01.dbf    ONLINE

         6 /sda8/rman/rman.dbf                      ONLINE

         7 /opt/oracle/database/temp1/test1.dbf     ONLINE

         8 /opt/oracle/database/temp1/undo1.dbf     ONLINE

         9 /opt/oracle/database/temp1/test12.dbf    ONLINE

9 rows selected.

或者通过v$datafile,v$tablespace,v$recover_file

3.

SQL> startup force

ORACLE instance started.

Total System Global Area  603979776 bytes

Fixed Size                  1220796 bytes

Variable Size             285216580 bytes

Database Buffers          310378496 bytes

Redo Buffers                7163904 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 7 - see DBWR trace file

ORA-01110: data file 7: '/opt/oracle/database/temp1/test1.dbf'

 

4.run {

allocate channel t1 type disk;

sql 'alter database datafile 7 offline';

restore datafile 7;

recover datafile 7;

sql 'alter database datafile 7 online';

sql 'alter database open';

}

 

D.对DBF文件的存储或磁盘坏了

思路：

先找出那个DBF文件以及对应的编号，再将DBF对应的表空间offline,然后改名到其它路径

 

2.

run{

allocate channel t1 type disk;

sql 'alter database datafile x offline';

sql 'alter database open';

set newname for datafile x to '/opt/oracle/oradate/test1.dbf';

restore tablespace test1;

switch datafile x;

recover tablespace test1;

sql 'alter tablespace test1 online';

}

 

 E.全备之后，新建了tt.DBF文件，所有DBF被删除

run{

allocate channel t1 type disk;

restore datafile

sql 'alter database create datafile "/opt/oracle/oradata/tt.dbf" as "/opt/oracle/oradata/tt.dbf";

recover database;

sql 'alter database open';

}

 

F.全库恢复

1.startup nomount

2.restore controlfile from autobackup;

3.sql 'alter database mount';

4.restore database;

5.sql "alter session set nls_date_format=''yyyy.mm.dd hh24:mi:ss''";

6.recover database until time '2009.11.05 15:23:00';(全备之后的时间)