Tablespace Quota's Missing..

Recently we did migration on one of the database from HP-UX to Linux. Post migration we have provided unlimited quotas on few of its tablespace.

Later some time it came in our compliance tool that user is having UNLIMITED TABLESPACE privilege granted.

So this privilege was revoked and later few days we observed that tablespace quota's were missing.

I've generated a scenario which explains this.

##########################
## Error
##########################

Tablespace Quota's Missing

##########################
## Command Executed
##########################

Revoke unlimited tablespace from XDB;

**************************************** Step By Step Analysis ******************************************************

#########################################
# Reproduce the Issue
#########################################

a) Grant unlimited quota on specific tablespace.

Alter user XDB quota unlimited on USER_DATA_TS01;

SQL> Alter user XDB quota unlimited on USER_DATA_TS01;
User altered.

b) Grant unlimited tablespace privilege to the user.

Grant unlimited tablespace to xdb;

SQL> Grant unlimited tablespace to xdb;
Grant succeeded.
SQL> 

=====================================================================================================================

#########################################
# Check the Quota
#########################################

SQL> select username,tablespace_name name,decode(greatest(max_bytes, -1),-1,'Unrestricted',
to_char(max_bytes/1024, '999,999,990')) quota, bytes/1024 used from dba_ts_quotas where username in ('XDB') order by 1,2;
  2
USERNAME                       NAME                           QUOTA                           USED
------------------------------ ------------------------------ ------------------------- ----------
XDB                            USER_DATA_TS01                 Unrestricted                   57728

=====================================================================================================================

#########################################
# Scenario 1)
#########################################

Grant Unlimited Quota on Tablespace and Revoke UNLIMITED TABLESPACE privilege.

SQL> alter user XDB quota unlimited on USER_DATA_TS01;
User altered.
SQL> revoke unlimited tablespace from xdb;
Revoke succeeded.

#### Check Quota

SQL> select username,tablespace_name name,decode(greatest(max_bytes, -1),-1,'Unrestricted',
to_char(max_bytes/1024, '999,999,990')) quota, bytes/1024 used from dba_ts_quotas where username in ('XDB') order by 1,2;
  2
no rows selected
SQL> 

#### From this we can understand that revoking unlimited tablespace privilege removes unlimited tablespace quota on any/all tablespace to that user.

=====================================================================================================================

#########################################
# Scenario 2)
#########################################

Grant Specific(1gB) Quota on Tablespace and REVOKE UNLIMITED TABLESPACE privilege.

SQL>  Alter user  XDB quota 1g on USER_DATA_TS01;
User altered.
SQL> select username,tablespace_name name,decode(greatest(max_bytes, -1),-1,'Unrestricted',
to_char(max_bytes/1024, '999,999,990')) quota, bytes/1024 used from dba_ts_quotas where username in ('XDB') order by 1,2;
  2
USERNAME                       NAME                           QUOTA                           USED
------------------------------ ------------------------------ ------------------------- ----------
XDB                            USER_DATA_TS01                    1,048,576                   57728
SQL> revoke unlimited tablespace from xdb;
Revoke succeeded.

#### Check Quota

SQL> select username,tablespace_name name,decode(greatest(max_bytes, -1),-1,'Unrestricted',
to_char(max_bytes/1024, '999,999,990')) quota, bytes/1024 used from dba_ts_quotas where username in ('XDB') order by 1,2;
  2
no rows selected
SQL> 

#### From this we can understand that revoking unlimited tablespace privilege removes all quota's on all tablespace granted to that user.

=====================================================================================================================
So the bottom line is before revoking unlimited tablespace privilege from the user, make a note of the quota's that user has and then once revoked grant the tablespace quota's back.
=====================================================================================================================

Move datafile from File system to ASM (or) Move datafile from NON-ASM to ASM

One of our application team has deployed new version of application, and we gave privilege to create tabelspace for their deployment.

As usual application team created the datafile in File System but our database is using ASM. So We need to Move datafile from File system to ASM.

##########################

#  Task 
##########################

Move Datafile From File system to ASM Diskgroup.

##########################
#  Pre checks 
##########################

1. File Name and Tablespace Name where Datafiles are Created.
2. Check Whether Diskgroup is having Sufficient Space
3. Intimate Application team that data's will not be available during this maintenance.

