Oracle DBVERIFY (dbv) by Example

===========================
General - What is DBVERIFU (dbv)
===========================
DBVERIFY is an external command-line utility that performs a physical data structure integrity check on an offline database. 
It can be used against backup files and online files (or pieces of files). 
You use DBVERIFY primarily when you need to ensure that a backup database (or datafile) is valid before it is restored or as a diagnostic aid when you have encountered data corruption problems.

Oracle reference.


===========================
Real Example
===========================

The issue: For no apparent reason, Oracle was restarting every 30 minutes or so.

Looking into Oracle alert.log, there are many errors related to SMON process.

What is SMON Process:

SMON (System MONitor) is an Oracle background process created when you start a database instance. 

The SMON process performs instance recovery, cleans up after dirty shutdowns and coalesces adjacent free extents into larger free extents.

SMON wakes up every 5 minutes to perform housekeeping activities. 

The system monitor performs recovery when a failed instance starts up again. 

SMON also cleans up temporary segments that are no longer in use and recovers dead transactions skipped during system failure and instance recovery because of file-read or offline errors. 

These transactions are eventually recovered by SMON when the tablespace or file is brought back online.

SMON must always be running for an instance. If not, the instance will terminate.

===========================
Errors in alert.log
===========================
Thu Sep 29 06:28:41 2016
Non-fatal internal error happenned while SMON was doing flushing of monitored table stats.
SMON exceeded the maximum limit of 100 internal error(s).
Errors in file /software/oracle/diag/rdbms/igt/igt/trace/igt_smon_3858.trc:
ORA-00600: internal error code, arguments: [13013], [5001], [484], [4197615], [76], [4197615
], [4], [], [], [], [], []
SMON (ospid: 3858): terminating the instance due to error 474
Thu Sep 29 06:28:42 2016
System state dump requested by (instance=1, osid=3858 (SMON)), summary=[abnormal instance te
rmination].
System State dumped to trace file /software/oracle/diag/rdbms/igt/igt/trace/igt_diag_3844_20
160929062842.trc
Dumping diagnostic data in directory=[cdmp_20160929062842], requested by (instance=1, osid=3
858 (SMON)), summary=[abnormal instance termination].
Instance terminated by SMON, pid = 3858
Thu Sep 29 06:28:52 2016
Starting ORACLE instance (normal)
...
...
...
Thu Sep 29 06:29:00 2016
QMNC started with pid=31, OS id=15232
Completed: ALTER DATABASE OPEN
Thu Sep 29 06:30:00 2016
Starting background process SMCO
Thu Sep 29 06:30:00 2016
SMCO started with pid=58, OS id=18314
Thu Sep 29 06:45:00 2016
Errors in file /software/oracle/diag/rdbms/igt/igt/trace/igt_smon_14774.trc  (incident=1237949):
ORA-00600: internal error code, arguments: [13013], [5001], [484], [4197615], [76], [4197615], [4], [], [], [], [], []
Incident details in: /software/oracle/diag/rdbms/igt/igt/incident/incdir_1237949/igt_smon_14774_i1237949.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Non-fatal internal error happenned while SMON was doing flushing of monitored table stats.
SMON encountered 1 out of maximum 100 non-fatal internal errors.
...
...
Thu Sep 29 06:45:00 2016
Dumping diagnostic data in directory=[cdmp_20160929064500], requested by (instance=1, osid=14774 (SMON)), summary=[incident=1237949].
Errors in file /software/oracle/diag/rdbms/igt/igt/trace/igt_smon_14774.trc  (incident=1237950):
ORA-00600: internal error code, arguments: [13013], [5001], [484], [4197615], [76], [4197615], [4], [], [], [], [], []
Incident details in: /software/oracle/diag/rdbms/igt/igt/incident/incdir_1237950/igt_smon_14774_i1237950.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Non-fatal internal error happenned while SMON was doing flushing of monitored table stats.
SMON encountered 2 out of maximum 100 non-fatal internal errors.
Dumping diagnostic data in directory=[cdmp_20160929064502], requested by (instance=1, osid=14774 (SMON)), summary=[incident=1237950].
Errors in file /software/oracle/diag/rdbms/igt/igt/trace/igt_smon_14774.trc  (incident=1237951):
ORA-00600: internal error code, arguments: [13013], [5001], [484], [4197615], [76], [4197615], [4], [], [], [], [], []
Incident details in: /software/oracle/diag/rdbms/igt/igt/incident/incdir_1237951/igt_smon_14774_i1237951.trc
...
...
Thu Sep 29 06:45:03 2016
Sweep [inc][1237951]: completed
Sweep [inc][1237950]: completed
Sweep [inc][1237949]: completed
Sweep [inc2][1237950]: completed
Sweep [inc2][1237949]: completed
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Non-fatal internal error happenned while SMON was doing flushing of monitored table stats.
SMON encountered 3 out of maximum 100 non-fatal internal errors.
Dumping diagnostic data in directory=[cdmp_20160929064503], requested by (instance=1, osid=14774 (SMON)), summary=[incident=1237951].
Errors in file /software/oracle/diag/rdbms/igt/igt/trace/igt_smon_14774.trc  (incident=1237952):
ORA-00600: internal error code, arguments: [13013], [5001], [484], [4197615], [76], [4197615], [4], [], [], [], [], []
Incident details in: /software/oracle/diag/rdbms/igt/igt/incident/incdir_1237952/igt_smon_14774_i1237952.trc
...
...

