Oracle 11g RAC 迁移至单机

说明：

数据库迁移几大因素：

1. 停机时间
2. 源端和目标端：操作系统平台、版本、对应的数据库版本
3. 数据量多少
4. 外界因素：存储空间、网络等

RAC为生产环境，基于RMAN备份恢复将生产环境恢复到单机上；单机环境只安装oralce数据库软件，不创建实例。

一、对于RAC环境

1、RAC环境信息

查看DBID:
select dbid from v$database;
查看SCN:
select FIRST_CHANGE#,NEXT_CHANGE#,STATUS from v$log;
查看数据文件、日志文件、临时文件
select group#,member from v$logfile;
select file#,name from v$datafile;
select name from v$tempfile;
创建pfile：
create pfile from spfile;

2、rman备份

注：先备份数据库和归档日志，再备份控制文件，因为rman备份信息存储在控制文件中。
rman备份一个全备FULL BACKUPSET：
rman target /
run{
allocate channel c1 device type disk ;
allocate channel c2 device type disk ;
backup database format '/u01/oracle/backup/db_%U_%T.bak';
sql 'alter system archive log current';
crosscheck archivelog all;
delete noprompt expired archivelog all;
backup archivelog format '/u01/oracle/backup/arch_%U_%T.bak';
backup spfile format '/u01/oracle/backup/spfile_%U_%T.bak';
backup current controlfile format '/u01/oracle/backup/ctl_%U_%T.bak';
release channel c1 ;
release channel c2 ;
delete noprompt obsolete;
crosscheck backup;
delete noprompt expired backup;
}

将pfile 与备份文传到单机上、（归档文件备份无效、 可选择将归档原文件传到单机上或弃用）

二、对于单机环境

1、修改单实例的参数文件

将备份的文件拷贝到单实例的相同目录
手动创建pfile文件，或者从备份的pfile文件进行修改：
db_name=orcl
log_archive_dest_1='location=/arch'
pga_aggregate_target=4G
sga_target=8G
compatible='11.2.0.4.0'
control_files='/u01/app/oracle/oradata/control.ctl'

参数文件参考如下：
vi initorcl.ora
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='NONE'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/orcl/control01.dbf'
*.db_block_size=8192
*.db_create_file_dest='/u01/app/oracle/oradata/'
*.db_create_online_log_dest_1='/u01/app/oracle/oradata/'
*.db_domain=''
*.db_files=2000
*.db_name='orcl'
*.deferred_segment_creation=FALSE
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.enable_ddl_logging=TRUE
*.event='28401 TRACE NAME CONTEXT FOREVER, LEVEL 1'
*.log_archive_dest_1='LOCATION=/arch'
*.log_archive_format='%t_%s_%r.dbf'
*.max_dump_file_size='1G'
*.open_cursors=300
*.pga_aggregate_target=288358400
*.processes=1500
*.remote_login_passwordfile='exclusive'
*.sec_case_sensitive_logon=FALSE
*.sessions=1655
*.sga_max_size=1100m
*.sga_target=1100m

2、创建单机环境使用的目录

--FRA目录
mkdir -p /u01/app/oracle/fast_recovery_area/orcl
--DATAFILE
mkdir -p /u01/app/oracle/oradata/orcl
--adump
mkdir -p /u01/app/oracle/admin/orcl/adump
--配置权限
chown -R oracle.oinstall /u01/app/oracle

3、创建密码文件，注意密码文件的位置（mos文档没有）

[oracle@orc1 dbs]$ orapwd file=orapworcl password=oracle entries=30

4、通过编辑好的初始化参数重启数据库到NOMOUNT

启动单实例到nomount：
[oracle@11g1 dbs]$ sqlplus / as sysdba
 
SQL*Plus: Release 11.2.0.4.0 Production on Fri Dec 12 20:39:56 2014
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> startup nomount pfile='/u01/app/oracle/backup/initorcl.ora';
ORACLE instance started.
Total System Global Area  7975003968 bytes
Fixed Size		    2257472 bytes
Variable Size		  222301632 bytes
Database Buffers	  566231040 bytes
Redo Buffers		    6733824 bytes

