DBA Fundamentals

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:

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 oracle.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 oracle.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 oracle.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 oracle.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.

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?