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...)

167. How does one do off-line database backups? (for DBA

Shut down the database from sqlplus or server manager. Backup all files to secondary storage (eg. tapes). Ensure that you backup all data files, all control files and all log files. When completed, restart your database.
Do the following queries to get a list of all files that needs to be backed up:
select name from sys.v_$datafile;
select member from sys.v_$logfile;
select name from sys.v_$controlfile;
Sometimes Oracle takes forever to shutdown with the "immediate" option. As workaround to this problem, shutdown using these commands:
alter system checkpoint;
shutdown abort
startup restrict
shutdown immediate
Note that if you database is in ARCHIVELOG mode, one can still use archived log files to roll forward from an off-line backup. If you cannot take your database down for a cold (off-line) backup at a convenient time, switch your database into ARCHIVELOG mode and perform hot (on-line) backups.

168. What is the difference between SHOW_EDITOR and EDIT_TEXTITEM?

Show editor is the generic built-in which accepts any editor name and takes some input string and returns modified output string. Whereas the edit_textitem built-in needs the input focus to be in the text item before the built-in is executed.

169. What are the built-ins that are used to Attach an LOV programmatically to an item?

set_item_property
get_item_property
(by setting the LOV_NAME property)

170. How does one do on-line database backups? (for DBA

Each tablespace that needs to be backed-up must be switched into backup mode before copying the files out to secondary storage (tapes). Look at this simple example.
ALTER TABLESPACE xyz BEGIN BACKUP;
! cp xyfFile1 /backupDir/
ALTER TABLESPACE xyz END BACKUP;
It is better to backup tablespace for tablespace than to put all tablespaces in backup mode. Backing them up separately incurs less overhead. When done, remember to backup your control files. Look at this example:
ALTER SYSTEM SWITCH LOGFILE; -- Force log switch to update control file headers
ALTER DATABASE BACKUP CONTROLFILE TO '/backupDir/control.dbf';
NOTE: Do not run on-line backups during peak processing periods. Oracle will write complete database blocks instead of the normal deltas to redo log files while in backup mode. This will lead to excessive database archiving and even database freezes.

171. How does one backup a database using RMAN? (for DBA

The biggest advantage of RMAN is that it only backup used space in the database. Rman doesn't put tablespaces in backup mode, saving on redo generation overhead. RMAN will re-read database blocks until it gets a consistent image of it. Look at this simple backup example.
rman target sys/*** nocatalog
run {
allocate channel t1 type disk;
backup
format '/app/oracle/db_backup/%d_t%t_s%s_p%p'
( database );
release channel t1;
}
Example RMAN restore:
rman target sys/*** nocatalog
run {
allocate channel t1 type disk;
# set until time 'Aug 07 2000 :51';
restore tablespace users;
recover tablespace users;
release channel t1;
}
The examples above are extremely simplistic and only useful for illustrating basic concepts. By default Oracle uses the database controlfiles to store information about backups. Normally one would rather setup a RMAN catalog database to store RMAN metadata in. Read the Oracle Backup and Recovery Guide before implementing any RMAN backups.
Note: RMAN cannot write image copies directly to tape. One needs to use a third-party media manager that integrates with RMAN to backup directly to tape. Alternatively one can backup to disk and then manually copy the backups to tape.

172. What are the different file extensions that are created by oracle reports?

Rep file and Rdf file.

173. What is strip sources generate options?

Removes the source code from the library file and generates a library files that contains only pcode. The resulting file can be used for final deployment, but can not be subsequently edited in the designer.ex. f45gen module=old_lib.pll userid=scott/tiger strip_source YES output_file

173. How does one put a database into ARCHIVELOG mode? (for DBA

The main reason for running in archivelog mode is that one can provide 24-hour availability and guarantee complete data recoverability. It is also necessary to enable ARCHIVELOG mode before one can start to use on-line database backups. To enable ARCHIVELOG mode, simply change your database startup command script, and bounce the database:
SQLPLUS> connect sys as sysdba
SQLPLUS> startup mount exclusive;
SQLPLUS> alter database archivelog;
SQLPLUS> archive log start;
SQLPLUS> alter database open;
NOTE1: Remember to take a baseline database backup right after enabling archivelog mode. Without it one would not be able to recover. Also, implement an archivelog backup to prevent the archive log directory from filling-up.
NOTE2: ARCHIVELOG mode was introduced with Oracle V6, and is essential for database point-in-time recovery. Archiving can be used in combination with on-line and off-line database backups.
NOTE3: You may want to set the following INIT.ORA parameters when enabling ARCHIVELOG mode: log_archive_start=TRUE, log_archive_dest=... and log_archive_format=...
NOTE4: You can change the archive log destination of a database on-line with the ARCHIVE LOG START TO 'directory'; statement. This statement is often used to switch archiving between a set of directories.
NOTE5: When running Oracle Real Application Server (RAC), you need to shut down all nodes before changing the database to ARCHIVELOG mode.

174. What is the basic data structure that is required for creating an LOV?

Record Group.

175. How does one backup archived log files? (for DBA

One can backup archived log files using RMAN or any operating system backup utility. Remember to delete files after backing them up to prevent the archive log directory from filling up. If the archive log directory becomes full, your database will hang! Look at this simple RMAN backup script:
RMAN> run {
2> allocate channel dev1 type disk;
3> backup
4> format '/app/oracle/arch_backup/log_t%t_s%s_p%p'
5> (archivelog all delete input);
6> release channel dev1;
7> }

176. Does Oracle write to data files in begin/hot backup mode? (for DBA

Oracle will stop updating file headers, but will continue to write data to the database files even if a tablespace is in backup mode.
In backup mode, Oracle will write out complete changed blocks to the redo log files. Normally only deltas (changes) are logged to the redo logs. This is done to enable reconstruction of a block if only half of it was backed up (split blocks). Because of this, one should notice increased log activity and archiving during on-line backups.

(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