此方法已工作中實際使用，樣板是借用，修改后與工作實施過程保持一致！！！！

需求：

出于安全原因，,參數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日志正常傳輸。