| PMON trace file | $ORACLE_BASE/diag/rdbms/database_name/SID/trace/SID_pmon_PID.trc | where SID is the database SID and PID is the OS process ID |
| Process Spawner Process | $ORACLE_BASE/diag/rdbms/database_name/SID/trace/SID_pspN_PID.trc | where SID is the database SID, N is the process No and PID is the OS process ID |
| Virtual Keeper of Time | $ORACLE_BASE/diag/rdbms/database_name/SID/trace/SID_vktm_PID.trc | where SID is the database SID and PID is the OS process ID |
| General Task Execution | $ORACLE_BASE/diag/rdbms/database_name/SID/trace/SID_genN_PID.trc | where SID is the database SID, N is the process No and PID is the OS process ID |
| Diagnostic Capture | $ORACLE_BASE/diag/rdbms/database_name/SID/trace/SID_diag_PID.trc | where SID is the database SID and PID is the OS process ID |
| DB Resource Manager | $ORACLE_BASE/diag/rdbms/database_name/SID/trace/SID_dbrm_PID.trc | where SID is the database SID and PID is the OS process ID |
| Interconnect Latency | $ORACLE_BASE/diag/rdbms/database_name/SID/trace/SID_ping_PID.trc | where SID is the database SID and PID is the OS process ID |
| Automic CF to Memory | $ORACLE_BASE/diag/rdbms/database_name/SID/trace/SID_acms_PID.trc | where SID is the database SID and PID is the OS process ID |
| Diagnostic Process | $ORACLE_BASE/diag/rdbms/database_name/SID/trace/SID_diaN_PID.trc | where SID is the database SID, N is the process No and PID is the OS process ID |
| Global ENQ Service Monitor | $ORACLE_BASE/diag/rdbms/database_name/SID/trace/SID_lmon_PID.trc | where SID is the database SID and PID is the OS process ID |
| Global ENQ Service Daemon | $ORACLE_BASE/diag/rdbms/database_name/SID/trace/SID_lmdN_PID.trc | where SID is the database SID, N is the process No and PID is the OS process ID |
| Global Cache Serivce | $ORACLE_BASE/diag/rdbms/database_name/SID/trace/SID_lmsN_PID.trc | where SID is the database SID, N is the process No and PID is the OS process ID |
| RAC Management | $ORACLE_BASE/diag/rdbms/database_name/SID/trace/SID_rmsN_PID.trc | where SID is the database SID, N is the process No and PID is the OS process ID |
| Global Cache/ENQ HB Mon | $ORACLE_BASE/diag/rdbms/database_name/SID/trace/SID_lmhb_PID.trc | where SID is the database SID and PID is the OS process ID |
| Memory Manager | $ORACLE_BASE/diag/rdbms/database_name/SID/trace/SID_mman_PID.trc | where SID is the database SID and PID is the OS process ID |
| DB Writer | $ORACLE_BASE/diag/rdbms/database_name/SID/trace/SID_dbwN_PID.trc | where SID is the database SID, N is the process No and PID is the OS process ID |
| Log Writer | $ORACLE_BASE/diag/rdbms/database_name/SID/trace/SID_lgwr_PID.trc | where SID is the database SID and PID is the OS process ID |
| Checkpoint | $ORACLE_BASE/diag/rdbms/database_name/SID/trace/SID_ckpt_PID.trc | where SID is the database SID and PID is the OS process ID |
| System Monitor | $ORACLE_BASE/diag/rdbms/database_name/SID/trace/SID_smon_PID.trc | where SID is the database SID and PID is the OS process ID |
| Recovery | $ORACLE_BASE/diag/rdbms/database_name/SID/trace/SID_reco_PID.trc | where SID is the database SID and PID is the OS process ID |
| ASM Rebalance Master | $ORACLE_BASE/diag/rdbms/database_name/SID/trace/SID_rbal_PID.trc | where SID is the database SID and PID is the OS process ID |
| ASM Background | $ORACLE_BASE/diag/rdbms/database_name/SID/trace/SID_asmb_PID.trc | where SID is the database SID and PID is the OS process ID |
| Manageability Monitor | $ORACLE_BASE/diag/rdbms/database_name/SID/trace/SID_mmon_PID.trc | where SID is the database SID and PID is the OS process ID |
| Manageability Monitor Lite | $ORACLE_BASE/diag/rdbms/database_name/SID/trace/SID_mmnl_PID.trc | where SID is the database SID and PID is the OS process ID |
| Mark AU Sync Coordinator | $ORACLE_BASE/diag/rdbms/database_name/SID/trace/SID_mark_PID.trc | where SID is the database SID and PID is the OS process ID |
| Instance ENQ Background | $ORACLE_BASE/diag/rdbms/database_name/SID/trace/SID_lckN_PID.trc | where SID is the database SID, N is the process No and PID is the OS process ID |
| Remote Slave Monitor | $ORACLE_BASE/diag/rdbms/database_name/SID/trace/SID_rsmn_PID.trc | where SID is the database SID and PID is the OS process ID |
| DG Broker Monitor | $ORACLE_BASE/diag/rdbms/database_name/SID/trace/SID_dmon_PID.trc | where SID is the database SID and PID is the OS process ID |
| PQ Slave | $ORACLE_BASE/diag/rdbms/database_name/SID/trace/SID_pxxx_PID.trc | where SID is the database SID, xxx is the slave No and PID is the OS process ID |
| ASM Connection Pool | $ORACLE_BASE/diag/rdbms/database_name/SID/trace/SID_oxxx_PID.trc | where SID is the database SID, xxx is the proc No and PID is the OS process ID |
| Archiver | $ORACLE_BASE/diag/rdbms/database_name/SID/trace/SID_arcN_PID.trc | where SID is the database SID, N is the process No and PID is the OS process ID |
| REDO Transport | $ORACLE_BASE/diag/rdbms/database_name/SID/trace/SID_nsaN_PID.trc | where SID is the database SID, N is the proc No and PID is the OS process ID |
| Global Transaction | $ORACLE_BASE/diag/rdbms/database_name/SID/trace/SID_gtxN_PID.trc | where SID is the database SID, N is the proc No and PID is the OS process ID |
| Result Cache | $ORACLE_BASE/diag/rdbms/database_name/SID/trace/SID_rcbg_PID.trc | where SID is the database SID and PID is the OS process ID |
| AQ Coordinator | $ORACLE_BASE/diag/rdbms/database_name/SID/trace/SID_qmnc_PID.trc | where SID is the database SID and PID is the OS process ID |
| AQ Server Class | $ORACLE_BASE/diag/rdbms/database_name/SID/trace/SID_qxxx_PID.trc | where SID is the database SID, xxx is the proc No and PID is the OS process ID |
| Space Mgmt Coordinator | $ORACLE_BASE/diag/rdbms/database_name/SID/trace/SID_smco_PID.trc | where SID is the database SID and PID is the OS process ID |
| Job Queue Coordinator | $ORACLE_BASE/diag/rdbms/database_name/SID/trace/SID_cjqN_PID.trc | where SID is the database SID, N is the proc No and PID is the OS process ID |
| Space Mgmt Slave | $ORACLE_BASE/diag/rdbms/database_name/SID/trace/SID_wxxx_PID.trc | where SID is the database SID, xxx is the slave No and PID is the OS process ID |
| Space Mgmt Slave | $ORACLE_BASE/diag/rdbms/database_name/SID/trace/SID_wxxx_PID.trc | where SID is the database SID, xxx is the slave No and PID is the OS process ID |
| User Process | $ORACLE_BASE/diag/rdbms/database_name/SID/trace/SID_ora_PID.trc | where SID is the database SID and PID is the OS process ID |
Monday, January 6, 2020
Oracle Trace Files - Location
Oracle Log Files - Location
The following tables detail the location and purpose of the Oracle database log files and trace files. This includes Database log files and Grid Infrastructure log files written to as part of a RAC cluster or Oracle's HA solutions.
Database Log File - Location
Database Log File - Location
| Oracle Database Alert Log | $ORACLE_BASE/diag/rdbms/database_name/SID/trace/alert_SID.log | where SID is the database SID |
Grid Infrastructure Log Files
| Clusterware Alert Log | $ORA_CRS_HOME/log/hostname/alerthostname.log | |
| Disk Monitor Daemon | $ORA_CRS_HOME/log/hostname/diskmon | |
| OCRDUMP, OCRCHECK, OCRCONFIG, CRSCTL | $ORA_CRS_HOME/log/hostname/client | |
| Cluster Time Synchronization Service | $ORA_CRS_HOME/log/hostname/ctssd | |
| Grid Interprocess Communication Daemon | $ORA_CRS_HOME/log/hostname/gipcd | |
| Oracle High Availability Services Daemon | $ORA_CRS_HOME/log/hostname/ohasd | |
| Cluster Ready Services Daemon | $ORA_CRS_HOME/log/hostname/crsd | |
| Grid Plug and Play Daemon | $ORA_CRS_HOME/log/hostname/gpnpd | |
| Mulitcast Domain Name Service Daemon | $ORA_CRS_HOME/log/hostname/mdnsd | |
| Event Manager Daemon | $ORA_CRS_HOME/log/hostname/evmd | |
| Cluster Synchronization Service Daemon | $ORA_CRS_HOME/log/hostname/cssd | |
| Server Manager | $ORA_CRS_HOME/log/hostname/srvm | |
| HA Service Daemon Agent | $ORA_CRS_HOME/log/hostname/agent/ohasd/oraagent_oracle | |
| HA Service Daemon CSS Agent | $ORA_CRS_HOME/log/hostname/agent/ohasd/oracssdagent_root | |
| HA Service Daemon ocssd Monitor Agent | $ORA_CRS_HOME/log/hostname/agent/ohasd/oracssdmonitor_root | |
| HA Service Daemon Oracle Root Agent | $ORA_CRS_HOME/log/hostname/agent/ohasd/orarootagent_root | |
| CRS Daemon Oracle Agent | $ORA_CRS_HOME/log/hostname/agent/crsd/oraagent_oracle | |
| CRS Daemon Oracle Root Agent | $ORA_CRS_HOME/log/hostname/agent/crsd/orarootagent_root | |
| Grid Naming Service Daemon | $ORA_CRS_HOME/log/hostname/gnsd | |
| SCAN Listener Logs | $ORA_CRS_HOME/log/diag/tnslsnr/hostname/listener_scanN/trace | where N is the node number |
TKPROF
The TKPROF program converts Oracle trace files into a more readable form.
TKPROF also determine the execution plans of SQL statement and creates a SQL script that stores the statistics in the database.
Steps and Commands to trace a session and run TKPROF :-
1. Login to putty and connect to Oracle .
2. To get the name of the trace file that will be generated run the following sql in the same session.
select rtrim(c.value,'/')|| '/'||d.instance_name|| '_ora_'|| ltrim(to_char(a.spid))||'.trc'
from v$process a, v$session b, v$parameter c, v$instance d
where a.addr=b.paddr
and b.audsid=sys_context('userenv','sessionid')
and c.name='user_dump_dest';
The result of the above query gives the path and name of the trace file that will be generated for the session.
3. Following parameter have to be set before tracing a session.
SET TIMING ON
4. Following queries have to be fired to trace the session
alter session set timed_statistics=true;
TIMED_STATISTICS is set to TRUE to get timing information in our trace files.
alter session set max_dump_file_size = unlimited;
MAX_DUMP_FILE_SIZE – Controls the maximum size of the trace file.
alter session set events '10046 trace name context forever, level 12';
Above command enables tracing for our specific user session.
5. Execute all the SQLs or PL/SQLs for which we want to trace the session.
6. Once we are done with tracing, we either exit our session to stop tracing or we can use the alter session command to stop it
ALTER SESSION SET EVENTS '10046 trace name context OFF';
7. We can also set trace outside an applications session, i.e. if we don’t have access to the source code, we can remotely enable an extended trace for another session.
The command for the same is
EXEC SYS.DBMS_SYSTEM.SET_EV(&SID, &SERIAL_NUMBER, 10046, &TRACELEVEL, ' ');
The SID and SERIAL# can be fetched from V$SESSION.
Oracle will create the trace file in the server’s udump directory.
To stop the trace in the other session we can use the command as explained in step 6 or we use the following command: -
EXEC SYS.DBMS_SUPPORT.STOP_TRACE_IN_SESSION(SID, SERIAL_NUMBER);
8. Connect to a duplicate session in Putty and go to the path where the trace file is generated (we get the path from the step 2). We can open the .trc file and check the content of it but the content is not in a readable format. Thus, we go for TKPROF.
9. Go to the tkprof directory in the BIN directory of the Oracle and run the following command:-
TKPROF /u01/app/ORACLE/PRODUCT/12.2.0/ADMIN/TEST\UDUMP\TEST_ORA_4936.TRC /u01/temp/OUTPUT.TXT
EXPLAIN=I102/ORACLE66@TEST TABLE=SYS.PLAN_TABLE SYS=NO WAITS=YES SORT=FCHELA,EXEELA,PRSELA
The output file name should be given with proper path where we want to create the .prf file.
NOTE: - We should have the 777 permission(permission to read, write, and execute) on all directories to run the TKPROF.
RAC startup and shutdown procedure
How to Shutdown Oracle Real Application ClustersDatabase ?
1. Shutdown Oracle Home process accessing database.
2. Shutdown RAC Database Instances on all nodes.
3. Shutdown All ASM instances from all nodes.
5. Shut down the Oracle Cluster ware or CRS.
1. Shutdown Oracle Home process accessing database:
[grid@node1 bin]$ srvctl stop listener -n node1
[grid@node1 bin]$ srvctl status listener -n node1
Listener LISTENER is enabled on node(s): node1
Listener LISTENER is not running on node(s): node1
2. Shutdown RAC Database Instances on all nodes:
[oracle@node2 ~]$ srvctl status database -d RACDB
Instance RACDB1 is running on node node1
Instance RACDB2 is running on node node2
[oracle@node2 ~]$ srvctl stop database -d RACDB
[oracle@node2 ~]$ srvctl status database -d RACDB
Instance RACDB1 is not running on node node1
Instance RACDB2 is not running on node node2
***We just need to execute one command from any one of the server having database and it will stop all database instances on all servers.
3. Shutdown All ASM instances from all nodes:
[grid@node2 oracle]# srvctl stop asm -n node1 -f
[grid@node2 oracle]# srvctl stop asm -n node2 -f
[grid@node2 oracle]# srvctl status asm -n node1
ASM is not running on node1
[grid@node2 oracle]# srvctl status asm -n node2
ASM is not running on node2
***Sometimes, Database administrator face some issues in stopping ASM instance, In that case use "-f" option to forcefully shutdown ASM instances.
4. Shutdown Node applications running on nodes:
[grid@node2 oracle]# srvctl stop nodeapps -n node1 -f
[grid@node2 oracle]# srvctl status nodeapps -n node1
VIP node1-vip is enabled
VIP node1-vip is running on node: node1
Network is enabled
Network is running on node: node1
GSD is disabled
GSD is not running on node: node1
ONS is enabled
ONS daemon is running on node: node1
***Repeat same command for all nodes one by one. If you face any issue in stopping node applications use "-f" as force option to stop applications.
5. Shut down the Oracle Clusterware or CRS:
[root@node1 bin]# crsctl check cluster -all
**************************************************************
node1:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
*************************************************************
node2:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
**************************************************************
[root@node1 bin]# crsctl stop crs
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'node1'
CRS-2673: Attempting to stop 'ora.crsd' on 'node1'
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on 'node1'
CRS-2673: Attempting to stop 'ora.LISTENER_SCAN2.lsnr' on 'node1'
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'node1'
CRS-2673: Attempting to stop 'ora.LISTENER_SCAN3.lsnr' on 'node1'
CRS-2673: Attempting to stop 'ora.node2.vip' on 'node1'
-------------------------------------------------
-------------------------------------------------
-------------------------------------------------
CRS-2677: Stop of 'ora.cssd' on 'node1' succeeded
CRS-2673: Attempting to stop 'ora.gipcd' on 'node1'
CRS-2677: Stop of 'ora.gipcd' on 'node1' succeeded
CRS-2673: Attempting to stop 'ora.gpnpd' on 'node1'
CRS-2677: Stop of 'ora.gpnpd' on 'node1' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'node1' has completed
CRS-4133: Oracle High Availability Services has been stopped.
[root@node1 bin]# crsctl check cluster -all
CRS-4639: Could not contact Oracle High Availability Services
CRS-4000: Command Check failed, or completed with errors.
How to Start Oracle Real Application Clusters Database ?
1. Start Oracle Clusterware or CRS.
2. Start Node applications running on nodes.
3. Start All ASM instances from all nodes.
4. Start RAC Database Instances on all nodes.
5. Start Oracle Home process accessing database.
1. Start Oracle Clusterware or CRS:
[root@node1 bin]# crsctl start crs
CRS-4123: Oracle High Availability Services has been started
[root@node2 bin]# crsctl check cluster -all
**************************************************************
node1:
CRS-4535: Cannot communicate with Cluster Ready Services
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
**************************************************************
node2:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
**************************************************************
***Here, DBA can see "CRS-4639: Could not contact Oracle High Availability Services" or "CRS-4535: Cannot communicate with Cluster Ready Services" messages. Wait 5 minutes and then again check with "crsctl check cluster -all" command. This time Database administrator will get "CRS-4537: Cluster Ready Services is online".
*****If still same issue DBA can start ora.crsd process to resolve this issue. Below is the command
[root@node1 bin]# crsctl start res ora.crsd -init
[root@node1 bin]# crsctl check cluster -all
**************************************************************
node1:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
**************************************************************
node2:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
**************************************************************
2. Start Node applications running on nodes:
[grid@node1 bin]$ srvctl start nodeapps -n node1
[grid@node1 bin]$ srvctl status nodeapps -n node1
VIP node1-vip is enabled
VIP node1-vip is running on node: node1
Network is enabled
Network is running on node: node1
GSD is disabled
GSD is not running on node: node1
ONS is enabled
ONS daemon is running on node: node1
***DBA has to execute this command for each node to start Real Application Clusters Cluster database.
3. Start All ASM instances from all nodes:
[grid@node1 bin]$ srvctl start asm -n node1
[grid@node1 bin]$ srvctl status asm -n node1
ASM is running on node1
DBA has to start ASM instance on all database nodes.
4. Start RAC Database Instances on all nodes:
[grid@node1 bin]$ srvctl start database -d RACDB
[grid@node1 bin]$ srvctl status database -d RACDB
Instance RACDB1 is running on node node1
Instance RACDB2 is running on node node2
5. Start Oracle Home process accessing database:
[grid@node1 bin]$ srvctl start listener -n node1
[grid@node1 bin]$ srvctl status listener -n node1
Listener LISTENER is enabled on node(s): node1
Listener LISTENER is running on node(s): node1
Subscribe to:
Posts (Atom)