Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]

Search This Blog

Monday, October 31, 2011

OSW creating web profile "exit value was 2" "Exception encountered when trying to create new file under profile."

Installed OSW on our unix host and tried to run some reports from the archive data but getting the weird errors.

OS Watcher User Guide [ID 301137.1]
OS Watcher Graph (OSWg) User Guide [ID 461053.1]
OSW System Profile - Sample [ID 461054.1]


Here I am not briefing how to install/deploy the OSW but discussing how to view the OSW data using the OSWg and while doing this, what are all the errors I have experienced.

I tried to create the profile using my OSW archive data.

Here I used option R initially to be sure it won't complain or pick up any previous gif files.

myunixhost>(MYPRODDB) /export/home/oracle/cron/osw
>java -jar oswg.jar -i /export/home/oracle/cron/osw/archive

Starting OSWg V3.0.1
OSWatcher Graph Written by Oracle Center of Expertise
Copyright (c) 2008 by Oracle Corporation

Parsing Data. Please Wait...

Parsing file myunixhost>_iostat_11.10.30.0100.dat ...
Parsing file myunixhost>_iostat_11.10.30.0200.dat ...

Parsing file myunixhost>_vmstat_11.10.31.1200.dat ...

Parsing Completed.


Enter 1 to Display CPU Process Queue Graphs
Enter 2 to Display CPU Utilization Graphs
Enter 3 to Display CPU Other Graphs
Enter 4 to Display Memory Graphs
Enter 5 to Display Disk IO Graphs

Enter 6 to Generate All CPU Gif Files
Enter 7 to Generate All Memory Gif Files
Enter 8 to Generate All Disk Gif Files

Enter L to Specify Alternate Location of Gif Directory
Enter T to Specify Different Time Scale
Enter D to Return to Default Time Scale
Enter R to Remove Currently Displayed Graphs
Enter P to Generate A Profile
Enter Q to Quit Program

Please Select an Option:R


Enter 1 to Display CPU Process Queue Graphs
Enter 2 to Display CPU Utilization Graphs
Enter 3 to Display CPU Other Graphs
Enter 4 to Display Memory Graphs
Enter 5 to Display Disk IO Graphs

Enter 6 to Generate All CPU Gif Files
Enter 7 to Generate All Memory Gif Files
Enter 8 to Generate All Disk Gif Files

Enter L to Specify Alternate Location of Gif Directory
Enter T to Specify Different Time Scale
Enter D to Return to Default Time Scale
Enter R to Remove Currently Displayed Graphs
Enter P to Generate A Profile
Enter Q to Quit Program

---Now when I tried to create the profile it complained directory already exists.. ouch
I don't even have a directory here so how come you are complaining me about this.

Please Select an Option:P
Enter a unique profile directory name or enter to accept default name:
exit value was 2
cmd was mkdir profile/Oct30010017_1320079500
This directory already exists. Rewriting...
exit value was 2
cmd was cp src/OSW_profile.htm profile/Oct30010017_1320079500
This directory already exists. Rewriting...
exit value was 2
cmd was mkdir profile/Oct30010017_1320079500/OSW_profile_files
This directory already exists. Rewriting...
exit value was 2
cmd was cp src/coe_logo.gif profile/Oct30010017_1320079500/OSW_profile_files
This directory already exists. Rewriting...
exit value was 2
cmd was cp src/missing_graphic.gif profile/Oct30010017_1320079500/OSW_profile_files
This directory already exists. Rewriting...
exit value was 2
cmd was cp src/watch.gif profile/Oct30010017_1320079500/OSW_profile_files
This directory already exists. Rewriting...
exit value was 2
cmd was cp src/tombody.gif profile/Oct30010017_1320079500/OSW_profile_files
This directory already exists. Rewriting...
Exception encountered when trying to create new file under profile.




Enter 1 to Display CPU Process Queue Graphs
Enter 2 to Display CPU Utilization Graphs
Enter 3 to Display CPU Other Graphs
Enter 4 to Display Memory Graphs
Enter 5 to Display Disk IO Graphs

Enter 6 to Generate All CPU Gif Files
Enter 7 to Generate All Memory Gif Files
Enter 8 to Generate All Disk Gif Files

