Oracle RAC 迁移至单机

3/7/2017来源:SQL技巧人气:2981

RAC迁移至单实例利用RMANDUPLICATE-Tim

测试环境: Oracle 11.2.0.3.0 RAC + Redhat 5.5 +ASM 

源库:

       db_name=PRod  SID=prod1/prod2

       ip: 192.168.6.60/62

一. 目标库的准备

1. 安装redhat 5.5 操作系统

2. 安装Oracle 软件, 这里只安装软件,不创建实例。

(如果内存不够就不用再起一个虚拟机了,直接在第二节点操作也行)

二. 源库操作

1. 创建pfile文件

  SQL> create pfile from spfile;

    File created.

2. 将创建的pfile文件传到目标数据库的$ORACLE_HOME/dbs/ 目录下

(这里我们目标库为single,PFILE要改名为initsingle.ora)

3. 备份源库,并将备份文件传到目标库的相同位置。 备份目录必须相同。

先创建备份目录

[[email protected] ~]$ mkdir -p /home/oracle/backup

用RMAN 全备数据库: 

[[email protected] ~]$ rman target /

Recovery Manager: Release 11.2.0.3.0 -

Production on Wed Jul 30 10:06:35 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: PROD (DBID=265283811)

RMAN> show all;

using target database control file instead of recovery catalog

RMAN configuration parameters for database with db_unique_name PROD are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default

CONFIGURE BACKUP OPTIMIZATION OFF; # default

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

CONFIGURE CONTROLFILE AUTOBACKUP ON;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/home/oracle/backup/%F';

CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO COMPRESSED BACKUPSET;

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE MAXSETSIZE TO UNLIMITED; # default

CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE  # default

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_prod1.f'; # default

RMAN>  RUN {

allocate channel c1 type disk;

allocate channel c2 type disk;

BACKUP  FORMAT  '/home/oracle/backup/prod_%U_%T' DATABASE;

sql 'alter system archive log current';

BACKUP FORMAT '/home/oracle/backup/arch_%U_%T' ARCHIVELOG ALL;

release channel c2;

release channel c1;

}

三. 目标库操作

1. 创建目标库single 的口令文件

[[email protected] dbs]$ pwd

/u01/app/oracle/product/11.2.0/db_1/dbs

[[email protected] dbs]$ orapwd file=orapurpdb passWord=oracle

2.创建备份目录

[[email protected] ~]$ mkdir -p /home/oracle/backup

3.1 如果是另起一台虚拟机,需要配置监听,这里我们直接在rh2操作,就不用配置监听了

3.2 配置TNS,添加如下内容

sourceprod =

(DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.6.60)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = prod)

      (INSTANCE_NAME = prod1)

    )

)

4. 修改参数文件

源参数文件:

prod1.__db_cache_size=218103808

prod2.__db_cache_size=218103808

prod1.__java_pool_size=4194304

prod2.__java_pool_size=4194304

prod1.__large_pool_size=4194304

prod2.__large_pool_size=4194304

prod1.__pga_aggregate_target=142606336

prod2.__pga_aggregate_target=142606336

prod1.__sga_target=423624704

prod2.__sga_target=423624704

prod1.__shared_io_pool_size=0

prod2.__shared_io_pool_size=0

prod1.__shared_pool_size=188743680

prod2.__shared_pool_size=188743680

prod1.__streams_pool_size=0

prod2.__streams_pool_size=0

*.audit_file_dest='/u01/app/oracle/admin/prod/adump'

*.audit_trail='db'

*.cluster_database=true

*.compatible='11.2.0.0.0'

*.control_files='+ASM_DATA/prod/controlfile/current.256.853445481','+ASM_RCY/prod/controlfile/current.256.8

53445485'

*.db_block_size=8192

*.db_create_file_dest='+ASM_DATA'

*.db_domain=''

*.db_name='prod'

*.db_recovery_file_dest='+ASM_RCY'

*.db_recovery_file_dest_size=6005194752   

*.db_recovery_file_dest_size=6005194752

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=prodXDB)'

prod1.instance_number=1

prod2.instance_number=2

*.log_archive_dest_1='LOCATION=+ASM_RCY'

*.log_archive_format='%t_%s_%r.dbf'

*.open_cursors=300

*.pga_aggregate_target=140509184

*.processes=150

*.remote_listener='scan-ip:1521'

*.remote_login_passwordfile='exclusive'

*.sga_target=421527552

prod2.thread=2

prod1.thread=1

prod2.undo_tablespace='UNDOTBS2'

prod1.undo_tablespace='UNDOTBS1'

查看源库文件位置:

SQL> select file#,name from v$datafile;

     FILE# NAME

1 +ASM_DATA/prod/datafile/system.259.853445501

2 +ASM_DATA/prod/datafile/sysaux.260.853445535

3 +ASM_DATA/prod/datafile/undotbs1.261.853445563

4 +ASM_DATA/prod/datafile/undotbs2.263.853445599

5 +ASM_DATA/prod/datafile/users.264.853445607

----------

--------------------------------------------------

SQL> select file#,name from v$tempfile;

     FILE# NAME

1 +ASM_DATA/prod/tempfile/temp.262.853445571

----------

--------------------------------------------------

SQL> select member from v$logfile;

MEMBER

--------------------------------------------------------------------------------

