Monitor number of now changed for mview base table

One programer has interested on number of rows changed for a mview base table, thus he suggested to query x$knstmvr table , which may be found in http://avdeo.com/tag/materialized-view/.  As such, I have concern on accessing to the SYS x$ view.

Initially, I thought to use DBA_TAB_MODIFICATIONS, but it requires to run dbms_stats.flush_database_monitoring_info and gather table statistics of rest the counter, and time co-related to mview refresh.


 For performance reasons, the Oracle Database does not populate this view immediately when the actual modifications occur. Run the FLUSH_DATABASE_MONITORING_INFO procedure in the DIMS_STATS PL/SQL package to populate this view with the latest information. 


Confirmed from https://docs.oracle.com/cd/E26401_01/doc.122/e48791/T578797T589127.htm#T578863, that count on MLOG$_ is an easier way to achieve the purpose. 

MLOG$ Table

An MLOG$ table is the Snapshot Log table created against a base table. A row is inserted into the table each time a change (insert/update/delete) is made to the base table.

When a snapshot is refreshed in fast (incremental) mode, it reads the rows in the MLOG$ table and uses this information to get the updated information from the base table into the snapshot for further processing.

Standard RDBMS functionality manages rows inserted into the MLOG tables. When a snapshot is refreshed in either fast or complete mode, then the RDBMS will delete rows from the MLOG table. However, if multiple snapshots reference the same MLOG, then all the snapshots must be refreshed before the RDBMS can remove any rows from the MLOG table.

The RDBMS creates snapshot logs on the base table and automatically names them using MLOG$_[20 characters of the table name]. For example:

MRP_FORECAST_DATES (18 char) creates MLOG$_MRP_FORECAST_DATES BOM_DEPARTMENT_RESOURCES (24 char) creates MLOG$_BOM_DEPARTMENT_RESOU

In case of a tie, the RDBMS adds a number to the MLOG name. For example:

WSM_COPY_OP_RESOURCES creates MLOG$_WSM_COPY_OP_RESOURCE WSM_COPY_OP_RESOURCE_INSTANCES creates MLOG$_WSM_COPY_OP_RESOURCE1

SQL> select count(*) from mlog$_bonus; 
COUNT(*)
----------0

from http://www.dba-oracle.com/t_mlog_dollar_tables.htm is a real case.

Moreover, questions to programer:
What is the purpose of getting this number?
What actions should be taken if the amount exceeds certain number? What number should be set as threshold?

Thanks for the challenge.

How to check if a table statistics is locked

LIQY@ORCL1> select table_name from user_tables;

TABLE_NAME
------------------------------
T1

LIQY@ORCL1> select STATTYPE_LOCKED from user_tab_statistics;

STATT
-----


LIQY@ORCL1> exec dbms_stats.lock_table_stats('LIQY','T1');

PL/SQL procedure successfully completed.

LIQY@ORCL1> select STATTYPE_LOCKED from user_tab_statistics;

STATT
-----
ALL

LIQY@ORCL1> exec dbms_stats.UNlock_table_stats('LIQY','T1');

PL/SQL procedure successfully completed.

LIQY@ORCL1> select STATTYPE_LOCKED from user_tab_statistics;

STATT
-----

Akeeba backup for Joomla 1.5

Helping to maintain a site using Joomla 1.5. Recently the hosting provider's server was hacked again, cause the site not working for few days.  Pouring oil on the fire by the provider is its poor technical support.
Luckily the site has Akeeba backup installed and takes snapshot on demand, and I found a backup copy of Aug. 

As the content of this site is quite static, I decided to roll back to Aug by using Akeeba kickstart script.

After reading the quick guide, I renamed the not-working www directory to www_bad, and uploaded below files to in the new www root directory, except for

• the existing configuration.php
• kickstart.php 
• en-GB.kickstart.ini as English installation language
• August's Akeeba backup file

With above and type my domain.com/kickstart.php for the restoration to production site. In few steps and minutes, I got the site working again.

However, pictures of phocagallery were not found.  Compared the directory , and found the image/phocagallery were not backed up in the Akeeba backup file. ( Akeeba backup version is v3.4.3).  Lucily, I didn't delete the old www directory, and it works again after copy the directory  from www_bad/image/phocagallery.


The last step is to rebuild thumnail of phocagallery, as it showed blank in phocagallery, but once clicked, the picture is still shown properly.

Cheers and Thanks Akeeba backup & Kickstart.

Say bye-bye to the hosting provider soon.

auto clean up in-doubt distributed transaction

[How the problem comes]

Mirror database and DB link technology are used intensively in my environment.
Mirror database is created by using storage level technology daily at mid-night specific time.

In the source database, transactions may not be still running (yet commit yet), however the hard storage split has to happen, which also transparent to operator.

