Incremental statistics

In 11g Oracle introduce incremental gathering statistics for partitioned tables. For huge tables gathering statistics takes a lot of time, but if incremental statistic is enabled on table, of course gathering will be faster.
Below is a query when we can check which  tables don't have enabled incremental statistics:

SELECT UNIQUE TABLE_OWNER,TABLE_NAME,
  SUM(NUM_ROWS)
FROM DBA_TAB_PARTITIONS
WHERE TABLE_OWNER not in ('SYS','SYSTEM')
AND table_name NOT IN
  (SELECT table_name
  FROM DBA_TAB_STAT_PREFS
  WHERE PREFERENCE_NAME='INCREMENTAL'
  AND PREFERENCE_VALUE ='TRUE'
  )
GROUP BY TABLE_OWNER,TABLE_NAME
ORDER BY 3;

and here is script to enable incremental statistcs for table:


EXEC dbms_stats.set_table_prefs('SCHEMA1', 'TABLE1', 'INCREMENTAL', 'TRUE');
EXEC DBMS_STATS.set_table_prefs('SCHEMA1', 'TABLE1', 'ESTIMATE_PERCENT', DBMS_STATS.AUTO_SAMPLE_SIZE);
EXEC dbms_stats.set_table_prefs('SCHEMA1', 'TABLE1', 'GRANULARITY', 'AUTO');
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA1', 'TABLE1');

Data Guard - Real Time Apply

In a normal configuration Data Guard , applying changes I  turn on by command:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY DISCONNECT FROM SESSION;


But in this mode, standby database apply changes after switching redolog group.

In order to applying changes in real time need to use the command:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

and now in v$managed_standby on standby, status for MRP0 (managed recovery process) process is APPLYING_LOG

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 AQ - pl/sql notification does not work

 I fought  with the issue of pl/sql notification in the Advanced Queuing. Sending a message to the queue, received the status of "READY". Clearing the queue did not help. The problem was only with the  PL/SQL notification and for mail notification, everything worked without problems.

I noticed that the automatic job AQ$_PLSQL_NTFN calling the callback function waits and locks row in the table  SYS.AQ$AQ_SRVNTFN_TABLE_1.

Helped clear the table by a modified procedure  from http://riyazmsm.blogspot.com:

Appropriate queue name  for pl/sql notification you can check using:
select * from sys.AQ_SRVNTFN_TABLE_1 


EXEC DBMS_AQADM.START_QUEUE('AQ$_AQ_SRVNTFN_TABLE_1_E',false, true); 

DECLARE
  dequeue_options DBMS_AQ.dequeue_options_t;
  message_properties DBMS_AQ.message_properties_t;
  dq_msgid RAW(16);
  payload RAW(1);
  no_messages EXCEPTION;
  pragma exception_init (no_messages, -25263);
  msg_count NUMBER(2);
  CURSOR c_msg_ids
  is
    SELECT MSG_ID FROM sys.AQ$AQ_SRVNTFN_TABLE_1 WHERE queue = 'AQ$_AQ_SRVNTFN_TABLE_1_E';
BEGIN
  dequeue_options.wait         := DBMS_AQ.NO_WAIT;
  dequeue_options.navigation   := DBMS_AQ.FIRST_MESSAGE;
  dequeue_options.dequeue_mode := dbms_aq.remove_nodata;
  FOR v_msg_id                 IN c_msg_ids
  LOOP
    dequeue_options.msgid := v_msg_id.msg_id;
    MSG_COUNT             := 0;
    DBMS_AQ.DEQUEUE(queue_name => 'sys.AQ$_AQ_SRVNTFN_TABLE_1_E', dequeue_options => dequeue_options, message_properties => message_properties, payload => payload, msgid => dq_msgid);
    dbms_output.put_line('Message id : '||v_msg_id.msg_id||' removed');
    msg_count                  := msg_count + 1;
    dequeue_options.msgid      := NULL;
    dequeue_options.navigation := DBMS_AQ.NEXT_MESSAGE;
  END LOOP;
