Oracle Database Experts

$ORA_CRS_HOME/log/<hostname>

Has the Oracle Clusterware alert log (alert[hostname].log)
The subdirectories grow fast and need to be purged on a regular basis
Need to check for core and dump files and remove them

Note:
1. This directory be put on shared storage

Subdirectories include:

client
Contains log files for various OCR applications including clscfg, css, ocrcheck, ocrconfig, ocrdump and oifcfg

crsd
houses log files for the crsd daemon including crsd.log

cssd
houses logfiles for ocssd daemon including ocssd.log

evmd
houses logfiles for evmd daemon including evmd.log

racg
houses logfiles for node applications including VIP and ONS

racg logfiles also exist at $ORACLE_HOME/log/hostname/racg

Posted by:  Charles Kim, Oracle ACE

Typical Latencies for RAC Operations

In a RAC AWR report, there is a table in the RAC Statistics section containing average time (latencies) for some Global Cache Services and Global Enqueue Services operations.

Those latencies should be monitored over time, and significant increases in their value should be investigated. Factors that may cause variations to those latencies include:

1. Utilization of the IPC protocol. User-mode IPC protocols are faster

2. Scheduling delays, when the system is under high CPU utilization

3. Log flushes for current blocks served

Other RAC latencies in the AWR reports are mostly derived from V$GES_STATISTICS

The time to process consistent read (cr) block request in the cache corresponds to (build time + flush time + send time), and the time to process current block request in the cache corresponds to (pin time + flush time + send time).

Posted by:  Charles Kim @ DBAExpert.com

Where is the OCR?

rac1.dbaexpert.com:/etc/oracle CRS > cat ocr.loc
ocrconfig_loc=/dev/oracle/ocr_disk1
ocrmirrorconfig_loc=/dev/oracle/ocr_disk2
local_only=FALSE

DBATOOL1 > ocrcheck

Status of Oracle Cluster Registry is as follows :
         Version                  :          2
         Total space (kbytes)     :     306972
         Used space (kbytes)      :       6952
         Available space (kbytes) :     300020
         ID                       :  467462906
         Device/File Name         : /dev/oracle/ocr_disk1
                                    Device/File integrity check succeeded
         Device/File Name         : /dev/oracle/ocr_disk2
                                    Device/File integrity check succeeded

         Cluster registry integrity check succeeded

         Logical corruption check bypassed due to non-privileged user

Posted by: Charles Kim, Oracle ACE Director

trace_level_listener = 16
trace_file_listener = listener
trace_directory_listener = /u01/app/oracle/product/11.1.0/db/network/trace
trace_timestamp_listener = on
trace_filelen_listener = 1000
trace_fileno_listener = 12
logging_listener = off
log_directory_listener = /u01/app/oracle/product/11.1.0/db/network/log
log_file_listener=listener

Explanation of parameters:
TRACE_LEVEL_[CLIENT|SERVER|LISTENER] = [0-16|USER|ADMIN|SUPPORT|OFF]
Determines the degree to which Oracle Net tracing is provided.
Configuration file is sqlnet.ora, listener.ora.
Level 0 is disabled (off) – level 16 (support) is the most verbose tracing level.
Listener tracing requires the Net Listener to be reloaded or restarted after adding trace parameters to listener.ora.
Oracle Net (client/server) tracing takes immediate effect after tracing parameters are added to sqlnet.ora.
By default, the trace level is OFF.

1. OFF (equivalent to 0) disabled – provides no tracing.
2. USER (equivalent to 4) traces to identify user-induced error conditions.
3. ADMIN (equivalent to 6) traces to identify installation-specific problems.
4. SUPPORT (equivalent to 16) trace information required by OSS for troubleshooting.

TRACE_FILE_[CLIENT|SERVER|LISTENER] =
Determines the trace file name.
Configuration file is sqlnet.ora, listener.ora.
Trace file is automatically appended with ‘.trc’.
Default trace file name is sqlnet.trc, listener.trc.

