Archive

Archive for the ‘Oracle’ Category

Oracle Character Scanner: csscan is 0 (zero) bytes in size on Solaris SPARC

Problem description:

You are trying to perform a character set scan on Solaris (SPARC), but the binary is exiting immediately:

oracle@ora01 $ cd $ORACLE_HOME/bin
oracle@ora01 $ ./csscan help=y
oracle@ora01 $ echo $?
0
oracle@ora01 $

 
When checking the executable, you’ll see a size of 0 bytes:

oracle@solaris01 $ ls -l /oracle/app/oracle/product/10.1.0/db_1/bin/csscan
-rwxr-xr-x   1 oracle   oinstall       0 Nov  9  2005 /oracle/app/oracle/product/10.1.0/db_1/bin/csscan
oracle@solaris01 $

 
Cause:

You are on Sun Solaris SPARC and have applied the Oracle Patch Set 10.1.0.5. Due to an internal unpublished Oracle bug (5051614), csscan related binaries are 0 bytes in size:

oracle@solaris01 $ cd $ORACLE_HOME/bin
oracle@solaris01 $ ls -l csscan lxchknlb lxegen lxinst
-rwxr-xr-x   1 oracle   oinstall       0 Nov  9  2005 csscan
-rwxr-xr-x   1 oracle   oinstall       0 Nov  9  2005 lxchknlb
-rwxr-xr-x   1 oracle   oinstall       0 Nov  9  2005 lxegen
-rwxr-xr-x   1 oracle   oinstall       0 Nov  9  2005 lxinst
oracle@solaris01 $

 
Problem resolution:

Download Oracle Patch 5051614 and copy/move the contained binaries (csscan, lxchknlb, lxegen and lxinst) to the $ORACLE_HOME/bin directory:

oracle@solaris01 $ pwd
/home/oracle/Patch5051614
oracle@solaris01 $ 
oracle@solaris01 $ unzip p5051614_10105_SOLARIS64.zip 
Archive:  p5051614_10105_SOLARIS64.zip
  inflating: csscan                  
  inflating: lxchknlb                
  inflating: lxegen                  
  inflating: lxinst                  
  inflating: README.txt              
oracle@solaris01 $ 
oracle@solaris01 $ ls -al
total 14838
drwxr-xr-x   2 oracle   oinstall     512 Jul 22 22:18 .
drwxr-xr-x  18 oracle   oinstall    1024 Jul 22 22:18 ..
-rw-rw-r--   1 oracle   oinstall  355952 Nov 29  2006 csscan
-rw-rw-r--   1 oracle   oinstall  940776 Nov 29  2006 lxchknlb
-rw-rw-r--   1 oracle   oinstall 1458272 Nov 29  2006 lxegen
-rwxrwxr-x   1 oracle   oinstall 2933984 Nov 14  2006 lxinst
-rw-r--r--   1 oracle   oinstall 1836231 Jul 22 22:18 p5051614_10105_SOLARIS64.zip
-rwxrwxr-x   1 oracle   oinstall     536 Nov 29  2006 README.txt
oracle@solaris01 $ 
oracle@solaris01 $ cp csscan $ORACLE_HOME/bin
oracle@solaris01 $ cp lxchknlb $ORACLE_HOME/bin
oracle@solaris01 $ cp lxegen $ORACLE_HOME/bin  
oracle@solaris01 $ cp lxinst $ORACLE_HOME/bin  
oracle@solaris01 $

 
After applying the functional binaries from Patch 5051614, you should be able to execute and use csscan:

oracle@solaris01 $ csscan help=y
 
 
Character Set Scanner v2.0 : Release 10.1.0.4.0 - Production on Fri Jul 22 22:48:55 2011
 
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
 
 
You can let Scanner prompt you for parameters by entering the CSSCAN        
command followed by your username/password:                                 
 
  Example: CSSCAN SYSTEM/MANAGER                                            
 
Or, you can control how Scanner runs by entering the CSSCAN command         
followed by various parameters. To specify parameters, you use keywords:    
 
  Example: CSSCAN SYSTEM/MANAGER FULL=y TOCHAR=utf8 ARRAY=102400 PROCESS=3  
 
