- 10 May, 2019
- 0 Comments
- 3 Mins Read
Archive log Mode and No Archive Log Mode in Oracle 12C Database
In this blog we will see you how to put database in archive log mode and no archive log mode.
Oracle database have two kind of log mode
- Archive log mode
- No Archive log mode.
Database can be put in archive log mode whenever we want to take hot backup. Roll forward is possible with the help of archive. Hence point in time recovery can be done when enough archives are available.
Database put in no archive log mode when maintenance activities happens on database and archive does not need to be generated.
Following is the possible way to put database in archive log mode.
We need to shutdown the database services in order to change the log mode of oracle database. Always take the approval from business as this activity need downtime.
- First set up the environment for database using .oraenv script and connect to sqlplus developer utility.
Connect to sqlplus and check the current log mode of oracle Database.
$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Sun May 12 15:45:34 2019
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
2. Check the current log mode of the database. here the automatic archival is disabled and database is in no archive log mode.
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 16
Current log sequence 18
SQL>
SQL>
3. First we need to start the automatic archival process.
SQL> ALTER SYSTEM ARCHIVE LOG START;
4. Change the log archive destination of the database. all new archives will be generated to this location.
SQL> alter system set log_archive_dest_1='LOCATION=/data/archive' scope=both;
System altered.
5. In above steps we have changed the automatic archival and log archive location, here we are going to check if the above changes reflect to database.
No changes has been recorded as we need to bounce the database to get the new changes.
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /data/archive
Oldest online log sequence 16
Current log sequence 18
SQL>
6. We need to put database in mount stage to change the log mode of the database.
SQL> alter database archivelog;
alter database archivelog
*
ERROR at line 1:
ORA-01126: database must be mounted in this instance and not open in any
instance
7. Shutdown the database.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
8. Open the database in mount state to change the archive log mode.
SQL> startup mount
ORACLE instance started.
Total System Global Area 411041792 bytes
Fixed Size 2925024 bytes
Variable Size 276827680 bytes
Database Buffers 125829120 bytes
Redo Buffers 5459968 bytes
Database mounted.
SQL>
9. Use following alter command to put database in archive log.
SQL> alter database archivelog;
Database altered.
10. Here we can see the database is in archive log mode and automatic archival is also enabled.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /data/archive
Oldest online log sequence 16
Next log sequence to archive 18
Current log sequence 18
SQL>
I am switching the logfile as I am interested to see if the archive logs genereated to the new location.
SQL> alter system switch logfile;
System altered.
Here we can see archives has been generated with current date timestamp.
[oracle@oracle data]$ ls -lrt /data/archive
total 3600
-rw-r—– 1 oracle oinstall 3652096 May 10 20:40 1_18_1007726002.dbf
-rw-r—– 1 oracle oinstall 2048 May 10 20:40 1_19_1007726002.dbf
-rw-r—– 1 oracle oinstall 3584 May 10 20:40 1_20_1007726002.dbf
-rw-r—– 1 oracle oinstall 1024 May 10 20:40 1_21_1007726002.dbf
if you want the above process live , you can go through the following videos. Please do like,subscribe and share the channel for future videos.