Oracle RAC迁移到单实例DB

  1. 云栖社区>
  2. 袋鼠云技术团队>
  3. 博客>
  4. 正文

Oracle RAC迁移到单实例DB

持续高温 2019-06-09 17:15:23 浏览6427
展开阅读全文

这篇文章记录Oracle RAC通过RMAN备份如何迁移到单机DB,文章中描述了几个容易出错的地方,如下:
    ①RAC到单机redo文件位置处理
    ②RAC多个UNDO处理
    ③RAC到单机临时表空间处理
下面开始文章正文。

一、备份源库

1、检查数据库大小

SQL> select sum(a.bytes - b.bytes)/1024/ 1024 "sum_used MB"
  2      from (select tablespace_name, sum(bytes) bytes  
  3           from dba_data_files  
  4          group by tablespace_name) a,  
  5        (select tablespace_name, sum(bytes) bytes, max(bytes) largest  
  6           from dba_free_space  
  7          group by tablespace_name) b  
  8       where a.tablespace_name = b.tablespace_name;

2、检查备份磁盘大小

df -h

3、备份数据库

rman target /
run{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
backup incremental level 0 database format '/backup/fulldb_%u_%p_%c.bak';
sql 'alter system archive log current';
backup format '/backup/arc_%u_%p_%c' archivelog all;
backup current controlfile format '/backup/control_%U.bak';
backup spfile format '/backup/spfile_%U.bak';
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}

二、在目标端还原数据库

1、将备份文件传到目标端服务器

    目标端服务器已装完数据库软件,不创建DB,安装软件过程这里就不在叙述了。

2、创建必要目录

mkdir -p /u01/oracle/app/oracle/admin/xxx/adump    //其中xxx是数据库db_name
mkdir -p /u01/oracle/app/oracle/oradata/hisprod
mkdir -p /u01/oracle/app/oracle/oradata/arch

3、修改参数文件

将集群参数全部去除,注意如果目标端服务器配置和源端不一样,需要对应修改内存值

*.audit_file_dest='/u01/oracle/app/oracle/admin/xxx/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/oracle/app/oracle/oradata/xxx/control01.ctl','/u01/oracle/app/oracle/oradata/xxx/control02.ctl'
*.db_block_size=8192
*.db_create_file_dest='/s01/oracle/app/oracle/oradata/xxx'
*.db_domain=''
*.db_name='xxx'
*.deferred_segment_creation=FALSE
*.diagnostic_dest='/s01/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=xxxDB)'
*.memory_target=1932735283
*.nls_territory='CHINA'
*.O7_DICTIONARY_ACCESSIBILITY=TRUE
*.open_cursors=300
*.optimizer_index_caching=80
*.optimizer_index_cost_adj=20
*.processes=500
*.sec_case_sensitive_logon=FALSE
*.undo_tablespace='UNDOTBS1'

修改完成后数据库启动到nomount状态

