Skip to content
Home » Oracle Database » How to Resolve ORA-02065: illegal option for ALTER SYSTEM

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.

3 thoughts on “How to Resolve ORA-02065: illegal option for ALTER SYSTEM”

  1. ALTER SYSTEM SET partitioning = TRUE SCOPE=SPFILE;
    ALTER SYSTEM SET partitioning = TRUE SCOPE=SPFILE
    *
    ERROR at line 1:
    ORA-02065: illegal option for ALTER SYSTEM

    How to solve that problem?

    1. PARTITIONING is not a valid parameter, so you can’t enable it in this way. In fact, table partitioning is an optional feature for enterprise edition. Extra cost is required to have the feature. Please refer to Oracle Database Licensing for more information.

Leave a Reply

Your email address will not be published. Required fields are marked *