Alter database VS Alter system

Alter system

alter database commands cannot be audited; where as alter system can

alter database needs bouncing(reboot) database but alter system doesn't 

An "alter system" command is (mostly) only possible in status OPEN. The only exception from this I recall presently is 

"alter system set param=value" to modify initialization parameters. That is already possible in NOMOUNT status.

ALTER SYSTEM is an Instance Level command. Generally this applies for running processes, parameters etc. (however "ALTER SYSTEM DUMP" seems to be an exception).

Use the ALTER SYSTEM statement to dynamically alter your Oracle instance. The settings stay in effect as long as the database is mounted. HOWEVER, if you wish to make the changes permanent then the SPFile must be amended.

Alter database

but ALTER DATABASE BACKUP CONTROL FILE TO TRACE; doesn't require bouncing.

"alter database" on the other hand is already possible in status MOUNT, where tablespaces including the system tablespace that contains audit information is not accessible. That is why "alter database" cannot be audited probably.

As a rule of thumb: You can do "alter database" in MOUNT already, but "alter system" only in status OPEN.

ALTER DATABASE is a Database Level command. Generally, this applies to the physical structure of the database.

Think of an RAC environment. Most ALTER SYSTEM commands (ALTER SYSTEM DUMP is one exception) are local to the instance (although SET can set for multiple instances). The ALTER DATABASE commands are for the whole database.

Use the ALTER DATABASE statement to modify, maintain, or recover an existing database.



I hope you all have enjoyed reading this article. Comments are welcome....