Audit DDL Statements from alert.log file from Oracle Database

Audit of DDL

Every time a user attempts anything in the database where audit is enabled the Oracle kernel checks to see if an audit record should be created or updated (in the case or a session record) and generates the record in a table all changes are present in alert.log file of database.

Example-----

In 11g you can record you ddl operations by setting the parameter enable_ddl_logging to true:

ALTER SYSTEM SET enable_ddl_logging=TRUE SCOPE=BOTH;

 Example:
I have tried with create and drop command .. you can also do it with alter,rename,comment and truncate and you will get all results in alert*.log file

1. I have connected to debashis schema.
2. Enabling the ddl_looging by using the above command.
3. Create a table debashiskhatai
4. Drop the table
5. Disable the ddl_logging

 

Now if you go and check the alert log file you will find two 4 DDL commands over there

                                                                                                                                         inalert.logfile__________________________________________________________________________

ALTER SYSTEM SET enable_ddl_logging=TRUE SCOPE=BOTH;

 create table debashiskhatai(regid number);

drop table debashiskhatai;

ALTER SYSTEM SET enable_ddl_logging=false SCOPE=both;
__________________________________________________________________________________

 Path of alertlog file databse (use the below command and whatever the path in background_dump_dest is ..actually the path of alert*.log file)

SQL>show parameter background

 
 Now when I opened the alert_orcl1.log file I found all those 4 DDL statements in my alert.log file.

 

Done !! yipeee...

Hope you enjoy it ..... :)




 

FlashBack Conecpt: Recover the droped tables from recyclebin

What is Recycle Bin
Oracle has introduced "Recycle Bin" Feature Oracle 10g to store all the dropped objects.
If any table in Oracle 10g is dropped then any associated objects to this table such as indexes,
constraints and other dependant objects are simply renamed with a prefix of BIN$$.

Why Recycle Bin
A user drops a very important table--accidentally, of course--and it needs to be revived as soon as possible.
Oracle9i Database introduced the concept of a Flashback Query option to retrieve data from a point in time in the past, but it can't flash back DDL operations such as dropping a table.
The only recourse is to use tables pace point-in-time recovery in a different database and then recreate
the table in the current database using export/import or some other method.


How to Enable/Disable Recycle Bin
++++++++++++++++++++++++++++++++++++++

1. 1st Check the status of it from the inti.ora file (parameter file)

Mostly it is in the on status

SQL > SELECT Value FROM V$parameter WHERE Name = 'recyclebin';

Value
-----
On


SQL> show parameter recyclebin;

NAME                       TYPE        VALUE
-------------------------- ----------- -----------------------------
recyclebin                 string      ON


if the Value Is “on” then recyclebin feature is enabled for the Database.
If the Value is “off” the recyclebin feature is disabled.

The following commands are used to enable or Disable the Feature


SQL > ALTER SYSTEM SET recyclebin = ON;
or
SQL > ALTER SESSION SET recyclebin = ON;

SQL > ALTER SYSTEM SET recyclebin = OFF;
or
SQL > ALTER SESSION SET recyclebin = OFF;


Show the Contents in RECYCLEBIN  (there are 3 things recycclebin,user_recyclebin and dba_recyclebin)
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

user_reyclebin and recyclebin is same
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@


Use the following commands to show all the objects that are stored in Recycle Bin,

SQL > SHOW RECYCLEBIN;
Or
SQL > SELECT * FROM USER_RECYCLEBIN;


Steps to recover the dropped table

For example I have dropped a table(khatai1) from khatai schema. 

SQL> show user
USER is "KHATAI"

SQL> select * from recyclebin;  --> nothing is present, recyclebin is empty

no rows selected
SQL> drop table khatai1;

Table dropped.

SQL> desc khatai1;   
ERROR:
ORA-04043: object khatai1 does not exist  ----> Because table was dropped on previous step.


Now again check the recyclebin for finding out is there any object is present or not

 SQL> select * from recyclebin;

