Why should we perform consistent export?

As per TomKyte....

it makes all of the queries run by exp be "as of the same point in time-- consistent with
regards to eachother"

imagine if you started an export at 9am of the EMP and DEPT tables.

EMP started exporting at 9am and DEPT at 9:15am.

Now, the EMP data would be "as of 9am", but the DEPT data would be as of 9:15am.  What if
you import that data now -- will it work?  maybe, maybe not -- perhaps at 9:10am, someone
fired the last employee in department 50 and deleted department 50.  Your EMP export
would have them in this deptno, your DEPT export would not have this deptno.  The import
would fail.

If you use consistent=y, Oracle will export all of the data "as of 9am", so deptno=50
will be included in all tables and the import will succeed.

RMAN BACKUP VALIDATION AND RECOVERY FROM BLOCK CORRUPTION:

The main purpose of RMAN validation is to check for corrupt blocks and missing files

Corruption in block:

Block corruption is while the data is being written to the data blocks, if the write to the block fails abruptly, I mean that there is a partial write in the block, may be because of power disruption or I/O problem, leaving no time for header to be updated, or row data to be populated, oracle leaves the block corrupt.In case of block corruption you can normally use the database unless you try to read that particular block, against which it shoots up the block corruption error.Generally block corruption occurs if write fails on the block, when the transaction is being committed

Physical(media corrupt) and Logical(software corrupt) Block Corruption:

In a physical corruption, which is also called a media corruption, the database does not recognize the block at all: the checksum is invalid.

Checksum:

A number calculated by the database from all the bytes stored in a data or redo block. If the DB_BLOCK_CHECKSUM initialization parameter is enabled, then the database calculates the checksum for every datafile or online redo log block and stores it in the block header when writing to disk. The database can use the checksum value to check consistency.

In a logical corruption, the contents of the block are logically inconsistent.

The logical corruption happens within the blocks , for eg. some index entry pointing towards a null rowid.

Validating Database Files with BACKUP VALIDATE:

You can use the BACKUP VALIDATE command to do the following:

-Check datafiles for physical and logical block corruption.

-Confirm that all database files exist and are in the correct locations.

Validating only physical corruption:

BACKUP VALIDATE DATABASE ARCHIVELOG ALL;

Validating both physical and logical corruption:

BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL;

Validating Backups Before Restoring Them:

You can run RESTORE ... VALIDATE to test whether RMAN can restore a specific file or set of files from a backup.

RESTORE DATABASE VALIDATE;

RESTORE ARCHIVELOG ALL VALIDATE;

RECOVRING A BLOCK CORRUPTION:

If it finds corrupted blocks(after checking with BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL;) it will place the information about the corruption into a view:

v$database_block_corruption

SQL>select * from v$database_block_corruption;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO

———- ———- ———- —————— ———

5 81 4 0 CORRUPT

this is what we find in the alert_.log:

Corrupt block relative dba: 0x014000b1 (file 5, block 177)

Bad header found during backing up datafile

Data in bad block:

type: 67 format: 7 rdba: 0x0a545055

last change scn: 0×0000.0007bc77 seq: 0×3 flg: 0×04

spare1: 0×52 spare2: 0×52 spare3: 0×0

consistency value in tail: 0xbc772003

check value in block header: 0xb32

computed block checksum: 0xe4c1

Reread of blocknum=177, file=/u01/app/oracle/oradata/orcl/

example01.dbf.

found same corrupt data 

Now we can tell RMAN to recover all the blocks which it has found as being corrupt:

RMAN> blockrecover corruption list;

# (all blocks from v$database_block_corruption)

Starting blockrecover at 05-04-2006:10:09:15

using channel ORA_DISK_1

channel ORA_DISK_1: restoring block(s) from datafile copy /u01/app/

oracle/flash_recovery_area/ORCL/datafile/o1_mf_example_236tmb1c_.dbf

starting media recovery

archive log thread 1 sequence 2 is already on disk as file /u01/app/oracle/

flash_recovery_area/ORCL/archivelog/2006_04_05/o1_mf_1_2_236wxbsp_.arc

archive log thread 1 sequence 1 is already on disk as file

/u01/app/oracle/oradata/

orcl/redo01.log

media recovery complete, elapsed time: 00:00:01

Finished blockrecover at 05-04-2006:10:09:24

Also read:


Segment checking using DBV


DBMS_REPAIR




Enjoy:-)

Difference between WITH GRANT and WITH ADMIN

Sometimes people are puzzled to find out the difference between WITH GRANT and WITH ADMIN.

Here is the simple explanation:

WITH GRANT OPTION:

We can grant object privileges only with grant option like (select,update,insert)

 A--------->B--------->C

If A want to revoke privileges from C, it is not possible. The user who granted the privilege can only revoke. we have to revoke the privilege from B ,that automatically revoke the privileges from C.

WITH ADMIN OPTION:

We can give the system privileges onlywith admin option(CREATE TABLE,CREATE INDEX,CREATE SESSION)

 A------>B-------->C

In admin option,it is possible to revoke the privileges from both B and C by A.Here suppose if we revoke the privileges from B means it didn’t revoke the privileges of C.


Examples:

WITH ADMIN OPTION  When a user is granted a system privilege, the grantor (i.e., the person granting the privilege, typically the DBA) also has the option to allow the grantee (the person receiving the privilege, typically the user) to grant the same privilege to other users. If this is the result desired, the grantor can grant the privilege using the WITH ADMIN OPTION. When privileges are granted WITH ADMIN OPTION,this means that the grantor has decided that the grantee can be fully trusted by him as well as by the user that granted him the system privilege in the first place. In essence 
all users holding a system privilege


Security Hole:  WITH ADMIN OPTION are considered equal and can grant and revoke that privilege from anyone, including the person who granted it to them in the first place.



For example, if you give the key to your car to a friend and tell him that it is alright to make copies of the key, when you ask for the key back from your friend, you cannot, at the same time, get back all copies that were made by him and given to others. In order to get the other copies of the key back, you need to find out who has them. Similarly, in Oracle you need to query the data dictionary to determine which other users were granted the permission being revoked by the user from which it is being revoked.



WITH GRANT OPTION on object privileges allows a user granted 
the privilege to grant it to someone else. The reason for doing this is to minimize the administrative burden of granting object privileges.if an object privilege isrevoked from a user to whom it was granted WITH GRANT OPTION, that privilege would also be removed from anyone that user granted the privilege to. For example,if Damir granted John the SELECT privilege on the DAMIR.JOHN1 table WITH GRANT OPTION, and John then granted the SELECT privilege to Tim, then if Damir issued the command REVOKE SELECT ON DAMIR.JOHN1 FROM JOHN, Tim would also no longer have the privilege. This is because when object privileges are revoked,the revoke also cascades to anyone that the privilege was granted to by the user from whom it is being revoked.



Enjoy:-)

Manually create database in Oracle 10g Linux

Hello,

It is very exciting to create database manually on Linux platform.

Play this link

Enjoy:-) 

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:-)