Tales From A Lazy Fat DBA

Its all about Databases, their performance, troubleshooting & much more …. ¯\_(ツ)_/¯

Posts Tagged ‘Errors’

« Previous Entries
Next Entries »

EMCTL Error (OC4J Configuration Issue)

Posted by FatDBA on April 12, 2013

Noticed that some of my test databases monitored in OEM Grid Control started showing status of ‘Agent Unreachable’. This is what we are getting when we tried checking status of the Agent on the servers …

[oracle@prashant ~]$ emctl status dbconsole
OC4J Configuration issue. /u01/app/oracle/product/11.2.0/db_1/oc4j/j2ee/OC4J_DBConsole_prashant_orcl not found.

Below provided steps are sequence of steps that we should adopt in such scenario and will help us to fix the issue most of the times.

  • Set the SID
  • Drop/Deconfig current EM repository.
  • Recreate EM repository for the same DB.
  • Re-check the Status using EMCTL commands for both Dbconsole & Agent

1. Set the SID
[oracle@prashant ~]$ echo $ORACLE_SID
orcl

2. Drop/Deconfig current EM repository.
This step will require you to feed deconfig process following details.
SID, Listener Port Number, SYS and SYSMAN Password.

[oracle@prashant ~]$ emca -deconfig dbcontrol db -repos drop;

STARTED EMCA at Apr 12, 2013 10:40:14 PM
EM Configuration Assistant, Version 11.2.0.0.2 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Enter the following information:
Database SID: orcl
Listener port number: 1521
Password for SYS user:
Password for SYSMAN user:

Do you wish to continue? [yes(Y)/no(N)]: Y

Apr 12, 2013 10:41:14 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/app/oracle/cfgtoollogs/emca/REDANT/emca_2013_04_12_22_40_14.log.
Apr 12, 2013 10:41:14 PM oracle.sysman.emcp.EMDBPreConfig performDeconfiguration
WARNING: EM is not configured for this database. No EM-specific actions can be performed.
Apr 12, 2013 10:41:14 PM oracle.sysman.emcp.ParamsManager checkListenerStatusForDBControl
WARNING: Error initializing SQL connection. SQL operations cannot be performed
Apr 12, 2013 10:41:14 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Dropping the EM repository (this may take a while) …

Apr 12, 2013 10:45:42 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully dropped
Enterprise Manager configuration completed successfully
FINISHED EMCA at Apr 12, 2013 10:45:43 PM

3. Recreate EM repository for the same DB.
This step will require you to feed config process following details.
SID, Listener Port Number, Email address (Optional), SMTP server details (Optional) along with SYS and DBSNMP and SYSMAN Password.

[oracle@prashant ~]$ emca -config dbcontrol db -repos create;

STARTED EMCA at Apr 12, 2013 10:46:17 PM
EM Configuration Assistant, Version 11.2.0.0.2 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Enter the following information:
Database SID: orcl
Listener port number: 1521
Listener ORACLE_HOME [ /u01/app/oracle/product/11.2.0/db_1 ]:
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
—————————————————————–

You have specified the following settings

Database ORACLE_HOME ……………. /u01/app/oracle/product/11.2.0/db_1

Local hostname ……………. prashant
Listener ORACLE_HOME ……………. /u01/app/oracle/product/11.2.0/db_1
Listener port number ……………. 1521
Database SID ……………. orcl
Email address for notifications ……………
Outgoing Mail (SMTP) server for notifications ……………

—————————————————————–
Do you wish to continue? [yes(Y)/no(N)]:

Apr 12, 2013 10:46:59 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/app/oracle/cfgtoollogs/emca/REDANT/emca_2013_04_12_22_46_16.log.
Apr 12, 2013 10:47:00 PM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) …

Apr 12, 2013 10:54:15 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Apr 12, 2013 10:54:28 PM oracle.sysman.emcp.EMReposConfig uploadConfigDataToRepository
INFO: Uploading configuration data to EM repository (this may take a while) …
Apr 12, 2013 10:55:43 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Uploaded configuration data successfully
Apr 12, 2013 10:55:47 PM oracle.sysman.emcp.util.DBControlUtil configureSoftwareLib
INFO: Software library configured successfully.
Apr 12, 2013 10:55:47 PM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary
INFO: Deploying Provisioning archives …
Apr 12, 2013 10:56:06 PM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary
INFO: Provisioning archives deployed successfully.
Apr 12, 2013 10:56:06 PM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Securing Database Control (this may take a while) …
Apr 12, 2013 10:56:40 PM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Database Control secured successfully.
Apr 12, 2013 10:56:40 PM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) …

