重庆思庄Oracle、KingBase、PostgreSQL、Redhat认证学习论坛

标题: 11g RAC 使用adg方式迁移库 [打印本页]

作者: mahan 时间: 2025-6-8 11:33
标题: 11g RAC 使用adg方式迁移库
本帖最后由 mahan 于 2025-6-8 11:47 编辑

0 说明
接到应用侧需求工单，需要克隆现有的一套测试库出来做业务测试，数据库是11204双节点RAC集群，源端数据库大小5.1T，数据文件总大小7.3T。
检查了本机数据库主机的环境配置，剩余存储和剩余内存均满足复制需求，因此原本的数据库环境主机上，克隆源RAC数据库得到一个新的实例。源库和目标库将共享相同的物理服务器和IP地址，但使用不同的instance_name、db_name和db_unique_name。

1 环境规划
在进行数据库克隆操作之前，需要对源端（主）和目标端（备）的环境进行详细规划，确保各项配置参数清晰明确，以便后续操作顺利进行。

IP  VIP 操作系统  数据库服务名  instance_name db_name db_unique_name  数据库版本控制文件路径  数据文件路径  归档路径
源端（主） 192.168.56.101 192.168.56.102 192.168.56.103 192.168.56.104 rhel 6.8  rac rac1 rac2 rac rac 11.2.0.4.0  +DATA/rac/controlfile/  +DATA/rac/datafile/ +DATA/rac/tempfile  +FRA/rac/archivelog/
目标端（备）  192.168.56.101 192.168.56.102 192.168.56.103 192.168.56.104 rhel 6.8  rac_lpy rac_lpy1 rac_lpy2 rac_lpy rac_lpy 11.2.0.4.0  +DATA/rac_lpy/controlfile/  +DATA/rac_lpy/datafile/ +DATA/rac_lpy/tempfile  +FRA/rac_lpy/archivelog/
2 源端开启归档
rman duplicate命令需要源数据库处于归档模式，以便在克隆过程中持续应用产生的重做日志，保证目标库的数据一致性。

# 主任意节点
[oracle@r11gn1 ~]$ srvctl stop database -d rac
[oracle@r11gn1 ~]$ srvctl start instance -d rac -i rac1 -o mount

# 主1
SQL> alter database archivelog;
SQL> alter system set log_archive_dest_1='location=+FRA' scope=both sid='*';

SQL> archive log list
Database log mode             Archive Mode
Automatic archival          Enabled
Archive destination          +FRA
Oldest online log sequence    48
Next log sequence to archive 49
Current log sequence          49

# 主1
SQL> alter database open;

# 主2
srvctl start instance -d rac -i rac2
3 源端开启强制日志
确保所有数据文件修改均记录到重做日志，防止克隆过程中因未记录（nologging）的直接路径加载（Direct Path Load）导致数据不一致。

# 主1
SQL> alter database force logging;
SQL> Select LOG_MODE,OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS,GUARD_STATUS,FORCE_LOGGING from v$database;

LOG_MODE    OPEN_MODE          PROTECTION_MODE    DATABASE_ROLE SWITCHOVER_STATUS GUARD_S FOR
------------ -------------------- -------------------- ---------------- -------------------- ------- ---
ARCHIVELOG READ WRITE          MAXIMUM PERFORMANCE  PRIMARY       NOT ALLOWED       NONE YES
4 创建目标端参数文件
基于源库的spfile生成pfile，然后修改关键标识符参数：

SQL> create pfile='/home/oracle/rac_lpy.ora' from spfile;
添加以下参数：

*.db_unique_name='rac_lpy'
定义数据库的唯一名称，必须与源库不同，是区分数据库实例的核心标识。

把rac全部替换为rac_lpy。

修改完成后示例如下：

...
*.cluster_database=TRUE
*.db_name='rac_lpy'
*.db_unique_name='rac_lpy'
...
rac_lpy1.instance_number=1
rac_lpy2.instance_number=2
...
rac_lpy1.thread=1
rac_lpy2.thread=2
...
rac_lpy1.undo_tablespace='UNDOTBS1'
rac_lpy2.undo_tablespace='UNDOTBS2'
...
*.control_files='+DATA/rac_lpy/controlfile/controlfile1.ctl'
...
5 创建备库目录
# audit_file_dest  两个节点都要创建
$ su - oracle
[oracle@r11gn1 ~]$ mkdir -p /u01/app/oracle/admin/rac_lpy/adump

