Sql-Tuning-Hierarchy

kill blocking session from database

During adpatch, deadlock will happen when some jobs depends on resource where another job its holding the resource. Using adctrl,

you can find two jobs in running state there will be no log updates.

* To find blocking session jobs below query will useful. It will return two rows.

select process,sid, blocking_session from v$session where blocking_session is not null;

E.g.

SQL> select process,sid, blocking_session from v$session where blocking_session is not null;

PROCESS SID BLOCKING_SESSION

———— ———- —————-

1234 365 366

1234 366 365

* Second step to find the serial number for the Blocking Session to kill

select SERIAL# from v$session where SID=<SID number>

E.g.

SQL> select SERIAL# from v$session where SID=365;

SERIAL#

———-

130

* Final step to kill the blocking session

alter system kill session ‘SID,SERIAL#’;

E.g.

SQL> alter system kill session ’365,130′;

System altered.

FATAL] [INS-40912] Virtual host name: racnode02-vip is assigned to another system on the network

Error while re-adding deleted cluster node to cluster 

[grid@racnode01 addnode]$ ./addnode.sh -silent "CLUSTER_NEW_NODES={racnode02}" "CLUSTER_NEW_VIRTUAL_HOSTNAMES={racnode02-vip}" "CLUSTER_NEW_NODE_ROLES={hub}"

[FATAL] [INS-40912] Virtual host name: racnode02-vip is assigned to another system on the network.

   CAUSE: One or more virtual host names appeared to be assigned to another system on the network.

   ACTION: Ensure that the virtual host names assigned to each of the nodes in the cluster are not currently in use, and the IP addresses are registered to the domain name you want to use as the virtual host name.

  

2. Virtual IP's should NOT be pingable before the installation of the Grid Infrastructure.

#ping racnode02-vip

PING racnode02-vip.com (aaa.aa.aa.17) 56(84) bytes of data.

64 bytes from racnode02-vip.com(aaa.aa.aa.17):icmp_seq=1 ttl=64 time=0.067 ms

64 bytes from racnode02-vip.com(aaa.aa.aa.17):icmp_seq=2 ttl=64 time=0.156 ms

I’m able to ping node2 VIP, Looks something wrong

• Verified /etc/hosts file on all cluster nodes ,vip details not added

• Check network configuration with ifconfig

root@racnode01~ # ifconfig -a | grep aaa.aa.aa.17

  inet aaa.aa.aa.17  netmask 255.255.254.0  broadcast aaa.aa.aa.255

       

bond0:5: flags=5187<UP,BROADCAST,RUNNING,MASTER,MULTICAST>  mtu 1500

  inet aaa.aa.aa.17  netmask 255.255.254.0  broadcast aaa.aa.aa.255

  ether 00:aa:b3:00:02:2f  txqueuelen 1000  (Ethernet)

       

3. Alias was created for NIC interface with the use of the virtual IP (during  previous installation)

 

Brought down interface

ifconfig bond0:5 down

Recover a deleted datafile on linux using symbolic link when database is up

On Linux and other unix box , when a datafile is accidently dropped , using process (PID) belong to the datafile we can recover the data.

Note : If the process lost , it is not possible to recover using symbolic link. 

To have a look let us create a tablespace called DEMO,

SQL> create tablespace demo datafile '/u01/app/oracle/oradata/demo01.dbf' size 10M;

Tablespace created.

SQL> create table test tablespace demo as select * from dba_users;

Table created.

SQL> select count(*) from test;

           COUNT(*)

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

                 34

SQL> select name from v$datafile;

           NAME

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

/u01/app/oracle/oradata/system01.dbf

/u01/app/oracle/oradata/sysaux01.dbf

/u01/app/oracle/oradat/undotbs01.dbf

/u01/app/oracle/oradata/users01.dbf

/u01/app/oracle/oradata/encrypted_ts01.dbf

/u01/app/oracle/oradata/demo01.dbf

6 rows selected.

[oracle@11G dbs]$ cd /u01/app/oracle/oradata/

Let me  go ahead and drop demo01.dbf (datafile) manually from OS,

[oracle@11G]$ ls -lrt demo01.dbf

-rw-r----- 1 oracle oinstall 10493952 Aug 28 21:31 demo01.dbf

[oracle@11G]$ rm demo01.dbf

[oracle@11G]$ ls -lrt demo01.dbf