Apr 12, 2013 10:57:23 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Apr 12, 2013 10:57:23 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is https://prashant:5500/em <<<<<<<<<<<
Apr 12, 2013 10:57:32 PM oracle.sysman.emcp.EMDBPostConfig invoke
WARNING:
************************  WARNING  ************************

Management Repository has been placed in secure mode wherein Enterprise Manager data will be encrypted.  The encryption key has been placed in the file: /u01/app/oracle/product/11.2.0/db_1/prashant_REDANT/sysman/config/emkey.ora.   Please ensure this file is backed up as the encrypted data will become unusable if this file is lost.

***********************************************************
Enterprise Manager configuration completed successfully

4. Re-check the Status using EMCTL commands for both Dbconsole & Agent.

[oracle@prashant ~]$ emctl status dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.1.0
Copyright (c) 1996, 2009 Oracle Corporation.  All rights reserved.
https://prashant:5500/em/console/aboutApplication
Oracle Enterprise Manager 11g is running.
——————————————————————
Logs are generated in directory /u01/app/oracle/product/11.2.0/db_1/prashant_REDANT/sysman/log

[oracle@prashant ~]$ emctl status agent
Oracle Enterprise Manager 11g Database Control Release 11.2.0.1.0
Copyright (c) 1996, 2009 Oracle Corporation.  All rights reserved.
—————————————————————
Agent Version     : 10.2.0.4.2
OMS Version       : 10.2.0.4.2
Protocol Version  : 10.2.0.4.2
Agent Home        : /u01/app/oracle/product/11.2.0/db_1/prashant_REDANT
Agent binaries    : /u01/app/oracle/product/11.2.0/db_1
Agent Process ID  : 26794
Parent Process ID : 26757
Agent URL         : https://prashant:3938/emd/main
Repository URL    : https://prashant:5500/em/upload/
Started at        : 2013-04-12 22:56:42
Started by user   : oracle
Last Reload       : 2013-04-12 22:56:42
Last successful upload                       : 2013-04-12 22:58:34
Total Megabytes of XML files uploaded so far :    10.04
Number of XML files pending upload           :        0
Size of XML files pending upload(MB)         :     0.00
Available disk space on upload filesystem    :    26.87%
Data channel upload directory                : /u01/app/oracle/product/11.2.0/db_1/prashant_REDANT/sysman/recv
Last successful heartbeat to OMS             : 2013-04-12 22:58:24
—————————————————————
Agent is Running and Ready

 

OEM Final screen is up!

Untitled

Posted in Advanced | Tagged: , | Leave a Comment »

Recovery Error: ORA-01207: file is more recent than control file – old control file

Posted by FatDBA on January 21, 2013

Solution steps when you started receiving ORA-01207.

Reason: The control file change sequence number in the data file is greater than the number in the control file. This implies that the wrong control file is being used. Note that repeatedly causing this error can make it stop happening without correcting the real problem. Every attempt to open the database will advance the control file change sequence number until it is great enough.

Scenario: In my scenario one of our SIT box lost all the copies of multiplexed ControlFiles from the system and after couple of minutes Database got crashed. While trying fixing the problem we discovered that we did’nt have the backup copies of Control Files Available, but we had a mount point backup available which holds all backup information but due to issues with our SMARTS (Monitoring Tool) backup script got failed to update the control file backup. So we didn’t knew that the ControlFile backup available was too old and have a very old sequence number registered.

So, after restoring the controlfile from that outdated backup we started recieving this error message during Databse OPEN:

SQL> alter database open;

ORA-00283: recovery session canceled due to errors
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: ‘/app/oracle/oradata/rac10g/system01.dbf’
ORA-01207: file is more recent than control file – old control file

Solution: recreate the control file.

1) dump controlfile to trace
SQL> alter database backup controlfile to trace as ‘/tmp/ccc.sql’;

2) startup database nomount
SQL> startup nomount

3) create the control file
SQL> @/tmp/ccc.sql
Control file created.

sys@SIT4435Z89> recover database using backup controlfile;

SQL> recover database using backup controlfile ;
ORA-00279: change 1904510 generated at 12/11/2012 15:46:23 needed for thread 1
ORA-00289: suggestion : +FRA
ORA-00280: change 1904510 for thread 1 is in sequence #62

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
+DATA/orcl/onlinelog/group_1.261.801074115
ORA-00310: archived log contains sequence 61; sequence 62 required
ORA-00334: archived log: ‘+DATA/orcl/onlinelog/group_1.261.801074115’

