Oracle DBA Flashcards
What is PGA or Private Global Area?
Any work inside a system or server is done through background processes. These processes need some memory to store basic information. On database server, we have one server process created for every user connection. These server process also takes some memory. This memory is known as PGA.
What is SGA?
SGA is shared global area. It is one of the parts of Oracle database instance and resides on RAM. Anything placed in SGA is shared with all the users
What do you understand by In-Memory sort?
All the small data filtering happens in PGA. This is known as In-Memory sort. If the data is big, sorting is done under temp tablespace
Can you explain how an update statement is executed in the database?
In an update statement, we need the old value as the user might rollback the transaction. Hence, undo tablespace comes into the picture. The user data and a free undo block are copied into the LRU list. These blocks are then copied to PGA where data swapping happens. Redo entries are generated and the dirty blocks are placed in the write list. LGWR writes redo entries and then DBWR writes dirty blocks to the database.
Explain about SCN and checkpoint number
- SCN is a unique transaction number assigned to a set of redo logs generated. This identifies that OK, these all redo entries are part of one transaction.
- A checkpoint is a database event, which synchronizes the database blocks in memory with the data files on disk. It has two main purposes – To establish a data consistency and enable faster database Recovery.
Which groups are assigned to the oracle user for installation and administration purposes?
oinstall and dba
Why run orainstRoot.sh and root.sh scripts at the end of installation?
orainstRoot.sh will change the permissions for oraInventory and
root.sh will create the oratab file.
Explain Oracle installation pre-requisite steps
Create oinstall& dba groups, modify kernel parameters, check disk space for installation, create oracle user and provide permissions on installation location to oracle user
Why wouldn’t you be able to use the SQLPLUS utility?
Environment variables aren’t set (ORACLE_HOME, PATH) or .bash_profile is not executed immediately after making changes to it.
How can I check environment variables are set properly?
Using env | grep ORA.
Using echo command like echo $ORACLE_HOME.
How would you determine what databases are running on an instance?
ps -ef|grep pmon
Can we change the database block size after creation?
No
You’ve been tasked with decommissioning a database, what are your steps?
- Check to see what is actively connecting to the DB
- Shutdown the Listener for a period of time to determine no users are accessing it still
- Shutdown the DB and take a backup
- Drop the Database
How do I determine who is connected to the DB?
V$SESSION
How do I terminate a user session?
alter system kill session ‘SID, Serial#’;
What is the difference between a pfile and an spfile?
Pfile is human readable file and spfile is binary file. We can start database instance with either of the files but first preference is given to spfile.
Both reside under $ORACLE_HOME/dbs location and are used to allocate instance memory
If I only have an spfile how do I create a pfile?
create pfile from spfile;
What are the stages of starting a database?
NOMOUNT: Starts the instance by reading the initialization file but does not open the database.
MOUNT: Associates the instance with the database and reads control files.
OPEN: Fully opens the database, making it accessible for users and applications.
How do you resize redo log files?
You have to create a new group and then drop the old one.
What is OMF?
Oracle Managed Files. Oracle will do a fair amount of the work for you but the naming conventions are generally system generated. They are used with ASM.
What is a trace file and how does one make one?
A trace file is created for each server and backend process. When a process or user process detects an operational fault, it spills data about the bugs to its trace.
What happens when you run a SQL Statement in Oracle?
First, it validates the syntax, if it finds the results in the buffer, it’ll return the results, else, it’ll generates an execution plan based on current info available to the engine, then it’ll return all the results
What type of files make up a Database?
Redo Log Files, Parameter Files, Control Files, Data Files, Password Files
What are the different types of Oracle Database Shutdown Modes?
Normal, Immediate, Transactional, Abort
What are the main aspects of Oracle database security management?
- Controlling access to data(authorization).
- Restricting access to legitimate users (authentication).
- Ensuring accountability on part of the users(auditing).
- Safeguarding key data in the database(encryption).
- Managing the security of the entire organizational information structure (enterprise security).
What are the contents of the Control File?
- The database name
- Names and locations of associated datafiles and online redo log files
- The timestamp of the database creation
- The current log sequence number
- Checkpoint information
How do you recover a database if you lose all the control files?
Rman to the Target, Start in nomount, restore from autobackup, recover, open resetlogs;
What is a SYSTEM tablespace and why do we need it?
System tablespace is created at the time of database creation. This tablespace holds all the data dictionary tables and hence it is used for the creation of various database objects. System tablespace must stay online for the database to function smoothly.
What is a Recovery Catalog?
It stores information on:
- Data files & their backup files.
- Archived Redo Logs & their backup sets.
- Stored scripts
- Incarnation
- Backup history
How can we identify the resources for which the sessions are waiting?
v$session_waits and v$system_waits
How can we monitor the space allocations in a database?
OEM or DBA_FREE_SPACE, DBA_SEGMENTS, DBA_DATA_FILES
Why do we create Stored Procedures & Functions in PL/SQL and how are they different?
- SPs may or may not return a value, can include DML, can call functions, support Try/Catch
- Functions always return only a single value, no DML, can’t call procedures, no Try/Catch
What is SCN?
System Change Number – This is a value added whenever a deadlock occurs or database changes are made.
What are some of the Top Linux Commands for an Oracle DBA
- free -> finding out free memory
- top -> finding out top running tasks in OS
- iostat -> Report OS i/o stats
- sar -> monitor the CPU,memory, disk, and network
What table holds info on actual SQL Commands recently executed, how many times it was executed, and how much CPU time it took?
V$SQL and V$SQLAREA
Which table holds general information on the database?
V$DATABASE
Where can you get info on SQL Queries that are taking longer than 6 seconds?
V$SESSION_LONGOPS
Where can you find info on what queries are waiting for?
V$SESSION_WAIT
Where can you find info on what the database has been waiting on since the last start-up?
V$SYSTEM_WAIT_CLASS
How do you validate backups?
RMAN> VALIDATE DATABASE;
How do you validate the backup of a PDB?
RMAN> VALIDATE PLUGGABLE DATABASE <pdb_name>;</pdb_name>
How do you restart a service in RHEL?
systemctl restart <service_name></service_name>
How do you query what is actively going on inside the database?
V$PROCESSES
How do you determine what is blocking a certain session?
V$SESSION_BLOCKERS
What are the 3 modes of Dataguard?
- Maximum Availability
- Maximum Protection
- Maximum Performance
What is the Linux command to check OS Performance?
vmstat <how> <How></How></how>
What is the Linux command that shows the current usage of memory?
free
What is the Linux command that shows OS I/O stats
iostat
How do you monitor the CPU, memory, disk, and network information in Linux?
sar
Which sar flag shows basic CPU information?
sar -u
Which sar flag shows Unused Memory?
sar -r
Which sar flag shows Average Disk I/O?
sar -b
Which Linux command shows the most intensive processes in the OS?
top
How do you shutdown and reboot the server in Linux?
init 6 reboots and init 0 shutsdown
What is the purpose of the Oracle Listener?
Registers Oracle DBs and then directs remote connections to the proper DB
Explain the architecture of Oracle Database.
Oracle consists of the following components:
Instance: Set of memory structures and background processes that manage a database
Database: Collection of physical files on the disk
Tablespace: Logical storage unit within a database where the data is stored.
Data Files, ControlFiles, Redo Log Files: Physical files associated with the DB.
How do you identify and resolve performance bottlenecks in Oracle?
Using tools like OEM or by querying dynamic performance views (V$SESSION, V$SQL, V$SQL_PLAN). Common tuning techniques include optimizing SQL queries, adding indexes, adjusting memory parameters, and optimizing I/O.
Explain the steps to recover a database using RMAN after a critical failure.
- Restore DB Files from a valid backup
- Apply incremental backups
- Apply archived redo logs
- Open the database with RESETLOGS option to rest the redo log sequence
How do you add a datafile to a tablespace?
alter tablespace <tablespace_name> add datafile 'datafile location' size 32G;</tablespace_name>
How do you resolve a 10-log gap in your standby database in Dataguard?
1) Stop the MRP process
2) Identify Missing Archive Logs or compare SCNs:
- SELECT *FROM V$ARCHIVE_GAP;
- SELECT CURRENT_SCN FROM V$DATABASE;
3) Copy the Missing Archive Logs, this can be done manually or with RMAN
4) Register the Archive Logs:
- ALTER DATABASE REGISTER LOGFILE ‘/path’
5) Resume Redo Apply
- Restart the MRP
What are the key steps in an Oracle database upgrade?
- Backup the database.
- Gather statistics and clean up unnecessary objects.
- Run Oracle’s pre-upgrade utility.
- Install the new database software.
- Execute the upgrade scripts.
- Perform post-upgrade checks and testing.
How do you generate an explain plan for a SQL Statement?
EXPLAIN PLAN FOR <statement></statement>
Explain the concept of indexing in Oracle and what types of indexes there are.
“Indexing in Oracle is a database optimization technique that improves the speed of data retrieval operations. The main types of indexes include B-tree, bitmap, and unique indexes, each serving different purposes to enhance query performance.”
What is the benefit of using BIND VARIABLES?
Bind variables are used in queries to avoid repeatedly having to hard parse a query over and over. If you use a literal string or a specific value, chances are it’ll be parsed and then never used again, where as with a bind variable, it can be reused for multiple values limiting the amount of hard parses against the DB. They enable the efficient execution of plan caching
How do you recover from a 1000-log gap in Dataguard?
- Stop the MRP
- Find the SCN on the Standby DB
- On the primary, use RMAN to create an incremental backup from the SCN of the standby
- Shutdown the standby database
- On Primary, create a new standby controlfile
- Copy the new standby control file to the control file location and start the standby database in nomount
- Connect to RMAN on standby, catalog backup files
- Recover Database
- Start MRP
Why is it important for LGWR (Log Writer) to come before DBWR (Database Writer)?
LGWR comes before DBWR because the Recordkeeping of transactions is always more important than their processing and recovery. The repeated logs of LGWR store a record of all changes that are made in the database. This allows the recovery of transactions even in cases of power failure. The DBWR writes the changes in the data to the storage.
Where are AWR snapshots stored?
SYSAUX Table
Which process is responsible for collecting the statistics in the SGA and saving them?
MMOM - Memory Monitor
What wait event indicates Full Table Scans?
DB FILE SCATTERED READ
What wait event indicates Index Scans?
DB FILE SEQUENTIAL READ
What are you looking for in an AWR report that might indicate a bottleneck in the system?
Waits and Wait Avg(ms)
What is a wait class that might signal an issue?
Concurrency
What is authorization?
Controlling access to the data
What is Authentication?
Restricting access to legitimate users
What is auditing?
Ensuring accountability on part of the users
What is Encryption?
Safeguarding key data in the database