用pfile生成spfile文件：
SQL> create spfile from pfile;
SQL> shutdown immediate；
SQL> startup nomount;

5、从备份集恢复控制文件,并启动到mount状态

[oracle@orcl ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Fri Dec 12 20:40:14 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: orcl (not mounted)

RMAN> set DBID=3882088725;
RMAN> restore controlfile from '/u01/oracle/backup/clt_0dpq0ka5_1_1_20141212.bak'; --建议使用
Starting restore at 12-DEC-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=171 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/oradata/control.ctl
Finished restore at 12-DEC-14

RMAN> alter database mount;

6、恢复数据文件

6.1 将备份注册进控制文件里:
RMAN> catalog start with '/u01/oracle/backup/';
RMAN> list backup;
RMAN> list backup of archivelog all;

确认数据文件、联机日志文件、临时文件路径，通过RMAN重命名数据文件和临时文件，进行还原。
6.2 修改数据文件路径(可以进行批量修改)，恢复数据文件：
SQL> set pages 999
SQL> set line 500
SQL> select 'alter database rename file ''' || name || ''' to ' || '''/u01/app/oracle/oradata/orcl/' || 
 substr(name,instr(name, '/', -1)+1,instr(name, '.', -1)-instr(name, '/', -1)-1) || '.dbf''' || ';'
from v$datafile;

RMAN> 
run{
     set newname for datafile 1 to '/u01/app/oracle/data/system01.dbf';
     set newname for datafile 2 to '/u01/app/oracle/data/sysaux01.dbf';
     set newname for datafile 3 to '/u01/app/oracle/data/UNDOTBS1.dbf';
     set newname for datafile 4 to '/u01/app/oracle/data/users01.dbf';
     set newname for datafile 5 to '/u01/app/oracle/data/UNDOTBS2.dbf';
     set newname for datafile 6 to '/u01/app/oracle/data/siebeldata.268.dbf';
     set newname for datafile 7 to '/u01/app/oracle/data/siebelindex.dbf';
     set newname for datafile 8 to '/u01/app/oracle/data/prod_mds.dbf';
     set newname for datafile 9 to '/u01/app/oracle/data/prod_biplatform.271.dbf';
     set newname for datafile 10 to '/u01/app/oracle/data/prd_biplatform.273.dbf';
     set newname for datafile 11 to '/u01/app/oracle/data/prd_mds.dbf';
     set newname for datafile 12 to '/u01/app/oracle/data/siebeldata.dbf';
     restore database;
     switch datafile all;
   }

6.3 恢复数据库:
RMAN> recover database;

# 恢复完成后，将库启动到read only模式，查询一下数据是否正常
SQL> alter database open read only;

6.4 修改联机日志文件的路径：
# 查看日志文件：
SQL> select * from v$logfile;
SQL> select member from v$logfile;
SQL> select 'alter database rename file ''' || member ||'''' || ' to '''|| substr(member,0,instr(member,'/',-1) -1)|| '_ST' || substr(member,instr(member,'/',-1)) ||''';'
from v$logfile;

# 修改联机日志文件的路径：
alter database rename file '+ASMDATA/orcl/onlinelog/group_1.267.781033527' to '/u01/app/oracle/oradata/orcl/redo1_1.log'; 
alter database rename file '+RECOVERY/orcl/onlinelog/group_1.257.781033533' to '/u01/app/oracle/oradata/orcl/redo1_2.log'; 
alter database rename file '+ASMDATA/orcl/onlinelog/group_2.266.781033537' to '/u01/app/oracle/oradata/orcl/redo2_1.log'; 
alter database rename file '+RECOVERY/orcl/onlinelog/group_2.258.781033543' to '/u01/app/oracle/oradata/orcl/redo2_2.log'; 
alter database rename file '+ASMDATA/orcl/onlinelog/group_3.259.781033803' to '/u01/app/oracle/oradata/orcl/redo3_1.log'; 
alter database rename file '+RECOVERY/orcl/onlinelog/group_3.260.781033809' to '/u01/app/oracle/oradata/orcl/redo3_2.log'; 
alter database rename file '+ASMDATA/orcl/onlinelog/group_4.258.781033815' to '/u01/app/oracle/oradata/orcl/redo4_1.log'; 
alter database rename file '+RECOVERY/orcl/onlinelog/group_4.261.781033821' to '/u01/app/oracle/oradata/orcl/redo4_2.log'; 
alter database rename file '+ASMDATA/orcl/onlinelog/group_5.262.781033549' to '/u01/app/oracle/oradata/orcl/redo5_1.log'; 
alter database rename file '+RECOVERY/orcl/onlinelog/group_5.259.781033555' to '/u01/app/oracle/oradata/orcl/redo5_2.log';

6.5 恢复归档日志文件：
注：后续还有日志文件需要恢复，千万别RESETLOGS打开数据库，等迁移恢复完日志文件后再resetlogs打开库，否则数据会不一致。
# 将归档日志拷贝到目标机（scp归档）
cd /u01/app/oracle/archivelog/
ll -lrt
scp 1_6948_917786690.dbf 172.16.36.30:/u01/app/oracle/archivelog/
# 把归档日志注册到rman
RMAN> catalog archivelog '/u01/app/oracle/archivelog/arch_1_72_929977336.arc','/u01/app/oracle/archivelog/arch_1_73_929977336.arc';
或者：
RMAN> catalog start with '/u01/app/oracle/archivelog/';

RMAN> recover database;

6.6 RESETLOGS打开数据库：
SQL> alter database open resetlogs;

三、后续操作

1、查看redo log 信息，并删除无效日志组：

查看redo log 信息，并删除无效日志组（节点2日志）
select THREAD#, STATUS, ENABLED from v$thread;
   THREAD# STATUS ENABLED
---------- ------ --------
         1 OPEN   PUBLIC
         2 CLOSED PUBLIC

select group# from v$log where THREAD#=2;    
    GROUP#
----------
         3
         4
         6
         8
SQL> alter database disable thread 2;
SQL> alter database drop logfile group 3；
SQL> alter database drop logfile group 4；
SQL> alter database drop logfile group 6；
SQL> alter database drop logfile group 8；
SQL> select THREAD#, STATUS, ENABLED from v$thread;
   THREAD# STATUS ENABLED
---------- ------ --------
         1 OPEN   PUBLIC

创建新的redo文件：
alter database add logfile group 5 '/u01/app/oracle/data/redo1.log' size 500M;
alter database add logfile group 6 '/u01/app/oracle/data/redo2.log' size 500M;
alter database add logfile group 7 '/u01/app/oracle/data/redo3.log' size 500M;

2、查看undo表空间，并删除节点2（在此不使用）的undo表空间

SQL> show parameter undo;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1

SQL> select tablespace_name from dba_tablespaces where contents='UNDO';
TABLESPACE_NAME
------------------------------
UNDOTBS1
UNDOTBS2

SQL> drop tablespace UNDOTBS2 including contents and datafiles;

3、查看temp表空间，并重建temp表空间

SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_temp_cr602c5z_.tmp

SQL> select tablespace_name from dba_tablespaces where contents='TEMPORARY';
TABLESPACE_NAME
------------------------------
TEMP

SQL> create temporary tablespace TEMP1 tempfile '/u01/app/oracle/oradata/orcl/temp01.dbf' size 50M;
SQL> alter database default temporary tablespace TEMP1;
SQL> drop tablespace TEMP including contents and datafiles;

4、查看监听状态并配置，编辑tnsname.ora文件。（mos文档没有）

[oracle@orcl ~]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/network/admin
[oracle@rac1 admin]$ vi tnsnames.ora
ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.154)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

5、通过以下命令创建密码文件，注意密码文件的位置（mos文档没有）

[oracle@orc1 dbs]$ orapwd file=orapworcl password=oracle entries=30

6、将新数据库添加到oratab中

[oracle@orcl dbs]$ vi /etc/oratab
orcl:/u01/app/oracle/product/11.2.0/dbhome_1:N