Posted on by uCertify

OCP 1Z0-040 Short Notes: Exam passing tips

New Features of Oracle 10g, Oracle 10g Installation, Server Configuration, and Maintenance of the Software

  • Memory – 512MB, Temp space – 400MB of free space in the /tmp directory applicable for the Unix operating system, Swap space – 1GB or twice the amount of RAM, Free disk space – 1.5GB are the minimum hardware requirements to install Oracle 10g.
  • The Installation Type list box is used to decide the type of installation.
  • Oracle Management Agent (OMA) is a centralized administrative tool that is capable of administering the database server and an application server.
  • The location (/u01/app/oracle) will be considered as the base of Optimal Flexible Architecture (OFA).
  • The password file is used to authenticate the administrative tasks from remote locations.
  • An Oracle 10g client can be used to connect to an Oracle 8i or 9i database by using the following syntax:
    Pass Oracle OCP Certification DBA 10g Certification in first attempt.

    CONNECT username/password@[//]host[:port][/service_name]
    In Oracle 10g, the maximum size has been increased to (4GB-1byte) *DB_BLOCK_SIZE.

  • The quote operator allows database users to specify their own delimiter.
  • The default value of the TIMESTAMP data type is 6.

  • The ORDER BY clause sorts the records in ascending order and it comes last in the SELECT statement.
  • The DESC keyword used with the ORDER BY clause sorts the customer names in descending order.
  • The WHERE clause is used to specify conditions for a SELECT statement.
  • The LIKE operator is a comparison operator used for pattern matching.
  • The BETWEEN operator tests whether or not the specified value lies within a range of values.
  • The ORDER BY clause is used with a SELECT statement in order to sort the rows retrieved by the SELECT statement in the specified order.
  • Once a table alias is defined for a table, the columns of the table can be qualified with the table alias only. They cannot be qualified with the table name.
  • The ALTER SYSTEM FLUSH BUFFER CACHE; statement is used to flush the buffer cache to provide an identical starting point in order to compare rewritten SQL statements.
  • REGEXP_SUBSTR is a function that searches for a regular expression pattern within a given string and returns the matched sub string.
  • Starting the database instance with the server parameter file will increase the database buffer cache.
  • The Use Grid Control for Database Management option is used to manage more than one database using a single EM interface.
  • 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
  • DBMS_REGISTRY is a DBMS package that is used to determine which components are loaded into a database.
  • It is sometimes necessary to downgrade a database to its previous release such as in case of issues with applications in the new database.
  • The sqlplus /NOLOG @startmydb.sql is used to run the startmydb.sql script for the 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.
  • 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.
  • 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.
  • The steps to clone a database are given below:
    1. Start the DBCA and choose Manage Templates.

    2. Choose the From an Existing Database (structure as well as data) option.

    3. Choose the database.
  • The EM Database Control is a GUI and a Web-based interface that manages an Oracle instance and a database. It is installed at the time of Oracle installation.
  • The Database Usage Statistics page is used to determine the usage frequency of VPD.

Loading-Unloading of Data and Space Management

  • 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.
  • Data Pump consists of the following components:
    1. Data Pump API (DBMS_DATAPUMP)

    2. Metadata API (DBMS_METADATA)

    3. Client tools such as Enterprise Manager and SQL*Plus etc.

    4. Data movement API’s
  • The TRANSFORM parameter is used when the storage characteristics of the source and target databases are different in a copy operation.
  • While transferring the cluster tables of a database from one schema to another, the Data pump will self-configure the settings and use the External Table data access.
  • The HELP parameter has not been replaced by any other parameter in the expdp utility.
  • The CREATE TABLE…. ORGANIZATION EXTERNAL…. AS SELECT…. FROM…. command-line is used to create and populate external tables.
  • The CREATE TABLE….ORGANIZATION EXTERNAL SQL statement is used to create an external table.
  • 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.
  • For Oracle databases that have been upgraded from previous versions to Oracle 10g, all the tablespace alerts are off by default. Alerts are not necessary when the tablespace is set as read-only.
  • The DBA_ALERT_HISTORY data dictionary view is used to increase the warning threshold and to clear all previous alert messages.
  • Following steps are required to transport a tablespace across platforms:
    1. Use the DBMS_TTS.TRANSPORT_SET_CHECK procedure to ensure that the tablespaces to be transported are self-contained.

    2. Use the ALTER TABLESPACE command to make tablespaces to be transported read-only in the source database.

    3. Use the expdp utility to unload the metadata information of the tablespaces to be transported.

    4. Use the impdp utility to import the metadata of the tablespaces to be transported in the target database.

    5. Make the transported (new) tablespaces read-write in the target database.
  • RMAN’s CONVERT command is used to convert the data files before the files are imported in the target database.
  • 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 ALTER TABLE command is used to reclaim the space that is being wasted in the table by using the segment shrink functionality.
  • The table scan after and before the deletion operation of rows takes an equal amount of time.
  • If a table is highly fragmented, then it needs a shrink operation without affecting active queries. To shrink the table, SHRINK SPACE with the COMPACT clause is used with the ALTER TABLE command.
  • The computer generated names are the combination of numbers ranging from 1 to 13 and lower case letters.
  • 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 contains the link to the result of the analysis at the bottom of the window.
  • The Undo Advisor is an ADDM diagnostic advisor that is used to determine appropriate sizing for Undo tablespaces and also helps in determining optimal UNDO_RETENTION settings.

Know about Automatic Management, Manageability Infrastructure, Application Tuning, and Automatic Storage Management

  • ASM defines disk groups for file management, and therefore, relieves the burden on a disk.
  • To define baselines for different time periods of the database, the start_snapshot_id, end_snapshot_id, baseline_name, and database_id parameters are used with the DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE procedure.
  • Both the awrrpt.sql and awrrpti.sql scripts are used to view the AWR reports.
  • In order to get the tuning advice, the task should be created first, appropriate parameters for the task should be set, analysis task should be performed, and finally, a report or a query should be obtained from the DBA_ADVISOR_FINDINGS view.
  • Whenever a table or a schema is locked by using the SET_*, DELETE_*, IMPORT_*, or GATHER_* procedure, the exec dbms_stats.gather_table_stats procedure will always return an error message.
  • The dba_tab_stats_history view is used to show the statistics updated at the table level.
  • Top SQL and Top session link in Database Control Performance pages is used to drill down into the session and SQL wait statistics.
  • The DBMS_WM package is used to hold different versions of a row.
  • The owminst.plb script is used to install the Workspace Manager.
  • 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 metrics.
  • The Enterprise Manager and the DBMS_WORKLOAD_REPOSITORY package are used to view the statistics collected and stored in an AWR snapshot.
  • CREATE_SNAPSHOT is a procedure in the DBMS_WORKLOAD_REPOSITORY package. It is used to create a snapshot at a specific time other than the one generated automatically. It is also a function whose return value is the snapshot_id.
  • The MMON background process is a part of the Server-generated alerts architecture.
  • The Resumable Space Allocation feature suspends and later resumes the execution of large database operations in the event of space allocation failures.
  • The DBMS_SERVER_ALERT package is used to configure an Oracle database server to issue an alert when a threshold for a specified server metric is violated.
  • If the tnsnames.ora file was not found at the exact directory location specified in TNS_ADMIN variable and if the database is not known to the Oracle client, then Oracle will return the “”ORA-12154: TNS: could not resolve the service name”” error message.
  • PGA Advisor forms a part of the advisory framework.
  • SHARED_POOL_SIZE is an ASM initialization parameter that specifies the size of the shared pool in bytes.
  • The Segment Advisor is an ADDM diagnostic advisor that determines whether an object needs a shrink operation.
  • Table, tablespaces, object, and segment are the levels for which the Segment Advisor generates advices.
  • The purpose of using a RAID structure is to provide fault-tolerance, redundancy, lower latency, and higher bandwidth for read/write operations.
  • The ALTER SYSTEM ENABLE RESTRICTED SESSION and SHUTDOWN IMMEDIATE commands are used to avoid connections to an ASM instance.
  • The ARBn and RBAL background processes coordinate the rebalance activity for ASM disk groups.
  • RMAN is used to migrate the database files from conventional file system storage to ASM storage.
  • Using RMAN can move the database objects from a non-ASM disk location to an ASM disk group.
  • While configuring Automatic Storage Management, the ASM_DISKGROUPS parameter is automatically maintained.
  • RMAN can be used to move database objects from a non-ASM disk location to an ASM disk group.
  • The control file is automatically created while migrating a database from non-ASM files to the ASM disk group by using RMAN.
  • File mirroring, automatic disk rebalancing, and file stripping are a few features of ASM.

General Storage, Backup, and Recovery Enhancements

  • When the SQL*Plus command line is used to upgrade the database, the following steps must be considered:
    1. Start up the database in MIGRATE mode.

    2. Create the SYSAUX tablespace with the required attributes.

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

    3. Run the script corresponding to the previous version of the database.

    4. Shut down the database.

    5. Start up the database normally.
  • The SYSAUX tablespace cannot ever be OFFLINE and it cannot be a temporary tablespace.
  • If the definition of the tablespace has been deleted in the CREATE DATABASE command, the tablespace will be created in the default tablespace location for the database, i.e. /dbs in Unix or %ORACLE_HOME%\database in Windows.
  • Due to disk failure, a control file can be damaged.
  • The COPY_FILE procedure of the DBMS_FILE_TRANSFER package is used to copy a file on a local file system.
  • The DBMS_FILE_TRANSFER and DBMS_SCHEDULER packages are used to transfer files within a single database and between databases.
  • User error failure refers to a database failure that results due to certain transactions on a database by a user.
  • SQL statements can be used to flashback a database.
  • The flashback database is enabled only in Archive log mode, as it recovers the database to an earlier specific point of time.
  • RVWR is a background process that is a part of the Flashback Database Architecture.
  • LGWR is a background process that is a part of the Flashback Database Architecture.
  • The DB_RECOVERY_FILE_DEST_SIZE is used to set up the Flash Recovery Area for recovery activities in an Oracle database. Both SPFILE and MEMORY are the valid scopes for the DB_RECOVERY_FILE_DEST_SIZE parameter.
  • When performing an incomplete database recovery and after performing an incomplete database recovery, the database can be opened using the RESETLOGS option. All the backups and Archive logs made before the use of the RESETLOGS option can still be used. The database incarnation number is changed and the log switch sequence number (SCN) is also changed.
  • Control files require a recovery operation with the RESETLOGS clause.
  • If there is a loss of the only member of an unarchived redo log group, then a database should be opened with the RESETLOGS option.
  • RMAN backups created before the RESETLOGS operation can still be used. Moreover, the incremental backups created after the RESETLOGS operation can be applied to full backups of the previous database incarnation. To avoid the ambiguity of naming convention between the log files created before and after the RESETLOGS operation, the default format for the LOG_ARCHIVE_FORMAT initialization parameter is changed from %t_%s_%r.arc to %t_%s_%r.dbf. The parameter %r represents the RESETLOGS identifier and changes every time a RESETLOGS operation occurs. This ensures that no conflicts occur about the name of the log file in the Flash Recovery Area or in an Archived Log File destination.
  • The CTWR background processes write to the change-tracking file.
  • An RMAN incrementally updated backup recovers a copy of a data file by applying an RMAN incremental backup to an existing RMAN image copy.
  • The V dynamic performance view includes the status, the location where the block change tracking file is stored, and the size in bytes of the file.
  • 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 data files.
  • An incremental backup of a database contains all used data blocks.

Analytical Applications Support and Improved VLDB Support

  • MERGE is a DML statement and is used to update a table.
  • The MERGE statement is a Data Manipulation Language (DML) statement that is used to update or insert rows conditionally into a table.
  • The DBMS_MVIEW.EXPLAIN_MVIEW procedure is used to determine if a materialized view is fast refreshable or eligible for a query rewrite.
  • A tempfile can be recreated by taking the following steps:

    1. Add another tempfile to the damaged temporary tablespace.

    2. Take the damaged tempfile offline.

    3. Drop the damaged file.
  • When a temporary tablespace associated with the database is corrupted, then it will return an error while connecting to the database. However, the database will operate as normal for the end user.
  • The SELECT statement fails in case of media or disk failure, which leads to unavailability of the temporary data files.
  • The steps that must be taken to start a database when a tempfile is missing are given below:
    1. Start the database in mount mode.

    2. Drop the tablespace inclusive of all its contents by using the DROP TABLESPACE TEMP INCLUDING CONTENTS command.

    3. Recreate the temporary tablespace by using the CREATE TEMPORARY TABLESPACE TEMP TEMPFILE command.
  • The DBMS_ROWID package is used to retrieve the data block number, the object number, and other components of the ROWID.
  • Whenever an index partition becomes unusable due to any of the partition DML commands (such as ADD PARTITION, SPLIT PARTITION, MERGE PARTITION, and MOVE PARTITION), and a SQL command such as SELECT is issued to access the unusable index, an ORA-01502 (index ‘schema. indexname’ or partition of such index is in unusable state) error message is displayed.
  • The DBA_IND_PARTITIONS data dictionary view is used to monitor the database for unusable indexes.
  • The “”ORA-01502: index ‘schema.indexname’ or partition of such index is in unusable state”” error is displayed when a SELECT statement tries to access an unusable index partition.
  • The COMPATIBLE parameter must be set to 10.0.0 or higher.
  • CREATE INDEX emp_id_ix2 on hr.employee(hire_date)

    global partition by hash(hire_date)

    partition p1 tablespace idx_1,

    partition p1 tablespace idx_2,

    partition p1 tablespace idx_3,

    partition p1 tablespace idx_4,

    partition p1 tablespace idx_5);

    CREATE INDEX emp_id_ix2 on hr.employee(hire_date)

    global partition by hash(hire_date)

    partition 5

    store in (idx_1, idx_2, idx_3, idx_4, idx_5);
    are used to create a hash-partioned global index to add a new DDl option for partition maintenance.

