1Z0-043 Notes – CH 11 – Managing Storage

Space Management Overview
Space is automatically managed by the Oracledatabase. It generates alerts about potential problems and recommends possible solutions. Features include:
• Oracle Managed Files (OMF)
• Free-space management with bitmaps (“locallymanaged”) and automatic data file extension
• Proactive space management (default thresholds and server-generated alerts)
• Space reclamation (shrinking segments, online table redefinition)
• Capacity planning (growth reports)

Allocating Extents
With locally managed tablespaces, the Oracle database looks for free space to allocate to a new extent by first determining a candidate data file in the tablespace and then searching the
data file’s bitmap for the required number of adjacent free blocks. If that data file does not have enough adjacent free space, then the Oracle database looks in another data file.
Two clauses affect the sizing of extents:
• With the UNIFORM clause, the database creates all extents of a uniform size that you specified (or a default size) for any objects created in the tablespace.
• With the AUTOALLOCATE clause, the database determines the extent-sizing policy for the tablespace.

Block Space Management
Space management involves the management of free space at the block level. With Automatic Segment Space Management, each block is divided into four sections, named FS1 (between 0 and 25% of free space), FS2 (25% to50% free), FS3 (50% to 75% free), and FS4 (75% to100% free).
Depending on the level of free space in the block, its status is automatically updated. That way, depending on the length of an inserted row, you can tell whether a particular block can
be used to satisfy an insert operation. Note that a “full” status means that a block is no longer available for inserts.

Row Chaining and Migrating
In two circumstances, the data for a row in a table may be too large to fit into a single data block. In the first case, the row is too large to fit into one data block when it is first inserted. In this case, the Oracle database stores the data for the row in a chain of data blocks (one or more) reserved for that segment. Row chaining most often occurs with large rows, such as
rows that contain a column of data type LONG or LONG RAW. Row chaining in these cases is unavoidable.
However, in the second case, a row that originally fit into one data block is updated, so that the overall row length increases, and the block’s free space is already completely filled. In this case, the Oracle database migrates the data for the entire row to a new data block, assuming that the entire row can fit in a new block. The database preserves the original row piece of a migrated row to point to the new block containing the migrated row. The ROWID of a migrated row does not change.
When a row is chained or migrated, I/O performance associated with this row decreases because the Oracle database must scan more than one data block to retrieve the information
for the row.
The Segment Advisor finds the segments containing migrated rows that result from an UPDATE.

Proactive Tablespace Monitoring
Tablespace disk space usage is proactively managed by the database in the following ways:
• Through the use of database alerts, you are informed when a tablespace runs low on available disk space as well as when particular segments are running out of space. You
can then provide the tablespace with more disk space, thus avoiding out-of-space conditions.
• Information gathered is stored in the Automatic Workload Repository (AWR) and is used to perform growth trend analysis and capacity planning of the database.

Monitoring Tablespace Space Usage (tips)
The database tracks space utilization while performing regular space management activities. This information is aggregated every 10 minutes by the MMON process. An alert is triggered when the threshold for a tablespace has been reached or cleared.
• Alerts should not be flagged on tablespaces that are in read-only mode, or tablespaces that were taken offline, because there is not much to do for them.
• In temporary tablespaces, the threshold value has to be defined as a limit on the used space in the tablespace.
• For undo tablespaces, an extent is reusable if it does not contain active or unexpired undo. For the computation of threshold violation, the sum of active and unexpired
extents is considered as used space.
• For tablespaces with autoextensible files, the thresholds are computed according to the maximum file size you specified, or the maximum OS file size.

