Who are you? Development DBA or Production DBA.

Development Oracle DBAs:

Generally speaking, Oracle Development DBAs are concerned with developing applications from a database perspective. Here are some of their tasks:

• ·         Know the source code very well, and the database structure
• ·         Look mainly after one or a set of applications
• ·         Involved in SQL tuning, and re-writing of code
• ·         Development of new functionality
• ·         Liaise with developers regularly in development issues

Production Oracle DBAs

On the whole, the Oracle Production DBA is all about maintaining a reliable, secure and performant database. Here are some common tasks:

• ·         Patching
• ·         Storage and capacity planning
• ·         Backup and recovery
• ·         Rollout of new releases
• ·         Performance tuning of instance as a whole
• ·         Troubleshooting expert

It’s also essential to bear in mind that there will certainly be overlap in any job you go to; just because you don’t work on production doesn’t mean you shouldn’t know how to patch or backup and recover a database. These are just an idea of the main functions of the different types of roles and what you can expect to spend most of your time doing.

It is very commong for you to be both the Development and Production DBA. I would say that at much larger organisations where there are many Oracle DBAs you are more likely to get the distinction between Development and Production DBAs. In smaller companies, you are more likely to be working both as the Development and Production DBA.

Enjoy:-)

Wishing you all a very Happy New year 2012

Secrets about SGA sizing

Hello everyone,

Please read the SGA sizing secrets by Oracle expert Tom:

Enjoy:-)

Checking redo's applied on standby or not

Here is the OTN discussion link that helps us to recognize the problem and solution.

https://forums.oracle.com/forums/thread.jspa?threadID=2303669&tstart=0

Enjoy:-)

Very good summary of DELETE TRUNCATE and DROP commands

Hello everyone,

Today i came across a very good explanation of  DELETE TRUNCATE and DROP commands.

Check here: -

Enjoy:-)

What is LOGGING,NOLOGGING and FORCE LOGGING?

Oracle gives us the ability to limit redo generation on tables and indexes by setting them in NOLOGGING mode.

NOLOGGING affect the recoverability. Before going into how to limit the redo generation, it is important to clear the misunderstanding that NOLOGGING is the way out of redo generation, there are some points regarding it:

NOLOGGING is designed to handle bulk inserts of data which can be easy reproduced.

Regardless of LOGGING status, writing to undo blocks causes generation of redo.

LOGGING should not be disabled on a primary database if it has one or more standby databases. For this reason oracle introduced the ALTER DATABASE FORCE LOGGING command in Oracle 9i R2. (Means that the NOLOGGING attribute will not have any effect on the segments) If the database is in FORCE LOGGING MODE. NOLOGGING can be also override at tablespace level using ALTER TABLESPACE … FORCE LOGGING.

FORCE LOGGING can be used on tablespace or database level to force logging of changes to the redo. This may be required for sites that are mining log data, using Oracle Streams or using Data Guard (standby databases).

Enjoy:-)

How To Check Whether Physical Standby is in Sync with the Primary or Not?

The following tips will help you in monitoring sync between primary and standby databases.

1. Check for GAP on standby

2. Check redo received on standby

3. Check redo applied on standby

Solution:

Execute following queries:

A. On Primary

SQL> SELECT THREAD# "Thread",SEQUENCE# "Last Sequence Generated"

FROM V$ARCHIVED_LOG

WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)

ORDER BY 1;

Thread     Last Sequence Generated

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

1          19

2          13

3          11

B. On Physical Standby

SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"

FROM

(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,

(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL

WHERE

ARCH.THREAD# = APPL.THREAD#

ORDER BY 1;

Thread     Last Sequence Received Last Sequence Applied Difference

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

1          19                     19                    0

2          13                     13                    0

3          11                     11                    0

C. On Physical Standby

SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

no rows selected

Now perform following checks:

1. Check for GAP

If query “C” returns any row then this means there are some archive log missing on standby.

Example:

SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

Thread     Low Sequence High Sequence

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

1          8            9

This example shows sequence 8 and 9 from thread 1 are missing on standby, Hence standby is not in sync with the primary.

If query “C” does not returns any row and output is “no row selected” than this means there is no archive gap on standby.

2. Check for redo received on standby

Compare value of “Last Sequence Generated” in query “A” with “Last Sequence Received” in query “B” for all threads.

If both values are same than this means that standby has received the last sequence generated on primary.

If both values are not same then there are some archives missing on standby, Hence standby is not in sync with the primary.

enjoy:-)