Author |
Message |
James Phifer
Joined: 20 Dec 2005 Posts: 7
|
|
DB Audit |
|
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
|
|
Tue Dec 20, 2005 4:23 pm |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
Re: DB Audit |
|
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
|
|
Tue Dec 20, 2005 4:53 pm |
|
![](templates/GreenTech/images/spacer.gif) |
James Phifer
Joined: 20 Dec 2005 Posts: 7
|
|
Re: DB Audit |
|
: 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.
|
|
Tue Dec 20, 2005 5:04 pm |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
Re: DB Audit |
|
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.
|
|
Tue Dec 20, 2005 5:27 pm |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
Re: DB Audit |
|
Does DB_AUDIT have CREATE SESSION or CONNECT privileges? : db_audit has execute priv for dbms_job and can select from dba_jobs view.
|
|
Tue Dec 20, 2005 5:29 pm |
|
![](templates/GreenTech/images/spacer.gif) |
James Phifer
Joined: 20 Dec 2005 Posts: 7
|
|
Re: DB Audit |
|
: Does DB_AUDIT have CREATE SESSION or CONNECT privileges?
db_audit has the connect role which has create session priv.
|
|
Tue Dec 20, 2005 5:32 pm |
|
![](templates/GreenTech/images/spacer.gif) |
James Phifer
Joined: 20 Dec 2005 Posts: 7
|
|
Re: DB Audit |
|
: 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.
|
|
Tue Dec 20, 2005 5:36 pm |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
Re: DB Audit |
|
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.
|
|
Tue Dec 20, 2005 5:58 pm |
|
![](templates/GreenTech/images/spacer.gif) |
James Phifer
Joined: 20 Dec 2005 Posts: 7
|
|
Re: DB Audit |
|
: 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)?
|
|
Wed Dec 21, 2005 12:07 pm |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
Re: DB Audit |
|
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)?
|
|
Wed Dec 21, 2005 1:10 pm |
|
![](templates/GreenTech/images/spacer.gif) |
James Phifer
Joined: 20 Dec 2005 Posts: 7
|
|
Re: DB Audit |
|
: 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
|
|
Wed Dec 21, 2005 2:19 pm |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
Re: DB Audit |
|
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
|
|
Wed Dec 21, 2005 4:03 pm |
|
![](templates/GreenTech/images/spacer.gif) |
James Phifer
Joined: 20 Dec 2005 Posts: 7
|
|
Re: DB Audit |
|
: 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.
|
|
Wed Dec 21, 2005 5:15 pm |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
Re: DB Audit |
|
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.
|
|
Wed Dec 21, 2005 7:52 pm |
|
![](templates/GreenTech/images/spacer.gif) |
|