Shrinking Segments
The diagram in the slide describes the two phases of a table shrink operation. The first phase does the compaction. During this phase, rows are moved to the left part of the segment as
much as possible. Internally, rows are moved by packets to avoid locking issues. After the rows have been moved, the second phase of the shrink operation is started. During this phase, the high-water mark (HWM) is adjusted and the unused space is released.
The COMPACT clause is useful if you have long-running queries that might span the shrink operation and attempt to read from blocks that have been reclaimed. When you specify the
SHRINK SPACE COMPACT clause, the progress of the shrink operation is saved in the bitmap blocks of the corresponding segment. This means that the next time a shrink operation is
executed on the same segment, the Oracle database remembers what has been done already.
You can then reissue the SHRINK SPACE clause without the COMPACT clause during offpeak hours to complete the second phase.

Results of Shrink Operation
• Improved performance and space utilization
• Indexes maintained
• Triggers not executed
• Number of migrated rows may be reduced
• Rebuilding secondary indexes on IOTs recommended

Space Reclamation with ASSM
A shrink operation is an online and in-place operation because it does not need extra database space to be executed.
• You cannot execute a shrink operation on segments managed by free lists. Segments in automatic segment-space managed tablespaces can be shrunk. However, the following
objects stored in ASSM tablespaces cannot be shrunk:
- Tables in clusters
- Tables with LONG columns
- Tables with on-commit materialized views
- Tables with ROWID-based materialized views
- IOT mapping tables
- Tables with function-based indexes
• ROW MOVEMENT must be enabled for heap-organized segments.
Note: Automatic Segment Space Management (ASSM) is the default type of segment space management

Free Space Management

• Automatic
• Enabled by the use of locally managed tablespaces
• Tracked by bitmaps in segments

Benefits:
• More flexible spaceutilization
• Run-time adjustment
• Multiple process search of BMBs

Managing Resumable Space Allocation
A resumable statement:
• Enables you to suspend large operations instead of receiving an error
• Gives you a chance to fix the problem while the operation is suspended, rather than starting over
• Is suspended for the following conditions:
– Out of space
– Maximum extents reached
– Space quota exceeded

1Z0-043 Notes – CH 12 – Automatic Storage Management

ASM divides files into allocation units (AUs) and spreads the AUs for each file evenly acr all the disks. aSM maintain a balanced load across the disks. This is done while the database is active.

ASM instance

contains two new background processes. One coordinates rebalance activity
for disk groups. It is called RBAL. The second one performs the actual rebalance AU
movements. There can be many of these at a time, and they are called ARB0, ARB1, and so
forth. An ASM instance also has some of the same background processes as a database
instance, including SMON, PMON, LGWR, DBWR, and CKPT.

Database instance

Each database instance using ASM has two new background processes called ASMB and
RBAL. RBAL performs global opens of the disks in the disk groups. At database instance
startup, ASMB connects as a foreground process into the ASM instance. Communication
between the database and the ASM instance is performed via this bridge.

ASM Instance Initialization Parameters
• INSTANCE_TYPE should be set to ASM for ASM instances.
• DB_UNIQUE_NAME specifies the service provider name for which this ASM instance
manages disk groups. The default value of +ASM must be modified only if you run
multiple ASM instances on the same node.
• ASM_POWER_LIMIT controls the speed for a rebalance operation. Values range from 1
through 11, with 11 being the fastest. If omitted, this value defaults to 1. The number of
slaves is derived from the parallelization level specified in a manual rebalance command
(POWER), or by the ASM_POWER_LIMIT parameter.
• ASM_DISKSTRING is an operating system–dependent value used by ASM to limit the
set of disks considered for discovery.
• ASM_DISK_GROUPS is the list of names of disk groups to be mounted by an ASM
instance at startup, or when the ALTER DISKGROUP ALL MOUNT command is used.

Disk Group Mirroring
ASM has three disk group types that support different types of mirroring:
• External redundancy: Do not provide mirroring. Use an external-redundancy disk
group if you use hardware mirroring or if you can tolerate data loss as the result of a disk
failure. Failure groups are not used with these types of disk groups.
• Normal-redundancy: Support two-way mirroring
• High-redundancy: Provide triple mirroring