Automating Tasks with the Scheduler, System Resource Management, and Security

  • SELECT owner, job_name, state FROM dba_scheduler_jobs; is used to know the state of all the jobs in a table.
  • The ENABLED attribute is used to enable a job.
  • Whenever a job is copied, the job copy requires a unique name and is disabled by default.
  • Schedules are used to run a job at some specific point in time.
  • The interval element of FREQ parameter can set the repeat_interval for a particular job.
  • The Scheduler Window is used to switch the resource plan from one time to another.
  • If the REPEAT_INTERVAL parameter is set to NULL, then the window will open only once at the specified start date.
  • BEGIN

    DBMS_SCHEDULER.CREATE_WINDOW(

    window_name => ‘WORK_HOURS_WINDOW’,

    resource_plan => ‘DAY_PLAN’,

    schedule_name => ‘WORK_HOURS_SCHEDULE’,

    duration => INTERVAL ’10’ HOUR,

    window_priority => ‘HIGH’);

    END;

    / is used to create a window that activates the DAY_PLAN resource plan and uses a schedule named WORK_HOURS_SCHEDULE.

  • The FORCE parameter is used to resolve the overlapping conflict in case of two windows named REQUIRED_HOURS_WINDOW and DONE_HOURS_WINDOW overlapping each other.
  • The DBA_SCHEDULER_WINDOWS views are used to retrieve information for all the scheduler windows in a database.
  • A window or window group is always referenced with a prefix SYS in the ENABLE procedure.
  • If the FORCE option is set to TRUE for a window group, the window group will be disabled. Any open window that is a member of the group will continue till its completion. Jobs that reference the window group as their schedule will not be disabled.
  • In order to grant sufficient privileges to the user, the GRANT dba To command can be used.
  • The calendaring expression is used to specify the repeat_interval attribute while creating a schedule.
  • A calendaring expression is used to schedule jobs for every possible combination of run dates.
  • The SWITCH_TIME_IN_CALL parameter is one among various CREATE_PLAN_DIRECTIVE procedure parameters.
  • A sub-plan can allocate the resources that have been allocated to it by the top-level plan. However, there is no conceptual differences between the two.
  • The CPU resources are always shared or allocated to the connected sessions only.
  • The MAX_IDLE_TIME and the MAX_IDLE_BLOCKER_TIME parameters are used to resolve the blocking session conflict.
  • The PMON process is used to terminate the blocking session.
  • If more than one attribute satisfies a session, the default attribute precedence set in the database determines the resource consumer group.
  • The DBMS_RLS package provides the fine-grained access control (row-level security) administrative interface.
  • In column masking behavior, the rows are displayed, but the columns with sensitive information are shown as NULL.
  • Column-level VPD is a policy that is used to restrict rows containing sensitive column data (default).
  • 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).
  • The AUDIT_FILE_DEST parameter is used to change the destination for connection-related information.
  • Auditing is the process of monitoring and recording the actions of selected users in a database.
Pass Oracle OCP Certification DBA 10g Certification in first attempt.