Posted on by uCertify

OCP 1Z0-043 Short Notes: Exam passing tips

Configuring Recovery Manager

  • The control file, target database catalog, and Recovery catalog is used to store the RMAN’s persistent settings.
  • The alert log file is used to find out the values of the changed parameters at the time of instance startup.
  • The NLS_LANG parameter must be set to specify the language, territory, and character set used in an application.
  • CONFIGURE RETENTION POLICY CLEAR is a command that will configure the retention policy to the default of REDUNDANCY 1.
  • CONFIGURE is an RMAN command that configures an RMAN environment.
  • 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 redo files are opened after executing the ALTER DATABASE OPEN command on a database when it is in MOUNT mode.

Understanding Globalization Support

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

  • The local time zone of each recipient is normalized to GMT, if the column with the sending time of mail is of TIMESTAMP WITH LOCAL TIMEZONE data type.
  • National Language Support (NLS) parameters are used to customize a database to store, process, and retrieve data in a native language and format.
  • The NLS_DATE_FORMAT parameter specifies the default date format for the TO_CHAR (datetime) and TO_DATE functions.
  • ‘NLS_TIMESTAMP_TZ_FORMAT = ‘YYYY-MM-DD HH:MI:SS.FF TZH:TZM’ format is a valid format for the NLS_TIMESTAMP_TZ_FORMAT parameter.
  • The NLS_TIME_TZ_FORMAT parameter is used to define the default time with time zone format to use with the TO_CHAR and TO_TIMESTAMP functions.
  • When a session is established, the client is implicitly required to issue an ALTER SESSION statement to synchronize the session NLS environment to match its NLS environment with that of the server’s NLS environment.
  • If NLS_SORT parameter is set to German_CI then the sort and the search operation will be case and accent insensitive on the database.
  • The WHERE clause will set the default sort method for the NLS_SORT parameter.
  • NVARCHAR2 and NCLOB datatype columns use a national character set to store data.
  • To switch over to another character set for the database the ALTER DATABASE CHARACTER SET and ALTER DATABASE NATIONAL CHARACTER SET commands are used.
  • Character sets are the sequences of bits.

Recovering from User Error

  • All Flashback Technologies, except Flashback Database, use undo data to recover database objects.
  • When a Flashback Table operation is performed, triggers are disabled by default.
  • Run the SHOW RECYCLEBIN command and fire a query against the RECYLEBIN view. Now, you want to crosscheck whether or not the Flashback Drop has been successful.
  • The DROP USER command bypasses the Recycle Bin and removes all user specific database objects immediately.
  • The VERSIONS BETWEEN clause cannot produce versions of the rows that were previously modified to the table structure. The Flashback Version Query uses the VERSIONS BETWEEN clause and can be used in DML and DDL statements to identify data.
  • The system privileges are needed to perform a Flashback Transaction Query to help identify the changes at the transaction level.
  • The FLASHBACK_TRANSACTION_QUERY view is used to provide information about all flashback transaction queries in a database.

