主页 / dba / 问题 / 283417
Accepted
SHR
Asked: 2021-01-18 02:39:15 +0800 CST

调用 DBMS_LOGMNR.START_LOGMNR 时出现“用户不存在”

  • 772

我有一个名为“MYADMIN”的 CDB 用户，我正在尝试让它连接到日志挖掘器。

-- enable calling admin username on CDB 
ALTER SESSION set "_ORACLE_SCRIPT"=true
/
-- create unique table space for admin
CREATE TABLESPACE myadmints DATAFILE '/path/to/admints.dbf' SIZE 20M AUTOEXTEND ON
/
-- create admin user on CDB
CREATE USER myadmin IDENTIFIED BY P@ssw0rd DEFAULT TABLESPACE myadmints QUOTA UNLIMITED ON myadmints ACCOUNT UNLOCK
/
-- allow access to all PDBs to the admin user
ALTER USER myadmin SET CONTAINER_DATA=ALL CONTAINER=CURRENT
/ 
-- grant needed permissions
GRANT DBA to myadmin 
GRANT CREATE SESSION TO myadmin 
GRANT CREATE TABLE TO myadmin 
GRANT EXECUTE_CATALOG_ROLE TO myadmin 
GRANT EXECUTE ON DBMS_LOGMNR TO myadmin 
GRANT SELECT ON V_$DATABASE TO myadmin 
GRANT SELECT ON V_$LOGMNR_CONTENTS TO myadmin 
GRANT SELECT ON V_$ARCHIVED_LOG TO myadmin 
GRANT SELECT ON V_$LOG TO myadmin 
GRANT SELECT ON V_$LOGFILE TO myadmin 
GRANT RESOURCE, CONNECT TO myadmin

我从“v$archived_log”中选择了一行并尝试加载文件。

BEGIN
  DBMS_LOGMNR.ADD_LOGFILE(LogFileName=>'/path/to/archive/ARC000011_1061542581',Options=>DBMS_LOGMNR.new);
  DBMS_LOGMNR.START_LOGMNR(StartScn=>3083464, EndScn=>3388245, Options=>DBMS_LOGMNR.DICT_FROM_ONLINECATALOG+DBMS_LOGMNR.NO_ROW_ID_IN_STMT);
END;

我可以从sys as sysdba用户运行它，但是当我从“myadmin”用户运行它时，我得到：

Error report - 
ORA-01435: user does not exist
ORA-06512: at "SYS.DBMS_LOGMNR", line 72
ORA-06512: at line 3
01435. 00000 - "user does not exist"
*Cause:
*Action:

当我删除它时，错误是关于该START_LOGMNR行的，没有错误。

我缺少哪个特权？

oracle logs

2 个回答

  1. Best Answer

    当您打破并忽略 CDB 体系结构和普通用户的基本概念并使用不受支持的“变通方法”( ALTER SESSION set "_ORACLE_SCRIPT"=true) 时，您就会得到这样的结果。

    当您以受支持的方式创建用户时，上面的代码可以完美运行。

    -- create admin user on CDB
CREATE USER c##myadmin IDENTIFIED BY myadmin DEFAULT TABLESPACE users QUOTA UNLIMITED ON users ACCOUNT UNLOCK
/
-- allow access to all PDBs to the admin user
ALTER USER c##myadmin SET CONTAINER_DATA=ALL CONTAINER=CURRENT
/ 
-- grant needed permissions
GRANT DBA to c##myadmin                            ;
GRANT CREATE SESSION TO c##myadmin                 ;
GRANT CREATE TABLE TO c##myadmin                   ;
GRANT EXECUTE_CATALOG_ROLE TO c##myadmin           ;
GRANT EXECUTE ON DBMS_LOGMNR TO c##myadmin         ;
GRANT SELECT ON V_$DATABASE TO c##myadmin          ;
GRANT SELECT ON V_$LOGMNR_CONTENTS TO c##myadmin   ;
GRANT SELECT ON V_$ARCHIVED_LOG TO c##myadmin      ;
GRANT SELECT ON V_$LOG TO c##myadmin               ;
GRANT SELECT ON V_$LOGFILE TO c##myadmin           ;
GRANT RESOURCE, CONNECT TO c##myadmin              ;

    然后：

    sqlplus c##myadmin/myadmin

BEGIN
  DBMS_LOGMNR.ADD_LOGFILE(LogFileName=>'/fra/RYCDB19/archivelog/2021_01_18/o1_mf_1_130_j0bo2onq_.arc',Options=>DBMS_LOGMNR.new);
  DBMS_LOGMNR.START_LOGMNR(Options=>DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG+DBMS_LOGMNR.NO_ROWID_IN_STMT);
END;
/

PL/SQL procedure successfully completed.

SQL> select count(*) from v$logmnr_contents;

  COUNT(*)
----------
    159587

    每当您用于ALTER SESSION set "_ORACLE_SCRIPT"=true创建自己的用户和对象时，它们的ORACLE_MAINTAINED属性都设置为Y. 这不受支持，并且可能会破坏功能的功能。

    • 1

  2. 如果在创建 logminer 用户后添加另一个 PDB，dbms_logmnr.start_logmnr 将导致 ORA-01435: user does not exist。

    • 0

相关问题