dba_locks does not show for both instances

Problem: While working on blocking session script below, I figured out that blocking sessions are not displayed in both nodes of the rac, exadata.

SELECT
 decode (s2.sid,s1.FINAL_BLOCKING_SESSION,'*'||s2.sid,' '||s2.sid) Blocker_SID
 ,s2.serial# blocker_serial
 ,s2.inst_id blocker_inst_id
 ,s2.con_id Blocker_con_id
 ,s2.status Blocker_Status
 ,s2.username Blocker_User
 ,s2.osuser Blocker_OS_User
 ,s2.process Blocker_OS_PID
 ,s2.sql_id Blocked_sql_id
 ,o.owner Object_owner
 ,o.object_name Locked_Object
 ,o.object_type Locked_Object_Type
 ,l2.lock_type Blocking_Lock_Type
 ,substr(s2.program,1,10) Blocker_Program
 ,s2.client_info Blocker_Client
 ,s1.sid blocked_sid
 ,s1.inst_id blocked_inst_id
 ,s1.con_id Blocker_con_id
 ,s1.status Blocked_Status
 ,s1.username Blocked_User
 ,s1.osuser Blocked_OS_User
 ,s1.process Blocked_OS_PID
 ,s1.sql_id Blocked_sql_id
 ,s1.last_call_et Blocked_Age
 ,substr(s1.program,1,10) Blocked_Program
 ,s1.client_info Blocked_Client
 FROM gv$session s2
 join gv$session s1 ON (s1.blocking_session=s2.sid)
 join dba_objects o on (o.object_id=s1.ROW_WAIT_OBJ#)
 join dba_locks l2 on (s2.sid=l2.session_id)
 left join dba_locks l1 on (s1.sid=l1.session_id and l1.lock_type = l2.lock_type AND l1.lock_id1 = l2.lock_id1 AND l1.lock_id2 = l2.lock_id2)
 where s1.last_call_et>10 and upper(l2.MODE_HELD)='EXCLUSIVE';

Cause: Usual suspect in the from clause are dba_objects, gv$session, and dba_locks. I just look into the dba_locks, because dba_objects is just related with database physical structure which should be common in RAC. gv$session also cannot be, because it is gv$, it must surely include common information among instances again. So, it must be for dba_locks, and there it is,

desc DBA_LOCKS
SESSION_ID NUMBER
LOCK_TYPE VARCHAR2(26)
MODE_HELD VARCHAR2(40)
MODE_REQUESTED VARCHAR2(40)
LOCK_ID1 VARCHAR2(40)
LOCK_ID2 VARCHAR2(40)
LAST_CONVERT NUMBER
BLOCKING_OTHERS VARCHAR2(40)

Solution:

There must be alternative real RAC aware version of it. It is GV$LOCK, some small differences in column names, and values, it has same information, and it shows more for both nodes.

desc GV$LOCK
INST_ID NUMBER
ADDR RAW(8)
KADDR RAW(8)
SID NUMBER
TYPE VARCHAR2(2)
ID1 NUMBER
ID2 NUMBER
LMODE NUMBER
REQUEST NUMBER
CTIME NUMBER
BLOCK NUMBER
CON_ID

Before my script looks like this:

SELECT
 decode (s2.sid,s1.FINAL_BLOCKING_SESSION,'*'||s2.sid,' '||s2.sid) Blocker_SID
 ,s2.serial# blocker_serial
 ,s2.inst_id blocker_inst_id
 ,s2.con_id Blocker_con_id
 ,s2.status Blocker_Status
 ,s2.username Blocker_User
 ,s2.osuser Blocker_OS_User
 ,s2.process Blocker_OS_PID
 ,s2.sql_id Blocked_sql_id
 ,o.owner Object_owner
 ,o.object_name Locked_Object
 ,o.object_type Locked_Object_Type
 ,l2.lock_type Blocking_Lock_Type
 ,substr(s2.program,1,10) Blocker_Program
 ,s2.client_info Blocker_Client
 ,s1.sid blocked_sid
 ,s1.inst_id blocked_inst_id
 ,s1.con_id Blocker_con_id
 ,s1.status Blocked_Status
 ,s1.username Blocked_User
 ,s1.osuser Blocked_OS_User
 ,s1.process Blocked_OS_PID
 ,s1.sql_id Blocked_sql_id
 ,s1.last_call_et Blocked_Age
 ,substr(s1.program,1,10) Blocked_Program
 ,s1.client_info Blocked_Client
 FROM gv$session s2
 join gv$session s1 ON (s1.blocking_session=s2.sid)
 join dba_objects o on (o.object_id=s1.ROW_WAIT_OBJ#)
 join dba_locks l2 on (s2.sid=l2.session_id)
 left join dba_locks l1 on (s1.sid=l1.session_id and l1.lock_type = l2.lock_type AND l1.lock_id1 = l2.lock_id1 AND l1.lock_id2 = l2.lock_id2)
 where s1.last_call_et>10 and upper(l2.MODE_HELD)='EXCLUSIVE';

After making modifications on column names and, associated values, my script turns out to be as follows:

SELECT
 decode (s2.sid,s1.FINAL_BLOCKING_SESSION,'*'||s2.sid,' '||s2.sid) Blocker_SID
 ,s2.serial# blocker_serial
 ,s2.inst_id blocker_inst_id
 ,s2.con_id Blocker_con_id
 ,s2.status Blocker_Status
 ,s2.username Blocker_User
 ,s2.osuser Blocker_OS_User
 ,s2.process Blocker_OS_PID
 ,s2.sql_id Blocked_sql_id
 ,o.owner Object_owner
 ,o.object_name Locked_Object
 ,o.object_type Locked_Object_Type
 ,l2.type Blocking_Lock_Type
 ,substr(s2.program,1,10) Blocker_Program
 ,s2.client_info Blocker_Client
 ,s1.sid blocked_sid
 ,s1.inst_id blocked_inst_id
 ,s1.con_id Blocker_con_id
 ,s1.status Blocked_Status
 ,s1.username Blocked_User
 ,s1.osuser Blocked_OS_User
 ,s1.process Blocked_OS_PID
 ,s1.sql_id Blocked_sql_id
 ,s1.last_call_et Blocked_Age
 ,substr(s1.program,1,10) Blocked_Program
 ,s1.client_info Blocked_Client
 FROM gv$session s2
 join gv$session s1 ON (s1.blocking_session=s2.sid)
 join dba_objects o on (o.object_id=s1.ROW_WAIT_OBJ#)
 join gv$lock l2 on (s2.sid=l2.sid)
 left join gv$lock l1 on (s1.sid=l1.sid and l1.type = l2.type AND l1.id1 = l2.id1 AND l1.id2 = l2.id2)
 where s1.last_call_et>10 and l2.LMODE=6;

 

 

SP2-0575: Use of Oracle SQL feature not in SQL92 Entry Level error

I had encountered “SP2-0575: Use of Oracle SQL feature not in SQL92 Entry Level” error message.

When:

After my session is killed. And issued “set autotrace traceonly” as below:

ERROR at line 8:
ORA-00028: your session has been killed
ORA-00028: your session has been killed
17:38:53 TUNED>set autotrace traceonly
SP2-0575: Use of Oracle SQL feature not in SQL92 Entry Level.
17:39:07 TUNED>set autotrace trace only
SP2-0575: Use of Oracle SQL feature not in SQL92 Entry Level.
17:39:10 TUNED>
17:39:11 TUNED>
17:39:11 TUNED>
17:39:11 TUNED>conn Admin1@mydb1
Enter password:
Connected.
Elapsed: 00:00:00.01
17:39:29 ADMIN1@MYDB:mydb1> set autotrace traceonly
17:39:32 ADMIN1@MYDB:mydb1>

 

Why are we using root cause analysis?

RCA stands for Root-Cause Analysis. Why are we using RCA or specifically, why we are using RCA in operation.

It is clear that documenting, archiving, publishing RCA, helps to solve problems faster in re-occurences of problems.

But the there seems to be a deeper philosophy behind…

While looking at Project Management Quality Management topics in Rita Mulcahy(RIP), I see a good principle which may help us in future problems.

 

The 80 percent of problems are due to 20 percent of the root causes. [Pareto Principle, 80/20 principle by Joseph Juran]

If this is real then solving 20 percent of root causes will solve our 80 percent of our problems. So why not find and deal with those big fishes… Or preventing them before occuring.

PLS-00302 error worths me two cups of Turkish tea!

Problem:

A db user came to me that, something strange is happening, he could not manage to execute a PL/SQL block.

He is getting PLS-00302 error while executing an anonymous PLSQL block.

In PLSQL block, there is a function QUERYFUNC which is already created in the same schema.

Thus giving right with grant option is nonsense.

The weird thing does not happened when I executed in SYSTEM schema.

This is what happens:

DUMMY@MYDB:node1> select DUMMY.QUERYFUNC(‘345354’) from dual;

DUMMY.QUERYFUNC(‘345354’)
—————————————————————————-
X

1 row selected.

DUMMY@MYDB:node1> declare
2
3  RetVal VARCHAR2(200);
4  MYVAR VARCHAR2(200);
5
6  BEGIN
7  MYVAR := ‘12345’;
8
9  RetVal := DUMMY.QUERYFUNC ( MYVAR );
10
11  –DBMS_OUTPUT.Put_Line(‘RetVal = ‘ || DUMMY.QUERYFUNC ( MYVAR ));
12
13  END;
14
15
16
17  /
RetVal := DUMMY.QUERYFUNC ( MYVAR );
*
ERROR at line 9:
ORA-06550: line 9, column 15:
PLS-00302: component ‘QUERYFUNC’ must be declared
ORA-06550: line 9, column 1:
PL/SQL: Statement ignored

Cause:

Below code worked, in which DUMMY schema is not specified.
DUMMY@MYDB:node1> declare
2
3  RetVal VARCHAR2(200);
4  MYVAR VARCHAR2(200);
5
6  BEGIN
7  MYVAR := ‘12345’;
8
9  RetVal := QUERYFUNC ( MYVAR );
10
11  –DBMS_OUTPUT.Put_Line(‘RetVal = ‘ || DUMMY.QUERYFUNC ( MYVAR ));
12
13  END;
14
15  /

PL/SQL procedure successfully completed.

First of all I guessed it was because we have same function somewhere else, or there is synonym object which overrides the QUERYFUNC function.

But after querying dba_objects I could not find any.

At last, the goal comes from other side…

Solution

There was a table named DUMMY in the schema named DUMMY.

Which assumes DUMMY.QUERYFUNC is a table column or something I guess.

Thank you for the solution, Mustafa Saburlu. “Afiyet olsun” for Turkish tea 🙂

How to open a database while restoring and recovering without archivelogs

In other words, how can I open a database, in a case that database is successfully restored, but cannot be recovered due to absence of  archivelogs?

•         The heart of opening the “wounded” database is based on using below init.ora parameters:

_ALLOW_RESETLOGS_CORRUPTION = TRUE    

_ALLOW_READ_ONLY_CORRUPTION = TRUE     

UNDO_MANAGEMENT = MANUAL

Beware of using above parameters that it will be last resort for opening the database.

Further, we asked for _corrupted_rollback_segments parameter to Oracle ACS, thanks Osman Abi:),  he told that we could have been used that parameter in case of corruption on undo segments which may prevent opening database.

•          Restore environment is 11.2.0.3, and source database is 11.2.0.2, therefore we opened the db as if making upgrade by:

alter database open resetlogs upgrade;

 

•          Lesson learnt from the case: 

Monitor archivelog backup health by following:

select sequence#,archived,deleted,backup_count, status from gv$archived_log where sequence#=111430 and inst_id=2;

 

 

DETAILS

While initiating recovery from RMAN, we got below errors:
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 10/09/2012 12:31:11
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of archived log for thread 2 with sequence 111430 and starting SCN of 11345139269565 found to restore
RMAN-06025: no backup of archived log for thread 2 with sequence 111428 and starting SCN of 11344754878547 found to restore
RMAN-06025: no backup of archived log for thread 2 with sequence 111426 and starting SCN of 11344754846270 found to restore
RMAN-06025: no backup of archived log for thread 2 with sequence 111420 and starting SCN of 11344754612059 found to restore
RMAN-06025: no backup of archived log for thread 2 with sequence 111418 and starting SCN of 11344754547555 found to restore
RMAN-06025: no backup of archived log for thread 2 with sequence 111416 and starting SCN of 11344754483883 found to restore
RMAN-06025: no backup of archived log for thread 2 with sequence 111415 and starting SCN of 11344754472813 found to restore
RMAN-06025: no backup of archived log for thread 2 with sequence 111414 and starting SCN of 11344754461578 found to restore
RMAN-06025: no backup of archived log for thread 2 with sequence 111413 and starting SCN of 11344754450683 found to restore
RMAN-06025: no backup of archived log for thread 2 with sequence 111412 and starting SCN of 11344754438403 found to restore
...
 

After investigating the cause of above messages by following:

1-      Whether archive log sequence 111430  exists in controlfile from RMAN connected to target database controlfile by:

oracle@kiev:/zfssa/restore1>rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Tue Oct 9 16:52:27 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: MYDB (DBID=1032014565, not open)
RMAN> list backup of archivelog FROM SEQUENCE 111245 UNTIL SEQUENCE 111247;
using target database control file instead of recovery catalog
specification does not match any backup in the repository
RMAN>


2-      Whether archive log sequence 111430  exists in recovery catalog querying from CATALOG DB metadata by:
RMAN> list backup of archivelog all completed between '2012-09-23 00:00:00' AND '2012-09-24 00:00:00';
List of Backup Sets
===================
BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
199745  11.25M     SBT_TAPE    00:00:10     23.09.2012 00:00:47
BP Key: 205852   Status: AVAILABLE  Compressed: NO  Tag: TAG20120923T000036
Handle: a3nlsh95_1_1   Media: J30643
List of Archived Logs in backup set 199745
Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
---- ------- ---------- ------------------- ---------- ---------
1    83839   11343439796011 22.09.2012 23:30:05 11343439805018 23.09.2012 00:00:03
...
3- Whether the sequence number exists in RMAN log file in /nsr/rman/log.
Not surpsrisingly, with parallel to above checking results,
there is not any entry for relevant archivelog sequence in RMAN log file.

4-  Whether the each archivelog was archived, backed up before deleted; by querying production database:
select sequence#,archived,deleted,backup_count, status
from gv$archived_log where sequence#=111430 and inst_id=2;

SEQUENCE#	ARCHIVED	DELETED	BACKUP_COUNT	STATUS
111430	YES	YES	0	D

As a result, we achieve a conclusion that archivelog file was deleted but not backed up because backup_count is 0.

  

And here comes, incomplete Inconsistent Recovery steps, make recovery without archivelogs:

 Restore controlfile and datafile as usual.

 Change parameter file as below.            

oracle@kiev:/tmp>more /u01/app/oracle/product/11.2.0/dbhome_1/dbs/initMYDB.ora
          *._ALLOW_RESETLOGS_CORRUPTION= TRUE
*._ALLOW_READ_ONLY_CORRUPTION=TRUE
*.cluster_database=false
*.audit_file_dest='/u01/app/oracle/admin/MYDB/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_file_record_keep_time=14
*.control_files='/tmp/ctl_mydb_01.dbf'
*.cursor_sharing='FORCE'
*.db_block_size=32768
*.db_cache_size=0
*.db_create_file_dest='/zfssa/restore1'
*.db_domain=''
*.db_name='MYDB'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=MYDBXDB)'
*.log_archive_dest_1='LOCATION=/zfssa/restore1'
*.memory_target=0
*.open_cursors=300
*.parallel_force_local=FALSE
*.parallel_max_servers=256
*.pga_aggregate_target=1212254720
*.processes=1000
*.remote_login_passwordfile='exclusive'
*.sessions=1105
*.sga_max_size=107374182400
*.sga_target=107374182400
*.shared_pool_size=34359738368
*.undo_tablespace='UNDOTBS1'
*.UNDO_MANAGEMENT=MANUAL

oracle@kiev:/tmp>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Oct 9 19:41:37 2012
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initMYDB.ora';

ORACLE instance started.

Total System Global Area 1,0689E+11 bytes
Fixed Size                2240984 bytes
Variable Size             3,4897E+10 bytes
Database Buffers          7,1941E+10 bytes
Redo Buffers              55267328 bytes

SQL> @bckcf.ctl
ORA-01081: cannot start already-running ORACLE – shut it down first
Control file created.
Database altered.

SQL> alter database open resetlogs upgrade;
Database altered.

Controlfile change content is like below:

oracle@kiev:/tmp>cat bckcf.ctl
-- End of tempfile additions.
--
--     Set #2. RESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- The contents of online logs will be lost and all backups will
-- be invalidated. Use this only if online logs are damaged.
-- WARNING! The current control file needs to be checked against
-- the datafiles to insure it contains the correct files. The
-- commands printed here may be missing log and/or data files.
-- Another report should be made after the database has been
-- successfully opened.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "MYDB" RESETLOGS  ARCHIVELOG
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 32
MAXLOGHISTORY 34824
LOGFILE
GROUP 5 '/zfssa/restore1/redolog5.dbf'  SIZE 500M BLOCKSIZE 512,
GROUP 6 '/zfssa/restore1/redolog6.dbf'  SIZE 500M BLOCKSIZE 512,
GROUP 7 '/zfssa/restore1/redolog7.dbf'  SIZE 500M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/zfssa/restore1/SYSTEM1.dbf',
'/zfssa/restore1/SYSAUX2.dbf',
'/zfssa/restore1/UNDOTBS3.dbf',
'/zfssa/restore1/UNDOTBS9.dbf',
'/zfssa/restore1/UNDOTBS4.dbf',
'/zfssa/restore1/UNDOTBS7.dbf',
'/zfssa/restore1/UNDOTBS8.dbf',
'/zfssa/restore1/UNDOTBS6.dbf',
'/zfssa/restore1/SYSAUX1.dbf',
'/zfssa/restore1/UNDOTBS2.dbf',
'/zfssa/restore1/UNDOTBS1.dbf',
'/zfssa/restore1/TBS_DWH_NODI_B.dbf'
CHARACTER SET WE8ISO8859P9;
-- Create log files for threads other than thread one.
ALTER DATABASE ADD LOGFILE THREAD 2
GROUP 8 '/zfssa/restore1/redolog8.dbf' SIZE 500M BLOCKSIZE 512 REUSE,
GROUP 9 '/zfssa/restore1/redolog9.dbf' SIZE 500M BLOCKSIZE 512 REUSE,
GROUP 10 '/zfssa/restore1/redolog10.dbf' SIZE 500M BLOCKSIZE 512 REUSE;
-- Database can now be opened zeroing the online logs.
--ALTER DATABASE OPEN RESETLOGS;
- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.


Oracle Database Gateway Characterset Problem

Problem

Environment: 11.1 Oracle Database Gateway

We are replacing, and consolidating our Oracle Database Gateway environment.

And after some time passed, we had encountered characterset problem.

– I will call gateway and gw instead of “Oracle Database Gateway” in the rest of this post.

While issuing select from remote sybase db table via new gateway,
we are seeing strange characters instead of Turkish characters.

Cause

After investigating the problem, we found that NLS_LANG environment,
variable in former gateway is different from the new gateway environment.

Solution

There is two alternative way of solving this problem:

First alternative:

Gateway is a listener, and only process you can see associated with listener is:

oracle@gw:>;;ps -ef|grep inherit|grep _MYDB
oracle 21758314 1 0 Sep 13 pts/0 0:01 /u01/app/oracle/product/11.1.0/bin/tnslsnr GW_MYDB_1664 -inherit

So, changing NLS_LANG parameter before starting listener solves the problem.
But, this is not a clean solution, because you need to know and deal with the environment variable before starting the listener.
Putting this to oracle user profile file “oracle@gw:/home/oracle/.profile” does not work for our situation,
because this is a consolidated gateway environment
and there exists other gateway definitions for other oracle to non-oracle databases already configured and running.

Second alternative:

While connecting to Sybase side, there is a parameter file influencing the sybase driver used by the listener.
After adding the HS_LANGUAGE parameter as below it solved the character set problem.

HS_LANGUAGE = AMERICAN_AMERICA.WE8ISO8859P9

Also, there is a lesson while making the trials for seeing the character problem.
It is, after changing the initMYDB.ora parameter file, and stop/starting the gateway listener,
you must close database link and issue the select statement after then.
If you do not close the database link by below SQL command,
it still uses the old initMYDB.ora based configuration.

select * from dual;

commit;

alter session close database link DG_NEMS_PRE;

SELECT * FROM “dbo”.”my_table1″@DG_MYDB_LIVE;

Gateway parameter file for sybase is like below:

/u01/app/oracle/product/11.1.0/dg4sybs/admin/initMYDB.ora
# This is a sample agent init file that contains the HS parameters that are
# needed for the Transparent Gateway for Sybase

HS_FDS_CONNECT_INFO=10.10.44.22:4100/mydb1
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER
HS_FDS_CONNECT_STRING=”EnableSPColumnTypes=2″
HS_FDS_SUPPORT_STATISTICS=FALSE
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_TRANSACTION_MODEL=COMMIT_CONFIRM
HS_FDS_TRACE_FILE_NAME = /home/oracle/derya/dg4sybs_mydb1.trc
HS_LANGUAGE = AMERICAN_AMERICA.WE8ISO8859P9
#
# Environment variables required for Sybase
#
set SYBASE=/u01/app/sybase
set LIBPATH=/u01/app/sybase/OCS-12_5/lib:/u01/app/sybase:/u01/app/oracle/product/11.1.0/lib
HS_FDS_QUOTE_IDENTIFIER=FALSE

Listener.ora entry for the service, given for informational purpose, not really needed for this case:

GW_MYDB_1664 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.2.20) (PORT = 1664))
)
)
)
SID_LIST_GW_MYDB_1664 =
(SID_LIST =
(SID_DESC =
(SID_NAME = MYDB1)
(ORACLE_HOME = /u01/app/oracle/product/11.1.0)
(PROGRAM = dg4sybs)
(ENVS=LIBPATH=/u01/app/oracle/product/11.1.0/dg4sybs/driver/lib:/u01/app/oracle/product/11.1.0/lib)
)
)

 

