Instance Recovery  

Posted by Mawahid in

Oracle performs instance recovery when the database is restarted due to instance failure or shutdown the database with ABORT option(shutdown abort) or startup the database with FORCE option(startup force). Instance recovery is taken care by SMON oracle background process automatically. Instance recovery consists of two steps. One is Roll forward, next is Roll backward.

Roll forward:

Changes being made to the database are recorded in the database buffer cache and buffer cache will be written into data files. At the same time, the changes are recorded in redo log buffer and redo log buffer will be written to redo log file. Oracle writes the data from database buffer cache to data file only when there is enough data in data buffer cache. It is not necessary that, every commit, oracle writes the data buffer cache into data file. When instance fails before committed data are written into data files, Oracle uses the data recorded in the online redo log files to recover the lost data when the associated database is re-started. This process is called Roll forward or cache recovery.

Roll backward:

When we make any changes in the database, the old image will be written into undo segment. Later undo segment will be used to rollback the data when we rollback the transaction. DBWR writes the buffer cache content into data file under different circumstances. It might be possible to write the uncommitted data from database buffer cache into data file. When instance fails and associated database is restarted, it rollback the uncommitted transaction in data file by using undo segment to maintain the read consistency. This is called roll forward or transaction recovery.

0 comments »

How to use histogram in Oracle  

Posted by Mawahid in

What is Histogram? Histograms are feature in CBO and it helps to optimizer to determine how data are skewed(distributed) with in the column. Histogram is good to create for the column which are included in the WHERE clause where the column is highly skewed. Histogram helps to optimizer to decide whether to use an index or full-table scan or help the optimizer determine the fastest table join order.

What are the advantage of Histogram? Histograms are useful in two places.

1. Histograms are useful for Oracle optimizer to choose the right access method in a table.

2. It is also useful for optimizer to decide the correct table join order. When we join multiple tables, histogram helps to minimize the intermediate result set. Since the smaller size of the intermediate result set will improve the performance.

Type of Histograms: Oracle uses two types of histograms for column statistics: height-balanced histograms and frequency histograms.

1. Height - balanced Histograms : The column values are divided into bands so that each band contains approximately the same number of rows. For instances, we have 10 distinct values in the column and only five buckets. It will create height based(Height balanced) histograms and it will evenly spread values through the buckets. A height-based histogram is when there are more distinct values than the number of buckets and the histogram statistics shows a range of rows across the buckets

2. Frequency Histograms : Each value of the column corresponds to a single bucket of the histogram. This is also called value based histogram. Each bucket contains the number of occurrences of that single value. Frequency histograms are automatically created instead of height-balanced histograms when the number of distinct values is less than or equal to the number of histogram buckets specified.

Method_opt Parameter: This is the parameter which tells about creating histogram while collecting the statistics. The default is FOR ALL COLUMNS SIZE AUTO in Oracle10g. But in oracle9i, the default is FOR ALL COLUMN SIZE 1 which will turn off the histogram collection.

FOR ALL [INDEXED HIDDEN] COLUMNS [size_clause]
FOR COLUMNS [size clause] columnattribute [size_clause] [,columnattribute [size_clause]...]

size_clause is defined as size_clause := SIZE {integer REPEAT AUTO SKEWONLY}

integer : Number of histogram buckets. Must be in the range [1,254]

REPEAT : Collects histograms only on the columns that already have histograms.

AUTO : Oracle determines the columns to collect histograms based on data distribution and the workload of the columns. We have a table called sys.col_usage$ that stores information about column usage. dbms_stats use this information to determine whether histogram is required for the columns.

SKEWONLY : Oracle determines the columns to collect histograms based on the data distribution of the columns.

Let me demonstrate how optimizer works with and without histogram as below two scenario. We take the emp table for this demonstration. The table has around 3.6 million records. The table emp_status column is highly skewed. It has two distinct values(Y,N). We have bitmap index on emp_status column.

Scenario 1 Let us generate the statistics without any histogram and see what kind of execution path optimizer is using. Without the histogram, oracle assume that, the data is evenly distributed and optimizer think that, we will have around 1.8 million record for emp_status Y and around another 1.8 million records for emp_status N.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select count(*),emp_status from scott.emp group by emp_status;

COUNT(*) E
---------- -
1 N
3670016 Y

SQL> execute DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'SCOTT', TABNAME => 'EMP',ESTIMATE_PERCENT =>
10, METHOD_OPT => 'FOR ALL COLUMNS SIZE 1',CASCADE => TRUE);

PL/SQL procedure successfully completed.

SQL> select ename from scott.emp where emp_status='Y';

3670016 rows selected.


--------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
--------------------------------------------------------------------------
0 SELECT STATEMENT 1832K 15M 5374 (5) 00:01:05
* 1 TABLE ACCESS FULL EMP 1832K 15M 5374 (5) 00:01:05
--------------------------------------------------------------------------

SQL> select ename from scott.emp where emp_status='N';

--------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
--------------------------------------------------------------------------
0 SELECT STATEMENT 1832K 15M 5374 (5) 00:01:05
* 1 TABLE ACCESS FULL EMP 1832K 15M 5374 (5) 00:01:05
--------------------------------------------------------------------------

Conclusion: Optimizer is using full table scan for the query which returns 3670016 records as well as it using full table scan for query which returns just only one record. This is obvisouly incorrect. This problem will be resolved by collecting histogram. Let us see in the next scenario.

Scenario 2 : Let us generate the statistics with histogram and see what kind of execution path optimizer is using. FOR COLUMN SIZE 2 EMP_STATUS will create two bucket for column emp_status. If we are not sure the distinct number of values in the column, then we can use AUTO option to collect histogram. With this histogram, oracle optimizer knows that, the column emp_status is highly skewed and it has two bucket and one bucket has around 3.6 million records with emp_status Y and another bucket has only one record with emp_status N. Now depends upon the query, optimizer decides whether to use index or Full table scan.

