iSelfSchooling - Advanced DBA Hands-On #06-Duplexing Archived Online Redo Log files

You, as a DBA, are responsible to duplex archived Online Redo log files in order to protect the organization from a loss of or a case of corrupted archived files. Your job’s responsibilities dictate that you should be at least informed of the following basic fundamental subjects:

Duplexing Archived Online Redo Log files

Setting an Oracle-Managed File destination

Setting a Redo Log Archive Duplex Destination

Querying all of the archive log destinations

Using the LOG_ARCHIVE_DUPLEX_DEST parameter

Using the V$ARCHIVE_DEST view

Using the V$ARCHIVED_LOG view

Checking the sequence archive number

Writing a procedure to generate some logs

Using the V$LOG_HISTORY view

FIRST_CHANGE#

NEXT_CHANGE#

De-activating the duplex archiving

Commands:

ALTER SYSTEM SET

SHOW PARAMETER

Hands-on

In this exercise you will learn how to duplex an Archived Online Redo Log file(s). You will also learn how to set the Oracle-Managed File destination and set the Redo Log Archive Duplex Destination.

Now, connect to SQLPlus as the SYSTEM/MANAGER user to start.
SQL> CONNECT system/manager AS SYSDBA

View archive log destinations
Query all of the archive log destinations.
SQL> SHOW PARAMETER %archive%dest
Make a note on the LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST.

Oracle-Managed File for archive log destionation
Let's first make a directory and set the LOG_ARCHIVE_DUPLEX_DEST parameter to configure a secondary archive log destination.
SQL> HOST MKDIR c:ndArclogs
SQL> ALTER SYSTEM SET log_archive_duplex_dest='c:ndArclogs'
/
Notice that when the scope is omitted, that means the System Parameter File (SPFILE) will be changed in both the memory and the SPFILE.

Query the V$ARCHIVE_DEST view.
SQL> SELECT status, schedule, destination
FROM v$archive_dest;
Notice that now, I have two archive log destinations.

Check archive log destinations
Check the last 8 archive logs.
SQL> SELECT * FROM v$archived_log
WHERE recid >
(SELECT MAX(recid) - 8 FROM v$archived_log)
/

Check the sequence archive number. Also notice that there is only one archive log destination.

Let's write a procedure to generate some logs in order to test if the archiving is working and if it writes into two archive log destinations.
SQL> BEGIN
SQL> -- This procedure does nothing. It inserts records into
SQL> -- the table and then it will delete them.
SQL> -- The purpose of this procedure is to create logs.
SQL> FOR i IN 1..25 LOOP
SQL> FOR j IN 1..100 LOOP
SQL> INSERT INTO iself.emp
SQL> VALUES (j,'AJ','CLERK',7788,'23-MAR-86',1000,null,30);
SQL> COMMIT;
SQL> END LOOP;
SQL> FOR j IN 1..100 LOOP
SQL> DELETE FROM iself.emp
SQL> WHERE empno = j;
SQL> COMMIT;
SQL> END LOOP;
SQL> END LOOP;
SQL> END;
SQL> /

Now, check the last 8 archived logs.
SQL> SELECT * FROM v$archived_log
WHERE recid > (SELECT MAX(recid) - 8 FROM v$archived_log)
/
Notice the DEST_ID values, 1 and 2. This demonstrates that how the Oracle database archives the Online Redo Log files into two archive destinations (ARCHICELOGS and MY2NDARCLOGS).

Check the last 10 log history using the V$LOG_HISTORY view.
SQL> SELECT * FROM v$log_history
WHERE recid > (SELECT MAX(recid) - 10 FROM v$log_history)
/
Notice the first_change# and next_change# columns. The next_change# column indicates the last SCN number in the archive log.

Now, deactivate the duplex archiving. Also, remember that you cannot duplex archive log files to a remote destination or a remote standby database. But, you can multiplex an archive log file to a remote destination or a remote standby database.
SQL> ALTER SYSTEM SET log_archive_duplex_dest=''
/

Now, write a procedure to generate some logs to test if the archiving is working and if it writes in only one archivelog file.
SQL> BEGIN
SQL> -- This procedure does nothing. It inserts records into
SQL> -- the table and then it will delete them.
SQL> -- The purpose of this procedure is to create logs.
SQL> FOR i IN 1..25 LOOP
SQL> FOR j IN 1..100 LOOP
SQL> INSERT INTO iself.emp
SQL> VALUES (j,'AJ','CLERK',7788,'23-MAR-86',1000,null,30);
SQL> COMMIT;
SQL> END LOOP;
SQL> FOR j IN 1..100 LOOP
SQL> DELETE FROM iself.emp
SQL> WHERE empno = j;
SQL> COMMIT;
SQL> END LOOP;
SQL> END LOOP;
SQL> END;
SQL> /

Now, check the last 8 archive logs.
SQL> SELECT * FROM v$archived_log
WHERE recid > (SELECT MAX(recid) - 8 FROM v$archived_log)
/
Notice that you are only writing into one destination this time.

“I've been thinking about that old Zen conundrum: what's the sound of one hand clapping? My personal opinion--nothing. You don't have two hands, you don't have any clapping. It's as simple as that. Stars, galaxies, clapping hands, what's the point? The point is that we all need somebody, whether you're a supercluster or a little proton, a yin or a yang. Everybody is hooked into everybody else.” Geoffrey Neighor, Northern Exposure, Duets, 1993

Questions:

Q: How do you set an Oracle-Managed archive log file destination?

Q: Describe an Oracle-Managed File (OMF).

Q: What are the following views?

V$ARCHIVE_DEST view

V$ARCHIVED_LOG view

V$LOG_HISTORY view

Q: What is the Sequence Archive log number?

Q: You, as a DBA, are responsible to duplex archived Online Redo log files in order to protect the organization from a loss of or a case of corrupted archived files. Take one of the redo log file group and add a member to it in a different disk. What are the steps?