Notes for exams 1Z0-053

Can a shutdown abort corrupt a database? No, it is impossible to corrupt the database using these types of commands.

At instance startup, the roll forward phase reconstructs UNDO segments in memory and buffer cache from the active and current redo logs to enable the rollback phase.

Instance recovery never needs an ARCHIVED redo log file.

MTTR (→ Mean Time To Recover) can be controlled by the FAST_START_MTTR_TARGET (which defaults to 0, that means UNLIMITED). This initialization parameter makes the DWR work more harder to ensure that the time between the checkpoint time (in the redo) and the current time tend to be near the value of this particular parameter. This parameter also enables "checkpoint auto-tunning".

When FAST_START_MTTR_TARGET is set to a nonzero value, it will override the LOG_CHECKPOINT_INTERVAL.

MTTR Advisor can be seen through V$INSTANCE_RECOVERY view.

Full checkpoint only occurs with orderly shutdown or by user request (alter system checkpoint).

Partial checkpoint occurs while:

An instance can have up to 8 multiplexed copies of a controlfile.

DB_RECOVERY_FILE_DEST_SIZE must be set before attempting to set DB_RECOVERY_FILE_DEST.

%Test:% 3 errors Q1,3,6 (2013-03-28)

%Test:% 0 error (2013-05-07)

An OPEN backup can only be made if the database is in ARCHIVELOG mode.

In NOARCHIVELOG mode, backup of database can only be done with database closed (MOUNT mode after a CLEAN shutdown).

Files that cannot be backed up with RMAN are:

If there is no level 0 backup, then the first level 1 differential or cumulative backup will in fact perform a level 0 backup!

Incremental backups ⇒ all changed blocks since the last incremental backup (which could be either a level 0 or a level 1)

Cumulative backups ⇒ all changed blocks since the last level 0 backup.

An image copy is a backup file that is identical to the input file.

An image copy can be used immediately, without a restore operation. Backupsets always need a restore operation to be usable.

Tape channels, compression, incremental backups CANNOT be used with image copy backups.

RMAN generates 3 types of sessions against the database:

