oracle 11.2.0.4 rac dg配置

原创 Leo 2022-11-28

424

文档课题：oracle 11.2.0.4 rac dg配置.

1、环境介绍

2、主节点建库
主节点1创建heal测试实例.
[oracle@hisdb1 ~]$ dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbName heal -sid heal -sysPassword oracle_4U -systemPassword oracle_4U -asmsnmpPassword oracle_4U -datafileDestination 'data/' -redoLogFileSize 50 -recoveryAreaDestination 'fra/' -storageType ASM -responseFile NO_VALUE -characterSet ZHS16GBK -nationalCharacterSet AL16UTF16 -continueOnNonFatalErrors false -disableSecurityConfiguration ALL -diskGroupName 'data' -emConfiguration NONE -listeners LISTENER -automaticMemoryManagement false -totalMemory 1024 -nodeinfo hisdb1,hisdb2 -databaseType OLTP
Copying database files
1% complete
3% complete
9% complete
15% complete
21% complete
27% complete
30% complete
Creating and starting Oracle instance
32% complete
36% complete
40% complete
44% complete
45% complete
48% complete
50% complete
Creating cluster database views
52% complete
70% complete
Completing Database Creation
73% complete
76% complete
85% complete
94% complete
100% complete
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/heal/heal.log" for further details.
[grid@hisdb1 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
NAME           TARGET STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
               ONLINE ONLINE       hisdb1
               ONLINE ONLINE       hisdb2
ora.FRA.dg
               ONLINE ONLINE       hisdb1
               ONLINE ONLINE       hisdb2
ora.LISTENER.lsnr
               ONLINE ONLINE       hisdb1
               ONLINE ONLINE       hisdb2
ora.OCRBK.dg
               ONLINE ONLINE       hisdb1
               ONLINE ONLINE       hisdb2
ora.asm
               ONLINE ONLINE       hisdb1                   Started
               ONLINE ONLINE       hisdb2                   Started
ora.gsd
               OFFLINE OFFLINE      hisdb1
               OFFLINE OFFLINE      hisdb2
ora.net1.network
               ONLINE ONLINE       hisdb1
               ONLINE ONLINE       hisdb2
ora.ons
               ONLINE ONLINE       hisdb1
               ONLINE ONLINE       hisdb2
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE ONLINE       hisdb1
ora.cvu
      1        ONLINE ONLINE       hisdb1
ora.heal.db
      1        ONLINE ONLINE       hisdb1                   Open
      2        ONLINE ONLINE       hisdb2                   Open
ora.hisdb1.vip
      1        ONLINE ONLINE       hisdb1
ora.hisdb2.vip
      1       ONLINE ONLINE       hisdb2
ora.oc4j
      1        ONLINE ONLINE       hisdb1
ora.orcl.db
      1        OFFLINE OFFLINE                               Instance Shutdown
      2        OFFLINE OFFLINE                               Instance Shutdown
ora.scan1.vip
      1        ONLINE ONLINE       hisdb1
说明：实例heal成功创建.
3、前期准备
3.1、主库准备
3.1.1、归档及强制记录日志
a、force logging + archivelog
b、创建新用户leo用作测试
[oracle@hisdb1 ~]$ . oraenv
ORACLE_SID = [orcl1] ? heal1
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@hisdb1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sat Nov 26 12:28:20 2022

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> create user leo identified by leo;

User created.

SQL> grant dba to leo;

Grant succeeded.

SQL> select name,open_mode,log_mode,force_logging,database_role,switchover_status from gv$database;

NAME      OPEN_MODE            LOG_MODE     FOR DATABASE_ROLE    SWITCHOVER_STATUS
--------- -------------------- ------------ --- ---------------- --------------------
HEAL      READ WRITE           NOARCHIVELOG NO PRIMARY          NOT ALLOWED
HEAL      READ WRITE           NOARCHIVELOG NO PRIMARY          NOT ALLOWED

SQL> alter database force logging;

Database altered.

SQL> select name,open_mode,log_mode,force_logging,database_role,switchover_status from gv$database;

NAME      OPEN_MODE            LOG_MODE     FOR DATABASE_ROLE    SWITCHOVER_STATUS
--------- -------------------- ------------ --- ---------------- --------------------
HEAL      READ WRITE           NOARCHIVELOG YES PRIMARY          NOT ALLOWED
HEAL      READ WRITE           NOARCHIVELOG YES PRIMARY          NOT ALLOWED

SQL> archive log list;
Database log mode             No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     4
Current log sequence           5

SQL> alter system set log_archive_dest_1='location=+fra' scope=spfile sid='heal1';

System altered.

SQL> alter system set log_archive_dest_1='location=+fra' scope=spfile sid='heal2';

System altered.

SQL> host srvctl stop database -d heal -o immediate;

SQL> host srvctl status database -d heal
Instance heal1 is not running on node hisdb1
Instance heal2 is not running on node hisdb2

SQL> host srvctl start database -d heal -o mount

SQL> conn / as sysdba

Connected.

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            +FRA
Oldest online log sequence     4
Current log sequence           5

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

SQL> alter database archivelog;

Database altered.

SQL> select name,open_mode,log_mode,force_logging,database_role,switchover_status from gv$database;

NAME      OPEN_MODE            LOG_MODE     FOR DATABASE_ROLE    SWITCHOVER_STATUS
--------- -------------------- ------------ --- ---------------- --------------------
HEAL      MOUNTED              ARCHIVELOG   YES PRIMARY          NOT ALLOWED
HEAL      MOUNTED              ARCHIVELOG   YES PRIMARY          NOT ALLOWED

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

SQL> alter database open;

Database altered.

SQL> select name,open_mode,log_mode,force_logging,database_role,switchover_status from gv$database;

NAME      OPEN_MODE            LOG_MODE     FOR DATABASE_ROLE    SWITCHOVER_STATUS
--------- -------------------- ------------ --- ---------------- --------------------
HEAL      READ WRITE           ARCHIVELOG   YES PRIMARY          NOT ALLOWED
HEAL      MOUNTED              ARCHIVELOG   YES PRIMARY          NOT ALLOWED
--主库open节点2的heal实例.
[oracle@hisdb2 ~]$ . oraenv
ORACLE_SID = [orcl2] ? heal2
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@hisdb2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sat Nov 26 12:41:05 2022

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> alter database open;

Database altered.

SQL> set line 9999
SQL> select name,open_mode,log_mode,force_logging,database_role,switchover_status from gv$database;

NAME      OPEN_MODE            LOG_MODE     FOR DATABASE_ROLE    SWITCHOVER_STATUS
--------- -------------------- ------------ --- ---------------- --------------------
HEAL      READ WRITE           ARCHIVELOG   YES PRIMARY          NOT ALLOWED
HEAL      READ WRITE           ARCHIVELOG   YES PRIMARY          NOT ALLOWED
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            +FRA
Oldest online log sequence     1
Next log sequence to archive   1
Current log sequence           1
3.1.2、standby redo log
主库添加standby redo log.
作用：
standby redo log与主库接收到的重做日志相对应，备库调用RFS进程将从主库接收到的重做日志按顺序写入到standby logfile，主库创建standby logfile旨在主备切换后备用.
创建原则：
a、确保standby redo log大小与主库online redo log的大小一致.
b、若主库为单实例：standby redo log组数=主库日志组总数+1.
c、若主库为rac：standby redo log组数=(每线程的日志组数+1)*最大线程数.
d、不建议复用standby redo log，避免增加额外的I/O以及延缓重做传输.
示例语句，单实例:
alter database add standby logfile group 4 ('/u01/app/oracle/oradata/oralg/standby_redo04.log') size 50m;
alter database add standby logfile group 5 ('/u01/app/oracle/oradata/oralg/standby_redo05.log') size 50m;
alter database add standby logfile group 6 ('/u01/app/oracle/oradata/oralg/standby_redo06.log') size 50m;
alter database add standby logfile group 7 ('/u01/app/oracle/oradata/oralg/standby_redo07.log') size 50m;
集群：
alter database add standby logfile thread 1 group 5 size 50M ,group 6 size 50M ,group 7 size 50M ;
alter database add standby logfile thread 2 group 8 size 50M ,group 9 size 50M ,group 10 size 50M;
实际操作，主库节点1执行：
SQL> select * from v$standby_log;

no rows selected

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

    GROUP#    THREAD#    MEMBERS
---------- ---------- ----------
         1          1          2
         2          1          2
         3          2          2
         4          2         2

SQL> col member for a60
SQL> select group#,status,type,member from v$logfile

    GROUP# STATUS TYPE    MEMBER
---------- ------- ------- ------------------------------------------------------------
         2         ONLINE +DATA/heal/onlinelog/group_2.289.1121775817
         2         ONLINE +DATA/heal/onlinelog/group_2.290.1121775817
         1         ONLINE +DATA/heal/onlinelog/group_1.287.1121775817
         1         ONLINE +DATA/heal/onlinelog/group_1.288.1121775817
         3         ONLINE +DATA/heal/onlinelog/group_3.293.1121775879
         3         ONLINE +DATA/heal/onlinelog/group_3.294.1121775879
         4         ONLINE +DATA/heal/onlinelog/group_4.295.1121775879
         4         ONLINE +DATA/heal/onlinelog/group_4.296.1121775879

8 rows selected.

SQL> alter database add standby logfile thread 1 group 5 size 50m,group 6 size 50m,group 7 size 50m;

Database altered.

SQL> alter database add standby logfile thread 2 group 8 size 50m,group 9 size 50m,group 10 size 50m;

Database altered.

SQL> col dbid for a15
SQL> select group#,dbid,thread#,sequence#,bytes/1024/1024,blocksize,used,archived,status from v$standby_log

    GROUP# DBID               THREAD# SEQUENCE# BYTES/1024/1024 BLOCKSIZE       USED ARC STATUS
---------- --------------- ---------- ---------- --------------- ---------- ---------- --- ----------
         5 UNASSIGNED               1          0              50        512          0 YES UNASSIGNED
         6 UNASSIGNED               1          0              50        512          0 YES UNASSIGNED
         7 UNASSIGNED               1          0              50        512          0 YES UNASSIGNED
         8 UNASSIGNED               2          0              50        512          0 YES UNASSIGNED
         9 UNASSIGNED               2          0              50        512          0 YES UNASSIGNED
        10 UNASSIGNED               2          0              50        512          0 YES UNASSIGNED

6 rows selected.

SQL> select group#,status,type,member from v$logfile;

    GROUP# STATUS TYPE    MEMBER
---------- ------- ------- ------------------------------------------------------------
         2         ONLINE +DATA/heal/onlinelog/group_2.289.1121775817
         2         ONLINE +DATA/heal/onlinelog/group_2.290.1121775817
         1         ONLINE +DATA/heal/onlinelog/group_1.287.1121775817
         1         ONLINE +DATA/heal/onlinelog/group_1.288.1121775817
         3         ONLINE +DATA/heal/onlinelog/group_3.293.1121775879
         3         ONLINE +DATA/heal/onlinelog/group_3.294.1121775879
         4         ONLINE +DATA/heal/onlinelog/group_4.295.1121775879
         4         ONLINE +DATA/heal/onlinelog/group_4.296.1121775879
         5         STANDBY +DATA/heal/onlinelog/group_5.298.1121777699
         5         STANDBY +DATA/heal/onlinelog/group_5.299.1121777699
         6         STANDBY +DATA/heal/onlinelog/group_6.300.1121777699

    GROUP# STATUS TYPE    MEMBER
---------- ------- ------- ------------------------------------------------------------
         6         STANDBY +DATA/heal/onlinelog/group_6.301.1121777699
         7         STANDBY +DATA/heal/onlinelog/group_7.302.1121777699
         7         STANDBY +DATA/heal/onlinelog/group_7.303.1121777699
         8         STANDBY +DATA/heal/onlinelog/group_8.304.1121778043
         8         STANDBY +DATA/heal/onlinelog/group_8.305.1121778043
         9         STANDBY +DATA/heal/onlinelog/group_9.306.1121778043
         9         STANDBY +DATA/heal/onlinelog/group_9.307.1121778043
        10         STANDBY +DATA/heal/onlinelog/group_10.308.1121778043
        10         STANDBY +DATA/heal/onlinelog/group_10.309.1121778043

20 rows selected.

SQL> show parameter name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name               string
db_file_name_convert                 string
db_name                              string      heal
db_unique_name                       string      heal
global_names                         boolean     FALSE
instance_name                        string      heal1
lock_name_space                      string
log_file_name_convert                string
processor_group_name                 string
service_names                       string      heal
3.1.3、参数文件
使用如下命令修改主库参数(说明：当下主库是使用spfile启动的实例)
注意：
a、log_archive_config='dg_config=( db_unique_name, db_unique_name)'，主库与备库端采用相同设置
b、log_archive_dest_n='service=……' ，service: 用于指定备库TNSNAMES描述符
c、db_file_name_convert、log_file_name_convert 参数值为转换路径，可直接写db_unique_name.
若使用ASM，可设置为*.db_file_name_convert=('+DATA','+RECOVERY')
d、fal_server、fal_client为TNSNAMES描述符
实际操作，主库节点1执行：
SQL> alter system set log_archive_config='dg_config=(heal,healdg)' sid='*';

System altered.

SQL> alter system set log_archive_dest_1='location=+fra valid_for=(all_logfiles,all_roles) db_unique_name=heal' sid='*';

System altered.

SQL> alter system set log_archive_dest_2='service=healdg lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=healdg' sid='*';

System altered.

SQL> alter system set log_archive_dest_state_1=enable sid='*';

System altered.

SQL> alter system set log_archive_dest_state_2=enable sid='*';

System altered.

SQL> alter system set standby_file_management='AUTO' sid='*';

System altered.

SQL> show parameter standby

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest                 string      ?/dbs/arch
standby_file_management              string      AUTO

说明：当主备切换时，需执行如下语句.
alter system set db_file_name_convert='+data/healdg/datafile/','+data/heal/datafile/','+data/healdg/tempfile/','+data/heal/tempfile/' sid='*' scope=spfile;
alter system set log_file_name_convert='+data/healdg/tempfile/','+data/heal/tempfile/','+data/healdg/onlinelog/','+data/heal/onlinelog/','+data/healdg/controlfile/','+data/heal/controlfile/' sid='*' scope=spfile;
alter system set standby_file_management='AUTO' sid='*';
alter system set fal_server='healdg' sid='*';
alter system set fal_client='heal' sid='*';
3.1.4、监听文件
3.1.4.1、主库监听
配置主库和备库的监听文件，整个DG的redo传输服务都依赖于Oracle Net，因此需要为主备库配置监听，且需要配置静态监听.配置方法可用netmgr，netca，以及直接编辑listener.ora文件.
主库节点1监听文件添加如下内容：
LISTENER =
(DESCRIPTION_LIST =
     (DESCRIPTION =
       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.210)(PORT = 11521))
     )
)

