部署RAC到单实例ADG（11G）

服务器信息

主库RAC环境信息

主库RAC基本环境

	节点1	节点2
OS	centos 7.9	centos 7.9
数据库版本	11.2.0.4	11.2.0.4
规格	1C4G	1C4G
主机名	racdb01	racdb02
public ip	192.168.40.135	192.168.40.145
vip	192.168.40.13	192.168.40.14
private ip	192.168.183.135	192.168.183.145
scanip	192.168.40.100
scan name	orcl-scan
ORACLE_HOME	/u01/app/oracle/product/11.2.0/db
db_name	topnet	topnet
db_unique_name	topnet	topnet
instance_name	topnet1	topnet2
端口	1521	1521

主库RAC文件路径环境

--查看所有数据文件位置
select file_name from dba_data_files
union all
select file_name from dba_temp_files
union all
select name from v$controlfile
union all
select name from v$archived_log;

FILE_NAME
--------------------------------------------------------------------------------
+DATA/topnet/datafile/users.268.1172044125
+DATA/topnet/datafile/undotbs1.267.1172044125
+DATA/topnet/datafile/sysaux.269.1172044125
+DATA/topnet/datafile/system.261.1172044125
+DATA/topnet/datafile/undotbs2.279.1172044263
+DATA/topnet/tempfile/temp.278.1172044205
+DATA/topnet/controlfile/current.260.1172044201
+ARCH/topnet/controlfile/current.317.1172044201

8 rows selected.

主机

数据文件路径

控制文件

归档日志文件

orcl01

+DATA/topnet/datafile/

+DATA/topnet/tempfile/

+DATA/topnet/controlfile/

+ARCH

orcl02

主库RAC的hosts文件

[oracle@orcl01:/home/oracle]$ cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

## OracleBegin

## RAC1 IP's: orcl01

## RAC1 Public IP
192.168.40.200 orcl01
## RAC1 Virtual IP
192.168.40.202 orcl01-vip
## RAC1 Private IP
192.168.183.200 orcl01-priv

## RAC2 IP's: orcl02

## RAC2 Public IP
192.168.40.201 orcl02
## RAC2 Virtual IP
192.168.40.203 orcl02-vip
## RAC2 Private IP
192.168.183.201 orcl02-priv

## SCAN IP
192.168.40.205 orcl-scan

备库环境规划

OS	centos 7.9
数据库版本	11.2.0.4
规格	1C4G
主机名	topnetdg
ip	192.168.40.52
端口	1521
ORACLE_HOME	/u01/app/oracle/product/11.2.0/db
db_name	dxjdg
db_unique_name	dxjdg
instance_name	dxjdg
备库数据文件存放路径	/oradata/topnetdg/datafile
备库联机日志文件文件存放路径	/oradata/topnetdg/onlinelog
备库临时表空间文件存放路径	/oradata/topnetdg/tempfile
备库控制文件存放路径	/oradata/topnetdg/controlfile
备库归档文件存放路径	/oradata/topnetdg/archivelog
备库相关日志存放路径	/oradata/topnetdg/adump
备库相关日志存放路径	/oradata/topnetdg/dpdump
备库相关日志存放路径	/oradata/topnetdg/hdump
备库相关日志存放路径	/oradata/topnetdg/pfile
备库闪回空间放路径	/u01/app/oracle/fast_recover_area

部署前准备工作

主库RAC

主库归档开启

确保主库开启归档，若未开启归档则按以下步骤进行开启归档，若已开启归档，则忽略该步骤。

--检查主库是否开启归档
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


--更改归档参数
alter system set log_archive_dest_1='location=+ARCH' scope=spfile sid='*';  

--关闭实例
srvctl stop database -d topnet -o immediate   #1个节点操纵，两个节点的数据库实例都会关闭

--将一个节点启动到mount状态
srvctl start instance -d topnet -i topnet1 -o mount

