full_backup_zydb

—数据块恢复
blockrecover datafile 13 block 72529

查看历史连接
select username ,count(*) from vsession_longops a where a.LAST_UPDATE_TIME > trunc(sysdate)-1 group by username; ---查看连接 select username ,machine,count(*) from vsession a group by username,machine;
----------rman

-------建立恢复目录步骤
1.-------------create tablespace
create tablespace rmancatalog
datafile ‘D:\oracletablespace\rman1.dbf’ size 512M reuse
autoextend on next 2M maxsize unlimited
nologging
extent management local autoallocate
segment space management auto;

2.--------------create catalog owner
create user rman1
identified by rman1
default tablespace rmancatalog
temporary tablespace TEMP
profile DEFAULT
quota unlimited on rmancatalog;

3.-------------grant privileges
grant connect to rman1;
grant resource to rman1;
grant recovery_catalog_owner to rman1;

4.-------------create catalog
–连上目录数据库
C:\Documents and Settings\lix>rman catalog /rman
RMAN> create catalog tablespace rmancatalog;

5.------connect with target database
RMAN> @bbs;

6.-------register target database

rman catalog rman/rman
connect target system/zeo5o1fly
show all;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE CONTROLFILE AUTOBACKUP on;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘/tol/backup/rman_bak/%F’;
CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT “/tol/backup/rman_bak/bbs_%U”;
change archivelog all crosscheck
list expired archivelog all;
delete expired archivelog all;
RMAN> crosscheck archivelog all;
RMAN> delete noprompt expired backup;
crosscheck backup;
RMAN> list EXPIRED backup;
RMAN> delete EXPIRED backup;
list backup of archivelog all;
CROSSCHECK BACKUP OF DATABASE;
CROSSCHECK copy OF DATABASE;
–备份所有的归档日志
run {
allocate channel c1 type disk;
sql ‘alter system archive log current’;
backup archivelog all delete input;
release channel c1;
}

CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE CONTROLFILE AUTOBACKUP on;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘/tol/backup/rman_bak/%F’;
CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT “/tol/backup/rman_bak/bbs_%U”;
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO BACKUPSET PARALLELISM 2;

–全备
run {
allocate channel dev1 type disk;
allocate channel dev2 type disk;
allocate channel dev3 type disk;
backup
full
tag ‘full_db_backup’
format ‘/tol/backup/rman_bak/full%u_%s_%p’
(database);
release channel dev1;
release channel dev2;
release channel dev3;

  }

–零级备份
run {
allocate channel dev1 type disk;
allocate channel dev2 type disk;
allocate channel dev3 type disk;
backup
incremental level 0
tag ‘increment_level0’
filesperset 4
format ‘/tol/backup/rman_bak/increment_level0/incremen_db0%u_%s_%p’
database skip readonly;
sql ‘alter system archive log current’;
backup format ‘/tol/oraarchived/arc/arch%u_%s_%p’
archivelog all delete input;
release channel dev1;
release channel dev2;
release channel dev3;
}
–一级备份
run {
allocate channel dev1 type disk;
allocate channel dev2 type disk;
allocate channel dev3 type disk;
backup
incremental level 1
tag ‘increment_level1’
filesperset 4
format ‘/tol/backup/rman_bak/increment_level1/incremen_db1%u_%s_%p’
database skip readonly;
sql ‘alter system archive log current’;
backup format ‘/tol/oraarchived/arc/arch%u_%s_%p’
archivelog all delete input;
release channel dev1;
release channel dev2;
release channel dev3;
}

run {
allocate channel dev1 type disk;
allocate channel dev2 type disk;
allocate channel dev3 type disk;
backup
incremental level 2
tag ‘increment_level2’
format ‘/tol/backup/rman_bak/increment_level2/incremen_db2%u_%s_%p’
database skip readonly;
sql ‘alter system archive log current’;
backup format ‘/tol/oraarchived/arc/arch%u_%s_%p’
archivelog all delete input;
release channel dev1;
release channel dev2;
release channel dev3;
}

–建立脚本(shell) rmanback.sh,在 crontab 中配置
—1.全备
#!/bin/sh
#set env
export ORACLE_HOME=/tol/apps/oracle/ora10g
export ORACLE_SID=bbs
export NLS_LANG=“AMERICAN_AMERICA.zhs16gbk”
export PATH=$PATH:$ORACLE_HOME/bin:/sbin:/usr/sbin
echo “-----------------------------start-----------------------------”;date
#backup start
$ORACLE_HOME/bin/rman catalog rman/rman <<EOF
connect target system/zeo5o1fly
#CROSSCHECK
CROSSCHECK BACKUP OF DATABASE;
#删除EXPIRED文件
DELETE NOPROMPT EXPIRED BACKUP;
#删除陈旧文件
DELETE NOPROMPT OBSOLETE;
backup database include current controlfile format ‘/tol/backup/rman_bak/%U_%s.dbf’ filesperset = 4;
run {
allocate channel dev1 type disk;
allocate channel dev2 type disk;
allocate channel dev3 type disk;
backup
full
tag ‘full_db_backup’
format ‘/tol/backup/rman_bak/full%u_%s_%p’
(database);
release channel dev1;
release channel dev2;
release channel dev3;

  }

