Holy oil is a rare substance. Castiel obtains some from Jerusalem, in order to use it to restrain Raphael. When the oil is ignited, its flames will trap angels and archangels, if an angel touches or attempts to pass through the flames they will die.
Sam describes it as "a devil's trap for angels" but it differs because angels can still use some of their powers while trapped within the flames. Sam and Dean keep bottles of the oil in the trunk of the Impala, each of which was presumably supplied by Castiel.
Holy fire is capable of hurting, though not killing demons. Castiel throws Meg onto a ring of holy fire to escape and Sam douses Abaddon in holy oil and sets her on fire. Both are not killed, but Abaddon is left in so much pain she abandons her meatsuit and flees. 
Castiel and Dean trap the archangel Raphael inside a ring of burning holy oil and ask him for God's location. He claims that God is dead, and refuses to cooperate. They leave him in the still burning circle. He eventually escapes.
Dean traps the angel Balthazar in a circle of holy oil and forces him to return the soul he bought from a boy, Aaron Birch, with a piece of the Staff of Moses. Castiel causes the circle to vanish with a gesture when he wishes to free the angel.
Dean, Sam and Bobby trap Castiel in a circle of ignited holy oil and force him to admit to working with Crowley. Castiel tells Dean, Sam and Bobby to leave him when the sound of an approaching storm of Demons is heard. They leave, and only Crowley enters; he causes the circle to vanish with a gesture, freeing the angel.
Sam and Castiel find the angel Josiah badly burned by holy fire. After falling for the trap laid out by Metatron, Josiah was doused with holy oil and set aflame by a flame thrower hanging over the entrance to what he thought was a doorway to Heaven.
In protest to the way that Crowley had been running Hell, a demon douses himself in holy oil and immolates himself in front of Crowley.