OBJECT_NAME                    ORIGINAL_NAME                    OPERATION TYPE
                    TS_NAME                        CREATETIME          DROPTIME
              DROPSCN PARTITION_NAME                   CAN CAN    RELATED BASE_O
BJECT PURGE_OBJECT      SPACE
------------------------------ -------------------------------- --------- ------
------------------- ------------------------------ ------------------- ---------
---------- ---------- -------------------------------- --- --- ---------- ------
----- ------------ ----------
BIN$lKNkXsCSQu6Vrzj6BIGwcA==$0 KHATAI1                          DROP      TABLE
                    USERS                          2015-07-15:14:47:27 2015-07-2
1:14:57:47    1364484                                  YES YES      74578
74578        74578          8

SQL>select droptime, dropscn from user_recyclebin where original_name='KHATAI1';


Now recover steps

SQL> flashback table khataid to before drop;

flashback complete  -----> and you are done.

Please check the below image



If you see the recyclebin is now empty because the table was recovered.

Note: All the tables that are created by user sys is in system tablespace and every table dropped from the system tablespace is not going to dba_recyclebin or recycle-bin.So be careful while droping the system tables.


Hope you are benefited.. thanks.. keep enjoying..

How to extact passwords from Oracle Database

The below steps which are meant to extract the password of the users from the oracle database.

Please Note: All the password are in the encrypted formatted.
                     If you are master in cracking it, then let me know too :) really helpful for me as well.

Mainly this steps are helpful when you are performing the database refresh or cloning purpose.

It might happen that the password of the same user may differ in PROD and TEST ( We are using the Oracle recommended process to follow the same password policy in all environments but sometimes while in hurry you might give some different passwords) and after cloning the user may face "not able to login into db due to username and password not correct".

To make it more clarity ,what I will do is..

Consider golia123 is the password in PROD environment and khataid is the password in TEST Environment and while DB refresh everything in PROD is migrated to TEST along with user credentials.After refresh when you are trying to connect to TEST with your TEST username/password  it will say cann't able to login as the password got changed.
So after refresh you have to run the password script which you have extracted  from the TEST environament (before the refresh activity) otherwise some user will face credentials problem.

Here I am trying with one user...

1.Spooling out all the passwords to a text file

2. Will try to connect to the DB with the current username/password.(which is present in the spool file).

3. I will change the password( alter user username identified by newpassword(which is different from the password present in the spool file)) --> now password got differ.

4. Again try with old username/pwd --> it will say username and password are not authenticate.

5.Now will use the the spooled encypted password for that particular use and again try to login with old username and password ---> it will allow me to login into the database.

Step1.  Spooling out all the password of the user in database by using below commands.

--- All passwords of the users before the refresh from SIT env.


2. Here the password of khataid is khataid which is in encrypted format i.e "84BE8EC9BA845EC0"

( Remember  84BE8EC9BA845EC0 = khataid )



 3. Now I am going to change the password of the khatai as "golia123" (without double quotes) by logging into sys user.

      Consider the database refresh has done
---  After refresh the password got changed to golia123



4. Here I will trying to connect to the oracle DB by using old password and below is the results(of course I have changed the password of the user khataid) --> expected results as o/p.

--> Not able to log in because the password got changed after database refresh.



5. Again I will connected as sys user and will use the command "alter user KHATAID identified by values '84BE8EC9BA845EC0';   ---> remember this ??
 
--- After refresh again I have to run the script which I have spolled out before the db cloning to avoid the credentials problem.

 (the password is the encypted one and it is nothing but khataid(old pasword)



In above alter statement if you see, I haven't given alter user khataid identified by khataid....I have given the encrypted password which I spooled out. and it was automatically taken as "khataid"

By using the below command,you can extract the passwords.

select 'alter user '||name||' identified by values '''||password||''';' from user$;


Hope it will help you.....Enjoy :) :)