Tuesday, October 26, 2010

ORA-01536: SPACE quota exceeded for tablespace

ORA-01536: space quota exceeded for tablespace is a common error in Oracle Database.
It means that users is trying to use more space than the Database Administrator assigned to him. Quota is a maximum number of bytes that a user can allocate in particular tablespace. Implementing quotas in database is a reasonable approach because it prevents buggy or malicious code to fill the tablespace.


It is worth to mentioned that prior to Oracle 11g quotas in Oracle could be assigned to both permanent and temporary tablespaces. Starting from Oracle 11g quotas can only be set on permanent tablespaces.
Database Administrators should remember that after they setup quotas for users they are responsible for constantly monitoring and extending quotes if required.


Below example presents how we can generate ORA-01535 in Oracle database, and how we can resolve it.

We start from creating a user with default tablespace set to USERS. Then we grant create session and create table privileges, to enable the user to connect to database and create a table. We also grant initially 1 MB quota on tablspace users.





SQL> conn / as sysdba

Connected.

SQL> set linesize 160

SQL> create user user1 default tablespace users quota 1M on users temporary tablespace temp identified by pass1

2 /



User created.



SQL> grant create session, create table to user1

2 /



Grant succeeded




Then we try to create a table using CTAS (create table as select) based on ALL_OBJECTS view. ALL_OBJECTS view
contains a list of ALL_OBJECTS in database.


SQL> conn user1/pass1

Connected.

SQL> set linesize 160

SQL> create table t1 as select * from all_objects

2 /

create table t1 as select * from all_objects

*

ERROR at line 1:

ORA-01536: space quota exceeded for tablespace 'USERS'



The error occurred while we tried to create the table, due to the fact that table t1 would require around 7MB of space and user1 quote was set to just 1MB.

SQL> conn / as sysdba

Connected.

SQL> select tablespace_name, username, bytes / 1024 / 1024 "Used MB", max_bytes / 1024 / 1024 as "Max MB"

2 from dba_ts_quotas

3 where tablespace_name = 'USERS' and username = 'USER1'

4 /



TABLESPACE_NAME USERNAME Used MB Max MB
USERS USER1 0 1





To resolve the problem we enlarge user1 quota to 10MB.

SQL> alter user user1 quota 10m on users

2 /



User altered.



SQL> conn user1/pass1

Connected.

SQL> create table t1 as select * from all_objects

2 /



Table created.

As we can see the table was created successfully. So the last thing to do is to check the quota usage.

SQL> conn / as sysdba

Connected.

SQL> select tablespace_name, username, bytes / 1024 / 1024 "Used MB", max_bytes / 1024 / 1024 as "Max MB"

2 from dba_ts_quotas

3 where tablespace_name = 'USERS' and username = 'USER1'

4 /



TABLESPACE_NAME USERNAME Used MB Max MB
USERS USER1 7 10



It is worth to remember that instead of granting quota in megabytes, we can grant UNLIMITED QUOTA on tablespace for
a user. In my opinion it sometimes be a security whole, but on other ocasions it can be useful.

So let's start from dropping the table t1.

SQL> conn user1/pass1

Connected.

SQL> drop table t1

2 /



Table dropped.



SQL> conn / as sysdba

Connected.

SQL> select tablespace_name, username, bytes / 1024 / 1024 "Used MB", max_bytes / 1024 / 1024 as "Max MB"

2 from dba_ts_quotas

3 where tablespace_name = 'USERS' and username = 'USER1'

4 /



TABLESPACE_NAME USERNAME Used MB Max MB
USERS USER1 7 10



SQL> show parameter recyclebin



NAME TYPE VALUE

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

recyclebin string on



After table drop you see that space used by the user has not been change.
You probably wondering why, the reson is simple, I used the statement DROP TABLE tableName; instead of DROP TABLE tableName PURGE; and I have RECYCLE_BIN ON. Below query allows be to confirm that my table is in RECYCLEBIN.

SQL> set linesize 160

SQL> column owner format a15

SQL> column original_name format a20

SQL> column can_purge format a10

