Oracle单实例数据库迁移到Oracle RAC 环境之(2)--实施篇

简介:

系统环境:

操作系统:RedHat EL55

Oracle : Oracle 11.2.0.1.0

集群软件:Oracle GI 11.2.0.1.0

本案例采用的是基于DataGuard的迁移方式

wKiom1N9c1KgHtr6AAGFVMkanCc106.jpg

主库(bjdb):

1、修改初始化参数文件

[oracle@bjsrv dbs]$ cat initcuug.ora 

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

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='/u01/app/oracle/oradata/cuug/control01.ctl','/u01/app/oracle/oradata/cuug/control02.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='cuug'

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

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

*.log_archive_format='arch_%t_%s_%r.log'

*.memory_target=419430400

*.open_cursors=300

*.processes=150

*.undo_tablespace='UNDOTBS1'


DB_UNIQUE_NAME=bjdb

LOG_ARCHIVE_CONFIG='DG_CONFIG=(bjdb,stddb)'

LOG_ARCHIVE_DEST_1='LOCATION=/dsk3/arch_cuug VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=bjdb'    

LOG_ARCHIVE_DEST_2='SERVICE=stddb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stddb'            

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=ENABLE

REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

LOG_ARCHIVE_MAX_PROCESSES=3


DB_FILE_NAME_CONVERT='+dg1/stddb/datafile/','/u01/app/oracle/oradata/cuug/','+dg1/stddb/tempfile/','/u01/app/oracle/oradata/cuug/'        

LOG_FILE_NAME_CONVERT='+dg1/stddb/onlinelog/','/dsk1/oradata/cuug','+rcy1/stddb/onlinelog/','/dsk2/oradata/cuug'       

STANDBY_FILE_MANAGEMENT=AUTO

2、通过新的初始化参数文件启动Instance

16:54:57 SYS@ cuug>create spfile from pfile;

16:55:57 SYS@ cuug>show parameter name

NAME                                 TYPE        VALUE

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

db_file_name_convert                 string      +dg1/stddb/datafile, /u01/app/oracle/or                                                                                     adata/cuug/,+dg1/stddb/tempfile,/u01/app/oracle/oradata/cuug

db_name                              string      cuug

db_unique_name                       string      bjdb

global_names                         boolean     FALSE

instance_name                        string      cuug

log_file_name_convert                string      +dg1/stddb/onlinelog, /dsk1/oradata/cuug

                                                 , +rcy1/stddb/onlinelog, /dsk2/oradata/c

                                                 uug

service_names                        string      bjdb


3、对数据库进行冷备份并建立standby controlfile

数据库在mount状态下,进行冷备:

[oracle@bjsrv admin]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Wed May 21 16:58:25 2014

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

connected to target database: CUUG (DBID=1329392875, not open)

RMAN> backup database format '/dsk3/%d_%s.bak';

生成standby controlfile:

RMAN> backup current controlfile for standby format '/dsk3/std_control01.ctl';


4、启动NFS共享将存储库备份的文件系统共享

[root@bjsrv ~]# cat /etc/exports 

/dsk3  *(rw,async,nohide,no_subtree_check)

[root@bjsrv ~]# exportfs -av

exporting *:/dsk3


5、拷贝实例初始化参数文件和口令文件到RAC主机

[oracle@bjsrv dbs]$ scp orapwcuug node1:$ORACLE_HOME/dbs/orapwstddb1    

[oracle@bjsrv dbs]$ scp orapwcuug node2:$ORACLE_HOME/dbs/orapwstddb2

[oracle@bjsrv dbs]$ scp initcuug.ora node1:~/initstddb1.ora


6、配置listener和tnsnames

listener 采用系统默认listener即可

[oracle@bjsrv admin]$ cat tnsnames.ora 

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

STDDB =

  (DESCRIPTION =

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

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

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = stddb)

    )

  )

BJDB =

  (DESCRIPTION =

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

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = bjdb)

    )

  )

备库(stddb):

1、修改初始化参数文件

[oracle@node1 ~]$ cat initstddb1.ora 

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

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='+dg1/stddb/controlfile/std_control01.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='cuug'

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

*.log_archive_format='arch_%t_%s_%r.log'

*.memory_target=419430400

*.open_cursors=300

*.processes=150

stddb1.instance_number=1

stddb2.instance_number=2

stddb2.thread=2

stddb1.thread=1

stddb1.undo_tablespace='UNDOTBS1'

stddb2.undo_tablespace='UNDOTBS2'

*.cluster_database=true

DB_UNIQUE_NAME=stddb

LOG_ARCHIVE_CONFIG='DG_CONFIG=(bjdb,stddb)'

LOG_ARCHIVE_DEST_1='LOCATION=+rcy1 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stddb'    

