DBA classes

  Oracle Database Architecture Basically, there are two main components of Oracle database –– instance and database itself. An instance consists of some memory structures and the background processes, whereas a database refers to the disk resources. ​Process Architecture Physical Structure. Memory Structure and background processes. Logical Structure. ​Memory Structures The two memory structures of Oracle are SGA – System Global Area PGA – Program Global Area ​SGA (Shared Global Area): Once the Instance is started it allocated memory to SGA. It is a basic component of oracle instance its size depends on RAM. Each database instance has its own SGA. Oracle Database automatically allocates memory for an SGA at instance startup and reclaims the memory at instance shutdown. The SGA consists of Database Buffer cache The shared pool The redo log buffer The Large Pool The Java Pool ​Database Buffer cache The database buffer cache is the portion of the SGA that holds copies of data blocks r...

How To Enable/Disable Archive Log Mode In Oracle Database Step 1: Connect to the database the following command ============================== [oracle@localhost ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Thu Apr 22 14:40:27 2021 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 1157627168 bytes Fixed Size 8895776 bytes Variable Size 436207616 bytes Database Buffers 704643072 bytes Redo Buffers 7880704 bytes Database mounted. Database opened. Step 2: To check the ARCHIVELOG mode status, enter the following SQL command: ================================== SQL> archive log list; Database log mode No Archive Mode Automatic archival Disabled Archive destination /u01/app/oracle/archive Oldest online log sequence 22 Current log sequence 24 Step 3:To enable ARCHIVELOG mode status, enter the following SQL commands: =================================...

Listeners : • It is an observer. • When a client request comes, the listener first receives it. And then it establishes a connection between the client and the database instance. • Once the client is connected to the database instance successfully, it hands over the client connection to the server process. • If the listener stops running, you cannot connect to the Oracle Database any more. • Listener can handle the 100 connections. • Default port number is 1521 • Location of the listener file  $ORACLE_HOME/network/admin To verify the up and running listener ps -ef | grep tns [oracle@localhost ~]$ ps -ef | grep tns root 36 2 0 09:24 ? 00:00:00 [netns] oracle 7293 3675 0 10:06 ? 00:00:00 /u01/app/oracle/product/12.2.0.1/dbhome_1/bin/tnslsnr LISTENER -inherit oracle 7320 7234 0 10:06 pts/0 00:00:00 grep --color=auto tns Oracle Listener control commands Listener Status The status of the listener such as alias, version, start date, u...

 Step 1:  Connect to the database with SQL*Plus =============================== [oracle@localhost ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Fri Apr 23 11:11:28 2021 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle.  All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 1157627168 bytes Fixed Size     8895776 bytes Variable Size   436207616 bytes Database Buffers   704643072 bytes Redo Buffers     7880704 bytes Database mounted. Database opened. Step 2: To view alert log, first you need to find it’s location using select command and exit the database ========================================================================= SQL> select value from v$diag_info; VALUE -------------------------------------------------------------------------------- TRUE /u01/app/oracle /u01/app/oracle/diag/rdbms/orcl/orcl /u01/app/oracle/diag/rdbms/orcl/orcl/...

 Alert Log Each database has an alert log file, which contains a chronological(sequential order in which they occurred) log of database messages and errors. Oracle will automatically create a new alert log file whenever the old one is deleted. The alert log includes the following errors and log messages: All internal errors (ORA-600), block corruption errors (ORA-1578), and deadlock errors (ORA-60).  Administrative operations such as DDL statements and the SQL*Plus commands STARTUP, SHUTDOWN, ARCHIVE LOG, and RECOVER Several messages and errors relating to the functions of shared server and dispatcher processes Errors during the automatic refresh of a materialized view Find the Location of alert log file in Oracle To view alert log, first you need to find it’s location using select command. SQL> SELECT VALUE FROM V$DIAG_INFO WHERE NAME = 'Diag Trace'; VALUE -------------------------------------------------------------------------------- /u01/app/oracle/diag/rdbms/orcl/orcl/...