Target Re-Configuration in OEM gives ==> Connection Failure May Be Due To A Slow Network, or to the presence of intervening firewall

##########################
## Error
##########################

Connection Failure May Be Due To A Slow Network, or to the presence of intervening firewall

### Full Error

Connection Failure May Be Due To A Slow Network, or to the presence of intervening firewall

##########################
#  Error Occurred
##########################

Error occured while trying to resolve metric collection error for a cluster database in 11.2.0.2.0 GI version

##########################
## Command Executed
##########################

Reconfiguration of Target in OEM

##########################
## Issue Description.
##########################

### 11.2.0.2.0

#########################################
# Metric Collection Errors
#########################################

One of our database is in metric collection Error, So when trying to re-configure the database in OEM got the connection failure error.

This may be because of the network issue between Agent and OMS server. In our case all the targets in this server are working fine. So this is not the issue

##########################
## Solution
##########################

Database has archive issues, FRA has become 100% full. Due to which agent cant communicate with the database.



set lines 200
select * from v$flash_recovery_Area_usage;

FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE                        .05                         0               1
REDO LOG                              0                         0               0
ARCHIVED LOG                      99.76                         0             136
BACKUP PIECE                          0                         0               0
IMAGE COPY                            0                         0               0
FLASHBACK LOG                         0                         0               0
FOREIGN ARCHIVED LOG                  0                         0               0

7 rows selected.

After running an archivelog backup issue got resolved.

So next time when a database has metric collection errors, dont forget to check the archive usage too.

ORA-01031: insufficient privileges ==> GAPS in Dataguard Primary and Standby Databases

##########################
## Error
##########################

ORA-01031: insufficient privileges

### Full Error

ORA-01031: insufficient privileges

##########################
#  Error Occurred
##########################

Dataguard Primary and standby are not in SYNC and log_archive_dest of standby site is showing ORA-01031 error in 11.2.0.3.0 version

##########################
## Command Executed
##########################

In Sqlplus

select dest_id,dest_name,target,name_space,destination,status,error,db_unique_name from v$archive_dest where destination is not null;

##########################
## Issue Description.
##########################

### 11.2.0.3.0

#########################################
# Gaps in Primary n Standby
#########################################

In Primary :
--------------

set lines 200
col dest_name for a20
col destination for a50
col error for a15
col db_unique_name for a15
col name for a100

select dest_id,dest_name,target,name_space,destination,status,error,db_unique_name from v$archive_dest where destination is not null;


   DEST_ID DEST_NAME            TARGET  NAME_SP DESTINATION                                        STATUS    ERROR           DB_UNIQUE_NAME
---------- -------------------- ------- ------- -------------------------------------------------- --------- --------------- ---------------
         1 LOG_ARCHIVE_DEST_1   PRIMARY SYSTEM  USE_DB_RECOVERY_FILE_DEST                          VALID                     est01p_stl
         2 LOG_ARCHIVE_DEST_2   STANDBY SYSTEM  est01p_gvl                                         ERROR     ORA-01031:      est01p_gvl
                                                                                                             insufficient
                                                                                                             privileges

Select thread#,sequence# from v$thread;

   THREAD#  SEQUENCE#
---------- ----------
         1      15933
         2        231

Standby :
------------

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

    Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
         1                  15933                 15933          0
         2                    212                   212          0

##########################
## Solution
##########################

From the above output we can see that, In Standby Destination received archive log sequence is 212 of thread 2, but in primary its 231.

#########################################
# Check Password File in Standby
#########################################


est01p_1 @ hostorap01:/u01/app/oracle/product/11.2/db_6/dbs
> ls -ltr orapwe*
est01p_1 @ hostorap01:/u01/app/oracle/product/11.2/db_6/dbs

Password File is not available for est01p database.

Create a new password file ( or ) FTP the password file from Primary to STandby location.

After ftping the password file, GAPS are resolved automatically.

In Primary :
--------------

set lines 200
col dest_name for a20
col destination for a50
col error for a15
col db_unique_name for a15
col name for a100

select dest_id,dest_name,target,name_space,destination,status,error,db_unique_name from v$archive_dest where destination is not null;

   DEST_ID DEST_NAME            TARGET  NAME_SP DESTINATION                                        STATUS    ERROR           DB_UNIQUE_NAME
---------- -------------------- ------- ------- -------------------------------------------------- --------- --------------- ---------------
         1 LOG_ARCHIVE_DEST_1   PRIMARY SYSTEM  USE_DB_RECOVERY_FILE_DEST                          VALID                     est01p_stl
         2 LOG_ARCHIVE_DEST_2   STANDBY SYSTEM  est01p_gvl                                         VALID                     est01p_gvl
         3 LOG_ARCHIVE_DEST_3   PRIMARY SYSTEM  /u01/app/oracle/exp/est01p/arch02                  ALTERNATE                 est01p_Stl


Standby :
------------

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;


    Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
         1                  15933                 15933          0
         2                    230                   230          0

ORA-15053: diskgroup "OCR_VOTE" contains existing files

##########################
## Error
##########################

ORA-15053: diskgroup "OCR_VOTE" contains existing files

### Full Error

SQL> Drop diskgroup OCR_VOTE;
Drop diskgroup OCR_VOTE
*
ERROR at line 1:
ORA-15039: diskgroup not dropped
ORA-15053: diskgroup "OCR_VOTE" contains existing files

##########################
#  Error Occurred
##########################

Error occured while trying to drop one of the diskgroup in 11.2.0.3.0 ASM version

##########################
## Command Executed
##########################

In ASM Instance Sqlplus,

Drop diskgroup OCR_VOTE;

##########################
## Issue Description.
##########################

### 11.2.0.3.0

#########################################
# 1) Check the Files Stored in ASM Diskgroup
#########################################

Disk group is not getting dropped because it has some files stored. Use the below query to find the files stored in Diskgroup.