LOG_ARCHIVE_DEST_2='SERVICE=bjdb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=bjdb'            

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=ENABLE

REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

LOG_ARCHIVE_MAX_PROCESSES=3


FAL_SERVER=bjdb

DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/cuug/','+dg1/stddb/datafile/','/u01/app/oracle/oradata/cuug/','+dg1/stddb/tempfile'

LOG_FILE_NAME_CONVERT='/dsk1/oradata/cuug','+dg1/stddb/onlinelog','/dsk2/oradata/cuug','+rcy1/stddb/onlinelog'

STANDBY_FILE_MANAGEMENT=AUTO

2、建立相关目录

[oracle@node1 ~]$ mkdir -p /u01/app/oracle/admin/stddb/adump

[oracle@node2 dbs]$ mkdir -p /u01/app/oracle/admin/stddb/adump

3、通过pfile启动Instance到nomount

SQL> startup force nomount pfile='/home/oracle/initstddb1.ora'

ORACLE instance started.

Total System Global Area  418484224 bytes

Fixed Size                  1336932 bytes

Variable Size             260049308 bytes

Database Buffers          150994944 bytes

Redo Buffers                6103040 bytes

SQL> show parameter name

NAME                                 TYPE        VALUE

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

db_file_name_convert                 string      /u01/app/oracle/oradata/cuug/,

                                                  +dg1/stddb/datafile,/u01/app/oracle/oradata/cuug/,

                                                  +dg1/stddb/tempfile

db_name                              string      cuug

db_unique_name                       string      stddb

global_names                         boolean     FALSE

instance_name                        string      stddb1

log_file_name_convert                string      /dsk1/oradata/cuug, +dg1/stddb/onlinelog

                                                 , /dsk2/oradata/cuug, +rcy1/st

                                                 ddb/onlinelog

service_names                        string      stddb

4、创建spfile和pfile 文件

SQL> create spfile='+dg1/stddb/spfilestddb.ora' from pfile='/home/oracle/initstddb1.ora';

File created.

[oracle@node1 dbs]$ cat initstddb1.ora

spfile='+dg1/stddb/spfilestddb.ora'

[oracle@node2 dbs]$ cat initstddb2.ora 

spfile='+dg1/stddb/spfilestddb.ora'

5、配置tnsnames

[oracle@node1 admin]$ cat tnsnames.ora 

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

STDDB =

  (DESCRIPTION =

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

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

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = stddb)

    )

  )

BJDB =

  (DESCRIPTION =

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

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = bjdb)

    )

  )

