RAC 恢复到单实例 ASM 和文件系统方法

jnrjian

已于 2022-04-16 06:19:57 修改

阅读量1.1k

点赞数

文章标签： dba

于 2022-04-15 21:01:02 首次发布

本文链接：https://blog.csdn.net/jnrjian/article/details/124203488

版权

ASM到ASM简单一点

ASM到RPM安装下的oracle，三点要改 datafile rename，arch改变，redo改动

改动太多，后期恢复完 recover完后重建控制文件。

1.set archivelog destination to ''/opt/oracle/arch';
2.set newname for datafile
3.alter database rename file '+DATA/RAC/ONLINELOG/group_6.311.1102011695' to '/opt/oracle/oradata/group_6.311.1102011695';

具体可以参考：

【RAC】将RAC备份集恢复为单实例数据库_ITPUB博客

将RAC备份集恢复到单实例数据库的过程基本上就是先将备份集恢复为RAC数据库，然后再将数据库转换为单实例的数据库。

数据库的备份可以使用如下的脚本：

1run
2{
3 allocate channel c1 type disk;
4 allocate channel c2 type disk;
5 backup database format '/home/oracle/rman_back/full_%n_%T_%t_%s_%p.bak';
6 sql 'alter system archive log current';
7 backup archivelog all format '/home/oracle/rman_back/arch_%d_%T_%s_%p.bak' delete input;
8 backup current controlfile format '/home/oracle/rman_back/ctl_%d_%T_%s_%p.bak';
9 release channel c1;
10 release channel c2;
11}

将RAC备份集恢复到单实例数据库可以分为恢复为ASM存储的单实例和FS存储的单实例，其处理过程分别不同。

1、rac恢复到ASM中

1ORACLE_SID=lhrdbasm
2startup nomount;
3set dbid 2136828548
4restore spfile to '/tmp/aabb.ora' from '/home/oracle/rman_back/full_LHRRAC1x_20180711_981219654_4_1.bak';
5
6strings /tmp/aabb.ora
7
8*.audit_file_dest='/u01/app/oracle/admin/lhrdbasm/adump'
9*.audit_trail='db'
10*.compatible='11.2.0.0.0'
11*.control_files='+FRA','+FRA'
12*.db_block_size=8192
13*.db_create_file_dest='+FRA'
14*.db_domain=''
15*.db_name='lhrrac1'
16*.db_recovery_file_dest='+FRA'
17*.db_recovery_file_dest_size=20558159872
18*.diagnostic_dest='/u01/app/oracle'
19*.dispatchers='(PROTOCOL=TCP) (SERVICE=lhrdbasmXDB)'
20*.memory_target=630194176
21*.open_cursors=300
22*.processes=150
23*.remote_login_passwordfile='exclusive'
24
25
26mkdir -p /u01/app/oracle/admin/lhrdbasm/adump
27create spfile from pfile='/tmp/b.txt';
28startup nomount force;
29restore controlfile from '/home/oracle/rman_back/ctl_LHRRAC1_20180711_7_1.bak';
30alter database mount;
31
32set line 9999 pagesize 9999
33col FILE_NAME format a60
34select 'datafile' file_type, file#,name FILE_NAME,status,enabled from v$datafile
35union all
36select 'tempfile',file#,name FILE_NAME,status,enabled from v$tempfile
37union all
38select 'logfile',group# file#,member FILE_NAME,status,'' from v$logfile
39union all
40select 'controlfile', to_number('') ,name FILE_NAME,status,'' from v$controlfile;
41
42
43set pagesize 200 linesize 200
44select 'set newname for datafile ' || a.FILE# || ' to "' || a.NAME || '";' from v$datafile a
45union all
46select 'set newname for tempfile ' || a.FILE# || ' to "' || a.NAME || '";' from v$tempfile a
47union all
48SELECT 'SQL "ALTER DATABASE RENAME FILE ''''' || a.MEMBER || ''''' to ''''' || a.MEMBER || ''''' ";' FROM v$logfile a;
49
50
51set pagesize 200 linesize 200
52select 'set newname for datafile ' || a.FILE# || ' to "+FRA";' from v$datafile a
53union all
54select 'set newname for tempfile ' || a.FILE# || ' to "+FRA";' from v$tempfile a
55union all
56SELECT 'SQL "ALTER DATABASE RENAME FILE ''''' || a.MEMBER || ''''' to ''''+FRA'''' ";' FROM v$logfile a;
57
58
59run {
60 ALLOCATE CHANNEL c1 DEVICE TYPE DISK;
61 set newname for datafile 1 to "+FRA";
62 set newname for datafile 2 to "+FRA";
63 set newname for datafile 3 to "+FRA";
64 set newname for datafile 4 to "+FRA";
65 set newname for datafile 5 to "+FRA";
66 set newname for datafile 6 to "+FRA";
67 set newname for tempfile 1 to "+FRA";
68 SQL "ALTER DATABASE RENAME FILE ''+DATA/lhrrac1/onlinelog/group_2.264.976375887'' to ''+FRA'' ";
69 SQL "ALTER DATABASE RENAME FILE ''+DATA/lhrrac1/onlinelog/group_2.265.976375889'' to ''+FRA'' ";
70 SQL "ALTER DATABASE RENAME FILE ''+DATA/lhrrac1/onlinelog/group_1.262.976375881'' to ''+FRA'' ";
71 SQL "ALTER DATABASE RENAME FILE ''+DATA/lhrrac1/onlinelog/group_1.263.976375883'' to ''+FRA'' ";
72 SQL "ALTER DATABASE RENAME FILE ''+DATA/lhrrac1/onlinelog/group_3.269.976376503'' to ''+FRA'' ";
73 SQL "ALTER DATABASE RENAME FILE ''+DATA/lhrrac1/onlinelog/group_3.270.976376505'' to ''+FRA'' ";
74 SQL "ALTER DATABASE RENAME FILE ''+DATA/lhrrac1/onlinelog/group_4.271.976376507'' to ''+FRA'' ";
75 SQL "ALTER DATABASE RENAME FILE ''+DATA/lhrrac1/onlinelog/group_4.272.976376509'' to ''+FRA'' ";
76 restore database;
77 SWITCH DATAFILE ALL;
78 SWITCH TEMPFILE ALL;
79 release channel c1;
80 }
81
82
83list backupset of archivelog all;
84RUN
85{
86set until sequence 10 thread 1;
87set until sequence 7 thread 2;
88recover database;
89}
90
91alter database open resetlogs;
92
93
94col instance format a20
95select thread#,instance,status,enabled from v$thread;
96alter database disable thread 2 ;
97alter database drop logfile group 3 ;
98alter database drop logfile group 4 ;
99
100
101drop tablespace undotbs2 including contents and datafiles;
102
1.
2.
3.
4.

