Chapter 13 Configuring Database Diagnostics

Set Up Automatic Diagnostic Repository
ADR always-on facility to capture errors to trace or dump files and package it to send to Oracle Support. Trace file is continuous output. Dump file is one-time output.
errors -> problems -> incidents
ADR Base can have multiple ADR Home
ADR Base would be set as initialization parameter, DIAGNOSTIC_DEST. If not, it would be set as environment variable, ORACLE_BASE. If not, it would be set as ORACLE_HOME/log.

select * from v$diag_info;

ADRCI command
or
Support Workbench: problem detection, documentation, resolution.
View details:- Problems, Incidents
Run Health Checks
Generate Diagnostic Data
Run Advisor
Package: Quick or Custom

1. alert: warning or critical
2. problem: key
3. incident: ID

Health Monitor -> Proactive or Reactive
Check:
Datafile, Memory, Transaction Integrity, Metadata, Process Usage
DBMS_HM
V$HM_CHECK, V$HM_RUN

ADRCI>show hm_run, create report hm_run, show report hm_run

Recover Individual Blocks
DB_BLOCK_CHECKING: OFF/False (default), LOW, MEDIUM:-Non-IOT, FULL/True:-Index
Block version, address, checksum (ORA-01578 data block corruption error)
RECOVER... BLOCK:
recover datafile # block #;
recover corruption list;

RMAN Backup/Validate
Analyze Table/Index
DBVERIFY
SQL
List Failure

V$DATABASE_BLOCK_CORRUPTION

Data Recovery Advisor
List Failure #/ALL/Critical, High, Low, Closed
Advice Failure
Repair Failure
Change Faiure # Priority Low/High

V$IR_FAILURE
V$IR_MANUAL_CHECKLIST
V$IR_REPAIR

Chapter 14 Using the Scheduler for Task Automation

Create a Job, Program, and Schedule
Understanding the Scheduler Architecture

DBA_SCHEDULER_JOBS view
CJQ0 monitor this view and lauches job slaves, the Jnnn processes, to run the jobs.
JOB_QUEUE_PROCESSES=0 Scheduler cannot run and will not function.
V$PROCESS
DBMS_SCHEDULER package

Jobs
what to do:
single SQL statement
a PL/SQL block
a PL/SQL, Java or external C stored procedure
any executable file: command, binary file, shell script or batch file

when to do:
timestamp at which to launch the job
repeat interval for future runs

DBMS_SCHEDULER.CREATE_JOB
JOB_NAME: unique within the schema

Program (PROGRAM_NAME)
JOB_TYPE: PLSQL_BLOCK, STORED_PROCEDURE, EXECUTABLE
JOB_ACTION: based on job_type
NUMBER_OF ARGUMENTS: arguments for job_action

Schedule (SCHEDULE_NAME)
START_DATE: first execution
REPEAT_INTERVAL: repeat frequency such as daily
END_DATE

JOB_CLASS: has to do with priorities and integration of the Scheduler with Resource Manager
ENABLED: default to FALSE
AUTO_DROP: default to TRUE
COMMENTS

Programs
DBMS_SCHEDULER.CREATE_PROGRAM
PROGRAM_NAME
PROGRAM_TYPE
PROGRAM_ACTION
NUMBER_OF_ARGUMENTS
ENABLED
COMMENTS

Schedules
DBMS_SCHEDULER.CREATE_SCHEDULE
SCHEDULE_NAME
START_DATE: default to the current date and time
REPEAT_INTERVAL: freq=, interval=, specifier=
END_DATE: without it, run forever
COMMENTS

Frequency:
YEARLY, MONTHLY, WEEKLY, DAILY, HOURLY, MINUTELY, SECONDLY

Specifier:
BYMONTH, BYWEEKNO, BYYEARDAY, BYMONTHDAY, BYHOUR, BYMINUTE, BYSECOND

eg. repeat_interval=>'freq=weekly,; interval=2; byday=mon; byhour=6; byminutes=10'

Job Classes
to associate one or more jobs with a Resource Manager comsumer group, and also to control logging levels.
DBMS_SCHEDULER.CREATE_JOB_CLASS
JOB_CLASS_NAME
RESOURCE_CONSUMER_GROUP
SERVICE
LOGGING_LEVEL
LOG_HISTORY
COMMENTS

The RESOURCE_CONSUMER_GROUP nominates the group whose resource allocations should be applied to the running job, as determined by the Resource Manager plan in effect.

Windows
A window opens at a certain time and closes after a certain duration.
DBMS_SCHEDULER.CREATE_WINDOW
WINDOW_NAME
RESOURCE_PLAN
START_DATE
REPEAT_INTERVAL
END_DATE
DURATION INTERVAL DAY TO SECOND ' ::'
WINDOW_PRIORITY: LOW (default), HIGH
COMMENTS

Privileges
SCHEDULER_ADMIN includes the followings:

• CREATE JOB
• CREATE ANY JOB
• EXECUTE ANY PROGRAM
• EXECUTE ANY CLASS
• MANAGE SCHEDULER

It is granted to SYSTEM with ADMIN by default


Event-Based Jobs:-
CREATE_JOB
event_condition
queue_spec

DBMS_SCHEDULER.ENABLE('JOBNAME');
DBMS_SCHEDULER.RUN_JOB('JOBNAME');


Create Lightweight Jobs
ideal for running many short-duration jobs that run frequently.
using predefined Job Template, which can be a PROGRAM (plsql_block or stored_procedure).
inherit privileges from the template's program