SID_LIST_LISTENER =
(SID_LIST =
   (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (PROGRAM = extproc)
   )
(SID_DESC =
    (GLOBAL_DBNAME = heal)
    (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
    (SID_NAME= heal1)
)
)
主库节点2监听文件添加如下内容：
LISTENER =
(DESCRIPTION_LIST =
     (DESCRIPTION =
       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.211)(PORT = 11521))
     )
)

SID_LIST_LISTENER =
(SID_LIST =
   (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (PROGRAM = extproc)
   )
(SID_DESC =
    (GLOBAL_DBNAME = heal)
    (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
    (SID_NAME= heal2)
)
)
--重启主库监听.
[grid@hisdb2 admin]$ srvctl stop listener -l listener
[grid@hisdb2 admin]$ srvctl start listener -l listener
[grid@hisdb2 admin]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 26-NOV-2022 14:06:49

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.133.211)(PORT=11521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                26-NOV-2022 14:04:49
Uptime                    0 days 0 hr. 2 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/hisdb2/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.133.211)(PORT=11521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.133.213)(PORT=11521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "heal" has 2 instance(s).
Instance "heal2", status UNKNOWN, has 1 handler(s) for this service...
Instance "heal2", status READY, has 1 handler(s) for this service...
Service "healXDB" has 1 instance(s).
Instance "heal2", status READY, has 1 handler(s) for this service...
The command completed successfully
注意：rac下是在grid用户下修改监听文件.
3.1.4.2、备库监听
先搭建rac dg环境，可参照如下博客，此处不再赘述.
https://blog.51cto.com/u_12991611/5376800
注意：
a、备库只安装gi及数据库软件，不安装实例.
b、备库两个节点监听文件配置参考主库.
备库节点1监听文件添加如下内容：
LISTENER =
(DESCRIPTION_LIST =
     (DESCRIPTION =
       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.220)(PORT = 1521))
     )
)

