前言:
今天你们对“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
---------------------------------------
NO
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
Copyright (c) 1997, 2020, Oracle. All rights reserved.
已使用的参数文件:
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
Copyright (c) 1997, 2020, Oracle. All rights reserved.
已使用的参数文件:
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
Copyright (c) 1982, 2020, Oracle. All rights reserved.
连接到:
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
Copyright (c) 1982, 2020, Oracle. All rights reserved.
连接到:
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
Copyright (c) 1982, 2020, Oracle and/or its affiliates. All rights reserved.
已连接到目标数据库: 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接受日志。
每天进步一点点
标签: #sqlplus登录提示协议适配器错误