2、RAC恢复到FS中

1ORACLE_SID=lhrfs
2startup nomount;
3set dbid 2136828548
4restore spfile to '/tmp/aabbcc.ora' from '/home/oracle/rman_back/full_LHRRAC1x_20180711_981219654_4_1.bak';
5
6*.audit_file_dest='/u01/app/oracle/admin/lhrfs/adump'
7*.audit_trail='db'
8*.compatible='11.2.0.0.0'
9*.control_files='/u01/app/oracle/oradata/lhrfs/control01.dbf','/u01/app/oracle/oradata/lhrfs/control02.dbf'
10*.db_block_size=8192
11*.db_create_file_dest='/u01/app/oracle/oradata/lhrfs'
12*.db_domain=''
13*.db_name='lhrrac1'
14*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
15*.db_recovery_file_dest_size=4558159872
16*.diagnostic_dest='/u01/app/oracle'
17*.dispatchers='(PROTOCOL=TCP) (SERVICE=lhrfsXDB)'
18*.memory_target=630194176
19*.open_cursors=300
20*.processes=150
21*.remote_login_passwordfile='exclusive'
22
23
24
25create spfile from pfile='/tmp/b.txt';
26startup nomount force;
27restore controlfile from '/home/oracle/rman_back/ctl_LHRRAC1_20180711_7_1.bak';
28alter database mount;
29
30set line 9999 pagesize 9999
31col FILE_NAME format a60
32select 'datafile' file_type, file#,name FILE_NAME,status,enabled from v$datafile
33union all
34select 'tempfile',file#,name FILE_NAME,status,enabled from v$tempfile
35union all
36select 'logfile',group# file#,member FILE_NAME,status,'' from v$logfile
37union all
38select 'controlfile', to_number('') ,name FILE_NAME,status,'' from v$controlfile;
39
40
41set pagesize 200 linesize 200
42select 'set newname for datafile ' || a.FILE# || ' to "' || a.NAME || '";' from v$datafile a
43union all
44select 'set newname for tempfile ' || a.FILE# || ' to "' || a.NAME || '";' from v$tempfile a
45union all
46SELECT 'SQL "ALTER DATABASE RENAME FILE ''''' || a.MEMBER || ''''' to ''''' || a.MEMBER || ''''' ";' FROM v$logfile a;
47
48
49set newname for datafile 1 to "/u01/app/oracle/oradata/lhrfs/system01.dbf";
50set newname for datafile 2 to "/u01/app/oracle/oradata/lhrfs/sysaux01.dbf";
51set newname for datafile 3 to "/u01/app/oracle/oradata/lhrfs/undotbs101.dbf";
52set newname for datafile 4 to "/u01/app/oracle/oradata/lhrfs/users01.dbf";
53set newname for datafile 5 to "/u01/app/oracle/oradata/lhrfs/example01.dbf";
54set newname for datafile 6 to "/u01/app/oracle/oradata/lhrfs/undotbs201.dbf";
55set newname for tempfile 1 to "/u01/app/oracle/oradata/lhrfs/temp01.dbf";
56SQL "ALTER DATABASE RENAME FILE ''+DATA/lhrrac1/onlinelog/group_2.264.976375887'' to ''/u01/app/oracle/oradata/lhrfs/G2_redo01.log'' ";
57SQL "ALTER DATABASE RENAME FILE ''+DATA/lhrrac1/onlinelog/group_2.265.976375889'' to ''/u01/app/oracle/oradata/lhrfs/G2_redo02.log'' ";
58SQL "ALTER DATABASE RENAME FILE ''+DATA/lhrrac1/onlinelog/group_1.262.976375881'' to ''/u01/app/oracle/oradata/lhrfs/G1_redo01.log'' ";
59SQL "ALTER DATABASE RENAME FILE ''+DATA/lhrrac1/onlinelog/group_1.263.976375883'' to ''/u01/app/oracle/oradata/lhrfs/G1_redo02.log'' ";
60SQL "ALTER DATABASE RENAME FILE ''+DATA/lhrrac1/onlinelog/group_3.269.976376503'' to ''/u01/app/oracle/oradata/lhrfs/G3_redo01.log'' ";
61SQL "ALTER DATABASE RENAME FILE ''+DATA/lhrrac1/onlinelog/group_3.270.976376505'' to ''/u01/app/oracle/oradata/lhrfs/G3_redo02.log'' ";
62SQL "ALTER DATABASE RENAME FILE ''+DATA/lhrrac1/onlinelog/group_4.271.976376507'' to ''/u01/app/oracle/oradata/lhrfs/G4_redo01.log'' ";
63SQL "ALTER DATABASE RENAME FILE ''+DATA/lhrrac1/onlinelog/group_4.272.976376509'' to ''/u01/app/oracle/oradata/lhrfs/G4_redo02.log'' ";
64
65
66
67run {
68 ALLOCATE CHANNEL c1 DEVICE TYPE DISK;
69set newname for datafile 1 to "/u01/app/oracle/oradata/lhrfs/system01.dbf";
70set newname for datafile 2 to "/u01/app/oracle/oradata/lhrfs/sysaux01.dbf";
71set newname for datafile 3 to "/u01/app/oracle/oradata/lhrfs/undotbs101.dbf";
72set newname for datafile 4 to "/u01/app/oracle/oradata/lhrfs/users01.dbf";
73set newname for datafile 5 to "/u01/app/oracle/oradata/lhrfs/example01.dbf";
74set newname for datafile 6 to "/u01/app/oracle/oradata/lhrfs/undotbs201.dbf";
75set newname for tempfile 1 to "/u01/app/oracle/oradata/lhrfs/temp01.dbf";
76SQL "ALTER DATABASE RENAME FILE ''+DATA/lhrrac1/onlinelog/group_2.264.976375887'' to ''/u01/app/oracle/oradata/lhrfs/G2_redo01.log'' ";
77SQL "ALTER DATABASE RENAME FILE ''+DATA/lhrrac1/onlinelog/group_2.265.976375889'' to ''/u01/app/oracle/oradata/lhrfs/G2_redo02.log'' ";
78SQL "ALTER DATABASE RENAME FILE ''+DATA/lhrrac1/onlinelog/group_1.262.976375881'' to ''/u01/app/oracle/oradata/lhrfs/G1_redo01.log'' ";
79SQL "ALTER DATABASE RENAME FILE ''+DATA/lhrrac1/onlinelog/group_1.263.976375883'' to ''/u01/app/oracle/oradata/lhrfs/G1_redo02.log'' ";
80SQL "ALTER DATABASE RENAME FILE ''+DATA/lhrrac1/onlinelog/group_3.269.976376503'' to ''/u01/app/oracle/oradata/lhrfs/G3_redo01.log'' ";
81SQL "ALTER DATABASE RENAME FILE ''+DATA/lhrrac1/onlinelog/group_3.270.976376505'' to ''/u01/app/oracle/oradata/lhrfs/G3_redo02.log'' ";
82SQL "ALTER DATABASE RENAME FILE ''+DATA/lhrrac1/onlinelog/group_4.271.976376507'' to ''/u01/app/oracle/oradata/lhrfs/G4_redo01.log'' ";
83SQL "ALTER DATABASE RENAME FILE ''+DATA/lhrrac1/onlinelog/group_4.272.976376509'' to ''/u01/app/oracle/oradata/lhrfs/G4_redo02.log'' ";
84 restore database;
85 SWITCH DATAFILE ALL;
86 SWITCH TEMPFILE ALL;
87 release channel c1;
88 }
89
90
91list backupset of archivelog all;
92RUN
93{
94set until sequence 10 thread 1;
95set until sequence 7 thread 2;
96recover database;
97}
98
99alter database open resetlogs;
100
101
102col instance format a20
103select thread#,instance,status,enabled from v$thread;
104alter database disable thread 2 ;
105alter database drop logfile group 3 ;
106alter database drop logfile group 4 ;
107
108
109drop tablespace undotbs2 including contents and datafiles;
110