SID_LIST_LISTENER =
(SID_LIST =
   (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (PROGRAM = extproc)
   )
(SID_DESC =
    (GLOBAL_DBNAME = healdg)
    (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
    (SID_NAME= healdg1)
)
)
注意：此处global_dbname需为healdg.
备库节点2监听文件参照如下内容：
[grid@hisdb4 admin]$ cat listener.ora
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1))))                # line added by Agent
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))))            # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON        # line added by Agent

LISTENER =
(DESCRIPTION_LIST =
     (DESCRIPTION =
       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.221)(PORT = 1521))
     )
)

SID_LIST_LISTENER =
(SID_LIST =
   (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (PROGRAM = extproc)
   )
(SID_DESC =
    (GLOBAL_DBNAME = healdg)
    (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
    (SID_NAME= healdg2)
)
)
说明：
a、此处global_dbname需为healdg.
b、备库节点2参照备库节点1添加，同时额外添加过LISTENER_SCAN1的信息，原本没有scan listener信息.
--备库重启监听.
[grid@hisdb4 admin]$ srvctl stop listener -l listener
[grid@hisdb4 admin]$ srvctl start listener -l listener
[grid@hisdb3 admin]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 26-NOV-2022 20:25:20

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.133.220)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                26-NOV-2022 20:20:35
Uptime                    0 days 0 hr. 4 min. 45 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/hisdb3/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.133.220)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "heal" has 1 instance(s).
Instance "healdg1", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
3.1.5、tnsnames.ora
配置主备库tnsnames.ora文件.注意：tnsnames.ora文件在oracle用户下，主库两节点加入如下内容：
HEAL =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.210)(PORT = 11521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.211)(PORT = 11521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = heal)
    )
)

HEALDG =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.220)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.221)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = healdg)
    )
)
--将tnsnames.ora传输到备库两节点对应目录.
[oracle@hisdb1 admin]$ scp tnsnames.ora oracle@192.168.133.220:/u01/app/oracle/product/11.2.0/db_1/network/admin
[oracle@hisdb1 admin]$ scp tnsnames.ora oracle@192.168.133.221:/u01/app/oracle/product/11.2.0/db_1/network/admin
3.1.6、密码文件
配置主备库密码文件，要求主备库sys密码相同，此处直接复制主库节点1的密码文件到其它3个节点并修改名称.注意：密码文件名为orapw+ORACLE_SID.
[oracle@hisdb1 dbs]$ pwd
/u01/app/oracle/product/11.2.0/db_1/dbs
[oracle@hisdb1 dbs]$ cp orapwheal1 orapwheal2
[oracle@hisdb1 dbs]$ cp orapwheal1 orapwhealdg1
[oracle@hisdb1 dbs]$ cp orapwheal1 orapwhealdg2
[oracle@hisdb1 dbs]$ scp orapwheal2 oracle@192.168.133.211:/u01/app/oracle/product/11.2.0/db_1/dbs
[oracle@hisdb1 dbs]$ scp orapwhealdg1 oracle@192.168.133.220:/u01/app/oracle/product/11.2.0/db_1/dbs
[oracle@hisdb1 dbs]$ scp orapwhealdg2 oracle@192.168.133.221:/u01/app/oracle/product/11.2.0/db_1/dbs
3.2、备库操作
3.2.1、修改oratab
在/etc/oratab添加条目.
备库节点1
healdg1:/u01/app/oracle/product/11.2.0/db_1:N
备库节点2
healdg2:/u01/app/oracle/product/11.2.0/db_1:N
3.2.2、启动到nomount
配置备库pfile文件，并启动到nomount状态.选择备库节点1作为实施节点.
[oracle@hisdb3 admin]$ cd /u01/app/oracle/product/11.2.0/db_1/dbs
[oracle@hisdb3 dbs]$ ll
total 8
-rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-r----- 1 oracle oinstall 1536 Nov 26 20:48 orapwhealdg1

[oracle@hisdb3 dbs]$ echo "db_name=healdg" > inithealdg1.ora
[oracle@hisdb3 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Nov 27 09:51:50 2022

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

Connected to an idle instance.

SQL> startup nomount pfile=?/dbs/inithealdg1.ora
ORACLE instance started.

Total System Global Area 254738432 bytes
Fixed Size                  2252176 bytes
Variable Size             197132912 bytes
Database Buffers           50331648 bytes
Redo Buffers                5021696 bytes
3.2.3、互通测试
主备库4个节点分别测试.
[oracle@hisdb1 admin]$ tnsping heal

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 26-NOV-2022 20:41:26

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

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.210)(PORT = 11521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.211)(PORT = 11521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = heal)))
OK (0 msec)
[oracle@hisdb1 admin]$ tnsping healdg

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 26-NOV-2022 20:41:32

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

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.220)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.221)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = healdg)))
OK (0 msec)
说明：此处仅用主库节点1示例.
3.2.4、连接验证
主库验证.
[oracle@hisdb1 admin]$ sqlplus sys/oracle_4U@healdg as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Nov 27 13:56:12 2022

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> show parameter db_unique_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      healdg
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
[oracle@hisdb1 admin]$ sqlplus sys/oracle_4U@heal as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Nov 27 13:57:42 2022

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> show parameter db_unique_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      heal
备库验证
[oracle@hisdb3 ~]$ sqlplus sys/oracle_4U@healdg as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Nov 27 12:19:22 2022

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> set line 200
SQL> show parameter db_unique_name

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
db_unique_name                       string                 healdg
[oracle@hisdb3 ~]$ sqlplus sys/oracle_4U@heal as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Nov 27 13:59:14 2022

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> show parameter db_unique_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      heal
3.2.5、新建目录
配置备库路径，11g一般创建如下路径，若是asm只需要创建/u01/app/oracle/admin/healdg/adump
[oracle@hisdb3 dbs]$ mkdir -p /u01/app/oracle/admin/healdg/adump
[oracle@hisdb4 dbs]$ mkdir -p /u01/app/oracle/admin/healdg/adump
--若不创建该目录报以下告警
RMAN-04014: startup failed: ORA-09925: Unable to create audit trail file
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 9925
说明：此次测试创建过以下目录.
ASMCMD> pwd
+data/healdg
ASMCMD> mkdir datafile tempfile onlinelog controlfile parameterfile
ASMCMD> ls
controlfile/
datafile/
onlinelog/
parameterfile/
tempfile/
4、活动复制
[oracle@hisdb3 ~]$ rman target sys/oracle_4U@192.168.133.210:11521/heal auxiliary sys/oracle_4U@healdg

