Coskan’s Approach to Oracle

June 11, 2009

How to use SYSMAN schema without EM

Filed under: Basics, Tips — coskan @ 4:18 pm

As you all know Oracle Enterprise Manager can provide incredible amount of information for the database by gathering information from the database. This information is not just in V$ / DBA_ or any internal table, there are also other tables under SYSMAN schema for providing information to EM repository.

on 11.1.0.6 Windows XP version for single instance EM Repository, my DB shows 681 tables under SYSMAN schema

on 10.2.0.4 HP-UX box for single instance EM Repository, my DB shows 341 tables under SYSMAN schema

As you can see it doubled up on 11G. New features brings new tables to this schema.

I could not find good documentation about the tables under SYSMAN schema but the structure of the table names are meaningfull enough to understand what they can be used for. For most of them The naming is like MGMT_X_Y. X stands for the generic name like HC (in my understanding Hardware Components/Configuration or something else ) and Y is for information under the generic root like HARDWARE_MASTER. Table full name is MGMT_HC_HARDWARE_MASTER

Lets start with this table and see what we can get from the table

SQL> @printtab "select * from sysman.MGMT_HC_HARDWARE_MASTER"
SNAPSHOT_GUID                 : B4E96AB2F8F9436E8A15B562B90E12B5
VENDOR_NAME                   : Dell Inc.
SYSTEM_CONFIG                 : Latitude D620
MACHINE_ARCHITECTURE          : x86
CLOCK_FREQ_IN_MHZ             : 133
MEMORY_SIZE_IN_MB             : 2038
LOCAL_DISK_SPACE_IN_GB        : 74.53
CPU_COUNT                     : 2
CPU_BOARD_COUNT               :
IOCARD_COUNT                  : 1
FAN_COUNT                     :
POWER_SUPPLY_COUNT            :
-----------------

As you can see Tables under SYSMAN schema can give you very nice information, which you can't find in any V$ view or DBA_ table especially for Host system.

Lets see what else we can get under MGMT_HC_%

SQL> select table_name from dba_tables where table_name like 'MGMT_HC%';

TABLE_NAME
------------------------------
MGMT_HC_SYSTEM_SUMMARY
MGMT_HC_HARDWARE_MASTER
MGMT_HC_CPU_DETAILS
MGMT_HC_IOCARD_DETAILS
MGMT_HC_NIC_DETAILS
MGMT_HC_OS_SUMMARY
MGMT_HC_OS_PROPERTIES
MGMT_HC_OS_COMPONENTS
MGMT_HC_FS_MOUNT_DETAILS
MGMT_HC_VENDOR_SW_SUMMARY
MGMT_HC_VENDOR_SW_COMPONENTS

We can get Operating system summary from MGMT_HC_OS_SUMMARY (this is my favourite on windows because it is hard to find 32bit 64 bit information even from OS itself)

SQL> @printtab "select * from sysman.MGMT_HC_OS_SUMMARY"
SNAPSHOT_GUID                 : B4E96AB2F8F9436E8A15B562B90E12B5
NAME                          : Microsoft Windows XP Workstation
VENDOR_NAME                   : Microsoft Corporation
BASE_VERSION                  : 5.1
UPDATE_LEVEL                  : Service Pack 2
DISTRIBUTOR_VERSION           : N/A
MAX_SWAP_SPACE_IN_MB          : 3933
ADDRESS_LENGTH_IN_BITS        : 32-bit
PATCHES                       : 152
-----------------

PL/SQL procedure successfully completed.

on HP-UX same table shows

SQL>  @printtab "select * from sysman.MGMT_HC_OS_SUMMARY"
SNAPSHOT_GUID                 : 66E304347A471561E044000000000000
NAME                          : HP-UX
VENDOR_NAME                   : Hewlett-Packard Co.
BASE_VERSION                  : B.11.11
UPDATE_LEVEL                  : U
DISTRIBUTOR_VERSION           : N/A
MAX_SWAP_SPACE_IN_MB          : 15598.066
ADDRESS_LENGTH_IN_BITS        : 64
-----------------

Do you want to learn which operating system fixes are installed on the box, then MGMT_HC_OS_COMPONENTS is the right table for you

SQL> @printtab "select * from sysman.MGMT_HC_OS_COMPONENTS where rownum<4"
SNAPSHOT_GUID                 : B4E96AB2F8F9436E8A15B562B90E12B5
NAME                          : KB867282
TYPE                          : Patch
VERSION                       :
DESCRIPTION                   : Windows XP Hotfix - KB867282
INSTALLATION_DATE             :
-----------------
SNAPSHOT_GUID                 : B4E96AB2F8F9436E8A15B562B90E12B5
NAME                          : KB873333
TYPE                          : Patch
VERSION                       :
DESCRIPTION                   : Windows XP Hotfix - KB873333
INSTALLATION_DATE             :
-----------------
SNAPSHOT_GUID                 : B4E96AB2F8F9436E8A15B562B90E12B5
NAME                          : KB873339
TYPE                          : Patch
VERSION                       :
DESCRIPTION                   : Windows XP Hotfix - KB873339
INSTALLATION_DATE             :
-----------------

