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

数据迁移_把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 ;

标签：group,另一台,database,edms,SQL,Oracle,ora11g,alter,RAC

来源： https://www.cnblogs.com/zhouwanchun/p/10682734.html