Category: Database

Posted on

Query – Tablespace Max Size

I sometimes see concerned emails from developers that they are running out of space in tablespace and the DBA need to do something immediately. The DBA will start wondering we have monitoring in place, then how come it did not catch an out of space situation, but a user/developer did…

For you developer/user, if you are wondering if your database/tablespace is running out of space, you may need to check if the tablespace has autoextensible data files…

Here is a simple query, that could be used to find the current size of the tablespace and how much it can grow using the autoextensible feature of data file.

  SELECT tablespace_name,
         ROUND (SUM (bytes) / 1048576) curr_size,
         ROUND (SUM (GREATEST (bytes, DECODE (autoextensible, ‘YES’, maxbytes, 0)))/ 1048576)  growable
    FROM dba_data_files
GROUP BY tablespace_name
ORDER BY tablespace_name;

Posted on

Rename Diskgroup in ASM

Rename ASM Diskgroup [11gR2]
In 11gR2 it is possible to rename an ASM diskgroup. This is especially useful when performing database copy using OS level LUN mirroring technologies.
The renamedg command is used to rename diskgroups. It has the following options:
$ renamedg -help
Parsing parameters..
phase                           Phase to execute,
                                (phase=ONE|TWO|BOTH), default BOTH
dgname                          Diskgroup to be renamed
newdgname                       New name for the diskgroup
config                          intermediate config file
check                           just check-do not perform actual operation,
                                (check=TRUE/FALSE), default FALSE
confirm                         confirm before committing changes to disks,
                                (confirm=TRUE/FALSE), default FALSE
clean                           ignore errors,
                                (clean=TRUE/FALSE), default TRUE
asm_diskstring                  ASM Diskstring (asm_diskstring=’discoverystring’,
                                ‘discoverystring1’ …)
verbose                         verbose execution,
                                (verbose=TRUE|FALSE), default FALSE
keep_voting_files               Voting file attribute,
                                (keep_voting_files=TRUE|FALSE), default FALSE
To rename a diskgroup, it must be stopped [or unmounted].
$ srvctl stop diskgroup -g oops1
$ renamedg dgname=oops1 newdgname=good1 verbose=true
$ srvctl start diskgroup –g good1
You must manually rename all database files on the diskgroup to reflect the new name using ALTER DATABSE RENAME FILE command.
Even after successful rename command, old diskgroup resources in Oracle Restart must be manually removed using the srvctl command.