ls: demo01.dbf: No such file or directory

Let me connect to the database,

[oracle@11G ~]$ export ORACLE_SID=proddb01

[oracle@11G ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Aug 28 22:15:52 2011

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

SQL> select count(*) from test;

           

           COUNT(*)

           ----------

                34

Opps .. Still I am able to see the records.

SQL> Alter system flush shared_pool;                             

System altered.

SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;

System altered.

Disconnect the session and open a new terminal and start a new session

[oracle@11G ~]$ export ORACLE_SID=proddb01

[oracle@11G ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Aug 28 22:15:52 2011

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

SQL> select * from test;

select * from test

              *

ERROR at line 1:

ORA-01116: error in opening database file 6

ORA-01110: data file 6: '/u01/app/oracle/oradata/demo01.dbf'

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

Yes …  Now we are not able to see my records

At the OS let us check the process 

[oracle@11G ~]$ ps -edf | grep dbw

oracle    4488     1  0 21:24 ?        00:00:01 ora_dbw0_ proddb01

oracle    5269  5231  0 21:40 pts/1    00:00:00 grep dbw

[oracle@11G ~]$ ls -l /proc/4488/fd/ | grep demo

lrwx------ 1 oracle oinstall 64 Aug 28 21:42 27 -> /u01/app/oracle/oradata/demo01.dbf (deleted)

You will see the above message saying the file is deleted.

 [oracle@11G ~]$ ls -l /proc/4488/fd/27

lrwx------ 1 oracle oinstall 64 Aug 28 21:45 /proc/4488/fd/27 -> /u01/app/oracle/oradata/demo01.dbf (deleted)

To make sure let me check whether demo datafile is available at the OS level

[oracle@11G ~]$ cd /u01/app/oracle/oradata/

[oracle@11G]$ ls

control01.ctl       redo01.log  redo03.log    system01.dbf  undotbs01.dbf

encrypted_ts01.dbf  redo02.log  sysaux01.dbf  temp01.dbf    users01.dbf

don't see demo01.dbf file. Now let us create a symbolic link using PID so that oracle can see it as it was before the delete

[oracle@11G]$ ln -s /proc/4488/fd/27 /u01/app/oracle/oradata/demo01.dbf

[oracle@11G SABA1]$ ls

control01.ctl                    redo01.log  sysaux01.dbf  undotbs01.dbf

demo01.dbf                    redo02.log  system01.dbf  users01.dbf

encrypted_ts01.dbf         redo03.log  temp01.dbf

Yes, Symbolic link is created.

Let me connect to the database,

[oracle@11G ~]$ export ORACLE_SID=proddb01

[oracle@11G ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Aug 28 22:15:52 2011

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

SQL> select count(*) from test;

           COUNT(*)

            ----------

                34

Now then data is accessible, but that will be lost if dbwriter closes it file handle (i.e if the database is closed)

However we can now set the tablespace read only so that it is checkpointed, and no writes occurs on it.

SQL> alter tablespace demo read only;
           Tablespace altered.

Drop the symbolic link.

[oracle@11G ~]$ rm /u01/app/oracle/oradata/demo01.dbf

Copy the data from PID to demo01.dbf

$cp -p /proc/6264/fd/30 /u01/app/oracle/oradata/demo01.dbf

SQL> alter tablespace demo read write;

Tablespace altered.

SQL> select count(*) from test;

           COUNT(*)

            ----------

                34

CRSCTL & SRVCTL command/references (RAC Administration)

Activate Physical Standby Database

Login to Standby database and run below commands 

 

SQL> select database_role,open_mode,name from gv$database;

DATABASE_ROLE    OPEN_MODE            NAME

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

PRIMARY         READ ONLY WITH APPLY  TSTSTDBY

 

SQL> alter database recover managed standby database cancel;

Database altered.

 

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

Database altered.

 

SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;

Database altered.

 

SQL> select open_mode from v$database;

OPEN_MODE

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

MOUNTED

 

SQL>  select database_role,open_mode,name from gv$database;

DATABASE_ROLE    OPEN_MODE            NAME

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

PRIMARY          MOUNTED              TSTSTDBY

 

SQL> alter database open;

Database altered.

 

SQL> select database_role,open_mode,name from gv$database;

DATABASE_ROLE    OPEN_MODE            NAME

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

PRIMARY          READ WRITE           TSTSTDBY