SQL> column type format a10

SQL> select owner, object_name, original_name, operation, type, can_purge

2 from dba_recyclebin

3 where owner = 'USER1' and type = 'TABLE'

4 /



OWNER OBJECT_NAME ORIGINAL_NAME OPERATION TYPE CAN_PURGE
USER1 BIN$2JQWhCy4RIiMi4/epuYlSQ==$0 T1 DROP TABLE YES



To purge the table from recyclebin we will login as user1 and execute PURGE TABLE statement.

SQL> conn user1/pass1

Connected.



SQL>select object_name, original_name, operation, type, can_purge

1 from user_recyclebin
2 where type = 'TABLE'

3 /



OBJECT_NAME ORIGINAL_NAME OPERATION TYPE CAN_PURGE
BIN$2JQWhCy4RIiMi4/epuYlSQ==$0 T1 DROP TABLE YES

SQL> purge table "BIN$2JQWhCy4RIiMi4/epuYlSQ==$0"

2 /



Table purged.

Confirmation of successful purge operation.

SQL> conn / as sysdba

Connected.

SQL> select tablespace_name, username, bytes / 1024 / 1024 "Used MB", max_bytes / 1024 / 1024 as "Max MB"

2 from dba_ts_quotas

3 where tablespace_name = 'USERS' and username = 'USER1'

4 /



TABLESPACE_NAME USERNAME Used MB Max MB
USERS USER1 0 10

Please note that above behavior with RECYCLEBIN will only work is instance parameter recyclebin is set to on, and if you are working on Oracle 10g or later. The above experiment was performed on Oracle 11g.

We can now replay our initial test and check that UNLIMITED QUOTA WORKS.

SQL> conn / as sysdba

Connected.

SQL> select tablespace_name, username, bytes / 1024 / 1024 "Used MB", max_bytes / 1024 / 1024 as "Max MB"

2 from dba_ts_quotas

3 where tablespace_name = 'USERS' and username = 'USER1'

4 /




TABLESPACE_NAME USERNAME Used MB Max MB
USERS USER1 0 10

SQL> alter user user1 quota 1M on users

2 /



User altered.



SQL> conn user1/pass1

Connected.

SQL> create table t1 as select * from all_objects

2 /

create table t1 as select * from all_objects

*

ERROR at line 1:

ORA-01536: space quota exceeded for tablespace 'USERS'



SQL> conn / as sysdba

Connected.

SQL> alter user user1 quota unlimited on users

2 /



User altered.

Unlimited Quota is denoted by -1 in MAX_BYTES column of DBA_TS_QUOTAS view.


SQL> select tablespace_name, username, bytes / 1024 / 1024 "Used MB",
2 decode(max_bytes,-1,'UNLIMITED',max_bytes / 1024 / 1024) as "Max MB"

3 from dba_ts_quotas

4 where tablespace_name = 'USERS' and username = 'USER1'

5 /



TABLESPACE_NAME USERNAME Used MB Max MB
USERS USER1 0 UNLIMITED

Finally we can run our CREATE TABLE statement.

SQL> conn user1/pass1

Connected.

SQL> create table t1 as select * from all_objects

2 /



Table created.

Thursday, October 14, 2010

Why excessive redo logs and archive logs are getting generated when we start a hot backup of database ?

This question was asked always in all interview datafile get updated with online transaction during hot backup or not , if not than why ?


Most of people always failed to convenience interviewer with valid reason. The below comments will help out to understand the complete flow of data during hot backup.



Many of you must have heard or experienced that while taking hot backup of database LGWR process writes aggressively. Meaning that more redo data has been written to redo log file and consecutively more archive logs gets generated.



Here is the common misconception we have in our mind. If some one ask, why excessive redo logs and archive logs are getting generated when we start a hot backup of database ??????????

Quickly we answer .. Its simple, when we put tablespace in hot backup mode,
Oracle will take a check point of tablespace and data files belonging to this tablespace will be freezed.

Any user activity happening on objects belonging to this tablespace wont write data to these datafiles, instead it will write data to redo log files. So obviously there will be more redo log file generation.