Now it is asking for Log sequence 62 which is not available under archive dest
ASMCMD> ls
thread_1_seq_52.314.801739289
thread_1_seq_53.294.801743225
thread_1_seq_54.295.801743259
thread_1_seq_55.285.801743403
thread_1_seq_56.283.801757235
thread_1_seq_57.282.801758303
thread_1_seq_58.281.801759635
thread_1_seq_59.286.801761231
thread_1_seq_60.305.801762327
thread_1_seq_61.296.801762385

We only have seq:61 available.

Hint: It could be still inside REDO Log groups and is not archived before the crash or Control file loss.
Hence we’ll check both of the two avialable redo groups to find seq:62
+DATA/orcl/onlinelog/group_1.261.801074115
+DATA/orcl/onlinelog/group_2.262.801074117

SQL> startup  mount
ORACLE instance started.

Total System Global Area 1636814848 bytes
Fixed Size                  2213856 bytes
Variable Size            1140852768 bytes
Database Buffers          486539264 bytes
Redo Buffers                7208960 bytes
Database mounted.
SQL> recover database using backup controlfile ;
ORA-00279: change 1904510 generated at 12/11/2012 15:46:23 needed for thread 1
ORA-00289: suggestion : +FRA
ORA-00280: change 1904510 for thread 1 is in sequence #62

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
+DATA/orcl/onlinelog/group_1.261.801074115
ORA-00310: archived log contains sequence 61; sequence 62 required
ORA-00334: archived log: ‘+DATA/orcl/onlinelog/group_1.261.801074115’

SQL> recover database using backup controlfile ;
ORA-00279: change 1904510 generated at 12/11/2012 15:46:23 needed for thread 1
ORA-00289: suggestion : +FRA
ORA-00280: change 1904510 for thread 1 is in sequence #62

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
+DATA/orcl/onlinelog/group_2.262.801074117
Log applied.
Media recovery complete.

Shows it was available under redo group: 2 (could be Current or Active) and it is successfully applied.

Finally we’d open DB in reset log mode.

SQL> alter database open resetlogs ;
Database altered.

Posted in Advanced | Tagged: , | Leave a Comment »

KEEP Clause * Error: ORA-19811: cannot have files in DB_RECOVERY_FILE_DEST with keep attributes*

Posted by FatDBA on January 13, 2013

KEEP option with RMAN:
If you have any backup criterion set and want to override settings then there is ‘KEEP FOREVER | UNTIL TIME’ clause from recovery manager which will help you to achieve required. There are certain conditions that’s needs to be fulfilled before using FOREVER clause.

Conditions:
1. You should be connected with the CATALOG server  (If using KEEP FOREVER Clause. No need if using KEEP UNTIL TIME Clause).
2. Oracle will not allow you to save backup pieces/set’s inside the FRA. You have to change the backup directory.

Let’s take an example:

[oracle@localhost ~]$ rman target / catalog rman/oracle90@orcl;
RMAN> backup current controlfile keep forever;

Starting backup at 13-JAN-13
current log archived

using channel ORA_DISK_1
backup will never be obsolete
archived logs required to recover from this backup will be backed up
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 13-JAN-13
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 01/13/2013 18:35:23
ORA-19811: cannot have files in DB_RECOVERY_FILE_DEST with keep attributes

Solution:
RMAN> backup current controlfile keep forever format ‘/u01/%U’;

Starting backup at 13-JAN-13
current log archived

using channel ORA_DISK_1
backup will never be obsolete
archived logs required to recover from this backup will be backed up
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 13-JAN-13
channel ORA_DISK_1: finished piece 1 at 13-JAN-13
piece handle=/u01/20nvb5gk_1_1 tag=TAG20130113T183811 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

using channel ORA_DISK_1
backup will never be obsolete
archived logs required to recover from this backup will be backed up
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 13-JAN-13
channel ORA_DISK_1: finished piece 1 at 13-JAN-13
piece handle=/u01/21nvb5gm_1_1 tag=TAG20130113T183811 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

current log archived
using channel ORA_DISK_1
backup will never be obsolete
archived logs required to recover from this backup will be backed up
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=66 RECID=39 STAMP=804623897
channel ORA_DISK_1: starting piece 1 at 13-JAN-13
channel ORA_DISK_1: finished piece 1 at 13-JAN-13
piece handle=/u01/22nvb5gq_1_1 tag=TAG20130113T183811 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