Relocate your entire database to an ASM disk group: (It is assumed that you are using a server
parameter file.)
1. Obtain the file names of the current control files and online redo logs by using V$CONTROLFILE and V$LOGFILE.
2. Shut down the database consistently. Modify the server parameter file of your database as follows:
- Set the necessary OMF destination parameters to the desired ASM disk group.
- Remove the CONTROL_FILES parameter.

3. Edit and run the RMAN command file, which backs up the database, switches the current data files to the backups, and renames the online redo logs. You can move only tablespaces or data files by using the BACKUP AS COPY command.
4. Delete the old database files.
Note: If you create an OMF control file, and if there is a server parameter file, then a
CONTROL_FILES initialization parameter entry is created in the server parameter file.

1Z0-043 Notes – CH 13 – Managing Resources

Database Resource Manager Concepts
Administering systems by using the Database Resource Manager involves the use of resource plans, resource consumer groups, and resource plan directives.
A resource consumer group defines a set of users or sessions that have similar requirements for using system and database resources.
A resource plan specifies how the resources are distributed among various resource consumer groups. The Database Resource Manager also allows for creation of plans within plans, called subplans.
Resource plan directives specify how a particular resource is shared among consumer groups or subplans. You associate resource consumer groups and subplans with a particular resourceplan through plan directives.
Resource allocation methods determine what policy to use when allocating for any particular resource. Resource allocation methods are used by resource plans and resource consumer groups.

You can manage database and operating system resources, such as:
– CPU usage
– Degree of parallelism
– Number of active sessions
– Undo generation
– Operation execution time – Operation does not start if the estimate is longer
than MAX_EST_EXEC_TIME. (ORA-07455)
– Idle time
• You can also specify criteria that, if met, cause the automatic switching of sessions to another consumer group.

Accessing Resource Plans
Using Enterprise Manager
The Enterprise Manager Database Control Console provides an easy-to-use  graphical interface for configuring resource plans, consumer groups, and so on.
Using the DBMS_RESOURCE_MANAGER Package
This PL/SQL package comprises many procedures, including the following:
• CREATE_PLAN: Names a resource plan and specifies its allocation methods
• UPDATE_PLAN: Updates a resource plan’s comment
• DELETE_PLAN: Deletes a resource plan and its directives

The SYSTEM_PLAN resource plan is one of the default plans provided for you. It contains directives for the following provided consumer groups:
• SYS_GROUP: The initial consumer group for the SYS and SYSTEM users
• OTHER_GROUPS: Used for all sessions that belong to consumer groups that are not part of the active resource plan. There must be a plan directive for OTHER_GROUPS in any active plan.
• LOW_GROUP: A group with lower priority than SYS_GROUP and OTHER_GROUPS in this plan. You must decide which user sessions will be part of LOW_GROUP. Initially, no user is associated with this consumer group. The switch privilege is granted to PUBLIC for this group.

Resource Allocation Methods for Resource Plans

CPU_MTH :
• EMPHASIS, the default method, is for multilevel plans that use percentages to specify how CPU is distributed among consumer groups.
• RATIO is for single-level plans that use ratios to specify how CPU is distributed.

PARALLEL_DEGREE_LIMIT_MTH – PARALLEL_DEGREE_LIMIT_ABSOLUTE

ACTIVE_SESS_POOL_MTH – ACTIVE_SESS_POOL_ABSOLUTE

QUEUING_MTH – FIFO_TIMEOUT

Resource Consumer Group Mapping
You can configure the Database Resource Manager to automatically assign consumer groups to sessions by providing mappings between session attributes and consumer groups. There are two types of session attributes: login attributes and run-time attributes.

Activating a Resource Plan for an Instance

The plan for an instance is defined using the RESOURCE_MANAGER_PLAN database initialization parameter. This parameter specifies the top plan to be used for this instance. If no plan is specified, the Resource Manager is not activated for the instance.

If the parameter is set in a parameter file, and the plan specified is not defined – ORA-07452: specified resource manager plan does not exist in the data dictionary