You may check for gateway configuration from a former post. This post covers configuration steps: from target sybase database  gateway parameter file creation to source oracle database dblink creation. 

https://deryaoktay.wordpress.com/2012/05/22/defining-database-link-by-using-oracle-gateway/

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

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.

Why parallel expdp gives unable to write error

Problem

Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-31693: Table data object “MYSCHEMA”.”MYTABLE” failed to load/unload and is being skipped due to error:
ORA-31617: unable to open dump file “/gecici/expdp/MYSCHEMAS04.expdp” for write
ORA-19505: failed to identify file “/gecici/expdp/MYSCHEMAS04.expdp”
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory

Cause

Error triggered, because it is RAC and consists of two nodes, and the filesystem mounted only on one of the nodes.

I thought that , if datapump had started without parallel option, it would not go to other db node, and everything would go well.
Because it is not the case… Datapump is started in parallel mode, and it tries to start parallel processes in ALL nodes of the cluster.

Solution

In order to prevent to start the parallel process in other node, I changed the parameter parallel_force_local value as true.

And also ensure my TNS entry will allow to go to only to desired node, by giving SID instead of SERVICE_NAME in the connection description as below:

MYDB1=(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = mydb1.turkcell.tgc)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SID = MYDB1)))

But it still continue to give the same error.

