Posted on by uCertify

OCA 1Z0-042 Short Notes: Exam passing tips

Oracle architecture

  • The size of the shared pool can be changed dynamically. The data dictionary cache of the shared pool holds the most recently accessed data dictionary information. The shared pool consists of a library cache and data dictionary cache.
  • Control file, redo log file, and data file are part of an Oracle database.
  • An SGA and Oracle background processes constitute an Oracle instance. An SGA is ended when an Oracle instance is shut down. Multiple users can share data in an SGA.
  • While making modifications in SPFILE, the instance should be in open and running state, whereas in the case of PFILE, the instance will have to be first shut down and restarted before making any modifications in it.
  • V is a dynamic performance view that is used to display the names and the location of all the control files.
  • Following is the correct sequence of the processes that occur when a database is opened:
    1. An initialization parameter file is read.

      2. Pass Oracle OCP Certification DBA 10g Certification in first attempt.

    2. An SGA is allocated.
    3. Background processes are started.
    4. A control file is read.

    5. Data files and online redo log files are opened.

Installation of Oracle Database Software

  • Memory – 512MB, Temp space – 400MB of free space in the /tmp directory applicable for the Unix operating system, Swap space – 1GB or twice of the amount of RAM, Free disk space – 1.5GB are the minimum hardware requirements to install Oracle 10g.
  • Oracle Management Agent (OMA) is a centralized administrative tool that is capable to administer a database server and an application server.
  • The password file is used to authenticate the administrative tasks from remote locations.
  • While uninstalling the Oracle software from , the built-up initialization files are not deleted.
  • The location (/u01/app/oracle) will be considered as the base of Optimal Flexible Architecture (OFA).
  • Following are the steps that should be performed to install Oracle 10g through OUI:
    1. Mount the CD and start the OUI.
    2. Perform pre-installation checks.
    3. Respond to server specific prompts for file locations, names, and so on.
    4. Select the products you want.
    5. Copy the files from the install media to .
    6. Compile the Oracle binaries.
    7. Perform post-install operations using configuration assistant.
  • The Installation Type list box is used to decide the type of installation.
  • The upgrade support feature of Oracle 10g supports the direct upgrade of database to Oracle 10g from only the following releases:
    1. Oracle 8 Release 8.0.6
    2. Oracle 8i Release 8.1.7
    3. Oracle 9i Release 1-9.0.1
    4. Oracle 9i Release 2-9.2.0
  • The environment variables configured during Oracle 10g installation, ORACLE_BASE, is set to /u01/app/oracle. The location is considered as the base of Optimal Flexible Architecture.
  • The DISPLAY and PATH are the environment variables that are needed to be set to install Oracle 10g on Unix based computers.
  • The command runInstaller -ignoreSysPrereqs is used to invoke the OUI and use of this command skips the system check step while installing Oracle 10g on a computer having Linux as the operating system.

Creating and controlling an Oracle Database

  • A database template that is created by using both the structure and the data of a database contains the data files, tablespaces, initialization parameters, and user-defined schemas and their data.
  • The DBCA is used to clone a database. One should first use DBCA to create a template from the existing database to contain the schema of the database and then the same template should be used to create the database at the new location.
  • DBCA is used to delete a database.
  • Seed templates provide an advantage of making a copy of the data files and redo logs included in the definition files.

Manage the Oracle Instance

  • The LGWR, SMON, and DBWn background processes are started by default when an Oracle instance is started.
  • Database buffer cache is a part of the SGA allocated to an Oracle instance and multiple Oracle instances can access a single database.
  • The DBMS_JOB package is used to schedule and manage the jobs in the job queue.
  • The URL format to access the Enterprise Manager Database Control from a remote location is either http://host name: http port number/em or http://IP address: http port number/em.
  • High DML activity is the tuning information that the performance page of the EM Database Control does not provide.
  • The BACKGROUND_DUMP_DEST initialization parameter is used to specify the location of an alert log file.
  • The cause of “”ORA – 10635: invalid segment or table space type”” error is that the segments are not in auto segment space managed tablespace and cannot be made to shrink.
  • Clicking the preferences icon and then modifying the history size can change the number of the queries stored as history.
  • STARTUP OPEN and STARTUP are the commands that are used to open a database.
  • Starting the database instance with server parameter file will increase the database buffer cache.
  • The SHUTDOWN ABORT command will not perform a clean shutdown.
  • The BACKGROUND_DUMP_DEST initialization parameter specifies the location of the alert log file.
  • The V dynamic performance view contains two columns named STATUS and NAME.
  • V$ views helps in performance tuning.
  • V is a dynamic performance view that provides information for the initialization parameters that are currently in effect for a particular session.

