Is it normal for MacBook Pro CD/DVD drive to makes noise on wake after long sleep
Yes.

After all, OS X wants to allow you to access what's on the disc currently inside the drive. When you eject a disc, the system reacts by no longer showing the disc on the desktop or in Finder. When you insert a disc, the system reacts by showing the disc on the desktop and in Finder.

When the system is asleep, it's no longer keeping track of whether a disc has been ejected, inserted, or replaced with a different disc. It has to check what's in the drive when it wakes up in order to react properly to what's there.