As DBA we are aware that Oracle is a mixture of CRD files. Now we are going to have look at Redolog.
Let’s have a technical definition of Redolog.
The most crucial structure for recovery operations is the redo log, which consists of two or more preallocated files that store all changes made to the database as they occur. Every instance of an Oracle Database has an associated redo log to protect the database in case of an instance failure.
Check Redo Log file Status: with the help of given select query.
SQL> select group#,status from v$log; GROUP# STATUS ---------- ---------------- 1 CURRENT 2 INACTIVE 3 INACTIVE
The log files have the following status values:
- USED Indicates either that a log has just been added but never used.
- CURRENT Indicates a valid log that is in use.
- ACTIVE Indicates a valid log file that is not currently in use.
- CLEARING Indicates a log is being re-created as an empty log due to DBA action.
- CLEARING CURRENT Means that a current log is being cleared of a closed thread. If a log stays in this status, it could indicate there is some failure in the log switch.
- INACTIVE Means that the log is no longer needed for instance recovery but may be needed for media recovery.
The v$logfile table has a status indicator that gives these additional codes:
- INVALID File is inaccessible.
- STALE File contents are incomplete (such as when an instance is shut down with SHUTDOWN ABORT or due to a system crash).
- DELETED File is no longer used.
- Null File in use.
Adding Redo Log Groups:
SQL> ALTER DATABASE ADD LOGFILE GROUP 4 'D:\oracle\product\11.2.0.4\oradata\orcl\REDO04.LOG' SIZE 10M;
Adding Redo Log Members:
SQL> ALTER DATABASE ADD LOGFILE MEMBER 'D:\oracle\product\11.2.0.4\oradata\orcl\REDO04b.LOG' TO GROUP 4;
Check the file Location of redo log files:
SQL> select group#,member from v$logfile; GROUP# MEMBER ----- -------------------------------------------------------- 3 D:\oracle\product\11.2.0.4\oradata\orcl\REDO03.LOG 2 D:\oracle\product\11.2.0.4\oradata\orcl\REDO02.LOG 1 D:\oracle\product\11.2.0.4\oradata\orcl\REDO01.LOG 4 D:\oracle\product\11.2.0.4\oradata\orcl\REDO04.LOG 4 D:\oracle\product\11.2.0.4\oradata\orcl\REDO04B.LOG
Dropping Online Redo Log Member:
SQL> ALTER DATABASE DROP LOGFILE MEMBER 'D:\oracle\product\11.2.0.4\oradata\orcl\REDO04B.LOG';
Dropping Online Redo Log Groups:
SQL> ALTER DATABASE DROP LOGFILE GROUP 4;
Move Redo Log File Destinations
SQL>SHUTDOWN; Copy the redo log file in new location. SQL> STARTUP MOUNT; SQL> ALTER DATABASE RENAME FILE 'D:\oracle\product\11.2.0.4\oradata\orcl\REDO01.LOG' TO 'D:\oracle\product\11.2.0.4\oradata\orcl\redologfile\REDO01.LOG'; SQL> alter database open;
Forcing Log Switch:
SQL> ALTER SYSTEM SWITCH LOGFILE;
Forcing Checkpoint:
SQL> ALTER SYSTEM CHECKPOINT;
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:
Telegram Channel : https://t.me/helporacle
Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/
Joel Perez’s LinkedIn: Joel Perez’s Profile
LinkedIn Group: Oracle Cloud DBAAS
Facebook Page: OracleHelp
