It means the player scored in the goal they're trying to defend, rather than the opponent's goal. It usually happens by mistake, for example, if a defender tries to block a shot that was initially going wide anyway, but ends up redirecting it into the goal.
Sounds weird to me too, what do you call it when a player scores for the other team by mistake? That is the meaning of שער עצמי. It is also used metaphorically sometimes, to express acting against one's own interests. It's not exactly an expression, but a common metaphor.