list backup;
exit;
EOF
echo “------------------------------end------------------------------”;
—2.零次备份
#!/bin/sh
#set env
export ORACLE_HOME=/tol/apps/oracle/ora10g
export ORACLE_SID=bbs
export NLS_LANG=“AMERICAN_AMERICA.zhs16gbk”
export PATH=$PATH:$ORACLE_HOME/bin:/sbin:/usr/sbin
echo “-----------------------------start-----------------------------”;date
#backup start
$ORACLE_HOME/bin/rman catalog rman/rman <<EOF
connect target system/zeo5o1fly
#CROSSCHECK
CROSSCHECK BACKUP OF DATABASE;
#删除EXPIRED文件
DELETE NOPROMPT EXPIRED BACKUP;
#删除陈旧文件
DELETE NOPROMPT OBSOLETE;
backup database include current controlfile format ‘/tol/backup/rman_bak/%U_%s.dbf’ filesperset = 4;
run {
allocate channel dev1 type disk;
allocate channel dev2 type disk;
allocate channel dev3 type disk;
backup
incremental level 0
tag ‘increment_level0’
filesperset 4
format ‘/tol/backup/rman_bak/increment_level0/incremen_db0%u_%s_%p’
database skip readonly;
sql ‘alter system archive log current’;
backup format ‘/tol/oraarchived/arc/arch%u_%s_%p’
archivelog all delete input;
release channel dev1;
release channel dev2;
release channel dev3;
}
list backup;
run {
allocate channel dev1 type disk;
backup archivelog all delete input format ‘/tol/backup/rman_bak/arch_%d_%u_%s’;
release channel dev1;
}
exit;
EOF
echo “------------------------------end------------------------------”;
—零次备份后做日志备份

—3.一次备份后做日志备份
#!/bin/sh
#set env
export ORACLE_HOME=/tol/apps/oracle/ora10g
export ORACLE_SID=bbs
export NLS_LANG=“AMERICAN_AMERICA.zhs16gbk”
export PATH=$PATH:$ORACLE_HOME/bin:/sbin:/usr/sbin
echo “-----------------------------start-----------------------------”;date
#backup start
$ORACLE_HOME/bin/rman catalog rman/rman <<EOF
connect target system/zeo5o1fly
#CROSSCHECK
CROSSCHECK BACKUP OF DATABASE;
#删除EXPIRED文件
DELETE NOPROMPT EXPIRED BACKUP;
#删除陈旧文件
DELETE NOPROMPT OBSOLETE;
backup database include current controlfile format ‘/tol/backup/rman_bak/%U_%s.dbf’ filesperset = 4;
run {
allocate channel dev1 type disk;
allocate channel dev2 type disk;
allocate channel dev3 type disk;
backup
incremental level 1
tag ‘increment_level1’
filesperset 4
format ‘/tol/backup/rman_bak/increment_level1/incremen_db1%u_%s_%p’
database skip readonly;
sql ‘alter system archive log current’;
backup format ‘/tol/oraarchived/arc/arch%u_%s_%p’
archivelog all delete input;
release channel dev1;
release channel dev2;
release channel dev3;
}
list backup;
run {
allocate channel dev1 type disk;
backup archivelog all delete input format ‘/tol/backup/rman_bak/arch_%d_%u_%s’;
release channel dev1;
}
exit;
EOF
echo “------------------------------end------------------------------”;

—4.二次备份后做日志备份
#!/bin/sh
#set env
export ORACLE_HOME=/tol/apps/oracle/ora10g
export ORACLE_SID=bbs
export NLS_LANG=“AMERICAN_AMERICA.zhs16gbk”
export PATH=$PATH:$ORACLE_HOME/bin:/sbin:/usr/sbin
echo “-----------------------------start-----------------------------”;date
#backup start
$ORACLE_HOME/bin/rman catalog rman/rman <<EOF
connect target system/zeo5o1fly
#CROSSCHECK
CROSSCHECK BACKUP OF DATABASE;
#删除EXPIRED文件
DELETE NOPROMPT EXPIRED BACKUP;
#删除陈旧文件
DELETE NOPROMPT OBSOLETE;
backup database include current controlfile format ‘/tol/backup/rman_bak/%U_%s.dbf’ filesperset = 4;
run {
allocate channel dev1 type disk;
allocate channel dev2 type disk;
allocate channel dev3 type disk;
backup
incremental level 2
tag ‘increment_level2’
format ‘/tol/backup/rman_bak/increment_level2/incremen_db2%u_%s_%p’
database skip readonly;
sql ‘alter system archive log current’;
backup format ‘/tol/oraarchived/arc/arch%u_%s_%p’
archivelog all delete input;
release channel dev1;
release channel dev2;
release channel dev3;
}
list backup;
run {
allocate channel dev1 type disk;
backup archivelog all delete input format ‘/tol/backup/rman_bak/arch_%d_%u_%s’;
release channel dev1;
}
exit;
EOF
echo “------------------------------end------------------------------”;

