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

Manage to install Oracle 11g on Windows XP Professional

Install the following items separately:
1. Install Oracle Software by OUI
2. Install Listener by netca
3. Install DB by dbca
4. Install Enterprise Manager dbconsole by emca

emca -deconfig dbcontrol db -repos drop : also do the dropping of sysman, etc. See below.
emca -config dbcontrol db -repos create

Command may be needed:
DROP USER sysman CASCADE;
DROP PUBLIC SYNONYM setemviewusercontext;
DROP ROLE mgmt_user;
DROP PUBLIC SYNONYM mgmt_target_blackouts;
DROP USER mgmt_view;

Manage to install Oracle 11g on CentOS 4.4

using Only RAM 512M PC2100 266, end of installation the system running very slow probably due to the OUI screen. restart it after long wait.

don't set the ORACLE_BASE and ORACLE_HOME first else (becos I set wrong to be /u01/app as ORACLE_BASE) the oraInventory will be same directory level as the ORACLE_BASE
/u01/oraInventory is prompted instead of /u01/app/oraInventory

ORACLE_BASE should be /u01/app/oracle instead of /u01/app
ORACLE_HOME should be $ORACLE_BASE/product/11.1.0/db_1

this is ORACLE_BASE
mkdir -p /u01/app/oracle
chown -R oracle:oinstall /u01/app
chmod -R 775 /u01/app

edit /etc/hosts 10.0.0.12 localhost.localdomain localhost
install necessary packages and configure kernel settings
NMO setuid error: run root.sh in ORACLE_HOME to solve

put ORACLE_BASE, ORACLE_HOME, PATH, LD_LIBRARY_PATH, ORACLE_SID in .bash_profile. Need logout and login to take effect of the environment variable.

Mistake made: it should be db_1 not db_01

emctl start dbconsole
lsnrctl start
startup

all working properly afterwards.

Oracle 11g Windows Installation Notes

Windows 2000 Server SP4
Microsoft Loopback Adaptor - Add device (one of the requirement to be verified)
Configure static IP: 192.168.0.1


Control Panel -> Local Security
Logon as Batch
Logon as Services

Remote Exception
Shutdown
Startup

Windows XP Professional SP3, CENTOS4 does not work when creating/starting Enterprise Manager using DBCA.

Unable to locate DLL - MSVCP60.dll
Download it from internet - http://support.microsoft.com/kb/259403
to put in c:\windows\system or c:\winnt\system folder