工作记录：rman备份恢复融合平台oracle数据库到异机服务器

最新推荐文章于 2024-04-12 11:57:25 发布

key1s

最新推荐文章于 2024-04-12 11:57:25 发布

阅读量507

点赞数

分类专栏： oracle

本文链接：https://blog.csdn.net/xlgjjff/article/details/50888227

版权

oracle 专栏收录该内容

19 篇文章 0 订阅

订阅专栏

这篇记录为工作中oracle移机记录，从自有服务器移植到阿里云服务器，以下为正常的备份恢复过程。由于移机中数据文件位置发生变化，导致出现了很多问题，最终峰回路转终于完成；出现的错误我会另起一片以作记录

1. ORACLE RMAN在线备份之前需要切换日志方式为归档日志；

a. 关闭数据库
SQL> shutdown immediate;

b. 启动数据库到mount状态
SQL> startup mount;

c. 启用归档模式
SQL> alter database archivelog;

d. 查看修改后的数据库备份和恢复策略及归档文件的位置
SQL> archive log list;

注意：修改成archive模式之后，之前所有的数据库备份均无效。

2、进入rman备份数据库
[oracle@clouddb rmanbcak]$ rman target / nocatalog
connected to target database: SDSRCDB (DBID=2273028019)
using target database control file instead of recovery catalog
执行rmanback 备份数据文件、归档、和控制文件
>run{
allocate channel c3 type disk;
allocate channel c4 type disk;
backup database format '/disk/rmanbcak/sdsrcfull_%d_%T_%s_%p';
sql 'alter system archive log current';
sql 'alter system archive log current';
sql 'alter system archive log current';
backup archivelog all format '/disk/rmanbcak/sdsrcarch_%D_%T_%s_%p' delete input;
backup current controlfile format '/disk/rmanbcak/sdsrcctl_%d_%T_%s_%p';
}

3、打包lock以及密码文件
/u01/app/oracle/product/11.2.0/db_1/dbs/orapwsdsrcdb
/u01/app/oracle/product/11.2.0/db_1/dbs/hc_sdsrcdb.dat
/u01/app/oracle/product/11.2.0/db_1/dbs/lkSDSRCDB

4、spfile建立pfile
SQL> create pfile='/tmp/sdpfile' from spfile;

5. 创建各种dump目录
-----------------------------------------------------
11g要创建这些目录
rm -rf $ORACLE_BASE/admin/$ORACLE_SID
mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/adump
mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/dpdump
mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/pfile
mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/scripts
mkidr -P $ORACLE_BASE/fast_recovery_area
mkdir /u01/app/oracle/fast_recovery_area/$ORACLE_SID
mkdir /disk1/oradata/$ORACLE_SID
chmod -R 750 $ORACLE_BASE/admin
chmod -R 750 /u01/app/oracle/fast_recovery_area/
chmod -R 750 /disk1/oradata/
rm -rf $ORACLE_BASE/diag/rdbms/$ORACLE_SID
mkdir -p $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/alert
mkdir -p $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/cdump
mkdir -p $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/hm
mkdir -p $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/incident
mkdir -p $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/incpkg
mkdir -p $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/ir
mkdir -p $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/lck
mkdir -p $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/metadata
mkdir -p $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/stage
mkdir -p $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/sweep
mkdir -p $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace
chmod -R 750 $ORACLE_BASE/diag/rdbms/$ORACLE_SID

6、使用pfile启动实例到nomount
[oracle@tc_oracle1 oracle]$ sqlplus / as sysdba
SQL> startup nomount pfile='/home/oracle/融合平台/sdpfile';

7、进入rman恢复数据库
[oracle@tc_oracle1 oracle]$ rman target /
restore controlfile from '/disk1/rhpt/sdsrcctl_SDSRCDB_20160314_8_1';

a、启动数据库到mount状态
RMAN> alter database mount;

b、验证有没有生效
select name from v$datafile;

