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 06 (Duplexing Archived Online Redo Log files)
-- Preparation
SET ECHO ON
SET linesize 1000 pagesize 55
COL name FORMAT a30
col description format a30
pause

--Start

CLEAR SCR
-- 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.

pause

CONNECT system/manager AS SYSDBA

pause

CLEAR SCR
-- Query all of the archive log destinations.

pause
pause
SHOW PARAMETER %archive%dest
-- Make a note on the LOG_ARCHIVE_DEST and 
-- LOG_ARCHIVE_DUPLEX_DEST.
pause
pause


CLEAR SCR
-- Let's first make a directory and set the 
-- LOG_ARCHIVE_DUPLEX_DEST parameter to configure a 
-- secondary archive log destination.

pause

HOST MKDIR c:\my2ndArclogs

ALTER SYSTEM SET log_archive_duplex_dest='c:\my2ndArclogs'
/

-- Notice that when the scope is omitted, that means the System 
-- Parameter File (SPFILE) will be changed in both the memory and
-- the SPFILE.

pause

CLEAR SCR
-- Query the V$ARCHIVE_DEST view.

pause

SELECT status, schedule, destination 
FROM v$archive_dest;

-- Notice that now, I have two archive log destinations.

pause

CLEAR SCR
-- Check the last 8 archive logs.

pause

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.

pause


CLEAR SCR
-- 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.
-- Please wait...
pause

BEGIN
-- This procedure does nothing. It inserts records into 
-- the table and then it will delete them.
-- The purpose of this procedure is to create logs.
FOR i IN 1..25 LOOP
FOR j IN 1..100 LOOP
INSERT INTO iself.emp
VALUES (j,'AJ','CLERK',7788,'23-MAR-86',1000,null,30);
COMMIT;
END LOOP;

FOR j IN 1..100 LOOP
DELETE FROM iself.emp
WHERE empno = j;
COMMIT;
END LOOP; 

END LOOP;
END;
/

pause

CLEAR SCR
-- Now, check the last 8 archived logs.

pause
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).

pause


CLEAR SCR
-- Check the last 10 log history using the V$LOG_HISTORY view.

pause


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.

pause


CLEAR SCR
-- 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.

pause

ALTER SYSTEM SET log_archive_duplex_dest=''
/

pause

CLEAR SCR
-- Now, write a procedure to generate some logs to 
-- test if the archiving is working and if it writes in
-- only one archivelog file.
-- Please wait...
pause

BEGIN
-- This procedure does nothing. It inserts records into 
-- the table and then it will delete them.
-- The purpose of this procedure is to create logs.
FOR i IN 1..25 LOOP
FOR j IN 1..100 LOOP
INSERT INTO iself.emp
VALUES (j,'AJ','CLERK',7788,'23-MAR-86',1000,null,30);
COMMIT;
END LOOP;

FOR j IN 1..100 LOOP
DELETE FROM iself.emp
WHERE empno = j;
COMMIT;
END LOOP; 

END LOOP;
END;
/

pause

CLEAR SCR
-- Now, check the last 8 archive logs.

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

pause

CLEAR SCR
-- Now, you should practice this Hands-On exercise.

-- For more information about the subject, you are encouraged
-- to read from a wide selection of available books.

-- Good luck.
--
pause
pause