set lines 200
col full_path for a110
SELECT full_path, dir, sys FROM (SELECT CONCAT('+'||gname,SYS_CONNECT_BY_PATH(aname,'/')) full_path,dir, sys FROM (SELECT g.name gname,a.parent_index pindex, a.name

aname,a.reference_index rindex, a.ALIAS_DIRECTORY dir,a.SYSTEM_CREATED sys FROM v$asm_alias a, v$asm_diskgroup g WHERE a.group_number = g.group_number) START WITH

(MOD(pindex, POWER(2, 24))) = 0 CONNECT BY PRIOR rindex = pindex ORDER BY dir desc, full_path asc)
WHERE UPPER(full_path) LIKE '%OCR_VOTE%';

FULL_PATH                                                                                                      D S
-------------------------------------------------------------------------------------------------------------- - -
+OCR_VOTE/dev-cluster                                                                                         Y Y
+OCR_VOTE/dev-cluster/OCRFILE                                                                                 Y Y
+OCR_VOTE/dev-cluster/OCRFILE/REGISTRY.255.828701551                                                          N Y

##########################
## Solution
##########################

Move/Drop the Files stored in the diskgroup before dropping, else use the below option to drop the diskgroup.

Drop diskgroup OCR_VOTE including contents;

SQL> Drop diskgroup OCR_VOTE including contents;

Diskgroup dropped.

ORA-15032: not all alterations performed ORA-15028: ASM file '+OCR_VOTE/spfileasm.ora' not dropped; currently being accessed

##########################
## Error
##########################

ORA-15028: ASM file '+OCR_VOTE/spfileasm.ora' not dropped; currently being accessed

### Full Error

SQL> alter diskgroup OCR_VOTE drop file '+OCR_VOTE/spfileasm.ora';
alter diskgroup OCR_VOTE drop file '+OCR_VOTE/spfileasm.ora'
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15028: ASM file '+OCR_VOTE/spfileasm.ora' not dropped; currently being accessed

##########################
#  Error Occurred
##########################

Error occured while trying to drop SPFILE from one diskgroup, which is supposed to be dropped in 11.2.0.3.0 version

##########################
## Command Executed
##########################

In Sqlplus

alter diskgroup OCR_VOTE drop file '+OCR_VOTE/spfileasm.ora';

##########################
## Issue Description.
##########################

### 11.2.0.3.0

#########################################
# 1) Why i'm dropping SPFILE
#########################################

We want to move all the files from +OCR_VOTE diskgroup to new diskgroup. All the files are moved except ASM Spfile.

As the ASM instance is currently using the Parameter file, it cant be dropped. So followed below steps to drop it.

##########################
## Solution
##########################

1) Create a pfile to a temporary location from Spfile.

SQL> create pfile='$ORACLE_HOME/dbs/init+ASM.ora' from spfile;

2) Shutdown ASM instance.

SQL> shutdown immediate

3) Startup with Pfile

SQL> startup pfile=$ORACLE_HOME/dbs/init+ASM.ora

4) Now Drop the Spfile from +OCR_VOTE diskgroup.

alter diskgroup OCR_VOTE drop file '+OCR_VOTE/spfileasm.ora';

PROT-30: The Oracle Cluster Registry location to be added is not usable

##########################
## Error
##########################

PROT-30: The Oracle Cluster Registry location to be added is not usable

### Full Error

+ASM1 @ hostd01:/u01/app/oracle/exp
> sudo ocrconfig -add /u01/app/oracle/exp
PROT-30: The Oracle Cluster Registry location to be added is not usable
PROC-8: Cannot perform cluster registry operation because one of the parameters is invalid. Operating System error [Resource temporarily unavailable] [11]

##########################
#  Error Occurred
##########################

Error occured while trying to add another(mirror) location to OCR Files. Our OCR is presently in ASM diskgroup.

##########################
## Command Executed
##########################

In Terminal, by setting ASM instance,

sudo ocrconfig -add /u01/app/oracle/exp

(or)

sudo ocrconfig -add /u01/app/oracle/product/11.2/ocr.dat

##########################
## Issue Description.
##########################

### 11.2.0.3.0

#########################################
# 1) Check the CRS logs for errors recorded while running this command.
#########################################

+ASM1 @ hostd01:/u01/app/oracle/exp
> sudo ocrconfig -add /u01/app/oracle/exp
PROT-30: The Oracle Cluster Registry location to be added is not usable
PROC-8: Cannot perform cluster registry operation because one of the parameters is invalid. Operating System error [Resource temporarily unavailable] [11]

Errors Recorded in crsd.log :

+ASM1 @ hostd01:/u01/app/11.2/grid1/log/hostd01/crsd
> tail -f crsd.log
2013-10-14 01:21:23.162: [   CRSPE][1187469632] {2:40710:2864} Expression Filter : ((NAME == ora.scan1.vip) AND (LAST_SERVER == hostd02))
2013-10-14 01:21:41.455: [  OCROSD][947927360]utdvch:-1: New location /u01/app/oracle/exp configured is not valid storage type. Return code [37].
2013-10-14 01:21:41.455: [  OCRRAW][947927360]propriodvch: Error  [8] returned device check for [/u01/app/oracle/exp]
2013-10-14 01:21:41.455: [  OCRRAW][947927360]dev_replace: master could not verify the new disk (8)
[  OCRSRV][947927360]proas_replace_disk: Failed in changing configurations in the Master 8

If we can see from the above crsd.log, its mentioned as STORAGE TYPE is not a valid one.

When tried the below command by creating a dummy file(ocr.dat) with touch command. Received the below errors,

sudo ocrconfig -add /u01/app/oracle/exp/ocr.dat

Errors Recorded in crsd.log :

