How to find the alert log location if the database is in the Downstate?

Rameshdba.com

  The contents are of my own testing and deployments. Not guaranteed that these might work in your environment. Please test before usage.


Summary: in this tutorial, you will learn how to find the Alert log location in the oracle database while DB is down,


what is an alert log in the oracle database?

  • The alert log file is the most important log file for Oracle DBAs, because if we get any errors in the Oracle database like a startup, shutdown, log switches, partition add, session kill, and jobs. 
  • An alert log is an easy way to find out the error if the database has struck anywhere.

How to find the alert log location if the database is in the Downstate?

  • We can use the ADRCI utility for that situation.
How we can use this ADRCI utility?

  • Login to the server
[oracle@ltitest ~]$ adrci

ADRCI: Release 12.1.0.2.0 - Production on Wed Mar 8 10:18:51 2023

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

ADR base = "/u01/app/oracle"
adrci> show alert

Choose the home from which to view the alert log:

1: diag/rdbms/ltitest/ltitest
2: diag/rdbms/galaxyprod/Galaxyprod
3: diag/rdbms/rwetest/RWETest
4: diag/rdbms/ltitest1/ltitest1
5: diag/tnslsnr/ltitest/listener
Q: to quit

Please select option: 4
Output the results to file: /tmp/alert_5565_13977_ltitest1_1.ado
2023-02-24 12:20:01.622000 +05:30
Create Relation ADR_CONTROL
Create Relation ADR_INVALIDATION
Create Relation INC_METER_IMPT_DEF
Create Relation INC_METER_PK_IMPTS
USER (ospid: 3759): terminating the instance
2023-02-24 12:20:03.214000 +05:30
Instance terminated by USER, pid = 3759
2023-02-24 12:20:08.217000 +05:30
Deleted file /u01/app/oracle/oradata/ltitest1/control01.ctl
Deleted file /u01/app/oracle/fast_recovery_area/ltitest1/control02.ctl
Completed: drop database
Shutting down instance (abort)
License high water mark = 1
2023-02-24 12:20:09.204000 +05:30
Instance shutdown complete
2023-02-24 12:20:33.160000 +05:30
Starting ORACLE instance (normal) (OS id: 3977)
CLI notifier numLatches:7 maxDescs:519
**********************************************************************
Dump of system resources acquired for SHARED GLOBAL AREA (SGA)
 Per process system memlock (soft) limit = 128G
 Expected per process system memlock (soft) limit to lock
 SHARED GLOBAL AREA (SGA) into memory: 680M
 Available system pagesizes:
  4K, 2048K
 Supported system pagesize(s):
  PAGESIZE  AVAILABLE_PAGES  EXPECTED_PAGES  ALLOCATED_PAGES  ERROR(s)
        4K       Configured          174084          174084        NONE
 Reason for not supporting certain system pagesizes:
  2048K - Dynamic allocate and free memory regions
**********************************************************************
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Initial number of CPU is 4
Number of processor cores in the system is 4
Number of processor sockets in the system is 2
Picked latch-free SCN scheme 3
Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
IMODE=BR
ILAT =51
LICENSE_MAX_USERS = 0
SYS auditing is enabled
NOTE: remote asm mode is local (mode 0x1; from cluster type)
NOTE: Using default ASM root directory ASM
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options.
"/tmp/alert_5565_13977_ltitest1_1.ado" [converted] 9170L, 382548C

You can get a lot of information from the adrci utility.

adrci> show home
ADR Homes:
diag/rdbms/ltitest/ltitest
diag/rdbms/galaxyprod/Galaxyprod
diag/rdbms/rwetest/RWETest
diag/rdbms/ltitest1/ltitest1
diag/tnslsnr/ltitest/listener


adrci> show base
ADR base is "/u01/app/oracle"


Purge alerts and trace files

This will purge data older than 1000 minutes.

adrci> purge -age 1000 -type ALERT
adrci> purge -age 1000 -type TRACE
adrci> purge -age 1000 -type incident
adrci> purge -age 1000 -type cdump


adrci> show incident

ADR Home = /u01/app/oracle/diag/rdbms/ltitest/ltitest:
*************************************************************************
0 rows fetched

ADR Home = /u01/app/oracle/diag/rdbms/galaxyprod/Galaxyprod:
*************************************************************************
INCIDENT_ID          PROBLEM_KEY                                                 CREATE_TIME
-------------------- ----------------------------------------------------------- ----------------------------------------
7273                 ORA 700 [kskvmstatact: excessive swapping observed]         2023-02-07 10:51:19.201000 +05:30

ADR Home = /u01/app/oracle/diag/rdbms/rwetest/RWETest:
*************************************************************************
0 rows fetched

ADR Home = /u01/app/oracle/diag/rdbms/ltitest1/ltitest1:
*************************************************************************
0 rows fetched

ADR Home = /u01/app/oracle/diag/tnslsnr/ltitest/listener:
*************************************************************************
0 rows fetched

                                                Thanks for your time!!

0 Comments

DELETE Statement in ORACLE DB— Step by Step Internal Flow with classroom example.

by
Imagine your Oracle database like a school, and you’re Teacher decides to remove a student (row) from the classroom(table). Let’s see how the journey works step by step: 1)Classroom = Oracle Table 2)Students = Rows (Records) 3)Class Teacher = Oracle Server Process 4)Attendance Register = Data Dictionary User fires DELETE statement: DELETE FROM students WHERE grade = 'D'; Internal Processing Steps: Client Process Sends DELETE Statement to Server Process The teacher receives a request to remove a student from the classroom. Syntax Check (Parsing) The teacher checks if the request is grammatically correct — proper command, keywords, and a semicolon. Semantic Check Teacher verifies if the classroom exists and whether the condition (like grade = 'D') makes sense. Object Resolution Teacher confirms that the ‘students’ classroom (table) exists in the school records (data dictionary). Optimization Decides the fastest way to find those students — by roll number, grade, or al...
Image