SQL> execute DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'SCOTT', TABNAME => 'EMP',ESTIMATE_PERCENT =>
10, METHOD_OPT => 'FOR COLUMNS SIZE 2 EMP_STATUS',CASCADE => TRUE);

PL/SQL procedure successfully completed.

SQL> select ename from scott.emp where emp_status='Y';

3670016 rows selected.

--------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
--------------------------------------------------------------------------
0 SELECT STATEMENT 3681K 31M 5375 (5) 00:01:05
* 1 TABLE ACCESS FULL EMP 3681K 31M 5375 (5) 00:01:05
--------------------------------------------------------------------------

SQL> select ename from scott.emp where emp_status='N';

--------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
--------------------------------------------------------------------------
0 SELECT STATEMENT 1 9 1 (0) 00:00:01
1 TABLE ACCESS BY INDEX ROWID EMP 1 9 1 (0) 00:00:01
2 BITMAP CONVERSION TO ROWIDS
* 3 BITMAP INDEX SINGLE VALUE IDX_EMP
--------------------------------------------------------------------------

Conclusion: Optimizer is using full table scan for the query which returns 3670016 records. At the same time, optimizer is using index scan when for other query which returns one record. This scenario, the optimizer choose the right execution plan based on the query WHERE clause.

Data dictionary objects for Histogram:
 
user_histograms
user_part_histograms
user_subpart_histograms
user_tab_histograms
user_tab_col_statistics

0 comments »

What's blocking my lock?  

Posted by Mawahid in

To begin, create a situation where one user is actively blocking another. Open two sessions. Issue the following commands in Session 1 to build the test table:

SQL> create table tstlock (foo varchar2(1), bar varchar2(1));

Table created.

SQL> insert into tstlock values (1,'a');

1 row created.

SQL> insert into tstlock values (2, 'b');

1 row created.

SQL> select * from tstlock ;

FOO BAR
--- ---
1   a
2   b

2 rows selected.

SQL> commit ;

Commit complete.

Now grab a lock on the whole table, still in Session 1:

SQL> select * from tstlock for update ;

And in Session 2, try to update a row:

SQL> update tstlock set bar= 'a' where bar='a' ;

This statement will hang, blocked by the lock that Session 1 is holding on the entire table.

Identify the blocking session

Oracle provides a view, DBA_BLOCKERS, which lists the SIDs of all blocking sessions. But this view is often, in my experience, a good bit slower than simply querying V$LOCK, and it doesn't offer any information beyond the SIDs of any sessions that are blocking other sessions. The V$LOCK view is faster to query, makes it easy to identify the blocking session, and has a lot more information.

SQL> select * from v$lock ;

ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
AF9E2C4C AF9E2C60        479 TX     131078      16739          0          6        685          0
ADDF7EC8 ADDF7EE0        422 TM      88519          0          3          0        697          0
ADDF7F74 ADDF7F8C        479 TM      88519          0          3          0        685          0
ADEBEA20 ADEBEB3C        422 TX     131078      16739          6          0        697          1
....     ....            ... ...      ....       ....       ....       ....        ....      ....


Note the BLOCK column. If a session holds a lock that's blocking another session, BLOCK=1. Further, you can tell which session is being blocked by comparing the values in ID1 and ID2. The blocked session will have the same values in ID1 and ID2 as the blocking session, and, since it is requesting a lock it's unable to get, it will have REQUEST > 0.

In the query above, we can see that SID 422 is blocking SID 479. SID 422 corresponds to Session 1 in our example, and SID 479 is our blocked Session 2.

To avoid having to stare at the table and cross-compare ID1's and ID2's, put this in a query:

SQL> select l1.sid, ' IS BLOCKING ', l2.sid
from v$lock l1, v$lock l2
where l1.block =1 and l2.request > 0
and l1.id1=l2.id1
and l1.id2=l2.id2;

       SID 'ISBLOCKING'         SID
---------- ------------- ----------
       422  IS BLOCKING         479

1 row selected.

Even better, if we throw a little v$session into the mix, the results are highly readable:

SQL> select s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' )  is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;

BLOCKING_STATUS
----------------------------------------------------------------------------------------------------
BULKLOAD@yttrium ( SID=422 )  is blocking BULKLOAD@yttrium ( SID=479 )

1 row selected.

There's still more information in the v$lock table, but in order to read that information, we need to understand a bit more about lock types and the cryptically-named ID1 and ID2 columns.

Lock type and the ID1 / ID2 columns

In this case, we already know that the blocking lock is an exclusive DML lock, since we're the ones who issued the locking statement. But most of the time, you won't be so lucky. Fortunately, you can read this information from the v$lock table with little effort.

The first place to look is the TYPE column. There are dozens of lock types, but the vast majority are system types. System locks are normally only held for a very brief amount of time, and it's not generally helpful to try to tune your library cache, undo logs, etc. by looking in v$lock! (See the V$LOCK chapter in the Oracle Database Reference for a list of system lock types.)

There are only three types of user locks, TX, TM and UL. UL is a user-defined lock -- a lock defined with the DBMS_LOCK package. The TX lock is a row transaction lock; it's acquired once for every transaction that changes data, no matter how many objects you change in that transaction. The ID1 and ID2 columns point to the rollback segment and transaction table entries for that transaction.

The TM lock is a DML lock. It's acquired once for each object that's being changed. The ID1 column identifies the object being modified.

Lock Modes

