November 30, 2015

Oracle archive log summary

Obviously this will not be even a quarter of what archive logs are up to in comparison to official oracle documentation, but just this is just a simple remainder of how to turn archive logs on and off if required.

Two remainders:
  1. A database restart is required in every case
  2. A full backup is required after switching the database to archivelog mode again.



--Check the status of everything.
select NAME, LOG_MODE, ARCHIVELOG_COMPRESSION, ARCHIVE_CHANGE#, OPEN_MODE from V$DATABASE;
 ARCHIVE LOG LIST;
show parameter recovery_file_dest;


SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
----------------------------------------
--If turning off the archive logs:
ALTER DATABASE NOARCHIVELOG;
--If turning on the archive logs:
ALTER DATABASE ARCHIVELOG;
-----------------------------------------
COMMIT;
ALTER DATABASE OPEN;
--This one is not really required, but will not cause any harm restarting the database again.
SHUTDOWN IMMEDIATE;
STARTUP;

--Check the status of everything again after the modification.
select NAME, LOG_MODE, ARCHIVELOG_COMPRESSION, ARCHIVE_CHANGE#, OPEN_MODE from V$DATABASE;
 ARCHIVE LOG LIST;
show parameter recovery_file_dest;