ARCHIVELOG MODE

How to Enable archive log mode

Once you have configured the flash recovery area, you can put the database in ARCHIVELOG mode. Unfortunately, this requires that the database be shutdown first with the shutdown command (however, from earlier in the chapter, we note that shutdown immediate is the best option). Once you have shutdown the database, you will start the database in mount Stage with the startup mount command. Then put the database in ARCHIVELOG mode, and finally open the database. Here is an example of how this all works from the command line.

SQL> archive log list;


Database log mode               No Archive Mode
Automatic archival              Disabled
Archive destination             d:oracleoradataDB10Garchive
Oldest online log sequence      427
Current log sequence            429

SQL> shutdown immediate;

Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area 1610609200 bytes
Fixed Size               8897072 bytes
Variable Size            385875968 bytes
Database Buffers         1207959552 bytes
Redo Buffers             7876608 bytes
Database mounted.


SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list;

Database log mode                  Archive Mode
Automatic archival                 Enabled
Archive destination                d:oracleoradataDB10Garchive
Oldest online log sequence         426
Next log sequence to archive       427
Current log sequence               428

How to Disable archive log mode 

SQL> archive log list;

Database log mode                Archive Mode
Automatic archival               Enabled
Archive destination              d:oracleoradataDB10Garchive
Oldest online log sequence       426
Next log sequence to archive     427
Current log sequence             428


SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area 1610609200 bytes
Fixed Size               8897072 bytes
Variable Size            385875968 bytes
Database Buffers         1207959552 bytes
Redo Buffers             7876608 bytes
Database mounted.



SQL> alter database noarchivelog;

Database altered.

SQL> alter database open;

Database altered.


SQL> archive log list;

Database log mode                NoArchive Mode
Automatic archival               Enabled
Archive destination              d:oracleoradataDB10Garchive
Oldest online log sequence       426
Next log sequence to archive     427
Current log sequence             428

Change Archive log mode and Destination in oracle 19c

[oracle@training ~]$ export ORACLE_SID=training1

[oracle@training ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jul 28 23:30:16 2021
Version 19.3.0.0.0

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

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 2432695144 bytes
Fixed Size                  8899432 bytes
Variable Size             536870912 bytes
Database Buffers         1879048192 bytes
Redo Buffers                7876608 bytes
Database mounted.
Database opened.

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     89
Current log sequence           91


SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - 
Production Version 19.3.0.0.0

[oracle@training ~]$ mkdir -p /u01/ARC_BKP

[oracle@training ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jul 28 23:38:09 2021
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

To check the current archiving location

SQL> SHOW PARAMETER DB_RECOVERY_FILE_DEST

NAME                       TYPE            VALUE
---------------------  ---------------   --------------
db_recovery_file_dest       string      /u01/app/oracle /fast_recovery_area
                                                 
db_recovery_file_dest_size  big integer  8256M

SQL> alter system set DB_RECOVERY_FILE_DEST='/u01/ARC_BKP';

System altered.

SQL> alter system set log_archive_dest=’/u01/ARC_BKP’;

System altered.

SQL> alter database close;

Database altered.

SQL> alter database archivelog;

Database altered.


SQL> shut immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 -
 Production Version 19.3.0.0.0
[oracle@training u01]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jul 28 23:52:05 2021
Version 19.3.0.0.0

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

Connected to an idle instance.


SQL> startup
ORACLE instance started.

Total System Global Area 2432695144 bytes
Fixed Size                  8899432 bytes
Variable Size             536870912 bytes
Database Buffers         1879048192 bytes
Redo Buffers                7876608 bytes

Database mounted.

Database opened.

SQL> archive log list;
Database log mode                Archive Mode
Automatic archival               Enabled
Archive destination              /u01/ARC_BKP
Oldest online log sequence       89
Next log sequence to archive     91
Current log sequence             91

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8