Is there orphan data on VC

If any of these return null then you have orphan data and need to implement KB 2058670

select sample_time as mim_sample1 from vpx_sample_time1 where time_id = (select min(time_id) from vpx_sample_time1);
select sample_time as max_sample1 from vpx_sample_time1 where time_id = (select max(time_id) from vpx_sample_time1);
select sample_time as min_hist1 from vpx_sample_time1 where time_id = (select min(time_id) from vpx_hist_stat1);
select sample_time as max_hist1 from vpx_sample_time1 where time_id = (select max(time_id) from vpx_hist_stat1);

select sample_time as min_sample2 from vpx_sample_time2 where time_id = (select min(time_id) from vpx_sample_time2);
select sample_time as max_sample2 from vpx_sample_time2 where time_id = (select max(time_id) from vpx_sample_time2);
select sample_time as min_hist2 from vpx_sample_time2 where time_id = (select min(time_id) from vpx_hist_stat2);
select sample_time as max_hist2 from vpx_sample_time2 where time_id = (select max(time_id) from vpx_hist_stat2);

select sample_time as min_sample3 from vpx_sample_time3 where time_id = (select min(time_id) from vpx_sample_time3);
select sample_time as max_sample3 from vpx_sample_time3 where time_id = (select max(time_id) from vpx_sample_time3);
select sample_time as min_hist3 from vpx_sample_time3 where time_id = (select min(time_id) from vpx_hist_stat3);
select sample_time as max_hist3 from vpx_sample_time3 where time_id = (select max(time_id) from vpx_hist_stat3);

reduce size of VC database

backup and shutdown VC first

For SQL server

SELECT
t . NAME AS TableName ,
s . Name AS SchemaName ,
p . rows AS RowCounts ,
SUM( a . total_pages ) * 8 AS TotalSpaceKB ,
SUM( a . used_pages ) * 8 AS UsedSpaceKB ,
(SUM ( a . total_pages ) – SUM ( a . used_pages )) * 8 AS UnusedSpaceKB
FROM
sys. tables t
INNER JOIN
sys. indexes i ON t . OBJECT_ID = i . object_id
INNER JOIN
sys. partitions p ON i . object_id = p . OBJECT_ID AND i . index_id = p . index_id
INNER JOIN
sys. allocation_units a ON p . partition_id = a . container_id
LEFT OUTER JOIN
sys. schemas s ON t . schema_id = s . schema_id
WHERE
t . NAME NOT LIKE ‘dt%’
AND t . is_ms_shipped = 0
AND i . OBJECT_ID > 255
GROUP BY
t . Name , s . Name , p . Rows
ORDER BY TotalSpaceKB DESC;

exec sp_spaceused

What’s taking up the majority of the data in vpx_field_val

SELECT count(SUBSTRING(mo_id, 1, CHARINDEX(‘-‘,mo_id) – 1)) , SUBSTRING(mo_id, 1, CHARINDEX(‘-‘,mo_id) – 1)
from vpx_field_val
group by SUBSTRING(mo_id, 1, CHARINDEX(‘-‘,mo_id) – 1)
order by SUBSTRING(mo_id, 1, CHARINDEX(‘-‘,mo_id) – 1) desc;

its’ suspected that the majority of this data is tasks if so remove not running tasks

If they’re tasks – the script below shows the ones which aren’t valid.

delete
from vpx_field_val
where mo_id like ‘task%’
and SUBSTRING(mo_id, CHARINDEX(‘-‘, mo_id) + 1, LEN(mo_id)) not in (
select task_id
from vpx_task
where complete_state not in (‘success’, ‘error’)
or complete_time >= (getdate() -1) );

if they’re vm’s

select field_id, mo_id
from vpx_field_val
where mo_id like ‘vm%’
and SUBSTRING(mo_id, CHARINDEX(‘-‘, mo_id) + 1, LEN(mo_id)) not in (
select id
from vpx_vm
);

delete
from vpx_field_val
where mo_id like ‘vm%’
and SUBSTRING(mo_id, CHARINDEX(‘-‘, mo_id) + 1, LEN(mo_id)) not in (
select id
from vpx_vm
);

SELECT * into VPX_INT_ARRAY_BK FROM VPX_INT_ARRAY
WHERE TYPE_ID = 42 AND EXISTS(SELECT 1 FROM VPX_VM_FLE_SNAP_DISK_UNIT WHERE ARRAY_ID=VPX_INT_ARRAY.ARRAY_ID);

truncate table VPX_INT_ARRAY;

INSERT INTO VPX_INT_ARRAY ([ARRAY_ID] ,[TYPE_ID] ,[VALUE] ,[ARRAY_INDEX] ,[MO_TYPE] ,[MO_ID])
SELECT [ARRAY_ID] ,[TYPE_ID] ,[VALUE] ,[ARRAY_INDEX] ,[MO_TYPE] ,[MO_ID] FROM [VPX_INT_ARRAY_BK];

DROP TABLE VPX_INT_ARRAY_BK;

SELECT * into VPX_TEXT_ARRAY_BK FROM VPX_TEXT_ARRAY
WHERE  EXISTS(SELECT 1 FROM VPX_ENTITY WHERE ID=VPX_TEXT_ARRAY.MO_ID);

truncate table VPX_TEXT_ARRAY;

SET IDENTITY_INSERT VPX_TEXT_ARRAY ON;

insert into VPX_TEXT_ARRAY (ARRAY_ID,[TYPE_ID],VALUE,MO_TYPE,MO_ID,SURR_KEY)
select ARRAY_ID,[TYPE_ID],VALUE,MO_TYPE,MO_ID,SURR_KEY from  VPX_TEXT_ARRAY_BK;

SET IDENTITY_INSERT VPX_TEXT_ARRAY OFF  ;

drop table VPX_TEXT_ARRAY_BK;

truncate table vpx_property_bulletin;

EXEC sp_MSforeachtable @command1=”print ‘?’ DBCC DBREINDEX (‘?’, ‘ ‘, 80)”

Postgres Corruption

Symptoms

Application logs showing

double register of key xxx
ERROR: invalid page in block x of relation pg_tblspc/x/x/x/x
ERROR: missing chunk number * for toast value * in pg_toast_*
checksum mismatch: disk has 0, should be xxx
DETAIL  could not open file “$psql/pg_clog/####”: No such file or directory
cp: cannot stat `pg_xlog/
Error “MultiXactId has not been created yet — apparent wraparound”

Continue reading “Postgres Corruption”

All things Database and VMware

Yvonne Murphy. Oracle Certified Master. VMware Certified Professional,  Member of the Office of the Chief Technical Ambassador VMware.

 

My role in VMware is to support customers running any Oracle product in a VM. But in all honestly Oracle do a great job of this so that doesn’t keep me busy.

There’s a lot of opportunities for a DBA to make herself useful in GSS though. I’m like a ninga. I go into those black boxes and I do some tricks in the database. Most of the blogs here are those scripts I use.

Do remember a bunny dies everytime you make a chance on one of these databases. so backup first!