A good leader will create some fear. Popular literature has made us afraid of fear. But a team that does not experience some fear is not dealing with reality.
There are many things in the marketplace for which we should have a healthy fear, and if the leader has a healthy fear of such things (competitive threat, the erosion of the value proposition, etc.) then his team needs to have a healthy fear as well. The leader should not always allay fear; sometimes it is better to harness it.