Backup and Recovery
1. What is the difference between restore and recover?
Restore copies files from a backup. Recover applies redo (archived and online) to bring the restored files up to date.
2. What is ARCHIVELOG mode and why use it?
In ARCHIVELOG mode full online redo logs are copied to archive files before reuse. It allows online (hot) backups and point-in-time recovery. Production databases should use it.
3. What is the difference between a hot and a cold backup?
A cold backup is taken with the database shut down. A hot backup is taken while it is open and requires ARCHIVELOG mode.
4. What is the difference between incremental level 0 and level 1?
Level 0 copies all used blocks and is the base. Level 1 copies only blocks changed since the last level 0 (cumulative) or since the last level 0 or 1 (differential).
5. What does CONTROLFILE AUTOBACKUP do?
RMAN backs up the control file and the SPFILE automatically after each backup and structural change, which makes restoring a lost control file easy.
6. What is a recovery catalog?
A separate database schema that stores RMAN metadata for many databases for longer than the control file keeps it. Without it, RMAN uses the control file.
7. How do you recover a lost non-system datafile while the database is open?
Take the datafile offline, RESTORE DATAFILE n, RECOVER DATAFILE n and bring it online. The rest of the database stays available.
8. All control files are lost. What do you do?
Start in NOMOUNT, RESTORE CONTROLFILE FROM AUTOBACKUP, mount, RESTORE and RECOVER DATABASE and open with RESETLOGS. Keep the DBID handy.
9. When is OPEN RESETLOGS needed?
After incomplete recovery, or after recovery with a restored or recreated control file. It creates a new redo stream, so take a new backup afterward.
10. How do you recover to a point in time?
Mount the database and run SET UNTIL TIME (or SCN or sequence), RESTORE DATABASE, RECOVER DATABASE, then OPEN RESETLOGS. For smaller damage prefer PDB or table recovery, or flashback.
11. What flashback features do you know?
Flashback Query (AS OF), Version Query, Flashback Table, Flashback Drop (recycle bin) and Flashback Database. The first four use undo or the recycle bin, and Flashback Database uses flashback logs.
12. Is flashback a replacement for backups?
No. Flashback cannot help when a datafile is physically lost. You still need RMAN backups.
13. How do you check that your backups are usable?
Use RESTORE DATABASE VALIDATE, BACKUP VALIDATE CHECK LOGICAL DATABASE and most importantly regular real restore tests on another server.
14. What is RPO and RTO?
RPO is the amount of data loss you can accept. RTO is the amount of downtime you can accept. They drive the backup design.
Data Pump
15. What is Data Pump and how does it differ from RMAN?
Data Pump (expdp/impdp) is a logical export and import of objects and data, written on the server. RMAN makes physical backups of datafiles. Data Pump is for moving data between databases and versions, RMAN is for backup and recovery.
16. How do you import into a different schema?
Use REMAP_SCHEMA=old:new with impdp. Combine with REMAP_TABLESPACE if tablespaces differ.
Users and Security
17. What is the difference between a system privilege and an object privilege?
A system privilege allows an action in the database, such as CREATE TABLE. An object privilege allows access to a specific object, such as SELECT on SHOP.ORDERS.
18. Why use roles?
They group privileges so you grant them to many users in one step, and change access in one place. It also makes audits easier.
19. How do you lock a user after failed logins?
Create a profile with FAILED_LOGIN_ATTEMPTS and PASSWORD_LOCK_TIME and assign it to the user.
20. What is unified auditing?
A single audit trail and policy framework, read from UNIFIED_AUDIT_TRAIL. You create policies with CREATE AUDIT POLICY and enable them with AUDIT POLICY.
21. What is TDE and what is the biggest risk?
Transparent Data Encryption encrypts data at rest using a master key in a keystore. The biggest risk is losing the keystore, because the encrypted data and backups then cannot be read.
22. What do SYSDBA and SYSOPER allow?
SYSDBA has full control including startup, shutdown, recovery and data access. SYSOPER can start up, shut down and do basic operations but cannot view user data.
Connectivity
23. A user gets ORA-12541. What do you check?
The listener is not running or not reachable. Check lsnrctl status, host and port, and firewall rules.
24. A user gets ORA-12514. What does it mean?
The listener does not know the requested service. Check the service name, whether the PDB is open and run ALTER SYSTEM REGISTER.
Conclusion
Be ready to explain backup concepts clearly, walk through two or three recovery scenarios and describe a sensible security setup with roles, profiles, auditing and encryption.

