oracle 11.2.0.4 rac dg配置

文档课题: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='locatinotallow=+fra' scope=spfile sid='heal1';

System altered.

SQL> alter system set log_archive_dest_1='locatinotallow=+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_cnotallow='dg_cnotallow=( 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_cnotallow=('+DATA','+RECOVERY')
d、fal_server、fal_client为TNSNAMES描述符
实际操作,主库节点1执行:
SQL> alter system set log_archive_cnotallow='dg_cnotallow=(heal,healdg)' sid='*';

System altered.

SQL> alter system set log_archive_dest_1='locatinotallow=+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_cnotallow='+data/healdg/datafile/','+data/heal/datafile/','+data/healdg/tempfile/','+data/heal/tempfile/' sid='*' scope=spfile;
alter system set log_file_name_cnotallow='+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='locatinotallow=+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_cnotallow='+data/heal/tempfile/','+data/healdg/tempfile/','+data/heal/onlinelog/','+data/healdg/onlinelog/','+data/heal/controlfile/','+data/healdg/controlfile/'
29> set db_file_name_cnotallow='+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 =
''locatinotallow=+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 = ''locatinotallow=+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_cnotallow='+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_cnotallow='dg_cnotallow=(heal,healdg)'
*.log_archive_dest_1='locatinotallow=+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_cnotallow='+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