Well, to some extent this is COMPLETELY WRONG !!!!!!!!!!!!!!!!!!


I will straight way come to the point and explain you what happens when we put the tablespace in hot backup mode.


When a tablespace is put into backup mode, the following three things happen:

1. Oracle checkpoints the tablespace, flushing all changes from shared memory to disk.


2. The SCN markers for each datafile in that tablespace are "frozen" at their current values. Even though further updates will be sent to the datafiles, the SCN markers will not be updated until the tablespace is taken out of backup mode.


3. Oracle switches to logging full images of changed database blocks to the redologs. Instead of recording how it changed a particular block (the change vector), it will log the entire image of the block after the change. This is why the redologs grow at a much faster rate while hot backups are going on.



Your first assumption that datafiles belonging to the tablespace in hot backup mode is freezed is wrong.

Datafiles are not freezed, only the datafile headers will be freezed !!!!!!!

So simply imagine that when you put the tablespace in backup mode, Oracle will take a checkpoint and update the datafile headers with checkpoint SCN and there after it is freezed until we take tablespace out of backup mode.

Other datafile (other then header part) remains as normal and data changes happens continuously to this datafile.

Now you may want to ask me “do I mean to say that datafiles gets updated continuously even when we are coping the same to backup location ?”. The answer is YES. Never think that the datafile you are coping is “Consistent”. No, datafiles gets changed continuously !!!!!!!!!!!!!!



You might want to ask couple of more questions then.

1) If we say that backup file is not consistent and changes continuously, then how come Oracle is able to recover the database when we restore that datafile?

2) If the data changes are anyway happening continuously on data files, then why there is excess redo log generation ?


Thats it !! don’t ask me more then this. Let me explain answers to these questions.



Consider a typical case, where an Oracle database is installed on Linux platform. The standard Oracle block size if 8K and lets say that OS level data block size is 512K.

Now when we put the tablespace in “Begin Backup” mode checkpoint has happened and datafile header is freezed.

You found which are the files related to this tablespace and started copying using OS command. Now when you copy a datafile using OS command it is going to copy as per OS block size.

Lets say when you start copying it gave 8 blocks to you to copy – that means you are copying 4K (512K X 4) to backup location.

That means you are copying half of Oracle block to backup location. Now this process of copy can be preempted by Server CPU depending on load. Lets say when you started copying after copy of those 8 block (4K, half of Oracle block), your process get preempted by CPU and it has allocated CPU time to some other important process.

Mean while DBWR process changes that block that you have copied halfway (since datafile is not freezed and only header is freezed, continuous updates can happen to datafile).


After a while CPU returns back and gives you next 8 blocks to copy (rest of the halk Oracle block). Now here is the problem !!!!!!!!!!!

we copied half of the oracle block taken at time T0 and another half taken at time T1 and in-between the data block got changed.

Does this sounds consistent ? Not to me !!!!!!!!!!!!!!!!!!!!!!!!!

Such type of block is called “Fractured Block”.

Well, since Oracle copies files like this it should do some thing, so that during recovery it wont face any problem.



Usually in case of a normal tablespace (which is not in begin backup mode), when a transaction happens oracle generates redo information and puts in redo log file.

This is the bare minimum information that oracle generates in order to redo the information. It does not copy the complete block.

Where as in case of begin backup mode, if a transaction happens and changes any block FOR THE FIST TIME, oracle copies the complete block to redo log file. This happens only during first time.

If subsequent transaction updates the same block again, oracle will not copy the complete block to redo, instead it will generate minimum information to redo the changes. Now because oracle has to copy the complete block when it changes for the first time in begin backup mode, we say that excess redo gets generated when we put tablespace in begin backup mode.


Question arises, why Oracle has to copy the complete block to redo log files. As you have seen above that during copy of datafile, there can be many fractured blocks, and during restore and recovery its going to put those block back and try to recover.

Now assume that block is fractured and oracle has minimum information that it generates in the redo. Under such condition it wont be possible for Oracle to recover such blocks. So instead Oracle just copies the entire block back from redo log files to datafiles during recovery process. This will make the datafile consistent.

