| ManualiOracle.it | ||
|
||
| |||||||||||||||||||||||||||||||||||||||||||||||
|
If it is not specified, the commands must be executed connected as SYS. About the solutions: - could be more than one. Here is one solution that often takes into account of the performance tuning - they could change because of the Oracle version (eg. Oracle 8.0 is quite different than Oracle 11g!) - it is not warranted to be error-free in all the Oracle environments December - Db Analysis
Level (*) : 3 Topic (*) : DBA/Progr
You have to write a PL/SQL program that do a short "db analysis". When you launch it, you must receive a result like this: ************************************************************************ ************************************************************************ Analysis Date: 07/12/2011 16:33:42 ************************************************************************ ************************************************************************ Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production Database: Dbid: 324368697 Name: DBLOR Created: 18-AGO-11 Log_mode: ARCHIVELOG Instance: Instance Number: 1 Name: DBLOR Parallel: NO Host name: mi088lor Archiver: STARTED Startup Time: 05/10/2011 16:08 SGA Statistics: Buffer Cache Hit Ratio: 97.384 % Library Cache Miss Ratio: 0.912 % Dictionary Cache Miss Ratio: 0.984 %
November - Contention and "Metadata query"
Level (*) : 3 Topic (*) : DBA
One your colleague, says that the EMP table and its indexes have contention. Of course this is an "application issue" but, as a DBA, you could reduce this problem (for the "future rows") by changing the INITRANS setting. So you have to find a "metadata query" that: - return the commands you could launch in order to apply this changing - return the current INITRANS Result Example: COMMAND INI_TRANS -------------------------------------- ---------- alter index SCOTT.EMP_IDX2 initrans 6; 2 alter index SCOTT.EMP_IDX1 initrans 6; 2 alter table SCOTT.EMP initrans 5; 1You have to find it without knowing the indexes names.
October - Initialization Parameters
Level (*) : 2 Topic (*) : DBA
You are in Oracle 10.2. and you want to avoid errors like :
ORA-00018: maximum number of sessions exceed ORA-00020: maximum number of processes (%s) exceededTo avoid them, you want to set correctly the parameters "processes" and "sessions". To do this analyze, you have to find a query that has result like this: RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION LIMIT_VALUE -------------- ------------------- --------------- ----------- processes 130 133 150 sessions 135 159 170 Solution
You should use the view v$resource_limit : it displays information regarding some of the system resources:
SELECT
resource_name,
current_utilization,
max_utilization,
limit_value
FROM
v$resource_limit
WHERE
resource_name in ('sessions', 'processes');
September - DDL generation problem
Level (*) : 2 Topic (*) : DBA
You are in Oracle 10.2. You have an existing DBMS_SCHEDULER job called FIND_JOB inside the schema SCOTT. You are using SQL*Plus and you want to generate the DDL of this job. How can you do it? Solution
You can use the function dbms_metadata.get_ddl.Its output is a CLOB so in order to see the result in SQL*Plus, you have to launch: set long 2000After this, you can use this query (the type of this object is PROCOBJ):
SELECT dbms_metadata.get_ddl('PROCOBJ', 'FIND_JOB', 'SCOTT')
FROM dual;
August - Export Problem
Level (*) : 3 Topic (*) : DBA
You are in Oracle 11.2.0.1. You use the "original" export (no datapump) in order to export the schema called LASSI and you receive this error: EXP-11 Table Does Not Exist or Table is IgnoredWhy? Which query can you launch before the export in order to see if you could receive this error? How can you avoid it? Which metalink note is useful to read?
July - Invalid Views
Level (*) : 2 Topic (*) : DBA
You have to find a query that shows the invalid views in your db. This query must be executed in SQL*Plus and you want a result like this:
INVALID_OBJECT TEXT
----------------------- ---------------------------------------
LASSI.VW_S_ORDER_ITEM select row_id,
PAR_ROW_ID,
S_ORDER_ITEM_X.ATTRIB_26,
S_ORDER_ITEM_X.ATTRIB_27
from pippo.S_ORDER_ITEM_X
LASSI.S_EXTRACT_NODE SELECT
T1.RULE_ID RULE_ID,
T1.NODE_ID NODE_ID,
T1.GROUP_CD GROUP_CD,
T2.NAME RULE_NAME
FROM S_EXTDATA_N T1, S_EXTDATA_R T2
WHERE T1.RULE_ID = T2.ROW_ID
Solution
You have to set up the SQL*Plus environment. Example:
set lines 155 pages 66 col invalid_object for a33 set long 2000And, after this, you can launch the query:
select o.owner || '.' || o.object_name invalid_object, v.text
from dba_objects o,
dba_views v
where status != 'VALID'
AND object_type = 'VIEW'
AND o.owner = v.owner
AND o.object_name = v.view_name;
June - Table Access
Level (*) : 3 Topic (*) : DBA
In the database, you have many "physical reads" on a small table called TAB_1.
Then you can try to improve the queries that read it and you can do others 2 things in order to improve the access to it. Find these 2 other solutions.
May - SQL and Files
Level (*) : 3 Topic (*) : DBA/Progr
You have to find a query that shows the datafiles and tempfiles created in the last X days, where X is a dynamic variable defined like this: &days.
For example, I want to know the files created in the last 90 days then my result is: TYPE TABLESPACE_NAME FILE_NAME MB CREATION ------ ------------------ ------------------------------------- -------- ---------- DATAF TS_TSOLC_TAB /oracle/dblor/data0/TS_TAB_d02.dbf 10240 22-FEB-11 DATAF TS_TSOLC_TAB /oracle/dblor/data0/TS_TAB_d03.dbf 10240 22-APR-11 DATAF USERS /oracle/dblor/data0/users02.dbf 16384 14-FEB-11 TEMPF. TS_ACN_UNB_TEMP /oracle/dblor/temp0/TS_ACN_TEMP.dbf 5120 14-FEB-11
April - Archiving
Level (*) : 2 Topic (*) : DBA
You have to find a query that shows the amount of redo logs generated every 10 minutes in the last 2 hours.
Then you want a result like this: DATE N.Archive Tot.MB ------------------- ------------ ---------- From 2011-01-25 17.20 3 708 From 2011-01-25 17.30 3 702 From 2011-01-25 17.40 1 234 From 2011-01-25 17.50 2 469 From 2011-01-25 18.00 1 234 From 2011-01-25 18.10 2 471 From 2011-01-25 18.20 2 469 From 2011-01-25 18.30 2 468 From 2011-01-25 18.40 1 234 From 2011-01-25 18.50 2 468 From 2011-01-25 19.00 2 480 From 2011-01-25 19.10 1 144 From 2011-01-25 19.20 1 0 Solution
You can see the view v$archived_log:
select 'From '|| substr(to_char(al.completion_time,'YYYY-MM-DD hh24.mi'),1,15) || 0 date,
count(al.recid) "N.Archive",
round(sum ((al.blocks+1)*al.block_size)/1048576) "Tot.MB"
from v$archived_log al
where al.completion_time >=sysdate-1/12
group by substr(to_char(al.completion_time,'YYYY-MM-DD hh24.mi'),1,15)
order by 1;
March - Auditing
Level (*) : 2 Topic (*) : DBA
You have the auditing activated in Oracle9i.
You upgrade the db to the 10.2.0.5 Oracle version. After that, you see that Oracle writes in the sys.aud$ but the result of one query is strange:
SELECT count(1)
FROM sys.aud$
WHERE TIMESTAMP# IS NOT NULL;
COUNT(1)
--------
0
You used this query in Oracle9i and now you expected to receive a result different than 0.What is the reason? Which metalink notes can you refer to? Solution
In the Oracle db version 10gR1 and above, the TIMESTAMP# column is obsoleted in favour of the new NTIMESTAMP# column.Since datatype of TIMESTAMP# (date) is coarse and can not capture fractional seconds and timezone, NTIMESTAMP# column is introduced of datatype timestamp(6). The old column is deprecated. Metalink ID 427296.1
February - Alert.log
Level (*) : 3 Topic (*) : DBA
Your Oracle server is crashed.
After it works again, you check the db. It seems to work fine but some people say that they can't connect to the db and in the alert.log you found this: Tue Jan 11 12:10:28 2011 Errors in file /oracle/db_lor/admin/bdump/db_lor_qmn0_5525654.trc: ORA-00600: internal error code, arguments: [4194], [73], [77], [], [], [], [], [] Doing block recovery for fno: 2 blk: 43 block recovery range from rba 17687.5432.0 to scn 1972.2718780554Which metalink notes can you refer to? How can you fix the problem?
January (2) - Listener Problem
Level (*) : 2 Topic (*) : DBA
You want to see the status of a listener and you receive this error:
ordblor # lsnrctl status LS_DBLOR_1521 LSNRCTL for IBM/AIX RISC System/6000: Version 9.2.0.8.0 - Production on 9-JAN-11 11:17 Copyright (c) 1991, 2006, Oracle Corporation. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=mi011)(PORT=1521))) TNS-01169: The listener has not recognized the passwordWhy have you received this error? How can you avoid it? Solution
You have received this error because you have implemented the password in the listener.The password in the listener was useful especially in Oracle 9i for security reasons. To avoid the error here described, you have to look at inside the file listener.ora . At the end of this file, you find something this: PASSWORDS_ls_dblor_1521 = B5C1F55A82F9E241Then you have to launch these commands: 1. lsnrctl 2. set current_listener LS_DBLOR_1521 3. set password B5C1F55A82F9E241 4. status
January - Oracle 11g Installation
Level (*) : 3 Topic (*) : DBA
Using OUI, you are installing Oracle 11.2.0.2 server on AIX machines (AIX 5.3 and AIX 6.1).
At Step 10 ("Perform Prerequisite Checks"), you receive errors like these: 1. free space: server88:/tmp : Expected Value 1GB, Actual Value 100MB 2. Checking for rsct.basic.rte(0.0); Not found 3. Checking for rsct.compat.clients.rte(0.0); Not found 4. APARs IZ42940, IZ49516, IZ52331 are missing 5. APARs IZ41855, IZ51456, IZ52319 are missingCan you ignore them? In which metalink notes can you find these problems?
(*) Level: 1: easy solution 2: medium solution 3: hard (or long) solution Topic: DBA: quiz usually suitable for DBAs Progr: quiz usually suitable for PL/SQL or SQL Programmers DBA/Progr: quiz suitable for DBAs and for Programmers
| ||||||||||||||||||||||||||||||||||||||||||||||