# DATA  主1创建
$ su - grid
[grid@r11gn1 ~]$ asmcmd
ASMCMD> cd data
ASMCMD> mkdir RAC_LPY
ASMCMD> ls
RAC/
RAC_LPY/
adump目录必须创建否则sqlplus / as sysdba登录失败。

6 启动目标端节点1
使用准备好的pfile启动目标库的1节点rac_lpy1，使其运行在 NOMOUNT 状态。这是创建控制文件和进行后续克隆操作的基础。如果能启动成功，则说明参数文件配置没有问题。

# 声明实例名环境变量
[oracle@r11gn1 ~]$ export ORACLE_SID=rac_lpy1
[oracle@r11gn1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jun 3 02:26:34 2025

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

Connected to an idle instance.

# 使用pfile启动到nomount状态
SQL> startup nomount pfile='/home/oracle/rac_lpy.ora';
ORACLE instance started.

Total System Global Area 2137886720 bytes
Fixed Size                2254952 bytes
Variable Size          620758936 bytes
Database Buffers       1509949440 bytes
Redo Buffers             4923392 bytes

# 查看参数
SQL> show parameter name

NAME                               TYPE       VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name             string
db_file_name_convert                string    rac, rac_lpy
db_name                            string    rac_lpy
db_unique_name                      string    rac_lpy
global_names                      boolean    FALSE
instance_name                      string    rac_lpy1
lock_name_space                   string
log_file_name_convert             string    rac, rac_lpy
processor_group_name                string
service_names                      string    rac_lpy
7 创建spfile
将修改好的pfile转换为spfile，并存放在ASM共享存储 (+DATA) 中。这是RAC环境的强制要求，因为所有实例必须能够访问同一个参数文件。

SQL> create spfile='+DATA/rac_lpy/spfilerac_lpy.ora' from pfile='/home/oracle/rac_lpy.ora';
File created.

SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.

[grid@r11gn1 ~]$ asmcmd
ASMCMD> cd data/rac_lpy
ASMCMD> ls
PARAMETERFILE/
spfilerac_lpy.ora
指定共享spfile启动：

[oracle@r11gn1 dbs]$ echo "SPFILE='+DATA/rac_lpy/spfilerac_lpy.ora'" > initrac_lpy1.ora
节点2也指定：

[oracle@r11gn2 dbs]$ echo "SPFILE='+DATA/rac_lpy/spfilerac_lpy.ora'" > initrac_lpy2.ora
启动节点1，看看是否spfile启动：

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 2137886720 bytes
Fixed Size                2254952 bytes
Variable Size          620758936 bytes
Database Buffers       1509949440 bytes
Redo Buffers             4923392 bytes
SQL> show parameter spfile;

NAME                               TYPE       VALUE
------------------------------------ ----------- -------------------------------
spfile                            string    +DATA/rac_lpy/spfilerac_lpy.ora
8 配置静态监听
因为目标库未启动，监听无法正常注册，为了使rman能够登录，要配置静态监听。

把下面这段监听配置添加到$ORACLE_HOME/network/admin/listener.ora文件中：

节点1配置

SID_LIST_LISTENER =
  (SID_LIST =
      (SID_DESC =
      (SID_NAME = rac1)
      (ORACLE_HOME = /u01/app/oracle/product/11.2/db_1)
      )
      (SID_DESC =
      (GLOBAL_DBNAME = rac_lpy)
      (ORACLE_HOME = /u01/app/oracle/product/11.2/db_1)
      (SID_NAME = rac_lpy1)
      )
  )
SID_NAME为备库实例名。GLOBAL_DBNAME为服务名，取什么都可以，需要和tnsnames.ora文件的server_name保持一致。

9 配置tns
节点1配置

RAC_LPY =
  (DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.101)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.102)(PORT = 1521))
(LOAD_BALANCE = off)
(CONNECT_DATA =
   (SERVER = DEDICATED)
   (SERVICE_NAME = rac_lpy)
)
  )
tnsping看看服务名通不通：

