How To Enable/Disable Archive Log Mode In Oracle Database

There are 2 types of logging mode in oracle database.

1. Archivelog mode :

In this mode, after the online redo logs are filled , it will move to archive location.

2. Noarchivelog mode :

In this mode, filled online redo logs wont be archives, instead they will be overwritten.

How to Enable archive log mode :

 

SQL > select name,log_mode from v$database;
 
NAME      LOG_MODE
--------- -----------
PROD      NOARCHIVELOG
 

SQL > archive log list
Database log mode              No Archive Mode
Automatic archival             Disbled
Archive destination            /u01/archive/PROD
Oldest online log sequence     506
Next log sequence to archive   513
Current log sequence           513
 

---- make sure db is running in spfile

SQL > alter system set log_archive_dest_1='LOCATION=/u01/archive/PROD' scope=spfile;
database altered.
 

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

SQL > startup mount
ORACLE instance started.
Total System Global Area 6415597568 bytes
Fixed Size                  2170304 bytes
Variable Size             905970240 bytes
Database Buffers         5502926848 bytes
Redo Buffers                4530176 bytes
Database mounted.
 

SQL >alter database archivelog;
 
database altered.
 

SQL >alter database open;
 
database altered.
 

SQL >select name,log_mode from v$database;
 
NAME      LOG_MODE
--------- -----------
PROD      ARCHIVELOG
 

SQL >archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/archive/PROD
Oldest online log sequence     506
Next log sequence to archive   513
Current log sequence           513
 

How to Disable archivelog mode :

SQL >select name,log_mode from v$database;

 

NAME      LOG_MODE

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

PROD      ARCHIVELOG

 

SQL > archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /u01/archive/PROD

Oldest online log sequence     506

Next log sequence to archive   513

Current log sequence           513

 

 

SQL > shutdown  immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

 

SQL > startup mount

ORACLE instance started.

Total System Global Area 6415597568 bytes

Fixed Size                  2170304 bytes

Variable Size             905970240 bytes

Database Buffers         5502926848 bytes

Redo Buffers                4530176 bytes

Database mounted.

 

SQL >alter database noarchivelog;

database altered.

SQL >alter database open;

 

database altered.

SQL > select name,log_mode from v$database;

 

NAME      LOG_MODE

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

PROD      NOARCHIVELOG

 

SQL > archive log list

Database log mode              No Archive Mode

Automatic archival             Disbled

Archive destination            /u01/archive/PROD

Oldest online log sequence     506

Next log sequence to archive   513

Current log sequence           513

 

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning… Have a great day!!!

 

How to Rename or Move Redo log files in Oracle

It is possible to move/rename the online redo logs should the need arise. This document will detail the steps required to move/rename the online redo logs in an 12cR2 environment on Linux. These steps also apply to a 10g, and 11gR2 environment.

First we will verify the current location of the online redo log files :

SQL>

SQL>
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/redo03.log
/u01/app/oracle/oradata/ORCL/redo02.log
/u01/app/oracle/oradata/ORCL/redo01.log

We are going to move the online redo logs from /u01/app/oracle/oradata/ORCL/ to /u01/app/oracle/oradata/ORCL/redo/. The redo logs cannot be moved/renamed while the database is online. The database must be in a mount state to move/rename the online redo logs.

First we will shutdown the database and move the online redo logs to their new location :

SQL>
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@orakldba ~]$
[oracle@orakldba ~]$ mv /u01/app/oracle/oradata/ORCL/redo03.log /u01/app/oracle/oradata/ORCL/redo/redo03.log

[oracle@orakldba ~]$ mv /u01/app/oracle/oradata/ORCL/redo02.log /u01/app/oracle/oradata/ORCL/redo/redo02.log

[oracle@orakldba ~]$ mv /u01/app/oracle/oradata/ORCL/redo01.log /u01/app/oracle/oradata/ORCL/redo/redo01.log

[oracle@orakldba ~]$

Next we bring up the database into mount mode and issue ALTER DATABASE RENAME FILE statements to update the data dictionary and control files. The last thing we do is open the database.

SQL>
SQL> startup mount;
ORACLE instance started.
Total System Global Area  536870912 bytes
Fixed Size                  8622776 bytes
Variable Size             448793928 bytes
Database Buffers           75497472 bytes
Redo Buffers                3956736 bytes
Database mounted.

SQL>
SQL>
SQL> alter database rename file '/u01/app/oracle/oradata/ORCL/redo03.log' to '/u01/app/oracle/oradata/ORCL/redo/redo03.log';