##########################

## Solution
##########################

File Name and Tablespace Name where Datafiles are Created.

set lines 200
col file_name for a60
col "size MB" for 9,99,999
col tablespace_name for a20

Select tablespace_name,file_id,file_name,ceil(bytes/1024/1024/1024) "Size in GB",autoextensible,status,ceil(maxbytes/1024/1024/1024) "MAx in GB" from dba_data_files where file_id=69;

TABLESPACE_NAME         FILE_ID FILE_NAME                                                    Size in GB AUT STATUS     MAx in GB
-------------------- ---------- ------------------------------------------------------------ ---------- --- --------- ----------
TBS_NEW                   69 /u01/app/oracle/product/11.2/db_5/dbs/TBS_NEW.DBF          1 YES AVAILABLE         32

Select tablespace_name,file_id,file_name,ceil(bytes/1024/1024/1024) "Size in GB",autoextensible,status,ceil(maxbytes/1024/1024/1024) "MAx in GB" from dba_data_files where tablespace_name='TBS_NEW';

TABLESPACE_NAME         FILE_ID FILE_NAME                                                    Size in GB AUT STATUS     MAx in GB
-------------------- ---------- ------------------------------------------------------------ ---------- --- --------- ----------
TBS_NEW                   69 /u01/app/oracle/product/11.2/db_5/dbs/TBS_NEW.DBF          1 YES AVAILABLE         32

Check Whether Diskgroup is having Sufficient Space

Select name,total_mb,free_mb from v$asm_diskgroup where state='CONNECTED';

NAME                             TOTAL_MB    FREE_MB

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

ORALIN_DATA                      2294348     495983

ORALIN_RECO                       184335     161864

Now Place the Tablespace OFFLINE.

SQL> alter tablespace TBS_NEW offline;

Tablespace altered.

Check Tablespace Status  

SQL> Select tablespace_name,status from dba_tablespaces where tablespace_name='TBS_NEW';

TABLESPACE_NAME      STATUS
-------------------- ---------
TBS_NEW           OFFLINE


Copy the Datafile from File System to ASM using RMAN command.

Copy Datafile 69 to '+ORALIN_DATA';

ORALIN_1 @ host01:/u01/app/oracle/diag/rdbms/ORALIN/ORALIN_1/trace

> rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Mon Feb 4 00:03:30 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORALIN (DBID=4269551271)

RMAN> copy datafile 69 to '+ORALIN_DATA';

Starting backup at 04-FEB-13

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=4848 instance=ORALIN_1 device type=DISK

channel ORA_DISK_1: starting datafile copy

input datafile file number=00069 name=/u01/app/oracle/product/11.2/db_5/dbs/TBS_NEW.DBF

output file name=+ORALIN_DATA/ORALIN/datafile/TBS_NEW.352.806457841 tag=TAG20130204T000400 RECID=68 STAMP=806457842

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03

Finished backup at 04-FEB-13

Starting Control File and SPFILE Autobackup at 04-FEB-13

piece handle=+ORALIN_RECO/ORALIN_gvl/autobackup/2013_02_04/s_806457844.487.806457845 comment=NONE

Finished Control File and SPFILE Autobackup at 04-FEB-13

RMAN>

Now Switch the Datafile to Update this new location in controlfile.

switch datafile 69 to copy;

RMAN> switch datafile 69 to copy;
datafile 69 switched to datafile copy "+ORALIN_DATA/ORALIN_gvl/datafile/TBS_NEW.352.806457841"

Place Tablespace ONLINE.

SQL> alter tablespace TBS_NEW online;

Tablespace altered.

Check Tablespace Status  & Datafile Location :

SQL>  Select tablespace_name,status from dba_tablespaces where tablespace_name='TBS_NEW';

TABLESPACE_NAME      STATUS

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

TBS_NEW           ONLINE

SQL> Select file_id,file_name,status from dba_data_files where tablespace_name='TBS_NEW';

   FILE_ID FILE_NAME                                                    STATUS
---------- ------------------------------------------------------------ ---------
        69 +ORALIN_DATA/ORALIN_gvl/datafile/TBS_NEW.352.806457841  AVAILABLE