--开启归档
alter database archivelog;
alter database open;

--查看归档
sqlplus / as sysdba
SQL> archive log list;
Database log mode	       Archive Mode
Automatic archival	       Enabled
Archive destination	       +ARCH
Oldest online log sequence     4
Next log sequence to archive   5
Current log sequence	       5

--启动第2个节点
srvctl start instance -d topnet -i topnet2

主库force logging模式开启

在节点RAC1执行，查看数据库的模式

--在节点RAC1执行，查看数据库的模式   force_logging为NO  说明未开启force logging模式
set linesize 999
col open_mode for a15
col protection_mode for a30
col database_role for a15
select log_mode,open_mode,protection_mode,database_role,switchover_status,guard_status,force_logging from v$database;

LOG_MODE     OPEN_MODE		  PROTECTION_MODE      DATABASE_ROLE	SWITCHOVER_STATUS    GUARD_S FOR
------------ -------------------- -------------------- ---------------- -------------------- ------- ---
ARCHIVELOG   READ WRITE 	  MAXIMUM PERFORMANCE  PRIMARY		NOT ALLOWED	     NONE    NO

在节点RAC1上执行：

--开启force logging模式
alter database force logging;

--查看数据库的模式 force_logging为YES  说明已开启force logging模式
set linesize 999
col open_mode for a15
col protection_mode for a30
col database_role for a15
select log_mode,open_mode,protection_mode,database_role,switchover_status,guard_status,force_logging from v$database;

LOG_MODE     OPEN_MODE		  PROTECTION_MODE      DATABASE_ROLE	SWITCHOVER_STATUS    GUARD_S FOR
------------ -------------------- -------------------- ---------------- -------------------- ------- ---
ARCHIVELOG   READ WRITE 	  MAXIMUM PERFORMANCE  PRIMARY		NOT ALLOWED	     NONE    YES

主库创建standby redo log 文件

主库RAC1节点sqlplus执行查看现有日志组情况

--查看现有日志组情况
set linesize 999
col member format a50;
select a.thread#,a.group#,a.bytes/1024/1024,b.member from v$log a,v$logfile b where a.group#=b.group#;

   THREAD#     GROUP# A.BYTES/1024/1024 MEMBER
---------- ---------- ----------------- --------------------------------------------------
	 1	    2		     50 +DATA/topnet/onlinelog/group_2.268.1172048547
	 1	    2		     50 +DATA/topnet/onlinelog/group_2.267.1172048547
	 1	    1		     50 +DATA/topnet/onlinelog/group_1.257.1172048547
	 1	    1		     50 +DATA/topnet/onlinelog/group_1.279.1172048547
	 2	    3		     50 +DATA/topnet/onlinelog/group_3.256.1172048579
	 2	    3		     50 +DATA/topnet/onlinelog/group_3.270.1172048579
	 2	    4		     50 +DATA/topnet/onlinelog/group_4.272.1172048579
	 2	    4		     50 +DATA/topnet/onlinelog/group_4.265.1172048579

8 rows selected.

每个 thread 都需要创建，standby redo log 比 redo log 多一组,大小相同

--查看是否配置OMF
SQL> show parameter db_create_file_dest

NAME				     TYPE			       VALUE
------------------------------------ --------------------------------- ------------------------------
db_create_file_dest		     string			       +DATA

--若配置的有OMF，按以下步骤添加standby logfile
alter database add standby logfile thread 1 group 11  size 50m;
alter database add standby logfile thread 1 group 12  size 50m;
alter database add standby logfile thread 1 group 13  size 50m;

alter database add standby logfile thread 2 group 21  size 50m;
alter database add standby logfile thread 2 group 22  size 50m;
alter database add standby logfile thread 2 group 23  size 50m;

