| 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 - Platform
Level (*) : 1 Topic (*) : DBA Which query can you do in order to see the Operating System in your Oracle server? For example, you must have a result like this: PLATFORM_NAME ---------------------------- Microsoft Windows x86 64-bit Solution
You should use the V$DATABASE view: SELECT platform_name FROM v$database;
November - Pmon in Solaris
Level (*) : 3 Topic (*) : DBA You work in some Unix-like machines. In all of them, you have a db called DBLOR. The owner of the db files is called osdblor but you start the dbs with a user called ordblor. If you want to see who has started the dbs, usually you correctly see this: lormachine > ps -ef | grep pmon ordblor 3095 1 0 Oct31 ? 00:30:57 ora_pmon_DBLORBut in the Solaris machine, you see this "wrong" information: lormachine > ps -ef | grep pmon osdblor 3095 1 0 Oct31 ? 00:30:57 ora_pmon_DBLORIf you check the listener process doing "ps -ef | grep tns", you don't have this problem. Why this happens with the pmon process and not with the listener process? Instead using "ps -ef", how can you see the correct information in Solaris?
October - NLS_INSTANCE_PARAMETERS
Level (*) : 3 Topic (*) : DBA Your database is in Oracle 11.2 and you want to change the parameters contained in the view NLS_INSTANCE_PARAMETERS. How can you do it? Show all steps useful to do it.
September - AWR
Level (*) : 2 Topic (*) : DBA These questions are about the AWR and its management through SQL*Plus : - in which dictionary view can you see the frequence of the snapshots? - how can you change this frequence? for example, if you want to have one snapshot every 30 minutes - how can you do a "manual" snapshot? - you want to launch the script awrrpt.sql about old information (10 days old). How can you do it? Solution
1) You see it in the DBA_HIST_WR_CONTROL view
2) To change the frequence:
exec dbms_workload_repository.modify_snapshot_settings (interval => 30)
3) Manual snapshot (you can use it also on the RAC)
exec DBMS_WORKLOAD_REPOSITORY.create_snapshot;
4) If you want AWR going back 10 days, before launching the script awrrpt.sql, you can
do this:
define num_days =10
August - Index Information
Level (*) : 3 Topic (*) : DBA You have to write a query in order to find the indexes created during the last month. About these indexes (partitioned and not), you have to find the following information: INDEXNAME OBJECT_TYPE TABLENAME MB START_CREATION ----------------------- ----------------- ----------------- ----- -------------- CRP_OWN.IDX_CA_IDX INDEX CRP_OWN.CA_IMPORT 25 20130826 11.36 SYS.WRH$_SQLSTAT_INDEX INDEX PARTITION SYS.WRH$_SQLSTAT 0 20130826 23.20 SYS.WRH$_WAITSTAT_PK INDEX PARTITION SYS.WRH$_WAITSTAT 0 20130826 23.20
July - Meta Script for RMAN
Level (*) : 3 Topic (*) : DBA/Progr You have the RMAN recovery catalog database called REP1 in Oracle 10g. In REP1, you have a lot of Oracle users and usually each user contain information about a backed up database. You want to know which Oracle users contain recent information of the backed up databases. E.g. when you launch that query, you have to receive something like: ENVIRONMENT MAX_START_BCK_PIECE ------------- ------------------- RMAN_DB1 RMAN_DB2 2006-02-10 06.53 RMAN_DB3 2006-12-06 14.14 ... RMAN_DB8 2013-07-30 06.10 RMAN_DB9 2013-07-30 07.00
June - Meta Script for ROLES
Level (*) : 3 Topic (*) : DBA/Progr You have to find a query useful to give object privileges to the roles SELECT_ROLE and ALL_ROLE. On all the tables of the LASSI schema, this script must give: - grant SELECT to SELECT_ROLE - grant ALL to ALL_ROLE This query must be written to be executed from LASSI or from SYS: E.g. when you launch that query connected as LASSI, you have to receive something like: GRANT_ON_OBJECT --------------------------------------------------------- grant SELECT on PESO_CLIENTI to SELECT_ROLE; grant ALL on PESO_CLIENTI to ALL_ROLE; grant SELECT on VENDITE to SELECT_ROLE; grant ALL on VENDITE to ALL_ROLE; grant SELECT on ATTIVAZIONI to SELECT_ROLE; grant ALL on ATTIVAZIONI to ALL_ROLE; ...And when you launch the same query connected as SYS, you have to receive something like: GRANT_ON_OBJECT --------------------------------------------------------------- grant SELECT on LASSI.PESO_CLIENTI to SELECT_ROLE; grant ALL on LASSI.PESO_CLIENTI to ALL_ROLE; grant SELECT on LASSI.VENDITE to SELECT_ROLE; grant ALL on LASSI.VENDITE to ALL_ROLE; grant SELECT on LASSI.ATTIVAZIONI to SELECT_ROLE; grant ALL on LASSI.ATTIVAZIONI to ALL_ROLE; ...
May - Role problem
Level (*) : 2 Topic (*) : DBA/Progr You have to find a query useful to discover when the roles have been created on the db. E.g. When you launch that query, you have to receive something like: ROLE CREATION_TIME ------------- ------------------- CONNECT 18/09/2012 11:36:12 RESOURCE 18/09/2012 11:36:12 DBA 18/09/2012 11:36:12 ... UPDATE_ECOS 23/04/2013 10:41:16 SELECT_EFF 23/04/2013 11:44:44 Solution
You can use this query:
SELECT r.role
,to_char(ctime,'dd/mm/yyyy hh24:mi:ss') creation_time
FROM dba_roles r,
user$ u
WHERE r.ROLE = u.name
ORDER BY ctime;
April - EM cloud control
Level (*) : 3 Topic (*) : DBA You have an EM cloud control 12.1.0.2. In the interface, you see an error like this: EMGC_GCDomain/instance1/ohs1 Target DownHow can you see if that target is really down? If this alert is false, how can you avoid that EM shows it again? Which metalink note show this problem?
March - Data Guard
Level (*) : 2 Topic (*) : DBA You have a dataguard environment in Oracle 11.2. You have to find a query that has a result like this: In the primary db ORCL: Instance: ORCL Status: OPEN Startup: 20120912 22.24 NAME DATABASE_ROLE OPEN_MODE PROTECTION_MODE -------------------------- -------------------- ----------------------- -------------------- SWITCHOVER_STATUS STANDBY_BECAME_PRIMARY_SCN FS_FAILOVER_STATUS FS_FAILOVER_OBSERVER_HOST ------------------ -------------------------- ---------------------- ------------------------- Database: ORCL PRIMARY READ WRITE MAXIMUM PERFORMANCE TO STANDBY 10468375 TARGET UNDER LAG LIMIT HOST_075 Instance: ORCL Status: OPEN Startup: 20120912 22.24In the physical standby STDBY: NAME DATABASE_ROLE OPEN_MODE PROTECTION_MODE -------------------------- -------------------- ----------------------- -------------------- SWITCHOVER_STATUS STANDBY_BECAME_PRIMARY_SCN FS_FAILOVER_STATUS FS_FAILOVER_OBSERVER_HOST ------------------ -------------------------- ---------------------- ------------------------- Database: ORCL PHYSICAL STANDBY MOUNTED MAXIMUM PERFORMANCE SWITCHOVER PENDING 0 TARGET UNDER LAG LIMIT HOST_075 Instance: STDBY Status: MOUNTED Startup: 20120912 22.42In order to have a result like this, you should use the UNION operator. Solution
You have to read v$database and v$instance. Then the query is : SELECT 'Database: ' || name name, database_role, open_mode, protection_mode, switchover_over, standby_became_primary_scn, fs_failover_status, fs_failover_observer_host FROM v$database union all SELECT 'Instance: ' || instance_name, 'Status: ' || status, 'Startup: ' || to_char(STARTUP_TIME,'yyyymmdd hh24.mi'), null, null, null, null, null FROM v$instance;
February - Hidden Parameters
Level (*) : 2 Topic (*) : DBA You have to find a query in order to see some information about the Oracle hidden parameters that have the string 'shared_p' in their name. The result must be something like this: NAME VALUE DEFAULT TYPE ------------------------------- ------------ --------- -------- __shared_pool_size 1241513984 FALSE 6 _dm_max_shared_pool_pct 1 TRUE number _enable_shared_pool_durations TRUE TRUE boolean _io_shared_pool_size 4194304 TRUE 6 _shared_pool_max_size 0 TRUE 6 _shared_pool_minsize_on FALSE TRUE boolean _shared_pool_reserved_min_alloc 4400 TRUE 6 _shared_pool_reserved_pct 5 TRUE numberAnd, if you want to see all the hidden parameters? Solution
The query is : select a.ksppinm name, b.ksppstvl value, b.ksppstdf default, decode (a.ksppity, 1, 'boolean', 2, 'string', 3, 'number', 4, 'file', a.ksppity) type from sys.x$ksppi a, sys.x$ksppcv b where a.indx = b.indx and a.ksppinm like '\_%' escape '\' and lower(a.ksppinm) like '%shared_p%' order by 1;If you want to see all the parameters, use the same query removing the row and lower(a.ksppinm) like '%shared_p%'
January - ORA-600 error
Level (*) : 3 Topic (*) : DBA/Progr Your db is in Oracle 11.2.0.3. You have a SQL statement like this: MERGE INTO table_1 USING ( SELECT a.col_1, b.col_2 FROM tab_2 a, tab_3 b, tab_4 c .... ....Until few days ago, the result was fine but now you receive: ERROR at line 2: ORA-00600: internal error code, arguments: [kkmupsViewDestFro_4], [0], [85883], [], []Why now you have the error and before not? Which metalink note explain this error? What is a simple workaround to avoid it?
(*) 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
| |||||||||||||||||||||||||||||||||||||||||||||