PL/SQL procedure successfully completed.

As you see these tables are there and ready to be used. There are too many other tables under SYSMAN schema, which might have valuable info for your needs. I think my duty is finished here, remaining is homework for you, to discover the information under the tables listed via this sql command


select table_name from dba_tables where owner='SYSMAN' and num_rows>0;

June 3, 2009

Too many trace files on 11G

Filed under: Tips — coskan @ 9:30 am

On 11G user and system traces are on the same directory (%DIAGNOSTIC_DEST%\diag\rdbms\SID\trace on my machine) and automatic health monitoring  looks like a bit more active than 10G. On 10G I wasn’t seeing automatic trace generation for user sessions, however on 11.1.0.6 Oracle nearly creates trace for every session and  it realy drives me mad when I am working on 10046 traces.  I checked unsupported parameters and I found _disable_health_check parameter. When you set this parameter to TRUE a Oracle stops generating trace files for every user session. Parameter needs restart of database.

It looks like there are 32 other diagnostic related parameters on 11G but _disable_health_check
was enough to solve my problem.

I can see this parameter is available on 10GR2 and 11GR1 but not available at least on 9.2.0.4

!!!!! As you see, it is unsupported dont use it on any production system without asking Oracle Support.

March 11, 2009

Alter user identified by values on 11G without using SYS.USER$

Filed under: Security, Tips — coskan @ 11:13 am

You probably know that on Oracle 11G, hash values of passwords, are no longer shown in dba_users table, so to change the password temporarily, it is not possible to use the way I explained here before.

On Oracle 11G, there is one method posted by Laurent Schneider which is, to use SYS.USER$ table to extract hash value of the password.

Another method, I tried and got success, is using DBMS_METADATA.GET_DDL function without going to any other dictionary tables.

DBA Session — check the output of DBMS_METADATA.GET_DDL

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0      Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

SQL> drop user coskan cascade;

User dropped.

SQL> create user coskan identified by oracle;

User created.

SQL> grant create session to coskan;

Grant succeeded.

SQL>  select dbms_metadata.get_ddl('USER','COSKAN') from dual;

DBMS_METADATA.GET_DDL('USER','COSKAN')
------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------

   CREATE USER "COSKAN" IDENTIFIED BY 
VALUES 'S:1F0648E7E665F0A0EE44B1E9BD4B626A77CA25B376A49177F9E97DF98BFA;26EB15F771A78542'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP"


As you see gathering DDL of the user gives us the hash value

On the User session ---try connection

SQL> connect coskan/oracle
Connected.
SQL>

DBA Session ---change user password

SQL> alter user coskan identified by msssql;

User altered.

User Session ---connect with new password

SQL> connect coskan/msssql
Connected.

DBA Session -- Update the user password with the value you gathered by DBMS_METADATA.GET_DDL

SQL> alter user coskan identified by
  2  VALUES 'S:1F0648E7E665F0A0EE44B1E9BD4B626A77CA25B376A49177F9E97DF98BFA;26EB15F771A78542';

User altered.

User Session -- Try the old password

SQL> connect coskan/oracle
Connected.
SQL>

I think this option is a bit easier than extracting from table approach.

Update-01/12/11 After I read the question on this oracle-l question http://www.freelists.org/post/oracle-l/11202-setting-password I think I need to mention that you need to have "SET LONG 999999" setting for your sqlplus env

March 5, 2009

What I learned during Oracle SQL Expert Exam Study Part-1

Filed under: Basics, Certification, PL/SQL, Tips — coskan @ 9:36 am

Yesterday, I passed the Oracle SQL Expert Exam. After 4 full years of working with Oracle, it took 2.5 weeks to study for this exam. I have studied from the SQL Fundementals 1-2 course material from Oracle University for focusing the exam topics and I used Oracle official documentation to support what I have learned from the course material. During my studies, I can say I learned a lot of small things and sometimes I felt shamed about not knowing the things that I should have learned as a junior DBA. At the end of the day, I can easily say that I can not be a SQL expert before writing that much SQL code as a database developer nevertheless, I am glad that, I spent time on learning new things by the help of certification. After this brief history, lets talk about something technical. (Warning!!! Post will be long and if you are already good with sql, just review the headers to se if there is something new for you. )

1- q Operator for using character literals with qoutation marks I never heard about it before. Document says its good for readibility but I am not sure :) Usage is simple if you want to say “coskan’s” all you need to do is put it in the braclets q’[]‘ or q’{}’ or q’()’ or q’<>’

SQL> select 'coskan''s approach to oracle' from dual;

'COSKAN''SAPPROACHTOORACLE'
---------------------------
coskan's approach to oracle

SQL> select 'coskan'||q[''s approach to oracle'] from dual;
ERROR:
ORA-01756: quoted string not properly terminated

SQL> select 'coskan'||q'['s approach to oracle]' from dual;

'COSKAN'||Q'['SAPPROACHTOOR
---------------------------
coskan's approach to oracle

SQL> select 'coskan'||q'('s approach to oracle)' from dual;

