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


系统环境:

操作系统:RedHat EL55

Oracle : Oracle 11.2.0.1.0

集群软件:Oracle GI 11.2.0.1.0

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

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

主库(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切换成主库,完成数据迁移。