6、Mount主库主机共享到本地(注意:本地目录需和共享目录相同的路径和名称

[root@node1 ~]# mount -o rw,bg,hard,nointr,rsize=32768,wsize=32768,tcp,actimeo=0,vers=3,timeo=600 bjsrv:/dsk3 /dsk3

7、在备库做数据库恢复(node1)

首先restore controlfile:

[oracle@node1 admin]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Wed May 21 17:31:06 2014

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

connected to target database: CUUG (not mounted)

RMAN> restore standby controlfile from '/dsk3/std_control01.ctl';

Starting restore at 21-MAY-14

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=1 device type=DISK

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

output file name=+DG1/stddb/controlfile/std_control01.ctl

Finished restore at 21-MAY-14

RMAN> alter database mount;

database mounted

released channel: ORA_DISK_1

再restore database:

RMAN> restore database;

Starting restore at 21-MAY-14

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=36 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00001 to +DG1/stddb/datafile/system01.dbf

channel ORA_DISK_1: restoring datafile 00002 to +DG1/stddb/datafile/sysaux01.dbf

channel ORA_DISK_1: restoring datafile 00003 to +DG1/stddb/datafile/undotbs01.dbf

channel ORA_DISK_1: restoring datafile 00004 to +DG1/stddb/datafile/users01.dbf

channel ORA_DISK_1: restoring datafile 00005 to +DG1/stddb/datafile/example01.dbf

channel ORA_DISK_1: reading from backup piece /dsk3/CUUG_1.bak

channel ORA_DISK_1: piece handle=/dsk3/CUUG_1.bak tag=TAG20140521T165858

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:01:35

Finished restore at 21-MAY-14


8、在mount状态下做Database Recovery

SQL> recover managed standby database disconnect from session;

Media recovery complete.


9、查看主库传送的归档日志

SQL> select name from v$archived_log where name is not null;

NAME

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

+RCY1/stddb/archivelog/2014_05_21/thread_1_seq_9.265.848168007

+RCY1/stddb/archivelog/2014_05_21/thread_1_seq_8.264.848168007

+RCY1/stddb/archivelog/2014_05_21/thread_1_seq_11.267.848168007

......


10、打开数据库

SQL> recover managed standby database cancel;

Media recovery complete.

SQL> alter database open;

Database altered.

查看据库角色:

SQL> select name,dbid,database_role,protection_mode,switchover_status from v$database;

NAME            DBID DATABASE_ROLE    PROTECTION_MODE      SWITCHOVER_STATUS

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

CUUG      1329392875 PHYSICAL STANDBY MAXIMUM PERFORMANCE  NOT ALLOWED

查看数据是否和主库同步:

SQL> select count(*) from scott.dept;

  COUNT(*)

----------

         4

SQL> select count(*) from scott.emp1;

  COUNT(*)

----------

        14

在主库查看数据库角色

8:18:00 SYS@ cuug>select name,dbid,database_role,protection_mode,switchover_status from v$database;

NAME            DBID DATABASE_ROLE    PROTECTION_MODE      SWITCHOVER_STATUS

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

CUUG      1329392875 PRIMARY          MAXIMUM PERFORMANCE  TO STANDBY

Elapsed: 00:00:00.04


   @至此,物理的DataGuard构建成功,下一步将进行主备库switchover,将RAC database切换成主库,完成数据迁移。











本文转自 客居天涯 51CTO博客,原文链接:http://blog.51cto.com/tiany/1415148,如需转载请自行联系原作者
目录
相关文章
|
4天前
|
SQL Oracle 关系型数据库
Oracle数据库优化方法
【10月更文挑战第25天】Oracle数据库优化方法
17 7
|
4天前
|
Oracle 关系型数据库 数据库
oracle数据库技巧
【10月更文挑战第25天】oracle数据库技巧
11 6
|
4天前
|
存储 Oracle 关系型数据库
Oracle数据库优化策略
【10月更文挑战第25天】Oracle数据库优化策略
11 5
|
10天前
|
算法 大数据 数据库
云计算与大数据平台的数据库迁移与同步
本文详细介绍了云计算与大数据平台的数据库迁移与同步的核心概念、算法原理、具体操作步骤、数学模型公式、代码实例及未来发展趋势与挑战。涵盖全量与增量迁移、一致性与异步复制等内容,旨在帮助读者全面了解并应对相关技术挑战。
19 3
|
11天前
|
存储 Oracle 关系型数据库
数据库数据恢复—Oracle ASM磁盘组故障数据恢复案例
Oracle数据库数据恢复环境&故障: Oracle ASM磁盘组由4块磁盘组成。Oracle ASM磁盘组掉线 ,ASM实例不能mount。 Oracle数据库故障分析&恢复方案: 数据库数据恢复工程师对组成ASM磁盘组的磁盘进行分析。对ASM元数据进行分析发现ASM存储元数据损坏,导致磁盘组无法挂载。
|
13天前
|
监控 Oracle 关系型数据库
Oracle数据库性能优化
【10月更文挑战第16天】Oracle数据库性能优化是
14 1
|
19天前
|
存储 关系型数据库 MySQL
Mysql(4)—数据库索引
数据库索引是用于提高数据检索效率的数据结构,类似于书籍中的索引。它允许用户快速找到数据,而无需扫描整个表。MySQL中的索引可以显著提升查询速度,使数据库操作更加高效。索引的发展经历了从无索引、简单索引到B-树、哈希索引、位图索引、全文索引等多个阶段。
54 3
Mysql(4)—数据库索引
|
4天前
|
关系型数据库 MySQL Linux
在 CentOS 7 中通过编译源码方式安装 MySQL 数据库的详细步骤,包括准备工作、下载源码、编译安装、配置 MySQL 服务、登录设置等。
本文介绍了在 CentOS 7 中通过编译源码方式安装 MySQL 数据库的详细步骤,包括准备工作、下载源码、编译安装、配置 MySQL 服务、登录设置等。同时,文章还对比了编译源码安装与使用 RPM 包安装的优缺点,帮助读者根据需求选择最合适的方法。通过具体案例,展示了编译源码安装的灵活性和定制性。
29 2
|
7天前
|
存储 关系型数据库 MySQL
MySQL vs. PostgreSQL:选择适合你的开源数据库
在众多开源数据库中,MySQL和PostgreSQL无疑是最受欢迎的两个。它们都有着强大的功能、广泛的社区支持和丰富的生态系统。然而,它们在设计理念、性能特点、功能特性等方面存在着显著的差异。本文将从这三个方面对MySQL和PostgreSQL进行比较,以帮助您选择更适合您需求的开源数据库。
32 4
|
13天前
|
存储 关系型数据库 MySQL
如何在MySQL中创建数据库?
【10月更文挑战第16天】如何在MySQL中创建数据库?

推荐镜像

更多