Database storage

  • The minimum units of I/O in a database are Oracle block, page, and data block.
  • The hierarchical sequence of logical storage structure of a database in ascending order is Data block < Extent < Segment < Data file < Tablespace
  • When an UNDO TABLESPACE clause is included in the CREATE DATABASE statement, and after the initialization parameter UNDO MANAGEMENT is set to AUTO, then the statement when executed will lead to the creation of an undo tablespace at database creation time.
  • The steps to rename a datafile of a tablespace is given below:
    1. The tablespace that contains the data file is taken offline.

    2. The data file is renamed by using the operating system command.
    3. The ALTER TABLESPACE……RENAME DATAFILE…… statement is then executed.
  • When the warning threshold is set at 80% in the EM Database Control, the user always gets an alert message as soon as the used space in that tablespace reaches 80% of the allocated space for that tablespace.
  • You need to switch the tablespace to offline mode, for performing some maintenance operations, such as recovering the tablespace or moving the data files to a new location. After performing the maintenance operations the tablespace is brought online again.
  • ASM defines disk groups for file management and therefore relieves the burden on a disk.

User administration and security

  • The CREATE USER statement is used to create a database-authenticated user account.
  • The ALTER USER statement is used to alter an account.
  • Privileges are granted to user accounts by using the GRANT statement.
  • The CREATE USER statement is used to create a user account. The (IDENTIFIED BY password) clause specifies a password for the account and specifies that the account is a database-authenticated user account.
  • External authentication mode of authentication will you use to create an Oracle user account that will be authenticated by the operating system.
  • Any role can be enabled by executing the SET ROLE RoleName; statement.
  • To drop a role, a user must possess the DROP ANY ROLE system privilege, or he must be granted the role with the ADMIN OPTION.
  • The SESSION_ROLES data dictionary view displays the names of roles that are currently enabled for the current user.
  • The SET ROLE statement is used to enable and disable roles for the current session.
  • The GRANT plustrace TO public; statement is used to grant privileges to all current and future database users.
  • The ALTER SYSTEM SET RESOURCE_LIMIT = TRUE; statement is used to dynamically set the RESOURCE_LIMIT initialization parameter to TRUE. This does not require the database to be restarted for enforcement to take effect.
  • Revoking the CREATE SESSION privilege from a user account will disable the user to connect to a database. It can also be accomplished by locking the user’s account. In both the cases, the objects stored in user’s schema will remain intact and accessible to other database users possessing proper privileges.
  • The SYSDBA privilege is appropriate in order to grant a user the privilege that allows him to create a database.
  • REMOTE_OS_AUTHENT = TRUE setting implies that the operating system authentication is enabled for a remote client.
  • Any system and role privileges, such as ALTER SYSTEM, CREATE ROLE, ALTER ANY ROLE, and DROP ROLE, etc., require the WITH ADMIN OPTION clause to grant the privileges appropriately.
  • A profile assigned to a user takes effect only after the user starts a new session. Initially, all resources in the DEFAULT profile are set to UNLIMITED. A profile assigned to a user can ensure that the user changes his password after a certain number of days. A profile assigned to a user can ensure that the user is automatically logged off if he remains idle for a certain period of time.
  • The default profile is the profile that is used by the database system when any profile is dropped or made inactive. The default profile can even be assigned manually if the profile is active.