TRACE_DIRECTORY_[CLIENT|SERVER|LISTENER] =
Determines the directory in which trace files are written.
Configuration file is sqlnet.ora, listener.ora.
Default trace directory is $ORACLE_HOME/network/trace

TRACE_TIMESTAMP_[CLIENT|SERVER|LISTENER] = [ON|TRUE|OFF|FALSE]
A timestamp in the form of [DD-MON-YY 24HH:MI;SS] is recorded against each operation traced by the trace file.
Configuration file is sqlnet.ora, listener.ora
Suitable for hanging or slow connection issues.
Default value is is OFF.

TRACE_FILELEN_[CLIENT|SERVER|LISTENER] =
Determines the maximum trace file size in Kilobytes (Kb).
Configuration file is sqlnet.ora, listener.ora.
Default value is UNLIMITED.

TRACE_FILENO_[CLIENT|SERVER|LISTENER] =
Determines the maximum number of trace files through which to perform cyclic tracing.
Configuration file is sqlnet.ora, listener.ora.
Suitable when disk space is limited or when tracing is required to be enabled for long periods.
Default value is 1 (file).

LOG_DIRECTORY_[CLIENT|SERVER|LISTENER] =
Determines the directory in which log files are written.
Configuration file is sqlnet.ora, listener.ora.
Default directory is $ORACLE_HOME/network/log.

LOG_FILE_[CLIENT|SERVER|LISTENER] =
Determines the log file name.
Configuration file is sqlnet.ora, listener.ora.
Log file is automatically appended with ‘.log’.
Default log file name is sqlnet.log, listener.log

LOGGING_LISTENER = [ON|OFF]
Disables Listener logging facility.
Configuration file is listener.ora.
Default value is ON.

#CLIENT-SIDE SQL*NET TRACE PARAMETERS
#====================================
TRACE_UNIQUE_CLIENT = ON
TRACE_LEVEL_CLIENT = 16
TRACE_DIRECTORY_CLIENT = C:\temp
TRACE_FILE_CLIENT = SQLNetTrace
TRACE_TIMESTAMP_CLIENT = ON
log_file_client = cli
log_directory_client = /u01/app/oracle/product/9.0.1/network/log

#Beyond 8.0
#trace_filelen_client = 100
#trace_fileno_client = 2
#tnsping.trace_directory = /u01/app/oracle/product/9.0.1/network/trace
#tnsping.trace_level = admin

CRSD: Engine for HA operation
Manages (start/stop/respawn) application resources
Maintains configuration profiles in the OCR (Oracle Configuration Repository)
Stores current known state in the OCR
Runs as root
Is restarted automatically on failure
OCSSD:
OCSSD is part of RAC and Single Instance with ASM
Provides access to node membership, group services, basic cluster locking
Integrates with vendor clusterware, when present
Can also runs without integration to vendor clusterware
Runs as Oracle
Failure exit causes machine reboot
Prevents data corruption in event of a split brain.
EVMD:
Generates events when things happen
Spawns/respawns evmlogger(s)
Scans callout directory and invokes callouts
Runs as Oracle
It is respawned automatically on failure

ocrcheck
——————————————————————————-
CRS > ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 2
Total space (kbytes) : 102508
Used space (kbytes) : 5360
Available space (kbytes) : 97148
ID : 1255672694
Device/File Name : /dev/dba_ocr_disk
Device/File integrity check succeeded

Device/File not configured

Cluster registry integrity check succeeded
——————————————————————————-
Note: The default logging directory $ORA_CRS_HOME/log/hostname/client

OCRDUMP
The ocrdump command can be used to dump the OCR (crs must be running)
ocrdump /tmp/`hostname`_ocrdump_`date +%m%d%y:%H%M`

