If you compare tablespace sizes with following two queries:
SQL> select * from dba_tablespace_usage_metrics
and
SQL> select x.tablespace_name,
sum(x.bytes/1024/1024) "Total Space (Mb)" ,
NVL((sum(x.bytes/1024/1024)-round(y.free,2)), sum(x.bytes/1024/1024)) "Used (Mb)",
NVL(round(y.free,2), 0.00) "Free (Mb)",
NVL(round(y.free/sum(x.bytes/1024/1024)*100,2), 0) "Free %"
from dba_data_files x,
(select tablespace_name,sum(bytes/1024/1024) free
from dba_free_space
group by tablespace_name) y
where x.tablespace_name = y.tablespace_name (+)
group by x.tablespace_name,y.free;
They are looking very different especially if the tablespaces are using autoextent.
This is because :
Tablespace_size in
DBA_TABLESPACE_USAGE_METRICS takes the maximum file size for
autoextensible tablespace which corresponds to maxblocks in
dba_data_files.
So basically DBA_TABLESPACE_USAGE_METRICS shows the maximum size to which a datafile can grow. And DBA_DATA_FILES (+ DBA_FREE_SPACE) current size of tablespace.
Look more from MOS (My Oracle Support) document:
This blog contains useful and interesting issues/problems about different sql and nosql databases. Mainly Oracle.
Wednesday, March 26, 2014
Monday, March 24, 2014
Oracle Grid Control Agent autostart in RHEL / CentOS
When you install Grid Control Agent in server then the installer also create /etc/init.d/gcstartup script in the server. This init script wont automatically start Agent in server boot. But you can change it to do so. Do following as a root user.
1. Add following in the start of the script:
#!/bin/sh
# chkconfig: 35 10 90
# description: oracle grid control agent stop/start script on system up/down
#
# Use chkconfig command to start this script in appropriate run level.
# --------------------------------------------------------------------
#Source Function Library
2. And run following commands:
chkconfig --add gcstartup
chkconfig --levels 2345 gcstartup on
chkconfig --list gcstartup
This will work at least with Grid Control 11g agents and RHEL / Centos.
1. Add following in the start of the script:
#!/bin/sh
# chkconfig: 35 10 90
# description: oracle grid control agent stop/start script on system up/down
#
# Use chkconfig command to start this script in appropriate run level.
# --------------------------------------------------------------------
#Source Function Library
2. And run following commands:
chkconfig --add gcstartup
chkconfig --levels 2345 gcstartup on
chkconfig --list gcstartup
This will work at least with Grid Control 11g agents and RHEL / Centos.
Tuesday, March 18, 2014
Oracle next extent check for tablespace fails and recyclebin (Grid Control)
If you using maintenance jobs to check does next extent fit in the tablespace and you get errors that they does not.
That means of course that your tablespace (or datafile) is so full that next extent would not fit into it.
"Quite like error ora-01653 unable to extend table in tablespace."
Sometimes this kind of errors can be confusing if you look tablespace usage from DBA_FREE_SPACE (or DBA_EXTENTS) . And this is because DBA_FREE_SPACE does not show dba_recyclebin usage. So if you remove database objects (for example table) from database but does not "purge" it then it will go into recyclebin and still takes same amount of space from same tablespace. This recyclebin used space can be taken into use if space is needed for new data. But it cannot be seen in DBA_FREE_SPACE as used space because it is reclaimable. But you can see this used space if you check for example tablespace (or datafile) usage from DBA_SEGMENTS.
Same thing is with the Grid Control (at least version 11G). If you check tablespaces usage it shows that there is free space to use in tablespace (this does not contain recyclebin space usage) but if you look the datafile space usage it will show real data space usage + recyclebin space usage.
NOTE! You wont get ORA-01653 errors in this kind of situations because Oracle can start to use space used by recyclebin when needed. But is is good to remember that you might get different space usage info if you wont purge dropped objects.
That means of course that your tablespace (or datafile) is so full that next extent would not fit into it.
"Quite like error ora-01653 unable to extend table in tablespace."
Sometimes this kind of errors can be confusing if you look tablespace usage from DBA_FREE_SPACE (or DBA_EXTENTS) . And this is because DBA_FREE_SPACE does not show dba_recyclebin usage. So if you remove database objects (for example table) from database but does not "purge" it then it will go into recyclebin and still takes same amount of space from same tablespace. This recyclebin used space can be taken into use if space is needed for new data. But it cannot be seen in DBA_FREE_SPACE as used space because it is reclaimable. But you can see this used space if you check for example tablespace (or datafile) usage from DBA_SEGMENTS.
Same thing is with the Grid Control (at least version 11G). If you check tablespaces usage it shows that there is free space to use in tablespace (this does not contain recyclebin space usage) but if you look the datafile space usage it will show real data space usage + recyclebin space usage.
NOTE! You wont get ORA-01653 errors in this kind of situations because Oracle can start to use space used by recyclebin when needed. But is is good to remember that you might get different space usage info if you wont purge dropped objects.
Friday, March 14, 2014
MySQL MyISAM table repair.
MySQL MyISAM tables might break down from time to time.
You might get following errors in database error log:
--
[ERROR] mysqld: Incorrect key file
for table '<TABLE_INDEX_FILE_NAME_AND_PATH>.MYI'; try to repair it
[ERROR] mysqld: Table '<TABLE_NAME_AND_PATH>' is marked as crashed and should be repaired--
If you get only error number from application then you can use perror utility from command prompt to get more info about error number (error numbers related to MyISAM tables usually are:
126 127 132 134 135 136 141 144 145) :
mysql/bin/perror 144
MySQL error code 144: Table is crashed and last repair failed
Check and fix MyISAM tables:
With myisamchk utility you can try to check and fix these tables/index files. Before run myisamchk commands you need to stop MySQL server and go into database directory (or specify directory in the command). I prefer myisamchk because it garentees that no one is not trying to use tables when doing the repair (MySQL server need to be stopped when running it).
To Check table errors:
To check all tables (to check only one table specify that table name):
myisamchk *.MYI
To do slower and more extend-check check run:
myisamchk -e *.MYI
You can also check tables from SQL (when server is running):
CHECK TABLE <TABLE_NAME> <OPTIONS>;
CHECK TABLE <TABLE_NAME> QUICK;
To Fixing Tables or Index files:
Repairing tables might sometimes lead to lost of data so it is good practice to take backup of files or database before running these if it is possible.
Fix only those tables that are broken ( -q makes quick repair that repairs only index files):
mysql/bin/myisamchk -r -q <TABLE_NAME>
With big tables (and index files) and to get more performance for fixing use following options (you need to tune these values suitable for your environment) with myisamchk (to see all myisamchk options you can use myisamchk --help ):
mysql/bin/myisamchk --sort_buffer_size=2G \
--key_buffer_size=2G \
--read_buffer_size=512M \
--write_buffer_size=512M \
--tmpdir=/data/myisam_repair \
--recover --quick \
<TABLE_NAME>
You can also try to fix tables from SQL (when server is running) :
REPAIR TABLE <TABLE_NAME> <OPTIONS> ;
REPAIR TABLE <TABLE_NAME> QUICK;
NOTE: If some reason myisamchk did not fix your table/index files or you want to get more detailed guide and info about MyISAM table repair check these links:
http://dev.mysql.com/doc/refman/5.7/en/myisam-repair.html
http://dev.mysql.com/doc/refman/5.7/en/check-table.html
http://dev.mysql.com/doc/refman/5.1/en/repair-table.html
You might get following errors in database error log:
--
[ERROR] mysqld: Incorrect key file
for table '<TABLE_INDEX_FILE_NAME_AND_PATH>.MYI'; try to repair it
[ERROR] mysqld: Table '<TABLE_NAME_AND_PATH>' is marked as crashed and should be repaired--
If you get only error number from application then you can use perror utility from command prompt to get more info about error number (error numbers related to MyISAM tables usually are:
126 127 132 134 135 136 141 144 145) :
mysql/bin/perror 144
MySQL error code 144: Table is crashed and last repair failed
Check and fix MyISAM tables:
With myisamchk utility you can try to check and fix these tables/index files. Before run myisamchk commands you need to stop MySQL server and go into database directory (or specify directory in the command). I prefer myisamchk because it garentees that no one is not trying to use tables when doing the repair (MySQL server need to be stopped when running it).
To Check table errors:
To check all tables (to check only one table specify that table name):
myisamchk *.MYI
To do slower and more extend-check check run:
myisamchk -e *.MYI
You can also check tables from SQL (when server is running):
CHECK TABLE <TABLE_NAME> <OPTIONS>;
CHECK TABLE <TABLE_NAME> QUICK;
To Fixing Tables or Index files:
Repairing tables might sometimes lead to lost of data so it is good practice to take backup of files or database before running these if it is possible.
Fix only those tables that are broken ( -q makes quick repair that repairs only index files):
mysql/bin/myisamchk -r -q <TABLE_NAME>
With big tables (and index files) and to get more performance for fixing use following options (you need to tune these values suitable for your environment) with myisamchk (to see all myisamchk options you can use myisamchk --help ):
mysql/bin/myisamchk --sort_buffer_size=2G \
--key_buffer_size=2G \
--read_buffer_size=512M \
--write_buffer_size=512M \
--tmpdir=/data/myisam_repair \
--recover --quick \
<TABLE_NAME>
You can also try to fix tables from SQL (when server is running) :
REPAIR TABLE <TABLE_NAME> <OPTIONS> ;
REPAIR TABLE <TABLE_NAME> QUICK;
NOTE: If some reason myisamchk did not fix your table/index files or you want to get more detailed guide and info about MyISAM table repair check these links:
http://dev.mysql.com/doc/refman/5.7/en/myisam-repair.html
http://dev.mysql.com/doc/refman/5.7/en/check-table.html
http://dev.mysql.com/doc/refman/5.1/en/repair-table.html
Subscribe to:
Posts (Atom)