tnsping RAC_LPY
10 验证目标端连通性
节点1 reload监听，在生产环境要注意，reload会导致数据库短暂无法连接。

[grid@r11gn1 ~]$ lsnrctl reload
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 03-JUN-2025 02:56:47

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

Starting /u01/app/11.2.0/grid/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /u01/app/11.2.0/grid/network/admin/listener.ora
Log messages written to /u01/app/grid/diag/tnslsnr/r11gn1/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias                   LISTENER
Version                TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date             03-JUN-2025 02:56:47
Uptime                   0 days 0 hr. 0 min. 0 sec
Trace Level             off
Security                ON: Local OS Authentication
SNMP                   OFF
Listener Parameter File /u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File       /u01/app/grid/diag/tnslsnr/r11gn1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
Services Summary...
Service "rac1" has 1 instance(s).
  Instance "rac1", status UNKNOWN, has 1 handler(s) for this service...
Service "rac_lpy" has 1 instance(s).
  Instance "rac_lpy1", status UNKNOWN, has 1 handler(s) for this service...
Service "racdg1" has 1 instance(s).
  Instance "racdg1", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
rac_lpy静态监听已生效。

目标端创建密码文件：

[oracle@r11gn1 dbs]$ cp orapwrac1 orapwrac_lpy1
[oracle@r11gn1 dbs]$ scp orapwrac_lpy1 r11gn2:/u01/app/oracle/product/11.2/db_1/dbs/orapwrac_lpy2
密码文件 (orapw) 存储SYS等特权用户的密码，每个实例需要独立的文件。这里复制源库的只是作为模板，后续克隆时会覆盖密码。

尝试登录：

[oracle@r11gn1 dbs]$ sqlplus sys/oracle@rac_lpy as sysdba
11 创建备库
核心步骤。使用RMAN的 DUPLICATE … FROM ACTIVE DATABASE 命令，通过网络直接从运行的源库 (rac) 克隆数据文件、控制文件等，创建目标库 (rac_lpy)。这是一种高效的“热克隆”方式。

主库rman duplicate到备库：

[oracle@r11gn1 dbs]$ rman target sys/oracle@rac1 auxiliary sys/oracle@rac_lpy

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Jun 3 03:09:50 2025

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

connected to target database: RAC (DBID=2753265927)
connected to auxiliary database: RAC_LPY (not mounted)

RMAN>
run{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate auxiliary channel c3 type disk;
allocate auxiliary channel c4 type disk;
duplicate target database to rac_lpy from active database;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}
备库要先启动到nomount状态。

复制报错：

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 06/03/2025 13:11:20
RMAN-05501: aborting duplication of target database
RMAN-06136: ORACLE error from auxiliary database: ORA-01503: CREATE CONTROLFILE failed
ORA-12720: operation requires database is in EXCLUSIVE mode
duplicate不支持rac to rac，需要在目标端将CLUSTER_DATABASE参数设置为FALSE，然后再做复制。

Rman duplicate fail with RMAN-06136, ORA-01503, ORA-12720, ORA-00494 enqueue [CF]

(Doc ID 1335479.1)

备库修改CLUSTER_DATABASE参数，重启:

SQL> alter system set cluster_database=false scope=spfile;

System altered.

SQL> shutdown immediate;

SQL> startup nomount;

SQL> show parameter cluster_database

NAME                               TYPE       VALUE
------------------------------------ ----------- ------------------------------
cluster_database                   boolean    FALSE
在源端重新执行复制：

[oracle@r11gn1 dbs]$ rman target sys/oracle@rac1 auxiliary sys/oracle@rac_lpy

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Jun 3 03:09:50 2025

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

connected to target database: RAC (DBID=2753265927)
connected to auxiliary database: RAC_LPY (not mounted)

RMAN>
run{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate auxiliary channel c3 type disk;
allocate auxiliary channel c4 type disk;
duplicate target database to rac_lpy from active database;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}
日志显示 Finished Duplicate Db at 则说明复制成功。

将cluster_database还原，并重启：

SQL> alter system set cluster_database=true scope=spfile;

System altered.

SQL> shutdown immediate;

SQL> startup；

SQL> show parameter cluster_database