Database altered.

SQL>
SQL> alter database rename file '/u01/app/oracle/oradata/ORCL/redo02.log' to '/u01/app/oracle/oradata/ORCL/redo/redo02.log';

Database altered.

SQL>
SQL> alter database rename file '/u01/app/oracle/oradata/ORCL/redo01.log' to '/u01/app/oracle/oradata/ORCL/redo/redo01.log';

Database altered.

SQL>
SQL> alter database open;

Database altered.

We can see that the changes were made in the data dictionary by issuing the following query again.

SQL>
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/redo/redo03.log
/u01/app/oracle/oradata/ORCL/redo/redo02.log
/u01/app/oracle/oradata/ORCL/redo/redo01.log

SQL>

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning… Have a great day!!!

 

How To Rename Or Move A Datafile In Oracle

 

In Oracle 12c :

 

If you are in 12c version ,then renaming a datafile can be done online, without making the datafile offline.

 

SQL>
SQL> select file_name,tablespace_name,online_status from dba_data_files where tablespace_name='SHASHI';
FILE_NAME                                                              TABLESPACE_NAME                ONLINE_
---------------------------------------------------------------------- ------------------------------ -------
/u01/app/oracle/oradata/ORCL/shashi.dbf                                SHASHI                         ONLINE

 

SQL>
SQL>
SQL> alter database move datafile '/u01/app/oracle/oradata/ORCL/shashi.dbf' to '/home/oracle/shashi01.dbf';
Database altered.

 

SQL>
SQL>
SQL> select file_name,tablespace_name,online_status from dba_data_files where tablespace_name='SHASHI';
FILE_NAME                                                              TABLESPACE_NAME                ONLINE_
---------------------------------------------------------------------- ------------------------------ -------
/home/oracle/shashi01.dbf                                              SHASHI                         ONLINE

 

 

 

In 11g and previous versions :

 

If you are in 11g or previous version, you need to follow below steps to move or rename a datafile without shutting down the database.

 

•        First make the datafile offline.
•        move the datafile as os level.
•        Rename the datafile at database level.
•        recover the particular datafile.
•        make the datafile online.


SQL>
SQL> set lines 200 pages 100
SQL> col FILE_NAME for a70
SQL>
SQL> select file_name,tablespace_name,online_status from dba_data_files where tablespace_name='SHASHI';
FILE_NAME                                                              TABLESPACE_NAME                ONLINE_
---------------------------------------------------------------------- ------------------------------ -------
/u01/app/oracle/oradata/ORCL/shashi.dbf                                SHASHI                         ONLINE

 

SQL>
SQL>
SQL> alter database datafile '/u01/app/oracle/oradata/ORCL/shashi.dbf' offline;
Database altered.

 

SQL>
SQL>
SQL> select file_name,tablespace_name,online_status from dba_data_files where tablespace_name='SHASHI';
FILE_NAME                                                              TABLESPACE_NAME                ONLINE_
---------------------------------------------------------------------- ------------------------------ -------
/u01/app/oracle/oradata/ORCL/shashi.dbf                                SHASHI                         RECOVER

 

SQL>
SQL>
SQL> !mv /u01/app/oracle/oradata/ORCL/shashi.dbf /home/oracle/shashi01.dbf

 

SQL>
SQL>
SQL> alter database rename file '/u01/app/oracle/oradata/ORCL/shashi.dbf' to '/home/oracle/shashi01.dbf';
Database altered.

 

SQL>
SQL>
SQL> alter database datafile '/home/oracle/shashi01.dbf' online;
alter database datafile '/home/oracle/shashi01.dbf' online
*
ERROR at line 1:
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: '/home/oracle/shashi01.dbf'

 

SQL>
SQL>
SQL> recover datafile 6;
Media recovery complete.

 

SQL>
SQL>
SQL>
SQL> alter database datafile '/home/oracle/shashi01.dbf' online;
Database altered.

 

SQL>
SQL>
SQL> select file_name,tablespace_name,online_status from dba_data_files where tablespace_name='SHASHI';
FILE_NAME                                                              TABLESPACE_NAME                ONLINE_
---------------------------------------------------------------------- ------------------------------ -------
/home/oracle/shashi01.dbf                                              SHASHI                         ONLINE

 

 

 

 

I hope this article helped you. Your suggestions/feedback are most welcome.
Keep learning… Have a great day!!!