Sunday, October 14, 2018

ORA-00257 archiver error

There are a several archived log errors that exist, and there are several different reasons for these errors as well. As we know that redo log groups are used in a circular fashion and redo logs are reused again and again. After every log switch, the previous redo log file is archived (if database is running in archivelog mode). If oracle is not able to create an archived log file after a redo log switch ORA-00257 would appear, and after a few more redo log switches the turn of this redo log file would come again. Since oracle was not able to archied this redo log previously, it will not overwrite this redo log and database would hang. This redo log must be archived before it could be reused. Alert log would who errors as follows if it is not able to create an archived log file

Fri May 19 12:10:13 2017
Unable to create archive log file '+ARCH'
ARC3: Error 19504 Creating archive log file to '+ARCH'
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance MYDB - Archival Error
ORA-16038: log 6 sequence# 90895 cannot be archived
ORA-19504: failed to create file ""
ORA-00312: online log 6 thread 1: '+REDO1/mydb/onlinelog/group_6.261.860506153'
ORA-00312: online log 6 thread 1: '+REDO2/mydb/onlinelog/group_6.261.860506155'

ORA-19809: limit exceeded for recovery files, and ORA19804
In some cased, following error could appear in the alert log file if archived log destination is set to use fast recovery area and size limit set using db_recovery_file_dest_size has reached.
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 43888640 bytes disk space from 314572800 limit
ARC0: Error 19809 Creating archive log file to 'C:\APP\ORACLE\FAST_RECOVERY_AREA\SALMAN12C\ARCHIVELOG\2017_05_23\O1_MF_1_982_%U_.ARC'
Tue May 23 15:56:02 2017
Errors in file C:\APP\ORACLE\diag\rdbms\salman12c\salman12c\trace\salman12c_arc3_20148.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 314572800 bytes is 100.00% used, and has 0 remaining bytes available.
Tue May 23 15:56:02 2017

  • If you face archiver error, you may consider following points relevant in order to investigate the issue.
  • Make sure you have properly set your log_archive_dest_n parameter and the directory specified in this parameter exists and “oracle” user has read and write privileges on this directory.
  • There is sufficient amount of space available on the file system where log archive destination is specified.
  • If you are using fast recovery area by setting db_recovery_file_dest, make sure that parameter db_recovery_file_dest_size parameter has a value large enough to store archived logs being generated by the database.
  • If you have shortage of space for archive destination, backup your archived log more frequently (using RMAN preferably) and delete old archived log files to make space for more incoming archived log files.
  • If archive destination is on ASM, it is better to provide diskgroup name under log_archive_dest_n (for example ‘+ARCH’). If you provide a directory path like ‘+ARCH\mydest\archive’, then you must make sure that you create this directory manually on the ASM diskgroup.

Friday, October 5, 2018

ORA-48913: Writing into trace file failed, file size limit...

In some environments DBAs limit the size of trace files generated by the database. This included all trace files that could get generated under USER_DUMP_DEST/DIAGNOSTIC_DEST). The parameter to set the limit for trace files is MAX_DUMP_FILE_SIZE and its value is in OS number of blocks. After setting this value, if any trace file size would increase form the size specified in this parameter, ORA-48913 would be recorded in alert log file.

Friday, September 28, 2018

Creating services with srvctl

In a RAC environment we can create services and run these services on a specific RAC instance/node (or a set of instances/nodes). This makes it possible for the applications to connect to specific instances. In this article I would explain how we create a service and make it run on the instance(s) of our choice, and make it automatically failover to another instance/node if current instance node goes down.

Sunday, September 23, 2018

How to Add and Remove database to CRS or Oracle Restart

In RAC or Oracle Restart environment we can use “srvctl” command to add resources to CRS for automatic management of the resources. In this article I would explain how we remove and add a database resource to CRS in RAC environment. Same process can be used for single instance database in Oracle Restart environment. These steps are needed to be executed as RDBMS software owner “oracle”. Starting 12c, single letter options (-d, -i, etc.) can still be used, but probably these would be made obsolete in coming releases because full length option (-db, -instance etc.) have been introduced and I will use these full length options here.

Wednesday, September 19, 2018

CRS-0245: User doesn't have enough privilege to perform the operation

Reason of CRS-0245 is that you are executing “srvctl” command to perform a task that is not authorized for current logged in user. For example, I was trying to remove a RAC database from CRS using srvctl and I faced this error because I was executing this command as “grid” user whereas I was supposed to execute this command as “oracle” user that is RDBMS software owner.

Friday, September 7, 2018

PRKO-3141 : Database could not be removed because it was running

If we use “srvctl” command to remove a database from CRS (in case of RAC) or Oracle Restart (in case of single server GI installation), we might face this error if database is currently  running. To successfully remove a database from CRS using srvctl, either shutdown the database (all instances), or alternatively you can use “–f” (for versions bellow 12c) or “-force” (for 12 and above) option.

Saturday, September 1, 2018

OEM Target Status Pending – OEM Metric Collection Error

Oracle Enterprise Manager Cloud Control is a very handy tool to monitor database servers. Sometimes it happens that Cloud Control does not show the actual status of a target. If status of your database is either “Pending” or “Metric Collection Error”, you can try following trouble shooting steps to resolve the problem. Similar steps may also be followed if targets other than database targets face same issue.

Friday, August 24, 2018

ORA-01145: offline immediate disallowed unless media recovery enabled

I faced this error while I was trying to take a datafile offline and my database was in mount state as well as in noarchivelog mode, at that time. Opening database would return ORA-01157 for this datafile.
SQL> alter database datafile 'd:\oracle\oradata\data01.dbf' offline;
alter database datafile 'd:\oracle\oradata\data01.dbf' offline
ERROR at line 1:
ORA-01145: offline immediate disallowed unless media recovery enabled

Wednesday, August 15, 2018

Creating Standby Database using Duplicate from Active Database

I already have articles written about how to create dataguard and creating dataguard in RAC environment. I used the DUPLICATE DATABASE command using backup, to build the standby database in aforementioned documents. In this article I would provide some tips about building standby database using DUPLICATE with ACTIVE DATABASE option.
Please also refer to this article about duplicate using ACTIVE DATABASE.
Follow all steps same as mentioned in how to create dataguard to build the standby, but remember following points/steps.

Friday, August 10, 2018

Slow tnsping Response and Slow Connections to the Database

We can use TNSPING utility to check connection with the database, for the client side. If you observe that TNSPING is returning very late/slow, and application connection is also very slow, there are a couple of things that you can check. First thing is to use network speed. You can use OS command “ping” to check if network is stable and response is fast enough. You can also seek help of your network team to verify if network is working as expected, and no latency or packet loss is being observed.

Popular Posts - All Times