修改redo默认传输用户sys到其他---redo_transport_user参数

此方法已工作中实际使用，样板是借用，修改后与工作实施过程保持一致！！！！

需求：

出于安全原因，,参数redo_transport_user,是定义传输redo日志的用户,默认使用sys用户传输,不希望仅将如此高特权的用户用于重做传输,故生产环境下需要更改其他用户传输。

Oracle手册：传输用户需要有SYSDBA或者SYSOPER权限,但是通过实际测试来看,只有SYSDBA是不行的,必须有SYSOPEN权限才行。

1.建立redo_transport_user的用户:

SYS@book> drop user a cascade;
User dropped.

SYS@book> show parameter redo_transport_user
NAME                     TYPE          VALUE
-------------------     ----------   --------
redo_transport_user  string 

--//缺省没有定义redo_transport_user.值为空，默认使用sys用户

--//主库:

SYS@book> create user yangzhuangzhuang identified by yzz666;
User created.

SYS@book> grant SYSOPER to yangzhuangzhuang ;
Grant succeeded.

USERNAME                 SYSDBA    SYSOPER   SYSAS
--------------------              -------          -----               -----
SYS                              TRUE        TRUE            FALSE
yangzhuangzhuang     FALSE       TRUE             FALSE

SYS@book> alter system set redo_transport_user=yangzhuangzhuang;
System altered.

SYS@book> show parameter redo_transport_user
NAME                      TYPE        VALUE
-------------------     ----------  ------
redo_transport_user   string       yangzhuangzhuang 

2.备库检查:

--//备库:
SYS@bookdg> show parameter  redo
NAME                       TYPE        VALUE
-------------------      ----------  ------
redo_transport_user   string

SQL> show parameter undo_

NAME                                 TYPE        VALUE

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

temp_undo_enabled                    boolean     FALSE

undo_management                      string      AUTO

undo_retention                         integer      900

undo_tablespace                        string       UNDOTBS1

SQL>

SYS@bookdg> alter system set redo_transport_user=yangzhuangzhuang ;
System altered.

SYS@bookdg> show parameter  redo
NAME                     TYPE       VALUE
-------------------    -------   ----------
redo_transport_user  string     yangzhuangzhuang 

SYS@bookdg> select * from v$pwfile_users ;
USERNAME             SYSDB  SYSOP SYSAS
-------------------- ----- ----- -----
SYS                       TRUE   TRUE  FALSE
--//发现这个视图直接读取的是口令文件，需要授权sysoper权限

SYS@bookdg> grant sysoper to yangzhuangzhuang ;
Grant succeeded.

SYS@bookdg> select USERNAME,SYSDBA,SYSOPER,SYSASM from v$pwfile_users ;
USERNAME                SYSDBA    SYSOPER    SYSAS
--------------------      -----        -----       -----
SYS                          TRUE        TRUE       FALSE
yangzhuangzhuang    FALSE       TRUE       FALSE

4.测试是否传输日志,重启主数据库看看:
--//备库:

SYS@bookdg> startup
ORACLE instance started.
Total System Global Area  634732544 bytes
Fixed Size                  2255792 bytes
Variable Size             197133392 bytes
Database Buffers          427819008 bytes
Redo Buffers                7524352 bytes
Database mounted.
Database opened.

设置备库端指向主库归档enable

SYS@bookdg> alter system set log_archive_dest_state_2=enable scope=memory;
System altered.

主库切归档

SYS@book> alter system swtich logfile;
System altered.

SYS@book> /
System altered.

SYS@book> /
System altered.

SYS@book> /
System altered.

查看归档最大Current log sequence主备始终保持一致

SYS@book> archive log list ;

Database log mode                Archive Mode

Automatic archival                 Enabled

Archive destination                /home/oracle/app/arch

Oldest online log sequence         61468

Next log sequence to archive       119473

Current log sequence              119473

查看无延迟无gap,dg状态均正常

--//传输正常!用户yangzhuangzhuang 正常做传输undo,不需要拷贝口令文件过qu

3.这样做的好处:

--//安全,用户yangzhuangzhuang没有权限登录数据库.
$ sql -s -l yangzhuangzhuang/yangzhuangzhuang666@test
ERROR:
ORA-01045: user A lacks CREATE SESSION privilege; logon denied
SP2-0751: Unable to connect to Oracle.  Exiting SQL*Plus

--//如果使用as sysoper登录,显示用户是PUBLIC.除了一些操作数据库的管理权限,其它建表相关的的操作不允许.
$ rlsql  a/a#test as sysoper
SQL*Plus: Release 11.2.0.4.0 Production on Wed Jan 3 10:11:31 2018
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

PUBLIC@book> create table a ( b number);--无法建表
create table a ( b number)
*
ERROR at line 1:
ORA-01031: insufficient privileges

4.继续测试

使用oracle一体机zdlra做备份，安装客户端时把redo_transport_user值改为yangzhuangzhuang666，primary库redo日志传输会报错(TT00 (PID:2815): Error 12543 received logging on to the standby)，需要在主库创建yangzhuangzhuang888用户，拷贝密码倒备库，重启日志实时应用
SQL> show parameter redo;

NAME     TYPE             VALUE
-------     ------            ----------

redo_transport_user string   yangzhuangzhuang888

SQL> create user yangzhuangzhuang888 identified by yangzhuangzhuang888;
SQL> grant sysoper,create session to yangzhuangzhuang888;
SQL> col username for a10;
SQL>select a.USERNAME,a.SYSOPER,a.ACCOUNT_STATUS from v$pwfile_users a;

USERNAME               SYSOP    ACCOUNT_STATUS
----------                  -----      ---------------------
SYS                       TRUE      OPEN
yangzhuangzhuang888    TRUE      OPEN

拷贝文件至备库：

scp 10.10.10.10:/u01/app/db/product/19.3/db_1/dbs/orapwqdtais1 ./dbs

启动备库，查看密码文件
SQL> startup
ORACLE instance started.

Total System Global Area 1476391080 bytes
Fixed Size 8896680 bytes
Variable Size 369098752 bytes
Database Buffers 1090519040 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.
SQL> col username for a10;
SQL> select a.USERNAME,a.SYSOPER,a.ACCOUNT_STATUS from v$pwfile_users a;

USERNAME               SYSOP   ACCOUNT_STATUS
----------                  -----      ------------------------------
SYS                       TRUE      OPEN
yangzhuangzhuang888    TRUE      OPEN

启动实时应用，redo日志正常传输。