+ASM_DATA/prod/onlinelog/group_1.257.853445487

+ASM_RCY/prod/onlinelog/group_1.257.853445491

+ASM_DATA/prod/onlinelog/group_2.258.853445493

+ASM_RCY/prod/onlinelog/group_2.258.853445497

+ASM_DATA/prod/onlinelog/group_3.265.853449629

+ASM_RCY/prod/onlinelog/group_3.259.853449633

+ASM_DATA/prod/onlinelog/group_4.266.853449637

+ASM_RCY/prod/onlinelog/group_4.260.853449641

改后目标参数文件:

[[email protected] dbs]$ vi initsingle.ora 

*.audit_file_dest='/u01/app/oracle/admin/single/adump'

*.audit_trail='db'

*.cluster_database=false

*.compatible='11.2.0.0.0'

*.control_files='/soft/single/controlfile/control01.ctl','/soft/single/controlfile/control02.ctl'

*.db_block_size=8192

*.db_create_file_dest='/soft/single/oradata/'

*.db_domain=''

*.db_name='single'

*.db_recovery_file_dest='/soft/single/Flash_recover_area/'

*.db_recovery_file_dest_size=2147483648

*.diagnostic_dest='/u01/app/oracle'

*.log_archive_dest_1='LOCATION=/soft/single/arch/'

*.log_archive_format='%t_%s_%r.dbf

'*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='exclusive'

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.log_file_name_convert=('+ASM_DATA/prod/onlinelog','/soft/single/oradata')

*.log_file_name_convert=('+ASM_RCY/prod/onlinelog','/soft/single/oradata')

*.db_file_name_convert=('+ASM_DATA/prod/datafile','/soft/single/oradata')

*.db_file_name_convert=('+ASM_DATA/prod/tempfile','/soft/single/oradata')

因为RAC的目录结构和我们的单实例不一样,所以我们这里进行转换一下。 这里面要根据自己的情况来决定。

5. 用SCP将源库的备份copy到目标库

6. 将目标库启动到nomount 状态,并进行duplicate

6.1 启动目标库到nomount状态

[[email protected] dbs]$export ORACLE_SID=single

[[email protected] dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jul 30 10:21:42 2014Copyright (c) 1982, 2011, Oracle.  All rights reserved.Connected to an idle instance.SQL> startup nomountORACLE instance started.

Total System Global Area  242208768 bytesFixed Size    2227176 bytesVariable Size  184550424 bytesDatabase Buffers   50331648 bytesRedo Buffers    5099520 bytes

6.2 进行duplicate

[[email protected] dbs]$ rman target sys/[email protected] auxiliary /

Recovery Manager: Release 11.2.0.3.0 -Production on Wed Jul 30 10:23:29 2014Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.connected to target database: PROD (DBID=265283811)connected to auxiliary database: SINGLE (not mounted)

RMAN> duplicate target database to single;

一条命令,剩下的活RMAN全干了

四. 目标库的其他扫尾操作

4.1 清除多余的undo文件查看UNDO 信息:

SQL> select name from v$tablespace where name like 'UNDO%';NAME

NAME                             -------------------------------------------------------------------------

 UNDOTBS1

 UNDOTBS2

 

因为我们使用的是UNDOTBS1,在pfile里设置的,所以把UNDOTBS2删除掉。

SQL> show parameter undo_tablespace;

NAME                                 TYPE        VALUE

-----------------------------------------------------------------------------undo_tablespace                      string      UNDOTBS1

SQL> drop tablespace undotbs2 including contents and datafiles;Tablespace dropped.

SQL> select name from v$tablespace where name like 'UNDO%';NAME

------------------------------

UNDOTBS1

SQL>

4.2 清除未使用线程的redo日志组

默认情况下,RAC 环境下,每个实例都有2个redo。 在单实例下,就没有必要了。 我们删除点线程2的redo 信息。SQL> select thread#,status,enabled from v$thread;   THREAD# STATUS ENABLED------------------------         1 OPEN   PUBLIC         2 CLOSED PRIVATE

SQL>  select group#,thread#,archived,status from v$log;

    GROUP#    THREAD# ARC STATUS

---------------------------------------         

1          1 YES UNUSED         

2          1 NO  CURRENT       

 3          2 NO  CURRENT         

4          2 YES UNUSED

SQL> alter database disable thread 2;

Database altered.

SQL> alter database clear unarchived logfile group 3;

Database altered.

SQL> alter database drop logfile group 3;

Database altered.

SQL> alter database drop logfile group 4;

Database altered.

SQL> select group#,thread#,archived,status from v$log;    GROUP#    THREAD# ARC STATUS

---------------------------------------         

1          1 YES INACTIVE        

 2          1 NO  CURRENT

现在就剩2个了。 一般的单实例是3个online redo。 我们再添加一组。

SQL> alter database add logfile group 3 ('/soft/single/oradata/redo03.log') size 50m;

Database altered.

SQL> select group#,thread#,archived,status from v$log;    GROUP#    THREAD# ARC STATUS

---------------------------------------        

 1          1 YES INACTIVE       

  2          1 NO  CURRENT        

 3          1 YES UNUSED

   4.3 重建临时表空间,并删除原来的数据文件SQL> select file#,name from v$tempfile;     FILE# NAME

-------------------------------------------------------------------------------     

    1 /soft/single/oradata/temp01.dbf