Enter L to Specify Alternate Location of Gif Directory
Enter T to Specify Different Time Scale
Enter D to Return to Default Time Scale
Enter R to Remove Currently Displayed Graphs
Enter P to Generate A Profile
Enter Q to Quit Program

Please Select an Option:Q
I searched the whole host to look for this profile directory existence but couldn't find one so I am pretty sure this is not the case.

so created the directory called "profile" under osw_home and tried again.


myunixhost>(MYPRODDB) /export/home/oracle/cron/osw
>mkdir profile
myunixhost>(MYPRODDB) /export/home/oracle/cron/osw
>java -jar oswg.jar -i /export/home/oracle/cron/osw/archive

Starting OSWg V3.0.1
OSWatcher Graph Written by Oracle Center of Expertise
Copyright (c) 2008 by Oracle Corporation

Parsing Data. Please Wait...

Parsing file myunixhost>_iostat_11.10.30.0100.dat ...
Parsing file myunixhost>_iostat_11.10.30.0200.dat ...
Parsing file myunixhost>_vmstat_11.10.31.1000.dat ...
Parsing file myunixhost>_vmstat_11.10.31.1100.dat ...
Parsing file myunixhost>_vmstat_11.10.31.1200.dat ...

Parsing Completed.



Enter 1 to Display CPU Process Queue Graphs
Enter 2 to Display CPU Utilization Graphs
Enter 3 to Display CPU Other Graphs
Enter 4 to Display Memory Graphs
Enter 5 to Display Disk IO Graphs

Enter 6 to Generate All CPU Gif Files
Enter 7 to Generate All Memory Gif Files
Enter 8 to Generate All Disk Gif Files

Enter L to Specify Alternate Location of Gif Directory
Enter T to Specify Different Time Scale
Enter D to Return to Default Time Scale
Enter R to Remove Currently Displayed Graphs
Enter P to Generate A Profile
Enter Q to Quit Program

Please Select an Option:P
Enter a unique profile directory name or enter to accept default name:
Warning: Cannot convert string "-monotype-arial-regular-r-normal--*-140-*-*-p-*-iso8859-1" to type FontStruct
Java Accessibility Bridge for GNOME loaded.

Generating file profile/Oct30010017_1320079793/OSW_profile_files/OSWg_OS_Run_Queue.gif
Generating file profile/Oct30010017_1320079793/OSW_profile_files/OSWg_OS_Block_Queue.gif
Generating file profile/Oct30010017_1320079793/OSW_profile_files/OSWg_OS_Wait_Queue.gif
Generating file profile/Oct30010017_1320079793/OSW_profile_files/OSWg_OS_Cpu_Idle.gif
Generating file profile/Oct30010017_1320079793/OSW_profile_files/OSWg_OS_Cpu_System.gif
Generating file profile/Oct30010017_1320079793/OSW_profile_files/OSWg_OS_Cpu_User.gif
Generating file profile/Oct30010017_1320079793/OSW_profile_files/OSWg_OS_Cpu_Interrupts.gif
Generating file profile/Oct30010017_1320079793/OSW_profile_files/OSWg_OS_Context_Switches.gif
Generating file profile/Oct30010017_1320079793/OSW_profile_files/OSWg_OS_Memory_Swap.gif
Generating file profile/Oct30010017_1320079793/OSW_profile_files/OSWg_OS_Memory_Free.gif
Generating file profile/Oct30010017_1320079793/OSW_profile_files/OSWg_OS_Memory_Scan_Rate.gif
Generating file profile/Oct30010017_1320079793/OSW_profile_files/OSWg_OS_IO_ST.gif
Generating file profile/Oct30010017_1320079793/OSW_profile_files/OSWg_OS_IO_RPS.gif
Generating file profile/Oct30010017_1320079793/OSW_profile_files/OSWg_OS_IO_WPS.gif
Generating file profile/Oct30010017_1320079793/OSW_profile_files/OSWg_OS_IO_PB.gif




Enter 1 to Display CPU Process Queue Graphs
Enter 2 to Display CPU Utilization Graphs
Enter 3 to Display CPU Other Graphs
Enter 4 to Display Memory Graphs
Enter 5 to Display Disk IO Graphs