--若未配置OMF，按以下步骤添加standby logfile,由于上面每组2个redo成员，故standby logfile每组也对应2个redo成员
alter database add standby logfile thread 1 group 11 ('+DATA','+DATA') size 50m;
alter database add standby logfile thread 1 group 12 ('+DATA','+DATA') size 50m;
alter database add standby logfile thread 1 group 13 ('+DATA','+DATA') size 50m;

alter database add standby logfile thread 2 group 21 ('+DATA','+DATA') size 50m;
alter database add standby logfile thread 2 group 22 ('+DATA','+DATA') size 50m;
alter database add standby logfile thread 2 group 23 ('+DATA','+DATA') size 50m;

创建完查看日志组

--查看日志组
set pagesize 999
select group#,type,member from v$logfile order by 2;

    GROUP# TYPE 		 MEMBER
---------- --------------------- --------------------------------------------------
	 2 ONLINE		   +DATA/topnet/onlinelog/group_2.268.1172048547
	 2 ONLINE		   +DATA/topnet/onlinelog/group_2.267.1172048547
	 1 ONLINE		   +DATA/topnet/onlinelog/group_1.257.1172048547
	 1 ONLINE		   +DATA/topnet/onlinelog/group_1.279.1172048547
	 3 ONLINE		   +DATA/topnet/onlinelog/group_3.256.1172048579
	 3 ONLINE		   +DATA/topnet/onlinelog/group_3.270.1172048579
	 4 ONLINE		   +DATA/topnet/onlinelog/group_4.272.1172048579
	 4 ONLINE		   +DATA/topnet/onlinelog/group_4.265.1172048579
	11 STANDBY		 +DATA/topnet/onlinelog/group_11.263.1172048987
	12 STANDBY		 +DATA/topnet/onlinelog/group_12.258.1172048987
	13 STANDBY		 +DATA/topnet/onlinelog/group_13.277.1172048987
	21 STANDBY		 +DATA/topnet/onlinelog/group_21.264.1172048997
	22 STANDBY		 +DATA/topnet/onlinelog/group_22.262.1172048997
	23 STANDBY		 +DATA/topnet/onlinelog/group_23.259.1172048999

14 rows selected.

主库参数修改

--查看name参数
SQL> show parameter db_name

NAME				     TYPE			       VALUE
------------------------------------ --------------------------------- ------------------------------
db_name 			     string			       topnet

SQL> show parameter db_unique_name

NAME				     TYPE			       VALUE
------------------------------------ --------------------------------- ------------------------------
db_unique_name			     string			       topnet

SQL> show parameter instance_name

NAME				     TYPE			       VALUE
------------------------------------ --------------------------------- ------------------------------
instance_name			     string			       topnet1

SQL> show parameter service_name

NAME				     TYPE			       VALUE
------------------------------------ --------------------------------- ------------------------------
service_names			     string			       topnet

DB_NAME备库要与主库保持一致，

DB_UNIQUE_NAME要有所区别，不能一样，

参数DG_CONFIG和LOG_ARCHIVE_CONFIG中会用到db_unique_name

log_archive_dest_2里的service=后面跟的是服务名，就是写到TNSNAMES.ORA里的

主库修改参数如下，在RAC1节点执行

alter system set log_archive_config='dg_config=(topnet,topnetdg)' sid='*';
alter system set log_archive_dest_1='location=+ARCH valid_for=(all_logfiles,all_roles) db_unique_name=topnet' sid='*';
alter system set log_archive_dest_2='service=topnetdg async noaffirm valid_for=(online_logfiles,primary_role) db_unique_name=topnetdg' scope=both sid='*';
alter system set standby_file_management=auto scope=both sid='*';
alter system set fal_client='topnet' scope=both sid='*';
alter system set fal_server='topnetdg' scope=both sid='*';
alter system set log_archive_max_processes=10 scope=both;
alter system set db_file_name_convert='/oradata/topnetdg/datafile','+DATA/topnet/datafile','/oradata/topnetdg/tempfile','+DATA/topnet/tempfile' scope=spfile sid='*';
alter system set log_file_name_convert='/oradata/topnetdg/onlinelog','+DATA/orcl/onlinelog' scope=spfile sid='*';

关闭数据库使参数生效

（这里关库重启主要是为了让db_file_name_convert和log_file_name_convert立即生效！这两个参数如果这里不配置也可以！等需要的时候再配置。如果配置了该参数没有立即重启库，以后可能会出现1个实例崩溃后无法正常启动，提示参数不致！！！！）

srvctl stop database -d topnet -o immediate

启动数据库实例

srvctl start database -d topnet -o open

主库创建pfile备份文件

由于主库是RAC，RAC和单点的pfile文件不同。RAC每个节点的pfile文件是指向共享磁盘spfile文件的一个链接，如下：

--切换到$ORACLE_HOME/dbs目录
cd /u01/app/oracle/product/11.2.0/db/dbs

--查看RAC的pfile参数文件内容
strings inittopnet1.ora  
SPFILE='+DATA/topnet/spfiletopnet.ora'

--创建spfile文件对应的pfile参数文件  RAC任一节点执行均可，本文档在节点1执行
su - oracle
sqlplus / as sysdba
create pfile='/home/oracle/pfile_topnet1_20240620.ora' from spfile;

主库修改监听文件tnsnames.ora

su - oracle
cd $ORACLE_HOME/network/admin
cp tnsnames.ora tnsnames.ora_bak_20240620

[oracle@orcl01:/u01/app/oracle/product/11.2.0/db/network/admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

TOPNET =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = orcl-scan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = topnet)
    )
  )

TOPNETDG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.40.52 )(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = topnetdg)
    )
  )

备库单点

备库创建目录

cd /oradata
mkdir -p topnetdg/datafile
mkdir -p topnetdg/onlinelog
mkdir -p topnetdg/tempfile
mkdir -p topnetdg/controlfile
mkdir -p topnetdg/archivelog

[oracle@topnetdg:/home/oracle]$ cd $ORACLE_BASE
[oracle@topnetdg:/u01/app/oracle]$ pwd
/u01/app/oracle
[oracle@topnetdg:/u01/app/oracle]$ ls
cfgtoollogs checkpoints diag oradata product 
[oracle@topnetdg:/u01/app/oracle]$ mkdir -p admin/topnetdg/adump
[oracle@topnetdg:/u01/app/oracle]$ mkdir -p admin/topnetdg/dpdump
[oracle@topnetdg:/u01/app/oracle]$ mkdir -p admin/topnetdg/hdump
[oracle@topnetdg:/u01/app/oracle]$ mkdir -p admin/topnetdg/pfile
[oracle@topnetdg:/u01/app/oracle]$ mkdir -p /u01/app/oracle/fast_recover_area

备库创建口令文件

拷贝主库RAC两个节点任意一个节点的口令文件到备库的$ORACLE_HOME/dbs目录下(本文档是在主库RAC1节点执行)，然后在备库重命名口令文件

--在主库RAC1节点执行，拷贝主库RAC两个节点任意一个节点的口令文件到备库的$ORACLE_HOME/dbs目录下
su - oracle
cd $ORACLE_HOME/dbs
scp orapwtopnet1 oracle@192.168.40.52:/u01/app/oracle/product/11.2.0/db/dbs/

--在备库重命名口令文件  口令文件命令格式orapw+sid
cd $ORACLE_HOME/dbs
mv orapwtopnet1 orapwtopnetdg

备库创建参数文件

拷贝主库RAC两个节点任意一个节点新创建的pfile参数文件到备库的$ORACLE_HOME/dbs目录下(本文档是在主库RAC1节点执行)，然后在备库上修改参数文件