c、注册备份集位置到controlfile
RMAN> catalog start with '/disk1/rhpt/';
开始恢复：
注：由于异机恢复数据文件位置发生变化需要重新定位一下文件名
RMAN> run{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
set newname for datafile '/disk/oradata/sdsrcdb/redo03.dbf' to '/disk1/oradata/sdsrcdb/redo03.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/redo02.dbf' to '/disk1/oradata/sdsrcdb/redo02.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/redo01.dbf' to '/disk1/oradata/sdsrcdb/redo01.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/system01.dbf' to '/disk1/oradata/sdsrcdb/system01.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/sysaux01.dbf' to '/disk1/oradata/sdsrcdb/sysaux01.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/undotbs01.dbf' to '/disk1/oradata/sdsrcdb/undotbs01.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/users01.dbf' to '/disk1/oradata/sdsrcdb/users01.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/ctuni_pub_data01.dbf' to '/disk1/oradata/sdsrcdb/ctuni_pub_data01.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/ctuni_pub_data02.dbf' to '/disk1/oradata/sdsrcdb/ctuni_pub_data02.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/ctuni_pub_index01.dbf' to '/disk1/oradata/sdsrcdb/ctuni_pub_index01.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/ctuni_sys_data01.dbf' to '/disk1/oradata/sdsrcdb/ctuni_sys_data01.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/ctuni_sys_data02.dbf' to '/disk1/oradata/sdsrcdb/ctuni_sys_data02.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/ctuni_sys_index01.dbf' to '/disk1/oradata/sdsrcdb/ctuni_sys_index01.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/ctuni_sd_data01.dbf' to '/disk1/oradata/sdsrcdb/ctuni_sd_data01.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/ctuni_sd_data02.dbf' to '/disk1/oradata/sdsrcdb/ctuni_sd_data02.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/ctuni_sd_data03.dbf' to '/disk1/oradata/sdsrcdb/ctuni_sd_data03.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/ctuni_sd_index01.dbf' to '/disk1/oradata/sdsrcdb/ctuni_sd_index01.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/ctuni_yn_data_01.dbf' to '/disk1/oradata/sdsrcdb/ctuni_yn_data_01.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/ctuni_yn_data_02.dbf' to '/disk1/oradata/sdsrcdb/ctuni_yn_data_02.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/ctuni_yn_data_03.dbf' to '/disk1/oradata/sdsrcdb/ctuni_yn_data_03.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/ctuni_yn_index_01.dbf' to '/disk1/oradata/sdsrcdb/ctuni_yn_index_01.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/ctuni_hain_data_01.dbf' to '/disk1/oradata/sdsrcdb/ctuni_hain_data_01.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/ctuni_hain_data_02.dbf' to '/disk1/oradata/sdsrcdb/ctuni_hain_data_02.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/ctuni_hain_data_03.dbf' to '/disk1/oradata/sdsrcdb/ctuni_hain_data_03.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/ctuni_hain_index_01.dbf' to '/disk1/oradata/sdsrcdb/ctuni_hain_index_01.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/ctuni_bj_data01.dbf' to '/disk1/oradata/sdsrcdb/ctuni_bj_data01.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/ctuni_tj_data01.dbf' to '/disk1/oradata/sdsrcdb/ctuni_tj_data01.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/ctuni_hb_data01.dbf' to '/disk1/oradata/sdsrcdb/ctuni_hb_data01.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/ctuni_sx_data01.dbf' to '/disk1/oradata/sdsrcdb/ctuni_sx_data01.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/ctuni_nmg_data01.dbf' to '/disk1/oradata/sdsrcdb/ctuni_nmg_data01.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/ctuni_ln_data01.dbf' to '/disk1/oradata/sdsrcdb/ctuni_ln_data01.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/ctuni_jl_data01.dbf' to '/disk1/oradata/sdsrcdb/ctuni_jl_data01.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/ctuni_hlj_data01.dbf' to '/disk1/oradata/sdsrcdb/ctuni_hlj_data01.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/ctuni_sh_data01.dbf' to '/disk1/oradata/sdsrcdb/ctuni_sh_data01.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/ctuni_js_data01.dbf' to '/disk1/oradata/sdsrcdb/ctuni_js_data01.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/ctuni_zj_data01.dbf' to '/disk1/oradata/sdsrcdb/ctuni_zj_data01.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/ctuni_ah_data01.dbf' to '/disk1/oradata/sdsrcdb/ctuni_ah_data01.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/ctuni_fj_data01.dbf' to '/disk1/oradata/sdsrcdb/ctuni_fj_data01.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/ctuni_jx_data01.dbf' to '/disk1/oradata/sdsrcdb/ctuni_jx_data01.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/ctuni_hn_data01.dbf' to '/disk1/oradata/sdsrcdb/ctuni_hn_data01.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/ctuni_hbs_data01.dbf' to '/disk1/oradata/sdsrcdb/ctuni_hbs_data01.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/ctuni_hns_data01.dbf' to '/disk1/oradata/sdsrcdb/ctuni_hns_data01.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/ctuni_gd_data01.dbf' to '/disk1/oradata/sdsrcdb/ctuni_gd_data01.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/ctuni_gx_data01.dbf' to '/disk1/oradata/sdsrcdb/ctuni_gx_data01.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/ctuni_cq_data01.dbf' to '/disk1/oradata/sdsrcdb/ctuni_cq_data01.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/ctuni_sc_data01.dbf' to '/disk1/oradata/sdsrcdb/ctuni_sc_data01.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/ctuni_gz_data01.dbf' to '/disk1/oradata/sdsrcdb/ctuni_gz_data01.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/ctuni_xz_data01.dbf' to '/disk1/oradata/sdsrcdb/ctuni_xz_data01.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/ctuni_sxs_data01.dbf' to '/disk1/oradata/sdsrcdb/ctuni_sxs_data01.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/ctuni_gs_data01.dbf' to '/disk1/oradata/sdsrcdb/ctuni_gs_data01.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/ctuni_qh_data01.dbf' to '/disk1/oradata/sdsrcdb/ctuni_qh_data01.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/ctuni_nx_data01.dbf' to '/disk1/oradata/sdsrcdb/ctuni_nx_data01.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/ctuni_xj_data01.dbf' to '/disk1/oradata/sdsrcdb/ctuni_xj_data01.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/ctuni_bj_index01.dbf' to '/disk1/oradata/sdsrcdb/ctuni_bj_index01.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/ctuni_tj_index01.dbf' to '/disk1/oradata/sdsrcdb/ctuni_tj_index01.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/ctuni_hb_index01.dbf' to '/disk1/oradata/sdsrcdb/ctuni_hb_index01.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/ctuni_sx_index01.dbf' to '/disk1/oradata/sdsrcdb/ctuni_sx_index01.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/ctuni_nmg_index01.dbf' to '/disk1/oradata/sdsrcdb/ctuni_nmg_index01.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/ctuni_ln_index01.dbf' to '/disk1/oradata/sdsrcdb/ctuni_ln_index01.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/ctuni_jl_index01.dbf' to '/disk1/oradata/sdsrcdb/ctuni_jl_index01.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/ctuni_hlj_index01.dbf' to '/disk1/oradata/sdsrcdb/ctuni_hlj_index01.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/ctuni_sh_index01.dbf' to '/disk1/oradata/sdsrcdb/ctuni_sh_index01.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/ctuni_js_index01.dbf' to '/disk1/oradata/sdsrcdb/ctuni_js_index01.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/ctuni_zj_index01.dbf' to '/disk1/oradata/sdsrcdb/ctuni_zj_index01.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/ctuni_ah_index01.dbf' to '/disk1/oradata/sdsrcdb/ctuni_ah_index01.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/ctuni_fj_index01.dbf' to '/disk1/oradata/sdsrcdb/ctuni_fj_index01.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/ctuni_jx_index01.dbf' to '/disk1/oradata/sdsrcdb/ctuni_jx_index01.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/ctuni_hn_index01.dbf' to '/disk1/oradata/sdsrcdb/ctuni_hn_index01.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/ctuni_hbs_index01.dbf' to '/disk1/oradata/sdsrcdb/ctuni_hbs_index01.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/ctuni_hns_index01.dbf' to '/disk1/oradata/sdsrcdb/ctuni_hns_index01.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/ctuni_gd_index01.dbf' to '/disk1/oradata/sdsrcdb/ctuni_gd_index01.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/ctuni_gx_index01.dbf' to '/disk1/oradata/sdsrcdb/ctuni_gx_index01.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/ctuni_cq_index01.dbf' to '/disk1/oradata/sdsrcdb/ctuni_cq_index01.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/ctuni_sc_index01.dbf' to '/disk1/oradata/sdsrcdb/ctuni_sc_index01.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/ctuni_gz_index01.dbf' to '/disk1/oradata/sdsrcdb/ctuni_gz_index01.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/ctuni_xz_index01.dbf' to '/disk1/oradata/sdsrcdb/ctuni_xz_index01.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/ctuni_sxs_index01.dbf' to '/disk1/oradata/sdsrcdb/ctuni_sxs_index01.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/ctuni_gs_index01.dbf' to '/disk1/oradata/sdsrcdb/ctuni_gs_index01.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/ctuni_qh_index01.dbf' to '/disk1/oradata/sdsrcdb/ctuni_qh_index01.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/ctuni_nx_index01.dbf' to '/disk1/oradata/sdsrcdb/ctuni_nx_index01.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/ctuni_xj_index01.dbf' to '/disk1/oradata/sdsrcdb/ctuni_xj_index01.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/ctuni_pub_data03.dbf' to '/disk1/oradata/sdsrcdb/ctuni_pub_data03.dbf' ;
set newname for datafile '/disk/oradata/sdsrcdb/ctuni_pub_data04.dbf' to '/disk1/oradata/sdsrcdb/ctuni_pub_data04.dbf' ;
restore database ;
switch datafile all;
}

8、使用归档恢复
RMAN> recover database;

Starting recover at 2016-03-14 16:33:00
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=4 device type=DISK

starting media recovery

unable to find archived log
archived log thread=1 sequence=522
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/14/2016 16:33:01
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 522 and starting SCN of 90708322

出现问题，查看为currentlog 没有备份到，重新备份归档
catalog backuppiece '/disk1/rhpt/arch_SDSRCDB_20160315_11_1';

RMAN> recover database;
9. 打开数据库
SQL> alter database open resetlogs;

10、创建spfile,保证以后可以直接 startup就可以
SQL> create spfile from pfile='/disk1/sdpfile';

File created.

11、添加临时文件

alter tablespace temp add tempfile '/disk1/oradata/sdsrcdb/temp01.dbf' size 5000M ;