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

已于 2024-06-20 17:18:26 修改
阅读量477 收藏 8
点赞数 3
文章标签： 服务器 centos 数据库
于 2024-06-20 14:31:02 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq961573863/article/details/139831964
版权

服务器信息

主库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

topnet

db_unique_name

topnetdg

instance_name

topnetdg

备库数据文件存放路径

/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;

参考链接：搭建记录：duplicate搭建oracle rac到单实例搭建物理DG_V7 - 墨天轮

董小姐yyds
关注
  • 3
    点赞
  • 踩
  • 8
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
Oracle 11g ADG 搭建
djwnb666的博客
11-21 1068
Oracle的ADG搭建，11g的，希望可以帮到你，很多照片传不上来不知道为什么，见谅
oracle11g 搭建 ADG
qq_24461637的博客
08-20 3751
1.实验环境   主库 备库 操作系统版本 CentOS 7.4 CentOS 7.4 数据库版本 11.2.0.4 11.2.0.4 实例名称 orclpri ...
11g ADG环境搭建
cuizhi4718的博客
09-05 214
一、primary (实例名：jzh) standby(实例名：jyp) IP地址：192.168.1.200 192.168.1.199 主机名：...
创建ADG(RAC--＞单实例（duplicate）)
Baldwin的博客
05-28 826
run { allocate channel ch00 type disk; allocate channel ch01 type disk; allocate channel ch02 type disk; allocate channel ch03 type disk; set newname for datafile 1 to '/u01/app/oracle/oradata/system.dbf'; set newname for datafile 2 to '/u01/app/oracle/ora
RAC+ADG(单节点ADG)
Baldwin的博客
03-31 6643
RAC+ADG有两种存储数据文件的形式，一个是将DG的数据文件放在ASM上，另一个是将数据文件放在本地磁盘上 本实验做的是数据放在本地磁盘上。 在搭建RAC+DG之前，请确保RAC是没有问题的 1.检查集群状态 grid用户下执行： crsctl status res -t RAC Primary RACDB1（主机） RACDB2（主机） 备注 Public IP 192....
ORACLE 11g_RAC部署方案
10-12
Oracle 11g RAC（Real Application Clusters）部署方案是一项关键任务，它涉及在多台服务器上构建一个高可用性和高性能的数据库集群。RAC允许多个实例同时访问同一个数据库，提供容错能力和负载均衡，是Oracle数据库...
oracle rac to rac adg 生产案例，某三甲医院部署场景
03-23
在这个案例中，我们看到一个使用Oracle 11g数据库和HPUX 11.31操作系统的环境，通过两台新购HP小型机构建生产库，两台利旧小型机用于容灾库，采用传统SAN存储架构，以及Oracle RAC 11g软件架构。 **Oracle RAC ...
搭建Oracle 11gR2单实例+ASM实例的生产与测试环境
10-10
2. **下载与准备**：从Oracle官网下载适用于你操作系统的数据库软件，如Linux x86_64平台的Oracle Database 11g Release 2 (11.2.0.4)。同时，创建一个合适的安装目录并解压软件包。 3. **运行安装程序**：使用root...
Oracle 11g RAC下DataGuard容灾(2+1)实施部署实战视频教程
06-13
本套课程讲解Oracle 11gR2 RAC+单实例Active Dataguard(ADG)备库，属于企业中的高端应用场景，企业中经常使用的一种方案，即Oracle高可用解决方案RAC+高安全数据容灾解决方案Dataguard。
[手工整理]208个Oracle安装文档，包含各个平台各个版本的单实例RAC以及DataGuard(1).xlsx
05-26
10 ADG Linux+Oracle 11g+RAC+12cc+adg国内业界最详细生产系统下实施文档 11 ADG Linuxel6.5 RAC+DG11204bestpratice 12 ADG Linuxel6.5 RAC+DG11204脚本安装 13 ADG Oracle 10g DataGuard实施文档 14 ADG ...
rac+单实例adg搭建步骤
10-20
在Linux环境下，如何实现RAC+单实例adg的搭建步骤详情
构建最高可用Oracle数据库系统 RAC和ADG环境搭建文档
01-14
构建最高可用Oracle数据库系统 环境搭建文档，包括RAC、dataguard、broker
构建最高可用Oracle数据库系统RAC和ADG环境搭建文档.pdf
07-04
构建最高可用Oracle数据库系统RAC和ADG环境搭建文档。
11g手工搭建ADG
congjiaax703023的博客
07-05 98
主库：开启归档模式 SYS@PROD1> archive log list; Database log mode No Archive Mode Automatic archiv...
ADG系列之oracle rac到单机的ADG环境搭建
weixin_34205826的博客
11-08 820
地址规划：RAC： 1.1.1.62 rac5-vip 1.1.1.64 rac6-vipdb_name hxzgdb_unique_name hxzgrac5-dg 1.1.1.68db_name hxzgdb_unique_name hxzgdg归档路径:/archivelog数据文件路径：/oradata/hxzg/一、基本环境准备1、确认机器为归档模式a...
oracle 11g rac adg搭建,ADG系列之oracle rac到单机的ADG环境搭建
weixin_42342010的博客
04-05 500
地址规划：RAC： 1.1.1.62 rac5-vip1.1.1.64 rac6-vipdb_name hxzgdb_unique_name hxzgrac5-dg 1.1.1.68db_name hxzgdb_unique_name hxzgdg归档路径:/archivelog数据文件路径：/oradata/hxzg/一、基本环境准备1、确认机器为归档模式archive log list2、开...
Centos7 oracle 11G 搭建ADG【亲测可用，全网最全教程】
最新发布
AKA石头
07-17 1879
Centos7 oracle 11G 搭建ADG【亲测可用，全网最全教程】
Oracle 11G ADG 搭建 RAC to Single 详细教程（RMAN DUPLICATE）
热门推荐
LuciferLiu_DBA
12-06 1万+
Oracle ADG

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

董小姐yyds

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值