active use of diskgroup "DATA" precludes its dismount

Problem

Environment details:

Restore test environment.

HP-UX myhost1 B.11.31 U ia64

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0

I want to drop database by drop database command.

Because, my SYSTEM tablespace needs recovery and I am lack of archive logs, I haven't chance to issue "drop database".

Further I tried to delete files by RMAN, but it does not work either. Because my controlfile also could not be found. Last resort, I tried flushing ASM disk headers by dd, it does not work either, it still shows DATA diskgroup and ASM disks. And finally I tried to drop disk group...

From asmca and asm command line I get below error message while dropping DATA disk group.

SQL>  DROP DISKGROUP data  INCLUDING CONTENTS;

DROP DISKGROUP data  INCLUDING CONTENTS *

ERROR at line 1:

ORA-15039: diskgroup not dropped ORA-15027: active use of diskgroup "DATA" precludes its dismount

Cause

I do not know the real cause, but, as I stated above, it may be because of following reasons:

- lack of controlfile

-SYSTEM tablespace needs media recovery

Solution

I managed to drop disk group by:

alter diskgroup data dismount force;
drop diskgroup data force including contents;

Note that, below trials does not work, I got same error message, despite the fact that the database remains closed state.

srvctl stop diskgroup -g DATA
srvctl disable diskgroup -g DATA
srvctl stop asm

fast_start_parallel_rollback recovery effect

I had a chance to see the effect of recovery while changing the fast_start_parallel_rollback parameter values.

Our database environment:

OS version: 2.6.18-194.el5 #1 SMP Tue Mar 16 21:52:39 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux

DB version: 11.2.0.2

CPU_COUNT = 64

I had used some internal script pari which uses gv$px_session view for finding number of parallel slave processes.

FAST_START_PARALLEL_ROLLBACK = LOW

From the Oracle documentation remember:

• FALSE Parallel rollback is disabled


• LOW Limits the maximum degree of parallelism to 2 * CPU_COUNT


• HIGH Limits the maximum degree of parallelism to 4 * CPU_COUNT

