Oracle 18c disable / enable Archive Log Mode

Disable Archive Log Mode

[oracle@z-fbhost2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 – Production on Wed Aug 8 10:17:47 2018
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle. All rights reserved.

Connected to an idle instance.

# Open database in mount state
SQL> startup mount
ORACLE instance started.

Total System Global Area 1.4227E+11 bytes
Fixed Size 29900024 bytes
Variable Size 2.2549E+10 bytes
Database Buffers 1.1919E+11 bytes
Redo Buffers 506970112 bytes
Database mounted.

# Check archive log status
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 11646
Next log sequence to archive 11646
Current log sequence 11647

# Disable archive log mode
SQL> alter database noarchivelog;

Database altered.

# Open Database
SQL> alter database open;

Database altered.

# Confirm archive log status
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 11647
Current log sequence 11648

SQL> show parameter log_archive_start

NAME TYPE VALUE
———————————— ———– ——————————
log_archive_start boolean FALSE

Enable Archive Log Mode

Shutdown the database using ‘shutdown immediate’ rather than ‘abort’ to avoid “ORA-00265 instance recovery required, cannot set ARCHIVELOG mode”

[oracle@z-fbhost2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 – Production on Wed Aug 8 10:31:01 2018
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle. All rights reserved.

Connected to an idle instance.

# Startup in mount state
SQL> startup mount
ORACLE instance started.

Total System Global Area 1.4227E+11 bytes
Fixed Size 29900024 bytes
Variable Size 2.2549E+10 bytes
Database Buffers 1.1919E+11 bytes
Redo Buffers 506970112 bytes
Database mounted.

# Check archive log status
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 11648
Current log sequence 11649

# Enable archive log
SQL> alter database archivelog;

Database altered.

# Open database
SQL> alter database open;

Database altered.

# Check archive log status
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 11648
Next log sequence to archive 11649
Current log sequence 11649

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s