[20180103]11G redo_transport_user参数.txt
--//11G 存在参数redo_transport_user,可以定义传输日志的用户,而不使用sys用户传输.测试看看.
1.环境:
SYS@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
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.
2.建立redo_transport_user的用户:
SYS@book> create user a identified by a#test;
User created.
SYS@book> grant SYSOPER to a;
Grant succeeded.
SYS@book> select * from v$pwfile_users ;
USERNAME SYSDB SYSOP SYSAS
-------------------- ----- ----- -----
SYS TRUE TRUE FALSE
A FALSE TRUE FALSE
SYS@book> alter system set redo_transport_user=A;
System altered.
SYS@book> show parameter redo_transport_user
NAME TYPE VALUE
------------------- ---------- ------
redo_transport_user string A
3.备库检查:
--//备库:
SYS@bookdg> show parameter redo
NAME TYPE VALUE
------------------- ---------- ------
redo_transport_user string
SYS@bookdg> alter system set redo_transport_user=A;
System altered.
SYS@bookdg> show parameter redo
NAME TYPE VALUE
------------------- ---------- -------
redo_transport_user string A
SYS@bookdg> select * from v$pwfile_users ;
USERNAME SYSDB SYSOP SYSAS
-------------------- ----- ----- -----
SYS TRUE TRUE FALSE
--//嗯,奇怪这里不同步吗?难道这个视图直接读取的是口令文件.
SYS@bookdg> grant sysoper to a;
Grant succeeded.
SYS@bookdg> select * from v$pwfile_users ;
USERNAME SYSDB SYSOP SYSAS
-------------------- ----- ----- -----
SYS TRUE TRUE FALSE
A FALSE TRUE FALSE
3.测试是否传输日志,我重启主数据库看看:
SYS@book> 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.
SYS@book> alter system set log_archive_dest_state_2=enable scope=memory;
System altered.
SYS@bookdg> @ &r/dg/dg
PROCESS PID STATUS CLIENT_P GROUP# THREAD# SEQUENCE# BLOCK# BLOCKS DELAY_MINS
--------- ------- ------------ -------- ------ ------- ---------- ---------- ---------- ----------
RFS 28405 IDLE ARCH N/A 0 0 0 0 0
RFS 28409 IDLE UNKNOWN N/A 0 0 0 0 0
RFS 28407 IDLE UNKNOWN N/A 0 0 0 0 0
ARCH 7392 CONNECTED ARCH N/A 0 0 0 0 0
RFS 28411 IDLE LGWR 1 1 716 19 1 0
ARCH 7394 CLOSING ARCH 4 1 713 2048 660 0
ARCH 7390 CLOSING ARCH 4 1 715 1 114 0
ARCH 7384 CLOSING ARCH 4 1 711 1 123 0
MRP0 7418 APPLYING_LOG N/A N/A 1 716 19 102400 0
9 rows selected.
--//传输正常!!如何能确定是用户A在做传输工作吗?...很奇怪这样操作不需要拷贝口令文件过去.
4.这样做的好处:
--//安全,用户A没有权限登录数据库.
$ rlsql -s -l a/a#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>
PUBLIC@book> create table a ( b number);
create table a ( b number)
*
ERROR at line 1:
ORA-01031: insufficient privileges