Recovery Manager: Release 11.2.0.4.0 - Production on Sun Nov 27 14:33:16 2022

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

connected to target database: HEAL (DBID=1822356550)
connected to auxiliary database: HEALDG (not mounted)

RMAN> run{
2> allocate channel c1 device type disk;
3> allocate channel c2 device type disk;
4> allocate channel c3 device type disk;
5> allocate channel c4 device type disk;
6> allocate channel c5 device type disk;
7> allocate channel c6 device type disk;
8> allocate channel c7 device type disk;
9> allocate channel c8 device type disk;
10> allocate auxiliary channel a1 device type disk;
11> allocate auxiliary channel a2 device type disk;
12> allocate auxiliary channel a3 device type disk;
13> allocate auxiliary channel a4 device type disk;
14> allocate auxiliary channel a5 device type disk;
15> allocate auxiliary channel a6 device type disk;
16> allocate auxiliary channel a7 device type disk;
17> allocate auxiliary channel a8 device type disk;
18> duplicate target database for standby nofilenamecheck from active database
19> DORECOVER
20> spfile
21> set db_unique_name='healdg'
22> set log_archive_dest_1='location=+fra valid_for=(all_logfiles,all_roles) db_unique_name=healdg'
23> set log_archive_dest_2='service=heal lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=heal'
24> set standby_file_management='AUTO'
25> set fal_client='healdg'
26> set fal_server='heal'
27> set control_files='+DATA/healdg/controlfile/control01.ctl'
28> set log_file_name_convert='+data/heal/tempfile/','+data/healdg/tempfile/','+data/heal/onlinelog/','+data/healdg/onlinelog/','+data/heal/controlfile/','+data/healdg/controlfile/'
29> set db_file_name_convert='+data/heal/datafile/','+data/healdg/datafile/','+data/heal/tempfile/','+data/healdg/tempfile/'
30> set audit_file_dest='/u01/app/oracle/admin/healdg/adump'
31> set db_create_file_dest = '+DATA'
32> set instance_number = '1';
33> }

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=79 instance=heal1 device type=DISK

allocated channel: c2
channel c2: SID=147 instance=heal1 device type=DISK

allocated channel: c3
channel c3: SID=202 instance=heal1 device type=DISK

allocated channel: c4
channel c4: SID=17 instance=heal1 device type=DISK

allocated channel: c5
channel c5: SID=63 instance=heal1 device type=DISK

allocated channel: c6
channel c6: SID=141 instance=heal1 device type=DISK

allocated channel: c7
channel c7: SID=203 instance=heal1 device type=DISK

allocated channel: c8
channel c8: SID=15 instance=heal1 device type=DISK

allocated channel: a1
channel a1: SID=174 device type=DISK

allocated channel: a2
channel a2: SID=13 device type=DISK

allocated channel: a3
channel a3: SID=175 device type=DISK

allocated channel: a4
channel a4: SID=14 device type=DISK

allocated channel: a5
channel a5: SID=176 device type=DISK

allocated channel: a6
channel a6: SID=15 device type=DISK

allocated channel: a7
channel a7: SID=177 device type=DISK

allocated channel: a8
channel a8: SID=16 device type=DISK

Starting Duplicate Db at 27-NOV-22

contents of Memory Script:
{
   backup as copy reuse
   targetfile '/u01/app/oracle/product/11.2.0/db_1/dbs/orapwheal1' auxiliary format
'/u01/app/oracle/product/11.2.0/db_1/dbs/orapwhealdg1'   targetfile
'/u01/app/oracle/product/11.2.0/db_1/dbs/spfileheal1.ora' auxiliary format
'/u01/app/oracle/product/11.2.0/db_1/dbs/spfilehealdg1.ora'   ;
   sql clone "alter system set spfile= ''/u01/app/oracle/product/11.2.0/db_1/dbs/spfilehealdg1.ora''";
}
executing Memory Script

Starting backup at 27-NOV-22
Finished backup at 27-NOV-22

sql statement: alter system set spfile= ''/u01/app/oracle/product/11.2.0/db_1/dbs/spfilehealdg1.ora''