Here comes the solution:
In 11.2, CLUSTER parameter has come. It will enable expdp to work properly in RAC as below:

expdp system@MYDB1 SCHEMAS=MYSCHEMA1,MYSCHEMA2,MYSCHEMA3 DIRECTORY=DATAPUMPDIR LOGFILE=MYDB.log DUMPFILE=MYDB%U.expdp COMPRESSION=ALL EXCLUDE=STATISTICS PARALLEL=8 CLUSTER=N

Oracle user cannot write to ASM DATA diskgroup after succesful installation of grid infra in 11.2

Problem:

While restoring controlfile to test environment, from filesystem or tape environment after installing grid infrastructure.

I got following errors from both RMAN  and DBCA, despite the fact that I could create directory alias in asmcmd.

RMAN> run {

2> allocate channel t1 device type ‘SBT_TAPE’

3> parms ‘ENV=(NSR_SERVER=bcksrv1, NSR_CLIENT=mydb, NSR_DATA_VOLUME_POOL=JB10)’;

4> RESTORE controlfile to ‘+DATA’ FROM ‘/tmp/ctl01.dbf’;

5> release channel t1 ;

6> }

allocated channel: t1

channel t1: SID=386 device type=SBT_TAPE

channel t1: NMO v5.0.0.0

Starting restore at 29-MAY-12

released channel: t1

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of restore command at 05/29/2012 14:31:34

ORA-19504: failed to create file “+DATA”

ORA-15045: ASM file name ‘+DATA’ is not in reference form

ORA-17502: ksfdcre:5 Failed to create file +DATA

ORA-15081: failed to submit an I/O operation to a disk

ORA-19600: input file is control file  (/tmp/ctl01.dbf)

ORA-19601: output file is control file  (+DATA)

Cause:

Access rights to oracle disk.

Solution:

I checked the asm disks. /dev/rdsk/disk * has grid:oinstall ownership.

Grid infra home and bin has grid:oinstall, while the oracle home and bin has oracle:oinstall, as told in the MOS.

In our case, access rights on disk device files, are not 77x. As the second 7 means access to group oinstall.

I changed the access rights to 777 and it worked.