You can see more information on TM and TX locks just by looking at the lock modes. The LMODE and REQUEST columns both use the same numbering for lock modes, in order of increasing exclusivity: from 0 for no lock, to 6 for exclusive lock. A session must obtain an exclusive TX lock in order to change data; LMODE will be 6. If it can't obtain an exclusive lock because some of the rows it wants to change are locked by another session, then it will request a TX in exclusive mode; LMODE will be 0 since it does not have the lock, and REQUEST will be 6. You can see this interaction in the rows we selected earlier from v$lock:

ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
AF9E2C4C AF9E2C60        479 TX     131078      16739          0          6        685          0
ADEBEA20 ADEBEB3C        422 TX     131078      16739          6          0        697          1

Note that ID1 and ID2 in Session 2, which is requesting the TX lock (LMODE=0, REQUEST=6), point back to the rollback and transaction entries for Session 1. That's what lets us determine the blocking session for Session 2.

You may also see TX locks in mode 4, Shared mode. If a block containing rows to be changed doesn't have any interested transaction list (ITL) entries left, then the session acquires a TX lock in mode 4 while waiting for an ITL entry. If you see contention for TX-4 locks on an object, you probably need to increase INITRANS for the object.

TM locks are generally requested and acquired in modes 3, aka Shared-Row Exclusive, and 6. DDL requires a TM Exclusive lock. (Note that CREATE TABLE doesn't require a TM lock -- it doesn't need to lock any objects, because the object in question doesn't exist yet!) DML requires a Shared-Row Exclusive lock. So, in the rows we selected earlier from v$lock, you can see from the TM locking levels that these are DML locks:

ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
ADDF7EC8 ADDF7EE0        422 TM      88519          0          3          0        697          0
ADDF7F74 ADDF7F8C        479 TM      88519          0          3          0        685          0


Identifying the locked object

Now that we know that each TM row points to a locked object, we can use ID1 to identify the object.

SQL> select object_name from dba_objects where object_id=88519 ;

OBJECT_NAME
--------------
TSTLOCK

Sometimes just knowing the object is enough information; but we can dig even deeper. We can identify not just the object, but the block and even the row in the block that Session 2 is waiting on.

Identifying the locked row

We can get this information from v$session by looking at the v$session entry for the blocked session:

SQL> select row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#
from v$session where sid=479 ;

ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
------------- -------------- --------------- -------------
        88519             16          171309             0


This gives us the object ID, the relative file number, the block in the datafile, and the row in the block that the session is waiting on. If that list of data sounds familiar, it's because those are the four components of an extended ROWID. We can build the row's actual extended ROWID from these components using the DBMS_ROWID package. The ROWID_CREATE function takes these arguments and returns the ROWID:

SQL> select do.object_name,
row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#,
dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )
from v$session s, dba_objects do
where sid=543
and s.ROW_WAIT_OBJ# = do.OBJECT_ID ;

OBJECT_NAME     ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# DBMS_ROWID.ROWID_C
--------------- ------------- -------------- --------------- ------------- ------------------
TSTLOCK                 88519             16          171309             0 AAAVnHAAQAAAp0tAAA

And, of course, this lets us inspect the row directly.

SQL> select * from tstlock where rowid='AAAVnHAAQAAAp0tAAA' ;

FOO BAR
--- ---
1   a

Conclusion

We've seen how to identify a blocking session, and how to inspect the very row that the waiting session is waiting for.

0 comments »

Bind Variable Peeking  

Posted by Mawahid in

Generally in OLTP systems, i.e. systems characterized by lots of short duration SQL statements, we want to use bind variables so as to minimize hard parses and maximize SQL reuse in the shared pool.

The exception occurred when we needed to search a column that contained relatively few heavily skewed data values. After indexing such a column and collecting optimizer statistics, Oracle was able to choose different execution plans when searching for different column values, based on their relative occurrence. Let us illustrate this:
CREATE TABLE Check_Status
(
Check_No  INTEGER      NOT NULL,
Status    VARCHAR2(10) NOT NULL
);

CREATE INDEX Skewed_Status ON Check_Status (Status);

BEGIN
  FOR l_check_no IN 1..10000 LOOP
    -- Lets cancel 1 in 100 checks   
    IF MOD (l_check_no, 100) = 0 THEN
      INSERT INTO Check_Status (Check_No, Status)
      VALUES (l_check_no, 'Cancelled');
    ELSE
      INSERT INTO Check_Status (Check_No, Status)
      VALUES (l_check_no, 'Processed');
    END IF;
  END LOOP;
END;

ANALYZE TABLE Check_Status COMPUTE STATISTICS;
ANALYZE TABLE Check_Status COMPUTE STATISTICS FOR COLUMNS Status;

SELECT Status, COUNT(*) FROM Check_Status GROUP BY Status;

STATUS      COUNT(*)
----------- --------
Cancelled        100
Processed       9900

The optimizer now has enough data available to choose the optimal execution plan:
SELECT * FROM Check_Status WHERE Status = 'Cancelled';

Operation                                   Rows  Bytes  Cost
SELECT STATEMENT Optimizer Mode=CHOOSE       100   1200     2
  TABLE ACCESS BY INDEX ROWID CHECK_STATUS   100   1200     2
    INDEX RANGE SCAN  SKEWED_STATUS          100            1

SELECT * FROM Check_Status WHERE Status = 'Processed';

Operation                                   Rows  Bytes  Cost
SELECT STATEMENT Optimizer Mode=CHOOSE      9900 118800     6
  TABLE ACCESS FULL  CHECK_STATUS           9900 118800     6

As the above plans show, the optimizer is clever enough to use the index range scan when we are looking for 'Cancelled' checks and the full table scan when looking for 'Processed' checks. This existing functionality handles skewed data well when literals are used but does not fare so well when bind variables are used instead of literals.

