數(shù)據(jù)遷移_把RAC環(huán)境備份的數(shù)據(jù)，恢復(fù)到另一臺(tái)單機(jī)Oracle本地文件系統(tǒng)下

作者：Eric
微信：loveoracle11g


1、創(chuàng)建pfile文件
# su - ora11g
# cd $ORACLE_HOME/dbs
# vim initedms.ora

--------------------------------粘貼復(fù)制--------------------------------
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'


創(chuàng)建參數(shù)文件中用到的目錄
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啟動(dòng)數(shù)據(jù)庫(kù)到nomount
# export ORACLE_SID=edms
# sqlplus / as sysdba


SQL> startup nomount force ;


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


2、恢復(fù)控制文件controlfile
登錄rman執(zhí)行恢復(fù)最新的控制文件
# 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、恢復(fù)數(shù)據(jù)文件
在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;
}

月/日/年 時(shí):分:秒


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.
執(zhí)行上面生成的sql


上面有幾個(gè)，就創(chuàng)建幾個(gè)日志組，上面有6個(gè)。
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; # 報(bào)錯(cuò)不管它


5、打開(kāi)數(shù)據(jù)庫(kù)
SQL> alter database open resetlogs ;


重啟數(shù)據(jù)庫(kù)
SQL> startup force ;


6、檢查
看參數(shù)文件
SQL> show parameter spfile ;

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

看數(shù)據(jù)文件
SQL> select NAME from v$datafile ;

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

?