'COSKAN'||Q'('SAPPROACHTOOR
---------------------------
coskan's approach to oracle

SQL> select 'coskan'||q'{'s approach to oracle}' from dual;

'COSKAN'||Q'{'SAPPROACHTOOR
---------------------------
coskan's approach to oracle

SQL> select 'coskan'||q'<'s approach to oracle>' from dual;

'COSKAN'||Q'<'SAPPROACHTOOR
---------------------------
coskan's approach to oracle

2- null with logical operators . ( I felt really shamed that I didnt know (null&false=false) and (null or true=true) ) table is like below null and true = null null and false = false null OR true = true null OR false = null

SQL> select * from hr.departments where (1=1 and department_id not in (select null from dual)) and rownum<2;

no rows selected

SQL> select * from hr.departments where (1=2 and department_id not in (select null from dual)) and rownum<2;

no rows selected

SQL> select * from hr.departments where (1=1 or department_id not in (select null from dual)) and rownum<2;

DEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
           10 Administration                        200        1700

SQL> select * from hr.departments where (1=2 or department_id not in (select null from dual)) and rownum<2;

no rows selected

3- Escape clause to avoid wildcard characters.

To use wildcard characters as normal character you can use Escape clause with Like

SQL> select first_name from hr.employees
     2  where first_name like '%E_%';

FIRST_NAME
--------------------
Ellen
Elizabeth
E_leni

SQL> select first_name from hr.employees 
     2 where first_name like '%E\_%' escape '\';

FIRST_NAME
--------------------
E_leni

SQL> select first_name from hr.employees 
    2   where first_name like '%E?_%' escape '?';

FIRST_NAME
--------------------
E_leni

4 Operator Precedence
You need to know these precedence to make sure your query is doing the right thing. (simple things can cause big problems)
1 Arithmetic operators
2 Concatenation operator
3 Comparison conditions
4 IS [NOT] NULL, LIKE, [NOT] IN
5 [NOT] BETWEEN
6 Not equal to
7 NOT logical condition
8 AND logical condition
9 OR logical condition

5-Order by Nulls FIRST/LAST Clause If you want to put nulls in the beginning or end of you resultset all you need to do is add

order by ..... nulls first
or
order by ..... nulls last

 
SQL> select * from hr.departments 
   2 order by manager_id nulls last;

DEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
           90 Executive                             100        1700
           60 IT                                    103        1400
          100 Finance                               108        1700
          230 IT Helpdesk                                      1700
          240 Government Sales                                 1700

5 rows selected.

SQL> select * from hr.departments 
   2 order by manager_id nulls first;

DEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
          240 Government Sales                                 1700
          230 IT Helpdesk                                      1700
           90 Executive                             100        1700
           60 IT                                    103        1400
          100 Finance                               108        1700

5 rows selected.

6- How to see seconds past midnight (dont know how I missed this when I prepare take control of time post)

SQL> select to_char(sysdate,'sssss') seconds_pass_midnight
  2  from dual;

SECON
-----
61546

7- nullif function
Document says NULLIF compares expr1 and expr2. If they are equal, then the function returns null. If they are not equal, then the function returns expr1.

Say you want to put null for the lenght_diff for employees whose first_name lenght is equal to last_name lenght (dont ask why :) )

SQL> select  first_name,last_name
  2  ,nullif (length(first_name),length(last_name)) lengt_diff
  3  from employees;

FIRST_NAME LAST_NAME  LENGT_DIFF
---------- ---------- ----------
MARKO      JANKO
JOE        JANKO               3

8- Coalesce Function
Document says; COALESCE returns the first non-null expr in the expression list. You must specify at least two expressions. If all occurrences of expr evaluate to null, then the function returns null.

Lets see how ;


SQL> SELECT last_name, employee_id,manager_id,department_id,
  2  COALESCE(to_char(department_id),TO_CHAR(manager_id), 'No manager no department who are you ???')
  3  FROM hr.employees
  4  ;