Monitoring the Resource Manager

DBA_RSRC_PLANS Plans and status

DBA_RSRC_PLAN_DIRECTIVES Plan directives

DBA_RSRC_CONSUMER_GROUPS Consumer groups

DBA_RSRC_CONSUMER_GROUP_PRIVS

DBA_RSRC_GROUP_MAPPINGS

• V$SESSION: Contains the resource_consumer_group column that shows the current group for a session
• V$RSRC_PLAN: A view that shows the active resource plan
• V$RSRC_CONSUMER_GROUP: A view that contains statistics for all active groups

1Z0-043 Notes – CH 14 – Automating Tasks with the Scheduler

Key Components and Steps
To simplify management tasks with the Scheduler,  perform the following steps:

1. Create a program.
DBMS_SCHEDULER.CREATE_PROGRAM(
program_name => ‘CALC_STATS2′,
program_action => ‘HR.UPDATE_HR_SCHEMA_STATS’,
program_type =>’STORED_PROCEDURE’,
enabled => TRUE);
END;

2. Create and use a schedule.
BEGIN
DBMS_SCHEDULER.CREATE_SCHEDULE(
schedule_name => ‘stats_schedule’,
start_date => SYSTIMESTAMP,
end_date => SYSTIMESTAMP + 30,
repeat_interval =>’FREQ=HOURLY;INTERVAL=1′,
comments => ‘Every hour’);
END;
/

3. Create and submit a job.
4. Monitor a job. – DBA_SCHEDULER_JOB_RUN_DETAILS

Creating a Time-Based Job

BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name=>’HR.DO_BACKUP’,
job_type => ‘EXECUTABLE’,
job_action => ‘/home/usr/dba/rman/nightly_incr.sh’,
start_date=> SYSDATE,
repeat_interval=>’FREQ=DAILY;BYHOUR=23′, /* next night at 11:00 PM */
comments => ‘Nightly incremental backups’);
END;
/

Creating an Event-Based Job

BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name=>’ADMIN.PERFORM_DATA_LOAD’,
job_type => ‘EXECUTABLE’,
job_action => ‘/home/usr/dba/rman/report_failure.sh’,
start_date => SYSTIMESTAMP,
event_condition => ‘tab.user_data.object_owner =
”HR” and tab.user_data.object_name = ”DATA.TXT”
and tab.user_data.event_type = ”FILE_ARRIVAL”
and tab.user_data.event_timestamp < 9 ‘,
queue_spec => ‘HR.LOAD_JOB_EVENT_Q’);
END;

Creating Job Chains
1. Create a chain object.  – CREATE_CHAIN
2. Define chain steps.   – DEFINE_CHAIN_STEP, DEFINE_CHAIN_EVENT_STEP
3. Define chain rules.  – DEFINE_CHAIN_RULE
4. Starting the chain:
– Enable the chain.
– Create a job that points to the chain.

Advanced Scheduler Concepts
Using the advanced Scheduler concepts, you can have more advanced control of aspects
of scheduling, such as prioritizing jobs. The components are summarized below, and are
discussed in detail in the following slides.
• A window is represented by an interval of time with a well-defined beginning and
end, and is used to activate different resource plans at different times. This allows
you to change resource allocation during a time period such as time of day or time
of the sales year.
• A window group represents a list of windows, and allows for easier management of
windows. You can use a window or window group as the schedule for a job to
ensure that the job runs only when a window and its associated resource plan are
active.
• Ajob class defines a category of jobs that share common resource usage
requirements and other characteristics. A job class groups jobs into larger entities.
• A resource consumer group associated with the job class determines the resources
that are allocated to the jobs in the job class.
• A resource plan enables users to prioritize resources (most notably CPU) among
resource consumer groups.

1Z0-043 Notes – CH 15 – Database Security

Oracle Transparent Data Encryption (TDE)

• Need for secure information
• Automatic encryption of sensitive information:
– Embedded in the Oracle database
– No need to change application logic
– Encrypts data and index values
• Using an encryption key:
– Master key for the entire database
– Stored in Oracle Wallet

Implementing Transparent Data Encryption

1. Create a wallet: automatically or by using Oracle Wallet Manager (define ENCRYPTION_WALLET_LOCATION in sqlnet.ora)
2. Set the master key from within your instance – ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY <password>;
3. Open the wallet from within your instance – ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY <password>;
4. Create tables that contain encrypted columns – CREATE TABLE emp ( salary NUMBER(6) ENCRYPT USING ’3DES168′, comm NUMBER(6) ENCRYPT);

Existing Tables and TDE

• Add encrypted columns:  ALTER TABLE emp ADD (ssn VARCHAR2(11) ENCRYPT);
• Encrypt unencrypted columns: ALTER TABLE emp MODIFY (first_name ENCRYPT);
• Disable column encryption: ALTER TABLE emp MODIFY (first_name DECRYPT);
• Add or remove salt: ALTER TABLE emp MODIFY (first_name ENCRYPT [NO] SALT);
• Change keys and the encryption algorithm:  ALTER TABLE emp REKEY USING ’3DES168′;

TDE consideration

1. You cannot encrypt tables owned by SYS.
   LONG and LOB data types are not supported.
2. Best practice tip: Back up the wallet.

Wallet Support for Usernames and Passwords
• Wallets can now hold more than just a certificate:
– You can store usernames and passwords in a wallet rather than providing them on the command line.
• Batch job processing:
– Protects exposure of usernames and passwords when listing processes on the OS :  connect /@db_connect_string
• Set up using:
– WALLET_LOCATION in sqlnet.ora
– mkstore utility

RMAN Encrypted Backups

Three possible encryption modes for your backups:
• Transparent mode:
– Requires Oracle Wallet
– Is best suited for day-to-day backup and restore operations at the same location
– Is the default encryption mode
• Password mode:
– Requires you to provide a password
– Is best suited for backups restored at remote locations
• Dual mode:
– Can use either Oracle Wallets or passwords
– Is best suited for backups restored locally and remotely

RMAN Transparent Mode  setup

1. Create a wallet: automatically or by using Oracle Wallet Manager.
2. Open the wallet from within your instance: ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY <password>;
3. Set the master key from within your instance: ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY <password>;
4. Configure RMAN to use transparent encryption: CONFIGURE ENCRYPTION FOR DATABASE ON
5. There are no changes to your backup or recover commands.
6. Permanent configuration can be temporarily overwritten: SET ENCRYPTION OFF

RMAN Password Mode Setup

1. Set your RMAN session to use password encryption: SET ENCRYPTION ON IDENTIFIED BY password ONLY
2. There are no changes to your backup commands.
3. Set your RMAN session to decrypt passwordencrypted backups: SET DECRYPTION IDENTIFIED BY password1
4. There are no changes to your recover commands.

RMAN-Encrypted Backups Considerations
• Image copy backups cannot be encrypted.
• COMPATIBLE must be set to at least 10.2.0.
• V$RMAN_ENCRYPTION_ALGORITHMS contains the list of possible encryption algorithms. (use CONFIGURE or SET in RMAN)

• Backup encryption is available only with Oracle Database Enterprise Edition.

Virtual Private Database: Features
• Column-level VPD enforces row-level access control based on accessed security columns.
• With customization, you can define static and nonstatic policies.
• Using shared policies, you can associate one policy with multiple objects.
• Policy type can be INDEX.
• Policy predicate text string can be of size 32 KB.

Creating a Column-Level Policy
1. Grant the privilege.
2. Create the function.
3. Apply the policy to the object.
BEGIN
dbms_rls.add_policy(object_schema => ‘hr’,
object_name => ‘employees’,
policy_name => ‘hr_policy’,
function_schema =>’hr’,
policy_function => ‘hrsec’,
statement_types =>’select,insert’,
sec_relevant_cols=>’salary,commission_pct’);
END;
/