using channel ORA_DISK_1
backup will never be obsolete
archived logs required to recover from this backup will be backed up
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 13-JAN-13
channel ORA_DISK_1: finished piece 1 at 13-JAN-13
piece handle=/u01/23nvb5gr_1_1 tag=TAG20130113T183811 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 13-JAN-13

Posted in Advanced | Tagged: , , | Leave a Comment »

ORA-03113: end-of-file on communication channel **During ASM Instance startup**

Posted by FatDBA on January 8, 2013

Faced one more end-of-file communication error with one of my Test ASM Environment. Below are the steps i’ve tried to fix the problem.
Error:
[oracle@localhost bin]$ sqlplus / as sysasm

SQL*Plus: Release 11.2.0.1.0 Production on Tue Jan 8 20:00:25 2013
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> ORA-03113: end-of-file on communication channel
1st Step:

I’ve tried to check the CRS (Cluster Restart Service or Oracle Restart Service) of instance. Using crs_stats you can get a list of all services which are linked with CRS with their respective state and target details.
(Always look for STATE & TARGET out of other stats provided and try to fix ‘Types’ with ‘OFFLINE’ state).

Example:
[oracle@localhost bin]$  crs_stat -t

Name           Type           Target    State     Host
————————————————————
ora.cssd       ora.cssd.type  OFFLINE   OFFLINE
ora.diskmon    ora….on.type OFFLINE   OFFLINE
ora.asm        ora….on      ONLINE    OFFLINE

* Clearly reflects broken ora.asm service is broken and is OFFLINE right now.

2nd Step:
Forcibly de-configure CRS Service via Root user.
(You’ll find the Perl script in GRID Home/crs/install directory)

Example:
[root@localhost install]# ./rootcrs.pl -deconfig -force
2013-01-08 19:53:59: Parsing the host name
2013-01-08 19:53:59: Checking for super user privileges
2013-01-08 19:53:59: User has super user privileges
Using configuration parameter file: ./crsconfig_params
Usage: srvctl <command> <object> [<options>]
commands: enable|disable|start|stop|status|add|remove|modify|getenv|setenv|unsetenv|config
objects: database|service|asm|diskgroup|listener|home|ons|eons
For detailed help on each command and object and its options use:
srvctl <command> -h or
srvctl <command> <object> -h
PRKO-2012 : nodeapps object is not supported in Oracle Restart
ACFS-9200: Supported
CRS-2613: Could not find resource ‘ora.registry.acfs’.
CRS-4000: Command Stop failed, or completed with errors.
CRS-4013: This command is not supported in a single-node configuration.
CRS-4000: Command Stop failed, or completed with errors.
You must kill crs processes or reboot the system to properly
cleanup the processes started by Oracle clusterware
error: package cvuqdisk is not installed
Successfully deconfigured Oracle clusterware stack on this node

Third Step:
Re-run root.sh script using Root account. This will configure OCR (Oracle Cluster Registry) keys for oracle user and restart ohasd (Oracle High Availability Service Deamon) again.

* Oracle Clusterware is run by Cluster Ready Services (CRS) which consisting of  Oracle Cluster Registry (OCR), which records and maintains the cluster and node membership information. Records Disk membership information in case of ASM configured on Non-RAC systems.

Example:
[root@localhost dbhome_1]# ./root.sh
Running Oracle 11g root.sh script…

The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME=  /u01/app/oracle/product/11.2.0/dbhome_1

Enter the full pathname of the local bin directory: [/usr/local/bin]:
The file “dbhome” already exists in /usr/local/bin.  Overwrite it? (y/n)
[n]:
The file “oraenv” already exists in /usr/local/bin.  Overwrite it? (y/n)
[n]:
The file “coraenv” already exists in /usr/local/bin.  Overwrite it? (y/n)
[n]:

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.sh script.
Now product-specific root actions will be performed.
2013-01-08 19:55:58: Checking for super user privileges
2013-01-08 19:55:58: User has super user privileges
2013-01-08 19:55:58: Parsing the host name
Using configuration parameter file: /u01/app/oracle/product/11.2.0/dbhome_1/crs/install/crsconfig_params
LOCAL ADD MODE
Creating OCR keys for user ‘oracle’, privgrp ‘oinstall’..
Operation successful.
CRS-4664: Node localhost successfully pinned.
Adding daemon to inittab
CRS-4123: Oracle High Availability Services has been started.
ohasd is starting
acfsroot: ACFS-9301: ADVM/ACFS installation can not proceed:

acfsroot: ACFS-9302: No installation files found at /u01/app/oracle/product/11.2.0/dbhome_1/install/usm/EL5/i386/2.6.18-8/2.6.18-8.el5uek-i686/bin.

