Brainscape's Knowledge GenomeTM


Top Flashcards (Certified)
All Flashcards

SQL Server > Oracle DBA > Flashcards

Oracle DBA Flashcards

1
Q

What is PGA or Private Global Area?

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

What is SGA?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

What do you understand by In-Memory sort?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

Can you explain how an update statement is executed in the database?

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

Explain about SCN and checkpoint number

A
  • 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.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

Which groups are assigned to the oracle user for installation and administration purposes?

A

oinstall and dba

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

Why run orainstRoot.sh and root.sh scripts at the end of installation?

A

orainstRoot.sh will change the permissions for oraInventory and

root.sh will create the oratab file.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

Explain Oracle installation pre-requisite steps

A

Create oinstall& dba groups, modify kernel parameters, check disk space for installation, create oracle user and provide permissions on installation location to oracle user

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

Why wouldn’t you be able to use the SQLPLUS utility?

A

Environment variables aren’t set (ORACLE_HOME, PATH) or .bash_profile is not executed immediately after making changes to it.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

How can I check environment variables are set properly?

A

Using env | grep ORA.

Using echo command like echo $ORACLE_HOME.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

How would you determine what databases are running on an instance?

A

ps -ef|grep pmon

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

Can we change the database block size after creation?

A

No

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

You’ve been tasked with decommissioning a database, what are your steps?

A
  • 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 well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

How do I determine who is connected to the DB?

A

V$SESSION

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

How do I terminate a user session?

A

alter system kill session ‘SID, Serial#’;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

What is the difference between a pfile and an spfile?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

If I only have an spfile how do I create a pfile?

A

create pfile from spfile;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

What are the stages of starting a database?

A

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 well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

How do you resize redo log files?

A

You have to create a new group and then drop the old one.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

What is OMF?

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

What is a trace file and how does one make one?

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

What happens when you run a SQL Statement in Oracle?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

What type of files make up a Database?

A

Redo Log Files, Parameter Files, Control Files, Data Files, Password Files

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

What are the different types of Oracle Database Shutdown Modes?

A

Normal, Immediate, Transactional, Abort

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
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).
26
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
27
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;
28
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.
29
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
30
How can we identify the resources for which the sessions are waiting?
v$session_waits and v$system_waits
31
How can we monitor the space allocations in a database?
OEM or DBA_FREE_SPACE, DBA_SEGMENTS, DBA_DATA_FILES
32
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
33
What is SCN?
System Change Number – This is a value added whenever a deadlock occurs or database changes are made.
34
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
35
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
36
Which table holds general information on the database?
V$DATABASE
37
Where can you get info on SQL Queries that are taking longer than 6 seconds?
V$SESSION_LONGOPS
38
Where can you find info on what queries are waiting for?
V$SESSION_WAIT
39
Where can you find info on what the database has been waiting on since the last start-up?
V$SYSTEM_WAIT_CLASS
40
How do you validate backups?
RMAN> VALIDATE DATABASE;
41
How do you validate the backup of a PDB?
RMAN> VALIDATE PLUGGABLE DATABASE ;
42
How do you restart a service in RHEL?
systemctl restart
43
How do you query what is actively going on inside the database?
V$PROCESSES
44
How do you determine what is blocking a certain session?
V$SESSION_BLOCKERS
45
What are the 3 modes of Dataguard?
- Maximum Availability - Maximum Protection - Maximum Performance
46
What is the Linux command to check OS Performance?
vmstat
47
What is the Linux command that shows the current usage of memory?
free
48
What is the Linux command that shows OS I/O stats
iostat
49
How do you monitor the CPU, memory, disk, and network information in Linux?
sar
50
Which sar flag shows basic CPU information?
sar -u
51
Which sar flag shows Unused Memory?
sar -r
52
Which sar flag shows Average Disk I/O?
sar -b
53
Which Linux command shows the most intensive processes in the OS?
top
54
How do you shutdown and reboot the server in Linux?
init 6 reboots and init 0 shutsdown
55
What is the purpose of the Oracle Listener?
Registers Oracle DBs and then directs remote connections to the proper DB
56
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.
57
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.
58
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
59
How do you add a datafile to a tablespace?
alter tablespace add datafile 'datafile location' size 32G;
60
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
61
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.
62
How do you generate an explain plan for a SQL Statement?
EXPLAIN PLAN FOR
63
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."
64
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
65
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
66
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.
67
Where are AWR snapshots stored?
SYSAUX Table
68
Which process is responsible for collecting the statistics in the SGA and saving them?
MMOM - Memory Monitor
69
What wait event indicates Full Table Scans?
DB FILE SCATTERED READ
70
What wait event indicates Index Scans?
DB FILE SEQUENTIAL READ
71
What are you looking for in an AWR report that might indicate a bottleneck in the system?
Waits and Wait Avg(ms)
72
What is a wait class that might signal an issue?
Concurrency
73
What is authorization?
Controlling access to the data
74
What is Authentication?
Restricting access to legitimate users
75
What is auditing?
Ensuring accountability on part of the users
76
What is Encryption?
Safeguarding key data in the database

SQL Server (2 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2025 Bold Learning Solutions. Terms and Conditions