Tuesday, March 12, 2013

0 Database replay from OEM

Database replay is Oracle 11g new feature and it is part of Oracle Real application testing. Another important feature of the Real Application testing is SQL Performance Analyzer (SPA) . The main difference between SPA and database replay is that we can customize the SQL set within the SPA and tune the SQL base on the SQL statement. Database replay is base on the entire system and consider the concurrence.

Create the test environment

mkdir /u01/app/oracle/db_replay
 
 
CREATE OR REPLACE DIRECTORY db_replay_dir AS '/u01/app/oracle/db_replay';
CREATE USER db_replay1 IDENTIFIED BY db_replay1 QUOTA UNLIMITED ON users;
GRANT CONNECT, CREATE TABLE TO db_replay1;

image

Monday, March 11, 2013

0 ORA-01045: user SYSMAN lacks CREATE SESSION privilege; logon denied

Somehow, my OEM seems to not able to connect to the database.  After I log in , it can see the database is open but not able to get the information.

image

I found the error message “ORA-01045: user SYSMAN lacks CREATE SESSION privilege; logon denied”.

image

After I grant the “grant create session to sysman” and bounce the EM. It just back to normal.

Still not sure how I get there in the first place.

0 The Em Key is not configured properly or is corrupted in the file system and does not exist in the Management Repository

After the host bounce, the EM is started but get  the 503 error when access the EM.

image

Emkey is random generate when EM repository is created. It looks like the hash encryption code.

image

After reviewing the EM log, it seems to be the emkey corruption issue.

image

This error message is somehow miss leading. It stands the emkey is at $ORACLE_HOME/sysman/config directory, but it is actually at $ORALE_HOME/$HOST_$SID/sysman/config directory. ( In my case , it is at /u01/app/oracle/product/11.2.0/panda2.localdomain_oradb2/sysman/config).

 

I was keep tying to import /recreate it at wrong location and without success.

image

image

In the end, I have just drop and recreate the repo

 

emca -deconfig dbcontrol db -repos drop
emca -config dbcontrol db -repos create

 

After repository is recreated, I got the agent unreachable in the EM.

image

From emctl status agent, I saw the message Collection Status : Disabled by Upload Manager

I run to test the agent, it seems fine.

image

After, I pork around, it is because the agent upload drive is full. I just clean up the space and it works fine.

image

Sunday, March 10, 2013

0 Data Recovery Advisor

Data recovery advisor is the new features in 11g.

 

Data recovery Advisor related tables

  • V$IR_FAILURE: List all the failures.  Similar to the V$HM_FINDING
  • V$IR_FAILURE_SET
  • V$IR_REPAIR List the automatic repair steps
  • V$IR_MANUAL_CHECKLIST: List the manually repair steps

 

Data recovery Advisor  from OEM

 

The advise home list all the failures from V$IR_FAILURE .  Click advise button,

image

I don’t have too much failure in my test environment. The only failure and its recommendation is to open case with Oracle Smile

image

Data recovery Advisor  from RMAN

$ORACLE_HOME/bin/rman target /
LIST FAILURE;
ADVISE FAILURE;
REPAIR FAILURE PREVIEW;
REPAIR FAILURE;

List failure

image

Advise failure

image

 

Data recovery Advisor  from SQL

select * from V$IR_FAILURE;
select * from V$IR_MANUAL_CHECKLIST;

image

image

1 Oracle Database Fault Diagnosability Infrastructure/DBMS_HM

Oracle Fault Diagnosability Infrastructure consolidate all the error log and store the files in the ADR ( automatic diagnostic repository ) outside of Oracle database. Here are the error log tyeps

 

  • Alert: XML base alert log.
  • Cdump: Core dumps for the database.
  • Trace: Trace files, including the text format of alert logs
  • Incident: one for each incident.
  • Health Monitor log

Health monitor check can be manually invoke by DBMS_HM package. Here are the Health Monitor check related tables:

  • V$HM_RUN : List all the Health Check has been executed
  • V$HM_FINDING : List the finding
  • V$HM_RECOMMENDATION : List the recommendation.
  • V$HM_CHEK: List all the check can be run by DBMS_HM

List all the diagnostic log file location

 show parameter diagnostic_dest
 col Name format A22
 col value format A50
 select Name,Value from v$diag_info;

image

List the check can be perform by DBMS_HM

 SELECT name FROM V$HM_CHECK WHERE INTERNAL_CHECK = 'N'

image

Invoke DBMS_HM check

exec dbms_hm.run_check('Dictionary Integrity Check', 'MY_HM1');

image

 

List the check has been run

select run_id, name from v$hm_run;

image

 

Retrieve the report

SET LONG 100000
SET LONGCHUNKSIZE 1000
SET PAGESIZE 1000
SET LINESIZE 512
SELECT DBMS_HM.GET_RUN_REPORT('MY_HM1') FROM DUAL;

image

adrci:

adrci – Automatic diagnostic repository command line interface is the command line which we can use to retrieve the alert log , HM report.   $ORACLE_HOME/bin/adrci

 

 