--在主库RAC1节点执行，拷贝主库RAC两个节点任意一个节点的参数文件到备库的$ORACLE_HOME/dbs目录下
su - oracle
cd $ORACLE_HOME/dbs
scp pfile_topnet1_20240620.ora oracle@192.168.40.52:/home/oracle/

注意备库参数文件中db_files参数要与主库RAC的db_files参数进行一下比较，要等于或多于主库RAC的db_files参数值。

注意db_file_name_convert参数:如果主库RAC的数据文件在多个路径下且有重名的数据文件情况下，此时注意修改该参数值不要在同一个文件夹下！！

修改后内容如下：

[oracle@topnetdg:/home/oracle]$ cat pfile_topnet1_20240620.ora
*.audit_file_dest='/u01/app/oracle/admin/topnetdg/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/oradata/topnetdg/controlfile/control01.ctl','/oradata/topnetdg/controlfile/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='+DATA/topnet/datafile','/oradata/topnetdg/datafile','+DATA/topnet/tempfile','/oradata/topnetdg/tempfile'
*.db_name='topnet'
*.db_recovery_file_dest='/u01/app/oracle/fast_recover_area'
*.db_recovery_file_dest_size=4621074432
*.db_files=1024
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=topnetdgXDB)'
*.fal_client='topnetdg'
*.fal_server='topnet'
*.log_archive_config='dg_config=(topnet,topnetdg)'
*.log_archive_dest_1='location=/oradata/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=topnetdg'
*.log_archive_dest_2='service=topnet async noaffirm valid_for=(online_logfiles,primary_role) db_unique_name=topnet'
*.log_archive_max_processes=10
*.log_file_name_convert='+DATA/topnet/onlinelog','/oradata/topnetdg/onlinelog'
*.log_archive_format='%t_%s_%r.dbf'
*.db_unique_name='topnetdg'
*.service_names='topnetdg'
*.undo_tablespace='UNDOTBS1'
*.memory_target=1645215744
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='exclusive'
*.standby_file_management='AUTO'

备库创建监听文件

listener.ora文件

增加静态监听内容：SID_LIST_LISTENER部分为静态监听内容

[oracle@topnetdg:/u01/app/oracle/product/11.2.0/db/network/admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.40.52)(PORT = 1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db)
        (SID_NAME = topnetdg)
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

tnsnames.ora文件

可将主库RAC节点1上的tnsnames.ora文件拷贝到备库，然后进行修改，修改后如下：

[oracle@orcl01:/u01/app/oracle/product/11.2.0/db/network/admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

TOPNET =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.40.200)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = topnet)
    )
  )

TOPNETDG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.40.52 )(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = topnetdg)
    )
  )

备库重启监听服务

lsnrctl stop
lsnrctl start

若跳过该步骤，后面rman连接主备库时会报错：

[oracle@topnetdg:/home/oracle]$ rman target sys/oracle@topnet auxiliary sys/oracle@topnetdg

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Jun 20 11:31:19 2024

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

connected to target database: TOPNET (DBID=3403746080)
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04006: error from auxiliary database: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

部署过程

备库启动实例至nomount状态

--通过pfile参数启库到nomount状态
idle 20-JUN-24> startup nomount  pfile='/home/oracle/pfile_topnet1_20240620.ora';
ORACLE instance started.

Total System Global Area 1653518336 bytes
Fixed Size		    2253784 bytes
Variable Size		 1006636072 bytes
Database Buffers	  637534208 bytes
Redo Buffers		    7094272 bytes

--通过pfile文件创建spfile文件
create spfile from pfile='/home/oracle/pfile_topnet1_20240620.ora';

--关库
shutdown abort

--spfile参数文件启库
startup nomount

--验证启库是通过spfile文件
set linesize 999
show parameter pfile

NAME				     TYPE			       VALUE
------------------------------------ --------------------------------- ------------------------------
spfile				     string			       /u01/app/oracle/product/11.2.0/db/dbs/spfiletopnetdg.ora

--依据spfile参数文件  创建$ORACLE_HOME/dbs目录下的pfile文件
create pfile from spfile;

启动的同时可以查看数据库日志观察数据库的操作：

su - oracle
cd $ORACLE_BASE/diag/rdbms/topnetdg/topnetdg/trace
tail -300f alert_topnetdg.log

tnsping后面跟的是tnsnames.ora文件中配置的网络名

RMAN复制主库RAC数据到备库，进行同步

RMAN复制主库RAC数据到备库，进行同步，数据同步完成后备库从startup nomount变成mount状态。

验证主备库网络

--备库验证主备库网络
tnsping topnet
tnsping topnetdg

输出的详细结果如下：


[oracle@topnetdg:/home/oracle]$ tnsping topnetdg

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 20-JUN-2024 11:29:02

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

Used parameter files:
/u01/app/oracle/product/11.2.0/db/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.40.52)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = topnetdg)))
OK (0 msec)

rman连接主备库

[oracle@topnetdg:/home/oracle]$ rman target sys/oracle@topnet auxiliary sys/oracle@topnetdg

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Jun 20 11:35:13 2024

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

connected to target database: TOPNET (DBID=3403746080)
connected to auxiliary database (not started)

问题处理

rman连接主备库时会报错

--问题描述
rman连接主备库时会报错
[oracle@topnetdg:/home/oracle]$ rman target sys/oracle@topnet auxiliary sys/oracle@topnetdg

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Jun 20 11:31:19 2024

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

connected to target database: TOPNET (DBID=3403746080)
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04006: error from auxiliary database: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

--解决办法
检查主备库网络，都显示ok,说明主备库网络无问题
tnsping topnet
tnsping topnetdg

重启备库监听
lsnrctl stop
lsnrctl start

复制主库数据到备库

复制主库数据到备库，有2种场景可选，视情况进行选择。

场景1：默认复制数据到备库未开启并行

duplicate target database for standby from active database nofilenamecheck;

详细过程如下：

RMAN> duplicate target database for standby from active database nofilenamecheck;

Starting Duplicate Db at 20-JUN-24
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=20 device type=DISK

contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/u01/app/oracle/product/11.2.0/db/dbs/orapwtopnet1' auxiliary format
 '/u01/app/oracle/product/11.2.0/db/dbs/orapwtopnetdg'   ;
}
executing Memory Script

Starting backup at 20-JUN-24
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=64 instance=topnet1 device type=DISK
Finished backup at 20-JUN-24

contents of Memory Script:
{
   backup as copy current controlfile for standby auxiliary format  '/oradata/topnetdg/controlfile/control01.ctl';
   restore clone controlfile to  '/oradata/topnetdg/controlfile/control02.ctl' from
 '/oradata/topnetdg/controlfile/control01.ctl';
}
executing Memory Script

Starting backup at 20-JUN-24
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0/db/dbs/snapcf_topnet1.f tag=TAG20240620T123540 RECID=1 STAMP=1172147741
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 20-JUN-24

Starting restore at 20-JUN-24
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 20-JUN-24

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

sql statement: alter database mount standby database