As such, although the mirror database is open to application team, but below error can be encountered when accessing the interested table.

ERROR at line 1:
ORA-01591: lock held by in-doubt distributed transaction

[Solution]

To solve the issue, we come out a script as shown below, to perform the clean up after database recovery. 

 cat check_dba_2pc_pending_trans.sql

--set echo on

set serveroutput on

set lines 150

col HOST format a20

col OS_USER format a20

col spoolname new_value spoolname

select '/oracle/node1/ops/logs/check_dba_2pc_pending_'||to_char(sysdate, 'yymmddhh24mmss')||(select '_'||instance_name from v$instance) spoolname from dual;

spool '&spoolname'

select LOCAL_TRAN_ID,STATE,FAIL_TIME,OS_USER,HOST,DB_USER,COMMIT#,ADVICE from dba_2pc_pending;

ALTER SYSTEM DISABLE DISTRIBUTED RECOVERY;

declare

   cursor_name     pls_integer default dbms_sql.open_cursor;

   ignore          pls_integer;

--   x  varchar2(22);

   procedure execute_immediate( p_sql in varchar2 )

   is

   BEGIN

      dbms_sql.parse(cursor_name, p_sql, dbms_sql.native);

      ignore := dbms_sql.execute(cursor_name);

      --dbms_sql.close_cursor(cursor_name);

   END;

begin

   for x in ( select local_tran_id,state from dba_2pc_pending ) loop

      dbms_output.put_line( 'Local Trans Id:  ' || x.local_tran_id );

      if x.state = 'committed' then

        dbms_transaction.purge_lost_db_entry(x.local_tran_id);

        commit;

      else

        execute_immediate( 'rollback force ''' || x.local_tran_id || '''' );

        commit;

        dbms_transaction.purge_lost_db_entry(x.local_tran_id);

        commit;

      end if;

   end loop;

   dbms_sql.close_cursor(cursor_name);

end;

/

ALTER SYSTEM ENABLE DISTRIBUTED RECOVERY;

select LOCAL_TRAN_ID,STATE,FAIL_TIME,OS_USER,HOST,DB_USER,COMMIT#,ADVICE from dba_2pc_pending;

spool off

Displaying Oracle 10046 Trace File in SQL developer

Good know that SQL Developer can display 10046 Trace file in nice view,  as an alternative to using the TKPROF program.
To open a .trc file in SQL Developer, click File/Open, and choose the desired trace file.

Once opened, we can examine the information in the Tree View,  Statistics View, and List View.

Below is my sample screenshots.

BTW, it seems List View requires large Graphic card memory.

Tips for TortoiseSVN

Many discussion on internet, think it is a SVN client. In fact, it is also a SVN server.

As I practice, I found SNV over WebDV has view only capability, even I tried websvn which can't help on check-in and check-out. I decided to use SVN protocol only, i.e. use TortoiseSVN serves as both server (deploy it on server) and client GUI (install it too in various PCs).

To clear authentication info, go to settings/saved data/clear authentication data, as shown below.

The key config files of SVN server are under the repository directory by default.e.g.

authorization file:  authz
password file: passwd
main config file:  svnserve.conf


First funny problem is when I enable the password file and hit authentication failed.

This is due to a space before the userid.   Remove it, do so for all other type of configuration just in case.

The 2nd Funny problem with authorization failed, whenever I enable authz-db in the server config file, I even can't read the repository , which was functioning.Finally I got the solution from someone else on the web.

I use the "/" for repository instead of particular repository name. e.g. change it from "/dbasvn" to "/" in authz-db file. e.g.

Changed it from 
[/dbasvn]
liqy = rw
* = r

TO

[/]
liqy = rw
* = r

Now , everything works perfectly.


Run svnserve as a Microsoft Windows service, so that we can use SVN protocol.  below is my sample command.

sc create svnserver binpath= "\"C:\Program Files\Subversion\bin\svnserve.exe\" --service -r D:\svnrepos" displayname= "Subversion" depend= Tcpip start= auto

Note if we want to delete a window service, can use "sc delete " , followed by reboot. 

Above is based TortoiseSVN v1.9.2 

SQL server 2000 profiler

One system is getting slower and slower, so I decided to use profiler for investigation.

Googled and read out the following useful articles:


http://serverfault.com/questions/162245/how-do-i-identify-slow-queries-in-sql-server
http://www.codeproject.com/Articles/21371/SQL-Server-Profiler-Step-by-Step

to minimize changes to production, I select using tracing file instead of a table.

confirmed with below two articles for finding out indexes columns.

https://bytes.com/topic/sql-server/answers/562317-how-do-i-find-indexes-columns-sql-server-2000-a

http://stackoverflow.com/questions/765867/list-of-all-index-index-columns-in-sql-server-db



Finally, I got a huge return -- the system has ZERO indexes created.