How to Resolve ORA-02065: illegal option for ALTER SYSTEM

ORA-02065

There're 2 error patterns of ORA-02065 in this post.

1. Enabling Archivelog Mode
2. Set DB_RECOVERY_FILE_DEST_SIZE

Enabling Archivelog Mode

Some may try to enable archivelog mode like the followings, but it failed with ORA-02065:

SQL> alter system archivelog;
alter system archivelog
             *
ERROR at line 1:
ORA-02065: illegal option for ALTER SYSTEM

ORA-02065 means that the operation at system-level is not valid, which means your ALTER SYSTEM statement is illegal syntactically. Actually, you should enable archivelog mode at database-level which uses ALTER DATABASE.

SQL> alter database archivelog;

Database altered.

Not all archived log related operations are invalid at system-level, at least, there's one operation on archived log is valid to use ALTER SYSTEM.

Explicit Archiving

To make current redo log archive and switch to the next redo, we can explicitly perform an archival.

SQL> alter system archive log current;

System altered.

I have talked about it in the post: ALTER SYSTEM SWITCH LOGFILE vs ARCHIVE LOG CURRENT.

Set DB_RECOVERY_FILE_DEST_SIZE

When we tried to increase the size of Fast Recovery Area (FRA), we got ORA-02065.

SQL> alter system set db_recovery_file_dest_size=1500GB sid='*' scope=both;
alter system set db_recovery_file_dest_size=1500GB sid='*' scope=both
                                                *
ERROR at line 1:
ORA-02065: illegal option for ALTER SYSTEM

This is because the valid size units of DB_RECOVERY_FILE_DEST_SIZE are T, G, M and K, you don't need to append B after them.

SQL> alter system set db_recovery_file_dest_size=1500G sid='*' scope=both;

System altered.

We made it.