contents of Memory Script:
{
   sql clone "alter system set db_unique_name =
''healdg'' comment=
'''' scope=spfile";
   sql clone "alter system set log_archive_dest_1 =
''location=+fra valid_for=(all_logfiles,all_roles) db_unique_name=healdg'' comment=
'''' scope=spfile";
   sql clone "alter system set log_archive_dest_2 =
''service=heal lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=heal'' comment=
'''' scope=spfile";
   sql clone "alter system set standby_file_management =
''AUTO'' comment=
'''' scope=spfile";
   sql clone "alter system set fal_client =
''healdg'' comment=
'''' scope=spfile";
   sql clone "alter system set fal_server =
''heal'' comment=
'''' scope=spfile";
   sql clone "alter system set control_files =
''+DATA/healdg/controlfile/control01.ctl'' comment=
'''' scope=spfile";
   sql clone "alter system set log_file_name_convert =
''+data/heal/tempfile/'', ''+data/healdg/tempfile/'', ''+data/heal/onlinelog/'', ''+data/healdg/onlinelog/'', ''+data/heal/controlfile/'', ''+data/healdg/controlfile/'' comment=
'''' scope=spfile";
   sql clone "alter system set db_file_name_convert =
''+data/heal/datafile/'', ''+data/healdg/datafile/'', ''+data/heal/tempfile/'', ''+data/healdg/tempfile/'' comment=
'''' scope=spfile";
   sql clone "alter system set audit_file_dest =
''/u01/app/oracle/admin/healdg/adump'' comment=
'''' scope=spfile";
   sql clone "alter system set db_create_file_dest =
''+DATA'' comment=
'''' scope=spfile";
   sql clone "alter system set instance_number =
1 comment=
'''' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set db_unique_name = ''healdg'' comment= '''' scope=spfile

sql statement: alter system set log_archive_dest_1 = ''location=+fra valid_for=(all_logfiles,all_roles) db_unique_name=healdg'' comment= '''' scope=spfile

sql statement: alter system set log_archive_dest_2 = ''service=heal lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=heal'' comment= '''' scope=spfile

sql statement: alter system set standby_file_management = ''AUTO'' comment= '''' scope=spfile

sql statement: alter system set fal_client = ''healdg'' comment= '''' scope=spfile

sql statement: alter system set fal_server = ''heal'' comment= '''' scope=spfile

sql statement: alter system set control_files = ''+DATA/healdg/controlfile/control01.ctl'' comment= '''' scope=spfile

sql statement: alter system set log_file_name_convert = ''+data/heal/tempfile/'', ''+data/healdg/tempfile/'', ''+data/heal/onlinelog/'', ''+data/healdg/onlinelog/'', ''+data/heal/controlfile/'', ''+data/healdg/controlfile/'' comment= '''' scope=spfile

sql statement: alter system set db_file_name_convert = ''+data/heal/datafile/'', ''+data/healdg/datafile/'', ''+data/heal/tempfile/'', ''+data/healdg/tempfile/'' comment= '''' scope=spfile

sql statement: alter system set audit_file_dest = ''/u01/app/oracle/admin/healdg/adump'' comment= '''' scope=spfile

sql statement: alter system set db_create_file_dest = ''+DATA'' comment= '''' scope=spfile

sql statement: alter system set instance_number = 1 comment= '''' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     855982080 bytes

Fixed Size                     2258040 bytes
Variable Size                348130184 bytes
Database Buffers             499122176 bytes
Redo Buffers                   6471680 bytes
allocated channel: a1
channel a1: SID=15 instance=healdg1 device type=DISK
allocated channel: a2
channel a2: SID=143 instance=healdg1 device type=DISK
allocated channel: a3
channel a3: SID=16 instance=healdg1 device type=DISK
allocated channel: a4
channel a4: SID=144 instance=healdg1 device type=DISK
allocated channel: a5
channel a5: SID=17 instance=healdg1 device type=DISK
allocated channel: a6
channel a6: SID=145 instance=healdg1 device type=DISK
allocated channel: a7
channel a7: SID=19 instance=healdg1 device type=DISK
allocated channel: a8
channel a8: SID=146 instance=healdg1 device type=DISK

contents of Memory Script:
{
   backup as copy current controlfile for standby auxiliary format '+DATA/healdg/controlfile/control01.ctl';
}
executing Memory Script

Starting backup at 27-NOV-22
channel c1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_heal1.f tag=TAG20221127T143356 RECID=1 STAMP=1121870037
channel c1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 27-NOV-22

contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database
RMAN-05529: WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.

contents of Memory Script:
{
   set newname for tempfile 1 to
"+data";
   switch clone tempfile all;
   set newname for datafile 1 to
"+data";
   set newname for datafile 2 to
"+data";
   set newname for datafile 3 to
"+data";
   set newname for datafile 4 to
"+data";
   set newname for datafile 5 to
"+data";
   backup as copy reuse
   datafile 1 auxiliary format
"+data"   datafile
2 auxiliary format
"+data"   datafile
3 auxiliary format
"+data"   datafile
4 auxiliary format
"+data"   datafile
5 auxiliary format
"+data"   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to +data in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 27-NOV-22
channel c1: starting datafile copy
input datafile file number=00001 name=+DATA/heal/datafile/system.281.1121775747
channel c2: starting datafile copy
input datafile file number=00002 name=+DATA/heal/datafile/sysaux.282.1121775747
channel c3: starting datafile copy
input datafile file number=00003 name=+DATA/heal/datafile/undotbs1.283.1121775749
channel c4: starting datafile copy
input datafile file number=00005 name=+DATA/heal/datafile/undotbs2.292.1121775853
channel c5: starting datafile copy
input datafile file number=00004 name=+DATA/heal/datafile/users.284.1121775749
output file name=+DATA/healdg/datafile/undotbs2.259.1121870051 tag=TAG20221127T143406
channel c4: datafile copy complete, elapsed time: 00:00:03
output file name=+DATA/healdg/datafile/users.260.1121870051 tag=TAG20221127T143406
channel c5: datafile copy complete, elapsed time: 00:00:03
output file name=+DATA/healdg/datafile/undotbs1.261.1121870051 tag=TAG20221127T143406
channel c3: datafile copy complete, elapsed time: 00:00:07
output file name=+DATA/healdg/datafile/system.257.1121870049 tag=TAG20221127T143406
channel c1: datafile copy complete, elapsed time: 00:00:16
output file name=+DATA/healdg/datafile/sysaux.258.1121870049 tag=TAG20221127T143406
channel c2: datafile copy complete, elapsed time: 00:00:16
Finished backup at 27-NOV-22

sql statement: alter system archive log current

contents of Memory Script:
{
   backup as copy reuse
   archivelog like "+FRA/heal/archivelog/2022_11_27/thread_2_seq_6.444.1121870049" auxiliary format
"+FRA"   archivelog like
"+FRA/heal/archivelog/2022_11_27/thread_1_seq_11.445.1121870065" auxiliary format
"+FRA"   archivelog like
"+FRA/heal/archivelog/2022_11_27/thread_2_seq_7.446.1121870065" auxiliary format
"+FRA"   archivelog like
"+FRA/heal/archivelog/2022_11_27/thread_1_seq_9.442.1121868505" auxiliary format
"+FRA"   archivelog like
"+FRA/heal/archivelog/2022_11_27/thread_1_seq_10.443.1121870049" auxiliary format
"+FRA"   ;
   catalog clone start with "+FRA";
   switch clone datafile all;
}
executing Memory Script

Starting backup at 27-NOV-22
channel c1: starting archived log copy
input archived log thread=2 sequence=6 RECID=12 STAMP=1121870049
channel c2: starting archived log copy
input archived log thread=1 sequence=11 RECID=13 STAMP=1121870065
channel c3: starting archived log copy
input archived log thread=2 sequence=7 RECID=14 STAMP=1121870065
channel c4: starting archived log copy
input archived log thread=1 sequence=9 RECID=10 STAMP=1121868504
channel c5: starting archived log copy
input archived log thread=1 sequence=10 RECID=11 STAMP=1121870049
output file name=+FRA/healdg/archivelog/2022_11_27/thread_2_seq_6.257.1121870071 RECID=0 STAMP=0
channel c1: archived log copy complete, elapsed time: 00:00:08
output file name=+FRA/healdg/archivelog/2022_11_27/thread_1_seq_11.260.1121870073 RECID=0 STAMP=0
channel c2: archived log copy complete, elapsed time: 00:00:08
output file name=+FRA/healdg/archivelog/2022_11_27/thread_2_seq_7.258.1121870071 RECID=0 STAMP=0
channel c3: archived log copy complete, elapsed time: 00:00:09
output file name=+FRA/healdg/archivelog/2022_11_27/thread_1_seq_10.259.1121870071 RECID=0 STAMP=0
channel c5: archived log copy complete, elapsed time: 00:00:11
output file name=+FRA/healdg/archivelog/2022_11_27/thread_1_seq_9.256.1121870071 RECID=0 STAMP=0
channel c4: archived log copy complete, elapsed time: 00:00:20
Finished backup at 27-NOV-22

searching for all files that match the pattern +FRA

List of Files Unknown to the Database
=====================================
File Name: +fra/healdg/archivelog/2022_11_27/thread_1_seq_9.256.1121870071
File Name: +fra/healdg/archivelog/2022_11_27/thread_2_seq_6.257.1121870071
File Name: +fra/healdg/archivelog/2022_11_27/thread_2_seq_7.258.1121870071
File Name: +fra/healdg/archivelog/2022_11_27/thread_1_seq_10.259.1121870071
File Name: +fra/healdg/archivelog/2022_11_27/thread_1_seq_11.260.1121870073
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: +fra/healdg/archivelog/2022_11_27/thread_1_seq_9.256.1121870071
File Name: +fra/healdg/archivelog/2022_11_27/thread_2_seq_6.257.1121870071
File Name: +fra/healdg/archivelog/2022_11_27/thread_2_seq_7.258.1121870071
File Name: +fra/healdg/archivelog/2022_11_27/thread_1_seq_10.259.1121870071
File Name: +fra/healdg/archivelog/2022_11_27/thread_1_seq_11.260.1121870073

datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=1121870089 file name=+DATA/healdg/datafile/system.257.1121870049
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=1121870089 file name=+DATA/healdg/datafile/sysaux.258.1121870049
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=1121870089 file name=+DATA/healdg/datafile/undotbs1.261.1121870051
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=1121870089 file name=+DATA/healdg/datafile/users.260.1121870051
datafile 5 switched to datafile copy
input datafile copy RECID=5 STAMP=1121870089 file name=+DATA/healdg/datafile/undotbs2.259.1121870051

contents of Memory Script:
{
   set until scn 1161679;
   recover
   standby
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 27-NOV-22

starting media recovery

archived log for thread 1 with sequence 10 is already on disk as file +FRA/healdg/archivelog/2022_11_27/thread_1_seq_10.259.1121870071
archived log for thread 1 with sequence 11 is already on disk as file +FRA/healdg/archivelog/2022_11_27/thread_1_seq_11.260.1121870073
archived log for thread 2 with sequence 6 is already on disk as file +FRA/healdg/archivelog/2022_11_27/thread_2_seq_6.257.1121870071
archived log for thread 2 with sequence 7 is already on disk as file +FRA/healdg/archivelog/2022_11_27/thread_2_seq_7.258.1121870071
archived log file name=+FRA/healdg/archivelog/2022_11_27/thread_1_seq_10.259.1121870071 thread=1 sequence=10
archived log file name=+FRA/healdg/archivelog/2022_11_27/thread_2_seq_6.257.1121870071 thread=2 sequence=6
archived log file name=+FRA/healdg/archivelog/2022_11_27/thread_1_seq_11.260.1121870073 thread=1 sequence=11
archived log file name=+FRA/healdg/archivelog/2022_11_27/thread_2_seq_7.258.1121870071 thread=2 sequence=7
media recovery complete, elapsed time: 00:00:01
Finished recover at 27-NOV-22
Finished Duplicate Db at 27-NOV-22
released channel: c1
released channel: c2
released channel: c3
released channel: c4
released channel: c5
released channel: c6
released channel: c7
released channel: c8
released channel: a1
released channel: a2
released channel: a3
released channel: a4
released channel: a5
released channel: a6
released channel: a7
released channel: a8
5、单机转rac
由于备库是rac，需修改spfile到磁盘组，然后才能启动rac dg的2个节点.
5.1、生成pfile
[oracle@hisdb3 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Nov 27 17:10:07 2022

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

SQL> show parameter cluster

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cluster_database                     boolean     TRUE
cluster_database_instances           integer     2
cluster_interconnects                string
SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string         /u01/app/oracle/product/11.2.0
                                                 /db_1/dbs/spfilehealdg1.ora
SQL> create pfile='/home/oracle/pfile.ora' from spfile;

File created.
5.2、修改pfile
--按如下内容修改pfile文件.
[oracle@hisdb3 ~]$ cat pfile.ora
*.audit_file_dest='/u01/app/oracle/admin/healdg/adump'
*.audit_trail='none'
*.cluster_database=true
*.compatible='11.2.0.4.0'
*.control_files='+DATA/healdg/controlfile/control01.ctl'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_file_name_convert='+data/heal/datafile/','+data/healdg/datafile/','+data/heal/tempfile/','+data/healdg/tempfile/'
*.db_name='heal'
*.db_recovery_file_dest='+DATA'
*.db_recovery_file_dest_size=4621074432
*.db_unique_name='healdg'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=healXDB)'
*.fal_client='healdg'
*.fal_server='heal'
*.log_archive_config='dg_config=(heal,healdg)'
*.log_archive_dest_1='location=+fra valid_for=(all_logfiles,all_roles) db_unique_name=healdg'
*.log_archive_dest_2='service=heal lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=heal'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_file_name_convert='+data/heal/tempfile/','+data/healdg/tempfile/','+data/heal/onlinelog/','+data/healdg/onlinelog/','+data/heal/controlfile/','+data/healdg/controlfile/'
*.open_cursors=300
*.pga_aggregate_target=213909504
*.processes=150
*.remote_listener='hisdb-scan:1521'
*.remote_login_passwordfile='exclusive'
*.sga_target=858783744
*.standby_file_management='AUTO'
healdg2.instance_number=2
healdg1.instance_number=1
healdg2.thread=2
healdg1.thread=1
healdg1.undo_tablespace='UNDOTBS1'
healdg2.undo_tablespace='UNDOTBS2'
注意蓝色标注内容.
5.3、创建spfile
备库节点1：
--创建spfile文件到磁盘组，并在pfile文件中添加spfile路径.
SQL> create spfile='+data/healdg/parameterfile/spfilehealdg.ora' from pfile='/home/oracle/pfile.ora';

File created.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
[oracle@hisdb3 ~]$ echo "SPFILE='+data/healdg/parameterfile/spfilehealdg.ora'" > $ORACLE_HOME/dbs/inithealdg1.ora
[oracle@hisdb3 ~]$ cat $ORACLE_HOME/dbs/inithealdg1.ora
SPFILE='+data/healdg/parameterfile/spfilehealdg.ora'
在备库节点2的pfile文件中添加spfile路径.
[oracle@hisdb4 ~]$ echo "SPFILE='+data/healdg/parameterfile/spfilehealdg.ora'" > $ORACLE_HOME/dbs/inithealdg2.ora
[oracle@hisdb4 ~]$ cat $ORACLE_HOME/dbs/inithealdg2.ora
SPFILE='+data/healdg/parameterfile/spfilehealdg.ora'
--备库删除原spfile文件.
[oracle@hisdb3 dbs]$ pwd
/u01/app/oracle/product/11.2.0/db_1/dbs
[oracle@hisdb3 dbs]$ rm -rf spfilehealdg1.ora
5.4、备库重启
启动备库两个节点后查看.
SQL> startup force
ORACLE instance started.

Total System Global Area 855982080 bytes
Fixed Size                  2258040 bytes
Variable Size             348130184 bytes
Database Buffers          499122176 bytes
Redo Buffers                6471680 bytes
Database mounted.
Database opened.
SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string         +DATA/healdg/parameterfile/spf
                                                 ilehealdg.ora
SQL> set line 9999
SQL> select name,open_mode,log_mode,force_logging,database_role,switchover_status from gv$database;

NAME      OPEN_MODE            LOG_MODE     FOR DATABASE_ROLE    SWITCHOVER_STATUS
--------- -------------------- ------------ --- ---------------- --------------------
HEAL      READ ONLY            ARCHIVELOG   YES PHYSICAL STANDBY NOT ALLOWED
--启动节点2后，查询结果如下.
SQL> select name,open_mode,log_mode,force_logging,database_role,switchover_status from gv$database

NAME      OPEN_MODE            LOG_MODE     FOR DATABASE_ROLE    SWITCHOVER_STATUS
--------- -------------------- ------------ --- ---------------- --------------------
HEAL      READ ONLY            ARCHIVELOG   YES PHYSICAL STANDBY NOT ALLOWED
HEAL      READ ONLY            ARCHIVELOG   YES PHYSICAL STANDBY NOT ALLOWED
5.5、加入crsctl
将备库加入crsctl中.说明：dbca创建的数据库会自动加入crsctl，但通过rman创建的库需手动添加，加入crsctl中后可通过srvctl管理.
5.5.1、集群状态
加入前集群状态.
[grid@hisdb3 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
NAME           TARGET STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
               ONLINE ONLINE       hisdb3
               ONLINE ONLINE       hisdb4
ora.FRA.dg
               ONLINE ONLINE       hisdb3
               ONLINE ONLINE       hisdb4
ora.LISTENER.lsnr
               ONLINE ONLINE       hisdb3
               ONLINE ONLINE       hisdb4
ora.OCR.dg
               ONLINE ONLINE       hisdb3
               ONLINE ONLINE       hisdb4
ora.asm
               ONLINE ONLINE       hisdb3                   Started
               ONLINE ONLINE       hisdb4                   Started
ora.gsd
               OFFLINE OFFLINE      hisdb3
               OFFLINE OFFLINE      hisdb4
ora.net1.network
               ONLINE ONLINE       hisdb3
               ONLINE ONLINE       hisdb4
ora.ons
               ONLINE ONLINE       hisdb3
             ONLINE ONLINE       hisdb4
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE ONLINE       hisdb4
ora.cvu
      1        ONLINE ONLINE       hisdb3
ora.hisdb3.vip
      1        ONLINE ONLINE       hisdb3
ora.hisdb4.vip
      1        ONLINE ONLINE       hisdb4
ora.oc4j
      1        ONLINE ONLINE       hisdb3
ora.scan1.vip
      1        ONLINE ONLINE       hisdb4
5.5.2、指令说明
[grid@hisdb3 ~]$ srvctl add database -h

Adds a database configuration to the Oracle Clusterware.

Usage: srvctl add database -d <db_unique_name> -o <oracle_home> [-c {RACONENODE | RAC | SINGLE} [-e <server_list>] [-i <inst_name>] [-w <timeout>]] [-m <domain_name>] [-p <spfile>] [-r {PRIMARY | PHYSICAL_STANDBY | LOGICAL_STANDBY | SNAPSHOT_STANDBY}] [-s <start_options>] [-t <stop_options>] [-n <db_name>] [-y {AUTOMATIC | MANUAL | NORESTART}] [-g "<serverpool_list>"] [-x <node_name>] [-a "<diskgroup_list>"] [-j "<acfs_path_list>"]
    -d <db_unique_name>      Unique name for the database
    -o <oracle_home>         ORACLE_HOME path
    -c <type>                Type of database: RAC One Node, RAC, or Single Instance
    -e <server_list>         Candidate server list for RAC One Node database
    -i <inst_name>           Instance name prefix for administrator-managed RAC One Node database (default first 12 characters of <db_unique_name>)
    -w <timeout>             Online relocation timeout in minutes
    -x <node_name>           Node name. -x option is specified for single-instance databases
    -m <domain>              Domain for database. Must be set if database has DB_DOMAIN set.
    -p <spfile>              Server parameter file path
    -r <role>                Role of the database (primary, physical_standby, logical_standby, snapshot_standby)
    -s <start_options>       Startup options for the database. Examples of startup options are OPEN, MOUNT, or 'READ ONLY'.
    -t <stop_options>        Stop options for the database. Examples of shutdown options are NORMAL, TRANSACTIONAL, IMMEDIATE, or ABORT.
    -n <db_name>             Database name (DB_NAME), if different from the unique name given by the -d option
    -y <dbpolicy>            Management policy for the database (AUTOMATIC, MANUAL, or NORESTART)
    -g "<serverpool_list>"   Comma separated list of database server pool names
    -a "<diskgroup_list>"    Comma separated list of disk groups
    -j "<acfs_path_list>"    Comma separated list of ACFS paths where database's dependency will be set
-h                       Print usage
5.5.3、操作过程
5.5.3.1、主库操作
主库将数据库置于mount状态.
[grid@hisdb1 ~]$ srvctl stop database -d heal -o immediate
[grid@hisdb1 ~]$ srvctl start database -d heal -o mount
[grid@hisdb1 ~]$ srvctl status database -d heal
Instance heal1 is running on node hisdb1
Instance heal2 is running on node hisdb2
5.5.3.2、备库操作
备库节点1操作.
[oracle@hisdb3 ~]$ srvctl add database -d healdg -c RAC -o /u01/app/oracle/product/11.2.0/db_1 -p '+data/healdg/parameterfile/spfilehealdg.ora' -r physical_standby -n heal
[oracle@hisdb3 ~]$ srvctl add instance -d healdg -i healdg1 -n hisdb3
[oracle@hisdb3 ~]$ srvctl add instance -d healdg -i healdg2 -n hisdb4
[oracle@hisdb3 ~]$ srvctl status database -d healdg
Instance healdg1 is not running on node hisdb3
Instance healdg2 is not running on node hisdb4
[oracle@hisdb3 ~]$ srvctl start database -d healdg
[oracle@hisdb3 ~]$ srvctl status database -d healdg
Instance healdg1 is running on node hisdb3
Instance healdg2 is running on node hisdb4
[oracle@hisdb3 ~]$ srvctl config database -d healdg -a
Database unique name: healdg
Database name: heal
Oracle home: /u01/app/oracle/product/11.2.0/db_1
Oracle user: oracle
Spfile: +data/healdg/parameterfile/spfilehealdg.ora
Domain:
Start options: open
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Server pools: healdg
Database instances: healdg1,healdg2
Disk Groups:
Mount point paths:
Services:
Type: RAC
Database is enabled
Database is administrator managed
5.5.3.2、备库查询
[grid@hisdb3 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
NAME           TARGET STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
                 ONLINE ONLINE       hisdb3
                   ONLINE ONLINE       hisdb4
ora.FRA.dg
               ONLINE ONLINE       hisdb3
               ONLINE ONLINE       hisdb4
ora.LISTENER.lsnr
               ONLINE ONLINE       hisdb3
               ONLINE ONLINE       hisdb4
ora.OCR.dg
               ONLINE ONLINE       hisdb3
               ONLINE ONLINE       hisdb4
ora.asm
               ONLINE ONLINE       hisdb3                   Started
               ONLINE ONLINE       hisdb4                   Started
ora.gsd
               OFFLINE OFFLINE      hisdb3
               OFFLINE OFFLINE      hisdb4
ora.net1.network
               ONLINE ONLINE       hisdb3
               ONLINE ONLINE       hisdb4
ora.ons
               ONLINE ONLINE       hisdb3
               ONLINE ONLINE       hisdb4
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE ONLINE       hisdb4
ora.cvu
      1        ONLINE ONLINE       hisdb3
ora.healdg.db
      1        ONLINE ONLINE       hisdb3                   Open
      2        ONLINE ONLINE       hisdb4                   Open
ora.hisdb3.vip
      1        ONLINE ONLINE       hisdb3
ora.hisdb4.vip
      1        ONLINE ONLINE       hisdb4
ora.oc4j
      1        ONLINE ONLINE       hisdb3
ora.scan1.vip
      1        ONLINE ONLINE       hisdb4

SQL> select group#,thread#,sequence#,bytes,blocksize,members,status from v$log;

    GROUP#    THREAD# SEQUENCE#      BYTES BLOCKSIZE    MEMBERS STATUS
---------- ---------- ---------- ---------- ---------- ---------- ----------------
         1          1         15   52428800        512          2 CURRENT
         2          1         14   52428800        512          2 CLEARING
         3          2         11   52428800        512          2 CURRENT
         4          2         10   52428800        512          2 CLEARING

SQL> select dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database;

DBID NAME      CURRENT_SCN PROTECTION_MODE      DATABASE_ROLE    FOR OPEN_MODE            SWITCHOVER_STATUS
---------- --------- ----------- -------------------- ---------------- --- -------------------- --------------------
1822356550 HEAL          1161678 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ ONLY            NOT ALLOWED
1822356550 HEAL          1161678 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ ONLY            NOT ALLOWED
SQL> col dbid for a15
SQL> select group#,dbid,thread#,sequence#,bytes,blocksize,used,archived,status from v$standby_log

GROUP# DBID               THREAD# SEQUENCE#      BYTES BLOCKSIZE       USED ARC STATUS
---------- --------------- ---------- ---------- ---------- ---------- ---------- --- ----------
         5 UNASSIGNED               1          0   52428800        512          0 NO UNASSIGNED
         6 1822356550                  1         15   52428800        512    2737664 YES ACTIVE
         7 UNASSIGNED               1          0   52428800        512          0 YES UNASSIGNED
         8 UNASSIGNED               2          0   52428800       512          0 NO UNASSIGNED
         9 1822356550                  2         11   52428800        512    2590208 YES ACTIVE
        10 UNASSIGNED               2          0   52428800        512          0 YES UNASSIGNED

6 rows selected.
5.6、实时同步
--开启实时同步
SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.
SQL> col db_unique_name for a20
SQL> select inst_id,dbid,name,db_unique_name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database

INST_ID DBID NAME DB_UNIQUE_NAME CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FOR OPEN_MODE     SWITCHOVER_STATUS
---------- ---------- --------- -------------------- ----------- -------------------- ---------------- --- -------------------- --------------------
2 1822356550 HEAL    healdg       1201461 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ ONLY WITH APPLY NOT ALLOWED
1 1822356550 HEAL    healdg       1201461 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ ONLY WITH APPLY NOT ALLOWED
SQL> col name for a70
SQL> r
1 SELECT dest_id, THREAD#, NAME, sequence#, archived, applied, a.NEXT_CHANGE#
2    FROM v$archived_log a
3   WHERE a.sequence# >= 12
4     AND resetlogs_change# = (SELECT d.RESETLOGS_CHANGE# FROM v$database d)
5* ORDER BY a.THREAD#, a.sequence#, a.dest_id

DEST_ID   THREAD# NAME                                           SEQUENCE# ARC APPLIED   NEXT_CHANGE#
---------- ---------- ---------------------------------------------------------------------- ---------- --- --------- ------------
         1          1 +FRA/healdg/archivelog/2022_11_27/thread_1_seq_12.261.1121870367               12 YES YES            1162135
         1          1 +FRA/healdg/archivelog/2022_11_27/thread_1_seq_13.264.1121878501               13 YES YES            1176097
         1          1 +FRA/healdg/archivelog/2022_11_27/thread_1_seq_14.266.1121888027               14 YES IN-MEMORY    1197527
6、备库验证
将主库两节点从mount开启到open状态，主库节点1切换日志验证.
[oracle@hisdb1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Nov 27 20:33:47 2022

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> alter database open;

Database altered.
SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> create table leo.test as select * from all_objects;

Table created.

SQL> select count(*) from leo.test;

COUNT(*)
----------
84432
--备库端查询日志应用情况
SQL> SELECT dest_id, THREAD#, NAME, sequence#, archived, applied, a.NEXT_CHANGE#
2    FROM v$archived_log a
3   WHERE a.sequence# >= 12
4     AND resetlogs_change# = (SELECT d.RESETLOGS_CHANGE# FROM v$database d)
5   ORDER BY a.THREAD#, a.sequence#, a.dest_id;

DEST_ID    THREAD# NAME                                              SEQUENCE# ARC APPLIED   NEXT_CHANGE#
---------- ---------- ---------------------------------------------------------------------- ---------- --- --------- ------------
         1          1 +FRA/healdg/archivelog/2022_11_27/thread_1_seq_12.261.1121870367               12 YES YES            1162135
         1          1 +FRA/healdg/archivelog/2022_11_27/thread_1_seq_13.264.1121878501               13 YES YES            1176097
         1          1 +FRA/healdg/archivelog/2022_11_27/thread_1_seq_14.266.1121888027               14 YES YES            1197527
         1          1 +FRA/healdg/archivelog/2022_11_27/thread_1_seq_15.267.1121891639               15 YES YES            1201480
         1          1 +FRA/healdg/archivelog/2022_11_27/thread_1_seq_16.268.1121891641               16 YES YES            1201555
         1          1 +FRA/healdg/archivelog/2022_11_27/thread_1_seq_17.272.1121891679               17 YES YES            1203810
         1          1 +FRA/healdg/archivelog/2022_11_27/thread_1_seq_18.273.1121891779               18 YES YES            1203928
         1          1 +FRA/healdg/archivelog/2022_11_27/thread_1_seq_19.274.1121891783               19 YES YES            1203934
         1          1 +FRA/healdg/archivelog/2022_11_27/thread_1_seq_20.276.1121891795               20 YES IN-MEMORY   1203979
         1          2 +FRA/healdg/archivelog/2022_11_27/thread_2_seq_12.270.1121891669               12 YES YES            1201849
         1          2 +FRA/healdg/archivelog/2022_11_27/thread_2_seq_13.271.1121891675               13 YES YES           1203682
         1          2 +FRA/healdg/archivelog/2022_11_27/thread_2_seq_14.275.1121891789               14 YES YES            1203944

12 rows selected.
SQL> select count(*) from leo.test;

COUNT(*)
----------
84432

结论：备库数据实时同步，物理rac dg搭建完成.

参考文档：
https://blog.51cto.com/lhrbest/2692041
https://www.manongdao.com/article-2379152.html

墨力计划

最后修改时间：2022-11-28 23:35:16

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

oracle 11.2.0.4 rac dg配置

1、环境介绍

2、主节点建库

3、前期准备

3.1、主库准备

3.1.1、归档及强制记录日志

3.1.2、standby redo log

3.1.3、参数文件

3.1.4、监听文件

3.1.4.1、主库监听

3.1.4.2、备库监听

3.1.5、tnsnames.ora

3.1.6、密码文件

3.2、备库操作

3.2.1、修改oratab

3.2.2、启动到nomount

3.2.3、互通测试

3.2.4、连接验证

3.2.5、新建目录

4、活动复制

5、单机转rac

5.1、生成pfile

5.2、修改pfile

5.3、创建spfile

5.4、备库重启

5.5、加入crsctl

5.5.1、集群状态

5.5.2、指令说明

5.5.3、操作过程

5.5.3.1、主库操作

5.5.3.2、备库操作

5.5.3.2、备库查询

5.6、实时同步

6、备库验证

评论