Explain the difference between a hot backup
and a cold backup and the benefits associated with each.
A hot
backup is basically taking a backup of the database while it is still up and
running and it must be in archive log mode.
A cold
backup is taking a backup of the database while it is shut down and does not require
being in archive log mode.
The
benefit of taking a hot backup is that the database is still available
for use
while the backup is occurring and you can recover the database to any point in
time.
The
benefit of taking a cold backup is that it is typically easier to administer
the backup and recovery process.
In addition, since you are taking cold backups the database does not require being in archive log mode and thus there will be a slight performance gain as the database is not cutting archive logs to disk.
In addition, since you are taking cold backups the database does not require being in archive log mode and thus there will be a slight performance gain as the database is not cutting archive logs to disk.
You
have just had to restore from backup and do not have any control files. How would
you go about bringing up this database?
I would
create a text based backup control file, stipulating where on disk all the data
files where and then issue the recover command with the using backup control
file clause.
Explain
the difference between a data block, an extent and a segment.
A data
block is the smallest unit of logical storage for a database object. As objects
grow they take chunks of additional storage that are composed of contiguous
data blocks.
These
groupings of contiguous data blocks are called extents. All the extents that an
object takes when grouped together are considered the segment of the database
object.
Explain
the difference between ARCHIVELOG mode and NOARCHIVELOG mode and the benefits
and disadvantages to each.
ARCHIVELOG
mode is a mode that you can put the database in for creating a backup of all
transactions that have occurred in the database so that you can recover to any
point in time.
NOARCHIVELOG
mode is basically the absence of ARCHIVELOG mode and has the disadvantage of
not being able to recover to any point in time.
NOARCHIVELOG
mode does have the advantage of not having to write transactions to an archive
log and thus increases the performance of the database slightly.
Give
the stages of instance startup to a usable state where normal users may access it.
STARTUP NOMOUNT - Instance startup
ALTER DATABASE MOUNT - The database is
mounted
ALTER DATABASE OPEN - The database is
opened
Explain
the difference between $ORACLE_HOME and $ORACLE_BASE.
ORACLE_BASE
is the root directory for oracle. ORACLE_HOME located beneath ORACLE_BASE is where
the oracle products reside.
Explain difference between Oracle Database and Instance
Oracle
database consist of following:
Datafiles, Control Files, Redo Log Files
Oracle
instance consist of following:
SGA and Oracle processes (DBWR, LGWR,
SMON, PMON, ARCH, CKPT, etc.)
What is main purpose of CHECKPOINT?
A
Checkpoint is a database event, which synchronizes the data blocks in memory
with the datafiles on disk.
A
checkpoint has two purposes:
1. To establish data consistency
2. Enable faster database recovery.
How do you rename a database?
1. Alter
Database backup control file to trace;
2. Above
step will create a text control file in user_dump_dest directory.
3. Change
name of the Database in above file and in init.ora file.
4.
STARTUP NOMOUNT
5. Run
the script that was modified in step 3
6. ALTER
DATABASE OPEN RESETLOGS;
What is a deadlock and
Explain?
A
deadlock is a condition where two or more users are waiting for data locked by
each other.
Oracle automatically
detects a deadlock and resolves them by rolling back one of the statements
involved in the deadlock, thus releasing one set of data locked by that
statement.
Statement
rolled back is usually the one which detects the deadlock. Deadlocks are mostly
caused by explicit locking because oracle does not do lock escalation and does
not use read locks. Multi-table deadlocks can be avoided by locking the tables
in same order in all the applications, thus precluding a deadlock.
What
are the system resources that can be controlled by profile?
1. Number
of concurrent sessions by user
2. CPU
processing time
3. Amount
of Logical I/O
4. Amount of Idle time
When a user process fails, what background process cleans up after it?
PMON
How do you resize a data file?
alter database datafile '/ora01/oradata/users02.dbf' resize 100M;
What is database?
A database is a logically coherent collection of
data with some inherent meaning, representing some aspect of real world and
which is designed, built and populated with data for a specific purpose.
What is the difference between DBMS and RDBMS?
DBMS is persistent and accessible when the data
is created or exists, but RDBMS tells about the relation between the table and
other tables.
RDBS supports a tabular structure for data and
relationship between them in the system whereas DBMS supports only the tabular
structure.
DBMS provide uniform methods for application that
has to be independently accessed, but RDBMS doesn’t provide methods like DBMS
but provide relationship which link one entity with another.
What is a Dataguard?
Oracle
Dataguard is a disaster recovery solution from Oracle Corporation that has been
utilized in the industry extensively at times of Primary site failure,
failover, switchover scenarios
What is the difference between Active Dataguard, and the Logical Standby
implementation of dataguard?
Active
dataguard is almost like a physical standby. We can use physical standby for
testing without compromising on data. We can open the physical standby on
read/write mode, so that we can do some destructive things in it (drop tables,
change data, whatever – run a test – perhaps with real application testing).
While we are using it for reporting, redo will still stream from production.
What is difference between physical and standby databases?
The main
difference between physical and logical standby databases is the manner in
which apply services apply the archived redo data:
a) For physical
standby databases, Data Guard uses Redo Apply technology, which applies redo
data on the standby database using standard recovery techniques of an Oracle
Database.
b) For logical
standby databases, Data Guard uses SQL Apply technology, which first transforms
the received redo data into SQL statements and then executes the generated SQL
statements on the logical standby database.
What is Data Guard Broker?
What is Data Guard Broker?
Data guard
Broker manage primary and standby databases using the SQL command-line
interfaces or the Data Guard broker interfaces, including a command-line
interface (DGMGRL) and a graphical user interface that is integrated in Oracle
Enterprise Manager.
It can be used
to perform:
a) Create and
enable Data Guard configurations, including setting up redo transport services
and apply services
b) Manage an
entire Data Guard configuration from any system in the configuration
c) Manage and
monitor Data Guard configurations that contain Oracle RAC primary or standby
databases
d) Simplify
switchovers and failovers by allowing you to invoke them using either a single
key click in Oracle Enterprise Manager or a single command in the DGMGRL
command-line interface.
e) Enable
fast-start failover to fail over automatically when the primary database
becomes unavailable. When fast-start failover is enabled, the Data Guard broker
determines if a failover is necessary and initiates the failover to the
specified target standby database automatically, with no need for DBA
intervention.
What are the Data guard Protection modes and summarize each?
Maximum
availability :
This protection
mode provides the highest level of data protection that is possible without
compromising the availability of a primary database. Transactions do not commit
until all redo data needed to recover those transactions has been written to
the online redo log and to at least one standby database.
Maximum performance:
This is the
default protection mode. It provides the highest level of data protection that
is possible without affecting the performance of a primary database. This is
accomplished by allowing transactions to commit as soon as all redo data
generated by those transactions has been written to the online log.
Maximum protection:
This protection
mode ensures that no data loss will occur if the primary database fails. To
provide this level of protection, the redo data needed to recover a transaction
must be written to both the online redo log and to at least one standby
database before the transaction commits. To ensure that data loss cannot occur,
the primary database will shut down, rather than continue processing
transactions.
If you have a low-bandwidth WAN network, what can you do to improve the
Oracle 11g data guard configuration in a GAP detected situation?
Oracle 11g
introduces the capability to compress redo log data as it transports over the
network to the standby database. It can be enabled using the compression
parameter. Compression becomes enabled only when a gap exists and the standby
database needs to catch up to the primary database.
alter system
set log_archive_dest_1=’SERVICE=DBA11GDR COMPRESSION=ENABLE’;
How can you control when an archive log can be deleted in the standby
database in oracle 11g ?
In Oracle 11g,
you can control it by using the log_auto_delete initialization parameter. The
log_auto_delete parameter must be coupled with the log_auto_del_retention_target
parameter to specify the number of minutes an archivelog is maintained until it
is purged. Default is 24 hours. For archivelog retention to be effective, the
log_auto_delete parameter must be set to true.
Can Oracle Data Guard be used with Standard Edition of Oracle?
Yes and No. The automated features of Data Guard are not available in the standard edition of Oracle. You can still however, perform log shipping manually and write scripts to manually perform the steps. If you are on unix platform, you can write shell scripts that identify the logs and then use the scp or sftp command to ship it to the standby server. Then on the standby server, identify which logs have not been applied and apply/recover them maually and remove them once applied.
What is dump destination? What are bdump, cdump
and udump?
Trace files for Oracle processes are stored in
dump destination.
Bdump-
Oracle writes to the trace log and creates trace files for background processes
in background dump destination. If this directory becomes full and more files
cannot be written, debugging becomes difficult.
Cdump- Oracle writes core files and background
processes in Core dump destination directory. If this directory becomes full
and more files cannot be written, debugging becomes difficult.
Udump – Oracle creates trace files for the user
processes in the User Dump directory if this directory becomes full and more
files cannot be written, debugging becomes difficult.
Why and how the deadlock situation arises
A deadlock situation arises when two or more
users wait for the same resource locked by one another or two or more processes
wait to update rows which are locked by other processes. Oracle if detects a
deadlock, rolls back the session chosen by the deadlock victim.
What is Cache Fusion Technology?
In Cache fusion, multiple buffers join to act as
one. It eliminates disk i/o operations by making use of a scalable shared
cache. It treats multiple buffer caches as one thereby resolving data
consistency issues. Cash fusion technology can provide more resources and
increases concurrency of users.
What is the difference between Cloning and
Standby databases?
The clone database is a copy of the database
which can be opened in read write mode. It is treated as a separate copy of the
database that is functionally completely separate.
The standby database is a copy of the production
database used for disaster protection. In order to update the standby database;
archived redo logs from the production database can be used. If the primary
database is destroyed or its data becomes corrupted, one can perform a failover
to the standby database, in which case the standby database becomes the new
primary database.
Where are the Clusterware files stored on a RAC environment?
The Clusterware
is installed on each node (on an Oracle Home) and on the shared disks (the
voting disks and the CSR file)
Where are the database software files stored on a RAC environment?
The base
software is installed on each node of the cluster and the
database storage on the shared disks.
database storage on the shared disks.
What is a raw device?
A raw device is a disk drive that does not yet have a file system set up. Raw devices are used for Real Application Clusters since they enable the sharing of disks.
What is the Cluster Verification Utiltiy (cluvfy)?
The Cluster Verification Utility (CVU) is a validation tool that you can use to check all the important components that need to be verified at different stages of deployment in a RAC environment.
How do I identify the voting disk location ?
#crsctl query
css votedisk
How do you backup voting disk ?
#dd
if=voting_disk_name of=backup_file_name
How do I identify the OCR file location
check
/var/opt/oracle/ocr.loc or /etc/ocr.loc ( depends upon platform)
or
#ocrcheck
or
#ocrcheck
What is SCAN?
Single Client
Access Name (SCAN) is s a new Oracle Real Application Clusters (RAC) 11g
Release 2 feature that provides a single name for clients to access an Oracle
Database running in a cluster. The benefit is clients using SCAN do not need to
change if you add or remove nodes in the cluster.
What is VIP used for?
It returns a
dead connection IMMIDIATELY, when its primary node fails. Without using VIP IP,
the clients have to wait around 10 minutes to receive ORA-3113: “end of file on
communications channel”. However, using Transparent Application Failover (TAF)
could avoid ORA-3113.
No comments:
Post a Comment