Posted on by uCertify

OCP 1Z0-045 Short Notes: Exam passing tips

General high availability technologies in 10g

  • The ORDER BY clause sorts the records in ascending order and it comes last in the SELECT statement.
  • The Online redo log files and the operating system flat file are used to store a copy of a data dictionary.
  • RMAN can be used to take an incremental backup for tablespaces and data files.
  • The rman target/ command is used to maintain backup copies by connecting to the target database and returns the RMAN prompt to issue further backup commands.
  • The NLS_LANG parameter must be set to specify the language, territory, and character set used in an application.
  • CONFIGURE is an RMAN command that configures an RMAN environment.
  • CONFIGURE RETENTION POLICY CLEAR is a command that will configure the retention policy to the default of REDUNDANCY 1.
  • RMAN is used to migrate the database files from conventional file system storage to ASM storage.
  • The control file, target database catalog, and Recovery catalog are used to store the RMAN’s persistent settings.
  • The redo files are opened after executing the ALTER DATABASE OPEN command on a database when it is in MOUNT mode.
  • RMAN can be used to move database objects from a non-ASM disk location to an ASM disk group.
  • 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.

    • Pass Oracle OCA Certification DBA 10g Certification in first attempt.

  • The alert log file is used to find out the values of the changed parameters at the time of instance startup.
  • The size of the block change tracking file is directly proportional to the number of enabled threads in Real Application Cluster (RAC) environment.
  • The correct order of the steps that should be performed to set up the Resource Manager is as follows:
    1. Creation of the pending area
    2. Creation of the consumer groups
    3. Creation of the directives
    4. Validation of the pending area
    5. Submission of the pending area
    6. Assignment of the users to consumer groups
  • Resource plan directive, a DRM component, is used to tie a plan to a resource group and can also define the allocation method to be utilized.
  • CPU usage and the number of cursors opened are the resources that can be allocated by using DRM.
  • The Database Resource Manager (DRM) supports the nested plans. However, the levels of nesting are not definite.
  • MAX_SESS_POOL is not the plan directive parameters of the Automatic Consumer Group Switching feature.
  • Following features are included in an online table redefinition:
    1. An object table can be converted into a relational table or a table with object columns or vice-versa.

    2. A table or cluster can be re-created to reduce fragmentation.
    3. A table or a cluster can be moved to a different tablespace in the same schema.
  • The five basic steps to redefine a table are as follows:
    1. Create a new image of the table with all of the desired attributes.
    2. Start the redefinition process.
    3. Create any triggers, indexes, grants, and constraints on the new image of the table.
    4. Optionally synchronize and validate data in the new image of the table periodically.
    5. Complete the redefinition of the table.
  • The DBMS_REDEFINITION package is used to perform an online rebuild of a table.
  • While redefining a table online by using the DBMS_REDEFINITION package materialized views, AQ tables, the overflow table of an IOT table and materialized view logs must be redefined online.

Various management tools in Oracle 10g

  • 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.
  • Read consistency is one of the reasons 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.
  • 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.
  • The V is used to determine the undo blocks that are consumed and also determines the size of the undo tablespace required to manage the workload on the database.
  • The UNDO and TEMPORARY tablespaces can be renamed.
  • The DBMS_WM package is used to hold different versions of a row.
  • When the WORKAREA_SIZE_POLICY is set to manual, it will ensure that the overall size of the PGA will never exceed the value of SORT_AREA_SIZE in case of sort operation.
  • The default value of WORKAREA_SIZE_POLICY parameter is AUTO, and in releases earlier than 9i, it was MANUAL.
  • PGA_AGGREGATE_TARGET and WORKAREA_SIZE_POLICY are the parameters used to enable Automated SQL Execution Memory Management.
  • The SGA_TARGET parameter is used to perform automatic memory allocation.
  • To define baselines for different time periods of the database, start_snapshot_id, end_snapshot_id, baseline_name, and database_id parameters are used with the DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE procedure.
  • 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.
  • The DBMS_MONITOR package contains the PL/SQL procedures that are used to control additional tracing and statistics gathering operations.
  • The ADDM runs every hour (60 minutes) by default. It is invoked automatically every time a new AWR snapshot is generated and its result is stored in Automatic Workload Repository (AWR).
  • The MAX_IDLE_TIME and MAX_IDLE_BLOCKER_TIME parameters are used to resolve the blocking session conflict.
  • 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.
  • 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.
  • SQL Access Advisor is used to find out the reason for the performance degradation of SQL statements.
  • 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 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 generate advices.
  • 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.
  • SQL Profiles and AWR are the two sources that the SQL Tuning Advisor will use to obtain the statistics for the current running session.
  • The SQL Access Advisor is used to achieve performance optimality by recommending the proper set of materialized views, materialized view logs, and indexes for a given workload.
  • ASM defines disk groups for file management and hence relieves the burden on a disk.
  • 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 can be used to perform database migration to ASM.
  • The control file is automatically created while migrating a database from non-ASM files to ASM disk group by using RMAN.
  • File mirroring, automatic disk rebalancing, and file stripping are a few features of ASM.
  • Rebalance (RBAL) and Actual Rebalance (ARBn) are the additional background processes that manage data storage operations.

