数据迁移_把RAC环境备份的数据，恢复到另一台单机Oracle本地文件系统下

作者：Eric
微信：loveoracle11g


1、创建pfile文件
# su - ora11g
# cd $ORACLE_HOME/dbs
# vim initedms.ora

--------------------------------粘贴复制--------------------------------
db_name='edms'
memory_target=1G
processes=150
audit_file_dest='/ora11g/app/oracle/admin/edms/adump'
audit_trail='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest='/ora11g/app/oracle/flash_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest='/ora11g/app/oracle'
dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
open_cursors=300 
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
control_files=('/ora11g/app/oracle/oradata/edms/ora_control1.ctl','/ora11g/app/oracle/oradata/edms/ora_control2.ctl')
compatible='11.2.0.4'
log_archive_dest_1='location=/ora11g/app/oracle/oradata/arch'


创建参数文件中用到的目录
mkdir -p /ora11g/app/oracle/admin/edms/adump
mkdir -p /ora11g/app/oracle/oradata/edms
mkdir -p /ora11g/app/oracle/flash_recovery_area


登录sqlplus以修改后的pfile启动数据库到nomount
# export ORACLE_SID=edms
# sqlplus / as sysdba


SQL> startup nomount force ;


SQL> create spfile from pfile ;
SQL> show parameter spfile ;
SQL> exit ;


2、恢复控制文件controlfile
登录rman执行恢复最新的控制文件
# ls -ltr /backup/rman/edms/ctl*
-rw-r-----. 1 ora11g oinstall 18939904 Nov 22 16:56 /backup/rman/edms/ctl_file_0qtijmsh_1_1_20181119.bak

确定最新的控制文件备份
# export ORACLE_SID=edms
# rman target /

RMAN> restore controlfile from '/backup/rman/edms/ctl_file_0qtijmsh_1_1_20181119.bak' ;
RMAN> alter database mount ;


3、恢复数据文件
在rman里面敲
RMAN> catalog start with '/backup/rman/edms/' ;

输入：yes

run {
allocate channel c1 device type DISK;
allocate channel c2 device type DISK;
allocate channel c3 device type DISK;
allocate channel c4 device type DISK;
SET NEWNAME FOR DATABASE TO '/ora11g/app/oracle/oradata/edms/%U';
restore database;
switch datafile all;
switch tempfile all;
set until time "TO_DATE('11/19/2018 10:00:00', 'MM/DD/YYYY HH24:MI:SS')";
recover database delete archivelog;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}

月/日/年 时:分:秒


4、重命名Redo log文件
SQL> set linesize 200
SQL> select  'alter database rename file '''||MEMBER||''' to '''||'/ora11g/app/oracle/oradata/edms/'||regexp_substr(MEMBER,'[^\/]+',1,4)||''';' from v$logfile;

'ALTERDATABASERENAMEFILE'''||MEMBER||'''TO'''||'/ORA11G/APP/ORACLE/ORADATA/EDMS/'||REGEXP_SUBSTR(MEMBER,'[^\/]+',1,4)||''';'
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
alter database rename file '+DATADG/edms/onlinelog/group_5.358.989230181' to '/ora11g/app/oracle/oradata/edms/group_5.358.989230181';
alter database rename file '+FRADG/edms/onlinelog/group_5.1297.989230181' to '/ora11g/app/oracle/oradata/edms/group_5.1297.989230181';
alter database rename file '+DATADG/edms/onlinelog/group_2.357.989230179' to '/ora11g/app/oracle/oradata/edms/group_2.357.989230179';
alter database rename file '+FRADG/edms/onlinelog/group_2.510.989230179' to '/ora11g/app/oracle/oradata/edms/group_2.510.989230179';
alter database rename file '+DATADG/edms/onlinelog/group_1.356.989230179' to '/ora11g/app/oracle/oradata/edms/group_1.356.989230179';
alter database rename file '+FRADG/edms/onlinelog/group_1.1352.989230179' to '/ora11g/app/oracle/oradata/edms/group_1.1352.989230179';
alter database rename file '+DATADG/edms/onlinelog/group_3.361.989230385' to '/ora11g/app/oracle/oradata/edms/group_3.361.989230385';
alter database rename file '+FRADG/edms/onlinelog/group_3.1474.989230387' to '/ora11g/app/oracle/oradata/edms/group_3.1474.989230387';
alter database rename file '+DATADG/edms/onlinelog/group_4.362.989230387' to '/ora11g/app/oracle/oradata/edms/group_4.362.989230387';
alter database rename file '+FRADG/edms/onlinelog/group_4.1371.989230387' to '/ora11g/app/oracle/oradata/edms/group_4.1371.989230387';
alter database rename file '+DATADG/edms/onlinelog/group_6.363.989230387' to '/ora11g/app/oracle/oradata/edms/group_6.363.989230387';
alter database rename file '+FRADG/edms/onlinelog/group_6.832.989230387' to '/ora11g/app/oracle/oradata/edms/group_6.832.989230387';

12 rows selected.
执行上面生成的sql


上面有几个，就创建几个日志组，上面有6个。
SQL> alter database clear logfile group 1;
SQL> alter database clear logfile group 2;
SQL> alter database clear logfile group 3;
SQL> alter database clear logfile group 4;
SQL> alter database clear logfile group 5;
SQL> alter database clear logfile group 6;
SQL> alter database clear logfile group 7;
SQL> alter database clear logfile group 8;
SQL> alter database clear logfile group 9;
SQL> alter database clear logfile group 10;


SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING; # 报错不管它


5、打开数据库
SQL> alter database open resetlogs ;


重启数据库
SQL> startup force ;


6、检查
看参数文件
SQL> show parameter spfile ;

看控制文件
SQL> select NAME from v$controlfile ;

看数据文件
SQL> select NAME from v$datafile ;

看日志文件
SQL> select MEMBER from v$logfile ;