Use adrci to retrieve the alert log

show alert -tail
show homes
set homepath diag/rdbms/oradb0/oradb0
show alert -tail

image

The tail is the same result if we use linux tail command to tail the alert log

image

Use adrci to retrieve the health monitor report

show report hm_run HM_RUN_1

The output is going to be the same if we view the file under HM directory .

image

 

Reference

Friday, March 8, 2013

0 Oracle TDE on tablespace

Oralce 11g has new feature that allow to encrypt entire tablespace via TDE. The major advantage for use Tablespace TDE is that, in the TDE on the column level, if that TDE column happen to be the index, the index scan will not pick the index since it would not know its value . It affect the performance. In the tablespace TDE, the TDE is on the disk level, hence there is no such problem hence reduce the encryption performance impact.

For setting up the TDE, please refer here.

Setting up the test environment

alter system set encryption wallet open IDENTIFIED BY "XXXX";
 
CREATE TABLESPACE TEST_TDE
  DATAFILE '/u01/app/oracle/oradata/oradb0/TEST_TDE01.dbf' SIZE 256K REUSE 
  AUTOEXTEND ON NEXT 64K;
CREATE TABLESPACE TEST_TDE2
  DATAFILE '/u01/app/oracle/oradata/oradb0/TEST_TDE02.dbf' SIZE 256K REUSE
  AUTOEXTEND ON NEXT 64K encryption default storage (encrypt);
 
CREATE USER tde IDENTIFIED BY tde DEFAULT TABLESPACE TEST_TDE;
ALTER USER tde QUOTA UNLIMITED ON TEST_TDE;
ALTER USER tde QUOTA UNLIMITED ON TEST_TDE2;
GRANT CONNECT TO tde;
GRANT CREATE TABLE TO tde;

image

Create test table

When we encrypt entire tablespace, the column size no longer has charset length limitation for encryption. In my testing, the encryption column can have maxim 3932 char, otherwise, we would get -- ORA-28331: encrypted column size too long for its data type

CREATE TABLE test_employee (
     first_name VARCHAR2(10),
     nick_name VARCHAR2(4000) encrypt,
     salary NUMBER(6)
) TABLESPACE TEST_TDE;
 
-- ORA-28331: encrypted column size too long for its data type
 
CREATE TABLE test_employee (
     first_name VARCHAR2(10),
     nick_name VARCHAR2(3932) encrypt,
     salary NUMBER(6)
) TABLESPACE TEST_TDE;
 
CREATE TABLE test_employee2 (
     first_name VARCHAR2(10),
     nick_name VARCHAR2(4000),
     salary NUMBER(6)
) TABLESPACE TEST_TDE2;

 

image

Because it is TDE, so it make no difference from application perspective. the data all looks the same from query.

INSERT INTO test_employee  VALUES ('SUMMER', 'PANDA_SUMMER',1000);
select * from test_employee;
INSERT INTO test_employee2  VALUES ('SUMMER', 'PANDA_SUMMER',1000);
select * from test_employee;
image

The main difference is from non-TDE table, we can find non encryption data in the HEX mode as below screenshot.

image

For TDE tablespace, the entire data file is encrypted.

image

select tablespace_name,ENCRYPTED from dba_tablespaces

image

Thursday, March 7, 2013

0 PostgresQL Magazine

Today, I come across this “PostgresQL Magazine”. This is the free open source magazine that operate by open source community.  It is great to see we have this new source to learn more about the PostgreSQL.   You can find the latest issue at http://pgmag.org  and download the previous issue in the Download tab.  They even have simplified Chinese version. ..

Wednesday, March 6, 2013

0 Resource Manager & MIXED_WORKLOAD_PLAN

List all the existing resource plan: DBA_RSRC_PLANS

select  * from DBA_RSRC_PLANS
image

Show the current resource plan

show parameter resource_manager_plan

image

 

Change the resource plan

Here are couple ways to change the resource

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'mydb_plan';
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'FORCE:mydb_plan';
DBMS_RESOURCE_MANAGER.SWITCH_PLAN

The keyword “FORCE“ and the DBMS_RESOURCE_MANAGER.SWITCH_PLAN  are enforce  the resource plan can not be changed unless sysdba manually use alter system to change the resource plan.

 

Disable the resource plan

To complete disable the resource plan, just se the resource_manager_plan to empty string.

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = '';

 

 

MIXED_WORKLOAD_PLAN

Oracle includes the MIXED_WORKLOAD_PLAN which has 2 resource group interactive_group and batch_group. Base on document here and blog post , I should able to simulate the group switch when the user process run over 60 secs.

Despite of many effort, I can not simulate that group switch. I suspect it is because I am running  the oracle on the VM.

image

0 ORA-02030: can only select from fixed tables/views

I want to grant the user ability to select from v$session. so I issue the GRANT SELECT ON v$session TO rcs1; However, I got the ORA-02030: can only select from fixed tables/views

image

It turns out I have to grant the V$SESSION is just SYNONYM. when you query the dba_objects table, the object name must be UPPER CASE.