Additional ocrdump options
Send the output to the screen –> ocrdump -stdout
ocrdump -stdout -keyname SYSTEM.css

Write it out to XML format:
ocrdump -stdout -keyname SYSTEM.css -xml

OCRCONFIG
ocrconfig -showbackup
o The default location is $ORA_CRS_HOME/cdata/crs
——————————————————————————-
rac1.dbaexpert.com2007/10/06 03:31:22 /apps/oracle/product/10.2.0/CRS/cdata/crs
rac1.dbaexpert.com2007/10/05 23:31:22 /apps/oracle/product/10.2.0/CRS/cdata/crs
rac1.dbaexpert.com2007/10/05 19:31:21 /apps/oracle/product/10.2.0/CRS/cdata/crs
rac1.dbaexpert.com2007/10/05 03:31:19 /apps/oracle/product/10.2.0/CRS/cdata/crs
rac1.dbaexpert.com2007/10/02 03:31:09 /apps/oracle/product/10.2.0/CRS/cdata/crs
——————————————————————————-

If you cd to $ORA_CRS_HOME/cdata/crs, you should see a day_.ocr, week.ocr and month.ocr.
–> I have yet to see the month ocr though.
-rw-r–r– 1 root system 10567680 Oct 02 03:31 week.ocr
-rw-r–r– 1 root system 10567680 Oct 05 03:31 day.ocr
-rw-r–r– 1 root system 10567680 Oct 06 03:31 day_.ocr
-rw-r–r– 1 root system 10567680 Oct 06 03:31 backup02.ocr
-rw-r–r– 1 root system 10567680 Oct 06 07:31 backup01.ocr
-rw-r–r– 1 root system 10567680 Oct 06 11:31 backup00.ocr

Note:
1. This is another reason for incremental backups on the file systems
2. We can move the ocrdump to $ORACLE_BASE/admin/$ORACLE_SID/bkups directory
3. Daily backups occur every 4 hours
To restore – as root:
ocrconfig -restore FILENAME

Posted by: Charles Kim @ DBAExpert.com

TWO MAIN WAYS

Option #1: /etc/init.d/init.crs
————————————————————————–
# Supported options:
# init.crs start manual startup of the CRS stack
# init.crs stop shuts down the CRS stack
# init.crs disable prevents CRS stack from starting at boot time
# init.crs enable allows CRS stack to start at boot time
————————————————————————–

init q
——
Tells the init command to re-examine the /etc/inittab file

Contents of /etc/inittab:
h1:2:respawn:/etc/init.evmd run >/dev/null 2>&1 </dev/null
h2:2:respawn:/etc/init.cssd fatal >/dev/null 2>&1 </dev/null
h3:2:respawn:/etc/init.crsd run >/dev/null 2>&1 </dev/null

Option #2:
$ORA_CRS_HOME/bin/crsctl stop crs
————————————————————————–
crsctl enable crs – enables startup for all CRS daemons
crsctl disable crs – disables startup for all CRS daemons
crsctl start crs – starts all CRS daemons.
crsctl stop crs – stops all CRS daemons. Stops CRS resources in case of cluster.
————————————————————————–

crs_start and crs_stop
New feature introduced in 10g R2
The crs_start and crs_stop command can be used to start and stop CRS managed resources
$ crs_start resource-name -all (all resource)
$ crs_stop resource-name -all (all resource)

crs_relocate
change where resource is active$ORA_CRS_HOME/bin/crsctl stop crs
————————————————————————–
crsctl enable crs – enables startup for all CRS daemons
crsctl disable crs – disables startup for all CRS daemons
crsctl start crs – starts all CRS daemons.
crsctl stop crs – stops all CRS daemons. Stops CRS resources in case of cluster.
————————————————————————–

