|
|
ManualiOracle.it |
|
|
||
| ||||||||||||||||||||||||||||||||||||||||||||||||||
|
December - Db Analysis
Level (*) : 3 Topic (*) : DBA/Progr
You have to write a PL/SQL program that do a short db analysis. This is an example of its result: ************************************************************************ ************************************************************************ 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
Level (*) : 2 Topic (*) : DBA
One colleague of you, says that the table EMP and its indexes have contention. You have to find a query that shows the commands useful in order to resolve this problem and this query must show the INITRANS at the moment: 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
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?
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 Ignored Why? 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
June - Table Access
Level (*) : 2 Topic (*) : DBA
In the database, you have many physical reads in table Tab_1.
This is table is small. 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 solutions.
May - SQL and Files
Level (*) : 2 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
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 write 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) ---------- 0You used this query in Oracle9i and now you wanted that the result was different then 0. What is the reason? Which metalink notes can you refer to?
February - Alert.log
Level (*) : 2 Topic (*) : DBA
Your Oracle server is crashed.
After it works again, you check the db. It seems to works 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:11 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?
January - Oracle 11g Installation
Level (*) : 3 Topic (*) : DBA
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
| |||||||||||||||||||||||||||||||||||||||||||||||||
