Windows环境下19C一主二备的ABB架构的物理DG搭建

时光旅人啦 08-02 161

前言：

今天你们对“sqlplus登录提示协议适配器错误”大约比较珍视，大家都需要分析一些“sqlplus登录提示协议适配器错误”的相关资讯。那么小编在网络上汇集了一些有关“sqlplus登录提示协议适配器错误””的相关内容，希望同学们能喜欢，咱们一起来学习一下吧！

概览

架构为一主对二备

安装参考文档：

Step by Step Guide on Creating Physical Standby Using RMAN DUPLICATE...FROM ACTIVE

DATABASE (文档 ID 1075908.1)

安装主库

（下载19.3安装包，单机，安装步骤略，win下不用配置参数，不用设置环境变量，直接下一步下一步即可）

打19.7的补丁。

改主库为归档模式

SQL> alter system set log_archive_dest_1= 'location=C:\arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=ora19c';

SQL> startup mount;

SQL> alter database archivelog;

SQL> alter database open;

改主库为强制日志模式

SQL> select force_logging from v$database;

FORCE_LOGGING

---------------------------------------

SQL> alter database force logging;

数据库已更改。

SQL> select force_logging from v$database;

FORCE_LOGGING

---------------------------------------

YES

主库添加备用联机日志文件

Standby redo log数量要比源库的redo log 多一个，如果是2节点RAC就多2个，3节点RAC就多3个，以此类推，standby redo大小要至少大于online redo的最大的值，建议全都弄成一样的。详见官档《Oracle® Data Guard Concepts and Administration 19C》7.2.3.1 Managing Standby Redo Logs

SQL> alter database add standby logfile group 4 ('C:\ORACLE\ORADATA\ORA19C\REDO04.LOG')size 200m;

SQL> alter database add standby logfile group 5 ('C:\ORACLE\ORADATA\ORA19C\REDO05.LOG')size 200m;

SQL> alter database add standby logfile group 6 ('C:\ORACLE\ORADATA\ORA19C\REDO06.LOG')size 200m;

SQL> alter database add standby logfile group 7 ('C:\ORACLE\ORADATA\ORA19C\REDO07.LOG')size 200m;

设置log_archive_config参数

SQL> alter system set log_archive_config='dg_config=(ora19c,ora19cs,ora19cst)';

设置log_archive_dest_n参数

SQL> alter system set log_archive_dest_2='service=ora19cs lgwr async valid_for=(online_logfile,primary_role) compression=enable db_unique_name=ora19cs';

SQL> alter system set log_archive_dest_3='service=ora19cst lgwr async valid_for=(online_logfile,primary_role) compression=enable db_unique_name=ora19cst';

设置fal_server，fal_client，standby_file_management参数

SQL> alter system set fal_server=ora19cs,ora19cst;

SQL> alter system set fal_client=ora19c;

SQL> alter system set standby_file_management=auto;

改standby_file_management这个参数为auto是为了方便管理，主库添加数据文件可以自动在备库也建。

见19C官档的9.3.1 Adding a Datafile or Creating a Tablespace

设置DB_FILE_NAME_CONVERT，LOG_FILE_NAME_CONVERT参数

SQL> ALTER SYSTEM SET db_file_name_convert='C:\ORACLE\ORADATA\ORA19CS','C:\ORACLE\ORADATA\ORA19C','C:\ORACLE\ORADATA\ORA19CST','C:\ORACLE\ORADATA\ORA19C' SCOPE=SPFILE;

SQL> ALTER SYSTEM SET log_file_name_convert='C:\ORACLE\ORADATA\ORA19CS','C:\ORACLE\ORADATA\ORA19C','C:\ORACLE\ORADATA\ORA19CST','C:\ORACLE\ORADATA\ORA19C' SCOPE=SPFILE；

重启主库

SQL> shutdown immediate

SQL> startup

创建pfile，为备库创建做准备

SQL> create pfile='C:\oracle\ora19c_pfile.ora' from spfile;

配置tnsnames

ora19cs=

