# 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
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
-----------------


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. [/sourcecode] 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 &#91;/sourcecode&#93; <strong>2- null with logical operators . </strong>( I felt really shamed that I didnt know (null&amp;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 &#91;/sourcecode&#93; <strong>3- Escape clause to avoid wildcard characters. </strong> To use wildcard characters as normal character you can use Escape clause with Like</pre> 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 &#91;/sourcecode&#93; <strong>12-ANY/ALL operator for subqueries </strong> My face is going red for not using these two before. I think I never need them <strong><a href="http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/conditions002.htm#sthref2792" target="_blank">Document </a></strong> 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 =, !=, &gt;, &lt;, =. 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 &#91;/sourcecode&#93; <strong>13- Using NOT IN with subqueries with NULL values</strong> 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 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. 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. After you change the fields top screen will be like below with UID SWAP PPID and CPU Time columns are added to fields 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. processes from oracle user only 5- If you want only top 10 processes from oracle user just press n and enter 10. To revert back enter 0 Ta daaa you have top 10 oracle sessions list on your top screen 6- To change the screen refreshment period press d and set the delay with seconds 7- To kill a session press k and give the PID of the session. After you entered the pid, It will ask you the signal with default of 15 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. Now its all sorted bymemory usage instead of CPU 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