Managing schema objects

  • Tables and views in the same schema are in the same namespace.
  • To add a descriptive comment to a table for a better description of the contents or usage of database objects, the COMMENT ON statement is used. The comment to be included should be enclosed in single quotes.
  • A primary key is an attribute that uniquely identifies other attributes of a given entity.
  • TRUNCATE TABLE, DROP TABLE, and DELETE are all valid SQL commands to drop a table.
  • The bitmap index, index type is preferably included in the data warehousing application.
  • A password associated with the database link is considered too sensitive for it to be permitted access. Therefore, it is stored in an access-restricted base table named LINK$.
  • To create a sequence, the CREATE SEQUENCE statement is used along with the name of the sequence.
  • DBA_TRIGGERS and USER_TRIGGERS are the data dictionary views that are used to examine the status of all the triggers in a particular database.

Managing data concurrency and consistency

  • The WHERE clause is optional in the DELETE statement.
  • Each time a new row is inserted in a table, Oracle inserts a leaf level index row for every row in the table for every non-clustered index. Similar is the case with the delete operation of a row in a table.
  • Setting the value of the PLSQL_OPTIMIZE_LEVEL parameter to 2 will degrade the compiler performance, as increment in the value of the PLSQL_OPTIMIZE_LEVEL parameter will lead to more efforts that the compiler has to put in to optimize the PL/SQL library units.
  • If the parameter PLSQL_CODE_TYPE has been set to NATIVE on a database Server then the compiled PL/SQL code will be stored in the native machine code.
  • Triggers can query other tables and can include complex SQL statements.
  • ANALYZE and DDL are the two triggering events that are fired when the database either gathers/deletes statistics or validates the structure of an object in a table.
  • When a table is being modified by a Data Manipulation Language (DML) statement, Oracle places a table level DML lock on the whole table.
  • Locking a row explicitly means that a row of a table is locked by a user at the beginning of the transaction.
  • ORA-00060: Deadlock detected while waiting for resource is an error message generated by Oracle when it encounters a deadlock situation.
  • If any user leaves his transaction without committing on any table, this can led to a table lock.

Managing undo data

  • Read consistency is one of the reasons indicating why the modification done by a user is not reflected to another database user.
  • An ORA-01650 message is returned when a query is fired against an undo tablespace and there is not enough undo space to handle all active transactions.
  • ALTER SYSTEM
    SET UNDO_MANAGEMENT = MANUAL;
    and
    ALTER SYSTEM
    SET UNDO_MANAGEMENT = MANUAL SCOPE = MEMORY;
    are used to change the UNDO_MANAGEMENT initialization parameter mode to MANUAL.
  • Set the UNDO_MANAGEMENT initialization parameter to AUTO and set the UNDO_MANAGEMENT initialization parameter to the name of an undo tablespace to create a database when the automatic undo management feature is enabled.
  • The size of an undo tablespace is required to be increased manually.
  • “”ORA-30044: ‘Retention’ can only be specified for undo tablespace”” error is displayed when RETENTION for a tablespace that is not UNDO cannot be set.
  • The size of the undo tablespace is not fixed.
  • V is a dynamic performance view that is used to retrieve all the online undo/rollback segments.
  • When the UNDO_MANAGEMENT parameter in the initialization parameter file is set to AUTO, it will automatically create an undo tablespace named SYS_UNDOTBS in addition to the database and other tablespaces.
  • If an undo tablespace is not sized appropriately, then issuance of the SELECT statement on a table can lead to the “”Snapshot too old”” error.
  • Thresholds are enabled by default. Therefore, the Disable Threshold label is used to modify the critical and the warning thresholds.
  • The UNDO_RETENTION parameter specifies the time period in seconds for which a system retains undo data for committed transactions.

Implementation of database security

  • According to the principle of least privilege, users are given the minimal privileges necessary to perform their respective jobs.
  • ALTER PROFILE is the SQL statement that is used to disconnect the session.
  • The AUDIT TABLE BY USER WHENEVER NOT SUCCESSFUL; statement is used to audit database activities, which includes searching those records that could not be audited by a user.
  • The DBA_FGA_AUDIT_TRAIL view is responsible for displaying all audit records of fine-grained auditing, and DBA_AUDIT_TRAIL is responsible for displaying all audit trail entries.
  • The location of audit_trail records is dependent on the value of an initialization parameter known as AUDIT_TRAIL.
  • DBMS_FGA is a package that provides fine-grained security functions. It is used to configure and manage fine-grained auditing (FGA).

