"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?
|