It is not possible for all of the duties of the DBA to become obsolote. For example, how can a DBMS make changes to its database structures (e.g. adding columns)? How would it know what to add - and what data type to choose? Database design, both logical and physical, will be needed (along with knowledge of the business) to create and maintain properly running database systems.

Additionally, what about backup and recovery? Would you trust a DBMS that had a corrupted database to recover itself? If it was that darn smart why did it become corrupted in the first place?

The job of the DBA will morph and change - as it has already during its 30+ years of existence. But that doesn't mean it will become obsolte... just different.