Change database in archive or unarchive mode

Unachieved database

Bring database in mount state first.

SQL>  shu immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>  startup  mount;

ORACLE instance started.

Total System Global Area  910163968 bytes

Fixed Size                  2024976 bytes

Variable Size             255855088 bytes

Database Buffers          645922816 bytes

Redo Buffers                6361088 bytes

Database mounted.

SQL> alter database noarchivelog;

Database altered.

SQL> archive log list;

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     37

Current log sequence           39

Achieved database

Bring database in mount state first.

SQL>  startup  mount;

ORACLE instance started.

SQL> archive log list;

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     37

Current log sequence           39

SQL> alter database archivelog;

Database altered.

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     37

Next log sequence to archive   39

Current log sequence           39

SQL> alter database open;

Set archive destination

SQL> alter  system  set LOG_ARCHIVE_DEST_1='location=/home/oracle';

System altered.

You can also set FRA as archive destination

SQL>  alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST';

System altered.

If you writing syntax wrong

LOG_ARCHIVE_DEST_1=USE_DB_RECOVERY_FILE_DEST scope=both;

 alter  system  set LOG_ARCHIVE_DEST_1=USE_DB_RECOVERY_FILE_DEST scope=both

*

ERROR at line 1:

ORA-32017: failure in updating SPFILE

ORA-16179: incremental changes to "log_archive_dest_1" not allowed with SPFILE

You can check FRA used and free size using below query

SQL>  SELECT FILE_TYPE "Type",

  2  PERCENT_SPACE_USED "% Used",

  3  PERCENT_SPACE_RECLAIMABLE "% Reclaim",

  4  NUMBER_OF_FILES "# Files"

  5  FROM V$FLASH_RECOVERY_AREA_USAGE;

Type             % Used  % Reclaim    # Files

------------ ---------- ---------- ----------

CONTROLFILE           0          0          0

ONLINELOG             0          0          0

ARCHIVELOG        11.47          0         10

BACKUPPIECE           0          0          0

IMAGECOPY             0          0          0

FLASHBACKLOG          0          0          0

View to see archiving related  information’s.

V$DATABASE

Identifies whether the database is in ARCHIVELOG or NOARCHIVELOG mode and whether MANUAL (archiving mode) has been specified.

V$ARCHIVED_LOG

Displays historical archived log information from the control file. If you use a recovery catalog, the RC_ARCHIVED_LOG view contains similar information.

V$ARCHIVE_DEST

Describes the current instance, all archive destinations, and the current value, mode, and status of these destinations.

V$ARCHIVE_PROCESSES

Displays information about the state of the various archive processes for an instance.

V$BACKUP_REDOLOG

Contains information about any backups of archived logs. If you use a recovery catalog, the RC_BACKUP_REDOLOG contains similar information.

V$LOG

Displays all redo log groups for the database and indicates which need to be archived.

V$LOG_HISTORY

Contains log history information such as which logs have been archived and the SCN range for each archived log.