...
...
SMON encountered 100 out of maximum 100 non-fatal internal errors.
Non-fatal internal error happenned while SMON was doing flushing of monitored table stats.
SMON exceeded the maximum limit of 100 internal error(s).
Errors in file /software/oracle/diag/rdbms/igt/igt/trace/igt_smon_14774.trc:
ORA-00600: internal error code, arguments: [13013], [5001], [484], [4197615], [76], [4197615], [4], [], [], [], [], []
SMON (ospid: 14774): terminating the instance due to error 474
Thu Sep 29 07:00:59 2016
System state dump requested by (instance=1, osid=14774 (SMON)), summary=[abnormal instance termination].
System State dumped to trace file /software/oracle/diag/rdbms/igt/igt/trace/igt_diag_14760_20160929070059.trc
Dumping diagnostic data in directory=[cdmp_20160929070059], requested by (instance=1, osid=14774 (SMON)), summary=[abnormal instance termination].
Instance terminated by SMON, pid = 14774
Thu Sep 29 07:01:26 2016
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Initial number of CPU is 8
Number of processor cores in the system is 6
Number of processor sockets in the system is 1
Picked latch-free SCN scheme 3
Autotune of undo retention is turned on.
IMODE=BR
ILAT =168
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up:
...
...
ARC0: STARTING ARCH PROCESSES COMPLETE
Archived Log entry 2177 added for thread 1 sequence 565 ID 0x453ba9bf dest 1:
[1354] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:570035914 end:570035934 diff:20 (0 seconds)
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is AL32UTF8
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Thu Sep 29 07:01:34 2016
QMNC started with pid=31, OS id=1476
Completed: ALTER DATABASE OPEN
Thu Sep 29 07:06:35 2016
Starting background process SMCO
Thu Sep 29 07:06:35 2016
SMCO started with pid=63, OS id=18401
==========> rebuild Index SYS.I_MON_MODS$_OBJ

===========================
Investigation of ORA-600
===========================
ORA-600 [13013] [a] [b] [c] [d] [e] [f] 

Arg [a] Passcount 
Arg [b] Data Object number 
Arg [c] Tablespace Relative DBA of block containing the row to be updated 
Arg [d] Row Slot number 
Arg [e] Relative DBA of block being updated (should be same as [c]) 
Arg [f] Code 
[13013]  [a]    [b]     [c]    [d]     [e]    [f] 
[13013],[5001],[484],[4197615],[76],[4197615],[4],[],[],[],[],[]