Various enhancements in Oracle 10g

  • An incremental backup of a database contains all used data blocks.
  • 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.
  • An RMAN incrementally updated backup recovers a copy of a data file by applying an RMAN incremental backup to an existing RMAN image copy.
  • 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 BACKUP AS COPY DURATION 3:00 MINIMIZE TIME DATABASE; command is used to run the backup of the database at full speed and should be able to complete the backup within the allotted time. Otherwise, RMAN terminates with an error message.
  • The Data Guard Broker has the capacity to run the DMON process.
  • The HELP command at the command-line interface is used to display the description and syntax for a given command.
  • The QUIT and EXIT commands are used to exit the program at Data Guard command-line interface.
  • The dba_tab_stats_history view is used to show the statistics updated at the table level.
  • 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. Run the script corresponding to the previous version of the database.

      4. Pass Oracle OCA Certification DBA 10g Certification in first attempt.

    4. Shut down the database.
    5. Start up the database normally.
  • Due to disk failure, a control file can be damaged.
  • 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.
  • 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.
  • DBMS_SERVER_ALERT is used to modify the threshold and critical alert conditions.

Installation, globalization support, server configuration, server security, and application tuning

  • 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.
  • The AUDIT_FILE_DEST parameter is used to change the destination for connection-related information.
  • An Oracle 10g client can be used to connect to an Oracle 8i or 9i database by using the following syntax:
    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.
  • GDK allows a single program to work with text in any language from anywhere in the world and it also enables to build a complete multilingual server application.
  • The Character Set Scanner assesses the feasibility and potential issues while migrating an Oracle database to a new database character set.
  • Character Set Scanner is a program that is used to determine the character data loss in the process of converting character sets of a database.
  • The ALTER SYSTEM command is used to restore the default values of initialization parameters.
  • The ISSES_MODIFIABLE column of the V is used to specify if the parameter can be changed with the ALTER SESSION command or not.
  • 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.
  • The Use Grid Control for Database Management option is used to manage more than one database using a single EM interface. 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.
  • 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.
  • Enterprise Manager Database Control can be used to determine the frequency of the Flashback Database feature.
  • 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.
  • SHARED_POOL_SIZE is an ASM initialization parameter that specifies the size of the shared pool in bytes.

Some important techniques in Oracle 10g

  • 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 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.
  • The 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.
  • While transferring the cluster tables of a database from one schema to another, Data Pump will self-configure the settings and use the External Table data access.
  • 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 HELP parameter has not been replaced by any other parameter in the expdp utility.
  • The TRANSFORM parameter is used when the storage characteristics of the source and target databases are different in a copy operation.
  • 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 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.
  • RMAN’s CONVERT command is used to convert the data files before the files are imported in the target database.
  • 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.
  • The DBA_DATAPUMP_JOBS view is used to display all data pump jobs in a database.
  • Master table, Master control process, and Worker process are used to restart failed Data Pump Export due to an accidental instance bounce.
  • The DBMS_WORKLOAD_REPOSITORY.modify snapshot settings procedure is used to change the AWR collection interval.
  • The Enterprise Manager and the DBMS_WORKLOAD_REPOSITORY package are used to view the statistics collected and stored in an AWR snapshot.
  • 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).
  • 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 Server-generated alerts architecture.
  • PGA Advisor forms a part of the advisory framework.
  • The DBA_ALERT_HISTORY data dictionary view is used to increase the warning threshold and to clear all previous alert messages.
  • Top SQL and Top session link in Database Control Performance pages are used to drill down into the session and SQL wait statistics.
  • 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.
  • By default, a server-generated warning alert is generated for tablespace space usage when the percentage of space usage exceeds 85%.
  • 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.

Automating tasks with the scheduler

  • SELECT owner, job_name, state FROM dba_scheduler_jobs; is used to know the state of all the jobs in a table.
  • The Scheduler Window is used to switch the resource plan from one time to another.
  • 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 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 the FREQ parameter can set the repeat_interval for a particular job.
  • 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 an overlapping conflict in case two windows named REQUIRED_HOURS_WINDOW and DONE_HOURS_WINDOW are overlapping each other.
  • The DBA_SCHEDULER_WINDOWS views are used to retrieve information for all the scheduler windows in a database.
  • 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 DBMS_SCHEDULER.LOGGING_RUNS procedure is used to set the LOGGING_LEVEL attribute of a job class to maintain job logs of all job activities so that detailed information is written for all runs of each job in the class.
  • The job table is used to set schedules for each and every job.
  • DBMS_SCHEDULER.LOGGING_RUNS is a setting for the LOGGING_LEVEL attribute. It ensures that detailed information is written for all runs of each and every job in the job class.
  • A program (scheduler object) must be used in case two jobs share a common resource plan.
  • 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 that leads to unavailablility 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 COMPATIBLE parameter must be set to 10.0.0 or higher.
  • The DBA_IND_PARTITIONS data dictionary view 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.
  • 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.

  • CREATE TABLESPACE DATAFILE ‘/u1/data/.dbf’ SIZE 100M; creates a smallfile tablespace.
  • The parameters are used to maximize the space in Flash Recovery Area.
  • A backup scheduler generates the RMAN script.
  • Flashback Query is used to view and repair historical data, and to view the contents of a table at any specific point in time.
  • FLASHBACK TABLE is used to recover one or more tables to a 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.
  • SQL statement is 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.
  • LGWR is a background process that is a part of Flashback Database Architecture.
  • The DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE parameters is required to be set to manually define the Flash Recovery Area.
  • Issuance of the ALTER DATABASE FLASHBACK ON command is the last step in configuring the Flashback Database.
  • The FLASHBACK_TRANSACTION_QUERY view is used to provide information about all flashback transaction queries in a database.

Miscellaneous new features in 10g

  • The steps 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, the SHRINK SPACE COMPACT clause is used with the ALTER TABLE command.
  • 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.
Pass Oracle OCA Certification DBA 10g Certification in first attempt.