(DESCRIPTION=

(ADDRESS_LIST=

(ADDRESS=(PROTOCOL=TCP)(HOST=10.71.232.102)(PORT=1521))

)

(CONNECT_DATA=

(SID=ora19cs)

(server=dedicated)

)

ora19cst=

(DESCRIPTION=

(ADDRESS_LIST=

(ADDRESS=(PROTOCOL=TCP)(HOST=10.71.232.103)(PORT=1521))

)

(CONNECT_DATA=

(SID=ora19cst)

(server=dedicated)

)

安装备库1（只装软件不要建库）

1. 打19.7补丁

更换opatch工具，打补丁

C>net stop msdtc

D> set PATH=%ORACLE_HOME%\perl\bin;%PATH%

D>set PERL5LIB=<回车>

D>cd C:\software\p30901317_190000_MSWIN-x86-64\30901317

D>C:\software\WINDOWS.X64_193000_db_home\OPatch\opatch.bat apply

配置静态监听

从主库那里拷贝一份过来，在相同的路径下，根据实际修改为备库的监听。只修改如下标蓝底的部分。

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = ora19cs)

(ORACLE_HOME = C:\software\WINDOWS.X64_193000_db_home)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.71.232.102)(PORT = 1521))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

)

从11gR2配置DG的经验来看，根据Doc ID 1645680.1里面指出，要把PROGRAM和ENVS去掉。提前去掉。

启动监听

C> lsnrctl start

配置tnsnames

直接把主库的tnsnames拷贝过来，放到相同的路径下。

根据主库的参数调整备库的参数文件

把主库上生成的pfile拷贝过来到任意目录

根据pfile创建spfile

增加或修改如下内容：

根据参数文件的路径，在备1库上创建所需要的目录

audit_file_dest='C:\oracle\admin\ora19cs\adump'

db_unique_name=ora19cs

control_files='C:\oracle\oradata\ORA19CS\control01.ctl','C:\oracle\oradata\ORA19CS\control02.ctl'

db_file_name_convert='C:\ORACLE\ORADATA\ORA19C','C:\ORACLE\ORADATA\ORA19CS','C:\ORACLE\ORADATA\ORA19CST','C:\ORACLE\ORADATA\ORA19CS'

dispatchers='(PROTOCOL=TCP) (SERVICE=ora19csXDB)'

fal_client='ORA19CS'

fal_server='ORA19C, ORA19CST '

local_listener='LISTENER_ORA19CS'

log_archive_dest_2='service=ora19c lgwr async valid_for=(online_logfile,primary_role) compression=enable db_unique_name=ora19c'

log_archive_dest_3='service=ora19cst lgwr async valid_for=(online_logfile,primary_role) compression=enable db_unique_name=ora19cst'

log_file_name_convert='C:\ORACLE\ORADATA\ORA19C','C:\ORACLE\ORADATA\ORA19CS','C:\ORACLE\ORADATA\ORA19CST','C:\ORACLE\ORADATA\ORA19CS'

起库到nomount状态

(win下有个必须步骤：oradim -new -sid ora19cs)

C>oradim -new -sid ora19cs

C>set ORACLE_SID=ora19cs

C>sqlplus / as sysdba

SQL> startup nomount pfile='C:\ORA19C_PFILE.ORA'

创建密钥文件

（直接从主库拷贝过来再重命名一下就可以，win的密钥文件在ORACLE_HOME\database下）

C>orapwd file=C:\software\WINDOWS.X64_193000_db_home\database\PWDora19cs.ora password=Yvdq97*19

也可以这样直接创建

Duplicate到备库

C:\Users\Administrator>tnsping ora19c

TNS Ping Utility for 64-bit Windows: Version 19.0.0.0.0 - Production on 10-8月 -2020 15:53:04

已使用的参数文件:

C:\software\WINDOWS.X64_193000_db_home\network\admin\sqlnet.ora

已使用 TNSNAMES 适配器来解析别名

尝试连接 (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = WIN-QDIA2810B8U)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ora19c)))

OK (20 毫秒)

C:\Users\Administrator>tnsping ora19cs