run {
#设置控制文件自动备份
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP format for device type disk to ‘D:\rman\ctl_%F’;
#设置备份保持策略14天
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS;
#执行一个检查点的命令
sql ‘alter system archive log current’;
#备份至2个地方，并删除已归档的日志。在线日志已归档且控制文件，归档日志已备份
BACKUP as compressed backupset database tag ‘FULL’ DEVICE TYPE DISK COPIES 2 FORMAT ‘D:\RMAN\RMAN_%’
#备份至2个地方，并删除已归档的日志。在线日志已归档且控制文件，归档日志已备份
BACKUP as compressed backupset database tag ‘FULL’ DEVICE TYPE DISK COPIES 2 FORMAT ‘D:\RMAN\RMAN_%T_%c.DB’,
‘C:\RMAN\RMAN_%T_%c.DB’ ARCHIVELOG ALL DELETE INPUT;
#CROSSCHECK
CROSSCHECK BACKUP OF DATABASE;
#删除EXPIRED文件
DELETE NOPROMPT EXPIRED BACKUP;
#报告陈旧文件
REPORT OBSOLETE;
#删除陈旧文件
DELETE NOPROMPT OBSOLETE;
#报告需要备份的数据文件
report need backup days 7;
}
-------------备份数据库
run {
allocate channel t1 type disk;
backup current controlfile format ‘/u01/ora_backup/rman/%d_%u_%s’;
backup database format ‘/u01/ora_backup/rman/%d_%u_%s’;
backup archivelog all delete input format ‘/u01/ora_backup/rman/arch_%d_%u_%s’;
release channel t1;
}
-------------------备份归档日志
run {
allocate channel dev1 type disk;
backup archivelog all delete input format ‘/tol/backup/rman_bak/arch_%d_%u_%s’;
release channel dev1;
}
—冷备
Cold backup (archivelog or noarchivelog mode)

run {
allocate channel t1 type disk;
shutdown immediate;
startup mount;
backup database include current controlfile format ‘/u01/ora_backup/rman/%d_%u_%s’;
alter database open;
}
---------------恢复数据库
Restore/recover a database
Full restore and recovery

startup nomount;
run {
allocate channel t1 type disk;
allocate channel t2 type disk;
allocate channel t3 type disk;
allocate channel t4 type disk;
restore controlfile;
restore archivelog all;
alter database mount;
restore database;
recover database;
}
sql ‘alter database open resetlogs’;
----------Restore and roll forward to a point in time

startup nomount;
run {
set until time =“to_date(‘30/08/2006 12:00’,‘dd/mm/yyyy hh24:mi’)”;
allocate channel t1 type disk;
allocate channel t2 type disk;
allocate channel t3 type disk;
allocate channel t4 type disk;
restore controlfile;
restore archivelog all;
alter database mount;
restore database;
recover database;
}
sql ‘alter database open resetlogs’;
---------Show the controlfile backup record
set pages 999 lines 100
col name format a60
break on set_stamp skip 1
select set_stamp
, to_char(ba.completion_time, ‘HH24:MI DD/MM/YY’) finish_time
, df.name
from v$datafile df , v$backup_datafile ba
where df.file# = ba.file#
and ba.file# != 0
order by set_stamp, ba.file#
/

rman target system/ zeo5o1fly catalog rman/rman
–确认备份的有效
backup validate database archivelog all;
list backupset;
list backup of database;
list backup of archivelog all;
report obsolete;
report obsolete redundancy = 2;
delete obsolete; - remove unneeded backups
restore database validate; - check the backup
report unrecoverable;
report schema; - show current db files
crosscheck backup; - make sure the backups in the catalog still physically exist
delete expired backup; - delete epired backups found by crosscheck
rman target sys/@scr10 catalog rman/rman@dbarep
LIST BACKUPSET OF DATABASE;
ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE DISK;
DELETE OBSOLETE REDUNDANCY = 4 device type disk;
delete obsolete REDUNDANCY = 2 device type disk;

Run
{
allocate channel ch1 type disk format ‘***_ORABACKUP/%U.bck'; allocate channel ch2 type disk format '_ORABACKUP/%U.bck’;
allocate channel ch3 type disk format '***_ORABACKUP/%U.bck'; allocate channel ch4 type disk format '_ORABACKUP/%U.bck’;
allocate channel ch5 type disk format ‘***_ORABACKUP/%U.bck'; set limit channel ch1 kbytes 2024800; set limit channel ch2 kbytes 2024800; set limit channel ch3 kbytes 2024800; set limit channel ch4 kbytes 2024800; set limit channel ch5 kbytes 2024800; backup database; sql 'ALTER SYSTEM ARCHIVE LOG CURRENT'; backup archivelog all format '***ORABACKUP/al%U.bck’;
backup current controlfile
tag = cf1
format '***_ORABACKUP/cf_%U.bck'; } ----------------Rman参数配置及使用方法 1．configure retention policy to redundancy 1: 是用来决定那些备份不再需要了，它一共有三种可选项，分别是 CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;sysdate-(select checkpoint_time from vdatafile;)>=7
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
CONFIGURE RETENTION POLICY TO NONE;
第一种recover window是保持所有足够的备份，可以将数据库系统恢复到最近七天内的任意时刻。任何超过最近七天的数据库备份将被标记为obsolete。

第二种redundancy 是为了保持可以恢复的最新的5份数据库备份，任何超过最新5份的备份都将被标记为redundancy。它的默认值是1份。

第三种不需要保持策略，clear将恢复回默认的保持策略。

一般最安全的方法是采用第二种保持策略。

2．CONFIGURE BACKUP OPTIMIZATION OFF
默认值为关闭，如果打开，rman将对备份的数据文件及归档等文件进行一种优化的算法。
3．Configure default device type to disk:
是指定所有I/O操作的设备类型是硬盘或者磁带，默认值是硬盘
磁带的设置是CONFIGURE DEFAULT DEVICE TYPE TO SBT;