-----实际练习---

-----

alter database rename file '+DATA/RAC/ONLINELOG/group_6.310.1102011683' to '/opt/oracle/oradata/group_6.310.1102011683';
alter database rename file '+DATA/RAC/ONLINELOG/group_6.311.1102011695' to '/opt/oracle/oradata/group_6.311.1102011695';

run {
set newname for datafile '+DATA/RAC/DCA5E07A37A901F7E0536E01A8C07422/DATAFILE/undo_4.322.1102012413' to '/opt/oracle/oradata/undo_4.322.1102012413';
set newname for datafile '+DATA/RAC/DCA5E07A37A901F7E0536E01A8C07422/DATAFILE/users.323.1102012417' to '/opt/oracle/oradata/users.323.1102012417';
set until scn 2829240;
restore database ;
switch datafile all;
recover database;
}

RMAN> run {
set archivelog destination to ''/opt/oracle/arch';
set until scn 2829240;
switch datafile all;
restore archivelog all;
recover database;
}

alter database backup controlfile to trace as '/opt/oracle/oradata.trc' reuse resetlogs;

-------
ORA-38856: Cannot mark instance UNNAMED_INSTANCE_2 (redo thread 2) as enabled
1. Set the following parameter in the auxiliary init.ora file: _no_recovery_through_resetlogs=TRUE.
2. Open the database in resetlogs mode.
3. Remove _no_recovery_through_resetlogs=TRUE from init.ora.
4. Restart database.
或者
ALTER DATABASE ADD LOGFILE THREAD 2
GROUP 14 '/opt/oracle/oradata/group_4.265.779207453' SIZE 10M REUSE,
GROUP 15 '/opt/oracle/oradata/group_5.266.779207459' SIZE 10M REUSE,
GROUP 16 '/opt/oracle/oradata/group_6.267.779207467' SIZE 10M REUSE;