LAST_NAME                 EMPLOYEE_ID MANAGER_ID DEPARTMENT_ID COALESCE(TO_CHAR(DEPARTMENT_ID),TO_CHAR(
------------------------- ----------- ---------- ------------- ----------------------------------------
OConnell                          198        124            50 50
Grant                             199        124            50 50
Whalen                            200        101            10 10
Hartstein                         201        100            20 20
Fay                               202        201           160 160
Mavris                            203        101            40 40
Baer                              204        101            70 70
Higgins                           205        101           110 110
Gietz                             206        205           110 110
King                              100                       90 90	******************
Kochhar                           101        100            90 90
....................................................................................
....................................................................................
....................................................................................
....................................................................................
Feeney                            197                          No manager no department who are you ??? ****************

107 rows selected.

9- Need of group by clause for nested group by functions.

---single aggregate function

SQL> select avg(salary) from employees;

AVG(SALARY)
-----------
 6461.68224


---nested aggregate functions
SQL> select max(avg(salary)) from employees;
select max(avg(salary)) from employees
           *
ERROR at line 1:
ORA-00978: nested group function without GROUP BY

SQL> select max(avg(salary)) from employees
  2  group by department_id;

MAX(AVG(SALARY))
----------------
      19333.3333

10-Natural Join
Despite coming from a SQL Server DBA background with computer engineering diploma I never heard about natural joins or I heard at university but never cared about it ....

Document says:
A natural join is based on all columns in the two tables that have the same name. It selects rows from the two tables that have equal values in the relevant columns. When specifying columns that are involved in the natural join, do not qualify the column name with a table name or table alias.

SQL> select employee_id,department_name from employees natural join departments;

32 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2052257371

----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |    11 |   330 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN         |             |    11 |   330 |     7  (15)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| DEPARTMENTS |    11 |   209 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMPLOYEES   |   108 |  1188 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("EMPLOYEES"."DEPARTMENT_ID"="DEPARTMENTS"."DEPARTMENT_ID"
              AND "EMPLOYEES"."MANAGER_ID"="DEPARTMENTS"."MANAGER_ID")
   2 - filter("DEPARTMENTS"."MANAGER_ID" IS NOT NULL)


11-When joining with JOIN...USING syntax table aliases are not allowed for the joining column

SQL> ---WRONG
SQL> select e.first_name,e.department_id
  2  from employees e join departments d
  3  using (department_id)
  4  where d.department_id=10 and rownum<2;
where d.department_id=10 and rownum<2
      *
ERROR at line 4:
ORA-25154: column part of USING clause cannot have qualifier


SQL> ---WRONG after removing from where
SQL> select e.first_name,e.department_id
  2  from employees e join departments d
  3  using (department_id)
  4  where department_id=10 and rownum<2;
select e.first_name,e.department_id
                    *
ERROR at line 1:
ORA-25154: column part of USING clause cannot have qualifier


SQL> --RIGHT
SQL> select e.first_name,department_id
  2  from employees e join departments d
  3  using (department_id)
  4  where department_id=10 and rownum<2;

FIRST_NAME           DEPARTMENT_ID
-------------------- -------------
Jennifer                        10 

12-ANY/ALL operator for subqueries
My face is going red for not using these two before. I think I never need them :)

Document says same thin for both of them;
Compares a value to each value in a list or returned by a query. Must be preceded by =, !=, >, <, =. Can be followed by any expression or subquery that returns one or more values.

The only difference is ANY Evaluates to FALSE if the query returns no rows. ALL Evaluates to TRUE if the query returns no rows.

I won't go deep into but if you not already know them its good practice to play with them

SQL> ---ANY
SQL> SELECT employee_id, last_name, job_id, salary
  2  FROM employees
  3  WHERE  salary < ANY
  4  (SELECT salary
  5  FROM employees
  6  WHERE job_id = 'ST_MAN')
  7  AND job_id <> 'ST_MAN' and rownum<2;

EMPLOYEE_ID LAST_NAME                 JOB_ID         SALARY
----------- ------------------------- ---------- ----------
        132 Olson                     ST_CLERK         2100

SQL>
SQL> ----USING ANY WITH NOT CHECK THE SYNTAX
SQL> SELECT employee_id, last_name, job_id, salary
  2  FROM employees
  3  WHERE  NOT salary < ANY
  4  (SELECT salary
  5  FROM employees
  6  WHERE job_id = 'IT_PROG')
  7  AND job_id <> 'IT_PROG' and rownum<2;

EMPLOYEE_ID LAST_NAME                 JOB_ID         SALARY
----------- ------------------------- ---------- ----------
        158 McEwen                    SA_REP           9000

SQL> ---ALL
SQL> SELECT employee_id, last_name, job_id, salary
  2  FROM employees
  3  WHERE  salary < ALL
  4  (SELECT salary
  5  FROM employees
  6  WHERE job_id = 'ST_MAN')
  7  AND job_id <> 'ST_MAN' and rownum<2;

EMPLOYEE_ID LAST_NAME                 JOB_ID         SALARY
----------- ------------------------- ---------- ----------
        105 Austin                    IT_PROG          4800

SQL>
SQL> ----USING ALL WITH NOT CHECK THE SYNTAX
SQL> SELECT employee_id, last_name, job_id, salary
  2  FROM employees
  3  WHERE  NOT salary < ALL
  4  (SELECT salary
  5  FROM employees
  6  WHERE job_id = 'IT_PROG')
  7  AND job_id <> 'IT_PROG' and rownum<2;

EMPLOYEE_ID LAST_NAME                 JOB_ID         SALARY
----------- ------------------------- ---------- ----------
        100 King                      AD_PRES         24000


13- Using NOT IN with subqueries with NULL values
I knew this but its nice to remind for the ones who doesnt know already. If you use NOT IN for a subquery with possible NULL clause it wont bring resultset because comparing null as you know equals to null. To workaround you can use NVL function.

Look what happens because of 2 NULL values


SQL> select count(*) from departments
  2  where department_id
  3  not in (select department_id
  4  from employees);

  COUNT(*)
----------
         0

SQL> select count(*) from employees
  2  where department_id is null;

  COUNT(*)
----------
         2
SQL>--change nulls with 999999

SQL> select count(*) from departments
  2  where department_id
  3  not in (select nvl(department_id,99999)
  4  from employees);

  COUNT(*)
----------
        16



14- Creating views with check option

To be honest I was totally unfamiliar to "with check option". Document says; Specify WITH CHECK OPTION to indicate that Oracle Database prohibits any changes to the table or view that would produce rows that are not included in the subquery. When used in the subquery of a DML statement, you can specify this clause in a subquery in the FROM clause but not in subquery in the WHERE clause.


SQL> CREATE OR REPLACE VIEW empv
  2  AS SELECT *
  3  FROM employees
  4  WHERE department_id = 10
  5  WITH CHECK OPTION CONSTRAINT empv10_ck ;

View created.

SQL> update empv set department_id=15 where Last_name='Whalen';
update empv set department_id=15 where Last_name='Whalen'
       *
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation

15- Sequences restart from MINVALUE(thanks Radino for the correction) with CYCLE option regardless from the START WITH value on the definition
I never used a sequence with cyle option before but I was thinking that it will re-use the already used ones when you recycle the values but it won't. If you set MINVALUE it will use MINVALUE, not the START WITH value and if you dont set MINVALUE it will use 1 which is default MINVALUE like below.


SQL> CREATE SEQUENCE cos_seq
  2  INCREMENT BY 10
  3  START WITH 10
  4  MAXVALUE 30
  5  NOCACHE
  6  CYCLE;

Sequence created.

SQL> select cos_seq.nextval from dual;

   NEXTVAL
----------
        10

SQL> select cos_seq.nextval from dual;

   NEXTVAL
----------
        20

SQL> select cos_seq.nextval from dual;

   NEXTVAL
----------
        30

SQL> select cos_seq.nextval from dual;

   NEXTVAL
----------
         1

SQL> select cos_seq.nextval from dual;

   NEXTVAL
----------
        11

SQL> select cos_seq.nextval from dual;

   NEXTVAL
----------
        21

SQL> select cos_seq.nextval from dual;

   NEXTVAL
----------
         1

16- NEXTVAL must be used before the initial usage of CURRVAL in a session.

Never tried, never got the error so never heard.


SQL> ---Brand New session
SQL> select cos_seq.currval from dual;
select cos_seq.currval from dual
       *
ERROR at line 1:
ORA-08002: sequence COS_SEQ.CURRVAL is not yet defined in this session


SQL> select cos_seq.nextval from dual;

   NEXTVAL
----------
        11
SQL> select cos_seq.currval from dual;

   CURRVAL
----------
        11

This is the end of PART-1. I hope I can complete the PART-2 of this series.

Warning: This post is not about what is being asked in Exam. It is about what I learned during my studies. If you want the questions just study and take the exam. Try to think what would you expect from a SQL Expert and study the things you think you would expect.

Update : Dont forget to check the links in Comment of Tonguc for better understanding of SQL Fundementals.

January 19, 2009

emca fails on 10.2.0.4 with ORA-04042 and ORA-06512

Filed under: Tips — coskan @ 7:01 pm

Today when I was trying to re-create the em repository after 10.2.0.2 to 10.2.0.4 upgrade with the command  “emca -deconfig dbcontrol db -repos create”  it failed with error stack below.

Jan 19, 2009 5:11:53 PM oracle.sysman.emcp.EMReposConfig invoke SEVERE: Error creating the repository
Jan 19, 2009 5:11:53 PM oracle.sysman.emcp.EMReposConfig invoke INFO: Refer to the log file at /oracle/product/ora10204/cfgtoollogs/emca/*****\emca_repos_create_<date>.log for more details.
Jan 19, 2009
5:11:53 PM oracle.sysman.emcp.EMConfig perform SEVERE: Error creating the repository

Then I check the error log, and  I found the stack below

Create SYSMAN user.

No errors.
DECLARE
*
ERROR at line 1:
ORA-04042: procedure, function, package, or package body does not exist
ORA-06512: at line 11

While it creates the sysman user it cannot find a procedure and fails. To find out which procedure it was looking for I applied metalink and quick Search on metalink took me the note ID 331938.1

The note says  problem is caused by the missing procedure  dbms_shared_pool (no idea why it was missing on a brand new 10.2.0.2 db)

To solve the problem

1- Create the package

@$ORACLE_HOME/rdbms/admin/dbmspool.sql

2- Give permission to package

grant execute on dbms_shared_pool to sysman;
grant execute on dbms_shared_pool to dba;

3- follow the re-creation steps from the beginning including dropping sysman role and droppping the existing configurations

4- re run the command  "emca -config dbcontrol db -repos create"

worked like charm on my case.

December 22, 2008

How to use “top” effectively on Linux as a DBA

Filed under: Linux, Tips — coskan @ 7:28 pm

I have been familiar with linux since 2000, when I was at second class of university, but I have never worked  on a  linux machine which was hosting primary production database.   The first OS  I used Oracle on, was IBM AIX then, I always worked on HP/UX with a Server management team  so, I never go that much deep for monitoring tools of Unix based systems, till last couple of weeks at which I started to study for managing oracle on linux exam.

Before studying, my   top command  usage was always like ,  run top / topas  command  then check the processes and quit the screen. I never asked myself, if I can do anything more. on top screen.  Another reason about this laziness, is that , I never liked the layout of manual pages. I always got bored in 5th second :) . On the other hand, when you are studying something on linux, you have to leave all your boredom about man pages behind and start to read them completely.

After reading the manual, I discovered that top screen is very powerfull interactive tool  on which you can do many modifications by parameters like changing the default sort option filtering by users changing the layout refresh time.  You will find small tutorial about what you can do with top at the pages below.

1-Basic top screen

$ top

top

2- For changing the default layout press f or o in the top screen.  After you press you will see the screen below. Letter with star indicator  are the fields you can see on the default page.

top2

3- When you press any of the starred letter on keyboard their star will diseppear and letter will be lowercase this means you deselected them from fields. When you press any on the non starred letter a star will apper at each one of them and letter will be uppercase This means you selected them as field.

top3

After you change the fields top screen will be like below with UID SWAP PPID and CPU Time columns are added to fields

top4

4- If you want to filter the processes from oracle user only (which is the best option for me) press u to filter the screen and write the username / userid . To remove the filter just press enter after u.

top5

processes from oracle user only

top6

5- If you want only top 10 processes from oracle user just press n and enter 10. To revert back enter 0

top7n

Ta daaa you have top 10 oracle sessions  list on your top screen

top8n

6- To change the screen refreshment period press d and set the delay with seconds

top9s

7- To kill a session press k and give the PID of the session. top10k

After you entered the pid, It will ask you the signal with default of 15

top11k

9-  Suppose that you want to change the sort order. There are two ways to do it. First way is press F and set the sorting column from the next screen by pressing the column letter . Second way is using moving by < > keys when you are at the top screen. It can also sort to a field which is not shown on the screen.

top-sort

Now its all sorted bymemory usage  instead of CPU

topsort-mem

10- you can also press W and it will write your current configuration to the conf file to keep it for future usage as a default.

These are all I wanted to mention about interactive usage of TOP command as a DBA.

For more info about the top command just write man top on you command line :)

Wish you Happy Christmas  for all.

December 19, 2008

ORA-29701 when starting oracle during server boot

Filed under: ASM, Tips — coskan @ 6:53 pm

I finally passed 11G New Features exam to upgrade my 10G OCP and now the new target is becoming Managing Oracle on Linux Certified Expert. I will write a blog post about my opinions and my approach to Oracle Certification when I find time,  but now, what I want to write is, a problem I faced during my tests for the exam.

When I was testing to start oracle (ASM Database Listener and dbconsole together) as a service during the server boot , my ASM instance was always failing with the error

“ORA-29701: unable to connect to Cluster Manager”

ASM needs CSS daemons to start before it starts, no matter  the sequence was right(  like below)  ASM wasn’t starting because  CSS daemons wasn’t  starting  in time.

service startup sequence
init.cssd start
dbora start
init.cssd run

On Metalink this problem  listed as  Bug 3458327 . As explained on Metalink Note Id: 264235.1 the workaround is changing the  location of the line in which CSS daemon start  (which is probably at the bottom of inittab) to between run levels rc2.d and rc3.d to make it ready for use of ASM instance like below,

.

l0:0:wait:/etc/rc.d/rc 0
l1:1:wait:/etc/rc.d/rc 1
l2:2:wait:/etc/rc.d/rc 2
h1:35:respawn:/etc/init.t/init.cssd run >/dev/null 2>&1 </dev/null
l3:3:wait:/etc/rc.d/rc 3
l4:4:wait:/etc/rc.d/rc 4
l5:5:wait:/etc/rc.d/rc 5
l6:6:wait:/etc/rc.d/rc 6

Another option is to change your service script to sleep for 120 sec (thanks Martin for correction) but I prefer the one on the top.

Tests are done on 11.1.0.7 running on Oracle EL5

December 17, 2008

ASMLib and undiscovered disks

Filed under: ASM, Tips — coskan @ 1:03 pm

I won’t give any detailed information but just a quick tip for users who are struggling with discovery of disks they mounted by ASM Lib

If you follow the steps as Tim Halls article ASM using ASMLib and Raw Devices normally everthing should go fine,  but somehow,  if your disks are not discovered, you can first follow very well written ASMLib  troubleshooting guide of Jason Arneil.

If checks are ok,  but disks are still not discovered

you should first try to put ORCL:* (Thanks Saurabh- see the comment) to your ASM disk discovery path

Still doesnt work,   like it did not in my case ?

Try this magical discovery path

/dev/oracleasm/disks/*

If you are lucky as me it will work like charm.

November 12, 2008

How to downgrade EM Repository

Filed under: Basics, Tips — coskan @ 3:05 pm

Nowadays, I am studying on to upgrade my OCP certificate to 11G. Despite that  none of the excellent data guard enhancements covered,  exam topics are still very satisfactory to cover new features for a DBA of Oracle Database 11G. If you try what you read you can go deep new features too. One of them is the necessity of separate EM Repository backup for a possible downgrade. There is a new tool, under 11gORACLE_HOME/bin directory,  called emdwgrd (enterprise manager downgrade) . It can backup your EM Repository before you upgrade and then restore it to the downgrade if you rollback the upgrade process. If you dont use this tool upgraded EM Repository cannot be used.

Here is how it works.

Before you start the actual upgrade (manual or DBUA) you ran the emdwgrd from new 11gORACLE_HOME/bin directory like below;

emdwgrd -save -sid old_SID -path save_directory

-sid is the SID of the database being upgraded
-path is the directory for the backup of em respository.

Path you choose must be emty otherwise you will get the error I got :)

C:\oracle\product\product\11.1.0\db_1\BIN>emdwgrd -save -sid ORACOS -path C:\oracle\product\10.2.0\flash_recovery_area
Enter sys password for database ORACOS?
********
Error: C:\oracle\product\10.2.0\flash_recovery_area must be an empty directory.

Output of the command ran  is like below

C:\oracle\product\product\11.1.0\db_1\BIN>mkdir C:\oracle\product\10.2.0\flash_recovery_area\em_before_upgrade
C:\oracle\product\product\11.1.0\db_1\BIN>emdwgrd -save -sid ORACOS -path C:\oracle\product\10.2.0\flash_recovery_area\em_before_upgrade
Enter sys password for database ORACOS?
********
Tue Nov 11 11:19:44 2008 – Verify EM DB Control files … pass
Tue Nov 11 11:19:44 2008 – Validating DB Connection to ORACOS … pass
shared = 0
Tue Nov 11 11:19:49 2008 – Creating directory … created
Tue Nov 11 11:19:50 2008 – Stopping DB Control … stopped
Tue Nov 11 11:20:10 2008 – Saving DB Control files
… saved
Tue Nov 11 11:21:46 2008 – Recompiling invalid objects … recompiled
Tue Nov 11 11:22:46 2008 – Exporting sysman schema for ORACOS … exported
Tue Nov 11 11:25:48 2008 – DB Control was saved successfully.
Tue Nov 11 11:25:48 2008 – Starting DB Control … started
Tue Nov 11 11:26:28 2008 – Dump directory was dropped successfully.

As you see it creates a temporary database directory in the database and dump the file using this directory including sysman schema export. After everything is finished it drops the temp database directory.

Directory structure of the backup  is like below. It has backup and logs directories

C:\oracle\product\10.2.0\flash_recovery_area\em_before_upgrade>dir
Directory of C:\oracle\product\10.2.0\flash_recovery_area\em_before_upgrade12/11/2008 11:39 backup
12/11/2008 11:38 logs
0 File(s) 0 bytes
4 Dir(s) 8,887,455,744 bytes free

In backup directory there are backups files including sysman schema export and copy of sysman and servername_ORACLESID directories related with EM under ORACLE_HOME directory

C:\oracle\product\10.2.0\flash_recovery_area\em_before_upgrade\backup>dir
Directory of C:\oracle\product\10.2.0\flash_recovery_area\em_before_upgrade\backup11/11/2008 11:25 13,000,704 EXPORT.DMP
11/11/2008 11:25 30,461 export.log

11/11/2008 11:21 sysman
11/11/2008 11:20 XXXXXXXXXXXXXXXXXX_ORACOS

In the logs directory you can view the logs of the backup proces

C:\oracle\product\10.2.0\flash_recovery_area\em_before_upgrade\logs>dir
Directory of C:\oracle\product\10.2.0\flash_recovery_area\em_before_upgrade\logs11/11/2008 11:21 115,427 copysave.log
11/11/2008 11:26 132 emctl_startsave.log
11/11/2008 11:20 124 emctl_stopsave.log
11/11/2008 11:22 0 export_dmp.log
11/11/2008 11:26 2,251 sqlsave.log

After this finish you can do your upgrade. Lets assume that you did your upgrade and decide to go back. After you finish the database downgrade steps you can now do post downgrade process for EM Repository. To do this you must first run the emca utility from 11gORACLE_HOME to copy the respository files to new oracle home.

C:\oracle\product\product\11.1.0\db_1\BIN>emca -restore dbSTARTED EMCA at 12-Nov-2008 11:57:06
EM Configuration Assistant, Version 11.1.0.5.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.

Enter the following information:
ORACLE_HOME for the database to be restored: C:\oracle\product\10.2.0\db_1
Database SID: ORACOS
Listener port number: 1522
Password for SYS user:
Password for SYS user:
Do you wish to continue? [yes(Y)/no(N)]: coskan12
Invalid input.
Do you wish to continue? [yes(Y)/no(N)]: Y
12-Nov-2008 11:59:42 oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at C:\oracle\product\cfgtoollogs\emca\oracos\emca_2008_11_12_11_57_04.log.
12-Nov-2008 11:59:43 oracle.sysman.emcp.EMDBPreConfig checkRestoreParams
WARNING: EM is not configured for this database. No EM-specific actions can be performed.
Enterprise Manager configuration completed successfully
FINISHED EMCA at 12-Nov-2008 11:59:51

As you see on the warning EM version must still be downgraded. When I connected to EM I can only be able to manage non EM specific actions as mentioned (sorry I couldnt upload the file :(  proxy issues )

Its time to finish EM Downgrade operation. Command is similar with two difference we need to use -restore option instead of -save and we need to give default temporary tablespace name for sysman schema. If we don’t give temp tablespace it will fail.

emdwgrd -save -sid old_SID -path save_directory -tempTablespace temptablespace

C:\oracle\product\product\11.1.0\db_1\BIN>emdwgrd -restore -sid ORACOS -path C:\oracle\product\10.2.0\flash_recovery_area\em_before
_upgrade -tempTablespace TEMP
Enter sys password for database ORACOS?********
Enter sysman password for database ORACOS?

2*******
Wed Nov 12 11:59:59 2008 – Verify EM DB Control files … pass
Wed Nov 12 11:59:59 2008 – Validating DB Connection to ORACOS … pass
Wed Nov 12 12:00:06 2008 – Validating TEMP tablespace in ORACOS … pass
shared = 0
Wed Nov 12 12:00:08 2008 – Creating directory … created
Wed Nov 12 12:00:12 2008 – Stopping DB Control … stopped
Wed Nov 12 12:01:00 2008 – dropping sysman schema … dropped
Wed Nov 12 12:03:51 2008 – Recreating sysman user … recreated
Wed Nov 12 12:03:54 2008 – Restoring DB Control files
… restored
Wed Nov 12 12:03:54 2008 – Importing sysman schema … imported
Wed Nov 12 12:11:17 2008 – Recompiling invalid objects … recompiled
Wed Nov 12 12:11:46 2008 – restoring dbms_jobs … restored
Wed Nov 12 12:11:47 2008 – restoring dbms_registry … restored
Wed Nov 12 12:13:14 2008 – DB Control was restored successfully.
Wed Nov 12 12:13:14 2008 – Starting DB Control … started
Wed Nov 12 12:13:57 2008 – Dump directory was dropped successfully.

As you see sysman schema is dropped and recreated again. All your scheduler jobs will be restored so you don’t need to create them again.

I hope you wont need to downgrade but its nice to know how to.

Upgraded DB: 10.2.0.4 Windows 32
Upgraded DB: 11.1.0.6 Windows 32


References used :
Oracle® Database Upgrade Guide 11g Release 1 (11.1)

FYI: Manual is for linux only but nothing special to linux is on the doc for this operation

October 23, 2008

Dictionary views which are not listed in Dictionary View

Filed under: Basics, Tips — coskan @ 4:20 pm

I usually check view called dictionary if I want to learn the name of the dictionary table which I need to query for specific info.

For example if I want to learn which views are available to view SCHEDULER related info I use query below ,

select * from dict where table_name like ‘%SCHED%’;

Then I do my research by querying  the table in the result set.

Today I was looking for which table I need to look for running scheduled jobs I realized that documented

DBA_SCHEDULER_RUNNING_JOBS view is not listed in Dictionary view, I crosschecked DBA_OBJECTS view to see if it is listed there. It was listed under DBA_OBJECTS as both  view and synonym. So what was the problem ?

In the definition of Dictionary view what you see is a union query. First part of the union is like below.

SELECT o.NAME, c.comment$
FROM SYS.obj$ o, SYS.com$ c
WHERE o.obj# = c.obj#(+)
AND c.col# IS NULL
AND o.owner# = 0
AND o.type# = 4
AND (   o.NAME LIKE ‘USER%’
OR o.NAME LIKE ‘ALL%’
OR (    o.NAME LIKE ‘DBA%’
AND EXISTS (SELECT NULL
FROM SYS.v$enabledprivs
WHERE priv_number = -47 /* SELECT ANY TABLE */)
)
)

UNION_ALL

..
..
..

DBA_SCHEDULER_RUNNING_JOBS view is definitely listed under SYS.obj$ so there must be a problem on SYS.com$ table which holds the comments of both tables and columns.

When I query SYS.com$ with the obj# of DBA_SCHEDULER_RUNNING_JOBS view, I realized that, there isn’t any row where c.col# IS NULL ( I assume the row that holds table comment. )

I think Oracle developers forgot to put a comment on this table. Further investigation was to see which dictionary tables were missed by dictionary table. Basic minus operation lists 36 tables that are missed by Dictionary view on 10.2.0.4.

As a workaround You can still query DBA_OBJECTS but it doesn’t have an indicator that the listed is a dictionary table so you might miss the ones you look for.

To solve the problem I created a DICT_MISSED table and put it as another UNION_ALL statement at the and of the DICTIONARY view source and created MYDICT view. The only problem is, you must refresh DICT_MISSED on every upgrade to catch new missed tables (I hope there will be none if someone from oracle reads this).

Full demonstration scripts

MYDICT view Script.

Note:  WordPressed changed a lot since I wrote for the last time Its a shame that they dont support txt upload anymore pufff

« Newer PostsOlder Posts »

The Silver is the New Black Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 199 other followers