4．CONFIGURE CONTROLFILE AUTOBACKUP OFF
强制数据库在备份文件或者执行改变数据库结构的命令之后将控制文件自动备份，默认值为关闭。这样可以避免控制文件和catalog丢失后，控制文件仍然可以恢复。

5．CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘%F’
是配置控制文件的备份路径和备份格式

6．CONFIGURE DEVICE TYPE DISK PARALLELISM 1;
是配置数据库设备类型的并行度。

7．CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
是配置数据库的每次备份的copy数量，oracle的每一次备份都可以有多份完全相同的拷贝。

8．CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1
是设置数据库的归档日志的存放设备类型

9．CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘C:ORACLE… SNCFTEST.ORA’
是配置控制文件的快照文件的存放路径和文件名，这个快照文件是在备份期间产生的，用于控制文件的读一致性。

10．CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT ‘C:…%d_DB_%u_%s_%p’;
是配置备份文件的备份路径和备份格式

CONFIGURE CHANNEL DISK CLEAR; 用于清除上面的信道配置

CONFIGURE EXCLUDE FOR TABLESPACE [CLEAR]; 此命令用于将指定的表空间不备份到备份集中， 此命令对只读表空间是非常有用的。

1.将备份文件存储到指定路径，如 e:\backupb
configure channel device type disk format ‘e:\backupb%d_db_%u’;
后面的%d_db_%u是存储格式
2.我们也可以单独指定control file 存储在另一个路径：如 e:\backupcontrol
configure controlfile autobackup format for device type disk to ‘e:\backupcontrol%F’;
后面的%F是存储格式

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

RMAN中常用configure命令2007-06-21 15:37
1 显示当前的配置信息

1.01 RMAN> show all;
1.02 查询RMAN设置中非默认值:
SQL> select name,value from v$rman_configure;

2. 常用的configure选项
   ===========================
   2.01 保存策略 (retention policy)
   configure retention policy to recovery window of 3 days;
   configure retention policy to redundancy 3;
   configure retention policy clear;

2.02 备份优化 backup optimization
configure backup optimization on;
configure backup optimization off;
configure backup optimization clear;

2.03 默认设备 default device type
configure default device type to disk;
configure default device type to stb;
configure default device type clear;

2.04 控制文件 controlfile
configure controlfile autobackup on;
configure controlfile autobackup format for device type disk to ‘/cfs01/backup/conf/conf_%F’;
configure controlfile autobackup clear;
configrue controlfile autobackup format for device type disk clear;
configrue snapshot controlfile name to ‘/cfs01/backup/snapcf/scontrofile.snp’;
configrue snapshot controlfile name clear;

2.05 并行数(通道数) device type disk|stb pallelism n;
configure device type disk|stb parallelism 2;
configure device type disk|stb clear;
configure channel device type disk format ‘e/:rmanback_%U’;
configure channel device type disk maxpiecesize 100m
configure channel device type disk rate 1200K
configure channel 1 device type disk format ‘e/:rmanback_%U’;
configure channel 2 device type disk format ‘e/:rmanback_%U’;
configure channel 1 device type disk maxpiecesize 100m

2.06 生成备份副本 datafile|archivelog backup copies
configure datafile backup copies for device type disk|stb to 3;
configure archivelog backup copies for device type disk|stb to 3;
configure datafile|archivelog backup copies for device type disk|stb clear
BACKUP DEVICE TYPE DISK DATABASE
FORMAT ‘/disk1/backup/%U’, ‘/disk2/backup/%U’, ‘/disk3/backup/%U’;

2.07 排除选项 exclude
configure exclude for tablespace ‘users’;
configrue exclude clear;

2.08 备份集大小 maxsetsize
configure maxsetsize to 1G|1000M|1000000K|unlimited;
configure maxsetsize clear;

2.09 其它选项 auxiliary
CONFIGURE AUXNAME FOR DATAFILE 1 TO ‘/oracle/auxfiles/aux_1.f’;
CONFIGURE AUXNAME FOR DATAFILE 2 TO ‘/oracle/auxfiles/aux_2.f’;
CONFIGURE AUXNAME FOR DATAFILE 3 TO ‘/oracle/auxfiles/aux_3.f’;
CONFIGURE AUXNAME FOR DATAFILE 4 TO ‘/oracle/auxfiles/aux_4.f’;

CONFIGURE AUXNAME FOR DATAFILE 1 CLEAR;
CONFIGURE AUXNAME FOR DATAFILE 2 CLEAR;
CONFIGURE AUXNAME FOR DATAFILE 3 CLEAR;
CONFIGURE AUXNAME FOR DATAFILE 4 CLEAR;

Rman的format格式中的%
%c 备份片的拷贝数
%d 数据库名称
%D 位于该月中的第几天 (DD)
%M 位于该年中的第几月 (MM)
%F 一个基于DBID唯一的名称,这个格式的形式为c-IIIIIIIIII-YYYYMMDD-QQ,其中IIIIIIIIII为该数据库的DBID，YYYYMMDD为
日期，QQ是一个1-256的序列
%n 数据库名称，向右填补到最大八个字符
%u 一个八个字符的名称代表备份集与创建时间
%p 该备份集中的备份片号，从1开始到创建的文件数
%U 一个唯一的文件名，代表%u_%p_%c
%s 备份集的号
%t 备份集时间戳
%T 年月日格式(YYYYMMDD)

