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 :) :)

Path of the alertlog file in Oracle Database 11gR2

How to view the alert log of the database.

alertlog file is meant to track the database when the database has started and shutdown and if there is any discrepancies are present in the database,all information are present in the alert log file.

Path of the alertlog file..

Steps are:  1. Connect to Oracle Database viz SYS or SYSTEM user.
                  2. Encounter the command " show parameter background"  or "show parameter dump"
                  3. Go to the path present beside "background_dump_dest"
                  4. search for alert_DEV_log file by ls -lrt *alert*.log
                  5. View the file for error of information.  use ..   view *alert*.log file


Above steps are demonstrate below attaching snaps.

1. Connect to Oracle Database as SYS or SYSTEM user.










2.  Encounter the command " show parameter background"  or "show parameter dump"


3. Step 3.4, 5



4. open the log file and check what you looking for


Press ESC :q!  and press enter--> to come to command prompt with out altering anything to the log file.