Enter 6 to Generate All CPU Gif Files
Enter 7 to Generate All Memory Gif Files
Enter 8 to Generate All Disk Gif Files

Enter L to Specify Alternate Location of Gif Directory
Enter T to Specify Different Time Scale
Enter D to Return to Default Time Scale
Enter R to Remove Currently Displayed Graphs
Enter P to Generate A Profile
Enter Q to Quit Program

Please Select an Option:Q

Nice this time it created the profile without a hitch.. damn it no where in the OSWg guide it says to create the profile directory. Anyway this is how I resolved the errors.

myunixhost>(MYPRODDB) /export/home/oracle/cron/osw


Now cd to the profile directory you just created and browse underneath to see the newly created directory with the webpage and stats files. Remember you need to copy both the webpage and the profile_files in order to view the we page and its contents.


>cd profile
/export/home/oracle/cron/osw/profile
myunixhost>(MYPRODDB) /export/home/oracle/cron/osw/profile
>ls -lrt
total 2
drwxr-xr-x 3 oracle oinstall 512 Oct 31 12:49 Oct30010017_1320079793
myunixhost>(MYPRODDB) /export/home/oracle/cron/osw/profile
>cd Oct30010017_1320079793
/export/home/oracle/cron/osw/profile/Oct30010017_1320079793
myunixhost>(MYPRODDB) /export/home/oracle/cron/osw/profile/Oct30010017_1320079793
>ls -lrt
total 46
-rw-r--r-- 1 oracle oinstall 22188 Oct 31 12:49 OSW_profile.htm
drwxr-xr-x 2 oracle oinstall 1024 Oct 31 12:49 OSW_profile_files

Thursday, October 20, 2011

11gR2 Flashback & restore point

Turn on flashback in 11gR2 database and create a restore point.

Stop you Ebiz apps if any:( Not needed but to have clean one before we start our testing)

adstpall.sh:Exiting with status 0



SQL> alter database flashback on;

Database altered.

SQL> SELECT flashback_on, log_mode FROM v$database;

FLASHBACK_ON LOG_MODE
------------------ ------------
YES ARCHIVELOG

SQL> SELECT name, scn, time, database_incarnation#, guarantee_flashback_database, storage_size FROM gv$restore_point;

no rows selected

SQL> show parameter flashback

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 1440

change the retention period to 24 hrs ---

SQL> alter system set db_flashback_retention_target=86400 scope=both sid='*';

System altered.

SQL> show parameter flashback

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 86400


SQL> SELECT name, scn, time, database_incarnation#, guarantee_flashback_database, storage_size FROM gv$restore_point;

no rows selected

create a retore point here:

SQL> create restore point benchmark_10202011;

Restore point created.


SQL> set linesize 121
col name format a15
col time format a32SQL> SQL>
SQL>
SQL> SELECT name, scn, time, database_incarnation#, guarantee_flashback_database, storage_size FROM gv$restore_point;

NAME SCN TIME DATABASE_INCARNATION# GUA STORAGE_SIZE
--------------- ---------- -------------------------------- --------------------- --- ------------
BENCHMARK_10202 1.2476E+13 20-OCT-11 02.54.56.000000000 PM 2 NO 0
011

BENCHMARK_10202 1.2476E+13 20-OCT-11 02.54.56.000000000 PM 2 NO 0
011


SQL> alter session set nls_date_format='mm/dd/yyy hh24:mi:ss';

Session altered.

SQL> SELECT name, scn, time, database_incarnation#, guarantee_flashback_database, storage_size FROM gv$restore_point;

NAME SCN TIME DATABASE_INCARNATION# GUA STORAGE_SIZE
--------------- ---------- -------------------------------- --------------------- --- ------------
BENCHMARK_10202 1.2476E+13 20-OCT-11 02.54.56.000000000 PM 2 NO 0
011

BENCHMARK_10202 1.2476E+13 20-OCT-11 02.54.56.000000000 PM 2 NO 0
011


SQL> commit;

Commit complete.

SQL> alter system flush shared_pool;

System altered.

SQL> alter system flush buffer_cache;

System altered.