===========================
Follow steps in Oracle Metalink Document
===========================

Section 1 >Find the corrupted Object

SELECT DBA_EXTENTS.segment_name, 

       DBA_EXTENTS.segment_type, 

   DBA_EXTENTS.block_id, 

   DBA_EXTENTS.blocks

  FROM   DBA_EXTENTS, DBA_DATA_FILES

 WHERE 1=1

   AND DBA_DATA_FILES.file_name = '/oracle_db/db1/db_igt/ora_sysaux_01.dbf'

   AND DBA_DATA_FILES.file_id = DBA_EXTENTS.file_id

   AND DBA_EXTENTS.block_id BETWEEN 89169 AND 89171;

Select object_name,object_type,owner from dba_objects where data_object_id=<value reported in argument b> ;

SELECT object_name,object_type,owner 
  FROM DBA_OBJECTS WHERE data_object_id=484;
MON_MODS$ TABLE SYS


Analyze table SYS.MON_MODS$ validate structure online ;
=> OK

Analyze table SYS.MON_MODS$   validate structure cascade online 
=> ORA-600 with no parameters

SELECT dbms_utility.data_block_address_file(4197615) Rfile#,
       dbms_utility.data_block_address_block(4197615) "Block#" 
  FROM dual; 
1 3311

SELECT name, block_size from V$DATAFILE where rfile#=1;

/oracle_db/db1/db_igt/ora_system_01.dbf 8192

From Linux run:
>% dbv file=/oracle_db/db1/db_igt/ora_system_01.dbf blocksize=8192 FEEDBACK=100
DBVERIFY: Release 11.2.0.4.0 - Production on Thu Sep 29 07:00:27 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = /oracle_db/db1/db_igt/ora_system_01.dbf



................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
........

DBVERIFY - Verification complete

Total Pages Examined         : 89600
Total Pages Processed (Data) : 26804
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 10877
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 4573
Total Pages Processed (Seg)  : 1
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 47346
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 11532511 (0.11532511)


Since there is no apparent corruption in the Table, per oracle Metalink, the next step is to rebuild Indexes on the table in question.

Section 2 >How to resolve if a Index is corrupted

You would need to drop and recreate the index

Ensure before dropping the Index, you have the DDL command 


SELECT owner, index_name 
FROM DBA_INDEXES 
WHERE table_name = 'MON_MODS$';

OWNER                          INDEX_NAME
------------------------------ ------------------------------
SYS                            I_MON_MODS$_OBJ

SQL>Spool /tmp/createindex.sql

SQL>Set long 100000000

SQL>Select dbms_metadata.get_ddl('INDEX','<Index name>',<'user name>') from dual 

SELECT dbms_metadata.get_ddl('INDEX','I_MON_MODS$_OBJ','SYS') 
  FROM DUAL;

SQL>Spool off


DBMS_METADATA.GET_DDL('INDEX','I_MON_MODS$_OBJ','SYS')
--------------------------------------------------------------------------------
  CREATE UNIQUE INDEX "SYS"."I_MON_MODS$_OBJ" ON "SYS"."MON_MODS$" ("OBJ#")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM"

SQL> DROP INDEX SYS.I_MON_MODS$_OBJ;

Index dropped.

SQL> @/tmp/createindex.sql

Index created.

SQL>Analyze table SYS.MON_MODS$ validate structure cascade online;

Table analyzed.

After these steps were performed, no more errors in alert.log
cat '==========> rebuild Index SYS.I_MON_MODS$_OBJ' >> alert.log

