EM CC上调用操作系统脚本报错：ORA-27369:Permission denied

郑全

在EM CLOUD CONTROL上使用SCHEDULER JOB 调用操作系统上的脚本,执行报错： ORA-27369: job of type EXECUTABLE failed with exit code: Permission denied

看到网上，很多贴子都说是EXTJOB权限不对，修改后，也不起作用，原文见：

Scheduled Job Running Shell Script Fails With ORA-27369 (Doc ID 391820.1)

In this Document

Symptoms

Changes

Cause

Solution

References

Applies to:
Oracle Database - Standard Edition - Version 10.1.0.2 to 12.1.0.2 [Release 10.1 to 12.1]
Oracle Database - Enterprise Edition - Version 10.1.0.2 to 12.1.0.2 [Release 10.1 to 12.1]
Linux x86
Oracle Solaris on SPARC (64-bit)
"ORA-27369: job of type EXECUTABLE failed with exit code: Unknown error" was still occurring for non oracle users running DBMS_SCHEDULER executed shell scripts. Problem was fixed when filesystem where ORACLE_HOME was mounted on was found to have been mounted w/ nosetuid. Changing mount option to setuid resolved problem

Symptoms
A Scheduler job has been configured using the DBMS_SCHEDULER package. The job executes a shell script using the option job_type=>'EXECUTABLE'. The script performs a write operation to an output file, for example a redirect operation or touch. The write operation fails with:
ORA-27369: job of type EXECUTABLE failed with exit code: Operation not permitted
Or
ORA-27369: job of type EXECUTABLE failed with exit code: 274662

Or

ORA-27369: job of type EXECUTABLE failed with exit  code: Unknown error

In dba_scheduler_job_run_details.additional_info, the following details are recorded:
ORA-27369: job of type EXECUTABLE failed with exit code: Operation not permitted
STANDARD_ERROR="touch: cannot touch `<file_name>': Permission denied"
Or
ORA-27369: job of type EXECUTABLE failed with exit code: 274662
STANDARD_ERROR="Oracle Scheduler error: Config file is not owned by root or is
writable by group or other or extjob is not setuid and owned by root"
Changes

Cause
If $ORACLE_HOME/rdbms/admin/externaljob.ora exists then external jobs run as the user and group specified in this file, which by default is a lowly privileged user (nobody).
The user 'nobody' and the group 'nobody' do not have the proper privileges for write operations.
As a result, the message 'Permission denied' is returned when attempting to write to files.
Solution
Certain files may need additional permissions and the proper ownership.  Root may  be required for some jobs, while the Oracle user may  be sufficient for others.  It will vary case to case.  In the below example, the root user is used.

Root access is required for the steps below.
1. Ensure the configuration file $ORACLE_HOME/rdbms/admin/externaljob.ora is owned by root:
# cd $ORACLE_HOME/rdbms/admin/
# chown root externaljob.ora
2. Ensure the file permissions are correctly set for $ORACLE_HOME/rdbms/admin/externaljob.ora.
Remove write privileges from group and other.
# chmod 640 externaljob.ora
# ls -la externaljob.ora
-rw-r----- 1 root oinstall 1537 Sep 13 09:24 externaljob.ora
3. Ensure the setuid bit is set on the $ORACLE_HOME/bin/extjob executable.  Also make sure the filesystem is mounted with setuid option.
# cd $ORACLE_HOME/bin
# chmod 4750 extjob
# ls -la extjob
-rwsr-x--- 1 root oinstall 64988 Mar 29 18:22 extjob
References
NOTE:300552.1 - Using Dbms_scheduler Package For Running Executables On Unix Fails With ORA-27369
NOTE:389685.1 - Items to Check when Problems Running Executables through the Scheduler
NOTE:979186.1 - Clarification on Externaljob.ora and Permissions

后再找，找到这篇文章：Oracle SIHA (Restart) Scheduler Executable Job Fails with ORA-27369 Permission denied  (Doc ID 2172256.1)

和我的环境一致，ORACLE 12.1.0.2,ORACLE RESTART,安装是通过 GRID,ORACLE分开安装的，经验证，解决问题。


具体方法就是：
1.建立登录操作系统的凭证

begin