Oracle 9i introduced a new feature known as "bind variable peeking." This allows the optimizer to look inside a bind variable when a statement is hard parsed (i.e. initially when it is loaded into the shared pool). Unfortunately, this feature does not allow us to eliminate the literals from the above code. In fact, doing so would be a bad idea because we would lose one of our executions plans. To illustrate this we use the following commands in an Oracle 9i environment to query the table using a bind variable set to 'Cancelled' and then look at the tkprof output.


VARIABLE l_status VARCHAR2(20);
EXECUTE :l_status := 'Cancelled';
SELECT * FROM Check_Status WHERE Status = :l_status;

call     count       cpu    elapsed     disk    query  current      rows
------- ------  -------- ---------- -------- -------- --------  --------
Parse        1      0.36       0.36        0        0        0         0
Execute      1      0.00       0.00        0        0        0         0
Fetch        8      0.00       0.04       29       40        0       100
------- ------  -------- ---------- -------- -------- --------  --------
total       10      0.36       0.40       29       40        0       100

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 57 

Rows     Row Source Operation
-------  ---------------------------------------------------
    100  TABLE ACCESS BY INDEX ROWID OBJ#(32937)
    100   INDEX RANGE SCAN OBJ#(32939)

Looking at the above output, we see that this statement was hard parsed (misses in library cache during parse is 1) and Oracle correctly performed the index range scan based on the value of the bind variable 'Cancelled'. However if we now query for the 'Processed' checks, tkprof shows us the following:
EXECUTE :l_status := 'Processed';
SELECT * FROM Check_Status WHERE Status = :l_status;

call     count       cpu    elapsed     disk    query  current      rows
------- ------  -------- ---------- -------- -------- --------  --------
Parse        1      0.00       0.00        0        0        0         0
Execute      1      0.00       0.00        0        0        0         0
Fetch      661      0.06       0.26       27     1375        0      9900
------- ------  -------- ---------- -------- -------- --------  --------
total      663      0.06       0.26       27     1375        0      9900

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 57

Rows     Row Source Operation
-------  ---------------------------------------------------
   9900  TABLE ACCESS BY INDEX ROWID OBJ#(32937)
   9900   INDEX RANGE SCAN OBJ#(32939)

Looking at the above output, we see that this statement was soft parsed (misses in library cache during parse is 0) and Oracle reused the previous execution plan which is definitely sub-optimal in this case. A full table scan would have been better here, as we saw when literals were used.

In summary, bind variable peeking in Oracle 9i does not help us when we need different execution plans for different values of a bind variable. Bind variable peeking allows the optimizer to take the value of a bind variable into account when first generating an execution plan for a SQL statement. If we know that the data distribution is heavily skewed and that future executions of the SQL statement may involve different data distributions for the bind variable value, we must still resort to using literals to get the best performance.

0 comments »

How TO: Check for Charater Set Conversion Problems & Fix Them  

Posted by Mawahid

Character set conversion was done without using the csscan to verify if its possible.

ORA-06552: PL/SQL: Compilation unit analysis terminated
ORA-06553: PLS-553: character set name is not recognized

Character set was converted from WE8ISO8859P1 to AL32UTF8.

Helpful metalink note: 286964.1

Below query is used to see if there is a mismatch in character set in the database.

select distinct(nls_charset_name(charsetid)) CHARACTERSET,
decode(type#, 1, decode(charsetform, 1, 'VARCHAR2', 2, 'NVARCHAR2','UNKOWN'),
9, decode(charsetform, 1, 'VARCHAR', 2, 'NCHAR VARYING', 'UNKOWN'),
96, decode(charsetform, 1, 'CHAR', 2, 'NCHAR', 'UNKOWN'),
112, decode(charsetform, 1, 'CLOB', 2, 'NCLOB', 'UNKOWN')) TYPES_USED_IN
from sys.col$ where charsetform in (1,2) and type# in (1, 9, 96, 112) order by CHARACTERSET;

CHARACTERSET                                                 TYPES_USED_IN
----------------------------------------             -------------
AL16UTF16                                          NCHAR
AL16UTF16                                          NCLOB
AL16UTF16                                          NVARCHAR2
AL32UTF8                                            CLOB
AL32UTF8                                            VARCHAR2
WE8ISO8859P1                                  CHAR
WE8ISO8859P1                                  CLOB
WE8ISO8859P1                                  VARCHAR2

So we can see that the above query also returns the old character set along with the new character set. It should ideally not show any record of the old character.

Here you can see that there are 2 different character sets returned for VARCHAR2, CLOB & CHAR data, which indicates a mixup in the database character set setup, which is the cause for this PLS-553 error.

Now lets find out which tables & columns are having problems:

For CHAR,VARCHAR2 and CLOB columns:

The old or problem character set in the below query, replace it with the one you are facing problem with.

