Let's see an example of monitoring the database alert log using the ADRCI utility:
- Ensure that the
ORACLE_HOMEandPATHenvironment variables are set properly. ThePATHenvironment variable must include theORACLE_HOME/bindirectory.export ORACLE_HOME=/u01/app/oracle2/product/11.2.0/dbhome_1 export PATH=$PATH:$ORACLE_HOME/bin
- Start the ADRCI command-line tool:
$ adrci ADRCI: Release 11.2.0.1.0 - Production on Thu Dec 20 02:06:49 2012 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. ADR base = "/u01/app/oracle2"
- We can run the
HELPcommand to get help on the usage of this utility:adrci> HELP HELP [topic] Available Topics: CREATE REPORT ECHO EXIT HELP HOST IPS PURGE RUN SET BASE SET BROWSER SET CONTROL SET ECHO SET EDITOR SET HOMES | HOME | HOMEPATH SET TERMOUT SHOW ALERT SHOW BASE SHOW CONTROL SHOW HM_RUN SHOW HOMES | HOME | HOMEPATH SHOW INCDIR SHOW INCIDENT SHOW PROBLEM SHOW REPORT SHOW TRACEFILE SPOOL There are other commands intended to be used directly by Oracle, type "HELP EXTENDED" to see the list
It's possible to get help for a specific command by specifying the topic in the
HELPcommand:adrci> HELP SHOW ALERT Usage: SHOW ALERT [-p <predicate_string>] [-term] [ [-tail [num] [-f]] | [-file <alert_file_name>]] Purpose: Show alert messages. Options: [-p <predicate_string>]: The predicate string must be double-quoted. The fields in the predicate are the fields: ORIGINATING_TIMESTAMP timestamp NORMALIZED_TIMESTAMP timestamp ORGANIZATION_ID text(65) COMPONENT_ID text(65) HOST_ID text(65) HOST_ADDRESS text(17) MESSAGE_TYPE number MESSAGE_LEVEL number MESSAGE_ID text(65) MESSAGE_GROUP text(65) CLIENT_ID text(65) MODULE_ID text(65) PROCESS_ID text(33) THREAD_ID text(65) USER_ID text(65) INSTANCE_ID text(65) DETAILED_LOCATION text(161) UPSTREAM_COMP_ID text(101) DOWNSTREAM_COMP_ID text(101) EXECUTION_CONTEXT_ID text(101) EXECUTION_CONTEXT_SEQUENCE number ERROR_INSTANCE_ID number ERROR_INSTANCE_SEQUENCE number MESSAGE_TEXT text(2049) MESSAGE_ARGUMENTS text(129) SUPPLEMENTAL_ATTRIBUTES text(129) SUPPLEMENTAL_DETAILS text(129) PROBLEM_KEY text(65) [-tail [num] [-f]]: Output last part of the alert messages and output latest messages as the alert log grows. If num is not specified, the last 10 messages are displayed. If "-f" is specified, new data will append at the end as new alert messages are generated. [-term]: Direct results to terminal. If this option is not specified, the results will be open in an editor. By default, it will open in emacs, but "set editor" can be used to set other editors. [-file <alert_file_name>]: Allow users to specify an alert file which may not be in ADR. <alert_file_name> must be specified with full path. Note that this option cannot be used with the -tail option Examples: show alert show alert -p "message_text like '%incident%'" show alert -tail 20
- Type the following statement to list the ADR home directories:
adrci> SHOW HOMES ADR Homes: diag/rdbms/india_ps/INDIAPS diag/asm/+asm/+ASM diag/tnslsnr/india_ps/listener
- Set the database
ADR HOMEto work on:adrci> SET HOME diag/rdbms/india_ps/INDIAPS - Monitor the last 20 lines of the database alert log file with the following statement:
adrci> SHOW ALERT -TAIL 20 2012-12-20 01:46:25.303000 +02:00 Archived Log entry 445 added for thread 1 sequence 3268 ID 0x4eea7a49 dest 1: Media Recovery Waiting for thread 1 sequence 3269 (in transit) Recovery of Online Redo Log: Thread 1 Group 5 Seq 3269 Reading mem 0 Mem# 0: /u01/app/oracle2/datafile/ORCL/std5.log 2012-12-20 01:46:28.383000 +02:00 RFS[20]: Selected log 4 for thread 1 sequence 3270 dbid 1319333016 branch 791552282 . . . Archived Log entry 451 added for thread 1 sequence 3274 ID 0x4eea7a49 dest 1: Media Recovery Waiting for thread 1 sequence 3275 (in transit) Recovery of Online Redo Log: Thread 1 Group 5 Seq 3275 Reading mem 0 Mem# 0: /u01/app/oracle2/datafile/ORCL/std5.log adrci>
- Run the following statement to monitor the alert log messages that contain the string
ORA-:adrci> SHOW ALERT -P "MESSAGE_TEXT LIKE '%ORA-%'" ... Errors in file /u01/app/oracle2/diag/rdbms/india_ps/INDIAPS/trace/INDIAPS_pr05_22496.trc: ORA-27090: Unable to reserve kernel resources for asynchronous disk I/O Additional information: 3 Additional information: 128 Additional information: 268423168 Errors in file /u01/app/oracle2/diag/rdbms/india_ps/INDIAPS/trace/INDIAPS_pr06_22498.trc:
- It's also possible to list the incidents with the
SHOW INCIDENTcommand:adrci> SHOW INCIDENT ADR Home = /u01/app/oracle/diag/rdbms/sb2db/SB2DB1: ****************************************************************** INCIDENT_ID PROBLEM_KEY CREATE_TIME ----------- --------------- ---------------------------- 320729 ORA 1578 2012-12-20 00:03:50.538000 +02:00 1 rows fetched
We've seen an example of using the ADRCI command-line tool to monitor alert log files. In a Data Guard-related problem, the first place to check will be the alert log files of the primary and standby databases. Using ADRCI, it's easy to read alert log files of all Oracle components and also list specific problems that are recorded in the alert log files.
For each database of a Data Guard configuration where a Data Guard broker is being used, the DMON process writes log data into a logfile. This logfile resides in the same directory as the alert log and is named drc<$ORACLE_SID>.log. It contains important information about the Data Guard's status that can be used to troubleshoot Data Guard's failures.
Let's check this file in our standby database:
$ cd /u01/app/oracle2/diag/rdbms/india_ps/INDIAPS/trace $ tail -50 drcINDIAPS.log ... 2012-12-20 02:15:37.050 Property 'LogFileNameConvert' has inconsistent values:METADATA='', SPFILE='', DATABASE='/u01/app/oracle2/datafile/ORCL, /u01/app/oracle2/datafile/ORCL' 2012-12-20 02:15:37.050 RSM0: HEALTH CHECK WARNING: ORA-16714: the value of property LogFileNameConvert is inconsistent with the database setting 2012-12-20 02:15:37.066 RSM Warning: Property 'LogArchiveTrace' has inconsistent values:METADATA='0', SPFILE='0', DATABASE='8192' 2012-12-20 02:15:37.066 RSM0: HEALTH CHECK WARNING: ORA-16714: the value of property LogArchiveTrace is inconsistent with the database setting 2012-12-20 02:15:37.077 00000000 2049726439 Operation HEALTH_CHECK continuing with warning, status = ORA-16792 2012-12-20 02:15:37.078 00000000 2049726439 Operation HEALTH_CHECK continuing with warning, status = ORA-16792
Dynamic performance views are special database views that are dynamically updated by the database itself and contain important information about the status and performance of database components. It's not possible to insert or update data in these views. DBAs only query them to gather information about the status of the database.
Here, we'll see some of the dynamic performance views that contain information about Data Guard's configuration or status:
V$DATABASE: This view includes a lot of general information about the database. In a Data Guard configuration, it's possible to query the role of the database, the protection mode, and the switchover status using this view. Run the following query in the databases in your Data Guard environment:SQL> SELECT PROTECTION_MODE, PROTECTION_LEVEL, DATABASE_ROLE ROLE, SWITCHOVER_STATUS FROM V$DATABASE; PROTECTION_MODE PROTECTION_LEVEL ROLE SWITCHOVER_STATUS -------------------- ----------------- ---------------- ---------- MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE PHYSICAL STANDBY NOTALLOWED
V$DATAGUARD_CONFIG: This view lists theDB_UNIQUE_NAMEparameters of the databases existing in the Data Guard configuration. You can query this view on any of the databases:SQL> SELECT * FROM V$DATAGUARD_CONFIG; DB_UNIQUE_NAME ------------------------------ INDIA_PS turkey_un INDIA_UN
V$ARCHIVE_DEST_STATUS: This view shows the configuration information for the archived redo log destinations. By running the following query in the primary database, we can display the recovery mode at the archival destination:SQL> SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS where dest_id=2; RECOVERY_MODE ----------------------- MANAGED REAL TIME APPLY
V$MANAGED_STANDBY: We query this view in a physical standby database to monitor the current status of specific Data Guard processes. Run the following query in the physical standby database and see which sequence is being applied and which sequences are being transferred from the primary database to the standby database:SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#,BLOCK#, BLOCKS FROM V$MANAGED_STANDBY; PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS --------- ------------ ---------- ---------- ---------- ---------- ARCH CLOSING 1 3272 18432 2043 ARCH CLOSING 1 3274 20480 1 ARCH CONNECTED 0 0 0 0 ARCH CLOSING 1 3273 18432 2034 RFS IDLE 0 0 0 0 RFS IDLE 0 0 0 0 RFS IDLE 0 0 0 0 MRP0 APPLYING_LOG 1 3275 4098 102400 RFS IDLE 1 3275 4098 1
V$ARCHIVED_LOG: This view contains detailed information about the archived logfiles of databases. In a physical standby database, theAPPLIEDcolumn shows whether the archived logfile was applied or not. The following query shows the archived log sequences that are received from the primary database but not applied:SQL> SELECT THREAD#, SEQUENCE#, FIRST_CHANGE#,NEXT_CHANGE# FROM V$ARCHIVED_LOG where APPLIED='NO'; no rows selected
V$DATAGUARD_STATUS: This view contains messages that are recently written to the alert log or trace files, related with Data Guard services. In case of a Data Guard issue, it's a good method to check errors using this view.SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS'; SQL> SELECT TIMESTAMP, MESSAGE FROM V$DATAGUARD_STATUS WHERE TIMESTAMP>SYSDATE-1 ORDER BY TIMESTAMP; TIMESTAMP MESSAGE -------------------- ------------------------------------------- 20-DEC-2012 01:48:13 Media Recovery Waiting for thread 1 sequence 3272 (in transit) 20-DEC-2012 01:48:16 ARC0: Beginning to archive thread 1 sequence 3272 (20612121-20612129) 20-DEC-2012 01:48:16 ARC0: Completed archiving thread 1 sequence 3272 (0-0) ... 20-DEC-2012 01:56:08 ARC1: Beginning to archive thread 1 sequence 3274 (20612140-20612682) 20-DEC-2012 01:56:08 ARC1: Completed archiving thread 1 sequence 3274 (0-0) 20-DEC-2012 01:56:09 Media Recovery Waiting for thread 1 sequence 3275 (in transit)
V$ARCHIVE_GAP: If there is a gap in a standby database that is blocking recovery, we can query the missing archived logfiles using this view. If there is no gap, the query will not return any rows.SQL> DESC V$ARCHIVE_GAP Name Null? Type --------------------------------------- -------- --------------- THREAD# NUMBER LOW_SEQUENCE# NUMBER HIGH_SEQUENCE# NUMBER SQL> SELECT * FROM V$ARCHIVE_GAP; no rows selected
V$LOGSTDBY_PROCESS: We can monitor SQL Apply in a logical standby database by querying this view. If SQL Apply is not running, the query will not return any rows.SQL> SELECT SID, SERIAL#, SPID, TYPE, HIGH_SCN FROM V$LOGSTDBY_PROCESS; SID SERIAL# SPID TYPE HIGH_SCN ----- ------- ----------- ---------------- ---------- 48 6 11074 COORDINATOR 7178242899 56 56 10858 READER 7178243497 46 1 10860 BUILDER 7178242901 45 1 10862 PREPARER 7178243295 37 1 10864 ANALYZER 7178242900 36 1 10866 APPLIER 7178239467 35 3 10868 APPLIER 7178239463 34 7 10870 APPLIER 7178239461 33 1 10872 APPLIER 7178239472
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.