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

需求：

出于安全原因，,參數(shù)redo_transport_user,是定義傳輸redo日志的用戶,默認(rèn)使用sys用戶傳輸,不希望僅將如此高特權(quán)的用戶用于重做傳輸,故生產(chǎn)環(huán)境下需要更改其他用戶傳輸。

Oracle手冊：傳輸用戶需要有SYSDBA或者SYSOPER權(quán)限,但是通過實(shí)際測試來看,只有SYSDBA是不行的,必須有SYSOPEN權(quán)限才行。

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.值為空，默認(rèn)使用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
--//發(fā)現(xiàn)這個(gè)視圖直接讀取的是口令文件，需要授權(quán)sysoper權(quán)限

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.測試是否傳輸日志,重啟主數(shù)據(jù)庫看看:
--//備庫:

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.

設(shè)置備庫端指向主庫歸檔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狀態(tài)均正常

--//傳輸正常!用戶yangzhuangzhuang?正常做傳輸undo,不需要拷貝口令文件過qu

3.這樣做的好處:

--//安全,用戶yangzhuangzhuang沒有權(quán)限登錄數(shù)據(jù)庫.
$ 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.除了一些操作數(shù)據(jù)庫的管理權(quán)限,其它建表相關(guān)的的操作不允許.
$ 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.繼續(xù)測試

使用oracle一體機(jī)zdlra做備份，安裝客戶端時(shí)把redo_transport_user值改為yangzhuangzhuang666，primary庫redo日志傳輸會(huì)報(bào)錯(cuò)(TT00 (PID:2815): Error 12543 received logging on to the standby)，需要在主庫創(chuàng)建yangzhuangzhuang888用戶，拷貝密碼倒備庫，重啟日志實(shí)時(shí)應(yīng)用
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

啟動(dòng)備庫，查看密碼文件
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

啟動(dòng)實(shí)時(shí)應(yīng)用，redo日志正常傳輸。