iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Training

    Home      .Services     Login       Start Learning     Certification      .                 .Share your BELIEF(s)...

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       Email2aFriend    |

 

Online Oracle Training for beginners and advanced - The most comprehensive Oracle tutorial

The authors do not guarantee or take any responsibility for the accuracy, or completeness of the information.

DBAs - Fundamentals II

 

Lesson 01 | Lesson 02 | Lesson 03 | Lesson 04 | Lesson 05 | Lesson 06 | Lesson 07 | Lesson 08 | Lesson 09 | Lesson 10 | Lesson 11 | Lesson 12 | Lesson 13 | Lesson 14 | Lesson 15 | Lesson 16 | Lesson 17 | Lesson 18 | Lesson 19 | Lesson 20 | Lesson 21 | Lesson 22 | Lesson 23 | Lesson 24 | Lesson 25 | Lesson 26 | Lesson 27 | Lesson 28 | Lesson 29 |

 

Lesson 05

"Life is far too important a thing ever to talk seriously about." Oscar Wilde (1854 - 1900), Lady Windermere's Fan, 1892, Act I

 

Read first then play the video:

   DBA-VIDEO -Duplexing Archived Online Redo Log Files (Advanced)

   

Duplexing Archived Online Redo Log files

 

Introduction

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?