(http://docs.oracle.com/cd/E14072_01/server.112/e10820/initparams083.htm)

ADMIN@MYDB:MYDB2> @pari

INSTANCE             USERNAME                  OSUSER             ACTIVE  INACTIVE SES_COUNT PERCENT
-------------------- ------------------------- --------------- --------- --------- --------- -------
MYDB1-MYDB1      ODI                       MYUSER                                   13  %5,07
                                               oracle                 12       116       128  %50
MYDB2-MYDB2      ODI                       MYUSER               14        11        31  %12,1
INSTANCE             Username                  QC/Slave   SID    SERIAL OSUSER            HASH_VALUE    AUDSID Re Ac MACHINE      STATUS
-------------------- ------------------------- ---------- ------ ------ --------------- ------------ ------------ -- -- ------------ ----------
MYDB1-MYDB1      ODI                       QC         1059   18677  MYUSER          3974800174  10212110  TURKCELL\T   ACTIVE
                                               QC         721    1      oracle                     0        0   MYDB1.bi   ACTIVE
                                               QC         721    1                                 0        0   MYDB2.bi   ACTIVE
MYDB2-MYDB2      ADMIN                     QC         1061   1393   MYUSER2          3551939252  10221613  TURKCELL\T   ACTIVE
INSTANCE             PARALLEL PROCESS INFO
-------------------- ----------------------------------------
MYDB2-MYDB2       USED : 31 AVAILABLE : 225 TOTAL : 256
MYDB1-MYDB1       USED : 141 AVAILABLE : 115 TOTAL : 256
ADMIN@MYDB:MYDB2>
ADMIN@MYDB:MYDB2> select * from gV$FAST_START_TRANSACTIONS;
  INST_ID       USN       SLT       SEQ STATE                                            UNDOBLOCKSDONE UNDOBLOCKSTOTAL       PID   CPUTIME PARENTUSN PARENTSLT PARENTSEQ XID              PXID             RCVSERVERS
--------- --------- --------- --------- ------------------------------------------------ -------------- --------------- --------- --------- --------- --------- --------- ---------------- ---------------- ----------
        1        71       112      9176 RECOVERING                                                 5211      159526       120   137       167        27      4681 47007000D8230000 A7001B0049120000        126
        1       150        21     11387 RECOVERING                                                 4019      155416        85   137       167        27      4681 960015007B2C0000 A7001B0049120000          1
        1       271        25      1662 RECOVERING                                                 2397      148283        89   137       167        27      4681 0F0119007E060000 A7001B0049120000          1
        1        42        41     37080 RECOVERED                                                    28          28              10                               2A002900D8900000                         128
4 rows selected.
-------------

After setting fast_start_parallel_rollback value from 'LOW' to 'FALSE'.

ADMIN@MYDB:MYDB2> alter system set fast_start_parallel_rollback=FALSE scope=both sid=*;
ADMIN@MYDB:MYDB2> select * from GV$FAST_START_TRANSACTIONS;
  INST_ID       USN       SLT       SEQ STATE                                            UNDOBLOCKSDONE UNDOBLOCKSTOTAL       PID   CPUTIME PARENTUSN PARENTSLT PARENTSEQ XID              PXID             RCVSERVERS
--------- --------- --------- --------- ------------------------------------------------ -------------- --------------- --------- --------- --------- --------- --------- ---------------- ---------------- ----------
        1        71       112      9176 RECOVERING                                                    4      153858               1                               47007000D8230000                           0
        1       271        25      1662 RECOVERING                                                    4      145586               1                               0F0119007E060000                           0
        1       150        21     11387 RECOVERING                                                    5      151280               1                               960015007B2C0000                           0
        1        42        41     37080 RECOVERED                                                    28          28              10                               2A002900D8900000                         128
4 rows selected.
ADMIN@MYDB:MYDB2> @pari
INSTANCE             USERNAME                  OSUSER             ACTIVE  INACTIVE SES_COUNT PERCENT
-------------------- ------------------------- --------------- --------- --------- --------- -------
MYDB1-MYDB1      ODI                       MYUSER                                   13  %5,07
MYDB2-MYDB2      ODI                       MYUSER               14        11        31  %12,1
INSTANCE             Username                  QC/Slave   SID    SERIAL OSUSER            HASH_VALUE    AUDSID Re Ac MACHINE      STATUS
-------------------- ------------------------- ---------- ------ ------ --------------- ------------ ------------ -- -- ------------ ----------
MYDB1-MYDB1      ODI                       QC         1059   18677  MYUSER          3974800174  10212110  TURKCELL\T   ACTIVE
MYDB2-MYDB2      ADMIN                     QC         1061   1393   MYUSER2          3551939252  10221613  TURKCELL\T   ACTIVE
INSTANCE             PARALLEL PROCESS INFO
-------------------- ----------------------------------------
MYDB2-MYDB2       USED : 31 AVAILABLE : 225 TOTAL : 256
MYDB1-MYDB1       USED : 13 AVAILABLE : 243 TOTAL : 256
ADMIN@MYDB:MYDB2>

-------------
The recovery is not so fast, so I changed the value of the parameter again.

After setting fast_start_parallel_rollback value from 'FALSE' to 'HIGH'.

Despite the huge number of parallel processes overhead, the outcome is remarkable, that you can see it is faster.

ADMIN@MYDB:MYDB2> alter system set fast_start_parallel_rollback=HIGH scope=both sid=*;
ADMIN@MYDB:MYDB2> select * from GV$FAST_START_TRANSACTIONS;
  INST_ID       USN       SLT       SEQ STATE                                            UNDOBLOCKSDONE UNDOBLOCKSTOTAL       PID   CPUTIME PARENTUSN PARENTSLT PARENTSEQ XID              PXID             RCVSERVERS
--------- --------- --------- --------- ------------------------------------------------ -------------- --------------- --------- --------- --------- --------- --------- ---------------- ---------------- ----------
        1        71       112      9176 RECOVERING                                                 1272       16970        80    24       167        27      4681 47007000D8230000 A7001B0049120000        233
        1       150        21     11387 RECOVERING                                                  651       20408        81    24       167        27      4681 960015007B2C0000 A7001B0049120000          1
        1       271        25      1662 RECOVERING                                                  909       11312        82    24       167        27      4681 0F0119007E060000 A7001B0049120000          1
        1        42        41     37080 RECOVERED                                                    28          28              10                               2A002900D8900000                         128
ADMIN@MYDB:MYDB2> @pari
INSTANCE             USERNAME                  OSUSER             ACTIVE  INACTIVE SES_COUNT PERCENT
-------------------- ------------------------- --------------- --------- --------- --------- -------
MYDB1-MYDB1      ODI                       MYUSER                                   13  %5,07
                                               oracle                243         0       243  %94,92
MYDB2-MYDB2      ODI                       MYUSER               24         0        30  %11,71
INSTANCE             Username                  QC/Slave   SID    SERIAL OSUSER            HASH_VALUE    AUDSID Re Ac MACHINE      STATUS
-------------------- ------------------------- ---------- ------ ------ --------------- ------------ ------------ -- -- ------------ ----------
MYDB1-MYDB1      ODI                       QC         1059   18677  MYUSER                   0  10212110  TURKCELL\T   ACTIVE
                                               QC         721    1      oracle                     0        0   MYDB1.bi   ACTIVE
                                               QC         721    1                                 0        0   MYDB2.bi   ACTIVE
MYDB2-MYDB2      ADMIN                     QC         1061   1393   MYUSER2          3551939252  10221613  TURKCELL\T   ACTIVE
INSTANCE             PARALLEL PROCESS INFO
-------------------- ----------------------------------------
MYDB1-MYDB1       USED : 256 AVAILABLE : 0 TOTAL : 256
MYDB2-MYDB2       USED : 30 AVAILABLE : 226 TOTAL : 256
ADMIN@MYDB:MYDB2>

Why not any sql work parallel in database?

Problem

Environment:

Exadata X2-2

Oracle: 11.2.0.2.3

Linux version:2.6.18-274.18.1.0.1.el5

My customer came to me that the UPDATE statement which used to finish around 30 minutes, now lasts longer and does not finish.

After investigating the issue, the problem turns out to be parallelism problem.

Rephrasing the problem: Sessions cannot work parallel.

For a SQL like below, Oracle EM SQL Monitor detailed screenshot is given:

select /*+ PARALLEL (T 16) */ *from MYSCHEMA.MYTABLE;

Cause

Who knows ...

I checked below areas for discovering for the cause, but nothing found:

- execution plan: There exists PX COORDINATOR lines, no problem.

- parallel hint in the query:Syntax correct, it must work.

- parallel_max_servers: It is 256.

- select * from v$px_process: No rows returned.

- DEGREE value of  table: 16.

- Checking parallelism value for consumer group: 16

- Disabling resource plans: Disabling resource plan does not change the behavior.

- alert.log: No parameter change or internal error in the log.

Click on image, for bigger size.

Solution

Killing user processes, make it work!

It got the parallelism.