1Z0-043 Notes – CH 16 – Using Globalization Support

What Is a Character Set?

The Oracle database provides different classes of encoding schemes:

Single-Byte Character Set

• 7-bit character set   — US7ASCII
• 8-bit character set  — WE8ISO8859P1, WE8DEC

Multibyte Character Sets   — JA16SJIS, AL32UTF8(a strict superset of ASCII)

* A strict superset means that each and every character in 7-
bit ASCII is available in AL32UTF8 with the same corresponding codepoint value.

Unicode

• AL16UTF16 encoding is the 16-bit encoding of Unicode. AL16UTF16 is the main Unicode encoding that is used
  by both Microsoft Windows 2000 and Windows XP.
• Supplementary Characters : The initial version of Unicode used the 2-byte encoding format. By using 16 bits for every
  code point, a total of 65,536 characters can be represented. However, there is a need to support
  many more characters

Notice:  above that UTF-16 and UTF-8, with hyphens, refer to the Unicode Standard
encodings, whereas UTF8, AL32UTF8, and AL16UTF16, without hyphens, refer to Oracle
database character sets based on the Unicode Standard.

How Are Character Sets Used?

• Oracle Net compares the client NLS_LANG setting to the character set on the server.
• If needed, conversion occurs automatically and transparently.

Specifying Language-Dependent Behavior for the Session

NLS_LANG=<language>_<territory>.<charset>  e.g. NLS_LANG=FRENCH_CANADA.WE8ISO8859P1

Database Character Sets and National Character Sets

database character set must have either EBCDIC or 7-bit ASCII as a subset

A national character set is an alternate character set that enables you to store Unicode character data in a database that does not have a Unicode database character set. SQL NCHAR, NVARCHAR2, and NCLOB data types support Unicode data only. You can use either the UTF8 or the AL16UTF16 character set.

Obtaining Character Set Information

NLS_DATABASE_PARAMETERS

Linguistic Searching and Sorting

Three types of sorting:
• Binary sorting

– Sorted according to the binary values of the encoded characters
• Monolingual linguistic sorting

– A two-pass sort based on a character’s assigned major and minor values
• Multilingual linguistic sorting

– Based on the ISO standard (ISO 14651), and the Unicode 3.2 Standard for multilingual collation
– Ordered by the number of strokes, PinYin, or radicals for Chinese characters

You can specify the type of sort used for character data with the:
• NLS_SORT parameter
– Default value derived from the NLS_LANG environment variable, if set
– Can be specified for the session, client, or server
• NLSSORT function
– Defines the sorting method at the query level

note :

NLS_COMP – Can be set to ANSI or BINARY. When NLS_COMP is set to ANSI, SQL operations perform a linguistic comparison based on the value of NLS_SORT.

Case- and Accent-Insensitive Search and Sort

e.g. NLS_SORT = FRENCH_M_AI, NLS_SORT = XGERMAN_CI

Linguistic Index Support

• Create an index on linguistically sorted values.
• Rapidly query data without having to specify ORDER BY clause and NLSSORT:
• Set the NLS_SORT parameter to match the linguistic definition that you want to use for the linguistic sort when creating the index.

NLS Data Conversion with Oracle Utilities

Multiple data conversions can take place when data is exported from one database and imported into another if the same character sets are not used.
• External tables use the NLS settings on the server for determining the data character set.
• SQL*Loader:
– Conventional path: Data is converted into the session character set specified by NLS_LANG.
– Direct path: Data is converted using client-side directives.

NLS Data Conversion with Data Pump

• Data Pump Export always saves data in the same character set as the database from which the data originates.
• Data Pump Import converts the data to the character set of the target database, if needed.
• The Data Pump log file is written in the language specified by NLS_LANG for the session that started Data Pump.