Solved: Re: sce cm reporting issue

Martin Kyrc · ‎11-07-2011

hello experts,

can anybody tell me, how can I solve this issue on CM? Our CM is receiving RDRs, but SCE reporting shows only data till 2 weeks ago. server has lot ot free space, SCE devices are connected to CM (ad they are sending rdrs).

$ ./cm version

CM CD Version 3.5.5 Build 182

$ ./cm dbversion

Sybase ase1502

$ ./dbfree.sh

Name % Data Free % Log Free

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

Database 0 99

$ crontab -l

15 3 * * * /opt/scmscm/db_maint/periodic_del.sh --name='daily real-time' --active=true --daystokeep=90 'RPT_LUR,RPT_MALUR,RPT_MEDIA,RPT_PUR,RPT_SUR,RPT_TOPS_PERIOD0,RPT_TOPS_PERIOD1,RPT_VLUR,RPT_GUR,RPT_TOPS_PERIOD0_CUMULATIVE,RPT_TOPS_PERIOD1_CUMULATIVE' 2>&1 | logger -p daemon.notice

45 3 * * * /opt/scmscm/db_maint/periodic_del.sh --name='daily rpt_tr' --active=true --daystokeep=30 'RPT_TR' 2>&1 | logger -p daemon.notice

-bash-3.00$ ~scmscm/db_maint/create_periodic_del_procs.sh

Old periodic procedure for table RPT_GUR has been dropped.

Periodic procedure for table RPT_GUR has been created.

The following problem occured during DB operation:

Can't allocate space for object 'sysprocedures' in database 'apricot' because 'system' segment is full/has no free extents. If you ran out of space in syslogs, dump the transaction log. Otherwise, use ALTER DATABASE to increase the size of the segment.

--

martin

usujlana · ‎11-07-2011

did you try this :

./dbtables.sh -fa

find the table that is biggest then do :

- stop the CM
- drop the table (eg. ./droptable.sh --f RPT_LUR )
- start the CM
- wait about two minutes for the table to be created
- verify that the table was created, by running dbtables.sh -af (confirm that RPT_TR & RPT_LUR is there)

- begin procedure to install periodic delete.

Note you might loose data so backup you DB.

View solution in original post

usujlana · ‎11-07-2011

did you try this :

./dbtables.sh -fa

find the table that is biggest then do :

- stop the CM
- drop the table (eg. ./droptable.sh --f RPT_LUR )
- start the CM
- wait about two minutes for the table to be created
- verify that the table was created, by running dbtables.sh -af (confirm that RPT_TR & RPT_LUR is there)

- begin procedure to install periodic delete.

Note you might loose data so backup you DB.

Martin Kyrc · ‎11-08-2011

Hi,

I tried this (the biggest table is RPT_TR):

-bash-3.00$ ./dbtables.sh -f | grep RPT_TR

RPT_TR| 114948927| |

-bash-3.00$

-bash-3.00$ ./prunetable.sh 30 RPT_TR

Are you sure you want to prune table RPT_TR ? (yes/no): yes

Periodic delete not created. Use user guide to create periodic delete procedures.

-bash-3.00$

^^ I tried this with -f flag, the same result

-bash-3.00$ ./dbtables.sh -f | grep RPT_TR

RPT_TR| 114948927| |

however, there are data from 6th of september

When I drop table RPT_TR, it is created when I start CM again (or restart bundled sybase db)?

--

martin

usujlana · ‎11-08-2011

so did you ever drop that table ? once you delete the table and restart CM the table should be created. But as i mentioned before you need to keep a back up copy of your DB for later.

Martin Kyrc · ‎11-08-2011

thanks for solution. after dropping table (biggest: RPT_TR) and starting CM reporting is working again.

periodic delete is configured (as I wrote before: crontab -l). maybe parameters (daystokeep) is a bit higher (30 days for RPT_TR and 90 days for all other) and that is problem point - db is filled before some datas are deleted.

what are optimal parameters for periodic delete? maximum report history is based on this parameter (daystokeep) or is it possible get report for 90days ago with daystokeep set to 15?

--

martin

davidfarjecalderon · ‎01-13-2012

I'm getting the same error message but I'm attempting to switch from sybase to mysql.

the tables get created in MySQL

mysql> show tables;

+-----------------------------+

| Tables_in_apricot |