alert.log:
Completed: ALTER DATABASE OPEN
Thu Sep 29 07:06:35 2016
Starting background process SMCO
Thu Sep 29 07:06:35 2016
SMCO started with pid=63, OS id=18401
==========> rebuild Index SYS.I_MON_MODS$_OBJ
Thu Sep 29 07:31:31 2016
Thread 1 advanced to log sequence 567 (LGWR switch)
  Current log# 3 seq# 567 mem# 0: /oracle_db/db1/db_igt/ora_redo_03_a.rdo
Thu Sep 29 07:31:31 2016
Archived Log entry 2178 added for thread 1 sequence 566 ID 0x453ba9bf dest 1:

Generated dbverify commands

set echo off feedback off verify off pages 0 termout off linesize 150 

select 'dbv file=' || name || ' blocksize=' || block_size || ' feedback=' || round(blocks*.10,0)||' LOGFILE='

||REPLACE(SUBSTR(name,INSTR(name,'/',-1)+1),'.dbf','.log')

  from v$datafile;

dbv file=/oracle_db/db1/db_igt/ora_sysaux_01.dbf blocksize=8192 feedback=7680 LOGFILE=ora_sysaux_01.log

dbv file=/oracle_db/db1/db_igt/ora_undotbs_01.dbf blocksize=8192 feedback=39680 LOGFILE=ora_undotbs_01.log

dbv file=/oracle_db/db1/db_igt/ora_igt_table_01.dbf blocksize=8192 feedback=171674 LOGFILE=ora_igt_table_01.log

dbv file=/oracle_db/db1/db_igt/ora_igt_index_01.dbf blocksize=8192 feedback=128 LOGFILE=ora_igt_index_01.log

dbv file=/oracle_db/db1/db_igt/ora_workarea_01.dbf blocksize=8192 feedback=128 LOGFILE=ora_workarea_01.log

dbv file=/software/oracle/igt/ora_igt_table_02.dbf blocksize=8192 feedback=104986 LOGFILE=ora_igt_table_02.log

Monitor Instance Activity by logging sessions, processes, and Logon Trigger by Example

===============================
General
===============================
Example of loging activity on Instance by several processes:
Log Sessions.
Log Processes.
Use Logon trigger, writing all login, logout, and basic session activity to a log file

===============================
Current Sessions Usage

===============================

SELECT schemaname, COUNT(*)
FROM V$SESSION
GROUP BY schemaname
ORDER BY COUNT(*) DESC;
===============================
Log Sessions and Processes
===============================

Files:

SH_PROCESSES_HIST.sql
SH_SESSIONS_HIST.sql

MONITOR_DB_PKG.sql

SH_PROCESSES_HIST.sql
-- Create table
create table SH_PROCESSES_HIST
(
  run_date      DATE not null,
  schema_name   VARCHAR2(30) not null,
  processes_num NUMBER(9) not null
)
tablespace IGT_TABLE
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table SH_PROCESSES_HIST
  add constraint SH_PROCESSES_HIST_PK primary key (RUN_DATE, SCHEMA_NAME)
  using index 

  tablespace IGT_INDEX

SH_SESSIONS_HIST.sql
  -- Create table
create table SH_SESSIONS_HIST
(
  run_date     DATE not null,
  schema_name  VARCHAR2(30) not null,
  sessions_num NUMBER(9) not null
)
tablespace IGT_TABLE
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table SH_SESSIONS_HIST
  add constraint SH_SESSIONS_HIST_PK primary key (RUN_DATE, SCHEMA_NAME)
  using index tablespace IGT_INDEX;

MONITOR_DB_PKG.sql

CREATE OR REPLACE PACKAGE BODY MONITOR_DB_PKG IS
  PROCEDURE LOG_SESSIONS IS
  BEGIN
    INSERT INTO SH_SESSIONS_HIST  (run_date, schema_name, sessions_num)
    SELECT SYSDATE, schemaname, count(*)
      FROM ( SELECT *
               FROM V$SESSION
              WHERE paddr IN (
                    SELECT addr
                      FROM V$PROCESS
                     WHERE background IS NULL
                       AND username = 'oracle'
                       AND program LIKE 'oracle@aut-tma%')
    )
    GROUP BY schemaname;
    COMMIT;

  EXCEPTION
    WHEN OTHERS THEN
      INSERT INTO SH_SESSIONS_HIST  (run_date, schema_name, sessions_num)
      VALUES (SYSDATE,'N/A',0);
      COMMIT;
  END LOG_SESSIONS;
