Oracle Flashback Technology

Oracle Flashback Database

At this article I will present The Flashback Oracle Tech. As we all know this features let us view the past state of objects.

Also with this amazing technology we can perform queries that shows a details history and Recovery tables to a previous point in time.Also Rollback transaction.

Let me doing some example OF FLASHBACK DATABASE.

First of all we have to flashback ON. So, connect to SQLplus And switch the flashback ON at mount mode.However I would like to set my flashback location…

SQL>ALTER SYSTEM SET log_archive_dest_1='location=/u01/app/veysideneme/archive' SCOPE=SPFILE;

SQL>ALTER SYSTEM SET log_archive_format='ARC%S_%R.%T' SCOPE=SPFILE;

AND now

[oracle@ora ~]$ sqlplus / as sysdba

SQL>STARTUP MOUNT EXCLUSIVE

SQL>ALTER DATABASE FLASHBACK ON;

SQL>ALTER DATABASE OPEN;

Now check our Db_flashback_retention_target time

SQL> show parameter flashback

NAME                                 TYPE        VALUE

---------------------------------- ----------- ------------------------------

db_flashback_retention_target        integer     1440

So, a day we can return back…

And Now I will connect as my user and create a dummy table to exampled.

SQL> conn veysi/veysi

Connected.

SQL> CREATE TABLE flshbck_db_test ( id NUMBER(20) );

Table created.

SQL> conn / as sysdba

Connected.

SQL> shu immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>startup mount exclusive;

ORACLE instance started.

Now lets get back to time

SQL> Flashback database to timestamp sysdate-(4/8/14);

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

Lets check table is gone or not!

SQL> conn veysi/veysi

Connected.

SQL> desc flshbck_db_test 

ERROR:

ORA-04043: object flshbck_db_test does not exist

As we see the table already is gone.

Let me doing some other example.

First creating a table to exampled..

SQL> create table flshbck_db_test as select * from dba_objects where rownum < 501;

Table created.

Select my table to check

SQL> select count(*) from flshbck_db_test;

COUNT(*)

---------

500

I will use the SCN number to get back the table so,lets look at the SCN of database

SQL> SELECT CURRENT_SCN FROM V$DATABASE;

 CURRENT_SCN

------------

1116143

Now truncate the table…

SQL> truncate table flshbck_db_test;

Table truncated..

Check the table

SQL> select count(*) from flshbck_db_test;

COUNT(*)

---------

0

Now close the database and use flashback to back

SQL> shu immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

Open mount mode,

SQL> startup mount;

ORACLE instance started.

Database mounted.

Now  get the table back

SQL> Flashback database to scn 1116143;

Flashback complete.

.Open the database by resetlogs.

SQL> alter database open resetlogs;

Database altered.

Select my table to check

SQL> select count(*) from flshbck_db_test;

COUNT(*)

---------

500

Killing Oracle Sessions

Killing Oracle Sessions

Today i had killed session and i would like to share the details with you.

The important thing is here identify session. "DO NOT KILL WRONG SESSION!" 

Connecting to sqlplus as SYS admin

[oracle@ora ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun feb 5 19:26:28 2014

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Check user

SQL> show user

USER is "SYS"

setting format

SQL> set linesize 100

SQL> column spid format A10

SQL> column username format A10

SQL> column program format A45

SQL> set pagesize 60

Now Selecting our session to be killed

SQL> Select

  2  x.inst_id,

  3  x.sid,

  4  x.serial#,

  5  y.spid,

  6  x.username,

  7  x.program

  8  From gv$session x     

  9  Join gv$process y ON y.addr = x.paddr AND y.inst_id = x.inst_id

 10  Where x.type != 'BACKGROUND';

   INST_ID        SID    SERIAL# SPID       USERNAME   PROGRAM

---------- ---------- ---------- ---------- ---------- ---------------------------------------------

         1         55         19 4616       VEYSI      sqlplus@ora.localdomain (TNS V1-V3)

         1          1         91 4920       SYS        sqlplus@ora.localdomain (TNS V1-V3)

         1         49         23 4923       VEY        sqlplus@ora.localdomain (TNS V1-V3)

         1         51         38 5375                  oracle@ora.localdomain (J000)

         1         47         37 5377                  oracle@ora.localdomain (J001)

Or the other select way

SQL> select inst_id,sid,serial# from gv$session where username='VEYSI';

   INST_ID        SID    SERIAL#

---------- ---------- ----------

         1         55         19

Here we go the session information 

Now the simple command used to killing!

Unix commands

find SPID 

% ps -ef | grep ora

Kill by Unix Command 

% kill -9 spid

OR...

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#';

SQL> ALTER SYSTEM KILL SESSION '55,19,1';

However Oracle 11g change the killing command

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id';

SQL> ALTER SYSTEM KILL SESSION '55,19,@1';