|
ORACLE Interview Questions and Answers
Part:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
(Continued from previous part...)
851. Explain UNION,MINUS,UNION ALL, INTERSECT ?
INTERSECT returns all distinct rows selected by both queries.MINUS - returns all distinct rows selected by the first query but not by the second.UNION - returns all distinct rows selected by either queryUNION ALL - returns all rows selected by either query, including all duplicates.
852. Should the OEM Console be displayed at all times (when there are scheduled jobs)? (for DBA
When a job is submitted the agent will confirm the status of the job. When the status shows up as scheduled, you can close down the OEM console. The processing of the job is managed by the OIA (Oracle Intelligent Agent). The OIA maintains a .jou file in the agent's subdirectory. When the console is launched communication with the Agent is established and the contents of the .jou file (binary) are reported to the console job subsystem.
Note that OEM will not be able to send e-mail and paging notifications when the Console is not started.
853. Difference between SUBSTR and INSTR ?
INSTR (String1,String2(n,(m)),INSTR returns the position of the mth occurrence of the string 2 instring1. The search begins from nth position of string1.SUBSTR (String1 n,m)SUBSTR returns a character string of size m in string1, starting from nth position of string1.
854. What kind of jobs can one schedule with OEM? (for DBA
OEM comes with pre-defined jobs like Export, Import, run OS commands, run sql scripts, SQL*Plus commands etc. It also gives you the flexibility of scheduling custom jobs written with the TCL language.
855. What are the pre requisites ?
I. to modify data type of a column ? ii. to add a column with NOT NULL constraint ? To Modify the datatype of a column the column must be empty. to add a column with NOT NULL constrain, the table must be empty.
856. How does one backout events and jobs during maintenance slots? (for DBA
Managemnet and data collection activity can be suspended by imposing a blackout. Look at these examples:
agentctl start blackout # Blackout the entrire agent
agentctl stop blackout # Resume normal monitoring and management
agentctl start blackout ORCL # Blackout database ORCL
agentctl stop blackout ORCL # Resume normal monitoring and management
agentctl start blackout -s jobs -d 00:20 # Blackout jobs for 20 minutes
857. What are the types of SQL Statement ?
Data Definition Language :
CREATE,ALTER,DROP,TRUNCATE,REVOKE,NO AUDIT & COMMIT.
Data Manipulation Language:
INSERT,UPDATE,DELETE,LOCK
TABLE,EXPLAIN PLAN & SELECT.Transactional Control:
COMMIT & ROLLBACKSession Control: ALTERSESSION & SET
ROLESystem Control :
ALTER SYSTEM.
858. What is the Oracle Intelligent Agent? (for DBA
The Oracle Intelligent Agent (OIA) is an autonomous process that needs to run on a remote node in the network to make the node OEM manageable. The Oracle Intelligent Agent is responsible for:
. Discovering targets that can be managed (Database Servers, Net8 Listeners, etc.);
. Monitoring of events registered in Enterprise Manager; and
. Executing tasks associated with jobs submitted to Enterprise Manager.
859. How does one start the Oracle Intelligent Agent? (for DBA
One needs to start an OIA (Oracle Intelligent Agent) process on all machines that will to be managed via OEM.
For OEM 9i and above:
agentctl start agent
agentctl stop agent
For OEM 2.1 and below:
lsnrctl dbsnmp_start
lsnrctl dbsnmp_status
On Windows NT, start the "OracleAgent" Service.
If the agent doesn't want to start, ensure your environment variables are set correctly and delete the following files before trying again:
1) In $ORACLE_HOME/network/admin: snmp_ro.ora and snmp_rw.ora.
2) Also delete ALL files in $ORACLE_HOME/network/agent/.
Can one write scripts to send alert messages to the console?
Start the OEM console and create a new event. Select option "Enable Unsolicited Event". Select test "Unsolicited Event". When entering the parameters, enter values similar to these:
Event Name: /oracle/script/myalert
Object: *
Severity: *
Message: *
One can now write the script and invoke the oemevent command to send alerts to the console. Look at this example:
oemevent /oracle/script/myalert DESTINATION alert "My custom error message"
where DESTINATION is the same value as entered in the "Monitored Destinations" field when you've registered the event in the OEM Console.
860. Where can one get more information about TCL? (for DBA
One can write custom event checking routines for OEM using the TCL (Tool Command Language) language. Check the following sites for more information about TCL:
. The Tcl Developer Xchange - download and learn about TCL
. OraTCL at Sourceforge - Download the OraTCL package
. Tom Poindexter's Tcl Page - Oratcl was originally written by Tom Poindexter
861. Are there any troubleshooting tips for OEM? (for DBA
. Create the OEM repository with a user (which will manage the OEM) and store it in a tablespace that does not share any data with other database users. It is a bad practice to create the repository with SYS and System.
. If you are unable to launch the console or there is a communication problem with the intelligent agent (daemon).
Ensure OCX files are registered. Type the following in the DOS prompt (the current directory should be $ORACLE_HOME\BIN:
C:\Orawin95\Bin> RegSvr32 mmdx32.OCX
C:\Orawin95\Bin> RegSvr32 vojt.OCX
. If you have a problem starting the Oracle Agent
Solution A: Backup the *.Q files and Delete all the *.Q Files ($Oracle_home/network/agent folder)
Backup and delete SNMP_RO.ora, SNMP_RW.ora, dbsnmp.ver and services.ora files ($Oracle_Home/network/admin folder)
Start the Oracle Agent service.
Solution B: Your version of Intelligent Agent could be buggy. Check with Oracle for any available patches. For example, the Intelligent Agent that comes with Oracle 8.0.4 is buggy.
Sometimes you get a Failed status for the job that was executed successfully.
Check the log to see the results of the execution rather than relying on this status.
(Continued on next part...)
Part:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
| 