------------------------------------------------
  PROCEDURE LOG_PROCESSES IS
  BEGIN
    INSERT INTO SH_PROCESSES_HIST  (run_date, schema_name, processes_num)
    SELECT SYSDATE, schema_name, COUNT(*) 
      FROM (
            SELECT SESSIONS.schemaname AS schema_name,
                   PROCESSES.spid as process
              FROM V$PROCESS PROCESSES,               
                   V$SESSION SESSIONS 
             WHERE PROCESSES.program like 'oracle@aut-tma%' 
               AND PROCESSES.background IS NULL 
               AND PROCESSES.addr=SESSIONS.paddr  
               )
    GROUP BY schema_name ;
    COMMIT;

  EXCEPTION
    WHEN OTHERS THEN
      INSERT INTO SH_PROCESSES_HIST  (run_date, schema_name, processes_num)
      VALUES (SYSDATE,'N/A',0);
      COMMIT;
  END LOG_PROCESSES;
------------------------------------------------
  PROCEDURE LOG_ACTIVITY IS
  BEGIN    
    LOG_SESSIONS;
    LOG_PROCESSES;
  EXCEPTION
    WHEN OTHERS THEN 
      NULL;  
  END LOG_ACTIVITY;
------------------------------------------------
END MONITOR_DB_PKG;

===============================
Logon Trigger
===============================

Files:

permission.sql

AUD_ACTIVITY.sql

LOGON_AUDIT_TRIGGER.sql

LOGOFF_AUDIT_TRIGGER.sql

permission.sql

CREATE USER MONITOR_USER IDENTIFIED BY MONITOR_PASS;

GRANT CONNECT, RESOURCE TO MONITOR_USER;

GRANT SELECT ANY DICTIONARY TO MONITOR_USER;

GRANT UNLIMITED TABLESPACE TO MONITOR_USER;

GRANT ADMINISTER DATABASE TRIGGER TO MONITOR_USER;

AUD_ACTIVITY.sql

-- Create table

create table AUD_ACTIVITY

(

  session_id          NUMBER(10) not null,

  session_sid         NUMBER(10) not null,

  session_serial_num  VARCHAR2(40) not null,

  user_id             VARCHAR2(120),

  process             VARCHAR2(24),

  session_host        VARCHAR2(64),

  session_os_user     VARCHAR2(30),

  session_schema_name VARCHAR2(30),

  session_program     VARCHAR2(48),

  session_module      VARCHAR2(64),

  session_action      VARCHAR2(64),

  last_program        VARCHAR2(48),

  last_action         VARCHAR2(64),

  last_module         VARCHAR2(64),

  logon_day           DATE,

  logon_time          VARCHAR2(40),

  logoff_day          DATE,

  logoff_time         VARCHAR2(40),

  elapsed_minutes     NUMBER(8)

)

tablespace IGT_TABLE;

-- Create/Recreate indexes 

create index AUD_ACTIVITY_IND1 on AUD_ACTIVITY (USER_ID, LOGON_DAY) tablespace IGT_INDEX;

  -- Create/Recreate primary, unique and foreign key constraints 

alter table AUD_ACTIVITY

  add constraint AUD_ACTIVITY_PK primary key (SESSION_ID, SESSION_SID, SESSION_SERIAL_NUM)  using index tablespace IGT_INDEX;

LOGON_AUDIT_TRIGGER.sql

CREATE OR REPLACE TRIGGER LOGON_AUDIT_TRIGGER AFTER LOGON ON DATABASE