[oracle@wrtest dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Jun 9 13:56:49 2019

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount pfile='/u01/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/pfile.ora';
ORACLE instance started.

Total System Global Area 1937457152 bytes
Fixed Size                  2254464 bytes
Variable Size            1275070848 bytes
Database Buffers          654311424 bytes
Redo Buffers                5820416 bytes
SQL> 


SQL> create spfile from pfile='/u01/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/pfile.ora';

File created.

SQL> 
SQL> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup nomount; 
ORACLE instance started.

Total System Global Area 1937457152 bytes
Fixed Size                  2254464 bytes
Variable Size            1275070848 bytes
Database Buffers          654311424 bytes
Redo Buffers                5820416 bytes
SQL>

4、恢复控制文件

从备份文件中恢复从之文件,操作如下:

RMAN> restore controlfile from '/software/bak/control_7eu36n34_1_1.bak';

Starting restore at 09-JUN-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=9 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/oracle/app/oracle/oradata/xxx/control01.ctl
output file name=/u01/oracle/app/oracle/oradata/xxx/control02.ctl
Finished restore at 09-JUN-19

RMAN>
SQL> select status from v$instance;

STATUS
------------
STARTED

SQL> alter database mount;

Database altered.

SQL> select status from v$instance;

STATUS
------------
MOUNTED

SQL>

5、设置归档

alter database archivelog;
alter system set log_archive_dest_1='location=/u01/oracle/app/oracle/oradata/arch';

6、还原数据文件

catalog start with '/software/bak';

run {
set newname for datafile  4 to '/u01/oracle/app/oracle/oradata/xxx/users01.dbf';
set newname for datafile  3 to '/u01/oracle/app/oracle/oradata/xxx/undotbs01.dbf';
set newname for datafile  2 to '/u01/oracle/app/oracle/oradata/xxx/sysaux.dbf';
set newname for datafile  1 to '/u01/oracle/app/oracle/oradata/xxx/system.dbf';
set newname for datafile  5 to '/u01/oracle/app/oracle/oradata/xxx/undotbs02.dbf';
set newname for datafile  6 to '/u01/oracle/app/oracle/oradata/xxx/xxx1.dbf';
set newname for datafile  7 to '/u01/oracle/app/oracle/oradata/xxx/xxx2.dbf';
set newname for datafile  8 to '/u01/oracle/app/oracle/oradata/xxx/xxx3.dbf';
set newname for datafile  9 to '/u01/oracle/app/oracle/oradata/xxx/xxx4.dbf';
set newname for tempfile 1  to '/u01/oracle/app/oracle/oradata/xxx/temp01.dbf';     
set newname for tempfile 2  to '/u01/oracle/app/oracle/oradata/xxx/xxx5.dbf';
restore database;
switch datafile all;
switch tempfile all;
}

注:在还原数据文件时,数据库默认的临时表空间在数据文件还原后是会创建数据文件,其他自己手动创建的临时表空间则不会生成,需要手动处理。

7、修改redo日志位置

select GROUP#,STATUS,TYPE,MEMBER  from v$logfile;

alter database rename file '+DATA/xxx/onlinelog/group_1.302.978617245' to '/u01/oracle/app/oracle/oradata/xxx/redo01_1.log';
alter database rename file '+BACKUPDG/xxx/onlinelog/group_1.1215.978617247' to '/u01/oracle/app/oracle/oradata/xxx/redo01_2.log';

alter database rename file '+DATA/xxx/onlinelog/group_2.303.978617247' to '/u01/oracle/app/oracle/oradata/xxx/redo02_1.log';
alter database rename file '+BACKUPDG/xxx/onlinelog/group_2.866.978617247' to '/u01/oracle/app/oracle/oradata/xxx/redo02_2.log';

alter database rename file '+DATA/xxx/onlinelog/group_3.306.978617337' to '/u01/oracle/app/oracle/oradata/xxx/redo03_1.log';
alter database rename file '+BACKUPDG/xxx/onlinelog/group_3.1243.978617337' to '/u01/oracle/app/oracle/oradata/xxx/redo03_2.log';

alter database rename file '+DATA/xxx/onlinelog/group_4.307.978617337' to '/u01/oracle/app/oracle/oradata/xxx/redo04_1.log';
alter database rename file '+BACKUPDG/xxx/onlinelog/group_4.1227.978617337' to '/u01/oracle/app/oracle/oradata/xxx/redo04_2.log';

8、恢复数据库

RMAN> recover database;

Starting recover at 09-JUN-19
using channel ORA_DISK_1

starting media recovery

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=7886
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=9475
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=9476
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=7887
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=9477
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=7888
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=7889
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=9478
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=9479
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=7890
channel ORA_DISK_1: reading from backup piece /software/bak/arc_7pu36obs_1_1
channel ORA_DISK_1: piece handle=/software/bak/arc_7pu36obs_1_1 tag=TAG20190603T193613
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
archived log file name=/u01/oracle/app/oracle/oradata/arch/1_9475_978617245.dbf thread=1 sequence=9475
archived log file name=/u01/oracle/app/oracle/oradata/arch/2_7886_978617245.dbf thread=2 sequence=7886
archived log file name=/u01/oracle/app/oracle/oradata/arch/1_9476_978617245.dbf thread=1 sequence=9476
archived log file name=/u01/oracle/app/oracle/oradata/arch/2_7887_978617245.dbf thread=2 sequence=7887
archived log file name=/u01/oracle/app/oracle/oradata/arch/1_9477_978617245.dbf thread=1 sequence=9477
archived log file name=/u01/oracle/app/oracle/oradata/arch/2_7888_978617245.dbf thread=2 sequence=7888
archived log file name=/u01/oracle/app/oracle/oradata/arch/2_7889_978617245.dbf thread=2 sequence=7889
archived log file name=/u01/oracle/app/oracle/oradata/arch/1_9478_978617245.dbf thread=1 sequence=9478
archived log file name=/u01/oracle/app/oracle/oradata/arch/1_9479_978617245.dbf thread=1 sequence=9479
archived log file name=/u01/oracle/app/oracle/oradata/arch/2_7890_978617245.dbf thread=2 sequence=7890
unable to find archived log
archived log thread=2 sequence=7891
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/09/2019 14:36:05
RMAN-06054: media recovery requesting unknown archived log for thread 2 with sequence 7891 and starting SCN of 1730567738
SQL> select status from v$instance;

STATUS
------------
MOUNTED

SQL> alter database open resetlogs;

Database altered.

SQL>

9、修复临时数据文件

select user,temporary_tablespace from dba_users;
create temporary tablespace tmp1 tempfile '/u01/oracle/app/oracle/oradata/xxx/tmp1.dbf' size 2G autoextend on;
alter user user01 temporary tablespace tmp1;
drop tablespace tmpa including contents and datafiles;

10、删除多余的日志组

select b.member,a.group#,thread#,a.status from v$log a,v$logfile b where a.group#=b.group# order by a.thread#,b.group#;
alter database disable thread 2;
alter database drop logfile group 3;
alter database drop logfile group 4;

11、清理多余undo

select name from v$tablespace where name like '%UNDO%';
drop tablespace undotbs2 including contents and datafiles;

至此,完成从RAC恢复到单机操作!

网友评论

登录后评论
0/500
评论
持续高温
+ 关注
所属云栖号: 袋鼠云技术团队