Preview only show first 6 pages with water mark for full document please download
Transcript
What are the pre-requisites of the Oracle Installation?
Prerequisites:
Make sure your process architecture is certified by oracle corporation:
$uname –m
$grep “model name” /proc/cpuinfo
Make sure that your linux distribution is certified by oracle corporation.
$cat /etc/redhat-release
Make sure that all of the required operating system packages have been installed on the
database server. Which package and versions are required will vary depending on your linux
distribution and hardware platform. You can verify that a package has been installed:
$rpm –q
Make sure that your hardware is sufficient. At least 1 GB RAM, 1.5 times the amount of
physical memory and bare minimum of 6.5 GB of disk space.
$grep MemTotal /proc/meminfo
$grep SwapTotal /proc/meminfo
Oracle installer will need access to a directory with at least 1 GB of free space for writing
temporary files during installation.
Make sure that the linux kernel on the database server has parameters set sufficiently high for
oracle. You can change kernel parameters in linux simply by editing the /etc/sysctl.conf file
and rebooting the server.
Create a linux group that will be used by the oracle software owner. You can call it anything
you like, but the standard is “oinstall”. You can create your oinstall group with a command
like:
$/usr/sbin/groupadd –g 501 oinstall
Create a linux group that will be used by oracle database administrator. You can call it
anything you like, but the standard is “dba”. You can create your dba group with a command
like:
$/usr/sbin/groupadd –g 502 dba
Create a linux user that will be the oracle software owner. You can call it anything you like,
but the standard is “Oracle”.
$/usr/sbin/useradd –c ‘Oracle software owner’ –d /home/oracle –g oinstall –G dba –m –u 501
–s /bin/bash oracle
$passwd oracle
It is necessary to increase limits that the shell imposes on the oracle user for maximum
number of open file descriptors and process.
Create mount points for the oracle software and the oracle database. Each mount point should
correspond to a separate physical device or set of devices. You’ll need at least one mount
point. Create an app/oracle subdirectory below the software mount point, and oradata
subdirectories below the mount point to be used for database files. Make these subdirectories
owned by the oracle user and oinstall group, and give them 775 permissions, except for the
datafile directory, give this 750 permissions.
If you downloaded the oracle software then use unzip to unpack the distribution. If you have
the software on CD or DVD, then mount the media.
Edit the oracle user’s login file on the database server so that the environment will be
configured automatically on login. If you are using bash shell, then edit .bash_profile.
Log out and log back in as the oracle user from an X window so that the environment is set
correctly.
Make sure your DISPLAY variable is set. If you had to set your DISPLAY variable manually,
then ensure that the X server on your workstation will allow your database server to open
windows on your display.
Ensure that the mount point you plan to use for the oracle software has sufficient free space.
Log in as oracle user, and start OUI with runInstaller command. Follow the oracle installation
dialog box and respond to the prompt.
How to upgrade the database from 8i to 10g?
Upgrading Manually
The steps for a manual upgrade to the Oracle Database 10g database version are following:
Create a spool file.
SQL> SPOOL upgrade.log
2. Log in as a user with the SYSDBA privilege, and run the Pre-Upgrade Information Tool.
Make any changes it recommends.
To start the Pre-Upgrade Information Tool, run the following:
SQL> @$ORACLE_HOME\rdbms\admin\utlu102i.sql
To see the results of the pre-upgrade check, turn spooling off with the following command:
SQL> spool off
Check the upgrade.log spool file to see if you meet all upgrade requirements.
Back up the database you’re going to upgrade.
Copy the current init.ora file to the new Oracle Database 10g init.ora file location.
Copy your current init.ora file to its default location in the new Oracle Database 10g Release
2 home ($ORACLE_HOME/dbs). You should also make the changes that the Pre-Upgrade
Information Tool recommended. Remove all obsolete and deprecated parameters and add the
new parameters, such as SGA_TARGET, which automates shared memory management.
Shut down the database and restart it under the new Oracle 10g home in the STARTUP
UPGRADE mode.
Shut down the current database if it’s running, and start it up again with the updated init.ora
parameter file under the new Oracle Database 10g home.
Create the required Sysaux tablespace.
SQL> CREATE TABLESPACE sysaux DATAFILE '/u10/oradata/prod/sysaux01.dbf'
SIZE 500M REUSE
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;
The database is now technically converted into an Oracle Database 10g version
SQL> SELECT * FROM V$VERSION;
Run the catupgrd.sql upgrade script.
Make sure you’re logged in as a user with SYSDBA privileges, and run the upgrade script
from the new environment:
SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql
Check to see if any objects became invalidated during the database upgrade.
SQL> SELECT count(*) FROM DBA_OBJECTS
WHERE status = 'INVALID';
Run the utlrp.sql script to recompile any objects that became invalid during the database
upgrade.
You can recompile all invalidated Oracle database objects using the utlrp.sql script:
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql
To check that there aren’t any invalid objects left, you should run the check again:
SQL> SELECT count(*) FROM dba_objects
WHERE status = 'INVALID';
Run the Post-Upgrade Status Tool.
After the upgrade process completes, you must run the Post-Upgrade Status Tool, using the
following script:.
SQL> @utlu102s.sql
The Post-Upgrade Status Tool summarizes the upgrade process.
Caution Don’t start the newly upgraded database under the old Oracle home—this corrupts
your database.
End the spool file.
After the upgrade script had finished, you can turn off the spooling of the upgrade process:
SQL> SPOOL OFF
Shutting Down and Starting Up the New Database
You can now shut down and restart the instance so you’re ready for normal database
operations:
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP
It’s a good idea to promptly back up the new database. You must also change the passwords
for the Oracle-supplied user accounts if you manually upgraded the database. You may also
need to modify the listener.ora file, as well as migrate to the SPFILE from your init.ora file.
Choose to migrate all your dictionary-managed tablespaces to locally managed tablespaces
by using the DBMS_SPACE_ADMIN package.
SQL>EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL
('TEST1');
Error in RMAN backup and recovery.
RMAN fails with ORA-01031 (insufficient privileges) or ORA-01017 (invalid
username/password) errors when trying to connect to the target database
RMAN-10003: unable to connect to target database using connect string
system/manager@proddb"
RMAN-04004: error from recovery catalog database: ORA-28009: connection as SYS should
be as SYSDBA or SYSOPER
RMAN-20202: Tablespace not found in the recovery catalog
RMAN-03009: failure of backup command on ch2 channel at 11/07/2011 17:37:00
ORA-19504: failed to create file "/PDF/AMDD_BACKUP/AMDD_Lvl0_Hot_Back"
ORA-27038: created file already exists
RMAN-03009: failure of allocate command on t1 channel at 07/21/2005 13:49:06
ORA-19554: error allocating device, device type: SBT_TAPE, device name:
ORA-27211: Failed to load Media Management Library
RMAN-03009: failure of backup command on ch00 channel at 02/04/2010 23:34:43
ORA-19566: exceeded limit of 0 corrupt blocks for file
/db/FINPRODDATA/oradata/psindex.dbf
RMAN-06012: channel: C2 not allocated
RMAN-06023: no backup or copy of datafile 4 found to restore
RMAN-06059: expected archived log not found, lost of archived log
compromises recoverability
RMAN-05001: auxiliary file name conflicts with a file used by the target database . RMAN05501 Getting this when creating standby database
RMAN-06002: command not allowed when not connected to a recovery catalog
What will you do if the archive location becomes full?
A: Once the archive destination becomes full the location also becomes invalid. Normally
Oracle does not do a recheck to see if space has been made available.
Using the command:
alter system archive log all to '/u07/app/oradata/DBADB/archive';
gives Oracle a valid location for the archive logs. Even after using this, the archive log
destination parameter is still invalid and automatic archive does not work. This solution is
best for systems that do not archive regularly but cannot be shutdown. You can also use this
to allow you to do a Shutdown immediate instead of Shutdown abort.
Shutdown and restart of the database resets the archive log destinstation parameter to be
valid. Do not forget to make disk space available before starting the database.
Use the REOPEN attribute of the LOG_ARCHIVE_DEST_n parameter to determine whether
and when ARCn attempts to re-archive to a failed destination following an error. REOPEN
applies to all errors, not just OPEN errors. REOPEN=n sets the minimum number of seconds
before ARCn should try to reopen a failed destination. The default value for n is 300 seconds.
A value of 0 is the same as turning off the REOPEN option, in other words, ARCn will not
attempt to archive after a failure. If you do not specify the REOPEN keyword, ARCn will
never reopen a destination following an error.
alter system archive log stop;
alter system archive log start;
A tablespace is in begin backup mode and my database crashes – what would be the
result
A: we cannot startup a database whose any of the datafile is in backup mode.
AT STARTUP AFTER CRASH WITH TABLESPACE(S) IN HOT BACKUP
*******************************************************
1. Mount the database.
SQL> STARTUP MOUNT;
2. Find out which datafiles were in hot backup mode when the database crashed or was
shutdown abort or the machine was rebooted by running the query:
SQL> SELECT V1.FILE#, NAME
FROM V$BACKUP V1, V$DATAFILE V2
WHERE V1.STATUS = 'ACTIVE' AND V1.FILE# = V2.FILE#;
3. For each of the files returned by the above query, issue the command:
SQL> ALTER DATABASE DATAFILE '' END BACKUP;
4. Open the database.
SQL> ALTER DATABASE OPEN;
I have a corrupted system datafile for the database what will you do
A: We need to start the database in mount mode, then restore the system datafile to its proper
location and apply the recover database command. When we get recovery completion
message, we open the database.
How do I detect corruptions on the database
A: We detect the corruptions on the database by using DBVERIFY utility. It is used to
perform a physical data structure integrity check on datafiles whether the database is online
or offline. We invoke the dbverify utility from the O/S command line
$dbv file=’’ log=’’ block=’’
A datafile of the non system tablespace is corrupt – what will I do
A: First of all, we change the status of relevant datafiles to offline, restore the datafiles to its
proper location and apply the ALTER DATABASE RECOVER DATAFILE ‘’;
once the datafile is recovered, we bring back the datafile in online status.
How do you diagnose a lock issue
A: The first thing that we need to do is to know what the session is actually doing, whether it
is idle, processing or waiting. If session is in waiting state, we need to find out which thing
makes session waiting. We diagnose the problem using STATE and EVENT column of
V$SESSION view. If the session is waiting for anything, we need to find out how long the
session has been waiting by using SESSION_IN_WAIT column. A very long wait usually
indicates some sort of bottleneck. The EVENT column shows not only the event a session is
experiencing now but also the last wait event the session has experienced. Another column—
WAIT_TIME—in the same V$SESSION view shows how long that wait lasted. The next
logical question is which session holds that lock the answer comes with
BLOCKING_SESSION column. If the session holding the lock is an orphan session, you
may want to kill it to release the lock. Find the SQL statement the session is executing. We
also query the V$LOCK view to identify the blocking session.
Describe how to trace a database session
A: To trace a particular session, we need session id and serial number for that session. Once
we have sid and serial#, we invoke
execute dbms_system.set_sql_trace_in_session(,, true);
at sql prompt, we run the task to demonstrate the problem and after executing the task, we
disable the trace by invoking
execute dbms_system.set_sql_trace_in_session(,, false);
It will create a dump file in user_dump_dest location in the database. We need to convert this
dump file in human readable format by using tkprof utility.
A query is going for full table scans , what will you do
A: When a query goes to full table scan, we need to focus on the number of rows in that table,
if it is a small table, full table scan does not matter, but with the large table, we need to check
whether stats for related table are latest or not. Sometimes, a missing or corrupted index also
cause the full table scan.
A tablespace with 2 datafiles was added after the last backup taken. One of the datafiles
has become corrupt. How do I deal with this scenario
A: we need to create a new empty datafile to replace the damaged datafile that has no
corresponding backup then recover the new datafile by applying archivelog files.
What are the advantages of using RMAN
A: The RMAN utility has following advantages over user managed backup:
It automatically manages the backup files without DBA intervention.
It automatically deletes unnecessary backup datafiles and archived redo log files both from
disk and tape
It provides you with detailed reporting of backup actions.
It provides considerable help in duplicating a database or creating a standby database.
It lets you verify that available backups are usable for recovery.
It lets you make incremental backups, which isn’t possible by any other means of any backup.
It automatically detects corrupt data blocks during backups, with the corruption relevant
information recorded in the V$DATABASE_BLOCK_CORRUPTION view.
When only a few data blocks are corrupted, you can recover at the data block level, instead of
recovering an entire datafile.
You can take advantage of the unused block compression feature, wherein RMAN skips
unused data blocks during a backup.
Only RMAN provides the ability to perform encrypted backups.
What is CPU Patches and how to install ?
An n-apply CPU patch consists of groups of security fixes, where each group is called a
molecule. Each molecule is an independent patch and does not conflict with any of the other
molecule patches within the CPU. Prior to the patch installation, the following checks are
performed for each molecule patch being installed:
Conflicts with other patches
Subset patches (patch to be installed is a subset of an existing patch in the Oracle Home)
Duplicates (patch to be installed is the same as an existing patch in the Oracle Home)
If a conflict is found, the installation will stop and you will be notified to file a merge request
for the conflicting patches.
Select one of the following options:
Option 1: Resolve patch conflicts and install CPU together
File an SR for the merge request indicating all patches that conflict with the CPU as reported
by OPatch.
Install the merge patches when they become available.
Install the CPU patch.
Option 2: Perform a partial installation of the CPU and resolve the conflicts later
At the conclusion of the aborted installation, OPatch returns the necessary command to install
all molecules that do not conflict with patches in the Oracle home. Run this command to
perform a partial CPU installation.
File an SR for the merge request indicating all patches that conflict with the CPU as reported
by OPatch.
Install the merge patches when they become available.
If a subset or duplicate is found, the patch installation will continue. The subset and duplicate
patches will not be installed as they are already installed.
export PATH=$PATH:/usr/ccs/bin
unzip p14038791_112020_.zip
cd 14038791
opatch napply -skip_subset -skip_duplicate
cd $ORACLE_HOME/rdbms/admin
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> STARTUP
SQL> @catbundle.sql cpu apply
SQL> QUIT
Check the following log files in $ORACLE_BASE/cfgtoollogs/catbundle for any errors:
catbundle_CPU__APPLY_.log
catbundle_CPU__GENERATE_.log
First of all what will check in awr report?
First of all we need to check the elapsed time and the database time. The database time
should be greater than elapsed time. Database time/elapsed time gives us database load means
average active sessions. Then we focus on foreground wait events, total wait/(elapsed time *
60) gives average inactive sessions. Then we focus on time model statistics, check how many
percent of DB time is for sql execution. Then we focus on top 5 timed foreground events and
make sure which kind of waits is being experienced. If wait belongs to user input/output,
check tablespace, datafile, wait histogram, segment reads and sql by reads. It would be better
to review disk and move one datafile. It wait belongs to cpu, then check os cpu, sql by cpu
and sql by gets. It would be better to add index on inventories. If wait belongs to commit,
then check load profile and instance stats. This is optimal for OLTP instance, short
transactions and frequent commits. If wait is due to lock, then check histogram and segments.
The solution is to apply lock on only required table.
Suppose top wait event are showing buffer busy wait,db file sequential read,what will
you do?
Buffer busy wait:
This wait happens when a session wants to access a database block in the buffer cache but it
cannot as the buffer is "busy". The two main cases where this can occur are:
Another session is reading the block into the buffer
Another session holds the buffer in an incompatible mode to our request
Normal wait time is 1 second. If the session has been waiting for an exclusive buffer during
the last wait then it waits 3 seconds this wait. The session will keep timing-out/waiting until it
acquires the buffer.
Finding the blocking process can be quite difficult as the information required is not
externalised. If P3 (Reason Code) shows that the "buffer busy wait" is waiting for a block
read to complete then the blocking session is likely to be waiting on an IO wait (eg: "db file
sequential read" or "db file scattered read") for the same file# and block#.
If the wait is due to the buffer being held in an incompatible mode then it should be freed
very soon.
Db_file_sequential_read: A sequential read wait occurs within an Oracle database when a
single block is read. A single read is most commonly an index probe by ROWID into an
individual table, or the access of an index block. Sequential reads are single-block reads, as
opposed to multiblock (scattered) reads.
Solutions to Physical Read Waits:
Tune the SQL
Re-schedule contentious SQL
Re-analyze schema using dbms_stats
Distribute disk I/O across more spindles
Use the KEEP pool
Increase the data buffer cache size
In top 5 Sql section ,how to analyze the sql statement and how to find this sql statement
is giving the problem.
In order to analyze a sql statement, that has regressed, simply generate an awr sql report for a
time when the statement was performing well, and a second report when statement was
performing poorly, the execution plan and statistics can then be compared between the two
reports to determine why the sql statement has regressed. Not all regressions are due to plan
changes, it may be that the statement is executed more often, retrieves more data, or incurs
more wait time. Comparing both the statistics and the execution plans can help determine the
cause of regression.
Steps involved in RMAN cloning.
RMAN> connect target /
RMAN> backup database plus archivelog;
Enter the listener information in listener.ora file and tnsnames.ora.
Create the init.ora file for the new duplicate database. Specifiy the db_file_name_convert and
log_file_name_convert parameters.
Create the password file for new duplicate database.
Source the environment for duplicate database
Connect as sysdba and start the database in nomount state using new created pfile and exit
from the session.
Source the environment again for primary database.
RMAN>connect target / auxiliary sys/passwd@dupdb
RMAN> duplicate target database to dupdb;
Relation b/w commit and ckeckpoint.
There is no relationship between commit and checkpoint.
Checkpoint is a feature, it does not trigger LGWR.
DBWR may trigger LGWR to write in log file if some redo verctors that protect blocks of the
previous list are not already written.
SCN no relation with commit. SCN may increase but this has no relation with commit (in
user sense).
SCN is more System Change Number, it ticks many changes, not only commits. it is not
always that DBWR triggers LGWR.It does so only when it finds that the dirty blocks in the
redo are not yet written. Locks are released at commit (or rollback) not at checkpoint.
Commits (and rollbacks) are user actions, checkpoint is background action.
LGWR and DBWR works together, they are not independent, it is the opposite, they are
tightly linked.
Relation b/w logswitch and ckeckpoint.
A log switch is when we fill up one online redo log and goto the next. A log switch will
always initiate a CHECKPOINT (it always starts a checkpoint going).
A log switch implies a checkpoint will start. A checkpoint implies nothing about a log switch
(checkpoints may be started based on time, number of changed OS blocks, log switches or
operator intervention. A checkpoint does not cause a log switch, a log switch causes a
checkpoint). So, that is the relationship between the two. The reason a log switch fires a
checkpoint is because the system wants to get the blocks in the buffer cache that are
"protected" by the online redo log we just filled flushed to disk (so we can reuse that redo log
file).
If we did not flush these blocks out -- we would need to keep that filled online redo log file
until we did flush them out (it is needed to recover those dirty blocks in the event of a
failure). So, the log switch fires a checkpoint in anticipation of needing to reuse that redo log
file.
What if top command doesn’t work?
Top command displays information about top consuming resource process. But if top does
not work, we can use ps command to get that information. E.g.,
$ps –e –o pcpu,pid,user,tty,args | sort –n –k 1 –r | head
How will you trace the user session? What is event 10046?
SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';
SQL> ALTER SESSION SET EVENTS '10046 trace name context off';
SQL> EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123, serial#=>1234,
sql_trace=>TRUE);
SQL> EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123, serial#=>1234,
sql_trace=>FALSE);
SQL> EXEC DBMS_SYSTEM.set_ev(si=>123, se=>1234, ev=>10046, le=>8, nm=>' ');
SQL> EXEC DBMS_SYSTEM.set_ev(si=>123, se=>1234, ev=>10046, le=>0, nm=>' ');
Event 10046 is an undocumented, but well known feature within the Oracle database that
generates detailed information on how a statement is parsed, what the values of bind
variables are, and what wait events occurred during a particular session. Activating Event
10046 gives us a more detailed trace that that provided by alter session set sql_trace=true
I emptied the recycle bin and I need the table back or I will lose my job. How do I
recover a dropped table? Can I restore the table and recover the dropped table without
affecting other objects of the database?
DBAs get fired for dropping production tables, so act quickly! DDL like drop table cannot be
rolled-back, and you have lost your recycle bin, but you still have several options for
recovering a dropped table:
• Data pump exports - If you care about the data in this table, you would have exported it
nightly.
• RMAN - You can recover a single dropped table by restoring the entire database into your
test environment using RMAN, and then roll forward. You can then extract the table and copy
it from test to production using CTAS over a database link and then re-add the indexes and
constraints.
• LogMiner - Oracle LogMiner can used to recover a dropped table. See here, the steps to
recover a dropped table using LogMiner.
• Flashback - It's easy to recover a dropped table with these steps in flashback.
Here is an example of recovering a dropped table with flashback. To recover the table we first
check to see that it resides in the recyclebin, and then we issue the “flashback table to before
drop” command to recover the table:
SQL> drop table mytab;
Table dropped.
SQL> select original_name from dba_recyclebin;
ORIGINAL_NAME
-------------------------------MYTAB
SQL> flashback table MYTAB to before drop;
Flashback complete.
SQL> select * from mytab;
ID
---------2
2
What are the advantages of DATAPUMP over export import?Why Datapump is very
fast compared to export/import?
Ans:Top 10 difference between exp/imp(export/import) and expdp/impdp(Datapump export
and import) are:
1)Data Pump Export and Import operate on a group of files called a dump file set rather than
on a single sequential dump file.
2)Data Pump Export and Import access files on the server rather than on the client. This
results in improved performance. It also means that directory objects are required when you
specify file locations.
3)The Data Pump Export and Import modes operate symmetrically, whereas original export
and import did not always exhibit this behavior.
For example, suppose you perform an export with FULL=Y, followed by an import using
SCHEMAS=HR. This will produce the same results as if you performed an export with
SCHEMAS=HR, followed by an import with FULL=Y.
4)Data Pump Export and Import use parallel execution rather than a single stream of
execution, for improved performance. This means that the order of data within dump file sets
and the information in the log files is more variable.
5)Data Pump Export and Import represent metadata in the dump file set as XML documents
rather than as DDL commands. This provides improved flexibility for transforming the
metadata at import time.
6)Data Pump Export and Import are self-tuning utilities. Tuning parameters that were used in
original Export and Import, such as BUFFER and RECORDLENGTH, are neither required
nor supported by Data Pump Export and Import.
7)At import time there is no option to perform interim commits during the restoration of a
partition. This was provided by the COMMIT parameter in original Import.
8)There is no option to merge extents when you re-create tables. In original Import, this was
provided by the COMPRESS parameter. Instead, extents are reallocated according to storage
parameters for the target table.
9)Sequential media, such as tapes and pipes, are not supported.
10)The Data Pump method for moving data between different database versions is different
than the method used by original Export/Import. With original Export, you had to run an
older version of Export (exp) to produce a dump file that was compatible with an older
database version. With Data Pump, you can use the
current Export (expdp) version and simply use the VERSION parameter to specify the target
database version
Reorg? How and why and for which objects generally?
MOVE is easy. That moves an object from one tablespace (and set of data files) to another
tablespace (and another set of data files). That may or may not decrease the size of the table.
It would be used when you need to temporarily or permanently relocate an object, which is
what would need to happen if you need to shrink a data file.
COALESCE is only related to index-organized tables or hash partitioned tables. It does the
same thing for an index-organized table that coalescing an index does; it merges the contents
of an index block.
SHRINK SPACE tells Oracle to physically move rows around in a table (this is why row
movement must be enabled for the table) in order to release free space in the table back to the
tablespace. You'd do this if, for example, you have a log table that had been storing 9 months
of data and you've decided to permanently decrease that to 3 months of data, deleted the old 6
months worth of data, leaving the table permanently smaller (assuming no increase in the log
generation rate) and 67% empty.
ALTER TABLE SHRINK is a two phase operation:
1st. step: reads table from the back and deletes first row found , then read table from the front
and uses first free slot to reinsert it.
This is reiterated until the position for reading from back and reading from fron tis the same.
Then the segtment is compact, right. This is DML and indexes are maintained.
2. step adjusts high water mark. This need Exclusive Table lock.
On the other hand the ALETR TABLE MOVE is similar to a CREATE TABLE AS SELECT
which does not use the SGA but the PGA. Here arrays are filled in temporary segments and
then dumped into newly formatted blocks.
> Indexes are not maintained, the rows change their physical address and the indexes must be
rebuilt afterwards.
> Alter table move is not an online operation. No DMLs allowed in between.
> alter table move is to be used to reduce the no of empty blocks
In general ways of reorg are
table As Select (CTAS) - This copies the table rows into a clean area, lowering the highwater-mark, packing the rows densely (as dictated by PCTFREE) and releasing free space.
Online reorg - Using the dbms_redefinition package you can use parallel CTAS to reorganize
tables, while the tables continue to accept updates.
Data pump (expdp, impdp) - Rarely used in high-speed production apps, except for backups.
Alter table move - The alter table xxx move command moves rows down into un-used space
and adjusts the HWM but does not adjust the segments extents, and the table size remains the
same. The alter table move syntax also preserves the index and constraint definitions.
Alter table shrink space - Using the "alter table xxx shrink space compact" command will repack the rows, move down the HWM, and releases unused extents. With standard Oracle
tables, you can reclaim space with the "alter table shrink space" command:
If mount point gets full then how will we know. every time we don’t go to the server and
we won’t issue df -h. Then how can we know that it’s getting full?
#!/bin/bash
rm ~/latest_problem_with_disk_space
for i in `df -kh|grep -v cciss|grep -v intfdata|grep -v Available|grep -v none|grep -v stage|awk
'{print $4}'|sed -e 's/%//g' -e '1,2d'` ;
do
if [ $i -lt 90 ]; then
echo "good" ;
else
echo "we have a problem on `df -h | grep $i `" >> ~/latest_problem_with_disk_space ;
fi ;
done
if [ -e ~/latest_problem_with_disk_space ] ; then
mailx -s "FileSystem Full alert" [email protected] <
~/latest_problem_with_disk_space ;
fi
Why do I have invalid objects? What causes them? How you identify them ?
An object that references another object is called a dependent object, and an object being
referenced is a referenced object. These references are established at compile time, and if the
compiler cannot resolve them, the dependent object being compiled is marked invalid. When
a referenced object is changed in a way that might affect a dependent object, the dependent
object is marked invalid. It is important to be aware of changes that can invalidate schema
objects, because invalidation affects applications running on the database. Changes to tables,
views, or PL/SQL units typically occur when an application is patched or upgraded using a
patch script or ad-hoc DDL statements. Dependent objects might be left invalid after a patch
has been applied to change a set of referenced objects.
To identify the invalid objects:
SELECT object_name, object_type FROM dba_objects
WHERE status = 'INVALID'
How do you recommend to your client for applying cpu patches. How to they impact the
business.
Oracle believes that the timely application of Critical Patch Updates is necessary for
organizations to maintain a proper security in-depth posture. It is not mandatory to install
Critical Patch Updates, but Oracle strongly recommends that they are applied to fix security
vulnerabilities and minimize the risk of a successful attack. If this is not possible, customers
should determine the risk to machines based on factors such as:
the severity of unfixed vulnerabilities;
the sensitivity of data stored; and
the accessibility of the machine to attackers.
what is the use of psu patches ( patch set updates, i understand that both cpu and psu
are db patches and both are released quarterly), can you please throw some more light
on it .
Oracle PSU is a new patching strategy whereby the DBA can choose only "recommended"
and "proactive" patches, instead of all of the patches in a quarterly Critical Patch Update
(CPU).
An Oracle PSU contains recommended bug fixes and "proactive" cumulative patches, a nice
change that makes it simple for the DBA to chose to apply "priority" patches.
Starting each quarter, Oracle Critical Patch Updates (CPU) will now contain both the PSU
and CPU, so the DBA may choose to apply a whole CPU or only the selected patches in the
PSU patch bundle.
The naming conventions for CPU and PSU is the last-order digit, so it's easy to see of you are
patching with CPU or PSU:
CPU = 10.2.0.4.1
PSU = 10.2.0.4.2
Also, the PSU supports Oracle's zero-downtime patching, a method for RAC databases
whereby each node is patched independently with no downtime.
what is version conflict while patching and how to resolve it
if a patch has been applied, all the bugs fixed by that patch could reappear after another patch
is applied. This is called a conflict situation. OPatch detects such situations and raises an error
when a it detects a conflict.
OPatch can detect the following types of conflicts:
Bug Superset — If all the bugs fixed by a patch in the system are also fixed by the patch to be
applied, this patch (the patch to be applied) is considered to be a superset of the patch already
applied. If a bug superset condition is detected, it is not considered an error situation. All the
subset patches are removed from the system and the new patch is applied.
For example, consider a scenario where four patches A,B,C, and D are applied in a system,
each of which fixes 2 bugs as shown in Figure 6–1. If you apply a patch E that fixes bugs
5,6,7,8,9, and 10, patch E will be the superset of patch C and D.
If you want OPatch to error out if the current patch bugs-to-fix is a superset or the same as an
installed patch bugs-fixed in the Oracle home directory, you can use the -no_bug_superset
flag:
$ OPatch/opatch apply -no_bug_superset
Subset — Patches to be applied can be subsets of other patches installed in the Oracle home.
Consider the following scenario:
Patch A, installed in the Oracle home, fixed bugs 1, 2, and 3.
Patch B, installed in the Oracle home, fixed bugs 10, 11, and 12.
Patch D, to be installed, fixes bugs 1 and 2.
Patch D is a subset of Patch A.
Using the skip_subset Option
When you want to skip patches formerly applied in the Oracle home that are now subsets of
other patches you want to apply now, you can use the skip_subset option of napply. For
example, if you used napply yesterday for patch A that fixed bugs 1 and 2, then you use
napply today with the skip_subset option for patch B that fixes bug 1 and patch C that fixes
bugs 1, 2, and 3, then subset patch A is skipped, and patch C then becomes a superset of patch
A.
The following example applies all patches under the directory. OPatch
skips duplicate patches and subset patches (patches under that are subsets
of patches installed in the Oracle home).
opatch napply -skip_subset -skip_duplicate
The following example applies patches 1, 2, and 3 that are under the
directory. OPatch skips duplicate patches and subset patches (patches under
that are subsets of patches installed in the Oracle home).
opatch napply -id 1,2,3 -skip_subset -skip_duplicate
See the description for the skip_subset option in Table 4–3 for more information.
Duplicate — A duplicate patch fixes the same set of bugs that another patch fixed. For
example, if you applied Patch A that fixed bugs 1, 2 and 3, and now apply Patch B that also
fixes bugs 1, 2 and 3, then Patch B is a duplicate of Patch A. A patch is always a duplicate of
itself.
Using the skip_duplicate Option
If you specify this option, OPatch removes duplicate patches from the list of patches to be
applied. For example, if you used napply yesterday for Patch A discussed above, then use
napply today with the -skip_duplicate option for Patch A and other patches, duplicate Patch A
is skipped.
Bug Conflict — If a set of bugs to be fixed by the current interim patch includes some but
not all bugs already fixed by one or more previously installed interim patches, this is
considered a bug conflict. You must remove the bug conflict before proceeding with the patch
by using the apply command with the -force flag, which rolls back the conflicting patches
before applying the new one.
For example, consider a scenario where four patches A,B,C, and D are applied in a system,
each of which fixes 2 bugs as shown in Figure 6–2. If you apply a patch E that fixes bugs
1,3,5,7,9, and 10, this patch fixes bugs 1,3,5,7,9, and 10, opens bugs 2,4,6, and, 8. This is a
conflict situation.
File Conflict — If a set of files to be patched by the current interim patch include files
already patched by one or more previously installed interim patches and it is not a bug
superset, this is considered a file conflict. You must remove the file conflict before you
proceed with patching by using the apply command with the -force flag, which rolls back the
conflicting patches before applying the new one.
Combination Conflict — If a set of patches has a combination of a bug superset and bug
or file conflict, this is considered a combination conflict and is an error situation. In this case,
OPatch removes all conflicting patches as well as the subset patches and then re-applies the
new patch.
For example, consider a scenario where four patches A,B,C, and D are applied in a system,
each of which fixes 2 bugs as shown in Figure 6–3. Patch C is the subset of patch D. Patch A
and patch B are conflicting patches of patch D. If you apply this patch D that fixes bugs
1,3,5,6,7, and 8 with the -force flag, OPatch rolls back patches A, B, and C and applies patch
D.
Patch Conflict Detection and Resolution
OPatch detects and reports any conflicts encountered when applying an interim patch with a
previously applied patch. The patch application fails in case of conflicts. You can use the
-force option of OPatch to override this failure. If you specify -force, the installer firsts rolls
back any conflicting patches and then proceeds with the installation of the desired interim
patch.
You may experience a bug conflict and might want to remove the conflicting patch. This
process is known as patch rollback. During patch installation, OPatch saves copies of all the
files that were replaced by the new patch before the new versions of these files are loaded,
and stores them in $ORACLE_HOME/.patch_storage. These saved files are called rollback
files and are key to making patch rollback possible. When you roll back a patch, these
rollback files are restored to the system. If you have gained a complete understanding of the
patch rollback process, you should only override the default behavior by using the -force flag.
To roll back a patch, execute the following command:
$ OPatch/opatch rollback -id
How can you tell how much space is left on a given file system and how much space each
of the file system’s subdirectories take-up?
$df –h
$du –sh
You have 4 instances running on the same UNIX box. How can you determine which
shared memory and semaphores are associated with which instance?
First, run "ipcs -b" on the database server.
Now log into each individual instance you have up and running by setting your
"ORACLE_HOME" and "ORACLE_SID". Log into each instance using "svrmgrl" and use
the following command:
svrmgr> connect internal
Connected.
SVRMGR> oradebug ipc
output shows that the particular database instance I was logged into, owned shared memory
segment 353 while its semaphore identifiers where 458758 and 458759. You can verify that
these are correct by looking them
After logging into each database instance and running "oradebug ipc", you will know which
segments are valid on the running databases. Using this process of elimination you can
identify the idle segments from a crashed instance. You can then kill them using "ipcrm -m"
and "ipcrm -s" respectfully.
The command syntax to remove the shared memory segments or semaphores is as follows:
% ipcrm -m
% ipcrm -s
What spfile/init.ora file parameter exists to force the CBO to make the execution path of
a given statement use an index, even if the index scan may appear to be calculated as
more costly?
Optimizer_mode=’first_rows’
optimizer_index_cost_adj=force
Assuming today is Monday, how would you use the DBMS_JOB package to schedule
the execution of a given procedure owned by SCOTT to start Wednesday at 9AM and to
run subsequently every other day at 2AM.
EXEC DBMS_JOB.SUBMIT(,’SCOTT.’,’trunc(sysdate)
+2+9/24’,’trunc(sysdate)+2+2/24’);
How would you edit your CRONTAB to schedule the running of /test/test.sh to run
every other day at 2PM?
00 14 */2 * * /test/test.sh
How would you extract DDL of a table without using a GUI tool?
select dbms_metadata.get_ddl('TABLE','tab_name') from dual;
desc
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.
First of all secure all the archives and all the backups you have on the tape or other system.
Then run fschk to check the filesystem. If the corruption is detected at the filesystem level
and is not recoverable by fschk format the file system and restore the database through
RMAN.
How do you increase the OS limitation for open files (LINUX and/or Solaris)?
you can increase the maximum number of open files by setting a new value in kernel variable
/proc/sys/fs/file-max as follows (login as the root):
# sysctl -w fs.file-max=100000
Above command forces the limit to 100000 files. You need to edit /etc/sysctl.conf file and put
following line so that after reboot the setting will remain as it is:
# vi /etc/sysctl.conf
Append a config directive as follows:
fs.file-max = 100000
Save and close the file. Users need to log out and log back in again to changes take effect or
just type the following command:
# sysctl –p
Verify your settings with command:
# cat /proc/sys/fs/file-max
OR
# sysctl fs.file-max
Provide an example of a shell script which logs into SQLPLUS as SYS, determines the
current date, changes the date format to include minutes & seconds, issues a drop table
command, displays the date again, and finally exits.
Create a file.sh using vi then type the following line
sqlplus -s sys/*******@service_name
select sysdate from dual;
ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD MI:SS'
drop table xx;
select sysdate from dual;
exit;
What would you use to improve performance on an insert statement that places millions
of rows into that table?
1. DML Triggers to be DISABLED and then ENABLED once the insert completed.
2. DISABLE the Clustered Index and then ENABLED once the insert completed.
3. After completion of INSERT, Defragmentation should be there.
4. After completion of INSERT, Statistics should be updated.
5. (Optional) If it is non-peek time & This is the only activity is going on then, Database
recovery mode should be SIMPLE / BULK-LOGGED. ( It should not be there along with
other activities....)
What would you do with an “in-doubt” distributed transaction?
A transaction is in-doubt when there is a failure during any aspect of the two-phase commit.
Distributed transactions become in-doubt in the following ways:
A server machine running Oracle software crashes
A network connection between two or more Oracle databases involved in distributed
processing is disconnected
An unhandled software error occurs
You can manually force the commit or rollback of a local, in-doubt distributed transaction.
Because this operation can generate consistency problems, perform it only when specific
conditions exist.
No action is required by the administrator of any node that has one or more in-doubt
distributed transactions due to a network or system failure. The automatic recovery features
of Oracle transparently complete any in-doubt transaction so that the same outcome occurs on
all nodes of a session tree (that is, all commit or all roll back) after the network or system
failure is resolved.
In extended outages, however, you can force the commit or rollback of a transaction to
release any locked data. Applications must account for such possibilities.
If you’re unsure in which script a sys or system-owned object is created, but you know
it’s in a script from a specific directory, what UNIX command from that directory
structure can you run to find your answer?
$grep –R “string”
What’s the benefit of “dbms_stats” over “analyze”?
Starting with the introduction of the dbms_stats package, Oracle provides a simple way for
the Oracle professional to collect statistics for the CBO. The old-fashioned analyze table and
dbms_utility methods for generating CBO statistics are obsolete and somewhat dangerous to
SQL performance because they don't always capture high-quality information about tables
and indexes. The CBO uses object statistics to choose the best execution plan for all SQL
statements. The dbms_stats utility does a far better job in estimating statistics, especially for
large partitioned tables, and the better stats result in faster SQL execution plans.
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?
In no archivelog mode, it is not possible to restore or recover a particular backup without
effecting other tablespace. One thing that can be done is to make a clone from the latest
backup and export the tablespace to be restored and import that export to the original
database.
How do you recover a datafile that has not been physically been backed up since its
creation and has been deleted. Provide syntax example.
Assume that the file ‘/u01/ORADATA/data/data01.dbf’ of tablespace DATA is damaged and
you don't have a backup of the data file.
• SQL
• SQL> connect internal
• connected
• SQL> shutdown abort
• Oracle Instance Aborted
• SQL> startup mount
• Oracle Instance Started
• SQL> alter database create datafile ‘/u01/ORADATA/data/data01.dbf’;
• Statement Processed
• SQL> recover datafile ‘/u01/ORADATA/data/data01.dbf’;
• Statement Processed
• SQL> select * from v$datafile;
• You will see a list of datafiles with their status. If the status of the concerned file has not
been reset to ONLINE, issue the following command.
• SQL> alter database datafile ‘/u01/ORADATA/data/data01.dbf’ online;
• Statement Processed
• SQL> select * from dba_tablespaces;
• If the status of the tablespace CASE is not ONLINE, then issue the following command.
• SQL> alter tablespace CASE online;
• Statement Processed
• SQL> alter database open;
Statement Processed
what will you do if you get a complain your database is slow?
If we get a complaint regarding database slowness, first of all we need to ensure whether the
issue is user specific, module specific or on entire application level. In order to understand
better problem, you should ask questions to yourself or person encountering performance
issue like
Is whole application slow or only component of the application is slow? ( like only
concurrent manager running slow)
Is this a particular component like report server slow then is a particular report or all reports
are slow?
Is performance issue during all time or at specific time let’s say from 11AM to 4PM?
Was performance issue popped up recently or performance degradation is since long?
What changed recently?
Depending on answer you can start looking into application like if issue is user specific,
module specific or on entire application level.
If it is user specific problem, trace the user session and find the root cause of the problems
and area where database is performing poorly, and either tune (if possible) or kill the session.
At broad level, we need to focus on these areas and to find the root cause of the problems.
Is gather stats schedule (atleast weekly?)?
Is Purge Obsolete Workflow request & concurrent request purging scheduled?
Is there any runaway process on database tier or application tier? (Runaway process is
process taking unexceptionally long time with high CPU or memory usage)
Any discoverer process taking long time or high CPU? (usually on Middle tier like dis4ws..)
Any custom report taking long time?
what is the diff between lock and deadlock?
A lock enables multiple users to access the database concurrently. When a user modify the
database record, a lock is applied on that record and it prevents other users to write to
database. Depending on the various modification tasks, a lock can be classified into three
categories: exclusive lock, shared lock and update lock. Whereas a deadlock is a situation
where two or more transactions are put into wait state simultaneously, each transaction
acquires a lock that prevents other transaction to write until it get released.and in this
situation, each waits for the other transaction to get released. Whenever we get a deadlock
situation, in the alert log file, we can see ORA-00060 error. We need to trace the session
experiencing the deadlock and take the appropriate action to kill one session.
What is a recycle bin – how do I clear it
A : Recycle bin is a feature which is introduced in 10g. When we enable recycle bin in our
database, any table that is dropped is not actually get deleted. Oracle just renames the table
and all its associated objects with a system generated name that begins with BIN$. The
dropped table takes place in recycle bin. If we need to undrop the table, we supply the
command FLASHBACK TABLE .... TO BEFORE DROP. This command simply renames
the table name from BIN$... to its original name. To clear a recyclebin, we need to purge it.
How do I do a clone using export / import
A:
You must have full backup dump
2. List the content from export dump
Imp file=’/dump/xyz.dmp’ log=’/dump/imp.log’ full=y show=y
It will list all sql statement in log (imp.log)
c. Open the imp.log file, search and find the tablespace and users sql statements
i. Create tablespace .......
ii. Create users ....
iii. Create index..
iv. And what every you want
d. Copy and past it in a file.sql. now u will the script for creating a database.
e. Copy and save ‘create user.. ‘ is seperate file and ‘create tablespace..’ in sepereate sql file.
3. Create required folders (bdump,udump, also oradata)
4. Edit the pfile, use it from backup, if not. copy it from old db alert log or statspack report if
u have.
5. Create oracle service
Windows : oradim –new –sid xxx
Solaris : export oracle_sid= xxx
6. Startup the database
Sqlplus “/as sysdba”
Startup nomount pfile=’/pfile.ora’
7. Run the script
$> @ file.sql
It will create all tablespaces
8. Open the database
Alter database mount;
Alter database open;
9. Create the users
Run the script to create users
@users.sql’
10. Import the datas
Imp file=’/dump/xyz.dmp’ log=’/dump/imp.log’ full=y
After import data, update the database metrics using analyze command..
What is SGA_MAX_SIZE and SGA_TARGET?
SGA_TARGET specifies the total size of all SGA components. If the SGA_TARGET is set,
then the following memory pools are automatically sized :
Buffer cache (DB_CACHE_SIZE)
Shared pool (SHARED_POOL_SIZE)
Large pool (LARGE_POOL_SIZE)
Java pool ( JAVA_POOL_SIZE)
SGA_MAX_SIZE specifies the hard limit upto which the SGA_TARGET can dynamically
grow. While executing DBCA, Oracle suggests that the estimated SGA_MAX_SIZE is to set
aside 40% of memory. However, it should be set according to your requirement that depends
on multiple factors such as no of concurrent users, volume of transactions and growth rate of
database. Under normal operation, you can set the SGA_MAX_SIZE equals to the
SGA_TARGET. Sometimes, we need to perform some extra-heavy batch processing jobs that
leads to more SGA size. At this circumstance, you must have capability to adjust for peak
loads. That is why, you set hard limit for your SGA_MAX_SIZE.
SGA_MAX_SIZE cannot be changed dynamically without bouncing the database whereas
SGA_TARGET can be changed dynamically without bouncing the database.
While applying a rdbms patch using opatch you are getting the error, unable to read
inventory/inventory is corrupted/ORACLE_HOME is not not registered, what you will
do, and how you will apply the patch?
Ans: We will check the inventory directory permission, try to apply the patch after giving 777
permissions to that inventory directory. If still it won’t work we will apply patch with the
following command:
Opatch apply no_inventory
Have you applied rdbms patches and for what?
Ans : We got ORA-7445 error in alert log, for which oracle recommended to apply a rdbms
patch.
There are four Oracle Databases running on a mahine. How would you check the
location of their Oracle Homes ?
- Check entry in oratab file
When Database is up, listener is up. listener.ora and tnsnames.ora both are configured
properly, still client is not being able to make a connection to the database. What may
be the possible issues ?
– Firewall blocking db port between client and server
– Authentication restricted in sqlnet.ora (Client allowed from only selected machines)
1. Give one method for transferring a table from one schema to another:
There are several possible methods, export-import, CREATE TABLE... AS SELECT, or
COPY.
2. What is the purpose of the IMPORT option IGNORE? What is it?s default setting
The IMPORT IGNORE option tells import to ignore "already exists" errors. If it is not
specified the tables that already exist will be skipped. If it is specified, the error is ignored
and the tables data will be inserted. The default value is N.
3. You have a rollback segment in a version 7.2 database that has expanded beyond
optimal, how can it be restored to optimal
Use the ALTER TABLESPACE ..... SHRINK command.
4. If the DEFAULT and TEMPORARY tablespace clauses are left out of a CREATE
USER command what happens? Is this bad or good? Why
The user is assigned the SYSTEM tablespace as a default and temporary tablespace. This is
bad because it causes user objects and temporary segments to be placed into the SYSTEM
tablespace resulting in fragmentation and improper table placement (only data dictionary
objects and the system rollback segment should be in SYSTEM).
5. What are some of the Oracle provided packages that DBAs should be aware of
Oracle provides a number of packages in the form of the DBMS_ packages owned by the
SYS user. The packages used by DBAs may include: DBMS_SHARED_POOL,
DBMS_UTILITY, DBMS_SQL, DBMS_DDL, DBMS_SESSION, DBMS_OUTPUT and
DBMS_SNAPSHOT. They may also try to answer with the UTL*.SQL or CAT*.SQL series
of SQL procedures. These can be viewed as extra credit but aren?t part of the answer.
6. What happens if the constraint name is left out of a constraint clause
The Oracle system will use the default name of SYS_Cxxxx where xxxx is a system
generated number. This is bad since it makes tracking which table the constraint belongs to or
what the constraint does harder.
7. What happens if a tablespace clause is left off of a primary key constraint clause
This results in the index that is automatically generated being placed in then users default
tablespace. Since this will usually be the same tablespace as the table is being created in, this
can cause serious performance problems.
8. What is the proper method for disabling and re-enabling a primary key constraint
You use the ALTER TABLE command for both. However, for the enable clause you must
specify the USING INDEX and TABLESPACE clause for primary keys.
9. What happens if a primary key constraint is disabled and then enabled without fully
specifying the index clause
The index is created in the user?s default tablespace and all sizing information is lost. Oracle
doesn?t store this information as a part of the constraint definition, but only as part of the
index definition, when the constraint was disabled the index was dropped and the information
is gone.
10. (On UNIX) When should more than one DB writer process be used? How many
should be used
If the UNIX system being used is capable of asynchronous IO then only one is required, if the
system is not capable of asynchronous IO then up to twice the number of disks used by
Oracle number of DB writers should be specified by use of the db_writers initialization
parameter.
11. You are using hot backup without being in archivelog mode, can you recover in the
event of a failure? Why or why not
You can?t use hot backup without being in archivelog mode. So no, you couldn?t recover.
12. What causes the "snapshot too old" error? How can this be prevented or mitigated
This is caused by large or long running transactions that have either wrapped onto their own
rollback space or have had another transaction write on part of their rollback space. This can
be prevented or mitigated by breaking the transaction into a set of smaller transactions or
increasing the size of the rollback segments and their extents.
13. How can you tell if a database object is invalid By checking the status column of the
DBA_, ALL_ or USER_OBJECTS views, depending upon whether you own or only
have permission on the view or are using a DBA account.
13. A user is getting an ORA-00942 error yet you know you have granted them
permission on the table, what else should you check
You need to check that the user has specified the full name of the object (select empid from
scott.emp; instead of select empid from emp;) or has a synonym that balls to the object
(create synonym emp for scott.emp;)
14. A developer is trying to create a view and the database won?t let him. He has the
"DEVELOPER" role which has the "CREATE VIEW" system privilege and SELECT
grants on the tables he is using, what is the problem
You need to verify the developer has direct grants on all tables used in the view. You can?t
create a stored object with grants given through views.
15. If you have an example table, what is the best way to get sizing data for the
production table implementation
The best way is to analyze the table and then use the data provided in the DBA_TABLES
view to get the average row length and other pertinent data for the calculation. The quick and
dirty way is to look at the number of blocks the table is actually using and ratio the number of
rows in the table to its number of blocks against the number of expected rows.
16. How can you find out how many users are currently logged into the database? How
can you find their operating system id
There are several ways. One is to look at the v$session or v$process views. Another way is to
check the current_logins parameter in the v$sysstat view. Another if you are on UNIX is to
do a "ps -ef|grep oracle|wc -l? command, but this only works against a single instance
installation.
17. A user selects from a sequence and gets back two values, his select is: SELECT
pk_seq.nextval FROM dual;What is the problem Somehow two values have been
inserted into the dual table. This table is a single row, single column table that should
only have one value in it.
18. How can you determine if an index needs to be dropped and rebuilt
Run the ANALYZE INDEX command on the index to validate its structure and then calculate
the ratio of LF_BLK_LEN/LF_BLK_LEN+BR_BLK_LEN and if it isn?t near 1.0 (i.e.
greater than 0.7 or so) then the index should be rebuilt. Or if the ratio BR_BLK_LEN/
LF_BLK_LEN+BR_BLK_LEN is nearing 0.3.
How can you tell if a tablespace has excessive fragmentation
If a select against the dba_free_space table shows that the count of a tablespaces extents is
greater than the count of its data files, then it is fragmented.
You see the following on a status report: redo log space requests 23 redo log space wait
time 0 Is this something to worry about? What if redo log space wait time is high? How
can you fix this Since the wait time is zero, no. If the wait time was high it might
indicate a need for more or larger redo logs.
What can cause a high value for recursive calls? How can this be fixed
A high value for recursive calls is cause by improper cursor usage, excessive dynamic space
management actions, and or excessive statement re-parses. You need to determine the cause
and correct it By either relinking applications to hold cursors, use proper space management
techniques (proper storage and sizing) or ensure repeat queries are placed in packages for
proper reuse.
You attempt to add a datafile and get: ORA-01118: cannot add anymore datafiles: limit
of 40 exceeded What is the problem and how can you fix it
When the database was created the db_files parameter in the initialization file was set to 40.
You can shutdown and reset this to a higher value, up to the value of MAX_DATAFILES as
specified at database creation. If the MAX_DATAFILES is set to low, you will have to
rebuild the control file to increase it before proceeding.
Users aren’t being allowed on the system. The following message is received: ORA00257 archiver is stuck. Connect internal only, until freed What is the problem
The archive destination is probably full, backup the archive logs and remove them and the
archiver will re-start.
Your users get the following error: ORA-00055 maximum number of DML locks
exceeded What is the problem and how do you fix it
The number of DML Locks is set by the initialization parameter DML_LOCKS. If this value
is set to low (which it is by default) you will get this error. Increase the value of
DML_LOCKS. If you are sure that this is just a temporary problem, you can have them wait
and then try again later and the error should clear.
How do you set up your tablespace on installation
The answer here should show an understanding of separation of redo and rollback, data and
indexes and isolation os SYSTEM tables from other tables. An example would be to specify
that at least 7 disks should be used for an Oracle installation so that you can place SYSTEM
tablespace on one, redo logs on two (mirrored redo logs) the TEMPORARY tablespace on
another, ROLLBACK tablespace on another and still have two for DATA and INDEXES.
They should indicate how they will handle archive logs and exports as well. As long as they
have a logical plan for combining or further separation more or less disks can be specified.
You have installed Oracle and you are now setting up the actual instance. You have been
waiting an hour for the initialization script to finish, what should you check first to
determine if there is a problem
Check to make sure that the archiver isn?t stuck. If archive logging is turned on during install
a large number of logs will be created. This can fill up your archive log destination causing
Oracle to stop to wait for more space.
How you manage the servers?
A: We have our custom scripts which is sheduled to run at a specific time which monitors
applications/databases servers availability by ping and traceroute results, uptime, file systems
details, free disk space in each mount point, memory utilization, CPU utilization, top CPU
consuming processes, input/output stats, general database information such as tablespaces
and datafiles, control files, redo log files, SGA configuration, user details, scheduled backup
status, invalid objects, critical errors in alert log, database hit ratio, top 5 wait events, top 5
resource consuming queries. And it will mail us if some processes is not running, any value
reaches to threshold value. Based on this we will react.
1: Assume that your production database is in standby you want to upgrade your production
database total down time require for that is 10 hours but client wants to finish the task with in
5 hours what is the solution?
2: What are different types of incremental backup's and what are they?
3: Using RMAN how to take the backup of archives and at the same time we have to delete
backup-ed archive's(what ever we have taken backup that should be deleted from the archive
location to make archive destination space free.
4: Using 10g EXPDP i have exported one schema into a dump file, Is that dump file
recognized by oracle 9i binaries if i Import that dump file to 9i?
5: What are the views to monitor Expdp & Impdp?
6: Difference between partial checkpoint and full checkpoint?
7: What is a fail group? What's its purpose(asm concept)?
8: Can we keep standby database in higher version(11i) and primary database in 10g? if i do
so, what about the archives generated at primary db is this archives are going to applied
(MRP) to standby?
9: How to configure logical standby database ? In real time which one is prefer
logical/physical standby?
10: Difference between convention path and direct path in data pump?
11: How to Find Out DB Buffer Cache Size by Select Statement ?
12: If listener details are not at default location($oracle_home/network/admin) then how to
find out the details?
13: We can't create role and user with the same name. Why?
14: What is high water mark...and what is its use?
15: What are the steps to take to run the SQL query quickly?
16: What is the possible value of 'AUDIT_TRAIL' parameter and Which data dictionary to
show it?
17: What is Read Consistency ?.. How can find out consistency?
18: What is the command to assign multiple roles to multiple users?
1: How can I come to know that whether my database is configured or running using PFILE
or SPFILE?
2: What happens if you kill any one of the background process?
3: What is the difference between Root user and Oracle user?
4: What do you mean by RAC? What is the importance of it?
5: Where will the statistics collected in AWR will be stored? In which path?
6: How can you detect whether a table is locked or not?
7: The snapshot collected by executing snapshot.snap will be stored in statspack table, Where
will be the statspack table? In which path?
8: Do the db name and Instance name should be equal? Predict either Yes or No? Give reason
for your prediction?
9: How to get/download the recent patches released by oracle?
10: Is there any way to take incremental backup's in user managed backup's(with out using
RMAN)?
11: Assume that a database consists of a user tablespace and system tablespace then How to
store one tablespace into one diskgroup and another tablespace into other diskgroup if i am
using ASM concept?
12: what are the different ways to recreate the control file when the db is not opened (bcz
control file is lost)?
13: What are the responsibilities of L1, L2, L3 dba's?
14: What is the use of Index Rebuilding?
15: What to do if the file system becomes full?
16: What to do if the Buffer Cache hit ratio is below 90%?
17: What to do if the Data Dictionary Cache falls below 90%?
18: What to do if the Library Cache falls below 75%?
19: In our company, OEM is not installed, so how can I collect the statistics using AWR and
ADDM? And how can I tune the DB?
20: My DB was performing ok yesterday there is no change in code no backup or batch job is
running and performance is very slow how to investiagate and how to solve the prob?
21: My database is down and I cannot restore. How to Restore?
22: When we should run catcpu.sql and Why?
1: What is difference between backup piece, backupset & image copy?
2: What is meant by UNTIL SEQUENCE (or) UNTIL CANCEL?
3: How do you find the size of the table?
4: What is the day to day activities of a Oracle DBA?
5: What kind of issues will Oracle DBA usually be facing daily and how to solve those
issues?
6: What is the query to get the number of rows or rowids present in a table?
7: What is the difference between Export and Import(oracle 9i) and Datapump(oracle 10g)?
8: During SQL query execution what are the process takes place in SGA?
9: What major role does the background process plays in displaying the output for an SQL
query?
10: What is the difference between Physical backup and Logical backup?
11: How can I come to know that which SQL query is taking long time to get executed?
12: What is the difference between data guard and standby?
13: What do you mean by High Water Mart(HWM)?
14: Explain explain plan code for (select * from emp) query?
15: Explain Hot backup and Cold backup with an RMAN script?
16: What major challenges and problems did you face in your DBA job? How did you handle
them?
17: Will there be any changes in writing RMAN scripts for hot backup and cold backup? If
yes, then give me the script for hot backup and cold backup?
18: What are pathes in oracle?
19: What is compress utilities?
20: What are the steps involved in rman cloning?
21: What is a 'hist'?
22: What is a File system?
1: I have created one materialized view every day its taking 1hr :30 mins to refresh it
completely n today it took 4hrs:30mins still it didn't get completed. What is the reason behind
this? How to solve?
2: How to kill 50% user's session ( DB Level )?
3: What is fragmentation?
4: What is row chaining?
5: What is row migration?
6: What happens in the background, when we issue the command ALTER DATABASE
BEGIN BACKUP?
7: Which tablespace will RECYCLEBIN use?
8: What is a Latch?
9: What is hit ratio?
10: What is meant by incarnation number?
11: How to know the number of users are connecting to the db?
12: How to monitor the db?
13: How to reclaim the space from deleted entries from table?
14: For 250gb database, What is the size of SGA?
15: How to check free space in tablespace?
16: How to check free space in tablespace? If tablespace size 1mb autoextend on max is 2mb
after completing 1mb space what does it shows in freespace?
17: How to take the backup of oracle_home?
18: When a user fire a query then if it is not giving any o/p what might be the reason?
19: How we can know the db is fine?
20: What is latch?
21: If archived log location is 100% full what happens to the database?
22: How to create a db_link for a schema in your database to connect to another database?
1: How INDEX tablespace will be reorganized? Why?
2: When Oracle will display 'Unable to extend TEMP table space', Why and What is the
solution?
3: Is it possible to install oracle10g in RH-5? i tried to install but its giving "bash .runInstaller
: /bin/sh: bad interpreter :permission denied". What's the Problem?
4: Why we use cursors?
5: 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?
6: Typically, Where is the conventional directory structure chosen for Oracle binaries to
reside?
7: What's the benefit of "dbms_stats" over "analyze"?
8: You create a private database link and upon connection, fails with: ORA2085: connects to . What is the problem?
How would you resolve?
9: What are the list of ORA Errors(causes and solutions) does a DBA face in day today
activities?
10: Is it possible to add a disk to ASM Disk Group while DB is up and running? If yes, it is
how?
11: Does DB work, if FAL_CLIENT and FAL_SERVER parameters have been not
configured?
12: How can you restore SYSTEM.DBF in hot backup?
13: Is recovery possible for control file?
14: Give details what happens to DB buffer cache,Redo buffer cache and Redo log files
When Update is going on large amounts of data, but Redo buffers and Redo log files are not
enough to maintain.
15: When DB is up and running ,If control files are removed. What is the status of DB and
what happens?
16: If a user told that he is not connecting to DB, What should be his problem? How can you
solve his problem?
17: Is it possible to create a DB with only two redo log files(one group)?
18: Scenario: Redolog files are full, User inserted 1 TB of data and user didn't save, What
happens?
19: How can you confirm whether cluster installed successfully or not? Which command will
you use?
20: Is it possible to increase the SGA size when DB is up and running?
21: There are three voting disks , two are corrupted, does cluster work?
22: Where can you find table query statistics?
1: What are the major challenges faced as a DBA? How did you handled?
2: In windows VMware i have installed RHEL 4 and oracle 10g, now want to use Toad for
oracle DBA suite. How to configure toad with oracle 10g?
3: If a job is running daily for 10 mins, and same job is running for long hours on one day.
What are all the things we have to check in this scenario to findout why it's running slow on
that particular day?
4: What is the rman step restore database with backupset any on server location.
5: How to create a database manually in 9i? Difference between manual database creation in
9i and 10g.
6: HOW TO EXPORT A SCHEMA OF 30GB, IF 10GB IN /U01, 10GB IN /U02, 10GB
IN /U03.
7: Having 4 multiple controlfiles,one of the controlfile size is increased,why?
8: What is the pre-requirement of installing oracle 9i on linux?
9: Database is hang due to archive location 100% filled,what is the immediate solution?
10: Datafile disk corrupted, How will you recover these datafiles?
11: Someone changed the listener location,whether its affect the database or not?
12: I have 8x,9x,10x home in server,it is possible to use single listener for all the 3 home's?
13: How will you see all databases names in the server?
14: Using top command show more cpu utilization, How will u determined which user
process cause this?
15: How ill u enable archivemode without downtime? If yes, How?
16: During database startup PMON backround process run first. Why?
17: When will you change the dbname and dbid in databases?
18: In the scenario,you get the error is unable to extend in non-system tablespace,there is no
space in disk and datafile size reached 2gb, How to resolve this?
19: What is semapores? If semapore=1000, What will happen?
20: Existing users are able to connect but New user's can't connect to the database, Why?
(listener,maxuser is not a problem)
21: How can we find which server is Primary and which is Satnd by in dataguard?
22: What a DBA supposed to monitor and how in Database monitoring?
1: What may be the reasons for session inactive? Except archive log full.
2: What is database schema?
3: What is join in database?
4: What is dual in database?
5: What is the default format of date data type in database?
6: What is the difference between SQL and PL/SQL?
7: What is the difference between on-line and off-line backups?
8: What is the step for install oracle 10g on linux. What is the step for migrate database from
windows xp to linux.
9: Which Process reads data from Datafiles?
10: Which Process writes data in Datafiles?
11: How to configure OEM?
12: What are steps take place in SGA during the execution of INSERT?
13: Explain "Differences in tuning a OLTP and OLAP systems"?
14: Explain the difference between SPFILE, PFILE and init.ora?
15: What are the main errors that conatin in Alert Log File?
16: What are the day to day activities of a DBA?
17: What are INITIAL and NEXT parameter of an object & Is it possible to change?
18: How to find the size and free space of a Tablespace, Database Size & Log Files?
19: What is meant by undo page faulting
20: Difference between Physical Standby and Logical Standby Database.
21: How to overcome chained rows in oracle?
22: What are the main new features in Oracle 11g?
1: How can you check whether backup process is successful or not in rman?
2: How to improve the performance while exports and import time?
3: Explain the important parameter of the pfile, which is related to database
performance/tunning of the database?
4: What is recovery in instance?
5: How to recover SPFILE , Controlfile from the RMAN backup?
6: Why u need creating the catalog in RMAN?
7: I loss my undo table space in the DataBase. How should overcome from this situation to
recover the DataBase?
8: I loss my current alert log file? What hepppens to the database?
9: If online redo log files are lost. How to recover from this situation?
10: In solaris oracle if a client is unable to connect with one of his databases, what may cause
the error and how can we fix it?
11: I want to multiplex my control file with out shutdown my database, my database is a
producation database? If possible, How?
12: What type of login issues are faced by a dba?
13: What type of procedures will be followed for dumping by dba?
14: What is high water mark?
15: How to see the RAM size?
16: What datafile header contains?
17: What is top command?
18: I want to move the database from Dev to Prod, What are the security checks you have to
take from your side?
19: How many archive processes used in oracle?
20: What is ORA-01555 error?
21: Having 4 multiple controlfiles, What will be the size of all these control files?
22: Why do we need to generate the AWR and ADDM reports? What is use of It?
1: How to clone the database? What is the use of duplicate command?
2: Explain Oracle Error ORA-7444?
3: Explain ASM?
4: What is the Database Re-organization?
5: If I lose the control files, How to recover the Database?
6: What is the pre-requirement of installing oracle 10g on linux?
7: What is semapores & semapore=1000? what will happen?
8: What is the difference between traditional exp/imp and data pump?
9: What is RMAN?
10: Is RMAN a Cold Backup or Hot Backup?
11: How we can know our database is a dedicated server or shared server?
12: What is the tablespace Re-organization?
13: What are the type of the Startup and shutdown comment in realtime environment?
14: How to monitor the Oracle database in realtime?
15: What does it mean that "doesn't wait for awk in max protection",it does wait for awk in
max availablility and if didn't get it will shutdown the database in max performance in
dataguard.
16: If ASM instance is down(or not started the ASM instance) and we have started the
database. Now my question is that if we commit any transaction after adding/updating few
records. Will our transactions will save or not? If yes, where will the data save?
17: What is the advantages of using transportable tablespaces?
18: Can we start the database instance without starting the ASM instance?
19: What is the command to apply redologs?
20: I lost my pfile / spfile . How to i recover this without any backup?
21: Is it possible to restore a RMAN from Linux system to Windows system? How?
22: What are the steps to convert the physical standby db to logical standby db?
1: What you will work as a shift DBA?
2: What is difference between dba_tables,user_tables,all_tables?
3: How do u kill the user session connected to the Database?
4: How do u find which process is taking high cpu memory?
5: The present listener is running, After some time we make some changes in listener and we
make reload the listener what happens to the existing users.
6: Can we export multipule owners objects at a time like exp owner=u1,u2 if yes, How?
7: How to change our producation database max performance to max avaliablity and max
protection,which parameter used for this and Explain about this.
8: How many types of tunning in oracle 10g?
9: Explain the concepts : Database, SQL , Netwrok, Memory Tunning?
10: Explain the Tunning concepts in oracle 10g?
11: Where do u set the kernel level parameters? What are they? What is the use?
12: What is the use of bind variables? Which parameters we want to set in parameter file for
using the bind variables in library cache?
13: How to audit a particular user?
14: How to check the database size and tablespace size?
15: How to check the block corruption and how to resolve that? Different types?
16: What is the difference between Maximum Protection, Availability, Performance?
17: How can you export the table structure without the data?
18: How can you export few days updated data?
19: How to check the which database taking 100% cpu utilization in server?
20: How to see uptime for a database?
21: In imports, We use ignore=y. What is the use of it?
22: Explain the Errors: ORA-600 and ORA-01555?
1: What is diff between mount and nomount stage of a databse?
2: What is error ORA-7444?
3: How will bring the database into archivelog mode. Discuss the steps.
4: What is cache fusion?
5: What is db file sequence read?
6: What is the diff btwn rman catalog and rman nocatalog?
7: What is the diff between oracle session tracing and oracle auditing?
8: If listener is start, When users are connected,after sometime listener is stop then what
happens the connected users.
9: Can we made standby database read write mode, If yes, How do we do?
10: Can we transfer physical files from producation database to standby database through ftp.
It is to do manually. Is any method to do automatically?
11: What is master node in RAC and how to find it?
12: How to configure the stand by database from production database using RMAN backup?
13: What is fractured block? Where do we see that?
14: What is metalink?
15: How can we apply cpu patch.
16: DB is not allowing the new users. What is the Problem?
17: Explain CONSISTENT=Y/N parameter in export with example for both Y & N?
18: Why it ll show "snap shot too old error" if we set CONSISTENT=Y?
19: What are the daily activities of Oracle DBA?
20: What is the crucial situation you got in last two years of carrier while handling production
database?
21: What is best problem you solved in your company as a DBA and you got appreciation?
22: Tell me some situations, When the database gets hang. How you find the problem and
solve them?
1: How can i see redo log file is empty and full and which view i can use to see it?
2: What is difference b/w maximum availability , maximum protection and maximum
performance in dataguard?
3: What is the meaning of roll forward and roll backward?
4: How to convert oracle database multilingual?
5: What is diff. b/w small file and big file? In real time which file is using?
6: How do u view how many users connected in a database oracle 10g?
7: User creation script in real time environment.
8: My database is up and running...i lost my control file.... after that what will happen if i give
the command like " alter system backup control file to trace" it will work or not?
9: How can i find out how many user logged in a database?
10: What are the day to day activities of Oracle DBA?
11: If my database size 100gb what is the size of sga?
12: What are the New features of 11g?
13: How to limit the session, for ex 100 users only allowed.
14: What are the real time issues faced by DBA's?
15: How to check the database size?
16: How the Bitmap Index help in Data Warehousing?
17: Describe v$session.
18: How to check our Oracle software version?
19: Where would you look to find out if a redo log was corrupted assuming you are using
Oracle mirrored redo logs?
20: How to determine if the standby is operating as a physical or logical standby?
21: How to see alert log file for errors?
22: What are the different modes of mounting a Database with the Parallel Server?
1: You have room for each to grow by 20 more extents each. Is there a problem? Should you
take any action.
2: You see multiple extents in the temporary tablespace. Is this a problem?
3: Define OFA.
4: How do you set up your tablespace on installation?
5: Which query you will use to find the tables with no indexes for particular user?
6: How to find the tables which are not having indexes?
7: In oracle 9i/10g, default db_writer_process is 1. How to change the number of
db_writer_process?
8: What is the difference between segment space management auto and manual?
9: Can i drop undo tablespace that which status is pending? If i drop pending undo
tablespace, what will happen?
10: What is mean by MAXINSTANCES in controlfile?
11: What is the difference between connect_time and cpu_per_session in profile creation?
12: Which privilege user must have to shutdown the database?
13: Consider the following incomplete Recovery.. "RECOVER DATABASE UNTIL
CALCEL" In this What UNTIL CALCEL means?
14: What is the difference between whole database backup and full database backup?
15: I created a script in RMAN for incremental level 1 backup. But i forgot that script name.
Now i wants to execute that script. How can i find that script name that is created by me?
16: In Normal export(Not data pump) paramter transport_tablespace = (Y/N).. In this Wat Y
and N means?
17: How to identify that the perticular table has been exported or not in its lifetime.
18: How to check the database in the consistent state?
19: During a Linux based installation of Oracle what is the purpose of modifying the kernel
parameters in /etc/sysctl.conf file?
20: What are the types of logs?
21: What are the different high availability solutions provided by Oracle?
22: Cold backup script in linux?
1: What are some indications that you need to increase the shared_pool_size parameter?
2: What is the general guideline for sizing db_block_size and db_multi_block_read for an
application that does many full table scans?
3: What is the fastest query method for a table?
4: Explain the use of TKPROF? What initialization parameter should be turned on to get full
TKPROF output?
5: What are the 10g new features in oracle DBA?
6: When looking at v$sysstat you see that sorts(disk) is high. Is this bad or good?
7: When should you increase copy latches. What parameters control copy latches?
8: Where can you get a list of all initialization parameters for your instance? How about an
indication if they are default settings or have been changed?
9: Describe hit ratio as it pertains to the database buffers. What is the difference between
instantaneous and cumulative hit ratio and which should be used for tuning?
10: What is the fundamental difference between cold & hot cloning?
11: In RMAN can we do complete & incomplete recovery ? How?
12: Discuss row chaining? How does it happen? How can you reduce it? How do you correct
it.
13: When looking at the estat events report you see that you are getting busy buffer waits. Is
this bad? How can you find what is causing it.
14: If you see contention for library caches how can you fix it.
15: If you see statistics that deal with "undo" what are they really talking about.
16: If tablespace has a default pct increase of zero what will this cause(in relationship to the
SMON process).
17: How can you tell if a tablespace has excessive fragmentation?
18: You see the following on a status report: redo log space requests 23 redo log space wait
time 0 Is this some thing to worry about? What if redo log space wait time is high? How can
you fix this.
19: What can cause a high value for recursive calls? How can this be fixed?
20: If you see a pin hit ratio of less than 0.8 in the estat library cache report. Is this a
problem? If so, how do you fix it.
21: You look at the dba_rollback_segs view and see that there is large number of shrinks and
they are of relatively small size, Is this a problem? How can it be fixed if it is a problem?
22: You look at the dba_rollback_segs view and see that you have a large number of wraps. Is
this a problem.
1 How does one create a new database?
2 What database block size should I use?
3 What database aspects should be monitored?
4 How does one rename a database?
5 Can one rename a database user (schema)?
6 Can one rename a tablespace?
7 How does one see the uptime for a database?
8 Can one resize tablespaces and data files?
9 How do I find the overall database size?
10 How do I find the used space within the database size?
11 Where can one find the high water mark for a table?
12 How do I find used/free space in a TEMPORARY tablespace?
13 How can one see who is using a temporary segment?
14 Who is using which UNDO or TEMP segment?
15 How does one get the view definition of fixed views/tables?
16 How full is the current redo log file?
17 Tired of typing sqlplus '/as sysdba' every time you want to do something?
18 What patches are installed within an Oracle Home?
19 How does one give developers access to trace files (required as input to tkprof)?
I: General Backup and Recovery question
1: Why and when should I backup my database?
2 :What strategies are available for backing-up an Oracle database?
3: What is the difference between online and offline backups?
4: What is the difference between restoring and recovering?
5: My database is down and I cannot restore. What now?
6: How does one backup a database using the export utility?
7: How does one put a database into ARCHIVELOG mode?
8: I've lost an archived/online REDO LOG file, can I get my DB back?
II: User managed backup and recovery
1: How does one do off-line database backups?
2: How does one do on-line database backups?
3: My database was terminated while in BACKUP MODE, do I need to recover?
4: Does Oracle write to data files in begin/hot backup mode?
III: RMAN backup and recovery
1: What is RMAN and how does one use it?
2: How does one backup and restore a database using RMAN?
3: How does one backup and restore archived log files?
4: How does one create a RMAN recovery catalog?
5: How does one integrate RMAN with third-party Media Managers?
6: How does one clone/duplicate a database with RMAN?
7: Can one restore RMAN backups without a CONTROLFILE and RECOVERY
CATALOG?
IV:General questions on oracle database
1: How to find the size of a table?
2: what is crontab?
3: what is ora inventory?
4: what is ora env?
5: what is oratab?
6: How do you check the free space of database?
7: How do you find the size of the database?
8: The database size is 20 GB, Now I have added datafile, After one week, database size
shown as 30 GB. How would know this.
9: Installation of Oracle.
(i) what is the use of running root.sh and orainst.sh?
10: what is ckpt?
11: what happens in the background when we give SQL statement?
12: There are three control files in the database, if one control file misses, what will happen to
the database?
13: How to recreate control file?
14: what is the use -R while granting permissions to user?
15: what is the use of resetlogs option?
16: client asks he needs the database for testing purpose, what will you do, and how will you
give the database without shutting down the production database?
17: While doing Hot Backup, After giving the statement alter database begin backup or alter
tablespace begin backup? what happens in the background?
18: How to resize logfile?
19: How to rename logfile?
20: How to add logfile?
21: How to check how many databases are running?
22: What happens if we give commit statement in background?
23: What is logfile?
24: What are modes of logfile?
25: What is CKPT? When it occurs and what happens if it occurs?
26: Can we use Arch instead of smon in
ps -ef|grep smon
27: what is the difference between statspack, addm and awr?
28: when does the dbwr, lgwr will write to the disks?
29: What is Instance Recovery?
30: If you miss the init.ora file, what is the situation of the database?
31: what is the use of SPFILE?
32: If spfile is crashed when database is running? what happens?
33: If the database is running on shutdown if pfile and spfile is missing? what happens now?
34: what is a latch?
35: Types of locks?
36: what is the use of archive log files?
37: cloning of database.
38: what is materialized view?
39: Types of Indexes?
40: Difference between B-tree and Bit-map Index?
41: Whar is Logical backup?
42: what is difference between normal export, import and datapump?
43: what is compress=y parameter and consistent=y in export?
44: Error codes in alert log file?
45: What kind of statement will be there in logfile?
46: what is external user?
47: New features in Oracle 11g.
48: What is Role and profile?
49: How will set a profile like, when he gives a wrong password for six times, the session
should lock and after some time it should unlock the session.
50: How many types of Optimizers are there?
51: How will you set the performance tuning statistics?
52: yesterday the user has executed the sql statement properly, today the same sql statement is
not executing properly? what do you do as a DBA?
53: What information is generated in the trace file?
54: What kind of errors you have been resolved after viewing the alert the log file?
55: Error code 01115?
56: After we creating control file, have u seen any messages in alert log files.
57: Name the Main Background Processes.
58: What PMON will do?
59: If SMON is missed, does PMON will give any messages.
60: When does LGWR writes files to the log files?
61: Name the components of SGA.
62: What does shared pool do?
63: How do you configure recovery catalog database?
64: Have you done in Upgradation.
65: Have you used SQL*Loader.
66: Have you created standby database.
67: What is semaphore?
68: What is Parsing?
69: What happens in background, when we give select * from emp?
70: What happens in the background,if DML command is issued?
71: What is Optimizer?
72: What is ORA-01555?
73: What is snapshot too old error and how it is specified?
74: What will save in the redo log files and when does LGWR will writes data from log
buffer cache to log files?
75: When check point occurs?
76: What happens between begin backup and end backup?
77: When begin backup occurs, if it was a big tablespace, then what about the redo log files?
78: What kind of locks will happens for DML and DDL statements?
79: What is the worst situation, you have faced in your job?
80: What is your daily activity?
81: What kind of errors will occur in alert log file and what kind of errors you have solved?
82: what is the maximum size of your database?
83: Advantages of data pump.
84: new features of oracle 10g.
85: what is consistent=y in exp.
86: what kind of data is stored in undo segments?
87: what occurs in commit and rollback, if it occurs?
88: where the committed will go?
89: what is the buffer paramter in exp.
90: what kind of performance planning do you use.
91: what is retention guarantee in undo?
92: If performance degraded, what are the pre-liminary steps taken by DBA.
93: One user A is having a table, and the other user B wants to select the columns from A
table? what will you do for this scenario.
94: what happens when archive log destination is full?
95: the production database is having a table called employee and 100 partitions are there in
this table.In the development database the same table is present with 50 partitions. (How will
you update the table in production to develpment using datapump). How will you update this
table to 100 partitions using datapump.
96: what is your backup strategy?
97: Do you go with export and import.
98: Do you go with user level backups and table level backups.
99: tell about the oracle architecture.
100: Tell about RMAN cloning steps.
101: what is the parameter DB_FILE_NAME_CONVERT will do?
102: RMAN cloning, How the cloning database will recognize the locations?
103: Do you know about Data Guard.
104: Do you know performance issues.
105: when user is accessing the database, yesterday the database was good, today the
database performance is low, what is you do?
106: Have u generated AWR scripts. How do you awr scripts?
107: Have you generated ADDM scripts?
108: How do you know the disk storage space in linux and unix?
109: how do you check cpu usage?
110: Tablespace utilization.
111: how much time it is required to export a user database.
112: database administartion tasks.
113: what is planning of the database. how do you plan.
114: If the database size is 10 GB for one user, and there are 60 users, how many tablespace
you will create?
115: I have four data files, the data files size have the size of 5 GB with auto extend option
and one with 10 GB with out auto extend option. which will you give the better performance.
116: what is the difference between 9i and 10g?
117: I have exported your database, on some users data in oracle 10g, I want to import in 9i
database. How do you import. what is the procedure.
118: I am importing one dumpfile. If it is giving some header is missing, what is the solution
for this.
119: what is the base element of configuration of an oracle database?
120: what is the of storage clause?
121: How do you find out the optimizer plan to choose the best execution plan?
122: what are the other methods to choose the best execution plan?
123: I have 5 branches, and each branch having one database, now the head office want to
view all the databases. Now it is better to keep all the databases in one database or keeping
each other different. What is your solution.
124: Difference between cold backup and hot backup?
125: What is export?
126: what is mirroring?
127: what is the use of mirroring? and what would happen if any mirror files are lost. will the
mirror file recover automatically.
128: Have you involved in crash recovery.
129: Explain Installation of Linux.
130: Explain three-tier architecture and network connections used in that.
131: difference between pfile and spfile.
132: Explain oracle architecture.
133: what will you do if a control file is lost.
134: have you used different block sizes for different tablespaces.
135: how many databases are there?
136: what is the maximum size of the database?
137: for the maximum size of the database, what is the SGA_TARGET value you set to?
138: what is the size of redo log file of the maximum database.
139: what is the size of the archive log file for the maximum size of the database?
140: what is the database reorganization?
141: which software version you are using for your operating?
142: what is the command to know the memory use of the server?
143: which command is used for the memory usage?
144: In 10g, is it compulsory to create sysaux tablespace.
145: What does SYSAUX will store.
146: can sysaux tablespace can be taken as offline.
147: can undo tablespace can be taken as offline.
148: where the recylcebin data will be stored.
149: explain the error ORA-600.
150: can temporary and system tablespace can be taken offline.
151: what is your backup strategy?
152. what is the difference between user managed backup and rman backup?
153. the client says, he need the backup the database is no archively mode through RMAN, in
this situation how will you react?
154. Have you undergone any migrations from one platform to another?
155.Rman clonning steps?
156. How do you move a table from one tablespace to another tablespace?
157. There are 10 indexes for a table, now you have moved the table from one tablespace to
another tablespace. what will happen to the indexes?
158. I have one table for this table there are two indexes created for this table. one index is
corrupted. How will you recover this index?
159. When will you rebuild a Index?
160. Explain about scheduling backups?
161. In Hot backup will you backup the undo tablespace, temporary tablespace and system
tablespace?
162. What is a database link? what is the use of a database link?
163. What is materialised View?
164. What is the difference between view and materialized view, what is use of the
materialized view?
165. what is the database statistics, what is use?
166. Ones user says the query is running slowly at the time what are the actions will take?
167. What is the difference between costbased optimization and rulebased optimization?
168. There are 2 databases, in that database one schema user data needed into the another
databases, what action will you do for this scenario?
169. what is difference between static and dynamic parameters?
170. For example X,Z one parameter how do you find x and z parameters are static or
dynamic?
171. What is the difference between logical standby database and physical database?
172. What is dataguard?
173. How you kill a user at the database level?
174. What is the difference in windows platform and linux platform while creating database
manually?
175. What is semaphore?
176. How can you limit the user password limit to three digits only?
177. If the user complaint that his section is flow how can you find out if how do you
rectified that?
178. How can you find out sql ID? Datadictionary view?
179. How can you start and shutdown the host without user interface? cronjob need
userinterface?
180. Installation, performance tunning know?
181. What are kernel parameters set while creating database?
182. In Rman how can you recover the lost archive log file?
183. If user finds out that emp table was lost in previous time? how can you restore it?
184. What are your day to day activities?
185. Where is you company?
186. What is your ticketing tool?
187. 10g new features? Which is your favorite one?
188. How can you do backup, hot , rman, datapump?
189. kernel parameters in installation? What is that path?
190. Background processes?
191. Tool used in company?
192. Smon, Have you done database refreshing?
193. If the space is filled up in Tablespace, what to do?
194. Hot and cold backup using Rman?
195. If there are 10 databases, how can you put them in crontab, using Rman how can you
backup?
196. What is Row chaining? how can you prevent row chaing?
197. Control file demultiplexing?
198. Backup incremental mechanism?
199. Current version?
200. Clonning using hot backup?
201. Hot backup (what happend internally when you put begin backup mode) can you put
system tablespace in begin backup mode?
202. When the disk is crashed, can you move datafiles to another location ( external tape
drive)?
203. Can you rename undo tablespace?
204. Rman can you use rman without catalog? if possible what happened? Crash recovery?
205. SGA : what is sga_ target and sga_ max? can sga_target will be zero?
206. Dynamic memory component?
207. What info can you see in alert log file? if a datafile is deleted/truncated/updated will that
info enters into alertlog file?
208.Cross checking or health checking using rman?
209. What are prerequisites before hot backup?
210. What are prerequisites before dataguard?
211. What is difference between dictionary managed tablespace and locally managed
tablespace?
212. Explain transportable tablespace procedure?
V:Real Time related Questions
1: which tool is used for db ticketing?
2: how do you check alert log file?
3: what is your team size?
4: when archive destination is full, what happens to the database? how to increase the size the
archive? if we don't need to resize, then what is the solution?
5: if one control file is lost, when the database is up, does it affects to the running database?
If you lost any of the control files, database will stop working and fail. You need to restore
the control file by copying any existed multiplexed control file to lost control file location
and rename the control file as lost control file name.
6: when sql statement is issued what happens in the background?
7: when you create a new user, you need to parse the password when he logins?
8: difference between hot and cold backup?
9: how do you connect to the remote server as a DBA?
10: what are the physical and logical files in a database?
11: add a new datafile to a existing tablespace and how will you add a temp file to a
temporary tablespace?
12: what are the tools you use to monitor the database?
13: how do you come to know whether you database is full or how will you check your
database is corrupted?
14: what will happen when the tablespace is in begin backup mode?
15: in os level, how do you check in which user you are?
16: what are the steps to be taken before inserting a new data file to a particular tablespace?
17: what are your daily activities?
18: exporting a schema from schema A to schema B?
19: first time- select * from emp; - t1
second time - select * from emp; - t2
which one will be faster executed?
20: difference between cold and hot backup? which one you will prefer?
21: creating a table using select *?
22: for the above question, you need to get the table definition or table structure?
23: what is the size of your databases in your environment?
24: transaction related question - i will give dml or ddl, create table tablename and you have
inserted certian rows into this table, after some point of time, you will agian alter the table
using alter command, and you will just come out with out saving(without commit or
rollback). whether the transaction will be committed or not, when we relogin again.
25: what are the background processes?
26: different types of segments?
27: different types of physical files in oracle database.
28: difference between procedures and functions.
29: Tell me about your environment.
30: what is the size of the database?
31: how many databases are there in your environment?
32: RMAN Cloning Steps
33: what will the ORA-600 error causes? what will you do?
34: database Patching.
35: Upgration procedures
36: how do you check the objects exists in your database?
37: difference between exp and expdp?
38: How do you rename a temporary tablespace?
39: what are the steps neeeded to rename a undo tablespace?
40: alter system set check point? what will happen?
41: steps to cold and hot backup.
42: while taking a backup, redo log files are mandatory?
43: while recovery, without redo log files, can we start the database.
44: while taking hot backup, which are the things will occur in the background?
45: what is logical backup? why it is named so?
46: explain oracle architecture.
47: what is the tar command in linux?
48: explain file system in linux.
49: how do you check the disk usage in linux?
50: how do you schedule your crontab?
51: steps to rename the system tablespace.
52: how do you increase the size of sga?
53: create the pfile from the destination database.
54: without knowing the location of tablespaces and the locations of dump directories, at this
situation we don't have the control file. what do you do to bring the database.
55: difference between 9i and 10g?
56: difference between flashback query and flashback database?
57: what are the contents of flashback?
58: how do you export a schema from one schema to antoher using data pump, what is the
difference while executing noraml export and data pump.
59: how data stored in logical backup.
60: what are the contents of sysaux tablespace?
61: what does the resetlogs option will do?
62: what is the use of flashback when you have backup in RMAN?
\63: how do you know which background processes are running and how will you know
which databases are running in a particular server.
64: what is the use of password file?
65: default port number of OEM.
66: if tablespace is in begin backup mode, can we add a datafile to that particular tablespace.
67: during RMAN backup, some files are missing, do RMAN backup will continue or not.
68: how do you solve the deadlock errors.
69: operating system version and oracle versions which you are using.
70: how many concurrent users will connect at a time.
71: team members in your company.
72: what are the roles.
73: what's your operating system bit size?
74: you got new database, how will you perform hot backup on it.
75: define row migration and row chaining.
76: how do you check alert log file.
77: how data will be there in alert log file.
78: what are the critical errors you got to know in alert log file, do you remember the error
number, and what does these done to your database.
79: size of your database and how many databases are there in your environment.
80: different types of segments. explain all.
81: explain your environment looks like.
82: Architecture
83: Tablespaces
84: checkpoint
85: Recovery
86: Expdp & Impdp
87: Data Guard
88: Tuning
89: RMAN
90: Hot backup - cronjobs
91: Cronjobs
92: Oracle Server Configuration
93: Server Version
94: OS Version
95: Row migration & Row Migration
96: all control files lost. no backup. how do you recover.
97: how do you resize online redo log file.
98: rename a redo log file.
99: some unix commands
100: in alert log file what do you find.
101: recent problem you faced in your environment.
102: How do see the top 20 processes running in the server.
103: how do you check how many users are there in unix environment.
104: explain TOP command.
105: Unix basic commands.
106: in your enviroment, what are your different server types, like cpu's and memory size.
107: how do you add a temp file to temporary tablespace.
108: Explain about undo tablespace.
109: what are the steps taken before hot backup.
110: explain about normalization.
111: explaing about tkprof.
112: explain point in time recovery.
113: how do you usually check alert log file.
114: explain about your project.
115: explain about flashback.
116: difference between truncate and delete
117: architecture
118: performance tuning: database buffer cache hit ratio using AWR report.
119: cold and hot backup.
120: data file resize and adding a datafile.
121: roles and responsibilities.
122: day to day work.
123: alert log file.
124: Architecture
125: Imports and Exports
126: Joins
127: PLSQL
128: Can you make a datafile resize in all the scenarios.
129: Point in time reocvery.
130: hot backup.
131: SGA target
132: data pump
133: Redolog files
134: SQL query selection process.
135: imposing a tablespace if it is full. how will you make it to wait.
136: checkpoint.
137: roles and responsibilities
138: what is your day to day activities.
139: what is your daily work.
140: what are the errors during export and import.
141: what is the export and import, if you have RMAN.
142: How do you monitor the database.
143: recent solrais version
144: RAC
145: Transportable Tablespace
146: Exp/Imp error on data type LOB.
147: Managing control files.
148: RMAN
VI:HR Related Questions:
1: tell me about yourself
2: what are your day to day activities
3: what are semahpores
4: how can you limit the user password limit to 3 digits only
5: if the user complaints that his session is slow,how can you find out it ,how do you rectify
that ?
6: how can you find sql id ? data dictionary view ?
7: how can you start and shutdown the host without user interface ? cron jobs need user
interface ?
8: do you know installation and performance tuning?
9: what are kernal parameters set while creating database ?
10: in Rman how can you recover the lost archive log files ?
11: if user finds out that emp table was lost in previous time? how can you restore it?
Nothing flashback query should be fired, by firing this our database will go prior to the time
(Back)..
12: what are ur contributions to ur previous company ?
13: what is ur ticketing tool ?
14: Have you worked on sun solaris?
15: 10 G new fatures ? which is ur favorite one ?
16: how can u do backups / hot / rman / datapump ?
17: tell me about kernal parameters in installation ? what is that path ?
18: tell about background processes ?
19: tools used in ur company ?
hcl - remedy
ibm - amgen
better to tell UNICENTER service disk
USD
20: have u done database refreshing ?
21: if the space is filledup in ts what u do ?
22: hot/cold backup using Rman
23: arch dest is full or lost what you do ?
24: What is Ur Ticking Tool ??
25: If there are 10 Databases, how can you put them in crontab, using Rman how can you
Backup
26: Row Chaining / How can you prevent Row chaining
27: Controlfile Demultiplexing
28: Backup internal Machanism
29: if thr are 10 databases,how can you monitor backup & recoverys
30: how to prevent row chaining
31: controlfile demultiplexing
32: internal process in backups
33: Alert log file lost
34: which process reads data process the undo tablespace
35: which background process helps in issuing the alert when it reach the threshold value
36: current version database you are using?
37: cloning using hot backup ?
38: hot backup ( what happend internally when you put begin backup mode)
can you put system tablespace in begin backupmode ?
39: when the disk is crashed,can you move datafiles to another location { external tape drive}
40: can you rename undo tablespace ?
41: Rman: can you use Rman without catalog ? if possible what happened ?
42: crash recovery
43: SGA: what is sga_target, & sga_max ? can sga_target will be Zero ?
44: prerequisites before hot backup ?
45: prerequisites before Dataguard ?
46: dataguard setup ?
47: levels of protection in dataguard ?
48: what info can you see in Alertlog file ? if a datafile is deleted/truncated/updated will that
info enters into alertlogfile ?
49: crosschecking in rman / & healthchecks using rman ?
How do you partitioned a table?
How do you design a database to detect and prevent unauthorized intrussion?
what is the function of flashback database, flashback transaction, and flashback query?
What is an RMAN repository used for.
How you will proceed for Performance Tunning on poor-performing DB?
What is Split brain in RAC? How to avoid ?
What are the pre-requisites for STANDBY DB creation?
What is Network-Link in datapump?
Describe top Wait Events,which comes in AWR ?