4、修改数据文件和日志文件路径
select 'set newname for datafile '||chr(39)||name||chr(39)||' to '||chr(39)||replace(name,'+DG0/db/datafile/','/home/oracle/data2/')||chr(39)||';' from v$datafile;
#######################

SQL> select 'set newname for datafile '||chr(39)||name||chr(39)||' to '||chr(39)||replace(name,'+DG0/db/datafile/','/home/oracle/data2/')||chr(39)||';' from v$datafile;

'SETNEWNAMEFORDATAFILE'||CHR(39)||NAME||CHR(39)||'TO'||CHR(39)||REPLACE(NAME,'+DG0/DB/DATAFILE/','/HOME/ORACLE/DATA2/')||CHR(39)||';'
------------------------------------------------------------------------------------------------------------------------------------------------------
set newname for datafile '+DG0/db/datafile/sysaux.257.780159407' to '/home/oracle/data2/sysaux.257.780159407';
set newname for datafile '+DG0/db/datafile/system.256.780159407' to '/home/oracle/data2/system.256.780159407';
set newname for datafile '+DG0/db/datafile/timelineweb01.dbf' to '/home/oracle/data2/timelineweb01.dbf';
set newname for datafile '+DG0/db/datafile/undotbs1.258.780159409' to '/home/oracle/data2/undotbs1.258.780159409';
set newname for datafile '+DG0/db/datafile/undotbs2.264.780159511' to '/home/oracle/data2/undotbs2.264.780159511';
set newname for datafile '+DG0/db/datafile/users.259.780159409' to '/home/oracle/data2/users.259.780159409';