localhost     2013/01/08 19:56:20     /u01/app/oracle/product/11.2.0/dbhome_1/cdata/localhost/backup_20130108_195620.olr
Successfully configured Oracle Grid Infrastructure for a Standalone Server
Updating inventory properties for clusterware
Starting Oracle Universal Installer…

Checking swap space: must be greater than 500 MB.   Actual 2047 MB    Passed
The inventory pointer is located at /etc/oraInst.loc
The inventory is located at /u01/app/oraInventory
‘UpdateNodeList’ was successful.
You have new mail in /var/spool/mail/root

Step 4th:
Start cssd and diskmon services.

* Oracle Cluster Synchronization Services daemon (OCSSd) provides basic ‘group services’ support. This is also required in a single instance configuration if Automatic Storage Management (ASM) is use.
* diskmon (Disk Monitor daemon): Responsibel for performing Monitoring activities. The diskmon daemon is always started when ocssd starts.

[oracle@localhost ~]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/bin
[oracle@localhost bin]$ ./crsctl start resource ora.cssd
CRS-2672: Attempting to start ‘ora.cssd’ on ‘localhost’
CRS-2672: Attempting to start ‘ora.diskmon’ on ‘localhost’
CRS-2676: Start of ‘ora.diskmon’ on ‘localhost’ succeeded
CRS-2676: Start of ‘ora.cssd’ on ‘localhost’ succeeded

Step 5th:
Let’s try to Mount the ASM Diskgroup.
ASMCMD> mount DATA

(If not mounted, you’ll probably receive error codes: ORA-15110: ‘no diskgroups mounted’ and will be fixed once you mount Diskgroup).

Step 6th:
Finally start your ASM instance.

[oracle@localhost bin]$ sqlplus / as sysasm

SQL*Plus: Release 11.2.0.1.0 Production on Tue Jan 8 20:00:25 2013
Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
With the Automatic Storage Management option

SQL>

Done…

Posted in Advanced | Tagged: , | Leave a Comment »

ORA-03113: end-of-file on communication channel **Error due to insufficient Disk Space in ASM DGroup**

Posted by FatDBA on January 7, 2013

Steps to fix an ASM related issue/error when you failed to start an Instance configured on ASM Disk Groups.

Error Message:
ORA-03113: end-of-file on communication channel

When (Live/Real-Time Situation):
[oracle@localhost trace]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Mon Jan 7 20:43:52 2013
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1337720 bytes
Variable Size             239077000 bytes
Database Buffers          289406976 bytes
Redo Buffers                5840896 bytes
Database mounted.

ORA-03113: end-of-file on communication channel
Process ID: 10910
Session ID: 21 Serial number: 3

How to track the error message:
As always Alert log for that instance.

Snippetsfrom AlertLog:
Errors in file /u01/app/oracle/diag/rdbms/etr/etr/trace/etr_ora_6969.trc:
ORA-19816: WARNING: Files may exist in db_recovery_file_dest that are not known to database.
ORA-17502: ksfdcre:4 Failed to create file +DATA
ORA-15041: diskgroup “DATA” space exhausted
*************************************************************
WARNING: A file of type ARCHIVED LOG may exist in
db_recovery_file_dest that is not known to the database.
Use the RMAN command CATALOG RECOVERY AREA to re-catalog
any such files. If files cannot be cataloged, then manually
delete them using OS command. This is most likely the
result of a crash during file creation.
*************************************************************
Errors in file /u01/app/oracle/diag/rdbms/etr/etr/trace/etr_ora_6969.trc:
ORA-19504: failed to create file “+DATA”
ORA-17502: ksfdcre:4 Failed to create file +DATA
ORA-15041: diskgroup “DATA” space exhausted
ARCH: Error 19504 Creating archive log file to ‘+DATA’

Results: It clearly says about Space Exhaustion in Line Number 15 with error message ‘ORA-15041: diskgroup “DATA” space exhausted’
Let’s understand this error message using OERR Utlity:

[oracle@localhost ~]$ oerr ORA 15041
15041, 00000, “diskgroup \”%s\” space exhausted”
// *Cause:  The diskgroup ran out of space.
// *Action: Add more disks to the diskgroup, or delete some existing files.

Let’s Verify space of ASM Disk Group:
SQL> select name, state, type, total_mb, free_mb from v$asm_diskgroup;

NAME                                     STATE          TYPE     TOTAL_MB    FREE_MB
—————————— ———–        ——         ———-    ————————————
DATA                                  MOUNTED     NORMAL       4000         17