Configuration of Oracle Network Environment

  • The sqlnet.ora and tnsnames.ora are the network configuration files that will be used to resolve the “”ORA-12154: TNS: could not resolve service name”” error.
  • Single-tier architecture is commonly associated with mainframe-type applications and the terminals in single-tier architecture are non-graphical and character based.
  • Oracle net establishes the connection between the Oracle server and client and it is also capable of communicating with non-Oracle data sources.
  • The listener.ora file typically resides in /network/admin on UNIX platforms and ORACLE_HOME
    etwork\admin on Windows NT.
  • The Connect Time failover is a process of making a connection repeatedly until it is done.
  • The STATUS L1 command is used to provide status information about a listener.
  • The listener.ora file is used to control all the listeners present on a server machine.
  • The START command is typed at the lsnrctl program prompt to start a specified listener.
  • Oracle shared servers have a scalability enhancement option; whereas Oracle dedicated servers have a performance enhancement option.
  • When a listener to your database is created and Oracle Net Manager is used to enable tracing then information about every Oracle Net connection is available as additional information.

Proactive database maintenance

  • The ANALYZE command with the COMPUTE STATISTICS keyword instructs Oracle to analyze the entire table rather than analyzing a part of a table.
  • The DBMS_WORKLOAD_REPOSITORY.modify snapshot settings procedure is used to change the AWR collection interval.
  • Setting a baseline metric can help to get an appropriate message (notification) whenever the performance of the production database falls by certain set metric.
    • Pass Oracle OCP Certification DBA 10g Certification in first attempt.

  • The Enterprise Manager and the DBMS_WORKLOAD_REPOSITORY package is used to view the statistics collected and stored in an AWR snapshot.
  • The DBA_ADVISOR_RATIONALE data dictionary view provides the rationale for each ADDM recommendation.
  • Comprehensive is the most resource intensive depth level when all the ADDM diagnostic advisors submit their analysis tasks to AWR in the form of a job.
  • To configure the ADDM alerts so that they are sent via e-mail, you have to click the Setup link present at the top of the EM database control window.
  • The ADDM analysis period can go back in time and be reviewed by analyzing the previous performance spikes.
  • The alert information is always displayed in the EM Database Control main window.
  • If the database performance is degrading due to frequent checkpointing then performing the changes advised by MTTR advisor can help.
  • STATISTICS_LEVEL = BASIC will disable the collection of important statistics that are required by Oracle database features and functionalities.
  • The performance tab is used to drill down into the details of any performance metrics.

Performance monitoring

  • Oracle Enterprise Manager Repository and Oracle Enterprise Manager Database Control are the two additional components that must be configured in a Web-enabled grid control interface to monitor a database server.
  • The findings in the ADDM summary window are always displayed in descending order according to their impact on performance.
  • DBA_OBJECTS and DBA_INDEXES data dictionary views are used to access the status of various objects of the database.
  • The SQL Access Advisor and the SQL Tuning Advisor are used to determine the appropriate indexes that are responsible for performance degradation.
  • The computer-generated names are the combination of numbers ranging from 1 to 13 and lower case letters.
  • SQL Access Advisor is used to find out the reason for the performance degradation of SQL statements.
  • When you have reached the SQL Access Advisor: Review window and have clicked the Submit button, you are navigated to the Advisor Central window. The Advisor Central window now contain the links to the result of the analysis at the bottom of the window.
  • The SGA_TARGET parameter is used to perform automatic memory allocation.
  • When a database user clicks on the Advice tab located next to the Total SGA Size (MB) in the Memory Advisor main window, the Memory Advisor will formulate tuning recommendations for the Shared Pool, Buffer Cache, Large Pool, and Java Pool (memory structures).

