Archive

Archive for the ‘Database’ Category

ORA-00600: internal error code, arguments: [ORA_NPI_ERROR], [600], [ORA-00600: internal error code, arguments: [KFCHK_ERRRET], [kfa.c], [3025]

January 2nd, 2014 Matthias Pölzinger No comments

 
Problem description:

You are trying to recursively delete an ASM folder in asmcmd on 12.1.0.1 and are receiving the following ORA-00600:

ASMCMD> rm -rf 2013_12_10
ORA-29261: bad argument
ORA-00600: internal error code, arguments: [ORA_NPI_ERROR], [600], [ORA-00600: internal error code, arguments: [KFCHK_ERRRET], [kfa.c], [3025], [], [], [], [], [], [], [], [], []
], [], [], [], [], [], [], [], [], []
ORA-06512: at line 4 (DBD ERROR: OCIStmtExecute)
ASMCMD>

 
Problem resolution:

This seems to be a currently unresolved issue in 12.1.0.1. Even applying PSU1 will not fix this issue. Currently it is only possible to workaround this issue by not recursively deleting folders and instead delete the files inside:

ASMCMD> rm -f 2013_12_10/*
ASMCMD> ls -l
Type  Redund  Striped  Time             Sys  Name
                                        Y    2013_12_11/
                                        Y    2013_12_12/
                                        Y    2013_12_13/
ASMCMD>

 
System generated folders will be deleted automatically by Oracle ASM if no files are stored inside any longer.

Categories: Database, Grid Infrastructure, Oracle Tags:

Oracle: ORA-00245: control file backup operation failed when trying to perform an actively load balanced RMAN backup in Real Application Cluster

Problem description:

You are running an Oracle Real Application Cluster Version >= 11.2.0.1 and are trying to perform an actively load balanced backup operation on more than one database instance. The backup of the control file fails with the following error message:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
 
RMAN-03009: failure of backup command on dev_0 channel at 06/24/2011 11:11:24
ORA-00245: control file backup operation failed

 
Complete output:

RMAN> run {
  allocate channel 'dev_0' type 'sbt_tape'
    parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=racdb,OB2BARLIST=VTL-ORA-racdb-rac-scan)'
    connect 'sys@racdb1';
  allocate channel 'dev_1' type 'sbt_tape'
    parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=racdb,OB2BARLIST=VTL-ORA-racdb-rac-scan)'
    connect 'sys@racdb2';
 
  send device type 'sbt_tape' 'OB2BARHOSTNAME=rac-scan.intern.initso.at';
  backup incremental level 0 format 'VTL-ORA-racdb-rac-scan<racdb_%s:%t:%p>.dbf' database;
  backup format 'VTL-ORA-racdb-rac-scan<racdb_%s:%t:%p>.dbf' archivelog all not backed up 2 times;
  backup format 'VTL-ORA-racdb-rac-scan<racdb_%s:%t:%p>.dbf' current controlfile;
 } 
 
using target database control file instead of recovery catalog
allocated channel: dev_0
channel dev_0: SID=51 instance=racdb1 device type=SBT_TAPE
channel dev_0: Data Protector A.06.11/PHSS_41802/PHSS_41803/DPSOL_00435/DPLNX_
 
allocated channel: dev_1
channel dev_1: SID=19 instance=racdb2 device type=SBT_TAPE
channel dev_1: Data Protector A.06.11/PHSS_41802/PHSS_41803/DPSOL_00435/DPLNX_
 
sent command to channel: dev_1
sent command to channel: dev_0
 
Starting backup at 24-JUN-11
channel dev_0: starting incremental level 0 datafile backup set
channel dev_0: specifying datafile(s) in backup set
input datafile file number=00002 name=+DATA01/racdb/datafile/sysaux.335.752504897
input datafile file number=00006 name=+DATA01/racdb/datafile/undotbs2.364.753481801
input datafile file number=00008 name=+DATA01/racdb/datafile/undotbs4.362.753482033
input datafile file number=00005 name=+DATA01/racdb/datafile/owbsys.402.753454499
input datafile file number=00010 name=+DATA01/racdb/datafile/undotbs6.360.753482663
channel dev_0: starting piece 1 at 24-JUN-11
channel dev_1: starting incremental level 0 datafile backup set
channel dev_1: specifying datafile(s) in backup set
input datafile file number=00004 name=+DATA01/racdb/datafile/users.332.752504905
input datafile file number=00001 name=+DATA01/racdb/datafile/system.336.752504891
input datafile file number=00003 name=+DATA01/racdb/datafile/undotbs1.334.752504899
input datafile file number=00007 name=+DATA01/racdb/datafile/undotbs3.363.753481931
input datafile file number=00009 name=+DATA01/racdb/datafile/undotbs5.361.753482293
channel dev_1: starting piece 1 at 24-JUN-11
channel dev_0: finished piece 1 at 24-JUN-11
piece handle=VTL-ORA-racdb-rac-scan<racdb_163:754485037:1>.dbf tag=TAG20110624T111037 comment=API Version 2.0,MMS Version 65.6.11.0
channel dev_0: backup set complete, elapsed time: 00:00:46
channel dev_0: starting incremental level 0 datafile backup set
channel dev_0: specifying datafile(s) in backup set
channel dev_1: finished piece 1 at 24-JUN-11
piece handle=VTL-ORA-racdb-rac-scan<racdb_164:754485038:1>.dbf tag=TAG20110624T111037 comment=API Version 2.0,MMS Version 65.6.11.0
channel dev_1: backup set complete, elapsed time: 00:00:45
channel dev_1: starting incremental level 0 datafile backup set
channel dev_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel dev_1: starting piece 1 at 24-JUN-11
RMAN-03009: failure of backup command on dev_0 channel at 06/24/2011 11:11:24
ORA-00245: control file backup operation failed
continuing other job steps, job failed will not be re-run
channel dev_1: finished piece 1 at 24-JUN-11
piece handle=VTL-ORA-racdb-rac-scan<racdb_166:754485083:1>.dbf tag=TAG20110624T111037 comment=API Version 2.0,MMS Version 65.6.11.0
channel dev_1: backup set complete, elapsed time: 00:00:04
released channel: dev_0
released channel: dev_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
 
RMAN-03009: failure of backup command on dev_0 channel at 06/24/2011 11:11:24
ORA-00245: control file backup operation failed
 
RMAN>

 
Cause:

Oracle changed the mechanism on how control file backups are performed. Until 11.2 Oracle requested a control file enqueue before performing the actual backup. Currently with 11.2.0.1 and 11.2.0.2, Oracle performs a control file backup without requesting a control file enqueue, but instead requires that during backup, the snapshot controlfile is shared between all instances. For non-RAC databases this does not change anything and neither it does for RAC databases if you always perform your backup on the same RAC-instance (without allocating any channel on another instance).

But if your are making use of distributing your backup load over several instances and your snapshot control file is located in you non-shared Oracle Home, you will end up with an “ORA-00245: control file backup operation failed”.

 
Problem resolution:

You have to configure your snapshot control file destination to a shared location accessible by all Oracle RAC instances. In our case, an ASM Cluster Filesystem was available due to the storage of external application data.

Change of snapshot controlfile destintation:

RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/acfs_app/snapcf/racdb/snapcf_racdb.f';
 
new RMAN configuration parameters:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/acfs_app/snapcf/racdb/snapcf_racdb.f';
new RMAN configuration parameters are successfully stored
 
RMAN>

 
After changing the snapshot controlfile location to a directory located on ACFS, the backup did execute without any issues:

RMAN> run {
  allocate channel 'dev_0' type 'sbt_tape'
    parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=racdb,OB2BARLIST=VTL-ORA-racdb-rac-scan)'
    connect 'sys@racdb1';
  allocate channel 'dev_1' type 'sbt_tape'
    parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=racdb,OB2BARLIST=VTL-ORA-racdb-rac-scan)'
    connect 'sys@racdb2';
 
  send device type 'sbt_tape' 'OB2BARHOSTNAME=rac-scan.intern.initso.at';
  backup incremental level 0 format 'VTL-ORA-racdb-rac-scan<racdb_%s:%t:%p>.dbf' database;
  backup format 'VTL-ORA-racdb-rac-scan<racdb_%s:%t:%p>.dbf' archivelog all not backed up 2 times;
  backup format 'VTL-ORA-racdb-rac-scan<racdb_%s:%t:%p>.dbf' current controlfile;
 } 
using target database control file instead of recovery catalog
allocated channel: dev_0
channel dev_0: SID=19 instance=racdb1 device type=SBT_TAPE
channel dev_0: Data Protector A.06.11/PHSS_41802/PHSS_41803/DPSOL_00435/DPLNX_
 
allocated channel: dev_1
channel dev_1: SID=19 instance=racdb2 device type=SBT_TAPE
channel dev_1: Data Protector A.06.11/PHSS_41802/PHSS_41803/DPSOL_00435/DPLNX_
 
sent command to channel: dev_1
sent command to channel: dev_0
 
Starting backup at 24-JUN-11
channel dev_0: starting incremental level 0 datafile backup set
channel dev_0: specifying datafile(s) in backup set
input datafile file number=00002 name=+DATA01/racdb/datafile/sysaux.335.752504897
input datafile file number=00006 name=+DATA01/racdb/datafile/undotbs2.364.753481801
input datafile file number=00008 name=+DATA01/racdb/datafile/undotbs4.362.753482033
input datafile file number=00005 name=+DATA01/racdb/datafile/owbsys.402.753454499
input datafile file number=00010 name=+DATA01/racdb/datafile/undotbs6.360.753482663
channel dev_0: starting piece 1 at 24-JUN-11
channel dev_1: starting incremental level 0 datafile backup set
channel dev_1: specifying datafile(s) in backup set
input datafile file number=00004 name=+DATA01/racdb/datafile/users.332.752504905
input datafile file number=00001 name=+DATA01/racdb/datafile/system.336.752504891
input datafile file number=00003 name=+DATA01/racdb/datafile/undotbs1.334.752504899
input datafile file number=00007 name=+DATA01/racdb/datafile/undotbs3.363.753481931
input datafile file number=00009 name=+DATA01/racdb/datafile/undotbs5.361.753482293
channel dev_1: starting piece 1 at 24-JUN-11
channel dev_0: finished piece 1 at 24-JUN-11
piece handle=VTL-ORA-racdb-rac-scan<racdb_167:754485274:1>.dbf tag=TAG20110624T111433 comment=API Version 2.0,MMS Version 65.6.11.0
channel dev_0: backup set complete, elapsed time: 00:00:35
channel dev_0: starting incremental level 0 datafile backup set
channel dev_0: specifying datafile(s) in backup set
including current control file in backup set
channel dev_0: starting piece 1 at 24-JUN-11
channel dev_1: finished piece 1 at 24-JUN-11
piece handle=VTL-ORA-racdb-rac-scan<racdb_168:754485274:1>.dbf tag=TAG20110624T111433 comment=API Version 2.0,MMS Version 65.6.11.0
channel dev_1: backup set complete, elapsed time: 00:00:36
channel dev_1: starting incremental level 0 datafile backup set
channel dev_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel dev_1: starting piece 1 at 24-JUN-11
channel dev_0: finished piece 1 at 24-JUN-11
piece handle=VTL-ORA-racdb-rac-scan<racdb_169:754485309:1>.dbf tag=TAG20110624T111433 comment=API Version 2.0,MMS Version 65.6.11.0
channel dev_0: backup set complete, elapsed time: 00:00:07
channel dev_1: finished piece 1 at 24-JUN-11
piece handle=VTL-ORA-racdb-rac-scan<racdb_170:754485310:1>.dbf tag=TAG20110624T111433 comment=API Version 2.0,MMS Version 65.6.11.0
channel dev_1: backup set complete, elapsed time: 00:00:07
Finished backup at 24-JUN-11
 
Starting backup at 24-JUN-11
current log archived
skipping archived log file +FRA01/racdb/archivelog/2011_06_21/thread_2_seq_32.373.754437613; already backed up 2 time(s)
skipping archived log file +FRA01/racdb/archivelog/2011_06_21/thread_2_seq_33.388.754437685; already backed up 2 time(s)
skipping archived log file +FRA01/racdb/archivelog/2011_06_22/thread_2_seq_34.1310.754445543; already backed up 2 time(s)
skipping archived log file +FRA01/racdb/archivelog/2011_06_22/thread_2_seq_35.1319.754445549; already backed up 2 time(s)
skipping archived log file +FRA01/racdb/archivelog/2011_06_22/thread_2_seq_36.1323.754445553; already backed up 2 time(s)
skipping archived log file +FRA01/racdb/archivelog/2011_06_22/thread_2_seq_37.1326.754445557; already backed up 2 time(s)
skipping archived log file +FRA01/racdb/archivelog/2011_06_22/thread_2_seq_38.953.754462269; already backed up 2 time(s)
skipping archived log file +FRA01/racdb/archivelog/2011_06_22/thread_2_seq_39.479.754483331; already backed up 2 time(s)
skipping archived log file +FRA01/racdb/archivelog/2011_06_21/thread_3_seq_40.859.754427105; already backed up 2 time(s)
skipping archived log file +FRA01/racdb/archivelog/2011_06_22/thread_3_seq_41.1316.754445547; already backed up 2 time(s)
skipping archived log file +FRA01/racdb/archivelog/2011_06_22/thread_3_seq_42.1328.754445559; already backed up 2 time(s)
skipping archived log file +FRA01/racdb/archivelog/2011_06_22/thread_3_seq_43.1247.754462267; already backed up 2 time(s)
skipping archived log file +FRA01/racdb/archivelog/2011_06_22/thread_3_seq_44.972.754483331; already backed up 2 time(s)
skipping archived log file +FRA01/racdb/archivelog/2011_06_21/thread_4_seq_30.885.754434005; already backed up 2 time(s)
skipping archived log file +FRA01/racdb/archivelog/2011_06_22/thread_4_seq_31.1315.754445545; already backed up 2 time(s)
skipping archived log file +FRA01/racdb/archivelog/2011_06_22/thread_4_seq_32.1332.754445559; already backed up 2 time(s)
skipping archived log file +FRA01/racdb/archivelog/2011_06_22/thread_4_seq_33.791.754462267; already backed up 2 time(s)
skipping archived log file +FRA01/racdb/archivelog/2011_06_22/thread_4_seq_34.276.754483333; already backed up 2 time(s)
skipping archived log file +FRA01/racdb/archivelog/2011_06_21/thread_5_seq_29.1220.754434005; already backed up 2 time(s)
skipping archived log file +FRA01/racdb/archivelog/2011_06_22/thread_5_seq_30.1313.754445543; already backed up 2 time(s)
skipping archived log file +FRA01/racdb/archivelog/2011_06_22/thread_5_seq_31.1329.754445559; already backed up 2 time(s)
skipping archived log file +FRA01/racdb/archivelog/2011_06_22/thread_5_seq_32.723.754462267; already backed up 2 time(s)
skipping archived log file +FRA01/racdb/archivelog/2011_06_22/thread_5_seq_33.901.754483331; already backed up 2 time(s)
skipping archived log file +FRA01/racdb/archivelog/2011_06_21/thread_6_seq_26.386.754437615; already backed up 2 time(s)
skipping archived log file +FRA01/racdb/archivelog/2011_06_22/thread_6_seq_27.1322.754445551; already backed up 2 time(s)
skipping archived log file +FRA01/racdb/archivelog/2011_06_22/thread_6_seq_28.453.754462267; already backed up 2 time(s)
skipping archived log file +FRA01/racdb/archivelog/2011_06_22/thread_6_seq_29.889.754483333; already backed up 2 time(s)
channel dev_0: starting archived log backup set
channel dev_0: specifying archived log(s) in backup set
input archived log thread=2 sequence=40 RECID=212 STAMP=754483597
input archived log thread=3 sequence=45 RECID=208 STAMP=754483595
input archived log thread=5 sequence=34 RECID=209 STAMP=754483595
input archived log thread=6 sequence=30 RECID=210 STAMP=754483596
input archived log thread=4 sequence=35 RECID=211 STAMP=754483596
channel dev_0: starting piece 1 at 24-JUN-11
channel dev_1: starting archived log backup set
channel dev_1: specifying archived log(s) in backup set
input archived log thread=3 sequence=46 RECID=216 STAMP=754485320
input archived log thread=5 sequence=35 RECID=217 STAMP=754485321
input archived log thread=6 sequence=31 RECID=213 STAMP=754485319
channel dev_1: starting piece 1 at 24-JUN-11
channel dev_0: finished piece 1 at 24-JUN-11
piece handle=VTL-ORA-racdb-rac-scan<racdb_171:754485324:1>.dbf tag=TAG20110624T111523 comment=API Version 2.0,MMS Version 65.6.11.0
channel dev_0: backup set complete, elapsed time: 00:00:07
channel dev_0: starting archived log backup set
channel dev_0: specifying archived log(s) in backup set
input archived log thread=4 sequence=36 RECID=214 STAMP=754485319
input archived log thread=2 sequence=41 RECID=215 STAMP=754485320
channel dev_0: starting piece 1 at 24-JUN-11
channel dev_1: finished piece 1 at 24-JUN-11
piece handle=VTL-ORA-racdb-rac-scan<racdb_172:754485324:1>.dbf tag=TAG20110624T111523 comment=API Version 2.0,MMS Version 65.6.11.0
channel dev_1: backup set complete, elapsed time: 00:00:07
channel dev_0: finished piece 1 at 24-JUN-11
piece handle=VTL-ORA-racdb-rac-scan<racdb_173:754485331:1>.dbf tag=TAG20110624T111523 comment=API Version 2.0,MMS Version 65.6.11.0
channel dev_0: backup set complete, elapsed time: 00:00:07
Finished backup at 24-JUN-11
 
 
Starting backup at 24-JUN-11
channel dev_0: starting full datafile backup set
channel dev_0: specifying datafile(s) in backup set
including current control file in backup set
channel dev_0: starting piece 1 at 24-JUN-11
channel dev_0: finished piece 1 at 24-JUN-11
piece handle=VTL-ORA-racdb-rac-scan<racdb_174:754485339:1>.dbf tag=TAG20110624T111539 comment=API Version 2.0,MMS Version 65.6.11.0
channel dev_0: backup set complete, elapsed time: 00:00:07
Finished backup at 24-JUN-11
 
released channel: dev_0
 
released channel: dev_1
 
RMAN>

Oracle: “ORA-12545: Connect failed because target host or object does not exist” when trying to connect through SCAN-Listeners

Problem description:

You are trying to connect to a database and are receiving the following error message:

[oracle@ls01 admin]$ sqlplus system@racdb
 
SQL*Plus: Release 10.2.0.5.0 - Production on Sat Jun 18 08:47:47 2011
 
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.
 
Enter password: 
ERROR:
ORA-12545: Connect failed because target host or object does not exist
 
 
Enter user-name:

 
Cause:

Normally this error is generated by specifying a wrong hostname with the ADDRESS parameters or by specifying a hostname which can not be looked up:

12545, 00000, "Connect failed because target host or object does not exist"
// *Cause: The address specified is not valid, or the program being 
// connected to does not exist.
// *Action: Ensure the ADDRESS parameters have been entered correctly; the
// most likely incorrect parameter is the node name.  Ensure that the 
// executable for the server exists (perhaps "oracle" is missing.)
// If the protocol is TCP/IP, edit the TNSNAMES.ORA file to change the
// host name to a numeric IP address and try again.

 
In this case, we have a tnsnames.ora-entry working fully functional on the database servers and even using IP-addresses:

RACDB =
  (DESCRIPTION =
    (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.200.101)(PORT = 1521))
     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.200.102)(PORT = 1521))
     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.200.103)(PORT = 1521))
    )
    (LOAD_BALANCE = yes)
    (CONNECT_DATA =
      (SERVICE_NAME = app199.db.initso.at)
      (SERVER = DEDICATED)
      (FAILOVER_MODE=
        (TYPE=select)
        (METHOD=basic)
        (RETRIES=20)
        (DELAY=15)
      )
    )
  )

 
Above mentioned tnsnames.ora-entry is used to connect to a remote 11g Release 2 RAC-database by utilizing the newly introduced SCAN-Listeners (IP-addresses necessary for clients < 11.2). The tnsnames.ora-entry works fine for clients in the local area network, but if you are trying to connect from another location, you are receiving an ORA-12545 (even though you are not using any hostname).

This is caused by making use of the SCAN-Listeners from a remote site. The SCAN-Listeners will route you to a “normal” VIP-Listener in order to spawn the connection. This routing is based on hostnames and not IP-addresses. If the client receives the hostname for the VIP-Listener to connect to and it is unable to resolve it, you will also see the ORA-12545.

 
Problem resolution:

Enter all Oracle 11g Release 2 RAC relevant IP-addresses and hostnames in your DNS server or your hosts-file.

Example for hosts-file:

192.168.200.201		rac01.initso.at rac01
192.168.200.202		rac02.initso.at rac02
192.168.200.203		rac03.initso.at rac03
 
192.168.200.211		rac01-vip.initso.at rac01-vip
192.168.200.212		rac02-vip.initso.at rac02-vip
192.168.200.213		rac03-vip.initso.at rac03-vip
 
192.168.200.101		rac-scan.initso.at
192.168.200.102		rac-scan.initso.at
192.168.200.103		rac-scan.initso.at

 
After configuring all appropriate hosts-file entries, you should be able to connect without any issue:

[oracle@ls01 admin]$ sqlplus username/password@RACDB
 
SQL*Plus: Release 10.2.0.5.0 - Production on Sat Jun 18 09:01:38 2011
 
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.
 
 
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
 
SQL> select instance_number, instance_name from v$instance;
 
INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
	      1 rac1
 
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
[oracle@ls01 admin]$

Oracle RMAN: Duplicate database fails with “RMAN-06457: UNTIL scn (4321) is ahead of last scn in archived logs (1234)” or provides an old image of the database

June 16th, 2011 Matthias Pölzinger 1 comment

Problem description:

You are trying to duplicate a database and you are always ending up with the same old image or you are receiving the following error message during restore:

Starting Duplicate Db at 05-JUN-2011 22:50:27
released channel: stb1
released channel: stb2
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure OF Duplicate Db command at 06/05/2009 22:51:26
RMAN-06457: UNTIL scn (4321) IS ahead OF LAST scn IN archived logs (1234)

 
Cause:

You are duplicating a RAC database with more than one redo log threads. If one of the threads is in “CLOSED”-state you will either receive above mentioned error message or end up with an old duplicated image (because duplicate database will use an scn <= the latest changes from the closed thread).

Example output from v$log:

SQL> SELECT thread#, STATUS, instance FROM v$thread ORDER BY 1;
 
   THREAD# STATUS INSTANCE
---------- ------ --------------------
	 1 OPEN   rac1
	 2 CLOSED rac2
	 3 OPEN   rac3
	 4 OPEN   rac4
 
SQL>

 
Problem resolution:

Either

  1. drop the closed thread if it is no longer needed.

    Example:

    SQL> ALTER DATABASE disable thread 2;
     
    DATABASE altered.
     
    SQL>
    SQL> ALTER DATABASE DROP logfile GROUP 201;
     
    DATABASE altered.
     
    SQL> ALTER DATABASE DROP logfile GROUP 202;
     
    DATABASE altered.
     
    SQL> ALTER DATABASE DROP logfile GROUP 203;
     
    DATABASE altered.
     
    SQL>

     
    v$thread will now no longer list the thread:

    SQL> SELECT thread#, STATUS, instance FROM v$thread ORDER BY 1;
     
       THREAD# STATUS INSTANCE
    ---------- ------ --------------------
    	 1 OPEN   rac1
    	 3 OPEN   rac3
    	 4 OPEN   rac4
     
    SQL>

     

  2. open the thread by starting the currently stopped RAC database instance and perform a log switch

    Example:

    [oracle@rac1 ~]$ srvctl start instance -d rac -i rac2
    [oracle@rac1 ~]$

     
    v$thread will now list the thread as open:

    SQL> SELECT thread#, STATUS, instance FROM v$thread ORDER BY 1;
     
       THREAD# STATUS INSTANCE
    ---------- ------ --------------------
    	 1 OPEN   rac1
     	 2 OPEN   rac2
    	 3 OPEN   rac3
    	 4 OPEN   rac4
     
    SQL>

     
    After archiving the redo logs, reperform your duplicate database.

Oracle: ORA-01775: looping chain of synonyms when querying a public synonym

Problem description:
You are trying to execute a query which raises the error ORA-01775:

SQL> SELECT * FROM mytestname;
SELECT * FROM mytestname
              *
ERROR at line 1:
ORA-01775: looping chain OF synonyms
 
 
SQL>

 
Cause:
If a synonym is unable to translate to the described object, because it is missing, Oracle normally would raise an “ORA-00980: synonym translation is no longer valid” error.

Example:

SQL> CREATE public synonym pubsynonym10 FOR system.asdfasdasdf;
 
Synonym created.
 
SQL> SELECT * FROM pubsynonym10;
SELECT * FROM pubsynonym10
              *
ERROR at line 1:
ORA-00980: synonym translation IS no longer valid
 
 
SQL>

 
But with 10.1 onwards, Oracle raises an “ORA-01775: looping chain of synonyms” if the accessed public synonym and the missing base table share the same name.

Example:

SQL> SHOW USER
USER IS "SYS"
SQL> 
SQL> CREATE public synonym mytestname FOR system.mytestname;
 
Synonym created.
 
SQL> 
SQL> SELECT * FROM mytestname;
SELECT * FROM mytestname
              *
ERROR at line 1:
ORA-01775: looping chain OF synonyms
 
 
SQL>

 
Problem resolution:

Check the existance of the base object referenced and fix the no longer valid translation.

Categories: Database, Oracle Tags:

Oracle: impdp raises ORA-39083: Object type PROCOBJ failed to create with error: ORA-01843: not a valid month

Problem description:

You are importing a data pump dumpfile and impdp raises the following error(s) when trying to create a job or schedule:

ORA-39083: Object TYPE PROCOBJ failed TO CREATE WITH error:
ORA-01843: NOT a valid MONTH
Failing SQL IS:
BEGIN
... dbms_scheduler commands ...
COMMIT;
END;

Cause:

The exporting data pump session used different NLS-settings than your currently importing one.

DBMS_SCHEDULER invokes your current NLS-settings when creating jobs/schedules. For instance, if your environment variable NLS_LANG was set to AMERICAN_AMERICA.WE8ISO8859P1 for your export, you should use the same setting during import. Otherwise creating scheduler jobs/schedules will raise above mentioned error messages.

Problem resolution:

You should use equivalent NLS_LANG settings in your environment when performing expdp and impdp. This will workaround the issue.

Categories: Data Pump, Database, Oracle, Utilities Tags:

Oracle Database: HOWTO determine the granule size on your release/platform

Problem description:

Upon starting an Oracle Database Instance, memory gets allocated in granule sized memory units. If you set the sga_target parameter to a specific value (e.g. 2500MB), it will be rounded up to the next full granule (e.g. 2512MB on platforms with 16MB granule size).

The granule size depends on the platform you are running Oracle on, which version you are using and your SGA size.

Problem resolution:

To determine the granule size for your Oracle Database instance, just execute the following query:

SELECT * FROM v$sgainfo WHERE name = 'Granule Size';

Example for 11.2.0.2 on AIX:

oracle@aix01 /home/oracle # sqlplus "/ as sysdba"
 
SQL*Plus: Release 11.2.0.2.0 Production ON Mon May 22 13:18:16 2011
 
Copyright (c) 1982, 2010, Oracle.  ALL rights reserved.
 
 
Connected TO:
Oracle DATABASE 11g Release 11.2.0.2.0 - 64bit Production
WITH the REAL Application Clusters AND Automatic Storage Management options
 
SQL> SELECT * FROM v$sgainfo WHERE name = 'Granule Size';
 
NAME                                  BYTES RES
-------------------------------- ---------- ---
Granule SIZE                       16777216 No
 
SQL>
Categories: Database, Oracle Tags:

Oracle Grid Infrastructure on AIX: “0403-006 Execute permission denied.” during installation of binaries.

Problem description:

While installing Grid Infrastructure on AIX, you are receiving an error when Oracle Universal Installer starts to link the binaries. The OUI log file contains the error message “0403-006 Execute permission denied.”:

INFO: Exception thrown from action: make
Exception Name: MakefileException
Exception String: Error in invoking target 'sdo_on no_opts asm_on rac_on' of makefile '/u01/app/grid/11.2.0/grid/rdbms/lib/ins_rdbms.mk'. See '/u01/app/oraInventory/logs/installActions2011-03-12_11-32-45PM.log' for details.
Exception Severity: 1
INFO: Linking sdo Options
INFO: Linking sdo Options
INFO: The output of this make operation is also available at: '/u01/app/grid/11.2.0/grid/install/make.log'
INFO:
 
INFO: Start output from spawned process:
INFO: ----------------------------------
INFO:
 
INFO:   /bin/ar -X64 cr /u01/app/grid/11.2.0/grid/rdbms/lib/libknlopt.a /u01/app/grid/11.2.0/grid/rdbms/lib/kxmwsd.o
 
INFO: /bin/sh: /u01/app/grid/11.2.0/grid/bin/echodo: 0403-006 Execute permission denied.
 
INFO: make: 1254-004 The error code from the last command is 126.

Cause:
In this case the error message was quite misleading. The problem was not caused by a permission issue. It was introduced by another process using some space on the /tmp file system. Oracle Universal Installer was not able to allocate any further temporary space on the /tmp file system, because it was completely filled up and therefore raised above error message. Even if you would use the environment variables TMPDIR and TEMPDIR, OUI would still use /tmp for some tasks and 1GB of free space would not be enough.

Problem resolution:
Increase the free space on /tmp (2GB of total space should be fine) and press retry in the Oracle Universal Installer or cleanup and restart your installation if you have already cancelled it.

root@aix01 / # df -g /tmp
Filesystem    GB blocks      Free %Used    Iused %Iused Mounted on
/dev/hd3           2.00      1.99    1%      151     1% /tmp
root@aix01 / #

Oracle: HOWTO run the SQL Tuning Advisor without Enterprise Manager DB Console or Grid Control

Problem description:

You want to run the SQL Tuning Advisor, because of a very resource consuming SQL statement, but no Enterprise Manager component is installed or configured for performance reasons or because of troubles in the past.

Problem resolution:
It is not necessary to run Enterprise Manager in order to make use of the SQL Tuning Advisor. Here is a short how to for running the SQL Tuning Advisor on a SQL statement currently using a lot of resources after updating the schema statistics.

  • First of all create a tuning task:

    DECLARE
      l_sql_tune_task_id  VARCHAR2(100);
    BEGIN
      l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                              sql_id      => '&&my_sql_id',
                              scope       => DBMS_SQLTUNE.scope_comprehensive,
                              time_limit  => 60,
                              task_name   => 'sql_tuning_task_&&my_sql_id',
                              description => 'Tuning task for statement &&my_sql_id.');
      DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
    END;
    /

    Output:

    SQL> SET serveroutput ON
    SQL> 
    SQL> DECLARE
      2   l_sql_tune_task_id  VARCHAR2(100);
      3  BEGIN
      4    l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
      5                            sql_id      => '&&my_sql_id',
      6                            scope       => DBMS_SQLTUNE.scope_comprehensive,
      7                            time_limit  => 60,
      8                            task_name   => 'sql_tuning_task_&&my_sql_id',
      9                            description => 'Tuning task for statement &&my_sql_id.');
     10    DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
     11  END;
     12  /
     
    Enter VALUE FOR my_sql_id: 2vbmgruzfy58y
    old   5:			   sql_id      => '&&my_sql_id',
    NEW   5:			   sql_id      => '2vbmgruzfy58y',
    old   8:			   task_name   => 'sql_tuning_task_&&my_sql_id',
    NEW   8:			   task_name   => 'sql_tuning_task_2vbmgruzfy58y',
    old   9:			   description => 'Tuning task for statement &&my_sql_id.');
    NEW   9:			   description => 'Tuning task for statement 2vbmgruzfy58y');
    l_sql_tune_task_id: sql_tuning_task_2vbmgruzfy58y
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>

  • If you want, you can check the status of your newly created tuning task by querying dba_advisor_log:

    SELECT task_name, STATUS FROM dba_advisor_log WHERE task_name LIKE 'sql_tuning_task_&&my_sql_id';

    Output:

    SQL> SELECT task_name, STATUS FROM dba_advisor_log WHERE task_name LIKE 'sql_tuning_task_&&my_sql_id';
    OLD   1: SELECT task_name, STATUS FROM dba_advisor_log WHERE task_name LIKE 'sql_tuning_task_&&my_sql_id'
    NEW   1: SELECT task_name, STATUS FROM dba_advisor_log WHERE task_name LIKE 'sql_tuning_task_2vbmgruzfy58y'
     
    TASK_NAME		       STATUS
    ------------------------------ -----------
    sql_tuning_task_2vbmgruzfy58y  INITIAL
     
    SQL>

  • Next step is to actually execute your SQL Tuning task:

    EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'sql_tuning_task_&&my_sql_id');

    Output:

    SQL> EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'sql_tuning_task_&&my_sql_id');
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>

  • Status will change from INITIAL to COMPLETED:

    SELECT task_name, STATUS FROM dba_advisor_log WHERE task_name LIKE 'sql_tuning_task_&&my_sql_id';

    Output:

    SQL> SELECT task_name, STATUS FROM dba_advisor_log WHERE task_name LIKE 'sql_tuning_task_&&my_sql_id';
    OLD   1: SELECT task_name, STATUS FROM dba_advisor_log WHERE task_name LIKE 'sql_tuning_task_&&my_sql_id'
    NEW   1: SELECT task_name, STATUS FROM dba_advisor_log WHERE task_name LIKE 'sql_tuning_task_2vbmgruzfy58y'
     
    TASK_NAME		       STATUS
    ------------------------------ -----------
    sql_tuning_task_2vbmgruzfy58y  COMPLETED
     
    SQL>

  • Now you can review the recommendations generated by the SQL Tuning Advisor:

    SET LINES 150
    SET pages 50000
    SET long 5000000
    SET longc 5000000
     
    SELECT DBMS_SQLTUNE.report_tuning_task('sql_tuning_task_&&my_sql_id') AS recommendations FROM dual;

    Output:

    SQL> SET LINES 150
    SQL> SET pages 50000
    SQL> SET long 5000000
    SQL> SET longc 5000000
    SQL> 
    SQL> SELECT DBMS_SQLTUNE.report_tuning_task('sql_tuning_task_&&my_sql_id') AS recommendations FROM dual;
    OLD   1: SELECT DBMS_SQLTUNE.report_tuning_task('sql_tuning_task_&&my_sql_id') AS recommendations FROM dual
    NEW   1: SELECT DBMS_SQLTUNE.report_tuning_task('sql_tuning_task_2vbmgruzfy58y') AS recommendations FROM dual
     
    RECOMMENDATIONS
    ------------------------------------------------------------------------------------------------------------------------------------------------------
    GENERAL INFORMATION SECTION
    -------------------------------------------------------------------------------
    Tuning Task Name   : sql_tuning_task_2vbmgruzfy58y
    Tuning Task Owner  : SYS
    Workload TYPE	   : Single SQL Statement
    Scope		   : COMPREHENSIVE
    TIME LIMIT(seconds): 60
    Completion STATUS  : COMPLETED
    Started at	   : 05/20/2011 09:44:13
    Completed at	   : 05/20/2011 09:44:33
     
    -------------------------------------------------------------------------------
    Schema Name: SYS
    SQL ID	   : 2vbmgruzfy58y
    SQL Text   : SELECT COUNT(*) FROM dba_tables, dba_users
     
    -------------------------------------------------------------------------------
    FINDINGS SECTION (3 findings)
    -------------------------------------------------------------------------------
     
    1- Restructure SQL finding (see plan 1 IN EXPLAIN plans SECTION)
    ----------------------------------------------------------------
      An expensive cartesian product operation was found at line ID 24 OF the
      execution plan.
     
      Recommendation
      --------------
      - Consider removing the disconnected TABLE OR VIEW FROM this statement OR
        ADD a JOIN condition which refers TO it.
     
    2- Restructure SQL finding (see plan 1 IN EXPLAIN plans SECTION)
    ----------------------------------------------------------------
      An expensive cartesian product operation was found at line ID 17 OF the
      execution plan.
     
      Recommendation
      --------------
      - Consider removing the disconnected TABLE OR VIEW FROM this statement OR
        ADD a JOIN condition which refers TO it.
     
    3- Restructure SQL finding (see plan 1 IN EXPLAIN plans SECTION)
    ----------------------------------------------------------------
      An expensive cartesian product operation was found at line ID 15 OF the
      execution plan.
     
      Recommendation
      --------------
      - Consider removing the disconnected TABLE OR VIEW FROM this statement OR
        ADD a JOIN condition which refers TO it.
     
    -------------------------------------------------------------------------------
    EXPLAIN PLANS SECTION
    -------------------------------------------------------------------------------
     
    1- Original
    -----------
    Plan hash VALUE: 674672025
     
    ------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation				     | Name		       | ROWS  | Bytes | Cost (%CPU)| TIME     |
    ------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT			     |			       |     1 |   227 |   380	 (3)| 00:00:05 |
    |   1 |  SORT AGGREGATE 			     |			       |     1 |   227 |	    |	       |
    |*  2 |   HASH JOIN				     |			       | 15530 |  3442K|   380	 (3)| 00:00:05 |
    |   3 |    TABLE ACCESS FULL			     | TS$		       |     7 |    21 |     4	 (0)| 00:00:01 |
    |*  4 |    HASH JOIN RIGHT OUTER		     |			       | 15530 |  3397K|   376	 (3)| 00:00:05 |
    |   5 |     INDEX FULL SCAN			     | I_USER2		       |    45 |   180 |     1	 (0)| 00:00:01 |
    |*  6 |     HASH JOIN RIGHT OUTER		     |			       | 15530 |  3336K|   374	 (3)| 00:00:05 |
    |   7 |      INDEX FAST FULL SCAN		     | I_OBJ1		       | 15494 |   121K|    16	 (0)| 00:00:01 |
    |*  8 |      HASH JOIN RIGHT OUTER		     |			       | 15530 |  3215K|   357	 (2)| 00:00:05 |
    |   9 |       INDEX FAST FULL SCAN		     | I_OBJ1		       | 15494 | 77470 |    16	 (0)| 00:00:01 |
    |* 10 |       HASH JOIN RIGHT OUTER		     |			       | 15530 |  3139K|   341	 (3)| 00:00:05 |
    |  11 |        TABLE ACCESS FULL		     | SEG$		       |  4081 | 44891 |    34	 (0)| 00:00:01 |
    |* 12 |        HASH JOIN			     |			       |  9971 |  1908K|   306	 (2)| 00:00:04 |
    |* 13 | 	TABLE ACCESS FULL		     | TAB$		       |  1200 | 33600 |   223	 (0)| 00:00:03 |
    |* 14 | 	HASH JOIN			     |			       |   128K|    20M|    82	 (7)| 00:00:01 |
    |  15 | 	 MERGE JOIN CARTESIAN		     |			       |   374 | 58718 |    28	(11)| 00:00:01 |
    |* 16 | 	  HASH JOIN			     |			       |     8 |  1224 |    24	(13)| 00:00:01 |
    |  17 | 	   MERGE JOIN CARTESIAN 	     |			       |     8 |  1120 |    24	(13)| 00:00:01 |
    |* 18 | 	    HASH JOIN			     |			       |     8 |   680 |    24	(13)| 00:00:01 |
    |* 19 | 	     HASH JOIN			     |			       |     8 |   656 |    19	(11)| 00:00:01 |
    |* 20 | 	      HASH JOIN 		     |			       |     8 |   632 |    15	(14)| 00:00:01 |
    |* 21 | 	       HASH JOIN OUTER		     |			       |     8 |   608 |    12	 (9)| 00:00:01 |
    |* 22 | 		HASH JOIN		     |			       |     8 |   376 |    10	(10)| 00:00:01 |
    |  23 | 		 NESTED LOOPS		     |			       |     8 |   360 |     7	 (0)| 00:00:01 |
    |  24 | 		  MERGE JOIN CARTESIAN	     |			       |     1 |    16 |     4	 (0)| 00:00:01 |
    |* 25 | 		   TABLE ACCESS FULL	     | PROFILE$ 	       |     1 |     8 |     2	 (0)| 00:00:01 |
    |  26 | 		   BUFFER SORT		     |			       |     1 |     8 |     2	 (0)| 00:00:01 |
    |* 27 | 		    TABLE ACCESS FULL	     | PROFILE$ 	       |     1 |     8 |     2	 (0)| 00:00:01 |
    |* 28 | 		  TABLE ACCESS BY INDEX ROWID| USER$		       |    12 |   348 |     3	 (0)| 00:00:01 |
    |* 29 | 		   INDEX SKIP SCAN	     | I_USER2		       |    12 |       |     1	 (0)| 00:00:01 |
    |  30 | 		 TABLE ACCESS FULL	     | PROFNAME$	       |     1 |     2 |     2	 (0)| 00:00:01 |
    |* 31 | 		TABLE ACCESS FULL	     | RESOURCE_GROUP_MAPPING$ |     1 |    29 |     2	 (0)| 00:00:01 |
    |  32 | 	       TABLE ACCESS FULL	     | USER_ASTATUS_MAP        |     9 |    27 |     2	 (0)| 00:00:01 |
    |  33 | 	      TABLE ACCESS FULL 	     | TS$		       |     7 |    21 |     4	 (0)| 00:00:01 |
    |  34 | 	     TABLE ACCESS FULL		     | TS$		       |     7 |    21 |     4	 (0)| 00:00:01 |
    |  35 | 	    BUFFER SORT 		     |			       |     1 |    55 |    20	(15)| 00:00:01 |
    |* 36 | 	     FIXED TABLE FULL		     | X$KSPPI		       |     1 |    55 |     0	 (0)| 00:00:01 |
    |  37 | 	   FIXED TABLE FULL		     | X$KSPPCV 	       |   100 |  1300 |     0	 (0)| 00:00:01 |
    |  38 | 	  BUFFER SORT			     |			       |    45 |   180 |    28	(11)| 00:00:01 |
    |  39 | 	   INDEX FAST FULL SCAN 	     | I_USER2		       |    45 |   180 |     1	 (0)| 00:00:01 |
    |* 40 | 	 TABLE ACCESS FULL		     | OBJ$		       | 15494 |   166K|    52	 (0)| 00:00:01 |
    ------------------------------------------------------------------------------------------------------------------------
     
    Predicate Information (IDENTIFIED BY operation id):
    ---------------------------------------------------
     
       2 - access("T"."TS#"="TS"."TS#")
       4 - access("CX"."OWNER#"="CU"."USER#"(+))
       6 - access("T"."DATAOBJ#"="CX"."OBJ#"(+))
       8 - access("T"."BOBJ#"="CO"."OBJ#"(+))
      10 - access("T"."FILE#"="S"."FILE#"(+) AND "T"."BLOCK#"="S"."BLOCK#"(+) AND "T"."TS#"="S"."TS#"(+))
      12 - access("O"."OBJ#"="T"."OBJ#")
      13 - FILTER(BITAND("T"."PROPERTY",1)=0)
      14 - access("O"."OWNER#"="U"."USER#")
      16 - access("KSPPI"."INDX"="KSPPCV"."INDX")
      18 - access("U"."TEMPTS#"="TTS"."TS#")
      19 - access("U"."DATATS#"="DTS"."TS#")
      20 - access("U"."ASTATUS"="M"."STATUS#")
      21 - access("CGM"."VALUE"(+)="U"."NAME")
      22 - access("U"."RESOURCE$"="P"."PROFILE#")
      25 - FILTER("DP"."RESOURCE#"=1 AND "DP"."TYPE#"=1 AND "DP"."PROFILE#"=0)
      27 - FILTER("PR"."RESOURCE#"=1 AND "PR"."TYPE#"=1)
      28 - FILTER("U"."RESOURCE$"="PR"."PROFILE#")
      29 - access("U"."TYPE#"=1)
           FILTER("U"."TYPE#"=1)
      31 - FILTER("CGM"."ATTRIBUTE"(+)='ORACLE_USER' AND "CGM"."STATUS"(+)='ACTIVE')
      36 - FILTER("KSPPI"."KSPPINM"='_dml_monitoring_enabled')
      40 - FILTER(BITAND("O"."FLAGS",128)=0)
     
    -------------------------------------------------------------------------------
     
     
    SQL>

  • If you have reviewed your recommendations, you can remove the task by simply calling drop_tuning_task:

    BEGIN
      DBMS_SQLTUNE.drop_tuning_task (task_name => 'sql_tuning_task_&&my_sql_id');
    END;
    /

    Output:

    SQL> BEGIN
      2    DBMS_SQLTUNE.drop_tuning_task (task_name => 'sql_tuning_task_&&my_sql_id');
      3  END;
      4  /
     
    old   2:   DBMS_SQLTUNE.drop_tuning_task (task_name => 'sql_tuning_task_&&my_sql_id');
    NEW   2:   DBMS_SQLTUNE.drop_tuning_task (task_name => 'sql_tuning_task_2vbmgruzfy58y');
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
Categories: Database, Enterprise Manager, Oracle, Tuning Tags:

PRKP-1030 / CRS-2632 / CRS-0223 when trying to start database service

Problem description:

You are trying to start your database service(s) after some maintenance work and the following error stack is reported even though your database instances are already up & running:

[oracle@linux01 bin]$ srvctl start service -d mydb
PRKP-1030 : Failed to start the service myappsrv.
CRS-2632: There are no more servers to try to place resource 'ora.mydb.myappsrv.cs' on that would satisfy its placement policy
CRS-0223: Resource 'ora.mydb.myappsrv.cs' has placement error.
[oracle@linux01 bin]$

Cause:
Your database instance(s) are still in RESTRICTED mode.

Problem resolution:
Disable restricted session mode and retry to start your service(s):

  • Disable restricted session mode:
    SQL> ALTER system disable restricted SESSION;
     
    System altered.
     
    SQL>
  • Start your service(s):
    [oracle@linux01 bin]$ srvctl start service -d mydb
    [oracle@linux01 bin]$