oracle 11g Dataguard 之 Remote_Transport_user
来源：互联网 发布：去他妈的全世界 知乎 编辑：程序博客网 时间：2024/05/18 14:23
1）背景：
当配置Dataguard SYS密码会一直变化的时候如何保证Dataguard主库到备库的日志传输和通信？
因为有些金融公司的Security 要求比较高，对数据库用户的密码权限回收，若果在配置11g active dataguard如果使用了sys账号用作redo传输的用户，默认数据库的redo transport user 为空表示该用户为SYS。
如果sys密码一直在变那会出现如下错误：
Error 1017 received logging on to the standby------------------------------------------------------------Check that the primary and standby are using a password fileand remote_login_passwordfile is set to SHARED or EXCLUSIVE, and that the SYS password is same in the password files. returning error ORA-16191------------------------------------------------------------FAL[client, ARC0]: Error 16191 connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname)(PORT=1521))(CONNECT_DATA=
(SERVICE_NAME=dg_std)(SERVER=DEDICATED))) for fetching gap sequence
2）解决：
更改redo transport user 为密码不变用户（有些公司的账号管理对于密码保持不变的，需要通过设置service id来实现密码固定策略）
3）测试：
以下Demo只为记录，转载自网络，方便大家有个整体过程的体验。
1. In this case transport service running on Primary database, Standby database opened Read Only With Apply (Active Data Guard is running).REDO_TRANSPORT_USERparameter is not set on both side.
On primary side:
Alter log of primary database:
On standby side :
Alert log of standby database:
Primary database is in MAXIMUM PERFORMANCE mode ASYNC transport going normally. Now I'm changing SYS password of primary.
Alert log of primary database:
Yes, we are getting error when changed SYS password. I coping primary password for standby with Linux copy command (cp)
Trying again.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
310
Alert log of primary database:
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2 ******************************************************************
Wed May 01 12:19:06 2013 Archived Log entry 614 added for thread 1 sequence 309 ID 0xf23a6e3f dest 1: LNS: Standby redo logfile selected for thread 1 sequence 310 for destination LOG_ARCHIVE_DEST_2 Wed May 01 12:19:07 2013 ARC3: Standby redo logfile selected for thread 1 sequence 309 for destination LOG_ARCHIVE_DEST_2 Thread 1 cannot allocate new log, sequence 311 Checkpoint not complete Current log# 1 seq# 310 mem# 0: /u01/app/oracle/oradata/admdb/redo01.log Thread 1 advanced to log sequence 311 (LGWR switch) Current log# 2 seq# 311 mem# 0: /u01/app/oracle/oradata/admdb/redo02.log
On Standby:
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
310
Alert log of standby database:
Transport continue normally after copy password file of primary database to standby side.
2. In this case I create a user and granting SYSOPER and setting REDO_TRANSPORT_USER to this user.
On primary side :
SQL> create user RTU identified by rtu; User created.
SQL> grant SYSOPER to RTU;
Grant succeeded.
SQL> select * from v$PWFILE_USERS;
USERNAME SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS TRUE TRUE FALSE RTU FALSE TRUE FALSE
SQL> alter system set REDO_TRANSPORT_USER='RTU';
System altered.
SQL> show parameter REDO_TRANSPORT_USER NAME TYPE VALUE
------------------ --------- ---------------
redo_transport_user string RTU
Alert log of primary database:
On Standby side :
SQL> select username from all_users where username ='RTU';
USERNAME
------------------------------
RTU
SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS TRUE TRUE FALSE
SQL> grant SYSOPER to RTU;
Grant succeeded.
SQL> alter system set REDO_TRANSPORT_USER=RTU;
System altered.
Now we can check, transport process.
On primary:
SQL> alter system switch logfile;
System altered.
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
314
On standby side :
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
314
Redo transport service using RTU user for transport redo from primary to standby database. I changing
SYS user password on primary again.
SQL> alter user sys identified by SYSPass2;
User altered.
On primary :
SQL> alter system switch logfile; System altered.
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
315
On Standby side :
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
315
It means transport is not stopped. If we change RTU user’s password then Redo Transport will stop, Because RTU user is privileged SYSOPER and this password change must be on password file. It means, if we change RTU user’s password we must copy password file from primary to standby side, again.ConclusionPassword files must be same for Data Guard Configuration databases. In a Data Guard configuration, all physical and snapshot standby databases must use a copy of the password file from the primary database, and that copy must be refreshed whenever the SYSOPER or SYSDBA privilege is granted or revoked, and after the password of any user with these privileges is changed.
Regards
Mahir M. Quluzade
- oracle 11g Dataguard 之 Remote_Transport_user
- 探索Oracle之11g DataGuard 配置
- oracle 11g dataguard
- Oracle Study之案例--Oracle 11g DataGuard Snapshot Standby
- Oracle 11g DataGuard 配置
- oracle 11g dataguard 搭建
- oracle 11g dataguard配置
- ORACLE 11g DataGuard切换
- oracle-11g-配置dataguard
- Oracle 11g dataguard介绍
- Oracle 11g R2之Dataguard搭建物理standby
- Oracle 11g R2之物理Dataguard 重命名数据文件
- ORACLE 11G 之DATAGUARD搭建物理standby
- ORACLE 11G 之DATAGUARD搭建逻辑standby
- ORACLE 11G DATA GUARD配置之Dataguard简介
- ORACLE 11G DATA GUARD配置之Dataguard基本原理
- 配置Oracle 11g Active Dataguard
- Oracle 11g DataGuard物理standby配置
- java JDBC 增删改查 总结
- 关于EF上线文异常问题整理
- 使用OPC UA的十个原因
- E:before E:after 插入项目编号
- MongoDb的学习
- oracle 11g Dataguard 之 Remote_Transport_user
- CADisplayLink和NSTimer的区别
- js表单验证
- 如何让Perl脚本同时只运行一个实例
- 商品数量很少 如何打造完美电商网站
- Infragistics组件-UltraGrid对象间关系总结
- Nginx从开发到精通
- mac 搭建hadoop设置JAVA_HOME
- [心得] 趋势！海康威视告诉你下一代电子警察什么样
