Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
In today’s article we will learn to Setting The Log Archive Trace Parameter.
Although the LOG_ARCHIVE_TRACE parameter is an optional parameter, we use it for troubleshotting.
This parameter shows in detail the process of sending archives sent to the Standby party.
Trace files are written to the ADR (Automatic Diagnostic Repository) location. This location is as follows.
1 2 3 4 5 | [Primary] SQL> show parameter diagnostic NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ diagnostic_dest string /u01/app/oracle |
1 | ADR_HOME=<diagnostic_dest>diag/rdbms/<db_name>/<instance_name>/trace |
Although its default value is 0, it can take the following values. If we want more than one feature to be turned on then we add these values.
LET’S TEST
1. We list the files in the ADR_HOME directory according to the processing time, according to the old.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | [oracle@primary trace]$ ls -alt | more total 2332 -rw-r----- 1 oracle oinstall 579867 Feb 10 09:05 drcprimary.log -rw-r----- 1 oracle oinstall 63006 Feb 10 09:05 primary_mmon_27780.trc -rw-r----- 1 oracle oinstall 6211 Feb 10 09:05 primary_mmon_27780.trm -rw-r----- 1 oracle oinstall 63515 Feb 10 09:04 primary_lgwr_27748.trc -rw-r----- 1 oracle oinstall 6929 Feb 10 09:04 primary_lgwr_27748.trm -rw-r----- 1 oracle oinstall 143012 Feb 10 08:59 alert_primary.log -rw-r----- 1 oracle oinstall 2434 Feb 10 08:59 primary_arc3_27861.trc -rw-r----- 1 oracle oinstall 250 Feb 10 08:59 primary_arc3_27861.trm -rw-r----- 1 oracle oinstall 2135 Feb 10 08:41 primary_nsv4_9940.trc -rw-r----- 1 oracle oinstall 142 Feb 10 08:41 primary_nsv4_9940.trm -rw-r----- 1 oracle oinstall 2516 Feb 10 08:41 primary_rsm0_9983.trc -rw-r----- 1 oracle oinstall 253 Feb 10 08:41 primary_rsm0_9983.trm -rw-r----- 1 oracle oinstall 1710 Feb 10 08:41 primary_nsv1_9842.trc -rw-r----- 1 oracle oinstall 118 Feb 10 08:41 primary_nsv1_9842.trm -rw-r----- 1 oracle oinstall 2144 Feb 10 08:41 primary_arc2_27853.trc -rw-r----- 1 oracle oinstall 254 Feb 10 08:41 primary_arc2_27853.trm |
2. We set the LOG_ARCHIVE_TRACE parameter to a value.
1 2 3 | DGMGRL> sql "alter system set log_archive_trace=16 scope=both"; Succeeded. DGMGRL> exit |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | [oracle@primary trace]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Fri Feb 10 09:07:39 2017 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options [Primary] SQL> show parameter log_archive_trace NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_trace integer 16 |
3. We list the files in the ADR_HOME directory again according to the processing time.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | [oracle@primary trace]$ ls -alt | more total 2492 -rw-r----- 1 oracle oinstall 186176 Feb 10 09:08 primary_lgwr_27748.trc -rw-r----- 1 oracle oinstall 9070 Feb 10 09:08 primary_lgwr_27748.trm -rw-r----- 1 oracle oinstall 33647 Feb 10 09:08 primary_nss2_10361.trc -rw-r----- 1 oracle oinstall 952 Feb 10 09:08 primary_nss2_10361.trm -rw-r----- 1 oracle oinstall 581863 Feb 10 09:08 drcprimary.log -rw-r----- 1 oracle oinstall 10666 Feb 10 09:07 primary_arc1_27849.trc -rw-r----- 1 oracle oinstall 457 Feb 10 09:07 primary_arc1_27849.trm -rw-r----- 1 oracle oinstall 143087 Feb 10 09:07 alert_primary.log -rw-r----- 1 oracle oinstall 63006 Feb 10 09:05 primary_mmon_27780.trc -rw-r----- 1 oracle oinstall 6211 Feb 10 09:05 primary_mmon_27780.trm -rw-r----- 1 oracle oinstall 2434 Feb 10 08:59 primary_arc3_27861.trc -rw-r----- 1 oracle oinstall 250 Feb 10 08:59 primary_arc3_27861.trm -rw-r----- 1 oracle oinstall 2135 Feb 10 08:41 primary_nsv4_9940.trc -rw-r----- 1 oracle oinstall 142 Feb 10 08:41 primary_nsv4_9940.trm -rw-r----- 1 oracle oinstall 2516 Feb 10 08:41 primary_rsm0_9983.trc -rw-r----- 1 oracle oinstall 253 Feb 10 08:41 primary_rsm0_9983.trm -rw-r----- 1 oracle oinstall 1710 Feb 10 08:41 primary_nsv1_9842.trc -rw-r----- 1 oracle oinstall 118 Feb 10 08:41 primary_nsv1_9842.trm -rw-r----- 1 oracle oinstall 2144 Feb 10 08:41 primary_arc2_27853.trc -rw-r----- 1 oracle oinstall 254 Feb 10 08:41 primary_arc2_27853.trm |
New files that arrive after the parameter is set are marked in blue above.
4. When the log files are very large, we can search by word if we want.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 | [oracle@primary trace]$ grep Error primary_arc1_27849.trc Linux-x86_64 Error: 2: No such file or directory OCISessionBegin failed. Error -1 Linux-x86_64 Error: 2: No such file or directory OCISessionBegin failed. Error -1 Linux-x86_64 Error: 2: No such file or directory OCISessionBegin failed. Error -1 Linux-x86_64 Error: 2: No such file or directory Error 1034 received logging on to the standby Error 1034 connecting to destination LOG_ARCHIVE_DEST_2 standby host 'prmyFS' krsi_verify_network: Error 1034 attaching to destination LOG_ARCHIVE_DEST_2 standby host 'prmyFS' Linux-x86_64 Error: 2: No such file or directory OCISessionBegin failed. Error -1 Linux-x86_64 Error: 2: No such file or directory OCISessionBegin failed. Error -1 Linux-x86_64 Error: 2: No such file or directory OCISessionBegin failed. Error -1 Linux-x86_64 Error: 2: No such file or directory Error 1034 received logging on to the standby Error 1034 connecting to destination LOG_ARCHIVE_DEST_2 standby host 'prmyFS' krsi_verify_network: Error 1034 attaching to destination LOG_ARCHIVE_DEST_2 standby host 'prmyFS' Linux-x86_64 Error: 2: No such file or directory OCISessionBegin failed. Error -1 Linux-x86_64 Error: 2: No such file or directory OCISessionBegin failed. Error -1 Linux-x86_64 Error: 2: No such file or directory OCISessionBegin failed. Error -1 Linux-x86_64 Error: 2: No such file or directory Error 1034 received logging on to the standby Error 1034 connecting to destination LOG_ARCHIVE_DEST_2 standby host 'prmyFS' krsi_verify_network: Error 1034 attaching to destination LOG_ARCHIVE_DEST_2 standby host 'prmyFS' Linux-x86_64 Error: 2: No such file or directory OCISessionBegin failed. Error -1 Linux-x86_64 Error: 2: No such file or directory OCISessionBegin failed. Error -1 Linux-x86_64 Error: 2: No such file or directory OCISessionBegin failed. Error -1 Linux-x86_64 Error: 2: No such file or directory Error 1034 received logging on to the standby Error 1034 connecting to destination LOG_ARCHIVE_DEST_2 standby host 'prmyFS' krsi_verify_network: Error 1034 attaching to destination LOG_ARCHIVE_DEST_2 standby host 'prmyFS' Linux-x86_64 Error: 2: No such file or directory OCISessionBegin failed. Error -1 Linux-x86_64 Error: 2: No such file or directory OCISessionBegin failed. Error -1 Linux-x86_64 Error: 2: No such file or directory OCISessionBegin failed. Error -1 Linux-x86_64 Error: 2: No such file or directory Error 1034 received logging on to the standby Error 1034 connecting to destination LOG_ARCHIVE_DEST_2 standby host 'prmyFS' krsi_verify_network: Error 1034 attaching to destination LOG_ARCHIVE_DEST_2 standby host 'prmyFS' Error 3113 detaching RFS from standby instance at host 'logical' Error 12537 received logging on to the standby Error 12537 connecting to destination LOG_ARCHIVE_DEST_4 standby host 'logical' krsi_verify_network: Error 12537 attaching to destination LOG_ARCHIVE_DEST_4 standby host 'logical' |
5. We turn off the trace.
1 2 | DGMGRL> sql "alter system set log_archive_trace=0 scope=both"; Succeeded. |
1 2 3 4 5 | [Primary] SQL> show parameter log_archive_trace NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_trace integer 0 |
