单实例Primary快速搭建Standby RAC参考手册(19.16 ADG)

实例,primary,快速,搭建,standby,rac,参考手册,adg · 浏览次数 : 264

小编点评

**备库信息查询** ```sql select col name for a22col value for a22col SOURCE_DB_UNIQUE_NAME for a10set lines 180select * from v$archive_gap; select process, client_process, sequence#, status from v$managed_standby; select sequence#, first_time, next_time, applied from v$archived_log; select archived_thread#, archived_seq#, applied_thread, applied_seq# from v$archive_dest_status; select thread#, max (sequence#) from v$log_history group by thread#; select thread, max (sequence#) from v$archived_log where APPLIED='YES' group by thread#; ``` **资源添加** ```sql --oracle user:crsctl stat res -tsrvctl add database -db demorac -dbname demo -oraclehome /u01/app/oracle/product/19.3.0/db_1 -dbtype RAC -spfile +DATADG/DEMORAC/PARAMETERFILE/spfile.286.1127387689 -role physical_standby -diskgroup DATADG,ARCHDGsrvctl add instance -db demorac -instance jydb1 -node db01rac1srvctl add instance -db demorac -instance jydb2 -node db01rac2srvctl start database -db demorac添加完成后，crsctl stat res -t 显示的资源可以看到我们的备库信息：ora.demorac.db 1 ONLINE ONLINE db01rac1 Open,Readonly,HOME=/ u01/app/oracle/produ ct/19.3.0/db_1,STABL E 2 ONLINE ONLINE db01rac2 Open,Readonly,HOME=/ u01/app/oracle/produ ct/19.3.0/db_1,STABL E至此，单实例Primary快速搭建Standby RAC已完成。。归纳总结以上内容，生成内容时需要带简单的排版

正文

环境：Single Instance -> RAC

Single Instance:

db_name=demo
db_unique_name=demo
instance_name=demo
service_names=demo

RAC（2 nodes）:

db_name=demo
db_unique_name=demorac
instance_name=jydb1、jydb2
service_names=demorac

上述为这里我做为演示环境的基本规划。
本文作为step by step的快速指导手册，方便快速部署此类ADG环境。

依据之前的文章

模拟生产搭建Standby RAC实验环境(11.2.0.4 DG)

历史文章步骤已经很全且描述的很细致，本篇复用之前的章节风格，尽可能简洁化，方便熟练后直接引用。

关于前两部分：这里默认准备就绪：

Single Instance作为初始的primary端，已经安装好19.16的软件和数据库
RAC作为初始的standby端，已经安装好19.16的软件

对于后面的章节，列举如下：

3.主库配置
4.备库配置
5.备库恢复数据库
6.检查同步状态
7.添加资源

3.主库配置

3.1 数据库归档模式

sqlplus / as sysdba
startup mount
alter database archivelog;
alter database open;

3.2 Force Logging

alter database force logging;
select FORCE_LOGGING FROM V$DATABASE;

3.3 主库参数文件修改

使用下面语句查询现在DG相关参数的设置情况：

set linesize 500
col value for a70
col name for a50
 
select name, value
from v$parameter
where name in ('db_name','db_unique_name','log_archive_config', 'log_archive_dest_1','log_archive_dest_2',
               'log_archive_dest_state_1','log_archive_dest_state_2', 'remote_login_passwordfile',
               'log_archive_format','log_archive_max_processes','fal_server','db_file_name_convert',
                     'log_file_name_convert', 'standby_file_management');

修改参数值：

alter system set log_archive_config= 'DG_CONFIG=(demo,demorac)';
alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=demo';
alter system set log_archive_dest_2='SERVICE=demorac VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=demorac';
alter system set log_archive_dest_state_1=enable;
alter system set log_archive_dest_state_2=enable;
alter system set FAL_SERVER='demorac';
alter system set db_file_name_convert='+DATADG/demorac','/flash/oradata/DEMO' scope=spfile;
alter system set log_file_name_convert='+DATADG/demorac','/flash/oradata/DEMO','+ARCHDG/DEMORAC','/flash/fast_recovery_area/DEMO' scope=spfile;
alter system set standby_file_management=AUTO;

3.4 创建SRLs

select member from v$logfile;

alter database add standby logfile thread 1 group 11 '/flash/oradata/DEMO/standby_group_11.log' size 209715200;
alter database add standby logfile thread 1 group 12 '/flash/oradata/DEMO/standby_group_12.log' size 209715200;
alter database add standby logfile thread 1 group 13 '/flash/oradata/DEMO/standby_group_13.log' size 209715200;
alter database add standby logfile thread 1 group 14 '/flash/oradata/DEMO/standby_group_14.log' size 209715200;

alter database add standby logfile thread 2 group 21 '/flash/oradata/DEMO/standby_group_21.log' size 209715200;
alter database add standby logfile thread 2 group 22 '/flash/oradata/DEMO/standby_group_22.log' size 209715200;
alter database add standby logfile thread 2 group 23 '/flash/oradata/DEMO/standby_group_23.log' size 209715200;
alter database add standby logfile thread 2 group 24 '/flash/oradata/DEMO/standby_group_24.log' size 209715200;

select * from v$standby_log;

3.5 备份数据库

采用duplicate方式创建备库，无需手工备份数据库。

3.6 创建备库参数文件

create pfile='pfile_for_standby.txt' from spfile;

默认存在$ORACLE_HOME/dbs目录下。

3.7 更新tnsnames.ora文件

DEMO =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.4)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = demo)
    )
  )

LISTENER_DEMO =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.4)(PORT = 1521))

DEMORAC =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.15)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = demorac)
    )
  )

DEMORAC1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.13)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = demorac)
      (SID = jydb1)
    )
  )

DEMORAC2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.14)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = demorac)
      (SID = jydb2)
    )
  )

LISTENER_DEMORAC1 =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.13)(PORT = 1521))

LISTENER_DEMORAC2 =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.14)(PORT = 1521))

这里需要注意service_name，sid的配置符合部署环境要求，然后同步该文件到备库：

[oracle@bogon admin]$ scp tnsnames.ora 192.168.1.11:/u01/app/oracle/product/19.3.0/db_1/network/admin
[oracle@bogon admin]$ scp tnsnames.ora 192.168.1.12:/u01/app/oracle/product/19.3.0/db_1/network/admin

4.备库配置

4.1 拷贝主库参数文件

拷贝主库参数文件到备库所有节点：

[oracle@bogon dbs]$ scp pfile_for_standby.txt 192.168.1.11:/u01/app/oracle/product/19.3.0/db_1/dbs
[oracle@bogon dbs]$ scp pfile_for_standby.txt 192.168.1.12:/u01/app/oracle/product/19.3.0/db_1/dbs

4.2 拷贝密码文件

拷贝主库密码文件到备库所有节点：

[oracle@bogon dbs]$ scp orapwdemo 192.168.1.11:/u01/app/oracle/product/19.3.0/db_1/dbs/orapwjydb1
[oracle@bogon dbs]$ scp orapwdemo 192.168.1.12:/u01/app/oracle/product/19.3.0/db_1/dbs/orapwjydb2

注意:

密码文件的命名方式orapw<sid>;
如果主库也是RAC，那需要pwcopy命令从ASM磁盘组中拷贝到文件系统上，这里主库是单实例，不需要。

4.3 创建备库需要的目录

# Using Oracle User, On all nodes:
mkdir -p /u01/app/oracle/admin/demorac/adump

4.4 修改备库参数文件

*.audit_file_dest='/u01/app/oracle/admin/demorac/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='+DATADG'
#*.control_files='+DATADG/DEMORAC/CONTROLFILE/current.256.1127325539'#Restore Controlfile
*.db_block_size=8192
*.db_file_name_convert='/flash/oradata/DEMO','+DATADG/DEMORAC'
*.db_name='demo'
*.db_unique_name='demorac'
*.db_recovery_file_dest='+ARCHDG'
*.db_recovery_file_dest_size=15360m
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=demoracXDB)'
*.enable_pluggable_database=true
*.fal_server='demo'
jydb1.local_listener='LISTENER_DEMORAC1'
jydb2.local_listener='LISTENER_DEMORAC2'
*.remote_listener='db01rac-scan:1521'
*.log_archive_config='DG_CONFIG=(demo,demorac)'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=demorac'
*.log_archive_dest_2='SERVICE=demo VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=demo'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_file_name_convert='/flash/oradata/DEMO','+DATADG/DEMORAC','/flash/fast_recovery_area/DEMO','+ARCHDG/DEMORAC'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=2048m
*.processes=2560
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=4096m
*.standby_file_management='AUTO'
*.cluster_database=TRUE
jydb1.instance_number=1
jydb2.instance_number=2
jydb1.thread=1
jydb2.thread=2
jydb1.undo_tablespace='UNDOTBS1'
jydb2.undo_tablespace='UNDOTBS2'

注意：这里添加了新的参数，比如：cluster_database、db_unique_name、remote_listener、jydb1.、jydb2. 等参数，另外按照ADG要求修改之前相关配置，检查确认符合要求即可。

4.5 拷贝tnsnames.ora文件

在3.7步骤中已同步完成，检查已同步成功即可。

4.6 创建ASM相关目录

ASMCMD> mkdir +datadg/DEMORAC
ASMCMD> cd +datadg/DEMORAC
ASMCMD> mkdir PDBSEED PDB1 PDB2

注意：这里测试过，如果不创建这些子目录在duplicate时会报错对应的目录条目不存在。

5.备库恢复数据库

5.1 使用启动实例到nomount状态

sqlplus / as sysdba
startup nomount pfile=$ORACLE_HOME/dbs/pfile_for_standby.txt

5.2 初始化standby数据库

使用duplicate命令创建standby数据库，需要临时配置静态监听。

静态监听配置：

[grid@db01rac1 admin]$ pwd
/u01/app/19.3.0/grid/network/admin
[grid@db01rac1 admin]$ vi listener.ora  尾部增加内容：

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = demorac)
      (ORACLE_HOME = /u01/app/oracle/product/19.3.0/db_1)
      (SID_NAME = jydb1)
    )
  )

[grid@db01rac1 admin]$ lsnrctl reload
[oracle@db01rac1 ~]$ sqlplus sys/oracle@demorac1 as sysdba

使用duplicate命令创建standby数据库:

vi dup.sh 

rman target sys/oracle@demo auxiliary sys/oracle@demorac1 <<EOF
DUPLICATE TARGET DATABASE FOR STANDBY from active database NOFILENAMECHECK;
EOF

[oracle@db01rac1 ~]$ nohup sh dup.sh &
[oracle@db01rac1 ~]$ tail -200f nohup.out

5.3 确认已创建ORLs和SRLs

# 添加RAC实例2的ORLs：
# SQL> recover managed standby database cancel;
alter system set standby_file_management=manual;

alter database add logfile thread 2 group 4 '+DATADG' size 209715200;
alter database add logfile thread 2 group 5 '+DATADG' size 209715200;
alter database add logfile thread 2 group 6 '+DATADG' size 209715200;

# recover managed standby database disconnect;
alter system set standby_file_management=auto;

SELECT MEMBER FROM V$LOGFILE;

# SRLs确认，可以选择删除重建符合要求的。

5.4 确认备库tnsnames.ora

之前已从主库同步过该配置文件。

5.5 启动MRP

RECOVER MANAGED STANDBY DATABASE DISCONNECT;

为了更直观的确保实时同步，可创建一个测试表（选做）

# 创建测试表，验证ADG同步状态；
create table test (id number);
insert into test values(1);
commit;

select * from test;

5.6 创建spfile然后使用spfile启动数据库

指定RAC各个节点的参数文件init.ora，内容统一指向ASM磁盘组中的路径：

create spfile='+datadg' from pfile='$ORACLE_HOME/dbs/pfile_for_standby.txt';

[oracle@db01rac1 dbs]$ cat initjydb1.ora
spfile='+DATADG/DEMORAC/PARAMETERFILE/spfile.286.1127387689'

[oracle@db01rac2 dbs]$ cat initjydb2.ora
spfile='+DATADG/DEMORAC/PARAMETERFILE/spfile.286.1127387689'

在数据库启动时也可以在alert日志中确认使用了正确的参数文件。

6.检查同步状态

可以在备库根据下面的SQL查询相关的信息：

col name for a22
col value for a22
col SOURCE_DB_UNIQUE_NAME for a10
set lines 180
select * from v$dataguard_stats;

同步状态的结果类似如下：

SOURCE_DBID SOURCE_DB_ NAME		      VALUE		     UNIT			    TIME_COMPUTED		   DATUM_TIME			      CON_ID
----------- ---------- ---------------------- ---------------------- ------------------------------ ------------------------------ ------------------------------ ----------
	  0	       transport lag	      +00 00:00:00	     day(2) to second(0) interval   01/29/2023 17:48:12 	   01/29/2023 17:48:12			   0
	  0	       apply lag	      +00 00:00:00	     day(2) to second(0) interval   01/29/2023 17:48:12 	   01/29/2023 17:48:12			   0
	  0	       apply finish time      +00 00:00:00.000	     day(2) to second(3) interval   01/29/2023 17:48:12 						   0
	  0	       estimated startup time 11		     second			    01/29/2023 17:48:12 						   0

其他常用查询语句：

select * from v$archive_gap;
select process, client_process, sequence#, status from v$managed_standby;
select sequence#, first_time, next_time, applied from v$archived_log;
select archived_thread#, archived_seq#, applied_thread#, applied_seq# from v$archive_dest_status;
select thread#, max (sequence#) from v$log_history group by thread#;
select thread#, max (sequence#) from v$archived_log where APPLIED='YES' group by thread#;

7.添加资源

最后我们需要把Standby RAC的数据库和实例都添加到OCR中，使他们能够方便被CRS所管理：

--oracle user:
crsctl stat res -t

srvctl add database -db demorac -dbname demo -oraclehome /u01/app/oracle/product/19.3.0/db_1 -dbtype RAC -spfile +DATADG/DEMORAC/PARAMETERFILE/spfile.286.1127387689 -role physical_standby -diskgroup DATADG,ARCHDG
srvctl add instance -db demorac -instance jydb1 -node db01rac1
srvctl add instance -db demorac -instance jydb2 -node db01rac2
srvctl start database -db demorac

添加完成后，crsctl stat res -t 显示的资源可以看到我们的备库信息：

ora.demorac.db
      1        ONLINE  ONLINE       db01rac1                 Open,Readonly,HOME=/
                                                             u01/app/oracle/produ
                                                             ct/19.3.0/db_1,STABL
                                                             E
      2        ONLINE  ONLINE       db01rac2                 Open,Readonly,HOME=/
                                                             u01/app/oracle/produ
                                                             ct/19.3.0/db_1,STABL
                                                             E

至此，单实例Primary快速搭建Standby RAC已完成。

单实例Primary快速搭建Standby RAC参考手册(19.16 ADG)

小编点评

正文

3.主库配置

4.备库配置

5.备库恢复数据库

6.检查同步状态

7.添加资源

与单实例Primary快速搭建Standby RAC参考手册(19.16 ADG) 相似的内容：

单实例Primary快速搭建Standby RAC参考手册(19.16 ADG)

19c ADG Switchover 切换测试

Linux平台Oracle 23c单实例安装部署配置快速参考

验证ADG的坏块检测和自动修复

数据库系列：数据库高可用及无损扩容

Redis集群介绍及测试思路

从Spring源码看Spring如何解决循环引用的问题

Spring源码——初识Spring容器

Singleton Pattern 单例模式简介与 C# 示例【创建型5】【设计模式来了_5】

Java JVM——10.对象实例化、内存布局与访问定位

# 热门排行