Clusterware out of sync

I had strange inconsistency in Clusterware 12c.

crsctl stat res -t -w "NAME = ora.dbname.db"

return information that db is configured on:
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details      
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.dbname.db
      1        ONLINE  ONLINE       node04              Open,Readonly,STABLE
      2        ONLINE  UNKNOWN      node01              Abnormal Termination
                                                             ,STABLE
--------------------------------------------------------------------------------

but when I checked configuration for this database:

srvctl config database -d dbname
...
Database instances: dbname1,dbname1
Configured nodes: node03,node04

 In documentation is a info that crsctl has a option "relocate resource" but dosn't work :/

To relocate resource I had to use:
srvctl modify instance -d dbname -i dbname1 -n node03


In one of document on metalink I found that only modify option force clusterware to synchronize information about resources

Purge materialized view log table

We found huge MV log (~14GB) on table which has 1G. The reason was that few months ago, after some maintenance we had to recreate materialized view (REFRESH FAST).

After recreating MV, log had two registered snapshots and leave data for first MV which was dropped. MV has been created to replicate data beatween two databases and inMV definition we using database links. After dropping MV,  information about registred MV had not been refreshed.

To purge log table from database which waiting for not existed MV I used dbms_mview.purge_mview_from_log procedure, but in first step I have to check which snapshot not exist (thanks Remek: http://remigium.blogspot.com/2013/08/the-orphaned-mview-registration-on.html)

SELECT s.mowner,
  s.master,
  s.snaptime,
  'exec dbms_mview.purge_mview_from_log('
  ||s.snapid
  ||');' fix1
FROM sys.slog$ s
WHERE NOT EXISTS
  (SELECT 1 FROM DBA_REGISTERED_SNAPSHOTS R WHERE S.SNAPID=R.SNAPSHOT_ID
  )
ORDER BY MOWNER,
  master;

and run purge_mview_from_log for not exited snapid.

After that all data for not existed MV will be remove, but only for this snap id, so we don't need run complete refresh for exited MV.

MV log table is a normall segment and it's no problem with shrinking segment by alter table MLOG$_EX_TABLE shrink space;

On our db after purging and shrinking, log table segment has 80MB.

11.2.0.2 - high 'cursor: pin s wait on x'

Recently I've got problem with high wait event "cursor: pin s wait on x" a high CPU utilization on CPU. On database we see many child cursors (~300) . Query is very complex (explain plan has ~1000 lines) and generate about 20 different execution plans (bind variables are in use) and sometimes execution plan is very slow.

In V$SQL_SHARED_CURSOR I checked mismatch and on two columns (AUTH_CHECK_MISMATCH and INSUFF_PRIVS) value is 'Y'.
I query is used SYS_CONTEXT function and I though that can be problem with VPD but no.
I also checking by trace 10046 where database spend time but here I found that parse time elapsed is nearly 97% ( but probably through  recursive sql database badly count time).

After some investigation on metalink I found BUG 11930680

"If optimizer_secure_view_merging is enabled then some SQL statements may
not be shared due to AUTH_CHECK_MISMATCH / INSUFF_PRIVS even if the
SQL is issued repeatedly by the same user. This can cause excess shared
pool memory use and other contention issues due to the high child cursor
count."


and after patch installation problem has been solved - one child cursor.

How to move segments

Here are a few commands in one place to move segments:

In first step we move tables without partitions and subpartitions:

SELECT 'alter table '
  ||OWNER
  ||'.'
  ||TABLE_NAME
  ||' move tablespace '
  ||TABLESPACE_NAME
  ||';'
FROM DBA_TABLES
WHERE TABLESPACE_NAME IN ('USERS')
AND TABLE_NAME NOT    IN
  ( SELECT TABLE_NAME FROM DBA_TAB_PARTITIONS WHERE TABLESPACE_NAME IN ('USERS')
  UNION ALL
  SELECT TABLE_NAME
  FROM DBA_TAB_SUBPARTITIONS
  WHERE TABLESPACE_NAME IN ('USERS')
  );

for partitions and subpartitions...

SELECT 'alter table '
  ||TABLE_OWNER
  ||'.'
  ||TABLE_NAME
  ||' move partition '
  ||PARTITION_NAME
  ||' tablespace '
  ||TABLESPACE_NAME
  ||' UPDATE  INDEXES;'
FROM DBA_TAB_PARTITIONS
WHERE TABLESPACE_NAME IN ('USERS') and SUBPARTITION_COUNT=0;;

SELECT 'alter table '
  ||TABLE_OWNER
  ||'.'
  ||TABLE_NAME
  ||' move subpartition '
  ||SUBPARTITION_NAME
  ||' tablespace '
  ||TABLESPACE_NAME
  ||' UPDATE  INDEXES;'
FROM dba_tab_subpartitions
WHERE tablespace_name IN ('USERS');

and for LOB objects

SELECT 'alter table '
  ||owner
  ||'.'
  ||table_name
  ||' move lob('
  ||column_name
  ||') store as (tablespace '
  ||TABLESPACE_NAME
  ||');'
FROM dba_lobs
WHERE TABLESPACE_NAME IN ('USERS');
 
and for LOB subpartitions objects

SELECT 'ALTER TABLE '||TABLE_OWNER ||'."'|| TABLE_NAME || '" MOVE SUBPARTITION '|| SUBPARTITION_NAME ||'  TABLESPACE '||TABLESPACE'|| LOB ('||
COLUMN_NAME||') STORE AS (TABLESPACE '||TABLESPACE_NAME||');'
 FROM DBA_LOB_SUBPARTITIONS WHERE TABLESPACE_NAME='USERS';

After that we have to rebuild indexes:

SELECT 'alter index '
  ||owner
  ||'.'
  ||SEGMENT_NAME
  ||' rebuild online;'
FROM DBA_SEGMENTS
WHERE TABLESPACE_NAME IN ('USERS')
AND segment_type       ='INDEX'; 

SELECT 'ALTER INDEX '
  ||INDEX_OWNER
  ||'.'
  || INDEX_NAME
  ||' rebuild subpartition '
  || SUBPARTITION_NAME
  ||' online;'
FROM DBA_IND_SUBPARTITIONS
WHERE STATUS='UNUSABLE';

SELECT 'ALTER INDEX '
  ||INDEX_OWNER
  ||'.'
  || index_name
  ||' rebuild partition '
  || PARTITION_NAME
  ||' online;'
FROM DBA_IND_PARTITIONS
WHERE status='UNUSABLE'; 

Sometimes we want to move subpartitions to another tablespace, but information about tablespace is also on the partition level (despite the fact that there is no segment): 

ALTER TABLE USER1.TABLE1  MODIFY DEFAULT ATTRIBUTES FOR PARTITION DATA_1 TABLESPACE USER_DATA_2;
 

Oracle and PHP - change password

PHP and change expired password on the Oracle database.

For databases with more users with expiring  passwords if not all users have sqlplus client installed is the problem with changing passwords. Unfortunately, few tools support this feature.

The easiest way is to write a simple interface PHP that uses a very useful function oci_password_change:

public function change_and_connect($user, $old_pw, $new_pw, $database)
    {
          $c = oci_password_change($database, $user, $old_pw, $new_pw);
        if (!$c) {
            $m = oci_error();
            $_SESSION['err_message'] =$m["message"];
            return($m['code']);
        }
        else {
            return(true);
        }
       
    }