+ASM1 @ hostd01:/u01/app/11.2/grid1/log/hostd01/crsd
> tail -f crsd.log
2013-10-14 01:15:23.108: [   CRSPE][1187469632] {2:40710:2858} Expression Filter : ((NAME == ora.scan1.vip) AND (LAST_SERVER == hostd02))
2013-10-14 01:16:04.995: [  OCROSD][945826112]utstoragetypecommon: Oracle Cluster Registry does not support the storage type configured.
OCR can be configured on: ASM, OCFS, OCFS2, NFS, Block Device, Character Device, VxFS
2013-10-14 01:16:04.995: [  OCROSD][945826112]utdvch:-1: New location /u01/app/oracle/exp/ocr.dat configured is not valid storage type. Return code [37].
2013-10-14 01:16:04.995: [  OCRRAW][945826112]propriodvch: Error  [8] returned device check for [/u01/app/oracle/exp/ocr.dat]
2013-10-14 01:16:04.995: [  OCRRAW][945826112]dev_replace: master could not verify the new disk (8)
[  OCRSRV][945826112]proas_replace_disk: Failed in changing configurations in the Master 8

If we look in the error log details, it shows as,

OCR can be configured on: ASM, OCFS, OCFS2, NFS, Block Device, Character Device, VxFS

Checking the File system type,

df -T /u01/app/oracle/exp


+ASM1 @ hostd01:/u01
> df -T /u01/app/oracle/exp
Filesystem    Type   1K-blocks      Used Available Use% Mounted on
/dev/mapper/vg00-oracle--exp
              ext3     5160576   4205536    692896  86% /u01/app/oracle/exp

##########################
## Solution
##########################

As the file system type is ext3, i have add the alternate location for OCR to ASM diskgroup.


+ASM1 @ hostd01:/u01/app/oracle/backup
> sudo ocrconfig -add +test_DATA
+ASM1 @ hostd01:/u01/app/oracle/backup

+ASM1 @ hostd01:/u01/app/oracle/backup
> ocrcheck
Status of Oracle Cluster Registry is as follows :
         Version                  :          3
         Total space (kbytes)     :     262120
         Used space (kbytes)      :       3444
         Available space (kbytes) :     258676
         ID                       : 1882253608
         Device/File Name         :  +OCR_VOTE
                                    Device/File integrity check succeeded
         Device/File Name         : +test_DATA
                                    Device/File integrity check succeeded

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

         Cluster registry integrity check succeeded

         Logical corruption check bypassed due to non-privileged user

TNS-12541: TNS:no listener

##########################
## Error
##########################

TNS-12541: TNS:no listener

### Full Error

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = hostp01)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED)
(SID = oralin)))
TNS-12541: TNS:no listener

##########################
#  Error Occurred
##########################

Error occured while trying to connect a database using its service name in 10.2.0.3.0 version and also tnsping of service name gives TNS-12541

##########################
## Command Executed
##########################

In terminal

tnsping oralin

##########################
## Issue Description.
##########################

### 11.2.0.3.0

#########################################
# 1) check whether listener is running or not
#########################################

+ASM @ hostp01:/opt/oracle/product/11.2.0.1/grid/network/admin
> ps -ef|grep tns
  oracle 16743  2737   0 01:00:52 ?           0:00 /opt/oracle/product/11.2.0.1/grid/bin/tnslsnr LISTENER -inherit
  oracle 16747 11537   0 01:00:58 pts/2       0:00 grep tns

From above output, we can see LISTENER is up and running.

#########################################
# 2) check services listened by LISTENER
#########################################

lsnrctl status LISTENER

+ASM @ hostp01:/opt/oracle/product/11.2.0.1/grid/network/admin
> lsnrctl status LISTENER

LSNRCTL for Solaris: Version 11.2.0.1.0 - Production on 17-SEP-2013 01:00:52

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Solaris: Version 11.2.0.1.0 - Production
Start Date                17-SEP-2013 01:00:52
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/product/11.2.0.1/grid/network/admin/listener.ora
Listener Log File         /opt/oracle/diag/tnslsnr/hostp01/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
The listener supports no services
The command completed successfully

Listener is not listening to any database service. So database is not using this listener.

##########################
## Solution
##########################

#########################################
# 1) check database home ( or ) Grid home listener.ora file
#########################################

Check database home or GRID home listener.ora to find the list of listeners configured and what are the services listening to that.

oralin @ hostp01:/opt/oracle/product/11.1.0.7/db_1/network/admin
> more listener.ora
# listener.ora Network Configuration File: /opt/oracle/product/11.2.0.1/grid/network/admin/listener.ora
# Generated by Oracle configuration tools.

LSNR_11G =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = hostp01)(PORT = 1521))
    )
  )

SID_LIST_LSNR_11G =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /opt/oracle/product/11.1.0.7/db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME=+ASM)
      (SID_NAME = +ASM)
      (ORACLE_HOME = /opt/oracle/product/11.2.0.1/grid)
    )
    (SID_DESC =
      (GLOBAL_DBNAME=oralin.example.com)
      (SID_NAME = oralin)
      (ORACLE_HOME = /opt/oracle/product/11.1.0.7/db_1)
    )
  )


From the above output, we can see that a listener named " LSNR_11G " is configured in LISTENER.ora and oralin database is listened using this listener.

#########################################
# 2) Start the Listener
#########################################

+ASM @ hostp01:/opt/oracle/product/11.1.0.7/db_1/network/admin
> lsnrctl start LSNR_11G

LSNRCTL for Solaris: Version 11.2.0.1.0 - Production on 17-SEP-2013 03:34:38

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Starting /opt/oracle/product/11.2.0.1/grid/bin/tnslsnr: please wait...