contents of Memory Script:
{
   set newname for tempfile  1 to
 "/oradata/topnetdg/tempfile/temp.269.1172048549";
   switch clone tempfile all;
   set newname for datafile  1 to
 "/oradata/topnetdg/datafile/system.275.1172048469";
   set newname for datafile  2 to
 "/oradata/topnetdg/datafile/sysaux.260.1172048469";
   set newname for datafile  3 to
 "/oradata/topnetdg/datafile/undotbs1.278.1172048469";
   set newname for datafile  4 to
 "/oradata/topnetdg/datafile/users.281.1172048469";
   set newname for datafile  5 to
 "/oradata/topnetdg/datafile/undotbs2.261.1172048561";
   backup as copy reuse
   datafile  1 auxiliary format
 "/oradata/topnetdg/datafile/system.275.1172048469"   datafile
 2 auxiliary format
 "/oradata/topnetdg/datafile/sysaux.260.1172048469"   datafile
 3 auxiliary format
 "/oradata/topnetdg/datafile/undotbs1.278.1172048469"   datafile
 4 auxiliary format
 "/oradata/topnetdg/datafile/users.281.1172048469"   datafile
 5 auxiliary format
 "/oradata/topnetdg/datafile/undotbs2.261.1172048561"   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /oradata/topnetdg/tempfile/temp.269.1172048549 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 20-JUN-24
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DATA/topnet/datafile/system.275.1172048469
output file name=/oradata/topnetdg/datafile/system.275.1172048469 tag=TAG20240620T123549
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+DATA/topnet/datafile/sysaux.260.1172048469
output file name=/oradata/topnetdg/datafile/sysaux.260.1172048469 tag=TAG20240620T123549
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=+DATA/topnet/datafile/undotbs1.278.1172048469
output file name=/oradata/topnetdg/datafile/undotbs1.278.1172048469 tag=TAG20240620T123549
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=+DATA/topnet/datafile/undotbs2.261.1172048561
output file name=/oradata/topnetdg/datafile/undotbs2.261.1172048561 tag=TAG20240620T123549
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DATA/topnet/datafile/users.281.1172048469
output file name=/oradata/topnetdg/datafile/users.281.1172048469 tag=TAG20240620T123549
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 20-JUN-24

sql statement: alter system archive log current

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=1172147784 file name=/oradata/topnetdg/datafile/system.275.1172048469
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=1172147784 file name=/oradata/topnetdg/datafile/sysaux.260.1172048469
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=1172147784 file name=/oradata/topnetdg/datafile/undotbs1.278.1172048469
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=1172147784 file name=/oradata/topnetdg/datafile/users.281.1172048469
datafile 5 switched to datafile copy
input datafile copy RECID=5 STAMP=1172147784 file name=/oradata/topnetdg/datafile/undotbs2.261.1172048561
Finished Duplicate Db at 20-JUN-24

场景2：复制数据到备库开启并行

run 
{
allocate channel d1 type disk;
allocate channel d2 type disk;
allocate channel d3 type disk;
allocate channel d4 type disk;
allocate auxiliary channel c1 type disk;
allocate auxiliary channel c2 type disk;
allocate auxiliary channel c3 type disk;
allocate auxiliary channel c4 type disk;
duplicate target database for standby from active database nofilenamecheck;
}

详细过程未做实验。

问题处理

RMAN-04006&ORA-27101

--问题描述
复制主库RAC数据向备份时提示如下报错
RMAN> duplicate target database for standby from active database nofilenamecheck;

Starting Duplicate Db at 20-JUN-24
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 06/20/2024 12:32:58
RMAN-05501: aborting duplication of target database
RMAN-06403: could not obtain a fully authorized session
RMAN-04006: error from auxiliary database: ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory

--问题原因
备库未启动至nomount状态

--解决办法
启动备库
startup nomount

查看复制完数据后备库状态

RMAN复制主库RAC数据到备库，进行同步，数据同步完成后备库从startup nomount变成mount状态。

--查看备库情况
idle 20-JUN-24> set linesize 999
idle 20-JUN-24> select dbid,name,open_mode,database_role from v$database;

      DBID NAME 									    OPEN_MODE							 DATABASE_ROLE
---------- -------------------------------------------------------------------------------- ------------------------------------------------------------ ------------------------------------------------
3403746080 TOPNET									    MOUNTED							 PHYSICAL STANDBY

同步数据

--mount模式启动主备库实时同步（ADG）
alter database recover managed standby database using current logfile disconnect from session;

打开备库至open状态

--取消备库同步主库数据
idle 20-JUN-24> alter database recover managed standby database cancel;

Database altered.

--打开备库至open状态   open备库前必须先停止数据同步
idle 20-JUN-24> alter database open;

Database altered.

--查看备库状态   OPEN_MODE 由mount 转变成了 READ ONLY

idle 20-JUN-24> select dbid,name,open_mode,database_role from v$database;

      DBID NAME 									    OPEN_MODE							 DATABASE_ROLE
---------- -------------------------------------------------------------------------------- ------------------------------------------------------------ ------------------------------------------------
3403746080 TOPNET									    READ ONLY							 PHYSICAL STANDBY

--备库open库后，备库开启实时同步主库数据  OPEN_MODE 由READ ONLY 转变成了 READ ONLY WITH APPLY
idle 20-JUN-24> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

idle 20-JUN-24> select dbid,name,open_mode,database_role from v$database;

      DBID NAME 									    OPEN_MODE							 DATABASE_ROLE
---------- -------------------------------------------------------------------------------- ------------------------------------------------------------ ------------------------------------------------
3403746080 TOPNET									    READ ONLY WITH APPLY					 PHYSICAL STANDBY

验证主备库数据同步

主库创建测试数据

--创建用户
create user pijiake identified by pijiake;
grant resource,connect to pijiake;

--构建测试数据
create table pijiake.t1 as select level as id from dual connect by level<=10;

--查询测试数据
select count(*) from pijiake.t1;

备库观察数据是否同步

主备库数据查询一样即主备库ADG部署完成。

--查询测试数据
select count(*) from pijiake.t1;

同步管理

查看主备库状态

--主库信息   switchover显示to standby或者session active为正常
set linesize 999
col open_mode for a30
select name,open_mode,database_role,protection_mode,switchover_status from v$database;

NAME			       OPEN_MODE		      DATABASE_ROLE				       PROTECTION_MODE						    SWITCHOVER_STATUS
------------------------------ ------------------------------ ------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------
TOPNET			       READ WRITE		      PRIMARY					       MAXIMUM PERFORMANCE					    TO STANDBY


--备库信息
set linesize 999
col open_mode for a30
select name,open_mode,database_role,protection_mode,switchover_status from v$database;

NAME										 OPEN_MODE			DATABASE_ROLE					 PROTECTION_MODE					      SWITCHOVER_STATUS
-------------------------------------------------------------------------------- ------------------------------ ------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------
TOPNET										 READ ONLY WITH APPLY		PHYSICAL STANDBY				 MAXIMUM PERFORMANCE					      NOT ALLOWED

备库停止同步进程

--停止同步进程
sqlplus / as sysdba
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

备库开启实时同步

方法1：使用alter database recover managed standby database delay 120 disconnect from session;
方法2：使用alter system set log_archive_dest_3='service=db3 lgwr async delay=120 valid_for= (all_logfiles,all_roles) db_unique_name=db3';
在ADG服务器上启动一个vip，此vip是RAC的service-ip/scan-ip，这样可省去应用程序修改库连接配置和重启的时间，大大缩减业务中断时长。
观察数据同步延迟情况可以通过查看视图v$standby_event_histogram。

同步验证日志查看

在主库上进行日志切换

ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';
SELECT sequence#, first_time, next_time FROM v$archived_log ORDER BY sequence#;
ALTER SYSTEM SWITCH LOGFILE;

在从库上执行如下语句查看日志是否同步切换到最新

ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';
SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#;

查看切换状态，是否有GAP或目标不可达的情况发生

select switchover_status from v$database;

主库返回to standby或session active，备库返回NOT ALLOWED表示无问题。

日志切换

alter system archive log current;

ADG同步延迟

--观察数据同步延迟
select * from v$standby_event_histogram;

查询当前SCN号

select  to_char(current_scn) from v$database;

查询是否同步

sqlplus / as sysdba
SELECT * FROM V$ARCHIVE_GAP;
SELECT max(sequence#) from v$archived_log where applied='YES';
SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;