Keyword    Default Prompt Description                                       
---------- ------- ------ ------------------------------------------------- 
USERID             yes    username/password                                 
FULL       N       yes    scan entire database                              
USER               yes    owner of tables to be scanned                     
TABLE              yes    list of tables to scan                            
EXCLUDE                   list of tables to exclude from scan               
TOCHAR             yes    new database character set name                   
FROMCHAR                  current database character set name               
TONCHAR                   new national character set name                   
FROMNCHAR                 current national character set name               
ARRAY      102400  yes    size of array fetch buffer                        
PROCESS    1       yes    number of concurrent scan process                 
MAXBLOCKS                 split table if block size exceed MAXBLOCKS        
CAPTURE    N              capture convertible data                          
SUPPRESS                  maximum number of exceptions logged for each table
FEEDBACK                  report progress every N rows                      
BOUNDARIES                list of column size boundaries for summary report 
LASTRPT    N              generate report of the last database scan         
LOG        scan           base file name of report files                    
PARFILE                   parameter file name                               
PRESERVE   N              preserve existing scan results                    
LCSD       N       no     enable language and character set detection       
LCSDDATA   LOSSY   no     define the scope of the detection                 
HELP       N              show help screen (this screen)                    
---------- ------- ------ ------------------------------------------------- 
Scanner terminated successfully.
oracle@solaris01 $

Oracle Grid Infrastructure: roothas.pl fails with “Oracle Restart stack is not active on this node” / How to forcefully deconfig all old Grid Infrastructure information

Problem description:

root.sh fails during execution for an Oracle Grid Infrastructure 11.2 installation with the following message:

[root@ora01 ~]# /u01/app/grid/11.2.0/grid/root.sh
Running Oracle 11g root script...
 
The following environment variables are set as:
    ORACLE_OWNER= grid
    ORACLE_HOME=  /u01/app/grid/11.2.0/grid
 
Enter the full pathname of the local bin directory: [/usr/local/bin]: 
The contents of "dbhome" have not changed. No need to overwrite.
The contents of "oraenv" have not changed. No need to overwrite.
The contents of "coraenv" have not changed. No need to overwrite.
 
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Using configuration parameter file: /u01/app/grid/11.2.0/grid/crs/install/crsconfig_params
Improper Oracle Clusterware configuration found on this host
Deconfigure the existing cluster configuration before starting
to configure a new Clusterware 
run '/u01/app/grid/11.2.0/grid/crs/install/roothas.pl -deconfig' 
to configure existing failed configuration and then rerun root.sh
/u01/app/grid/11.2.0/grid/perl/bin/perl -I/u01/app/grid/11.2.0/grid/perl/lib -I/u01/app/grid/11.2.0/grid/crs/install /u01/app/grid/11.2.0/grid/crs/install/roothas.pl execution failed
[root@ora01 ~]#

 
When executing roothas.pl as mentioned by root.sh, an “Oracle Restart stack is not active” error is raised. Output messages will ask you to restart the SIHA stack (although you might not have any Oracle Clusterware stack configured):

[root@ora01 ~]# /u01/app/grid/11.2.0/grid/crs/install/roothas.pl -deconfig
Using configuration parameter file: /u01/app/grid/11.2.0/grid/crs/install/crsconfig_params
Oracle Restart stack is not active on this node
Restart the SIHA stack (use /u01/app/grid/11.2.0/grid/bin/crsctl start has) and retry
Failed to verify HA resources
[root@ora01 ~]#

 
Cause:

These messages can have various causes like a previous failed Grid Infrastructure installation, an old Oracle Clusterware installation, etc.

 
Problem resolution:

In order to forcefully cleanup old configuration information execute roothas.pl with the force option:

