I get this error message when I click the Schedule
Job on the Audit Method Tab for Advanced System Audit
Options for Oracle "Connect Error, Database not
available, Ora-01031". I am logged into the
DB Audit console as sys. I have already created
the db_audit schema and procedures. I have granted
the privs to db_audit that the gui recommended.
I have set job_queue_processes to 1 in the database.

Why am I getting an error trying to schedule a job
from the dbaudit console?

Thanks,

James

ORA-01031 normally indicates insufficient privileges. My guess is that it is trying to setup a job running under DB_AUDIT user account. Please make sure DB_AUDIT has EXECUTE privileges for the DBMS_JOB package and also can SELECT from DBA_JOBS system view.

: I get this error message when I click the Schedule
: Job on the Audit Method Tab for Advanced System Audit
: Options for Oracle "Connect Error, Database not
: available, Ora-01031". I am logged into the
: DB Audit console as sys. I have already created
: the db_audit schema and procedures. I have granted
: the privs to db_audit that the gui recommended.
: I have set job_queue_processes to 1 in the database.

: Why am I getting an error trying to schedule a job
: from the dbaudit console?

: Thanks,

: James

: ORA-01031 normally indicates insufficient privileges. My guess is that it is
: trying to setup a job running under DB_AUDIT user account. Please make
: sure DB_AUDIT has EXECUTE privileges for the DBMS_JOB package and also can
: SELECT from DBA_JOBS system view.

db_audit has execute priv for dbms_job and can select from dba_jobs view.

Does DB_AUDIT have SESSION privilege?

: ORA-01031 normally indicates insufficient privileges. My guess is that it is
: trying to setup a job running under DB_AUDIT user account. Please make
: sure DB_AUDIT has EXECUTE privileges for the DBMS_JOB package and also can
: SELECT from DBA_JOBS system view.

Does DB_AUDIT have CREATE SESSION or CONNECT privileges?

: db_audit has execute priv for dbms_job and can select from dba_jobs view.

: Does DB_AUDIT have CREATE SESSION or CONNECT privileges?

db_audit has the connect role which has create session priv.

: db_audit has the connect role which has create session priv.

I can execute the sp_audit_sysmove procedure as
the dbaudit user manually through sql*plus without
errors. The db_audit user is not able to create and
schedule jobs through dbms_job.

Did you already change the default password for the db_audit user?

Anyway, all it essentially is trying to do is scheduling the job

Try running the following from SQL*Plus
DECLARE
job BINARY_INTEGER;
BEGIN
dbms_job.submit( job, 'db_audit.sp_audit_sysmove;', SYSDATE, 'SYSDATE + 1/24', FALSE );
END;

Here, 'SYSDATE + 1/24' stands for hourly runs. Change it as needed if you want some other job recursion frequency.

: I can execute the sp_audit_sysmove procedure as
: the dbaudit user manually through sql*plus without
: errors. The db_audit user is not able to create and
: schedule jobs through dbms_job.

: Did you already change the default password for the db_audit user?

: Anyway, all it essentially is trying to do is scheduling the job

: Try running the following from SQL*Plus
: DECLARE
: job BINARY_INTEGER;
: BEGIN
: dbms_job.submit( job, 'db_audit.sp_audit_sysmove;', SYSDATE, 'SYSDATE +
: 1/24', FALSE );
: END;

: Here, 'SYSDATE + 1/24' stands for hourly runs. Change it as needed if you
: want some other job recursion frequency.

Yes, I changed the password for db_audit. I ran the above
from sql*plus as db_audit successfully. Why does the gui
give me an error when I try to schedule the job (same error
as before)?

To schedule a job as DB_AUDIT use it needs to open a new connection using that user's logon.

: Yes, I changed the password for db_audit. I ran the above
: from sql*plus as db_audit successfully. Why does the gui
: give me an error when I try to schedule the job (same error
: as before)?

: To schedule a job as DB_AUDIT use it needs to open a new connection using
: that user's logon.

I am logged into the dbaudit console as the sys user.
Is this correct?
I have tried logging in as db_audit and I get a login
error for db_audit also when clicking on schedule job
button.
The db_audit user has the following privs in Oracle DB

The connect role
Execute and procedure system priv
Select any table system priv
Delete on sys.aud$
execute on sys.dbms_job
execute on sys.dbms_lob
execute on sys.dbms_output
execute on sys.dbms_utility
select on sys.aud$
select on sys.v_$mystat
select on sys.v_$session

I think the issue is in the changed password. Do not use DB_AUDIT user for normal logons. It is used as a storage schema for the audit repository objects and also used by the console during the initial audit setup. By the way, the default password is IAUDIT. Because you have changed the default password the console is unable to schedule jobs on behave of that user as it doesn't know the new password. Please restore the default password if you are still configuring the system. When you are done with the setup you then should change the default password.

: I am logged into the dbaudit console as the sys user.
: Is this correct?
: I have tried logging in as db_audit and I get a login
: error for db_audit also when clicking on schedule job
: button.
: The db_audit user has the following privs in Oracle DB

: The connect role
: Execute and procedure system priv
: Select any table system priv
: Delete on sys.aud$
: execute on sys.dbms_job
: execute on sys.dbms_lob
: execute on sys.dbms_output
: execute on sys.dbms_utility
: select on sys.aud$
: select on sys.v_$mystat
: select on sys.v_$session

: I think the issue is in the changed password. Do not use DB_AUDIT user for
: normal logons. It is used as a storage schema for the audit repository
: objects and also used by the console during the initial audit setup. By
: the way, the default password is IAUDIT. Because you have changed the
: default password the console is unable to schedule jobs on behave of that
: user as it doesn't know the new password. Please restore the default
: password if you are still configuring the system. When you are done with
: the setup you then should change the default password.

I set the db_audit password back to IAUDIT.
I logged into db_audit console as sys and got errors clicking
the schedule job button.
I logged into db_audit console as system and clicked
the schedule job button and the jobs were scheduled
as system and owned by system. I removed the jobs
from the schedule.
I logged into db_audit console as the db_audit user
and clicked the schedule job button and the jobs were
scheduled as db_audit and owned by db_audit without
errors.
It seems that after creating the db_audit user and
schema objects you have to log out of the console as sys
and log back in as dba_audit user to successfully
schedule jobs through the console.

Please tell me what Oracle account should be used at which times
in the process in using the console.

Any admin account should be ok. SYS or SYSTEM accounts are needed in the beginning when creating audit procedures and mantenance jobs accessing SYS owned objects.

if you find SYSTEM account convinient, feel free to use it.

: I set the db_audit password back to IAUDIT.
: I logged into db_audit console as sys and got errors clicking
: the schedule job button.
: I logged into db_audit console as system and clicked
: the schedule job button and the jobs were scheduled
: as system and owned by system. I removed the jobs
: from the schedule.
: I logged into db_audit console as the db_audit user
: and clicked the schedule job button and the jobs were
: scheduled as db_audit and owned by db_audit without
: errors.
: It seems that after creating the db_audit user and
: schema objects you have to log out of the console as sys
: and log back in as dba_audit user to successfully
: schedule jobs through the console.

: Please tell me what Oracle account should be used at which times
: in the process in using the console.