Only 17 MB of Free Space is left in Disk Group.

Resolution:
Create a new RAW Disk and alter Diskgroup to add that newly created rdisk and create some extra space.

SQL> alter diskgroup DATA add disk ‘/dev/raw/raw6’;
Diskgroup altered.

SQL> select name, state, type, total_mb, free_mb from v$asm_diskgroup;

NAME                                       STATE          TYPE     TOTAL_MB    FREE_MB
—————————— ———–        ——           ———- ——————————-
DATA                                     MOUNTED     NORMAL       5000       1015

Let’s try to start the same instance again:

SQL> startup
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1337720 bytes
Variable Size             239077000 bytes
Database Buffers          289406976 bytes
Redo Buffers                5840896 bytes
Database mounted.

Database opened.

Fixed.

Posted in Advanced | Tagged: , | Leave a Comment »

Recovering Parameter File

Posted by FatDBA on January 2, 2013

Today while working discovered that both my Pfile and SPfile from one of my test database are missing which we realized after a bounce, when it failed to start and started throwing error message about loss with it’s previous location.

ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file ‘/u01/app/oracle/product/10.2.0/dbhome_2/dbs/initorcl.ora’

I tried starting my database from RMAN console to recover my SPfile from my latest Autobackup’s, which is not possible until the database is in atleast nomount mode by Recovery Manager.  This is not possible without parameter file hence Oracle allows RMAN to start-up related instance in nomount mode with the help of a DUMMY parameter file which it creates in it’s default location ($ORACLE_HOME/dbs).

The purpose of this DUMMY file is to aid restoration of SPfile from available backup’s. The file contains below information (10.2.0.1 Database)

db_name=DUMMY
remote_login_passwordfile=exclusive
compatible=10.2.0.1.0
sga_target=150M
_dummy_instance=TRUE

connected to target database (not started)
RMAN> startup nomount;
OR
RMAN> startup force nomount;

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file ‘/u01/app/oracle/product/11.2.0/dbhome_2/dbs/initorcl.ora’

starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started

Total System Global Area     159019008 bytes

Fixed Size                     1335192 bytes
Variable Size                 75497576 bytes
Database Buffers              79691776 bytes
Redo Buffers                   2494464 bytes

Now you can retrieve your missing server parameter files from backup’s.

The another way to recover Parameter file when there is not any backup’s available is through the
1. Alert Log:
Database always copies all entries from pfile to alert log during every startup and shutdown of database.

2. If you have any recent stats reports available like: AWR (Automated Workload Repository) etc. :
It always contains PFile snap at the end of the report.

 

Posted in Advanced | Tagged: , | Leave a Comment »

ORA-00845: MEMORY_TARGET not supported on this system

Posted by FatDBA on December 30, 2012

Today while working on one of my practice machine, I’ve started receiving error when tried to start-up one of the instance which is on Cooked File system. Error reads about MEMORY_TARGET.  To be precise about the error message and code, below is what i was getting second ago.

SQL> startup
ORA-00845: MEMORY_TARGET not supported on this system

Below are the steps in sequence what i performed to test and mitigate the error.

1. I’ve checked the pfile of the instance to check MEMORY_TARGET entires and values to discover if there is any problem with the values assigned (I have the AMM enabled in my machine)
*.memory_target=715128832

2. Then i’ve checked error details more closely using OERR utlity to find the cause of the problem and suggestions.

[oracle@localhost dbs]$ oerr ORA 845
00845, 00000, “MEMORY_TARGET not supported on this system”
// *Cause: The MEMORY_TARGET parameter was not supported on this operating system or /dev/shm was not sized correctly on Linux.
// *Action: Refer to documentation for a list of supported operating systems. Or, size /dev/shm to be at least the SGA_MAX_SIZE on each Oracle instance running on the system.

Error clearly points towards the /dev/shm size on OS and asking to set the size of it to atleast SGA_MAX_SIZE on instance running which is 684m.

3. Now when we have the Cause in hand let’s try to fix the problem.

let me check current settings.

[oracle@localhost dbs]$ df -h
Filesystem            Size    Used       Avail            Use%        Mounted on
/dev/mapper/VolGroup00-LogVol00
30G   23G        6.0G            80%         /
/dev/sda1              99M   55M       40M            58%          /boot
tmpfs                 664M  154M   510M         24%        /dev/shm        /*tmps (Temp File Storage area) is in MB’s – CAUSE CLEARED*/

And it’s 664M which is clearly below the SGA_MAX_SIZE = 684m