SQL> set lines 199
SQL> col owner format a10
SQL> col table_name format a20
SQL> col column_name format a20
SQL> select OWNER, TABLE_NAME, COLUMN_NAME from DBA_TAB_COLS where COLUMN_ID in (select unique COL# from sys.col$ where CHARSETID=NLS_CHARSET_ID('WE8ISO8859P1') and charsetform ='1')
order by OWNER, TABLE_NAME, COLUMN_NAME;

OWNER TABLE_NAME COLUMN_NAME
---------- -------------------- --------------------
TEST TEST_TABLE1 CODE
TEST TEST_TABLE1 CREATETIME
TEST TEST_TABLE1 LASTMODIFIEDTIME
TEST TEST_TABLE1 TYPEID
TEST TEST_TABLE2 ACTIONTYPE
.
.
.
49692 rows selected.

In this case almost all schemas in the database are affected including sys and system.

For Nchar, Nvarchar2 and Nclob:

SQL> set lines 199
SQL> col owner format a10
SQL> col table_name format a20
SQL> col column_name format a20
SQL> select OWNER, TABLE_NAME, COLUMN_NAME from DBA_TAB_COLS where COLUMN_ID in (select unique COL# from sys.col$ where CHARSETID=NLS_CHARSET_ID('WE8ISO8859P1') and charsetform ='2')
order by OWNER, TABLE_NAME, COLUMN_NAME;

no rows selected

Oracle note suggests that if the character set conversion has happened between a 7/8 bit character set like WE8ISO8859P1, US7ASCII etc to a mutibyte character set like UTF8, AL32UTF8 etc, then there will be data loss for clob columns which display the old character set. So it is best to take a full back of the database, preferably using the tradional export utility.

Check the following things:

SQL> show parameter CLUSTER_DATABASE

NAME                                   TYPE                      VALUE
------------------------------------ ----------- ------------------------------
cluster_database                             boolean               FALSE
cluster_database_instances       integer                  1

SQL> show parameter PARALLEL_SERVER

NAME                                                   TYPE                      VALUE
------------------------------------ ----------- ------------------------------
parallel_server                                  boolean               FALSE
parallel_server_instances            integer                  1

The parameters CLUSTER_DATABASE & parallel_server should be false.

Save the values for;

SQL> show parameter JOB_QUEUE_PROCESSES

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 10
SQL> show parameter AQ_TM_PROCESSES
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 2

To start the fix:

Shutdown listener, make sure there are not connections to the database.

SQL> shutdown immediate;
SQL> STARTUP MOUNT;
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
System altered.

SQL> ALTER DATABASE OPEN;
Database altered.

SQL> COL VALUE NEW_VALUE CHARSET

SQL> SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_CHARACTERSET';

VALUE
--------------------------------------------------------------------------------
AL32UTF8

SQL> COL VALUE NEW_VALUE NCHARSET

SQL> SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_NCHAR_CHARACTERSET';
VALUE
--------------------------------------------------------------------------------
AL16UTF16

SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE &CHARSET;
old 1: ALTER DATABASE CHARACTER SET INTERNAL_USE &CHARSET
new 1: ALTER DATABASE CHARACTER SET INTERNAL_USE AL32UTF8
Database altered.

SQL> ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE &NCHARSET;
old 1: ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE &NCHARSET
new 1: ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE AL16UTF16
Database altered.

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP;

SQL> SHUTDOWN IMMEDIATE;

SQL> startup

Need to restart the database twice.

Check the database to see if there are any problems:

select distinct(nls_charset_name(charsetid)) CHARACTERSET,
decode(type#, 1, decode(charsetform, 1, 'VARCHAR2', 2, 'NVARCHAR2','UNKOWN'),
9, decode(charsetform, 1, 'VARCHAR', 2, 'NCHAR VARYING', 'UNKOWN'),
96, decode(charsetform, 1, 'CHAR', 2, 'NCHAR', 'UNKOWN'),
112, decode(charsetform, 1, 'CLOB', 2, 'NCLOB', 'UNKOWN')) TYPES_USED_IN
from sys.col$ where charsetform in (1,2) and type# in (1, 9, 96, 112);

CHARACTERSET                                 TYPES_USED_IN
---------------------------------------- -------------------------------
AL32UTF8                                            VARCHAR2
AL16UTF16                                          NCLOB
AL16UTF16                                          NVARCHAR2
AL32UTF8                                            CHAR
AL32UTF8                                            CLOB
AL16UTF16                                          NCHAR

6 rows selected.


SQL> select OWNER, TABLE_NAME, COLUMN_NAME from DBA_TAB_COLS where COLUMN_ID in (select unique COL# from sys.col$ where CHARSETID=NLS_CHARSET_ID('WE8ISO8859P1') and charsetform ='1') order by OWNER, TABLE_NAME, COLUMN_NAME;


SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=10;

SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=2;

This procedure doesn't fix any data, it cannot get back the lost data due to character set changes. That will need to be restored from backup.

All it does is fixes the references the tables are having to the old character set. Hence this is just the solution to fix the data dictionary not the data, or the loss that has happened.

0 comments »

Advanced Queues  

Posted by Mawahid

1. Create a user, tablespace and grant the privileges
First of all, we'll create a user, named AQDEMO, to own and use the message queues. In real life, you'll probably separate the queue owner from message publishers and consumers. However, to keep that demo short, we'll use AQDEMO to publish and consume messages too. It's worth to notice that a tablespace that contains queue tables doesn't support Tablespace Point In Time Recovery (TSPITR). For that reason and to ease the management of our AQ environment, we'll store the queues in a separate tablespace called AQ. Lastly if you want to share queues across multiple databases, you should  use a distributed environment and set the global_names parameter of all the databases to true.

create tablespace aq datafile '/u01/app/oracle/oradata/BLACK/aq01.dbf'  size 25M autoextend on maxsize 266M;
create user aqdemo identified by aqdemo default tablespace aq temporary tablespace temp;
grant connect, resource to aqdemo;
grant execute on dbms_aqadm to aqdemo;
grant execute on dbms_aq to aqdemo;

2. Create a queue table, a multiple consumer queue and start it
To perform these steps, we'll connect as AQDEMO and will use:
dbms_aqadm.create_queue_table - to create the queue table
dbms_aqadm.create_queue - to create the queue
dbms_aqadm.start_queue - to start the queue
To keep it simple, we'll won't use a user defined type for the message but just a RAW:

                        connect aqdemo/aqdemo

begin
 dbms_aqadm.create_queue_table(
    queue_table        =>'myqueue_table',
    queue_payload_type =>'RAW',
    storage_clause     =>'tablespace aq',
    multiple_consumers => true,
    comment            =>'AQ Demo',
    compatible=>'11.1');
end;
/

begin
 dbms_aqadm.create_queue(
    queue_name        => 'myqueue',
    queue_table       => 'myqueue_table');
end;
/

begin
 dbms_aqadm.start_queue(
    queue_name => 'myqueue',
    enqueue    => true,
    dequeue    => true);
end;
/

Note:

If you plan to create a queue in another schema, you can prefix the queue table or queue name with the schema name like AQDEMO.MYQUEUE_TABLE.
If you want to mix cases, you can surround the name with double-quotes, like "AQDEMO"."myqueue_table" but that's probably easier to use uppercase.


Once the queue table created, several tables and views are used to store and query the associated data; here is an example with AQ$[QUEUE_TABLE]:

                desc aq$myqueue_table


3. Create subscribers for the queue

In the case of Publish/Subscribe, you have to create subscribers to dequeue messages. In our example, we'll register 2 subscribers called SUBSCRIBER1 and SUBSCRIBER2 for that purpose:


connect aqdemo/aqdemo

declare
 subscriber  sys.aq$_agent;
begin
 subscriber := sys.aq$_agent(
                'subscriber1',
                'myqueue',
                null);
 dbms_aqadm.add_subscriber(
                queue_name    => 'myqueue',
                subscriber    => subscriber,
                  delivery_mode => dbms_aqadm.buffered);
end;
/

declare
 subscriber  sys.aq$_agent;
begin
 subscriber := sys.aq$_agent(
               'subscriber2',
               'myqueue',
               null);
 dbms_aqadm.add_subscriber(
               queue_name    => 'myqueue',
               subscriber    => subscriber,
                 delivery_mode => dbms_aqadm.buffered);
end;
/


You can check queue subscribers by querying the AQ$MYQUEUE_TABLE_S view (or AQ$_MYQUEUE_TABLE_S table) like below:

col name    format a12
col address format a40

select name, address
from aq$myqueue_table_s
where queue='MYQUEUE';

NAME      ADDRESS
------------ -------------------------
SUBSCRIBER1
SUBSCRIBER2


4. Create a procedure to enqueue buffered messages

The procedure below can be used to enqueue buffered messages; The options and properties used to enqueue the messages are the following:
·         The message is a RAW(10) passed from the procedure parameter as an hexadecimal string
·         The list of message recipients are SUBSCRIBER1 and SUBSCRIBER2
·         The message is a buffered message (dbms_aq.buffered)


connect aqdemo/aqdemo

create or replace procedure demo_enqueue(p_hexamsg varchar2) is
 enqueue_options     DBMS_AQ.enqueue_options_t;
 message_properties  DBMS_AQ.message_properties_t;
 recipients          DBMS_AQ.aq$_recipient_list_t;
 message_handle      RAW(16);
 message             RAW(10);
begin
 message := hextoraw(p_hexamsg);
 recipients(1) := sys.aq$_agent('SUBSCRIBER1', 'MYQUEUE', NULL);
 recipients(2) := sys.aq$_agent('SUBSCRIBER2', 'MYQUEUE', NULL);
 message_properties.recipient_list := recipients;
 enqueue_options.visibility := dbms_aq.immediate;
 enqueue_options.delivery_mode := dbms_aq.buffered;
 dbms_aq.enqueue(
     queue_name         => 'MYQUEUE',
     enqueue_options    => enqueue_options,
     message_properties => message_properties,
     payload            => message,
     msgid              => message_handle);
 commit;
end;
/

Once the procedure created, you can easily enqueue messages from the AQDEMO user:

                SQL> exec demo_enqueue('00000000000000000001');

5. Create a procedure to dequeue buffered messages

The procedure below dequeues the buffered messages and display it with DBMS_OUTPUT.PUT_LINE;  it takes the subscriber name as a parameter

connect aqdemo/aqdemo

set serveroutput on

create or replace procedure demo_dequeue(p_subscriber varchar2)
is
 dequeue_options       dbms_aq.dequeue_options_t;
 message_properties    dbms_aq.message_properties_t;
 message_handle        RAW(16);
 message               RAW(10);
 no_messages           exception;
 pragma exception_init(no_messages, -25228);
begin
 dequeue_options.wait          := dbms_aq.no_wait;
 dequeue_options.consumer_name := p_subscriber;
 dequeue_options.navigation    := dbms_aq.first_message;
 dequeue_options.visibility    := dbms_aq.immediate;
 dequeue_options.delivery_mode := dbms_aq.buffered;
loop
begin
 dbms_aq.dequeue(
     queue_name         => 'myqueue',
     dequeue_options    => dequeue_options,
     message_properties => message_properties,
     payload            => message,
     msgid              => message_handle);
 dbms_output.put_line('Message: '|| hextoraw(message) );
 dequeue_options.navigation := dbms_aq.next_message;
end;
end loop;
exception
 when no_messages then
    dbms_output.put_line('No more messages');
    commit;
end;
/

You can dequeue messages with demo_dequeue like below:

set serveroutput on
exec demo_dequeue('SUBSCRIBER1');

Message: 00000000000000000001
No more messages

6. Enqueue & Dequeue messages

Here is a sequence of enqueue and dequeue of buffered messages:

exec demo_enqueue('00000000000000000002');
exec demo_enqueue('00000000000000000003');

set serveroutput on
exec demo_dequeue('SUBSCRIBER1');

Message: 00000000000000000002
Message: 00000000000000000003
No more messages

exec demo_enqueue('00000000000000000004');

exec demo_dequeue('SUBSCRIBER1');
Message: 00000000000000000004
No more messages

set lines 100
col subscriber_name format a15
col subscriber_type format a15
col startup_time    format a18
col total_dequeued_msg format 999,999

select queue_id,
     subscriber_name,
     subscriber_type,
     startup_time,
     total_dequeued_msg
from v$buffered_subscribers
where queue_schema='AQDEMO'
 and queue_name='MYQUEUE';

QUEUE_ID SUBSCRIBER_NAME SUBSCRIBER_TYPE STARTUP_TIME     TOTAL_DEQUEUED_MSG
-------- --------------- --------------- ------------------ ------------------
 73821 SUBSCRIBER1     SUBSCRIBER      03-JAN-09 17:55:22                  0
 73821 SUBSCRIBER2     SUBSCRIBER      03-JAN-09 17:55:22                  0

7. Monitor queues and subscribers

You can query the V$BUFFERED_QUEUE and V$BUFFERED_SUBSCRIBERS fixed views to check the number of messages in the queues and how many messages have been dequeued:

               
connect / as sysdba

alter session set nls_date_format='DD-MON-YY HH24:MI:SS';

col queue_id     format 999999
col startup_time format a18
col num_msgs     format 999,999
col spill_msgs   format 999,999
set lines 100

select   queue_id
    , startup_time
    , sysdate
    , num_msgs
    , spill_msgs
from v$buffered_queues
where queue_schema='AQDEMO'
 and queue_name='MYQUEUE';

QUEUE_ID STARTUP_TIME       SYSDATE            NUM_MSGS SPILL_MSGS
-------- ------------------ ------------------ -------- ----------
 74026 04-JAN-09 00:13:12 04-JAN-09 00:17:17        4          0

set lines 100
col subscriber_name format a15
col subscriber_type format a15
col startup_time    format a18
col total_dequeued_msg format 999,999

select queue_id
   , subscriber_name
   , subscriber_type
   , startup_time
   , total_dequeued_msg
from v$buffered_subscribers
where queue_schema='AQDEMO'
 and queue_name='MYQUEUE';

QUEUE_ID SUBSCRIBER_NAME SUBSCRIBER_TYPE STARTUP_TIME       TOTAL_DEQUEUED_MSG
-------- --------------- --------------- ------------------ ------------------
 74026 SUBSCRIBER1     SUBSCRIBER      04-JAN-09 00:13:12                  4
 74026 SUBSCRIBER2     SUBSCRIBER      04-JAN-09 00:13:12                  0

You can dequeue the message from the other subscriber:
exec demo_dequeue('SUBSCRIBER2');

Message: 00000000000000000001
Message: 00000000000000000002
Message: 00000000000000000003
Message: 00000000000000000004
No more messages

8. Illustrate how you can loose messages by crashing an instance
You can easily show that a buffered messages can be lost. Here is a simple scenario to illustrate that point with a bounce of the Oracle instance:
connect aqdemo/aqdemo
exec demo_enqueue('00000000000000000005');

connect / as sysdba
startup force

connect aqdemo/aqdemo
exec demo_enqueue('00000000000000000006');

set serveroutput on
exec demo_dequeue('SUBSCRIBER1');

Message: 00000000000000000006
No more messages

exec demo_dequeue('SUBSCRIBER2')
Message: 00000000000000000006
No more messages

As you can see above, the message 00000000000000000005 has never been dequeued but is lost; The same scenario illustrates, as expected, that statistics of queue and subscribers usage are reset when an instance is bounced:
connect / as sysdba

alter session set nls_date_format='DD-MON-YY HH24:MI:SS';

col queue_id     format 999999
col startup_time format a18
col num_msgs     format 999,999
col spill_msgs   format 999,999
set lines 100

select queue_id
   , startup_time
   , sysdate
   , num_msgs
   , spill_msgs
from v$buffered_queues
where queue_schema='AQDEMO'
 and queue_name='MYQUEUE';

QUEUE_ID STARTUP_TIME       SYSDATE            NUM_MSGS SPILL_MSGS
-------- ------------------ ------------------ -------- ----------
 74026 04-JAN-09 00:30:03 04-JAN-09 00:33:55        0          0

set lines 100
col subscriber_name format a15
col subscriber_type format a15
col startup_time    format a18
col total_dequeued_msg format 999,999

select queue_id
   , subscriber_name
   , subscriber_type
   , startup_time
   , total_dequeued_msg
from v$buffered_subscribers
where queue_schema='AQDEMO'
 and queue_name='MYQUEUE';

QUEUE_ID SUBSCRIBER_NAME SUBSCRIBER_TYPE STARTUP_TIME      TOTAL_DEQUEUED_MSG
-------- --------------- --------------- ------------------ ------------------
 74026 SUBSCRIBER1     SUBSCRIBER      04-JAN-09 00:30:03                  1
 74026 SUBSCRIBER2     SUBSCRIBER      04-JAN-09 00:30:03                  1

9. Illustrate Flow Control and Spilled Messages
One of the benefit of using buffered messages is that they remain in memory and are not stored on disks. A direct consequence of that is that buffered messages are not logged and as we've seen previously can be lost. The devil being in the details, the memory allocated for the Streams Pool, that store the messages, is not unlimited and compete with other memory pools. To prevent that memory to increase without limit Oracle offers several mechanisms; One is Publisher Flow Control that prevents messages from being enqueued when the messages are not dequeued fast enough. Another one is the ability for messages to spill on disks in the queue table. Here is a simple test case shows these features :
connect aqdemo/aqdemo

begin
 for i in 7..100000 loop
    demo_enqueue(lpad(to_char(i),20,'0'));
 end loop;
end;
/

begin
*
ERROR at line 1:
ORA-25307: Enqueue rate too high, flow control enabled
ORA-06512: at "SYS.DBMS_AQ", line 6
ORA-06512: at "SYS.DBMS_AQ", line 216
ORA-06512: at "AQDEMO.DEMO_ENQUEUE", line 14
ORA-06512: at line 3

If you monitor the content of the queues right after you've enqueued them, you'll see that messages are in the memory associated with the queue:
connect / as sysdba

select queue_id
   , startup_time
   , sysdate
   , num_msgs
   , spill_msgs
from v$buffered_queues
where queue_schema='AQDEMO'
 and queue_name='MYQUEUE';

QUEUE_ID STARTUP_TIME     SYSDATE   NUM_MSGS SPILL_MSGS
-------- ------------------ --------- -------- ----------
 74026 04-JAN-09          04-JAN-09    4,021          0

select MSG_STATE
   , count(*)
from aqdemo.aq$myqueue_table
where queue='MYQUEUE'
group by MSG_STATE;

MSG_STATE COUNT(*)
--------- --------
IN MEMORY     8042

col bytes format 999,999,999
col name  format a30
set pages 1000

select name, bytes
from v$sgastat
where pool='streams pool'
order by name;

NAME                              BYTES
-------------------------- ------------
Sender info                       8,484
deqtree_kgqmctx                      80
fixed allocation callback           260
free memory                   5,702,748
image handles                   337,452
kgqbt_alloc_block                99,456
kgqmdm_fl_2                     241,288
kgqmsub                             144
kodpaih3 image                8,130,416
kwqbcqini:spilledovermsgs         1,968
kwqbdaspl:spilledovermsgs       172,896
kwqbsinfy:bms                   305,368
kwqbsinfy:bqg                       808
kwqbsinfy:mpr                 1,928,180
kwqbsinfy:sta                       208
msgtree_kgqmctx                      80
name_kgqmsub                         32
recov_kgqbtctx                    8,192
recov_kgqmctx                       616
recov_kgqmsub                       336
spilled:kwqbl                     2,316
spilled:kwqbm                     8,624
substree_kgqmctx                     80
time manager index                   80

And you'll also see the publisher is "IN FLOW CONTROL":
select publisher_state
from v$buffered_publishers
where queue_schema='AQDEMO'
 and queue_name='MYQUEUE';

PUBLISHER_STATE
-----------------------------------------------------------
IN FLOW CONTROL: INSUFFICIENT MEMORY AND UNBROWSED MESSAGES

If you wait for a few minutes, you'll see messages start to spill on disks:
select queue_id
   , startup_time
   , sysdate
   , num_msgs
   , spill_msgs
from v$buffered_queues
where queue_schema='AQDEMO'
 and queue_name='MYQUEUE';

QUEUE_ID STARTUP_TIME       SYSDATE   NUM_MSGS SPILL_MSGS
-------- ------------------ --------- -------- ----------
 74026 04-JAN-09          04-JAN-09    4,021      4,021

select MSG_STATE, count(*)
from aqdemo.aq$myqueue_table
where queue='MYQUEUE'
group by MSG_STATE;

MSG_STATE COUNT(*)
--------- --------
SPILLED      8042

You can dequeue the messages as you've done already
connect aqdemo/aqdemo

begin
 for i in 1..5000 loop
    demo_dequeue('SUBSCRIBER1');
    demo_dequeue('SUBSCRIBER2');
 end loop;
end;
/

10. Clean up the environment
connect / as sysdba

drop procedure aqdemo.demo_enqueue;
drop procedure aqdemo.demo_dequeue;

declare
 subscriber       sys.aq$_agent;
begin
 subscriber := sys.aq$_agent('subscriber1',
                             'aqdemo.myqueue',
                             null);
 dbms_aqadm.remove_subscriber(
       queue_name => 'aqdemo.myqueue',
       subscriber =>  subscriber);
 subscriber := sys.aq$_agent('subscriber2',
                             'aqdemo.myqueue',
                              null);
 dbms_aqadm.remove_subscriber(
       queue_name => 'aqdemo.myqueue',
       subscriber =>  subscriber);
end;
/

begin
 dbms_aqadm.stop_queue(
    queue_name => 'aqdemo.myqueue',
    enqueue    => true,
    dequeue    => true);
end;
/

begin
 dbms_aqadm.drop_queue(
    queue_name => 'aqdemo.myqueue');
end;
/

begin
 dbms_aqadm.drop_queue_table(
    queue_table =>'aqdemo.myqueue_table');
end;
/

drop user aqdemo;

drop tablespace aq
   including contents and datafiles;

Related References:
Master Note for Troubleshooting Advanced Queuing and Oracle Streams Propagation Issues [ID 233099.1]
DBA Views:
DBA_QUEUE_SCHEDULES            -              Propagation Schedules
DBA_QUEUES                                    -              Queues in Database
DBA_QUEUE_TABLES                     -              Queue Tables in Database
DBA_QUEUE_SUBSCRIBERS        -              Queue Subscribers in Database
QUEUE_PRIVILEGES                       -              Queues for Which User Has Queue Privilege
AQ$Queue_Table_Name             -              Messages in Queue Table
AQ$Queue_Table_Name_S        -              Queue Subscribers
AQ$Queue_Table_Name_R       -              Queue Subscribers and Their Rules
V$BUFFERED_QUEUES                  -              Buffered Queues in the Instance
V$BUFFERED_SUBSCRIBERS        -              Subscribers for All Buffered Queues in the Instance

For more information on AQ views

http://download.oracle.com/docs/cd/B28359_01/server.111/b28420/aq_views.htm

0 comments »
<< Newer Posts
Older Posts >>