DECLARE

 v_session_id    NUMBER(10):=0;

 v_session_sid         V$SESSION.sid%TYPE;

 v_session_serial      V$SESSION.serial#%TYPE;

 v_session_paddr       V$SESSION.paddr%TYPE;

 v_session_module      V$SESSION.module%TYPE;

 v_session_program     V$SESSION.program%TYPE;

 v_session_action      V$SESSION.action%TYPE;

 v_session_host        V$SESSION.machine%TYPE;

 v_session_os_user     V$SESSION.osuser%TYPE;

 v_schema_name         V$SESSION.schemaname%TYPE;

 v_process             V$PROCESS.spid%TYPE;

BEGIN

  v_session_id    := sys_context('USERENV','SESSIONID');

  IF v_session_id != 0 AND USER NOT IN ('SYS','SYSTEM','SITEBACKUP','SHDAEMON') AND USER = 'MONITOR_USER'

  THEN

    BEGIN

      SELECT sid, serial#, paddr, module, program, action, machine, osuser, schemaname

      INTO v_session_sid, v_session_serial, v_session_paddr, v_session_module,  v_session_program, v_session_action, v_session_host, v_session_os_user, v_schema_name

      FROM V$SESSION

      WHERE audsid=v_session_id

      AND ROWNUM<2;

    EXCEPTION

      WHEN OTHERS THEN

        v_session_sid:=9999999;

        v_session_serial:='9999999';

        v_session_module := 'N/A';

        v_session_program:= 'N/A';

        v_session_host := 'N/A';

        v_session_os_user := 'N/A';

        v_schema_name := 'N/A';

        v_session_action := 'N/A';

    END;

    BEGIN

      SELECT spid

        INTO v_process

        FROM V$PROCESS

       WHERE ADDR = v_session_paddr;

    EXCEPTION

      WHEN OTHERS THEN

        v_process := 0;

    END;

    INSERT INTO AUD_ACTIVITY

    (

      session_id,   

      session_sid,

      session_serial_num,

      user_id,

      process,

      session_host,

      session_os_user,

      session_schema_name,

      session_program,

      session_module,

      session_action,

      last_program,

      last_action,

      last_module,

      logon_day,

      logon_time,

      logoff_day,

      logoff_time,

      elapsed_minutes     

    )

    VALUES

    ( v_session_id,

      v_session_sid,

      v_session_serial,

      USER,

      v_process,

      v_session_host,

      v_session_os_user,

      v_schema_name,

      v_session_program,

      v_session_module,

      v_session_action,

      NULL,

      NULL,

      NULL,

      SYSDATE,

      TO_CHAR(SYSDATE,'HH24:MI:SS'),

      NULL,

      NULL,

      NULL);

  END IF;

EXCEPTION

  WHEN OTHERS THEN

    NULL;

END;

LOGOFF_AUDIT_TRIGGER.sql

CREATE OR REPLACE TRIGGER LOGOFF_AUDIT_TRIGGER BEFORE LOGOFF ON DATABASE

DECLARE

  v_session_id    NUMBER(10):=0;

  v_last_module   V$SESSION.module%TYPE;

  v_last_program  V$SESSION.program%TYPE;

  v_last_action   V$SESSION.action%TYPE;  

BEGIN

  -- ***************************************************

  -- Update the last action accessed

  -- ***************************************************

  v_session_id    := sys_context('USERENV','SESSIONID');

  IF v_session_id != 0 AND USER NOT IN ('SYS','SYSTEM','SITEBACKUP','SHDAEMON') AND USER = 'MONITOR_USER'

  THEN

    BEGIN

      SELECT action, program, module

        INTO v_last_action, v_last_program, v_last_module

        FROM V$SESSION

       WHERE audsid = v_session_id

         AND rownum < 2;

    EXCEPTION

      WHEN OTHERS THEN

        v_last_action   := NULL;

        v_last_program  := NULL;

        v_last_module   := NULL;

    END;

    UPDATE AUD_ACTIVITY

     SET last_action  = v_last_action,

         last_program = v_last_program,

         last_module  = v_last_module,

         logoff_day   = sysdate,

         logoff_time  = to_char(sysdate, 'hh24:mi:ss'),

         elapsed_minutes = round((sysdate - logon_day) * 1440)

    WHERE session_id = v_session_id;

  END IF;

