|
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...)
789. What are the responsibilities of a Database Administrator ?
Installing and upgrading the Oracle Server and application tools.
Allocating system storage and planning future storage requirements for the database system. Managing primary database structures (tablespaces) Managing primary objects (table,views,indexes)
Enrolling users and maintaining system security. Ensuring compliance with Oralce license agreement Controlling and monitoring user access to the database. Monitoring and optimizing the performance of the database. Planning for backup and recovery of database information. Maintain archived data on tape
Backing up and restoring the database. Contacting Oracle Corporation for technical support.
790. What is a trace file and how is it created ?
Each server and background process can write an associated trace file. When an internal error is detected by a process or user process, it dumps information about the error to its trace. This can be used for tuning the database.
791. What are the roles and user accounts created automatically with the database?
DBA - role Contains all database system privileges.
SYS user account - The DBA role will be assigned to this account. All of the base tables and views for the database's dictionary are store in this schema and are manipulated only by ORACLE.
SYSTEM user account - It has all the system privileges for the database and additional tables and views that display administrative information and internal tables and views used by oracle tools are created using this username.
792. What are the minimum parameters should exist in the parameter file (init.ora) ?
DB NAME - Must set to a text string of no more than 8 characters and it will be stored inside the datafiles, redo log files and control files and control file while database creation.
DB_DOMAIN - It is string that specifies the network domain where the database is created. The global database name is identified by setting these parameters
(DB_NAME & DB_DOMAIN) CONTORL FILES - List of control filenames of the database. If name is not mentioned then default name will be used.
DB_BLOCK_BUFFERS - To determine the no of buffers in the buffer cache in SGA.
PROCESSES - To determine number of operating system processes that can be connected to ORACLE concurrently. The value should be 5 (background process)
and additional 1 for each user.
ROLLBACK_SEGMENTS - List of rollback segments an ORACLE instance acquires at database startup. Also optionally LICENSE_MAX_SESSIONS,LICENSE_SESSION_WARNING and LICENSE_MAX_USERS.
793. Why and when should I backup my database? (for DBA
Backup and recovery is one of the most important aspects of a DBAs job. If you lose your company's data, you could very well lose your job. Hardware and software can always be replaced, but your data may be irreplaceable!
Normally one would schedule a hierarchy of daily, weekly and monthly backups, however consult with your users before deciding on a backup schedule. Backup frequency normally depends on the following factors:
. Rate of data change/ transaction rate
. Database availability/ Can you shutdown for cold backups?
. Criticality of the data/ Value of the data to the company
. Read-only tablespace needs backing up just once right after you make it read-only
. If you are running in archivelog mode you can backup parts of a database over an extended cycle of days
. If archive logging is enabled one needs to backup archived log files timeously to prevent database freezes
. Etc.
Carefully plan backup retention periods. Ensure enough backup media (tapes) are available and that old backups are expired in-time to make media available for new backups. Off-site vaulting is also highly recommended.
Frequently test your ability to recover and document all possible scenarios. Remember, it's the little things that will get you. Most failed recoveries are a result of organizational errors and miscommunications.
794. What strategies are available for backing-up an Oracle database? (for DBA
The following methods are valid for backing-up an Oracle database:
Export/Import - Exports are "logical" database backups in that they extract logical definitions and data from the database to a file.
Cold or Off-line Backups - Shut the database down and backup up ALL data, log, and control files.
Hot or On-line Backups - If the databases are available and in ARCHIVELOG mode, set the tablespaces into backup mode and backup their files. Also remember to backup the control files and archived redo log files.
RMAN Backups - While the database is off-line or on-line, use the "rman" utility to backup the database.
It is advisable to use more than one of these methods to backup your database. For example, if you choose to do on-line database backups, also cover yourself by doing database exports. Also test ALL backup and recovery scenarios carefully. It is better to be save than sorry.
Regardless of your strategy, also remember to backup all required software libraries, parameter files, password files, etc. If your database is in ARCGIVELOG mode, you also need to backup archived log files.
795. What is the difference between online and offline backups? (for DBA
A hot backup is a backup performed while the database is online and available for read/write. Except for Oracle exports, one can only do on-line backups when running in ARCHIVELOG mode.
A cold backup is a backup performed while the database is off-line and unavailable to its users.
796. What is the difference between restoring and recovering? (for DBA
Restoring involves copying backup files from secondary storage (backup media) to disk. This can be done to replace damaged files or to copy/move a database to a new location.
Recovery is the process of applying redo logs to the database to roll it forward. One can roll-forward until a specific point-in-time (before the disaster occurred), or roll-forward until the last transaction recorded in the log files.
Sql> connect SYS as SYSDBA
Sql> RECOVER DATABASE UNTIL TIME '2001-03-06:16:00:00' USING BACKUP CONTROLFILE;
797. How does one backup a database using the export utility? (for DBA
Oracle exports are "logical" database backups (not physical) as they extract data and logical definitions from the database into a file. Other backup strategies normally back-up the physical data files.
One of the advantages of exports is that one can selectively re-import tables, however one cannot roll-forward from an restored export file. To completely restore a database from an export file one practically needs to recreate the entire database.
Always do full system level exports (FULL=YES). Full exports include more information about the database in the export file than user level exports.
(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
| 