THINGS TO KNOW IN ORACLE THINGS TO KNOW IN ORACLE
---1. Default location for alert logs? 1. Default location for alert logs?
a)Normally it is under $ORACLE_HOME/rdbms/logs, OR a)Normally it is under $ORACLE_HOME/rdbms/logs, OR
b)show parameter background_dump_dest (after login sqlplus / as sysdba). b)show parameter background_dump_dest (after login sqlplus / as sysdba).
[NOTE : $ORACLE_HOME/rdbms/trace for trace files] [NOTE : $ORACLE_HOME/rdbms/trace for trace files] 2. explain unmount, mount, and open?
2. explain unmount, mount, and open? in nomount-instance is started in nomount-instance is started
in mount - database started but datafile inaccessible in mount - database started but datafile inaccessible in open - datafiles put in accessible mode
in open - datafiles put in accessible mode 3.purpose of a pfile/spfile?
3.purpose of a pfile/spfile? to allocate system memory to allocate system memory
to point to control/admin file locations for the database to point to control/admin file locations for the database
4. is it possible to change location of admin/control file with database in mount 4. is it possible to change location of admin/control file with database in mount state?
state?
it aint possible because changing location requires changing pfile/spfile it aint possible because changing location requires changing pfile/spfile which have already been used by the time the database gets to MOUNT mode.
which have already been used by the time the database gets to MOUNT mode. 5. difference between obsolete and expired backup?
5. difference between obsolete and expired backup?
EXPIRED: Displays backup sets, proxy copies, and image copies marked in the EXPIRED: Displays backup sets, proxy copies, and image copies marked in the repository as expired, that is, "not found."To ensure that LIST EXPIRED shows repository as expired, that is, "not found."To ensure that LIST EXPIRED shows up-to-date output, issue a CROSSCHECK command periodically. When you issue a
to-date output, issue a CROSSCHECK command periodically. When you issue a CROSSCHECK command, RMAN searches on disk and tape for the backups and copies CROSSCHECK command, RMAN searches on disk and tape for the backups and copies recorded in the repository. If it does not find them, then it updates their recorded in the repository. If it does not find them, then it updates their repository records to status EXPIRED.
repository records to status EXPIRED. OBSOLETE:
OBSOLETE:
6. default location for spfile/pfile? 6. default location for spfile/pfile?
$ORACLE_HOME/dbs $ORACLE_HOME/dbs
7. default location for oracle password file? 7. default location for oracle password file?
$ORACLE_HOME/database $ORACLE_HOME/database
8. how do you set the oracle database to use a password file? 8. how do you set the oracle database to use a password file?
create an oracle password and you are up and running create an oracle password and you are up and running #orapwd file=PWDdb1.ora password=sys entries=6
#orapwd file=PWDdb1.ora password=sys entries=6 9. how do you unset the database recovery area?
9. how do you unset the database recovery area? 10. difference between a trace and an alert file? 10. difference between a trace and an alert file?
Each server and background process can write to an associated trace file. Each server and background process can write to an associated trace file. When an
When an
internal error is detected by a process, it dumps information about the error to internal error is detected by a process, it dumps information about the error to its trace
its trace
file. Some of the information written to a trace file is intended for the database file. Some of the information written to a trace file is intended for the database administrator, while other information is for Oracle Support Services. Trace file administrator, while other information is for Oracle Support Services. Trace file information is also used to tune applications and instances.
information is also used to tune applications and instances.
The alert file, or alert log, is a special trace file. The alert log of a database The alert file, or alert log, is a special trace file. The alert log of a database is a
is a
chronological log of messages and errors. chronological log of messages and errors. 11. what does the alert file capture? 11. what does the alert file capture?
An alert log file stores information about the deadlocks occuring in a An alert log file stores information about the deadlocks occuring in a database, examples are;
database, examples are;
*Data Corruption errors, Dead Lock information, Run out of space, Adding new *Data Corruption errors, Dead Lock information, Run out of space, Adding new space,
space, Database in Database in backup mode, backup mode, Command issues Command issues to change to change Database configuration,Database configuration, Database stuck due to archive log directory full, Database startup/shutdown time. Database stuck due to archive log directory full, Database startup/shutdown time. 12.what are the key environment variables?
12.what are the key environment variables? ORACLE_HOME and ORACLE_SID
ORACLE_HOME and ORACLE_SID ....PATH and ORACLE_BASE ....PATH and ORACLE_BASE
13. how do you start and stop a listener? 13. how do you start and stop a listener?
lsnrctl [start, stop, status] lsnrctl [start, stop, status] 14.what are the key oracle file? 14.what are the key oracle file?
tnsnames, sqlnet, listener tnsnames, sqlnet, listener
15.Difference between sqlnet, tnsnames and listener? 15.Difference between sqlnet, tnsnames and listener?
tnsnames > list of database connection info for client/server, This is a tnsnames > list of database connection info for client/server, This is a text file contains the information about the oracle databases.For each database, text file contains the information about the oracle databases.For each database, it must contains the db server name, db listener port number, and instance name. it must contains the db server name, db listener port number, and instance name.
sqlnet > communication parameter setup sqlnet > communication parameter setup
listener > list of databases to listen for on the machine listener > list of databases to listen for on the machine 16.How do i see the pl/sql procedure output?
16.How do i see the pl/sql procedure output? SET SERVEROUTPUT ON;
SET SERVEROUTPUT ON;
17.how do i execute a SQL file? 17.how do i execute a SQL file?
@filename.sql @filename.sql
18.how to see who is currently connected? 18.how to see who is currently connected?
SELECT username, program FROM v$session WHERE username IS NOT NULL; SELECT username, program FROM v$session WHERE username IS NOT NULL; 19.how do i recompile invalid objects?
19.how do i recompile invalid objects? @?/rdbms/admin/utlrp.sql
@?/rdbms/admin/utlrp.sql
20.how do i tell which database am in? 20.how do i tell which database am in?
SELECT name from v$database; SELECT name from v$database;
OR SELECT instance_name, host_name from v$instance; OR SELECT instance_name, host_name from v$instance; 21. how do u export and import in oracle?
21. how do u export and import in oracle?
> make sure you run the script $ORACLE_HOME/rdbms/admin/catexp.sql > make sure you run the script $ORACLE_HOME/rdbms/admin/catexp.sql >import and export use IMP/EXP utilities which are both located in >import and export use IMP/EXP utilities which are both located in $ORACLE_HOME/bin directory
$ORACLE_HOME/bin directory > c:\>EXP HELP=Y > c:\>EXP HELP=Y
> c:\>EXP USERID=scott/tiger OWNER=scott FILE=scott.dmp > c:\>EXP USERID=scott/tiger OWNER=scott FILE=scott.dmp
> c:\>IMP USERID=scott/tiger FILE=scott.dmp FULL=Y (which moves the dmp > c:\>IMP USERID=scott/tiger FILE=scott.dmp FULL=Y (which moves the dmp file)
file)
22.how to add different service to one oracle default listener? 22.how to add different service to one oracle default listener?
Just edit the SID_LIST ie; Just edit the SID_LIST ie;
23.default location of tnsnames, sqlnet and listener? 23.default location of tnsnames, sqlnet and listener?
$ORACLE_HOME\network\admin $ORACLE_HOME\network\admin
24.Steps to moving admin and controlfiles? 24.Steps to moving admin and controlfiles? ***************MOVING A CONTRROLFILE
***************MOVING A CONTRROLFILE -shutdown database
-shutdown database
-copy controlfile to new location -copy controlfile to new location
-edit pfile/spfile pointing to new controlfile location -edit pfile/spfile pointing to new controlfile location -restart instance
-restart instance
***************MOVING ADMIN FILES ***************MOVING ADMIN FILES
-you can do the same as for controlfile -you can do the same as for controlfile
-OR--Use the ALTER SYSTEM command -Use the ALTER SYSTEM command
[ALTER SYSTEM set background_dump_dest='' SCOPE='spfile'] [ALTER SYSTEM set background_dump_dest='' SCOPE='spfile'] 25.steps to partial movement of log/data files by renaming them?
25.steps to partial movement of log/data files by renaming them? ********************
********************
-shutdown the database -shutdown the database
-copy logfiles to the desired new location -copy logfiles to the desired new location -start the database in mount mode
-start the database in mount mode
-issue the ALTER DATABASE RENAME FILE '' TO ''; -issue the ALTER DATABASE RENAME FILE '' TO ''; -Open the database
-Open the database
-OR--put particular tablespace offline [alter tablespace INDS offline] -put particular tablespace offline [alter tablespace INDS offline] -use OS to move files to new location
-use OS to move files to new location
-rename files [alter tablespace INDS rename datafile '' to ''; -rename files [alter tablespace INDS rename datafile '' to ''; -put tablespace back online [alter tablespace INDS onnline] -put tablespace back online [alter tablespace INDS onnline]
26.steps to full movement of log/data files by controlfile recreation? 26.steps to full movement of log/data files by controlfile recreation? *******************
*******************
-backup the controlfile to trace and edit it according to the new desired -backup the controlfile to trace and edit it according to the new desired location
tnsnames, sqlnet, listener tnsnames, sqlnet, listener
15.Difference between sqlnet, tnsnames and listener? 15.Difference between sqlnet, tnsnames and listener?
tnsnames > list of database connection info for client/server, This is a tnsnames > list of database connection info for client/server, This is a text file contains the information about the oracle databases.For each database, text file contains the information about the oracle databases.For each database, it must contains the db server name, db listener port number, and instance name. it must contains the db server name, db listener port number, and instance name.
sqlnet > communication parameter setup sqlnet > communication parameter setup
listener > list of databases to listen for on the machine listener > list of databases to listen for on the machine 16.How do i see the pl/sql procedure output?
16.How do i see the pl/sql procedure output? SET SERVEROUTPUT ON;
SET SERVEROUTPUT ON;
17.how do i execute a SQL file? 17.how do i execute a SQL file?
@filename.sql @filename.sql
18.how to see who is currently connected? 18.how to see who is currently connected?
SELECT username, program FROM v$session WHERE username IS NOT NULL; SELECT username, program FROM v$session WHERE username IS NOT NULL; 19.how do i recompile invalid objects?
19.how do i recompile invalid objects? @?/rdbms/admin/utlrp.sql
@?/rdbms/admin/utlrp.sql
20.how do i tell which database am in? 20.how do i tell which database am in?
SELECT name from v$database; SELECT name from v$database;
OR SELECT instance_name, host_name from v$instance; OR SELECT instance_name, host_name from v$instance; 21. how do u export and import in oracle?
21. how do u export and import in oracle?
> make sure you run the script $ORACLE_HOME/rdbms/admin/catexp.sql > make sure you run the script $ORACLE_HOME/rdbms/admin/catexp.sql >import and export use IMP/EXP utilities which are both located in >import and export use IMP/EXP utilities which are both located in $ORACLE_HOME/bin directory
$ORACLE_HOME/bin directory > c:\>EXP HELP=Y > c:\>EXP HELP=Y
> c:\>EXP USERID=scott/tiger OWNER=scott FILE=scott.dmp > c:\>EXP USERID=scott/tiger OWNER=scott FILE=scott.dmp
> c:\>IMP USERID=scott/tiger FILE=scott.dmp FULL=Y (which moves the dmp > c:\>IMP USERID=scott/tiger FILE=scott.dmp FULL=Y (which moves the dmp file)
file)
22.how to add different service to one oracle default listener? 22.how to add different service to one oracle default listener?
Just edit the SID_LIST ie; Just edit the SID_LIST ie;
23.default location of tnsnames, sqlnet and listener? 23.default location of tnsnames, sqlnet and listener?
$ORACLE_HOME\network\admin $ORACLE_HOME\network\admin
24.Steps to moving admin and controlfiles? 24.Steps to moving admin and controlfiles? ***************MOVING A CONTRROLFILE
***************MOVING A CONTRROLFILE -shutdown database
-shutdown database
-copy controlfile to new location -copy controlfile to new location
-edit pfile/spfile pointing to new controlfile location -edit pfile/spfile pointing to new controlfile location -restart instance
-restart instance
***************MOVING ADMIN FILES ***************MOVING ADMIN FILES
-you can do the same as for controlfile -you can do the same as for controlfile
-OR--Use the ALTER SYSTEM command -Use the ALTER SYSTEM command
[ALTER SYSTEM set background_dump_dest='' SCOPE='spfile'] [ALTER SYSTEM set background_dump_dest='' SCOPE='spfile'] 25.steps to partial movement of log/data files by renaming them?
25.steps to partial movement of log/data files by renaming them? ********************
********************
-shutdown the database -shutdown the database
-copy logfiles to the desired new location -copy logfiles to the desired new location -start the database in mount mode
-start the database in mount mode
-issue the ALTER DATABASE RENAME FILE '' TO ''; -issue the ALTER DATABASE RENAME FILE '' TO ''; -Open the database
-Open the database
-OR--put particular tablespace offline [alter tablespace INDS offline] -put particular tablespace offline [alter tablespace INDS offline] -use OS to move files to new location
-use OS to move files to new location
-rename files [alter tablespace INDS rename datafile '' to ''; -rename files [alter tablespace INDS rename datafile '' to ''; -put tablespace back online [alter tablespace INDS onnline] -put tablespace back online [alter tablespace INDS onnline]
26.steps to full movement of log/data files by controlfile recreation? 26.steps to full movement of log/data files by controlfile recreation? *******************
*******************
-backup the controlfile to trace and edit it according to the new desired -backup the controlfile to trace and edit it according to the new desired location
-shutdown the database -shutdown the database
27.steps to manually create an oracle database? 27.steps to manually create an oracle database?
-create directories -create directories -create pfile
-create pfile
-startup nomount using pfile -startup nomount using pfile -run create database script -run create database script
-execute the catproc and catalog scripts -execute the catproc and catalog scripts
-alter database put it in mount and open mode -alter database put it in mount and open mode 28.steps to restore rman backup to a different node? 28.steps to restore rman backup to a different node?
-define the directories for the files -define the directories for the files
-connect to NODE 1 using rman and backup database to an accessible location -connect to NODE 1 using rman and backup database to an accessible location -Move the following files to the NODE 2 (+The database backup pieces,
-Move the following files to the NODE 2 (+The database backup pieces, +Controlfile backup piece,+The parameter file i.e init.ora file)
+Controlfile backup piece,+The parameter file i.e init.ora file)
-Edit the PFILE on NODE 2 to change the environment specific parameters -Edit the PFILE on NODE 2 to change the environment specific parameters like.(user_dump_dest=,background_dump_dest=,control_files=)
like.(user_dump_dest=,background_dump_dest=,control_files=)
-Once the PFILE is suitably modified invoke Rman on the NODE 2 after setting -Once the PFILE is suitably modified invoke Rman on the NODE 2 after setting the Oracle environment variables and start the database in nomount mode:
the Oracle environment variables and start the database in nomount mode: -Restore the controlfile from the backup piece.
-Restore the controlfile from the backup piece. -Mount the database.
-Mount the database.
RMAN> alter database mount ; RMAN> alter database mount ;
-Now catalog the backup pieces that were shipped from NODE 1. -Now catalog the backup pieces that were shipped from NODE 1.
RMAN> catalog backuppiece RMAN> catalog backuppiece
'/home/oracle/test/backup/o1_mf_annnn_TAG20070213T002925_2x21m6ty_.bkp'; '/home/oracle/test/backup/o1_mf_annnn_TAG20070213T002925_2x21m6ty_.bkp'; -Get to know the last sequence available in the archivelog backup using the -Get to know the last sequence available in the archivelog backup using the following command.
following command.
RMAN > list backup of archivelog all; RMAN > list backup of archivelog all;
-Rename the Redologfiles,so that they can be created in new locations when -Rename the Redologfiles,so that they can be created in new locations when opened the database is opened in resetlogs.
opened the database is opened in resetlogs. SQL> alter database rename file SQL> alter database rename file
'/u01/oracle/product/oradata/ora10g/log/redo01.log' to '/u01/oracle/product/oradata/ora10g/log/redo01.log' to '/home/oracle/test/log/redo01.log';...
'/home/oracle/test/log/redo01.log';...
-execute script to restore datafiles to new node and recover -execute script to restore datafiles to new node and recover
RMAN> run RMAN> run
{ set until sequence <seq_no> { set until sequence <seq_no> set newname for datafile 1 to set newname for datafile 1 to '/home/oracle/test/data/sys01.dbf';
'/home/oracle/test/data/sys01.dbf';
set newname for datafile 2 to set newname for datafile 2 to '/home/oracle/test/data/undotbs01.dbf';
'/home/oracle/test/data/undotbs01.dbf';
set newname for datafile 3 to set newname for datafile 3 to '/home/oracle/test/data/sysaux01.dbf';
'/home/oracle/test/data/sysaux01.dbf';
set newname for datafile 4 to set newname for datafile 4 to '/home/oracle/test/data/users01.dbf';
'/home/oracle/test/data/users01.dbf';
set newname for datafile 5 to '/home/oracle/test/data/1.dbf'; set newname for datafile 5 to '/home/oracle/test/data/1.dbf'; set newname for datafile 6 to
set newname for datafile 6 to '/home/oracle/test/data/sysaux02.dbf';
'/home/oracle/test/data/sysaux02.dbf';
set newname for datafile 7 to set newname for datafile 7 to '/home/oracle/test/data/undotbs02.dbf';
'/home/oracle/test/data/undotbs02.dbf'; restore database; restore database; switch datafile all; switch datafile all; recover database; recover database;
alter database open resetlogs; alter database open resetlogs; }
}
29.steps to creating and running rman backup scripts? 29.steps to creating and running rman backup scripts?
30.steps to manual database cloning with controlfile recreation? 30.steps to manual database cloning with controlfile recreation?
-create directories for the database you are to clone -create directories for the database you are to clone
-create init file for the clone with pointers to the locations of the new -create init file for the clone with pointers to the locations of the new
desired controlfile
-backup controlfile of database to clone, edit the backed up controlfile into a script pointing to the location of new datafiles and logfiles.
-shutdown database to clone and move the datafiles and redolog files to their desired new location
-startup the instance using a created pfile that points to the location of the controlfiles for the clone but startup should be in NOMOUNT mode.
-Now execute the controlfile script to create the controlfiles.
-open database with a RESETLOGS to avoid errors that may arise as a result of SCN number difference/change.
31.steps to manual database cloning without controlfile recreation? 32.difference between cloned and duplicate database?
33.steps to connect to rman using the catalog/auxilliary? 34.steps to recreating a lost controlfile?
35.why do we use a resetlog option when recreating a controlfile? 36.steps to manually upgrade a database?
-connect to database to be upgraded as sysdba -Analyse the database to be upgraded
[by executing the script /rdbms/utlu10.sql which is located in
oracle_home for new version --sript is executed from the old environment, execute a sql>spool info.log, execute the script utlulu10.sql, turn off spool sql>spool off, read the info.log for information about the database and what needs to be upgraded]
-Backup the database to be upgraded while in old environment
[sign on to RMAN: rman "target / nocatalog", issue backup command:RUN {
ALLOCATE CHANNEL chan_name TYPE DISK;
BACKUP DATABASE FORMAT some_backup_directory%U TAG before_upgrade;� � BACKUP CURRENT CONTROLFILE TO save_controlfile_location ;� �
} ]
-shutdown instances
-copy configuration files [spfile, password file] from old environment to your new home and then edit the pfiles accordingly]
-make sure Oracle services are stopped
-set your environment variables to the new release ie [oracle_home, path, oracle_base,Ld_library_path and oracle_sid]
-start sqlplus connecting with SYSDBA in your new release environment -startup upgrade with pfile [sql>startup upgrade pfile='']
-create the SYSAUX tablespace - dont forget to specify [CREATE TABLESPACE sysaux
DATAFILE '/u01/oradata/sysaux01.dbf' SIZE 700M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;] 'cause if extent management and segment space management are not set, sysaux tablespace cannot be created.
-spool upgrade log [sql>spool upgrade.log]
-Run the upgrade script[sql>@catupgrd.sql (this determines which upgrade scripts need to be run and then runs eac necessary script, according to your old release, this creates and upgrades data dictionary tables)
-Put spool off
-Run [sql>@utlu101s.sql TEXT], this specifies the status of the database components in the upgraded database.
-shutdown and restart the instance
-run [sql>@utlrp.sql], this recompiles any remaining stored PL/SQL and java code and also recompiles any invalid objects.
-YOUR DATABASE IS NOW UPGRADED. ***************In case you need recovery
-sign on to RMAN [rman "target / nocatalog"] -issue the recovery command
STARTUP NOMOUNT RUN
{
REPLICATE CONTROLFILE FROM save_controlfile_location ;� � ALTER DATABASE MOUNT;
RESTORE DATABASE FROM TAG before_upgrade ALTER DATABASE OPEN RESETLOGS;
}
*************************************************
37.explain steps for database backup and recovery using the O.S? 38.explain steps for database backup and recovery using RMAN? 39.what do u do incase of a mssing lib in oracle?
copy it to the directory where it's required and then execute [RELINK] $ORACLE_HOME/bin/relink all
OR bin# ./relink all
40.Explain differences between a hot backup and a cold/closed backup and the benefits of each?
A hot backup is basically taking a backup of the database while it is still up and running and it must be in archive log mode. A cold backup is taking a
backup of the database while it is shut down and does not require being in archive log mode. The benefit of taking a hot backup is that the database is still
available for use while the backup is occurring and you can recover the database to any point in time. The benefit of taking a cold backup is that it is typically easier to administer the backup and recovery process. In addition, since you are taking cold backups the database does not require being in archive log mode and thus there will be a slight performance gain as the database is not cutting archive logs to disk.
41.You have just had to restore from backup and do not have any control files. How would you go about bringing up this database?
I would create a text based backup control file, stipulating where on disk all the data files where and then issue the recover command with the using backup control file clause.
42.what is an oracle instance?
43.Describe what redo logs are & their advantage?
Redo logs are logical and physical structures that are designed to hold all the changes made to a database and are intended to aid in the recovery of a
database.
44.what are the background processes in oracle? 44.why set db_block size to 8192?
8192 size is the default size but not the most recommended, because it has to be bigger when it comes to dataware houses.
45. what are the default ports?
for oracle net listener and em repository port:1521/1526 [commonly used listener ports: 1522-1540]
for em:
oracle names server:1575
oracle connection manager:1630 http server listen port:80 EM Agent port:1831
EM reporting port:3339 iSQL plus: 5560/5580
46.how do you start?
em > emctl start dbconsole
isqlplus>isqlplus ctl [start/stop]
IN NOMOUNT> instant starts but doesnt utilise the controlfile yet IN MOUNT>control file is in use but datafiles are not open
IN OPEN>datafiles can be accessed
48.What s the benefit of dbms_stats over analyze ?� � � � �
49.41. How would you configure your networking files to connect to a database by the name of DSS which resides in domain icallinc.com?
50.Explain the concept of the DUAL table?
A single row table provided by oracle for selecting values and expressions. 51.What are the ways tablespaces can be managed and how do they differ?
52.From the database level, how can you tell under which time zone a database is operating?
select DBTIMEZONE from dual;
53.How do you recover a datafile that has not been physically been backed up since its creation and has been deleted. Provide syntax example?
54.You have found corruption in a tablespace that contains static tables that are part of a database that is in NOARCHIVE log mode. How would you restore the
tablespace without losing new data in the other tablespaces?
55.Typically, where is the conventional directory structure chosen for Oracle binaries to reside?
56.Explain undo retention?
57.How does Oracle guarantee data integrity of data changes?
58.Which environment variables are absolutely critical in order to run the OUI? 59.Explain how you would restore a database using RMAN to Point in Time?
60.Database crashes. Corruption is found scattered among the file system neither of your doing nor of Oracle s. What database recovery options are available?� Database is in archive log mode?
61.What query tells you how much space a tablespace named test is taking up, and� � how much space is remaining?
SQL>select sum(bytes/(1024*1024)) mb from dba_free_space where tablespace_name='TEST'
62.Which dictionary tables and/or views would you look at to diagnose a locking issue?
63.What is theh difference between an spfile and a pfile?
63.would an oracle instance start incase you lost the controlfile?
Yes it would but database wouldnt open because controlfile points to location of the physical files.
64.Explain the use of setting GLOBAL_NAMES equal to TRUE.
Setting GLOBAL_NAMES dictates how you might connect to a database. This variable is either TRUE or FALSE and if it is set to TRUE it enforces database links to have the same name as the remote database to which they are linking. 65. What command would you use to encrypt a PL/SQL application?
WRAP
66.Explain the difference between a FUNCTION, PROCEDURE and PACKAGE.
A function and procedure are the same in that they are intended to be a collection of PL/SQL code that carries a single task. While a procedure does not have to return any values to the calling application, a function will return a single value. A package on the other hand is a collection of functions and procedures that are grouped together based on their commonality to a business function or application.
67.Name three advisory statistics you can collect.
Buffer Cache Advice, Segment Level Statistics, & Timed Statistics 68.Where in the Oracle directory tree structure are audit traces placed by default?
In unix $ORACLE_HOME/rdbms/audit, in Windows the event viewer 69.Explain materialized views and how they are used.
Materialized views are objects that are reduced sets of information that have been summarized, grouped, or aggregated from base tables. They are typically used in
data warehouse or decision support systems.
70.When a user process fails, what background process cleans up after it? PMON
71.What background process refreshes materialized views? The Job Queue Processes.
72.How would you determine what sessions are connected and what resources they are waiting for?
Use of V$SESSION and V$SESSION_WAIT 73.How would you force a log switch? ALTER SYSTEM SWITCH LOGFILE;
74.Give two methods you could use to determine what DDL changes have been made. You could use Logminer or Streams
75.Explain steps in configuring your database for auditing? 76.What does coalescing a tablespace do?
Coalescing is only valid for dictionary-managed tablespaces and de-fragments space by combining neighboring free extents into large single extents.
77.Name a tablespace automatically created when you create a database. The SYSTEM tablespace.
78.What is the difference between a TEMPORARY tablespace and a PERMANENT tablespace?
A temporary tablespace is used for temporary objects such as sort structures while permanent tablespaces are used to store those objects meant to be used as the true objects of the database.
79.How do you add a data file to a tablespace? ALTER TABLESPACE ADD DATAFILE SIZE
80.How do you resize a data file? ALTER DATABASE DATAFILE RESIZE ;
81.What view would you use to look at the size of a data file? DBA_DATA_FILES
82.What view would you use to determine free space in a tablespace? DBA_FREE_SPACE
83.How would you determine who has added a row to a table? Turn on fine grain auditing for the table.
84. Explain steps to setting a database for fine grain auditing? 85.How can you rebuild an index?
ALTER INDEX REBUILD;
86.Explain what partitioning is and what its benefit is.
Partitioning is a method of taking large tables and indexes and splitting them into smaller, more manageable pieces.
87.You have just compiled a PL/SQL package but got errors, how would you view the errors?
88.How can you gather statistics on a table? The ANALYZE command.
89.How can you enable a trace for a session? Use the DBMS_SESSION.SET_SQL_TRACE or
Use ALTER SESSION SET SQL_TRACE = TRUE;
90.What is the difference between the SQL*Loader and IMPORT utilities?
These two Oracle utilities are used for loading data into the database. The
difference is that the import utility relies on the data being produced by another Oracle utility EXPORT while the SQL*Loader utility allows data to be loaded that has been produced by other utilities from different data sources just so long as it conforms to ASCII formatted or delimited files.
91.Name two files used for network connection to a database. TNSNAMES.ORA and SQLNET.ORA
Technical - UNIX
92.Why is a Where clause faster than a group filter or a format trigger?
Because, in a where clause the condition is applied during data retrieval than after retrieving the data.
93.What is the difference between TRUNCATE and DELETE?
The delete command will log the data changes in the log file where as the truncate will simply remove the data without it. Hence data removed by DELETE command can be rolled back but not the data removed by TRUNCATE. Truncate is a DDL statement whereas DELETE is a DML statement.
94. What is user Account in Oracle database?
A user account is not a physical structure in database but it is having important relationship to the objects in the database and will be having certain privileges. 95. How will you enforce security using stored procedures?
Don t grant user access directly to tables within the application.�
Instead grant the ability to access the procedures that access the tables.
When procedure executed it will execute the privilege of procedures owner. Users cannot access tables except via the procedure.
96. What are the dictionary tables used to monitor a database space? DBA_FREE_SPACE
DBA_SEGMENTS DBA_DATA_FILES.
97. What is OCI. What are its uses?
OCI is Oracle Call Interface. When applications developers demand the most
powerful interface to the Oracle Database Server, they call upon the Oracle Call Interface(OCI). OCI provides the most comprehensive access to all the Oracle
Database functionality. The newest performance, scalability and security features appear first in the OCI API. If you write applications for the Oracle Database, you likely already depend on OCI. Some types of applications that depend upon OCI
ar (PL/SQL apps executing SQL, C++ apps using OCCI, Java Apps using OCI-based JDBC driver, C Apps using ODBC driver, Pro*C apps, distributed SQL, VB apps using OLEDB driver)
98. What is Multi Node System?
Multi node system in oracle Applications 11i means you have Applications 11i
component on more than one system. Typical example is database, concurrent Manager on one machine and forms, webserver on second machine is example of two node
system.
99.What is a pseudo column. Give examples?
information such as row numbers and row descriptions ae automatically stored by oracle and is directly accessible i.e not through tables. This information is
contained within pseudo columns. These pseudo columns can be retrieved in queries. These pseudo columns can be included in queries within select data from tables.[A pseudocolumn behaves like a table column, but is not actually stored in the table. You can select from pseudocolumns, but you cannot insert, update, or delete their values.]
Availables pseudo columns are ROWNUM - order number in which a row values is retrieved ROWID-physical row(memory or disk address) location i.e the unique row identification, SYSDATE-system or today's date, UID - user identification number indicating the current user, USER - name of current logged in user.
100. What is TWO_TASK in oracle Database?
TWO_TASK mocks your tns alias which you are going to use to connect to database. Lets assume you ave database client with tns alias defined as PROD to connect to database PROD on machine teachmeoracle.com listening on port 1521. The usual way to connect is sqlplus username/passwd@PROD; now if you dont want to use @PROD then you set TWO_TASK=PROD and then can simply use sqlplus username/passwd then sql will check that it has to connect to tnsalias define by value PROD i.e TWO_TASK. 101. What is the difference between unique and primary key?
A unique key can have null whereas primary key is always not null. both bear unique names.
102.It is possible to use raw devices as data files and what is the advantages over file. system files ?
Yes. The advantages over file system files. I/O will be improved because Oracle is bye-passing the kernnel which writing into disk. Disk Corruption will be very
less.
103.What are disadvantages of having raw devices ?
We should depend on export/import utility for backup/recovery (fully reliable) The tar command cannot be used for physical file backup, instead we can use dd command which is less flexible and has limited recoveries.
104.What is the use of INCTYPE option in EXP command ?
Type export should be performed COMPLETE,CUMULATIVE,INCREMENTAL. List the sequence of events when a large transaction that exceeds beyond its optimal value when an entry wraps and causes the rollback segment toexpand into anotion Completes. e. will be written.
105.What is the use of FILE option in IMP command ?
The name of the file from which import should be performed. 106.What is a Shared SQL pool?
The data dictionary cache is stored in an area in SGA called the Shared SQL Pool. This will allow sharing of parsed SQL statements among concurrent users.
107.List the Optional Flexible Architecture (OFA) of Oracle database? or How can we organize the tablespaces in Oracle database to have maximum performance ? SYSTEM - Data dictionary tables.
DATA - Standard operational tables.
DATA2- Static tables used for standard operations INDEXES - Indexes for Standard operational tables.
INDEXES1 - Indexes of static tables used for standard operations. TOOLS - Tools table.
TOOLS1 - Indexes for tools table.
RBS - Standard Operations Rollback Segments,
RBS1,RBS2 - Additional/Special Rollback segments. TEMP - Temporary purpose tablespace
TEMP_USER - Temporary tablespace for users. USERS - User tablespace.
108.How to implement the multiple control files for an existing database ?
Shutdown the database Copy one of the existing control file to new location Edit Config ora file by adding new control file. name Restart the database.
109.What is advantage of having disk shadowing/ Mirroring ?
Shadow set of disks save as a backup in the event of disk failure. In most
Operating System if any disk failure occurs it automatically switchover to place of failed disk. Improved performance because most OS support volume shadowing can direct file I/O request to use the shadow set of files instead of the main set of files. This reduces I/O load on the main set of disks.
110.How will you force database to use particular rollback segment ? SET TRANSACTION USE ROLLBACK SEGMENT rbs_name.
111.Why query fails sometimes ?
Rollback segment dynamically extent to handle larger transactions entry loads. A single transaction may wipeout all available free space in the Rollback Segment Tablespace. This prevents other user using Rollback segments.
112.What is the use of RECORD LENGTH option in EXP command ? Record length in bytes.
113.How will you monitor rollback segment status ? Querying the DBA_ROLLBACK_SEGS view
IN USE - Rollback Segment is on-line.
AVAILABLE - Rollback Segment available but not on-line. OFF-LINE - Rollback Segment off-line
INVALID - Rollback Segment Dropped.
NEEDS RECOVERY - Contains data but need recovery or corupted.
PARTLY AVAILABLE - Contains data from an unresolved transaction involving a distributed database.
114.What is meant by Redo Log file mirroring ? How it can be achieved?
Process of having a copy of redo log files is called mirroring. This can be
achieved by creating group of log files together, so that LGWR will automatically writes them to all the members of the current on-line redo log group. If any one group fails then database automatically switch over to next group. It degrades performance.
115.Which parameter in Storage clause will reduce no. of rows per block? PCTFREE parameter
Row size also reduces no of rows per block. 116.What is meant by recursive hints ?
Number of times processes repeatedly query the dictionary table is called recursive hints. It is due to the data dictionary cache is too small. By increasing the SHARED_POOL_SIZE parameter we can optimize the size of Data Dictionary Cache.
117.What is the use of PARFILE option in EXP command ? Name of the parameter file to be passed for export.
118.What is the difference between locks, latches, enqueues and semaphores? (for DBA)
A latch is an internal Oracle mechanism used to protect data structures in the SGA from simultaneous access. Atomic hardware instructions like TEST-AND-SET is used to implement latches. Latches are more restrictive than locks in that they are always exclusive. Latches are never queued, but will spin or sleep until they obtain a resource, or time out.
and concurrency. They are queued and serviced in a first-in-first-out (FIFO) order.
Semaphores are an operating system facility used to control waiting. Semaphores are controlled by the following Unix parameters: semmni, semmns and semmsl. Typical settings are:
semmns = sum of the "processes" parameter for each instance (see init<instance>.ora for each instance)
semmni = number of instances running simultaneously; semmsl = semmns
119.What is a logical backup?
Logical backup involves reading a set of database records and writing them into a file. Export utility is used for taking backup and Import utility is used to
recover from backup.
120.What is a Rollback segment entry ?
It is the set of before image data blocks that contain rows that are modified by a transaction. Each Rollback Segment entry must be completed within one rollback segment. A single rollback segment can have multiple rollback segment entries. 121.What database events can be set? (for DBA)
The following events are frequently used by DBAs and Oracle Support to diagnose problems:
" 10046 trace name context forever, level 4 Trace SQL statements and show bind variables in trace output.
" 10046 trace name context forever, level 8 This shows wait events in the SQL trace files
" 10046 trace name context forever, level 12 This shows both bind variable names and wait events in the SQL trace files
" 1401 trace name errorstack, level 12 1401 trace name errorstack, level 4 1401 trace name processstate Dumps out trace information if an ORA-1401 "inserted value too large for column" error occurs. The 1401 can be replaced by any other Oracle Server error code that you want to trace.
" 60 trace name errorstack level 10 Show where in the code Oracle gets a deadlock (ORA-60), and may help to diagnose the problem.
The following lists of events are examples only. They might be version specific, so please call Oracle before using them:
" 10210 trace name context forever, level 10 10211 trace name context forever, level 10 10231 trace name context forever, level 10 These events prevent database block corruptions
" 10049 trace name context forever, level 2 Memory protect cursor " 10210 trace name context forever, level 2 Data block check
" 10211 trace name context forever, level 2 Index block check " 10235 trace name context forever, level 1 Memory heap check
" 10262 trace name context forever, level 300 Allow 300 bytes memory leak for connections
Note: You can use the Unix oerr command to get the description of an event. On Unix, you can type "oerr ora 10053" from the command prompt to get event details. 122.What are the different kind of export backups?
Full back - Complete database
Incremental - Only affected tables from last incremental date/full backup date. Cumulative backup - Only affected table from the last cumulative date/full backup date.
123.How does Space allocation table place within a block ? Each block contains entries as follows
Fixed block header Variable block header
Row Header,row date (multiple rows may exists)
PCTEREE (% of free space for row updation in future)
124.What are the factors causing the reparsing of SQL statements in SGA?
place while executing SQL statements. If the ratio is greater than 1 then increase the SHARED_POOL_SIZE. LOGICAL & PHYSICAL ARCHITECTURE OF DATWhat is dictionary cache ?
Dictionary cache is information about the databse objects stored in a data dictionary table.ABASE.
125.What is dictionary cache ?
Dictionary cache is information about the databse objects stored in a data dictionary table.
126.What is Database Buffers ?
Database buffers are cache in the SGA used to hold the data blocks that are read from the data segments in the database such as tables, indexes and clusters
DB_BLOCK_BUFFERS parameter in INIT.ORA decides the size. 127.What is a Control file ?
Database overall physical architecture is maintained in a file called control file. It will be used to maintain internal consistency and guide recovery operations. Multiple copies of control files are advisable.
128.How will you create multiple rollback segments in a database ?
Create a database which implicitly creates a SYSTEM Rollback Segment in a SYSTEM tablespace. Create a Second Rollback Segment name R0 in the SYSTEM tablespace. Make new rollback segment available (After shutdown, modify init.ora file and Start database) Create other tablespaces (RBS) for rollback segments. Deactivate Rollback Segment R0 and activate the newly created rollback segments.
130.What is cold backup? What are the elements of it?
Cold backup is taking backup of all physical files after normal shutdown of database. We need to take.
- All Data files. - All Control files.
- All on-line redo log files. - The init.ora file (Optional)
131.What is meant by redo log buffer ?
Changes made to entries are written to the on-line redo log files. So that they can be used in roll forward operations during database recoveries. Before writing them into the redo log files, they will first brought to redo log buffers in SGA and LGWR will write into files frequently. LOG_BUFFER parameter will decide the size.
132.How will you monitor the space allocation ? By querying DBA_SEGMENT table/view.
133.What is meant by free extent ?
A free extent is a collection of continuous free blocks in tablespace. When a segment is dropped its extents are reallocated and are marked as free.
134.What are the different methods of backing up oracle database ? - Logical Backups
- Cold Backups
- Hot Backups (Archive log)
135.The database instance crashed because of power failure. The database files are not affected by the crash. Which files will be used for the instance recovery at the next database instance startup? (Choose all that apply)
Data files Control file Redo log files
136.The database instance is started using SPFILE and the database is in MOUNT state. Which two operations can you perform in the MOUNT state of the database? (Choose two)
Renaming the data files
Configuring the database in the ARCHIVELOG mode
AND NEVER NEVER Creating new tablespace, Adding the database user
The restoration of files is not required for accomplishing the recovery. The unsolicited or wrongly committed transactions can be recovered.
AND NEVER NEVER The accidental loss of schema can be recovered, The accidental loss of tablespace can be recovered.
138. Which statement is true for the database configured in the ARCHIVELOG mode? You can take backup of the database without shutting down the database. AND NEVER NEVER Archiving information is written to the data files and redo log files, Flash recovery area must be used to store the archived redo log
files,The online redo log files must be multiplexed before putting the database in the ARCHIVELOG mode.
139.The abnormal termination of the database background process causes the
database instance to shut down without synchronizing the database files. Arrange the steps required for instance recovery?
i) SMON applies redo to the database ii) The database opens
iii)Uncommitted transactions are rolled back by SMON
NB:Perform recovery manually AND SMON reads the archived redo log files and online redo log files are not required.
140.The database is running and users are connected to the database instance using the LSNR1 listener. You observe that LSNR1 has stopped. Which two statements are true in this situation? (Choose all that apply)
-New connections are not allowed and -The connected sessions are not affected
141.UNDO_RETENTION is set to 900 in your database, but undo retention guarantee is not enabled. Which statement is true in this scenario?
Undo data is overwritten if no space is available for the new undo data AND NEVER NEVER Undo data is retained even if no space is available and new undo generated is stored in the temporary tablespace, Undo data becomes obsolete if a long-running transaction takes more time than that specified by UNDO_RETENTION, All undo data is purged if no space is available to accommodate new undo generated 142.What is a 2 Phase Commit?
Two Phase commit is used in distributed data base systems. This is useful to
maintain the integrity of the database so that all the users see the same values. It contains DML statements or Remote Procedural calls that reference a remote object. There are basically 2 phases in a 2 phase commit.
a) Prepare Phase : Global coordinator asks participants to prepare
b) Commit Phase : Commit all participants to coordinator to Prepared, Read only or abort Reply
143.What are mutating tables?
When a table is in state of transition it is said to be mutating. eg : If a row has been deleted then the table is said to be mutating and no operations can be done on the table except select.
144.Can U disable database trigger? How? Yes. With respect to table
ALTER TABLE TABLE [[ DISABLE all_trigger ]] 145.How many columns can table have?
The number of columns in a table can range from 1 to 254. 146.Is space acquired in blocks or extents ?
In extents .
147.What are attributes of cursor? %FOUND , %NOTFOUND , %ISOPEN,%ROWCOUNT
148.What are the various types of Exceptions ? User defined and Predefined Exceptions.
149.Can we define exceptions twice in same block ? No.
150.Can you have two functions with the same name in a PL/SQL block ? Yes.
151.Can you have two stored functions with the same name ? Yes.
152.What are the various types of parameter modes in a procedure ? IN, OUT AND INOUT.
153.What is Over Loading and what are its restrictions ?
OverLoading means an object performing different functions depending upon the no. of parameters or the data type of the parameters passed to it.
154.Can functions be overloaded ? Yes.
155.Can two functions have same name & input parameters but differ only by return datatype
No.
156.What are the constructs of a procedure, function or a package ? The constructs of a procedure, function or a package are :
variables and constants cursors
exceptions
157.Why Create or Replace and not Drop and recreate procedures ? So that Grants are not dropped.
158.Can you pass parameters in packages ? How ?
Yes. You can pass parameters to procedures or functions in a package. 159.What are the parts of a database trigger ?
The parts of a trigger are: A triggering event or statement A trigger restriction
A trigger action
160.What are the various types of database triggers ? There are 12 types of triggers, they are combination of : Insert, Delete and Update Triggers.
Before and After Triggers. Row and Statement Triggers. (3*2*2=12)
161.What is the advantage of a stored procedure over a database trigger ?
We have control over the firing of a stored procedure but we have no control over the firing of a trigger.
162.What is the maximum no. of statements that can be specified in a trigger statement?
One.
163.Can views be specified in a trigger statement ? No
164.What are the values of :new and :old in Insert/Delete/Update Triggers ? INSERT : new = new value, old = NULL
DELETE : new = NULL, old = old value
UPDATE : new = new value, old = old value
165.What are cascading triggers? What is the maximum no of cascading triggers at a time?
When a statement in a trigger body causes another trigger to be fired, the triggers are said to be cascading. Max = 32.
166.What are mutating triggers ?
A trigger giving a SELECT on the table on which the trigger is written. 167.Describe Oracle database's physical and logical structure ?
Physical : Data files, Redo Log files, Control file. Logical : Tables, Views, Tablespaces, etc.
168.Can you increase the size of a tablespace ? How ? Yes, by adding datafiles to it.
169.What are constraining triggers ?
A trigger giving an Insert/Updat e on a table having referential integrity constraint on the triggering table.
170.What is the use of Control files ?
Contains pointers to locations of various data files, redo log files, etc. 171.What is the use of Data Dictionary ?
Used by Oracle to store information about various physical and logical Oracle structures e.g. Tables, Tablespaces, datafiles, et
172.What are the disadvantages of clusters ? The time for Insert increases.
173.What are the advantages of clusters ? Access time reduced for joins.
174.What are the min. extents allocated to a rollback extent ? Two
175.An insert statement followed by a create table statement followed by rollback ? Will the rows be inserted ?
No.
176.What are the states of a rollback segment ? What is the difference between partly available and needs recovery ?
The various states of a rollback segment are :
ONLINE, OFFLINE, PARTLY AVAILABLE, NEEDS RECOVERY and INVALID. 177.What is the significance of the & and && operators in PL SQL ?
The & operator means that the PL SQL block requires user input for a variable. The && operator means that the value of this variable should be the same as inputted by the user previously for this same variable.
If a transaction is very large, and the rollback segment is not able to hold the rollback information, then will the transaction span across different rollback segments or will it terminate ?
It will terminate (Please check ).
178.Can you pass a parameter to a cursor ?
Explicit cursors can take parameters, as the example below shows. A cursor parameter can appear in a query wherever a constant can appear.
CURSOR c1 (median IN NUMBER) IS
SELECT job, ename FROM emp WHERE sal > median;
179.What are the various types of RollBack Segments ? Public Available to all instances
Private Available to specific instance
180.Can you use %RowCount as a parameter to a cursor ? Yes
181.What is Optimal Flexible Architecture?
A directory structure. It is a standard or a set of configuration guidelines created to ensure fast and reliable oracle databases that require little
maintenance. The main purposes thereof are
-organising large amounts of complicated software and data on disk to prevent hardware bottlenecks and poor performance, facilitating routine
administrative tasks that are highly vvulnerable to data corruption, facilitating switching between databases, managing the database growth, avoiding fragmentation of free space in the data dictionary, minimizing resource contention. In short oracle has designed the OFA as a file system directory structure that helps in maintaining multiple versions of multiple oracle products.
182. Which other protocols can oracle Net in 11g use?
In release 11g Oracle's network support is limited to TCP, TCP with secure socket,the newer Sockets Direct Protocol SDP and Windows Named Pipes(or WNP) 183.Explain is ORACLE NET
Oracle Net is a software component that enables connectivity between two oracle networks. It can also be defined as a suite of networking components that provide enterprise-wide connectivity solutions in distributed and heterogeneous computing environments. Oracle Net Services consists of
-Oracle Net, Listener, Oracle Connection Manager, Oracle Net configuration Assistant, Oracle Net Manager.
Database Upgrade Assistant is a GUI tool used to upgrade an existing
database. Oracle recommends using DBUA while upgrading a database and DBCA while creating a database.
185. Explain DBCA?
Database Configuration Assistant is a GUI-based oracle tool, which can run as part of OUI or as a standalone application and is used to create, configure, manage and delete a database. DBCA runs during database installation when the
create a database option is selected. The DBCA utility is able to examine database settings. It is also optionally used to export the current data of a database.
DBCA stores templates in a file with a .dbc extension, variables such as ORACLE_BASE and DB_NAME allow DBCA to install files into an appropriate directory for an oracle installation. While exporting data from the database, DBCA creates a zip file having extension .dfj, which consists of raw images of each data file. These images are automatically written directly to disk instead of using SQL
statements. Therefore, it is appropriate to use .dfj files for database creation, and not as a backup.
186. Explain OEM?
Oracle Enterprise Manager is a graphical system management tool that is used to manage components of Oracle and administer databases. OEM comprises a graphical console, management server, Oracle Intelligent Agent, repository database, and tools to provide an intergrated and comprehensive system management platform for managing oracle products. Tools intergrated with OEM can be used to examine the performance of an Oracle database and tune the database accordingly.
187.Scenario: you work as a DBA for Infotech Inc. The company used Oracle as its database. You have opened the database to perform update. Which files if lost can cause the database to crash?
Datafile from the SYSTEM tablespace and controlfile. Remember - if any of the control files and a datafile from the SYSTEM tablespace is missing, the
instance will immediately abort reason being that these files are responsible for a database to remain in the MOUNT mode.
AND NEVER NEVER archivelog file or datafile from SYSAUX reason being the instance can take the datafile from the SYSAUX tablespace offline automatically, and therefore is capable to remain open even if it is lost. and also if anything happened to an archived log file, it cannot have any effect on the instance. 188.Why do you have to open the database with RESETLOGS after recreating a controlfile?
189.which views are available in NOMOUNT mode? It's the V$INSTANCE and V$SESSION
AND NEVER NEVER and view prefixed with DBA_ are the data dictionary views which are only seen in OPEN mode and also YOU CAN NEVER find V$DATABASE and
V$DATAFILE because V$_ views are dynamic views that are populated from the control file and therefore are only available in mount mode or Open mode
190.Explain parameters?
USER_DUMP_DEST- an init parameter that specifies the destination in which the use processes inclusinve of background and foreground processes will write trace files.
BACKGROUND_DUMP_DEST - specifies location for trace files of background processes to be written and also location for the alert.log file
DB_CREATE_FILE_DEST- parameter to specify the default location for Oracle managed datafiles with properties [parameter type:string,
syntax:DB_CREATE_FILE_DEST=directory|disk group, default value: No default value, Modifiable:ALTER SESSION, ALTER SYSTEM], if DB_CREATE_ONLINE_LOG_DEST_n init
parameter is not specified then DB_CREATE_FILE_DEST is considered as the default location for oracle managed control files and online redo logs.
REMEMBER: there is no such parameter as background_trace_dest 191. Give types of segments?
Table, Data dictionary,index, cluster
SEGMENT: is a set of extents allocated for a specific database object such as mentioned above, each time a database object is created, oracle allocates a segment to it, this segment contains atleast one extent that intun contains atleast one block. A single segment holds all the data of the corresponding database object. A segment can belong to only one tablespace, but can be
associated to multiple datafiles. Extents allocated to a segment can belong to multiple datafiles but datablocks allocated to an extent can belong to only one datafile.
193.which command is used to exit from the Listener command utility without saving changes to the listener.ora file?
The QUIT command
194.Explain the Listener utility?
The LSNRCTL utility enables a database administrator to manage one or more listeners. It cannot be used to create or configure listeners. It only proides commands to control various listener functions listener functions such as
starting, stopping and getting status of listeners plus also changing parameter settings in the listener.ora file
START-starts a listener STOP-stops a listener
STATUS-sees status of a listener
SERVICES-checks services offered by the listener VERSION-shows version of a listener
RELOAD-forces a listener to re-read its entry in listener.ora file SAVE_CONFIG-used to write any changes made online to the listener TRACE-enables tracing of a listener's activity
CHANGE_PASSWORD-sets password for a listener's administration
EXIT-used to exit from the tool and save changes to the listener.ora file QUIT-used to exit the tool without saving changes made to the listener.ora file
SET-used to set various options such as tracing and timeouts SHOW-used to show options that have been set for a listener SERVICE and STATUS commands are used to see the status of a listener. REMEMBER - ABORT is not a listener command
195.Explain the Data pump utility and the files belonging to it?
Data pump is a new feature introduced in Oracle 10g to moev data between databases and to or from operating system files very efficiently. It provides parallel import and export utilities(impdb, expdp) on the command-line as well as the web-based oracle enterprise manager export/import interface. It is ideally beneficial for large databases and data waehousing environments. Oracle data pump facility runs on the server.
some of the functions performed by oracle data pump are; - it is used to copy data from one schema to another between two databases or within a single database and also it can be used to extract a logical copy of the entire database, a list of tablespaces and a list of schemas or a list of tables.
Types of files managed by the data pump are;
-dump files:containing the data and metadata that is being moved, Log
files:these record messages associated with an operation, SQL files:these record the output of a SQLFILE operation. a SQLFILE operation is invoked using the Data pump import SQLFILE parameter and results in all of the SQL DDL that import wil be executing based on other parameters, being writtent o a SQL file.
The Data pupm utility uses the export parameter CONTENT to filter the exported objects such as data, metedata or both.
196.Explain SQL*Loader?
197.When the SGA_TARGET is set to a non-zero value, apart from shared pool, buffer cache and Large pool which other memory structures are cconfigured as part of the Automatic Shared Memory Management feature in oracle?
It is the Java pool and Streams pool, because if SGA is specified then the memory pools that are automatically sized are Buffer cache(DB_CACHE_SIZE), share
pool(SHARED_POOL_SIZE), large pool(LARGE_POOL_SIZE), java pool(JAVA_POOL_SIZE), streams pool(STREAMS_POOL_SIZE)
NOTE: Streams pool is an optional SGA structure 198.Explain the flash_recovery_area?
The flash recovery area is a specific location on disk that stores and manages files for backup and recovery purposes. It is an automatic feature
available in Oracle 10g. Oracle Managed Files(OMF) configures the flash recovery area and utilises the disk resources managed by Automatic Storage Management(ASM). RMAN performs the task of automatic cleaning up. The flash recovery area acts as a cache area for the backup components that are to be copied to the tape. The flash recovery area contains the following database file: Control files, archive log files, flashback logs, control file and SPFILE auto-backups, and datafile image copies. It is necessary to configure the flash recovery area when the database is set up for the very first time.
199.When database is in noarchielog mode and you used RMAN to take backup. which types of backups are possible?
Only closed and Incremental backups
INCREMENTAL BACKUP is an RMAN backup in which only data blocks that were modified since the last incremental backup are backed up. Incremental backup are classified by levels. The baseline backup for an incremental backup is a level 0 incremental backup. A level 0 incremental backup, like a full backup, backs up all data blocks that have ever been used. However, a full backup cannot be used as the baseline backup for subsequent incremental backups.
Incremental backups at levels greater than 0 back up only data blocks modified since the last incremental backup. Forexample, a level 1 incremental backup will only backup only those data blocks that have changed since the level 0 incremental backup. furthermore, a level 2 incremental backup will back up only those data blocks that have changed since teh level 1 incremental backup.
Incremental backups are quicker, and they occupy less space because they do not contain all data blocks. Incremental backups can be applied to the baseline backup, wen required to form a complete backup.
A closed backup is a backup of one or more database files, which is taken while the database is closed. Usually, a closed backup is also a whole database backup(a backup of the control file and all datafiles of the database). A closed backup can be either consistent or inconsistent, A backup of a closed database that was
cleanly sutdown(NORMAL, IMMEDIATE, TRANSACTIONAL) is a closed consistent backup. On the other hand, if a database is shutdown using the ABORT command or the
associated instance terminates abnormally before the backup is taken, the backup will be a closed inconsistent backup. A closed backup is also called a cold backp and is the only type of backup that can be taken for a database running in
NOARCHIVELOG mode.
NOTE: that an open or online backup is one done while the datafiles are open and is always an inconsistent one that requires recovery(application of redo data) before it can be made consistent.
200.which files are created by default at database create command even if not specified?
-control file, online redo log files, SYSAUX tablespace datafile and SYSTEM tablespace datafile.
201.Explain Normalisation?
NOTE: Many-to-many is cardinality that is avoided in the normalisation technique 202.Which background process is used to write dirty blocks to disk?
DBWn (it doesnt change)
203.How long does it take for MMON to gather a snapshot and launch the ADDM? 60 minutes.
204.Which view shows all the tables in a database? it is the DBA_TABLE
AND NEVER NEVER user_tables, all_tables, dba_views 205.what is an aggregate function?
206.How do you convert a date to a string?
To_char. A bonus would be that they always include a format mask. 207.Describe the block structure of PLSQL?
Declaration, Begin, exception, end. 208.What is an anonymous block?
Unnamed PL/SQL block.
209.Why would you choose to use a package versus straight procedures and functions?
I look for maintenance, grouping logical functionality, dependency
management, etc. I want to believe that they believe using packages is a good� thing .�
210.Explain what happens when you issue a commit command?
211.Your developers asked you to create an index on the PROD_ID column of the SALES_HISTORY table, which has 100million rows. The table has approximately 2 million rows of new data loaded on the first day of every mont. For the remainder of the month, the table is only queried. Most reports are generated according to the PROD_ID, which has 96 distinct values. Which type of index would be
appropriate?
212.You examine the alert log file and notice that errors are being generated from an SQL*Plus session. Which files are best for providing you with more information about the nature of the problem?
213.Users pward and psmith have left the company. You nolonger want them to have access to the database. You need to make sure that the objects they created in the database remain. What do you need to do?
214.which two actions cause a log switch?
215.which facts do you know about a shared pool?
216.You need to create an index on the PASSPORT_RECORDS table. It contains 10 million rows of data. The key columns have low cardinality. The queries generated against this table use a combination of multiple WHERE conditions involving the OR operator. Which type of index would be best for this type of table?
217.Explain the concept of locally managed tablespace?
218.You decided to use multiple buffer pools in the database buffer cache of your database, You set the sizes of the buffer pools with the DB_KEEP_CACHE_SIZE and DB_RECYCLE_CACHE_SIZE parameters and restarted your instance. What else must you do to enable the use of the buffer pools?
219.How do you create a hierarchical query? By using a CONNECT BY.
220.How would you generate XML from a query?
The answer here is A lot of different ways . They should know that there� � are SQL functions: XMLELEMENT, XMLFOREST, etc and PL/SQL functions: DBMS_XMLGEN, DBMS_XMLQUERY, etc.
221.How can you tell if a SELECT returned no rows? By uisng the NO_DATA_FOUND exception.
222.What is an autonomous transaction?
Identified by pragma autonomous. A child transaction separate from the parent that MUST be committed or rolled back.
223.The DBA has decreased LOG_CHECKPOINT_INTERVAL to 6000 and changed
LOG_CECKPOINT_TIMEOUT from 0 to 5. What best identifies the impact of these settings on the oracle database?
224.which SQL*loader parameters enables you to load a specified number of records stored in the data file?
225.You are attempting to create a robust backup and recovery strategy using Oracle Enterprise Manager 2.0. which two tools allow you to start and stop the Oracle Instance?
improved against 10g?
Oracle 9i didnt have recovery_file_dest so had no flash recoevry area, no SYSAUX tablespace in 9i which is important for recovery.
227.Which roles must be granted to the recovery catalog owner in order for RMAN to work properly when you create a recovery catalog for a production database?
228.Explain rman commands? How you connect to an auxilliary and catalog database? sql>rman connect target sys/pwd@db1 CATALOG rman/pwd@catdb
sql>rman connect target sys/pwd@db1 AUXILLIARY sys/pwd@auxdb
229.You are backing up an oracle database on the UNIX platform while the database is still open. Before issuing the tar command, which of statements should be
issued?
230.You are evaluating the complexity of an existing recovery strategy in your organisation. Offline backups on OARCHIVELOG mode databases offer which of the following benefits over offline backups on ARCHIVELOG databases?
231.when a DBA attempts to backup the oracle database control file. after issuing the ALTER DATABASE BACKUP CONTROLFILE TO TRACE command, where can the DBA find the backup control file creation materials Oracle created for him?
232.You are trying to determine the status of a database backup. In order to determine the data files involved in the backup, which queries are appropriate?
SELECT file# from V$BACKUP where STATUS='ACTIVE'; this lists files being backed up(in backup progress)
233.You are attempting to identify synchronisation between files on a mounted database that was just backed up. Which dictionary view may offer assistance in this task?
234.which background process does an oracle database use to ensure the detection of the need for database recovery by checking for synchronisation?
235.Explain base tables?
A table used to define a view 236.Explain the concept of a large pool?
The large pool is created to avoid contention in the shared pool in the cae of shard server environment. The UGA process takes place in the shared pool in the case of shared server environment so to avoid contentions all the user processes are shifted
237.The DBA is configuring use of RMAN with a recovery catalog. When using a
recovery catalog in conjuction with RMAN, where can be the actual backup copies of database information found?
238.The DBA is assessing maintenance of recovery catalog creation. Once created, where does the recovery catalog draw much of its information from when maintenance is performed on it?
[options:production database file, recovery catalog control file, production database control file, archived redo logs]
239.The DBA is configuring RMAN for use as the backup/recovery tool for the oracle database. After creating the production database and the recovery catalog, which command is used for establishing the baseline information required in the recovery catalog for proper RMAN usage?
[options:RESET, REGISTER, RESYNC, REPORT]
240.The DBA has shutdown the database and made copies of all datafiles manually as a benchmark backup for stress testing. Which of the following RMAN commands will make that backup usable in the context of the rest of the contents of the recovery catalog?
[options:RESYNC, CATALOG, REPORT, LIST]
241.The DBA needs to identify available backups for the sys03.dbf datafile
belonging to the SYSTEM tablespace. Which command would be most appropriate for this process?
242.The DBA is developing scripts in RMAN. In order to store but not process commands in a scipt in RMAN for database backup, which of the following command choices are appropriate?