dbms_scheduler.create_credential(

credential_name => 'ORACLE_CRED',

username => 'oracle',

password => 'oracle');

end;

/

2.创建JOB，使用创建的凭证。

begin

dbms_scheduler.create_job

(

job_name => 'MY_EXE_JOB',

program_name => 'MY_EXE_PROG',

schedule_name => 'MY_EXE_JOB_SCHEDULE',

credential_name => 'ORACLE_CRED',

comments => 'Run MY_EXE_JOB',

enabled => TRUE

);

end;

/

之后，再去执行就OK了。



下面附原文：

Oracle SIHA (Restart) Scheduler Executable Job Fails with ORA-27369 Permission denied  (Doc ID 2172256.1)

In this Document

Symptoms

Changes

Cause

Solution

References

Applies to:
Oracle Database - Enterprise Edition - Version 12.1.0.2 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.
Symptoms
In an Oracle SIHA (Oracle Restart) environment, external executable jobs are failing with:

Errors in file /u01/app/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_j000_28299.trc:
ORA-12012: error on auto execute of job "SYS"."MY_EXE_JOB"
ORA-27369: job of type EXECUTABLE failed with exit code: Permission denied

Changes
None
Cause
The oraagent on Oracle Restart systems is owned by the GI Software owner which is why you are seeing the OS User for the DB background processes as grid. This is by design on SIHA Systems (Oracle Restart) in order to reduce the number of processes in turn reducing the overhead of the processes. In a GI for a Cluster environment, each DB install owner would get its own oraagent process that is owned by that user which is why we do not see this behavior in RAC.
OS User for Background Processes when the database is started via SRVCTL (as the DB software owner):
SQL> select osuser from v$session where type='BACKGROUND';
OSUSER
------------------------------
grid
grid
grid
. . .
OS User for Background Processes when the database is started via SQLPLUS (as the DB software owner):

SQL> select osuser from v$session where type='BACKGROUND';
OSUSER
------------------------------
oracle
oracle
oracle
. . .

Solution
In a role separated SIHA (Restart) environment you must configure the scheduler job to utilize credentials to allow the Job to run as the DB Software owner.  The steps to do so are as follows:

1. Create the credential for the oracle OS user:
begin
dbms_scheduler.create_credential(
credential_name => 'ORACLE_CRED',
username => 'oracle',
password => '');
end;
/

2. Modify the job to make use of the credentials created in step 1:
begin
dbms_scheduler.set_attribute
(
NAME => 'MY_EXE_JOB',
ATTRIBUTE => 'CREDENTIAL_NAME',
VALUE => 'ORACLE_CRED'
);
end;
/

If you want to do this at the time of job creation, the PL/SQL block would be:
begin
dbms_scheduler.create_job
(
job_name => 'MY_EXE_JOB',
program_name => 'MY_EXE_PROG',
schedule_name => 'MY_EXE_JOB_SCHEDULE',
credential_name => 'ORACLE_CRED',
comments => 'Run MY_EXE_JOB',
enabled => TRUE
);
end;
/

References
NOTE:279866.1 - DBMS_SCHEDULER FAILS WITH ORA-27369 WHEN JOB TYPE IS EXECUTABLE
NOTE:1953432.1 - Processes Started With srvctl does not Inherit Secondary Group Modifications
BUG:22477962 - INCORRECT MEMLOCK SETTING USED IN GI STANDALONE

BUG:13442441 - DATABASE PROCESSES OWNED BY GRID USER IN GI STANDALONE SERVER
BUG:13503874 - WRONG OWNER FOR DATABASE PROCESS
NOTE:166650.1 - Working Effectively With Oracle Support - Best Practices
NOTE:1473610.1 - Background Processes Ownership When Registering Database With Oracle Restart
NOTE:961019.1 - DBMS_SCHEDULER Extjob Fails With "Login Executable Not Setuid-Root"

郑全

当然，还有一种办法：

就是手工使用SQLPLUS 先把数据库关闭，再使用SQLPLUS把数据库启动，再去按常规的方式执行JOB（不创建凭证） ，也没有问题。

windjack

补充一张以EMCC上创建JOB并引用凭证的截图：

登录/注册后可看大图