Below are the high level steps to setup Oracle Streams: 1. Put the Source database in ARCHIVELOG mode (if not already) 2. Create a tablespace dedicated for streams queue tables 3. Set up the Streams administrator (in this particular example, the user will be streams_admin) with the default tablespace of the streams_data tablespace and connect, resource and DBA. The easiest way to setup the streams administrative user is to use EM DBConsole. The streams user setup is located in the Data Movement Tab: Data Movement > Streams > Setup > Streams Global, Schema, Table, and Subset Replication Wizard 4. On both source and target databases, grant the streams administrator privilege using the DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE procedure 5. Setup the streams_pool_size (>= 200mb) and global_names init.ora parameters 6. Also setup and job_queue_processes (>2) and compatible parameter (>9.2.0) 7. On both the source and target, create a private database link under the Streams Administrator schema in the source database to the target database using DBMS_STREAMS_ADM.SET_UP_QUEUE(); 8. Enable supplemental logging at the source database at either the table or database level: ALTER TABLE docs ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE) COLUMNS; 9. Configure the capture process at the source database at either the table, schema or database level using the DBMS_STREAMS_ADM.ADD_TABLE_RULES procedure or add_schema_rules procedure to configure capture at the schema level or add_global_rules procedure to configure capture at the database level. 10. Configure the propagation process at the source database using the DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES procedure. 11. Create the destination table using rman, exp/imp, Data Pump, copy, etc. 12. Grant object privileges to streams administrator so that it can perform the appropriate DML and DDL 13. Obtain the SCN of the source database using DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER() ß- For Oracle9i SELECT current_scn FROM V$DATABASE; ß From Oracle10g onwards 14. Set the instantiation SCN using DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@TARGET_DB_LINK 15. Configure the apply process at the destination database using the DBMS_STREAMS_ADM.ADD_TABLE_RULES procedure 16. Start the capture and apply processes using DBMS_CAPTURE_ADM.START_CAPTURE at the source and DBMS_CAPTURE_ADM.START_APPLY at the target DB

Default VIP parameters can be changed via modifying Oracle's $CRS_HOME/bin/racgvip shell script:
1. The VIP check interval can be changed (default is 60 seconds) to ensure that it is up and running
2. The default restart attempts is set to 1
3. The default gateway can be changed

ASM Filenames
Some file name formats are derived at creation time and some are used for referencing ASM files (aliases).

Filename Type Format
Fully Qualified ASM Filename +dg/DATABASE_NAME/file_type/file_type_tag.file.incarnation
Numeric ASM Filename +dg.file.incarnation
Alias ASM Filenames +dg/directory/filename
Alias ASM Filename with Template +dg(template)/alias
Incomplete ASM Filename +dg
Incomplete ASM Filename with Template +dg(template)

ASM Dynamic Views
v$asm_alias – Lists all aliases in all currently mounted diskgroups
v$asm_client – Lists all the databases currently accessing the diskgroups
v$asm_disk – Lists all the disks discovered by the ASM instance
v$asm_diskgroup – Lists all the diskgroups discovered by the ASM instance
v$asm_file – Lists all files that belong to diskgroups mounted by the ASM instance
v$asm_operation – Reports information about current active operations.
Rebalance activity is reported in this view
v$asm_template – Lists all the templates currently mounted by the ASM instance
v$asm_diskgroup_stat – same as v$asm_diskgroup but it does discover new diskgroups. You should use this view instead of v$asm_diskgroup
v$asm_disk_stat – same as v$asm_disk but it does not discover new disks. You should use this view instead of v$asm_disk

Fixed Views
x$kfals – ASM aliases
x$kfdsk – ASM disks
x$kffil – ASM files
x$kfgrp – ASM diskgroups
x$kfgmg – ASM operations
x$kfkid – ASM disk performance
x$kfncl – ASM clients
x$kf??? – ASM templates
x$kffxp – ASM extents of ASM files relative to extents in the ASM diskgroup. Can use this view to see how well balanced individual disks are.

x$kfdat – ASM extent of diskgroup relative to extents in the ASM file