Some more checks:
[root@localhost ~]# more /etc/fstab
/dev/VolGroup00/LogVol00 /                       ext3    defaults        1 1
LABEL=/boot             /boot                   ext3    defaults        1 2
tmpfs                   /dev/shm                tmpfs   defaults        0 0
devpts                  /dev/pts                devpts  gid=5,mode=620  0 0
sysfs                   /sys                    sysfs   defaults        0 0
proc                    /proc                   proc    defaults        0 0
/dev/VolGroup00/LogVol01 swap                    swap    defaults        0 0

4. Let’s try to add up some memory space to tmpfs area.
I’ve added exact 1GB to /etc/fstab entry. Let me check the change

[root@localhost ~]# vi /etc/fstab

[root@localhost ~]# more /etc/fstab
/dev/VolGroup00/LogVol00 /                       ext3    defaults        1 1
LABEL=/boot             /boot                   ext3    defaults        1 2
tmpfs                   /dev/shm                tmpfs   defaults,size=1G        0 0
devpts                  /dev/pts                devpts  gid=5,mode=620  0 0
sysfs                   /sys                    sysfs   defaults        0 0
proc                    /proc                   proc    defaults        0 0
/dev/VolGroup00/LogVol01 swap                    swap    defaults        0 0

Alrighty, to fix the settings/changes let’s remount /dev/shm

[root@localhost ~]# mount -o remount /dev/shm
[root@localhost ~]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
30G   23G  6.0G  80% /
/dev/sda1              99M   55M   40M  58% /boot
tmpfs                 1.0G  154M  4.9G   4% /dev/shm

D.O.N.E

5. Now we are done with the change, let’s try to start the instance once again.

[oracle@localhost ~]$ . oraenv
ORACLE_SID = [orcl] ? orcl
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_2 is /u01/app/oracle

SQL> startup
ORACLE instance started.

Total System Global Area  715616256 bytes
Fixed Size                  1338924 bytes
Variable Size             482345428 bytes
Database Buffers          226492416 bytes
Redo Buffers                5439488 bytes
Database mounted.
Database opened.

It’s UP!!

Posted in Advanced | Tagged: | Leave a Comment »

ORA-01555: Snapshot Too Old

Posted by FatDBA on November 7, 2012

Reasons:
The ORA-01555 error can occur when a long read only transaction is run against database
and there are many DML transactions being executed on database (on same data).
The longer query runs, there are more chances of encountering ORA-01555 exception.

The ORA-01555 is caused by Oracle “Read Consistency Mechanism”.
Oracle provides read consistency by reading the “before image” of updated data
from “Online UNDO Segments”. If there are lots of updates, long running read-only SQL
and a small UNDO, the ORA-01555 error may encounter.

ORA-01555 can be avoided by following precautions:
1. Database should be in Automatic Undo Management mode.
2. Use a large optimal value for undo_retention.
3. Use a large optimal size of rollback segment (undo) size.
4. Should Commit less often at the time of long running query, to reduce Undo Segment slot reuse.
5. Try to run long running queries on off peak hours, when there is less DML transactions

You can use the retention guarantee parameter (introduced in Oracle 10g) of the create UNDO Tablespace or alter UNDO Tablespace command, as shown in these examples:

-- Assumes OMF is configured.
Create UNDO Tablespace undotbs01
size 200m autoextend on
retention guarantee;

alter tablespace undotbs01 retention guarantee;

Use the alter tablespace retention noguarantee command to reset an UNDO Tablespace so that undo retention is no longer guaranteed.

Example:

— Reset the undo low threshold.
ALTER SYSTEM SET UNDO_RETENTION = 2400;

— Guarantee the minimum threshold is maintained.
ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;

SELECT tablespace_name, retention FROM dba_tablespaces;

TABLESPACE_NAME                RETENTION
—————————— —————————————————————–
SYSTEM                             NOT APPLY
UNDOTBS1                       GUARANTEE
SYSAUX                            NOT APPLY
TEMP                               NOT APPLY
USERS                             NOT APPLY

5 rows selected.

— Switch back to the default mode.
ALTER TABLESPACE undotbs1 RETENTION NOGUARANTEE;

TABLESPACE_NAME                RETENTION
—————————— —————————————————————-
SYSTEM                            NOT APPLY
UNDOTBS1                       NOGUARANTEE
SYSAUX                            NOT APPLY
TEMP                               NOT APPLY
USERS                             NOT APPLY

5 rows selected.

Posted in Advanced | Tagged: | Leave a Comment »

Real-Time Apply Error: ORA-38500: ‘USING CURRENT LOGFILE option not available without stand’