[root@ora01 grid]# /u01/app/grid/11.2.0/grid/crs/install/roothas.pl -deconfig -force -verbose
Using configuration parameter file: /u01/app/grid/11.2.0/grid/crs/install/crsconfig_params
Failure in execution (rc=-1, 256, No such file or directory) for command 1 /u01/app/grid/11.2.0/grid/bin/crsctl stop resource ora.cssd -f
Failure in execution (rc=-1, 256, No such file or directory) for command 1 /u01/app/grid/11.2.0/grid/bin/crsctl delete resource ora.cssd -f
Failure in execution (rc=-1, 256, No such file or directory) for command 1 /u01/app/grid/11.2.0/grid/bin/crsctl stop has -f
Failure in execution (rc=-1, 256, No such file or directory) for command 1 /u01/app/grid/11.2.0/grid/bin/crsctl check has
You must kill ohasd processes or reboot the system to properly 
cleanup the processes started by Oracle clusterware
/u01/app/grid/11.2.0/grid/bin/acfsdriverstate: line 51: /lib/acfstoolsdriver.sh: No such file or directory
/u01/app/grid/11.2.0/grid/bin/acfsdriverstate: line 51: exec: /lib/acfstoolsdriver.sh: cannot execute: No such file or directory
Successfully deconfigured Oracle Restart stack
[root@ora01 grid]#

 
This cleaned up all stale information and configuration and allowed a successful rerun of root.sh.

Oracle Grid Control: Agent Collection State Disabled by Upload Manager / How to resync/unblock a Grid Control Agent

Problem description:

Grid Control lists targets for a server as DOWN and agent has an UNKNOWN state. If you are checking the status on the agent, you will see that your agent is running, but has a Collection Status of “Disabled by Upload Manager”:

oracle@racdb01 /u01/app/agent/agent11g/bin # ./emctl status agent
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
Agent Version     : 11.1.0.1.0
OMS Version       : 11.1.0.1.0
Protocol Version  : 11.1.0.0.0
Agent Home        : /u01/app/agent/agent11g
Agent binaries    : /u01/app/agent/agent11g
Agent Process ID  : 17105112
Parent Process ID : 8192042
Agent URL         : https://racdb01:3872/emd/main/
Repository URL    : https://gridc01.initso.at:4901/em/upload
Started at        : 2011-07-05 10:12:11
Started by user   : oracle
Last Reload       : 2011-07-05 10:12:11
Last successful upload                       : (none)
Last attempted upload                        : (none)
Total Megabytes of XML files uploaded so far :     0.00
Number of XML files pending upload           :      337
Size of XML files pending upload(MB)         :    50.24
Available disk space on upload filesystem    :    24.84%
Collection Status                            : Disabled by Upload Manager
Last attempted heartbeat to OMS              : 2011-07-05 10:17:07
Last successful heartbeat to OMS             : unknown
---------------------------------------------------------------
Agent is Running and Ready
oracle@racdb01 /u01/app/agent/agent11g/bin #

 
Cause:

On the Management Agents page (accessible via Setup -> Agents Subtab) agents will be marked as blocked:

 

If you display the details of the agent, Grid Control will present you the message “The Oracle Management Server(OMS) has blocked this agent because it has either been reinstalled or restored from a filesystem backup. Please click on the Agent Resynchronization button to resync the agent.”:

 


 
Problem resolution:

Click on “Agent Resynchronization” in order to resync the agent and reestablish a normal agent status (leave “Unblock agent” checked):

 

Grid Control will start to resync the agent configuration immediately:

 

Upon end of the resync process you will be redirected to the agents detail page:

 

“emctl status agent” will now point out that the agent is again able to communicate with the Oracle Management Server and upload collected data:

oracle@racdb01 /u01/app/agent/agent11g/bin # ./emctl status agent
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
Agent Version     : 11.1.0.1.0
OMS Version       : 11.1.0.1.0
Protocol Version  : 11.1.0.0.0
Agent Home        : /u01/app/agent/agent11g
Agent binaries    : /u01/app/agent/agent11g
Agent Process ID  : 17105112
Parent Process ID : 8192042
Agent URL         : https://racdb01:3872/emd/main/
Repository URL    : https://gridc01.initso.at:4901/em/upload
Started at        : 2011-07-05 10:12:11
Started by user   : oracle
Last Reload       : 2011-07-05 10:28:35
Last successful upload                       : 2011-07-05 10:29:27
Total Megabytes of XML files uploaded so far :     2.89
Number of XML files pending upload           :        0
Size of XML files pending upload(MB)         :     0.00
Available disk space on upload filesystem    :    25.00%
Last successful heartbeat to OMS             : 2011-07-05 10:28:42
---------------------------------------------------------------
Agent is Running and Ready
oracle@racdb01 /u01/app/agent/agent11g/bin #

 
After processing by the Oracle Management Server, the status information will also update in the graphical interface:

 

