Wednesday, November 27, 2019

Restricting Access/Connections to the Database

In order to restrict the connections to the database we can add some parameters in the sqlnet.ora file under ORACLE_HOME form where listener is running. This would make sure that sessions to the database are coming only from specific list of hosts, and any session from all other hosts is not allowed to connect to the database. Following are the parameters we can use for such restriction.

Saturday, November 16, 2019

Archivelog Deletion Policy for RMAN

RMAN archive deletion policy is used to set when we want archived logs to be deleted when DELETE ARCHIVELOG command is executed from the RMAN prompt, or if we are using Fast Recovery Area for archived logs, and are not deleting archived logs manually and relying on automatic feature of Oracle for deletion of archived logs (archive deletion would happen automatically if space crunch is observed by Oracle under fast recovery area).

Wednesday, October 30, 2019

ORA-12546: TNS:permission denied

I recently faced this error message while trying to connect with the database (running on my local Windows host) using a TNS service. Although connecting without TNS service was working fine, however, connection was failing with ORA-12546 when I tried to connect using TNS service. Following is what I faced.

Wednesday, October 16, 2019

ORA-01511 and ORA-01523

While renaming a datafile or redo log file you might face these both errors together and command would fail as shown below.
SQL> ALTER DATABASE RENAME FILE ‘d:\oracle\oradata\db\users01.dbf’ to ‘e:\oracle\oradata\users01.dbf’;
ALTER DATABASE RENAME FILE ‘d:\oracle\oradata\db\users01.dbf’ to ‘e:\oracle\oradata\users01.dbf’;
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01523: cannot rename datafile to d:\oracle\oradata\db\users01.dbf’ –
File already part of database

Thursday, October 10, 2019

ORA-01000: maximum open cursors exceeded

Cursor is a pointer to a memory area which is opened to execute an SQL statement issued by the end user/application. It means that every SQL (SELECT/DML) you want to execute would require utilizing this memory area to execute the SQL.

Wednesday, September 18, 2019

ORA-01186 ORA-01157 ORA-01111 ORA-01110

Managed Standby Recovery starting Real Time Apply
Thu Sep 19 04:05:35 2019
Errors in file /u01/app/oracle/diag/rdbms/mydb/mydb/trace/rgb1p117_dbw0_10657.trc:
ORA-01186: file 166 failed verification tests
ORA-01157: cannot identify/lock data file 166 - see DBWR trace file
ORA-01111: name for data file 166 is unknown - rename to correct file
ORA-01110: data file 166: '/u01/app/oracle/product/12.1.0/dbhome_1/dbs/UNNAMED00166'
Thu Sep 19 04:05:35 2019
File 166 not verified due to error ORA-01157
Thu Sep 19 04:05:35 2019

Thursday, September 12, 2019

ORA-01438: value larger than specified precision allowed for this column

You may also want to see this article about the ORA-12899 which is returned if a value larger than column’s width is inserted in the column. Similarly, ORA-01438 is returned if value being inserted is larger than what is defined for the NUMBER datatype column. Number datatype columns are defined in precision and scale (NUMBER(p,s)). If you define a number column as “NUMBER(5,2)”, it would mean that maximum width of the data could be 5 digits, out of which 2 will be decimal part (for example 123.45). Following are some examples that explain this concept further.

Tuesday, September 3, 2019

ORA-12899: value too large for column...

You may want to learn about a similar error message ORA-01438 caused by larger values being inserted in NUMBER columns. 
Message displayed with error ORA-12899 is self-explained. This error would come if you are trying to insert into a table’s column which is not big enough to hold the data being inserted. Suppose you define a column with a width of 10 characters (VARCHAR2(10)), and later if you try to insert a value longer than 10 characters, ORA-12899 would be returned. To solve this problem you can either reduce the data width being inserted, or alternatively increase the column width. If you have a multi-byte database characterset,  a VARCHAR2 column with a width of 10 may not store exactly 10 characters. Following is one example to produce ORA-12899.

Tuesday, August 27, 2019

DBUA Does not Find parameter file to Start Database for Upgrade

While upgrading my database form using database upgrade assistant (DBUA), it showed me error that said following

The Upgrade Assistant failed in bringing up the database <db_name>. Oracle Home <ORACLE_HOME> obtained from file /etc/oratab was used to connect to the database. Either database is not running from Oracle Home <ORACLE_HOME> or correct initialization Parameter file (pfile) was not found.
To start the database, Oracle needs the pfile. Provide the full path name of the init file for the database <db_name>
Screenshot of DBUA can be seen bellow

Thursday, August 15, 2019

PING[ARC1]: Heartbeat failed to connect to standby 'my_standby'. Error is 12154

If your primary database is not able to ship archived logs to the standby database with error ORA-12154, there is something wrong with the TNS entry you are using in LOG_ARCHIVE_DEST_n parameter that points to your standby database. If you check the alert log file of the primary database you will see error messages similar to the following.

Popular Posts - All Times