Posted by FatDBA on October 25, 2012

Hi Folks,

Going to discuss about one of the most recent problem that i faced while configuring/activating the Real-Time Apply service on one of the standby database.

(Using Oracle 10g r2)
Prior Status:
SQL> SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID=2 ;

RECOVERY_MODE
———————–
MANAGED

To start real-time apply i used CURRENT LOGFILE clause but received below provided error message:

SQL> alter database recover managed standby database disconnect using current logfile;
alter database recover managed standby database disconnect using current logfile
*
ERROR at line 1:
ORA-38500: USING CURRENT LOGFILE option not available without stand

Resolution:
1. I created Log Groups and Log Files for Standby Database to try fix this issue and as per my expectations it worked.

Added Three log groups of same size 50MB each on Primary Database for Standby (Log Groups should be always be of same size – What you have on Primary DB, Mine was – 52428800 (50MB)):

SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 3 SIZE 50M;
Database Altered

SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 SIZE 50M;
Database Altered

SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 SIZE 50M;
Database Altered

SQL> select group#, member, type from v$logfile;

GROUP#         MEMBER                                                                        TYPE
———- ———————————————————————————————————————————–
4       /u01/app/oracle/flash_recovery_area/DOOM/onlinelog/o1_mf_4_88j4601r_.log                      STANDBY
3       /u01/app/oracle/flash_recovery_area/DOOM/onlinelog/o1_mf_3_88j4859l_.log                      STANDBY
5       /u01/app/oracle/flash_recovery_area/DOOM/onlinelog/o1_mf_5_88j4d4t5_.log                      STANDBY

On Standby Database:
2. Added a standby logfile on Std DB end:

SQL> alter database add standby logfile ‘/u01/app/oracle/admin/doom/stdy_log.log’ size 10m;
Database altered.

SQL> alter database recover managed standby database disconnect using current logfile;
Database altered.

SQL> SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID=2 ;
RECOVERY_MODE
———————–
MANAGED REAL TIME APPLY

SQL> select RECOVERY_MODE, DATABASE_MODE, PROTECTION_MODE from v$archive_dest_status;

RECOVERY_MODE           DATABASE_MODE   PROTECTION_MODE
———————– ————— ——————–
MANAGED REAL TIME APPLY MOUNTED-STANDBY MAXIMUM PERFORMANCE
MANAGED REAL TIME APPLY MOUNTED-STANDBY MAXIMUM PERFORMANCE
MANAGED REAL TIME APPLY MOUNTED-STANDBY MAXIMUM PERFORMANCE
MANAGED REAL TIME APPLY MOUNTED-STANDBY MAXIMUM PERFORMANCE
MANAGED REAL TIME APPLY MOUNTED-STANDBY MAXIMUM PERFORMANCE
MANAGED REAL TIME APPLY MOUNTED-STANDBY MAXIMUM PERFORMANCE
MANAGED REAL TIME APPLY MOUNTED-STANDBY MAXIMUM PERFORMANCE
MANAGED REAL TIME APPLY MOUNTED-STANDBY MAXIMUM PERFORMANCE
MANAGED REAL TIME APPLY MOUNTED-STANDBY MAXIMUM PERFORMANCE
MANAGED REAL TIME APPLY MOUNTED-STANDBY MAXIMUM PERFORMANCE
MANAGED REAL TIME APPLY MOUNTED-STANDBY MAXIMUM PERFORMANCE

11 rows selected.

Posted in Advanced | Tagged: , , | Leave a Comment »

ORA-01157: cannot identify/lock data file (Error)

Posted by FatDBA on September 11, 2012

Today while working i mistakenly deleted one DataFile which is of no use and was not part of any backup as well. I was happy and was good until i started recieving a weird error which is related with this old Datafile which i’ve already deleted from my Database Box and it not allowing me to start the DB instance and throwing errors:

 

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 – Production on Tue Sep 11 09:57:30 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options

ERROR at line 1:
ORA-01157: cannot identify/lock data file 11 – see DBWR trace file
ORA-01110: data file 11: ‘/u01/appwiz.dbf’
So it clearly says that it’s all because of that recent deletion of Data File 11 ‘Appwiz.dbf’ which triggered this problem.
Resolution: I’ve offline deleted that datafile (11) and tried starting the instance and finally

SQL> alter database datafile 11 offline drop;
Database altered.

SQL> alter database open;
Database altered.

SQL> select status from v$instance;

STATUS
————
OPEN

Posted in Advanced | Tagged: | Leave a Comment »

« Previous Entries
Next Entries »