EXCEPTION

  WHEN OTHERS THEN

    NULL;

END;

Archive in Oracle by Example. How to Change Archive destination, Change Archive mode.

============================================
Non Archive DB - view current status
============================================
SQL> SELECT name, log_mode FROM V$DATABASE;

NAME      LOG_MODE
--------- ------------
IGT       NOARCHIVELOG


SQL> show parameter log_archive
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_config                   string
log_archive_dest                     string
log_archive_dest_1                   string      location=/oracle_db/db2/db_igt/arch
log_archive_duplex_dest              string
log_archive_format                   string      arch%T_%s_%r.arc
log_archive_local_first              boolean     TRUE
log_archive_max_processes            integer     4
log_archive_min_succeed_dest         integer     1
log_archive_start                    boolean     FALSE
log_archive_trace                    integer     0


SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /oracle_db/db2/db_igt/arch
Oldest online log sequence     15528
Current log sequence           15530


SQL> show parameter recovery_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string
db_recovery_file_dest_size           big integer 0

============================================
Switch to Archive Log Destination
============================================
ALTER SYSTEM SET log_archive_dest_1='LOCATION=/oracle_db/db2/db_igt/arch' SCOPE=BOTH;

============================================
copy pfile
============================================

============================================
Switch to Archive mode
============================================
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;  [ALTER DATABASE NOARCHIVELOG;]
ALTER DATABASE OPEN;

============================================
Archive DB - view current status
============================================
SELECT name, log_mode FROM V$DATABASE;

NAME      LOG_MODE
--------- ------------
IGT       ARCHIVELOG

SQL> show parameter log_archive_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_config                   string
log_archive_dest                     string
log_archive_dest_1                   string      location=/oracle_db/db2/db_igt/arch
log_archive_duplex_dest              string
log_archive_format                   string      arch%T_%s_%r.arc
log_archive_local_first              boolean     TRUE
log_archive_max_processes            integer     4
log_archive_min_succeed_dest         integer     1
log_archive_start                    boolean     FALSE
log_archive_trace                    integer     0


SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /oracle_db/db2/db_igt/arch
Oldest online log sequence     56240
Next log sequence to archive   56242
Current log sequence           56242

SQL> show parameter recovery_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string
db_recovery_file_dest_size           big integer 0


============================================
Additional Options - Change Archive Destination
============================================

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /u02/app/oracle/oradata/orcl/arch_old
Oldest online log sequence     25

Current log sequence           27

ALTER SYSTEM SET log_archive_dest_1='LOCATION=/oracle_db/db2/db_igt/arch_new' scope=both;

System altered.

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /u02/app/oracle/oradata/orcl/arch_new
Oldest online log sequence     25
Current log sequence           27

============================================
Additional Parameters
============================================

log_archive_local_first

possible values: true/false

Default is log_archive_local_first=true, which meant that Oracle MUST finish writing the redo log before it can be transmitted to a remote server using log transport services. (this feature does not apply to Streams where it can read the online redo logs...)

Prior to 10g, Oracle would synchronously write redo log (the ACRn background process) while Oracle was simultaneously transmitting (FTP) the redo logs to a remote server.  

To re-establish the synchronous redo log transmission, set log_archive_local_first=false.

Deprecated since Oracle 11

log_archive_start

Deprecated in Oracle 11

Default is TRUE

When set to false, there is no automatic archiving of redo files, and need to handle that manually.