Categories: Enterprise Manager, Grid Control, 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: HOWTO delete a service which is not configured by Oracle Clusterware

Problem description:

You are running a Real Application cluster database and Grid Control reports that one of your database services is down, but all your database services managed by Oracle Clusterware are up and running.

Cause:

Maybe a no longer used service has still an entry in dba_services (that’s one of the views which Grid Control will check). This for example can happen if you change the database domain parameter after installation.

Problem resolution:

Check all database service entries in dba_services:

SQL> SELECT service_id, name, creation_date, enabled FROM dba_services ORDER BY 1;
 
SERVICE_ID NAME                                                             CREATION_DATE   ENA
---------- ---------------------------------------------------------------- --------------- ---
         1 SYS$BACKGROUND                                                   20-MAY-11       NO
         2 SYS$USERS                                                        20-MAY-11       NO
         3 O11GXDB                                                          20-MAY-11       NO
         4 O11G                                                             20-MAY-11       NO
         5 O11G.oracle.initso.at                                            23-MAY-11       NO
         6 O11GFAIL                                                         25-MAY-11       NO
 
6 ROWS selected.
 
SQL>

In my case, O11G was the service Grid Control reported as down, as after changing the database domain to “oracle.initso.at”, the service was no longer used.

If you want to remove a service which is no longer used by Oracle Clusterware or database connections, you can remove it by using the following commands (if the service was configured using srvctl/Oracle Clusterware, please use srvctl to remove the service!):

SQL> EXEC dbms_service.delete_service('O11G');
 
PL/SQL PROCEDURE successfully completed.
 
SQL>

Grid Control will now no longer report the service as down, because it’s no longer known by the database:

SQL> SELECT service_id, name, creation_date, enabled FROM dba_services ORDER BY 1;
 
SERVICE_ID NAME                                                             CREATION_DATE   ENA
---------- ---------------------------------------------------------------- --------------- ---
         1 SYS$BACKGROUND                                                   20-MAY-11       NO
         2 SYS$USERS                                                        20-MAY-11       NO
         3 O11GXDB                                                          20-MAY-11       NO
         5 O11G.oracle.initso.at                                            23-MAY-11       NO
         6 O11GFAIL                                                         25-MAY-11       NO
 
5 ROWS selected.
 
SQL>

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 Grid Control: /secFarm_GCDomain/GCDomain/EMGC_ADMINSERVER/FMW Welcome Page Application(11.1.0.0.0) down

Problem description:

After installing Grid Control 11.1.0.1, you may see the target “/secFarm_GCDomain/GCDomain/EMGC_ADMINSERVER/FMW Welcome Page Application(11.1.0.0.0)” reported as DOWN:



Cause:

This issue is caused by unpublished bug “BROKEN ‘FMW WELCOME PAGE APPLICATION’ APPLICATION DISCOVERED OUT-OF-BOX”.

Problem resolution:

Patch 9431704 fixes this issue and is available via Oracle My Support.