SELECT owner, object_type FROM dba_objects WHERE object_name = 'V$SESSION';

image

The actual table for the v$session is V$_session.

select table_owner, table_name FROM dba_synonyms where synonym_name = ‘V$SESSION’;

image

Lets try to grant the permission again.

GRANT SELECT ON V_$SESSION TO rsc1;

image

Tuesday, March 5, 2013

1 Resource Manager: I/O Calibration

The new 11g feature Resource manager can let us simulate the IO load on the system by using DBMS_RESOURCE_MANAGER.CALIBRATE_IO package .

The CALIBRATE_IO simulate the actual oracle kernel operation.

In the oracle document , we need to set the ASYNC IO in order to perform the testing.

SHOW PARAMETER FILESYSTEMIO_OPTIONS
col name format a50
SELECT name, asynch_io FROM v$datafile f,v$iostat_file i
  WHERE f.file#        = i.file_no
  AND   filetype_name  = 'Data File'

image

image

However, I am able to continue the testing without error.

 

 

SET SERVEROUTPUT ON
Declare
v_max_iops PLS_INTEGER:=1;
v_max_mbps PLS_INTEGER:=1;
v_actual_latency PLS_INTEGER:=1;
begin
DBMS_RESOURCE_MANAGER.CALIBRATE_IO(
max_iops=>v_max_iops,
max_mbps=>v_max_mbps,
actual_latency=>v_actual_latency);
dbms_output.put_line('Results follow: ');
dbms_output.put_line('Max IOPS: '||v_max_iops);
dbms_output.put_line('Max MBPS: '||v_max_mbps);
dbms_output.put_line('Actual Latency: '||v_actual_latency);
end;
/

Here is the screenshot without turn on the ASYNC IO,

image

I did  another test again and turn on the ASYNC IO.

ALTER SYSTEM SET FILESYSTEMIO_OPTIONS=SETALL SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP;

image

I got better IOPS after turn on ASYNC IO.

image

Only one IO testing can be executed at a time. While testing is ongoing, we can query the v$io_calibration_status for the status.

 

Select status from v$io_calibration_status;
image

image

After the testing is completed, the status column change to READY.

image

DBA_RSRC_IO_CALIBRATE

The DBA_RSRC_IO_CALIBRATE table stores the result for the testing. The table would be purge after the database bounce.

 

select * from DBA_RSRC_IO_CALIBRATE

image

 

Reference

http://docs.oracle.com/cd/E11882_01/appdev.112/e10577/d_resmgr.htm

Monday, March 4, 2013

0 AWR Baseline

  • Moving window baseline: The baseline base on the entire set of the snapshot. The set is changing base on the “MOVING WINDOW”. MOVING WINDOW must smaller than retention period.
  • Single baseline: Baseline schedule base on the future date.
  • Repeating baseline : Define a baseline to be captured for a repeating period of time in the future

Relate tables

  • wrm$_baseline
  • wrm$_baseline_template
  • wrm$_baseline_details

 

  • DBA_HIST_BASELINE
  • DBA_HIST_BASELINE_DETAILS
  • DBA_HIST_BASELINE_TEMPLATE

 

MOVING WINDOW:wrm$_baseline

select baseline_name,baseline_type,moving_window_size from wrm$_baseline;

image

DBMS_WORKLOAD_REPOSITORY.MODIFY_BASELINE_WINDOW_SIZE can be used to modify it. See here .

Single baseline: dbms_workload_repository.create_baseline_template

Single baseline is used to define the baseline template so we can use it to capture the future workload. Fore example, if you know there is certain type of the work will be execute at Saturday afternoon.you can set it to capture the workload .

exec DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE ( start_time => to_date('20130305 19:30', 'yyyymmdd HH24:MI'), end_time =>  to_date('20130305 22:30', 'yyyymmdd HH24:MI'), baseline_name => 'singleBaseline_20130305', template_name => 'template_20130305', expiration => 30);
select template_name,baseline_name_prefix,  template_type,start_time,end_time,expiration from wrm$_baseline_template;

image

image

Repeating Baselines

BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE (
day_of_week => 'Saturday', hour_in_day => 12,
duration => 3, expiration => 30,
start_time => to_date('20130309 19:30', 'yyyymmdd HH24:MI'), 
end_time => to_date('20131231 19:30', 'yyyymmdd HH24:MI'), 
baseline_name_prefix => 'baseline_sat_reports_', 
template_name => 'template_Sat_reports');
END;
/

image

List all the template

wrm$_baseline_template  -> DBA_HIST_BASELINE_TEMPLATE

SELECT TEMPLATE_NAME,BASELINE_NAME_PREFIX,  TEMPLATE_TYPE,START_TIME,END_TIME,EXPIRATION FROM WRM$_BASELINE_TEMPLATE;
select * from DBA_HIST_BASELINE_TEMPLATE;

image

image

Drop baseline template

DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE_TEMPLATE (template_name => 'template_Sat_reports');

image

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates