Latches

Today , I  will tell you some basic Differences Between Latches and Lock

Latches	Locks
Latches Provide only exclusive access to protected data structures	Locks Allow serialized access to some resources
Request are not queued, if a request fails, process may try later	Requests for locks are queued and serviced in order
Latches have Simple data structure	Locks have a Complex data structure , that is further protected by latch
Protect resources that are briefly needed (LRU list)	Protect resources needed for a longer time (e.g. tables)
Less efficient	Very efficient

                                                       

Dataguard Modes

In some situations, a business cannot afford to lose data. In other situations, the availability of the database may be more important than the loss of data. Some applications require maximum database performance and can tolerate some small amount of data loss. The following descriptions summarize the three distinct modes of data protection.

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 each transaction must be written to both the local online redo log and to the standby redo log on at least one standby database before the transaction commits. To ensure data loss cannot occur, the primary database shuts down if a fault prevents it from writing its redo stream to the standby redo log of at least one transactionally consistent standby database.

Maximum availability This protection mode provides the highest level of data protection that is possible without compromising the availability of the primary database. Like maximum protection mode, a transaction will not commit until the redo needed to recover that transaction is written to the local online redo log and to the standby redo log of at least one transactionally consistent standby database. Unlike maximum protection mode, the primary database does not shut down if a fault prevents it from writing its redo stream to a remote standby redo log. Instead, the primary database operates in maximum performance mode until the fault is corrected, and all gaps in redo log files are resolved. When all gaps are resolved, the primary database automatically resumes operating in maximum availability mode. This mode ensures that no data loss will occur if the primary database fails, but only if a second fault does not prevent a complete set of redo data from being sent from the primary database to at least one standby database.

Maximum performance This protection mode (the default) provides the highest level of data protection that is possible without affecting the performance of the primary database. This is accomplished by allowing a transaction to commit as soon as the redo data needed to recover that transaction is written to the local online redo log. The primary database's redo data stream is also written to at least one standby database, but that redo stream is written asynchronously with respect to the transactions that create the redo data.  When network links with sufficient bandwidth are used, this mode provides a level of data protection that approaches that of maximum availability mode with minimal impact on primary database performance. The maximum protection and maximum availability modes require that standby redo log files are configured on at least one standby database in the configuration.

 All three protection modes require that specific log transport attributes be specified on the LOG_ARCHIVE_DEST_n initialization parameter to send redo data to at least one standby database

Alert log

The alert log file (also referred to as the ALERT.LOG) is a chronological log of messages and errors written out by an Oracle Database. Typical messages found in this file is: database startup, shutdown, log switches, space errors, etc. This file should constantly be monitored to detect unexpected messages and corruptions.

Oracle will write the alert.log file to the directory as specified by the BACKGROUND_DUMP_DEST parameter.

In Oracle 10g

SQL> show parameter BACKGROUND_DUMP_DEST

NAME                         TYPE            VALUE

------------------------- -----------   -------------------------------

background_dump_dest         string    /u01/user10/demo/bdump/alert.log

SQL> show parameter BACKGROUND_DUMP_DEST

In Oracle 11g

SQL> show parameter BACKGROUND_DUMP_DEST

NAME                        TYPE        VALUE

------------------------- ----------- ------------------------------

background_dump_dest        string      /app/oracle/diag/rdbms/o11gr1/

                                        trace

Multiple Temporary Tablespaces: Using Tablespace Groups

You can create a temporary tablespace group that can be specifically assigned to users in the same way that a single temporary tablespace is assigned. A tablespace group can also be specified as the default temporary tablespace for the database.


A tablespace group has the following characteristics:

1. It contains at least one tablespace. There is no explicit limit on the maximum number of tablespaces that are contained in a group.

2. It shares the namespace of tablespaces, so its name cannot be the same as any tablespace.

3. You can specify a tablespace group name wherever a tablespace name would appear when you assign a default temporary tablespace for the database or a temporary tablespace for a user.


How to create temporary tablespace group

Note: You do not explicitly create a tablespace group. Rather, it is created implicitly when you assign the first temporary tablespace to the group. The group is deleted when the last temporary tablespace it contains is removed from it.

SQL> ALTER TABLESPACE temp TABLESPACE GROUP group1;

Tablespace altered.

SQL> CREATE TEMPORARY TABLESPACE temp1
2 TEMPFILE 'c:\oracle\product\10.1.0\oradata\temp02.dbf' size 5m
3 TABLESPACE GROUP group2;

Tablespace created.

SQL> desc dba_tablespace_groups;
Name Null? Type
----------------------------------------- -------- ----------------------------

GROUP_NAME NOT NULL VARCHAR2(30)
TABLESPACE_NAME NOT NULL VARCHAR2(30)

SQL> select * from dba_tablespace_groups;

GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
GROUP1 TEMP
GROUP2 TEMP1


How to change TABLESPACE GROUP


SQL> select * from dba_tablespace_groups;

GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
GROUP1 TEMP
GROUP2 TEMP1

SQL> ALTER TABLESPACE temp1 TABLESPACE GROUP group1;

Tablespace altered.

SQL> select * from dba_tablespace_groups;

GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
GROUP1 TEMP
GROUP1 TEMP1


How to assign TABLESPACE GROUP to particular user.


SQL> select temporary_tablespace
2 from dba_users
3 where username = 'SCOTT';

TEMPORARY_TABLESPACE
------------------------------
GROUP1

SQL> alter user scott temporary tablespace group2;


How to delete temporary tablespace groups


SQL> select * from dba_tablespace_groups;

GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
GROUP1 TEMP
GROUP2 TEMP1

SQL> alter tablespace temp1 tablespace group '';

Tablespace altered.

SQL> select * from dba_tablespace_groups;

GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
GROUP1 TEMP


How to Assigning a Tablespace Group as the Default Temporary Tablespace


SQL> select * from dba_tablespace_groups;

GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
GROUP1 TEMP
GROUP1 TEMP1

SQL> alter database ORCL default temporary tablespace group1;

Database altered.


Using a tablespace group, rather than a single temporary tablespace, can alleviate problems caused where one tablespace is inadequate to hold the results of a sort, particularly on a table that has many partitions. A tablespace group enables parallel execution servers in a single parallel operation to use multiple temporary tablespaces.

Interview Qustions for 2+ Year experienced Candidates

 Hii , Today I will Give you some Questions that you may face in DBA Interview :

How do you find the number of rows in a Table ?

A bad answer is : count them (SELECT COUNT(*) FROM table_name)

 A good answer is : 'By generating SQL to ANALYZE TABLE table_name COUNT STATISTICS by querying Oracle System Catalogues (e.g. USER_TABLES or ALL_TABLES).

The best answer is : to refer to the utility which Oracle released which makes it unnecessary to do ANALYZE TABLE for each Table individually.

Why do you set kernel parameters?

Ans: Oracle recommends that you set shared memory segment attributes as well as semaphores to the following values.

If not set, database instance creation will fail. I added the following lines to /etc/sysctl.conf file.

Every OS process needs  semaphore where It waits on for the resources.

For more on semaphore, please read the UNIX os documents.

How you Identify the Corruption (rman)

An alternative method to identify block corruption in an Oracle data file is to use the RMAN validate function. The following example is taken from Windows:

RMAN> connect target sys/****

connected to target database: TAXPROD (DBID=3492187718)

RMAN> BACKUP VALIDATE CHECK LOGICAL DATABASE FILESPERSET=10;

Once the validate completes, the V$DATABASE_BLOCK_CORRUPTION can be used to check for corruption:

Q. Common Listener Issues You Faced ?

It is not very common to find listener-related problems in Oracle, and most connectivity-related problems are generally associated with database server problems, such as shortage of memory.

Here are some common errors:

    ORA-12500 TNS:listener failed to start a dedicated server process  - This is generally associated with memory shortages or permission problems in UNIX.

    ORA-3113 end-of-file on communication channel – This is generally a network failure

    TNS-12547 TNS:lost contact – This can be corrected by increasing the connect_timeout_listener parameter.

    TNS-12224 TNS:no listener – Clients will get this message if the listener process is not running.

    ORA-12570 TNS:packet reader failure – This is a network problem.

    ORA-12571 TNS:packet writer failure – This is a network problem

Note that in many cases, a failure of the listener will not be logged on the database server, but will instead be presented as a message on the client workstation.  Hence, the vast majority of listener errors are reported by end-users.

Q. How we see that our listener log is located in ?? 

 

 cat `lsnrctl stat|grep Log|awk '{print $4 }'`|grep ORA-

/ora7/home/dba/product/7.3.4/network/log/listener.log.