CREATE_JOB
job_style: LIGHTWEIGHT, By default, it is REGULAR


Use Job Chains to Perform a Series of Related Task
Understanding Job Chains

1. CREATE_CHAIN (chain_name, comments)
2. DEFINE_CHAIN_STEP or DEFINE_CHAIN_EVENT_STEP (chain_name, step_name, program_name)
3. DEFINE_CHAIN_RULE (chain_name, rule_name, condition, action, comments)
4. ENABLE('chain_name')
5. CREATE_JOB with job_type of CHAIN

RUN_JOB or RUN_CHAIN(chain_name, job_name, start_steps)

Monitoring Job Chains using the following views:-
*_SCHEDULER_CHAINS
*_SCHEDULER_CHAIN_STEPS
*_SCHEDULER_CHAIN_RULES
*_SCHEDULER_RUNNING_CHAINS

Note that all jobs that do not specify a job class are assigned the job class DEFAULT_JOB_CLASS.

What happens if a window closes before a job that is run in the window has completed.
STOP_ON_WINDOW_CLOSE=FALSE (default)
The default behavior is that the job will continue until it finishes.

STOP_ON_WINDOW_CLOSE=TRUE
The job will abort if it has not finished by the time its window closes.

Use Advanced Scheduler Concepts to Prioritize Jobs
Level 1 Class Level: Resource Manager with different class of jobs based on the consumer group
Level 2 Job Level: Scheduler within the class based on job priority

So job with priority 1 might still run after job with priority 5 in another class if the second job's class is in a consumer group with a higher Resource Manager priority.

DBMS_SCHEDULER.SET_ATTRIBUTE(
name=>'job_name',
attribute=>'job_priority',
value=>2);

default is 3, range from 1 (highest) to 5.

DBMS_SCHEDULER.LOGGING_OFF
DBMS_SCHEDULER.LOGGING_RUNS
DBMS_SCHEDULER.LOGGING_FULL

DBA_SCHEDULER.JOB_LOG
DBA_SCHEDULER.JOB_RUN_DETAILS

Chapter 15 Database Globalization

Globaliztion Capabilities
Character Sets :-
US7ASCII Single-byte seven-bit
WE8ISO8859P15 Single-byte eight-bit
AL16UTF16 Unicode fixed-width two-byte character set
AL32UTF8 Unicode varying_width character set
UTF8 Unicode varying_width character set ranging from one to four bytes (standard on Unix systems)

Language Support
select * from v$nls_valid_values where parameter='LANGUAGE';
NLS_LANGUAGE default AMERICAN
NLS_DATE_LANGUAGE default AMERICAN
NLS_SORT default BINARY
select * from v$nls_valid_values where parameter='SORT';

Territory Support
select * from v$nls_valid_values where parameter='TERRITORY';
NLS_TERRITORY
NLS_CURRENCY, NLS_DUAL_CURRENCY, NLS_ISO_CURRENCY
NLS_DATE_FORMAT, NLS_TIMESTAMP_FORMAT, NLS_TIMESTAMP_TZ_FORMAT
NLS_TIME_FORMAT, NLS_TIME_TZ_FORMAT
NLS_NUMERIC_CHARACTERS: .,

Other NLS Settings
NLS_CALENDAR
NLS_COMP: BINARY / ANSI
NLS_LENGTH_SEMANTICS: BYTE / CHARACTER
NLS_NCHAR_CONV_EXCP: FALSE / TRUE for error message conversion between VARCHAR2 and NVARCHAR

NLS_CHARACTERSET
NLS_NCHAR_CHARACTERSET
NLS_RDBMS_VERSION


Using Globalization Support Features

1. The database: nls_database_parameters
2. The instance: nls_instance_parameters
3. The client environment: NLS_LANG=language_territory.characterset
4. The session: V$nls_parameters, nls_session_parameters
5. The statement

Database Character Set default US7ASCII / OS default NLS setting
National Character Set + Unicode default AL16UTF16

Changing Character Sets
Database Character Set Scanner:
csscan system/password full=y tochar=utf8 (run csminst.sql script first)
Language and Character Set File Scanner: lcsscan to scan text file to guess character set

Work with Database and NLS Character Sets
Linguistic Sorting and Selection
Linguistic Sort: numeric value that reflects its correct position in the sequence appropriate to the language in use.

Monolingual Sort: FRENCH
first pass based on the "major" value of each character (Uppercase with no accents)
second pass using the "minor" value (Case and diacritic sensitive)

Multilingual Sort: FRENCH_M

Case and diacritic insensitive Sort:
NLS_SORT=GENERIC_BASELETTER
NLS_COMP=ANSI

Accent insensitive Sort: Suffixed with _AI
Case insensitive Sort: Suffixed with _CI

Locale Builder: lbuilder

Using Time Zones
Timestamp with time zone: Not normalize to the database time zone. time zone indicator
Timestamp with local time zone: Normalize to the database time zone. converted to client time zone on retrieval.
Timestamp: Normalize to the database time zone. Display unchanged.
Date: Normalize to the database time zone. Display unchanged.

Client time zone default to that of the client operating system or it can be set with environment variable ORA_STDZ

ALTER DATABASE SET TIME_ZONE=
ALTER SESSION SET TIME_ZONE=
V$TIMEZONE_NAMES

Free Software

OS: Linux: CentOS, Fedora, etc
DB: PostgreSQL
Software Development Kit: Eclipse
Language: Java
Java Container: Tomcat
Web Server: Apache


Not Free
Software Development Kit: Visual Studio 2005, 2008
Language: C#.Net