Restartable backups allow failed backups to be restarted where they left off using the SINCE TIME option:

BACKUP DATABASE NOT BACKED UP SINCE TIME ‘15-JAN-01 01:00:00’;
BACKUP DATABASE NOT BACKED UP SINCE TIME ‘SYSDATE - 2’;

-----------------------查询写到每个数据文件的blocks数目
select file#,incremental_level,completion_time,blocks,datafile_blocks/1024/1024
from v$backup_datafile
where incremental_level > 0
and completion_time>trunc(sysdate)
order by file#,completion_time;

--------强制删除备份文件
DELETE NOPROMPT force BACKUP;
delete noprompt force copy;
-----备份时排除表空间
configure exclude for tablespace users;
—使排除的表空间exclude实效
configure exclude for tablespace users clear;

CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE CONTROLFILE AUTOBACKUP on;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘/tol/backup/rman_bak/%F’;
CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT “/tol/backup/rman_bak/bbs_%U”;
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO BACKUPSET PARALLELISM 2;

–全备
run {
allocate channel dev1 type disk;
allocate channel dev2 type disk;
allocate channel dev3 type disk;
backup
full
tag ‘full_db_backup’
format ‘/tol/backup/rman_bak/full%u_%s_%p’
(database);
release channel dev1;
release channel dev2;
release channel dev3;

  }

–零级备份
run {
allocate channel dev1 type disk;
allocate channel dev2 type disk;
allocate channel dev3 type disk;
backup
incremental level 0
tag ‘increment_level0’
filesperset 4
format ‘/tol/backup/rman_bak/increment_level0/incremen_db0%u_%s_%p’
database skip readonly;
sql ‘alter system archive log current’;
backup format ‘/tol/oraarchived/arc/arch%u_%s_%p’
archivelog all delete input;
release channel dev1;
release channel dev2;
release channel dev3;
}
–一级备份
run {
allocate channel dev1 type disk;
allocate channel dev2 type disk;
allocate channel dev3 type disk;
backup
incremental level 1
tag ‘increment_level1’
filesperset 4
format ‘/tol/backup/rman_bak/increment_level1/incremen_db1%u_%s_%p’
database skip readonly;
sql ‘alter system archive log current’;
backup format ‘/tol/oraarchived/arc/arch%u_%s_%p’
archivelog all delete input;
release channel dev1;
release channel dev2;
release channel dev3;
}

run {
allocate channel dev1 type disk;
allocate channel dev2 type disk;
allocate channel dev3 type disk;
backup
incremental level 2
tag ‘increment_level2’
format ‘/tol/backup/rman_bak/increment_level2/incremen_db2%u_%s_%p’
database skip readonly;
sql ‘alter system archive log current’;
backup format ‘/tol/oraarchived/arc/arch%u_%s_%p’
archivelog all delete input;
release channel dev1;
release channel dev2;
release channel dev3;
}
--------强制删除备份或copy文件
DELETE NOPROMPT force BACKUP;
delete noprompt force copy;

–建立脚本(shell) rmanback.sh,在 crontab 中配置
—1.全备
#!/bin/sh
#set env
export ORACLE_HOME=/tol/app/oracle/product/10.2.0/db_1
export ORACLE_SID=bbs
export NLS_LANG=“AMERICAN_AMERICA.zhs16gbk”
export PATH=$PATH:$ORACLE_HOME/bin:/sbin:/usr/sbin
echo “-----------------------------start-----------------------------”;date
#backup start
$ORACLE_HOME/bin/rman catalog rman/rman <<EOF
connect target system/zeo5o1fly
delete noprompt obsolete;
#CROSSCHECK
CROSSCHECK BACKUP OF DATABASE;
#删除EXPIRED文件
DELETE NOPROMPT EXPIRED BACKUP;
backup database include current controlfile format ‘/tol/backup/rman_bak/%U_%s.dbf’ filesperset = 4;
run {
allocate channel dev1 type disk;
allocate channel dev2 type disk;
allocate channel dev3 type disk;
backup
full
tag ‘full_db_backup’
format ‘/tol/backup/rman_bak/full%u_%s_%p’
(database);
release channel dev1;
release channel dev2;
release channel dev3;

  }

list backup;
exit;
EOF
echo “------------------------------end------------------------------”;
—2.零次备份
#!/bin/sh
#set env
export ORACLE_HOME=/tol/app/oracle/product/10.2.0/db_1
export ORACLE_SID=bbs
export NLS_LANG=“AMERICAN_AMERICA.zhs16gbk”
export PATH=$PATH:$ORACLE_HOME/bin:/sbin:/usr/sbin
echo “-----------------------------start-----------------------------”;date
#backup start
$ORACLE_HOME/bin/rman catalog rman/rman <<EOF
connect target system/zeo5o1fly
delete noprompt obsolete;
#CROSSCHECK
CROSSCHECK BACKUP OF DATABASE;
#删除EXPIRED文件
DELETE NOPROMPT EXPIRED BACKUP;
backup database include current controlfile format ‘/tol/backup/rman_bak/%U_%s.dbf’ filesperset = 4;
run {
allocate channel dev1 type disk;
allocate channel dev2 type disk;
allocate channel dev3 type disk;
backup
incremental level 0
tag ‘increment_level0’
filesperset 4
format ‘/tol/backup/rman_bak/increment_level0/incremen_db0%u_%s_%p’
database NOT BACKED UP SINCE TIME ‘SYSDATE - 2’ skip readonly;
sql ‘alter system archive log current’;
backup format ‘/tol/oraarchived/arc/arch%u_%s_%p’
archivelog all delete input;
release channel dev1;
release channel dev2;
release channel dev3;
}
list backup;
run {
allocate channel dev1 type disk;
backup archivelog all delete input format ‘/tol/backup/rman_bak/arch_%d_%u_%s’;
release channel dev1;
}
exit;
EOF
echo “------------------------------end------------------------------”;
—零次备份后做日志备份

