Lesson 7	Manually switching log files
Objective	Force a log switch.

Manually Switching Log Files

Under normal circumstances, Oracle writes changes to redo a log file group until the files in that group become full. Then Oracle closes the files in that group, opens the files in the next group, and begins writing to those files. This process of moving to the next log file group is referred to as a log switch. If circumstances warrant, you can force Oracle to do a log switch at any time by issuing the following command:

ALTER SYSTEM SWITCH LOGFILE;

command to switch Log File Groups

This command to switch log file groups comes in handy when you need to add or drop members from a log file group that is currently in use. The ability to switch log files on demand means that you don't have to wait to perform maintenance. The next lesson provides a review of what you have learned in this module.

Resolving archived redo log gaps using incremental backups

Imagine that due to the network failure, some archived redo log files weren't shipped to the standby database and were deleted from the primary database according to the defined RMAN retention policy. As you can't jump to the next generated archived redo log file before successfully applying all of them one by one in subsequent order, you need to either create a standby database from scratch, or - Sure you have another option. By applying necessary incremental backup taken from the primary database, you can take the database forward and ?bypass? applying missing archived redo log files. In the following scenario we?re going to show the steps of recovering standby database without having archived redo log files

At first we need to have standby database with missing redo log files. For this:

Change the LOG_ARCHIVE_DEST_2 parameter on the primary site
Manually switch log files to create some archived redo log files
Delete generated archived redo log files from the primary site

First of all, check V$ARCHIVED_LOG file and get the last generated archived redo log sequence value on both databases, then change the LOG_ARCHIVE_DEST_2 initialization parameter on the primary site to block archived redo log shipping to the standby site as follows:

# Run the following code on the primary database:

SQL> SELECT max(sequence#) FROM v$archived_log;

MAX(SEQUENCE#)

--------------
44
# Run the following code on the standby database:

SQL> SELECT max(sequence#) FROM v$archived_log;

MAX(SEQUENCE#)

--------------
44
SQL> SHOW PARAMETER LOG_ARCHIVE_DEST_2


NAME                        TYPE        VALUE

--------------------------- ----------- ------------------------------
log_archive_dest_2          string      service=test optional reopen=15


SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='service=noservice';

System altered.

SQL>  SHOW PARAMETER LOG_ARCHIVE_DEST_2;

NAME                         TYPE        VALUE

---------------------------- ----------- ------------------------------
log_archive_dest_2            string      service=noservice

Now make some manual redo log switch and check the V$ARCHIVED_LOG view on both sites again:

# Run the following codes on the primary database:
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.

SQL> /
System altered.


SQL> /
System altered.


SQL> SELECT max(sequence#) FROM v$archived_log;
MAX(SEQUENCE#)
--------------
47

SQL>

# Run the following code on the standby database:

SQL> SELECT max(sequence#) FROM v$archived_log;

MAX(SEQUENCE#)

--------------
44

As you see, the last three archived redo log files were NOT shipped to the standby site. Go to the primary site and delete last generated three archived redo log files with sequence 45, 46 and 47:

Manually Switching log Files - Quiz

Click the Quiz link below to answer a few questions about redo logs.
Manually Switching log Files - Quiz