SQL> drop restore point benchmark_10202011;

Restore point dropped.

SQL> SELECT name, scn, time, database_incarnation#, guarantee_flashback_database, storage_size FROM gv$restore_point;

no rows selected

SQL> create restore point benchmark_QC_10202011 guarantee flashback database;

Restore point created.

SQL> col name format a25
SQL> SELECT name, scn, time, database_incarnation#, guarantee_flashback_database, storage_size FROM gv$restore_point;

NAME SCN TIME DATABASE_INCARNATION# GUA STORAGE_SIZE
------------------------- ---------- -------------------------------- --------------------- --- ------------
BENCHMARK_QC_10202011 1.2476E+13 20-OCT-11 03.00.59.000000000 PM 2 YES 67125248



-- Going back to the defined restore point

SQL>conn / as sysdba

SQL>shutdown immediate;

SQL>startup mount;

SQL>flashback database to restore point BENCHMARK_QC_10202011;

SQL>alter database open resetlogs;

gud luck.

Wednesday, October 5, 2011

ORA-00959: tablespace '_$deleted$$0' does not exist

I just did a reorg of one of our tablespace DATA_001 and dropped the tablespace and after the re org when I tried to query one of the objects got the below error




Issues was; These objects were taking 0 blocks and hence were not picked up my re-org script from the dba_segments for re-org
so these were just sitting there without occupying any blocks but still pointing to the old tablespace which we deleted after the
re-org


select table_name,blocks from dba_tables where tablespace_name='DATA_001' and blocks='0'

this will give us the list of the objects that has 0 blocks but indeed belong to the above tablespace which we dropped.


select * from USER.USER_EMPLOYEE_ERROR move
*
ERROR at line 1:
ORA-00959: tablespace '_$deleted$25$0' does not exist

so tried move this table to new tablespace

SQL> alter table USER.USER_EMPLOYEE_ERROR move tablespace DATA_001;
alter table USER.USER_EMPLOYEE_ERROR move tablespace DATA_001
*
ERROR at line 1:
ORA-00959: tablespace '_$deleted$25$0' does not exist


how about atleast move..

SQL> alter table USER.USER_EMPLOYEE_ERROR move;
alter table USER.USER_EMPLOYEE_ERROR move
*
ERROR at line 1:
ORA-00959: tablespace '_$deleted$25$0' does not exist


SQL> select tablespace_name from dba_tablespaces where tablespace_name like '%deleted%';

no rows selected


where as I can still see the objects in the database. So decided to create the ddl scripts via TOAD
and then dropped these objects and recreated them.



drop table USER.USER_EMPLOYEE_ERROR purge; <-- without purge we cannot drop the table
create table USER.USER_EMPLOYEE_ERROR (col a,b,c..);

Monday, October 3, 2011

Global AWR report for RAC and AWR snapshot interval change

In 11gR2 we have two new scripts awrgrpt.sql & awrgdrpt.sql for RAC

awrsqrpt.sql -- Standard SQL statement Report
awrddrpt.sql -- Period diff on current instance
awrrpti.sql -- Workload Repository Report Instance (RAC)
awrgrpt.sql -- AWR Global Report (RAC)
awrgdrpt.sql -- AWR Global Diff Report (RAC)
spawrrac.sql  -- Server Performance RAC report
spawrio.sql
 





SQL> select extract( day from snap_interval) *24*60+extract( hour from snap_interval) *
   60+extract( minute from snap_interval ) snapshot_interval,
    extract( day from retention) retention_interval
from dba_hist_wr_control; 
SNAPSHOT_INTERVAL RETENTION_INTERVAL_DAYS
----------------- ------------------
               60                  8


Change it to 15 mins and 30 days



SQL> execute dbms_workload_repository.modify_snapshot_settings(

interval => 15,
retention => 43200);

PL/SQL procedure successfully completed.

SQL>  select extract( day from snap_interval) *24*60+extract( hour from snap_interval) *
   60+extract( minute from snap_interval ) snapshot_interval,
    extract( day from retention) retention_interval
from dba_hist_wr_control;   2    3    4
SNAPSHOT_INTERVAL RETENTION_INTERVAL
----------------- ------------------
               15                 30