Oracle DBA Admin- Production Readiness Assignment

Module 1 — Control Files

What is a control file, and what is its purpose in an Oracle database? (L0)
How many control files should typically be present? What’s the significance of multiplexing them? (L0)
Is the CONTROL_FILES parameter static or dynamic? (L0)
To change the CONTROL_FILES parameter, what state/mode must the database be in? (L0)
Write a query to check current control file locations and verify their status (v$controlfile, related dba_ views). (L0)
How to multiplex control files using a PFILE. (L0)
How to multiplex control files using an SPFILE. (L1)
Scenario: Shutdown the database, move a control file to a new location without updating the parameter, start the database, and observe/explain the error. (L1)
Steps to correctly relocate/rename a control file (including the parameter change) due to space constraints. (L1)
Explain the process of creating a control file using CREATE CONTROLFILE. What considerations must be kept in mind? (L1)
Can a control file be resized? If so, what are the steps? (L1)
How to check the consistency and integrity of control files. (L1)
What is the CONTROL_FILE_RECORD_KEEP_TIME parameter? (L1)
How does Oracle use control files during instance recovery vs. media recovery? (L1)
What issues can arise with control files, and how are they resolved? Why are regular backups important? (L1)
How to take a backup of the control file (to trace, and binary backup). (L0)
Scenario: If there is no control file and no backup exists, can you start the database? What are your options? (L1)
How to rename a database using the control file method. (L1)
How to rename a database using the NID utility. (L1)

Module 2 — Redo Log Files

What types of changes get recorded in redo log files? Does a SELECT, a CREATE TABLE, and a TRUNCATE each generate redo — why or why not? (L0)
What is a Log Sequence Number (LSN), and what is its significance? (L0)
How to find the current log sequence number. (L0)
How to perform a manual log switch. (L0)
What is the maximum number of redo log groups and members per group? Which parameters control these limits (MAXLOGFILES, MAXLOGMEMBERS)? (L0)
Can you add more groups/members than MAXLOGFILES/MAXLOGMEMBERS? Is this a hard limit or a soft limit? (L1)
Which hidden view can be used to check the effective MAXLOGMEMBERS limit? (L1)
Practical: Add 2 members to a redo log group — one sized 20MB, one sized 30MB. Can members within the same group have different sizes? Observe the result. (L0)
Can different redo log groups have different sizes from one another? Explain the implications. (L1)
Display redo log group information — members, size, and status of each group and member (`vlog‘,‘vlog`, `v log‘,‘vlogfile`). (L0)
What are the possible statuses of a redo log group (CURRENT, ACTIVE, INACTIVE, UNUSED)? (L0)
What are the possible statuses of a redo log member (INVALID, STALE, DELETED, NULL)? (L0)
Which redo log group cannot be dropped/removed? (Hint: check group status first.) (L0)
Scenario: You have 2 groups sized 50MB each and need to resize them to 100MB. What steps do you perform? (L1)
How to move a redo log file from one location (e.g., /u01) to another (/u02)? (L1)
Can the control file block size be set to 16KB? What’s the impact? (L1)

📌 Reference note: minimum redo log size is 4MB; block size depends on OS/disk (512B/1024B/4096B); control file block size behaves similarly.

Module 3 — Archive Log Management

How to enable/disable ARCHIVELOG mode? What are the prerequisites? (L0)
Step-by-step: put the database into ARCHIVELOG mode. (L0)
If the Fast Recovery Area (FRA) is not configured, where do archived logs go by default? (L0)
How many archive log destinations can be configured? (L0)
What are the mandatory archive log destinations? (L0)
Difference between LOG_ARCHIVE_DEST, LOG_ARCHIVE_DEST_n, and LOG_ARCHIVE_DUPLEX_DEST. (L1)
What is the use of the LOG_ARCHIVE_DEST_STATE_n parameter? (L1)
How to mark an archive destination as DEFERRED / set up an alternate destination? (L1)
What is the naming format for archived log files? Which parameter controls it — list all format elements and their meaning. (L1)
How to change the default format of archived log filenames? (L0)
Which views provide information about archive logs (`varchivedlog‘,‘varchived_log`, `v archivedlog‘,‘varchive_dest`)? (L0)
Explore v$archive_dest — what information does it expose? (L0)

Module 4 — Data Blocks, Segments, Extents & Space Management

What is a data block? Why is free space (PCTFREE) deliberately kept inside it? (L0)
What is ASSM (Automatic Segment Space Management), and how does it differ from manual/freelist-based management? (L0)
Meaning of PCTFREE, PCTUSED, FREELISTS, FREELIST GROUPS. (L0)
How to check the current values of these parameters for a segment. (L0)
Meaning of storage clause parameters: INITIAL, NEXT, PCTINCREASE, MINEXTENTS, MAXEXTENTS, MAXSIZE, OPTIMAL, AUTOEXTEND, INCREMENT. (L0)
Which data dictionary table is queried to find free extents in a Dictionary Managed Tablespace (DMT)? (L1)
What is HWM (High Water Mark)? (L0)
Practical: Create a Products table (Product ID, Name, Qty, Price), insert 10 dummy records, then find: blocks/extents/segments consumed, the datafile(s) storing it, its tablespace, and its segment name. (L0)
Practical: Bulk-load a large volume of data into an empty table and observe the HWM shift before and after. (L0)
Create an index on product_id and find its blocks/extents/segment consumed, datafile(s), tablespace, and segment name. (L0)
How to check how many Oracle data blocks are consumed by a specific table, e.g., HR.EMPLOYEES. (L0)
What is the format/structure of a ROWID — what does each component represent? (L0)
What scenarios make Locally Managed Tablespaces (LMT) more beneficial than Dictionary Managed? (L1)
How to monitor and manage space usage within an LMT — which queries/views help? (L1)
How to monitor table fragmentation. (L1)
How to check for row chaining and row migration. (L1)
What solutions exist for reducing fragmentation, chaining, and migration? (L1)
How to identify and clean up unused or fragmented space. (L1)
How to use table compression to save space. (L1)

📌 Reference reading: Oracle Docs — “Allocation of Extents” and “Allocation of Segments” (Logical Storage Structures).

Module 5 — Oracle Managed Files (OMF) & Fast Recovery Area (FRA)

What is OMF (Oracle Managed Files)? (L0)
Which parameters must be set to configure OMF? (L0)
What is the FRA (Fast Recovery Area)? (L0)
Which types of files are stored in the FRA? (L0)
Configure DB_CREATE_FILE_DEST to /u02/dt/. (L0)
What happens when the FRA destination becomes full? (L1)
Configure the DB_CREATE_ONLINE_LOG_DEST_n parameter. (L1)
Practical: Create a new database named Sep23 with OMF fully configured. (L1)
Using OMF, create a control file, a redo log file, and a data file without explicitly naming the file. (L0)

Module 6 — Tablespace Management: Temporary & Undo

A. Temporary Tablespace

What is a temporary tablespace, and what type of data does it hold? (L0)
For which operations does the database use TEMP (sorts, hash joins, global temp tables, etc.)? (L0)
How to check PGA sort_area sizing settings that influence TEMP usage. (L0)
How to check the current default temporary tablespace. (L0)
What is the command and prerequisite for changing the default temporary tablespace? (L0)
Explore the DATABASE_PROPERTIES view — what relevant properties does it store? (L0)
Write a query to check current usage/activity in the temp tablespace. (L0)
How to find which users/sessions are currently using TEMP segments. (L0)
Find the total, used, and free space of the temporary tablespace. (L0)
Find rows in V$TEMPSEG_USAGE where the username is NULL — what does this indicate? (L1)
How to explicitly assign a specific (non-shared) temp tablespace to a user. (L0)
What are temporary tablespace groups? How to list groups and their members. (L1)
Practical: Create a temp tablespace group with 2 member tablespaces. (L1)
How to change the default temp tablespace to a group instead of a single tablespace. (L1)
How to add/remove members of an existing tablespace group. (L1)
Scenario: A transaction is actively using the current default temp tablespace, and the DBA changes the default temp tablespace mid-transaction — what happens to that transaction? (L1)
Can a database exist without a temporary tablespace? (L1)
Can a temp tablespace be dropped or renamed? (L0)
Can temp files be relocated while the database is running (online)? (L1)
List the data dictionary views related to temp tablespace management. (L0)

B. Undo Tablespace
21. Write queries to check the current status and statistics of the Undo tablespace. (L0)
22. Practical: Create a new Undo tablespace, set appropriate parameters, and switch the database to use it. (L1)
23. Implement and demonstrate Oracle Flashback Query using Undo tablespace data. (L1)

Module 7 — Users, Authentication, Roles, Profiles & Privileges

A. Authentication & SYS/Admin Basics

What are the administrative privileges in Oracle (SYSDBA, SYSOPER, SYSBACKUP, SYSDG, SYSKM)? (L0)
What is the V$PWFILE_USERS view used for? (L0)
How does authentication happen for the SYS user? (L0)
Difference between logging in as sys as sysdba / password vs. plain sys / password. (L0)
Default location of the password file. (L0)
If the password file is missing, can a user still log in with admin privilege? (L1)
Is there a default password for SYS when the database is created? (L0)
How to change the SYS password (ALTER USER, and via orapwd) — change it to 'clover' using orapwd. (L0)
Can you log in as SYS without a password file? How (OS authentication)? (L1)
Where does the database store credentials for normal users (e.g., HR)? (L0)

B. User Creation & Quota Management
11. What should be checked/decided before creating a new database user? (L0)
12. Create user ACD, assigning default tablespace and quota in a single CREATE USER statement. (L0)
13. Create a user using only OS authentication. (L1)
14. What happens if a user is assigned a quota of zero on a tablespace? (L0)
15. Scenario: Create user Ganesh with a 2MB quota on the default tablespace. Create a table and insert 50,000 rows. Now alter the quota to zero — explain the impact on existing data vs. new inserts. (L1)
16. Advantages and disadvantages of granting UNLIMITED TABLESPACE. (L1)
17. Can a user be assigned a quota on a temporary tablespace? If yes, how; if no, why not? (L1)

C. Privileges
18. What does GRANT ALL actually grant? (L0)
19. Difference between system privileges and object privileges. (L0)
20. Query to find all system privileges assigned to a user. (L0)
21. Query to find all object privileges assigned to a user. (L0)

D. Roles
22. What are the CONNECT and RESOURCE roles, what privileges do they bundle, and why are they considered legacy for application design? (L0)
23. How to check which privileges are stored inside the DBA role. (L0)
24. Where to view all existing roles in the database. (L0)
25. Check which users currently have the DBA role granted. (L0)
26. How to create a custom role. (L0)
27. Practical (RBAC): Create a read_only_role that permits only SELECT on specified tables; assign it to a test user; verify the user can read but not perform DML. (L1)

E. Profiles & Password Policy
28. What is a user profile? What does it control? Inspect the contents of a profile. (L0)
29. How to assign a profile to a user. (L0)
30. Practical: Create profile limited_profile with CPU time, session, and idle-time limits; assign it to a test user; attempt to exceed the limits and confirm enforcement. (L1)
31. If a user account gets locked, what steps should the DBA take? (L0)
32. Scenario: User Rohan is logged in on two terminals — from the DBA account, identify and terminate one specific session. (L0)
33. Set a password policy enforcing complexity rules (minimum length, special characters, etc.). (L0)
34. Reproduce this error: Oracle refuses to set a password because it fails verification/complexity rules. (L1)
35. Reproduce this error: Oracle refuses a password change because the new password matches the old one too closely. (L1)
36. What is a password verify function, and how does it attach to a profile? (L0)
37. Implement a custom password verification function and attach it to a profile. (L1)
38. Explore $ORACLE_HOME/rdbms/admin/utlpwdmg.sql and catpvf.sql. List Oracle’s built-in verify functions: ORA12C_VERIFY_FUNCTION, ORA12C_STRONG_VERIFY_FUNCTION, VERIFY_FUNCTION_11G. (L1)

F. End-to-End Capstone
39. Full workflow: Create assignment_user with a password → grant CONNECT → create role app_user_role → grant SELECT on a sample table to the role → assign the role to the user → additionally grant CREATE TABLE directly → apply a password-policy profile with complexity rules → simulate failed logins and confirm lockout at the configured threshold → unlock the account → finally drop the user. (L1)

Module 8 — Indexing

A. Optimizer Fundamentals

What is the Oracle Query Optimizer? (L0)
What types of optimizers exist (RBO vs. CBO)? (L0)
How does Cost-Based Optimization work internally? (L0)
How to check the current default optimizer mode. (L0)
What factors does the optimizer weigh when estimating the cost of execution plans? (L1)
How does the optimizer decide between using an index vs. a full table scan? (L1)

B. Index Basics
7. What is an index, and how does it improve performance? (L0)
8. Guidelines for deciding when to create an index. (L0)
9. Syntax for creating a basic index. (L0)
10. Practical: Demonstrate a Full Table Scan vs. an Index Scan for the same query and confirm via execution plan. (L0)
11. Explore DBA_INDEXES and DBA_SEGMENTS — what information do they provide? (L0)

C. Index Types
12. What is a B-tree index? Explain its internal structure (root/branch/leaf) and how it speeds up retrieval. (L0)
13. Difference between a unique and a non-unique index — when to use each. (L0)
14. Practical: Create both a unique and a non-unique index and compare behavior. (L0)
15. What is a bitmap index? Which column types suit it, and why? (L1)
16. What is a function-based index? Give a scenario where it’s useful. (L1)
17. What is a reverse key index? Why use it — advantages and disadvantages? (L1)
18. What are Index-Organized Tables (IOTs), and what advantages do they offer over table+index? (L1)
19. Practical: Create examples of a function-based index, an IOT, a bitmap index, and a bitmap join index. (L1)

D. Statistics & Query Optimization
20. What is the DBMS_STATS package used for? (L0)
21. How to gather table and index statistics using DBMS_STATS. (L0)
22. Describe the process of analyzing a table and gathering fresh statistics — how does this aid optimization? (L1)
23. Explain the types of index scans (unique, range, full, fast full, skip) — run sample SQL for each and confirm via DBMS_XPLAN. (L1)

E. Index Health, Fragmentation & Maintenance
24. What is index fragmentation, and what commonly causes it? (L0)
25. Difference between logical and physical fragmentation in Oracle. (L1)
26. Which view is used to check index fragmentation (INDEX_STATS)? (L0)
27. In INDEX_STATS, what does the HEIGHT/BLEVEL column signify, and how do you check it? (L1)
28. If the B-level (height) of an index increases, what is the functional/performance impact? (L1)
29. What solutions exist for fixing index fragmentation? (L0)
30. When does an index become UNUSABLE? (L0)
31. What is Index Unusability vs. Index Invisibility — how do they differ? (L0)
32. Practical: Demonstrate making an index UNUSABLE and separately INVISIBLE — observe optimizer/query behavior in each case. (L1)
33. How to rebuild an index? What happens internally? (L1)
34. Difference between an ONLINE index rebuild and a normal (offline) rebuild. (L1)
35. What techniques reduce index rebuild time (parallel, nologging, online)? (L1)

Module 9 — Networking: Listener, TNS, SQL*Net

What is TNS (Transparent Network Substrate)? (L0)
What is a listener? Difference between a local listener and a remote listener. (L0)
What is SQL*Net? What is sqlnet.ora, and what parameters does it commonly contain? (L0)
Difference between tnsping and OS ping — what does each actually verify? (L0)
What is telnet, and how can it test basic connectivity to the listener port? (L0)
How to check how many listeners are currently running on the server. (L0)
What are the possible statuses of a listener? (L0)
If a listener status shows UNKNOWN, how do you bring it back to a healthy state? (L1)
How to determine whether a listener is using static or dynamic registration. (L1)
Difference between static and dynamic service registration. (L1)
Is a tnsnames.ora entry required on the server side for the local listener to function? (L1)
Scenario: The listener is up and tnsping succeeds, but the client still gets a connection-timeout error — how do you troubleshoot? (L1)
How to change the default listener port. (L0)
Is it possible to configure one listener for multiple databases? How? (L1)
Configure multiple listeners for a single database. (L1)
Create a new TNS entry and validate client connectivity using tnsping and sqlplus. (L0)