TNS Ping Utility for 64-bit Windows: Version 19.0.0.0.0 - Production on 10-8月 -2020 15:53:10

已使用的参数文件:

C:\software\WINDOWS.X64_193000_db_home\network\admin\sqlnet.ora

已使用 TNSNAMES 适配器来解析别名

尝试连接 (DESCRIPTION= (ADDRESS_LIST= (ADDRESS=(PROTOCOL=TCP)(HOST=10.71.232.102)(PORT=1521))) (CONNECT_DATA= (SID=ora19cs) (server=dedicated)))

OK (0 毫秒)

C:\Users\Administrator>sqlplus sys/oracle@ora19c AS SYSDBA

SQL*Plus: Release 19.0.0.0.0 - Production on 星期一 8月 10 16:25:06 2020

Version 19.7.0.0.0

连接到:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.7.0.0.0

SQL> exit

从 Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.7.0.0.0 断开

C:\Users\Administrator>sqlplus sys/oracle@ora19cs AS SYSDBA

SQL*Plus: Release 19.0.0.0.0 - Production on 星期一 8月 10 16:25:17 2020

Version 19.7.0.0.0

连接到:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.7.0.0.0

SQL> exit

从 Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.7.0.0.0 断开

Duplicacte，主库发起命令：

C:\Users\Administrator>rman target sys/Yvdq97*19@ora19c auxiliary=sys/Yvdq97*19@ora19cs

恢复管理器: Release 19.0.0.0.0 - Production on 星期二 8月 11 16:29:19 2020

Version 19.7.0.0.0

已连接到目标数据库: ORA19C (DBID=1101018689)

已连接到辅助数据库: ORA19C (未装载)

RMAN>

run{

allocate channel chan1 type disk;

allocate channel chan2 type disk;

allocate channel chan3 type disk;

allocate channel chan4 type disk;

allocate auxiliary channel aux1 type disk;

allocate auxiliary channel aux2 type disk;

allocate auxiliary channel aux3 type disk;

allocate auxiliary channel aux4 type disk;

duplicate target database for standby from active database dorecover nofilenamecheck;

}

备库1开启日志应用模式

SQL> alter database recover managed standby database using current logfile disconnect;

数据库已更改

查看日志应用状态

SQL> set linesize 150;

SQL> set pagesize 200;

SQL> column PROCESS format a7;

SQL> column STATUS format a15;

SQL> column client_pid format a12;

SQL> column CLIENT_PROCESS format a12;

SQL> select pid,process,status,client_process,client_pid,

2 thread#,sequence# seq#,block#,blocks

3 from v$managed_standby;

PID PROCESS STATUS CLIENT_PROCE CLIENT_PID THREAD# SEQ# BLOCK# BLOCKS

------------------------ ------- --------------- ------------ ------------ ---------- ---------- ---------- ----------

4940 DGRD ALLOCATED N/A N/A 0 0 0 0

2612 ARCH CONNECTED ARCH 2612 0 0 0 0

4692 DGRD ALLOCATED N/A N/A 0 0 0 0

4212 ARCH CONNECTED ARCH 4212 0 0 0 0

3784 ARCH CONNECTED ARCH 3784 0 0 0 0

3248 ARCH CONNECTED ARCH 3248 0 0 0 0

1164 RFS IDLE Archival 980 1 0 0 0

3176 RFS IDLE LGWR 1116 1 35 12869 1

3324 MRP0 APPLYING_LOG N/A N/A 1 35 12869 409600

已选择 9 行。

安装备库2

步骤同安装备库1，记得改下相应参数

如果是配置ABC级联的架构，C也要从Aduplicate,但是最后还需要修改下C的参数，使C从B接受日志。

每天进步一点点

本文地址：http://www.longkongtuishu.com/cacc8BAZsBFcDD1JQ.html

标签： #sqlplus登录提示协议适配器错误

Windows环境下19C一主二备的ABB架构的物理DG搭建

Windows环境下19C一主二备的ABB架构的物理DG搭建

云平台安装11g rac haip问题导致节点2执行root.sh失败