So recovery process is very important which takes care of all fractured blocks and makes it possible to recover a database.

I hope this explains above 2 questions.


Now you can easily explain why hot backup is not possible if database is in NOARCHIVELOG mode.


When you take a backup using RMAN, it does not generate excessive redo logs. The reason is simple. RMAN is intelligent. It does not use OS block for copying, instead it uses oracle blocks for copying datafiles so the files are consistent.


Hope this helps !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Wednesday, October 13, 2010

Virtual IP vip in Oracle RAC in Place of Public IP

Question : Why VIP is used in RAC in place of Public ip(9i rac db).


This question was asked by many RAC DBA expert and i was always failed to convince them for valid reason.


But I am sure now after read the below comments you will be in positions to convince them with valid reason .....


How new connection establish in Oracle RAC?

For failover configuration we should need to configure our physical ip of host name in listener configuration. Listener process is accepting new connection request and handover user process to server process or dispatcher process in Oracle.

Means using listener new connection is being established by Oracle. Once connection get established there is no need of listener process. If new connection is trying to get session in database and listener is down then what will be happening.

User process gets error message and connection fails. Because listener is down in same host or something else problem. But in Oracle RAC database environment database is in sharing mode. Oracle RAC database is shared by all connected nodes. Means more than 1 listeners are running in various nodes.

===>> How it work in Oracle 9I RAC DB ==>>

In Oracle RAC database if user process is trying to get connection with some listener and found listener is down or node is down then Oracle RAC automatically transfer this request to another listener on another node.

Up to Oracle 9i we use physical IP address in listener configuration. Means if requested connection gets failed then it will be diverting to another node using physical IP address of another surviving node. But during this automatically transfer, connection should need to wait up to get error message of node down or listener down using TCP/IP connection timeout.

Means session should need to wait up to getting TCP/IP timeout error dictation. Once error message is received oracle RAC automatically divert this new connection request to another surviving node.


Using physical IP address there is biggest gap to get TCP/IP timeout for failover suggestion. Session should need to wait for same timeout. High availability of Oracle RAC depends on this time wasting error message.




===>>> Why VIP (Virtual IP) needs in Oracle RAC? ===>


From Oracle 10g, virtual IP considers to configure listener. Using virtual IP we can save our TCP/IP timeout problem because Oracle notification service maintains communication between each nodes and listeners.

Once ONS found any listener down or node down, it will notify another nodes and listeners with same situation. While new connection is trying to establish connection to failure node or listener, virtual IP of failure node automatically divert to surviving node and session will be establishing in another surviving node.

This process doesn't wait for TCP/IP timeout event. Due to this new connection gets faster session establishment to another surviving nodes/listener.


====>>> Characteristic of Virtual IP in Oracle RAC: <====


Virtual IP (VIP) is for fast connection establishment in failover dictation. Still we can use physical IP address in Oracle 10g in listener if we have no worry for failover timing. We can change default TCP/IP timeout using operating system utilities or commands and kept smaller.

But taking advantage of VIP (Virtual IP address) in Oracle 10g RAC database is advisable. There is utility also provided to configure virtual IP (vip) with RAC environment called VIPCA. Default path is $ORA_CRS_HOME/bin. During installation of Oracle RAC, it is executed.



==>>>>>>> Advantage of Virtual IP deployment in Oracle RAC: <<===

Using VIP configuration, client can be able to get connection fast even fail over of connection request to node. Because vip automatically assign to another surviving node faster and it canĂ¢€™t wait for TNS timeout old fashion.



===>>>>>>>Disadvantage of Virtual IP deploymenin Oracle RAC: <<==


Some more configurations is needed in system for assign virtual IP address to nodes like in /etc/hosts and others. Some misunderstanding or confusion may occur due to multiple IP assigns in same node.



===>><<<== Important for VIP configuration: <<<====


The VIPs should be registered in the DNS. The VIP addresses must be on the same subnet as the public host network addresses. Each Virtual IP (VIP) configured requires an unused and resolvable IP address.