Mode of Logging
There are two types of logging modes in Oracle Database :
ARCHIVELOG :
In this type of logging whatever oracle writes in a redo log file related to transactions in database, saved to another location after a log file has been filled . This location is called Archive location. if database is in Archive log mode then in case of any disaster, we can recover our database upto the last commit and user don’t have to reenter their data. Until a redo log file is not written to the Archive location it cannot be reused by oracle to write redo related data.
NOARCHIVELOG :
In this type of logging whatever oracle writes in a redo log file related to transactions in database must be overwritten when all the log files have been filled. In this type of logging we can recover our database upto the last consistent backup we have with us, after that users have to reenter their data.
How to check log mode in Oracle Database 10g/11g :
[cognos@rac1 u02]$ sqlplus SQL*Plus: Release 11.2.0.1.0 Production on Thu Oct 25 23:03:44 2012 Copyright (c) 1982, 2009, Oracle. All rights reserved. Enter user-name: /as sysdba Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> archive log list Database log mode No Archive Mode Automatic archival Disabled Archive destination /backup/orcl/ Oldest online log sequence 1 Current log sequence 1
SQL> select name,log_mode from v$database; NAME LOG_MODE --------- - ----------- ORCL NOARCHIVELOG Currently the ORCL database is in NOARCHIVELOG mode
To change the Oracle database in ARCHIVELOG mode. Below mentioned steps :
1. If needed set the archive log destination where you want to save your archive logs whether to a single location or to multiple location. If this is not set then Oracle save archive log files in DB_RECOVERY_FILE_DEST location if set. If you have not set your DB_RECOVERY_FILE_DEST location then you have to set your archive location before changing your database to ARCHIVELOG mode.
SQL> alter system set log_archive_dest_1='LOCATION=/u02/archive' scope=spfile; System altered. Note -- To change this parameter while database is open, your database has to run with SPFILE, if running through PFILE then shut down your database and make changes in your PFILE and then start the database in MOUNT mode using that changed PFILE
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1025298432 bytes Fixed Size 1341000 bytes Variable Size 322963896 bytes Database Buffers 696254464 bytes Redo Buffers 4739072 bytes Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> archive log list
Database log mode Archive Mode Automatic archival Enabled Archive destination /u02/archive Oldest online log sequence 1 Next log sequence to archive 1 Current log sequence 1
SQL> select name,log_mode from v$database;
NAME LOG_MODE
--------- ------------
ORCL ARCHIVELOG
Database changed to ARCHIVELOG mode.
Note :- After you changed your database to ARCHIVELOG mode, take a backup of your database immediately because in recovery scenarios you can recover your database from the last backup taken in this mode. #############################################################################
To change the Oracle database in NOARCHIVELOG mode. Below mentioned steps:
1.Shutdown your running database.
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
2. Start your database in MOUNT mode.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1025298432 bytes Fixed Size 1341000 bytes Variable Size 322963896 bytes Database Buffers 696254464 bytes Redo Buffers 4739072 bytes Database mounted.
SQL> alter database noarchivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u02/archive
Oldest online log sequence 1
Current log sequence 1
SQL> select name,log_mode from v$database;
NAME LOG_MODE --------- ------------ ORCL NOARCHIVELOG
Standby Database Process Status:
You can run following query on standby database to see what MRP and RFS processes are doing, which block of which archivelog sequences are being shipped or being applied.
SQL> select process, status, thread#, sequence#, block#, blocks from v$managed_standby ;
PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH CLOSING 1 69479 932864 261
ARCH CLOSING 1 69480 928768 670
ARCH CLOSING 2 75336 933888 654
ARCH CLOSING 2 78079 930816 842
ARCH CLOSING 1 69475 943104 79
RFS IDLE 0 0 0 0
...
RFS RECEIVING 1 69481 688130 1024
MRP0 WAIT_FOR_LOG 2 78080 0 0
RFS IDLE 2 78080 873759 3
Last Applied Log:
Run this query on the standby database to see the last applied archivelog sequence number for each thread.
SQL> SELECT thread#, max(SEQUENCE#) FROM V$ARCHIVED_LOG where APPLIED='YES' group by thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 69479
2 78079
Apply/transport lags:
v$dataguard_stats view will show the general synchronization status of standby database.
Better to use on 11gR2 even with the latest PSU (Check bugs : 13394040, 7119382, 9968073, 7507011, 13045332, 6874522).
SQL> set lines 200
SQL> col name format a40
SQL> col value format a20
SQL> select * from v$dataguard_stats;
NAME VALUE UNIT TIME_COMPUTED DATUM_TIME
------------------------ ----------------- ------ --------------------- ---------------------
transport lag +00 00:09:44 … 07/01/2013 15:49:29 07/01/2013 15:49:27
apply lag +00 00:09:44 … 07/01/2013 15:49:29 07/01/2013 15:49:27
apply finish time +00 00:00:00.001 … 07/01/2013 15:49:29
estimated startup time 27 second 07/01/2013 15:49:29
Apply rate:
To find out the speed of media recovery in a standby database, you can use this query:
SQL> set lines 200
SQL> col type format a30
SQL> col ITEM format a20
SQL> col comments format a20
SQL> select * from v$recovery_progress;
START_TIM TYPE ITEM UNITS SOFAR TOTAL TIMESTAMP COMMENTS
--------- ---------------- -------------------- ------------------ ---------- --------- ----
20-JUN-13 Media Recovery Log Files Files 3363 0
20-JUN-13 Media Recovery Active Apply Rate KB/sec 21584 0
20-JUN-13 Media Recovery Average Apply Rate KB/sec 3239 0
20-JUN-13 Media Recovery Maximum Apply Rate KB/sec 48913 0
20-JUN-13 Media Recovery Redo Applied Megabytes 2953165 0
20-JUN-13 Media Recovery Last Applied Redo SCN+Time 0 0 01-JUL-13
20-JUN-13 Media Recovery Active Time Seconds 233822 0
20-JUN-13 Media Recovery Apply Time per Log Seconds 57 0
20-JUN-13 Media Recovery Checkpoint Time per Seconds 11 0
Log
20-JUN-13 Media Recovery Elapsed Time Seconds 933565 0
20-JUN-13 Media Recovery Standby Apply Lag Seconds 483 0
11 rows selected.
You can also use below before 11gR2. (Deprecated in 11gR2):
SQL> select APPLY_RATE from V$STANDBY_APPLY_SNAPSHOT;
To check Redo apply mode on physical standby database:
SQL> SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS where dest_id=2;
RECOVERY_MODE
-----------------------
MANAGED
To check what MRP process is waiting:
select a.event, a.wait_time, a.seconds_in_wait from gv$session_wait a, gv$session b where a.sid=b.sid and b.sid=(select SID from v$session where PADDR=(select PADDR from v$bgprocess where NAME='MRP0'))
EVENT WAIT_TIME SECONDS_IN_WAIT
---------------------------------------------- ---------- ---------------
parallel recovery control message reply 0 0
Archive Lag Histogram:
The V$STANDBY_EVENT_HISTOGRAM view came with 11gR2 and shows the historical occurance of archive lags in terms of seconds. For example following output shows that in 07/01/2013 archive lag reached 5 hours and in 06/15/2013 gap was 22 hours which was resolved after more than a week.
SQL> col name format a10
SQL> select * from V$STANDBY_EVENT_HISTOGRAM;
NAME TIME UNIT COUNT LAST_TIME_UPDATED
---------- ---------- ------------ -------- --------------------
apply lag 0 seconds 0
apply lag 1 seconds 1 04/13/2013 01:40:23
apply lag 2 seconds 1 04/13/2013 01:40:24
apply lag 3 seconds 1 04/13/2013 01:40:25
apply lag 4 seconds 1 04/13/2013 01:40:26
...
apply lag 25 seconds 3 05/21/2013 06:31:19
apply lag 26 seconds 3 05/21/2013 06:31:20
apply lag 27 seconds 3 05/21/2013 06:31:23
apply lag 28 seconds 5 05/21/2013 06:31:22
apply lag 29 seconds 1 05/15/2013 07:47:46
apply lag 30 seconds 4 05/21/2013 06:31:24
...
apply lag 44 seconds 8 06/26/2013 00:33:14
apply lag 45 seconds 8 06/26/2013 00:33:15
apply lag 46 seconds 8 06/26/2013 00:33:17
apply lag 47 seconds 8 06/26/2013 00:33:18
apply lag 48 seconds 9 06/26/2013 00:33:19
...
apply lag 57 seconds 29 06/26/2013 06:33:02
apply lag 58 seconds 25 06/26/2013 06:33:27
apply lag 59 seconds 28 06/26/2013 06:33:28
apply lag 1 minutes 0
apply lag 2 minutes 9316 06/30/2013 18:33:45
apply lag 3 minutes 94601 07/01/2013 14:23:11
apply lag 4 minutes 209262 07/01/2013 14:56:13
apply lag 5 minutes 355744 07/01/2013 16:02:33
apply lag 6 minutes 522176 07/01/2013 16:03:30
apply lag 7 minutes 634199 07/01/2013 16:01:10
...
apply lag 47 minutes 28174 07/01/2013 05:14:53
apply lag 48 minutes 28231 07/01/2013 05:14:49
apply lag 49 minutes 27099 07/01/2013 05:14:44
apply lag 50 minutes 26532 07/01/2013 05:14:40
...
apply lag 3 hours 564493 07/01/2013 05:00:08
apply lag 4 hours 511628 06/22/2013 07:43:26
apply lag 5 hours 448572 06/22/2013 07:34:03
apply lag 6 hours 369037 06/22/2013 07:09:59
apply lag 7 hours 206117 06/21/2013 00:53:27
apply lag 8 hours 137932 06/21/2013 00:33:53
apply lag 9 hours 137091 06/21/2013 00:03:33
apply lag 10 hours 98103 06/20/2013 23:26:34
apply lag 11 hours 104157 06/20/2013 22:53:12
apply lag 12 hours 102141 06/20/2013 22:14:07
apply lag 13 hours 89214 06/20/2013 21:32:22
apply lag 14 hours 64880 06/20/2013 21:04:29
apply lag 15 hours 43471 06/20/2013 21:01:45
apply lag 16 hours 38075 06/20/2013 20:59:37
apply lag 17 hours 38449 06/20/2013 20:55:34
apply lag 18 hours 22049 06/16/2013 01:22:55
apply lag 19 hours 19873 06/16/2013 00:53:55
apply lag 20 hours 15985 06/15/2013 23:52:16
apply lag 21 hours 13290 06/15/2013 03:08:49
apply lag 22 hours 7330 06/15/2013 02:07:26
apply lag 23 hours 1606 02/15/2013 22:16:11
apply lag 1 days 3216 02/15/2013 22:00:42
apply lag 2 days 16768 02/15/2013 20:54:06
144 rows selected.
Redo switch report of primary database can be seen with the following query. This information may be helpful when investigating the possible causes of archive gaps, apply lags or data guard performance issues.
SQL> SET PAGESIZE 9999
SQL> col day format a15
SQL> SELECT A.*, Round(A.Count#*B.AVG#/1024/1024) Daily_Avg_Mb FROM(SELECT To_Char(First_Time,'YYYY-MM-DD') DAY, Count(1) Count#, Min(RECID) Min#, Max(RECID) Max# FROM gv$log_history GROUP BY To_Char(First_Time,'YYYY-MM-DD') ORDER BY 1 DESC) A,(SELECT Avg(BYTES) AVG#, Count(1) Count#, Max(BYTES) Max_Bytes, Min(BYTES) Min_Bytes FROM gv$log ) B;
DAY COUNT# MIN# MAX# DAILY_AVG_MB
--------------- ---------- ---------- ---------- ------------
2013-07-01 442 147345 147566 452608
2013-06-30 526 147083 147347 538624
2013-06-29 532 146817 147082 544768
2013-06-28 928 146353 146816 950272
2013-06-27 760 145973 146352 778240
2013-06-26 708 145619 145972 724992
2013-06-25 560 145338 145618 573440
2013-06-24 498 145090 145339 509952
2013-06-23 104 145038 145089 106496
2013-06-22 338 144869 145037 346112
2013-06-21 748 144495 144868 765952
2013-06-20 748 144121 144494 765952
2013-06-19 952 143645 144120 974848
2013-06-18 882 143204 143644 903168
2013-06-17 914 142746 143203 935936
2013-06-16 454 142520 142747 464896
2013-06-15 1520 141760 142519 1556480
2013-06-14 1862 140829 141759 1906688
2013-06-13 970 140343 140828 993280
2013-06-12 598 140045 140345 612352
2013-06-11 550 139770 140044 563200
2013-06-10 516 139511 139769 528384
2013-06-09 178 139423 139512 182272
2013-06-08 296 139275 139422 303104
2013-06-07 490 139030 139274 501760
2013-06-06 572 138744 139029 585728
2013-06-05 488 138499 138743 499712
Following PL/SQL code can be used to find out time difference between primary and standby database. (Run on primary database, not for real-time apply)
Following output shows 10 minutes and 54 seconds of time difference.
set serveroutput on;
DECLARE
v_diff NUMBER := 0;
v_hrs NUMBER := 0;
v_min NUMBER := 0;
v_sec NUMBER := 0;
p_dte1 DATE;
p_dte2 DATE;
date1 long;
date2 long;
BEGIN
date1 := 'select sysdate from dual';
date2 := 'select MAX (next_time) FROM gv$archived_log where APPLIED=''YES''';
execute immediate date1 into p_dte1;
execute immediate date2 into p_dte2;
v_diff := ABS(p_dte2 - p_dte1);
v_hrs := TRUNC(v_diff, 0)*24;
v_diff := (v_diff - TRUNC(v_diff, 0))*24;
v_hrs := v_hrs + TRUNC(v_diff, 0);
v_diff := (v_diff - TRUNC(v_diff, 0))*60;
v_min := TRUNC(v_diff, 0);
v_sec := TRUNC((v_diff - TRUNC(v_diff, 0))*60, 0);
DBMS_OUTPUT.put_line(
TO_CHAR(v_hrs) ||' '||
TO_CHAR(v_min) ||' '||
TO_CHAR(v_sec) );
END;
/
0 10 54
PL/SQL procedure successfully completed.
This one will give the same output with the above code, but this must be run on standby database.
set serveroutput on;
DECLARE
v_diff NUMBER := 0;
v_hrs NUMBER := 0;
v_min NUMBER := 0;
v_sec NUMBER := 0;
p_dte1 DATE;
p_dte2 DATE;
date1 long;
date2 long;
BEGIN
date1 := 'select sysdate from dual';
date2 := 'select max(TIMESTAMP) from v$recovery_progress';
execute immediate date1 into p_dte1;
execute immediate date2 into p_dte2;
v_diff := ABS(p_dte2 - p_dte1);
v_hrs := TRUNC(v_diff, 0)*24;
v_diff := (v_diff - TRUNC(v_diff, 0))*24;
v_hrs := v_hrs + TRUNC(v_diff, 0);
v_diff := (v_diff - TRUNC(v_diff, 0))*60;
v_min := TRUNC(v_diff, 0);
v_sec := TRUNC((v_diff - TRUNC(v_diff, 0))*60, 0);
DBMS_OUTPUT.put_line(
TO_CHAR(v_hrs) ||' '||
TO_CHAR(v_min) ||' '||
TO_CHAR(v_sec) );
END;
/
0 10 54
PL/SQL procedure successfully completed.
The last one is a shell command and lists the archive log apply records of standby database alert log with the corresponding times at the end of the line. This is useful to see a clean picture of redo apply status on the standby database.
tail -10000 /u01/app/oracle/product/diag/rdbms/testdb/TESTDB/trace/alert_TESTDB.log |awk -v x="" '{if (index($0,"Media Recovery Log ")!=0) print $0" "x; else if($1=="Mon"||$1=="Tue"||$1=="Wed"||$1=="Thu"||$1=="Fri"||$1=="Sat"||$1=="Sun") x=$0}'
Media Recovery Log +DATA/…/thread_1_seq_69468.904.819643305 Mon Jul 01 14:42:14 2013
Media Recovery Log +DATA/…/thread_1_seq_69469.899.819643701 Mon Jul 01 14:48:51 2013
Media Recovery Log +DATA/…/thread_2_seq_78072.741.819643579 Mon Jul 01 14:49:24 2013
Media Recovery Log +DATA/…/thread_1_seq_69470.956.819643639 Mon Jul 01 14:50:30 2013
Media Recovery Log +DATA/…/thread_2_seq_78073.1129.819644003 Mon Jul 01 14:53:55 2013
Media Recovery Log +DATA/…/thread_1_seq_69471.1123.819643961 Mon Jul 01 14:54:10 2013
Media Recovery Log +DATA/…/thread_1_seq_69472.861.819644303 Mon Jul 01 14:58:54 2013
Media Recovery Log +DATA/…/thread_2_seq_78074.1136.819644507 Mon Jul 01 15:02:14 2013
Media Recovery Log +DATA/…/thread_1_seq_69473.1024.819644695 Mon Jul 01 15:05:24 2013
Media Recovery Log +DATA/…/thread_2_seq_78075.936.819644933 Mon Jul 01 15:09:25 2013
Media Recovery Log +DATA/…/thread_1_seq_69474.904.819645085 Mon Jul 01 15:11:57 2013
Media Recovery Log +DATA/…/thread_1_seq_69475.911.819645509 Mon Jul 01 15:19:01 2013
Media Recovery Log +DATA/…/thread_2_seq_78076.899.819645377 Mon Jul 01 15:19:17 2013
Media Recovery Log +DATA/…/thread_1_seq_69476.1018.819646001 Mon Jul 01 15:27:12 2013
Media Recovery Log +DATA/…/thread_2_seq_78077.1060.819645837 Mon Jul 01 15:27:53 2013
Media Recovery Log +DATA/…/thread_1_seq_69477.956.819645995 Mon Jul 01 15:28:48 2013
Media Recovery Log +DATA/…/thread_2_seq_78078.861.819646339 Mon Jul 01 15:32:50 2013
Media Recovery Log +DATA/…/thread_1_seq_69478.1123.819646363 Mon Jul 01 15:33:17 2013
Media Recovery Log +DATA/…/thread_1_seq_69479.741.819646705 Mon Jul 01 15:38:57 2013
Media Recovery Log +DATA/…/thread_2_seq_78079.890.819646767 Mon Jul 01 15:40:00 2013
Media Recovery Log +DATA/…/thread_1_seq_69480.904.819647027 Mon Jul 01 15:44:21 2013
Media Recovery Log +DATA/…/thread_2_seq_78080.911.819647307 Mon Jul 01 15:48:59 2013
Media Recovery Log +DATA/…/thread_1_seq_69481.1136.819647365 Mon Jul 01 15:49:57 2013
Media Recovery Log +DATA/…/thread_1_seq_69482.1018.819647679 Mon Jul 01 15:55:10 2013
Media Recovery Log +DATA/…/thread_2_seq_78081.936.819647855 Mon Jul 01 15:58:03 2013
Media Recovery Log +DATA/…/thread_1_seq_69483.1024.819648003 Mon Jul 01 16:00:35 2013
Below are keyboard shortcut keys in Oracle Apps 11i. Use these to flaunt your skills in apps.. 
Trust me when you do this in front of clients it works like magic 🙂