—3.一次备份后做日志备份
#!/bin/sh
#set env
export ORACLE_HOME=/tol/app/oracle/product/10.2.0/db_1
export ORACLE_SID=bbs
export NLS_LANG=“AMERICAN_AMERICA.zhs16gbk”
export PATH=$PATH:$ORACLE_HOME/bin:/sbin:/usr/sbin
echo “-----------------------------start-----------------------------”;date
#backup start
$ORACLE_HOME/bin/rman catalog rman/rman <<EOF
connect target system/zeo5o1fly
delete noprompt obsolete;
#CROSSCHECK
CROSSCHECK BACKUP OF DATABASE;
#删除EXPIRED文件
DELETE NOPROMPT EXPIRED BACKUP;
backup database include current controlfile format ‘/tol/backup/rman_bak/%U_%s.dbf’ filesperset = 4;
run {
allocate channel dev1 type disk;
allocate channel dev2 type disk;
allocate channel dev3 type disk;
backup
incremental level 1
tag ‘increment_level1’
filesperset 4
format ‘/tol/backup/rman_bak/increment_level1/incremen_db1%u_%s_%p’
database skip readonly;
sql ‘alter system archive log current’;
backup format ‘/tol/oraarchived/arc/arch%u_%s_%p’
archivelog all delete input;
release channel dev1;
release channel dev2;
release channel dev3;
}
list backup;
run {
allocate channel dev1 type disk;
backup archivelog all delete input format ‘/tol/backup/rman_bak/arch_%d_%u_%s’;
release channel dev1;
}
exit;
EOF
echo “------------------------------end------------------------------”;

—4.二次备份后做日志备份
#!/bin/sh
#set env
export ORACLE_HOME=/tol/app/oracle/product/10.2.0/db_1
export ORACLE_SID=bbs
export NLS_LANG=“AMERICAN_AMERICA.zhs16gbk”
export PATH=$PATH:$ORACLE_HOME/bin:/sbin:/usr/sbin
echo “-----------------------------start-----------------------------”;date
#backup start
$ORACLE_HOME/bin/rman catalog rman/rman <<EOF
connect target system/zeo5o1fly
delete noprompt obsolete;
#CROSSCHECK
CROSSCHECK BACKUP OF DATABASE;
#删除EXPIRED文件
DELETE NOPROMPT EXPIRED BACKUP;
backup database include current controlfile format ‘/tol/backup/rman_bak/%U_%s.dbf’ filesperset = 4;
run {
allocate channel dev1 type disk;
allocate channel dev2 type disk;
allocate channel dev3 type disk;
backup
incremental level 2
tag ‘increment_level2’
format ‘/tol/backup/rman_bak/increment_level2/incremen_db2%u_%s_%p’
database skip readonly;
sql ‘alter system archive log current’;
backup format ‘/tol/oraarchived/arc/arch%u_%s_%p’
archivelog all delete input;
release channel dev1;
release channel dev2;
release channel dev3;
}
list backup;
run {
allocate channel dev1 type disk;
backup archivelog all delete input format ‘/tol/backup/rman_bak/arch_%d_%u_%s’;
release channel dev1;
}
exit;
EOF
echo “------------------------------end------------------------------”;

run {
#设置控制文件自动备份
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP format for device type disk to ‘D:\rman\ctl_%F’;
#设置备份保持策略14天
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS;
#执行一个检查点的命令
sql ‘alter system archive log current’;
#备份至2个地方，并删除已归档的日志。在线日志已归档且控制文件，归档日志已备份
BACKUP as compressed backupset database tag ‘FULL’ DEVICE TYPE DISK COPIES 2 FORMAT ‘D:\RMAN\RMAN_%’
#备份至2个地方，并删除已归档的日志。在线日志已归档且控制文件，归档日志已备份
BACKUP as compressed backupset database tag ‘FULL’ DEVICE TYPE DISK COPIES 2 FORMAT ‘D:\RMAN\RMAN_%T_%c.DB’,
‘C:\RMAN\RMAN_%T_%c.DB’ ARCHIVELOG ALL DELETE INPUT;
#CROSSCHECK
CROSSCHECK BACKUP OF DATABASE;
#删除EXPIRED文件
DELETE NOPROMPT EXPIRED BACKUP;
#报告陈旧文件
REPORT OBSOLETE;
#删除陈旧文件
DELETE NOPROMPT OBSOLETE;
#报告需要备份的数据文件
report need backup days 7;
}
-------------备份数据库
run {
allocate channel t1 type disk;
backup current controlfile format ‘/u01/ora_backup/rman/%d_%u_%s’;
backup database format ‘/u01/ora_backup/rman/%d_%u_%s’;
backup archivelog all delete input format ‘/u01/ora_backup/rman/arch_%d_%u_%s’;
release channel t1;
}
-------------------备份归档日志
run {
allocate channel dev1 type disk;
backup archivelog all delete input format ‘/tol/backup/rman_bak/arch_%d_%u_%s’;
release channel dev1;
}
—冷备
Cold backup (archivelog or noarchivelog mode)