Implementing database backup and recovery

  • To overcome the statement failures, appropriate privileges on the table and views should be provided. The issue can also be resolved by adding more space to the tablespace or by increasing the users quota on the tablespace.
  • If a database fails due to sudden power outage then one should run the STARTUP command, and the instance recovery will be performed automatically by Oracle.
  • An instance failure is a termination of an Oracle instance due to a hardware problem (e.g., power outage) or software problem (e.g., an operating system crash) that prevents the Oracle instance from continuing its work.
  • When the database is in open state and a media failure occurs, resulting in the loss of all the control files, the database cannot continue to operate anymore, as the instance of the database has been aborted.
  • An Oracle database must contain at least two redo log files, as it contains the records of all changes made to a database. Therefore, Redo log files ensure that committed data are never lost even if the data are not saved in data files.
  • Checkpoint process starts when a checkpoint occurs and the process updates the headers of data files and control files.
  • In the Redo Log Groups maintenance window, the size of individual Redo Log members is not required to be specified. The size cannot differ, as all the group members should be of the same size.
  • When an UPDATE statement is issued for the first time in a database after the instance is started, then the archiver process writes the modified data to the archived redo log files, and the control files are updated to reflect the most recent checkpoints.
  • To recover the datafiles that corrupted due to media failure, restore the entire database from the most recent backup, and then start the instance and open the database.
  • In the SHUTDOWN phase, all the background processes are inactive.
  • The ORA-01157 and ORA-01110 error messages are displayed when a datafile is missing or corrupted.
  • The FAST_START_MTTR_TARGET parameter specifies the time in seconds, to recover a single instance from instance failure.
  • Decreasing the size of the redo log files and configuring mean time to recover (MTTR) to a lower value can reduce the recovery time.
  • When the instance gets aborted and the STARTUP command is issued in SQL*Plus to bring the instance up, the system monitor (SMON) performs crash recovery.
  • %d is a predefined substitution variable that corresponds to the database id.
  • The steps that should be followed to put a database in ARCHIVELOG mode are as follows:
    1. Shut down the database.

    2. Set up all the required initialization parameters.
    3. Start up the database in MOUNT mode.
    4. Enable ARCHIVELOG mode by using the command ALTER DATABASE ARCHIVELOG.
    5. Open the database by using the command ALTER DATABASE OPEN.
  • The correct order of steps to disable ARCHIVELOG mode are as follows:
    1. Shut down the database.

    2. Start up and mount the database.
    3. Disable Archive mode by using the command ALTER DATABASE NOARCHIVE.
    4. Open the database by using the command ALTER DATABASE OPEN.
  • Multiplexing the online redo log files is not included while configuring a database to archive redo log files.
  • If a database is in ARCHIVELOG mode and the datafiles belonging to the SYSTEM tablespace have become corrupted then the database can be recovered until the last commit.
  • The maximum number of archive log destinations is ten.
  • The consistent backup is performed while working on a database where downtime can be tolerated and the database is operating in NOARCHIVELOG mode.
  • If the database operates in NOARCHIVELOG mode, you can perform consistent backup.
  • Online backups are called as inconsistent backups, as they make copies of datafiles when they are not in synchronization with the control files.
  • Set the parameter in the text box against the Parallelism label is used to reduce the overall backup time while performing a database backup.
  • RMAN commands are used to obtain backup in case of heavy transactions recorded in a database through DML to avoid the risk of data loss in the event of a database crash or media failure.
  • RMAN can be used to take an incremental backup for tablespaces and datafiles.
  • An incremental backup of a database contains all used data blocks.
  • Automatic Shared Memory management uses MMAN background process to co-ordinate the size of memory components.
  • The Catalog Additional Files is the tab that you must choose to add the image copy backups made outside RMAN to the RMAN catalog.
  • The backup scheduler generates a RMAN script.
  • Backup a control file to trace.
  • A backup set stores all datafiles in a single file, and an image copy stores one datafile per image copy.
  • After the execution of the ALTER DATABASE BACKUP CONTROLFILE TO TRACE; command, it generates a new SQL script that can be used to re-create the control file in the trace file.
  • The DB_RECOVERY_FILE_DEST parameter gives all the details concerning the Flash Recovery Area.