6 rows selected.

######################
5. 恢复数据库到新的目录（我的环境是原来是RAW恢复到文件系统）
select 'alter database rename file '||chr(39)||member||chr(39)||' to '||chr(39)||replace(member,'+DG0/db/onlinelog/','/home/oracle/data2/')||chr(39)||';' from v$logfile;
SQL> select 'alter database rename file '||chr(39)||member||chr(39)||' to '||chr(39)||replace(member,'+DG0/db/onlinelog/','/home/oracle/data2/')||chr(39)||';' from v$logfile;

#*.audit_file_dest='dump'
*.audit_trail='db'
*.cluster_database=false
*.compatible='19.0.0'
*.control_files='/opt/oracle/oradata/control_01.ctl'
*.db_block_size=8192
*.db_name='RAC'
*.db_recovery_file_dest_size=5932m
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=RACXDB)'
*.enable_pluggable_database=true
family:dw_helper.instance_mode='read-only'
*.local_listener='-oraagent-dummy-'
*.log_archive_format='%t_%s_%r.dbf'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=459m
*.processes=300
*.remote_login_passwordfile='exclusive'
*.sga_target=1377m
*.undo_tablespace='UNDOTBS1'
_no_recovery_through_resetlogs=TRUE
~

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "RAC" RESETLOGS NOARCHIVELOG
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 32
MAXLOGHISTORY 292
LOGFILE
GROUP 1 (
'/opt/oracle/oradata/group_4.265.779207453'
) SIZE 200M BLOCKSIZE 512,
GROUP 2 (
'/opt/oracle/oradata/group_5.266.779207459'
) SIZE 200M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/opt/oracle/oradata/system.286.1102009977',
'/opt/oracle/oradata/sysaux.287.1102010035',
'/opt/oracle/oradata/undotbs1.288.1102010061',
'/opt/oracle/oradata/system.297.1102010717',
'/opt/oracle/oradata/sysaux.298.1102010717',
'/opt/oracle/oradata/users.289.1102010061',
'/opt/oracle/oradata/undotbs1.299.1102010717',
'/opt/oracle/oradata/undotbs2.301.1102011161',
'/opt/oracle/oradata/undotbs3.302.1102011161',
'/opt/oracle/oradata/system.318.1102012385',
'/opt/oracle/oradata/sysaux.319.1102012385',
'/opt/oracle/oradata/undotbs1.317.1102012385',
'/opt/oracle/oradata/undo_3.321.1102012411',
'/opt/oracle/oradata/undo_4.322.1102012413',
'/opt/oracle/oradata/users.323.1102012417'
CHARACTER SET AL32UTF8
;

-- Configure RMAN configuration record 1
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '+DATA';
-- ALTER DATABASE REGISTER LOGFILE '+DATA';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE

------

jnrjian

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
RAC 恢复到单实例 ASM 和文件系统方法

ASM到ASM简单一点ASM到RPM安装下的oracle，三点要改 datafile rename，arch改变，redo改动改动太多，后期恢复完 recover完后重建控制文件。1.set archivelog destination to ''/opt/oracle/arch';2.set newname for datafile3.alter database rename file '+DATA/RAC/ONLINELOG/group_6.311.1102011695' t...
复制链接

扫一扫