run {
allocate channel t1 type disk;
shutdown immediate;
startup mount;
backup database include current controlfile format ‘/u01/ora_backup/rman/%d_%u_%s’;
alter database open;
}
---------------恢复数据库
Restore/recover a database
Full restore and recovery

startup nomount;
run {
allocate channel t1 type disk;
allocate channel t2 type disk;
allocate channel t3 type disk;
allocate channel t4 type disk;
restore controlfile;
restore archivelog all;
alter database mount;
restore database;
recover database;
}
sql ‘alter database open resetlogs’;
----------Restore and roll forward to a point in time

startup nomount;
run {
set until time =“to_date(‘30/08/2006 12:00’,‘dd/mm/yyyy hh24:mi’)”;
allocate channel t1 type disk;
allocate channel t2 type disk;
allocate channel t3 type disk;
allocate channel t4 type disk;
restore controlfile;
restore archivelog all;
alter database mount;
restore database;
recover database;
}
sql ‘alter database open resetlogs’;
---------Show the controlfile backup record
set pages 999 lines 100
col name format a60
break on set_stamp skip 1
select set_stamp
, to_char(ba.completion_time, ‘HH24:MI DD/MM/YY’) finish_time
, df.name
from v$datafile df , v$backup_datafile ba
where df.file# = ba.file#
and ba.file# != 0
order by set_stamp, ba.file#
/

list backupset;
list backup of database;
list backup of archivelog all;
report obsolete;
report obsolete redundancy = 2;
delete obsolete; - remove unneeded backups
restore database validate; - check the backup
report unrecoverable;
report schema; - show current db files
crosscheck backup; - make sure the backups in the catalog still physically exist
delete expired backup; - delete epired backups found by crosscheck

LIST BACKUPSET OF DATABASE;
ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE DISK;
DELETE OBSOLETE REDUNDANCY = 4 device type disk;
delete obsolete REDUNDANCY = 2 device type disk;

-------------还原数据文件到新位置
run {
SQL ‘ALTER TABLESPACE users OFFLINE IMMEDIATE’;
SQL ‘ALTER TABLESPACE tools OFFLINE IMMEDIATE’;
# restore the datafile to a new location
SET NEWNAME FOR DATAFILE ‘/oradata/trgt/users01.dbf’ TO ‘+ORADATA1/users01.dbf’;
SET NEWNAME FOR DATAFILE ‘/oradata/trgt/tools01.dbf’ TO ‘+ORADATA1/tools01.dbf’;
RESTORE TABLESPACE users, tools;
SWITCH DATAFILE ALL; # point control file to new filenames
RECOVER TABLESPACE users, tools;
}
如果恢复成功，online 表空间：
SQL ‘ALTER TABLESPACE users ONLINE’;
SQL ‘ALTER TABLESPACE tools ONLINE’;

-----还原控制文件到新位置
run {
# or log sequence number.
# SET UNTIL TIME = ‘date_string’;
RESTORE CONTROLFILE TO ‘+ORADATA1/control01.ctl’; # restore to new location
# replicate the control file manually to CONTROL_FILES locations
RESTORE CONTROLFILE FROM ‘+ORADATA1/control01.ctl’;
STARTUP MOUNT;
}
执行介质恢复；
----------还原归档日志到新位置
RUN
{
SET ARCHIVELOG DESTINATION TO ‘+ORADATA1/temp_restore’;
RESTORE ARCHIVELOG ALL;

restore and recover datafiles as needed

.
.
.
}
指定多个还原位置，虽然不能同时指定这些归档目录。如：
RUN
{

Set a new location for logs 1 through 10.

SET ARCHIVELOG DESTINATION TO ‘/tmp’;
RESTORE ARCHIVELOG FROM SEQUENCE 1 UNTIL SEQUENCE 10;

Set a new location for logs 11 through 20.

SET ARCHIVELOG DESTINATION TO ‘/oradata’;
RESTORE ARCHIVELOG FROM SEQUENCE 11 UNTIL SEQUENCE 20;

Set a new location for logs 21 through 30.

SET ARCHIVELOG DESTINATION TO ‘/dbs’;
RESTORE ARCHIVELOG FROM SEQUENCE 21 UNTIL SEQUENCE 30;

restore and recover datafiles as needed

.
.
.
}
-------------------在新主机上还原数据库