Handling Database Corruption

  • The ORA- 01578: Oracle data block corrupted (file #string, blockstring) error message specifies that the data block has been corrupted due to program/software malfunctioning.
  • The DB_BLOCK_CHECKSUM parameter when set to true, helps detect damages introduced by the disk or I/O systems.
  • The DBMS_REPAIR package is used to recover from the corruption when there is corruption in data blocks.
  • The ANALYZE TABLE EMP.e1 validate structure will return an ORA- 01498 error, if the database object has corrupt blocks.
  • The backup scheduler is used to configure database backup to run automatically.
  • The ANALYZE command is used for the partitioning task.
  • The ANALYZE command is commonly used to detect the corrupted blocks in a database object.
  • The DBVERIFY utility is used to check only the data files for corruption.
  • DBMS_REPAIR is a PL/SQL package used to repair data block corruptions in database schema objects.
  • If there is corruption in one of the blocks of a table, the DBMS_REPAIR package is used to rectify the issue.
  • The run{SET MAXCORRUPT for datafile 5 to 50;
    BACKUP datafile 5;}     command block is used to back up a data file that contains some corrupted blocks ranging from 2 to 70.

  • Backup of the target database and Backup of data blocks that have changed since a previous backup are the components that are included in the recovery catalog.
  • The CHANGE_PASSWORD and SET_PASSWORD commands are used to protect the listener by using a password and also does not shut down the listener.

Understanding Automatic Database Management

  • The Undo Advisor is used to prevent snapshot too old error.
  • The Automatic Workload Repository (AWR) can be shared by all Oracle advisors.
  • ASH size = total number of CPU’s * 2MB of memory, or 5% of the Shared pool size
  • Automatic SQL Tuning is used to analyze SQL and tune database system performance. If the statistics gathered by SQL analysis are missing or stale then the query optimizer will not generate an accurate execution plan, the query optimizer will return a recommendation to gather relevant statistics, and query optimizer will return additional information in the form of a SQL profile.
  • The SQL Tuning Advisor is used to generate recommendations for the SQL statements. The SQL Tuning Sets are used as sources for the advisor to analyze SQL statements.
  • Any access-related (lack of indexes) issues can be analyzed by using the SQL Access Advisor.
  • The Undo Advisor is used to determine appropriate sizing for Undo tablespaces and also helps in determining optimal UNDO_RETENTION settings.

Using Recovery Manager

  • The run block is used to backup a database including the current control file.
  • The DUPLICATE command is used to move a database to another server.
  • CTWR background process is used to record the address of each changed block in the change-tracking file.
  • If any unauthorized user has access to a table then the queries performed by an unauthorized user can be monitored by enabling Fine Grained Access Control (FGAC) for the table.
  • Block change tracking records the blocks modified since the last incremental backup and stores blocks in a block change tracking file.
  • RMAN > BACKUP TABLESPACE USERS; will maintain a backup set.
  • The DELETE command of RMAN is used to delete backups and copies. The command also removes references to them from the recovery catalog and updates their control file records to DELETED status.
  • In level 0 backup, every data block in the file, except those that have never been used, are included.
  • The backup metadata will be retained in the control file by setting the CONTROL_FILE_RECORD_KEEP_TIME parameter to 20.
  • The LIST BACKUPSET BY FILE and LIST BACKUPSET BY BACKUP VERBOSE commands are used to obtain detailed information about the backup sets and image copies generated by the RMAN BACKUP command.
  • Synchronization of the RMAN repository with the control file must be performed in order to get the accurate report from the REPORT command.
  • The REPORT NEED BACKUP days 20; statement will return a list of files that have not been backed up for 20 days.
  • OBSOLETE is the status of the datafiles and the control files after executing the CONFIGURE RETENTION POLICY TO RECOVER WINDOW OF 2 DAYS; statement.

Recovering from Non-critical Losses

  • 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 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 online log files can be added and dropped by using the EM Database Control interface.
  • A new redo log member will be added to the online redo log group because the group is inactive and any transactions are not actively being written to it.
  • V and Oracle Enterprise Manager Database Control are used to determine the optimal size of the redo log files.
  • An index tablespace can be restored and recovered only if the database is running in Archive Log mode.
  • PARALLEL and NOLOGGING options are used to re-create the indexes.
  • Following are the conditions when media recovery is needed:
    1. The last backup of the read only tablespace is taken.
    2. The tablespace was read and write and made read-only explicitly.

      3. .

  • If the password file associated with a database is damaged then the database will continue to operate as normal and the database can be started up only if the REMOTE_LOGIN_PASSWORDFILE instance parameter is set to NONE.
  • The steps that should be performed to rebuild a password file are given below:
    1. Shut down the database.

    2. Start up the database.
    3. Run the ORAPWD utility.
  • In order to know all the users listed in the password file, a query is fired against the V view.
  • orapwd file=orapwORA101T password=syspass entries=20 is the correct syntax for specifying the entries in the password file.

  • The configuration settings are obtained by using the STATUS L1 command for the L1 listener.

Monitoring and Managing Storage

  • To modify the settings of the threshold values, the DBMS_SERVER_ALERT package and the SET_THRESHOLD procedure are used.
  • The upper case S in ‘LOG%t_%_S_%r.dbf’ causes the value of the log sequence number to be of fixed length and padded to the left with zeros.
  • The % sign in ‘LOG%t_%_S_%r.dbf’ uniquely identifies the Archived redo logs with the redo log group number for every incarnation of the database.
  • The DBMS_SPACE package has significantly reduced the complexity involved in estimating the size of indexes.
  • The MMON background process is responsible for enabling the alert system.
  • Either a table shrink, or, moving indexes to a different tablespace are online operation.
  • An ORA-01555 snapshot too old error is triggered if an undo tablespace runs out of space.
  • 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 effecting on active queries. To shrink the table SHRINK SPACE with COMPACT clause is used with the ALTER TABLE command.
  • The DELETE FROM and CREATE TABLE statements triggers Resumable Space Allocation.
  • Rebuilding the index, and Dropping and re-creating the index are necessary to avoid the loss of space efficiency.
  • Compact storage, high data availability, and fast primary key access are few features that are associated with the index organized table.

Understanding Automatic Storage Management

  • The purpose of using a RAID structure is to provide fault-tolerance, redundancy, lower latency, and higher bandwidth for read/write operations.
  • An ASM instance is started by using the STARTUP command. The INSTANCE_TYPE parameter is required by ASM instance.
  • While configuring Automatic Storage Management, the ASM_DISKGROUPS parameter is automatically maintained.
  • ALTER SYSTEM ENABLE RESTRICTED SESSION and SHUTDOWN IMMEDIATE commands are used to avoid connections to an ASM instance.
  • The Archived redo logs are stored by ASM.
  • If an ASM instance fails, the RDBMS instances will abort.
  • The ARBn and RBAL background processes coordinates the rebalance activity for ASM disk groups.
  • ASM instances do not have any data dictionary or control file, all connections to an ASM instance can be established via the SYS or SYSTEM user by using only the operating system authentication.
    • Pass Oracle OCP Certification DBA 10g Certification in first attempt.

  • RBAL and OSMB form a combination of background processes that are new for the database instances that use an ASM disk.
  • 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.

Monitoring and Managing Memory

  • The Streams Pool and the Log Buffer are the SGA components that are not referred to as auto-tune components.
  • The STATISTICS_LEVEL instance parameter can be used to disable Automatic Shared Memory Management. Moreover, to enable Automatic Shared Memory Management, the ALL and TYPICAL settings can be used.
  • Since the log buffer is an exception to granule, the size of the log buffer will be set to what is desired, i.e., 1GB (107341824 bytes). All the rules applied to a granule are not applicable for the log buffer.
  • MMAN background processes will be started when the Automatic Shared Memory Management feature is enabled.
  • SGA stores data in memory so that a user can efficiently access data. Therefore, tuning of the SGA is necessary. If the system performance has degraded and comes to a standstill; it means that the SGA is either very small or unnecessarily large. Exporting the database, creating a new database with the DB_BLOCK_SIZE initialization parameter of required capacity and importing the database into the Oracle instance will help in tuning the SGA.
  • Java pool, Large pool, Shared pool, and Database Buffer Cache are automatically tuned when Automatic Shared Memory Management is configured in SGA.
  • To configure an SGA component that will use less memory, the shared pool have to be configured.
  • Bind information, stack space, and sort space are the information that PGA includes.
  • To increase the memory available for the SQL work area, the PGA_AGGREGATE_TARGET initialization parameter is used.

Database Recovery

  • 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.
  • To take a full backup of data files that are not part of SYSTEM tablespaces or the currently active UNDO tablespace, perform the following steps:
    1. Take the damaged data files offline.

    2. Restore the damaged data files.
    3. Recover the damaged data files.
    4. Take the damaged data files online.
  • The control file has to be restored even if it is not damaged in case a tablespace of a database is dropped accidentally.
  • One must connect to the Database Control with SYSDBA privileges to perform an incomplete recovery using the Recovery Wizard in Enterprise Manager.
  • Command blocks are started and delimited with curly braces.
  • The steps to perform incomplete recovery are as given below:
    1. Shut down the database.

    2. Perform the backup.
    3. Restore all data files.
    4. Mount the database.
    5. Recover the database UNTIL time, cancel, or change number.
    6. Open the database with RESETLOGS operation.
    7. Backup the database.
  • If few data files and one of the members from the online redo log group have been damaged due to permanent media failure then drop the damaged online redo log member and add a new one to the group.
  • UNTIL TIME, UNTIL SEQUENCE, UNTIL SCN, UNTIL CHANGE, and UNTIL CANCEL are the clauses that are used with RMAN commands to perform incomplete recovery.
  • A cancel-based recovery is usually performed when the requirement is to recover up to a particular archived redo log file.
  • YYYY-MM-DD:HH24:MI:SS is a valid format while recovering the database in the RECOVER DATABASE UNTIL statement.
  • Cancel based recovery is appropriate in case a data file of one of the database has been damaged.
  • When performed an incomplete database recovery and after this opened the database using the RESETLOGS option then 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 have to be opened with the RESETLOGS option.

Understanding the Flashback Database

  • User error failure refers to a database failure that results due to certain transactions on a database by a user.
  • SQL statement can be used to flashback a database.
  • The dropped table can be recovered by using Flashback Drop.
  • The flashback database is enabled only in Archive log mode, as it recovers the database to an earlier specific point of time.
  • Flashback Database is used to reverse the changes made to a table.
  • RVWR is a background process, which is a part of the Flashback Database Architecture.
  • LGWR is a background process that is a part of Flashback Database Architecture.
  • The correct order of steps that will enable the flashback database are as follows:
    1. Put the database in Archive log mode.

    2. Set up a flash recovery area.
    3. Set the period for the flashback retention target.
    4. Shut down and mount the database.
    5. Enable flashback logging.
    6. Open the database.
  • The overall utilization of the flash recovery area is displayed in megabytes.
  • To enable the Flashback Database, take the following steps:
    1. Ensure that the database is in Archive Log mode.

    2. Setup a flash recovery area.
    3. Set the duration for the flashback retention target.
    4. Shutdown the database.
    5. Mount the database.
    6. Enable flashback logging.
    7. Open the database.
  • SELECT oldest_flashback_scn FROM V; statement is used to retrieve an approximate system change number (SCN) to which a database can be flashed back.
  • The Perform Recovery: Review screen is used to display the RMAN script that performs the Flashback Database recovery.
  • The portlist.ini file is used to get all the port numbers of the HTTP listeners.

Managing Resources

  • The correct order of the steps that should be performed to set up the Resource Manager are 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.
  • DBMS_RESOURCE_MANAGER is an Oracle supplied package that is used to maintain plans, consumer groups, and plan directives and the DBMS_RESOURCE_MANAGER_PRIVS is an Oracle supplied package used to maintain the privileges that are associated with the Resource Manager.
  • 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.
  • A resource consumer group is a logical grouping of user sessions on the basis of the resource requirements of each user session in order to save the system resources.
  • If a user is granted the switch privilege, he can use the DBMS_SESSION.SWITCH_CURRENT_CONSUMER_GROUP procedure to explicitly change the consumer group for his current session.
  • The DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP procedure is used to grant the switch privilege to users or to roles. In such cases, the users or roles can switch to any consumer group for which they have been granted the switch privilege and they can also grant the switch privilege to any other user if they have been granted the privilege with the ADMIN option.
  • The ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = ‘FORCE:DAY_PLAN’; statement will enable the Database Resource Manager in which DAY PLAN will be the top level plan and also restrict any changes to the top level plan.
  • The DBMS_RESOURCE_MANAGER.DELETE_PLAN_CASCADE procedure is used to delete the resource plan along with all it subordinate objects.
  • In case of slow response of the active queries fired against a database, the active session pool should be set to zero for all the resource groups. As a result all the sessions at that moment will hang, the SQL statements already in progress will continue until they are completed, the SYS_GROUP resource group will be able to issue more SQL statements, and the database will be frozen for maintenance purposes.
  • If a plan does not have a directive for the OTHER_GROUPS consumer group, the validation of the plan in the pending area will fail, and the plan is not saved from the pending area to the data dictionary.
  • 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 difference between the two.
  • The CPU resources are always shared or allocated to the connected sessions only.

Automating Tasks with Scheduler

  • SELECT owner, job_name, state FROM dba_scheduler_jobs; statement 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 referencing the window group will not be disabled, as their schedule are not disabled.
  • In order to grant sufficient privileges to a 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 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 the overlapping conflict, in case of two windows named REQUIRED_HOURS_WINDOW and DONE_HOURS_WINDOW overlapping each other.
  • The DBA_SCHEDULER_WINDOWS view is used to retrieve information for all the scheduler windows in a database.
  • 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.
    • Pass Oracle OCP Certification DBA 10g Certification in first attempt.

  • 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.