STARTUP AND SHUTDOWN QUESTIONS
1.What is the difference between shutting down a non CDB and shutting down a Container
database? How does one shut down a pluggable database? How does one shut down a container
database?
2.Enumerate the four shutdown Oracle modes? What is each mode used for?
3.Which tools can be used to startup Oracle database
4.Where are the Oracle configuration parameters stored?
5.What are various startup modes? What is each mode used for?
6.What are the two common ways to start a pluggable database?
7.Which pluggable databases are always automatically started up when starting a container
database?
USER ACCOUNTS QUESTIONS
1.What is a ‘Common’ user in a container database?
2.What is a ‘Local’ user?
3.What are the three types of user privileges which can be granted to users?
4.What attributes should be specified when creating an Oracle account?
5.When a non CDB is plugged into a CDB, how does it affect the administrative accounts? How does it affect the user created accounts?it affect the user created accounts?
6.What is Privilege Analysis? Which role is required to use Privilege Analysis?
BACKUP AND RECOVERY QUESTIONS
1.What is a hot backup and cold backup? In which situation would you use each?
2.What is a incremental differential backup?
3.What are incremental cumulative backups?
4.A database backup is running very slow for the past few days such that they are creeping into business hours. The DBA is utilizing RMAN for nightly backups. How can you help speed up the backups?
5.A database is unexpectedly inaccessible to users following a disk error. The DBA determines that a data file needs to be restored from a backup. How much data would be lost if all data files and archived redo logs needs to be restored from a backup. How can one check for database corruption within RMAN tool?
6.Which command can be used to restore and recover a datafile using an RMAN session?Which command can be used to restore and recover a datafile using an RMAN session?
7.What is Oracle Flashback technology? What is Oracle Flashback technology?
8.How can one diagnose failures for individual PDBs? How does one execute repairs for individual PDBs?
PERFORMANCE MONITORING AND TUNING QUESTIONS
1.Which feature of Oracle database provides collection of performance statistics? What are the different types of statistics collected with this tool?
2.How can the performance of queries be improved?
3.What are some of the administrative tasks which help improve database performance? Can these tasks be automated
4.What is Active Session History?
5.A user is complaining of a performance problem. How would you help them resolve the problem?A user is complaining of a performance problem. How would you help them resolve the problem?
6.What is SGA?
7.How should one choose database data block size?
8.Name a few of the Oracle memory caches which affect performance?
9.What is Automatic Memory Management? Which initializations parameters can be used for Automatic Memory Management?Memory Management?
10.What is Automatic Database Diagnostics Monitor (ADDM)?
DATABASE SECURITY QUESTIONS
1.Name four authentication methods used to authenticate to an Oracle database?
2.What are the ways to restrict access to data?
3.What is package invoking with Definer’s rights? What are Invoker’s rights?
4.What is fine grained access control to data?
ORACLE UTILITIES QUESTIONS
1.Which tools can be used to recreate object definitions on a test system?Which tools can be used to recreate object definitions on a test system?
2.Name one method to improve the speed of an export operation using Oracle data pump? What Name one method to improve the speed of an export operation using Oracle data pump? What about import operation?about import operation?
3.What is SQL*Loader? What is SQL*Loader?
4.What is an external table? When could external tables be used?What is an external table? When could external tables be used?
5.What are some of the ways a flat data file be loaded into the database?What are some of the ways a flat data file be loaded into the database?
SQL QUESTIONS
1.What is a cursor?
2.What Is the difference between %rowtype and %type?
3.What are bind variables?
4.What is client side results cache?
5.What is a SQL hint?
6.Which SQL statement can be used to determine the execution plan of a SQL statement?
ANSWERS
STARTUP AND SHUTDOWN ANSWERS
1.A container database may contain one or more pluggable databases. If a CDB is shut down, all its pluggable databases will also be shut down. A non CDB database does not have any pluggable databases connected to it.To shut down a pluggable database, make the PDB as your current container and issue the shutdown command.In order to shut down the container database, issue the shutdown command with the CDB$ROOT as the current container.
2.NORMAL, TRANSACTIONAL, ABORT, IMMEDIATE
NORMAL no new connections, waits for all connected users to disconnect, may take a while to shut because of users still connected to database
IMMEDIATE use when users cannot be contacted to log off, or when functionality is irregular.
TRANSACTIONAL planned shut down, waits for active transactions to complete, users not required to log off
ABORT used when none of the other modes did not work, shutdown is immediate
3.Database can be started up using srvctl , SQL*Plus, RMAN, Enterprise Manager Cloud Control
SRVCTL Recommended in a Real Application Clusters Environment
4.SPFILE which is a binary file, or PFILE, which is a text file
5.STARTUP FORCE used to shut down an already instance before starting it back up
STARTUP RESTRICT allows only users with RESTRICTED SESSION privilege
STARTUP MOUNT only mounts the database, does not open it
STARTUP OPEN mounts and opens the specified database
STARTUP NOMOUNT starts up only the Oracle instance, no databases are mounted
STARTUP QUIET suppresses startup messages on screen
6.STARTUP PLUGGABLE DATABASE command or ‘alter pluggable database db_name open’ command
7.Pluggable databases have to be explicitly started by default. Only the PDB$SEED is started up when a CDB is started up
USER ACCOUNTS ANSWERS
1.A Common user is a database user which can connect to any existing or future pluggable A Common user is a database user which can connect to any existing or future pluggable database. Common users are created in a CDB$ROOT container. Database system accounts such SYS and SYSTEM are CDB Common users.
2.Any database user who not a Common user is a local user for a specific pluggable database.
3.System privileges, object privileges, and roles.
4.User name, password, default tablespace, default tablespace quota, temporary tablespace, User name, password, default tablespace, default tablespace quota, temporary tablespace, default profile, and grant CREATE SESSION privilege.
5.When a non CDB is plugged in to a CDB, the common system users are merged. Password of the CDB take precedence. User created accounts are merged with Common user accounts. If privileges of the account were modified in the source, they are maintained only in the new pluggable database which was created when plugging into the CDB.
6.Privilege Analysis runs within the Oracle database kernel. It helps increase the security of applications by capturing the privileges used by database users from the database itself. It eliminates the need to interview and question hundreds of users when trying to develop a least privilege policy. The role CAPTURE_ADMIN is require to use the feature. Oracle Enterprise Manager Cloud Control or DBA_PRIVILEGE_CAPTURE package can be used to capture and report on privileges.
BACKUP AND RECOVERY ANSWERS
1.A cold backup is taken when the database is closed and no user activity is occurring. Hot backups are the backups, taken while database is open and users are connected to the database. A cold backup includes the data files only as no transactional activity is being logged. When users need to be logged in while backups are occurring, a hot backup or online backup is required. The database backs up all data files and redo logs which may be required to recover the database up to a certain point in time.
2.A differential incremental backup is the backup of data which includes all blocks changed after the most recent incremental backup at level 1 or 0.
3.A cumulative incremental backup is the backup of data which includes all blocks changed after the most recent incremental backup at level 0.
4.One of the ways a backup can be sped up is by multiplexing the backups using FILESPERSET value. Increasing the MAXOPENFILES parameter of ALLOCATE CHANNEL could allow RMAN to read from more than the default value of 8 files simultaneously.
5.No data would be lost if all data files and archived redo logs were backed up per schedule. Online redo logs would be present too which can be used to restore and recover the data file up to the point in time of failure. One can check for corruption within RMAN by using the VALIDATE DATABASE command.
6.RESTORE DATAFILE and RECOVER DATAFILE commands can be used to recover a datafile.
7.Oracle Flashback technology provides a snapshot of the database at a previous point in time. This can help diagnose problems or rewind the database to a previous time without having to restore files.
8.One can validate a PDB data by using the ADVISE FAILURE command from RMAN prompt. One can execute REPAIR FAILURE command from RMAN prompt to repair a database.
PERFORMANCE MONITORING AND TUNING ANSWERS
1.The Automatic Workload Repository (AWR) provides for capturing and collection of various database performance statistics. Different types of statistics collected with AWR include object access and usage performance statistics, system and session based performance statistics, time model statistics, SQL statements and resources used, and active session history.
2.By properly normalizing or de-normalizing the database design as appropriate, using indexes, using hints in queries, keeping database statistics updated.
3.Rebuilding or reorganizing tables and indexes, updating database statistics, tuning database performance via initialization parameters, regularly diagnose and analyze performance problems? All administrative tasks can be automated either via scripts or by Oracle database diagnostics.
4.Active Session History (ASH) contains records of all sessions and users connected to the database. It can be Active Session History (ASH) contains records of all sessions and users connected to the database. It can be used to diagnose transient performance problems which are short-lived.
5.Interview the user to find out when the problem started occurring and if any changes were made after which the problem appeared. Then use Automatic Database Diagnostic monitor or ASH to determine the causes of problem at the times of the error.
6.SGA is the System Global Area, or the main memory area of Oracle instance. It comprises the Oracle Shared Pool, Large Pool, Database Buffer Cache and other instance memory structures.
7.Database block size should be selected based on the amount of data which is read in a typical I/O operation. In and OLTP system a small database block size would be appropriate whereas for a data warehousing application, a larger block size would provide the most optimal performance.
8.Database Buffer cache, redo log buffer, shared pool, large pool
9.With Automatic Memory Management, the database can automatically tune and optimize the memory allocation for the SGA and PGA. Setting the initializations parameters MEMORY_TARGET and MEMORY_MAX_TARGET enables Automatic Memory Management.
10.ADDM regularly analyzes the database performance data present in the Automatic Workload Workload Repository and recommends corrective actions. After each snapshot, the ADDM runs automatically and stores its analysis in the database. automatically and stores its analysis in the database.
DATABASE SECURITY ANSWERS
1.Database, operating system, network Database, operating system, network ––Secure Sockets Layer, Kerberos, Single Sign-On, Directory based
2.Access to sensitive data in the database can be restricted using roles, privileges, fined grained security policies using Virtual Private Database, and via code based access control
3.When a package is invoked with Definer’s rights, it executes with the privileges of the user who created the package. If a package was created with Invoker’s rights, it is executed with the privileges of the user who is invoking the package. The invoker’s rights procedure would have been created with the AUTHID clause.
4.Fine Grained Access Control allows defining policies to create a virtual private database for the user at the row and column level.
ORACLE UTILITIES ANSWERS
1.Data PumpData Pump
2.To improve the performance of an Oracle data pump job, the PARALLEL parameter can be used. It sets the maximum number of processes which can run simultaneously. The PARALLEL parameter can be used with the import operation also.
3.Oracle SQL*Loader is a utility to load data from external files into the database. It can be used to load data in a variety of formats, multiple source files simultaneously, manipulate data prior to loading, and uses conventional path, direct path, or external table load.loading, and uses conventional path, direct path, or external table load.
4.An external table resides outside the database on the operating system. Only the data definition for the object is contained within the database. External tables can be used to dump table data onto an operating system file. This is especially useful when working with very large amounts of data.
5.Using Data Pump, external tables
SQL ANSWERS
1.%rowtype represents a data type belonging to a single row in a table.The %type can be used to assign the data type of a variable to that of a particular column type in the table.
2.Bind variables allow for more efficient reuse of SQL code. When the same SQL statement is used by the application many time, with only the values of some variables changing, the statement could be coded using bind variables. For example, select name from emp where employee_id=bv_empid. This statement can be parsed once and reused many times..
3.Client side results cache provides client side caching of query results on the users machine. It is especially useful when the same function is executed many times throughout the day. Instead of fetching results over the network each time, the query results can be cached locally and thus reduce time to fetch the results.
4.SQL hints are program structures which can be used to provide directives to the query optimizer. By default, the database query optimizer chooses an access path based on its own algorithm and criteria. If fine grained control is required, the user can specify hints to explicitly choose a certain access path which may be required.
5.SQL statement EXPLAIN PLAN can be used to report the execution plan of a SQL statement.
