Archive

Archive for the ‘problem-cause-solution’ Category

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

March 28, 2013 Leave a comment

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?

February 19, 2013 1 comment

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!

February 13, 2013 Leave a comment

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

October 13, 2012 6 comments

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

September 14, 2012 Leave a comment

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

July 27, 2012 1 comment

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?

July 3, 2012 1 comment

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.

Don Charisma

because anything is possible with Charisma

Carol no Mundo!

Aventuras de uma intercambista a trabalho pelo mundo!

Blog do PHP

Oracle DBA for rookies!

nimaidba

Welcome to the world of Oracle with me....

Tech

News and reviews from the world of gadgets, gear, apps and the web

WordPress.com

WordPress.com is the best place for your personal blog or business site.

Gurcan Orhan's ODI and DWH Blog

Some words about Oracle Data Integrator and Data Warehousing.