/tol/oradata1/tol24/tol24/bjcnc_data01.dbf +ORADATA1/data1/oradata/dbbak/class_data02.dbf
/tol/oradata1/tol24/tol24/class_data01.dbf +ORADATA1/data2/dmpdir/class_data03.dbf
/tol/oradata1/tol24/tol24/class_data02.dbf +ORADATA1/data3/dmpdir/class_data01.dbf
/tol/oradata1/tol24/tol24/class_data03.dbf +ORADATA1/data2/dmpdir/class_index02.dbf
/tol/oradata1/tol24/tol24/class_index04.dbf +ORADATA1 /data2/dmpdir/class_index03.dbf
/tol/oradata1/tol24/tol24/class_index01.dbf +ORADATA1 /data3/dmpdir/class_index01.dbf
/tol/oradata1/tol24/tol24/class_index02.dbf +ORADATA1/data2/dmpdir/curr_course02.dbf
/tol/oradata1/tol24/tol24/class_index03.dbf +ORADATA1/data3/dmpdir/curr_course01.dbf
/tol/oradata1/tol24/tol24/class_index05.dbf +ORADATA1/data2/oradata/dbbak/kele01.dbf
/tol/oradata1/tol24/tol24/curr_course01.dbf +ORADATA1/data1/oradata/dbbak/lenovo02.dbf
/tol/oradata1/tol24/tol24/hlxk_data01.dbf +ORADATA1 /data2/dmpdir/lenovo02.dbf
/tol/oradata1/tol24/tol24/hlxk_index01.dbf +ORADATA1/data1/oradata/dbbak/rss02.dbf
/tol/oradata1/tol24/tol24/rss01.dbf +ORADATA1/data3/dmpdir/rss01.dbf
/tol/oradata1/tol24/tol24/rss02.dbf +ORADATA1/data1/oradata/dbbak/sysaux01.dbf
/tol/oradata1/tol24/tol24/sc_newclass.dbf +ORADATA1/data1/oradata/dbbak/system01.dbf
/tol/oradata1/tol24/tol24/sysaux01.dbf +ORADATA1 /data2/dmpdir/undotbs02.dbf
/tol/oradata1/tol24/tol24/system01.dbf +ORADATA1/data1/oradata/dbbak/users01.dbf
/tol/oradata1/tol24/tol24/undotbs03.dbf +ORADATA1/data1/oradata/dbbak/user_access_log01.dbf
/tol/oradata1/tol24/tol24/undotbs04.dbf +ORADATA1/data2/dmpdir/user_lesson_log01.dbf
/tol/oradata1/tol24/tol24/user02.dbf
/tol/oradata1/tol24/tol24/users01.dbf
/tol/oradata1/tol24/tol24/user_access_log01.dbf
/tol/oradata1/tol24/tol24/user_lesson_log01.dbf

/tol/oradata1/tol24/tol24/control01.ctl +ORADATA1/data1/oradata/dbbak/control01.ctl
/tol/oradata1/tol24/tol24/control02.ctl +ORADATA1/data1/oradata/dbbak/control02.ctl
/tol/app/oracle/oradata/tol24/control03.ctl +ORADATA1/data1/oradata/dbbak/control03.ctl

/tol/app/oracle/ora10g/dbs/arc /tol/oradata
/tol/oradata1/tol24/tol24/arc

使目标初始化参数文件在新主机上可用；
保证在还原主机上用于恢复的备份可用访问；
不能用rman 在某一主机上的disk 备份或拷贝还原到新主机上；不过，
可以用os 工具来传送文件，如果文件位于新主机上的相同位置上，不需要
recatalog 它们。如果传送到新的位置上，使用 CATALOG 命令更新rman 资料
库并用CHANGE … UNCATALOG 命令uncatalog 老的文件名
。
因为还原的数据库没有生产数据库的在线重做日志，需要执行不完全恢
复并用RESETLOGS 选项打开数据库。用下面查询得到需要的SCN：
SELECT MIN(SCN)
FROM (SELECT MAX(NEXT_CHANGE#) SCN
FROM V$ARCHIVED_LOG
GROUP BY THREAD#);

catalog datafilecopy
catalog controlfilecopy ‘d.CTL’
change controlfilecopy ‘d:.CTL’ uncatalog;

catalog datafilecopy ‘/u02/oradata/acdb920/drsys01.dbf’;–新位置

change datafilecopy ‘/u02/oradata/acdb920/drsys01.dbf’ catalog;–老位置
run{
allocate channel c1 type disk;
restore database;
recover database until scn 296511742;-- recover database until time ‘’;
sql ‘ALTER DATABASE OPEN RESETLOGS’;
release channel c1;
}

CATALOG ARCHIVELOG ‘/oradata/archive1_30.dbf’, ‘/oradata/archive1_31.dbf’,
‘/oradata/archive1_32.dbf’;

CATALOG DATAFILECOPY ‘/oradata/users01.bak’ LEVEL 0;

CHANGE COPY OF ARCHIVELOG ALL UNCATALOG;

编目录备份片

CATALOG BACKUPPIECE ‘/oradata/01dmsbj4_1_1.bcp’;
编目录归档日志

CATALOG ARCHIVELOG ‘/oradata/archive1_30.dbf’, ‘/oradata/archive1_31.dbf’;
编目录数据文件拷贝，并且将其做为增量备份的level 0

CATALOG DATAFILECOPY ‘/oradata/users01.bak’ LEVEL 0;
编目录控制文件拷贝

CATALOG CONTROLFILECOPY ‘controlfilecopy.ctl’
编目录整个目录，如果备份片或者归档日志文件太多，可以放到一个目录中，一次性编目录就行

CATALOG START WITH ‘/tmp/arch_logs’;
编目录闪回恢复区

CATALOG RECOVERY AREA NOPROMPT;

–db1大表
select * from (select segment_name,segment_type,ROUND(bytes/1024/1024/1024,2) “G”
from dba_segments where owner=’’ order by 3 desc) WHERE ROWNUM<30;

—bbs大表
select * from (select segment_name,segment_type,round(bytes/1024/1024/1024,2) “G” from dba_segments where owner=’’ order by 3 desc) WHERE ROWNUM<12;