RMAN’s parallelism (# of channels) cannot exceed the number of input (if the multisection backup is disabled) and output files.

Default RMAN encryption requires a wallet and will be AES128. Alternatively, a password or longer keys can be specified.

CONFIGURE BACKUP OPTIMIZATION ON; allows RMAN not to backup certain files if it considers it already has suffiscient copies of the files (related to retention policy). For read-write datafiles, it will never have identical copies of the file.

DELETE EXPIRED; will not delete anything on disk, only the references marked as expired in the RMAN repository.

DELETE OBSOLETE; will delete files and update the repository accordingly.

RMAN stores its repository on controlfile ⇒ loss of it means no restore possible! RMAN Recovery Catalog avoids this.

If you use LOG_ARCHIVE_DEST or LOG_ARCHIVE_DUPLEX_DEST, these locations must be disks (Filesystems or ASM). It cannot be another Oracle instance.

If a MANDATORY archive destination is unavailable, the database will shut down.

Control files and redo logs are considered as permanent. A copy of the controlfile and each online log is kept in the FRA. Archivelog and backups are considered as transient.

%Test:% 3 errors Q2,5,10 (2013-03-31)

%Test:% 1 error Q5 (2013-05-07)

Health monitor will run reactively or on demand and will write errors details to the ADR.

Health monitor’s PL/SQL package ⇒ DBMS_HM

If one or more failure exists, then you should typically use:

DRA will function only for a single-instance database. It cannot work with a RAC clustered database, nor against a DataGuard standby database.

In NOarchivelog mode, the corruption of a datafile will mean full resore and a clear of the logfile groups using ALTER DATABASE CLEAR LOGFILE GROUP <group_number>;. This recreates the specified logfile group. Other solution would be to drop the relevant tablespace…

In NOarchivelog mode, restoring an incremental backup will need the use of RECOVER DATABASE NOREDO; command after the full backup restore.

RMAN will always apply incremental backups in preference to applying redo data (if they are available).

SYSTEM and/or active UNDO tablespaces and/or any controlfile copy corruption will all bring database down.

Other tablespaces' datafile corruption ⇒ datafile will be brought offline (and the rest of the database will remain open).

Incomplete recovery is necessary if there is a missing archivelog or if all members of the current online redo log file group are missing.

Autobackup of controlfile and spfile will rely on DBID, so keep the DBID in your documentation.

RESTORE CONTROLFILE|SPFILE FROM AUTOBACKUP|'<file_path>'; are the only RMAN commands that can be executed in NOMOUNT mode.

In RMAN, the SET commands (SET UNTIL, NEWNAME, DBID,…) can only be executed in a run block.

Block corruptions will not take the datafile offline. RMAN can detect them as it performs backup operations and repair them automatically.

To manually recover a block using RMAN, use the block recover datafile <datafile_number> block <coma_separated_block_numbers_to_recover>;.

If you use the backup with the MAXCORRUPT option, you can revover corrupted blocks using block recover corruption list [until SYSDATE-7];(for example). The "until sysdate - 7" would recover from a backup at least older than 7 days.

%Test:% 4 errors Q1,6,9,15 (2013-03-31)

%Test:% 4 error Q6,9,11,15 (2013-05-07)

When using rman recovery catalog, RMAN repository is also be stored in the controlfile of the target database.

The retention of the RMAN repository contained in the controlfile is controlled by the CONTROLFILE_RECORD_KEEP_TIME parameter that defaults to 7 days. The recovery catalog can retain data indefinitely.

The user owning the recovery catalog must have the RECOVERY_CATALOG_OWNER role.

The RMAN executable must be the same release as the TARGET database, but it does not need to be the same version as the catalog.

The RMAN catalog must be CREATED with a version of RMAN that is equal or higher than the version of any database that will be registered in it.

If you are using synchronous I/O but you have set BACKUP_DISK_IO_SLAVES init parameter, then the I/O performance is monitored in the V$BACKUP_ASYNC_IO.

%Test:% 1 error Q3 (2013-04-01)

Recovery from loss of a multiplexed online redo log can be done while the database is open (whereas for controlfiles it cannot), and therefore does not entail any database. Use the ALTER DATABASE CLEAR LOGFILE GROUP <group_number>; command when the given group is inactive to recreate the members on disk.

To recover from the loss of a tempfile, create a new one and drop the old one. This can be done online.

V$RECOVER_FILE is the list of all datafiles found to be damaged or missing. Available in both mount or open mode.

In NOarchivelog mode, the loss of a datafile will always result in a complete restore of the database.

A RECOVER DATABASE UNTIL ...; will stop immediately BEFORE applying the change vector of the nominated time or SCN (not immediately after).

%Test:% 1 error Q2 (2013-04-01)

Flashback Database (and other flashback technologies) will not back out physical corruption

Oracle Cluster Services are required on the host in order to setup the communication between the RDBMS instance and the ASM instance.

You can use ASM only for database and recovery files. Not for Oracle Home, Alert log, trace files, passord files and Static PFILE.

ASM Mirroring defaults to a single mirror, but can be set to NONE or DOUBLE.

Stripping is automatic and cannot be disabled.

ASM is a management and control facility that makes files available. It does not do the actual I/O work.

An ASM instance cannot mount or open a database.

You can only connect to an ASM instance using a password file or OS authentication.

A rebalancing operation will start automatically in response to a disk group reconfiguration.

On the RDBMS instance:

Required fields in an ASM instance parameter file are:

If an ASM instance fails, the dependent RDBMS instances using it will abort. RMAN is the only tool that can backup ASM files.

Test: 0 error (2013-04-03)

RESOURCE_LIMITS parameter has nothing to do with the Resource Manager. It has to do with the PROFILES.

The RESOURCE_MANAGER_PLAN instance parameter (that defaults to DEFAULT_MAINTENANCE_PLAN) is the way to control Resource Manager. It can also be set using the scheduler or with the DBMS_RESOURCE_MANAGER.SWITCH_PLAN procedure.

DBMS_RESOURCE_MANAGER_PRIVS package is used to put users into consumer groups and to grant system privileges necessary to administer the Resource Manager. The corresponding role (ADMINISTER RESOURCE MANAGER) cannot be granted or revoked other way than using this package.

Every user can switch its consumer group using the DBMS_SESSION.SWITCH_CURRENT_CONSUMER_GROUP procedure.

A user with rights to administer Resource Manager can use:

There are 4 priority levels that can be used in a plan.

Memory allocated to Resource Manager is the Pending Area (part of the SGA). It is also used to validate a plan before saving it.

Every plan must include the OTHER_GROUP group; otherwise, the validation will fail in the pending area and the plan will not be saved in the data dictionnary.

Active session in Resource Manager vocabulary includes running sessions and idle sessions with uncommited transaction(s).

Test: 5 errors Q1,2,3,10,11 (2013-04-05)

JOB_QUEUE_PROCESS instance parameter limits the max number (0 to 1000 and defaults to 1000) of Jnnn that can be launched. If set to 0, the scheduler will not function.

By default jobs and programs are disabled in the scheduler at creation time.

Job class is used to associate 1 or more job with a Resource Manager consumer group and also to control logging levels.

The MANAGE SCHEDULER system privilege is needed to create job classes and windows and to force windows to open or close irrespective of their schedules.

PROGRAMS and JOBS share the same namespace ⇒ They cannot have the same name! The same is true for SCHEDULES and WINDOWS.

You cannot create lightweight jobs using Database Control. Only the DBMS_SCHEDULER package enables you to do that.

A lightweight job has always AUTO_DROP to TRUE and END_DATE defaulting to current timestamp.

Priorities cannot be set on creation of the job. You must use the DBMS_SCHEDULER.SET_ATTRIBUTE procedure.

Jobs priorities within a class are from 1 to 5 (highest to lowest).

Only one window an be open at once. Other things being equal, the window with the longest to run will open or remain open if 2 windows are overlapping and having the same priority.

Test: 2 errors Q5,8 (2013-04-06)

There will be 3 stages of PGA memory allocation:

The LOG_BUFFER is the only SGA structure that cannot be adjusted dynamically. It cannot therefore be automatically generated.

If AMM (MEMORY_TARGET) is set and that you also set PGA_AGGREGATE_TARGET and SGA_TARGET, these will be considered as MINIMUM. AMM will never reduce PGA and SGA beneath those sizes.

When Automatic Memory Management is enabled, the individual advisors (which are necessary for AMM to function) can be seen in V$ views, but only the overall advisor is displayed by Database Control.

Automatic memory management cannot function unless the statistics_level instance parameter is set to TYPICAL (which is the default) or ALL.

Memory advisor views: v$memory_target_advice, v$sga_target_advice and v$pga_target_advice

An invalid object may become valid on next access, but unusable indexes must be made valid by manual rebuild.

Globalization settings can be specified at the following levels (in order or precedence):

Since release 9i, the National Character Set of the database can only be unicode (but the database characterset can be something else) ⇒ UTF8 (variable size) and AL16UTF16 (fixed-width).

NLS_DATE_LANGUAGE and NLS_SORT are controlled by NLS_LANGUAGE.

NLS_DATE_FORMAT and NLS_NUMERIC_CHARACTERS are controlled by NLS_TERRITORY.

V$NLS_VALID_VALUES lists the supported values for the various NLS parameters.

Test: 3 errors Q6,9,11 (2013-04-10)

The ADR is a central file-based repository for all diagnostic information. This includes various dumps and trace files, the alert log, and health monitor reports.

ADR_BASE defaults to (in order of precedence):

A problem is a critical error in the database or the instance. An incident is an occurrence of a problem. An incident package is a collection of data regarding one or more incidents and problems, formatted for upload to Oracle Support Services as part of an SR.

Before the REFRESHFROMMETALINK job can run, Database Control must be able to make an outbound HTTP connection to My Oracle Support website.

Using the EM Workbench Support, you can create full or incremental packages, add or remove trace files to a package and add SQL test cases to the package.

Test: 3 errors Q5,6,8 (2013-04-10)

Test: 5 errors Q2,3,4,6,8 (2013-05-07)