TNSLSNR for Solaris: Version 11.2.0.1.0 - Production
System parameter file is /opt/oracle/product/11.2.0.1/grid/network/admin/listener.ora
Log messages written to /opt/oracle/diag/tnslsnr/hostp01/lsnr_11g/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hostp01)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LSNR_11G
Version                   TNSLSNR for Solaris: Version 11.2.0.1.0 - Production
Start Date                17-SEP-2013 03:34:38
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/product/11.2.0.1/grid/network/admin/listener.ora
Listener Log File         /opt/oracle/diag/tnslsnr/hostp01/lsnr_11g/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hostp01)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM", status UNKNOWN, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "oralin.example.com" has 1 instance(s).
  Instance "oralin", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully


+ASM @ hostp01:/opt/oracle/product/11.1.0.7/db_1/network/admin
> ps -ef|grep tns
  oracle 16743  2737   0 01:00:52 ?           0:01 /opt/oracle/product/11.2.0.1/grid/bin/tnslsnr LISTENER -inherit
  oracle 26233  2737   0 03:34:38 ?           0:00 /opt/oracle/product/11.2.0.1/grid/bin/tnslsnr LSNR_11G -inherit
  oracle 26240 11537   0 03:34:53 pts/2       0:00 grep tns

Now the tnsping output is successful and database can be connected.

oralin @ hostp01:/opt/oracle/product/11.1.0.7/db_1/network/admin
> tnsping oralin

TNS Ping Utility for Solaris: Version 11.1.0.7.0 - Production on 17-SEP-2013 04:45:58

Copyright (c) 1997, 2008, Oracle.  All rights reserved.

Used parameter files:
/opt/oracle/product/11.1.0.7/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = hostp01)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SID =

oralin)))
OK (0 msec)

ORA-19502: write error on file "", block number (block size=)

##########################
## Error
##########################

ORA-19502: write error on file "", block number  (block size=)

### Full Error

ORA-19502: write error on file "", block number  (block size=)

##########################
#  Error Occurred
##########################

Error occured while trying to check archive log destination status and its modes in 11.2.0.3.0 version

##########################
## Command Executed
##########################

set lines 200
col dest_name for a20
col db_unique_name for a15

select dest_name,db_unique_name,database_mode,recovery_mode,gap_status,error from v$archive_Dest_Status;

##########################
## Issue Description.
##########################

### 11.2.0.3.0

#########################################
# 1) check the Log archive destination and its status
#########################################

set lines 200
col dest_name for a20
col db_unique_name for a15

select dest_name,db_unique_name,database_mode,recovery_mode,gap_status,error from v$archive_Dest_Status;

DEST_NAME            DB_UNIQUE_NAME  DATABASE_MODE   RECOVERY_MODE           GAP_STATUS               ERROR
-------------------- --------------- --------------- ----------------------- ------------------------ -----------------------------------------------------------------
LOG_ARCHIVE_DEST_1   oralin_prim    OPEN            IDLE
LOG_ARCHIVE_DEST_2   oralin_std    MOUNTED-STANDBY MANAGED REAL TIME APPLY
LOG_ARCHIVE_DEST_3   oralin_prim    OPEN            IDLE                                             ORA-19502: write error on file "", block number  (block size=)

LOG_ARCHIVE_DEST_3 has been set as an alternate destination.


sho parameter LOG_ARCHIVE_DEST_1

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1                   string      LOCATION=USE_DB_RECOVERY_FILE_
                                                 DEST VALID_FOR=(ALL_LOGFILES,A
                                                 LL_ROLES) DB_UNIQUE_NAME=oralin_prim noreopen max_failure=0
                                                  mandatory alternate=log_archi
                                                 ve_dest_3

sho parameter LOG_ARCHIVE_DEST_3

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_3                   string      location=/u01/app/oracle/exp/oralin/arch02 VALID_FOR=(ALL_
                                                 LOGFILES,ALL_ROLES) DB_UNIQUE_
                                                 NAME=oralin_prim

sho parameter LOG_ARCHIVE_DEST_state_3

SQL> sho parameter LOG_ARCHIVE_DEST_state_3

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_3             string      ALTERNATE

No Errors has been recorded in Alert log file.

##########################
## Solution
##########################


Tried reset the parameter back to its same value again and it worked like a charm,

Alter system set log_archive_Dest_3='location=/u01/app/oracle/exp/oralin/arch02 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=oralin_prim';

SQL> Alter system set log_archive_Dest_3='location=/u01/app/oracle/exp/oralin/arch02 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=oralin_prim';

System altered.

SQL> sho parameter log_archive_dest_3

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_3                   string      location=/u01/app/oracle/exp/oralin/arch02 VALID_FOR=(ALL_
                                                 LOGFILES,ALL_ROLES) DB_UNIQUE_
                                                 NAME=oralin_prim


DEST_NAME            DB_UNIQUE_NAME  DATABASE_MODE   RECOVERY_MODE           GAP_STATUS               ERROR
-------------------- --------------- --------------- ----------------------- ------------------------ -----------------------------------------------------------------
LOG_ARCHIVE_DEST_1   oralin_prim    OPEN            IDLE
LOG_ARCHIVE_DEST_2   oralin_std    MOUNTED-STANDBY MANAGED REAL TIME APPLY NO GAP
LOG_ARCHIVE_DEST_3   oralin_prim    OPEN            IDLE

expdp ESTIMATE_ONLY=Y gives ORA-39070: Unable to open the log file.

##########################
## Error
##########################

ORA-39070: Unable to open the log file.

### Full Error

ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation

##########################
## Cause
##########################

Error occured while trying to estimate the size of the size of the dump file using expdp

##########################
## Command Executed
##########################

expdp \'/ as sysdba\' tablespaces=USERS ESTIMATE_ONLY=Y

##########################
## Solution
##########################

### Ran the ESTIMATE_ONLY=Y option without Directory Option, failed in this database.

oralin_3 @ hostu07:/u01/home/oracle
> expdp \'/ as sysdba\' tablespaces=USERS ESTIMATE_ONLY=Y

Export: Release 11.2.0.3.0 - Production on Mon Sep 2 07:09:11 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation

So initially what i did is ran the estimate_only command with directory option and it got succeeded.

expdp \'/ as sysdba\' tablespaces=USERS ESTIMATE_ONLY=Y directory=export_dump

I have never given directory option before for estimating the size of the dumpfile earlier.

------------------------------------------------------------------------------------------------------

Again i tried the same in another database ( orawin_1 ) without directory option and it has succeeded.

orawin_1 @ hostp03:/u01/home/oracle
> expdp \'/ as sysdba\' tablespaces=USERS ESTIMATE_ONLY=Y

Export: Release 11.2.0.3.0 - Production on Tue Sep 3 00:04:04 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLESPACE_01":  "/******** AS SYSDBA" tablespaces=USERS ESTIMATE_ONLY=Y
Estimate in progress using BLOCKS method...
Total estimation using BLOCKS method: 0 KB
Job "SYS"."SYS_EXPORT_TABLESPACE_01" successfully completed at 00:04:52

So i investigated more on this and found below.

When a expdp command is run with ESTIMATE_ONLY=Y option, it doesnt create any dump file but it tries to create a log file, due to this i got the error when i ran 
the command in oralin_3 database.

You might be wondering why i didnt receive the same error in orawin_1 database its because from Oracle 10g onwards, a default directory named DATA_PUMP_DIR is created.

So if a directory option is not mentioned it will try to create the dumpfile and logfile in the location mentioned in DATA_PUMP_DIR directory.

In orawin_1 this directory exist and its physical location is also VALID.


OWNER           DIRECTORY_NAME            DIRECTORY_PATH
--------------- ------------------------- ------------------------------------------------------------
SYS             DATA_PUMP_DIR             /u01/app/oracle/product/11.2/db_10/rdbms/log/

SQL> !ls -ltr /u01/app/oracle/product/11.2/db_10/rdbms/log/
total 16
-rw-r--r-- 1 oracle dba 14365 Sep  3 00:06 export.log

But in oralin_3, the physical location of this directory is not available and it failed.

ORA-28002: the password will expire within 7 days

##########################
## Error
##########################

ORA-28002: the password will expire within 7 days

### Full Error

oralin_2 @ hostp03:/etc
> sqlplus system/pass_word@oralin_gvl

SQL*Plus: Release 11.2.0.2.0 Production on Fri Aug 30 06:14:48 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

ERROR:
ORA-28002: the password will expire within 7 days

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL>

##########################
## Cause
##########################

Error occured while trying to connect using a user.

##########################
## Command Executed
##########################

conn system/pass

##########################
## Solution
##########################

###

Check User Account Status :

 select username,account_status,expiry_date,profile from dba_users where username='SYSTEM';

USERNAME                       ACCOUNT_STATUS                   EXPIRY_DA PROFILE
------------------------------ -------------------------------- --------- ------------------------------
SYSTEM                         EXPIRED(GRACE)                   06-SEP-13 DEFAULT


Check the Profile limit set for password life time

select * from dba_profiles where RESOURCE_NAME='PASSWORD_LIFE_TIME';

PROFILE                                  RESOURCE_NAME                  RESOURCE LIMIT
---------------------------------------- ------------------------------ -------- -------------------------
DEFAULT                                  PASSWORD_LIFE_TIME             PASSWORD 180

If you dont want to alter the profile limit set, then change the password for the user by noting it values from user$ and then reset it back.

If you wanna change the profile limit then,

Alter profile default limit password_life_time unlimited;

SQL> Alter profile default limit password_life_time unlimited;

Profile altered.

select * from dba_profiles where RESOURCE_NAME='PASSWORD_LIFE_TIME';

PROFILE                                  RESOURCE_NAME                  RESOURCE LIMIT
---------------------------------------- ------------------------------ -------- -------------------------
DEFAULT                                  PASSWORD_LIFE_TIME             PASSWORD UNLIMITED

After changing the profile limit also the user account will be in expired status only,

USERNAME                       ACCOUNT_STATUS                   EXPIRY_DA PROFILE
------------------------------ -------------------------------- --------- ----------------------------------------
SYSTEM                         EXPIRED(GRACE)                   06-SEP-13 DEFAULT

Now change the password you would like or use the same password to set.

Alter user system identified by pass_word;

SQL> Alter user system identified by pass_word;

User altered.

SQL> select username,account_status,expiry_date,profile from dba_users where username='SYSTEM';


USERNAME                       ACCOUNT_STATUS                   EXPIRY_DA PROFILE
------------------------------ -------------------------------- --------- ----------------------------------------
SYSTEM                         OPEN                                       DEFAULT

ORA-27211: Failed to load Media Management Library

##########################
## Error
##########################

ORA-27211: Failed to load Media Management Library

### Full Error

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 08/29/2013 10:09:21
ORA-19554: error allocating device, device type: SBT_TAPE, device name:
ORA-27211: Failed to load Media Management Library
Additional information: 2

##########################
#  Error Occurred
##########################

Error occured while a backup was fired in one of the database of 11.2.0.3.0 version

##########################
## Command Executed
##########################

In RMAN,

backup archivelog logseq 5432 thread 1;

##########################
## Issue Description.
##########################

### 11.2.0.3.0


RMAN> backup archivelog logseq 5432 thread 1;

Starting backup at 29-AUG-13
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 08/29/2013 10:09:21
ORA-19554: error allocating device, device type: SBT_TAPE, device name:
ORA-27211: Failed to load Media Management Library
Additional information: 2

This database was failed over from hostp02 to hostp03 due to some issue. Backup was working fine in hostp02 server. backup is not working after failover
to hostp03 server.

While checking found that libobk.so64 library of netbackup is not linked to oracle libraries.

oralin_2 @ hostp03:/u01/app/oracle/product/11.2/db_10/lib
> ls libob*
ls: libob*: No such file or directory

##########################
## Solution
##########################

So Created a symbolic link in $ORACLE_HOME/lib location and tested the backup and it worked fine.

ln -s /usr/openv/netbackup/bin/libobk.so64 libobk.so

oralin_2 @ hostp03:/u01/app/oracle/product/11.2/db_10/lib
> ln -s /usr/openv/netbackup/bin/libobk.so64 libobk.so

oralin_2 @ hostp03:/u01/app/oracle/product/11.2/db_10/lib
> ls -ltr libob*
lrwxrwxrwx 1 oracle oinstall 36 Aug 29 09:32 libobk.so -> /usr/openv/netbackup/bin/libobk.so64
oralin_2 @ hostp03:/u01/app/oracle/product/11.2/db_10/lib

RMAN>  backup archivelog logseq 5432 thread 1;

Starting backup at 29-AUG-13
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=2799 instance=oralin_1 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Veritas NetBackup for Oracle - Release 7.1 (2011020316)

Different Methods to find or Check whether the installed Oracle Client Software is 32 bit or 64 bit in Linux / Unix Environments

########################
 Task
########################

To find whether the installed Client Software is 32 bit or 64 bit in Linux / Unix Environments

$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/client_1

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

=====================================================================================================================
Method 1 :
--------
Using ORACLE_HOME properties File
=====================================================================================================================


$ cat $ORACLE_HOME/inventory/ContentsXML/oraclehomeproperties.xml | grep -i architecture

     

From above output, we can find its 64 bit, 32 means then the client software is 32 bit.

=====================================================================================================================
Method 2 :
--------
Using File OS command
=====================================================================================================================

$ file $ORACLE_HOME/bin/sqlplus

/u01/app/oracle/product/11.2.0/client_1/bin/sqlplus: ELF 64-bit LSB executable, AMD x86-64, version 1 (SYSV), for GNU/Linux 2.6.9, dynamically linked (uses shared libs), for GNU/Linux 2.6.9, not stripped

From above output, we can find x86-64, so this is 64 bit version.

=====================================================================================================================
Method 3 :
--------
From lib directories created during installation.
=====================================================================================================================

$ ls -l $ORACLE_HOME | grep lib

drwxr-xr-x  2 oracle oinstall 4096 Jun 26 14:26 jlib
drwxr-xr-x  3 oracle oinstall 4096 Jun 26 14:26 lib

If the two directories $ORACLE_HOME/lib32 and $ORACLE_HOME/lib exist, then it is 64 bit client.

If you have only $ORACLE_HOME/lib you need to use method 1 as there are client versions (11.2) where $ORACLE_HOME/lib32 directory does not exist on 64-bit client installations.

=====================================================================================================================
Method 4 :
--------
Using Perl Version
=====================================================================================================================

$ perl -v

This is perl, v5.8.8 built for x86_64-linux-thread-multi

Note: Perl is installed according to the bit version of the ORACLE_HOME but not the OS bit version.

=====================================================================================================================

ORA-20600: The specified target is in the process of being deleted.

##########################
## Error
##########################

ORA-20600: The specified target is in the process of being deleted.

### Full Error

Saving instance oralin_1 to repository and agent https://host01:3872/emd/main...java.sql.SQLException: ORA-20600: The specified target is in the process of being deleted.(target name = oralin_1)(target type = oracle_database)(target guid = 31427C7C89C2C89148EB7B775E90E20D) ORA-06512: at "SYSMAN.TARGETS_INSERT_TRIGGER", line 46 ORA-04088: error during execution of trigger 'SYSMAN.TARGETS_INSERT_TRIGGER' ORA-06512: at "SYSMAN.EM_TARGET", line 2283 ORA-06512: at "SYSMAN.MGMT_TARGET", line 2720 ORA-06512: at line 1
... finished.
Saving instance oralin_2 to repository and agent https://host02:3872/emd/main... ... finished.
Properties for instance oralin have been updated.

##########################
#  Error Occurred
##########################

When trying to add “oralin_1” instance in OEM. Above errors has occurred.

##########################
## Command Executed
##########################

Add instance of a cluster in OEM

##########################
## Issue Description.
##########################

Couple of minutes back, “oralin_1” instance was deleted through OEM and the same was tried to add back.

Checking the instance registration details in OEM Repository database,

Set lines 200
col target_name for a30
select target_name, target_type, target_guid from mgmt_targets where target_name like '%oralin%' and target_type='oracle_database';

TARGET_NAME                    TARGET_TYPE                                                      TARGET_GUID
------------------------------ ---------------------------------------------------------------- --------------------------------
oralin_2                       oracle_database                                                  83F1FC27B89BAEFB9F4077620D700303


##########################
## Solution
##########################

Only the 2nd instance details were available. So tried to register the same after 2 minutes and it worked like charm.

TARGET_NAME                    TARGET_TYPE                                                      TARGET_GUID
------------------------------ ---------------------------------------------------------------- --------------------------------
oralin_2                       oracle_database                                                  83F1FC27B89BAEFB9F4077620D700303
oralin_1                       oracle_database                                                  31427C7C89C2C89148EB7B775E90E20D

Temporary Tablespace Group : ORA-01652: unable to extend temp segment by 256 in tablespace AU_TEMP

Our Application team has encountered the below error while running the month end jobs. So below are the recommendation which fixed the issue.

##########################
## Error
##########################

ORA-01652: unable to extend temp segment by 256 in tablespace AU_TEMP

### Full Error

ERROR at line 1:
ORA-12801: error signaled in parallel query server P000, instance host01.example.com:aua01p2 (2)
ORA-01652: unable to extend temp segment by 256 in tablespace AU_TEMP
ORA-06512: at "au_ADHOC.au_EOM_REPORTS_NEW", line 3334
ORA-06512: at "au_ADHOC.au_EOM_REPORTS_NEW", line 3904
ORA-06512: at line 1

##########################
#  Error Occurred
##########################

Error occured while application month end jobs are running in 10.2.0.3.0 version

##########################
## Command Executed
##########################

Application jobs

##########################
## Issue Description.
##########################

### 10.2.0.3.0

We have a temporary tablespace of size 63 GB, due to month end multiple jobs were running at the same time and it errors out with unable to extend error.

##########################
## Solution
##########################

From oracle 10g onwards, we can create a temporary tablespace group such that if one tablespace in that group ran out of space then the other available tablespace will be used.

A new group will be created when a tablespace is assigned to the group.

SQL> Select * from dba_tablespace_groups;

no rows selected

Alter tablespace AU_TEMP tablespace group au_TMP_GROUP;

Alter tablespace USER_TEMP tablespace group au_TMP_GROUP;

SQL> Select * from dba_tablespace_groups;

GROUP_NAME                     TABLESPACE_NAME
------------------------------ -------------------------
au_TMP_GROUP                  USER_TEMP
au_TMP_GROUP                  AU_TEMP

And then change the tablespace of the user who is executing the job.

SET LINES 200
COL USERNAME FOR A25
COL ACCOUNT_STATUS FOR A18
COL DEFAULT_TABLESPACE FOR A20
COL PROFILE FOR A20
col password for a25
col temporary_tablespace for a15

select username,account_status,password,PROFILE,DEFAULT_TABLESPACE,temporary_tablespace,created from dba_users
where username='au_ADHOC' order by username;

USERNAME                  ACCOUNT_STATUS     PASSWORD                  PROFILE              DEFAULT_TABLESPACE   TEMPORARY_TABLE CREATED
------------------------- ------------------ ------------------------- -------------------- -------------------- --------------- ---------
au_ADHOC                 OPEN               DEFAULT              au_ADHOCD1M         AU_TEMP     09-DEC-04

#### Assigning the user the newly created tablespace group.

Alter user au_adhoc temporary tablespace au_tmp_group;

SQL> Alter user au_adhoc temporary tablespace au_tmp_group;

User altered.

SQL> select username,account_status,password,PROFILE,DEFAULT_TABLESPACE,temporary_tablespace,created from dba_users
where username='au_ADHOC' order by username;
  2
USERNAME                  ACCOUNT_STATUS     PASSWORD                  PROFILE              DEFAULT_TABLESPACE   TEMPORARY_TABLE CREATED
------------------------- ------------------ ------------------------- -------------------- -------------------- --------------- ---------
au_ADHOC                 OPEN               DEFAULT              au_ADHOCD1M         au_TMP_GROUP   09-DEC-04

#### To unassign the tablespace from the temp tablespace group.

Alter tablespace USER_TEMP tablespace group '';


SQL> Select * from dba_tablespace_groups;

no rows selected

ORA-00600: internal error code, arguments: [kccscf_1], [9], [72704], [65535], [], [], [], []

##########################
## Error
##########################

ORA-00600: internal error code, arguments: [kccscf_1], [9], [72704], [65535], [], [], [], []

### Full Error

check rman_dup_proddb_uatdb_08062013.log for RMAN Duplication Log.

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 08/06/2013 23:26:29
RMAN-06136: ORACLE error from auxiliary database: ORA-01503: CREATE CONTROLFILE failed
ORA-00600: internal error code, arguments: [kccscf_1], [9], [72704], [65535], [], [], [], []

##########################
#  Error Occurred
##########################

Error occured while RMAN Duplication is performed, once duplication is done and when oracle tried to create controlfile in 10.2.0.3.0 version of database

##########################
## Command Executed
##########################

In sqlplus,

Create Controlfile Statment,

##########################
## Issue Description.
##########################

### 10.2.0.3.0


Error occurred RMAN duplication of create controlfile statement in 10.2.0.3.0 version.

Problem is related to parameter MAXLOGHISTORY specified in create controlfile  command.  It's complaining that maximum should  be 65535. whereas in the create controlfile statement its 72704. due to which its failing.

This seems to be a Bug

Bug:
-------

This is unpublished bug 4877360
Abstract: APPSST SRV 10G :ORA-600: INTERNAL ERROR CODE, ARGUMENTS: [KCCSCF_1], [9], [65732], [65535]

##########################
## Solution
##########################

A possible workaround is to manually recreate the control file, and change the maxloghistory set to 65535 or lower in the script.

The bug is fixed in 10.2.0.4 and 11.1.0.6 versions.

ORA-16033: parameter LOG_ARCHIVE_DEST_1 destination cannot be the same as

##########################
## Error
##########################

ORA-16033: parameter LOG_ARCHIVE_DEST_1 destination cannot be the same as

### Full Error

Alter system set log_archive_dest_1="location=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stdby";

*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16033: parameter LOG_ARCHIVE_DEST_1 destination cannot be the same as
parameter LOG_ARCHIVE_DEST_4 destination

##########################
#  Error Occurred
##########################

Error occured while trying to modify log_archive_dest_1 parameter to resolve archive gaps issue in our standby database

##########################
## Command Executed
##########################

In Sqlplus,

Alter system set log_archive_dest_1="location=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stdby";


##########################
## Issue Description.
##########################

###

Error occurred because, there is already a destination  defined with the same physical location. so we need to define a new value.

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_4                   string      LOCATION=USE_DB_RECOVERY_FILE_
                                                 DEST VALID_FOR=(ALL_LOGFILES,A
                                                 LL_ROLES) DB_UNIQUE_NAME=stdby

##########################
## Solution
##########################

Either change the value for LOG_ARCHIVE_DEST_4 to a different path or reset LOG_ARCHIVE_DEST_1 to a different value.

Alter system set log_archive_dest_4="location=/u01/app/oracle/11upgrade/stdby/rman_standby VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stdby";

ORA-32018: parameter cannot be modified in memory on another instance

##########################
## Error
##########################

ORA-32018: parameter cannot be modified in memory on another instance

### Full Error

SQL> Alter system set large_pool_size=1.3g;
Alter system set large_pool_size=1.3g
*
ERROR at line 1:
ORA-32018: parameter cannot be modified in memory on another instance

##########################
#  Error Occurred
##########################

Error occured while trying to modify the large_pool_size parameter in RAC database,

##########################
## Command Executed
##########################

Alter system set large_pool_size=1.3g;

(or)

Alter system set large_pool_size=1.3g sid='*';

##########################
## Issue Description.
##########################

###

Parameter cant be changed in another instance, needs to be changed by logging to each instance.

##########################
## Solution
##########################

Mention the SID of that particular instance to be changed. Do the same in other instances by changing their instance names.

Alter system set large_pool_size=1300m sid='rac11gn1';

ORA-00245: control file backup failed; target is likely on a local file system

Full database backup of 2 node RAC database failed when its trying to run autobackup of controlfile.

##########################
#   Errors 
##########################

Starting Control File and SPFILE Autobackup at 2013-09-04:21:25:34
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of Control File and SPFILE Autobackup command on ORA_SBT_TAPE_1 channel at 09/04/2013 21:25:36
ORA-00245: control file backup failed; target is likely on a local file system

##########################
#   Command Used  
##########################

Controlfile Autobackup.

##########################
#   Informations  
##########################

From 11gR2 onwards, the controlfile backup happens without holding the controlfile enqueue. For non-RAC database, this doesn't change anything. But for RAC database, due to the changes made to the controlfile backup mechanism in 11gR2, any instance in the cluster may write to the snapshot controlfile. Due to this snapshot controlfile need to be visible to all instances. 


The snapshot controlfile MUST be accessible by all nodes of a RAC database, if the snapshot controlfile does not reside on a shared device error will be raised at the time of RMAN backup while taking snapshot of controlfile.

##########################

#  Solution 
##########################

 configure snapshot controlfile name to '+ORALIN_RECO/snapcf_oralin.f';  

show snapshot controlfile name;

RMAN> show snapshot controlfile name;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name oralin are:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2/db_1/dbs/snapcf_oralin1.f'; # default


configure snapshot controlfile name to '+ORALIN_RECO/snapcf_oralin.f';

RMAN> configure snapshot controlfile name to '+ORALIN_RECO/snapcf_oralin.f';

using target database control file instead of recovery catalog
new RMAN configuration parameters:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+ORALIN_RECO/snapcf_oralin.f';

new RMAN configuration parameters are successfully stored.

After setting snapshot controlfile to Shared location (diskgroup) all the backups are successful.

ORA-00955: name is already used by an existing object

While doing the Production deployment with the scripts given by application team, encountered the error.

This error which initially looked like strange but then came to know that Oracle works Like this..

##########################
#   Errors 
##########################

ORA-00955: name is already used by an existing object

##########################
#   Command Used  
##########################


SQL> ALTER TABLE tracking ADD CONSTRAINT tracking_pk PRIMARY KEY (menu_id,asset_version);
ALTER TABLE tracking ADD CONSTRAINT tracking_pk PRIMARY KEY (menu_id,asset_version)
*
ERROR at line 1:
ORA-00955: name is already used by an existing object


##########################
#   Informations  

##########################

Initially i thought that a constraint exists with the same name TRACKING_PK 
But there is no constraint created with the same name in this schema. 

So started to Dig more on this...

##########################

#  Solution 
##########################

  Drop the indexes which were previously created as a part of Primary Key Constraint. 

The constraint which we are creating is a Primary Constriant. 

When we create a Primary Constraint, Oracle Creates 2 objects.

1) Constraint
2) Index

These 2 objects controls the uniqueness in the table.

So i checked DBA_INDEXES and found that a index with the same name TRACKING_PK still exists in the database which is not allowing to create a constraint with the same name TRACKING_PK. 

Select owner,index_name,index_type,table_owner,table_name from dba_indexes where table_name='tracking' and owner='AU_USER';

OWNER                          INDEX_NAME                     TABLE_OWNER                    TABLE_NAME 

------------------------------ ------------------------------------------------------------ 

AU_USER       tracking_PK                      AU_USER       tracking   

So Dropped the index and added the constraint and it worked.

Drop index "AU_USER"."tracking_PK";



SQL> ALTER TABLE tracking ADD CONSTRAINT tracking_pk PRIMARY KEY (menu_id,asset_version);

Table altered.

Oradim -edit -sid gives Unable to start service, OS Error 1056

Every Time i restart the system, i need to bring up the Oracle instance service either from services.msc or using netstart.

So i have decided to make it auto start whenever i restart the system.

##########################
#   Errors 
##########################

Unable to start service, OS Error 1056

##########################
#   Command Used  
##########################

oradim -edit -sid orawin -startmode auto -srvcstart system


##########################
#   Informations  
##########################

I have used this command before in 10g but it didnt give any errors, But when i tried it in 11.2.0.1.0 version of database, getting this error. This seems to be Bug 9584383 and its expected if a service is edited when its running.

But in my case, i have tried by stopping the service also still  the error comes. So whether a service is running or not running, when we try to modify the startmode of a service we will get an error.

##########################

#  Solution 
##########################

  We don't have to worry about the error because eventhough we get an error while modifying the service startmode, the command does it purpose, Instance Service startmode gets changed when we issue the command. 

This bug is fixed in.

• 12.1 (Future Release)
• 11.2.0.2 (Server Patch Set)
• 11.2.0.1 Patch 2 on Windows Platforms

Change the Database Service Startmode from Manual to Automatic.

oradim -edit -sid orawin -startmode auto -srvcstart system

Change the Database Service Startmode from Automatic to Manual.

oradim -edit -sid orawin -startmode manual -srvcstart demand

Below Link will be useful if you want start the service using commands like oradim and net start.

http://stepintooracledba.blogspot.com/2010/05/there-are-two-methods-to-start-database.html