Avatar of ajaybelde
ajaybelde
Flag for United States of America asked on

Auditing

HI,
I have to write the auditing script, so guys please help me where i was wrong, all the auditing is to be done by sys.aud$, and the records needs to move to new schema info, i have to schedule every day to move the audit records from sys.aud$ to info.order,
below is the script which i wrote , can u make some changes and tell me where i was wrong

spool audit.log

--"Auditing Initialisation Parameters: check initialization parameter"

select name || '=' || value from v$parameter where name like '%audit%'
/

---"if auditing is disabled then issue this command and bounce"

alter system set audit_trail=db,extended scope = spfile
/

shutdown immediate

startup

select name || '=' || value from v$parameter where name like '%audit%'
/


create tablespace ORDER_DATA datafile '+DDATA' size 50m;

create user INFO identified by INFO;

Grant connect,resource to INFO;

Alter user INFO quota unlimited on ORDER_DATA;

connect INFO/INFO

create table ORDER
 partition by range (Timestamp#)
 subpartition by hash(dbid)
  subpartition template
  (subpartition sp1 tablespace users,
 subpartition sp2 tablespace users)(
  partition p1 values less than (TO_DATE('07/29/2010','MM/DD/YYYY')),
   partition p2 values less than (TO_DATE('07/29/2011','MM/DD/YYYY')),
 partition p3 values less than (MAXVALUE)) tablespace AUDIT_DATA as select * from sys.aud$



-----AUDIT ON CREATE SESSION,EXECUTE PROCEDURE, UPDATE TABLE, ALTER TABLE WHEN EVER IT IS FAILURE----------
AUDIT CREATE SESSION, EXECUTE PROCEDURE, UPDATE TABLE, ALTER TABLE BY ACCESS WHENEVER NOT SUCCESSFUL;



---- "Statement Audits Enabled on this Database"
column user_name format a10
column audit_option format a40
select *
from   sys.dba_stmt_audit_opts
/

----"Check Privilege Audits Enabled on this Database"
select * from dba_priv_audit_opts
/

----"Check Objects, Audits Enabled on this Database"
select (owner ||'.'|| object_name) object_name,
       alt, aud, com, del, gra, ind, ins, loc, ren, sel, upd, ref, exe
from   dba_obj_audit_opts
where  alt != '-/-' or aud != '-/-'
   or  com != '-/-' or del != '-/-'
   or  gra != '-/-' or ind != '-/-'
   or  ins != '-/-' or loc != '-/-'
   or  ren != '-/-' or sel != '-/-'
   or  upd != '-/-' or ref != '-/-'
   or  exe != '-/-'
/


spool off;

exit

insert into info.ORDER as select * from sys.aud$;

/
Oracle Database

Avatar of undefined
Last Comment
mrjoltcola

8/22/2022 - Mon
mrjoltcola

How about asking a specific question?

What is wrong? What isn't working as planned?
ajaybelde

ASKER
I dont how to schedule the script for moving the audit records sys.aud$ to new schema, how i need to schedule in crontab.can u provide the commands
ASKER CERTIFIED SOLUTION
mrjoltcola

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
johnsone

Isn't this question the same as

https://www.experts-exchange.com/Database/Oracle/Q_27238911.html

The script looks identical.
Your help has saved me hundreds of hours of internet surfing.
fblack61
mrjoltcola

>>I dont how to schedule the script for moving the audit records sys.aud$ to new schema,

Setup a DBMS_SCHEDULER job to call a PL/SQL procedure. Put your logic inside that procedure.

Docs here:

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_sched.htm#i1000363

Use JOB_TYPE of STORED_PROCEDURE or PLSQL_BLOCK

Example here: http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/schedadmin006.htm#i1009099
ajaybelde

ASKER
can u please help me in writing the scripts for auditing in 10gr2,11gr1, and 11gr2. for moving the auditng records from sys.aud$ to info.order aon every day.can u please provide commands using above example script
ajaybelde

ASKER
@johnsone yes..nobody is responding over there, thats why i reposted it.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
mrjoltcola

Hmm, you should ping the original thread, not post duplicates. We now have to merge the points due to EE guidelines. Personally I don't check duplicates, but johnsone as pointed it out. You need to edit each question and assign half point to each, unfortunately, and this doesn't make experts of the original thread happy, usually; though I know sdstuber and he won't likely mind.
ajaybelde

ASKER
@mrjoltcola : i can give you full points if you help me over here.becuase i am looking for exact solution how to write the scripts in all the three versions in 10gr2, 11gr1, and 11gr2
johnsone

I don't typically check duplicates either.  However, I had seen the original earlier today and knew that script look very familiar.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
mrjoltcola

I'll help. I have a meeting with a customer shortly, will have to return to this later on. I've also notified sdstuber that we are working in this thread as well, hopefully he will pop in.
ajaybelde

ASKER
Ok thanks, probably at what time you are going to start it, i will be waiting for you.
ajaybelde

ASKER
HI,
can anyone help me over here in writing the audit script
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ajaybelde

ASKER
i need to write a auditing script in all the versions of 10gr2, 11gr1, and 11gr2, so any can one help me here to write the script.
Sean Stuber

what do you need that you don't already have?

ajaybelde

ASKER
the script should select the version and run according to that version,
for example if it is 10.2.0.4 version then the script needs to run according to the 10g version, if it is 11gr1 and 11gr2 it should run according to that version..?
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Sean Stuber

your script looks to me like it should run fine on all 3 versions.  - are you getting errors? if so, what are they?

the dbms_scheduler snippet I posted should be the same on all 3 as well.

is there something specific about each version that you're trying to support?
ajaybelde

ASKER
i need a procedure to move the records not a dbms_scheduler for moving the scheduler, because the dbms_scheduler supports in 11gr2, so i need a procedure for moving the audit records from sys.aud$ to info.order in 10gr2, and 11gr1 version.
mrjoltcola

Write one SQL statement that will be compatible with all.

Due to time constraints I cannot write the script for you anytime soon, but I'd be happy to help if you show initiative to start your own work first.

Essentially what you need to do is write SQL to copy records from A to B, then trim A.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ajaybelde

ASKER
Begin
insert into info.order select * from sys.aud$;
delete sys.aud$;
commit;
end;
/

write a procedure for this sql statement
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ajaybelde

ASKER
what about hash partioning with database name,how i need to keep in procedure..?, does this procedure supports in 10gr2, and 11gr1..?, today is my last day, if it is 10gr2, and 11gr1 the procedure needs to be executed and if it is 11gr2 the dbms_scheduler needs to be executed. so what i have to do to change the script.
ajaybelde

ASKER
can u keep the for loops or if loops to make the script look good and the script needs to run in all the versions, today is the last day for this task, i have to submit today.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Sean Stuber

>>>what about hash partitioning?

what about it?


>>> today is my last day   -- what do you mean by this?


, if it is 10gr2, and 11gr1 the procedure needs to be executed

 and if it is 11gr2 the dbms_scheduler

why?  different methods?  That's not what you asked for, what changed?


you request is very simple,  take the dbms_scheduler block above

put the procedure mrjoltcola wrote inside it (or put the contents inside as an anonymous block)

and run it
done

should work for 10gr2, 11gr1,11gr2 without modification
if it doesn't,  please post the exact error you are getting






Sean Stuber

>>> can u keep the for loops or if loops to make the script look good


there are no loops in what you need
as for "looking good"  format the code to whatever standard your company uses.

if there is no standard, then format to suit your own whim

if you don't have a preference, then leave as is
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.