解决思路：在备份出来的数据中（控制文件），包含了数据库名，数据文件，redo log等文件的存放路径，如果在本地直接做恢复，将和原有的数据库，因此，需要修改这些信息，将备份出来的数据恢复到另外一个数据库中。具体步骤如下：
1创建新数据库数据文件及跟踪文件存放目录
-bash-205b$mkdir -p /oracle/oradata/test
-bash-205b$mkdir -p /oracle/admin/test/bdump
-bash-205b$mkdir -p /oracle/admin/test/udump
-bash-205b$mkdir -p /oracle/admin/test/cdump
2登录到生产数据库。查看当前数据文件信息：
-bash-205b$ sqlplus '/ as sysdba'
SQL> select file#,name from v$datafile;
FILE# NAME
---------- --------------------------------------------------
1 /oracle/oradata/test/system01dbf
2 /oracle/oradata/test/undo01dbf
3 /oracle/oradata/test/users01dbf
3利用rman更改数据文件存放路径并restore到新的路径
run {
set newname for datafile 1 to '/oracle/oradata/test/system01dbf';
set newname for datafile 2 to '/oracle/oradata/test/undo01dbf';
set newname for datafile 3 to '/oracle/oradata/test/users01dbf';
restore datafile 1;
restore datafile 2;
restore datafile 3;
}
4。重建控制文件
CREATE CONTROLFILE REUSE set DATABASE "test" RESETLOGS ARCHIVELOG
-- SET STANDBY TO MAXIMIZE PERFORMANCE
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 30
MAXINSTANCES 1
MAXLOGHISTORY 453
LOGFILE
GROUP 1 '/oracle/oradata/test/redo01log' SIZE 10M,
GROUP 2 '/oracle/oradata/test/redo02log' SIZE 10M,
GROUP 3 '/oracle/oradata/test/redo03log' SIZE 10M
-- STANDBY LOGFILE
DATAFILE
'/oracle/oradata/test/system01dbf',
'/oracle/oradata/test/undo01dbf',
'/oracle/oradata/test/users01dbf'
CHARACTER SET ZHS16GBK
;
5打开数据库
SQL> alter database open resetlogs;
本答案来自于互联网，仅供参考学习作用
如果您对我的回答有不满意的地方，还请您继续追问；
答题不易，互相理解，互相帮助！在ORACLE数据库中，RMAN备份的脚本非常多，下面介绍一例shell脚本如何通过RMAN备份，以及FTP上传RMAN备份文件以及归档日志文件的脚本。
fullbacksh 里面调用RMAN命令做数据库备份，它使用的cmdfile为/home/oracle/backup/bin/fullbackrcv，同时在/home/oracle/backup/logs目录下生成日志文件。
1: [oracle@DB-Server bin]$ more fullbacksh
2:
3: #!/bin/bash
4:
5: export ORACLE_BASE=/u01/app/oracle
6:
7: export ORACLE_SID=gps
8:
9: ORACLE_HOME=$ORACLE_BASE/product/1020/db_1; export ORACLE_HOME
10:
11: TMP=/tmp; export TMP
12:
13: TMPDIR=$TMP; export TMPDIR
14:
15: PATH=/usr/sbin:$PATH; export PATH
16:
17: PATH=$ORACLE_HOME/bin:$PATH; export PATH
18:
19: LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
20:
21: CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib;
22:
23: export CLASSPATH
24:
25: TODAY=`date +%Y_%m_%d`
26:
27: rman nocatalog target / cmdfile /home/oracle/backup/bin/fullbackrcv log /home/oracle/backup/logs/fullbackup_$TODAYlog
28:
29: /home/oracle/backup/bin/ftpbackupsh
30:
fullbackrcv文件非常简单， 如下所示：
1: [oracle@DB-Server bin]$ more /home/oracle/backup/bin/fullbackrcv
2:
3: run{
4:
5: allocate channel c4 type disk;
6:
7: backup as compressed backupset
8:
9: skip inaccessible
10:
11: tag fullbackupwitharchivelog
12:
13: (database);
14:
15: backup current controlfile;
16:
17: backup spfile;
18:
19: sql "alter system archive log current";
20:
21: delete noprompt obsolete;
22:
23: release channel c4;
24:
25: }
26:
RMAN生成的备份文件，需要通过FTP上传到FTP服务器，一则数据库服务器没有这么多空间存储多天的备份，二则是出于容灾、数据安全需要。
下面脚本中FTP服务器，用户名密码均使用xxx替代，在实际环境中，使用具体的信息替代即可。
1: [oracle@DB-Server bin]$ more ftpbackupsh
2:
3: #!/bin/sh、
4:
5: rm -f /home/oracle/netrc
6:
7: date_yesterday=`date -d'yesterday' +%Y_%m_%d`
8:
9: date_today=`date +%Y_%m_%d`
10:
11: echo "default login xxxx password xxxxxx" >> /home/oracle/netrc
12:
13: echo "macdef init" >> /home/oracle/netrc
14:
15: echo "binary" >> /home/oracle/netrc
16:
17: echo "cd archivelog" >> /home/oracle/netrc
18:
19: echo "mkdir $date_yesterday" >> /home/oracle/netrc
20:
21: echo "cd $date_yesterday" >> /home/oracle/netrc
22:
23: echo "lcd /u04/flash_recovery_area/gps/archivelog/$date_yesterday" >> /home/oracle/netrc
24:
25: echo "mput " >> /home/oracle/netrc
26:
27: echo "cd " >> /home/oracle/netrc
28:
29: echo "mkdir $date_today" >>/home/oracle/netrc
30:
31: echo "cd $date_today" >>/home/oracle/netrc
32:
33: echo "lcd /u04/flash_recovery_area/gps/archivelog/$date_today" >>/home/oracle/netrc
34:
35: echo "mput ">>/home/oracle/netrc
36:
37: echo "cd " >>/home/oracle/netrc
38:
39: echo "cd /backupset" >> /home/oracle/netrc
40:
41: echo "mkdir $date_today" >> /home/oracle/netrc
42:
43: echo "cd $date_today" >> /home/oracle/netrc
44:
45: echo "lcd /u04/flash_recovery_area/gps/backupset/$date_today" >> /home/oracle/netrc
46:
47: echo "mput " >> /home/oracle/netrc
48:
49: echo "cd " >> /home/oracle/netrc
50:
51: echo "cd /autobackup" >> /home/oracle/netrc
52:
53: echo "mkdir $date_today" >> /home/oracle/netrc
54:
55: echo "cd $date_today" >> /home/oracle/netrc
56:
57: echo "lcd /u04/flash_recovery_area/gps/autobackup/$date_today" >> /home/oracle/netrc
58:
59: echo "mput " >> /home/oracle/netrc
60:
61: echo "quit" >> /home/oracle/netrc
62:
63: echo "" >> /home/oracle/netrc
64:
65: chmod 600 /home/oracle/netrc
66:
67: ftp -i -v xxxxxxxxxxxx 8021 >>/home/oracle/backup/logs/ftp$date_todaylog 2>&1
68:
另外，关于归档日志也需要每隔2小时上传一次到FTP服务器，2小时上传一次归档日志的shell脚本如下所示：
1: [oracle@DB-Server bin]$ more ftp2hourssh
2:
3: #!/bin/sh
4:
5: rm -f /home/oracle/netrc
6:
7: date_yesterday=`date -d'yesterday' +%Y_%m_%d`
8:
9: date_today=`date +%Y_%m_%d`
10:
11: echo "default login xxxx password xxxx" >> /home/oracle/netrc
12:
13: echo "macdef init" >> /home/oracle/netrc
14:
15: echo "binary" >> /home/oracle/netrc
16:
17: echo "cd archivelog" >> /home/oracle/netrc
18:
19: echo "mkdir $date_today" >>/home/oracle/netrc
20:
21: echo "cd $date_today" >>/home/oracle/netrc
22:
23: echo "lcd /u04/flash_recovery_area/gps/archivelog/$date_today" >>/home/oracle/netrc
24:
25: echo "mput ">>/home/oracle/netrc
26:
27: echo "quit" >> /home/oracle/netrc
28:
29: echo "" >> /home/oracle/netrc
30:
31: chmod 600 /home/oracle/netrc
32:
33: ftp -i -v xxxxxxxxxxxx 8021 >>/home/oracle/backup/logs/ftp2hours$date_todaylog 2>&1
34:
最后需要将RMAN备份生成的日志文件，以及FTP上传备份文件以及归档日志的记录通过邮件形式发送给DBA或系统管理员，
1: [oracle@DB-Server bin]$ more chkbackandmailsh
2: #!/bin/bash
3: rm -f /home/oracle/backup/bin/sendmailpl
4: date_today=`date +%Y_%m_%d`
5: subject="Oracle Backup Alert Service on $date_today"
6: content="Dear colleagues,
7:
8: Attached please find the logs of xxx(xxxxxxxxxxxx) oracle database backup and transfer to FTP Server(xxxxxxxxxxxx), please
9: review the file and check whether the backup succeeded or not,and double check all backups have been dumped to tape, many tha
10: nks
11:
12:
13:
14:
15: Best regards
16: Oracle Alert Services
17:
18: "
19: file="/home/oracle/backup/logs/fullbackup_$date_todaylog,/home/oracle/backup/logs/ftp$date_todaylog"
20: echo "#!/usr/bin/perl" >> /home/oracle/backup/bin/sendmailpl
21: echo "use Mail::Sender;" >> /home/oracle/backup/bin/sendmailpl
22: echo "\$sender = new Mail::Sender {smtp => 'xxxxxxxxxxxx', from => 'xxxx@xxxcom'}; ">> /home/oracle/backup/bin/sendmai
23: lpl
24: echo "\$sender->MailFile({to => 'xxx@esquelcom',">> /home/oracle/backup/bin/sendmailpl
25: echo "cc=>'xxx@xxxcom,xxx@xxxcom,xxx@xxxcom'," >> /home/oracle/backup/b
26: in/sendmailpl
27: echo "subject => '$subject',">> /home/oracle/backup/bin/sendmailpl
28: echo "msg => '$content',">> /home/oracle/backup/bin/sendmailpl
29: echo "file => '$file'});">> /home/oracle/backup/bin/sendmailpl
30: perl /home/oracle/backup/bin/sendmailpl
最后在Crontab 作业里面配置调用这些shell脚本。例如如下所示，在1：01分执行fullbacksh ，每隔两个小时（例如0:50、2:50)执行一次ftp2hourssh, 在每天早上8:40执行chkbackandmailsh 发送fullbacksh 以及ftp2hoursh的执行日志记录。在命令窗口里面执行
DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7';
说明
SYSDATA-7，表明当前的系统时间7天前，before关键字表示在7天前的归档日志，如果使用了闪回功能，也会删除闪回的数据。
同样道理，也可以删除从7天前到现在的全部日志，不过这个命令要考虑清楚，做完这个删除，最好马上进行全备份数据库
DELETE ARCHIVELOG FROM TIME 'SYSDATE-7';
UNIX/LINUX下也可以通过FIND找到7天前的归档数据，使用EXEC子 *** 作删除
find /oraarchive -xdev -mtime +7 -name "dbf" -exec rm -f {} ;
这样做仍然会在RMAN里留下未管理的归档文件
仍需要在RMAN里执行下面2条命令
crosscheck archivelog all;
delete expired archivelog all;
所以还不如上面的方法好用，不过用FIND的好处就是，可以在条件上，和EXEC子项上做很多 *** 作，实现更复杂的功能
Oracle数据库RMAN的自动备份脚本简介
各位同学知道Oracle数据库RMAN如何自动备份脚本嘛下面我为大家整理了关于Oracle数据库RMAN的自动备份脚本文章，希望能为你提供帮助：
1、数据库设置为归档方式
2、数据库的备份脚本
db_full_backupsh ：数据库全备脚本
db_l0_backupsh ：数据库0级备份脚本
db_l1_backupsh ：数据库1级备份脚本
ftpsh ：数据FTP上传脚本
ftp_delsh ：数据FTP清理脚本
rman_baksh ：数据备份主程序
3、备份原理
每周1、3、6进行0级备份
每周日、2、4、5进行1级备份
备份文件上传到FTP服务器
FTP服务器每周清理一次，但是清理后将周六和周日的备份进行保留(6bak和0bak)
所有工作防暑crontab中自动执行备份
4、备份目录含义
arc ：数据库归档目录
rmanbak ：数据库备份文件的保存目录
rmanscripts ：数据库脚本存放路径
5、FTP目录
ftp上必须手动建立目录
L0：
---1
---3
---6
---6bak
L1：
---2
---4
---5
---0
---0bak
rman_baksh脚本主程序
#!/bin/bash
#--------------------------------------------
# Oracle auto backup using rman
#
# author:songrh
# week:1,3,6 Level 0 backup
# 2,4,5,0 Level 1 backup
# Copyright by ChenLong Tec
#--------------------------------------------
#
#
export ORACLE_BASE=/u02/oracle
export ORACLE_HOME=/u02/oracle/product/924
export ORACLE_SID=PROD
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib:$ORACLE_HOME/network/jlib
export NLS_LANG=american_americaZHS16GBK
export TNS_ADMIN=$ORACLE_HOME/network/admin
export ORA_NL33=$ORACLE_HOME/ocommon/nls/admin/data
export PATH=/bin:/usr/bin:/usr/sbin:$ORACLE_HOME/bin:$PATH
export PATH=$PATH:/opt/local/bin
#
SH_PATH=/u02/rmanscripts
ARC_PATH=/u02/arch
RMAN_BAK_PATH=/u02/rmanbak
#
#FULL_PATH=$RMAN_BAK_PATH/full
L0_PATH=$RMAN_BAK_PATH/L0
L1_PATH=$RMAN_BAK_PATH/L1
#
#DAY_TAG=`date "%Y-%m-%d"`
LOG_TAG=`date "%Y-%m-%d"`
#FIRST_DAY=`date %e`
WEEK=`date %w`
#WEEK=1
#
# FTP configure
IP="122120150155"
FTPUSER="ftpbak"
FTPPASS=""
FTPROOT0="L0"
FTPROOT1="L1"
#
DISK_USE=`df -k |sed -n '/u02/'p | awk '{print $5}' |sed 's/%//'`
####check path function
############
if [[ $DISK_USE -ge 90 ]]; then
rm -rf $L0_PATH/
rm -rf $L1_PATH/
fi
if [ "$WEEK" = "6" -o "$WEEK" = "3" -o "$WEEK" = "1" ]; then
if [ ! -d $L0_PATH ]; then
mkdir $L0_PATH
fi
if [ "$WEEK" = "1" ]; then
rm -rf $L0_PATH/
rm -rf $L1_PATH/
mkdir $L0_PATH/$WEEK
$SH_PATH/db_l0_backupsh $L0_PATH/$WEEK
cd $L0_PATH/$WEEK
$SH_PATH/ftp_delsh $IP $FTPUSER $FTPPASS $FTPROOT0 $WEEK $WEEK_$LOG_TAGlog
else
if [ ! -d $L0_PATH/$WEEK ]; then
mkdir $L0_PATH/$WEEK
$SH_PATH/db_l0_backupsh $L0_PATH/$WEEK
cd $L0_PATH/$WEEK
$SH_PATH/ftpsh $IP $FTPUSER $FTPPASS $FTPROOT0 $WEEK $WEEK_$LOG_TAGlog
else
;1，全备数据库PRODrun{
allocate channel c1 device type disk;
allocate channel c2 device type disk;
backup tag 'SH_DB' as compressed backupset format ‘/home/Oracle/full_%Ubak' database
include current controlfile;
backup tag 'SH_ARCH' archivelog al format ‘/home/oracle/arch_%Ubak’l;
release channel c1;
release channel c2;
}
2，确认控制文件的备份片
RMAN> list backup of control file;
/home/oracle/full_03pkgtf4_1_1bak
3，导出pfile，将控制文件修改为+DATA和+FRA
SYS@PROD >create pfile='/home/oracle/prodpfile' from spfile;
[oracle@single ~]$ vi /home/oracle/prodpfile
audit_file_dest='/u01/admin/PROD/adump'
audit_trail='db'
compatible='112040'
##control_files='/u01/oradata/PROD/control01ctl','/u01/fast_recovery_area/PROD/control02ctl'
control_files='+DATA/PROD/controlfile/control01ctl','+FRA/PROD/controlfile/control02ctl'
db_block_size=8192
db_create_file_dest='+DATA'
db_domain=''
db_name='PROD'
##db_recovery_file_dest='/u01/fast_recovery_area'
db_recovery_file_dest='+FRA'
db_recovery_file_dest_size=4385144832
diagnostic_dest='/u01'
dispatchers='(PROTOCOL=TCP) (SERVICE=PRODXDB)'
log_archive_format='%t_%s_%rdbf'
memory_target=583008256
open_cursors=300
processes=150
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
4，删除PROD
SYS@PROD >startup force mount restrict;
SYS@PROD >drop database;
5，创建灾备端所需目录（DBCA删除数据库需要此步骤）
mkdir -p $ORACLE_BASE/admin/PROD/adump
6，创建数据库密码文件 （DBCA删除数据库需要此步骤）
orapwd file=$ORACLE_HOME/dbs/orapwPROD password=oracle entries=5 force=y
7，采用修改过的pfile启动到nomount
$ sqlplus / as sysdba
SYS@PROD >startup nomount pfile=/home/oracle/pfile;
8，将spfile文件创建到+DATA目录下，利用Oracle11g新特性from memory
SYS@PROD >create spfile='+DATA/PROD/spfilePRODora' from memory;
9，创建一个pfile来指向spfile。将红色字体内容填写在vi创建的pfile文件中。 在Oracle11g中安装过集群软件都需要这样配置，无论是单实例ASM还是RAC+ASM
SYS@PROD >!vi $ORACLE_HOME/dbs/initPRODora
SPFILE='+DATA/PROD/spfilePRODora’
10，恢复控制文件到+DATA
RMAN> startup force nomount;
RMAN> restore controlfile from '/home/oracle/full_03pkgtf4_1_1bak';
11，启动到mount
RMAN> mount database;
12，识别备份集
RMAN> catalog start with '/home/oracle/';
13，恢复数据库，注意关键词set newname 和 switch datafile all是迁移文件系统到ASM的关键。
run{
set newname for datafile 1 to '+DATA';
set newname for datafile 2 to '+DATA';
set newname for datafile 3 to '+DATA';
set newname for datafile 4 to '+DATA';
set newname for tempfile 1 to '+DATA';
restore database;
switch datafile all;
recover database;
}
14，更改联机日志路径为+DATA
SYS@PROD >alter database rename file '/u01/oradata/PROD/redo01log' to '+DATA';
SYS@PROD >alter database rename file '/u01/oradata/PROD/redo02log' to '+DATA';
SYS@PROD >alter database rename file '/u01/oradata/PROD/redo03log' to '+DATA';
15，open resetlogs开库
SYS@PROD >alter database open resetlogs;
16, 如果在set newname tempfile步骤不能成功的迁移tempfile使用下面的命令手工追加
SYS@PROD >alter tablespace temp add tempfile '+DATA' size 10m;
SYS@PROD >alter tablespace temp drop tempfile '/u01/oradata/PROD/temp01dbf’;
转载，仅供参考。
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）