commit;
EXCEPTION
WHEN no_messages THEN
  DBMS_OUTPUT.PUT_LINE ('No of Messages Removed: '||msg_count);
  COMMIT;
END;
/

or

 EXEC DBMS_AQADM.START_QUEUE('AQ_SRVNTFN_TABLE_Q_1',false, true);  

DECLARE
  dequeue_options DBMS_AQ.dequeue_options_t;
  message_properties DBMS_AQ.message_properties_t;
  dq_msgid RAW(16);
  payload RAW(1);
  no_messages EXCEPTION;
  pragma exception_init (no_messages, -25263);
  msg_count NUMBER(2);
  CURSOR c_msg_ids
  is
    SELECT MSG_ID FROM sys.AQ$AQ_SRVNTFN_TABLE_1 WHERE queue = 'AQ_SRVNTFN_TABLE_Q_1';
BEGIN
  dequeue_options.wait         := DBMS_AQ.NO_WAIT;
  dequeue_options.navigation   := DBMS_AQ.FIRST_MESSAGE;
  dequeue_options.dequeue_mode := dbms_aq.remove_nodata;
  FOR v_msg_id                 IN c_msg_ids
  LOOP
    dequeue_options.msgid := v_msg_id.msg_id;
    MSG_COUNT             := 0;
    DBMS_AQ.DEQUEUE(queue_name => 'sys.AQ_SRVNTFN_TABLE_Q_1', dequeue_options => dequeue_options, message_properties => message_properties, payload => payload, msgid => dq_msgid);
    dbms_output.put_line('Message id : '||v_msg_id.msg_id||' removed');
    msg_count                  := msg_count + 1;
    dequeue_options.msgid      := NULL;
    dequeue_options.navigation := DBMS_AQ.NEXT_MESSAGE;
  END LOOP;
commit;
EXCEPTION
WHEN no_messages THEN
  DBMS_OUTPUT.PUT_LINE ('No of Messages Removed: '||msg_count);
  COMMIT;
END;
/

After that database should be restarted.

It is interesting that in this table were the messages until the queue to work properly.

TSPITR using auxiliary destination

Unfortunately, After some time, we noted a logical corruption of data within an application schema  which could not be eliminated by scripts :/

Schema used by the application is only a small part of  data warehouses. Scheme had to be restored to a certain point in time.
After a moment's thought decided to use the new functionality of restore tablespaces using auxiliary destination with automatic instance.

Fortunately application had separated twelve tablespaces , so there was concern that violate the data from other systems.

We need to restore 400GB of 7TB database.

We created a separate directory for the external instance and run command:

run {
    SET AUXILIARY INSTANCE PARAMETER FILE TO '/oracle/app/11.2/dbs/aux_rman_instance.ora';
    recover TABLESPACE APP_DATA1,...,APP_DATA12 auxiliary destination '/oradb/aux';
}

After restoring tablespaces RMAN performs all the steps, which in previous versions you had to do manually. Now, all these steps are performed automatically. Cool, but can cause big problems and instead of saving time, it will bring a lot of work.

First problem:
The default memory settings for the automatic instance is too low and need to set  shared_pool_size = 200M and sga_target=400M. In this way, after about 12 hours restored is stopped and external instance, along with the files was removed after  got the error ORA-04031.

So, once again :)

The second problem, after several hours, again a error. This time has exceeded the number of processes (probably due  to run export process)   and  we change a parameter processes=150.

This time, however, despite the error, the auxiliary instance is not removed and datafiles can be used.
Further steps I done manually, since I could not wait for next 12 hours :)

One more thing. In parameter file for auxiliary instance should be specified db_name. Default db_name is random and thus easier to connect to it and export metadata for datafiles.

Oracle DB 11.2 - password_life_time

Oracle database 11.2 - password life time

After migrating a database from 10g to 11g version, I noticed that in the default profile was changed parameter password_life_time = 180 days which can cause problems for applications


Solution is change default profile or create a special profile "application"

alter profile default limit password_life_time unlimited;

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);
        }
       
    }