Performing database recovery

  • The control file is the most crucial file in a database, as a database cannot be mounted without it. Therefore, there must be multiple copies of control file in a database. The total number of control files can be eight. They are created at the time of database creation, not after it has been created.
  • Shut down the database, copy the control file to more locations by using an operating system command, effect a change in the initialization parameter file to include the new control file name(s) in the parameter CONTROL_FILES, and then start up an instance to copy the control file to keep a backup for recovery purposes.
  • An Oracle server automatically updates control files.
  • The following steps are taken to add more control file copies:
    1. Shut down the database.

    2. Copy the control file to more locations by using an operating system command.
    3. Change the initialization parameter file to include the new control file name(s) in the parameter CONTROL_FILES.
    4. Start up the instance.
  • The steps to add one or more control files are given below:
    1. Shut down the instance.

    2. Copy the control file to the third location.
    3. Modify the CONTROL_FILES parameter in the init.ora file.

    4. Open the database.
  • A checkpoint (CKPT) is a background process that writes modified data from the buffer cache to disk and updates control files and the header of each of the data files.
  • The ALTER DATABASE CLEAR LOGFILE GROUP 3; statement will execute successfully in both the cases when group 3 is archived and unarchived.
  • The status “”stale”” of the V dynamic performance view specifies that the redo log file member is new and has never been used.
  • If a database is functional but one of the disks containing a mirrored redo log member has bad sectors in it (corrupted) then the database instance will not have any effect.
  • 4MB is the minimum size of a redo log file when Oracle 10g database is created.
  • UNDO and SYSTEM are the tablespaces contain data files, for which only closed recovery is possible.
  • The loss of a single or more datafiles of a tablespace does not stop other users from performing transactions in any other tablespace. When the database is up, running, and available, the affected (missing or damaged) datafiles of a tablespace can be recovered.

Perform flashback

  • The parameters used to maximize the space in Flash Recovery Area.
  • A backup scheduler generates the RMAN script.
  • FLASHBACK TABLE is used to recover one or more tables to a specific point in time.
  • To configure LogMiner via a GUI-based interface within Oracle Enterprise Manager, the sequence used is Tools > Database Applications.
  • User error failure refers to a database failure that results due to certain transactions on a database by a user.
  • Flashback Query is used to view and repair historical data, and to view the contents of a table at any specific point in time.
  • UNDO_RETENTION is a parameter in the init.ora initialization parameters file that specifies the time period in seconds for which a system retains undo data for committed transactions.
  • Flashback Transaction Query helps in the repair process, once the Flashback Versions Query is run to diagnose the erroneous transaction.

Moving data

  • The Data Pump utility is used to import data and can use EM Database Control to export data. It is also used to transfer metadata from one database to another.
  • expdp system/secret full=Y dumpfile= dir1:fulla%U.dmp, dir2:fullb%U.dmp
    filesize 4g parallel=2 logfile=dir:full.log
    statement performs a full database mode export to files in dir1 and dir2.
  • scott/tiger
    directory = export_directory
    dumpfile = scott.dmp
    include = table
    include = view:like””%salary%””
    content = data_only
    is used to export the objects owned by you by using the Data Pump utility. The exported object belongs to a table named Salary and the view contains a string salary.
  • SQL*Loader is an Oracle utility that is used to load data from external data files into tables of an Oracle database.
  • SQL*Loader uses the following types of data loading methods for loading data into database tables:
    1. Conventional path load

    2. Direct path load
    3. External table load
  • By using the SQL*Loader, a database user can use the operating system’s file system to access the input data files and database user can also manipulate the data before loading it into a database.
  • The conventional path Export does not support the collection data types and it uses the SELECT statement to extract data from tables.
  • The WHEN clause is used to specify one or more field conditions while loading a large amount of data into a database by using SQL*Loader.
  • If active transactions have been performed against the table, the Direct Path Load method cannot be used to load data into the table and triggers cannot be fired when the Direct Path Load method is used for loading data into the table.
  • The sqlldr command invokes the SQL*Loader followed by one or more command-line parameters.
  • While transferring the cluster tables of a database from one schema to another, the Data pump will self-configure the settings and uses the External Table data access.
Pass Oracle OCP Certification DBA 10g Certification in first attempt.