NAME                               TYPE       VALUE
------------------------------------ ----------- ------------------------------
cluster_database                   boolean    TRUE
cluster_database_instances          integer    2
12 启动节点2
[oracle@r11gn2 ~]$ export ORACLE_SID=rac_lpy2
[oracle@r11gn2 ~]$ se

SQL> startup nomount;

SQL> alter database mount;

Database altered.

SQL> alter database open;

Database altered.
SQL> show parameter name

NAME                               TYPE       VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name             string
db_file_name_convert                string
db_name                            string    RAC_LPY
db_unique_name                      string    RAC_LPY
global_names                      boolean    FALSE
instance_name                      string    rac_lpy2
lock_name_space                   string
log_file_name_convert             string
processor_group_name                string
service_names                      string    RAC_LPY

SQL> select inst_id,db_unique_name,open_mode,switchover_status,database_role from gv$database;

INST_ID DB_UNIQUE_NAME                OPEN_MODE          SWITCHOVER_STATUS DATABASE_ROLE
---------- ------------------------------ -------------------- -------------------- ----------------
      1 RAC_LPY                      READ WRITE          NOT ALLOWED       PRIMARY
      2 RAC_LPY                      READ WRITE          NOT ALLOWED       PRIMARY

SQL> select inst_id,instance_name,host_name,STARTUP_TIME,STATUS,ARCHIVER from gv$instance;

INST_ID INSTANCE_NAME HOST_NAME                                                       STARTUP_T STATUS    ARCHIVE
---------- ---------------- ---------------------------------------------------------------- --------- ------------ -------
      2 rac_lpy2       r11gn2                                                          03-JUN-25 OPEN       STARTED
      1 rac_lpy1       r11gn1                                                          03-JUN-25 OPEN       STARTED
13 数据库注册到集群
使用Oracle集群件 (srvctl) 管理新克隆的RAC数据库 rac_lpy，包括启动、停止、状态查看、资源配置等。

[oracle@r11gn2 ~]$ srvctl add database -d rac_lpy -o $ORACLE_HOME
[oracle@r11gn2 ~]$ srvctl add instance -d rac_lpy -i rac_lpy1 -n r11gn1
[oracle@r11gn2 ~]$ srvctl add instance -d rac_lpy -i rac_lpy2 -n r11gn2
先sqlplus关闭所有节点数据库实例，然后用srvctl启动：

[oracle@r11gn1 ~]$ srvctl start database -d rac_lpy

[oracle@r11gn1 ~]$ crsctl stat res -t
ora.rac_lpy.db
   1       ONLINE  ONLINE    r11gn1                Open
   2       ONLINE  ONLINE    r11gn2                Open
查看数据库配置：

[oracle@r11gn1 dbs]$ srvctl config database -d rac_lpy -a
Database unique name: rac_lpy
Database name:
Oracle home: /u01/app/oracle/product/11.2/db_1
Oracle user: oracle
Spfile:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: rac_lpy
Database instances: rac_lpy1,rac_lpy2
Disk Groups:
Mount point paths:
Services:
Type: RAC
Database is enabled
Database is administrator managed
注册Spfile到集群：

srvctl modify database -d rac_lpy -p '+DATA/rac_lpy/spfilerac_lpy.ora'
注册Disk Groups,：

srvctl modify database -d rac_lpy -a DATA,FRA,OCR_VOTE
注册Services：

[oracle@r11gn1 ~]$ srvctl add service -d rac_lpy -s lpy_srv1 -r rac_lpy1 -a rac_lpy2 -P BASIC -y AUTOMATIC
[oracle@r11gn1 ~]$ srvctl add service -d rac_lpy -s lpy_srv2 -r rac_lpy2 -a rac_lpy1 -P BASIC -y AUTOMATIC

[oracle@r11gn1 ~]$ srvctl start service -d rac_lpy -s lpy_srv1,lpy_srv2
14 总结
通过以上步骤，可完整实现 Oracle RAC 集群的复制，确保新集群在网络、存储与功能层面与源端隔离且正常运行。该方案适用于数据库迁移、开发测试环境搭建等场景，操作时需严格遵循参数配置与环境隔离原则。

欢迎光临重庆思庄Oracle、KingBase、PostgreSQL、Redhat认证学习论坛 (http://bbs.cqsztech.com/)