Killing session in Oracle RAC Database
Hi DBAs,
Whenever we find some locking issue in RAC Database and Developers requested to kill the session which is blocking and creating lock.,please make sure you are killing the correct session.Below steps can be very much useful.
Step 1:Find the Blocking sessionSQL> SET LINES 1000
SQL> select process,sid,blocking_session,BLOCKING_INSTANCE from gv$session
where blocking_session is not null; 2
PROCESS SID BLOCKING_SESSION BLOCKING_INSTANCE
------------------------ ---------- ---------------- -----------------
24822 1139 5366 1
1 rows selected.
Step 2:Check the Program which is blocking
SELECT s.inst_id,
s.sid,
s.serial#,
p.spid,
s.username,
s.program
FROM gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
where s.sid=1139;
So ,1139 is the CALC program
Step 3:Find the Session details
SQL> select sid,serial#, INST_ID from gv$session where sid=' 1139';
SID SERIAL# INST_ID
---------- ---------- ----------
1139 56959 3
Step 4:Kill the session immediately
SQL> alter system kill session '1139,56959,@3' immediate;
System altered.
Whenever we find some locking issue in RAC Database and Developers requested to kill the session which is blocking and creating lock.,please make sure you are killing the correct session.Below steps can be very much useful.
Step 1:Find the Blocking sessionSQL> SET LINES 1000
SQL> select process,sid,blocking_session,BLOCKING_INSTANCE from gv$session
where blocking_session is not null; 2
PROCESS SID BLOCKING_SESSION BLOCKING_INSTANCE
------------------------ ---------- ---------------- -----------------
24822 1139 5366 1
1 rows selected.
Step 2:Check the Program which is blocking
SELECT s.inst_id,
s.sid,
s.serial#,
p.spid,
s.username,
s.program
FROM gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
where s.sid=1139;
So ,1139 is the CALC program
Step 3:Find the Session details
SQL> select sid,serial#, INST_ID from gv$session where sid=' 1139';
SID SERIAL# INST_ID
---------- ---------- ----------
1139 56959 3
Step 4:Kill the session immediately
SQL> alter system kill session '1139,56959,@3' immediate;
System altered.
Working with FLASHBACK in oracle Database
FLASHBACK in Oracle DATABASE:
=============================
Flashback is nothing but going to previous state,same case with Oracle
Database also,here we go to a previous state of Database.
Advantages and Applications of Flashback:
--------------------------------------------
1)Useful in recovering the lost data.
2)Useful in saving time and system resources.
2)Usefull in performing Data testing.
Disadvantages:
---------------
1)Additional Overhead on Database
prerequisites:
---------------
prerequisite 1: Configure FRA(Flash Recovery Area)
SQL> alter system set db_recovery_file_dest_size=1g;
System altered.
SQL> alter system set db_recovery_file_dest=’/oracle/test_flash’;
System altered.
Note: If it is a RAC database the FRA should point to the shared storage.
Ensure you estimate appropriately db_recovery_file_dest_size and have enough space.
STORAGE_SIZE column from V$RESTORE_POINT can help you for that.
prerequisite 2:The Oracle database should be in archivelog
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 70
Next log sequence to archive 73
Current log sequence 73
Note:Below steps can be use to switch Database to archive log mode.
[oracle@orclbox ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.2.0 Production
Copyright (c) 1982, 2010, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> alter system set log_archive_dest_1=’location=/oracle/test_flash’;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1653518336 bytes
Fixed Size 2227032 bytes
Variable Size 1275069608 bytes
Database Buffers 369098752 bytes
Redo Buffers 7122944 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oracle/test_flash
Oldest online log sequence 2
Next log sequence to archive 4
Current log sequence 4
SQL>
prerequisite 2:On the Flashback in Database
SQL> alter database flashback on;
Database altered.
SQL> select log_mode,flashback_on from v$database;
LOG_MODE FLASHBACK_ON
------------ ------------------
ARCHIVELOG YES
Note:To off the flashback use the below statement.
SQL> alter database flashback off;
Database altered.
SQL> select log_mode,flashback_on from v$database;
LOG_MODE FLASHBACK_ON
------------ ------------------
ARCHIVELOG NO
Example 1: Let us consider one eg.of restoring a table to a particular scn(system change number):
Below i'm creating one 'test' user with some test data,than I will check the scn after committing the changes,than I will delete the data and finally I will restore that table using flashback scn technique.
SQL> create user test identified by test;
User created.
SQL> grant connect,resource to test;
Grant succeeded
SQL> insert into test.flashback_test values(1);
1 row created.
SQL> insert into test.flashback_test values(2);
1 row created.
SQL> insert into test.flashback_test values(3);
1 row created.
SQL> commit;
SQL> select current_scn, scn_to_timestamp(current_scn) from v$database;
CURRENT_SCN SCN_TO_TIMESTAMP(CURRENT_SCN)
----------- ---------------------------------------------------------------------------
931892 28-JUL-12 09.49.40.000000000 AM
SQL> delete from test.flashback_test;
3 rows deleted.
SQL> commit;
Commit complete.
SQL> select * from test.flashback_test;
no rows selected
Restore of table flashback_test using flashback database:
=====================================================
step 1:Shut down the instances of Database
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down
Step 2:Bring the Database in mount state
SQL> startup mount
ORACLE instance started.
Total System Global Area 1071333376 bytes
Fixed Size 1341312 bytes
Variable Size 620759168 bytes
Database Buffers 444596224 bytes
Redo Buffers 4636672 bytes
Database mounted.
Step 3:Use flashback database command to go to scn when table was dropped.
If you want to revert the database to SCN=930717 where flashback_test table has 3 rows
SQL> flashback database to scn 930717;
Flashback complete.
SQL> alter database open resetlogs;
--set log sequence no. and the database will be synchonized.
Verify the Data:
SQL> select * from flashback_test;
COL1
———-
1
2
3
Example 2:Flashback to timestamp
Lets say you have to drop some table on a particular day and the user know the time when he had dropped that table,than we can use the below steps to recover that table using flashback technique.
flashshback to timestamp:
======================
step 1:Shut down the instances of Database
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down
Step 2:Bring the Database in mount state
SQL> startup mount
ORACLE instance started.
Total System Global Area 1071333376 bytes
Fixed Size 1341312 bytes
Variable Size 620759168 bytes
Database Buffers 444596224 bytes
Redo Buffers 4636672 bytes
Database mounted.
step 3: flashback to timestamp 28-JUL-12 09.49.40
SQL>flashback database to timestamp TO_TIMESTAMP('2012-07-28 09:49:40','YYYY-MM-DD HH24:MI:SS');
Flashback complete.
SQL> select * from test.flashback_test;
COL1
———-
1
2
3
Example 3: Enable recyclebin and get back the lost data.
Step 1:Enable recycle bin when the Database is in open state.
SQL>alter session enable recyclebin=on;
Step 2:Recover lost table using below command.
SQL> FLASHBACK TABLE test.flashback_test TO BEFORE DROP;
Flashback complete.
SQL> select * from test.flashback_test;
COL1
———-
1
2
3
Example 4:Create guaranteed restore point and restore that restore point:
========
How we can restore the entire data which is lost in a particular period,this can be use to test data also.
This is very vital feature of Oracle flashback.
Create Restore point:
=====================
Step 1:Create restore point 'test_rest1':
SQL> create restore point test_rest1 guarantee flashback database;
Restore point created.
* To view the restore points:
SQL> select NAME,SCN,TIME,GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE
from V$RESTORE_POINT
where GUARANTEE_FLASHBACK_DATABASE='YES';
SQL> select NAME, SCN, TIME, GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE
from V$RESTORE_POINT
where GUARANTEE_FLASHBACK_DATABASE='YES'; 2 3
NAME
--------------------------------------------------------------------------------
SCN
----------
TIME GUA
--------------------------------------------------------------------------- ---
STORAGE_SIZE
------------
TEST_REST1
930537
28-JUL-12 08.57.51.000000000 AM YES
0
NAME
--------------------------------------------------------------------------------
SCN
----------
TIME GUA
--------------------------------------------------------------------------- ---
STORAGE_SIZE
------------
TEST_REST2
930660
28-JUL-12 09.02.54.000000000 AM YES
30203904
NAME
--------------------------------------------------------------------------------
SCN
----------
TIME GUA
--------------------------------------------------------------------------- ---
STORAGE_SIZE
------------
TEST1
932549
28-JUL-12 10.30.01.000000000 AM YES
8192000
Restore Restore point:
=======================
step 1:shut the database
SQL> shut immediate
ORA-01031: insufficient privileges
SQL> conn /as sysdba
Connected.
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down
step 2:Startup in mount state
SQL> startup mount
ORACLE instance started.
Total System Global Area 1071333376 bytes
Fixed Size 1341312 bytes
Variable Size 620759168 bytes
Database Buffers 444596224 bytes
Redo Buffers 4636672 bytes
Database mounted.
step 3:flashback Database to restore point 'test1':
SQL> FLASHBACK database TO RESTORE POINT test1;
Flashback complete
Note:To restore a table below command can be used.
FLASHBACK TABLE emp TO RESTORE POINT test1;
step 4:Open the Database with resetlogs
SQL> alter database open resetlogs;
Database altered
step 6:Verify the data
SQL> select *from test.flashback_test;
COL1
----------
1
2
3
Step 7:Monitor flashback v$flashback_database_log
SQL> select oldest_flashback_scn from v$flashback_database_log;
OLDEST_FLASHBACK_SCN
--------------------
930314
Note: Dropping restore point.
SQL> DROP RESTORE POINT TEST_REST1;
Restore point dropped.
Note:
Possible flashback options available are:
SQL>flashback table test.flashback_test to (SYSDATE-1/24);
-- be sure to substitute your SCN
FLASHBACK DATABASE TO SCN 19513917;
or
FLASHBACK DATABASE TO RESTORE POINT bef_damage;
/*
FLASHBACK DATABASE TO TIMESTAMP (SYSDATE-1/24);
FLASHBACK DATABASE TO TIMESTAMP timestamp'2002-11-05 14:00:00';
FLASHBACK DATABASE
TO TIMESTAMP to_timestamp('2002-11-11 16:00:00','YYYY-MM-DD HH24:MI:SS');
*/
Offcourse,we do have flashback query,flashback transaction query,but above are very useful.
I Hope it helps....
Enjoy dba Task.
.
.
No comments:
Post a Comment