通常我们需要将生产环境的数据导入到测试环境，定期对测试环境的数据进行更新，但使用EXPDP/IMPDP会出现许多的问题，还会有序列等无法导出的问题，于是推荐使用RMAN备份异地恢复，该操作耗时少，且不容易出错。
一、准备工作
生产环境：CRMPROD 生产数据库：CRMPROD
测试环境：CRMTEST 测试数据库：CRMTEST
删除CRMTEST中/u1/db/oracle/oradata/CRMTEST目录下的所有文件。
二、全备份
在生产环境使用数据备份脚本进行一次全备份，由于是用于测试环境，归档日志便不用进行备份。
备份脚本bk_level0.sh
#!/bin/bash
#Author: xiamengyou
#Created Time: 2019-03-07
#Description: backup
BACKUP_DATE=`date +%Y%m%d`
LOG_FILE=/u1/db/oracle/backup/scripts/logs/fulllogs/backup_database_log.$BACKUP_DATE.log
TODAY=`date`
USER=`id|cut -d "(" -f2|cut -d ")" -f1`
echo "-----------------$TODAY-------------------">$LOG_FILE
ORACLE_HOME=/u1/db/oracle/product/11.2.0/db_1
export ORACLE_HOME
RMAN=$ORACLE_HOME/bin/rman
export RMAN
ORACLE_SID=CRMPRO
export ORACLE_SID
ORACLE_USER=oracle
export ORACLE_USER
echo "ORACLE_SID: $ORACLE_SID">>$LOG_FILE
echo "ORACLE_HOME:$ORACLE_HOME">>$LOG_FILE
echo "ORACLE_USER:$ORACLE_USER">>$LOG_FILE
echo "----------------------------">>$LOG_FILE
echo "BACKUP DATABASE BEGIN......">>$LOG_FILE
echo " ">>$LOG_FILE
chmod 666 $LOG_FILE
WEEK_DAILY=`date +%a`
BACKUP_DIR="/u1/db/oracle/backup/data/full/${BACKUP_DATE}/"
if [[ ! -d ${BACKUP_DIR} ]]
then
mkdir -p ${BACKUP_DIR}
fi
echo "BACKUP_DIR: $BACKUP_DIR">>$LOG_FILE
echo " ">>$LOG_FILE
BAK_LEVEL=0
export BAK_LEVEL=$BAK_LEVEL
echo "Today is : $WEEK_DAILY incremental level= $BAK_LEVEL">>$LOG_FILE
RUN_STR="
BAK_LEVEL=$BAK_LEVEL
export BAK_LEVEL
ORACLE_HOME=$ORACLE_HOME
export ORACLE_HOME
ORACLE_SID=$ORACLE_SID
export ORACLE_SID
$RMAN target / msglog $LOG_FILE append <<EOF
run
{
allocate channel c1 type disk format='"$BACKUP_DIR"db_bak_lev"$BAK_LEVEL"_%t_%p_%s';
allocate channel c2 type disk format='"$BACKUP_DIR"db_bak_lev"$BAK_LEVEL"_%t_%p_%s';
backup incremental level= $BAK_LEVEL tag='db_bak_lev"$BAK_LEVEL"'
filesperset 4
skip inaccessible
database;
###backup archived log######
backup archivelog all tag='arc_bak'
format='"$BACKUP_DIR"archive_log_%t_%p_%s'
skip inaccessible
filesperset 4
not backed up 1 times
delete input;
backup current controlfile tag='bak_ctlfile_LEVEL' format='"$BACKUP_DIR"ctl_file_%t_%p_%s';
release channel c1;
release channel c2;
}
EOF
"
if [ "$USER" = "root" ];then
su - $ORACLE_USER -c "$RUN_STR">>$LOG_FILE
STATUS=$?
else
/bin/sh -c "$RUN_STR" >>$LOG_FILE
STATUS=$?
fi
if [ "$STATUS" = "0" ];then
echo "Backup successfully ended!">>$LOG_FILE;
fi
echo "-------------------------------------------">>$LOG_FILE
echo "review the parameters and scripts...">>$LOG_FILE
echo "$RUN_STR">>$LOG_FILE
echo "Run user is :$USER">>$LOG_FILE
echo " ">>$LOG_FILE
date>>$LOG_FILE
[[email protected] scripts]$ pwd
/u1/db/oracle/backup/scripts
[[email protected] scripts]$ ./bk_level0.sh
[[email protected] full]$ pwd
/u1/db/oracle/backup/data/full
[[email protected] full]$ scp -r 20190702/ [email protected]:/home/oracle
三、修改初始化参数（CRMTEST）
[[email protected] ~]$ vim .bash_profile
...
ORACLE_SID=CRMPROD
...
[[email protected] ~]$ cd $ORACLE_HOME/dbs
[[email protected] ~]$ cp initCRMTEST.ora initCRMPROD.ora
[[email protected] ~]$ vim initCRMPROD.ora
initCRMPROD.ora（主要是*.db_name和*.db_unique_name）
CRMTEST.__db_cache_size=822083584
CRMTEST.__java_pool_size=33554432
CRMTEST.__large_pool_size=16777216
CRMTEST.__oracle_base='/u1/db/oracle'#ORACLE_BASE set from environment
CRMTEST.__pga_aggregate_target=838860800
CRMTEST.__sga_target=2483027968
CRMTEST.__shared_io_pool_size=0
CRMTEST.__shared_pool_size=1560281088
CRMTEST.__streams_pool_size=16777216
*.audit_file_dest='/u1/db/oracle/admin/CRMTEST/adump'
*.audit_trail='none'
*.compatible='11.2.0.0.0'
*.control_files='/u1/db/oracle/oradata/CRMTEST/control01.ctl','/u1/db/oracle/fast_recovery_area/CRMTEST/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='CRMPROD'
*.db_recovery_file_dest='/u1/db/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4322230272
*.db_unique_name='CRMPROD'
*.diagnostic_dest='/u1/db/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=CRMTESTXDB)'
*.open_cursors=300
*.pga_aggregate_target=824180736
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=2473590784
*.undo_tablespace='UNDOTBS1'
四、NOMOUNT数据库（CRMTEST）
SQL> conn /as sysdba
SQL> startup nomount pfile=initCRMPROD.ora
五、恢复控制文件（CRMTEST）
[[email protected] ~]$ rman target /
RMAN> restore controlfile from ‘/home/oracle/20190702/ctl_file_1012563290_1_2644’
六、MOUNT数据库（CRMTEST）
RMAN> alter database mount;
七、catalog备份集（CRMTEST）
RMAN> crosscheck backup;
RMAN> list expired backup;
RMAN> delete noprompt expired backup;
RMAN> delete noprompt expired backup;
specification does not match any backup in the repository
重新注册备份集到控制文件
注册整个目录
RMAN> catalog start with '/home/oracle/20190702';
RMAN> list backup of database;
八、重命名数据文件并restore数据库（CRMTEST）
重命名数据文件路径（在CRMPROD中运行以下查询）
SELECT 'set newname for datafile ' || t.File_Id || ' to ''' || t.File_Name ||
''';'
FROM Dba_Data_Files t
order by t.FILE_ID
将rename.tsv中的CRMPROD全部更改为CRMTEST将查询的数据导出到TSV文件rename.tsv
提取重命名的语句形成如下的SQL文件rename.sql
run {
set newname for datafile 1 to '/u1/db/oracle/oradata/CRMTEST/system01.dbf';
set newname for datafile 2 to '/u1/db/oracle/oradata/CRMTEST/sysaux01.dbf';
set newname for datafile 3 to '/u1/db/oracle/oradata/CRMTEST/undotbs01.dbf';
set newname for datafile 4 to '/u1/db/oracle/oradata/CRMTEST/users01.dbf';
set newname for datafile 5 to '/u1/db/oracle/oradata/CRMTEST/crm_data01.dbf';
set newname for datafile 6 to '/u1/db/oracle/oradata/CRMTEST/crm_idx01.dbf';
set newname for datafile 7 to '/u1/db/oracle/oradata/CRMTEST/crm_data02.dbf';
set newname for datafile 8 to '/u1/db/oracle/oradata/CRMTEST/crm_data03.dbf';
set newname for datafile 9 to '/u1/db/oracle/oradata/CRMTEST/crm_data04.dbf';
set newname for datafile 10 to '/u1/db/oracle/oradata/CRMTEST/crm_data06.dbf';
set newname for datafile 11 to '/u1/db/oracle/oradata/CRMTEST/crm_data07.dbf';
set newname for datafile 12 to '/u1/db/oracle/oradata/CRMTEST/crm_data08.dbf';
set newname for datafile 13 to '/u1/db/oracle/oradata/CRMTEST/crm_data09.dbf';
set newname for datafile 14 to '/u1/db/oracle/oradata/CRMTEST/crm_data10.dbf';
set newname for datafile 15 to '/u1/db/oracle/oradata/CRMTEST/crm_data11.dbf';
set newname for datafile 16 to '/u1/db/oracle/oradata/CRMTEST/crm_data12.dbf';
set newname for datafile 17 to '/u1/db/oracle/oradata/CRMTEST/crm_data13.dbf';
set newname for datafile 18 to '/u1/db/oracle/oradata/CRMTEST/crm_data05.dbf';
set newname for datafile 19 to '/u1/db/oracle/oradata/CRMTEST/crm_data14.dbf';
set newname for datafile 20 to '/u1/db/oracle/oradata/CRMTEST/crm_data15.dbf';
set newname for datafile 21 to '/u1/db/oracle/oradata/CRMTEST/crm_data16.dbf';
set newname for datafile 22 to '/u1/db/oracle/oradata/CRMTEST/crm_data17.dbf';
set newname for datafile 23 to '/u1/db/oracle/oradata/CRMTEST/crm_data18.dbf';
set newname for datafile 24 to '/u1/db/oracle/oradata/CRMTEST/crm_data19.dbf';
set newname for datafile 25 to '/u1/db/oracle/oradata/CRMTEST/crm_data20.dbf';
set newname for datafile 26 to '/u1/db/oracle/oradata/CRMTEST/crm_data21.dbf';
set newname for datafile 27 to '/u1/db/oracle/oradata/CRMTEST/crm_data22.dbf';
set newname for datafile 28 to '/u1/db/oracle/oradata/CRMTEST/crm_data23.dbf';
restore database;
switch datafile all;
}
SQL> @/home/oracle/rename.sql
九、recover数据库（CRMTEST）
RMAN> recover database;
......
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/16/2014 11:20:55
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 7 and starting SCN of 1871963664
RMAN> recover database until sequence 7 thread 1;
Finished recover
十、清理重做日志组（CRMTEST）
有时候从一个RAC环境克隆至单节点环境，如果不注意直接使用alter database open resetlogs命令打开数据库，会发现出现错误，因为找不到online log的文件位置，此时我们需要将online log文件进行重命名。
//用于正常打开重做日志
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
+DATADG/prod/onlinelog/group_1.304.979982767
+DATADG/prod/onlinelog/group_2.305.979982769
+DATADG/prod/onlinelog/group_3.302.979982771
+DATADG/prod/onlinelog/group_4.303.979982771
SQL> ALTER DATABASE RENAME FILE '+DATADG/prod/onlinelog/group_1.304.979982767' to '/u1/db/oracle/oradata/CRMTEST/redo01.log';
SQL> ALTER DATABASE RENAME FILE '+DATADG/prod/onlinelog/group_2.305.979982769' to '/u1/db/oracle/oradata/CRMTEST/redo02.log';
SQL> ALTER DATABASE RENAME FILE '+DATADG/prod/onlinelog/group_3.302.979982771' to '/u1/db/oracle/oradata/CRMTEST/redo03.log';
SQL> ALTER DATABASE RENAME FILE '+DATADG/prod/onlinelog/group_4.303.979982771' to '/u1/db/oracle/oradata/CRMTEST/redo04.log';
SQL> alter database open resetlogs;
数据库成功打开，至此数据库的恢复工作已经完成，下面是数据库一些收尾工作
十一、删除多余重做日志组（CRMTEST）
SQL> select thread#,status,enabled from v$thread;
THREAD# STATUS ENABLED
---------- ------ --------
1 OPEN PUBLIC
2 CLOSED PRIVATE
禁用thread 2
SQL> alter database disable thread 2;
Database altered.
查看重做日志信息
SQL> select member from v$logfile;
删除所有日志组，新建日志组，注意系统最少需要两个日志组，所以删除日志组和新建日志组必须穿插着进行。
可参考：Oracle在线添加日志组
alter database add logfile group 1 （‘/u1/db/oracle/oradata/CRMTEST/redo01.log’,'/u1/db/oracle/fast_recovery_area/CRMTEST/redo01.log'） size 200M;
alter database add logfile group 2 （‘/u1/db/oracle/oradata/CRMTEST/redo02.log’,'/u1/db/oracle/fast_recovery_area/CRMTEST/redo02.log'） size 200M;
alter database add logfile group 3 （‘/u1/db/oracle/oradata/CRMTEST/redo03.log’,'/u1/db/oracle/fast_recovery_area/CRMTEST/redo03.log'） size 200M;
最后结果应该如下：
SQL> select group#,thread#,sequence#,members,archived,status from v$log;
GROUP# THREAD# SEQUENCE# MEMBERS ARC STATUS
---------- ---------- ---------- ---------- --- ----------------
1 1 9 2 NO CURRENT
2 1 8 2 YES INACTIVE
3 1 6 2 YES INACTIVE
十二、表空间重建（CRMTEST）
删除多余的undo表空间
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string APPS_UNDOTS1
SQL> select tablespace_name from dba_tablespaces where contents='UNDO';
TABLESPACE_NAME
------------------------------
APPS_UNDOTS1
APPS_UNDOTS2
SQL> drop tablespace APPS_UNDOTS2 including contents and datafiles;
Tablespace dropped.
创建临时表空间
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
......
SQL> select tablespace_name from dba_tablespaces where contents='TEMPORARY';
TABLESPACE_NAME
------------------------------
TEMP
SQL> create temporary tablespace temp1 tempfile '/u1/db/oracle/oradata/CRMTEST/temp01.dbf' size 500M;
Tablespace created.
SQL> alter database default temporary tablespace temp1;
Database altered.
SQL> drop tablespace TEMP including contents and datafiles;
Tablespace dropped.
十三、修改dbname和sid（CRMTEST）
修改DBNAME
SQL> show parameter db_name;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string CRMPROD
数据库mount
SQL> startup mount pfile='initCRMPROD.ora'
执行修改（sys为CRMPROD中CRMPROD的sys密码）
[[email protected] dbs]$ nid TARGET=sys/*** DBNAME=CRMTEST SETNAME=YES
修改initCRMPROD.ora文件（主要是*.db_name和*.db_unique_name）
CRMTEST.__db_cache_size=822083584
CRMTEST.__java_pool_size=33554432
CRMTEST.__large_pool_size=16777216
CRMTEST.__oracle_base='/u1/db/oracle'#ORACLE_BASE set from environment
CRMTEST.__pga_aggregate_target=838860800
CRMTEST.__sga_target=2483027968
CRMTEST.__shared_io_pool_size=0
CRMTEST.__shared_pool_size=1560281088
CRMTEST.__streams_pool_size=16777216
*.audit_file_dest='/u1/db/oracle/admin/CRMTEST/adump'
*.audit_trail='none'
*.compatible='11.2.0.0.0'
*.control_files='/u1/db/oracle/oradata/CRMTEST/control01.ctl','/u1/db/oracle/fast_recovery_area/CRMTEST/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='CRMTEST'
*.db_recovery_file_dest='/u1/db/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4322230272
*.db_unique_name='CRMTEST'
*.diagnostic_dest='/u1/db/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=CRMTESTXDB)'
*.open_cursors=300
*.pga_aggregate_target=824180736
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=2473590784
*.undo_tablespace='UNDOTBS1'
修改oracle的.bash_profile
ORACLE_SID=CRMTEST
[[email protected] ~]$ source .bash_profile
[[email protected] dbs]$ pwd
/u1/db/oracle/product/11.2.0/db_1/dbs
[[email protected] dbs]$ cp initCRMPROD.ora initCRMTEST.ora
[[email protected] dbs]$ sqlplus / as sysdba
SQL> create spfile from pfile;
SQL> startup;
至此，工作基本都已经完成了，后续是一些收尾工作，可做可不做
十四、删除CRMPROD的文件和目录（CRMTEST）
[[email protected] dbs]$ rm initCRMPROD.ora spfileCRMPROD.ora
[[email protected] oradata]$ pwd
/u1/db/oracle/oradata
[[email protected] oradata]$ rm -rf CRMPROD
![———EMPORIOARMANI———FW23早秋新品[图]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)