1、RAC主库备份
RMAN>backup database format '/backup/rman/full_%U.bak' plus archivelog format '/backup/rman/arch_%U.bak';
----先归档，再备份归档日志，再备份所有数据文件，再备份当前控制文件和当前spfile，再归档，再备份刚生成的归档日志。

2、把主库备份拷贝到单机的目录/home/oracle

3、单机创建pfile，参数中，db_name和rac主库一致，_开头的参数全部删除，只留下*.开头的参数，参数内容中根据单机的实际情况进行修改(比如去掉cluster_database等参数，确保参数中的目录在单机中存在且可访问)

4、单机创建密码文件
orapwd file=/u01/app/oracle/product/11.2.0/db_1/dbs/orapwSID password=123456 entries=10;

5、单机通过pfile启动到nomount状态

6、单机rman恢复拷贝过来的控制文件,并catalog所有拷贝过来的文件

RMAN>restore controlfile from '/home/oracle/control';
RMAN>catalog backuppiece '/home/oracle/full_data';
RMAN>catalog backuppiece '/home/oracle/arch';

7、单机alter database mount

8、单机恢复数据文件(当然也可以recover到某个thread的某个sequence，如何找这个最近时间点的thread和sequence呢，单机可以直接recover database，这样会出现类似后面的提示：media recovery requesting unknown archived log for thread 2 with sequence 567 and starting SCN of 24628526；再执行后面语句就ok了，recover database until sequence 567 thread 2;)

RUN {
set UNTIL TIME "TO_DATE('2017-01-01 10:00:00','YYYY-MM-DD hh24:mi:ss')";

SET NEWNAME FOR DATAFILE 1 to '/oracle/ora11g/oradata/system01.dbf';

SET NEWNAME FOR DATAFILE 2 to '/oracle/ora11g/oradata/sysaux01.dbf';

SET NEWNAME FOR DATAFILE 3 to '/oracle/ora11g/oradata/undotbs01.dbf';

SET NEWNAME FOR DATAFILE 4 to '/oracle/ora11g/oradata/users01.dbf';

SET NEWNAME FOR DATAFILE 5 to '/oracle/ora11g/oradata/undotbs02.dbf';

SET NEWNAME FOR DATAFILE 6 to '/oracle/ora11g/oradata/test01.dbf';

SET NEWNAME FOR TEMPFILE 1 to '/oracle/ora11g/oradata/temp01.dbf';

RESTORE DATABASE;

SWITCH DATAFILE ALL;

SWITCH TEMPFILE ALL;

recover database;

}

--SET NEWNAME用到的新路径，可以通过如下sql来拼接，比如把+DATA/pdb替换为/u01/app/oracle/oradata/PDB
select file_id,file_name,'SET NEWNAME FOR DATAFILE '||file_id||' to '''||replace(file_name, '+DATA/pdb', '/u01/app/oracle/oradata/PDB')||''';' from dba_data_files order by file_id;
select file_id,file_name,'SET NEWNAME FOR DATAFILE '||file_id||' to '''||replace(file_name, '+DATA/pdb', '/u01/app/oracle/oradata/PDB')||''';' from dba_temp_files order by file_id;

--以上rman运行命令可以写到一个文件中，如restorecover.rman，再在后台运行这个rman语句
no
hup rman target / cmdfile=recover.rman msglog=recover.log &

9、单机重建控制文件
执行sql>alter database backup controlfile to trace as '/home/oracle/ctl.control';
修改以上控制文件中redo 日志路径，原来有四组两个thread，每组两个redo，不用管，直接把留下两组redo，不用管着两组来自哪个thread，这样就默认创建thread 1的在线日志，并且使用RESETLOGS ARCHIVELOG

改后的控制文件内容如
STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "TESTDB" RESETLOGS ARCHIVELOG

MAXLOGFILES 192

MAXLOGMEMBERS 3

MAXDATAFILES 1024

MAXINSTANCES 32

MAXLOGHISTORY 584

LOGFILE

GROUP 1 (

'/oracle/ora11g/oradata/redo01.log'

) SIZE 50M BLOCKSIZE 512,

GROUP 2 (

'/oracle/ora11g/oradata/redo02.log'

) SIZE 50M BLOCKSIZE 512,

-- STANDBY LOGFILE

DATAFILE

'/oracle/ora11g/oradata/system01.dbf',

'/oracle/ora11g/oradata/sysaux01.dbf',

'/oracle/ora11g/oradata/undotbs01.dbf',

'/oracle/ora11g/oradata/users01.dbf',

'/oracle/ora11g/oradata/undotbs02.dbf',

'/oracle/ora11g/oradata/test01.dbf'

CHARACTER SET AL32UTF8

;

10、单机shutdown abort后使用修改后的控制文件启动，会自动到mount状态
sqlplus>shutdown abort;
sqlplus>@/home/oracle/ctl.control

11、上面第10步启动到moun状态后，只创建了thread 1的在线日志，需要重新创建thread2的在线日志

ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 3 '/oracle/ora11g/oradata/redo03.dbf' SIZE 50M,GROUP 4 '/oracle/ora11g/oradata/redo04.dbf' SIZE 50M;

12、单机执行sql>alter database open resetlogs;

13、单机删除多余的thread 2的在线日志
alter database disable thread 2;
alter database drop logfile group 3;
alter database drop logfile group 4;

14、单机删除多余的undo、新增tempfile
select name from v$tablespace where name like 'UNDO%';

show parameter undo_tablespace;

drop tablespace undotbs2 including contents and datafiles;
select file_name from dba_temp_files;
alter tablespace temp add tempfile 'XX' size 100M autoextend on next 100M;

心得：
第9步做完后，可以直接alter database open resetlogs吗，不可以，因为控制文件中记录的在线日志还是ASM路径的，会报错找不到在线日志文件，报错信息为ERROR: slave communication error with ASM
第11步如果不做的话直接做第12步，第12步会报错ORA-38856: cannot mark instance UNNAMED_INSTANCE_2 (redo thread 2) as enabled

ITPub博客

RAC恢复到单机

最新文章