+-----------------------------+

| CONF_SE_TZ_OFFSET |

| IMEI_DEVICETYPE |

| RPT_DVLINK |

| RPT_FUR |

| RPT_GUR |

| RPT_LUR |

| RPT_MALUR |

| RPT_MEDIA |

| RPT_PUR |

| RPT_SPAM |

| RPT_SUR |

| RPT_TOPS_PEAK_CUMULATIVE |

| RPT_TOPS_PEAK_PERIOD |

| RPT_TOPS_PERIOD0 |

| RPT_TOPS_PERIOD0_CUMULATIVE |

| RPT_TOPS_PERIOD1 |

| RPT_TOPS_PERIOD1_CUMULATIVE |

| RPT_TOP_APN |

| RPT_TOP_DEVICE_TYPE |

| RPT_TOP_HTTP_DOMAINS |

| RPT_TOP_HTTP_HOSTS |

| RPT_TOP_NETWORK_TYPE |

| RPT_TOP_SGSN |

| RPT_TOP_USER_LOCATION |

| RPT_TOP_VIDEO_DOMAINS |

| RPT_TOP_VIDEO_HOSTS |

| RPT_TR |

| RPT_UVLINK |

| RPT_VLUR |

| RPT_ZUR |

+-----------------------------+

30 rows in set (0.00 sec)

mysql>

and there is in fact data in the mysql database

mysql> select * from CONF_SE_TZ_OFFSET;

+---------------------+------------+

| TIME_STAMP | OFFSET_MIN |

+---------------------+------------+

| 2012-01-13 16:30:35 | -300 |

+---------------------+------------+

1 row in set (0.00 sec)

mysql>

diegomarra · ‎07-12-2012

Thanks!!!!!! Excelent post

muchasiarhey · ‎09-13-2012

My problem repeat every day after 23:15 o'clock. SCA BB don't show graphics (Global Bandwidth per Service) and other

(user scmscm)

Test: 01db_up.sh. Status: PASS. Message: db is up

Test: 02cm_up.sh. Status: PASS. Message: CM is running

test 03free_db.sh output was invalid, cannot parse status/message

Test: 03free_db.sh. STATUS: Unknown. Message: Unknown state returned

test 04free_log.sh output was invalid, cannot parse status/message

Test: 04free_log.sh. STATUS: Unknown. Message: Unknown state returned

Test: 05cm_persistent_buffers.sh. Status: FAIL. Message: The following directory/ies have more than 500 files in them - JDBCAdapter

(from root)

Test: 01db_up.sh. Status: FAIL. Message: DB is not running

Test: 02cm_up.sh. Status: PASS. Message: CM is running

test 03free_db.sh output was invalid, cannot parse status/message

Test: 03free_db.sh. STATUS: Unknown. Message: Unknown state returned

test 04free_log.sh output was invalid, cannot parse status/message

Test: 04free_log.sh. STATUS: Unknown. Message: Unknown state returned

Test: 05cm_persistent_buffers.sh. Status: FAIL. Message: The following directory/ies have more than 500 files in them - JDBCAdapter

I do this -

(user scmscm)

/home/scmscm/cm/bin/cm restart --- solve my problem but restart cm every day bad idea

after cm restart

Test: 01db_up.sh. Status: PASS. Message: db is up

Test: 02cm_up.sh. Status: PASS. Message: CM is running

test 03free_db.sh output was invalid, cannot parse status/message

Test: 03free_db.sh. STATUS: Unknown. Message: Unknown state returned

test 04free_log.sh output was invalid, cannot parse status/message

Test: 04free_log.sh. STATUS: Unknown. Message: Unknown state returned

Test: 05cm_persistent_buffers.sh. Status: PASS. Message: All Adapter persistent buffers are in Normal state

I have low experience with CM

vi /home/scmscm/cm/logs/cm.log

.................many message like that...

2012-09-13 09:53:41,491 [Thread-9] WARN com.cisco.scmscm.buffer.PersistentBuffer - Persistent Buffer: deleting before read: /home/scmscm/cm/adapters/JDBCAdapter/74.rdr

2012-09-13 09:53:42,544 [Thread-9] WARN com.cisco.scmscm.buffer.PersistentBuffer - Persistent Buffer: deleting before read: /home/scmscm/cm/adapters/JDBCAdapter/75.rdr