Oracle Database Administration and Utilities Essentials

Oracle Database Concepts and Synonyms

• True: A synonym is a local or public representation of a schema object.
• True: With the PUBLIC option, a public synonym is created if the user has GRANT privileges.
• False: Oracle 10g auditing includes standard, value-based, and Fine-Grained Auditing (FGA).
• True: Value capture auditing records changes and insertions using a built-in SQL trigger.
• False: FGA is based on policies and does not require the use of triggers, unlike value-based auditing.
• True: The PCTFREE parameter indicates the minimum percentage of a block reserved for row updates.
• True: The INITRANS parameter specifies the initial transaction slots in a block (Index: 2, Data: 1).
• False: A database schema is a collection of objects owned by a single user, including tables, views, stored procedures, functions, packages, and sequences.
• True: The Oracle Export utility supports moving data and database rebuilding.
• True: The Import utility allows importing data via the ROWS parameter or just table structures.
• False: To multiplex a control file using an SPFILE, the instance must be shut down to physically copy the file, as it alters dynamic parameters.
• True: SQL*Loader can load data from external files into one or more tables.
• True: The RMAN utility performs backup and recovery of the database, datafiles, tablespaces, and SPFILEs.
• False: For physical redo file recovery, the ALTER DATABASE OPEN RESETLOGS command is typically used after incomplete recovery.

Database Utility Operations

SQL*Loader and Export Procedures

1. SQL*Loader Methods: The primary loading methods are INSERT, APPEND, and REPLACE.
2. Exporting HR Schema: To export only the HR schema tables, run: EXP SYSTEM/DUOCADMIN FILE=HR.DMP LOG=HR.LOG OWNER=HR.
3. Import Parameters: Key parameters include USER/PWD, FILE, LOG, FULL, FROMUSER, and TOUSER.
4. RMAN Capabilities: RMAN enables database support and does support the SPFILE.
5. Synonym Access: To grant access to HR.COUNTRIES, the HR user must execute: GRANT SELECT ON COUNTRIES TO PUBLIC.
6. Multiplexing Control Files: In Database XE, run ALTER SYSTEM SET control_files with SCOPE=SPFILE, then SHUTDOWN IMMEDIATE and copy the physical files to the specific location.
7. Database Recovery: The command RECOVER DATABASE UNTIL CANCEL allows for the physical recovery of lost redo logs.

Data Loading and Auditing Configuration

1. Export Command: To back up a dataset without a direct connection, use the EXPORT command-line utility.
2. RMAN Backups: Use RMAN to back up specific datafiles.
3. Importing Data: To perform a data load into a table using the ROWS=Y parameter, use the IMPORT utility.
4. SQL*Loader Control: To load data using a control file (file_name.CTL), use SQL*Loader.
5. Standard Auditing: Use ALTER SYSTEM SET AUDIT_TRAIL to activate standard auditing.
6. Fine-Grained Auditing: The DBA must create policies to audit tables or views using FGA.