Steps to fix this issue:

  • Stop all Oracle Management Server processes:

    oracle@gc01:/u01/app/middleware/oms11g/bin> ./emctl stop oms -all
    Oracle Enterprise Manager 11g Release 1 Grid Control
    Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
    Stopping WebTier...
    WebTier Successfully Stopped
    Stopping Oracle Management Server...
    Oracle Management Server Successfully Stopped
    Oracle Management Server is Down
    oracle@gc01:/u01/app/middleware/oms11g/bin>

  • Unzip patch 9431704:

    oracle@gc01:/u01/INSTALL> unzip p9431704_111120_Generic.zip
    Archive:  p9431704_111120_Generic.zip
       creating: 9431704/
       creating: 9431704/etc/
       creating: 9431704/etc/config/
      inflating: 9431704/etc/config/deploy.xml
      inflating: 9431704/etc/config/actions.xml
      inflating: 9431704/etc/config/inventory.xml
       creating: 9431704/etc/xml/
      inflating: 9431704/etc/xml/ShiphomeDirectoryStructure.xml
      inflating: 9431704/etc/xml/GenericActions.xml
      inflating: 9431704/README.txt
       creating: 9431704/files/
       creating: 9431704/files/modules/
       creating: 9431704/files/modules/oracle.wsm.common_11.1.1/
      inflating: 9431704/files/modules/oracle.wsm.common_11.1.1/wsm-dependencies.jar
    oracle@gc01:/u01/INSTALL>

  • Set ORACLE_HOME to Middleware oracle_common directory and apply patch:

    oracle@gc01:/u01/INSTALL/9431704> export ORACLE_HOME=/u01/app/middleware/oracle_common
    oracle@gc01:/u01/INSTALL/9431704> /u01/app/middleware/oracle_common/OPatch/opatch apply
    Invoking OPatch 11.1.0.8.4
     
    Oracle Interim Patch Installer version 11.1.0.8.4
    Copyright (c) 2011, Oracle Corporation.  All rights reserved.
     
     
    Oracle Home       : /u01/app/middleware/oracle_common
    Central Inventory : /u01/app/oraInventory
       from           : /etc/oraInst.loc
    OPatch version    : 11.1.0.8.4
    OUI version       : 11.1.0.7.0
    OUI location      : /u01/app/middleware/oracle_common/oui
    Log file location : /u01/app/middleware/oracle_common/cfgtoollogs/opatch/opatch2011-06-08_23-25-29PM.log
     
    Patch history file: /u01/app/middleware/oracle_common/cfgtoollogs/opatch/opatch_history.txt
     
     
    OPatch detects the Middleware Home as "/u01/app/middleware"
     
    ApplySession applying interim patch '9431704' to OH '/u01/app/middleware/oracle_common'
     
    Running prerequisite checks...
    Provide your email address to be informed of security issues, install and
    initiate Oracle Configuration Manager. Easier for you if you use your My
    Oracle Support Email address/User Name.
    Visit http://www.oracle.com/support/policies.html for details.
    Email address/User Name:
     
    You have not provided an email address for notification of security issues.
    Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]:  y
     
    OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only.
     
     
    Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
    (Oracle Home = '/u01/app/middleware/oracle_common')
     
     
    Is the local system ready for patching? [y|n]
    y
    User Responded with: Y
    Backing up files and inventory (not for auto-rollback) for the Oracle Home
    Backing up files affected by the patch '9431704' for restore. This might take a while...
    Backing up files affected by the patch '9431704' for rollback. This might take a while...
     
    Patching component oracle.jrf.j2ee, 11.1.1.2.0...
    Copying file to "/u01/app/middleware/oracle_common/modules/oracle.wsm.common_11.1.1/wsm-dependencies.jar"
    ApplySession adding interim patch '9431704' to inventory
     
    Verifying the update...
    Inventory check OK: Patch ID 9431704 is registered in Oracle Home inventory with proper meta-data.
    Files check OK: Files from Patch ID 9431704 are present in Oracle Home.
     
    The local system has been patched and can be restarted.
     
     
    OPatch succeeded.
    oracle@gc01:/u01/INSTALL/9431704>

  • Start OMS processes:

    oracle@gc01:/u01/app/middleware/oms11g/bin> ./emctl start oms
    Oracle Enterprise Manager 11g Release 1 Grid Control
    Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
    Starting WebTier...
    WebTier Successfully Started
    Starting Oracle Management Server...
    Oracle Management Server Successfully Started
    Oracle Management Server is Up
    oracle@gc01:/u01/app/middleware/oms11g/bin>

After applying this one-off patch, FMW Welcome Page Application should no longer be reported as down:


Categories: Grid Infrastructure, Oracle Tags: